Standard PO Interface or Conversion in Oracle Apps

Overview:

This document focuses on creating Standard Purchase Orders using Interface approach.

This will help you to load or insert Standard PO records in Base tables of POs.

I have used below code for Conversion, migrate data from 11i to R12.2.7

Interface Tables are:

   1) PO_HEADERS_INTERFACE

   2) PO_LINES_INTERFACE

   3) PO_DISTRIBUTIONS_INTERFACE

   4) PO_LINE_LOCATIONS_INTERFACE

Oracle import standard program:   Import Standard Purchase Orders

Base Tables are:

   1) PO_HEADERS_ALL

   2) PO_LINES_ALL

   3) PO_LINE_LOCATIONS_ALL

   4) PO_DISTRIBUTIONS_ALL

   

Mandatory Columns

1) PO_HEADERS_INTERFACE:

   (1)  Interface_header_id

   (2)  Action

   (3)  Document_type_code

   (4)  Vendor_Id

   (5)  Vendor_Site_Id

   (6)  Org_id

   (7)  Currency_code

   (8)  Agent_id

   (9)  Ship_to_location_id

   (10) Bill_to_location_id

   (11) Approved_date

   (12) Creation_date

   (13) Created_by

   (14) Effective_date

   

2) PO_LINES_INTERFACE:

   (1)  Interface_line_id

   (2)  Interface_header_id

   (3)  Line_type

   (4)  Line_num

   (5)  Unit_of_measure

   (6)  Quantity

   (7)  Unit_price

   (8)  Item_id

   (9)  Item_description

   (10) Ship_to_location_id

   (11) Creation_date

   (12) Created_by

   (13) Action

   (14) Need_by_date

   (15) Promised_date

3) PO_DISTRIBUTIONS_INTERFACE:

   (1)  Interface_header_id

   (2)  Interface_line_id

   (3)  Interface_distribution_id

   (4)  Distribution_num

   (5)  Quantity_ordered

   (6)  Charge_account_id

   (7)  Created_by

   (8)  Creation_date

   (9)  Last_updated_by

   (10) Last_update_date

   (11) Last_update_login

4) PO_LINE_LOCATIONS_INTERFACE

   (1)  Interface_header_id

   (2)  Interface_line_id

   (3)  Interface_line_location_id

   (4)  Shipment_num

   (5)  Created_by

   (6)  Last_updated_by

   (7)  Last_update_login

   (8)  Qty_rcv_exception_code

   (9)  Days_early_receipt_allowed

   (10) Allow_substitute_receipts_flag

   (11) Days_late_receipt_allowed

   (12) Receipt_days_exception_code

   (13) Enforce_ship_to_location_code

   (14) Need_by_date

   (15) Promised_date

   (16) Creation_date

   (17) Last_update_date

----------------------

Validate all mandatory fields and insert valid records in Interface tables:

*Validation of header level fields:

  --1) Validation of Buyer Name

     SELECT agent_id INTO ln_agent_id

     FROM po_agents_v

     WHERE UPPER (agent_name) = UPPER (lreq_hdr.buyer_name)

       AND (end_date_active IS NULL OR end_date_active > SYSDATE);

 --2) Validation of Vendor Number

     SELECT vendor_id INTO ln_vendor_id

     FROM ap_suppliers

     WHERE UPPER (segment1) = UPPER (lreq_hdr.supplier_no)

       AND (end_date_active IS NULL OR end_date_active > SYSDATE);

   

 --3) Validation of Vendor Site Code

     SELECT vendor_site_id INTO ln_vendor_site_id

     FROM ap_supplier_sites_all

     WHERE UPPER (vendor_site_code) = UPPER (lreq_hdr.vendor_site_code)

       AND vendor_id = ln_vendor_id

       AND org_id = ln_org_id

       AND (inactive_date IS NULL OR inactive_date > SYSDATE);  

 --4) Validation of Ship to location/ deliver_loc_code

     SELECT location_code INTO lc_ship_to_location

     FROM hr_locations

     WHERE UPPER (location_code) = UPPER (lreq_hdr.deliver_loc_code)

       AND (inactive_date IS NULL OR inactive_date > SYSDATE);

 --5) Validation of Vendor Contact

     SELECT vendor_contact_id INTO ln_vendor_contact_id

     FROM po_vendor_contacts

     WHERE vendor_site_id = ln_vendor_site_id

       AND UPPER (first_name || ' ' || middle_name || ' ' || last_name ) = UPPER (lreq_hdr.vendor_contact)

       AND (inactive_date IS NULL OR inactive_date > SYSDATE);

 --6) Validation of Bill to location code

     SELECT location_id INTO ln_bill_to_location_id

     FROM hr_locations

     WHERE UPPER (location_code) = UPPER (lreq_hdr.bill_to_location_code)

       AND (inactive_date IS NULL OR inactive_date > SYSDATE);

   

