對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2018/8/18 13:18:01
Purchase Document Open Interface(PDOI)
PO模塊也有自己的接口表，多用於把其他業務系統在Oracle EBS系統生成採購訂單記錄。| 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的狀態。
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.
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
原文出處： Purchase Document Open Interface(PDOI) - you Richer - 博客园