CREATE OR REPLACE PACKAGE BODY APPS.XXC_PO_ETA_PKG AS /************************************************ Author : SANDEEP File Name : XXC_PO_ETA_PKG Created Date : 07th SEP 2011 Modified By : Harish S/SANDEEP L /MAHENDRA D Modified Date : 21th DEC 2012 Package Name : XXC_PO_ETA_PKG Description : This package is used to Update ETA DATE in PO RECIEPTs DFF, inserting data into Landed Cost table and transfering data from Regal Warehouse to Brooklyn Warehouse Comments : Intial program : SM Commented the Date conversion in extract_eta_date proc. : This was causing issue in date display in the receving headers oracle : Will change the report display in discover : Count for Landed Cost has been moved from below the for loop to above for loop : This was causing to restrict inserting all the records when we are Exporting data to Temp Table : Landed Cost variable has been changed to display decimal upto 5 digit precision :on 14-DEC-2011, In XXC_SUBMIT_INV_REQUEST Procedure added Updated statement by Harish S, once the transfer is done then updating the process flag. :on 15-Dec-2011, Added while loop by Harish S in XXC_LC_RCV_INTERFACE_PRC , this while loop is used to wait till the inventory trsactions worker comeplted and the record foud in rcv_shiment_headers with inventory source for the file number. :on 19-Dec-2011, In XXC_LANDED_COST procedure removed the TO_CHAR function for v_landed_cost. This is causing the error while inserting into Staging table. :on 22-Dec-2011, Added column orig_quantity_received while inserting into regal_inv_landed_cost_tab. :on 23-DEC-2011, Added begin block XXC_RCV_SUBMIT_REQUEST. Here we are getting the request status of "Inventory Transaction Worker" program This is used to exit the program if the Inventory transaction worker program is got errored out. :on 29-DEC-2011, While Inserting in rcv_transaction_interface added ATTIRIBUTE1 columns. :on 02-Jan-2012, In XXC_LC_RCV_INTERFACE_PRC procedure modified the While loop condition. :On 03-Jan-2012, Added Inv_processed, brw_processed columns to find the shipment stage. Inv_processed is indicates Inter-Org Tranfer, brw_Processed indicates Received to Brooklyn. :On 13-Jan-2012, Added Functuion xxc_get_rhc_qty to RHC quanity. This function is used in Discoverer User. :On 01-May-2012, In XXC_MTL_TRANSACTIONS procedure, truncate sysdate while checking sysdate in Org accounting periods because of system time it will getting trasanction now system time is truncated. :On 15-May-2012, In XXC_LANDED_COST proceduere main cursor added union all condition. This is same as in Landed Cost Report query. :On 20-Jul-2012,In XXC_PO_ETA_INTRANSIT added If condition to check Open Accounting periods :On 20-Dec-2012, Added Procedure UPDATE_FILE_TRANSFER_DATE this procedure is used to update the file transfer date in Receiving headers DFF column. :On 31-DEC-2012, Modified package to move partial Shipments to Brooklyn :On 17-Jan-2013, Added ABS function to get +ve Value. :On 18-Jan-2013, Commented display message of lv_count. :on 26-Apr-2013, Added column QUANTITY_RECEIVED in Update Statement in the procedure XXC_RCV_SUBMIT_REQUEST. :on 26-Apr-2013, Added column QUANTITY_REMAINING in Update Statement in the procedure XXC_SUBMIT_INV_REQUEST. :On 02-May-2013, Added a condition while inserting data into mtl_material_transaction_temp . If we do any partial transactions to get Transaction Cost. :On 03-May-2013, Added a condition while inserting data into mtl_material_transaction_temp ,orig_quantity_received must be greater than Zero. ************************************************/ PROCEDURE extract_eta_date( P_errbuf OUT VARCHAR2, P_ret_code OUT VARCHAR2, p_organization_id in Number, p_receipt_number IN VARCHAR2, p_shipment_number IN VARCHAR2, p_eta_date IN VARCHAR2, p_reference_number in VARCHAR2) IS l_eta_date date; /*********************************************** This Procedure used to Update ETA DATE in PO RECIEPTs DFF ************************************************/ BEGIN XXC_DISPLAY_MESSAGE('log','Date Value passed :'||p_eta_date); XXC_DISPLAY_MESSAGE('log','Converting :'||TO_DATE(p_eta_date,'YYYY/MM/DD HH24:MI:SS')); -- l_eta_date := to_date(p_eta_date,'YYYY/MM/DD HH24:MI:SS'); SM Commented the date conversion BEGIN UPDATE rcv_shipment_headers SET ATTRIBUTE1 =p_eta_date,--TO_CHAR(p_eta_date,'DD/MON/YYYY 00:00:00') ATTRIBUTE2 =p_reference_number WHERE receipt_num=p_receipt_number AND shipment_num=p_shipment_number; EXCEPTION WHEN OTHERS THEN XXC_DISPLAY_MESSAGE('log','error in updating Receipt header'); END; END extract_eta_date; PROCEDURE XXC_DISPLAY_MESSAGE(p_mode VARCHAR2, p_message VARCHAR2) IS /*********************************************** This Procedure is used for displaying a message ************************************************/ BEGIN IF UPPER(p_mode) = 'OUTPUT' THEN FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message); ELSIF UPPER(p_mode) = 'LOG' THEN FND_FILE.PUT_LINE(FND_FILE.LOG,p_message); dbms_output.put_line(p_message); END IF; dbms_output.put_line(substr(p_message,1,80)); END XXC_DISPLAY_MESSAGE; PROCEDURE XXC_LANDED_COST (p_file_number IN VARCHAR2) IS p_ecode NUMBER; p_emesg VARCHAR2(100); /************************************************************************************* This Procedure used to insert data into Landed Cost(regal_inv_landed_cost_tab) Table *************************************************************************************/ CURSOR landed_cost_cur1 IS select poh.segment1 po_number, trans.attribute1 trans_attribute1, items.segment1||'.'||items.segment2||'.'||items.segment3||'.'||items.segment4 item_num, trans.transaction_date, sum(nvl(trans.primary_quantity,0)-nvl(inln.return_quantity,0)) primary_quantity, trans.po_unit_price, items.inventory_item_id, items.organization_id, items.description FROM mtl_system_items items, po_lines_all pol, po_headers_all poh, rcv_transactions trans, (SELECT parent_transaction_id, SUM(primary_quantity) return_quantity FROM rcv_transactions where transaction_type = 'RETURN TO VENDOR' GROUP BY parent_transaction_id) inln WHERE trans.organization_id = items.organization_id AND pol.item_id = items.inventory_item_id AND poh.po_header_id = pol.po_header_id AND trans.po_line_id = pol.po_line_id AND trans.transaction_type = 'RECEIVE' AND trans.attribute1 = p_file_number AND trans.transaction_id =inln.parent_transaction_id(+) AND nvl(trans.primary_quantity,0)-nvl(inln.return_quantity,0) !=0 GROUP BY poh.segment1 ,trans.attribute1 ,items.segment1||'.'||items.segment2||'.'||items.segment3||'.'||items.segment4,trans.transaction_date, trans.po_unit_price,items.inventory_item_id,items.organization_id,items.description --UNION UNION ALL --Added Union All Condition insted of UNION by Harish S on 15-May-2012. select '9999' po_number, inv.attribute1 trans_attribute1, 'XXXXXX.XXX.XXXX.XX' item_num, inv.invoice_date transaction_date, 1 primary_quantity, dist.amount po_unit_price, null inventory_item_id, null organization_id, null description FROM po_vendors pov, ap_invoice_distributions_all dist, ap_invoice_lines_all lines, ap_invoices_all inv WHERE inv.vendor_id = pov.vendor_id AND inv.invoice_id = dist.invoice_id AND inv.invoice_id = lines.invoice_id AND dist.invoice_line_number = lines.line_number --Added Condition by Harish S, on 15-May-2012 AND dist.amount >0 AND lines.amount>0 AND dist.LINE_TYPE_LOOKUP_CODE != 'PREPAY' AND UPPER(dist.description) LIKE '%ADDED-GOODS%' AND UPPER(lines.description) LIKE '%ADDED-GOODS%' AND inv.attribute1 =p_file_number ORDER BY 3 ASC; v_landed_cost NUMBER; v_total_received NUMBER; v_extended_cost NUMBER; v_total_landed_cost NUMBER; v_overhead NUMBER; V_landed_pct NUMBER; V_USER NUMBER; v_count NUMBER; lv_waybill_airbill_num VARCHAR2(30); BEGIN v_landed_pct:=(xxc_get_landed_pct(p_file_number)); XXC_DISPLAY_MESSAGE('log','v_landed_pct: ' ||V_landed_pct); v_user:=fnd_global.user_id; BEGIN SELECT COUNT (1) INTO v_count FROM regal.regal_inv_landed_cost_tab WHERE file_number = p_file_number; EXCEPTION --Exception Handling WHEN OTHERS THEN XXC_DISPLAY_MESSAGE('log','Data not found from Landed Cost table'); END; --Added Begin belock by Harish S, to update way bill number. on 15-Dec-2011. BEGIN SELECT DISTINCT waybill_airbill_num INTO lv_waybill_airbill_num FROM rcv_shipment_headers WHERE shipment_num = p_file_number -- '15ASPM AND waybill_airbill_num IS NOT NULL; EXCEPTION WHEN OTHERS THEN lv_waybill_airbill_num := NULL; XXC_DISPLAY_MESSAGE('log','Error in Getting Way Bill Number for Shipment :'||p_file_number); END; FOR crec IN landed_cost_cur1 loop BEGIN --v_landed_cost := TO_CHAR(ROUND((V_landed_pct* crec.po_unit_price)+ (crec.po_unit_price),5),'9.99999'); v_landed_cost := (v_landed_pct* crec.po_unit_price)+ (crec.po_unit_price); ---Modify by Harish S, 19-DEC-2011. Removed TO_CHAR function. This is causing Chanrecter to number conversion error. v_total_received := crec.primary_quantity*crec.po_unit_price; v_extended_cost := v_landed_cost * crec.primary_quantity; v_total_landed_cost := v_landed_cost * crec.primary_quantity; v_overhead := (v_extended_cost - (crec.po_unit_price*crec.primary_quantity)); IF v_count = 0 THEN INSERT into regal.regal_inv_landed_cost_tab (po_number, file_number , item_number , receipt_date, quantity_received, orig_quantity_received, --Added by Harish S, on 22-Dec-2011. purchase_price, Total_Received, Quantity_Remaining, --Added by Mahendra D, on 31-DEC-2012. Landed_Cost, Total_Landed_Cost, Total_overheads, Landed_pct, created_by, creation_date, last_updated_by, last_update_date, selected_flag, inventory_item_id, organization_id, brw_processed, --Added on 03-Jan-2011 inv_processed, --Added on 03-Jan-2011 description, waybill_airbill) VALUES (crec.po_number, crec.trans_attribute1, crec.item_num, crec.transaction_date, --crec.primary_quantity, --Commented by Mahendra D, on 03-Jan-2013 0, --Added by Mahendra D, on 31-DEC-2012. crec.primary_quantity, --Added by Harish S, on 22-Dec-2011. crec.po_unit_price, v_total_received, crec.primary_quantity, --Added by Mahendra D, on 31-DEC-2012. v_landed_cost, v_extended_cost, v_overhead, (v_landed_pct*100), v_user, sysdate, v_user, sysdate, 'N', crec.inventory_item_id, crec.organization_id, 'N', --Added on 03-Jan-2011 'N', --Added on 03-Jan-2011 crec.DESCRIPTION, lv_waybill_airbill_num); ELSE xxc_display_message('log','This File Number is Already Loaded.File Number is :'||p_file_number); END IF; EXCEPTION WHEN OTHERS THEN --Exception Handling xxc_display_message('log','Error in Inserting into Staging Table for item :'||crec.item_num||' Error is :'||Sqlerrm); END; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN --Exception Handling xxc_display_message('log','Data not found from cursor or Data have exception :'||Sqlerrm); END XXC_LANDED_COST; FUNCTION xxc_get_landed_pct (p_file_number IN VARCHAR2) RETURN NUMBER IS v_po_sum NUMBER; v_inv_sum NUMBER; p_landed_pct NUMBER; /************************************************** This Function is used calculate Landed Percentage ***************************************************/ BEGIN BEGIN SELECT NVL(SUM(po_amt), 1) po_sum INTO v_po_sum FROM ( SELECT NVL(SUM((NVL(trans.primary_quantity,0)-NVL(inln.return_quantity,0)) * trans.po_unit_price),1) po_amt FROM mtl_system_items items, po_lines_all pol, po_headers_all poh, rcv_transactions trans, (SELECT parent_transaction_id, SUM(primary_quantity) return_quantity FROM rcv_transactions WHERE transaction_type = 'RETURN TO VENDOR' GROUP BY parent_transaction_id) inln WHERE trans.organization_id = items.organization_id AND pol.item_id = items.inventory_item_id AND poh.po_header_id = pol.po_header_id AND trans.po_line_id = pol.po_line_id AND trans.transaction_type = 'RECEIVE' -- AND trans.transaction_id NOT IN (SELECT trans1.parent_Transaction_id -- FROM rcv_transactions trans1 --WHERE trans1.transaction_type ='RETURN TO VENDOR') AND trans.attribute1 = p_file_number AND trans.transaction_id =inln.parent_transaction_id(+) AND NVL(trans.primary_quantity,0)-NVL(inln.return_quantity,0) !=0 UNION ALL SELECT sum(dist.amount) po_amt FROM po_vendors pov, ap_invoice_distributions_all dist, ap_invoice_lines_all lines, ap_invoices_all inv WHERE inv.vendor_id = pov.vendor_id AND inv.invoice_id = dist.invoice_id AND inv.invoice_id = lines.invoice_id AND dist.invoice_line_number = lines.line_number --Added by Harish S on 15-May-2012 AND dist.amount >0 AND lines.amount>0 AND dist.LINE_TYPE_LOOKUP_CODE != 'PREPAY' AND UPPER(dist.description) LIKE '%ADDED-GOODS%' AND UPPER(lines.description) LIKE '%ADDED-GOODS%' AND inv.attribute1 = p_file_number); EXCEPTION --Exception Handling WHEN OTHERS THEN XXC_DISPLAY_MESSAGE('log','Data not found while calculating Landed Percentage'); END; BEGIN SELECT NVL(SUM(dist.amount),0) INTO v_inv_sum FROM po_vendors pov, ap_invoice_distributions_all dist, ap_invoices_all inv WHERE inv.vendor_id = pov.vendor_id AND dist.dist_code_combination_id <> 1024 AND inv.invoice_id = dist.invoice_id --AND dist.amount >0 AND dist.line_type_lookup_code != 'PREPAY' AND UPPER(dist.description) NOT LIKE '%DISCOUNT%' AND inv.attribute1 = p_file_number; p_landed_pct := NVL((v_inv_sum/v_po_sum),0); RETURN(p_landed_pct); --Commented by Sandeep L COMMIT; EXCEPTION --Exception Handling WHEN OTHERS THEN XXC_DISPLAY_MESSAGE('log','Data not found while calculating Landed Percentage'); RETURN(p_landed_pct); END; END xxc_get_landed_pct; PROCEDURE XXC_MTL_TRANSACTIONS(P_errbuf OUT VARCHAR2, P_ret_code OUT VARCHAR2, p_file_number IN VARCHAR2) IS /********************************************************************************************** This Procedure used to transfer data from Regal to Brooklyn Warehouse using Intransit Shipment ***********************************************************************************************/ v_count NUMBER; BEGIN BEGIN INSERT INTO mtl_material_transactions_temp ( inventory_item_id, source_code, transaction_header_id, transaction_temp_id, process_flag, transaction_mode, last_update_date, last_updated_by, creation_date, created_by, organization_id, primary_quantity, transaction_quantity, transaction_uom, transaction_date, transaction_type_id, transaction_action_id, transaction_source_type_id, subinventory_code, transfer_organization, transfer_subinventory, shipment_number, transportation_cost, transportation_account, expected_arrival_date, item_description, item_primary_uom_code, item_inventory_asset_flag, allowed_units_lookup_code, posting_flag, intransit_account, fob_point, acct_period_id, waybill_airbill --Added way bill Number by Harish S, on 15-DEC-2011. ) SELECT lc.inventory_item_id, --Inventory item id 'DATA COLLECTION', --Source code regal.regal_inv_landed_cost_seq.nextval, --Transaction Header id regal.regal_inv_landed_cost_seq.nextval, --Transaction Temp id 'Y', --process flag 3, --transaction mode lc.last_update_date, --last update date lc.last_updated_by, --last updated by sysdate, --creation date lc.created_by, --created by lc.organization_id, --organization_id -- lc.quantity_received*-1, --Primary Quantity --Commented by Mahendra D On 03-Jan-2013 -- lc.quantity_received*-1, --transaction Quantity --Commented by Mahendra D On 03-Jan-2013 lc.quantity_remaining*-1, lc.quantity_remaining*-1, msi.primary_uom_code , --transaction UOM sysdate, --transaction date 21, --transaction type ID(3 for Direct Org Transfer) 21, --transaction action ID 13, --transaction source type ID 'Vessel', -- subinventory_code 82, -- transfer organization 'Brooklyn', -- transfer subinventory lc.file_number, -- shipment number -- lc.total_overheads, --Transportation Cost -- Commented on 02-May-2013 BY Mahendra ((lc.quantity_remaining*lc.total_overheads)/lc.orig_quantity_received) , --Transportation Cost --Added on 02-May-2013 BY Mahendra 1129, --Transportation Account sysdate, --Expected Arrival Date lc.description, --Description msi.primary_uom_code, --item_primary_uom_code 'Y', --item_inventory_asset_flag 3, --Allowed_units_lookup_code 'Y', --posting_flag 1021, --intransit_account 2, --FOB_POINT oap.acct_period_id, --Account Period ID lc.waybill_airbill --waybill airbill --Added way bill Number by Harish S, on 15-DEC-2011. FROM regal.regal_inv_landed_cost_tab lc, mtl_system_items msi, org_acct_periods oap WHERE lc.file_number = p_file_number AND lc.organization_id = msi.organization_id AND lc.inventory_item_id = msi.inventory_item_id AND TRUNC(sysdate) between oap.period_start_date and oap.schedule_close_date --Added Trunc function by Harish S, on 01-May-2012. AND lc.organization_id = oap.organization_id AND lc.orig_quantity_received > 0 -- Added a Condition on 03-May-2013 BY Mahendra AND SELECTED_FLAG = 'Y'; -- AND NVL(inv_processed,'N') ='N'; ----Inter org tranfered Flag --Commented by Mahendra.D on 31-DEC-2012 COMMIT; v_count:= SQL%ROWCOUNT; --XXC_DISPLAY_MESSAGE('log','Total Records Inserted Into mtl_material_transactions_temp :'||v_count); EXCEPTION WHEN OTHERS THEN --Exception Handling XXC_DISPLAY_MESSAGE('log','Inter Org Transfer failed while fetching data from Landed Cost table'); END; --Commented by Harish S. /* BEGIN UPDATE regal.regal_inv_landed_cost_tab SET process_flag ='Y' WHERE SELECTED_FLAG = 'Y' AND process_flag IS NOT NULL; COMMIT; EXCEPTION WHEN OTHERS THEN --Exception Handling XXC_DISPLAY_MESSAGE('log','Process Flag not Updated'); END; */ END XXC_MTL_TRANSACTIONS; PROCEDURE XXC_LC_RCV_INTERFACE_PRC(P_errbuf OUT VARCHAR2, P_ret_code OUT VARCHAR2, p_file_number IN VARCHAR2 ) IS /************************************************** This Procedure is used for generation of Receipt ***************************************************/ v_header_interface_id NUMBER; v_interface_group_id NUMBER; v_shipment_header_id NUMBER; v_shipment_line_id NUMBER; v_transaction_iface_id NUMBER; v_shipment_num VARCHAR2(30); v_shipment_count NUMBER; v_shipment_check_count NUMBER; CURSOR rcv_interface_cur (p_shipment_header_id IN NUMBER) IS SELECT rsl.shipment_line_id, mmt.inventory_item_id, abs(mmt.primary_quantity) quantity_received , mmt.TRANSACTION_UOM primary_uom_code,mc.category_id, mmt.created_by,mmt.last_updated_by FROM rcv_shipment_lines rsl, mtl_material_transactions mmt, mtl_categories mc, mtl_item_categories mic WHERE rsl.shipment_header_id = p_shipment_header_id AND rsl.mmt_transaction_id = mmt.transaction_id AND mc.category_id = mic.category_id AND mic.inventory_item_id = mmt.inventory_item_id AND mic.organization_id = mmt.organization_id AND mic.category_set_id = 1 --Added Condition by Harish S, on 15-Dec-2011. This is condition is used to process the perticular line item which is seledted through form. AND EXISTS (SELECT 1 FROM regal.regal_inv_landed_cost_tab a, rcv_shipment_headers b WHERE rsl.shipment_header_id = b.shipment_header_id AND a.file_number = b.shipment_num AND a.inventory_item_id = rsl.item_id AND a.selected_flag = 'Y'); -- AND NVL(a.brw_processed,'N') ='N' ); ------Brooklyn Received Flag --Commented by Mahendra on 31-DEC-2012 BEGIN ---Checking selected lines in staging table SELECT count(1) INTO v_shipment_count FROM regal.regal_inv_landed_cost_tab WHERE file_number = p_file_number AND selected_flag = 'Y'; --Commented by Harish S, on 19-DEC-2011. --Checking Records exists in rcv_shipment_headers with inventory source SELECT count(1) INTO v_shipment_check_count FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl WHERE shipment_num = p_file_number AND receipt_source_code = 'INVENTORY' AND rsh.shipment_header_id =rsl.shipment_header_id AND NOT EXISTS ( SELECT 1 FROM rcv_transactions rt WHERE rt.shipment_header_id = rsh.shipment_header_id AND rsl.shipment_line_id = rt.shipment_line_id); XXC_DISPLAY_MESSAGE('log','Line count :'||v_shipment_check_count ||' for filenumber '||p_file_number ); XXC_DISPLAY_MESSAGE('log','Total lines Selected from the staging table is :'||v_shipment_count); IF v_shipment_count >0 AND v_shipment_check_count > 0 THEN --SELECT rcv_headers_interface_s.nextval -- INTO v_header_interface_id --FROM dual ; SELECT rcv_interface_groups_s.nextval INTO v_interface_group_id FROM dual; BEGIN SELECT shipment_header_id INTO v_shipment_header_id FROM rcv_shipment_headers rsh WHERE shipment_num = p_file_number AND receipt_source_code = 'INVENTORY'; --AND NOT EXISTS -- ( SELECT 1 FROM rcv_transactions rt -- WHERE rt.shipment_header_id = rsh.shipment_header_id); EXCEPTION WHEN OTHERS THEN XXC_DISPLAY_MESSAGE('log','Shipment Header ID for file number ' || p_file_number || ' is ' ||v_shipment_header_id); --Testing XXC_DISPLAY_MESSAGE('log','This file number is not transacted through Intransit Shipment'); RETURN; END; XXC_DISPLAY_MESSAGE('log','Shipment Header ID for file number ' || p_file_number || ' is ' ||v_shipment_header_id); --Testing /*BEGIN INSERT INTO rcv_headers_interface ( HEADER_INTERFACE_ID, GROUP_ID, PROCESSING_STATUS_CODE, RECEIPT_SOURCE_CODE, RECEIPT_HEADER_ID, --Added on 30-DEC-2011. TRANSACTION_TYPE, AUTO_TRANSACT_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY, --LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, VALIDATION_FLAG, COMMENTS, SHIPMENT_NUM, FROM_ORGANIZATION_ID, SHIP_TO_ORGANIZATION_ID, EXPECTED_RECEIPT_DATE --,RECEIPT_NUM ) SELECT v_header_interface_id , --Header Interface ID v_interface_group_id, --Group ID 'PENDING', --Processing Status Code 'INVENTORY', --Receipt source Code v_shipment_header_id, --Receipt Header Id --Added on 30-DEC-2011. 'RECEIVE', --Transaction Type 'DELIVER' , --AUT Transact Code sysdate, --last update date fnd_global.user_id, --last updated by --1053, --Last Update Login sysdate, --creation date fnd_global.user_id, --created by 'Y', --Validation Flag 'Receiving Through Interface', --Comments p_file_number , --Shipment Number 81, --From Org 82, --To org sysdate --Expected Receipt Date FROM DUAL; EXCEPTION WHEN OTHERS THEN --Exception Handling XXC_DISPLAY_MESSAGE('log','Receiving Transaction failed while loading data into rcv_headers_interface'); END;*/ BEGIN FOR crec IN rcv_interface_cur(v_shipment_header_id) LOOP SELECT rcv_transactions_interface_s.nextval INTO v_transaction_iface_id FROM dual; INSERT INTO rcv_transactions_interface ( --HEADER_INTERFACE_ID, --Commented on 02-JAN-2012 GROUP_ID, INTERFACE_TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, PROCESSING_STATUS_CODE, PROCESSING_MODE_CODE, TRANSACTION_STATUS_CODE, CATEGORY_ID, QUANTITY, ATTRIBUTE1, --Added on 29-Dec-2011. -- ATTRIBUTE3, --Added on 19-DEC-2012 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, RECEIPT_SOURCE_CODE, SHIPMENT_NUM, --Added on 30-DEC-2011 DESTINATION_TYPE_CODE, AUTO_TRANSACT_CODE, SOURCE_DOCUMENT_CODE, -- UNIT_OF_MEASURE, INTERFACE_SOURCE_CODE, ITEM_ID, -- ITEM_DESCRIPTION, UOM_CODE, --EMPLOYEE_ID, SHIPMENT_HEADER_ID, SHIPMENT_LINE_ID, TO_ORGANIZATION_ID, SUBINVENTORY, FROM_ORGANIZATION_ID, FROM_SUBINVENTORY, EXPECTED_RECEIPT_DATE, SHIPPED_DATE, VALIDATION_FLAG -- ,INTRANSIT_OWNING_ORG_ID ) SELECT -- v_header_interface_id, --Header Interface ID v_interface_group_id, --Group ID v_transaction_iface_id, --Interface_transaction_id 'RECEIVE', --Transaction Type sysdate, --Transaction Date 'PENDING', --Processing Status Code 'BATCH', --Processing Mode Code 'PENDING', --Transaction Status Code crec.category_id, --Category ID crec.quantity_received , --Quantity p_file_number, --File Number DFF --Added on 29-Dec-2011. --sysdate, --Transaction Date --Added on 19-Dec-2012. sysdate, --last update date crec.last_updated_by, --last updated by sysdate, --creation date crec.created_by, --created by 'INVENTORY', --Receipt source Code p_file_number, --Shipment Number --Added on 30-DEC-2011 'INVENTORY', --Destination Type Code 'DELIVER' , --AUTO Transact Code 'INVENTORY', --Source Document Code --crec.primary_unit_of_measure, --Unit Of Measure 'RCV', --Interface Source Code crec.inventory_item_id, --Item ID -- crec.description, --Item Description crec.primary_uom_code, --UOM COde --1053, --User v_shipment_header_id, --Shipment Header ID crec.shipment_line_id, --SHipment Line ID 82, --To Organization ID 'Brooklyn', --Sub Inventory ID 81, --From Organization 'Vessel', --From Subinventory sysdate, --Expected Receipt Date sysdate, --Shipped Date 'Y' --Validation Flag --,81 --Intransit Owining Org Id --Added on 02-Jan-2012 FROM dual; END LOOP; END; ELSE XXC_DISPLAY_MESSAGE('log','This File Number has No Inter-Org Transfer Records. Check the rcv_lines_headers with Source'); P_ret_code:=2; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN --Exception Handling XXC_DISPLAY_MESSAGE('log','Receiving Transaction failed while loading data into rcv_transactions_interface'); END XXC_LC_RCV_INTERFACE_PRC; PROCEDURE XXC_MTL_SUBMIT_REQUEST (x_request_id OUT NUMBER)IS /************************************************** This Procedure is used to call Process Transaction Interface ***************************************************/ v_dev_phase VARCHAR2(30); v_dev_status VARCHAR2(30); v_func_phase VARCHAR2(30); v_func_status VARCHAR2(30); v_func_message VARCHAR2(240); v_wait BOOLEAN := TRUE; v_user_description VARCHAR2(240) := 'Process Transaction Interface'; errcode NUMBER; v_program VARCHAR2(200); v_wait_status BOOLEAN; BEGIN errcode := fnd_request.submit_request ( 'INV', --Application 'INCTCM', --Program v_user_description, --Description NULL, FALSE, CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0)); IF errcode = 0 THEN XXC_DISPLAY_MESSAGE('log','Cannot Submit Request') ; else XXC_DISPLAY_MESSAGE('log','Submitting Job to Run '||v_user_description||' Look at Request ID: '||to_char(errcode)) ; COMMIT; END IF; --clear_block; x_request_id := errcode; IF errcode != 0 THEN IF v_wait --if parm to wait is true THEN v_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST(errcode,60,60, v_func_phase,v_func_status, v_dev_phase,v_dev_status,v_func_message); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); IF v_dev_phase != 'COMPLETE' or v_dev_status not in ('NORMAL','WARNING') or NOT v_wait_status THEN XXC_DISPLAY_MESSAGE('log','ERROR: ' || v_user_description || ' did not Complete Normally'); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); END IF; END IF; --end of if p_wait End If; END XXC_MTL_SUBMIT_REQUEST; PROCEDURE XXC_RCV_SUBMIT_REQUEST(p_file_number IN VARCHAR2, p_request_id IN OUT NUMBER, p_ret_code OUT NUMBER ) IS /************************************************** This Procedure is used to call Regal Receive Intransit To Brooklyn ***************************************************/ v_dev_phase VARCHAR2(30); v_dev_status VARCHAR2(30); v_func_phase VARCHAR2(30); v_func_status VARCHAR2(30); v_func_message VARCHAR2(240); v_wait BOOLEAN := TRUE; v_user_description VARCHAR2(240) := 'Regal Receive Intransit To Brooklyn'; errcode NUMBER; v_program VARCHAR2(200); v_wait_status BOOLEAN; v_shipment_check_count NUMBER := 0; lv_test VARCHAR2(8) := 'RUN'; lv_start_time NUMBER; --Added on 03-JAN-2012. lv_end_time NUMBER; --Added on 03-JAN-2012. BEGIN /*While v_shipment_check_count =0 Loop SELECT count(1) INTO v_shipment_check_count FROM rcv_shipment_headers rsh WHERE shipment_num = p_file_number AND receipt_source_code = 'INVENTORY' AND NOT EXISTS ( SELECT 1 FROM rcv_transactions rt WHERE rt.shipment_header_id = rsh.shipment_header_id);*/ IF p_request_id != 0 THEN IF v_wait --if parm to wait is true THEN v_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST(p_request_id,60,60, v_func_phase,v_func_status, v_dev_phase,v_dev_status,v_func_message); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); IF v_dev_phase != 'COMPLETE' or v_dev_status not in ('NORMAL','WARNING') or NOT v_wait_status THEN XXC_DISPLAY_MESSAGE('log','ERROR: ' || v_user_description || ' did not Complete Normally'); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); END IF;--end of if v_dev_phase END IF; --end of if p_wait End If; BEGIN --XXC_DISPLAY_MESSAGE('log', 'Before While Loop Sytem Time :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH12:MI:SS AM')); SELECT TO_NUMBER(((TO_CHAR(Sysdate, 'J') - 1 ) * 86400) + TO_CHAR(Sysdate, 'SSSSS')) INTO lv_start_time From Dual; XXC_DISPLAY_MESSAGE('log', 'Start Time :'||lv_start_time); WHILE lv_test <> 'STOP' LOOP --Checking Records exists in rcv_shipment_headers with inventory source SELECT count(1) INTO v_shipment_check_count FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl WHERE shipment_num = p_file_number AND receipt_source_code = 'INVENTORY' AND rsl.shipment_header_id = rsh.shipment_header_id AND NOT EXISTS ( SELECT 1 FROM rcv_transactions rt WHERE rt.shipment_header_id = rsh.shipment_header_id AND rsl.shipment_line_id = rt.shipment_line_id); --Getting system Time.Added on 03-JAN-2012 SELECT TO_NUMBER(((TO_CHAR(Sysdate, 'J') - 1 ) * 86400) + TO_CHAR(Sysdate, 'SSSSS')) INTO lv_end_time From Dual; IF v_shipment_check_count > 0 THEN lv_test :='STOP'; END IF; IF (lv_end_time - lv_start_time) >= 300 AND v_shipment_check_count = 0 --Maximum wait limit is 5 minitues THEN XXC_DISPLAY_MESSAGE('log','End Time :'||lv_end_time); XXC_DISPLAY_MESSAGE('log','ERROR IN INTER-ORG TRANSFER PLEASE CHECK'); p_ret_code:=2; lv_test :='STOP'; END IF; END LOOP; XXC_DISPLAY_MESSAGE('log', 'After While Loop Sytem Time :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH12:MI:SS AM')); END; ---Update staging stable if the Inter-org transfer is done successfully. UPDATE regal.regal_inv_landed_cost_tab ril SET inv_processed ='Y' WHERE file_number = p_file_number AND EXISTS (SELECT 1 FROM rcv_shipment_headers rh, rcv_shipment_lines rl WHERE rh.shipment_header_id = rl.shipment_header_id AND rh.receipt_source_code = 'INVENTORY' AND rh.shipment_num = ril.file_number AND rl.item_id = ril.inventory_item_id); COMMIT; XXC_DISPLAY_MESSAGE('log','Shipment Line Count is :'||v_shipment_check_count); IF v_shipment_check_count >0 THEN errcode := fnd_request.submit_request ( 'REGAL', --Application 'XXC_PO_ETA_RECEIVE', --Program v_user_description, --Description NULL, FALSE, p_file_number, CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0)); IF errcode = 0 THEN XXC_DISPLAY_MESSAGE('log','Cannot Submit Request') ; ELSE XXC_DISPLAY_MESSAGE('log','Submitting Job to Run '||v_user_description||' Look at Request ID: '||to_char(errcode)) ; COMMIT; END IF; --clear_block; IF errcode != 0 THEN IF v_wait --if parm to wait is true THEN v_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST(errcode,60,60, v_func_phase,v_func_status, v_dev_phase,v_dev_status,v_func_message); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); IF v_dev_phase != 'COMPLETE' or v_dev_status not in ('NORMAL','WARNING') or NOT v_wait_status THEN XXC_DISPLAY_MESSAGE('log','ERROR: ' || v_user_description || ' did not Complete Normally'); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); END IF; --end of errcode END IF; --end of if p_wait End If; END IF; END XXC_RCV_SUBMIT_REQUEST; PROCEDURE XXC_RCV_TRANSACTION_WORKER IS /************************************************** This Procedure is used to call Receiving Transaction Processor ***************************************************/ v_dev_phase VARCHAR2(30); v_dev_status VARCHAR2(30); v_func_phase VARCHAR2(30); v_func_status VARCHAR2(30); v_func_message VARCHAR2(240); v_wait BOOLEAN := TRUE; v_user_description VARCHAR2(240) := 'Receiving Transaction Processor'; errcode NUMBER; v_program VARCHAR2(200); v_wait_status BOOLEAN; v_mode VARCHAR2(20) := 'BATCH'; v_operating_unit NUMBER := 81; BEGIN errcode := fnd_request.submit_request ( 'PO', 'RVCTP', v_user_description, NULL, FALSE, v_mode, NULL, v_operating_unit, CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0)); IF errcode = 0 THEN XXC_DISPLAY_MESSAGE('log','Cannot Submit Request') ; ELSE XXC_DISPLAY_MESSAGE('log','Submitting Job to Run '||v_user_description||' Look at Request ID: '||to_char(errcode)) ; COMMIT; END IF; --clear_block; IF errcode != 0 THEN IF v_wait --if parm to wait is true THEN v_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST(errcode,60,60, v_func_phase,v_func_status, v_dev_phase,v_dev_status,v_func_message); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); IF v_dev_phase != 'COMPLETE' or v_dev_status not in ('NORMAL','WARNING') or NOT v_wait_status THEN XXC_DISPLAY_MESSAGE('log','ERROR: ' || v_user_description || ' did not Complete Normally'); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); END IF; END IF; --end of if p_wait End If; END XXC_RCV_TRANSACTION_WORKER; PROCEDURE XXC_SUBMIT_INV_REQUEST(P_errbuf OUT VARCHAR2, p_ret_code OUT VARCHAR2, p_file_number IN VARCHAR2) IS /********************************************************************* This procedure is used to call all the concurrent programs in Landed Cost **************************************************************************/ v_errbuff VARCHAR2(250); v_retcode NUMBER; v_retcode1 NUMBER; v_dev_phase VARCHAR2(30); v_dev_status VARCHAR2(30); v_func_phase VARCHAR2(30); v_func_status VARCHAR2(30); v_func_message Varchar2(240); v_wait BOOLEAN := TRUE; v_user_description VARCHAR2(240) := 'Regal Intransit Shipment'; errcode NUMBER; v_wait_status BOOLEAN; x_request_id NUMBER; v_start_date DATE; v_end_date DATE; lv_wait VARCHAR2(8) := 'RUN'; lv_wait_start_time NUMBER; lv_wait_end_time NUMBER; lv_count NUMBER; BEGIN XXC_DISPLAY_MESSAGE('log', 'Program Started Sytem Time :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH12:MI:SS AM')); BEGIN --Added by Sandeep on 20-Jul-2012 SELECT oap.period_start_date,oap.schedule_close_date INTO v_start_date,v_end_date FROM org_acct_periods oap WHERE TRUNC(sysdate) BETWEEN oap.period_start_date AND oap.schedule_close_date AND oap.organization_id = 82; EXCEPTION WHEN OTHERS THEN XXC_DISPLAY_MESSAGE('log','No Open Accounting Period found with Transaction Date:'||sysdate||' for the organization: BRW'); END; XXC_DISPLAY_MESSAGE('log','Accounting period Start Date :' ||v_start_date||' Accounting period End Date :'|| v_end_date); IF TRUNC(sysdate) BETWEEN v_start_date AND v_end_date THEN errcode := fnd_request.submit_request ( 'REGAL', --Application 'XXC_PO_ETA_INTRANSIT', --Program Name v_user_description, --Description NULL, FALSE, p_file_number, CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0), CHR(0)); IF errcode = 0 THEN XXC_DISPLAY_MESSAGE('log','Cannot Submit Request') ; ELSE XXC_DISPLAY_MESSAGE('log','Submitting Job to Run '||v_user_description||' Look at Request ID: '||to_char(errcode)) ; COMMIT; END IF; IF errcode != 0 THEN IF v_wait --if parm to wait is true THEN v_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST(errcode,60,60, v_func_phase,v_func_status, v_dev_phase,v_dev_status,v_func_message); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); IF v_dev_phase != 'COMPLETE' or v_dev_status not in ('NORMAL','WARNING') or NOT v_wait_status THEN XXC_DISPLAY_MESSAGE('log','ERROR: ' || v_user_description || ' did not Complete Normally'); XXC_DISPLAY_MESSAGE('log','Phase is ' || v_func_phase || ', Status is ' || v_func_status); END IF; END IF; --end of if p_wait End If; --end of error code XXC_MTL_SUBMIT_REQUEST(x_request_id); XXC_RCV_SUBMIT_REQUEST(p_file_number,x_request_id,v_retcode); --Added p_ret_code on 03-JAN-2012. If The Inter-Org Transfer Failes then Making request as Error. p_ret_code := v_retcode; IF v_retcode = 2 THEN NULL; XXC_DISPLAY_MESSAGE('log','Program Error'); ELSE XXC_RCV_TRANSACTION_WORKER; --Receiving Tansaction worker. END IF; ---Deleting Errored shipment lines in material Transaction Temp table. /*DELETE FROM mtl_material_transactions_temp WHERE shipment_number = p_file_number AND process_flag ='E'; */ --Added by Harish S, on 03-Jan-2012. BEGIN UPDATE regal.regal_inv_landed_cost_tab ril SET brw_processed = 'Y', selected_flag = 'N', QUANTITY_RECEIVED = (QUANTITY_RECEIVED + QUANTITY_REMAINING), --Added By Mahendra on 26-Apr-2013 QUANTITY_REMAINING = 0 -- Added By Mahendra on 26-Apr-2013 WHERE selected_flag = 'Y' AND file_number =p_file_number AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions rt WHERE rsh.shipment_header_id = rsl.shipment_header_id AND rsh.shipment_num = ril.file_number AND rsh.receipt_source_code ='INVENTORY' AND rsl.item_id = ril.inventory_item_id AND rsl.shipment_line_id =rt.shipment_line_id AND rt.transaction_type ='RECEIVE' AND rt.organization_id =82); COMMIT; EXCEPTION WHEN OTHERS THEN --Exception Handling XXC_DISPLAY_MESSAGE('log','Process Flag not Updated'); END; COMMIT; ELSE p_ret_code := 2; XXC_DISPLAY_MESSAGE('log','**Please Open The Organization Accounting Period and Try Again'); END IF; --Ending Account period if ----Calling Update File Transfer date procedure --Added on 21-Dec-2012 IF NVL(p_ret_code,1) = 2 THEN NULL; ELSE BEGIN SELECT TO_NUMBER(((TO_CHAR(Sysdate, 'J') - 1 ) * 86400) + TO_CHAR(Sysdate, 'SSSSS')) INTO lv_wait_start_time From Dual; WHILE lv_wait <> 'STOP' LOOP SELECT TO_NUMBER(((TO_CHAR(Sysdate, 'J') - 1 ) * 86400) + TO_CHAR(Sysdate, 'SSSSS')) INTO lv_wait_end_time From Dual; SELECT count(1) INTO lv_count FROM apps.mtl_material_transactions mmt WHERE mmt.shipment_number = p_file_number AND mmt.organization_id = 82; -- XXC_DISPLAY_MESSAGE('log',' Value in lv_count variable is :'||lv_count); -- Commented ON 18-JAN-2013 By Mahendra IF ABS(lv_wait_start_time - lv_wait_end_time) >= 120 OR lv_count > 0 THEN --wait limit is 2 minitues -- Added ABS function on 17-JAN-2013 by Mahendra lv_wait :='STOP'; END IF; END LOOP; IF lv_count = 0 THEN p_ret_code :=1; XXC_DISPLAY_MESSAGE('log',' Records not found in mtl_material_transactions'); -- Added ON 18-JAN-2013 By Mahendra END IF; END; UPDATE_FILE_TRANSFER_DATE(v_errbuff,v_retcode1,p_file_number); XXC_DISPLAY_MESSAGE('log',' Buffer Message :'||v_errbuff); END IF; ----end added code on 21-Dec-2012. XXC_DISPLAY_MESSAGE('log', 'Program Finished Sytem Time :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH12:MI:SS AM')); END XXC_SUBMIT_INV_REQUEST; FUNCTION xxc_get_rhc_qty(p_file_number IN VARCHAR2, p_item_id IN VARCHAR2) RETURN NUMBER AS v_qty NUMBER:= 0; BEGIN SELECT SUM(rsl.quantity_received) "Receiving Quantity" INTO v_qty FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions rcv WHERE rsh.receipt_source_code = 'VENDOR' AND rsh.shipment_num = p_file_number AND rsh.shipment_header_id = rsl.shipment_header_id AND rcv.organization_id = 81 AND rcv.transaction_type = 'RECEIVE' AND rsh.shipment_header_id = rcv.shipment_header_id AND rcv.shipment_line_id = rsl.shipment_line_id AND rsl.item_id = p_item_id; RETURN v_qty; EXCEPTION --Exception Handling WHEN OTHERS THEN XXC_DISPLAY_MESSAGE('log', 'Data not found while calculating Landed Percentage'); RETURN v_qty; END xxc_get_rhc_qty; -- This procedure is used to Update Receiving headers DFF column. Added on 20-Dec-2012 PROCEDURE UPDATE_FILE_TRANSFER_DATE (p_errbuf OUT VARCHAR2, p_ret_code OUT VARCHAR2, p_file_number IN VARCHAR2) IS BEGIN XXC_DISPLAY_MESSAGE('log','Calling UPDATE_FILE_TRANSFER_DATE Program'); XXC_DISPLAY_MESSAGE('log',' Program START Time :'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH12:MI:SS AM')); BEGIN UPDATE apps.rcv_shipment_headers rsh SET rsh.attribute3 = (SELECT MIN(TO_CHAR(NVL(mmt.transaction_date,mmt.creation_date),'YYYY/MM/DD HH24:MI:SS')) FROM apps.mtl_material_transactions mmt WHERE mmt.shipment_number = rsh.shipment_num AND mmt.organization_id = 82) --Brooklyn Oraganization /* (SELECT TO_CHAR(NVL(rbh.shipped_date,rbh.creation_date),'YYYY/MM/DD HH24:MI:SS') FROM apps.rcv_shipment_headers rbh WHERE rsh.shipment_num=rbh.shipment_num AND rbh.receipt_source_code ='INVENTORY') --File transffered */ WHERE rsh.shipment_num = NVL(p_file_number,rsh.shipment_num)--'JAN1008'--'ASPM1412' AND rsh.receipt_source_code ='VENDOR' --PO Receipt AND rsh.attribute3 IS NULL --BRW Transferred Date -- AND DECODE(NVL(p_file_number,'XX'),'XX',NVL(rsh.shipped_date,rsh.creation_date),p_file_number,'01-JAN-1042') >= DECODE(NVL(p_file_number,'XX'),'XX','19-DEC-2012',p_file_number,'01-JAN-1042') AND EXISTS (SELECT 1 FROM apps.rcv_shipment_headers rbh WHERE rsh.shipment_num=rbh.shipment_num AND rbh.receipt_source_code ='INVENTORY'); EXCEPTION WHEN OTHERS THEN XXC_DISPLAY_MESSAGE('log','ERROR In updating File Transfer date in Receiving Headers DFF. Error is :'||sqlerrm); p_ret_code:=2; END; XXC_DISPLAY_MESSAGE('log', ' Total Records Updated :'||SQL%ROWCOUNT); XXC_DISPLAY_MESSAGE('log',' Program END Time :' ||TO_CHAR(SYSDATE,'DD-MON-YYYY HH12:MI:SS AM')); COMMIT; END UPDATE_FILE_TRANSFER_DATE; END XXC_PO_ETA_PKG; /