This blog post is to describe the strategy and procedure to be undertaken to process PO Receipt information from non-Oracle systems to Oracle applications In addition the document will highlight information related to the following:
⦁ High-Level Interface Strategy
⦁ Staging table specifications
⦁ Concurrent Program Details
Back ground:
⦁ Receipts against Standard Purchase Order.
⦁ Receipts against Blanket Purchase Order Release.
Interface Strategy:
⦁ XX system will insert receipt transaction data directly into the staging tables with processing flag = 1 and a unique sequence number as interface_id.
⦁ XX_RCV_IMPORT_PKG will select data from the staging table based on the processing flag value equals to 1 and insert a row in RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE. While inserting this data iif any error occurs, it will be recorder in a log file. All the processed data will be flagged as follows
⦁ Successfully Processed = 2
⦁ Warning/Error = 3
⦁ If there is a Warning or Error it will show warning at the concurrent program level.
⦁ The records in the staging table will remain in the staging table for n number of days for tracking purposes. After n number of days based on creation_date column in the staging table, all the successful records will be deleted. The processing flag = 3 will not be deleted from the staging table. It is user responsibility to correct these records. Number of days will be a parameter on the concurrent request. Default value of this parameter will be 30 days.
⦁ Once the records are inserted into the interface table from the staging table. It will get processed by Oracle standard program “Receiving Transaction Processor” to import Receipt into Oracle Purchasing. This process will create a log file. If there are any errors or warnings, XX User can correct the data directly the Open Interface tables through SQL*PLUS.
A concurrent request set will be defined to
a) To execute XX_RCV_IMPORT_PKG insert program
b) To execute Oracle Standard “Receiving Transaction Processor” program.
This concurrent request set will be assigned to XX INV Super User, XX System Support responsibilities.
Assumptions:
The following are assumptions for the Receipts Interface:
⦁ The Data is inserted directly into the staging table XX_RCV_TRANSACTIONS_STG with processing flag ‘1’.
⦁ All the functional setups and master data (MD100 Setup Document) are already completed in Oracle Applications.
⦁ Any pre-requisites – for example opening of accounting periods – need to be carried out before the import.
⦁ XX Communication will provide sample data/test scenarios from XX to team.
⦁ There is only one operating unit
⦁ The receipts are related to external vendors only.
⦁ UOM codes on receiving transactions will be the same as the ones on the corresponding purchase orders.
⦁ The interface program will generate a log report giving details on all erroneous transaction records that were submitted for import.
⦁ Materials are not under lot or serial control for the transaction types defined in this document.
⦁ Receipts are allowed to be imported even if the quantity is less than what was ordered on the Purchase Orders.
⦁ Over-receipt of quantities against purchase order will be handled based on tolerance defined in Oracle Applications.
⦁ The rows in the staging table will be grouped based on Group_id column in the staging table.
⦁ ICS users will resolve/monitor failed transaction interface rows using the Oracle Standard reporting feature by using “Receiving Interface Errors Report”.
⦁ Asset Management users will resolve/monitor failed staging interface row using sql*plus and log file provided by the XX_RCV_IMPORT_PKG. User will change processing_flag to 1, in order to reprocess corrected transactions.
⦁ Oracle application needs created_by, last_updated_by columns to keep track of the transactions. Fnd user id of the person, who will schedule this concurrent program, will be used as created_by and last_updated by columns. The creation_date and last_update_date will be system date.
⦁ The columns defined in the interface table are the minimum column required by the Oracle Standard Receiving import program.
⦁ XX transactions names in the staging table will not be tracked in Oracle. The transaction will be deleted from the staging table after n number of days. Default value for N, will be set to 30 days. This will be a parameter on the concurrent request, which can be changed by the users.
⦁ Loading data into the staging table is the responsibility of XX Systems. If any errors occur during this stage, e.g. if some records fail to get inserted into the staging table due to some required columns being null, Oracle will not have any details of the same.
⦁ PO is approved.
⦁ Following default values will be used in the interface tables, for example Transaction Status code will be used as ‘COMPLETED’, Transaction type will be ‘RECEIVE’ and Destination Type Code as ‘INVENTORY’ or ‘EXPENSE’. If item_num is not provided it will be considered as ‘EXPENSE’ otherwise ‘INVENTORY’.
⦁ Expected receipt date in the interface table will be defaulted from transaction date, if present, otherwise from sysdate.
⦁ If items are locator controlled, locator information needs to be provided.
⦁ The responsibilities XX INV Super User, XX System Support will be defined in the development system, if not already defined.
⦁ All necessary setups will be performed in Oracle Development Instance in order for team to do all necessary testing.
⦁ Pre validation will be performed at the XX side. The Oracle Standard import process will also validate all the rows before processing it.
⦁ All the transactions will be considered as new transactions.
⦁ The document shipment line number is not part of the staging table. The Receiving Open Interface allocates the shipment quantity against the shipments in a first-in, first-out order based on the PROMISED_DATE or the NEED_BY_DATE in the Purchasing tables.
⦁ RELEASE_NUM must be a valid release number for the purchasing document number provided and, if a release number is not provided, the Receiving Open Interface allocates the quantity across all open shipments for all releases.
⦁ If one row of a group error out during inserting from staging table to the interface table, the whole group will not be rejected in the staging table. The error row will be left behind in the staging table.
