When I tried to understand the flow of data between PO transactions to GL, I found the below and sharing the same. All the queries used below were tested in R12.1.1 instance.
Collecting the Receiving data related to PO
select rrsl.rcv_sub_ledger_id -- Connects to SLA tables
,rrsl.rcv_transaction_id
,rrsl.je_source_name
,rrsl.code_combination_id
,rrsl.accounted_dr
,rrsl.accounted_cr
,rrsl.reference1
,rrsl.reference2 PO_HEADER_ID
,rrsl.reference3 PO_DISTRIBUTION_ID
,rt.transaction_id
,rt.transaction_type
,rt.po_header_id
,rt.po_release_id
,rt.po_line_id
,rt.po_line_location_id
,pd.code_combination_id
FROM rcv_receiving_sub_ledger rrsl
,rcv_transactions rt
,po_distributions_all pd
WHERE pd.po_distribution_id = rt.PO_DISTRIBUTION_ID
AND rt.transaction_id = rrsl.rcv_transaction_id
AND rt.PO_DISTRIBUTION_ID = rrsl.reference3
AND rt.po_header_id = 500;
Collecting the data from XLA
In R12, we have XLA tables that holds link between any sub ledger data to General ledger data.
The below query gives you the important columns available in the XLA tables and GL linking columns for the above RCV_SUB_LEDGER_ID
SELECT xal.gl_sl_link_id -- Connects to GL tables
,xal.gl_sl_link_table -- Connects to GL tables
,xdl.source_dIstribution_id_num_1 rcv_sub_ledger_id
,xdl.line_definition_code
,xdl.event_class_code
,xdl.event_type_code
,xah.ae_header_id
,xah.event_id
,xah.entity_id
,xah.event_type_code
,xah.description
,xal.ae_line_num
,xal.code_combination_id
,xal.accounting_class_code
FROM xla_events xev
,xla_ae_headers xah
,xla_ae_lines xal
,XLA_DISTRIBUTION_LINKS xdl
WHERE xah.application_id = xev.application_id
AND xev.event_id = xah.event_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xev.application_id = xdl.application_id
AND xal.ae_line_num = xdl.ae_line_num
AND xah.ae_header_id = xdl.ae_header_id
AND xdl.SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
AND xdl.source_dIstribution_id_num_1 = 3002;
Collecting the data from GL
Below query collects the data from GL.
In the below query, you can notice the linking columns in the first five places and others were just important ones.
SELECT glimp.reference_5 ENTITY_ID
,glimp.reference_6 EVENT_ID
,glimp.reference_7 AE_HEADER_ID
,glimp.gl_sl_link_id
,glimp.gl_sl_link_table
,glb.je_batch_id
,glb.name
,glb.status
,glb.description
,glh.je_header_id
,glh.je_category
,glh.je_source
,glh.name
,glh.description
,glh.running_total_accounted_cr
,glh.running_total_accounted_dr
,gll.je_line_num
,gll.code_combination_id
FROM gl_import_references glimp
,gl_je_batches glb
,gl_je_headers glh
,gl_je_lines gll
WHERE glimp.je_header_id = glh.je_header_id
AND glimp.je_line_num = gll.je_line_num
AND glimp.je_batch_id = glb.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND glh.je_batch_id = glb.je_batch_id
AND glimp.gl_sl_link_id = 245133
AND glimp.gl_sl_link_table = 'XLAJEL';
6 comments:
I’m no more certain where you’re obtaining your data.
.. I must spend an afternoon understanding more or perhaps
identifying much more.
Also visit my web-site : Genital Wart Remover
We do trust every one of the principles you’ve introduced in your post.
They may be pretty persuasive and will certainly perform. Nevertheless,
the discussions are extremely simple for starters. May you actually
be sure to expand these people a tad via subsequent period?
Information write-up.
Visit my site ... Young Driver Car Insurance
You help it become search really easy together with your
business presentation, even so was getting that make a difference to
be in fact a very important factor that we really feel I'd certainly not understand. It seems like too intricate and extremely in depth to me. I’m taking a look forward on your own subsequent web site, I'll make an effort to obtain the hang of it!
Check out my webpage ; Genital Warts Home Remedy
Hello colleagues, nice piece of writing and fastidious urging commented at this place,
I am really enjoying by these.
my website - www.tedxyse.com
Its like you read my mind! You seem to know so much about this, like you wrote the book in
it or something. I think that you could do with some pics
to drive the message home a bit, but other than that, this is great blog.
A great read. I will definitely be back.
Feel free to visit my weblog; Air Max
You've made some good points there. I checked on the net for more information about the issue and found most individuals will go along with your views on this site.
Here is my webpage - Louis Vuitton Handbags
Post a Comment