Orders Conversion as Booked

CREATE TABLE XX_HEADER_STG
(
HEADER_ID NUMBER(10),
ORDER_NUMBER VARCHAR2(40),
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2(50),
VERSION_NUMBER NUMBER,
ORG_ID NUMBER,
CUSTOMER_NUMBER VARCHAR2(30),
ORDERED_DATE DATE,
ORDER_TYPE_ID NUMBER,
SALESREP_ID NUMBER(20),
NAME VARCHAR2(240),
PRICE_LIST_ID NUMBER,
CONVERSION_RATE NUMBER,
CONVERSION_TYPE_CODE VARCHAR2(30),
TAX_EXEMPT_REASON_CODE VARCHAR2(30),
BOOKED_FLAG VARCHAR2(1),
BOOKED_DATE DATE,
OPEN_FLAG VARCHAR2(1),
TRANSACTIONAL_CURR_CODE VARCHAR2(15),
INVOICING_RULE_ID NUMBER(15),
PAYMENT_TERM_ID NUMBER(15),
PARTY_NAME VARCHAR2(360),
SHIPMENT_PRIORITY_CODE VARCHAR2(30),
SHIPPING_METHOD_CODE VARCHAR2(30),
FOB_POINT_CODE VARCHAR2(30),
FREIGHT_TERMS_CODE VARCHAR2(30),
DESCRIPTION VARCHAR2(240),
SHIP_TO_ADDRESS VARCHAR2(240),
BILL_TO_ADDRESS VARCHAR2(240),
CITY VARCHAR2(60),
SHIPTO NUMBER(15),
BILLTO NUMBER(15),
SOLD_TO_CONTACT_ID NUMBER,
SALES_CHANNEL_CODE VARCHAR2(30),
ORDER_CATEGORY_CODE VARCHAR2(30),
SOLD_FROM_ORG_ID NUMBER,
SOLD_TO_ORG_ID NUMBER,
DELIVER_TO_ORG_ID NUMBER,
REQUEST_ID NUMBER,
STATUS_FLAG VARCHAR2(1),
ERROR_MESSAGE VARCHAR2(1000)
);
========================================================================

CREATE TABLE XX_LINE_STG
(
HEADER_ID NUMBER(10),
LINE_ID NUMBER(10),
LINE_TYPE_ID NUMBER,
LINE_NUMBER NUMBER,
ORG_ID NUMBER,
SEGMENT1 VARCHAR2(40),
PRICING_QUANTITY NUMBER,
PRICING_QUANTITY_UOM VARCHAR2(3),
ORDERED_QUANTITY NUMBER,
ORDER_QUANTITY_UOM VARCHAR2(3),
SHIPPING_QUANTITY NUMBER,
SHIPPING_QUANTITY_UOM VARCHAR2(3),
SHIPMENT_NUMBER NUMBER,
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2 (50),
ORIG_SYS_LINE_REF VARCHAR2 (50),
ORIG_SYS_SHIPMENT_REF VARCHAR2(50),
INVENTORY_ITEM_ID NUMBER,
ORGANIZATION_ID NUMBER,
SALESREP_ID NUMBER(20),
SNAME VARCHAR2(240),
ACCOUNTING_RULE_ID NUMBER,
SOURCE_TYPE_CODE VARCHAR2 (30),
SHIPMENT_PRIORITY_CODE VARCHAR2(30),
FREIGHT_TERMS_CODE VARCHAR2(30),
FOB_POINT_CODE VARCHAR2(30),
UNIT_SELLING_PRICE NUMBER,
UNIT_LIST_PRICE NUMBER,
PAYMENT_TERM_ID NUMBER(15),
NAME VARCHAR2(15),
PRICE_LIST_ID NUMBER,
INVOICING_RULE_ID NUMBER(15),
TAX_VALUE NUMBER,
UNIT_COST NUMBER,
DELIVERY_LEAD_TIME NUMBER,
SHIP_TO_ADDRESS VARCHAR2(240),
BILL_TO_ADDRESS VARCHAR2(240),
CITY VARCHAR2(60),
BILLTO NUMBER(15),
SHIPTO NUMBER(15),
SOLD_FROM_ORG_ID NUMBER,
SOLD_TO_ORG_ID NUMBER,
BOOKED_FLAG VARCHAR2(1),
OPEN_FLAG VARCHAR2(1),
REQUEST_ID NUMBER,
REFERENCE_TYPE VARCHAR2(30),
REFERENCE_HEADER_ID NUMBER,
REFERENCE_LINE_ID NUMBER,
DEMAND_CLASS_CODE VARCHAR2(30),
STATUS_FLAG VARCHAR2 (1) ,
ERROR_MESSAGE VARCHAR2(1000)
);

