Drill Down Oracle JV R12

To Drill down Oracle JV, we have to follow the below steps:

1. a) In R12, the link between GL and any subledger is via XLA.
2. b) GL tables WILL NO longer be storing any subledger specific information

like invoice_id/date etc onto the GL_JE_LINES/GL_JE_HEADERS etc. Max

information transferred between subledger to GL is the Doc Sequence Number

1. c) Any reconciliation between XLA-GL is via GL_SL_LINK_ID and

GL_SL_LINK_TABLE. these 2 columns together form a unique pair to join any row

of GL to XLA_AE_LINES.

XLA_AE_LINES.GL_SL_LINK_ID = GL_IMPORT_REFERENCES.GL_SL_LINK_ID and

XLA_AE_LINES.GL_SL_LINK_TABLE= GL_IMPORT_REFERENCES.GL_SL_LINK_TABLE

.

Now if the posting is in summary, the GL_SL_LINK_ID is available in

GL_IMPORT_REFERENCES only. If its detailed mode transfer then it’s available

in BOTH GL_IMPORT_REFERENCES and GL_JE_LINES. In any case, if the data is

coming via XLA, then GL_IMPORT_REFERENCES will always have these rows

populated.

.

So all your reconciliation has to follow the following route:

.

1) GL_JE_LINES & GL_IMPORT_REFERENCES

2) GL_IMPORT_REFERENCES & XLA_AE_LINES

3) XLA_AE_LINES/XLA_DISTRIBUTION_LINKS & Subledger distribution tables

or

XLA_AE_LINES/XLA_AE_HEADERS/XLA_EVENTS & Subledger events table.

 

Subledger Distribution Tables:

XLA_DISTRIBUTION_LINKS —-

If you find a row with SOURCE_DISTRIBUTION_TYPE as AP_INV_DIST in the XLA_DISTRIBUTION_LINKS table then check the SOURCE_DISTRIBUTION_ID_NUM_1 which would be

the INVOICE_DISTRIBUTION_ID in the AP_INVOICE_DISTRIBUTIONS_ALL and from there you can get the INVOICE_ID.

XLA_DISTRIBUTION_LINKS —-

If you find a SOURCE_DISTRIBUTION_TYPE as AP_PMT_DIST in the XLA_DISTRIBUTION_LINKS table then check

the SOURCE_DISTRIBUTION_ID_NUM_1 which would be the PAYMENT_HIST_DIST_ID in the

AP_PAYMENT_HIST_DISTS and from there you can find out the INVOICE_PAYMENT_ID and

go to AP_INVOICE_PAYMENTS_ALL and find the CHECK_ID.

For Example

select * from

GL_JE_Headers GJH

where GJH.je_header_id=:p_je_header_id

select * from

GL_JE_LINES GJL

where GJL.je_header_id=:p_je_header_id

select GIR.gl_sl_link_id,GIR.* from

GL_IMPORT_REFERENCES GIR

where GIR.je_header_id=:p_je_header_id

select XAL.ae_header_id,XAL.* from

XLA_AE_LINES XAL

where XAL.gl_sl_link_id=:p_gl_sl_link_id

select XDL.SOURCE_DISTRIBUTION_ID_NUM_1,XDL.* from

XLA_DISTRIBUTION_LINKS XDL

where XDL.ae_header_id=:p_ae_header_id

select aida.invoice_id,aida.* from

ap_invoice_distributions_all aida

where aida.INVOICE_DISTRIBUTION_ID=: p_invoice_id

select * from

ap_invoices_all aia

where aia.invoice_id=:p_invoice_id

Drill Down Oracle JV from AP

Complete query to get invoice and payment drill down

SELECT A.LEGAL_ENTITY  , A.NAME    , A.JE_LINE_NUM    , A.ACCOUNTED_DR    , A.ACCOUNTED_CR    , A.OPERATING_UNT    , A.entity_code DOCUENNT_CODE    , A.VENDOR_NAME    ,

A.DOCUMENT_NUM    , A.DOCUMENT_DATE    , A.ACCOUNTING_DATE    , A.ACCOUNTED_DR_SL    , A.ACCOUNTED_CR_SL   , A.GL_ACCT

, A.NA_Account FROM

