Initiating a webservice API from Plsql package !!
create or replace PACKAGE xxat_3pl_webservice_pkg
AS
--===============================================================================================
-- Program name Creation Date Original Version Created by JIRA
-- xx_webapi_dhl_fnc *********** 1.0
-- -----------------------------------------------------------------------------------------------
-- Description:
-- This function will use a dummy waybill number for 3PL carriers and get the original waybill number.
-- This program uses SOA platform to post and get the information from 3PL carrier.
--
-- Parameter : p_waybill_dummy_no IN Scan SHIP by user
--
-- -----------------------------------------------------------------------------------------------
-- Modification History:
-- Modified Date Version Done by Change Description
-- **-***-**** 1.1
-- ===============================================================================================
FUNCTION xx_webapi_dhl_fnc(p_waybill_dummy_no IN VARCHAR2
,p_3pl_carrier IN VARCHAR2) RETURN VARCHAR2;
FUNCTION prepare_json_3pl_service (p_waybill_dummy_no IN VARCHAR2
, p_delivery_no IN VARCHAR2) RETURN CLOB;
PROCEDURE main (errbuff OUT VARCHAR2
,retcode OUT NUMBER
,p_waybill_dummy_no IN VARCHAR2
,p_carrier IN VARCHAR2);
FUNCTION xx_total_declared_val(p_delivery_no IN VARCHAR2
--added for ver 1.1 for FedEx Start
,p_3pl_carrier IN VARCHAR2
,p_sell_price_flag IN VARCHAR2
,p_selling_price OUT NUMBER
,p_item_id IN NUMBER
--ver 1.1 End
) RETURN VARCHAR2;
-- ver 1.1 FedEx Start
FUNCTION xx_webapi_FedEx_fnc(p_waybill_dummy_no IN VARCHAR2
,p_3pl_carrier IN VARCHAR2) RETURN VARCHAR2;
FUNCTION prepare_json_FedEx_service (p_waybill_dummy_no IN VARCHAR2
, p_delivery_no IN VARCHAR2) RETURN CLOB;
--ver 1.1 FedEx End
END xxat_3pl_webservice_pkg;
/
create or replace PACKAGE BODY xxat_3pl_webservice_pkg
AS
--===============================================================================================
-- Program name Creation Date Original Version Created by JIRA
-- xx_webapi_dhl_fnc **-***-**** 1.0 ********
-- -----------------------------------------------------------------------------------------------
-- Description:
-- This function will use a dummy waybill number for 3PL carriers and get the original waybill number.
-- This program uses SOA platform to post and get the information from 3PL carrier.
--
-- Parameter : p_waybill_dummy_no IN Scan SHIP by user
--
-- -----------------------------------------------------------------------------------------------
-- Modification History:
-- Modified Date Version Done by Change Description
-- **-***-**** 1.1 ******** Modified for webservice.
-- **-***-**** 1.2 ******** Modified for webservice2. Involves using
-- Commercial Invoice and upto 5 decimal points for transmission.
-- ===============================================================================================
FUNCTION xx_webapi_dhl_fnc(p_waybill_dummy_no IN VARCHAR2
,p_3pl_carrier IN VARCHAR2) RETURN VARCHAR2
AS
l_instance VARCHAR2(100);
l_url VARCHAR2(1000);
l_username VARCHAR2(50);
l_password VARCHAR2(50);
l_wallet_url VARCHAR2(1000);
req UTL_HTTP.REQ;
l_content CLOB; --VARCHAR2(4000);
l_sub_content CLOB;
resp UTL_HTTP.RESP;
lc_response VARCHAR2(32767);
buffer VARCHAR2(32767);
l_shipmentTrackingNumber VARCHAR2(250);
l_soa_flow_id VARCHAR2(100);
l_ret_shiptrackno VARCHAR2(250);
l_img_format VARCHAR2(10);
l_type_code VARCHAR2(10);
l_lbl_base64 CLOB;
l_response_text CLOB;
l_output_clob CLOB;
l_response_clob CLOB;
l_pos_start NUMBER;
l_delivery_no_all VARCHAR2(2500);
l_ins_soaFlowID NUMBER;
l_ins_label64 CLOB;
l_carrier_name VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('Start of Function');
BEGIN
SELECT name
INTO l_instance
FROM v$pdbs;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while fetching instance name. '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Exception while fetching instance name. '||SQLERRM);
RETURN 'Error during Instance fetch.';
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_instance '||l_instance);
DBMS_OUTPUT.PUT_LINE('l_instance '||l_instance);
BEGIN
SELECT description url
,SUBSTR(meaning,1,INSTR(meaning,'_')-1) username
,tag password
INTO l_url
,l_username
,l_password
FROM fnd_lookup_values
WHERE lookup_type='XXAT_3PL_SOA_LKP_POST'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL (end_date_active, SYSDATE)
AND LANGUAGE = USERENV ('LANG')
AND source_lang = USERENV ('LANG')
AND enabled_flag = 'Y'
AND lookup_code = p_3pl_carrier;
EXCEPTION
WHEN OTHERS THEN
l_url := NULL;
l_username := NULL;
l_password := NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while fetching url from lookup XXAT_3PL_SOA_LKP_POST description. '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Exception while fetching url from lookup XXAT_3PL_SOA_LKP_POST description. '||SQLERRM);
RETURN 'Error during API details fetch from Lookup.';
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_url '||l_url);
DBMS_OUTPUT.PUT_LINE('l_url '||l_url);
BEGIN
SELECT 'file:'||SYS_CONTEXT('USERENV','ORACLE_HOME')||'/appsutil/wallet'
INTO l_wallet_url
FROM dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_wallet_url '||l_wallet_url);
DBMS_OUTPUT.PUT_LINE('l_wallet_url '||l_wallet_url);
EXCEPTION
WHEN OTHERS THEN
l_wallet_url := NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while fetching Oracle wallet. '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Exception while fetching Oracle wallet. '||SQLERRM);
RETURN 'Exception while fetching Oracle wallet. '||SUBSTR(SQLERRM,1,100);
END;
DBMS_OUTPUT.PUT_LINE('Before API being called.');
--API being called
BEGIN
SELECT LISTAGG(TRIM(delivery_no), ',') WITHIN GROUP(ORDER BY delivery_no)
INTO l_delivery_no_all
FROM xxat_3pl_waybill_service
WHERE waybill_number_dummy = TRIM(p_waybill_dummy_no);
SELECT DISTINCT carrier_name
INTO l_carrier_name
FROM xxat_3pl_waybill_service
WHERE waybill_number_dummy = TRIM(p_waybill_dummy_no);
l_content := prepare_json_3pl_service(p_waybill_dummy_no
,l_delivery_no_all);
DBMS_OUTPUT.PUT_LINE('After Content is set.');
FND_FILE.PUT_LINE(FND_FILE.LOG,'After Content is set.');
DBMS_OUTPUT.PUT_LINE('1');
UTL_HTTP.SET_WALLET(l_wallet_url,NULL);
DBMS_OUTPUT.PUT_LINE('2');
req := UTL_HTTP.BEGIN_REQUEST(l_url,'POST');
DBMS_OUTPUT.PUT_LINE('3');
UTL_HTTP.SET_AUTHENTICATION(req, l_username, l_password);
DBMS_OUTPUT.PUT_LINE('4');
UTL_HTTP.SET_HEADER(req,'user-agent','Apache-HttpClient/4.1.1 (java 1.5)');
DBMS_OUTPUT.PUT_LINE('5');
UTL_HTTP.SET_HEADER(req, 'accept-encoding', 'gzip, deflate');
DBMS_OUTPUT.PUT_LINE('6');
UTL_HTTP.SET_HEADER(req, 'content-type', 'application/json');
DBMS_OUTPUT.PUT_LINE('7');
UTL_HTTP.SET_HEADER(req, 'accept', 'application/json');
DBMS_OUTPUT.PUT_LINE('8');
UTL_HTTP.SET_HEADER(req, 'Connection', 'Keep-Alive');
DBMS_OUTPUT.PUT_LINE('9');
UTL_HTTP.SET_HEADER(req,'Content-Length',LENGTH(l_content) );
DBMS_OUTPUT.PUT_LINE('10, Length of content:'||LENGTH(l_content));
--UTL_HTTP.WRITE_TEXT(req,l_content);
--UTL_HTTP.WRITE_TEXT can use only VARCHAR2 and NOT CLOB data.
DECLARE
l_chunkData VARCHAR2(500);
l_chunkStart NUMBER := 1;
BEGIN
LOOP
l_chunkData := null;
l_chunkData := substr(l_content, l_chunkStart, 255);
UTL_HTTP.WRITE_TEXT(req, l_chunkData);
if (length(l_chunkData) < 255) then exit; end if;
l_chunkStart := l_chunkStart + 255;
END LOOP;
END;
DBMS_OUTPUT.PUT_LINE('11');
resp := UTL_HTTP.GET_RESPONSE(req);
dbms_output.put_line('After Response.');
DBMS_LOB.createtemporary (l_response_clob, FALSE);
BEGIN
LOOP
UTL_HTTP.READ_TEXT(resp,buffer,32767);
DBMS_LOB.writeappend (l_response_clob, LENGTH (buffer), buffer);
BEGIN
SELECT JSON_VALUE(buffer,'$.shipmentTrackingNumber')
, JSON_VALUE(buffer,'$.SOAFlowId')
, JSON_VALUE(buffer,'$.documents.imageFormat')
, LENGTH(JSON_VALUE(buffer,'$.documents.content'))
, JSON_VALUE(buffer,'$.documents.typeCode')
INTO l_shipmentTrackingNumber
, l_soa_flow_id
, l_img_format
, l_lbl_base64
, l_type_code
FROM dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ShipmentTrackingNumber:'||l_shipmentTrackingNumber
||' ,SOAflowId:'||l_soa_flow_id||' ,Image:'||l_img_format
||' ,Length of base64:'||l_lbl_base64
||' ,typeCode:'||l_type_code);
DBMS_OUTPUT.PUT_LINE('ShipmentTrackingNumber:'||l_shipmentTrackingNumber
||' ,SOAflowId:'||l_soa_flow_id||' ,Image:'||l_img_format
||' ,Length of base64:'||l_lbl_base64
||' ,typeCode:'||l_type_code);
IF (l_shipmentTrackingNumber IS NOT NULL) THEN
l_ret_shiptrackno := l_shipmentTrackingNumber;
fnd_file.put_line(fnd_file.log,'Shipment Track number:'||l_ret_shiptrackno);
DBMS_OUTPUT.PUT_LINE('Shipment Track number:'||l_ret_shiptrackno);
ELSIF (l_soa_flow_id IS NOT NULL) THEN
IF (l_ret_shiptrackno IS NOT NULL) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ShipmentTrackingNumber:'||l_ret_shiptrackno
||' ,SOAflowId:'||l_soa_flow_id);
DBMS_OUTPUT.PUT_LINE('ShipmentTrackingNumber:'||l_ret_shiptrackno
||' ,SOAflowId:'||l_soa_flow_id);
EXIT;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while getting the flow ID'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Error while getting the flow ID'||SQLERRM);
END;
END LOOP;
END;
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY
THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception inside UTL_HTTP.END_OF_BODY : '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Exception inside UTL_HTTP.END_OF_BODY : '||SQLERRM);
l_pos_start := INSTR(l_response_clob,'"content" : "');
fnd_file.put_line(fnd_file.log,'l_pos_start FOR "content :" '||l_pos_start);
dbms_output.put_line('l_pos_start FOR "content :" '||l_pos_start);
IF (l_pos_start = 0) THEN
fnd_file.put_line(fnd_file.log,'Failure msg :" '||l_response_clob);
dbms_output.put_line('Failure msg :" '||l_response_clob);
END IF;
l_ins_label64 :=
SUBSTR(l_response_clob
,INSTR(l_response_clob,'"',l_pos_start,3) + 1
,INSTR(l_response_clob,'"',l_pos_start,4) - INSTR(l_response_clob,'"',l_pos_start,3) - 1 );
l_pos_start := 0;
l_pos_start := INSTR(l_response_clob,'"SOAFlowId" : "');
dbms_output.put_line('SOAFlowId:'||SUBSTR(l_response_clob
,INSTR(l_response_clob,'"',l_pos_start,3) + 1
,INSTR(l_response_clob,'"',l_pos_start,4) - INSTR(l_response_clob,'"',l_pos_start,3) - 1));
l_ins_soaFlowID := TO_NUMBER(SUBSTR(l_response_clob
,INSTR(l_response_clob,'"',l_pos_start,3) + 1
,INSTR(l_response_clob,'"',l_pos_start,4) - INSTR(l_response_clob,'"',l_pos_start,3) - 1));
fnd_file.put_line(fnd_file.log,'Before updating label_base64 column for waybill '||p_waybill_dummy_no);
dbms_output.put_line('Before updating label_base64 column for waybill '||p_waybill_dummy_no);
BEGIN
UPDATE xxat_3pl_waybill_service xws
SET label_base64 = l_ins_label64
--,label_base64_box_flag = 'Y'
,soa_flow_id = l_ins_soaFlowID
,xws.response_json = l_response_clob
,waybill_number_dummy = p_waybill_dummy_no
WHERE parent_service_id IN (SELECT DISTINCT xws.waybill_service_id
FROM xxat_3pl_waybill_service xws
WHERE waybill_number_dummy = p_waybill_dummy_no
AND parent_service_id IS NULL);
fnd_file.put_line(fnd_file.log,'Before updating HAWB number');
dbms_output.put_line('Before updating HAWB number');
UPDATE xxat_3pl_waybill_service
SET hawb = l_ret_shiptrackno
,soa_flow_id = l_ins_soaFlowID
WHERE waybill_number_dummy = p_waybill_dummy_no;
COMMIT;
UPDATE xxat_3pl_waybill_service xws
SET hawb = l_ret_shiptrackno
,label_file_name = l_ret_shiptrackno||'_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'_'||TO_CHAR(SYSDATE,'HH24MISS')||'_label.pdf'
,label_base64_box_flag = 'Y'
,carrier_name = l_carrier_name
WHERE parent_service_id IN (SELECT DISTINCT xws.waybill_service_id
FROM xxat_3pl_waybill_service xws
WHERE waybill_number_dummy = p_waybill_dummy_no
AND parent_service_id IS NULL
AND rownum = 1);
COMMIT;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error during label_base64 ,flag and soaFlowID update:'||SQLERRM);
dbms_output.put_line('Error during label_base64 ,flag and soaFlowID update:'||SQLERRM);
UTL_HTTP.END_RESPONSE(resp);
RETURN 'ERROR_LABEL64_RESPONSE';
END;
UTL_HTTP.END_RESPONSE(resp);
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before closing the UTL_HTTP response');
DBMS_OUTPUT.PUT_LINE('Before closing the UTL_HTTP response');
UTL_HTTP.END_RESPONSE(resp);
COMMIT;
RETURN NVL(l_ret_shiptrackno ,'No_VAL_RET');
EXCEPTION
WHEN others THEN
NULL;
RETURN 'ERROR:'||SUBSTR(SQLERRM,1,100);
END xx_webapi_dhl_fnc;
FUNCTION prepare_json_3pl_service (p_waybill_dummy_no IN VARCHAR2
, p_delivery_no IN VARCHAR2) RETURN CLOB
AS
CURSOR c_packages
IS
SELECT Wnd.Initial_Pickup_Location_Id, wdd.lpn_id
, TO_CHAR(wdd.gross_weight,'fm9999990.00') gross_weight
, TO_CHAR(wdd.net_weight,'fm9999990.00') net_weight
,wdd.attribute3 length, wdd.attribute4 width, wdd.attribute5 height
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, wms_license_plate_numbers wlpn
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wlpn.lpn_id = wdd.lpn_id
AND wlpn.lpn_id = wlpn.outermost_lpn_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'WSH'
AND wnd.delivery_id IN (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_delivery_no, ',') + 1);
/*
--ITEM details
CURSOR c_line_items
IS
SELECT rownum, item_description ,inventory_item_id , source_line_id
, CASE WHEN (INSTR(TO_CHAR(unit_selling_price),'.') = 1) THEN '0'||TO_CHAR(unit_selling_price)
ELSE TO_CHAR(unit_selling_price)
END unit_selling_price
, currency_Code ,weight_uom_code, attribute1, delivery_id, tot_qty
FROM (SELECT wdd.item_description ,wdd.inventory_item_id ,wdd.source_line_id, wdd.unit_price, ROUND(oola.unit_selling_price,2) unit_selling_price
, wdd.currency_Code ,wdd.weight_uom_code, wdd.attribute1, wnd.delivery_id
,SUM(wdd.shipped_quantity) tot_qty
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, oe_order_lines_all oola
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'OE'
AND wnd.delivery_id IN (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_delivery_no, ',') + 1)
GROUP BY wdd.item_description ,wdd.inventory_item_id ,wdd.source_line_id, wdd.unit_price, oola.unit_selling_price, wdd.currency_Code ,wdd.weight_uom_code, wdd.attribute1, wnd.delivery_id);
*/
CURSOR c_line_items
IS
SELECT rownum, a.* FROM (
SELECT item_description ,inventory_item_id ,source_line_id, unit_selling_price
, currency_Code ,weight_uom_code,attribute1, delivery_id, hts, order_type, link_to_line_id
, SUM(shipped_quantity) tot_qty
FROM (SELECT wdd.item_description ,wdd.inventory_item_id ,wdd.source_line_id, wdd.unit_price, oola.unit_selling_price
, wdd.currency_Code ,wdd.weight_uom_code, wdd.attribute1, wnd.delivery_id
,(SELECT NVL (a.c_ext_attr2, '')
FROM ego_mtl_sy_items_ext_b a
WHERE a.attr_group_id = 92
AND ROWNUM = 1
AND a.c_ext_attr1 = 'MY'
AND a.inventory_item_id = oola.inventory_item_id
) hts
, ott.NAME order_type
, oola.link_to_line_id
, wdd.shipped_quantity
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, oe_order_lines_all oola
, oe_transaction_types_tl ott
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND oola.line_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND UPPER (ott.NAME) NOT LIKE '%INTERIM%'
AND wdd.organization_id = 5180
AND wdd.source_code = 'OE'
AND wnd.delivery_id IN (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_delivery_no, ',') + 1))
GROUP BY item_description ,inventory_item_id ,source_line_id, unit_selling_price
, currency_Code ,weight_uom_code,attribute1, delivery_id, hts, order_type, link_to_line_id) a;
--comm_inv_base64
CURSOR c_comm_inv_base64
IS
SELECT b.comm_inv_base64
FROM xxat_3pl_waybill_service a
, xxat_3pl_waybill_service b
WHERE a.waybill_service_id = b.parent_service_id
AND a.waybill_number_dummy = p_waybill_dummy_no;
l_content CLOB;
l_packages CLOB;
l_sub_content CLOB;
l_line_items_content CLOB;
l_shipper_receiver_dtls CLOB;
l_base_64 CLOB;
l_planned_shipDateTime VARCHAR2(100);
l_shipper_accno VARCHAR2(100);
l_net_box_val VARCHAR2(100);
l_gross_box_val VARCHAR2(100);
l_address1 VARCHAR2(100);
l_address2 VARCHAR2(100);
l_address3 VARCHAR2(100);
l_city VARCHAR2(100);
l_province VARCHAR2(100);
l_state VARCHAR2(100);
l_country_code VARCHAR2(100);
l_postal_code VARCHAR2(100);
l_company_name VARCHAR2(100);
l_shipper_phn_no VARCHAR2(100);
l_full_name VARCHAR2(100);
l_location_id NUMBER;
l_ship_to_site_use_id NUMBER;
l_rcv_postal_code VARCHAR2(100);
l_rcv_city VARCHAR2(100);
l_rcv_country VARCHAR2(100);
l_rcv_address1 VARCHAR2(100);
l_rcv_address2 VARCHAR2(100);
l_rcv_address3 VARCHAR2(100);
l_rcv_comp_name VARCHAR2(100);
l_rcv_party_id NUMBER;
l_rcv_phn_no VARCHAR2(100);
l_rcv_name VARCHAR2(100);
l_declval_curr VARCHAR2(2000);
l_curr_code VARCHAR2(10);
l_declared_val NUMBER;
l_reference_type VARCHAR2(3);
l_waybill_service_id NUMBER;
l_waybill_dummy_no VARCHAR2(50);
l_delivery_no NUMBER;
l_submit VARCHAR2(1);
l_desc_incoterm_uom VARCHAR2(1000);
l_incoterm VARCHAR2(10);
l_dtp VARCHAR2(5);
l_bill_shpmnt_to VARCHAR2(50);
l_duties_taxes_to VARCHAR2(50);
l_rcv_acc_no VARCHAR2(50);
l_third_party_no VARCHAR2(50);
l_avlbl_prd_to_dest VARCHAR2(1);
l_customs_decl VARCHAR2(10);
l_wy_count NUMBER;
l_country_count NUMBER;
l_us_hts VARCHAR2(100);
l_ship_to_overide VARCHAR2(250);
l_header_id NUMBER;
l_order_name OE_TRANSACTION_TYPES_TL.NAME%TYPE;
l_item_desc VARCHAR2(1000);
l_unit_selling_price NUMBER;
l_tot NUMBER;
l_sub_tot NUMBER := 0;
l_sell_price VARCHAR2(100);
l_unit_price VARCHAR2(100);
l_ship_to_org NUMBER;
l_location VARCHAR2(100);
lv_selling_price NUMBER;
BEGIN
dbms_output.put_line('Start of prepare_json_3pl_service function, p_delivery_no:'||p_delivery_no);
--based on DN create payload (make sure comm inv base64 should be ready at this point for that DN.)
BEGIN
SELECT DISTINCT wnd.initial_pickup_location_id, wdd.ship_to_site_use_id
,xws.reference_type, xws.waybill_service_id, xws.waybill_number_dummy, xws.delivery_no
,'N' submit_flag, duties_and_taxes_paid, receipient_phone_number, receipient_contact_person
,xws.available_product_destination, wdd.source_header_id
INTO l_location_id , l_ship_to_site_use_id
,l_reference_type, l_waybill_service_id, l_waybill_dummy_no, l_delivery_no
,l_submit ,l_dtp, l_rcv_phn_no, l_rcv_name
,l_avlbl_prd_to_dest ,l_header_id
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, xxat_3pl_waybill_service xws
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND xws.delivery_no = wnd.delivery_id
AND xws.organization_id = wdd.organization_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'OE'
AND xws.parent_service_id IS NULL
AND wnd.delivery_id = (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL = 1) --Any one DN number is sufficient.
AND xws.waybill_number_dummy = p_waybill_dummy_no;
dbms_output.put_line('l_location_id:'||l_location_id||' ,l_ship_to_site_use_id:'||l_ship_to_site_use_id||' ,l_reference_type:'||l_reference_type);
dbms_output.put_line('l_waybill_service_id:'||l_waybill_service_id||' ,l_waybill_dummy_no:'||l_waybill_dummy_no||' ,l_delivery_no:'||l_delivery_no||' ,l_submit:'||l_submit);
fnd_file.put_line(fnd_file.log,'l_location_id:'||l_location_id||' ,l_ship_to_site_use_id:'||l_ship_to_site_use_id||' ,l_reference_type:'||l_reference_type);
fnd_file.put_line(fnd_file.log,'l_waybill_service_id:'||l_waybill_service_id||' ,l_waybill_dummy_no:'||l_waybill_dummy_no||' ,l_delivery_no:'||l_delivery_no||' ,l_submit:'||l_submit);
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while fetching l_location_id , l_ship_to_site_use_id: '||SQLERRM);
dbms_output.put_line('Error while fetching l_location_id , l_ship_to_site_use_id: '||SQLERRM);
END;
IF (l_submit = 'N') THEN
--Receiver details
BEGIN
SELECT NVL(SUBSTR(hl.postal_code, 1, 12), '.') postal_code
, NVL(SUBSTR(hl.city, 1,35), '.')
, NVL(SUBSTR(hl.country, 1, 2), '.')
, NVL(SUBSTR(hl.address1, 1, 45), '.')
, NVL(SUBSTR(hl.address2, 1, 45), '.')
, NVL(SUBSTR(hl.address3, 1, 45), '.')
, NVL(SUBSTR(hp.party_name, 1, 60), '.')
, hp.party_id
, hcsua.Attribute21 Ship_To_Overide
INTO l_rcv_postal_code
, l_rcv_city
, l_rcv_country
, l_rcv_address1
, l_rcv_address2
, l_rcv_address3
, l_rcv_comp_name
, l_rcv_party_id
, l_ship_to_overide
FROM hz_cust_acct_sites_all hcsa,
hz_party_sites hps,
hz_cust_site_uses_all hcsua,
hz_cust_accounts hca,
hz_parties hp,
hz_locations hl
WHERE hps.party_site_id = hcsa.party_site_id
AND hcsa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hp.party_id = hca.party_id
AND hl.location_id = hps.location_id
AND hca.status = 'A'
AND hcsua.status = 'A'
AND hcsua.site_use_id = l_ship_to_site_use_id;
BEGIN
SELECT t.name
INTO l_order_name
FROM oe_transaction_types_tl t,
oe_order_headers_all h
WHERE h.order_type_id = t.TRANSACTION_TYPE_ID
AND UPPER(t.name) LIKE '%INTERNAL%'
AND t.language = USERENV('LANG')
AND h.header_id = l_header_id;
IF (UPPER(l_order_name) LIKE '%INTERNAL%' AND l_ship_to_overide IS NOT NULL) THEN
SELECT NVL(SUBSTR(hl.postal_code, 1, 12), '.') postal_code
, NVL(SUBSTR(hl.city, 1,35), '.')
, NVL(SUBSTR(hl.country, 1, 2), '.')
, NVL(SUBSTR(hl.address1, 1, 45), '.')
, NVL(SUBSTR(hl.address2, 1, 45), '.')
, NVL(SUBSTR(hl.address3, 1, 45), '.')
, NVL(SUBSTR(hp.party_name||' ('||hca.account_number||'), '||haou.name, 1, 60), '.')
, hp.party_id
INTO l_rcv_postal_code
, l_rcv_city
, l_rcv_country
, l_rcv_address1
, l_rcv_address2
, l_rcv_address3
, l_rcv_comp_name
, l_rcv_party_id
FROM hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units haou
WHERE haou.organization_id = hcasa.org_id
AND hps.party_site_id = hcasa.party_site_id
AND hl.location_id = hps.location_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hp.party_id = hps.party_id
AND hcasa.cust_acct_site_id = TO_NUMBER(TRIM(l_ship_to_overide))
AND ROWNUM=1;
END IF;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'NOT an INTERNAL order.');
dbms_output.put_line('NOT an INTERNAL order.');
END;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while fetching RECEIVER details:'||SQLERRM);
dbms_output.put_line('Error while fetching RECEIVER details:'||SQLERRM);
END;
BEGIN
--planned shipping dateAndTime
BEGIN
SELECT TO_CHAR(SYSDATE+1,'YYYY-MM-DD')
||'T'
||TO_CHAR(SYSDATE,'HH24:MI:SS')||'GMT'
||DBTIMEZONE --, SESSIONTIMEZONE --2022-03-03T14:00:31GMT+00:00
INTO l_planned_shipDateTime
FROM dual;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error during plannedShippingDateAndTime details fetch.');
dbms_output.put_line('Error during plannedShippingDateAndTime details fetch.');
END;
--Shipper account details
BEGIN
SELECT description
INTO l_shipper_accno
FROM fnd_lookup_values
WHERE lookup_type = 'XXAT_3PL_SHIPPER_ACCOUNT'
AND tag = 'DHL'
AND language = 'US'
AND enabled_flag = 'Y'
AND lookup_code = 'SHIPPER_ACCOUNT1';
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error during plannedShippingDateAndTime details fetch.');
dbms_output.put_line('Error during plannedShippingDateAndTime details fetch.');
END;
l_content :=
'{
"plannedShippingDateAndTime": "'
|| l_planned_shipDateTime --2022-03-03T14:00:31GMT+00:00
||'",
"pickup": {
"isRequested": false
},
"productCode": "'|| l_avlbl_prd_to_dest ||'",
"localProductCode": "'|| l_avlbl_prd_to_dest ||'"' ||',';
BEGIN
SELECT DISTINCT a.bill_shipment_to, a.duties_and_taxes_to, a.receipient_account_number, a.third_party_account_number
INTO l_bill_shpmnt_to, l_duties_taxes_to, l_rcv_acc_no, l_third_party_no
FROM xxat_3pl_waybill_service a
WHERE waybill_number_dummy = p_waybill_dummy_no
AND parent_service_id IS NULL;
--By default duties and taxes will be directed to 'RECEIVER'
IF ((l_bill_shpmnt_to = 'SHIPPER' AND l_duties_taxes_to = 'RECEIVER')
OR
(l_bill_shpmnt_to = 'SHIPPER' AND l_duties_taxes_to IS NULL)) THEN
l_content := l_content || '
"accounts": [
{
"typeCode": "shipper",
"number": "'||l_shipper_accno||'"
}
]'
||',';
--shipper and receiver account number should be max 9 digits.
ELSIF ((l_bill_shpmnt_to = 'RECEIVER' AND l_duties_taxes_to = 'RECEIVER')
OR
(l_bill_shpmnt_to = 'RECEIVER' AND l_duties_taxes_to IS NULL))THEN
l_content := l_content || '
"accounts": [
{
"typeCode": "shipper",
"number": "'||l_shipper_accno||'"
},
{
"typeCode": "payer",
"number": "'||l_rcv_acc_no||'"
}
]'
||',';
ELSIF ((l_bill_shpmnt_to = 'THIRD PARTY' AND l_duties_taxes_to = 'RECEIVER')
OR
(l_bill_shpmnt_to = 'THIRD PARTY' AND l_duties_taxes_to IS NULL)) THEN --
l_content := l_content || '
"accounts": [
{
"typeCode": "shipper",
"number": "'||l_shipper_accno||'"
},
{
"typeCode": "payer",
"number": "'||l_third_party_no||'"
}
]'
||',';
ELSIF (l_bill_shpmnt_to = 'RECEIVER' AND l_duties_taxes_to = 'THIRD PARTY') THEN --
l_content := l_content || '
"accounts": [
{
"typeCode": "shipper",
"number": "'||l_shipper_accno||'"
},
{
"typeCode": "payer",
"number": "'||l_rcv_acc_no||'"
},
{
"typeCode": "duties-taxes",
"number": "'||l_third_party_no||'"
}
]'
||',';
ELSIF (l_bill_shpmnt_to = 'SHIPPER' AND l_duties_taxes_to = 'THIRD PARTY') THEN
l_content := l_content || '
"accounts": [
{
"typeCode": "shipper",
"number": "'||l_shipper_accno||'"
},
{
"typeCode": "duties-taxes",
"number": "'||l_third_party_no||'"
}
]'
||',';
ELSIF (l_bill_shpmnt_to = 'SHIPPER' AND l_duties_taxes_to = 'SHIPPER') THEN
l_content := l_content || '
"accounts": [
{
"typeCode": "shipper",
"number": "'||l_shipper_accno||'"
},
{
"typeCode": "duties-taxes",
"number": "'||l_shipper_accno||'"
}
]'
||',';
END IF;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error during Bill To and Duties Taxes To details fetch.');
dbms_output.put_line('Error during Bill To and Duties Taxes To details fetch.');
END;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error during SHIPPER details fetch.');
dbms_output.put_line('Error during SHIPPER details fetch.');
END;
BEGIN
SELECT COUNT(*)
INTO l_wy_count
FROM fnd_lookup_values_vl
WHERE lookup_type = 'XXAT_3PL_NON-WY_COUNTRIES'
AND enabled_flag = 'Y'
AND lookup_code = l_rcv_country; --'CN'
--commented since we are not going to send the invoice as softcopy anymore that means no PLT service.
--uncommented for ver 1.2 Start
IF l_wy_count =0 THEN -- send "WY"
l_sub_content := '
"valueAddedServices": [
{
"serviceCode": "WY"
}';
IF (l_dtp = 'Y') THEN
l_sub_content := l_sub_content
||',
{
"serviceCode": "DD"
} ';
END IF;
l_sub_content := l_sub_content
||'
],';
ELSIF (l_wy_count = 1 AND l_dtp = 'Y') THEN
l_sub_content := '
"valueAddedServices": [
{
"serviceCode": "DD"
}
], ';
END IF;
--ver 1.2 End
IF (l_dtp = 'Y') THEN
l_sub_content := '
"valueAddedServices": [
{
"serviceCode": "DD"
}
], ';
END IF;
EXCEPTION
WHEN others THEN
NULL;
END;
l_sub_content := l_sub_content ||'
"outputImageProperties": {
"printerDPI": 200,
"encodingFormat": "pdf",
"imageOptions": [
{
"typeCode": "label",
"templateName": "ECOM26_84_001"
},
{
"typeCode": "waybillDoc",
"templateName": "ARCH_8X4",
"isRequested": true,
"hideAccountNumber": false,
"numberOfCopies": 2
}
]
}'
||',';
l_content := l_content
|| l_sub_content
||'
"customerReferences": [
{
"value": "'|| l_delivery_no ||'",
"typeCode": "'|| l_reference_type ||'"
}
],';
l_sub_content := '';
--commented since base64 is not being considered as part of API.
--uncommented for Ver 1.2 start
IF l_wy_count =0 THEN -- Only for WY based countries
l_content := l_content
||'
"documentImages": [ ';
FOR k IN c_comm_inv_base64 LOOP
l_base_64 := '
{
"content": "'|| k.comm_inv_base64 ||'",
"typeCode": "INV",
"imageFormat": "PDF"
},';
l_sub_content := l_sub_content || l_base_64;
END LOOP;
l_content := l_content
|| RTRIM(l_sub_content, ',')
||'
],';
END IF;
--ver 1.2 End
--Shipper Details
BEGIN
SELECT NVL(SUBSTR(hr_d.address_line_1, 1, 45),'.') address1
,NVL(SUBSTR(hr_d.address_line_2, 1, 45),'.') address2
,NVL(SUBSTR(hr_d.address_line_3, 1, 45),'.') address3
,NVL(SUBSTR(hr_d.town_or_city, 1, 35),'.') city
,nvl(fcl_pr.meaning,fcl_pr1.meaning) province
,nvl(fcl_st.meaning,fcl_st1.meaning) state
,NVL(hr_d.country,'.') country
,NVL(SUBSTR(hr_d.postal_code, 1, 12),'.') postal_code
,NVL(SUBSTR(hr_d.attribute7, 1, 60),'.') company_name
INTO l_address1
,l_address2
,l_address3
,l_city
,l_province
,l_state
,l_country_code
,l_postal_code
,l_company_name
FROM hr_locations hr_d
,fnd_common_lookups fcl_pr
,fnd_common_lookups fcl_st
,fnd_common_lookups fcl_pr1
,fnd_common_lookups fcl_st1
WHERE location_id = l_location_id --l_locid -- :initial_pickup_location_id --take the value from any one DN since ship_to_location has to be unique for all DN's under same waybill
AND fcl_pr.lookup_type(+) = hr_d.country||'_PROVINCE'
AND fcl_pr.lookup_code(+) = hr_d.region_1
AND fcl_st.lookup_type(+) = hr_d.country||'_STATE'
AND fcl_st.lookup_code(+) = hr_d.region_1
AND fcl_pr1.lookup_type(+) = hr_d.country||'_PROVINCE'
AND fcl_pr1.lookup_code(+) = hr_d.region_2
AND fcl_st1.lookup_type(+) = hr_d.country||'_STATE'
AND fcl_st1.lookup_code(+) = hr_d.region_2;
SELECT SUBSTR(description, 1, 25)
INTO l_shipper_phn_no
FROM fnd_lookup_values
WHERE lookup_type = 'XXAT_3PL_SHIPPER_ACCOUNT'
AND tag = 'DHL'
AND language = 'US'
AND enabled_flag = 'Y'
AND lookup_code = 'SHIPPER_PHONE_NUMBER1'; --SHIPPER_ACCOUNT1
SELECT SUBSTR(UPPER(papf.full_name), 1, 45)
INTO l_full_name
FROM fnd_user u
,per_all_people_f papf
WHERE u.employee_id = papf.person_id
AND SYSDATE BETWEEN effective_start_date AND NVL(effective_end_date, SYSDATE)
AND u.user_id = fnd_global.user_id; --125273 fnd_global.user_id
fnd_file.put_line(fnd_file.log,'Shipper Name:'||l_full_name);
dbms_output.put_line('Shipper Name:'||l_full_name);
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while fetching SHIPPER details:'||SQLERRM);
dbms_output.put_line('Error while fetching SHIPPER details:'||SQLERRM);
END;
dbms_output.put_line('Before Shipper and Receiver details.');
l_shipper_receiver_dtls := '
"customerDetails": {
"shipperDetails": {
"postalAddress": {
"postalCode": "' || l_postal_code ||'",
"cityName": "' || l_city ||'",
"countryCode": "' || l_country_code ||'",
"addressLine1": "'|| l_address1 ||'",
"addressLine2": "'|| l_address2 ||'",
"addressLine3": "'|| l_address3 ||'"
},
"contactInformation": {
"phone": "'|| l_shipper_phn_no ||'",
"companyName": "'|| l_company_name ||'",
"fullName": "'|| l_full_name ||'"
}
},
"receiverDetails": {
"postalAddress": {
"postalCode": "' || l_rcv_postal_code ||'",
"cityName": "' || l_rcv_city ||'",
"countryCode": "' || l_rcv_country ||'",
"addressLine1": "'|| l_rcv_address1 ||'",
"addressLine2": "'|| l_rcv_address2 ||'",
"addressLine3": "'|| l_rcv_address3 ||'"
},
"contactInformation": {
"phone": "'|| l_rcv_phn_no ||'",
"companyName": "'|| l_rcv_comp_name ||'",
"fullName": "'|| l_rcv_name ||'"
}
}
}, ';
l_content := l_content || l_shipper_receiver_dtls;
dbms_output.put_line('Before l_packages');
--OUTERMOST_LPN information
l_packages := '
"content": {
"packages": [';
FOR i IN c_packages LOOP
l_sub_content :=
'
{
"weight": '||i.gross_weight||',
"dimensions": {
"length": '||i.length||',
"width": '||i.width||',
"height": '||i.height||'
}
},';
l_packages := l_packages || l_sub_content;
END LOOP;
l_packages := RTRIM(l_packages, ',')
|| '
]'
||',';
fnd_file.put_line(fnd_file.log,'After l_packages ');
dbms_output.put_line('After l_packages');
l_content := l_content || l_packages;
BEGIN
dbms_output.put_line('Before CURR, Declared val');
SELECT currency_code, incoterm --, SUM(inv_price) declared_val
INTO l_curr_code, l_incoterm --, l_declared_val
FROM (SELECT DISTINCT oola.header_id, oola.line_id, wdd.currency_code , SUBSTR(wnd.fob_code,1,3) incoterm
,oola.ordered_quantity, oola.unit_selling_price , oola.ordered_quantity * ROUND(oola.unit_selling_price,2) inv_price
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, oe_order_lines_all oola
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'OE'
AND wnd.delivery_id IN (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_delivery_no, ',') + 1)) --(96072644))
GROUP BY currency_Code, incoterm;
l_declared_val := xx_total_declared_val(p_delivery_no
--added for ver 1.1 Start
, 'DHL'
, 'N'
, lv_selling_price
, NULL
--ver 1.1 End
);
dbms_output.put_line('AFTER CURR, Declared val');
IF (l_avlbl_prd_to_dest = 'P') THEN
l_customs_decl := 'true';
ELSE
l_customs_decl := 'false';
END IF;
l_declval_curr := '
"isCustomsDeclarable": '|| l_customs_decl ||',
"declaredValue": '|| l_declared_val ||',
"declaredValueCurrency": "'|| l_curr_code ||'",
"exportDeclaration": {
';
l_content := l_content || l_declval_curr;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while fetching "declaredValue and currency."'||SQLERRM);
dbms_output.put_line('Error while fetching "declaredValue and currency."'||SQLERRM);
END;
l_sub_content := '';
l_line_items_content := '
"lineItems": [';
FOR j IN c_line_items LOOP --based on each DN
SELECT TO_CHAR(gross_box_value,'fm999999999999990.00')
INTO l_gross_box_val
FROM (SELECT SUM(wdd.net_weight) net_box_value, SUM(wdd.gross_weight) gross_box_value
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, wms_license_plate_numbers wlpn
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wlpn.lpn_id = wdd.lpn_id
AND wlpn.lpn_id = wlpn.outermost_lpn_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'WSH'
AND wnd.delivery_id = j.delivery_id);
SELECT TO_CHAR(net_box_value,'fm999999999999990.00')
INTO l_net_box_val
FROM (SELECT SUM(wdd.shipped_quantity * msi.unit_weight) net_box_value
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, mtl_system_items_b msi
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND msi.organization_id = wdd.organization_id
AND wdd.organization_id = 5180
AND wnd.delivery_id = j.delivery_id
AND wdd.attribute1 = j.attribute1
);
BEGIN
--commented and added since HTS code should match with summary invoice report.
--commented logic is based on commercial invoice report.
--uncommented and added for ver 1.2 start
SELECT COUNT (1)
INTO l_country_count
FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXAT_WMS_INCOUNTRY_INVOICE'
AND LANGUAGE = 'US'
AND SUBSTR (meaning, 5, 2) = l_rcv_country;
IF l_country_count > 0 THEN
SELECT CASE WHEN (INSTR(attribute32,'-') > 0) THEN
SUBSTR(attribute32
,INSTR(attribute32,'-') + 1
)
ELSE
attribute32
END htscode
INTO l_us_hts
FROM xxat_extend_dff_attribs
WHERE object_pk1 = j.source_line_id
AND ATTRIBUTE_CONTEXT = 'ORDER_LINE'
AND rownum=1;
ELSE
SELECT CASE WHEN (INSTR(attribute33,'-') > 0) THEN
SUBSTR(attribute33
,INSTR(attribute33,'-') + 1
)
ELSE
attribute33
END htscode
INTO l_us_hts
FROM xxat_extend_dff_attribs
WHERE object_pk1 = j.source_line_id
AND ATTRIBUTE_CONTEXT = 'ORDER_LINE'
AND rownum=1;
END IF;
--ver 1.2 End
--commented for ver 1.2 start
/*
SELECT NVL (a.c_ext_attr2, '')
INTO l_us_hts
FROM ego_mtl_sy_items_ext_b a
WHERE a.attr_group_id = 92
AND ROWNUM = 1
AND a.c_ext_attr1 = 'MY'
AND a.inventory_item_id = j.inventory_item_id;
SELECT REPLACE(c_ext_attr1,CHR(10),'') c_ext_attr1
INTO l_item_desc
FROM apps.ego_mtl_sy_items_ext_b
WHERE organization_id = '105'
AND attr_group_id = '100'
AND inventory_item_id = j.inventory_item_id;
*/
-- ver 1.2 end
EXCEPTION
WHEN others THEN
l_us_hts := '.';
fnd_file.put_line(fnd_file.log,'Exception in getting HTS Info:'||sqlerrm);
END;
--Unit selling price
BEGIN
BEGIN
SELECT DECODE
(INSTR (UPPER (ott.NAME), 'INTERNAL'),
0, TO_NUMBER
(DECODE
(DECODE (oel.line_id,
NVL (oel.top_model_line_id,
oel.line_id
), 'XX',
NVL (oel.attribute15, 'XX')
),
'D', NULL,
'PD', NULL,
xxat_commercial_rpt.xxat_rollup_price
(oel.line_id,
oel.top_model_line_id,
oel.link_to_line_id,
'LINE_UNIT',
oel.attribute15
)
)
),
oel.tp_attribute13
) unit_selling_price
INTO l_unit_selling_price
FROM oe_order_lines_all oel,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
oe_transaction_types_tl ott
WHERE wda.delivery_id = wnd.delivery_id
AND oel.line_id = wdd.source_line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND oel.header_id = wdd.source_header_id
AND wdd.source_code = 'OE'
AND oel.line_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND UPPER (ott.NAME) NOT LIKE '%INTERIM%'
AND wnd.organization_id = 5180
AND wnd.delivery_id = j.delivery_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price txn type:'||SQLERRM);
END;
BEGIN
SELECT unit_selling_price, ship_to_org_id, hcsua.LOCATION
INTO l_sell_price, l_ship_to_org, l_location
FROM oe_order_lines_all oel, hz_cust_site_uses_all hcsua
WHERE 1 = 1
AND line_id = j.source_line_id
AND hcsua.site_use_id = ship_to_org_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_sell_price:'||SQLERRM);
END;
IF l_unit_selling_price IS NULL AND l_location NOT LIKE '%VMI%'
THEN
l_unit_selling_price := l_sell_price;
END IF;
IF j.order_type LIKE '%Trade%'
THEN
BEGIN
SELECT DECODE (item_type_code,
'KIT', NVL (attribute18,
l_unit_selling_price),
DECODE (top_model_line_id,
NULL, NVL (attribute18,
l_unit_selling_price
),
NULL
)
)
INTO l_unit_selling_price
FROM oe_order_lines_all oel
WHERE 1 = 1 AND line_id = j.source_line_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price using Trade value:'||SQLERRM);
END;
END IF;
IF NVL (l_unit_selling_price, 0) = 0
THEN
BEGIN
SELECT ROUND (( (x.unit_selling_price * bic.attribute6)
/ 100
/ bic.component_quantity
),
2
)
INTO l_unit_selling_price
FROM oe_order_lines_all x,
bom_bill_of_materials bom,
bom_inventory_components bic
WHERE x.line_id = j.link_to_line_id
AND bom.organization_id = 105
AND bic.disable_date IS NULL
AND x.inventory_item_id = bom.assembly_item_id
AND bom.bill_sequence_id = bic.bill_sequence_id
AND bic.component_item_id = j.inventory_item_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price using BOM:'||SQLERRM);
END;
END IF;
l_unit_price := TO_CHAR (l_unit_selling_price, 'fm99999999990.00000');
--l_tot := j.tot_qty * TO_NUMBER(TO_CHAR (l_unit_selling_price, 'fm99999999990.00'));
--l_unit_weight := j.unit_weight * j.ordered_quantity;
--l_sub_tot := l_sub_tot + l_tot;
fnd_file.put_line(fnd_file.log,'Unit price:'||c_line_items%ROWCOUNT||': '||l_unit_price);
END;
--only 3 decimals are allowed by API.
--NOTE: For ver 1.1 it is l_item_desc , for ver 1.2 it is j.item_description
l_sub_content := '
{
"number": '|| j.rownum ||',
"description": "'|| j.item_description ||'",
"price": '|| l_unit_price ||',
"commodityCodes": [
{
"typeCode": "inbound",
"value": "'|| l_us_hts ||'"
}
],
"priceCurrency": "'|| j.currency_code ||'",
"quantity": {
"value": '|| j.tot_qty ||',
"unitOfMeasurement": "'|| 'PCS' ||'"
},
"exportReasonType": "permanent",
"manufacturerCountry": "'|| j.attribute1 ||'",
"weight": {
"netValue": '|| l_net_box_val ||',
"grossValue": '|| l_gross_box_val ||'
}
},';
l_line_items_content := l_line_items_content || l_sub_content ;
END LOOP;
l_line_items_content := RTRIM(l_line_items_content, ',')
||'
]'
||','
||'
"invoice": {
"number": "'|| l_delivery_no ||'",
"date": "'|| TO_CHAR(SYSDATE,'YYYY-MM-DD') ||'"
}
},';
l_content := l_content || l_line_items_content;
l_desc_incoterm_uom := '
"description": "SEMICONDUCTORS PARTS",
"incoterm": "'|| l_incoterm ||'",
"unitOfMeasurement": "metric"
}
}';
l_content := l_content || l_desc_incoterm_uom;
dbms_output.put_line('End of prepare_json_3pl_service.');
BEGIN
UPDATE xxat_3pl_waybill_service xws
SET content_json = l_content
WHERE waybill_number_dummy = p_waybill_dummy_no
AND parent_service_id IS NULL;
COMMIT;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while updating "xxat_3pl_waybill_service" with commercial invoice base64 for waybillno:'||p_waybill_dummy_no||' ,withe error:'||SQLERRM);
dbms_output.put_line('Error while updating "xxat_3pl_waybill_service" with commercial invoice base64 for waybillno:'||p_waybill_dummy_no||' ,withe error:'||SQLERRM);
RETURN 'Error during "xxat_3pl_waybill_service" table updation.';
END;
dbms_output.put_line('Length:'||LENGTH(l_content));
RETURN l_content;
ELSE
fnd_file.put_line(fnd_file.log,'Delivery already submitted for 3PL service.');
RETURN 'Delivery already submitted for 3PL service.';
END IF;
EXCEPTION
WHEN others THEN
RETURN 'FAILURE';
END prepare_json_3pl_service;
PROCEDURE main (errbuff OUT VARCHAR2
,retcode OUT NUMBER
,p_waybill_dummy_no IN VARCHAR2
,p_carrier IN VARCHAR2)
AS
l_ret_val VARCHAR2(1000);
BEGIN
IF (UPPER(p_carrier) = 'DHL') THEN
fnd_file.put_line(fnd_file.log,'Before calling the function.');
dbms_output.put_line('Before calling the function.');
l_ret_val := xxat_3pl_webservice_pkg.xx_webapi_dhl_fnc(p_waybill_dummy_no, p_carrier);
fnd_file.put_line(fnd_file.log,'HAWB received:'||l_ret_val);
dbms_output.put_line('HAWB received:'||l_ret_val);
--FedEx Start
ELSIF (UPPER(p_carrier) = 'FEDEX') THEN
fnd_file.put_line(fnd_file.log,'Before calling the FedEx function.');
dbms_output.put_line('Before calling the FedEx function.');
l_ret_val := xxat_3pl_webservice_pkg.xx_webapi_FedEx_fnc(p_waybill_dummy_no, p_carrier);
fnd_file.put_line(fnd_file.log,'HAWB FedEx received:'||l_ret_val);
dbms_output.put_line('HAWB FedEx received:'||l_ret_val);
--FedEx End
END IF;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error in MAIN procedure:'||SQLERRM);
dbms_output.put_line('Error in MAIN procedure:'||SQLERRM);
END main; --666
FUNCTION xx_total_declared_val(p_delivery_no IN VARCHAR2 --can accept multiple DN with comma separated
--added for ver 1.1 for FedEx Start
,p_3pl_carrier IN VARCHAR2
,p_sell_price_flag IN VARCHAR2
,p_selling_price OUT NUMBER
,p_item_id IN NUMBER
--ver 1.1 End
) RETURN VARCHAR2
AS
CURSOR c_line_items
IS
SELECT rownum, a.*
FROM (
SELECT item_description ,inventory_item_id ,source_line_id, unit_selling_price
, currency_Code ,weight_uom_code,attribute1, delivery_id, hts, order_type, link_to_line_id
, SUM(shipped_quantity) tot_qty
FROM (SELECT wdd.item_description ,wdd.inventory_item_id ,wdd.source_line_id, wdd.unit_price, oola.unit_selling_price
, wdd.currency_Code ,wdd.weight_uom_code, wdd.attribute1, wnd.delivery_id
,(SELECT NVL (a.c_ext_attr2, '')
FROM ego_mtl_sy_items_ext_b a
WHERE a.attr_group_id = 92
AND ROWNUM = 1
AND a.c_ext_attr1 = 'MY'
AND a.inventory_item_id = oola.inventory_item_id
) hts
, ott.NAME order_type
, oola.link_to_line_id
, wdd.shipped_quantity
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, oe_order_lines_all oola
, oe_transaction_types_tl ott
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND oola.line_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND UPPER (ott.NAME) NOT LIKE '%INTERIM%'
AND wdd.organization_id = 5180
AND wdd.inventory_item_id = NVL(p_item_id, wdd.inventory_item_id) --TBA
AND wdd.source_code = 'OE'
AND wnd.delivery_id IN (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_delivery_no, ',') + 1))
GROUP BY item_description ,inventory_item_id ,source_line_id, unit_selling_price
, currency_Code ,weight_uom_code,attribute1, delivery_id, hts, order_type, link_to_line_id) a;
l_unit_selling_price NUMBER;
l_tot NUMBER;
l_sub_tot NUMBER := 0;
l_sell_price VARCHAR2(100);
l_ship_to_org NUMBER;
l_location VARCHAR2(100);
BEGIN
FOR i IN c_line_items LOOP
BEGIN
SELECT DECODE
(INSTR (UPPER (ott.NAME), 'INTERNAL'),
0, TO_NUMBER
(DECODE
(DECODE (oel.line_id,
NVL (oel.top_model_line_id,
oel.line_id
), 'XX',
NVL (oel.attribute15, 'XX')
),
'D', NULL,
'PD', NULL,
xxat_commercial_rpt.xxat_rollup_price
(oel.line_id,
oel.top_model_line_id,
oel.link_to_line_id,
'LINE_UNIT',
oel.attribute15
)
)
),
oel.tp_attribute13
) unit_selling_price
INTO l_unit_selling_price
FROM oe_order_lines_all oel,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
oe_transaction_types_tl ott
WHERE wda.delivery_id = wnd.delivery_id
AND oel.line_id = wdd.source_line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND oel.header_id = wdd.source_header_id
AND wdd.source_code = 'OE'
AND oel.line_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND UPPER (ott.NAME) NOT LIKE '%INTERIM%'
AND wnd.organization_id = 5180
AND wdd.inventory_item_id = NVL(p_item_id, wdd.inventory_item_id) --TBA
AND wnd.delivery_id = i.delivery_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price txn type:'||SQLERRM);
END;
BEGIN
SELECT unit_selling_price, ship_to_org_id, hcsua.LOCATION
INTO l_sell_price, l_ship_to_org, l_location
FROM oe_order_lines_all oel, hz_cust_site_uses_all hcsua
WHERE 1 = 1
AND line_id = i.source_line_id
AND hcsua.site_use_id = ship_to_org_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_sell_price:'||SQLERRM);
END;
IF l_unit_selling_price IS NULL AND l_location NOT LIKE '%VMI%'
THEN
l_unit_selling_price := l_sell_price;
END IF;
IF i.order_type LIKE '%Trade%'
THEN
BEGIN
SELECT DECODE (item_type_code,
'KIT', NVL (attribute18,
l_unit_selling_price),
DECODE (top_model_line_id,
NULL, NVL (attribute18,
l_unit_selling_price
),
NULL
)
)
INTO l_unit_selling_price
FROM oe_order_lines_all oel
WHERE 1 = 1 AND line_id = i.source_line_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price using Trade value:'||SQLERRM);
END;
END IF;
IF NVL (l_unit_selling_price, 0) = 0
THEN
BEGIN
SELECT ROUND (( (x.unit_selling_price * bic.attribute6)
/ 100
/ bic.component_quantity
),
2
)
INTO l_unit_selling_price
FROM oe_order_lines_all x,
bom_bill_of_materials bom,
bom_inventory_components bic
WHERE x.line_id = i.link_to_line_id
AND bom.organization_id = 105
AND bic.disable_date IS NULL
AND x.inventory_item_id = bom.assembly_item_id
AND bom.bill_sequence_id = bic.bill_sequence_id
AND bic.component_item_id = i.inventory_item_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price using BOM:'||SQLERRM);
END;
END IF;
--Modified for ver 1.1 and 1.2
IF (p_3pl_carrier = 'FEDEX') THEN
IF (p_sell_price_flag = 'Y') THEN
p_selling_price := TO_NUMBER(TO_CHAR (l_unit_selling_price, 'fm99999999990.000000'));
ELSE
p_selling_price := NULL;
END IF;
--l_unit_price := TO_CHAR (l_unit_selling_price, 'fm99,999,999,990.00');
l_tot := i.tot_qty * TO_NUMBER(TO_CHAR (l_unit_selling_price, 'fm99999999990.000000'));
--l_unit_weight := i.unit_weight * i.ordered_quantity;
l_sub_tot := l_sub_tot + l_tot;
fnd_file.put_line(fnd_file.log,'l_unit_selling_price'||TO_CHAR (l_unit_selling_price, 'fm99999999990.000000')||', Qty:'||i.tot_qty);
fnd_file.put_line(fnd_file.log,'Item Total'||c_line_items%ROWCOUNT||': '||l_sub_tot);
ELSE
IF (p_sell_price_flag = 'Y') THEN
p_selling_price := TO_NUMBER(TO_CHAR (l_unit_selling_price, 'fm99999999990.00000'));
ELSE
p_selling_price := NULL;
END IF;
--l_unit_price := TO_CHAR (l_unit_selling_price, 'fm99,999,999,990.00');
l_tot := i.tot_qty * TO_NUMBER(TO_CHAR (l_unit_selling_price, 'fm99999999990.00000'));
--l_unit_weight := i.unit_weight * i.ordered_quantity;
l_sub_tot := l_sub_tot + l_tot;
fnd_file.put_line(fnd_file.log,'l_unit_selling_price'||TO_CHAR (l_unit_selling_price, 'fm99999999990.00000')||', Qty:'||i.tot_qty);
fnd_file.put_line(fnd_file.log,'Item Total'||c_line_items%ROWCOUNT||': '||l_sub_tot);
END IF;
END LOOP;
fnd_file.put_line(fnd_file.log,'End of total declared value.');
RETURN l_sub_tot;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error in xx_total_declared_val function:'||SQLERRM);
RETURN -1;
END xx_total_declared_val;
-- ver 1.1 FedEx Start
FUNCTION xx_webapi_FedEx_fnc(p_waybill_dummy_no IN VARCHAR2
,p_3pl_carrier IN VARCHAR2) RETURN VARCHAR2
AS
l_instance VARCHAR2(100);
l_url VARCHAR2(1000);
l_username VARCHAR2(50);
l_password VARCHAR2(50);
l_wallet_url VARCHAR2(1000);
req UTL_HTTP.REQ;
l_content CLOB;
l_sub_content CLOB;
resp UTL_HTTP.RESP;
lc_response VARCHAR2(32767);
buffer VARCHAR2(32767);
l_shipmentTrackingNumber VARCHAR2(250);
l_soa_flow_id VARCHAR2(100);
l_ret_shiptrackno VARCHAR2(250);
l_img_format VARCHAR2(10);
l_type_code VARCHAR2(10);
l_lbl_base64 CLOB;
l_response_text CLOB;
l_output_clob CLOB;
l_response_clob CLOB;
l_pos_start NUMBER;
l_delivery_no_all VARCHAR2(2500);
l_ins_soaFlowID NUMBER;
l_ins_label64 CLOB;
l_carrier_name VARCHAR2(100);
l_waybill_service_id NUMBER;
l_cnt NUMBER;
BEGIN
fnd_file.put_line(fnd_file.log,'Start of XX_WEBAPI_FEDEX_FNC Function');
BEGIN
SELECT name
INTO l_instance
FROM v$pdbs;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while fetching instance name. '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Exception while fetching instance name. '||SQLERRM);
RETURN 'Error during Instance fetch.';
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_instance '||l_instance);
DBMS_OUTPUT.PUT_LINE('l_instance '||l_instance);
BEGIN
SELECT description url
,SUBSTR(meaning,1,INSTR(meaning,'_')-1) username
,tag password
INTO l_url
,l_username
,l_password
FROM fnd_lookup_values
WHERE lookup_type='XXAT_3PL_SOA_LKP_POST'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL (end_date_active, SYSDATE)
AND LANGUAGE = USERENV ('LANG')
AND source_lang = USERENV ('LANG')
AND enabled_flag = 'Y'
AND lookup_code = p_3pl_carrier; --l_instance;
EXCEPTION
WHEN OTHERS THEN
l_url := NULL;
l_username := NULL;
l_password := NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while fetching url from lookup XXAT_3PL_SOA_LKP_POST description. '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Exception while fetching url from lookup XXAT_3PL_SOA_LKP_POST description. '||SQLERRM);
RETURN 'Error during API details fetch from Lookup.';
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_url '||l_url);
DBMS_OUTPUT.PUT_LINE('l_url '||l_url);
BEGIN
SELECT 'file:'||SYS_CONTEXT('USERENV','ORACLE_HOME')||'/appsutil/wallet'
INTO l_wallet_url
FROM dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_wallet_url '||l_wallet_url);
DBMS_OUTPUT.PUT_LINE('l_wallet_url '||l_wallet_url);
EXCEPTION
WHEN OTHERS THEN
l_wallet_url := NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while fetching Oracle wallet. '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Exception while fetching Oracle wallet. '||SQLERRM);
RETURN 'Exception while fetching Oracle wallet. '||SUBSTR(SQLERRM,1,100);
END;
DBMS_OUTPUT.PUT_LINE('Before API being called.');
--API being called
BEGIN
SELECT LISTAGG(TRIM(delivery_no), ',') WITHIN GROUP(ORDER BY delivery_no)
INTO l_delivery_no_all
FROM xxat_3pl_waybill_service
WHERE waybill_number_dummy = TRIM(p_waybill_dummy_no); --'DUMM0001414';
SELECT DISTINCT carrier_name
INTO l_carrier_name
FROM xxat_3pl_waybill_service
WHERE waybill_number_dummy = TRIM(p_waybill_dummy_no);
SELECT carrier_name, waybill_service_id
INTO l_carrier_name, l_waybill_service_id
FROM xxat_3pl_waybill_service
WHERE waybill_number_dummy = TRIM(p_waybill_dummy_no)
AND parent_service_id IS NULL
AND rownum = 1;
--call FedEx URL
l_content := prepare_json_FedEx_service (p_waybill_dummy_no,l_delivery_no_all);
IF l_content <> 'NA' THEN
fnd_file.put_line(fnd_file.log,'After Content is set.');
fnd_file.put_line(fnd_file.log,'1');
UTL_HTTP.SET_WALLET(l_wallet_url,NULL);
fnd_file.put_line(fnd_file.log,'2');
req := UTL_HTTP.BEGIN_REQUEST(l_url,'POST');
fnd_file.put_line(fnd_file.log,'3');
UTL_HTTP.SET_AUTHENTICATION(req, l_username, l_password);
fnd_file.put_line(fnd_file.log,'4');
UTL_HTTP.SET_HEADER(req,'user-agent','Apache-HttpClient/4.1.1 (java 1.5)');
fnd_file.put_line(fnd_file.log,'5');
UTL_HTTP.SET_HEADER(req, 'accept-encoding', 'gzip, deflate');
fnd_file.put_line(fnd_file.log,'6');
UTL_HTTP.SET_HEADER(req, 'content-type', 'application/json');
fnd_file.put_line(fnd_file.log,'7');
UTL_HTTP.SET_HEADER(req, 'accept', 'application/json');
fnd_file.put_line(fnd_file.log,'8');
UTL_HTTP.SET_HEADER(req, 'Connection', 'Keep-Alive');
fnd_file.put_line(fnd_file.log,'9');
UTL_HTTP.SET_HEADER(req,'Content-Length',LENGTH(l_content) );
fnd_file.put_line(fnd_file.log,'10, Length of content:'||LENGTH(l_content));
--UTL_HTTP.WRITE_TEXT(req,l_content);
--UTL_HTTP.WRITE_TEXT can use only VARCHAR2 and NOT CLOB data.
DECLARE
l_chunkData VARCHAR2(500);
l_chunkStart NUMBER := 1;
BEGIN
LOOP
l_chunkData := null;
l_chunkData := substr(l_content, l_chunkStart, 255);
UTL_HTTP.WRITE_TEXT(req, l_chunkData);
if (length(l_chunkData) < 255) then exit; end if;
l_chunkStart := l_chunkStart + 255;
END LOOP;
END;
DBMS_OUTPUT.PUT_LINE('11');
resp := UTL_HTTP.GET_RESPONSE(req);
dbms_output.put_line('After Response.');
--start 19Mar
DBMS_LOB.createtemporary (l_response_clob, FALSE);
BEGIN
LOOP
UTL_HTTP.READ_TEXT(resp,buffer,32767);
DBMS_LOB.writeappend (l_response_clob, LENGTH (buffer), buffer);
BEGIN
SELECT JSON_VALUE(buffer,'$.output.transactionShipments.masterTrackingNumber')
, JSON_VALUE(buffer,'$.soaFlowId')
INTO l_shipmentTrackingNumber
, l_soa_flow_id
FROM dual;
IF (l_shipmentTrackingNumber IS NOT NULL) THEN
l_ret_shiptrackno := l_shipmentTrackingNumber;
fnd_file.put_line(fnd_file.log,'Shipment Track number1:'||l_ret_shiptrackno);
DBMS_OUTPUT.PUT_LINE('Shipment Track number1:'||l_ret_shiptrackno);
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while getting the flow ID'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Error while getting the flow ID'||SQLERRM);
END;
END LOOP;
END;
END IF;
EXCEPTION --line no 247 to be added
WHEN UTL_HTTP.END_OF_BODY
THEN
DBMS_OUTPUT.PUT_LINE('Inside Exception UTL_HTTP.END_OF_BODY.');
fnd_file.put_line(fnd_file.log,'Inside Exception UTL_HTTP.END_OF_BODY.');
--
l_cnt := REGEXP_COUNT(l_response_clob,'"encodedLabel" : "');
fnd_file.put_line(fnd_file.log,'l_cnt:'||l_cnt);
--
--l_pos_start := INSTR(l_response_clob,'"encodedLabel" : "');
IF (l_cnt = 0) THEN
fnd_file.put_line(fnd_file.log,'l_response_clob:'||l_response_clob);
INSERT INTO xxat_3pl_waybill_service
(parent_service_id
, waybill_number_dummy
, hawb
, carrier_name
, label_base64
, label_base64_box_flag
, label_file_name
, soa_flow_id
, response_json)
VALUES( l_waybill_service_id --parent_service_id
, p_waybill_dummy_no --waybill_number_dummy
, l_ret_shiptrackno --hawb
, l_carrier_name --carrier_name
, l_ins_label64 --label_base64
, 'N' --label_base64_box_flag
, NULL --label_file_name
, l_ins_soaFlowID --soa_flow_id
, l_response_clob --response_json
);
ELSE
FOR i IN 1..l_cnt LOOP
l_pos_start := INSTR(l_response_clob,'"encodedLabel" : "',1,i);
IF (l_pos_start = 0) THEN
fnd_file.put_line(fnd_file.log,'Failure msg :" '||l_response_clob);
dbms_output.put_line('Failure msg :" '||l_response_clob);
ELSE
fnd_file.put_line(fnd_file.log,'l_pos_start FOR "encodedLabel" '||l_pos_start);
dbms_output.put_line('l_pos_start FOR "encodedLabel" '||l_pos_start);
l_ins_label64 :=SUBSTR(l_response_clob
,INSTR(l_response_clob,'"',l_pos_start,3) + 1
,INSTR(l_response_clob,'"',l_pos_start,4) - INSTR(l_response_clob,'"',l_pos_start,3) - 1 );
END IF;
--insert label
INSERT INTO xxat_3pl_waybill_service(parent_service_id
, waybill_number_dummy
, hawb
, carrier_name
, label_base64
, label_base64_box_flag
, label_file_name
, soa_flow_id
, response_json)
VALUES( l_waybill_service_id --parent_service_id
, p_waybill_dummy_no --waybill_number_dummy
, l_ret_shiptrackno --hawb
, l_carrier_name --carrier_name
, l_ins_label64 --label_base64
, 'Y' --label_base64_box_flag
, l_ret_shiptrackno||'_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'_'||TO_CHAR(SYSDATE,'HH24MISS')||'_'||i||'_label.pdf' --label_file_name
, l_ins_soaFlowID --soa_flow_id
, l_response_clob --response_json
);
COMMIT;
END LOOP;
END IF;
--
l_pos_start := 0;
dbms_output.put_line('Length of l_response_clob:'||LENGTH(l_response_clob));
fnd_file.put_line(fnd_file.log,'Length of l_response_clob:'||LENGTH(l_response_clob));
l_pos_start := INSTR(l_response_clob,'"soaFlowId" : "');
dbms_output.put_line('l_pos_start:'||l_pos_start);
fnd_file.put_line(fnd_file.log,'l_pos_start:'||l_pos_start);
l_ins_soaFlowID := TO_NUMBER(SUBSTR(l_response_clob
,INSTR(l_response_clob,'"',l_pos_start,3) + 1
,INSTR(l_response_clob,'"',l_pos_start,4) - INSTR(l_response_clob,'"',l_pos_start,3) - 1));
dbms_output.put_line('l_ins_soaFlowID:'||l_ins_soaFlowID);
fnd_file.put_line(fnd_file.log,'l_ins_soaFlowID:'||l_ins_soaFlowID);
UPDATE xxat_3pl_waybill_service
SET hawb = l_ret_shiptrackno
,soa_flow_id = l_ins_soaFlowID
WHERE waybill_number_dummy = p_waybill_dummy_no
AND parent_service_id IS NULL;
/*
INSERT INTO xxat_3pl_waybill_service(parent_service_id
, waybill_number_dummy
, hawb
, carrier_name
, label_base64
, label_base64_box_flag
, label_file_name
, soa_flow_id
, response_json)
VALUES( l_waybill_service_id --parent_service_id
, p_waybill_dummy_no --waybill_number_dummy
, l_ret_shiptrackno --hawb
, l_carrier_name --carrier_name
, l_ins_label64 --label_base64
, 'Y' --label_base64_box_flag
, l_ret_shiptrackno||'_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'_'||TO_CHAR(SYSDATE,'HH24MISS')||'_label.pdf' --label_file_name
, l_ins_soaFlowID --soa_flow_id
, l_response_clob --response_json
);
COMMIT;
*/
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before closing the UTL_HTTP response');
DBMS_OUTPUT.PUT_LINE('Before closing the UTL_HTTP response');
UTL_HTTP.END_RESPONSE(resp);
COMMIT;
RETURN NVL(l_ret_shiptrackno ,'No_VAL_RET');
END xx_webapi_FedEx_fnc;
FUNCTION prepare_json_FedEx_service (p_waybill_dummy_no IN VARCHAR2
, p_delivery_no IN VARCHAR2) RETURN CLOB
AS
--packages
CURSOR c_fedex_packages
IS
SELECT rownum, Wnd.Initial_Pickup_Location_Id, wdd.lpn_id
, TO_CHAR(wdd.gross_weight,'fm99990.000') gross_weight
, TO_CHAR(wdd.net_weight,'fm99990.000') net_weight
, wdd.attribute3 length, wdd.attribute4 width, wdd.attribute5 height
, UPPER(wdd.weight_uom_code) weight_uom_code, wnd.delivery_id
, wdd.source_line_id
, COALESCE((SELECT SUM(quantity)
FROM wms_lpn_contents
WHERE organization_id = 5180
AND parent_lpn_id IN (SELECT a.lpn_id
FROM wms_license_plate_numbers a
WHERE a.lpn_id != a.outermost_lpn_id
AND a.organization_id = 5180
AND a.outermost_lpn_id = wdd.lpn_id))
,1 --when LPN has no contents i.e outerbox itself is considered as single entity
) pkg_qty
,(SELECT DISTINCT moqd1.inventory_item_id
FROM wms_license_plate_numbers wlpn1
,mtl_onhand_quantities_detail moqd1
WHERE moqd1.lpn_id = wlpn1.lpn_id
AND moqd1.organization_id = 5180
AND wlpn1.parent_lpn_id = wdd.lpn_id) inventory_item_id
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, wms_license_plate_numbers wlpn
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wlpn.lpn_id = wdd.lpn_id
AND wlpn.lpn_id = wlpn.outermost_lpn_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'WSH'
AND wnd.delivery_id IN (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_delivery_no, ',') + 1);
--line_items
CURSOR c_fedex_line_items
IS
SELECT rownum, a.* FROM (
SELECT item_description ,inventory_item_id ,source_line_id, unit_selling_price
, currency_Code ,weight_uom_code,attribute1, delivery_id, hts, order_type, link_to_line_id
, SUM(shipped_quantity) tot_qty
FROM (SELECT wdd.item_description ,wdd.inventory_item_id ,wdd.source_line_id, wdd.unit_price, oola.unit_selling_price
, wdd.currency_Code ,wdd.weight_uom_code, wdd.attribute1, wnd.delivery_id
,(SELECT NVL (a.c_ext_attr2, '')
FROM ego_mtl_sy_items_ext_b a
WHERE a.attr_group_id = 92
AND ROWNUM = 1
AND a.c_ext_attr1 = 'MY'
AND a.inventory_item_id = oola.inventory_item_id
) hts
, ott.NAME order_type
, oola.link_to_line_id
, wdd.shipped_quantity
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, oe_order_lines_all oola
, oe_transaction_types_tl ott
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND oola.line_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND UPPER (ott.NAME) NOT LIKE '%INTERIM%'
AND wdd.organization_id = 5180
AND wdd.source_code = 'OE'
AND wnd.delivery_id IN (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_delivery_no, ',') + 1))
GROUP BY item_description ,inventory_item_id ,source_line_id, unit_selling_price
, currency_Code ,weight_uom_code,attribute1, delivery_id, hts, order_type, link_to_line_id) a;
l_content CLOB;
l_packages CLOB;
l_sub_content CLOB;
l_line_items_content CLOB;
l_shipper_receiver_dtls CLOB;
l_base_64 CLOB;
l_planned_shipDateTime VARCHAR2(100);
l_shipper_accno VARCHAR2(100);
l_net_box_val VARCHAR2(100);
l_gross_box_val VARCHAR2(100);
l_address1 VARCHAR2(100);
l_address2 VARCHAR2(100);
l_address3 VARCHAR2(100);
l_city VARCHAR2(100);
l_province VARCHAR2(100);
l_state VARCHAR2(100);
l_country_code VARCHAR2(100);
l_postal_code VARCHAR2(100);
l_company_name VARCHAR2(100);
l_shipper_phn_no VARCHAR2(100);
l_full_name VARCHAR2(100);
l_location_id NUMBER;
l_ship_to_site_use_id NUMBER;
l_rcv_postal_code VARCHAR2(100);
l_rcv_city VARCHAR2(100);
l_rcv_country VARCHAR2(100);
l_rcv_address1 VARCHAR2(100);
l_rcv_address2 VARCHAR2(100);
l_rcv_address3 VARCHAR2(100);
l_rcv_comp_name VARCHAR2(100);
l_rcv_party_id NUMBER;
l_rcv_phn_no VARCHAR2(100);
l_rcv_name VARCHAR2(100);
l_declval_curr VARCHAR2(2000);
l_curr_code VARCHAR2(10);
l_declared_val NUMBER;
l_reference_type VARCHAR2(3);
l_waybill_service_id NUMBER;
l_waybill_dummy_no VARCHAR2(50);
l_delivery_no NUMBER;
l_submit VARCHAR2(1);
l_desc_incoterm_uom VARCHAR2(1000);
l_incoterm VARCHAR2(10);
l_dtp VARCHAR2(5);
l_bill_shpmnt_to VARCHAR2(50);
l_duties_taxes_to VARCHAR2(50);
l_rcv_acc_no VARCHAR2(50);
l_third_party_no VARCHAR2(50);
l_acct_no VARCHAR2(50);
l_acct_flag VARCHAR2(1) := 'N';
l_avlbl_prd_to_dest VARCHAR2(1);
l_customs_decl VARCHAR2(10);
l_wy_count NUMBER;
l_country_count NUMBER;
l_us_hts VARCHAR2(100);
l_ship_to_overide VARCHAR2(250);
l_header_id NUMBER;
l_order_name OE_TRANSACTION_TYPES_TL.NAME%TYPE;
l_stateOrProvince VARCHAR2(50);
l_broker_dtls VARCHAR2(250);
l_services VARCHAR2(250);
l_packaging VARCHAR2(250);
l_pkg_cnt NUMBER;
l_unit_selling_price NUMBER;
l_sell_price VARCHAR2(100);
l_unit_price VARCHAR2(100);
l_item_desc VARCHAR2(1000);
l_commodities VARCHAR2(2000);
l_ship_to_org NUMBER;
l_location VARCHAR2(100);
lr_brkr fnd_lookup_values_vl%ROWTYPE;
lv_selling_price NUMBER;
l_state_province_requrd NUMBER;
l_stateOrProvince_stmt VARCHAR2(250);
BEGIN
fnd_file.put_line(fnd_file.log,'Start of prepare_json_FedEx_service function, p_delivery_no:'||p_delivery_no);
BEGIN
SELECT DISTINCT wnd.initial_pickup_location_id, wdd.ship_to_site_use_id
,xws.reference_type, xws.waybill_service_id, xws.waybill_number_dummy, xws.delivery_no
,'N' submit_flag, duties_and_taxes_paid, SUBSTR(receipient_phone_number,1,10), SUBSTR(receipient_contact_person,1,70) receipient_contact_person
,xws.available_product_destination, wdd.source_header_id, xws.broker_details, xws.services
,xws.packaging, xws.bill_shipment_to, xws.duties_and_taxes_to, wdd.currency_Code
,xws.receipient_account_number, xws.third_party_account_number
INTO l_location_id , l_ship_to_site_use_id
,l_reference_type, l_waybill_service_id, l_waybill_dummy_no, l_delivery_no
,l_submit ,l_dtp, l_rcv_phn_no, l_rcv_name
,l_avlbl_prd_to_dest ,l_header_id, l_broker_dtls, l_services
,l_packaging, l_bill_shpmnt_to, l_duties_taxes_to, l_curr_code
,l_rcv_acc_no, l_third_party_no
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, xxat_3pl_waybill_service xws
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND xws.delivery_no = wnd.delivery_id
AND xws.organization_id = wdd.organization_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'OE'
AND xws.parent_service_id IS NULL
AND wnd.delivery_id = (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(p_delivery_no, '[^,]+', 1, LEVEL)))
FROM dual
CONNECT BY LEVEL = 1) --Any one DN number is sufficient.
AND xws.waybill_number_dummy = p_waybill_dummy_no;
dbms_output.put_line('l_location_id:'||l_location_id||' ,l_ship_to_site_use_id:'||l_ship_to_site_use_id||' ,l_reference_type:'||l_reference_type);
dbms_output.put_line('l_waybill_service_id:'||l_waybill_service_id||' ,l_waybill_dummy_no:'||l_waybill_dummy_no||' ,l_delivery_no:'||l_delivery_no||' ,l_submit:'||l_submit);
fnd_file.put_line(fnd_file.log,'l_location_id:'||l_location_id||' ,l_ship_to_site_use_id:'||l_ship_to_site_use_id||' ,l_reference_type:'||l_reference_type);
fnd_file.put_line(fnd_file.log,'l_waybill_service_id:'||l_waybill_service_id||' ,l_waybill_dummy_no:'||l_waybill_dummy_no||' ,l_delivery_no:'||l_delivery_no||' ,l_submit:'||l_submit);
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while fetching l_location_id , l_ship_to_site_use_id: '||SQLERRM);
dbms_output.put_line('Error while fetching l_location_id , l_ship_to_site_use_id: '||SQLERRM);
END;
IF (l_submit = 'N') THEN
--Receiver details
BEGIN
SELECT NVL(SUBSTR(hl.postal_code, 1, 12), '.') postal_code
, NVL(SUBSTR(hl.city, 1,35), '.')
, NVL(SUBSTR(hl.country, 1, 2), '.')
, NVL(SUBSTR(hl.address1, 1, 35), '.')
, NVL(SUBSTR(hl.address2, 1, 35), '.')
, NVL(SUBSTR(hl.address3, 1, 35), '.')
, NVL(SUBSTR(hp.party_name, 1, 35), '.')
, hp.party_id
, hcsua.Attribute21 Ship_To_Overide
, NVL(COALESCE(hl.state, hl.province),'.') stateOrProvince
INTO l_rcv_postal_code
, l_rcv_city
, l_rcv_country
, l_rcv_address1
, l_rcv_address2
, l_rcv_address3
, l_rcv_comp_name
, l_rcv_party_id
, l_ship_to_overide
, l_stateOrProvince
FROM hz_cust_acct_sites_all hcsa,
hz_party_sites hps,
hz_cust_site_uses_all hcsua,
hz_cust_accounts hca,
hz_parties hp,
hz_locations hl
WHERE hps.party_site_id = hcsa.party_site_id
AND hcsa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hp.party_id = hca.party_id
AND hl.location_id = hps.location_id
AND hca.status = 'A'
AND hcsua.status = 'A'
AND hcsua.site_use_id = l_ship_to_site_use_id;
BEGIN
SELECT t.name
INTO l_order_name
FROM oe_transaction_types_tl t,
oe_order_headers_all h
WHERE h.order_type_id = t.TRANSACTION_TYPE_ID
AND UPPER(t.name) LIKE '%INTERNAL%'
AND t.language = USERENV('LANG')
AND h.header_id = l_header_id;
IF (UPPER(l_order_name) LIKE '%INTERNAL%' AND l_ship_to_overide IS NOT NULL) THEN
SELECT NVL(SUBSTR(hl.postal_code, 1, 12), '.') postal_code
, NVL(SUBSTR(hl.city, 1,35), '.')
, NVL(SUBSTR(hl.country, 1, 2), '.')
, NVL(SUBSTR(hl.address1, 1, 35), '.')
, NVL(SUBSTR(hl.address2, 1, 35), '.')
, NVL(SUBSTR(hl.address3, 1, 35), '.')
, NVL(SUBSTR(hp.party_name||' ('||hca.account_number||'), '||haou.name, 1, 35), '.')
, hp.party_id
, NVL(COALESCE(hl.state, hl.province),'.') stateOrProvince
INTO l_rcv_postal_code
, l_rcv_city
, l_rcv_country
, l_rcv_address1
, l_rcv_address2
, l_rcv_address3
, l_rcv_comp_name
, l_rcv_party_id
, l_stateOrProvince
FROM hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units haou
WHERE haou.organization_id = hcasa.org_id
AND hps.party_site_id = hcasa.party_site_id
AND hl.location_id = hps.location_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hp.party_id = hps.party_id
AND hcasa.cust_acct_site_id = TO_NUMBER(TRIM(l_ship_to_overide))
AND ROWNUM=1;
END IF;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'NOT an INTERNAL order.');
dbms_output.put_line('NOT an INTERNAL order.');
END;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while fetching RECEIVER details:'||SQLERRM);
dbms_output.put_line('Error while fetching RECEIVER details:'||SQLERRM);
END;
--Shipper Details
BEGIN
dbms_output.put_line('1');
SELECT NVL(SUBSTR(hr_d.address_line_1, 1, 35),'.') address1
,NVL(SUBSTR(hr_d.address_line_2, 1, 35),'.') address2
,NVL(SUBSTR(hr_d.address_line_3, 1, 35),'.') address3
,NVL(SUBSTR(hr_d.town_or_city, 1, 35),'.') city
,nvl(fcl_pr.meaning,fcl_pr1.meaning) province
,nvl(fcl_st.meaning,fcl_st1.meaning) state
,NVL(hr_d.country,'.') country
,NVL(SUBSTR(hr_d.postal_code, 1, 10),'.') postal_code
,NVL(SUBSTR(hr_d.attribute7, 1, 35),'.') company_name
INTO l_address1
,l_address2
,l_address3
,l_city
,l_province
,l_state
,l_country_code
,l_postal_code
,l_company_name
FROM hr_locations hr_d
,fnd_common_lookups fcl_pr
,fnd_common_lookups fcl_st
,fnd_common_lookups fcl_pr1
,fnd_common_lookups fcl_st1
WHERE location_id = l_location_id -- initial_pickup_location_id --take the value from any one DN since ship_to_location has to be unique for all DN's under same waybill
AND fcl_pr.lookup_type(+) = hr_d.country||'_PROVINCE'
AND fcl_pr.lookup_code(+) = hr_d.region_1
AND fcl_st.lookup_type(+) = hr_d.country||'_STATE'
AND fcl_st.lookup_code(+) = hr_d.region_1
AND fcl_pr1.lookup_type(+) = hr_d.country||'_PROVINCE'
AND fcl_pr1.lookup_code(+) = hr_d.region_2
AND fcl_st1.lookup_type(+) = hr_d.country||'_STATE'
AND fcl_st1.lookup_code(+) = hr_d.region_2;
SELECT description
INTO l_shipper_accno
FROM fnd_lookup_values
WHERE lookup_type = 'XXAT_3PL_SHIPPER_ACCOUNT'
AND tag = 'FEDEX'
AND language = 'US'
AND enabled_flag = 'Y'
AND lookup_code LIKE 'SHIPPER_ACCOUNT%';
SELECT SUBSTR(description, 1, 15)
INTO l_shipper_phn_no
FROM fnd_lookup_values
WHERE lookup_type = 'XXAT_3PL_SHIPPER_ACCOUNT'
AND tag = 'FEDEX'
AND language = 'US'
AND enabled_flag = 'Y'
AND lookup_code = 'SHIPPER_PHONE_NUMBER2'; --SHIPPER_ACCOUNT1
SELECT SUBSTR(UPPER(papf.full_name), 1, 70)
INTO l_full_name
FROM fnd_user u
,per_all_people_f papf
WHERE u.employee_id = papf.person_id
AND SYSDATE BETWEEN effective_start_date AND NVL(effective_end_date, SYSDATE)
AND u.user_id = fnd_global.user_id; -- 125273 fnd_global.user_id
fnd_file.put_line(fnd_file.log,'Shipper Name:'||l_full_name);
dbms_output.put_line('Shipper Name:'||l_full_name);
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while fetching SHIPPER details:'||SQLERRM);
dbms_output.put_line('Error while fetching SHIPPER details:'||SQLERRM);
END;
SELECT COUNT(*)
INTO l_state_province_requrd
FROM fnd_lookup_values_vl
WHERE lookup_type = 'XXAT_3PL_STATE_PROVINCE_REQURD'
AND lookup_code = l_rcv_country;
SELECT DECODE(l_state_province_requrd,0,NULL,'"stateOrProvinceCode" : "'|| l_stateOrProvince ||'",')
INTO l_stateOrProvince_stmt
FROM dual;
l_declared_val := xx_total_declared_val(p_delivery_no
,'FEDEX'
,'N'
, lv_selling_price
, NULL);
l_content := '{
"mergeLabelDocOption" : "LABELS_AND_DOCS",
"labelResponseOptions" : "LABEL",
"requestedShipment" : {
"processDocument":"N",
"shipper" : {
"contact" : {
"personName" : "'|| l_full_name ||'",
"phoneNumber" : '|| l_shipper_phn_no ||',
"companyName" : "'|| l_company_name ||'"
},
"address" : {
"streetLines" : [
"'|| l_address1 ||'",
"'|| l_address2 ||'"
],
"city" : "'|| l_city ||'",
"postalCode" : '|| l_postal_code ||',
"countryCode" : "'|| l_country_code ||'"
}
},
"recipients" : [
{
"contact" : {
"personName" : "'|| l_rcv_name ||'",
"phoneNumber" : '|| l_rcv_phn_no ||',
"companyName" : "'|| l_rcv_comp_name||'"
},
"address" : {
"streetLines" : [
"'|| l_rcv_address1 ||'",
"'|| l_rcv_address2 ||'",
"'|| l_rcv_address3 ||'"
],
"city" : "'|| l_rcv_city ||'",'
|| l_stateOrProvince_stmt ||'
"postalCode" : "'|| l_rcv_postal_code ||'",
"countryCode" : "'|| l_rcv_country ||'"
}
}
],
"shipDatestamp" : "'||TO_CHAR(SYSDATE,'YYYY-MM-DD')||'",
"serviceType" : "'|| l_services ||'",
"packagingType" : "'|| l_packaging ||'",
"pickupType" : "USE_SCHEDULED_PICKUP",
"blockInsightVisibility" : false,
' ||
/* --commented since You can submit a customs value larger than $50,000
--but you can submit only up to $50,000 to declared value (also called the declared value of carriage)
"totalDeclaredValue" : {
"amount" : '|| l_declared_val ||',
"currency" : "' || l_curr_code ||'"
}, */
'"shippingChargesPayment" : {
"paymentType" : "'|| l_bill_shpmnt_to ||'"';
IF (TRIM(l_bill_shpmnt_to) = 'SENDER'
AND l_shipper_accno IS NOT NULL) THEN
l_acct_no := l_shipper_accno;
l_acct_flag := 'Y';
ELSIF (TRIM(l_bill_shpmnt_to) = 'RECIPIENT'
AND l_rcv_acc_no IS NOT NULL) THEN
l_acct_no := l_rcv_acc_no;
l_acct_flag := 'Y';
ELSIF (TRIM(l_bill_shpmnt_to) = 'THIRD_PARTY'
AND l_third_party_no IS NOT NULL) THEN
l_acct_no := l_third_party_no;
l_acct_flag := 'Y';
END IF;
fnd_file.put_line(fnd_file.log,'l_bill_shpmnt_to:'||l_bill_shpmnt_to||', l_acct_no:'||l_acct_no||', l_acct_flag:'||l_acct_flag);
IF l_acct_flag = 'Y' THEN
l_sub_content := ',
"payor" : {
"responsibleParty" : {
"accountNumber" : {
"value" : "'|| l_acct_no ||'"
}
}
}
},';
ELSE
l_sub_content := '
},';
END IF;
l_content := l_content || l_sub_content;
IF l_broker_dtls IS NOT NULL THEN
l_sub_content :=
'
"shipmentSpecialServices": {
"specialServiceTypes": [
"BROKER_SELECT_OPTION"
]
},';
l_content := l_content || l_sub_content;
END IF;
l_sub_content := '
"labelSpecification" : {
"labelFormatType" : "COMMON2D",
"imageType" : "PDF",
"labelStockType" : "STOCK_4X6"
},
"customsClearanceDetail" : {';
l_content := l_content || l_sub_content;
IF l_broker_dtls IS NOT NULL THEN
BEGIN
SELECT *
INTO lr_brkr
FROM fnd_lookup_values_vl
WHERE lookup_type = 'XXAT_3PL_BROKER_SERVICE'
AND lookup_code = l_broker_dtls
AND enabled_flag = 'Y';
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'ERROR BROKER details fetch:'||SQLERRM);
END;
l_sub_content := '
"brokers": [
{
"broker": {
"address": {
"streetLines": [
"'|| SUBSTR(NVL(lr_brkr.attribute6,'.'),1,30) ||'",
"'|| SUBSTR(NVL(lr_brkr.attribute6,'.'),31,60) ||'",
"'|| SUBSTR(NVL(lr_brkr.attribute6,'.'),61,90) ||'"
],
"city": "'|| lr_brkr.attribute7 ||'",
"stateOrProvinceCode": "'|| lr_brkr.attribute11 ||'",
"postalCode": "'|| SUBSTR(NVL(lr_brkr.attribute8,'.'),1,10) ||'",
"countryCode": "'|| SUBSTR(NVL(lr_brkr.attribute9,'.'),1,2) ||'",
"residential": true
},
"contact": {
"personName": "'|| SUBSTR((lr_brkr.attribute1||', '||lr_brkr.attribute2),1,70) ||'",
"emailAddress": "'|| SUBSTR(NVL(lr_brkr.attribute3,'.'),1,80) ||'",
"parsedPersonName": {
"firstName": "'|| lr_brkr.attribute1 ||'",
"lastName": "'|| lr_brkr.attribute2 ||'",
"middleName": "'|| '.' ||'",
"suffix": "'|| lr_brkr.attribute10 ||'"
},
"phoneNumber": "'|| SUBSTR(NVL(lr_brkr.attribute4,'.'),1,15) ||'",
"phoneExtension": '|| SUBSTR(NVL(lr_brkr.attribute12,'.'),1,6) ||',
"companyName": "'|| SUBSTR(NVL(lr_brkr.attribute5,'.'),1,35) ||'",
"faxNumber": '|| NVL(lr_brkr.attribute13,'.') ||'
}
},
"type": "IMPORT"
}
],';
l_content := l_content || l_sub_content;
END IF;
l_sub_content := CASE WHEN l_duties_taxes_to IS NOT NULL
THEN
'
"dutiesPayment" : {
"paymentType" : "'|| l_duties_taxes_to ||'"'||
CASE WHEN (l_duties_taxes_to = 'THIRD_PARTY' AND l_third_party_no IS NOT NULL)
THEN ',
"payor" : {
"responsibleParty" : {
"accountNumber" : {
"value" : "'|| l_third_party_no ||'"
}
}
}'
END
||'
},'
END
||'
"totalCustomsValue" : {
"amount" : '|| l_declared_val ||',
"currency" : "' || l_curr_code ||'"
},
"isDocumentOnly" : false,
"commodities" : [';
l_content := l_content || l_sub_content;
l_sub_content := '';
FOR k IN c_fedex_line_items LOOP
--Unit selling price
BEGIN
BEGIN
SELECT DECODE
(INSTR (UPPER (ott.NAME), 'INTERNAL'),
0, TO_NUMBER
(DECODE
(DECODE (oel.line_id,
NVL (oel.top_model_line_id,
oel.line_id
), 'XX',
NVL (oel.attribute15, 'XX')
),
'D', NULL,
'PD', NULL,
xxat_commercial_rpt.xxat_rollup_price
(oel.line_id,
oel.top_model_line_id,
oel.link_to_line_id,
'LINE_UNIT',
oel.attribute15
)
)
),
oel.tp_attribute13
) unit_selling_price
INTO l_unit_selling_price
FROM oe_order_lines_all oel,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
oe_transaction_types_tl ott
WHERE wda.delivery_id = wnd.delivery_id
AND oel.line_id = wdd.source_line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND oel.header_id = wdd.source_header_id
AND wdd.source_code = 'OE'
AND oel.line_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND UPPER (ott.NAME) NOT LIKE '%INTERIM%'
AND wnd.organization_id = 5180
AND wnd.delivery_id = k.delivery_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price txn type:'||SQLERRM);
END;
BEGIN
SELECT unit_selling_price, ship_to_org_id, hcsua.LOCATION
INTO l_sell_price, l_ship_to_org, l_location
FROM oe_order_lines_all oel, hz_cust_site_uses_all hcsua
WHERE 1 = 1
AND line_id = k.source_line_id
AND hcsua.site_use_id = ship_to_org_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_sell_price:'||SQLERRM);
END;
IF l_unit_selling_price IS NULL AND l_location NOT LIKE '%VMI%'
THEN
l_unit_selling_price := l_sell_price;
END IF;
IF k.order_type LIKE '%Trade%'
THEN
BEGIN
SELECT DECODE (item_type_code,
'KIT', NVL (attribute18,
l_unit_selling_price),
DECODE (top_model_line_id,
NULL, NVL (attribute18,
l_unit_selling_price
),
NULL
)
)
INTO l_unit_selling_price
FROM oe_order_lines_all oel
WHERE 1 = 1 AND line_id = k.source_line_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price using Trade value:'||SQLERRM);
END;
END IF;
IF NVL (l_unit_selling_price, 0) = 0
THEN
BEGIN
SELECT ROUND (( (x.unit_selling_price * bic.attribute6)
/ 100
/ bic.component_quantity
),
2
)
INTO l_unit_selling_price
FROM oe_order_lines_all x,
bom_bill_of_materials bom,
bom_inventory_components bic
WHERE x.line_id = k.link_to_line_id
AND bom.organization_id = 105
AND bic.disable_date IS NULL
AND x.inventory_item_id = bom.assembly_item_id
AND bom.bill_sequence_id = bic.bill_sequence_id
AND bic.component_item_id = k.inventory_item_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price using BOM for FedEx:'||SQLERRM);
END;
END IF;
l_unit_price := TO_CHAR (l_unit_selling_price, 'fm99999999990.000000');
--l_tot := j.tot_qty * TO_NUMBER(TO_CHAR (l_unit_selling_price, 'fm99999999990.00'));
--l_unit_weight := j.unit_weight * j.ordered_quantity;
--l_sub_tot := l_sub_tot + l_tot;
fnd_file.put_line(fnd_file.log,'FedEx Unit price:'||c_fedex_line_items%ROWCOUNT||': '||l_unit_price);
END;
--item description
BEGIN
SELECT NVL (a.c_ext_attr2, '')
INTO l_us_hts
FROM ego_mtl_sy_items_ext_b a
WHERE a.attr_group_id = 92
AND ROWNUM = 1
AND a.c_ext_attr1 = 'MY'
AND a.inventory_item_id = k.inventory_item_id;
SELECT REPLACE(c_ext_attr1,CHR(10),'') c_ext_attr1
INTO l_item_desc
FROM apps.ego_mtl_sy_items_ext_b
WHERE organization_id = '105'
AND attr_group_id = '100'
AND inventory_item_id = k.inventory_item_id;
EXCEPTION
WHEN others THEN
l_us_hts := '.';
fnd_file.put_line(fnd_file.log,'Exception in getting HTS Info for FedEx:'||sqlerrm);
END;
--weight
BEGIN
SELECT TO_CHAR(gross_box_value,'fm999999999999990.000000')
INTO l_gross_box_val
FROM (SELECT SUM(wdd.net_weight) net_box_value, SUM(wdd.gross_weight) gross_box_value
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, wms_license_plate_numbers wlpn
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wlpn.lpn_id = wdd.lpn_id
AND wlpn.lpn_id = wlpn.outermost_lpn_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'WSH'
AND wnd.delivery_id = k.delivery_id);
SELECT TO_CHAR(net_box_value,'fm999999999999990.000000')
INTO l_net_box_val
FROM (SELECT SUM(wdd.shipped_quantity * msi.unit_weight) net_box_value
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, mtl_system_items_b msi
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND msi.organization_id = wdd.organization_id
AND wdd.organization_id = 5180
AND wnd.delivery_id = k.delivery_id
AND wdd.attribute1 = k.attribute1
);
EXCEPTION
WHEN others THEN
l_us_hts := '.';
fnd_file.put_line(fnd_file.log,'Exception in getting WEIGHT Info for FedEx:'||sqlerrm);
END;
l_commodities := '
{
"description" : "' || l_item_desc ||'",
"countryOfManufacture" : "'|| k.attribute1 ||'",
"quantity" : '|| k.tot_qty ||',
"quantityUnits" : "PCS",
"unitPrice" : {
"amount" : '|| l_unit_price ||',
"currency" : "'|| k.currency_code ||'"
},
"customsValue" : {
"amount" : '|| l_unit_price ||',
"currency" : "'|| k.currency_code ||'"
},
"weight" : {
"units" : "'|| UPPER(k.weight_uom_code) ||'",
"value" : '|| l_net_box_val ||'
}
},';
l_sub_content := l_sub_content || l_commodities;
END LOOP;
l_sub_content := RTRIM(l_sub_content, ',')
||'
]
},
"shippingDocumentSpecification" : {
"shippingDocumentTypes" : [
"COMMERCIAL_INVOICE"
],
"commercialInvoiceDetail" : {
"documentFormat" : {
"stockType" : "PAPER_LETTER",
"docType" : "PDF"
}
}
},
"rateRequestType" : [
"NONE"
],
"requestedPackageLineItems" : [';
l_content := l_content || l_sub_content;
l_sub_content := '';
l_line_items_content := '';
FOR j IN c_fedex_packages LOOP
/*
--Unit selling price
BEGIN
BEGIN
SELECT DECODE
(INSTR (UPPER (ott.NAME), 'INTERNAL'),
0, TO_NUMBER
(DECODE
(DECODE (oel.line_id,
NVL (oel.top_model_line_id,
oel.line_id
), 'XX',
NVL (oel.attribute15, 'XX')
),
'D', NULL,
'PD', NULL,
xxat_commercial_rpt.xxat_rollup_price
(oel.line_id,
oel.top_model_line_id,
oel.link_to_line_id,
'LINE_UNIT',
oel.attribute15
)
)
),
oel.tp_attribute13
) unit_selling_price
INTO l_unit_selling_price
FROM oe_order_lines_all oel,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
oe_transaction_types_tl ott
WHERE wda.delivery_id = wnd.delivery_id
AND oel.line_id = wdd.source_line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND oel.header_id = wdd.source_header_id
AND wdd.source_code = 'OE'
AND oel.line_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND UPPER (ott.NAME) NOT LIKE '%INTERIM%'
AND wnd.organization_id = 5180
AND wnd.delivery_id = j.delivery_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price txn type:'||SQLERRM);
END;
BEGIN
SELECT unit_selling_price, ship_to_org_id, hcsua.LOCATION
INTO l_sell_price, l_ship_to_org, l_location
FROM oe_order_lines_all oel, hz_cust_site_uses_all hcsua
WHERE 1 = 1
AND line_id = j.source_line_id
AND hcsua.site_use_id = ship_to_org_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_sell_price:'||SQLERRM);
END;
IF l_unit_selling_price IS NULL AND l_location NOT LIKE '%VMI%'
THEN
l_unit_selling_price := l_sell_price;
END IF;
IF j.order_type LIKE '%Trade%'
THEN
BEGIN
SELECT DECODE (item_type_code,
'KIT', NVL (attribute18,
l_unit_selling_price),
DECODE (top_model_line_id,
NULL, NVL (attribute18,
l_unit_selling_price
),
NULL
)
)
INTO l_unit_selling_price
FROM oe_order_lines_all oel
WHERE 1 = 1 AND line_id = j.source_line_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price using Trade value:'||SQLERRM);
END;
END IF;
IF NVL (l_unit_selling_price, 0) = 0
THEN
BEGIN
SELECT ROUND (( (x.unit_selling_price * bic.attribute6)
/ 100
/ bic.component_quantity
),
2
)
INTO l_unit_selling_price
FROM oe_order_lines_all x,
bom_bill_of_materials bom,
bom_inventory_components bic
WHERE x.line_id = j.link_to_line_id
AND bom.organization_id = 105
AND bic.disable_date IS NULL
AND x.inventory_item_id = bom.assembly_item_id
AND bom.bill_sequence_id = bic.bill_sequence_id
AND bic.component_item_id = j.inventory_item_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error during l_unit_selling_price using BOM for FedEx:'||SQLERRM);
END;
END IF;
l_unit_price := TO_CHAR (l_unit_selling_price, 'fm99999999990.000000');
--l_tot := j.tot_qty * TO_NUMBER(TO_CHAR (l_unit_selling_price, 'fm99999999990.00'));
--l_unit_weight := j.unit_weight * j.ordered_quantity;
--l_sub_tot := l_sub_tot + l_tot;
fnd_file.put_line(fnd_file.log,'FedEx Unit price:'||c_fedex_line_items%ROWCOUNT||': '||l_unit_price);
END;
/*
--weight
BEGIN
SELECT TO_CHAR(gross_box_value,'fm999999999999990.000000')
INTO l_gross_box_val
FROM (SELECT SUM(wdd.net_weight) net_box_value, SUM(wdd.gross_weight) gross_box_value
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, wms_license_plate_numbers wlpn
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wlpn.lpn_id = wdd.lpn_id
AND wlpn.lpn_id = wlpn.outermost_lpn_id
AND wdd.organization_id = 5180
AND wdd.source_code = 'WSH'
AND wnd.delivery_id = j.delivery_id);
SELECT TO_CHAR(net_box_value,'fm999999999999990.000000')
INTO l_net_box_val
FROM (SELECT SUM(wdd.shipped_quantity * msi.unit_weight) net_box_value
FROM wsh_delivery_details wdd
, wsh_delivery_assignments wda
, wsh_new_deliveries wnd
, mtl_system_items_b msi
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND msi.organization_id = wdd.organization_id
AND wdd.organization_id = 5180
AND wnd.delivery_id = j.delivery_id
AND wdd.attribute1 = j.attribute1
);
EXCEPTION
WHEN others THEN
l_us_hts := '.';
fnd_file.put_line(fnd_file.log,'Exception in getting WEIGHT Info for FedEx:'||sqlerrm);
END;
*/
l_declared_val := xx_total_declared_val(j.delivery_id
,'FEDEX'
,'Y'
,lv_selling_price
,j.inventory_item_id);
l_sub_content := '
{
"sequenceNumber": '|| j.rownum ||',
"weight" : {
"value" : '|| j.net_weight ||',
"units" : "'|| j.weight_uom_code ||'"
},
"declaredValue" : {'
--"amount" : '|| TO_CHAR((lv_selling_price * j.pkg_qty),'fm999999999999990.000000') ||',
||'
"currency" : "'|| l_curr_code ||'"
}
},';
l_line_items_content := l_line_items_content || l_sub_content;
l_pkg_cnt := j.rownum;
END LOOP;
l_line_items_content := RTRIM(l_line_items_content, ',')
||'
],
';
l_content := l_content || l_line_items_content;
l_sub_content := '"totalPackageCount" : '|| l_pkg_cnt ||'
},
"accountNumber" : {
"value" : "'|| l_shipper_accno ||'"
}
}';
l_content := l_content || l_sub_content;
BEGIN
UPDATE xxat_3pl_waybill_service xws
SET content_json = l_content
WHERE waybill_number_dummy = p_waybill_dummy_no
AND parent_service_id IS NULL;
COMMIT;
EXCEPTION
WHEN others THEN
fnd_file.put_line(fnd_file.log,'Error while updating "xxat_3pl_waybill_service" with commercial invoice base64 for waybillno:'||p_waybill_dummy_no||' ,withe error:'||SQLERRM);
dbms_output.put_line('Error while updating "xxat_3pl_waybill_service" with commercial invoice base64 for waybillno:'||p_waybill_dummy_no||' ,withe error:'||SQLERRM);
RETURN 'Error during "xxat_3pl_waybill_service" table updation.';
END;
RETURN l_content;
ELSE
fnd_file.put_line(fnd_file.log,'Delivery already submitted for 3PL service.');
RETURN 'NA';
END IF;
END prepare_json_FedEx_service;
-- ver 1.1 FedEx End
END xxat_3pl_webservice_pkg;
/
Comments
Post a Comment