===========================================================================
SELECT OOH.HEADER_ID,
MSO.SEGMENT1,
OOS.ORDER_SOURCE_ID,
OOH.ORIG_SYS_DOCUMENT_REF,
OTT.TRANSACTION_TYPE_ID,
OOH.VERSION_NUMBER,
OOH.ORG_ID,
OOH.ORDERED_DATE,
HRO.ORGANIZATION_ID SOLD_FROM_ORG_ID,
HCS.SITE_USE_ID SHIPTO,
HCS1.SITE_USE_ID BILLTO,
OOH.SOLD_TO_ORG_ID,
OOH.DELIVER_TO_ORG_ID,
OOH.SOLD_TO_CONTACT_ID,
RSA.SALESREP_ID,
RSA.NAME,
OOH.TRANSACTIONAL_CURR_CODE,
HCA.ACCOUNT_NAME,
HCA.ACCOUNT_NUMBER,
HZP.ADDRESS1 SHIP_TO_ADDRESS,
HZP1.ADDRESS1 BILL_TO_ADDRESS,
HZP.CITY,
OTT.SHIPMENT_PRIORITY_CODE,
OTT.SHIPPING_METHOD_CODE,
OTT.FOB_POINT_CODE,
SPL.PRICE_LIST_ID,
SPL.FREIGHT_TERMS_CODE,
SPL.DESCRIPTION,
OOH.CONVERSION_RATE,
OOH.CONVERSION_TYPE_CODE,
OOH.TAX_EXEMPT_REASON_CODE,
OOH.BOOKED_FLAG,
OOH.BOOKED_DATE,
OOH.OPEN_FLAG,
RAR.RULE_ID,
RTB.TERM_ID,
OOH.SALES_CHANNEL_CODE,
OOH.ORDER_CATEGORY_CODE,
OOH.REQUEST_ID
FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_SOURCES OOS,
MTL_SALES_ORDERS MSO,
OE_TRANSACTION_TYPES_ALL OTT,
HR_ALL_ORGANIZATION_UNITS HRO,
HZ_CUST_SITE_USES_ALL HCS,
HZ_CUST_SITE_USES_ALL HCS1,
SO_PRICE_LISTS_B SPL,
RA_SALESREPS_ALL RSA,
HZ_PARTIES HZP,
HZ_PARTIES HZP1,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCOUNTS HCA1,
RA_RULES RAR,
RA_TERMS_B RTB
WHERE OOH.ORDER_NUMBER=MSO.SEGMENT1
AND OOH.ORDER_SOURCE_ID=OOS.ORDER_SOURCE_ID
AND OOH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_ID
AND OOH.SOLD_FROM_ORG_ID=HRO.ORGANIZATION_ID
AND OOH.SHIP_TO_ORG_ID=HCS.SITE_USE_ID
AND OOH.INVOICE_TO_ORG_ID=HCS1.SITE_USE_ID
AND OOH.SALESREP_ID=RSA.SALESREP_ID
AND OOH.PRICE_LIST_ID=SPL.PRICE_LIST_ID
AND OOH.INVOICING_RULE_ID=RAR.RULE_ID
AND OOH.PAYMENT_TERM_ID=RTB.TERM_ID
AND OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND OOH.SOLD_TO_ORG_ID=HCA1.CUST_ACCOUNT_ID
AND HCA.PARTY_ID=HZP.PARTY_ID
AND HCA1.PARTY_ID=HZP1.PARTY_ID
AND RSA.ORG_ID='204'
AND OOH.ORDER_NUMBER='56701';

================================================================

SELECT OOL.HEADER_ID,
OOL.LINE_ID,
OOL.LINE_NUMBER,
OOL.LINE_TYPE_ID,
OOL.SHIPMENT_NUMBER,
OOL.ORG_ID,
OOS.ORDER_SOURCE_ID,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
OOL.ORIG_SYS_DOCUMENT_REF,
OOL.ORIG_SYS_LINE_REF,
OOL.ORIG_SYS_SHIPMENT_REF,
OOL.PRICING_QUANTITY,
OOL.PRICING_QUANTITY_UOM,
OOL.ORDERED_QUANTITY,
OOL.ORDER_QUANTITY_UOM,
OOL.SHIPPING_QUANTITY,
OOL.SHIPPING_QUANTITY_UOM,
RSA.SALESREP_ID,
RSA.NAME SNAME,
OOL.SOURCE_TYPE_CODE,
OTT.SHIPMENT_PRIORITY_CODE,
OTT.FREIGHT_TERMS_CODE,
OTT.FOB_POINT_CODE,
OOL.UNIT_SELLING_PRICE,
OOL.UNIT_LIST_PRICE,
RTB.TERM_ID,
RTT.NAME,
SPL.PRICE_LIST_ID,
RAR.RULE_ID INVOICING_RULE_ID,
RAR1.RULE_ID ACCOUNTING_RULE_ID,
OOL.TAX_VALUE,
OOL.UNIT_COST,
OOL.DELIVERY_LEAD_TIME,
OOL.REQUEST_ID,
OOL.REFERENCE_TYPE,
OOL.REFERENCE_HEADER_ID,
OOL.REFERENCE_LINE_ID,
OOL.DEMAND_CLASS_CODE,
HZP.ADDRESS1 SHIP_TO_ADDRESS,
HZP1.ADDRESS1 BILL_TO_ADDRESS,
HZP.CITY,
HAO.ORGANIZATION_ID SOLD_FROM_ORG_ID,
HCS.SITE_USE_ID SHIPTO,
HCS1.SITE_USE_ID BILLTO,
HCA1.CUST_ACCOUNT_ID,
OOL.OPEN_FLAG,
OOL.BOOKED_FLAG
FROM OE_ORDER_LINES_ALL OOL,
OE_ORDER_HEADERS_ALL OOH,
MTL_SYSTEM_ITEMS_B MSI,
OE_ORDER_SOURCES OOS,
RA_SALESREPS_ALL RSA,
OE_TRANSACTION_TYPES_ALL OTT,
RA_TERMS_B RTB,
RA_TERMS_TL RTT,
RA_RULES RAR,
RA_RULES RAR1,
SO_PRICE_LISTS_B SPL,
HZ_PARTIES HZP,
HZ_PARTIES HZP1,
HR_ALL_ORGANIZATION_UNITS HAO,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCOUNTS HCA1,
HZ_CUST_SITE_USES_ALL HCS,
HZ_CUST_SITE_USES_ALL HCS1
WHERE OOH.HEADER_ID=OOL.HEADER_ID
AND OOL.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND OOL.ORDER_SOURCE_ID=OOS.ORDER_SOURCE_ID
AND OOL.SALESREP_ID=RSA.SALESREP_ID
AND OTT.TRANSACTION_TYPE_ID=OOH.ORDER_TYPE_ID
AND OOL.PAYMENT_TERM_ID=RTB.TERM_ID
AND OOL.PAYMENT_TERM_ID=RTT.TERM_ID
AND OOL.PRICE_LIST_ID=SPL.PRICE_LIST_ID
AND OOL.INVOICING_RULE_ID=RAR.RULE_ID
AND OOL.ACCOUNTING_RULE_ID=RAR1.RULE_ID
AND OOL.SOLD_FROM_ORG_ID=HAO.ORGANIZATION_ID
AND OOL.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND OOL.SOLD_TO_ORG_ID=HCA1.CUST_ACCOUNT_ID
AND OOL.SHIP_TO_ORG_ID=HCS.SITE_USE_ID
AND OOL.INVOICE_TO_ORG_ID=HCS1.SITE_USE_ID
AND HCA.PARTY_ID=HZP.PARTY_ID
AND HCA1.PARTY_ID=HZP1.PARTY_ID
AND OOL.HEADER_ID='94119'
AND MSI.ORGANIZATION_ID='204'
AND RSA.ORG_ID='204';
===========================================================================

