CREATE OR REPLACE PACKAGE BODY APPS.CALL_BPEL_PROCESS
IS
--Main Procedure to be called from a workflow.
PROCEDURE process_contract(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funmode IN VARCHAR2,
result OUT VARCHAR2
)
IS
l_date_signed VARCHAR2(100);
l_contract_number VARCHAR2(120);
l_opportunity_id VARCHAR2(450);
l_status VARCHAR2(100);
l_email VARCHAR2(240);
l_message VARCHAR2(4000);
l_debug_loc VARCHAR2(4000);
l_type VARCHAR2(30);
custom_exception EXCEPTION;
BEGIN
BEGIN
SELECT TO_CHAR(NVL(OKHB.date_approved,SYSDATE),'RRRR-MM-DD') DATE_SIGNED
,OKHB.attribute7 --clarified that we may not need to replace ; stored to commas. we would retains as suggested in REKS replace(attribute7,';',',') if reqd
--,REPLACE(OKHB.attribute7,';',',') attribute7
,OKHB.scs_code
,FU.email_address email
INTO l_date_signed
,l_opportunity_id
,l_type
,l_email
FROM okc_k_headers_b OKHB
,fnd_user FU
WHERE OKHB.contract_number = ITEMKEY
AND FU.user_id = OKHB.created_by;
EXCEPTION
WHEN no_data_found THEN
l_debug_loc := 'No details returned for contract passed';
RAISE custom_exception;
END;
IF FUNMODE = 'RUN' THEN
soap_main (
P_Contract_number => ITEMKEY,
p_status => 'Closed Won - Shipment or Service Booking', --'CLOSE',
p_signed_date => l_date_signed,
p_opportunity_id => l_opportunity_id,
p_email => l_email, --'sriramv@xx.com', ---Waiting on clarification, this would be derived based on business decision
x_status => l_status,
x_message => l_message
);
IF l_status ='SUCCESS' OR l_status IS NULL THEN
result :='COMPLETE:SUCCESS';
ELSE
result :='COMPLETE:ERROR';
END IF;
END IF;
EXCEPTION
WHEN custom_exception THEN
WF_CORE.CONTEXT(
pkg_name => 'CALL_BPEL_PROCESS',
proc_name => 'PROCESS_CONTRACT',
arg1 => l_debug_loc,
arg2 => null,
arg3 => null,
arg4 => null,
arg5 => null);
RAISE;
END process_contract;
--main procedure to call BPEL process using UTL_HTTP package.
PROCEDURE soap_main
(
P_Contract_number IN VARCHAR2,
p_status IN VARCHAR2,
p_signed_date IN VARCHAR2,
p_opportunity_id IN VARCHAR2,
p_email IN VARCHAR2,
x_status OUT VARCHAR2,
x_message OUT VARCHAR2
)
IS
ws_endpoint VARCHAR2 (2500);
ws_operation VARCHAR2 (200);
ws_soap_request VARCHAR2 (32767);
ws_soap_response NCLOB;
v_xmldoc DBMS_XMLDOM.domdocument;
buf VARCHAR2 (32767);
l_status VARCHAR2 (20);
l_err_msg VARCHAR2 (2400);
l_ws_process_name VARCHAR2 (240);
BEGIN
BEGIN
SELECT description
INTO ws_endpoint
FROM fnd_lookup_values
WHERE lookup_type = 'XX_OKS_SFDC_WEBSRV_LKP'
AND lookup_code = 'URL_WS_ORASFDC';
EXCEPTION
WHEN OTHERS
THEN
x_status := 'E';
x_message := 'WS End Point Location not added in Lookup';
END;
--Outcome: http://soaprodapp.xx.com:7777/orabpel/sfdcservices/SfdcAutoLinkageSvc/1.0
BEGIN
SELECT description
INTO l_ws_process_name
FROM fnd_lookup_values
WHERE lookup_type = 'XX_OKS_SFDC_WEBSRV_LKP'
AND lookup_code = 'WS_PROCESS_NAME';
EXCEPTION
WHEN OTHERS THEN
x_status := 'E';
x_message := 'WS Process Name not added in Lookup';
END;
--Outcome: SfdcAutoLinkageSvc
ws_operation := 'process';
--Building input XML payload to the BPEL process.
ws_soap_request :=
'
--Invoking BPEL web service
ws_soap_response := invoke_web_service
(ws_endpoint => ws_endpoint,
ws_operation => ws_operation,
ws_soap_request => ws_soap_request
);
--Converting NCLOB response to XML document
v_xmldoc := get_xml_document (ws_soap_response);
DBMS_XMLDOM.writetobuffer (v_xmldoc, buf);
SELECT REPLACE (buf, 'env:')
INTO buf
FROM DUAL;
SELECT REPLACE (buf,
'xmlns:ns1="http://xmlns.oracle.com/'
|| l_ws_process_name
|| '"'
)
INTO buf
FROM DUAL;
SELECT REPLACE (buf,
' xmlns="http://xmlns.oracle.com/'
|| l_ws_process_name
|| '"'
)
INTO buf
FROM DUAL;
SELECT REPLACE (buf, 'ns1:')
INTO buf
FROM DUAL;
SELECT REPLACE (buf, '
INTO buf
FROM DUAL;
SELECT REPLACE (buf, '
INTO buf
FROM DUAL;
BEGIN
SELECT XMLTYPE (buf).EXTRACT
( '/Envelope/Body/'
|| l_ws_process_name
|| 'ProcessResponse/statusCode/text()'
).getstringval (),
XMLTYPE (buf).EXTRACT
( '/Envelope/Body/'
|| l_ws_process_name
|| 'ProcessResponse/message/text()'
).getstringval ()
INTO x_status,
x_message
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
x_status := NULL;
DBMS_OUTPUT.PUT_LINE
( 'Error While XML Parser for CONTRACT-'|| P_Contract_number|| ' ErrMesg:XML EXTRACT Failed');
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE
( 'When Others Error '
|| SUBSTR (SQLERRM, 1, 500)
);
END soap_main;
FUNCTION invoke_web_service
(
ws_endpoint IN VARCHAR2 DEFAULT NULL,
ws_operation IN VARCHAR2 DEFAULT 'process',
ws_soap_request IN VARCHAR2 DEFAULT NULL
)
RETURN NCLOB
AS
soap_request VARCHAR2 (30000);
soap_response NCLOB;
--
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
l_excep_err VARCHAR2 (32767);
BEGIN
soap_request := ws_soap_request;
UTL_HTTP.set_response_error_check (TRUE);
UTL_HTTP.set_detailed_excp_support (TRUE);
http_req := UTL_HTTP.begin_request (ws_endpoint, 'POST', 'HTTP/1.1');
UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (soap_request));
UTL_HTTP.set_header (http_req, 'SOAPAction', ws_operation);
UTL_HTTP.write_text (http_req, soap_request);
--
http_resp := UTL_HTTP.get_response (http_req);
--
UTL_HTTP.read_text (http_resp, soap_response);
UTL_HTTP.end_response (http_resp);
RETURN soap_response;
EXCEPTION
WHEN UTL_HTTP.request_failed THEN
l_excep_err := 'WS Exception : Request failed - '|| UTL_HTTP.get_detailed_sqlerrm;
DBMS_OUTPUT.PUT_LINE ('Error While Invoking WS ' || l_excep_err);
WHEN UTL_HTTP.http_server_error THEN
l_excep_err := 'WS Exception : Server Error - ' || UTL_HTTP.get_detailed_sqlerrm;
DBMS_OUTPUT.PUT_LINE ('Error While Invoking WS ' || l_excep_err);
WHEN UTL_HTTP.http_client_error THEN
l_excep_err := 'WS Exception - Client Error - ' || UTL_HTTP.get_detailed_sqlerrm;
WHEN OTHERS THEN
l_excep_err := 'WS Exception : Others - ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE ('Error While Invoking WS ' || l_excep_err);
END invoke_web_service;
FUNCTION get_xml_document (ws_soap_response IN NCLOB)
RETURN DBMS_XMLDOM.domdocument
AS
xml_doc DBMS_XMLDOM.domdocument;
BEGIN
xml_doc := DBMS_XMLDOM.newdomdocument (SYS.XMLTYPE (ws_soap_response));
RETURN xml_doc;
END get_xml_document;
/*** This procedure used to invoke the SFDC Autholinkage BPEL process thru Concurrent Prog.****/
PROCEDURE submit_bpel_process(err_buf OUT VARCHAR2,
ret_code OUT VARCHAR2,
p_cont_number IN VARCHAR2)
IS
l_date_signed VARCHAR2(100);
l_contract_number VARCHAR2(120);
l_opportunity_id VARCHAR2(450);
l_status VARCHAR2(100);
l_email VARCHAR2(240);
l_message VARCHAR2(4000);
l_type VARCHAR2(30);
custom_exception EXCEPTION;
BEGIN
fnd_file.put_line (fnd_file.OUTPUT,'SFDC AutoLinkage Process Status for Contrat Number :' || p_cont_number);
fnd_file.put_line (fnd_file.OUTPUT,'============================================================================');
BEGIN
SELECT TO_CHAR(NVL(OKHB.date_approved,SYSDATE),'RRRR-MM-DD') DATE_SIGNED
,OKHB.attribute7 --clarified that we may not need to replace ; stored to commas. we would retains as suggested in REKS replace(attribute7,';',',') if reqd
--,REPLACE(OKHB.attribute7,';',',') attribute7
,OKHB.scs_code
,FU.email_address email
INTO l_date_signed
,l_opportunity_id
,l_type
,l_email
FROM okc_k_headers_b OKHB
,fnd_user FU
WHERE OKHB.contract_number = p_cont_number
AND FU.user_id = OKHB.created_by;
EXCEPTION
WHEN no_data_found THEN
RAISE custom_exception;
END;
fnd_file.put_line (fnd_file.OUTPUT,'Invoke SFDC AutoLinkage BPEL Process');
fnd_file.put_line (fnd_file.OUTPUT,' ');
fnd_file.put_line (fnd_file.OUTPUT,' ');
fnd_file.put_line (fnd_file.OUTPUT,'Submit Request - Input Parameter Value');
fnd_file.put_line (fnd_file.OUTPUT,'SVC Signed Date :' || l_date_signed);
fnd_file.put_line (fnd_file.OUTPUT,'Opportunity ID :' || l_opportunity_id);
fnd_file.put_line (fnd_file.OUTPUT,'SVC Category :' || l_type);
fnd_file.put_line (fnd_file.OUTPUT,'Email :' || l_email);
soap_main (
P_Contract_number => p_cont_number,
p_status => 'Closed Won - Shipment or Service Booking', --'CLOSE',
p_signed_date => l_date_signed,
p_opportunity_id => l_opportunity_id,
p_email => l_email, --'sriramv@xx.com', ---Waiting on clarification, this would be derived based on business decision
x_status => l_status,
x_message => l_message
);
fnd_file.put_line (fnd_file.OUTPUT,' ');
fnd_file.put_line (fnd_file.OUTPUT,'SFDC AutoLinkage BPEL Response');
fnd_file.put_line (fnd_file.OUTPUT,' Status : '||l_status);
fnd_file.put_line (fnd_file.OUTPUT,' Message : '||l_message);
EXCEPTION
WHEN custom_exception THEN
err_buf := 'No details returned for contract passed';
ret_code := 0;
END submit_bpel_process;
END CALL_BPEL_PROCESS;
/
