Data flow for Order-to-Cash cycle

Data flow for Order-to-Cash cycle

1. Order Entry
This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table.

• Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table oe_order_headers_all flow_status_code = ENTERED, booked_flag = N), Primary key=HEADER_ID

• No record exist in any other table for this order till now.

• Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID. oe_order_lines_all (flow_status_code = ENTERED, booked_flag = N, open_flag = Y) Primary key= LINE_ID

2.Order Booking
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.

• oe_order_headers_alL (flow_status_code as BOOKED, booked_flag updated to Y)
• oe_order_lines_all (flow_status_code as AWAITING_SHIPPING, booked_flag updated Y)
• wsh_delivery_details (DELIVERY_DETAIL_ID is assigned here, released_status ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
• wsh_delivery_assignments (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in wsh_delivery_details, DELIVERY_ID remains blank till this stage)

*In shipping transaction form order status remains "Ready to Release".

At the same time, Demand interface program runs in background And insert into inventory tables mtl_demand, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully get completed, the mtl_demand and mtl_reservations table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.

4. Pick Release
Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:

• If step 3 is not done then MTL_RESERVATIONS gets updated now.
• wsh_new_deliveries (one record gets inserted with SOURCE_HEADER_ID= order header ID, status_code=OP =>open)
• wsh_delivery_assignments (DELIVERY_ID gets assigned which comes from wsh_new_deliveries)
• wsh_delivery_details (released_status ‘S’ ‘submitted for release’)
• MTL_TXN_REQUEST_HEADERS
• MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)
• (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)
• Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id, this table holds the record temporally)
• MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)
• MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )

*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

5.Pick Confirm/ Move Order Transaction

Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.

• MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
• oe_order_lines_all (flow_status_code ‘PICKED’ )
• MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)
• mtl_transaction_accounts
• wsh_delivery_details (released_status becomes ‘Y’ => ‘Released’ )
• wsh_delivery_assignments
• MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
• MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')

* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6.Ship Confirm
Here ship confirm interface program runs in background. Data removed from wsh_new_deliveries.

The items on the delivery gets shipped to customer at this stage.

• oe_order_lines_all (flow_status_code ‘shipped’)
• wsh_delivery_details (released_status ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
• WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
• mtl_transaction_interface
• mtl_material_TRANSACTIONS (linked through Transaction source header id)
• mtl_transaction_accounts
• Data deleted from mtl_demand, MTL_reservations
• Item deducted from MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
• MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

7.Enter Invoice
After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales CREDIT for the particular order.

ra_interface_lines_all (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in

ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to order number and line_id of the orders)

8.Complete Line
In this stage order line level table get updated with Flow status and open flag.
oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)

9.Close Order
This is last step of Order Processing. In this stage only oe_order_lines_all table get updated. These are the table get affected in this step.

oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)

oe_order_HEADERS_all

 ü       Order Import Interface

ü       Item Import (Item conversion)

ü On-hand Quantity

ü Customer Conversion

ü Customer API

ü Auto Invoice Interface

ü Receipt API

ü Auto Lockbox Interface

ü AP Invoice Interface

ü Vendor Interface/Conversion

ü Requisition Import

ü PO receipts interface

ü GL Interface

ü GL Budget Interface

ü GL Daily conversion rates

 Order Import Interface (Sales Order Conversion)

          Interface tables:

· OE_HEADERS_IFACE_ALL        

· OE_LINES_IFACE_ALL                               

· OE_ACTIONS_IFACE_ALL                           

· OE_ORDER_SOURCES                      

· OE_CUSTOMER_INFO_IFACE_ALL       

· OE_PRICE_ADJS_IFACE_ALL    

· OE_PRICE_ATTS_IFACE_ALL

          Base tables:  

· OE_ORDER_HEADERS_ALL         

· OE_ORDER_LINES_ALL

          Pricing tables          

· QP_PRICING_ATTRIBUTES

          During import of orders, shipping tables are not populated.

If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL

Base Tables: HZ_PARTIES    HZ_LOCATIONS

Orders can be categorized based on their status:

                   1. Entered orders              

2. Booked orders               

3. Closed orders

                  

Concurrent Program: Order Import

 Validations:

· Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.

· Check for sales_rep_id. Should exist for a booked order.