(SELECT   xep.NAME legal_entity, gjh.NAME, gjl.je_line_num, gjl.accounted_dr,

gjl.accounted_cr, hou.NAME operating_unt, hou.organization_id,

xlate.entity_code,  pov.vendor_name,

ap.invoice_num document_num, ap.invoice_date document_date, xlal.accounting_date,

xlal.accounted_dr accounted_dr_sl, xlal.accounted_cr accounted_cr_sl,

gl.segment1

|| ‘.’

|| gl.segment2

|| ‘.’

|| gl.segment3

|| ‘.’

|| gl.segment4

|| ‘.’

|| gl.segment5

|| ‘.’

|| gl.segment6

|| ‘.’

|| gl.segment7

|| ‘.’

|| gl.segment8 gl_acct

FROM gl_je_headers gjh,

gl_je_lines gjl,

gl_import_references gir,

xla.xla_transaction_entities xlate,

ap_invoices_all ap,

xla_events xlae,

xla_ae_lines xlal,

xla_ae_headers xlah,

gl_code_combinations gl,

po_vendors pov,

hr_operating_units hou,

xle_entity_profiles xep

WHERE xlate.application_id = 200

AND xlate.source_id_int_1 = ap.invoice_id

AND pov.vendor_id = ap.vendor_id

AND gl.code_combination_id = xlal.code_combination_id

AND xlal.ae_header_id = xlah.ae_header_id

AND xlate.entity_id = xlae.entity_id

AND xlah.event_id = xlae.event_id

AND xlae.application_id = xlate.application_id

AND xlate.entity_code = ‘AP_INVOICES’

AND ap.org_id = hou.organization_id

AND xlal.gl_sl_link_id = gir.gl_sl_link_id

AND xlal.gl_sl_link_table = gir.gl_sl_link_table

AND gjh.je_header_id = gir.je_header_id

AND gjh.je_header_id = gjl.je_header_id

AND gir.je_line_num = gjl.je_line_num

AND xep.legal_entity_id = hou.default_legal_context_id

AND hou.SET_OF_BOOKS_ID LIKE :p_leg_id

AND TRUNC (xlal.accounting_date) BETWEEN :p_date_from AND :p_date_to

–and gl.SEGMENT1=:p_seg_one

UNION ALL

SELECT   xep.NAME legal_entity, gjh.NAME, gjl.je_line_num, gjl.accounted_dr,

gjl.accounted_cr, hou.NAME operating_unit, hou.organization_id,

xlate.entity_code, pov.vendor_name,

TO_CHAR (aca.check_number) documnet_num, aca.check_date document_date, xlal.accounting_date,

xlal.accounted_dr accounted_dr_sl, xlal.accounted_cr accounted_cr_sl,

gl.segment1

|| ‘.’

|| gl.segment2

|| ‘.’

|| gl.segment3

|| ‘.’

|| gl.segment4

|| ‘.’

|| gl.segment5

|| ‘.’

|| gl.segment6

|| ‘.’

|| gl.segment7

|| ‘.’

|| gl.segment8 gl_acct

FROM gl_import_references gir,

xla.xla_transaction_entities xlate,

ap_checks_all aca,

xla_events xlae,

xla_ae_lines xlal,

xla_ae_headers xlah,

gl_je_headers gjh,

gl_je_lines gjl,

gl_code_combinations gl,

po_vendors pov,

hr_operating_units hou,

xle_entity_profiles xep

WHERE xlate.application_id = 200

AND xlate.source_id_int_1 = aca.check_id

AND pov.vendor_id = aca.vendor_id

AND gl.code_combination_id = xlal.code_combination_id

AND xlal.ae_header_id = xlah.ae_header_id

AND xlate.entity_id = xlae.entity_id

AND xlah.event_id = xlae.event_id

AND xlae.application_id = xlate.application_id

AND xlate.entity_code = ‘AP_PAYMENTS’

AND hou.organization_id = aca.org_id

AND hou.default_legal_context_id = xep.legal_entity_id

AND hou.SET_OF_BOOKS_ID LIKE :p_leg_id

AND xlal.gl_sl_link_id = gir.gl_sl_link_id

AND xlal.gl_sl_link_table = gir.gl_sl_link_table

AND gjh.je_header_id = gir.je_header_id

AND gjh.je_header_id = gjl.je_header_id

AND gir.je_line_num = gjl.je_line_num

AND TRUNC (xlal.accounting_date) BETWEEN :p_date_from AND :p_date_to

–and gl.SEGMENT1=:p_seg_one

) A

ORDER BY 1, 3

 

Drill Down Oracle JV from AR

Similaralry Oracle AR invoice and bank receipts drill down

select gl.SEGMENT6,rcta.TRX_NUMBER,gjh.name ,gjl.je_line_num, gjl.accounted_dr, gjl.accounted_cr, hzp.party_name,hzc.ACCOUNT_NUMBER,xlal.ACCOUNTED_DR, xlal.ACCOUNTED_CR,xlae.EVENT_TYPE_CODE,xlate.SOURCE_ID_INT_1

