Purchase Order Import

Working with Purchase Order Import (Import Standard Purchase Orders - POXPOPDOI)

A prospective customer wants to use API Wizard to load purchase orders.  Oracle provides an open interface process, aka the PO import process, for loading POs.  My plan is to utilize this import process via API Wizard. So, the first thing I do is run a a couple of simple test transactions through the interface to see that I can create POs the way I want. And I'm off...

Error: PO_HEADER_ID There should be at least one line per document.

However, I was repeatedly vexed by the following error: "Error: PO_HEADER_ID There should be at least one line per document." which I see in the po_interface_errors table. 

As usual, Oracle makes things that should be seemingly easy way too difficult! This error only tells me that there should be one line per document but, as you could imagine, I already had multiple lines associated with the header... so what gives?  

First, a little background on the this process:

Oracle provides the following interface tables:

po_interface_headers
po_interface_lines
po_interface_distributions

These tables are linked with the field 'po_header_id' which should be generated using the sequence po_headers_interface_s.  This serves as the link between both tables.  There is also a field po_header_id.  The error actually references this column, which makes you think you need to have this value in both the header and lines table - but that's misleading - the interface actually populates this value during the import process and it becomes the header_id in the po_headers_all table once the PO is successfully imported.

Although I am not sure why the interface failed the first time I ran it, what ended up happening is that the lines associated with my header had been stamped by the interface with a process_code of 'ACCEPTED' and so the import process wasn't considering them when I tried to reimport the header.  Hence the import process complaining that there needed to be at least one line.

The bottom line is make sure you have the correct interface_header_id in the lines table and it pays to reset the process_code if you are trying to rerun (or potentially change to 'UPDATE').

As I was looking into this, I also came across someone who indicated that you can receive this same error message if you due to incorrectly setting the import parameter 'Create or Update Items' to 'Yes' for a one time item.  This is referenced in metalink document ID 862819.1.  The bottom line is you should keep this set to 'No' unless you are trying to create a one-time item (time for the Purchasing User Manual for more details).

PO Created but no confirmation in concurrent output

Once I got passed the 'at least one line per document' error, I was able to successfully create an 'incomplete PO' (I hadn't entered distribution info in my first pass).  Unfortunately, the concurrent request didn't generate any output - it would have been nice if it had told me my PO was created.  Anyway, the po_header_interface record was stamped with a process_code of 'INCOMPLETE' and when I looked in po_headers_all and po_lines_all, my PO was there (I also verified it through the front end forms).

SELECT *
FROM   po_headers_all
WHERE  po_header_id = 110309

The 'authorization_status' field was incomplete, so I know I have to bring in my distributions to finish this PO.  So I added it:

Adding the distribution record

You need at least one distribution record for each purchase order line and you must pass some basic information.  First, to associate the distribution with an PO header and line, you must pass the following fields (with the same values uses for the po_interface_headers and po_interface_lines) tables:

interface_header_id (from po_interface_headers)
interface_line_id (from po_interface_lines)

Use the suggested sequence to derive the interface_distribution_id (unless you are importing from another source and have a unique numbering convention):

Some other useful things to know:

1.  Check package po_pdoi_constants to find the valid values for key parameters such as 'Action', 'Document Type', 'Process Code', etc. (lots of interesting stuff here).

2.  Use the table po_interface_errors to see the errors for your transactions.  The following fields in this table are particularly useful:

Table_Name -> The table for which the error is reported (e.g. po_headers_interface)
Interface_header_id -> The header_id for the errored record (if available)
Interface_line_id -> The line_id for the errored record (if available)
Interface_distribution_id -> the distribution_id for the errored record (if available)
Column_name -> The column the error references
Error_message_name -> The error code
Error_message -> The user error message
Request_id -> The concurrent request id