· Ordered_date should exist. -------- header level

· Delivery_lead_time should exist. ----------- line level

· Earliest_acceptable_date should exist.

· Freight_terms should exist

Order Import API    OE_ORDER_PUB.GET_ORDER, PROCESS_ORDER

          Concurrent programs will in turn call APIs.

          2 APIs are called during order import process.

· OE_CNCL_ORDER_IMPORT_PVT (cancelled orders)                    

· ORDER_IMPORT_PRIVATE

          Procedure: import_order()

                                         

Item import (Item conversion)

Always import master and child records.

          Interface tables:     

· MTL_SYSTEM_ITEMS_INTERFACE

· MTL_ITEM_REVISIONS_INTERFACE(      If importing revisions, populate)

· MTL_ITEM_CATEGORIES_INTERFACE(If importing categories, populate)

· MTL_INTERFACE_ERRORS

          Item import can be run in create mode or update mode.  

Running the Item Open Interface In Create Mode:

Populate the mtl_system_items_interface with the following minimum required columns when creating new items:

PROCESS_FLAG = 1

TRANSACTION_TYPE = 'CREATE'

SET_PROCESS_ID = 1

ORGANIZATION_ID = Master Org id.

DESCRIPTION = 'Description of the item'

ITEM_NUMBER and/or SEGMENT(n)

Note:  Using the ITEM_NUMBER column in the mtl_system_items_interface is required if you are populating revision history data into the  mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported plus the segment separator.  If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both.

          Running the Item Open Interface In Update Mode:

To update existing item(s), set TRANSACTION_TYPE = 'UPDATE'.

For best performance, use inventory_item_id when updating items.

          Functionality

Every attribute updateable from the Item form is updateable through the interface, and all required validations are performed to enforce:

· Item Attribute Interdependencies

· Master - Child Attribute Dependencies

· Status Controlled Attributes

· Templates can be applied to existing Items. For best results, use template_id and template_name.

· The Item status can be changed for existing Items, and the proper attributes are Defaulted / Set accordingly. The Status change is recorded in

MTL_PENDING_ITEM_STATUS.

· To populate material costs from IOI: Populate the LIST_PRICE_PER_UNIT column with a value while importing items and you will see your material cost for your item in MTL_SYSTEM_ITEMS after running the Item Import process. (CREATE transaction_type only)

· When launching items into the Master Item Org, the Child records are copied into MTL_SYSTEM_ITEMS_INTERFACE for validation, and are identified with transaction_type of 'AUTO_CHILD'. These records are deleted if the parameter 'Delete Processed Rows' has been passed as 'Yes', and remain for diagnostic purposes if the parameter is passed as 'No'. When the defining attribute for a Functional area is enabled, the proper default category set and category is assigned to the Item.

· Master Items were loaded before child records in MTL_SYSTEM_ITEMS.

          Not Supported Issues

=========================================

· Item Costs cannot be UPDATED (using "UPDATE" transaction_type) through the interface.

· New Item revisions cannot be added to existing Items.

· Current functionality does not support updates to a PTO MODEL ITEM through

the IOI update feature. See notes: 1076412.6 and 2121870.6 Updating Item Attributes to NULL The method to update these columns to NULL is to use the following values:

1.      for Numeric fields: insert -999999

2.      for Character fields: insert '!'

3.      for Date fields: the above list does not include any updateable date fields.

Importing Master and Child Records

==================================

          The user procedures are as follows :

1. Populate the item interface tables (mtl_system_items_interface). This step is necessary if you are creating items and categories in the same run. For importing item category assignments for already existing items, you do not need to populate item interface table.

2. Populate the item categories interface table (mtl_item_categories_interface).

The user needs to populate the following mandatory columns in item categories interface table:

A. Either inventory_item_id or item_number. When item and category are being imported together, then user can only specify the item_number, since item id will be generated by the import process.

B. Either organization_id or organization_code or both.

C. The transaction_type column should be 'CREATE'. We do not support 'UPDATE' or 'DELETE' for item category assignment.

D. Either category_set_id or category_set_name or both.

E. Either category_id or category_name or both.

F. Process_flag column as 1.

G. Populate the set_process_id column. The item and category interface records should have the same set_process_id, if you are importing item and category assignment together.