,gl.SEGMENT1||’.’||gl.SEGMENT2||’.’||gl.SEGMENT3||’.’||gl.SEGMENT4||’.’||gl.SEGMENT5||’.’||gl.SEGMENT6||’.’||gl.SEGMENT7||’.’||gl.SEGMENT8 GL_ACCT

from gl_je_headers gjh ,gl_je_lines gjl , GL_IMPORT_REFERENCES gir,ra_customer_trx_all rcta , hr_operating_units hou

,xla.xla_transaction_entities xlate,xla_events xlae,xla_ae_lines xlal,xla_ae_headers xlah

, gl_code_combinations gl, hz_cust_accounts hzc, hz_parties hzp

where rcta.ORG_ID=hou.ORGANIZATION_ID

and hzc.CUST_ACCOUNT_ID=rcta.BILL_TO_CUSTOMER_ID

and hzc.PARTY_ID=hzp.PARTY_ID

and xlate.SOURCE_ID_INT_1=rcta.CUSTOMER_TRX_ID

AND RCTa.TRX_NUMBER=XLATE.TRANSACTION_NUMBER

and xlate.APPLICATION_ID=222

AND XLAL.AE_HEADER_ID=XLAH.AE_HEADER_ID

AND XLATE.ENTITY_ID=XLAE.ENTITY_ID

and xlae.ENTITY_ID=xlate.ENTITY_ID

AND xlah.event_id = xlae.event_id

and xlah.ENTITY_ID=xlae.ENTITY_ID

and XLAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID

and XLAL.GL_SL_LINK_TABLE= GIR.GL_SL_LINK_TABLE

and gjh.JE_HEADER_ID=gir.JE_HEADER_ID

and gjh.je_header_id=gjl.JE_HEADER_ID

and gir.JE_LINE_NUM=gjl.JE_LINE_NUM

AND xlae.application_id = xlate.application_id

and gl.CODE_COMBINATION_ID=xlal.CODE_COMBINATION_ID

and trunc(xlal.ACCOUNTING_DATE) between ’01-APR-16′ and ’30-APR-2016′

–and gl.SEGMENT1=’03’

union all

select gl.SEGMENT6,arc.RECEIPT_NUMBER,gjh.name ,gjl.je_line_num, gjl.accounted_dr, gjl.accounted_cr, hzp.party_name,  hzc.ACCOUNT_NUMBER, xlal.ACCOUNTED_DR, xlal.ACCOUNTED_CR,xlae.EVENT_TYPE_CODE,xlate.SOURCE_ID_INT_1

,gl.SEGMENT1||’.’||gl.SEGMENT2||’.’||gl.SEGMENT3||’.’||gl.SEGMENT4||’.’||gl.SEGMENT5||’.’||gl.SEGMENT6||’.’||gl.SEGMENT7||’.’||gl.SEGMENT8 GL_ACCT

from gl_je_headers gjh ,gl_je_lines gjl , GL_IMPORT_REFERENCES gir,ar_cash_receipts_all arc,  hr_operating_units hou

,xla.xla_transaction_entities xlate,xla_events xlae,xla_ae_lines xlal,xla_ae_headers xlah

, gl_code_combinations gl, hz_cust_accounts hzc, hz_parties hzp

where arc.ORG_ID=hou.ORGANIZATION_ID

and hzc.CUST_ACCOUNT_ID=arc.PAY_FROM_CUSTOMER

and hzc.PARTY_ID=hzp.PARTY_ID

and xlate.SOURCE_ID_INT_1=arc.CASH_RECEIPT_ID

AND arc.RECEIPT_NUMBER=XLATE.TRANSACTION_NUMBER

and xlate.APPLICATION_ID=222

and XLAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID

and XLAL.GL_SL_LINK_TABLE= GIR.GL_SL_LINK_TABLE

and gjh.JE_HEADER_ID=gir.JE_HEADER_ID

and gjh.je_header_id=gjl.JE_HEADER_ID

and gir.JE_LINE_NUM=gjl.JE_LINE_NUM

AND XLAL.AE_HEADER_ID=XLAH.AE_HEADER_ID

AND XLATE.ENTITY_ID=XLAE.ENTITY_ID

and xlae.ENTITY_ID=xlate.ENTITY_ID

AND xlah.event_id = xlae.event_id

and xlah.ENTITY_ID=xlae.ENTITY_ID

AND xlae.application_id = xlate.application_id

and gl.CODE_COMBINATION_ID=xlal.CODE_COMBINATION_ID

and trunc(xlal.ACCOUNTING_DATE) between  :p_date_from AND :p_date_to

and gl.SEGMENT1=:p_seg_one

IF you want to develop your blog similar to this one please download my eBook.

Please do not forget to share knowledge, share this on your social network. Hope you subscribe for email newsletter as well, thanks.

Any questions? or Suggestion leaves a comment below.