create or replace PROCEDURE XX_ESB_SOAP_REQUEST(
ERRBUFF OUT VARCHAR2 ,
RETCODE OUT NUMBER ,
P_IN_MODULE IN VARCHAR2,
P_IN_KEY_VALUE1 IN VARCHAR2,
P_IN_KEY_VALUE2 IN VARCHAR2)
IS
/******************************************************************************************
--HEADER: XXESBREQ.prc
--Description: This Procedure contains the HTTP SOAP trigger to ESB.
********************************************************************************************/
v_soap_request VARCHAR2(3000);
v_soap_response VARCHAR2(1500);
http_req utl_http.req;
http_resp utl_http.resp;
v_len NUMBER;
v_txt VARCHAR2(1500);
v_error_text VARCHAR2(1500);
v_key_value1 VARCHAR2(100);
v_key_value2 VARCHAR2(100);
v_curr_date VARCHAR2(100);
v_esb_soap_url VARCHAR2(100);
BEGIN
--Initialization
v_soap_request := NULL;
v_soap_response := NULL;
v_len := 0;
v_txt := NULL;
v_error_text := NULL;
v_esb_soap_url := NULL;
IF TRIM(P_IN_MODULE) = 'MDM_ORCL_CUST_INT' THEN
v_key_value1 := TRIM(P_IN_KEY_VALUE1); --MDM_PARTY_ID
--ESB Status Code from the staging table.
--Modified for ver1.1
--v_key_value2 := 'MAP-REQ';
v_key_value2 := TRIM(P_IN_KEY_VALUE2);
v_curr_date := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MM:SS');
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Begin Processing Request....... ');
FND_FILE.PUT_LINE(FND_FILE.LOG,'P_IN_MODULE: '||P_IN_MODULE);
FND_FILE.PUT_LINE(FND_FILE.LOG,'MDM_PARTY_ID: '||v_key_value1);
FND_FILE.PUT_LINE(FND_FILE.LOG,'ESB_STATUS_CODE: '||v_key_value2);
--Build SOAP Request
v_soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>'||
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v1="http://companyname.net/controller/v1.0">
<soapenv:Header/>
<soapenv:Body>
<v1:notify>
<eventRequest>
<events>
<userID>EBSService</userID>
<passPhrase></passPhrase>
<creationTime>'||v_curr_date||'</creationTime>
<action>CREATE</action>
<entity>MDM_CUSTOMERMASTER</entity>
<recordCount>1</recordCount>
<primaryKey>'||v_key_value1||'</primaryKey>
<transactionType>SINGLE</transactionType>
<sourceSystem>EBS</sourceSystem>
<eventType>'||v_key_value2||'</eventType>
</events>
</eventRequest>
</v1:notify>
</soapenv:Body>
</soapenv:Envelope>';
--Connecting to ESB Master Controller
-- Query to retrieve EBS URL Based on Oracle EBS Instance
BEGIN
SELECT FFVV.DESCRIPTION
INTO v_esb_soap_url
FROM FND_FLEX_VALUE_SETS FFVS
, FND_FLEX_VALUES_VL FFVV
WHERE FFVV.FLEX_VALUE = (SELECT INSTANCE_NAME FROM V$INSTANCE)
And FFVS.FLEX_VALUE_SET_ID = FFVV.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_NAME = 'XX_ESB_URL_LIST';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR NO ESB Controller URL defined. '||SUBSTR(SQLERRM,1,100));
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR while retrieving ESB Controller URL. '||SUBSTR(SQLERRM,1,100));
END;
http_req:= utl_http.begin_request
( v_esb_soap_url
, 'POST'
, 'HTTP/1.1'
);
UTL_HTTP.SET_HEADER(HTTP_REQ, 'Content-Type', 'text/xml charset=UTF-8');
UTL_HTTP.SET_HEADER(HTTP_REQ, 'Content-Length', LENGTH(v_soap_request));
UTL_HTTP.SET_HEADER(HTTP_REQ, 'SOAPAction','"MP-ORCL-MDM"'); -- SOAP Request HEader Attribute added
UTL_HTTP.WRITE_TEXT(http_req, v_soap_request);
http_resp:= UTL_HTTP.GET_RESPONSE(http_req);
-- Obtain the length of the response
utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1);
BEGIN
utl_http.read_text(http_resp, v_txt, v_len);
v_soap_response := v_txt; --Read the response
EXCEPTION
WHEN Utl_Http.End_Of_Body THEN NULL;
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While parsing the SOAP response. '||SUBSTR(SQLERRM,1,100));
END;
utl_http.end_response(http_resp);
FND_FILE.PUT_LINE(FND_FILE.LOG,'soap_response: '||v_soap_response);
--Update the SOAP response
IF v_soap_response IS NOT NULL THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Oracle - ESB trigger successful. ');
BEGIN
UPDATE XX_MDM_CUSTOMER_STG_ALL
SET ESB_RESPONSE = v_soap_response
,ERROR_CODE = 'TRIGGER_SUCCESS'||' '||ERROR_CODE
,LAST_UPDATED_BY = 'ARCUSTOMER'
,LAST_UPDATE_DATE = SYSDATE
WHERE MDM_PARTY_ID = v_key_value1
AND ESB_STATUS_CODE = v_key_value2
AND NVL(PROCESSED_FLAG,'N') = 'Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Response successfully updated on the staging table for Party Id: '||v_key_value1);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Updating the ESB Response on the staging table. '||SUBSTR(SQLERRM,1,100));
END;
END IF;
--Procedure Level exception
EXCEPTION
WHEN OTHERS THEN
v_error_text := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Triggering ESB.....'||SUBSTR(SQLERRM,1,100));
BEGIN
UPDATE XX_MDM_CUSTOMER_STG_ALL
SET ESB_RESPONSE = v_error_text
,ERROR_CODE = 'TRIGGER_FAILURE'||' '||ERROR_CODE
,ERROR_DESCRIPTION = v_error_text||' '||ERROR_DESCRIPTION
,LAST_UPDATED_BY = 'ARCUSTOMER'
,LAST_UPDATE_DATE = SYSDATE
WHERE MDM_PARTY_ID = v_key_value1
AND ESB_STATUS_CODE = v_key_value2
AND NVL(PROCESSED_FLAG,'N') = 'Y';
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR While Updating the TRIGGER_FAILURE exception Details. '||SUBSTR(SQLERRM,1,100));
END;
END;
No comments:
Post a Comment