3. After populating the item and category interface tables, launch the Item Import process from the applications. In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have have any value. Through the import process, we can only create item category assignment(s).

Updation or Deletion of item category assignment is not supported.

4. Once the concurrent process completes, check the mtl_interface_errors table for any error(s) during the item and category import. Correct those error conditions in the interface tables and run the item import again. If the process_flag is 7, that means the item category interface records were successfully imported.

Revisions

==============================

Note: Using the ITEM_NUMBER column in the mtl_system_items_interface table is required if you are populating revision data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported, plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both. For historical item revision data, do NOT populate the REVISION column in the mtl_system_items_interface table. This column is used only if the current revision of the item is being imported.

Populate these columns in the mtl_item_revisions_interface table:

PROCESS_FLAG = 1

TRANSACTION_TYPE = 'CREATE'

SET_PROCESS_ID = 1

ORGANIZATION_ID = Master Org ID.

REVISION

EFFECTIVITY_DATE

IMPLEMENTATION_DATE

ITEM_NUMBER = (Must match the item_number in mtl_system_items_interface table.)

Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Run the IOI process. Navigate --> Inventory: Items: Import Items

There are 6 parameters to enter to begin the process:

1. Specify one or all organizations.

2. Validate items, yes or no.

3. Process items, yes or no.

4. Delete processed rows, yes or no.

5. Process set (null for all)

6. Create or update items (1 for create, 2 for update)

Note: If you are importing Master and Child records, insert them into the mtl_system_items_interface and mtl_item_revisions_interface tables, and run them at the same time by setting the 'All organizations' parameter to 'Yes'. If you do not do this, then the Child revision records will not be imported.

Error Checking:

======================================

When importing multiple revisions, if one record for an item fails validation, all revisions for that item fail. Resolve failed rows by checking the mtl_interface_errors table.

SELECT table_name, column_name, error_message, message_name

FROM mtl_interface_errors;

          Base tables:           

§ MTL_SYSTEM_ITEMS_B           

§ MTL_ITEM_REVISIONS_B

§ MTL_CATEGORIES_B                         

§ MTL_CATEGORY_SETS_B

§ MTL_ITEM_STATUS       

§ MTL_ITEM_TEMPLATES

Concurrent program:  Item Import

Validations:    check for valid item type.

          Check for valid part_id/segment of the source table.

          Validate part_id/segment1 for master org.

          Validate and translate template id of the source table.

Check for valid template id. (attributes are already set for items, default attributes for

that template, i.e., purchasable, stockable, etc)

          Check for valid item status.

          Validate primary uom of the source table.

          Validate attribute values.

          Validate other UOMs of the source table.

          Check for unique item type. Discard the item, if part has non-unique item type.

          Check for description, inv_um uniqueness

          Validate organization id.

Load master records and category records only if all validations are passed.

Load child record if no error found.

          Interface Tables                                            Base Tables

          MTL_SYSTEM_ITEMS_INTERFACE             MTL_SYSTEM_ITEMS

          MTL_TRANSACTIONS_INTERFACE             

          MTL_ITEM_REVISION_INTERFACE              MTL_ITEM_REVISIONS

          MTL_DEMAND_INTERFACE

          MTL_ITEM_CATEGORIES_INTERFACE                   MTL_ITEM_CATEGORIES

          MTL_CROSS_REFERENCES_INTERFACE       MTL_CROSS_REFERENCES

 

On-hand quantity

          Interface tables:      MTL_TRANSACTIONS_INTERFACE

                                      MTL_TRANSACTION_LOTS_INTERFACE      

                                      MTL_SERIAL_NUMBERS_INTERFACE

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, PROCESS_FLAG to manipulate the records in the table. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

          Base tables:   MTL_ON_HAND_QUANTITIES

                             MTL_LOT_NUMBERS   MTL_SERIAL_NUMBERS

Concurrent program:

Validations:    validate organization_id, organization_code.

                   Validate inventory item id.

                   Transaction period must be open.

Customer conversion

Interface tables:      RA_CUSTOMERS_INTERFACE_ALL   

                             RA_CUSTOMER_PROFILES_INT_ALL                    

                             RA_CONTACT_PHONES_INT_ALL

          RA_CUSTOMER_BANKS_INT_ALL

          RA_CUST_PAY_METHOD_INT_ALL

