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 ; /
0 comments:
Post a Comment