Tuesday, September 27, 2016

Oracle Purchase Order Single Insert Script Process

Overview

            This document focuses on creating single insert scripts for importing standard purchase orders using interface approach. It also mentions the prerequisites for importing standard purchase orders.

This Document provides the guidance to the user with the necessary information for importing the purchase orders through interface approach.
          The Interface tables used here are:
           1. PO_HEADERS_INTERFACE
           2. PO_LINES_INTERFACE
           3. PO_DISTRIBUTIONS_INTERFACE



Mandatory Columns

COLUMN_NAME
DATATYPE
REQUIRED
COMMENTS
INTERFACE_HEADER_ID
NUMBER
Y
A sequential value
BATCH_ID
NUMBER
Y
To uniquely identify the records inserted
ACTION
VARCHAR2
Y
'ORIGINAL','UPDATE','ADD'
DOCUMENT_TYPE_CODE
VARCHAR2
Y
'STANDARD','BLANKET','QUOTATION'
VENDOR_ID
NUMBER
Y
Vendor Id
VENDOR_SITE_ID
NUMBER
Y
Vendor Site for the vendor
ORG_ID
NUMBER
OPTIONAL
Operating unit
CURRENCY_CODE
VARCHAR2
Y
Base Currency for the PO
AGENT_ID
NUMBER
Y
The buyer defined for the PO
SHIP_TO_LOCATION_ID
NUMBER
Y
Ship To Location for the purchase order
BILL_TO_LOCATION_ID
NUMBER
Y
Billing location
APPROVED_DATE
DATE
OPTIONAL
The date PO approved
CREATION_DATE
DATE
Y

CREATED_BY
NUMBER
Y
Who Column
EFFECTIVE_DATE
DATE
OPTIONAL



COLUMN_NAME
DATATYPE
REQUIRED
COMMENTS
INTERFACE_LINE_ID
NUMBER
Y
A sequential value
INTERFACE_HEADER_ID
NUMBER
Y
A sequential value matching the header value
LINE_TYPE
VARCHAR2
Y
'GOODS'
LINE_NUM
NUMBER
Y
Line number
UNIT_OF_MEASURE
VARCHAR2
Y
Unit of measure for the item
QUANTITY
NUMBER
Y
Quantity required
UNIT_PRICE
NUMBER
Y
The unit price for the item being ordered
ITEM_ID
NUMBER
OPTIONAL
The item id
ITEM_DESCRIPTION
VARCHAR2
Y
The description of the item
SHIP_TO_LOCATION_ID
NUMBER
OPTIONAL
Ship To Location for the purchase order
SHIP_TO_ORGANIZATION_ID
NUMBER
OPTIONAL
The organization corresponding to ship_to_location_id
CREATION_DATE
DATE
Y
Who column
CREATED_BY
NUMBER
Y
Who Column
SHIPMENT_NUMBER
NUMBER
OPTIONAL
Shipment number
CATEGORY_ID
NUMBER
OPTIONAL
The purchasing category assigned to this item.
PROMISED_DATE
DATE
Y
The promised date of deliverable

 


                        PO_DISTRIBUTIONS_INTERFACE

COLUMN_NAME
DATATYPE
REQUIRED
COMMENTS
INTERFACE_DISTRIBUTION_ID
NUMBER
Y
A sequential value
INTERFACE_LINE_ID
NUMBER
Y
A sequential value matching the line value
INTERFACE_HEADER_ID
VARCHAR2
Y
A sequential value matching the header value
DISTRIBUTION_NUMBER
NUMBER
Y
The distribution number
QUANTITY_ORDERED
VARCHAR2
Y
Quantity ordered in the PO
DESTINATION_TYPE_CODE
NUMBER
Y
'EXPENSE','INVENTORY'
ACRUE_ON_RECEIPT_FLAG
NUMBER
Y

CREATION_DATE
NUMBER
Y
Who column
CREATED_BY
VARCHAR2
Y
Who column
ORG_ID
NUMBER
OPTIONAL
Operating Unit

 



Insertion steps

Following are the steps that must be followed, in order, to perform the Single insertions for purchase orders in 12.1.3
Execution Step
Description
Step 1
Identify the vendor id and his corresponding vendor site id.

select * from ap_suppliers
where 1=1
and vendor_name like 'Star Gate Ltd'
and vendor_id = 5