Base tables:            HZ_PARTIES           

                             HZ_CONTACTS        

                             HZ_PROFILES                    

                             HZ_LOCATIONS

Base tables for RA_CUSTOMERS_INTERFACE_ALL

                            RA_CUSTOMERS                

                            RA_ADDRESSES_ALL

                            RA_CUSTOMER_RELATIONSHIPS_ALL        

                            RA_SITE_USES_ALL

         

          Uses TCA APIs.

Concurrent program: Customer Interface

          Validations:    Check if legacy values fetched are valid.

          ;                    Check if customer address site is already created.

                             Check if customer site use is already created.

                             Check is customer header is already created.

                             Check whether the ship_to_site has associated bill_to_site

                             Check whether associated bill_to_site is created or not.

          Profile amounts validation:   validate cust_account_id, validate customer status.

Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

         

Customer API

1.      Set the organization id

Exec dbms_application_info.set_client_info(‘204’);

2.      Create a party and an account

HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()

HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE

HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE

HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

3.      Create a physical location

HZ_LOCATION_V2PUB.CREATE_LOCATION()

HZ_LOCATION_V2PUB.LOCATION_REC_TYPE

4.      Create a party site using party_id you get from step 2 and location_id from step 3.

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()

HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE

5.      Create an account site using account_id you get from step 2 and party_site_id from step 4.

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()

HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE

6.      Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.

HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()

HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE

HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base table:   

· HZ_PARTIES                

· HZ_PARTY_SITES         

· HZ_LOCATIONS

· HZ_CUST_ACCOUNTS    

· HZ_CUST_SITE_USES_ALL

· HZ_CUST_ACCT_SITES_ALL     

· HZ_PARTY_SITE_USES

Validations:    Check if legacy values fetched are valid.

                             Check if customer address site is already created.

                             Check if customer site use is already created.

                             Check is customer header is already created.

                             Check whether the ship_to_site has associated bill_to_site

                             Check whether associated bill_to_site is created or not.

          Profile amounts validation:   validate cust_account_id, validate customer status.

Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

 

 

 

 

 

 

 

Auto Invoice interface

          Interface tables:      RA_INTERFACE_LINES_ALL

Base tables:           

• RA_CUSTOMER_TRX_ALL     
• RA_BATCHES
• RA_CUSTOMER_TRX_LINES_ALL     
• AR_PAYMENT_SCHEDULES_ALL      
• RA_CUSTOMER_TRX_LINE_SALESREPS      
• RA_CUST_TRX_GL_DIST_ALL
• AR_RECEIVABLES_APPLICATIONS             
• AR_ADJUSTMENTS
• AR_CASH_RECEIPTS           
• RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program: Auto invoice master program

          Validations: check for amount, batch source name, conversion rate, conversion type.

                            Validate orig_system_bill_customer_id, orig_system_bill_address_id, quantity.

                             Validate if the amount includes tax flag.

Receipt API

AR_RECEIPT_API_PUB.CREATE_CASH

AR_RECEIPT_API_PUB.CREATE_AND_APPLY

To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.

          Base tables:   AR_CASH_RECEIPTS

Validations:    check the currency and the exchange rate type to assign the exchange rate.

                   Validate bill to the customer.

                   Get bill to site use id.

                   Get the customer trx id for this particular transaction number.

                   Get payment schedule date for the customer trx id.

Lockbox interface

          Interface tables:      AR_PAYMENTS_INTERFACE_ALL (Import data from bank file )

Base tables:            AR_INTERIM_CASH_RECEIPTS_ALL   AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)

Related Tables:        AR_BANK_ACCOUNTS_ALL    AR_RECEIPT_METHODS

AR_TRANSMISSIONS_ALL     HZ_CUST_ACCOUNTS          HZ_CUST_SITE_USES_ALL           AR_CASH_RECEIPTS

(POST QUICK CASH -- applies the receipts and updates customer balances)

Concurrent program: nav-> receivables->interfaces->lockbox

         

Validations:    check for valid record type, transmission record id.

                   Validate sum of the payments within the transmission.

                   Identify the lockbox number (no given by a bank to identify a lockbox).

AP invoice interface

