Headers
| INTERFACE_HEADER_ID | BATCH_ID | ACTION | ORG_ID | DOCUMENT_TYPE_CODE | CURRENCY_CODE | AGENT_NAME | VENDOR_NAME | VENDOR_SITE_CODE | SHIP_TO_LOCATION | BILL_TO_LOCATION | APPROVAL_STATUS | FREIGHT_CARRIER | FOB | FREIGHT_TERMS | |
| 1 | 5 | ORIGINAL | 204 | STANDARD | USD | Stock, Ms. Pat | 3M Health Care | CORP HQ | M1- Seattle Mfg | V1- New York City | APPROVED | UPS | Origin | Due | |
| 2 | 5 | ORIGINAL | 204 | STANDARD | USD | Stock, Ms. Pat | 3M Health Care | CORP HQ | M1- Seattle Mfg | V1- New York City | APPROVED | UPS | Origin | Paid | |
Lines
| INTERFACE_HEADER_ID | INTERFACE_LINE_ID | LINE_NUM | SHIPMENT_NUM | LINE_TYPE | ITEM | ITEM_DESCRIPTION | ITEM_ID | UOM_CODE | QUANTITY | UNIT_PRICE | SHIP_TO_ORGANIZATION_CODE | SHIP_TO_LOCATION | LIST_PRICE_PER_UNIT | PROMISED_DATE | NEED_BY_DATE | ACTION |
| 1 | 1 | 1 | 1 | Goods | AS54999 | Sentinel Standard Desktop - Rugged | 2,155 | Ea | 1,000 | 10 | M1 | M1- Seattle Mfg | 100 | 08/27/2017 13:52:49 | 09/16/2017 13:52:49 | |
| 1 | 2 | 2 | 1 | Goods | AS54999 | Sentinel Standard Desktop - Rugged | 2,155 | Ea | 500 | 10 | M1 | M1- Seattle Mfg | 100 | 08/27/2017 13:54:08 | 09/16/2017 13:54:08 | |
| 2 | 3 | 2 | 1 | Goods | AS54999 | Sentinel Standard Desktop - Rugged | 2,155 | Ea | 200 | 10 | M1 | M1- Seattle Mfg | 100 | 08/27/2017 13:56:31 | 09/16/2017 13:56:31 | |
| 2 | 4 | 1 | 1 | Goods | AS54999 | Sentinel Standard Desktop - Rugged | 2,155 | Ea | 500 | 10 | M1 | M1- Seattle Mfg | 100 | 08/27/2017 13:59:48 | 09/16/2017 13:59:48 |
| INTERFACE_HEADER_ID | INTERFACE_LINE_ID | INTERFACE_DISTRIBUTION_ID | SET_OF_BOOKS_ID | DESTINATION_ORGANIZATION_ID | ORG_ID | ORDERED_QUANTITY | CHARGE_ACCOUNT_ID |
| 1 | 1 | 1 | 1 | 207 | 204 | 1,000 | 13,401 |
| 1 | 2 | 2 | 1 | 207 | 204 | 500 | 13,401 |
| 2 | 3 | 3 | 1 | 207 | 204 | 200 | 13,401 |
| 2 | 4 | 4 | 1 | 207 | 204 | 500 | 13,401 |
CREATE OR REPLACE PROCEDURE PO_INTERFACE(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2) AS
CURSOR C1 IS SELECT * FROM XX_PO_HEADERS;
CURSOR C2(P_INTERFACE_HEADER_ID NUMBER) IS SELECT * FROM XX_PO_LINES WHERE INTERFACE_HEADER_ID=P_INTERFACE_HEADER_ID;
CURSOR C3(P_INTERFACE_LINE_ID NUMBER) IS SELECT * FROM XX_PO_DISTRIBUTIONS WHERE INTERFACE_LINE_ID=P_INTERFACE_LINE_ID;
L_VENDOR_ID VARCHAR2(100);
L_ITEM VARCHAR2(150);
L_FLAG VARCHAR2(4) DEFAULT 'A';
L_MSG VARCHAR2(200);
L_SITE_CODE VARCHAR2(100);
L_CURR_CODE VARCHAR2(10);
L_ORG_ID NUMBER(6);
L_LOCATION_CODE VARCHAR2(100);
RECORD_COUNT NUMBER(20):=0;
V_SET_OF_BOOKS_ID NUMBER(10);
L_LINE_TYPE VARCHAR2(100);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
DELETE FROM PO_DISTRIBUTIONS_INTERFACE;
COMMIT;
FOR I IN C1 LOOP
RECORD_COUNT:=RECORD_COUNT+1;
BEGIN
-- VENDOR_NAME VALIDATION
SELECT VENDOR_ID
INTO L_VENDOR_ID
FROM APPS.PO_VENDORS
WHERE VENDOR_NAME = I.VENDOR_NAME;
EXCEPTION
WHEN OTHERS THEN
L_FLAG := 'E';
L_MSG := 'PLEASE CHECK THE VENDOR_ID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
--VENDOR SITE CODE VALIDATION
--================================
BEGIN
SELECT DISTINCT(VENDOR_SITE_CODE)
INTO L_SITE_CODE
FROM PO_VENDOR_SITES_ALL
WHERE VENDOR_SITE_CODE =I.VENDOR_SITE_CODE;
EXCEPTION
WHEN OTHERS THEN
L_FLAG := 'E';
L_MSG := 'PLEASE CHECK THE VENDOR SITE CODE';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
--CURRENCY CODE VALIDATION
--===========================
BEGIN
SELECT CURRENCY_CODE
INTO L_CURR_CODE
FROM FND_CURRENCIES
WHERE CURRENCY_CODE =I.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS THEN
L_FLAG := 'E';
L_MSG := 'CURRENCY CODE IS INVALID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
--OPERATING UNIT ID VALIDATION
--==============================
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORG_ID
FROM HR_OPERATING_UNITS
WHERE ORGANIZATION_ID = I.ORG_ID;
EXCEPTION
WHEN OTHERS THEN
L_FLAG := 'E';
L_MSG := 'INVALID ORGANIZATION ID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
--SHIP TO LOCATION VALIDATION
--===============================
BEGIN
SELECT LOCATION_CODE
INTO L_LOCATION_CODE
FROM HR_LOCATIONS
WHERE LOCATION_CODE=I.SHIP_TO_LOCATION;
EXCEPTION
WHEN OTHERS THEN
L_FLAG := 'E';
L_MSG := 'INVALID SHIP TO LOCATION';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
-- IF ALL THE COLUMNS ARE GOOD THEN INSERT INTO INTERFACE TABLE
IF L_FLAG != 'E' THEN
INSERT INTO PO_HEADERS_INTERFACE
(
INTERFACE_HEADER_ID-- WE GET THIS VALUE FROM THE STANDARD SEQUENCE
,BATCH_ID-- ITS ORIGINAL
,ACTION--
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME -- BUYER NAME
,VENDOR_NAME--SUPPLIER
,VENDOR_SITE_CODE-- SUPPLIER SITE CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,CREATION_DATE
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
)
VALUES
(PO_HEADERS_INTERFACE_S.NEXTVAL -- HEADER_ID
,I.BATCH_ID -- UNIQUE INDENTIFIER
,I.ACTION
,L_ORG_ID
,I.DOCUMENT_TYPE_CODE --TYPE OF DOCUMENT (STANDARD, PLANNED,BLANKET ETC)
,L_CURR_CODE --CURRECY CODE
,I.AGENT_NAME --BUYER
,I.VENDOR_NAME --SUPPLIER
,L_SITE_CODE --SUPPLIER SITE CODE
,I.SHIP_TO_LOCATION --SHIP_TO_LOCATION ADDRESS
,I.BILL_TO_LOCATION -- BILL TO LOCATION ADDRESS
,SYSDATE-10
,I.APPROVAL_STATUS
,SYSDATE
,I.FREIGHT_TERMS --FREIGHT TERMS
);
COMMIT;
END IF;
FOR J IN C2(I.INTERFACE_HEADER_ID) LOOP
L_FLAG := 'A';
--ITEM VALIDATION
--=======================
BEGIN
SELECT DISTINCT(SEGMENT1)
INTO L_ITEM
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1 = J.ITEM;
--AND ORGANIZATION_ID = FND_PROFILE.VALUE('ORG_ID');
EXCEPTION
WHEN OTHERS THEN
L_FLAG := 'E';
L_MSG := 'ITEM IS NOT VALID ITEM';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
--LINE_TYPE VALIDATIOM
--=============================
BEGIN
SELECT DISTINCT(LINE_TYPE) INTO L_LINE_TYPE FROM PO_LINE_TYPES_B WHERE LINE_TYPE=J.LINE_TYPE;
EXCEPTION
WHEN OTHERS THEN
L_FLAG := 'E';
L_MSG := 'LINE_TYPE IS NOT VALID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
--END OF THE ITEM VALIDATION
IF L_FLAG != 'E' THEN
INSERT INTO PO.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
,ACTION
)
VALUES
( PO_LINES_INTERFACE_S.NEXTVAL
,PO_HEADERS_INTERFACE_S.CURRVAL
,J.LINE_NUM
,J.SHIPMENT_NUM
,J.LINE_TYPE
,L_ITEM
,J.ITEM_DESCRIPTION
,J.ITEM_ID
,J.UOM_CODE
,J.QUANTITY,
J.UNIT_PRICE,
J.SHIP_TO_ORGANIZATION_CODE,
J.SHIP_TO_LOCATION,
J.NEED_BY_DATE,
J.PROMISED_DATE,
J.LIST_PRICE_PER_UNIT,
J.ACTION);
COMMIT;
END IF;
FOR K IN C3(J.INTERFACE_LINE_ID) LOOP
--SET OF BOOKS VALIDATION
--===========================
BEGIN
SELECT SET_OF_BOOKS_ID INTO V_SET_OF_BOOKS_ID FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=K.SET_OF_BOOKS_ID;
EXCEPTION
WHEN OTHERS THEN
L_FLAG := 'E';
L_MSG := 'SET_OF_BOOKS_ID IS NOT VALID';
FND_FILE.PUT_LINE(FND_FILE.LOG,L_MSG||'-'||RECORD_COUNT);
CONTINUE;
END;
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
DESTINATION_ORGANIZATION_ID,
ORG_ID,
CHARGE_ACCOUNT_ID,
QUANTITY_ORDERED)
VALUES
(PO_HEADERS_INTERFACE_S.CURRVAL
,PO_LINES_INTERFACE_S.CURRVAL
,PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,
V_SET_OF_BOOKS_ID,
K.DESTINATION_ORGANIZATION_ID,
K.ORG_ID,
K.CHARGE_ACCOUNT_ID,
K.ORDERED_QUANTITY);
END LOOP;
END LOOP;
END LOOP;
IF L_FLAG !='E'
THEN FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'RECORDS SUCCESSFULLY INTERFACED TO PO');
END IF;
END PO_INTERFACE;
/
In order to import the Purchase orders in order to find the errors during the import of purchase orders and Purge the records that are accepted and got interfaced to PO following Standard programs need to be executed
========================================================================
http://oracleapps88.blogspot.com/2011/08/po-interface-tables.html
PO INTERFACES
PO INTERFACES
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 ;
/
No comments:
Post a Comment