This document will give you high level overview about export compliance service integration with your Oracle E-bussiness suite (Order Management and Shipping Execution).
It is very simple.
What you need to know for this integration.
- Oracle E-Busniess Suite 11i/R12 ( Atleast Order Management and Shipping Execution)
- Oracle workflow customization
- Oracle PL/SQL and web service
- ECS Account (Kewill, eCustoms etc.)
STEP 1: Create a view to get ship to party information from Order Management.
SELECT oeh.header_id,
oeh.order_number,
stc.customer_id ship_to_cust_id,
stc.customer_number ship_to_cust_number,
stc.customer_name ship_to_customer,
TRIM(TRIM( slc.address1
|| ' '
|| slc.address2
|| ' '
|| slc.address3
|| ' '
|| slc.address4)
|| ' '
|| slc.city
|| ' '
|| slc.state
|| ' '
|| slc.postal_code)
ship_to_address,
slc.country ship_to_country,
sctc.contact_id ship_to_contact_id,
sctc.first_name || ' ' || sctc.last_name ship_to_contact
FROM oe_order_headers_all oeh,
hz_cust_site_uses_all stsu,
hz_cust_acct_sites_all stcas,
hz_party_sites sps,
hz_locations slc,
ra_customers stc,
ra_contacts sctc
WHERE 1 = 1
AND stsu.site_use_id = oeh.ship_to_org_id
AND stcas.cust_acct_site_id = stsu.cust_acct_site_id
AND stc.customer_id = stcas.cust_account_id
AND sps.party_site_id = stcas.party_site_id
AND slc.location_id = sps.location_id
AND sctc.contact_id(+) = oeh.ship_to_contact_id
AND oeh.header_id = p_oe_hdr_id;
STEP 2: Write a custom PL/SQL program to generate SOAP message and populate SHIP TO information.
Note: Following SOAP request example based on Kewill export compliance WSDL structure.
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<executeScreening xmlns="'||gbl_exp_url||'">
<request>
<administration>
<subscriberId>'||gbl_user_id||'</subscriberId>
<subscriberPassword>'||gbl_user_pwd||'</subscriberPassword>
</administration>
<serviceRequest>
<transaction>
<userDefined>'||rec1.order_number||'</userDefined>
<shipFromCountry>
<domain>COUNTRY</domain>
<code>US</code>
</shipFromCountry>
<party>
<id>'||rec1.ship_to_cust_id||'</id>
<type>SHIP_TO</type>
<name>'||replace_special_chr(rec1.ship_to_customer)||'</name>
<address>'||replace_special_chr(rec1.ship_to_address)||'</address>
<country>
<domain>COUNTRY</domain>
<code>'||replace_special_chr(rec1.ship_to_country)||'</code>
</country>
<userDefined>'||rec1.ship_to_cust_id||'-SHTO</userDefined>
</party>
<party>
<id>'||rec1.ship_to_contact_id||'</id>
<type>SHTO_CONTACT</type>
<name>'||replace_special_chr(rec1.ship_to_contact)||'</name>
<address>'||replace_special_chr(rec1.ship_to_address)||'</address>
<country>
<domain>COUNTRY</domain>
<code>'||replace_special_chr(rec1.ship_to_country)||'</code>
</country>
<userDefined>'||rec1.ship_to_contact_id||'-SHTO-CTC</userDefined>
</party>
</transaction>
</serviceRequest>
<parameters>
<threshold>'||gbl_threshold||'</threshold>
<countryMatch>'||gbl_countryMatch||'</countryMatch>
<redflag>'||gbl_redflag||'</redflag>
</parameters>
</request>
</executeScreening>
</soapenv:Body>
</soapenv:Envelope>
STEP 3: Use oracle's UTL_HTTP to call export compliance web service and submit SOAP request with POST method
CREATE OR REPLACE PROCEDURE xx_ECS_check_prc (
p_soap_request IN CLOB,
x_soap_respond OUT CLOB,
x_return_status OUT varchar2,
x_message OUT varchar2
)
IS
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
resp xmltype;
i integer;
BEGIN
UTL_HTTP.SET_WALLET (PATH => p_path, password => p_password);
http_req := UTL_HTTP.begin_request (p_url, 'POST', 'HTTP/1.1');
UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (p_soap_request));
UTL_HTTP.set_header (http_req, 'SOAPAction', '');
UTL_HTTP.write_text (http_req, p_soap_request);
http_resp := UTL_HTTP.get_response (http_req);
UTL_HTTP.read_text (http_resp, x_soap_respond);
UTL_HTTP.end_response (http_resp);
x_return_status := 'S';
EXCEPTION
WHEN UTL_HTTP.Request_Failed
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN UTL_HTTP.Http_Server_Error
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN UTL_HTTP.Http_Client_Error
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN OTHERS
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
END;
STEP 4: Parse SOAP response and check party denial status. If ship to party under denial status then call Order Management API to put order on hold.
CREATE OR REPLACE PROCEDURE appy_hold (
p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
x_return_status OUT varchar2,
x_msg_data OUT varchar2
)
IS
x_msg_count number;
x_msg_index NUMBER;
x_data VARCHAR2 (2000);
BEGIN
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (5);
oe_msg_pub.initialize;
/* --Sample Data for Hold
p_hold_source_rec := OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC;
p_hold_source_rec.hold_id := 23; -- hold_id--OE_HOLD_DEFINITIONS
p_hold_source_rec.hold_entity_code := 'O'; -- order level hold
p_hold_source_rec.hold_entity_id := 985736; -- header_id of the order
p_hold_source_rec.header_id := 985736; -- header_id of the order
p_hold_source_rec.HOLD_COMMENT :='Export Compliance Hold based on ship to country';
*/
OE_Holds_PUB.Apply_Holds (
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_NONE,
p_hold_source_rec => p_hold_source_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status <> FND_API.G_RET_STS_SUCCESS
THEN
FOR i IN 1 .. x_msg_count
LOOP
OE_MSG_PUB.get (p_msg_index => i,
p_encoded => FND_API.G_FALSE,
p_data => x_data,
p_msg_index_out => x_msg_index);
DBMS_OUTPUT.put_line ('OE Hold Error:' || x_data);
x_msg_data := TRIM (x_msg_data) || ' ' || x_data;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
x_return_status := 'E';
x_msg_data := SQLERRM;
END appy_hold;
STEP 5: Now customize Order Header Workflow to call custom ECS program at booking event.
Web service integration overview.
Now your System (Order Management/Shipping Execution) is ready to go.
Benefits:
- Realtime ECS integration
- No Third party service/application required
- Easy Development/Customizable
- Maintenance free and
- Cost effective
Thanks for ones marvelous posting! I seriously enjoyed reading it, you're a great author.I will be sure to bookmark your blog and definitely will come back in the foreseeable future. I want to encourage you continue your great job, have a nice day!
ReplyDeleteHi,The colour red is awfully evocative, which has a tendency to bring a memory in Web Design Cochin, mood, or photographs, for instance, lightly or indirectly to mind. when using web design cardiff it is not solely about creating something charming. Thanks.....
ReplyDelete