對這文章發表回應

發表限制: 非會員 可以發表

[轉貼]Oracle Purchase Order Single Insert Script Process

發表者：冷日發表時間： 2018/8/18 13:03:37

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

PO_HEADERS_INTERFACE

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

PO_LINES_INTERFACE

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

P re-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

原文出處：Oracle Applications: Oracle Purchase Order Single Insert Script Process

標題
發表者名
內容圖示
內容	樣本 [詳情...]
認證碼		注意事項：預覽不需輸入認證碼，僅真正發送文章時才會檢查驗證碼。認證碼有效期10分鐘，若輸入資料超過10分鐘，請您備份內容後，重新整理本頁並貼回您的內容，再輸入驗證碼送出。
選項	不使用表情圖示