Open Purchase Order uploading from legacy system to Oracle Apps

In order to upload open sales order following steps need to be performed:

1. Create a staging table as below.

2. Populate the staging table from user provided data in excel sheet.

3. Update the null field values based on the data provided as shown below.

4. Populate the data from the staging table into the Interface tables "PO_HEADERS_INTERFACE", "PO_LINES_INTERFACE" and "PO_DISTRIBUTIONS_INTERFACE" as shown below.

5. Run the "Import Standard Purchase Orders" request from the front end Apps Screen.

/********* CREATE SUPPLIER STAGING TABLE **********/

CREATE TABLE XX_OPEN_PO
(
SL_NO NUMBER,
VEND_CODE VARCHAR2(20 BYTE),
SUPPLIER VARCHAR2(50 BYTE),
SUPPLIER_SITE VARCHAR2(50 BYTE),
DESCRIPTION VARCHAR2(100 BYTE),
SHIP_TO_LOC VARCHAR2(50 BYTE),
BILL_TO_LOC VARCHAR2(50 BYTE),
ICODE VARCHAR2(50 BYTE),
ITEM VARCHAR2(50 BYTE),
QUANTITY NUMBER,
UOM VARCHAR2(10 BYTE),
UOM_CODE VARCHAR2(4 BYTE),
PRICE NUMBER,
CURRENCY VARCHAR2(4 BYTE),
NEED_BY_DATE DATE,
ORGANIZATION_NAME VARCHAR2(50 BYTE),
VERIFY_FLAG VARCHAR2(10 BYTE),
HEADER_ID NUMBER(10),
LINE_TYPE VARCHAR2(100 BYTE),
LINE_NUMBER NUMBER(12),
CHARGE_ACCOUNT VARCHAR2(50 BYTE),
MATCH_APPROVAL VARCHAR2(50 BYTE),
TAX VARCHAR2(50 BYTE),
CCID NUMBER,
VENDOR_ID NUMBER,
SHIP_TOLOC_ID NUMBER,
SHIP_TO_ORGID NUMBER
)


/******** GET AND UPDATE THE VENDOR_ID, SUPPLIER, AND SUPPLIER_SITE FIELDS STAGING TABLE *******/

update XX_open_po m set vendor_id = (select vendor_id from po_vendors
where upper(trim(vendor_name)) = upper(trim(m.supplier)))

update XX_open_po m set supplier = (select vendor_name from po_vendors
where upper(trim(vendor_name)) = upper(trim(m.supplier))) where m.VENDOR_ID is not null

update XX_open_po m set supplier_site =
(select a.vendor_site_code from
(SELECT MIN(vendor_site_id), vendor_site_code, vendor_id
FROM po_vendor_sites_all
GROUP BY vendor_site_id, vendor_site_code, vendor_id)a
where a.vendor_id = m.VENDOR_ID) where m.SUPPLIER_SITE is null


/***** INSERTING DATA INTO PO_HEADERS_INTERFACE *****/

TRUNCATE TABLE po.po_headers_interface

INSERT INTO po.po_headers_interface
(interface_header_id,
comments,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_id,
vendor_name,
vendor_site_code,
ship_to_location_id,
bill_to_location_id,
effective_date,
reference_num,
last_update_date
)
SELECT apps.po_headers_interface_s.NEXTVAL,
DESCRIPTION,
'PENDING',
'ORIGINAL',
81,-- l_org_id,
'STANDARD',
'INR',
84,
VEND_ID,
SUPPLIER,
SITE,
SHIPLOC_ID,
787,
TRUNC(TO_DATE('31-MAR-2011')),
'PO'||apps.po_headers_interface_s.NEXTVAL,
SYSDATE
FROM
(SELECT DISTINCT M.DESCRIPTION DESCRIPTION,
M.VENDOR_ID VEND_ID ,
M.supplier SUPPLIER,
M.SUPPLIER_SITE SITE,
M.SHIP_TOLOC_ID SHIPLOC_ID
FROM
XX_OPEN_PO M where verify_flag IS NULL)


/****** INSERTING DATA INTO PO_LINES_INTERFACE ******/

TRUNCATE TABLE po.po_lines_interface

INSERT INTO po.po_lines_interface
(interface_header_id,
interface_line_id,
line_num,
shipment_num,
line_type,
item,
item_description,
uom_code,
quantity,
unit_price,
organization_id,
need_by_date,
ship_to_organization_id,
ship_to_location
)
SELECT
P.INTERFACE_HEADER_ID,
APPS.PO_LINES_INTERFACE_S.NEXTVAL,
M.LINE_NUMBER,
M.LINE_NUMBER,
'Goods',
M.ICODE,
M.ITEM,
M.UOM_CODE,
M.QUANTITY,
M.PRICE,
M.SHIP_TO_ORGID,
TRUNC(TO_DATE('31-MAR-2011')),
M.SHIP_TO_ORGID,
M.SHIP_TO_LOC
FROM
XX_OPEN_PO M,po_headers_interface P
WHERE P.COMMENTS = M.DESCRIPTION

/******* INSERTING DATA INTO PO_DISTRIBUTIONS_INTERFACE ******/

TRUNCATE TABLE po.po_DISTRIBUTIONS_interface

INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered
)
-- CHARGE_ACCOUNT_ID)
SELECT L.INTERFACE_HEADER_ID,
L.INTERFACE_LINE_ID,
po.po_distributions_interface_s.NEXTVAL,
L.SHIPMENT_NUM,
M.QUANTITY
FROM
XX_OPEN_PO M,po_headers_interface P, PO_LINES_INTERFACE L
WHERE
P.COMMENTS = M.DESCRIPTION AND P.INTERFACE_HEADER_ID = L.INTERFACE_HEADER_ID
AND (M.LINE_NUMBER = L.LINE_NUM AND M.LINE_NUMBER = L.SHIPMENT_NUM)


/******* TRUNCATE TABLE PO_INTERFACE_ERRORS ********/

truncate table po.po_interface_errors

/**** AFTER COMPLETION OF THE UPLOADING PROCESS UPDATE VERIFY_FLAG OF STAGING TABLE *****/

UPDATE XX_OPEN_PO M SET VERIFY_FLAG = 'N' WHERE
M.DESCRIPTION IN (SELECT COMMENTS FROM po_headers_interface h
where h.PROCESS_CODE = 'REJECTED')