* Insert data into PO_HEADERS_INTERFACE table:

    INSERT INTO po_headers_interface

                (interface_header_id,

                 interface_source_code,

                 action,

                 po_header_id,

                 document_type_code,

                 document_subtype,

                 agent_id,

                 document_num,

                 vendor_id,

                 vendor_site_id,

                 vendor_site_code,

                 ship_to_location,

                 bill_to_location,

                 attribute_category,

                 vendor_contact_id,

                 created_by,

                 creation_date,

                 last_updated_by,

                 last_update_date,

                 last_update_login,

                 supply_agreement_flag,

                 expiration_date,

                 org_id, rate,

                 currency_code,

                 rate_type,

                 rate_date,

                 comments, freight_carrier,

                 effective_date

                )

        VALUES (po_headers_interface_s.NEXTVAL -- interface_header_id

                 'XX007' -- interface_source_code

                 'ORIGINAL' -- action

                 po_headers_s.NEXTVAL -- po_header_id

                 lreq_hdr.TYPE -- document_type_code

                 lreq_hdr.sub_type -- document_subtype

                 ln_agent_id         -- agent_id

                 lreq_hdr.po_number  -- document_num

                 ln_vendor_id        -- vendor_id

                 ln_vendor_site_id   -- vendor_site_id

                 NULL                -- vendor_site_code

                 lc_ship_to_location -- ship_to_location

                 lreq_hdr.bill_to_location_code -- bill_to_location

                 lreq_hdr.TYPE        -- attribute_category

                 ln_vendor_contact_id -- vendor_contact_id

                 ln_user_id           -- created_by

                 lreq_hdr.creation_date -- creation_date

                 ln_user_id -- last_updated_by

                 SYSDATE    -- last_update_date

                 ln_user_id -- last_update_login

                 NULL       -- supply_agreement_flag

                 NULL       -- expiration_date

                 lreq_hdr.org_id -- org_id

                 lreq_hdr.rate -- rate

                 lreq_hdr.currency_code -- currency_code

                 lreq_hdr.rate_type -- rate_type

                 lreq_hdr.rate_date -- rate_date

                 lreq_hdr.comments -- comments

                 lreq_hdr.carrier -- freight_carrier

                 lreq_hdr.effective_start_date -- effective_date

                );

* Validation of Line level fields:

 --1) Validation of line type

     SELECT line_type_id INTO ln_line_type_id

     FROM po_line_types_val_v

     WHERE UPPER (line_type) = UPPER (lreq_line.line_type);

 --2) Validation of dest_org_code

     SELECT organization_id, organization_code INTO ln_organization_id, lc_organization_code

     FROM org_organization_definitions

      WHERE UPPER (organization_code) = UPPER (lreq_line.dest_org_code);

--3) Validation of Item

     SELECT inventory_item_id INTO ln_item_id

     FROM mtl_system_items_b

     WHERE UPPER (segment1) = UPPER (lreq_line.part_number)

       AND organization_id = ln_organization_id

   AND purchasing_enabled_flag = 'Y'

   AND purchasing_item_flag = 'Y';

 --4) Validation of UOM_CODE

     SELECT unit_of_measure INTO lc_unit_of_measure

     FROM mtl_units_of_measure_tl

     WHERE UPPER (uom_code) = UPPER (lreq_line.supplier_uom)

       AND LANGUAGE = 'US';

 --5) Item Quantity is not null, its must be greater than 0.

