Link between po and xla tables.po to gl query in r12

Link between po and xla tables.po to gl query in r12

This below query providing all information's from Po to GL. This is the complete XLA information's from PO and XLA tables. In this query you can get Link between po and xla and po to gl query in r12

Join between po and gl

 

 

select

poh.segment1 po_number, pol.line_num,

nvl(poll.price_override,pol.unit_price) Unit_price, pod.quantity_ordered, pod.quantity_delivered, pod.quantity_billed, pod.quantity_cancelled,

xdl.ACCOUNTING_LINE_CODE, XDL.LINE_DEFINITION_CODE, XDL.EVENT_CLASS_CODE,poh.org_id,poll.shipment_num, por.release_num, pod.distribution_num,

xal.accounted_dr, xal.accounted_cr,

jeh.ledger_id, jeb.name, jeb.creation_date, jeh.je_source, jeh.je_category,

glcc.segment1, glcc.segment2, glcc.segment3, glcc.segment4, glcc.segment5, glcc.segment6,

jel.je_line_num, nvl(jel.accounted_dr,0) accounted_dr, nvl(jel.accounted_cr,0) accounted_cr

from

apps.po_headers_all poh,

apps.po_lines_all pol,

apps.po_line_locations_all poll,

apps.po_distributions_all pod,

apps.po_releases_all por,

apps.xla_distribution_links xdl,

apps.xla_ae_lines xal,

APPS.GL_IMPORT_REFERENCES gif,

apps.gl_je_lines jel,

apps.gl_je_headers jeh,

apps.gl_je_batches jeb,

apps.gl_code_combinations glcc

where 1=1

and xdl.AE_LINE_NUM = xal.AE_LINE_NUM

AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

and xal.gl_sl_link_table = gif.gl_sl_link_table

and xal.gl_sl_link_id = gif.gl_sl_link_id

and gif.je_header_id = jel.je_header_id

and gif.je_line_num = jel.je_line_num

and jel.code_combination_id = glcc.code_combination_id

and jel.je_header_id = jeh.je_header_id

and jeh.je_batch_id = jeb.je_batch_id

AND XDL.SOURCE_DISTRIBUTION_TYPE ='PO_DISTRIBUTIONS_ALL'

and poh.segment1='1234444' --PO NUMBER

AND XDL.APPLICATION_ID = 201

AND pod.po_distribution_id = XDL.SOURCE_DISTRIBUTION_ID_NUM_1

and pod.line_location_id = poll.line_location_id

and poll.po_line_id = pol.po_line_id

and pol.po_header_id = poh.po_header_id

and pod.po_release_id = por.po_release_id(+)

Link between po and xla tables.po to gl query in r12

link between ar and gl in oracle apps r12.link between ar and xla tables

Link between AR and GL in oracle apps r12.link between AR and XLA tables

 

 

AR to GL flow in oracle apps r12

In this post , i will share the XML linking between all the oracle modules to GL. We all know that all the Data flows from Different modules to General Ledger through XLA. XLA is the only link between GL and other sub-ledger modules.

link between ar and xla tables

SELECT GLB.NAME ,

GLB.description ,

gjh.je_category,

gjh.je_source,

gjh.period_name ,

gjh.NAME ,

gjh.status ,

gjh.description ,

gjl.je_line_num

FROM gl_je_batches GLB,

gl_je_headers gjh,

gl_je_lines gjl,

gl_code_combinations_kfv glcc,

gl_import_references gir,

xla_ae_lines xlal,

xla_ae_headers xlah,

xla_events xlae,

xla_transaction_entities xlate,

ra_customer_trx_all rct

WHERE GLB.je_batch_id = gjh.je_batch_id

AND gjh.je_header_id = gjl.je_header_id

AND xlal.code_combination_id = glcc.code_combination_id

AND gjl.je_header_id = gir.je_header_id

AND gjl.je_line_num = gir.je_line_num

AND gir.gl_sl_link_table = xlal.gl_sl_link_table

AND gir.gl_sl_link_id = xlal.gl_sl_link_id

AND xlal.ae_header_id = xlah.ae_header_id

AND xlah.event_id = xlae.event_id

AND xlae.entity_id = xlate.entity_id

AND xlae.application_id = xlate.application_id

AND gjh.je_source = 'Receivables'

AND rct.trx_number = xlate.transaction_number

AND xlate.transaction_number = :trx_number;

AND gcc.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID

AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID

AND gjh.STATUS = 'P'

AND gjh.Actual_flag = 'A'