With the above vendor_id get any one of the vendor_site_id which have purchasing_site_flag enabled. The vendor_site_id is  specific to the operating unit. Choose the vendor_site_id under the operating unit you are performing the import.

select * from ap_supplier_sites_all
where 1=1
and vendor_id = 5
AND purchasing_site_flag = 'Y'
and vendor_site_id = 6

Step 2
Get the agent_id i.e. buyer_id. A agent has to be defined for viewing any purchase order related forms. These agents are the employees of the organizations who are set up as buyers, so that they can raise purchase orders and requisitions.

SELECT full_name, agent_id
FROM po_agents pa, per_all_people_f ppl
WHERE 1=1
and pa.agent_id = ppl.person_id
AND UPPER (ppl.full_name) LIKE UPPER ('Stock, Ms. Pat')
and agent_id  = 25

Step 3
Identify the ship_to_location_id and bill_to_location_id defined for the vendor_sites using the Step 1 of vendor sites.

SELECT
vendor_site_id
,ship_to_location_id
,bill_to_location_id
FROM po_vendor_sites_all
WHERE 1=1
and vendor_id = 5
AND purchasing_site_flag = 'Y'
AND org_id = 204;


If there is no data for the location columns then derive the values from the below query. It returns the default ship_to and bill_to location values for a org id.

SELECT
ship_to_location_id
,bill_to_location_id
FROM financials_system_params_all
WHERE 1=1
AND org_id = 204

Step 4
Choose one of the values for the action column
'ORIGINAL' → A New record
'UPDATE' → To update an existing record
'ADD' → To add lines to an existing purchase order.

Choose the following values for the document_type_code column
STANDARD → One time purchase orders
BLANKET  →  Cover a specific date range and used when not sure of the exact quantity is not known prior.
QUOTATIONS → Quotation in response to the RFQ generated by the organization.

Step 5
INSERT INTO po_headers_interface
          (interface_header_id,
         batch_id,
         action,
         document_type_code,
         vendor_id,
         vendor_site_id,
         org_id,
         currency_code,
         agent_id,
         ship_to_location_id,
         bill_to_location_id,
         approved_date,
         creation_date,
         created_by,
         effective_date)
     VALUES (po_headers_interface_s.NEXTVAL,--interface_header_id,
             4321,                  -- batch_id
             'ORIGINAL',              -- action
             'STANDARD',  -- document_type_code
             5,                    -- vendor_id
             6,               -- vendor_site_id
             204,                     -- org_id
             'USD',            -- currency_code
             25,                    -- agent_id
             207,        -- ship_to_location_id
             204,        -- bill_to_location_id
             SYSDATE,          -- approved_date
             SYSDATE,          -- creation_date
             1318,                -- created_by
             SYSDATE           --effective_date
                    );

Step 6
Get an item id from mtl_system_items_b such that both the flags 'Purchasing_Enabled_Flag' and 'Purchasing Item Flag' are set to 'Y'. Also get the relevant unit_of_measure for the item.

SELECT
primary_unit_of_measure
,inventory_item_id
,description
,organization_id
FROM mtl_system_items_b
WHERE 1=1
AND segment1 = 'AS54999'
AND purchasing_enabled_flag = 'Y'
AND purchasing_item_flag = 'Y'

Step 7
Based on the ship_to_location_id column value given in the Po_Headers_Interface derive the corresponding ship_to_organization_id.
SELECT hu.NAME, hu.organization_id
FROM hr_organization_information hi, hr_all_organization_units hu
WHERE 1=1
AND hi.organization_id = hu.organization_id
AND hu.location_id = 207 --(as from the Step 3 )
AND hi.org_information1 = 'INV'

Step 8
There's an exclusive category set to be used for purchasing  which is 'Purchasing Category Set'.
The item being considered for purchase orders should fall  under this category.

SELECT
mcs.category_set_name
, mic.category_set_id
, mic.category_id
, mic.organization_id
FROM mtl_item_categories mic, mtl_category_sets_tl mcs
WHERE 1=1
AND mic.category_set_id = mcs.category_set_id
AND mic.inventory_item_id = 2155 --(as from the Step 6)


