po interface tables

po interface tables

We are going to discuss about po interface tables. Oracle has provide the po interface to import the purchase order's from external source into the oracle application. Using po interface tables we can mass import the purchase orders in oracle system. PO interface tables helps to create the po integration between the external application with oracle application. We need to provide the data in the form of PO interface tables to import in the oracle application. We first prepare the data in the po interface tables format and then bulk insert that data in to the Po interface. Then we need to run the PO import program to transferred the data from interface tables in to the PO base tables. Here below is the details about po interface tables.

po interface tables

4 Important po interface tables

These below are important PO interface tables which helps to import the Purchase order data from other source or applications into the oracle application.

1.po_headers_interface

2.po_lines_interface

3.po_distributions_interface

4.po_interface_errors

PO_HEADERS_INTERFACE :- 

This is Header Table in PO interface tables. In this Table , We do enter the Purchase Order Header Details. Important Columns in this table is interface_header_id,document_type_code, currency_code,agent_id, vendor_name,vendor_site_code,ship_to_location_id, bill_to_location_id. 

PO_LINES_INTERFACE :- 

This is the line table in PO interface tables. In this table , We do enter the Purchase order lines details. We do provide the details about PO item for which we are creating the purchase order. Important Columns is interface_header_id,interface_line_id,shipment_num, line_type,item, item_description, uom_code,quantity, unit_price,organization_id,need_by_date, ship_to_organization_id,ship_to_location.

PO_DISTRIBUTIONS_INTERFACE :- 

This is the Accounting Table for the PO interface tables. We enter the Accounting and shipment details for each PO line in this PO interface tables. Important Columns is interface_header_id,interface_line_id,interface_distribution_id,distribution_num,quantity_ordered.

PO_INTERFACE_ERRORS:- 

This tables helps to store the rejected records which failed during the PO import process in oracle apps. Using this table , we can able to view the error messages for each rejected purchase order records in PO interface tables.

Interface Script using po interface tables

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;

po interface tables

PO Interface Tables