* Insert validated lines in PO_LINES_INTERFACE table: 

  INSERT INTO po_lines_interface (interface_header_id,

              interface_line_id,

              po_header_id,

              po_line_id,

              action,

              document_num,

              line_type_id,

              item,

              uom_code,

              quantity,

              unit_price,

              need_by_date,

              promised_date,

              ship_to_organization_code,

              enforce_ship_to_location_code,

              receiving_routing_id,

              line_attribute15,

              line_num,

              created_by,

              creation_date,

              last_updated_by,

              last_update_date,

              last_update_login,

              vendor_product_num,

              CATEGORY,

              item_description,

              note_to_vendor,

              invoice_close_tolerance,

              receive_close_tolerance,

              qty_rcv_tolerance,

              item_revision,

              list_price_per_unit,

              line_loc_populated_flag)

   VALUES (po_headers_interface_s.CURRVAL -- interface_header_id

              po_lines_interface_s.NEXTVAL  -- interface_line_id

              po_headers_s.CURRVAL         -- po_header_id

              po_lines_s.NEXTVAL             -- po_line_id

              'ADD'                              -- action

              lreq_hdr.po_number           -- document_num

              ln_line_type_id              -- line_type_id

              lreq_line.part_number                -- item

              lreq_line.supplier_uom           -- uom_code

              lreq_line.qty_ord                -- quantity

              lreq_line.unit_price           -- unit_price

              lreq_line.need_by_date       -- need_by_date

              lreq_line.promised_date     -- promised_date

              lc_organization_code -- ship_to_organization_code

              'WARNING'   -- enforce_ship_to_location_code

              NULL                 -- receiving_routing_id

              lreq_line.line_number    -- line_attribute15

              lreq_line.line_number            -- line_num

              ln_user_id                     -- created_by

              SYSDATE                     -- creation_date

              ln_user_id                -- last_updated_by

              SYSDATE                  -- last_update_date

              ln_user_id              -- last_update_login

              lreq_line.vendor_product_num -- vendor_product_num

              lreq_line.CATEGORY               -- category

              lreq_line.item_description -- item_description

              lreq_line.note_to_vendor   -- note_to_vendor

              lreq_line.invoice_close_tolerance -- invoice_close_tolerance

              lreq_line.receive_close_tolerance -- receive_close_tolerance

              lreq_line.tolerance     -- qty_rcv_tolerance

              lreq_line.item_revision,     --item_revision

              lreq_line.list_price,  --list_price_per_unit

              'Y'

              );

