This interface lets you integrate Oracle Purchasing quickly with new or existing applications such as material requirements planning, inventory management, and production control systems and also helps to enter requisitions from external sources.
First Timer, know these first
Before you start , you need to understand the database objects which play critical role.
|PO_REQUISITIONS_INTERFACE_ALL||This is the main Requisition Import interface table||Interface table|
The PO_REQ_DIST_INTERFACE_ALL table was used in Release 11, for Self-
|PO_INTERFACE_ERRORS||This table stores all errors resulted from import process.||Errors table|
|PO_REQUISITION_HEADERS_ALL||Base table that stores requisition headers||REQ Base table|
|PO_REQUISITION_LINES_ALL||Base table that stores requisition lines||REQ Base table|
|PO_REQ_DISTRIBUTIONS_ALL||This table stores requisition distributions||REQ Base table|
Steps by Steps
These are the basic steps for the import process :
- Insert data into interface table
- Run import program to insert data into base tables
- Review imported data from the front end.
First steps start with Inserting data into the Requisitions Interface Tables
You can inserts a single row into the PO_REQUISITIONS_INTERFACE_ALL and/or the PO_REQ_DIST_INTERFACE_ALL table for each requisition line that you want to import.
You identify the set of rows you want to import with each other by setting the INTERFACE_SOURCE_CODE and BATCH_ID columns appropriately in the PO_REQUISITIONS_INTERFACE_ALL table. You then pass these values as parameters to the Requisition Import program. If you do not specify any values for these parameters, the program imports all the requisition lines in the PO_REQUISITIONS_INTERFACE_ALL table.
Typically while inserting you might notice as per documentation, there are three types of columns exist in the in interface tables, as
- Required Data
- Required : That mean, You must provide values for all columns that are required.No choice.
- Conditionally required :You may also have to provide values for columns that are conditionally required. Providing a CURRENCY_CODE, the RATE, RATE_DATE, and RATE_TYPE accordingly to Rate fields are conditionally required.
- Derived Data : System will default those columns using logic similar to that used by the Requisitions form.
- Optional Data : These are optional to be filled and will not stop the import process.
Review data before calling Import program.
when you submit the import program, third parameter is approval status , which altogether have different logic, which you need to understand the impact.
How the Initiate Approval After Requisition Import parameter works and is the relationship between it and AUTHORIZATION_STATUS field?
Yes, this is important to understand the significance and importance of this field .
Look at the table below , helps you to understand the value of parameter.
Value in table Parameter
Result Approved YES Req is approved, no call to workflow Incomplete YES Req is created, workflow is called. Result could be Incomplete, Approved, or In-Process Approved NO Has no effect, record in the Interface states
Approved, no call to req approval workflow. Req is created as Approved.
Incomplete NO Req is created, and is Incomplete. No call to the workflow for req approval
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 when IMPORT run
This seems sound intresting to you.
when you run Requisition Import concurrent request , program enforced to complete in three steps phases.
The Requisition Import program operates in three phases.
Phase 1 :Validation
The first phase, where the program validates your data and derives or defaults additional information.
- The program generates an error message for every validation that fails and accordingly, it will create a row in the PO_INTERFACE_ERRORS table with detailed information about each error.
- Program will then check for the column MULTI_DISTRIBUTIONS in the PO_REQUISITIONS_INTERFACE_ALL table, if it is set to yes ( Y ) , Requisition Import will then check for corresponding distributions in the PO_REQ_DIST_INTERFACE_ALL table and if it did not find any corresponding distribution information, it will loads these as errors in the PO_INTERFACE_ERRORS table.
Phase 2 :Grouping
In the second phase, the program groups and numbers the validated requisition lines according to the following criteria:
- If you specify a value in the REQ_NUMBER_SEGMENT1 column of the PO_REQUISITIONS_INTERFACE_ALL table, all lines with the same value for this column are grouped together under a requisition header.
- If you provide a value in the GROUP_CODE column, all lines with the same value in this column are grouped together under a requisition header.
- If you do not provide values in either of these columns, the Requisition Import program uses the Group By parameter to group lines together.
- If you do not provide a value for this parameter, the program uses the default Group By that you set up to group requisition lines.
Phase 3 :Deletion
In the third phase, the program deletes all the successfully processed rows in the interface tables, and creates an output which lists the following :
- Number of interface records that were successfully imported.
- Number of interface records that were not imported.
This output can be viewed by choosing View Output for the Requisition Import concurrent Request ID in the Requests window.
What Can , what Cann't
You can import approved or unapproved requisitions using the Requisitions Open Interface.
If you are using requisition encumbrance, approved requisitions that you import automatically become pre-approved.
In case there are some records not imported appears in the output from the report, then you can launch the Requisition Import Exceptions Report to view the rows that were not imported by the Requisition Import program along with the failure reason(s) for each row.
A very common Observation while Requisition import
When you trying to submit a request to run the Requisition Import and when you try to select from the list of values in the parameter field for Import Source, you do not find any value and you receive the error message: "FRM-41830 List of Values contains No Entries "
Therefore you need to understand the List of Values for the Import Source parameter is dynamically determined by the INTERFACE_SOURCE_CODE column value in the PO_REQUISITIONS_INTERFACE_ALL table, So that if there are no values for the LOV, then there are no records in the table for the current organization.
In that case you just have to ensure that there are records in the interface and that the field INTERFACE_SOURCE_CODE is already populated.
Hope this post will be helpful.