|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2018/8/18 13:03 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Oracle Purchase Order Single Insert Script Process
Oracle Purchase Order Single Insert Script Process 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
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 |
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 |
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. |
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
|
|
|
討論串
|
[轉貼]EBS 接口開發 (冷日