Skip to main content

My goal is to have “Execute Online SQL” event action to make a rest request to our API and then use the result of this call to show the warning or error if needed. 

 

I figured that i can show warn/err by using Error_SYS.Appl_general or Client_SYS.Add_Warning

I also know there is Plsql_Rest_Sender_API available. 

 

I would appreciate if somebody could post some sample code if they have done something like this. 

Thank you in advance. 

 

PS: We are using Aurena hosted in IFS cloud, so our access to the database is limited.

I almost got there . if this code helps someone. see below. I know it is specific to our custom entity, but essentially it calls our web service which returns a json object like so

 

{"severity": "Warning", "result": "Your message goes here"}

I am saying almost, because I am having issues where on-line sql does not like variables declared as PLSQLAP_DOCUMENT_API.Document

declare

PROCEDURE PROCESS_RESPONSE(RESPONSE_ IN CLOB)
IS

JSON_STR_ CLOB;
START_ NUMBER;
END_ NUMBER;
JSON_OBJ_ JSON_OBJECT_T;
RESULT_ varchar(4000);
SEVERITY_ varchar(20);

BEGIN
DBMS_OUTPUT.PUT_LINE(JSON_STR_);
START_ := INSTR(RESPONSE_, '{');
END_ := INSTR(RESPONSE_, '}', -1);
JSON_STR_ := SUBSTR(RESPONSE_, START_, END_ - START_ + 1);
JSON_OBJ_ := JSON_OBJECT_T.PARSE(JSON_STR_);

--JSON_OBJ_ := TREAT(JSON_OBJ_.GET_OBJECT('data') AS JSON_OBJECT_T);

RESULT_ := JSON_OBJ_.GET_STRING('result');
SEVERITY_ := JSON_OBJ_.GET_STRING('severity');
DBMS_OUTPUT.PUT_LINE('Returned response: ' || SEVERITY_ || ' - ' || RESULT_);

IF SEVERITY_ = 'Error' THEN
ERROR_SYS.APPL_GENERAL('TrucksForSuppliers', RESULT_);
ELSIF SEVERITY_ = 'Warning' THEN
Client_SYS.ADD_WARNING('TrucksForSuppliers',RESULT_);
ELSIF SEVERITY_ like 'Info%' THEN
Client_SYS.ADD_INFO('TrucksForSuppliers',RESULT_);
END IF;
END PROCESS_RESPONSE;

FUNCTION CALL_VALIDATION_API(
PAYLOAD IN clob,
ENDPOINT IN varchar2
) RETURN clob
IS
PAYLOAD_ CLOB;
ENDPOINT_ varchar(2000);
QUERY_PARAMS_ PLSQLAP_DOCUMENT_API.DOCUMENT;
URL_PARAMS_ PLSQLAP_DOCUMENT_API.DOCUMENT;

BEGIN
PAYLOAD_ := PAYLOAD;
ENDPOINT_ := ENDPOINT;
BEGIN
QUERY_PARAMS_ := PLSQLAP_DOCUMENT_API.NEW_DOCUMENT('query_params');
PLSQLAP_DOCUMENT_API.ADD_ATTRIBUTE(QUERY_PARAMS_, 'parameter1', 'GetText');

URL_PARAMS_ := PLSQLAP_DOCUMENT_API.NEW_DOCUMENT('url_params');
PLSQLAP_DOCUMENT_API.ADD_ATTRIBUTE(URL_PARAMS_, 'endpoint_url', ENDPOINT_);


PLSQL_REST_SENDER_API.CALL_REST_ENDPOINT_JSON_SYNC(
REST_SERVICE_ => 'PLS_VALIDATION',
SENDER_ => 'PLS_INTEGRATIONS',
URL_PARAMS_ => URL_PARAMS_,
QUERY_PARAMETERS_ => QUERY_PARAMS_,
JSON_ => PAYLOAD_,
HTTP_METHOD_ => 'POST',
HTTP_REQ_HEADERS_ => 'Content-Type: application/json',
INCLD_RESP_INFO_ => TRUE,
FND_USER_ => Fnd_Session_API.Get_Fnd_User()
);
RETURN PAYLOAD_;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
ERROR_SYS.APPL_GENERAL('TrucksForSuppliers', 'Error: ' || SQLERRM);
END;

END CALL_VALIDATION_API;

FUNCTION GET_PAYLOAD(
OBJKEY_ VARCHAR
) RETURN CLOB
IS
TRUCKJSON clob;
BEGIN
SELECT JSON_SERIALIZE(JSON_OBJECT(
'OBJECTKEY' VALUE T.OBJKEY,
'ACCOUNTPROMPT' VALUE CF$_ACCOUNT_PROMPT_DB,
'MATERIALPROMPT' VALUE CF$_MATERIAL_PROMPT_DB,
'JOBPROMPT' VALUE CF$_JOB_PROMPT_DB,
'TAREWEIGHT' VALUE CF$_TRUCK_TARE_WEIGHT,
'FILLTARGET' VALUE CF$_FILL_TARGET,
'GROSSLIMIT' VALUE CF$_TRUCK_GROSS_WEIGHT_LIMIT,
'TRUCKID' VALUE CF$_TRUCK_ID,
'TRUCKPIN' VALUE CF$_TRUCK_PIN,
'TRUCKSTATUS' VALUE CF$_TRUCK_STATUS_DB,
'CUSTOMERORDERNUMBER' VALUE L.ORDER_NO,
'CUSTOMERORDERLINENUMBER' VALUE L.LINE_NO,
'CUSTOMERORDERLINERELE' VALUE L.REL_NO,
'CUSTOMERORDERLINESTATUS' VALUE L.OBJSTATE,
'CUSTOMERORDERSTATUS' VALUE O.OBJSTATE,
'CUSTOMERORDERHAULERID' VALUE O.CF$_HAULER_ID,
'CUSTOMERORDERAWSPROMPT' VALUE O.CF$_PLS_RW_AWS_PROMPT,
'CUSTOMERORDERLINEAWSPROMPT' VALUE L.CF$_PLS_RW_AWS_PROMPT,
'CUSTOMERORDERLINEAWSENABLED' VALUE L.CF$_PLS_RW_AWS_ENABLED
))
INTO TRUCKJSON
FROM TRUCKS_FOR_SUPPLIERS_CLV T
LEFT JOIN CUSTOMER_ORDER_LINE_CFV L ON T.CF$_LAST_JOB_DB = L.OBJKEY
LEFT JOIN CUSTOMER_ORDER_CFV O ON O.ORDER_NO = L.ORDER_NO
WHERE T.OBJKEY = OBJKEY_ FETCH FIRST ROW ONLY;
RETURN TRUCKJSON;
END GET_PAYLOAD;

BEGIN
PROCESS_RESPONSE(
CALL_VALIDATION_API(
PAYLOAD => GET_PAYLOAD('E981355C7ED65A2EE053A443EF0ABEB9'),
ENDPOINT => 'your validation url'
)
);
END;


 


Reply