Step 9
INSERT INTO po_lines_interface (interface_line_id,
                                interface_header_id,
                                line_type,
                                line_num,
                                unit_of_measure,
                                quantity,
                                unit_price,
                                item_id,
                                item_description,
                                ship_to_location_id,
                                ship_to_organization_id,
                                creation_date,
                                created_by,
                                shipment_num,
                                category_id,
                                promised_date)
     VALUES (po_lines_interface_s.NEXTVAL,    -- interface_line_id
             po_headers_interface_s.CURRVAL,-- interface_header_id
             'Goods',                                 -- line_type
             1,                                        -- line_num
             'Each',                            -- unit_of_measure
             1,                                        -- quantity
             100,                                    -- unit_price
             2155,                                      -- item_id
             'Sentinel Standard Desktop',       -- item_description
             209,                           -- ship_to_location_id
             209,                           --ship_to_organization_id
             SYSDATE,                        -- creation_date
             1318,                              -- created_by
             1,                               -- shipment_num
             1,                                -- category_id
             SYSDATE + 10                     --promised_date
                      );

Step 10
INSERT INTO po_distributions_interface (interface_distribution_id,
                                        interface_line_id,
                                        interface_header_id,
                                        distribution_num,
                                        quantity_ordered,
                                        destination_type_code,
                                        accrue_on_receipt_flag,
                                        creation_date,
                                        created_by,
                                        org_id)
     VALUES (po_distributions_interface_s.NEXTVAL,--interface_distribut_id
             po_lines_interface_s.CURRVAL,   -- interface_line_id
             po_headers_interface_s.CURRVAL, -- interface_header_id
             1,                    -- distribution_num
             1,                    -- quantity_ordered
             'INVENTORY',     -- destination_type_code
             'N',            -- accrue_on_receipt_flag
             SYSDATE,                 -- creation_date
             1318,                        --created_by
             204                              --org_id
                ); 
Step 11
COMMIT
Step 12
Check the values in interface tables

select * from po_headers_interface
where 1=1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_lines_interface
where 1=1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_distributions_interface
where 1=1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_interface_errors
where 1=1
and creation_date > sysdate - 1
order by creation_date desc

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


The parameters for the concurrent program are
1.      Default Buyer  → A default buyer name which will appear on the imported PO.
2.      Create or Update Items → If even item creation or update is done along with PO check this.
3.      Approval Status → APPROVED, INCOMPLETE,INITIATE APPROVAL
4.      Batch Id → A unique identifier for the records inserted by us.

Step 14

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 = 4321; --( the batch id we passed for in step 5)

Step 15
Check the Base tables  and their respective columns effected.
Below listed are the important columns in each table.
select * from po_headers_all
where 1=1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_lines_all
where 1=1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_distributions_all
where 1=1
and creation_date > sysdate - 1
order by creation_date desc

           
1.      PO_HEADERS_ALL
     PO_HEADER_ID
AGENT_ID
TYPE_LOOKUP_CODE
SEGMENT1
SUMMARY_FLAG
ENABLED_FLAG
VENDOR_ID
VENDOR_SITE_ID
VENDOR_CONTACT_ID
SHIP_TO_LOCATION_ID
BILL_TO_LOCATION_ID
CURRENCT_CODE
CLOSED_CODE
ORG_ID
 
2.      PO_LINES_ALL
     PO_LINE_ID
PO_HEADER_ID
LINE_TYPE_ID
LINE_NUM
ITEM_ID
ITEM_REVISION
CATEGORY_ID
ITEM_DESCRIPTION
QUANTITY_COMMITTED
UNIT_PRICE
QUANTITY
CLOSED_CODE
ORG_ID

3.      PO_LINE_LOCATIONS_ALL
LINE_LOCATION_ID
PO_HEADER_ID
PO_LINE_ID
QUANTITY
QUANTITY_RECEIVED
QUANTITY_ACCEPTED
QUANTITY_REJECTED
QUANTITY_BILLED
QUANTITY_CANCELLED
SHIP_TO_LOCATION_ID
PROMISED_DATE


PO_DISTRIBUTIONS_ALL
   PO_DISTRIBUTION_ID
PO_HEADER_ID
PO_LINE_ID
LINE_LOCATION_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
QUANTITY_ORDERED
QUANTITY_DELIVERED
QUANTITY_BILLED
DESTINATION_TYPE_CODE
DESTINATION_ORGANIZATION_ID
ACCRUAL_ACCOUNT_ID
VARIANCE_ACCOUNT_ID
ORG_ID
           



