PO INTERFACES

Purchase Order

The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.

Import Standard Purchase Orders

Pre-requisites:

Suppliers, sites and contacts

Buyers

Line Types

Items

PO

Charge account setup

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

PO_INTERFACE_ERRORS (Fallouts)

Interface Program:

Import Standard Purchase Orders.

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

PO_LINE_LOCATIONS_ALL

Validations:

Header:

Check if OU name is valid

Check if Supplier is valid

Check if Supplier site is valid

Check if buyer is valid

Check if Payment term is valid

Check if Bill to and ship to are valid

Check if FOB, freight terms are valid

Lines:

Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid

General:

Check for duplicate records in interface tables

Check if the record already exists in base tables.

Some important columns that need to be populated in the interface tables:

PO_HEADERS_INTERFACE:

INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL), BATCH_ID, ORG_ID, INTERFACE_SOURCE_CODE, ACTION (‘ORIGINAL’,’UPDATE’,’REPLACE’), GROUP_CODE, DOCUMENT_TYPE_CODE, PO_HEADER_ID (NULL), RELEASE_ID, RELEASE_NUM, CURRENCY_CODE, RATE, AGENT_NAME, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION, BILL_TO_LOCATION, , PAYMENT_TERMS

PO_LINES_INTERFACE:

INTERFACE_LINE_ID, INTERFACE_HEADER_ID, LINE_NUM, SHIPMENT_NUM, ITEM, REQUISITION_LINE_ID, UOM, UNIT_PRICE, FREIGHT_TERMS, FOB

PO_DISTRIBUTIONS_INTERFACE:

INTERFACE_LINE_ID, INTERFACE_HEADER_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, QUANTITY_ORDERED, QTY_DELIVERED, QTY_BILLED, QTY_CANCELLED, DELIVER_TO_LOCATION_ID, DELIVER_TO_PERSON_ID, SET_OF_BOOKS, CHARGE_ACCT, AMOUNT_BILLED.

Staging Tables

CREATE TABLE xx_po_header_stg(

interface_header_id NUMBER

,batch_id NUMBER

,org_id NUMBER

,action VARCHAR2(25)

,document_type_code VARCHAR2(25)

,currency_code VARCHAR2(15)

,AGENT_ID NUMBER

,full_name VARCHAR2(240)

,vendor_name VARCHAR2(240)

,vendor_site_code VARCHAR2(15)

,ship_to_location VARCHAR2(60)

,bill_to_location VARCHAR2(60)

,approval_status VARCHAR2(25)

,freight_carrier VARCHAR2(25)

,fob VARCHAR2(25)

,freight_terms VARCHAR2(25))

CREATE TABLE xx_po_line_stg

(

interface_header_id NUMBER

,interface_line_id NUMBER

,line_num NUMBER

,shipment_num NUMBER

,line_type VARCHAR2(25)

,item VARCHAR2(1000)

,item_description VARCHAR2(240)

,item_id NUMBER

,uom_code VARCHAR2(3)

,quantity NUMBER

,unit_price NUMBER

,ship_to_organization_code VARCHAR2(3)

,ship_to_location VARCHAR2(60)

,list_price_per_unit NUMBER)

CREATE TABLE xx_po_distribution_stg

(interface_header_id NUMBER,

interface_line_id NUMBER,

interface_distribution_id NUMBER,

org_id NUMBER,

quantity_ordered NUMBER,

destination_organization_id NUMBER,

set_of_books_id NUMBER,

charge_account_id VARCHAR2(2000),

distribution_num NUMBER);

Creation Of The Package.

CREATE OR REPLACE PACKAGE XXAK_PO_IMP_PKG IS

PROCEDURE XXAK_PO_IMP_PRC(Errbuf OUT VARCHAR2, Retcode OUT number) ;

END;

/

Creation Of The Procedure.

CREATE OR REPLACE PACKAGE BODY XXAK_PO_IMP_PKG

IS

PROCEDURE XXAK_PO_IMP_PRC (errbuf OUT VARCHAR2, retcode OUT number)

IS

CURSOR cur_head

IS

SELECT *

FROM xx_po_header_stg;

CURSOR cur_line (p_interface_header_id NUMBER)

IS

SELECT *

FROM xx_po_line_stg where interface_header_id=p_interface_header_id;

CURSOR cur_dist (p_interface_line_id NUMBER)

IS

SELECT *

FROM xx_po_distribution_stg where interface_line_id=p_interface_line_id;

lv_vendor_id NUMBER (10);

lv_agent_id NUMBER(10);

lv_itemid NUMBER;

lv_site_code VARCHAR2 (100);

lv_lookup_code VARCHAR2(25);

lv_curr_code VARCHAR2 (10);

lv_org_id NUMBER (6);

BEGIN

BEGIN

SELECT organization_id

INTO lv_org_id

