— GL & XLA Query
SELECT glcc.concatenated_segments ACCOUNT, ac.customer_number,
ac.customer_name, xlal.currency_code, xlal.accounted_dr accounted_dr,
xlal.accounted_cr accounted_cr, xlal.entered_dr, xlal.entered_cr,
h.je_category transaction_type, xlal.accounting_class_code,
xlal.accounting_date transaction_date, h.period_name je_period_name,
xlate.transaction_number transaction_number
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
gl_code_combinations_kfv glcc,
gl_import_references gir,
xla.xla_ae_lines xlal,
xla.xla_ae_headers xlah,
xla.xla_events xlae,
xla.xla_transaction_entities xlate,
ra_customer_trx_all rcta,
ar_customers ac
WHERE 1 = 1
AND b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND xlal.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.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 xlah.ledger_id = xlal.ledger_id
AND xlah.application_id = xlal.application_id
AND xlah.application_id = 222
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlah.application_id
AND xlae.application_id = xlate.application_id
AND rcta.customer_trx_id = NVL (xlate.source_id_int_1, -99)
AND rcta.trx_number = xlate.transaction_number
AND rcta.bill_to_customer_id = ac.customer_id
AND xlate.entity_code = ‘TRANSACTIONS’
AND h.je_source = ‘Receivables’
AND h.period_name = ‘JAN14-14’
AND je_category_name IN
(‘Adjustment’, ‘Credit Memos’, ‘Debit Memos’, ‘Chargebacks’);
— GL Adjustment Query
SELECT glcc.concatenated_segments ACCOUNT, ac.customer_number,
ac.customer_name, xlal.currency_code, xlal.accounted_dr accounted_dr,
xlal.accounted_cr accounted_cr, xlal.entered_dr, xlal.entered_cr,
h.je_category transaction_type, xlal.accounting_class_code,
xlal.accounting_date transaction_date, h.period_name je_period_name,
xlate.transaction_number transaction_number
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
gl_code_combinations_kfv glcc,
gl_import_references gir,
xla.xla_ae_lines xlal,
xla.xla_ae_headers xlah,
xla.xla_events xlae,
xla.xla_transaction_entities xlate,
ra_customer_trx_all rcta,
ar_customers ac,
ar_adjustments_all aaa
WHERE 1 = 1
AND b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND xlal.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.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 xlah.ledger_id = xlal.ledger_id
AND xlah.application_id = xlal.application_id
AND xlah.application_id = 222
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlah.application_id
AND xlae.application_id = xlate.application_id
AND aaa.adjustment_id = NVL (xlate.source_id_int_1, -99)
AND rcta.customer_trx_id = aaa.customer_trx_id
AND aaa.ADJUSTMENT_NUMBER = xlate.transaction_number
AND rcta.bill_to_customer_id = ac.customer_id
AND xlate.entity_code = ‘ADJUSTMENTS’
–AND h.je_source = ‘Receivables’
AND h.period_name = ‘JAN14-14’
AND je_category_name = ‘Adjustment’
–IN (‘Adjustment’, ‘Credit Memos’, ‘Debit Memos’, ‘Chargebacks’)