* Validate charge account and insert data in PO_DISTRIBUTIONS_INTERFACE and PO_LINE_LOCATIONS_INTERFACE tables:

  --1) Validation of charge_account

     SELECT glcc.code_combination_id INTO ln_charge_account_id

     FROM gl_code_combinations glcc

     WHERE glcc.segment1 '.' || glcc.segment2

           || '.'|| glcc.segment3

           || '.'|| glcc.segment4

           || '.'|| glcc.segment5

           || '.'|| glcc.segment6

           || '.'|| glcc.segment7 = lreq_line_dist.charge_account

      AND (end_date_active IS NULL OR end_date_active > SYSDATE);

    INSERT INTO po_distributions_interface (interface_header_id,

              interface_line_id,

              interface_distribution_id,

              distribution_num,

              quantity_ordered,

              charge_account_id,

              created_by,

              creation_date,

              last_updated_by,

              last_update_date,

              last_update_login)

            VALUES (po_headers_interface_s.CURRVAL, -- interface_header_id

              po_lines_interface_s.CURRVAL, -- interface_line_id

              po_distributions_interface_s.NEXTVAL, -- interface_distribution_id

              lreq_line_dist.shipment_num, -- distribution_num

              lreq_line.qty_ord, -- quantity_ordered

              ln_charge_account_id, -- charge_account_id

              ln_user_id, -- created_by

              SYSDATE, -- creation_date

              ln_user_id, -- last_updated_by

              SYSDATE, -- last_update_date

              ln_user_id -- last_update_login

              );

    INSERT INTO po_line_locations_interface

              interface_header_id,

              interface_line_id,

              interface_line_location_id,

              shipment_num,

              created_by, creation_date,

              last_updated_by, 

              last_update_date,

              last_update_login,

              qty_rcv_exception_code,

              days_early_receipt_allowed,

              allow_substitute_receipts_flag,

              days_late_receipt_allowed,

              receipt_days_exception_code,

              enforce_ship_to_location_code,

              need_by_date,

              promised_date)

      VALUES (po_headers_interface_s.CURRVAL, -- interface_header_id

              po_lines_interface_s.CURRVAL, -- interface_line_id

              po_line_locations_interface_s.NEXTVAL, -- interface_line_location_id

              lreq_line_dist.shipment_num, -- shipment_num

              ln_user_id, -- created_by

              SYSDATE,   -- creation_date

              ln_user_id, -- last_updated_by

              SYSDATE, -- last_update_date

              ln_user_id, -- last_update_login

              lreq_line_dist.over_receipt_tolerance_action, --qty_rcv_exception_code

              lreq_line_dist.days_early, -- days_early_receipt_allowed

              lreq_line_dist.allow_substitute_receipt, -- allow_substitute_receipts_flag

              lreq_line_dist.days_late, -- days_late_receipt_allowed

              lreq_line_dist.receipt_days_excep_action, -- receipt_days_exception_code

              lreq_line_dist.enforce_ship_to, -- enforce_ship_to_location_code

              lreq_line.need_by_date,

              lreq_line.promised_date);

--Check the values in interface tables

   SELECT * FROM po_headers_interface

   where 1=1

   AND creation_date LIKE sysdate;

   

   SELECT * FROM po_lines_interface

   where 1=1

   AND creation_date LIKE sysdate;

   

   SELECT * FROM po_distributions_interface

   where 1=1

   AND creation_date LIKE sysdate;

   

   SELECT * FROM po_line_locations_interface

   where 1=1

   AND creation_date LIKE sysdate;

   

   SELECT * FROM po_interface_errors

   where 1=1

   AND creation_date LIKE sysdate;

-- Run Standard program for insert data in Base tables from interface tables:

   Responsibility: Purchasing Super User

   Standard Program Name: Import Standard Purchase Orders

   Navigation : Purchasing Super User → Reports → Run → Import Standard Purchase Orders

 

 
The parameters for the concurrent program are

   1. Default Buyer →          Optional:  A default buyer name which will appear on the imported PO.

   2. Create or Update Items → Mandatory: If even item creation or update is done along with PO check this.

   3. Approval Status →        Mandatory: APPROVED, INCOMPLETE,INITIATE APPROVAL

   4. Batch Id →               Optional:  A unique identifier for the records inserted by us. 

   5. Group Lines → Optional

   6. Batch Size → Optional

   7. Gather Stats → Optional

   If the import programs completes as normal check for the status of po_headers_interface action

      column. If its 'REJECTED' check in the PO_INTERFACE_ERRORS with the batch_id.

  

      SELECT *

      FROM po_interface_errors

      WHERE batch_id = 10821;

-- Check the Base tables:   

   SELECT * FROM po_headers_all

   where 1=1

   AND creation_date LIKE sysdate;

   

   SELECT * FROM po_lines_all

   where 1=1

   AND creation_date LIKE sysdate;

   

   SELECT * FROM po_distributions_all

   where 1=1

   AND creation_date LIKE sysdate;

   

   SELECT * FROM po_line_locations_all

   where 1=1

   AND creation_date LIKE sysdate;

   

** Typical errors:

   Error:    Standard PO went in INCOMPLETE status, because of Items.

   Solution: Check ASL and SR created or not for Item, if not created then create it before use same item in PO.