Posted on December 24th, 2007 by Sanjit Anand |

You can use Purchase Document Open Interface which allows you to quickly import a large volume of Standard Purchase Orders into Oracle Purchasing.

The Import process involves populating the PO interface tables with the document information to be imported and then running the Import
Standard Purchase Orders concurrent program which will validate the data and create the PO in the application and return an error message if something fail.

First Timer, know these first

Before you start, you need to understand the database objects which play critical role.

Steps by Steps

Know what is getting ining Data into Purchase Order Interface Tables

1. Load PO header, lines, shipments and distributions data from your source system into the following interface tables
   • PO_HEADERS_INTERFACE
   • PO_LINES_INTERFACE
   • PO_DISTRIBUTIONS_INTERFACE
2. Once the data has been inserted into the interface tables, a queries like the following can be used to review the information before running the import program :
   • 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

Review data before calling Import Standard Purchase Orders program.

when you submit the import program, third parameter is approval status , which altogether have different logic, which you need to understand the impact.

Understanding approval status in parameter

Significant impact is there on Approval Status parameter and have import logic which is as below:

Take Away

If the records got imported successfully without issues, the records will stay in the interface tables.

• You can notice , successful records get PROCESS_CODE as “ACCEPTED”
• It is good practice and important to check the Purchasing Interface Errors report always.
• This Error report you can submit after your import completed.


• Because the Purchasing Documents Open Interface saves or errors out line by line, it can accept partial documents. So that you may find a document has been accepted although some lines from it has been rejected . Therefore, to see which document lines were not submitted because of errors, you must check the Purchasing Interface Errors report.

What happen with IMPORT

This seems sound intresting to you.

• The Purchasing Documents Open Interface (PDOI) programs first process a record from the PO_HEADERS_INTERFACE table.
• Then, the program processes the child records in the PO_LINES_INTERFACE table then process the PO_DISTRIBUTIONS_INTERFACE table, before going on to the next PO represented by a record in PO_HEADERS_INTERFACE. Make sense.


• 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.

Other tools

This is most acceptable interface and widly used every where, therefore Oracle have Diagnostics tool for this keeping developer in mind.

Oracle Diagnostics tool name is Oracle Purchasing Documents Open Interface Data Collection Test.

This diagnostic test will verify the data in the interface tables used by the purchasing documents open interface (PDOI) so that it can be used proactively or reactively to resolve or prevent issues in the purchasing documents open interface (PDOI).

Hope you find this is very useful and productive tool.should you need any input send me offline.