SELECT DISTINCT
–gcck.concatenated_segments,
gcck.segment1 company,
gcck.segment2 location,
gcck.segment3 product,
gcck.segment4 function,
gcck.segment5 account,
gcck.segment6 intercompany,
gcck.segment7 subaccount,
gcck.segment8 future1,
gcck.segment9 future2,
rctld.gl_date,
gjb.name batch_name,
gjh.name journal_name,
(
SELECT
user_je_category_name
FROM
apps.gl_je_categories
WHERE
je_category_name = gjh.je_category
) journal_category,
gjh.period_name,
gjh.currency_code,
–gjh.je_header_id journal_header,
gjl.je_line_num journal_line,
gjl.description line_description,
gjl.accounted_cr,
gjl.accounted_dr,
rcta.trx_number,
rcta.trx_date,
rctld.amount
–glsob.name gl_book
–DECODE(gjh.status, ‘P’, ‘POSTED’, ‘U’, ‘unposted’, gjh.status) status,
–gcck.concatenated_segments,
–gjh.creation_date,
/* (
SELECT
user_je_source_name
FROM
apps.gl_je_sources
WHERE
je_source_name = gjh.je_source
) source,*/
FROM
apps.gl_code_combinations_kfv gcck,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_sets_of_books glsob,
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_distribution_links xdl,
apps.ra_cust_trx_line_gl_dist_all rctld,
apps.ra_customer_trx_all rcta
WHERE
gcck.code_combination_id = gjl.code_combination_id
AND gjh.ledger_id = glsob.set_of_books_id
AND gjh.period_name LIKE ‘%-20’
AND gcck.segment2 IN (
‘6671004’,
‘6671003’
)
AND gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gir.je_header_id = gjh.je_header_id
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 xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xah.entity_id = xe.entity_id
AND xe.event_id = xdl.event_id
AND xdl.ae_line_num = xal.ae_line_num
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_header_id = xdl.ref_ae_header_id
AND xdl.source_distribution_id_num_1 = rctld.cust_trx_line_gl_dist_id
AND rctld.account_class = ‘REV’
AND rctld.amount != 0
AND rctld.code_combination_id = gjl.code_combination_id
AND rctld.customer_trx_id = rcta.customer_trx_id
ORDER BY
1,
2,
3,
4,
5,
gjh.name,
gjl.je_line_num
Recommended Posts
