SLA Data Flow and Table Links

 GOAL : How does the data flow from Inventory (Material transactions coming from Inventory,WIP, and Receiving) to General Ledger(GL)? 

What are the tables that are involved?

SOLUTION : 

Subledger Accounting (SLA) Data Flow Chart

 

Important columns affected:

After a transaction is performed in forms:
mtl_material_transactions.costed_flag = 'N'

After the Cost Manager Picks up the data and processes it:
mtl_material_transactions.costed_flag is Null
xla_events.event_status_code = 'U'
xla_events.process_status_code = 'U'

After the Create Accounting - Cost Management is run:
xla_events.event_status_code = 'P'
xla_events.process_status_code = 'P'
xla_ae_headers.gl_transfer_status_code = 'N'
xla_ae_headers.gl_transfer_date is Null

After the Transfer To GL is run:
xla_ae_headers.gl_transfer_status_code = 'Y'
xla_ae_headers.gl_transfer_date is Not Null


Queries involved:

  1. select * from mtl_material_transactions where transaction_id = '&transaction_id'
  2. select * from mtl_transaction_accounts where transaction_id = '&transaction_id'
  3. select * from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id'
  4. select * from xla_events where entity_id in (select entity_id from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id')
  5. select * from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select inv_sub_ledger_id from mtl_transaction_accounts where transaction_id = '&txnid')
  6. select * from xla_ae_headers where ae_header_id in (select ae_header_id from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select inv_sub_ledger_id from mtl_transaction_accounts where transaction_id = '&txnid'))
  7. select * from xla_ae_lines where ae_header_id in (select ae_header_id from xla_distribution_links where source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and source_distribution_id_num_1 in (select inv_sub_ledger_id from mtl_transaction_accounts where transaction_id = '&txnid'))
  8. select * from gl_import_references where gl_sl_link_table = 'XLAJEL' and gl_sl_link_id in (<give the gl_sl_link_id from result of query 7>)
  9. select * from gl_je_lines where je_header_id in (<give the je_header_id from result of query 8>) and je_line_num in ('<result from query 8>')
  10. select * from xla_accounting_errors where event_id in (select event_id from xla_events where entity_id in (select entity_id from XLA_TRANSACTION_ENTITIES_upg where source_id_int_1 = '&transaction_id'))

 

Comments

Post a Comment

Popular posts from this blog

Oracle Subledger Accounting (SLA) Tables, Views

Oracle Subledger Accounting Tables: TABLE NAME DESCRIPTION XLA_AAD_GROUPS The XLA_AAD_GROUPS table stores the merge dependencies analyzed during the merge analysis.  All application accounting definitions with the same GROUP_NUM must be merged together. XLA_AAD_HDR_ACCT_ATTRS The XLA_AAD_HDR_ACCT_ATTRS stores standard, system and custom sources assigned to an accounting attribute at the AAD level. XLA_AAD_HEADER_AC_ASSGNS Store the analytical criteria for the application accounting definitions. XLA_AAD_LINE_DEFN_ASSGNS This table stores the journal lines definitions for the application accounting definitions. XLA_AAD_LOADER_DEFNS_T The XLA_AAD_LOADER_DEFNS_T table is the interface table that facilitates the data transfer from data files and the database. XLA_AAD_LOADER_LOGS The XLA_AAD_LOADER_LOGS table stores the errors and logs generated by the application accounting definitions loader. XLA_AAD_SOURCES XLA_AAD_SOURCES table stores a list of sources used by an Application Accounti...
Read more

Understanding Item Import and Debugging Problems with Item Import (Doc ID 268968.1)

  PURPOSE Item Import Overview ~~~~~~~~~~~~~~~~~~~~~~~~~ Use the item import process to import items from your legacy system or PDM system. You can import items from any source into Oracle Inventory and Oracle Engineering. When you import items through the Item Interface, you create new items in your Item Master organization or assign existing items to additional organizations. You can specify values for all the item attributes, or you can specify just a few attributes and let the remainder default or remain Null. You can also specify an item template for each item and inherit attribute values from the template. The Item Interface also lets you import revision details, including past and future revisions and effectivity dates. Validation of imported items is done using the same rules as the item definition forms, so you are insured of valid items. The Item Interface reads data from two tables for importing items and item details. You use the MTL_SYSTEMS_ITEM_INTERFACE table for new...
Read more

Invoices in the status of 'Selected for Validation' after cancelling Invoice Validation Program

Invoices in the status of 'Selected for Validation' after cancelling Invoice Validation Program Here we can segregate this issue into 2 Parts. 1. Invoices in status of Selected for validation when cancelled Invoice Validation Program it will be stamped with Validation_request_id. 2. Invoice Validation program keep on running from hours together and want to cancel the Request When ever we cancel the invoice validation program when its running, it should roolback all the invoices back to its normal status but some/many times it will not rollback the invoices and it will stamp the cancelled request id on all the invoices under column VALIDATION_REQUEST_ID of AP_INVOICE_HEADERS_ALL. What ever the invoices stamped with request_id can be get with this query to know the count or list of invoices. SQL: select * from ap_invoices_all where validation_request_id = 'XXXXX';-- or where validation_request_id is not null; Once we extract these invoices we need to fix the invoices with...
Read more