Wednesday, May 22, 2013

Inventory Subledger Accounting (SLA) Data Flow Chart

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 ()
 9.select * from gl_je_lines where je_header_id in () and je_line_num in ('')
 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'))

3 comments:

RAHALIYAS ASLAM said...

Thanks a lot..... God bless...

JAGS said...

Great job. It helped me a lot.

thank you so much...

Regards,
Jagadeesh. A

JAGS said...

Great Job. It helped me a lot.

Thank you so much...

Regards,
Jagadeesh. A