Pre-Requisitions

Below mentioned are the list of tasks which need to be setup prior to purchase orders import.
Execution Step
Description
Step 1
Buyer need to be defined for viewing purchase orders or requisitions. The buyer in general terms is an employee of the organization who has been given the privilege to buy the goods. To enable an employee as a buyer
Navigate to
Purchasing Super User → Setup → Personal → Buyer


Only selected list of buyers have the rights for the PO approval.
Step 2
Suppliers need to be defined with proper site associations. The supplier sites are organization specific and are to have the purchasing_site_flag enabled.
Step 3
The item which is being user in the purchase orders has to have 'Purchasing Enabled Flag' and 'Purchasing Item Flag' Enabled.




Code Attachments

The below sql code has the code to perform single insert into Interface tables for Standard Purchase orders.
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
DECLARE
   L_header_id         NUMBER := po_headers_interface_s.NEXTVAL;
   L_line_id           NUMBER := po_lines_interface_s.NEXTVAL;
   L_distribution_id   NUMBER := po_distributions_interface_s.NEXTVAL;
BEGIN
   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   --------> INSERT INTO HEADERS INTERFACE TABLE<---------
   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_headers_interface (interface_header_id,
                                     batch_id,
                                     action,
                                     document_type_code,
                                     vendor_id,
                                     vendor_site_id,
                                     org_id,
                                     currency_code,
                                     agent_id,
                                     ship_to_location_id,
                                     bill_to_location_id,
                                     approved_date,
                                     creation_date,
                                     created_by,
                                     effective_date)
        VALUES (l_header_id,                            --interface_header_id,
                4321,                                              -- batch_id
                'ORIGINAL',                                          -- action
                'STANDARD',                              -- document_type_code
                5,                                                -- vendor_id
                6,                                           -- vendor_site_id
                204,                                                 -- org_id
                'USD',                                        -- currency_code
                25,                                                -- agent_id
                207,                                    -- ship_to_location_id
                204,                                    -- bill_to_location_id
                SYSDATE,                                      -- approved_date
                SYSDATE,                                      -- creation_date
                1318,                                            -- created_by
                SYSDATE                                       --effective_date
                       );

   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   --------> INSERT INTO LINES INTERFACE <---------
   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_lines_interface (interface_line_id,
                                   interface_header_id,
                                   line_type,
                                   line_num,
                                   unit_of_measure,
                                   quantity,
                                   unit_price,
                                   item_id,
                                   item_description,
                                   ship_to_location_id,
                                   ship_to_organization_id,
                                   creation_date,
                                   created_by,
                                   shipment_num,
                                   category_id,
                                   promised_date)
        VALUES (l_line_id,                                -- interface_line_id
                l_header_id,                            -- interface_header_id
                'Goods',                                          -- line_type
                1,                                                 -- line_num
                'Each',                                     -- unit_of_measure
                1,                                                 -- quantity
                100,                                             -- unit_price
                2155,                                               -- item_id
                'Sentinel Standard Desktop - Rugged',      -- item_description
                209,                                    -- ship_to_location_id
                209,                                -- ship_to_organization_id
                SYSDATE,                                      -- creation_date
                1318,                                            -- created_by
                1,                                             -- shipment_num
                1,                                              -- category_id
                SYSDATE + 10                                   --promised_date
                            );

   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   --------> INSERT INTO DISTRIBUTIONS INTERFACE TABLE<---------
   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_distributions_interface (interface_distribution_id,
                                           interface_line_id,
                                           interface_header_id,
                                           distribution_num,
                                           quantity_ordered,
                                           destination_type_code,
                                           accrue_on_receipt_flag,
                                           creation_date,
                                           created_by,
                                           org_id)
        VALUES (L_distribution_id,                    --interface_distribut_id
                l_line_id,                                -- interface_line_id
                l_header_id,                            -- interface_header_id
                1,                                         -- distribution_num
                1,                                         -- quantity_ordered
                'INVENTORY',                          -- destination_type_code
                'N',                                 -- accrue_on_receipt_flag
                SYSDATE,                                      -- creation_date
                1318,                                             --created_by
                204                                                   --org_id
                   );

   COMMIT;
------------------------------------------------------------------------------
END;
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect