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接口表，用于记录订单头，行，Shipments以及Distributions信息
  •     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.
This is the interface table for price breaks and standard PO shipments. You should not populate these tables rather PO_LINES_INTERFACE as before. PDOI internally pushes records to this interface table for processing.

数据插入到接口表后，需要运行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.

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.

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. 

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), 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
For More Fields Descrption,Check This Blog

DOCUMENT_TYPE_CODE:  STANDARD, BLANKET , QUOTATION

Interface Scripts Examples
a - PO with a single line :
 
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

 

posted on 2013-09-30 22:47  you Richer  阅读(8398)  评论(0)  编辑  收藏  举报