Aim:
The below is the query for Account analysis drill down report for the Accural Account and it provides the detailed information with GL and PO Details in Oracle 11i.
Script :
SELECT gh.je_header_id, gl.je_line_num, gl.period_name, gh.NAME journal_name,
gb.NAME batch_name, gh.je_source journal_source,
gh.je_category journal_category, gcc.segment1, gcc.segment2,
gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7,
TO_CHAR (gh.default_effective_date, ‘DD-MON-YYYY’) gl_date,
TO_CHAR (gh.posted_date, ‘DD-MON-YYYY’) posted_date, gl.description,
gcc.segment1
|| ‘-‘
|| gcc.segment2
|| ‘-‘
|| gcc.segment3
|| ‘-‘
|| gcc.segment4
|| ‘-‘
|| gcc.segment5
|| ‘-‘
|| gcc.segment6
|| ‘-‘
|| gcc.segment7 ACCOUNT,
gh.currency_code, mta.transaction_id transaction_id,
mta.transaction_date transaction_date, msi.segment1 item,
v.vendor_name, NULL invoice_num, NULL invoice_date, poh.segment1 po_number,
NULL receipt_number,
DECODE (SIGN (mta.transaction_value),
1, mta.transaction_value,
0, 0,
NULL, DECODE (SIGN (mta.base_transaction_value),
1, mta.base_transaction_value,
NULL
)
) entered_dr,
DECODE (SIGN (mta.transaction_value),
-1, (-1 * mta.transaction_value),
0, 0,
NULL, DECODE (SIGN (mta.base_transaction_value),
-1, (-1 * mta.base_transaction_value)
)
) entered_cr,
DECODE (SIGN (mta.base_transaction_value),
1, mta.base_transaction_value,
0, 0,
NULL
) accounted_dr,
DECODE (SIGN (mta.base_transaction_value),
-1, (-1 * mta.base_transaction_value),
0, 0,
NULL
) accounted_cr
FROM inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr,
po_headers_all poh,
po_vendors v
WHERE mmt.organization_id = msi.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND mmt.transaction_id = mta.transaction_id
AND gcc.code_combination_id = mta.reference_account
AND mtt.transaction_type_id = mmt.transaction_type_id
AND gb.je_batch_id = gh.je_batch_id
AND gh.je_header_id = gl.je_header_id
AND gl.code_combination_id = mta.reference_account
AND mta.gl_batch_id =
TO_NUMBER (SUBSTR (gb.NAME, 1, INSTR (gb.NAME, ‘ ‘) – 1))
AND gh.je_category = ‘MTL’
AND gh.je_source = ‘Inventory’
AND gl.je_line_num = gr.je_line_num
AND gr.je_header_id = gl.je_header_id
AND gr.je_line_num = gl.je_line_num
AND mta.gl_batch_id = gr.reference_1
AND gh.period_name = :period_name — ENTER THE PERIOD
AND mmt.organization_id = ‘111’
AND gl.set_of_books_id = ‘1002’
AND gcc.segment1 = :seg1
AND gcc.segment2 = :seg2
AND gcc.segment3 = :seg3
AND gcc.segment4 = :seg4
AND gcc.segment5 = :seg5
AND gcc.segment6 = :seg6
AND gcc.segment7 = :seg7
AND mmt.currency_code = gh.currency_code
AND poh.po_header_id = mmt.transaction_source_id
AND v.vendor_id(+) = poh.vendor_id
UNION ALL
SELECT /*+use_nl(cc1 v r jh ael jl)*/
jl.je_header_id, jl.je_line_num, jh.period_name, jh.NAME journal_name,
jb.NAME batch_name, jh.je_source journal_source,
jh.je_category journal_category, cc1.segment1, cc1.segment2,
cc1.segment3, cc1.segment4, cc1.segment5, cc1.segment6, cc1.segment7,
TO_CHAR (jh.default_effective_date, ‘DD-MON-YYYY’) gl_date,
TO_CHAR (jh.posted_date, ‘DD-MON-YYYY’) posted_date, jl.description,
cc1.segment1
|| ‘-‘
|| cc1.segment2
|| ‘-‘
|| cc1.segment3
|| ‘-‘
|| cc1.segment4
|| ‘-‘
|| cc1.segment5
|| ‘-‘
|| cc1.segment6
|| ‘-‘
|| cc1.segment7 ACCOUNT,
jh.currency_code, NULL transaction_id, NULL transaction_date,
NULL item, v.vendor_name, i.invoice_num, i.invoice_date,
NULL po_number, NULL receipt_number, ael.entered_dr, ael.entered_cr,
ael.accounted_dr, ael.accounted_cr
FROM gl_je_batches jb,
gl_je_lines jl,
gl_je_headers jh,
gl_code_combinations cc1,
gl_import_references r,
ap_ae_lines_all ael,
po_vendors v,
ap_invoices_all i
WHERE jb.je_batch_id = jh.je_batch_id
AND jl.code_combination_id = cc1.code_combination_id
AND jh.je_header_id = r.je_header_id
AND jl.je_header_id = r.je_header_id
AND jl.je_header_id = jh.je_header_id
AND jl.je_line_num = r.je_line_num
AND r.gl_sl_link_id = ael.gl_sl_link_id
AND r.gl_sl_link_table = ‘APECL’
AND ael.source_table = ‘AP_INVOICE_PAYMENTS’
AND i.vendor_id = v.vendor_id
AND TO_CHAR (ael.reference2) = i.invoice_id
AND cc1.segment1 = :seg1
AND cc1.segment2 = :seg2
AND cc1.segment3 = :seg3
AND cc1.segment4 = :seg4
AND cc1.segment5 = :seg5
AND cc1.segment6 = :seg6
AND cc1.segment7 = :seg7
AND jh.period_name = :period_name
AND i.invoice_currency_code = jh.currency_code
AND jl.set_of_books_id = ‘1002’
UNION ALL
SELECT /*+use_nl(cc1 v r jh ael jl i id)*/
jl.je_header_id, jl.je_line_num, jh.period_name, jh.NAME journal_name,
jb.NAME batch_name, jh.je_source journal_source,
jh.je_category journal_category, cc1.segment1, cc1.segment2,
cc1.segment3, cc1.segment4, cc1.segment5, cc1.segment6, cc1.segment7,
TO_CHAR (jh.default_effective_date, ‘DD-MON-YYYY’) gl_date,
TO_CHAR (jh.posted_date, ‘DD-MON-YYYY’) posted_date, jl.description,
cc1.segment1
|| ‘-‘
|| cc1.segment2
|| ‘-‘
|| cc1.segment3
|| ‘-‘
|| cc1.segment4
|| ‘-‘
|| cc1.segment5
|| ‘-‘
|| cc1.segment6
|| ‘-‘
|| cc1.segment7 ACCOUNT,
jh.currency_code, NULL transaction_id, NULL transaction_date,
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = pl.item_id AND organization_id = 111) item,
v.vendor_name, i.invoice_num, i.invoice_date, ph.segment1 po_number,
NULL receipt_number, ael.entered_dr, ael.entered_cr, ael.accounted_dr,
ael.accounted_cr
FROM gl_je_batches jb,
gl_je_lines jl,
gl_je_headers jh,
gl_code_combinations cc1,
gl_import_references r,
ap_ae_lines_all ael,
ap_invoice_distributions_all ID,
po_distributions_all pd,
ap_batches_all b,
ap_invoices_all i,
po_headers_all ph,
po_lines_all pl,
po_vendors v
WHERE jb.je_batch_id = jh.je_batch_id
AND jl.code_combination_id = cc1.code_combination_id
AND jl.je_header_id = r.je_header_id
AND jl.je_header_id = jh.je_header_id
AND jh.je_header_id = r.je_header_id
AND jl.je_line_num = r.je_line_num
AND r.gl_sl_link_id = ael.gl_sl_link_id
AND r.gl_sl_link_table = ‘APECL’
AND ael.source_table = ‘AP_INVOICE_DISTRIBUTIONS’
AND ael.source_id = ID.invoice_distribution_id
AND ID.po_distribution_id = pd.po_distribution_id(+)
AND pd.po_header_id = ph.po_header_id(+)
AND ID.invoice_id = i.invoice_id
AND i.vendor_id = v.vendor_id
AND i.batch_id = b.batch_id
AND jh.period_name = :period_name
AND cc1.segment1 = :seg1
AND cc1.segment2 = :seg2
AND cc1.segment3 = :seg3
AND cc1.segment4 = :seg4
AND cc1.segment5 = :seg5
AND cc1.segment6 = :seg6
AND cc1.segment7 = :seg7
AND jh.period_name = :period_name
AND i.invoice_currency_code = jh.currency_code
AND jl.set_of_books_id = ‘1002’
AND ph.org_id = i.org_id
AND ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pd.po_line_id
UNION ALL
SELECT /*+use_nl(cc1 v r jh ael jl i id)*/
jl.je_header_id, jl.je_line_num, jh.period_name, jh.NAME journal_name,
jb.NAME batch_name, jh.je_source journal_source,
jh.je_category journal_category, cc1.segment1, cc1.segment2,
cc1.segment3, cc1.segment4, cc1.segment5, cc1.segment6, cc1.segment7,
TO_CHAR (jh.default_effective_date, ‘DD-MON-YYYY’) gl_date,
TO_CHAR (jh.posted_date, ‘DD-MON-YYYY’) posted_date, jl.description,
cc1.segment1
|| ‘-‘
|| cc1.segment2
|| ‘-‘
|| cc1.segment3
|| ‘-‘
|| cc1.segment4
|| ‘-‘
|| cc1.segment5
|| ‘-‘
|| cc1.segment6
|| ‘-‘
|| cc1.segment7 ACCOUNT,
jh.currency_code, NULL transaction_id, NULL transaction_date,
NULL item, v.vendor_name, i.invoice_num, i.invoice_date,
NULL po_number, NULL receipt_number, ael.entered_dr, ael.entered_cr,
ael.accounted_dr, ael.accounted_cr
FROM gl_je_batches jb,
gl_je_lines jl,
gl_je_headers jh,
gl_code_combinations cc1,
gl_import_references r,
ap_ae_lines_all ael,
ap_invoices_all i,
po_vendors v
WHERE jb.je_batch_id = jh.je_batch_id
AND jl.code_combination_id = cc1.code_combination_id
AND jl.je_header_id = r.je_header_id
AND jl.je_header_id = jh.je_header_id
AND jh.je_header_id = r.je_header_id
AND jl.je_line_num = r.je_line_num
AND r.gl_sl_link_id = ael.gl_sl_link_id
AND r.gl_sl_link_table = ‘APECL’
AND ael.source_table = ‘AP_INVOICES’
AND ael.source_id = i.invoice_id
AND i.vendor_id = v.vendor_id
AND cc1.segment1 = :seg1
AND cc1.segment2 = :seg2
AND cc1.segment3 = :seg3
AND cc1.segment4 = :seg4
AND cc1.segment5 = :seg5
AND cc1.segment6 = :seg6
AND cc1.segment7 = :seg7
AND jh.period_name = :period_name
AND i.invoice_currency_code = jh.currency_code
AND jl.set_of_books_id = ‘1002’
UNION ALL
SELECT /*+use_nl(cc1 v r jh ael jl )*/
DISTINCT jl.je_header_id, jl.je_line_num, jh.period_name,
jh.NAME journal_name, jb.NAME batch_name,
jh.je_source journal_source, jh.je_category journal_category,
cc1.segment1, cc1.segment2, cc1.segment3, cc1.segment4,
cc1.segment5, cc1.segment6, cc1.segment7,
TO_CHAR (jh.default_effective_date, ‘DD-MON-YYYY’) gl_date,
TO_CHAR (jh.posted_date, ‘DD-MON-YYYY’) posted_date,
jl.description,
cc1.segment1
|| ‘-‘
|| cc1.segment2
|| ‘-‘
|| cc1.segment3
|| ‘-‘
|| cc1.segment4
|| ‘-‘
|| cc1.segment5
|| ‘-‘
|| cc1.segment6
|| ‘-‘
|| cc1.segment7 ACCOUNT,
jh.currency_code, NULL transaction_id, NULL transaction_date,
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = pl.item_id
AND organization_id = 111) item,
v.vendor_name, NULL invoice_num, NULL invoice_date,
ph.segment1 po_number, rsh.receipt_num receipt_number,
jl.entered_dr, jl.entered_cr, jl.accounted_dr,
jl.accounted_cr
FROM gl_je_batches jb,
gl_je_lines jl,
gl_je_headers jh,
gl_code_combinations cc1,
gl_import_references r,
rcv_receiving_sub_ledger rrsl,
po_distributions_all pd,
po_headers_all ph,
po_lines_all pl,
po_vendors v,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rct
WHERE jb.je_batch_id = jh.je_batch_id
AND jl.code_combination_id = cc1.code_combination_id
AND jl.je_header_id = r.je_header_id
AND jl.je_header_id = jh.je_header_id
AND jh.je_header_id = r.je_header_id
AND jl.set_of_books_id = ‘1002’
AND cc1.segment1 = :seg1
AND cc1.segment2 = :seg2
AND cc1.segment3 = :seg3
AND cc1.segment4 = :seg4
AND cc1.segment5 = :seg5
AND cc1.segment6 = :seg6
AND cc1.segment7 = :seg7
AND jh.je_source = ‘Purchasing’
AND jl.je_line_num = r.je_line_num
AND r.gl_sl_link_id = rrsl.gl_sl_link_id
AND r.gl_sl_link_table = ‘RSL’
AND pd.po_header_id = ph.po_header_id(+)
AND jh.period_name = :period_name
AND ph.po_header_id = rsl.po_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rct.po_header_id = ph.po_header_id
AND rct.po_line_id = pl.po_line_id
AND rct.shipment_header_id = rsh.shipment_header_id
AND rct.shipment_line_id = rsl.shipment_line_id
AND ph.po_header_id = pl.po_header_id
AND v.vendor_id = ph.vendor_id
AND pd.po_distribution_id = rrsl.reference3
AND rct.transaction_id(+) = rrsl.rcv_transaction_id
For any queries, you can message us at contact@doyensys.com
