PO模块也有自己的接口表，多用于把其他业务系统在Oracle EBS系统生成采购订单记录。
下边是主要的三张PO接口表，用于记录订单头，行，Shipments以及Distributions信息
ACTION: ORIGINAL, REPLACE or UPDATE.
ORIGINAL: Original is one in which all information is new in your system.
REPLACE: Replaces already-created blanket purchase agreements or catalog quotations with new documents containing the new information. First, it looks for documents that have same document type
DOCUMENT_TYPE_CODE: STANDARD, BLANKET , QUOTATION
转载请注明出处：http://blog.csdn.net/pan_tian/article/details/12202365
| Table Name | Description | Type |
| PO_HEADERS_INTERFACE | This is the table where to insert PO headers data in interface table. | Interface table |
| PO_LINES_INTERFACE | This is where we insert PO lines information to be imported ( it is used also for Shipments details ) | Interface table |
| PO_DISTRIBUTIONS_INTERFACE | This is where we insert PO distribution details before import | Interface table |
| PO_INTERFACE_ERRORS | Stores all errors resulted from import process. | Errors table |
| PO_HEADERS_ALL | Stores document headers for purchase orders, purchase agreements,quotations, and RFQs | PO Base table |
| PO_LINES_ALL | Stores purchase document lines for purchase orders, purchase agreements, quotations, and RFQs | PO Base table |
| PO_LINE_LOCATIONS_ALL | Stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs | PO Base table |
| PO_DISTRIBUTIONS_ALL | Stores purchase order distributions | PO Base table |
- PO_HEADERS_INTERFACE
- PO_LINES_INTERFACE
- PO_DISTRIBUTIONS_INTERFACE
接口表的查询语句
- Select * from PO_HEADERS_INTERFACE where INTERFACE_HEADER_ID=&headerid;
- Select * from PO_LINES_INTERFACE where INTERFACE_HEADER_ID=&headerid;
- Select * from PO_DISTRIBUTIONS_INTERFACE where INTERFACE_HEADER_ID=&headerid;
R12还引入了另外一张Interface表，PO_LINE_LOCATIONS_INTERFACE，这张表其实暂时还用不到，还只是Oracle标准产品内部使用，但因为名称上很容易让人误解，所以这里提一下。
Table Name | Feature Area |
|---|---|
PO_LINE_LOCATIONS_INTERFACE | This table is intended for internal use only. |
数据插入到接口表后，需要运行Concurrent Program:Import Standard Purchase Orders，用于把接口表导入到采购订单相关表。
Concurrent Program参数：
Default Buyer(Optional):
Enter the default buyer name to be associated with the incoming documents that are not assigned to a buyer..
If a default buyer is not specified and the imported purchase order also does not have buyer information, the program will use the normal Purchasing defaulting mechanism to generate buyer information. If the buyer name provided in the interface is not valid, then the record will error out and the default buyer name will not be supplemented.
Create or Update Item (Required)
The valid response is Yes or No. If Yes, the program will also create items in the item master from those buyer part numbers in the interface data where there is no corresponding item master entry. If No, the program will not create new item master entries.
The valid response is Yes or No. If Yes, the program will also create items in the item master from those buyer part numbers in the interface data where there is no corresponding item master entry. If No, the program will not create new item master entries.
Approval Status
有三个可选值，APPROVED,INCOMPLETE,INITIATE APPROVAL.
关于这个字段Purchasing User Guide中有清楚的说明，接口表中Status字段 + 这个Concurrent Program的Approval Status共同决定最终PO的状态。
| Document Status in Interface Table | Concurrent Program Approval Status Parameter | Resulting Document Status |
| Null | INCOMPLETE | INCOMPLETE |
| Null | APPROVED | APPROVED |
| Null | INITIATE APPROVAL | INITIATE APPROVAL |
| INCOMPLETE | INCOMPLETE | INCOMPLETE |
| INCOMPLETE | APPROVED | INCOMPLETE |
| INCOMPLETE | INITIATE APPROVAL | INITIATE APPROVAL |
| APPROVED | INCOMPLETE | APPROVED |
| APPROVED | APPROVED | APPROVED |
| APPROVED | INITIATE APPROVAL | APPROVED |
You can import Standard purchase orders with an INCOMPLETE status and then automatically initiate the PO Approval Workflow for these imported documents. Specifying a status of Incomplete or leaving the status Null in the interface data, and specifying a value of Initiate Approval for the Approval Status runtime parameter can achieve this.
Standard purchase orders can also be imported with an APPROVED status. Specifying a status of Approved in the interface data or leaving the status Null in the interface data, and specifying a value of Approved for the Approval Status runtime parameter can achieve this. The PO Approval Workflow is not initiated.
Batch ID (Optional)
Enter a valid batch ID, that is a unique identifier for the group of documents in this submission. If you don\'t enter a Batch ID, one is generated for you when the process runs.
Enter a valid batch ID, that is a unique identifier for the group of documents in this submission. If you don\'t enter a Batch ID, one is generated for you when the process runs.
Import Standard Purchase Orders对应的后台程序为SQL*Plus类型的POXPDOI。
订单Import Program的后台逻辑
1.首先从PO_HEADERS_INTERFACE处理一条记录
2.根据INTERFACE_HEADER_ID，再处理行接口记录PO_LINES_INTERFACE，以及 PO_DISTRIBUTIONS_INTERFACE。都处理完之后，又从PO_HEADERS_INTERFACE表中Pick下一个订单头数据。
- In between , If the program gets an error while processing a record, the program writes the error details to the PO_INTERFACE_ERRORS table and increments the record\'s error counter.
- Therefore, the Purchasing Documents Open Interface saves or errors out on a line-byline basis.
- This means that if an error is found in a document line, only that line is rolled back (not submitted to Purchasing), and we will be able to find the error in the PO_INTERFACE_ERRORS table.
- You should be aware , because the Purchasing Documents Open Interface can accept partial documents as it saves or errors out line by line.
- If an error is found in a header, none of its lines are processed.
- The Purchasing Documents Open Interface rolls back the header, does not process its lines, and does the following:
- Sets the PROCESS_CODE column value to REJECTED in the PO_HEADERS_INTERFACE table.
- Writes out the record identification number and the details of the error to the PO_INTERFACE_ERRORS table.
- Begins processing the next header record.
- If no processing errors are found during processing, the header record and all successfully submitted child records are loaded into Purchasing, and then flagged as processed by setting the PROCESS_CODE column to ACCEPTED.
As mention earlier, To check for records in error, the Purchasing Interface Errors Report can be run to provide information as to the cause of the error.
Diagnose Problems With Importing Standard Purchase Orders
如果Import Standard Purchase Orders运行后报错，可使用Purchasing Interface Errors Report来查错，这个Report实际上就是PO_INTERFACE_ERRORS里的内容。
如果订单没有导入成功，可以使用下边的SQL查看错误信息，
Select * from PO_INTERFACE_ERRORS where interface_type = \'PO_DOCS_OPEN_INTERFACE\' order by creation_date desc;
TRACING
To trace the Import Standard Purchase Orders program from 11.5.10 onwards, do the following :
1 - Navigate to System Administrator responsibility
2 - Navigate to Profiles->System
3 - Query the Profile Option Concurrent: Allow Debugging and set it to Yes at User level
4 - Navigate to Purchasing responsibility
5 - From the Requests form, choose the Import Standard Purchase Orders program and set the required Parameters
6 - Click the Debug button
7 - Check the SQL Trace checkbox and specify Trace with Binds and Waits
8 - Submit the Concurrent program
9 - Retrieve the trace file created.
LOGGING
In order to create a FND Debug Log for the Import Standard Purchase Orders program, the following steps need to be followed :
1 - Using a SQL client run the following query and Note down this log_seq. :
select max(log_sequence) from fnd_log_messages;
2 - Set the following profiles at the user level:
FND: Debug Log Enabled = YES
FND: Debug Log Filename = NULL
FND: Debug Log Level = STATEMENT
FND: Debug Log Module = %
3 - Run Import Standard Purchase Orders program
4. Using a SQL client run the following query :
Select * from fnd_log_messages
where log_sequence > &log_seq_noted_above
order by log_sequence
The output of this query can indicate errors that were encountered during the Import Standard Purchase Orders program.
To trace the Import Standard Purchase Orders program from 11.5.10 onwards, do the following :
1 - Navigate to System Administrator responsibility
2 - Navigate to Profiles->System
3 - Query the Profile Option Concurrent: Allow Debugging and set it to Yes at User level
4 - Navigate to Purchasing responsibility
5 - From the Requests form, choose the Import Standard Purchase Orders program and set the required Parameters
6 - Click the Debug button
7 - Check the SQL Trace checkbox and specify Trace with Binds and Waits
8 - Submit the Concurrent program
9 - Retrieve the trace file created.
LOGGING
In order to create a FND Debug Log for the Import Standard Purchase Orders program, the following steps need to be followed :
1 - Using a SQL client run the following query and Note down this log_seq. :
select max(log_sequence) from fnd_log_messages;
2 - Set the following profiles at the user level:
FND: Debug Log Enabled = YES
FND: Debug Log Filename = NULL
FND: Debug Log Level = STATEMENT
FND: Debug Log Module = %
3 - Run Import Standard Purchase Orders program
4. Using a SQL client run the following query :
Select * from fnd_log_messages
where log_sequence > &log_seq_noted_above
order by log_sequence
The output of this query can indicate errors that were encountered during the Import Standard Purchase Orders program.
Key Fields in Interface Tables
PO_HEADERS_INTERFACE
PROCESS_CODE: Set it to PENDING. After processing it shows as ACCEPTED, REJECTED or NOTIFIED.
如果订单头出错，那么Program不会处理订单行中的内容，订单头接口表中PROCESS_CODE字段会被设置为REJECTED，如果没错则PROCESS_CODE字段为ACCEPTED
For blanket purchase agreements and standard purchase orders only, when the supplier sends an updated price/sales catalog, the Purchasing Documents Open Interface sets the PROCESS_CODE column to NOTIFIED for those lines with prices that exceed your price tolerance. For these price updates only, the Purchasing Documents Open Interface waits for you to accept or reject the price increase in the Exceeded Price Tolerances window before completing or rejecting the price change.
ORIGINAL: Original is one in which all information is new in your system.
REPLACE: Replaces already-created blanket purchase agreements or catalog quotations with new documents containing the new information. First, it looks for documents that have same document type
(DOCUMENT_TYPE_CODE), supplier (VENDOR_ID) and supplier document number (VENDOR_DOC_ NUM) with the existing supplier document number (VENDOR_ORDER _NUM) as the replacement document
UPDATE: Updates Unit Price, Item Description, UOM, Price Breaks, Expiration Date
UPDATE: Updates Unit Price, Item Description, UOM, Price Breaks, Expiration Date
For More Fields Descrption,Check This Blog
DOCUMENT_TYPE_CODE: STANDARD, BLANKET , QUOTATION
Interface Scripts Examples
a - PO with a single line :
b - PO with one line and one distribution :
c - PO with two lines :
d - PO with one line and two shipments :
e - PO with one line, one shipment and two distributions :
INSERT INTO po.po_headers_interface (interface_header_id, batch_id, process_code, action, org_id, document_type_code, currency_code, agent_id, vendor_name, vendor_site_code, ship_to_location, bill_to_location, reference_num) VALUES (apps.po_headers_interface_s.NEXTVAL, 1, \'PENDING\', \'ORIGINAL\', -- Indicates this is a new document 204, -- Org id for operating unit Vision Operations \'STANDARD\', -- Indicates a standard PO is being imported \'USD\', -- The currency to be used in the PO 57, -- The ID of the buyer \'Office Supplies, Inc.\', -- Supplier name \'OFFICESUPPLIES\', -- Supplier Site \'V1- New York City\', -- Ship to location name \'V1- New York City\', -- Bill to location name \'TestPO\'); -- Reference that can be tied to the imported PO INSERT INTO po.po_lines_interface (interface_line_id, interface_header_id, line_num, shipment_num, line_type, item, uom_code, quantity, unit_price, ship_to_organization_code, ship_to_location) VALUES (po_lines_interface_s.nextval, po_headers_interface_s.currval, 1, 1, \'Goods\', \'CM96713\', -- Item to imported on the PO line \'Ea\', -- Unit of Measure 1, 50, -- Line price in specified currency \'V1\', -- Inventory Organization which will receive the shipment \'V1- New York City\' ); -- Ship to location name commit;
b - PO with one line and one distribution :
INSERT INTO po.po_headers_interface (interface_header_id, batch_id, process_code, action, org_id, document_type_code, currency_code, agent_id, vendor_name, vendor_site_code, ship_to_location, bill_to_location, reference_num) VALUES (apps.po_headers_interface_s.NEXTVAL, 2, \'PENDING\', \'ORIGINAL\', 204, \'STANDARD\', \'USD\', 57, -- Your buyer id \'Office Supplies, Inc.\', \'OFFICESUPPLIES\', \'V1- New York City\', \'V1- New York City\', \'TestPO\'); INSERT INTO po.po_lines_interface (interface_line_id, interface_header_id, line_num, shipment_num, line_type, item, uom_code, quantity, unit_price, ship_to_organization_code, ship_to_location) VALUES (po_lines_interface_s.nextval, po_headers_interface_s.currval, 1, 1, \'Goods\', \'CM96713\', \'Ea\', 1, 50, \'V1\', \'V1- New York City\' ); INSERT INTO po.po_distributions_interface (interface_header_id, interface_line_id, interface_distribution_id, distribution_num, quantity_ordered, charge_account_id) VALUES (po_headers_interface_s.currval, po.po_lines_interface_s.CURRVAL, po.po_distributions_interface_s.NEXTVAL, 1, 1, 12975); -- Code Combination ID for the Charge Account to be used on the Distribution commit;
c - PO with two lines :
INSERT INTO po.po_headers_interface (interface_header_id, batch_id, process_code, action, org_id, document_type_code, currency_code, agent_id, vendor_name, vendor_site_code, ship_to_location, bill_to_location, reference_num) VALUES (apps.po_headers_interface_s.NEXTVAL, 3, \'PENDING\', \'ORIGINAL\', 204, \'STANDARD\', \'USD\', 57, \'Office Supplies, Inc.\', \'OFFICESUPPLIES\', \'V1- New York City\', \'V1- New York City\', \'TestPO\'); INSERT INTO po.po_lines_interface (interface_line_id, interface_header_id, line_num, shipment_num, line_type, item, uom_code, quantity, unit_price, ship_to_organization_code, ship_to_location) VALUES (po_lines_interface_s.nextval, po_headers_interface_s.currval, 1, 1, \'Goods\', \'CM96713\', \'Ea\', 1, 50, \'V1\', \'V1- New York City\' ); INSERT INTO po.po_lines_interface (interface_line_id, interface_header_id, line_num, shipment_num, line_type, item, uom_code, quantity, unit_price, ship_to_organization_code, ship_to_location) VALUES (po_lines_interface_s.nextval, po_headers_interface_s.currval, 2, 1, \'Goods\', \'CM96713\', \'Ea\', 1, 80, \'V1\', \'V1- New York City\' ); commit;
d - PO with one line and two shipments :
INSERT INTO po.po_headers_interface (interface_header_id, batch_id, process_code, action, org_id, document_type_code, currency_code, agent_id, vendor_name, vendor_site_code, ship_to_location, bill_to_location, reference_num) VALUES (apps.po_headers_interface_s.NEXTVAL, 4, \'PENDING\', \'ORIGINAL\', 204, \'STANDARD\', \'USD\', 57, \'Office Supplies, Inc.\', \'OFFICESUPPLIES\', \'V1- New York City\', \'V1- New York City\', \'TestPO\'); INSERT INTO po.po_lines_interface (interface_line_id, interface_header_id, line_num, shipment_num, line_type, item, uom_code, quantity, unit_price, ship_to_organization_code, ship_to_location, promised_date) VALUES (po_lines_interface_s.nextval, po_headers_interface_s.currval, 1, 1, \'Goods\', \'CM96713\', \'Ea\', 1, 50, \'V1\', \'V1- New York City\', sysdate+1); INSERT INTO po.po_lines_interface (interface_line_id, interface_header_id, line_num, shipment_num, line_type, item, uom_code, quantity, unit_price, ship_to_organization_code, ship_to_location, promised_date) VALUES (apps.po_lines_interface_s.NEXTVAL, apps.po_headers_interface_s.CURRVAL, 1, 2, \'Goods\', \'CM96713\', \'Ea\', 2, 50, \'V1\', \'V1- New York City\', sysdate+5); commit;
e - PO with one line, one shipment and two distributions :
INSERT INTO po.po_headers_interface (interface_header_id, batch_id, process_code, action, org_id, document_type_code, currency_code, agent_id, vendor_name, vendor_site_code, ship_to_location, bill_to_location, reference_num) VALUES (apps.po_headers_interface_s.NEXTVAL, 5, \'PENDING\', \'ORIGINAL\', 204, \'STANDARD\', \'USD\', 57, \'Office Supplies, Inc.\', \'OFFICESUPPLIES\', \'V1- New York City\', \'V1- New York City\', \'TestPO\'); INSERT INTO po.po_lines_interface (interface_line_id, interface_header_id, line_num, shipment_num, line_type, item, uom_code, quantity, unit_price, ship_to_organization_code, ship_to_location, promised_date) VALUES (po_lines_interface_s.nextval, po_headers_interface_s.currval, 1, 1, \'Goods\', \'CM96713\', \'Ea\', 5, 50, \'V1\', \'V1- New York City\', sysdate+1); INSERT INTO po.po_distributions_interface (interface_header_id, interface_line_id, interface_distribution_id, distribution_num, quantity_ordered, charge_account_id) VALUES (po_headers_interface_s.currval, po.po_lines_interface_s.CURRVAL, po.po_distributions_interface_s.NEXTVAL, 1, 1, 12975); INSERT INTO po.po_distributions_interface (interface_header_id, interface_line_id, interface_distribution_id, distribution_num, quantity_ordered, charge_account_id) VALUES (po_headers_interface_s.currval, po.po_lines_interface_s.CURRVAL, po.po_distributions_interface_s.NEXTVAL, 2, 4, 12976); commit
推荐阅读
转载请注明出处：http://blog.csdn.net/pan_tian/article/details/12202365
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26687597/viewspace-1207546/ ，如需转载，请注明出处，否则将追究法律责任。
