Script to import standard purchase order

CURSOR PHEADER IS SELECT * FROM XX_PO_IMP_STG_TABLE;

CURSOR PLINE (P_PO_NUMBER VARCHAR2)

IS

SELECT *

FROM XX_PO_IMP_STG_TABLE

WHERE PO_NUMBER = P_PO_NUMBER;

L_AGENT_ID              NUMBER;

l_vendor_id             NUMBER;

l_vendor_Site_id        NUMBER;

l_ship_to_location_id   NUMBER;

l_bill_to_location_id   NUMBER;

LV_VENDOR_ID            NUMBER;

LINE_LOCATION_ID        NUMBER;

LV_DESCRIPTION          VARCHAR2 (500);

LV_UOM_CODE             VARCHAR2 (10);

L_TERM_ID               NUMBER;

lv_lookup_code          VARCHAR2 (50);

FOB_lookup_code         VARCHAR2 (50);

BEGIN

FOR H IN PHEADER

LOOP

SELECT PERSON_ID

INTO L_AGENT_ID

FROM PER_ALL_PEOPLE_F

WHERE     FULL_NAME = H.BUYER_NAME

AND EFFECTIVE_END_DATE IN

(SELECT MAX (EFFECTIVE_END_DATE) FROM PER_ALL_PEOPLE_F)

AND EMPLOYEE_NUMBER LIKE ‘EMP%’;

l_ship_to_location_id := 141;

l_bill_to_location_id := 141;

SELECT TERM_ID

INTO L_TERM_ID

FROM AP_TERMS

WHERE NAME = H.PAYMENT_TERMS;

SELECT VENDOR_ID

INTO l_vendor_id

FROM ap_suppliers

WHERE VENDOR_NAME = H.VENDOR_NAME;

SELECT VENDOR_SITE_ID

INTO l_vendor_Site_id

FROM ap_supplier_sites_all

WHERE VENDOR_ID = l_vendor_id AND org_id = 1;

IF H.PO_DESCRIPTION IS NULL

THEN

LV_DESCRIPTION := ‘PO MIGRAION’;

ELSE

LV_DESCRIPTION := H.PO_DESCRIPTION;

END IF;

INSERT INTO PO_HEADERS_INTERFACE (INTERFACE_HEADER_ID,

DOCUMENT_NUM,

PROCESS_CODE,

ACTION,

ORG_ID,

DOCUMENT_TYPE_CODE,

CURRENCY_CODE,

AGENT_ID,

VENDOR_ID,

VENDOR_SITE_ID,

SHIP_TO_LOCATION_ID,

BILL_TO_LOCATION_ID,

–ATTRIBUTE2,

CREATION_DATE,

COMMENTS,

ATTRIBUTE_CATEGORY,

PAYMENT_TERMS,

TERMS_ID,

FOB,

FREIGHT_TERMS)

VALUES (po_headers_interface_s.NEXTVAL,  — INTERFACE_HEADER_ID,

H.PO_NUMBER,                                    –‘1491’,

‘PENDING’,

‘ORIGINAL’,                                   — ACTION,

H.ORGANIZATION_ID,                            — ORG_ID,

‘STANDARD’,                       — DOCUMENT_TYPE_CODE,

H.CURRENCY_CODE,

L_AGENT_ID,

l_vendor_id,

l_vendor_Site_id,

l_ship_to_location_id,

l_bill_to_location_id,

–‘Regular’,

H.PO_DATE,                             — CREATION_DATE,

SUBSTR (LV_DESCRIPTION, 0, 240),

‘STANDARD’,

H.PAYMENT_TERMS,

L_TERM_ID,

H.FOB,

H.FREIGHT_TERMS                –H.FOB, –lv_lookup_code,

–H.FREIGHT_TERM,

);

COMMIT;

FOR PL IN PLINE (H.PO_NUMBER)

LOOP

SELECT LOCATION_ID