CREATE OR REPLACE PACKAGE XX_OM_ORDER_PKG AS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE XX_OM_STG_PROC;
PROCEDURE XX_OM_VALIDATE;
PROCEDURE XX_OM_LOAD;
END XX_OM_ORDER_PKG;

===========================================================================

CREATE OR REPLACE PACKAGE BODY XX_OM_ORDER_PKG AS
L_ERROR_MESSAGE VARCHAR2(1000);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
AS
BEGIN
XX_OM_STG_PROC;
XX_OM_VALIDATE;
XX_OM_LOAD ;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END MAIN;
PROCEDURE XX_OM_STG_PROC
AS
CURSOR CUR_ORD_HEADER IS
SELECT OOH.HEADER_ID,
MSO.SEGMENT1,
OOS.ORDER_SOURCE_ID,
OOH.ORIG_SYS_DOCUMENT_REF,
OTT.TRANSACTION_TYPE_ID,
OOH.VERSION_NUMBER,
OOH.ORG_ID,
OOH.ORDERED_DATE,
HRO.ORGANIZATION_ID SOLD_FROM_ORG_ID,
HCS.SITE_USE_ID SHIPTO,
HCS1.SITE_USE_ID BILLTO,
OOH.SOLD_TO_ORG_ID,
OOH.DELIVER_TO_ORG_ID,
OOH.SOLD_TO_CONTACT_ID,
RSA.SALESREP_ID,
RSA.NAME,
OOH.TRANSACTIONAL_CURR_CODE,
HCA.ACCOUNT_NAME,
HCA.ACCOUNT_NUMBER,
HZP.ADDRESS1 SHIP_TO_ADDRESS,
HZP1.ADDRESS1 BILL_TO_ADDRESS,
HZP.CITY,
OTT.SHIPMENT_PRIORITY_CODE,
OTT.SHIPPING_METHOD_CODE,
OTT.FOB_POINT_CODE,
SPL.PRICE_LIST_ID,
SPL.FREIGHT_TERMS_CODE,
SPL.DESCRIPTION,
OOH.CONVERSION_RATE,
OOH.CONVERSION_TYPE_CODE,
OOH.TAX_EXEMPT_REASON_CODE,
OOH.BOOKED_FLAG,
OOH.BOOKED_DATE,
OOH.OPEN_FLAG,
RAR.RULE_ID,
RTB.TERM_ID,
OOH.SALES_CHANNEL_CODE,
OOH.ORDER_CATEGORY_CODE,
OOH.REQUEST_ID
FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_SOURCES OOS,
MTL_SALES_ORDERS MSO,
OE_TRANSACTION_TYPES_ALL OTT,
HR_ALL_ORGANIZATION_UNITS HRO,
HZ_CUST_SITE_USES_ALL HCS,
HZ_CUST_SITE_USES_ALL HCS1,
SO_PRICE_LISTS_B SPL,
RA_SALESREPS_ALL RSA,
HZ_PARTIES HZP,
HZ_PARTIES HZP1,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCOUNTS HCA1,
RA_RULES RAR,
RA_TERMS_B RTB
WHERE OOH.ORDER_NUMBER=MSO.SEGMENT1
AND OOH.ORDER_SOURCE_ID=OOS.ORDER_SOURCE_ID
AND OOH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_ID
AND OOH.SOLD_FROM_ORG_ID=HRO.ORGANIZATION_ID
AND OOH.SHIP_TO_ORG_ID=HCS.SITE_USE_ID
AND OOH.INVOICE_TO_ORG_ID=HCS1.SITE_USE_ID
AND OOH.SALESREP_ID=RSA.SALESREP_ID
AND OOH.PRICE_LIST_ID=SPL.PRICE_LIST_ID
AND OOH.INVOICING_RULE_ID=RAR.RULE_ID
AND OOH.PAYMENT_TERM_ID=RTB.TERM_ID
AND OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND OOH.SOLD_TO_ORG_ID=HCA1.CUST_ACCOUNT_ID
AND HCA.PARTY_ID=HZP.PARTY_ID
AND HCA1.PARTY_ID=HZP1.PARTY_ID
AND RSA.ORG_ID='204'
AND OOH.ORDER_NUMBER='56701';
CURSOR CUR_ORD_LINES(HEADER_ID varchar2) IS
SELECT OOL.HEADER_ID,
OOL.LINE_ID,
OOL.LINE_NUMBER,
OOL.LINE_TYPE_ID,
OOL.SHIPMENT_NUMBER,
OOL.ORG_ID,
OOS.ORDER_SOURCE_ID,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
OOL.ORIG_SYS_DOCUMENT_REF,
OOL.ORIG_SYS_LINE_REF,
OOL.ORIG_SYS_SHIPMENT_REF,
OOL.PRICING_QUANTITY,
OOL.PRICING_QUANTITY_UOM,
OOL.ORDERED_QUANTITY,
OOL.ORDER_QUANTITY_UOM,
OOL.SHIPPING_QUANTITY,
OOL.SHIPPING_QUANTITY_UOM,
RSA.SALESREP_ID,
RSA.NAME SNAME,
OOL.SOURCE_TYPE_CODE,
OTT.SHIPMENT_PRIORITY_CODE,
OTT.FREIGHT_TERMS_CODE,
OTT.FOB_POINT_CODE,
OOL.UNIT_SELLING_PRICE,
OOL.UNIT_LIST_PRICE,
RTB.TERM_ID,
RTT.NAME,
SPL.PRICE_LIST_ID,
RAR.RULE_ID INVOICING_RULE_ID,
RAR1.RULE_ID ACCOUNTING_RULE_ID,
OOL.TAX_VALUE,
OOL.UNIT_COST,
OOL.DELIVERY_LEAD_TIME,
OOL.REQUEST_ID,
OOL.REFERENCE_TYPE,
OOL.REFERENCE_HEADER_ID,
OOL.REFERENCE_LINE_ID,
OOL.DEMAND_CLASS_CODE,
HZP.ADDRESS1 SHIP_TO_ADDRESS,
HZP1.ADDRESS1 BILL_TO_ADDRESS,
HZP.CITY,
HAO.ORGANIZATION_ID SOLD_FROM_ORG_ID,
HCS.SITE_USE_ID SHIPTO,
HCS1.SITE_USE_ID BILLTO,
HCA1.CUST_ACCOUNT_ID,
OOL.OPEN_FLAG,
OOL.BOOKED_FLAG
FROM OE_ORDER_LINES_ALL OOL,
OE_ORDER_HEADERS_ALL OOH,
MTL_SYSTEM_ITEMS_B MSI,
OE_ORDER_SOURCES OOS,
RA_SALESREPS_ALL RSA,
OE_TRANSACTION_TYPES_ALL OTT,
RA_TERMS_B RTB,
RA_TERMS_TL RTT,
RA_RULES RAR,
RA_RULES RAR1,
SO_PRICE_LISTS_B SPL,
HZ_PARTIES HZP,
HZ_PARTIES HZP1,
HR_ALL_ORGANIZATION_UNITS HAO,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCOUNTS HCA1,
HZ_CUST_SITE_USES_ALL HCS,
HZ_CUST_SITE_USES_ALL HCS1
WHERE OOH.HEADER_ID=OOL.HEADER_ID
AND OOL.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND OOL.ORDER_SOURCE_ID=OOS.ORDER_SOURCE_ID
AND OOL.SALESREP_ID=RSA.SALESREP_ID
AND OTT.TRANSACTION_TYPE_ID=OOH.ORDER_TYPE_ID
AND OOL.PAYMENT_TERM_ID=RTB.TERM_ID
AND OOL.PAYMENT_TERM_ID=RTT.TERM_ID
AND OOL.PRICE_LIST_ID=SPL.PRICE_LIST_ID
AND OOL.INVOICING_RULE_ID=RAR.RULE_ID
AND OOL.ACCOUNTING_RULE_ID=RAR1.RULE_ID
AND OOL.SOLD_FROM_ORG_ID=HAO.ORGANIZATION_ID
AND OOL.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND OOL.SOLD_TO_ORG_ID=HCA1.CUST_ACCOUNT_ID
AND OOL.SHIP_TO_ORG_ID=HCS.SITE_USE_ID
AND OOL.INVOICE_TO_ORG_ID=HCS1.SITE_USE_ID
AND HCA.PARTY_ID=HZP.PARTY_ID
AND HCA1.PARTY_ID=HZP1.PARTY_ID
AND OOL.HEADER_ID='94119'
AND MSI.ORGANIZATION_ID='204'
AND RSA.ORG_ID='204';
BEGIN
DELETE XX_HEADER_STG;
DELETE XX_LINE_STG;
COMMIT;
FOR REC_CUR_ORD_HEADER IN CUR_ORD_HEADER LOOP
INSERT INTO XX_HEADER_STG
(HEADER_ID,
ORDER_NUMBER,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
VERSION_NUMBER,
ORG_ID,
CUSTOMER_NUMBER,
ORDERED_DATE,
ORDER_TYPE_ID,
SALESREP_ID,
NAME,
PRICE_LIST_ID,
CONVERSION_RATE,
CONVERSION_TYPE_CODE,
TAX_EXEMPT_REASON_CODE,
BOOKED_FLAG,
BOOKED_DATE,
OPEN_FLAG,
TRANSACTIONAL_CURR_CODE,
INVOICING_RULE_ID,
PAYMENT_TERM_ID,
PARTY_NAME,
SHIPMENT_PRIORITY_CODE,
SHIPPING_METHOD_CODE,
FOB_POINT_CODE,
FREIGHT_TERMS_CODE,
DESCRIPTION,
SHIP_TO_ADDRESS,
BILL_TO_ADDRESS,
CITY,
SHIPTO,
BILLTO,
SOLD_TO_CONTACT_ID,
SALES_CHANNEL_CODE,
ORDER_CATEGORY_CODE,
SOLD_FROM_ORG_ID,
SOLD_TO_ORG_ID,
DELIVER_TO_ORG_ID,
REQUEST_ID,
STATUS_FLAG)
VALUES
(REC_CUR_ORD_HEADER.HEADER_ID,
REC_CUR_ORD_HEADER.SEGMENT1,
REC_CUR_ORD_HEADER.ORDER_SOURCE_ID,
REC_CUR_ORD_HEADER.ORIG_SYS_DOCUMENT_REF,
REC_CUR_ORD_HEADER.VERSION_NUMBER,
REC_CUR_ORD_HEADER.ORG_ID,
REC_CUR_ORD_HEADER.ACCOUNT_NUMBER,
REC_CUR_ORD_HEADER.ORDERED_DATE,
REC_CUR_ORD_HEADER.TRANSACTION_TYPE_ID,
REC_CUR_ORD_HEADER.SALESREP_ID,
REC_CUR_ORD_HEADER.NAME,
REC_CUR_ORD_HEADER.PRICE_LIST_ID,
REC_CUR_ORD_HEADER.CONVERSION_RATE,
REC_CUR_ORD_HEADER.CONVERSION_TYPE_CODE,
REC_CUR_ORD_HEADER.TAX_EXEMPT_REASON_CODE,
REC_CUR_ORD_HEADER.BOOKED_FLAG,
REC_CUR_ORD_HEADER.BOOKED_DATE,
REC_CUR_ORD_HEADER.OPEN_FLAG,
REC_CUR_ORD_HEADER.TRANSACTIONAL_CURR_CODE,
REC_CUR_ORD_HEADER.RULE_ID,
REC_CUR_ORD_HEADER.TERM_ID,
REC_CUR_ORD_HEADER.ACCOUNT_NAME,
REC_CUR_ORD_HEADER.SHIPMENT_PRIORITY_CODE,
REC_CUR_ORD_HEADER.SHIPPING_METHOD_CODE,
REC_CUR_ORD_HEADER.FOB_POINT_CODE,
REC_CUR_ORD_HEADER.FREIGHT_TERMS_CODE,
REC_CUR_ORD_HEADER.DESCRIPTION,
REC_CUR_ORD_HEADER.SHIP_TO_ADDRESS,
REC_CUR_ORD_HEADER.BILL_TO_ADDRESS,
REC_CUR_ORD_HEADER.CITY,
REC_CUR_ORD_HEADER.SHIPTO,
REC_CUR_ORD_HEADER.BILLTO,
REC_CUR_ORD_HEADER.SOLD_TO_CONTACT_ID,
REC_CUR_ORD_HEADER.SALES_CHANNEL_CODE,
REC_CUR_ORD_HEADER.ORDER_CATEGORY_CODE,
REC_CUR_ORD_HEADER.SOLD_FROM_ORG_ID,
REC_CUR_ORD_HEADER.SOLD_TO_ORG_ID,
REC_CUR_ORD_HEADER.DELIVER_TO_ORG_ID,
REC_CUR_ORD_HEADER.REQUEST_ID,
'N');
FOR REC_CUR_ORD_LINES IN CUR_ORD_LINES(REC_CUR_ORD_HEADER.HEADER_ID) LOOP
INSERT INTO XX_LINE_STG
(HEADER_ID,
LINE_ID,
LINE_TYPE_ID,
LINE_NUMBER,
ORG_ID,
SEGMENT1,
PRICING_QUANTITY,
PRICING_QUANTITY_UOM,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
SHIPPING_QUANTITY,
SHIPPING_QUANTITY_UOM,
SHIPMENT_NUMBER,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SALESREP_ID,
SNAME,
ACCOUNTING_RULE_ID,
SOURCE_TYPE_CODE,
SHIPMENT_PRIORITY_CODE,
FREIGHT_TERMS_CODE,
FOB_POINT_CODE,
UNIT_SELLING_PRICE,
UNIT_LIST_PRICE,
PAYMENT_TERM_ID,
NAME,
PRICE_LIST_ID,
INVOICING_RULE_ID,
TAX_VALUE,
UNIT_COST,
DELIVERY_LEAD_TIME,
SHIP_TO_ADDRESS,
BILL_TO_ADDRESS,
CITY,
SHIPTO,
BILLTO,
SOLD_FROM_ORG_ID,
SOLD_TO_ORG_ID,
BOOKED_FLAG,
OPEN_FLAG,
REQUEST_ID,
REFERENCE_TYPE ,
REFERENCE_HEADER_ID,
REFERENCE_LINE_ID,
DEMAND_CLASS_CODE,
STATUS_FLAG)
VALUES
(REC_CUR_ORD_LINES.HEADER_ID,
REC_CUR_ORD_LINES.LINE_ID,
REC_CUR_ORD_LINES.LINE_TYPE_ID,
REC_CUR_ORD_LINES.LINE_NUMBER,
REC_CUR_ORD_LINES.ORG_ID,
REC_CUR_ORD_LINES.SEGMENT1,
REC_CUR_ORD_LINES.PRICING_QUANTITY,
REC_CUR_ORD_LINES.PRICING_QUANTITY_UOM,
REC_CUR_ORD_LINES.ORDERED_QUANTITY,
REC_CUR_ORD_LINES.ORDER_QUANTITY_UOM,
REC_CUR_ORD_LINES.SHIPPING_QUANTITY,
REC_CUR_ORD_LINES.SHIPPING_QUANTITY_UOM,
REC_CUR_ORD_LINES.SHIPMENT_NUMBER,
REC_CUR_ORD_LINES.ORDER_SOURCE_ID,
REC_CUR_ORD_LINES.ORIG_SYS_DOCUMENT_REF,
REC_CUR_ORD_LINES.ORIG_SYS_LINE_REF,
REC_CUR_ORD_LINES.ORIG_SYS_SHIPMENT_REF,
REC_CUR_ORD_LINES.INVENTORY_ITEM_ID,
REC_CUR_ORD_LINES.ORGANIZATION_ID,
REC_CUR_ORD_LINES.SALESREP_ID,
REC_CUR_ORD_LINES.SNAME,
REC_CUR_ORD_LINES.ACCOUNTING_RULE_ID,
REC_CUR_ORD_LINES.SOURCE_TYPE_CODE,
REC_CUR_ORD_LINES.SHIPMENT_PRIORITY_CODE,
REC_CUR_ORD_LINES.FREIGHT_TERMS_CODE,
REC_CUR_ORD_LINES.FOB_POINT_CODE,
REC_CUR_ORD_LINES.UNIT_SELLING_PRICE,
REC_CUR_ORD_LINES.UNIT_LIST_PRICE,
REC_CUR_ORD_LINES.TERM_ID,
REC_CUR_ORD_LINES.NAME,
REC_CUR_ORD_LINES.PRICE_LIST_ID,
REC_CUR_ORD_LINES.INVOICING_RULE_ID,
REC_CUR_ORD_LINES.TAX_VALUE,
REC_CUR_ORD_LINES.UNIT_COST,
REC_CUR_ORD_LINES.DELIVERY_LEAD_TIME,
REC_CUR_ORD_LINES.SHIP_TO_ADDRESS,
REC_CUR_ORD_LINES.BILL_TO_ADDRESS,
REC_CUR_ORD_LINES.CITY,
REC_CUR_ORD_LINES.SHIPTO,
REC_CUR_ORD_LINES.BILLTO,
REC_CUR_ORD_LINES.SOLD_FROM_ORG_ID,
REC_CUR_ORD_LINES.CUST_ACCOUNT_ID,
REC_CUR_ORD_LINES.BOOKED_FLAG,
REC_CUR_ORD_LINES.OPEN_FLAG,
REC_CUR_ORD_LINES.REQUEST_ID,
REC_CUR_ORD_LINES.REFERENCE_TYPE,
REC_CUR_ORD_LINES.REFERENCE_HEADER_ID,
REC_CUR_ORD_LINES.REFERENCE_LINE_ID,
REC_CUR_ORD_LINES.DEMAND_CLASS_CODE,
'N');
END LOOP;
COMMIT;
END LOOP;
COMMIT;
END XX_OM_STG_PROC;
PROCEDURE XX_OM_VALIDATE
IS
L_CUSTOMER_NUMBER HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
L_CUSTOMER_NAME HZ_CUST_ACCOUNTS.ACCOUNT_NAME%TYPE;
L_SALESREP_ID RA_SALESREPS_ALL.SALESREP_ID%TYPE;
L_NAME RA_SALESREPS_ALL.NAME%TYPE;
L_SNAME RA_SALESREPS_ALL.NAME%TYPE;
L_PRICE_LIST_ID SO_PRICE_LISTS_B.PRICE_LIST_ID%TYPE;
L_PARTY_NAME HZ_PARTIES.PARTY_NAME%TYPE;
L_FREIGHT_TERMS_CODE OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE%TYPE;
L_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
L_BILLTO HZ_CUST_SITE_USES_ALL.SITE_USE_ID%TYPE;
L_SHIPTO HZ_CUST_SITE_USES_ALL.SITE_USE_ID%TYPE;
L_SOLD_FROM_ORG_ID HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_ORGANIZATION_CODE MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
L_ACCOUNTING_RULE_ID RA_RULES.RULE_ID%TYPE;
L_SEGMENT1 MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000):=NULL;
CURSOR CUR_VALID_OM IS SELECT A.*,ROWID FROM XX_HEADER_STG A WHERE STATUS_FLAG='N';
CURSOR CUR_VALID_OM1(HEADER_ID NUMBER) IS SELECT B.*,ROWID FROM XX_LINE_STG B WHERE
STATUS_FLAG='N';
BEGIN
FOR REC_CUR_VALID_OM IN CUR_VALID_OM LOOP
BEGIN
SELECT ACCOUNT_NUMBER INTO L_CUSTOMER_NUMBER
FROM HZ_CUST_ACCOUNTS
WHERE ACCOUNT_NUMBER=REC_CUR_VALID_OM.CUSTOMER_NUMBER;
UPDATE XX_HEADER_STG
SET CUSTOMER_NUMBER=L_CUSTOMER_NUMBER
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'CUSTOMER NUMBER DOES NOT EXIST';
END;
BEGIN
SELECT ACCOUNT_NAME INTO L_CUSTOMER_NUMBER
FROM HZ_CUST_ACCOUNTS
WHERE ACCOUNT_NAME=REC_CUR_VALID_OM.PARTY_NAME;
UPDATE XX_HEADER_STG
SET PARTY_NAME=L_CUSTOMER_NAME
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'CUSTOMER NAME DOES NOT EXIST';
END;
BEGIN
SELECT SALESREP_ID INTO L_SALESREP_ID
FROM RA_SALESREPS_ALL
WHERE SALESREP_ID=REC_CUR_VALID_OM.SALESREP_ID
AND ORG_ID=REC_CUR_VALID_OM.ORG_ID;
UPDATE XX_HEADER_STG
SET SALESREP_ID=L_SALESREP_ID
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SALESREP ID DOES NOT EXIST';
END;
BEGIN
SELECT NAME INTO L_NAME
FROM RA_SALESREPS_ALL
WHERE NAME=REC_CUR_VALID_OM.NAME
AND ORG_ID=REC_CUR_VALID_OM.ORG_ID;
UPDATE XX_HEADER_STG
SET NAME=L_NAME
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SALES NAME DOES NOT EXIST';
END;
BEGIN
SELECT PRICE_LIST_ID INTO L_PRICE_LIST_ID
FROM SO_PRICE_LISTS_B
WHERE PRICE_LIST_ID=REC_CUR_VALID_OM.PRICE_LIST_ID;
UPDATE XX_HEADER_STG
SET PRICE_LIST_ID=L_PRICE_LIST_ID
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'PRICE LIST ID DOES NOT EXIST';
END;
BEGIN
SELECT PARTY_NAME INTO L_PARTY_NAME
FROM HZ_PARTIES
WHERE PARTY_NAME=REC_CUR_VALID_OM.PARTY_NAME;
UPDATE XX_HEADER_STG
SET PARTY_NAME=L_PARTY_NAME
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'PARTY NAME DOES NOT EXIST';
END;
BEGIN
SELECT LOOKUP_CODE INTO L_FREIGHT_TERMS_CODE
FROM FND_LOOKUP_VALUES FLT,FND_APPLICATION_TL FAT
WHERE FLT.VIEW_APPLICATION_ID=FAT.APPLICATION_ID
AND FLT.VIEW_APPLICATION_ID='660'
AND FLT.LOOKUP_CODE=REC_CUR_VALID_OM.FREIGHT_TERMS_CODE;
UPDATE XX_HEADER_STG
SET FREIGHT_TERMS_CODE=L_FREIGHT_TERMS_CODE
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'FREIGHT TERMS CODE DOES NOT EXIST';
END;
BEGIN
SELECT SITE_USE_ID INTO L_BILLTO
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID=REC_CUR_VALID_OM.BILLTO;
UPDATE XX_HEADER_STG
SET BILLTO=L_BILLTO
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SITE USE ID DOES NOT EXIST';
END;
BEGIN
SELECT SITE_USE_ID INTO L_SHIPTO
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID=REC_CUR_VALID_OM.SHIPTO;
UPDATE XX_HEADER_STG
SET SHIPTO=L_SHIPTO
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SITE USE ID DOES NOT EXIST';
END;
BEGIN
SELECT ORGANIZATION_ID INTO L_SOLD_FROM_ORG_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID=REC_CUR_VALID_OM.SOLD_FROM_ORG_ID;
UPDATE XX_HEADER_STG
SET SOLD_FROM_ORG_ID=L_SOLD_FROM_ORG_ID
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOLD FROM ORG ID DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_HEADER_STG
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
ELSE
UPDATE XX_HEADER_STG
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
END IF;
FOR REC_CUR_VALID_OM1 IN CUR_VALID_OM1(REC_CUR_VALID_OM.HEADER_ID) LOOP
L_ERROR_MESSAGE:=NULL;
L_ORGANIZATION_ID:=NULL;
L_INVENTORY_ITEM_ID:=NULL;
L_PRICE_LIST_ID:=NULL;
L_SOLD_FROM_ORG_ID:=NULL;
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = REC_CUR_VALID_OM1.ORGANIZATION_ID;
UPDATE XX_LINE_STG
SET ORGANIZATION_ID=L_ORGANIZATION_ID
WHERE ROWID= REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ORGANIZATION ID DOES NOT EXIST';
END;
BEGIN
SELECT INVENTORY_ITEM_ID
INTO L_INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID=REC_CUR_VALID_OM1.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= REC_CUR_VALID_OM1.ORGANIZATION_ID;
UPDATE XX_LINE_STG
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM DOES NOT EXIST';
END;
BEGIN
SELECT LOOKUP_CODE INTO L_FREIGHT_TERMS_CODE
FROM FND_LOOKUP_VALUES FLT,FND_APPLICATION_TL FAT
WHERE FLT.VIEW_APPLICATION_ID=FAT.APPLICATION_ID
AND FLT.VIEW_APPLICATION_ID='660'
AND FLT.LOOKUP_CODE=REC_CUR_VALID_OM1.FREIGHT_TERMS_CODE;
UPDATE XX_LINE_STG
SET FREIGHT_TERMS_CODE=L_FREIGHT_TERMS_CODE
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'FREIGHT TERMS CODE DOES NOT EXIST';
END;
BEGIN
SELECT SALESREP_ID INTO L_SALESREP_ID
FROM RA_SALESREPS_ALL
WHERE SALESREP_ID=REC_CUR_VALID_OM1.SALESREP_ID
AND ORG_ID=REC_CUR_VALID_OM.ORG_ID;
UPDATE XX_LINE_STG
SET SALESREP_ID=L_SALESREP_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SALESREP ID DOES NOT EXIST';
END;
BEGIN
SELECT NAME INTO L_SNAME
FROM RA_SALESREPS_ALL
WHERE NAME=REC_CUR_VALID_OM1.SNAME
AND ORG_ID=REC_CUR_VALID_OM.ORG_ID;
UPDATE XX_LINE_STG
SET SNAME=L_SNAME
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SALES NAME DOES NOT EXIST';
END;
BEGIN
SELECT PRICE_LIST_ID INTO L_PRICE_LIST_ID
FROM SO_PRICE_LISTS_B
WHERE PRICE_LIST_ID=REC_CUR_VALID_OM1.PRICE_LIST_ID;
UPDATE XX_LINE_STG
SET PRICE_LIST_ID=L_PRICE_LIST_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'PRICE LIST ID DOES NOT EXIST';
END;
BEGIN
SELECT SITE_USE_ID INTO L_BILLTO
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID=REC_CUR_VALID_OM1.BILLTO;
UPDATE XX_LINE_STG
SET BILLTO=L_BILLTO
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'BILL TO SITE ID DOES NOT EXIST';
END;
BEGIN
SELECT SITE_USE_ID INTO L_SHIPTO
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID=REC_CUR_VALID_OM1.SHIPTO;
UPDATE XX_LINE_STG
SET SHIPTO=L_SHIPTO
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SHIP TO SITE ID DOES NOT EXIST';
END;
BEGIN
SELECT ORGANIZATION_ID INTO L_SOLD_FROM_ORG_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID=REC_CUR_VALID_OM1.SOLD_FROM_ORG_ID;
UPDATE XX_LINE_STG
SET SOLD_FROM_ORG_ID=L_SOLD_FROM_ORG_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOLD FROM ORG ID DOES NOT EXIST';
END;
BEGIN
SELECT RULE_ID INTO L_ACCOUNTING_RULE_ID
FROM RA_RULES
WHERE RULE_ID=REC_CUR_VALID_OM1.ACCOUNTING_RULE_ID;
UPDATE XX_LINE_STG
SET ACCOUNTING_RULE_ID=L_ACCOUNTING_RULE_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ACCOUNTING RULE ID DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_LINE_STG
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
ELSE
UPDATE XX_LINE_STG
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
END IF;
END LOOP;
COMMIT;
END LOOP;
END XX_OM_VALIDATE;
PROCEDURE XX_OM_LOAD
AS
L_CREATED_BY NUMBER;
L_UPDATED_BY NUMBER;
L_CREATION_DATE DATE := SYSDATE;
L_LAST_UPDATE_DATE DATE := SYSDATE;
L_USER NUMBER;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_REC NUMBER := 0;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_VALID1 is select A.*,ROWID from XX_HEADER_STG A WHERE A.STATUS_FLAG='P';
CURSOR CUR_VALID2 (HEADER_ID NUMBER) IS select B.*,ROWID from XX_LINE_STG B
WHERE B.HEADER_ID=HEADER_ID;
BEGIN
SELECT USER_ID
INTO L_USER
FROM FND_USER
WHERE UPPER (USER_NAME) = 'OPERATIONS';
DELETE OE_HEADERS_IFACE_ALL;
DELETE OE_LINES_IFACE_ALL;
COMMIT;
FOR REC_CUR_VALID1 IN CUR_VALID1 LOOP
L_ERROR_MESSAGE:=NULL;
INSERT INTO OE_HEADERS_IFACE_ALL
(
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORG_ID,
ORDER_TYPE_ID,
HEADER_ID,
PRICE_LIST_ID,
CONVERSION_RATE,
CONVERSION_TYPE_CODE,
TAX_EXEMPT_REASON_CODE,
SALESREP_ID,
INVOICING_RULE_ID,
PAYMENT_TERM_ID,
SHIPMENT_PRIORITY_CODE,
SHIPPING_METHOD_CODE,
FREIGHT_TERMS_CODE,
FOB_POINT_CODE,
SOLD_FROM_ORG_ID,
SOLD_TO_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
DELIVER_TO_ORG_ID,
SOLD_TO_CONTACT_ID,
CUSTOMER_NAME,
SALES_CHANNEL_CODE,
BOOKED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OPERATION_CODE
)
VALUES
( '1046',
REC_CUR_VALID1.ORIG_SYS_DOCUMENT_REF,
REC_CUR_VALID1.ORG_ID,
REC_CUR_VALID1.ORDER_TYPE_ID,
OE_ORDER_HEADERS_S.NEXTVAL,
REC_CUR_VALID1.PRICE_LIST_ID,
REC_CUR_VALID1.CONVERSION_RATE,
REC_CUR_VALID1.CONVERSION_TYPE_CODE,
REC_CUR_VALID1.TAX_EXEMPT_REASON_CODE,
REC_CUR_VALID1.SALESREP_ID,
REC_CUR_VALID1.INVOICING_RULE_ID,
REC_CUR_VALID1.PAYMENT_TERM_ID,
REC_CUR_VALID1.SHIPMENT_PRIORITY_CODE,
REC_CUR_VALID1.SHIPPING_METHOD_CODE,
REC_CUR_VALID1.FREIGHT_TERMS_CODE,
REC_CUR_VALID1.FOB_POINT_CODE,
REC_CUR_VALID1.SOLD_FROM_ORG_ID,
REC_CUR_VALID1.SOLD_TO_ORG_ID,
REC_CUR_VALID1.SHIPTO,
REC_CUR_VALID1.BILLTO,
REC_CUR_VALID1.DELIVER_TO_ORG_ID,
REC_CUR_VALID1.SOLD_TO_CONTACT_ID,
REC_CUR_VALID1.PARTY_NAME,
REC_CUR_VALID1.SALES_CHANNEL_CODE,
REC_CUR_VALID1.BOOKED_FLAG,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
'INSERT'
);
FOR REC_CUR_VALID2 IN CUR_VALID2( REC_CUR_VALID1.HEADER_ID) LOOP
INSERT INTO OE_LINES_IFACE_ALL
(ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
LINE_ID,
ORG_ID,
INVENTORY_ITEM,
SOURCE_TYPE_CODE,
REQUEST_DATE,
DELIVERY_LEAD_TIME,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
SHIPPING_QUANTITY,
SHIPPING_QUANTITY_UOM,
PRICING_QUANTITY,
PRICING_QUANTITY_UOM,
SOLD_FROM_ORG_ID,
SOLD_TO_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
PRICE_LIST_ID,
REFERENCE_TYPE,
REFERENCE_HEADER_ID,
REFERENCE_LINE_ID,
UNIT_SELLING_PRICE,
TAX_VALUE,
INVOICING_RULE_ID,
PAYMENT_TERM_ID,
SHIPMENT_PRIORITY_CODE,
FREIGHT_TERMS_CODE,
FOB_POINT_CODE,
SALESREP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DEMAND_CLASS_CODE)
VALUES
('1046',
REC_CUR_VALID2.ORIG_SYS_DOCUMENT_REF,
REC_CUR_VALID2.ORIG_SYS_LINE_REF,
OE_ORDER_LINES_S.NEXTVAL,
REC_CUR_VALID2.ORG_ID,
'AS54999',
REC_CUR_VALID2.SOURCE_TYPE_CODE,
SYSDATE,
REC_CUR_VALID2.DELIVERY_LEAD_TIME,
REC_CUR_VALID2.ORDERED_QUANTITY,
REC_CUR_VALID2.ORDER_QUANTITY_UOM,
REC_CUR_VALID2.SHIPPING_QUANTITY,
REC_CUR_VALID2.SHIPPING_QUANTITY_UOM,
REC_CUR_VALID2.PRICING_QUANTITY,
REC_CUR_VALID2.PRICING_QUANTITY_UOM,
REC_CUR_VALID2.SOLD_FROM_ORG_ID,
REC_CUR_VALID2.SOLD_TO_ORG_ID,
REC_CUR_VALID2.SHIPTO,
REC_CUR_VALID2.BILLTO,
REC_CUR_VALID2.PRICE_LIST_ID,
REC_CUR_VALID2.REFERENCE_TYPE,
REC_CUR_VALID2.REFERENCE_HEADER_ID,
REC_CUR_VALID2.REFERENCE_LINE_ID,
REC_CUR_VALID2.UNIT_SELLING_PRICE,
REC_CUR_VALID2.TAX_VALUE,
REC_CUR_VALID2.INVOICING_RULE_ID,
REC_CUR_VALID2.PAYMENT_TERM_ID,
REC_CUR_VALID2.SHIPMENT_PRIORITY_CODE,
REC_CUR_VALID2.FREIGHT_TERMS_CODE,
REC_CUR_VALID2.FOB_POINT_CODE,
REC_CUR_VALID2.SALESREP_ID,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
REC_CUR_VALID2.DEMAND_CLASS_CODE);
END LOOP;
COMMIT;
END LOOP;
COMMIT;
END XX_OM_LOAD;
END XX_OM_ORDER_PKG;

=======================================================================

RUN THE IMPORT PROGRAM:
=======================

1)ORDER IMPORT STATISTICS
2)ORDER IMPORT

========================================================================