Objectives : The purpose of this document is to provide comprehensive information on project headers and its line-level details. This document aims to offer a detailed SQL query overview of project header and their corresponding lines details, which shows only those lines where gl code combinations “Segment5” is matched.
SQL Query:
———————————-
WITH query
AS (SELECT DISTINCT ppa.segment1
project_number,
pcdl.expenditure_item_id
Transaction_number,
ptv.task_number,
To_char(peia.expenditure_item_date, ‘MM/DD/YY’)
expenditure_item_date,
(SELECT expenditure_type_name
FROM pjf_exp_types_tl
WHERE expenditure_type_id = peia.expenditure_type_id
AND language = ‘US’)
expenditure_type,
(SELECT NAME
FROM hr_all_organization_units
WHERE organization_id = ppa.org_id)
expenditure_business_unit,
(SELECT user_transaction_source
FROM pjf_txn_sources_tl
WHERE
transaction_source_id = peia.transaction_source_id
AND language = ‘US’)
transaction_source,
(SELECT document_name
FROM pjf_txn_document_tl
WHERE document_id = peia.document_id
AND language = ‘US’)
document,
Replace(xdl.rounding_class_code, ‘_’, ‘ ‘)
class,
(SELECT NAME
FROM fusion.xla_event_types_tl
WHERE application_id = 10036
AND language = ‘US’
AND event_class_code = xdl.event_class_code
AND event_type_code = xdl.event_type_code)
Event,
gcc.segment1
|| ‘.’
|| gcc.segment2
|| ‘.’
|| gcc.segment3
|| ‘.’
|| gcc.segment4
|| ‘.’
|| gcc.segment5
|| ‘.’
|| gcc.segment6
|| ‘.’
|| gcc.segment7
|| ‘.’
|| gcc.segment8
|| ‘.’
|| gcc.segment9
Account,
gjl.accounted_dr
accounted_debit,
gjl.accounted_cr
accounted_credit,
ael.ae_line_num
je_line_num,
gcc.segment5
Nat_1,
To_char(ppa.creation_date, ‘MM/DD/YY’)
creation_date,
Count(gcc.segment5)
OVER(
partition BY gcc.segment5, pcdl.expenditure_item_id)
na_cnt
FROM xla_distribution_links xdl,
pjc_cost_dist_lines_all pcdl,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_transaction_entities ent,
gl_import_references gir,
gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc,
pjf_projects_all_b ppa,
pjc_exp_items_all peia,
pjf_tasks_v ptv
WHERE 1 = 1
–and ppa.SEGMENT1=’ZSAD-253-22′
AND ppa.project_id = peia.project_id
AND peia.task_id = ptv.task_id
AND xdl. event_id = pcdl.acct_event_id
–AND xdl.application_id = 10036
AND peia.expenditure_item_id IN ( expitemid )
AND peia.expenditure_item_id = pcdl.expenditure_item_id
AND ael.application_id = aeh.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND xdl.ae_header_id = aeh.ae_header_id
AND ael.ae_line_num = xdl.ae_line_num
AND ent.application_id = aeh.application_id
AND ent.entity_id = aeh.entity_id
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = ‘P’
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id)
SELECT rownum line,
query.project_number,
query.transaction_number,
query.task_number,
query.expenditure_item_date,
query.expenditure_type,
query.expenditure_business_unit,
query.transaction_source,
query.document,
query.event,
query.account,
Initcap(query.class),
query.accounted_debit,
query.accounted_credit,
query.je_line_num,
query.nat_1,
query.creation_date
FROM query
WHERE 1 = 1
AND query.na_cnt = 2
