PO conversion in oracle apps

PO conversion in oracle apps

In this post , I will share the code to do the PO conversion in the Oracle apps. There are three tables uses to do PO conversion in the Oracle apps. Oracle has provided these below three interface tables in the PO conversion.

 

1.po_headers_interface

2.po_lines_interface

3.po_distributions_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,
 INFORMATION,
 'PENDING',
 'ORIGINAL',
 85,
 'STANDARD',
 'INR',
 81,
 SUPPLIER_ID,
 SUPPLIER,
 VENDOR_SITE,
 SHIP_ID,
 BILL_ID
 TRUNC(SYSDATE),
 'PO'||apps.po_headers_interface_s.NEXTVAL,
 SYSDATE
 FROM
 XX_PO_HDR_STAGING;

/****** 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
 apps.po_headers_interface_s.CURRVAL,
 APPS.PO_LINES_INTERFACE_S.NEXTVAL,
 LINE_NUMBER,
SHIP_NUMBER,
 'Goods',
 ICODE,
 ITEM,
 UOM,
 QUANTITY,
 PRICE,
 SHIP_TO_ORGID,
 TRUNC(SYSDATE),
 M.SHIP_ORGID,
 M.SHIP_TO_LOC
 FROM
 XX_PO_LINE_STAGING;

/******* INSERTING DATA INTO 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 apps.po_headers_interface_s.CURRVAL
 APPS.PO_LINES_INTERFACE_S.CURRVAL,
 po.po_distributions_interface_s.NEXTVAL,
 SHIPMENT_NUM,
 QUANTITY
 FROM
 XX_PO_SHIP_STAGING;
To check the rejected records in the PO conversion we can see in this below table.

 po.po_interface_errors