FROM hr_operating_units

WHERE NAME LIKE 'Vision Operations';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid org_id');

END;

BEGIN

FOR var1 IN cur_head

LOOP

BEGIN

SELECT vendor_id

INTO lv_vendor_id

FROM po_vendors

WHERE vendor_name=var1.vendor_name;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid vendor_id');

END;

BEGIN

SELECT vendor_site_code

INTO lv_site_code

FROM po_vendor_sites_all

WHERE vendor_site_code = var1.vendor_site_code;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'invalid vendor_site_code');

END;

BEGIN

SELECT currency_code

INTO lv_curr_code

FROM fnd_currencies

WHERE currency_code = var1.currency_code;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid currency_code');

END;

BEGIN

SELECT DOCUMENT_TYPE_CODE

INTO lv_lookup_code

FROM PO_DOCUMENT_TYPES

WHERE DOCUMENT_TYPE_CODE = var1.DOCUMENT_TYPE_CODE;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid typecode');

END;

BEGIN

SELECT person_id

INTO lv_agent_id

FROM PER_ALL_PEOPLE_F

WHERE full_name= var1.full_name;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid Buyer');

END;

BEGIN

INSERT INTO po_headers_interface

(interface_header_id

, batch_id

, org_id

,action

, document_type_code

, currency_code

,agent_id

, agent_name

, vendor_name

, vendor_site_code

, ship_to_location

, bill_to_location

,approval_status

, freight_terms

,fob

,freight_carrier

,created_by

, creation_date

, last_update_date

, last_updated_by

)

VALUES

(PO_HEADERS_INTERFACE_S.NEXTVAL

, var1.batch_id

, lv_org_id

,var1.action

, var1.document_type_code

, var1.currency_code

, lv_agent_id

,var1.full_name

,var1.vendor_name

, var1.vendor_site_code

, var1.ship_to_location

, var1.bill_to_location

, var1.approval_status

, var1.freight_terms

,var1.fob

,var1.freight_carrier

, -1

, SYSDATE

, SYSDATE

, -1

);

EXCEPTION WHEN OTHERS THEN

FND_FILE.put_line(FND_FILE.output,'insertion successful into headers int');

END;

FOR var2 IN cur_line (var1.interface_header_id)

LOOP

BEGIN

SELECT INVENTORY_ITEM_ID

INTO lv_itemid

FROM mtl_system_items_b

WHERE segment1 = var2.item

AND organization_id =lv_org_id;

EXCEPTION

WHEN OTHERS

THEN

-- lv_item := NULL;

fnd_file.put_line (fnd_file.LOG, 'invalid item');

END;

BEGIN

INSERT INTO po_lines_interface

(interface_line_id

, interface_header_id

, line_num

, shipment_num

, line_type

,item

, item_description

, item_id

, uom_code

, quantity

, unit_price

,ship_to_organization_code

, ship_to_location

,NEED_BY_DATE

-- ,PROMISED_DATE

, list_price_per_unit

, created_by

, creation_date

, last_update_date

, last_updated_by

)

VALUES

(PO_LINES_INTERFACE_S.NEXTVAL

,PO_HEADERS_INTERFACE_S.CURRVAL

, var2.line_num

, var2.shipment_num

, var2.line_type

, var2.item

, var2.item_description

, var2.item_id

,var2.uom_code

, var2.quantity

, var2.unit_price

,var2.ship_to_organization_code

,var2.ship_to_location

,SYSDATE

-- ,SYSDATE

, var2.list_price_per_unit

, -1

,SYSDATE

, SYSDATE

, -1

);

EXCEPTION WHEN OTHERS THEN

FND_FILE.put_line(FND_FILE.output,'insertion successful into lines int');

END;

for var3 in cur_dist(var2.interface_line_id)

LOOP

BEGIN

INSERT INTO po_distributions_interface (

interface_header_id ,

interface_line_id ,

interface_distribution_id ,

org_id ,

quantity_ordered ,

destination_organization_id ,

set_of_books_id ,

charge_account_id ,

distribution_num

, created_by

, creation_date

, last_update_date

, last_updated_by

)

VALUES

(

PO_HEADERS_INTERFACE_S.CURRVAL,

PO_LINES_INTERFACE_S.CURRVAL,

PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL ,

lv_org_id ,

var3.quantity_ordered ,

var3.destination_organization_id ,

var3.set_of_books_id ,

var3.charge_account_id ,

var3.distribution_num

, -1

,SYSDATE

, SYSDATE

, -1 );

EXCEPTION WHEN OTHERS THEN

FND_FILE.put_line(FND_FILE.output,'insertion successful into dist int');

END;

END LOOP;

END LOOP;

END LOOP;

END;

COMMIT;

END XXAK_PO_IMP_PRC;

END XXAK_PO_IMP_PKG ;

/