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