Interface tables:      AP_INVOICES_INTERFACE    AP_INVOICE_LINES_INTERFACE

                            

          Base tables:            AP_INVOICES_ALL – header information

                             AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program: Payables Open Interface Import

          Validations:    check for valid vendor

                             Check for valid vendor site code.

                             Check if record already exists in payables interface table.

                  

Vendor conversion/interface

          No interface tables

          Base tables:   PO_VENDORS           PO_VENDOR_SITES_ALL

                            

No concurrent program as data is directly populated into base tables.

         

          Validations:    check if a vendor already exists with the same name as the TIMSS customer

mail name.

Check if the proper site code and id exists based on the site code from TIMSS.

Check for uppercase value of the vendor name existed in Oracle and in TIMSS, vendor name is mixed case, a new Oracle vendor will not be created.

         

Purchasing:

          Interface Tables                                  Base Tables

          PO_HEADERS_INTERFACE                         PO_HEADERS_ALL

          PO_LINES_INTERFACE                             PO_LINES_ALL

          PO_REQUISITIONS_INTERFACE_ALL PO_REQUISITIONS_HEADERS_ALL

                                                                   PO_REQUISITION_LINES_ALL

                                                                   PO_REQ_DISTRIBUTIONS_ALL

          PO_REQ_DIST_INTERFACE_ALL                 PO_REQ_DISTRIBUTIONS_ALL

          PO_DISTRIBUTIONS_INTERFACE                PO_DISTRIBUTIONS_ALL

          PO_RESCHEDULE_INTERFACE                   PO_REQUISITION_LINES_ALL

Requisition import

          Interface tables:      PO_REQUISITIONS_INTERFACE_ALL

                                      PO_REQ_DIST_INTERFACE_ALL                

          Basetables:  PO_REQUISITIONS_HEADERS_ALL                                                   

                            PO_REQUISITION_LINES_ALL     

                            PO_REQ_DISTRIBUTIONS_ALL

Concurrent program:  REQUISITION IMPORT

          Validations:    check for interface transaction source code, requisition destination type.

                             Check for quantity ordered, authorization status type.

PO Receipts Interface

          Interface tables:

· RCV_HEADERS_INTERFACE

· RCV_TRANSACTIONS_INTERFACE        

          Base tables:

· RCV_SHIPMENT_HEADERS

· RCV_SHIPMENT_LINES

· RCV_TRANSACTIONS

Concurrent program: RECEIVING OPEN INTERFACE

Error messages: 1. Run RECEIVING INTERFACE ERRORS REPORT

                      2. Look in PO_INTERFACE_ERRORS

Query to check interface errors: PO_INTERFACE_ERRORS .inteface_transaction_id =

RCV_HEADERS_INTERFACE.header_interface_id and processing_status_code in (‘error’ ,’print’)

          Validations:

GL interface

          Interface tables:      GL_INTERFACE

Base tables:           

• GL_JE_HEADERS       
• GL_JE_LINES 
• GL_JE_BACTHES

Concurrent Program: Journal Import

                             Journal Posting  --- populates GL_BALANCES

Validations:    check SOB, journal source name, journal category name, actual flag

                   A – actual amounts

                   B – budget amounts

                   E – encumbrance amount

If u enter E in the interface table, then enter appropriate encumbrance ID.

B – budget id.

Check if accounting date or GL date based period name is valid (i.e., not closed).

Check if accounting date falls in open or future open period status.

Check chart of accounts id based on Sob id.

Check if valid code combination.

Check if ccid is enabled.

Check if record already exists in GL interface table.

Check if already journal exists in GL application.

Validations for the staging table:

Check if the input data file is already uploaded into staging table.

                   Check if the record already exists in the interface table.

Check if the journal already exists in the GL application.  

GL budget interface

          Interface tables: GL_BUDGET_INTERFACE

          Base tables:           

• GL_BUDGETS 
• GL_BUDGET_ASSIGNMENTS 
• GL_BUDGET_TYPES

Concurrent program: Budget Upload

Validations: Check Account combination is valid or not. You check this in GL_CODE_COMBINATIONS table.

 GL daily conversion rates

          Interface tables:      GL_DAILY_RATES_INTERFACE

           Base tables:           

· GL_DAILY_RATES               

· GL_DAILY_CONVERSION_TYPES

                Concurrent Program: Program - Daily Rates Import and Calculation