GL Drill Down SQL Query

SELECT 

xte.SOURCE_ID_INT_1 trx_id,
xah.DOC_SEQUENCE_VALUE voucher_num,
pv.vendor_name party_name,
pv.segment1 party_number,
--gjh.DOC_SEQUENCE_VALUE voucher_number,
xte.transaction_number transaction_number,
TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY') transaction_date,
gjh.period_name period_name,
gjb.name Batch_Name, --
gjh.je_source j_source,
gjh.je_category j_category,
gjh.default_effective_date gl_date,
gcc.segment1 Company,
gcc.segment2 Cost_Center,
gcc.segment3 Main_Account,
gcc.segment4 Sub_Account,
gcc.segment5 Project,
gcc.segment6 Region,
gcc.segment7 future_1,
gcc.segment8 future_2,
xal.accounted_dr,
xal.accounted_cr,
xal.description

from

apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_import_references gir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte,
gl_code_combinations gcc,
apps.ap_suppliers pv,
gl_period_statuses ps

where

gjh.je_source = 'Payables'
--AND gjh.je_category IN ('Purchase Invoices', 'Payments') --
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND (gjl.accounted_dr != 0 OR gjl.accounted_cr != 0)
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xal.party_id = pv.vendor_id(+)
AND gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND ps.application_id = 101
AND gjl.period_name = ps.period_name
AND ps.ledger_id = gjh.LEDGER_ID
AND ps.effective_period_num >= (cc_period_from)
AND ps.effective_period_num <= (cc_period_to)
AND gcc.segment3 BETWEEN NVL (p_main_acct_frm, '0000')
AND NVL (p_main_acct_to, 'ZZZZ')

UNION ALL

SELECT 

xte.SOURCE_ID_INT_1 trx_id,xah.DOC_SEQUENCE_VALUE voucher_num,
hp.party_name party_name,
hca.account_number party_number,
-- aca.DOC_SEQUENCE_VALUE voucher_number,
--gjh.DOC_SEQUENCE_VALUE voucher_number,
xte.transaction_number transaction_number,
TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY') transaction_date,
gjh.period_name period_name,
gjb.name Batch_Name, --
gjh.je_source j_source,
gjh.je_category j_category,
gjh.default_effective_date gl_date,
gcc.segment1 Company,
gcc.segment2 Cost_Center,
gcc.segment3 Main_Account,
gcc.segment4 Sub_Account,
gcc.segment5 Project,
gcc.segment6 Region,
gcc.segment7 future_1,
gcc.segment8 future_2,
xal.accounted_dr,
xal.accounted_cr,
xal.description

FROM

apps.gl_je_batches gjb,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations gcc,
apps.gl_import_references gir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte,
apps.hz_cust_accounts hca,
apps.hz_parties hp,
gl_period_statuses ps

WHERE 

gjb.JE_BATCH_ID = gjh.JE_BATCH_ID
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gjh.je_source = 'Receivables'
AND (gjl.accounted_dr != 0 OR gjl.accounted_cr != 0)
AND gjl.CODE_COMBINATION_ID = gcc.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_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xal.party_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND ps.application_id = 101
AND gjl.period_name = ps.period_name
AND ps.ledger_id = gjh.LEDGER_ID
AND ps.effective_period_num >= (cc_period_from)
AND ps.effective_period_num <= (cc_period_to)
AND gcc.segment3 BETWEEN NVL (p_main_acct_frm, '0000')
AND NVL (p_main_acct_to, 'ZZZZ')

UNION ALL

SELECT 

NULL trx_id,
gjh.DOC_SEQUENCE_VALUE voucher_num,
NULL party_name,
NULL party_number,
-- NULL voucher_number,
NULL transaction_number,
NULL transaction_date,
gjh.period_name period_name,
gjb.name Batch_Name, --
gjh.je_source j_source,
gjh.je_category j_category,
gjh.default_effective_date gl_date,
gcc.segment1 Company,
gcc.segment2 Cost_Center,
gcc.segment3 Main_Account,
gcc.segment4 Sub_Account,
gcc.segment5 Project,
gcc.segment6 Region,
gcc.segment7 future_1,
gcc.segment8 future_2,
gjl.accounted_dr,
gjl.accounted_cr,
gjl.description

FROM 

apps.gl_je_batches gjb,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations gcc,
gl_period_statuses ps

WHERE 

gjb.JE_BATCH_ID = gjh.JE_BATCH_ID
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gjh.je_source NOT IN ('Receivables', 'Payables')
AND (gjl.accounted_dr != 0 OR gjl.accounted_cr != 0)
AND gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND ps.application_id = 101
AND gjl.period_name = ps.period_name
AND ps.ledger_id = gjh.LEDGER_ID
AND ps.effective_period_num >= (cc_period_from)
AND ps.effective_period_num <= (cc_period_to)
AND gcc.segment3 BETWEEN NVL (p_main_acct_frm, '0000')
AND NVL (p_main_acct_to, 'ZZZZ')

ORDER BY 11,2