INTO LINE_LOCATION_ID

FROM HR_LOCATIONS_ALL

WHERE LOCATION_CODE = PL.LOCATION_CODE;

BEGIN

SELECT DESCRIPTION

INTO LV_DESCRIPTION

FROM MTL_SYSTEM_ITEMS_B

WHERE     SEGMENT1 = PL.NEW_EBS_CODE

AND ORGANIZATION_ID = PL.ORGANIZATION_ID;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

lv_description := ‘OPEN PO MIGRATION’;

END;

INSERT INTO PO_LINES_INTERFACE (INTERFACE_LINE_ID,

INTERFACE_HEADER_ID,

ACTION,

LINE_NUM,

LINE_TYPE,

ITEM,

ITEM_ID,

UOM_CODE,

QUANTITY,

UNIT_PRICE,

SHIP_TO_ORGANIZATION_ID, ——————–

SHIP_TO_LOCATION_ID,

NEED_BY_DATE,

PROMISED_DATE,

CREATION_DATE,

LINE_LOC_POPULATED_FLAG, –ITEM_REVISION

ITEM_DESCRIPTION –PREFERRED_GRADE

)

VALUES (

po_lines_interface_s.NEXTVAL,  — INTERFACE_LINE_ID,

po_headers_interface_s.CURRVAL, — INTERFACE_HEADER_ID,

‘ADD’,                                    — ACTION,

PL.PO_LINE_NUMBER,                      — LINE_NUM,

‘Goods’,

PL.NEW_EBS_CODE,

(SELECT INVENTORY_ITEM_ID

FROM MTL_SYSTEM_ITEMS_B

WHERE     SEGMENT1 = PL.NEW_EBS_CODE

AND ORGANIZATION_ID = PL.ORGANIZATION_ID),

PL.UOM_CODE,

PL.QUANTITY,

PL.PRICE,

24,–PL.ORGANIZATION_ID,

LINE_LOCATION_ID,

’30-AUG-2024′, –to_date(PL.NEED_BY_DATE, ‘DD-MON-YYYY HH24:MI:SS’),              — NEED_BY_DATE,

’30-AUG-2024′, –to_date(PL.NEED_BY_DATE, ‘DD-MON-YYYY HH24:MI:SS’),

SYSDATE,                           — CREATION_DATE,

‘Y’,                     — LINE_LOC_POPULATED_FLAG,

LV_DESCRIPTION);

COMMIT;

INSERT INTO PO_LINE_LOCATIONS_INTERFACE (

INTERFACE_LINE_LOCATION_ID,

INTERFACE_HEADER_ID,

INTERFACE_LINE_ID,

SHIPMENT_TYPE,

SHIPMENT_NUM,

SHIP_TO_ORGANIZATION_ID,

NEED_BY_DATE,

PROMISED_DATE,

QUANTITY,

CREATION_DATE)

VALUES (po_line_locations_interface_s.NEXTVAL, — INTERFACE_LINE_LOCATION_ID,

po_headers_interface_s.CURRVAL, — INTERFACE_HEADER_ID,

po_lines_interface_s.CURRVAL,  — INTERFACE_LINE_ID,

‘STANDARD’,                        — SHIPMENT_TYPE,

PL.PO_LINE_NUMBER,                  — SHIPMENT_NUM,

’24’,–PL.ORGANIZATION_ID, — SHIP_TO_ORGANIZATION_ID,— SHIP_TO_LOCATION_ID,

’30-AUG-2024′, –to_date(PL.NEED_BY_DATE, ‘DD-MON-YYYY HH24:MI:SS’),

’30-AUG-2024′,                      — NEED_BY_DATE,

PL.QUANTITY,                            — QUANTITY,

SYSDATE);

COMMIT;

END LOOP;

DBMS_OUTPUT.PUT_LINE (H.PO_NUMBER);

COMMIT;

END LOOP;

END;