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;
/