Skip to main content
Question

Using PLSQL Event action with REST call for validation

  • June 6, 2023
  • 1 reply
  • 474 views

Forum|alt.badge.img+2

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.

1 reply

Forum|alt.badge.img+2
  • Author
  • Do Gooder (Customer)
  • 3 replies
  • June 13, 2023

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings