VIEW: APPS.PA_GL_REV_XFER_AUDIT_V

Source

View Text - Preformatted

SELECT DISTINCT jel.code_combination_id CODE_COMBINATION_ID, 'ERDL' LINE_TYPE, jeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, pe.event_type TRANSACTION_TYPE, pe.completion_date TRANSACTION_DATE, task.task_id TASK_ID, task.task_number TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, rdl.line_num RDL_LINE_NUM, rdl.event_num RDL_EVENT_NUM, to_number(null) DEBIT_AMOUNT, rdl.amount CREDIT_AMOUNT FROM gl_je_sources jes, pa_projects prj, pa_tasks task, pa_events pe, pa_cust_event_rdl_all rdl, pa_draft_revenues_all pdr, gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb WHERE prj.project_id = pe.project_id AND pdr.transfer_status_code||'' = 'A' AND rdl.project_id = pdr.project_id AND rdl.draft_revenue_num = pdr.draft_revenue_num AND rdl.project_id = pe.project_id AND NVL(rdl.task_id,-1) = NVL(pe.task_id,-1) AND rdl.event_num = pe.event_num AND task.task_id (+) = pe.task_id AND jeh.je_header_id = jel.je_header_id AND jeh.je_batch_id = jeb.je_batch_id AND jeh.reversed_je_header_id is null AND rdl.batch_name = jel.reference_1 || '' AND jel.code_combination_id = rdl.code_combination_id AND jes.je_source_name = 'Project Accounting' AND pdr.event_id IS NULL UNION ALL SELECT DISTINCT ael.code_combination_id CODE_COMBINATION_ID, 'ERDL' LINE_TYPE, aeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, pe.event_type TRANSACTION_TYPE, pe.completion_date TRANSACTION_DATE, task.task_id TASK_ID, task.task_number TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, rdl.line_num RDL_LINE_NUM, rdl.event_num RDL_EVENT_NUM, to_number(null) DEBIT_AMOUNT, rdl.amount CREDIT_AMOUNT FROM pa_projects prj, pa_tasks task, pa_events pe, pa_cust_event_rdl_all rdl, pa_draft_revenues_all pdr, xla_ae_lines ael, xla_ae_headers aeh, xla_distribution_links xdl WHERE prj.project_id = pe.project_id AND pdr.transfer_status_code||'' = 'A' AND rdl.project_id = pdr.project_id AND rdl.draft_revenue_num = pdr.draft_revenue_num AND rdl.project_id = pe.project_id AND NVL(rdl.task_id,-1) = NVL(pe.task_id,-1) AND rdl.event_num = pe.event_num AND task.task_id (+) = pe.task_id AND ael.code_combination_id = rdl.code_combination_id(+) AND aeh.ae_header_id = ael.ae_header_id AND aeh.event_id = pdr.event_id AND aeh.application_id = 275 AND xdl.ae_header_id = aeh.ae_header_id AND xdl.ae_line_num = ael.ae_line_num AND aeh.balance_type_code = 'A' AND aeh.accounting_entry_status_code = 'F' AND xdl.source_distribution_type = 'Revenue - Event Revenue' AND xdl.source_distribution_id_num_1 = pe.event_id AND xdl.source_distribution_id_num_2 = rdl.line_num AND pdr.event_id IS NOT NULL UNION ALL SELECT DISTINCT jel.code_combination_id CODE_COMBINATION_ID, 'RDL' LINE_TYPE, jeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, ei.expenditure_type TRANSACTION_TYPE, ei.expenditure_item_date TRANSACTION_DATE, task.task_id TASK_ID, task.task_number TASK_NUMBER, DECODE(emp.full_name, null,org.name, emp.full_name ) EMP_OR_ORG_NAME, rdl.expenditure_item_id EXPENDITURE_ITEM_ID, rdl.line_num RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, to_number(null) DEBIT_AMOUNT, rdl.amount CREDIT_AMOUNT FROM gl_je_sources jes, hr_organization_units org, per_people_f emp, pa_tasks task, pa_projects prj, pa_expenditure_items_all ei, pa_expenditures_all exp, pa_cust_rev_dist_lines_all rdl, pa_draft_revenues_all pdr, gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb WHERE task.project_id = prj.project_id AND ei.task_id = task.task_id AND pdr.transfer_status_code||'' = 'A' AND rdl.project_id = pdr.project_id AND rdl.draft_revenue_num = pdr.draft_revenue_num AND rdl.expenditure_item_id = ei.expenditure_item_id AND ei.expenditure_id = exp.expenditure_id AND jeh.je_header_id = jel.je_header_id AND jeh.je_batch_id = jeb.je_batch_id AND jeh.reversed_je_header_id is null AND rdl.batch_name = jel.reference_1 ||'' AND jel.code_combination_id = rdl.code_combination_id AND jes.je_source_name = 'Project Accounting' AND jes.je_source_name = jeh.je_source AND decode(ei.override_to_organization_id, null, exp.incurred_by_organization_id,ei.override_to_organization_id) = org.organization_id AND exp.incurred_by_person_id = emp.person_id (+) AND ( ei.expenditure_item_date BETWEEN nvl(emp.effective_start_date, ei.expenditure_item_date) AND nvl(emp.effective_end_date,ei.expenditure_item_date ) ) AND pdr.event_id IS NULL UNION ALL SELECT DISTINCT ael.code_combination_id CODE_COMBINATION_ID, 'RDL' LINE_TYPE, aeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, ei.expenditure_type TRANSACTION_TYPE, ei.expenditure_item_date TRANSACTION_DATE, task.task_id TASK_ID, task.task_number TASK_NUMBER, DECODE(emp.full_name, null,org.name, emp.full_name ) EMP_OR_ORG_NAME, rdl.expenditure_item_id EXPENDITURE_ITEM_ID, rdl.line_num RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, to_number(null) DEBIT_AMOUNT, rdl.amount CREDIT_AMOUNT FROM hr_organization_units org, per_people_f emp, pa_tasks task, pa_projects prj, pa_expenditure_items_all ei, pa_expenditures_all exp, pa_cust_rev_dist_lines_all rdl, pa_draft_revenues_all pdr, xla_ae_lines ael, xla_ae_headers aeh, xla_distribution_links xdl WHERE task.project_id = prj.project_id AND ei.task_id = task.task_id AND pdr.transfer_status_code||'' = 'A' AND rdl.project_id = pdr.project_id AND rdl.draft_revenue_num = pdr.draft_revenue_num AND rdl.expenditure_item_id = ei.expenditure_item_id AND ei.expenditure_id = exp.expenditure_id AND ael.code_combination_id = rdl.code_combination_id(+) AND aeh.ae_header_id = ael.ae_header_id AND aeh.event_id = pdr.event_id AND aeh.application_id = 275 AND xdl.ae_header_id = aeh.ae_header_id AND xdl.ae_line_num = ael.ae_line_num AND aeh.balance_type_code = 'A' AND aeh.accounting_entry_status_code = 'F' AND xdl.event_id = pdr.event_id AND xdl.source_distribution_type = 'Revenue - Normal Revenue' AND xdl.source_distribution_id_num_1 = rdl.expenditure_item_id AND xdl.source_distribution_id_num_2 = rdl.line_num AND decode(ei.override_to_organization_id, null, exp.incurred_by_organization_id,ei.override_to_organization_id) = org.organization_id AND exp.incurred_by_person_id = emp.person_id (+) AND ( ei.expenditure_item_date BETWEEN nvl(emp.effective_start_date, ei.expenditure_item_date) AND nvl(emp.effective_end_date,ei.expenditure_item_date ) ) AND pdr.event_id IS NOT NULL UNION ALL SELECT DISTINCT jel.code_combination_id CODE_COMBINATION_ID, 'UER' LINE_TYPE, jeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, null TRANSACTION_TYPE, to_date(null) TRANSACTION_DATE, to_number(null) TASK_ID, null TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, to_number(null) RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, -1*pdr.unearned_revenue_cr DEBIT_AMOUNT, to_number(null) CREDIT_AMOUNT FROM gl_je_sources jes, pa_projects prj, pa_draft_Revenues_all pdr, gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb WHERE prj.project_id = pdr.project_id AND pdr.transfer_status_code||'' = 'A' AND jeh.je_header_id = jel.je_header_id AND jeh.je_batch_id = jeb.je_batch_id AND jeh.reversed_je_header_id is null AND pdr.unearned_batch_name = jel.reference_1 AND jel.code_combination_id = pdr.unearned_code_combination_id AND jes.je_source_name = 'Project Accounting' AND pdr.event_id IS NULL UNION ALL SELECT DISTINCT ael.code_combination_id CODE_COMBINATION_ID, 'UER' LINE_TYPE, aeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, null TRANSACTION_TYPE, to_date(null) TRANSACTION_DATE, to_number(null) TASK_ID, null TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, to_number(null) RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, -1*pdr.unearned_revenue_cr DEBIT_AMOUNT, to_number(null) CREDIT_AMOUNT FROM pa_projects prj, pa_draft_Revenues_all pdr, xla_ae_lines ael, xla_ae_headers aeh, xla_distribution_links xdl WHERE prj.project_id = pdr.project_id AND pdr.transfer_status_code||'' = 'A' AND ael.code_combination_id = pdr.unearned_code_combination_id(+) AND aeh.ae_header_id = ael.ae_header_id AND aeh.event_id = pdr.event_id AND aeh.application_id = 275 AND xdl.ae_header_id = aeh.ae_header_id AND xdl.ae_line_num = ael.ae_line_num AND xdl.event_id = pdr.event_id AND xdl.source_distribution_type = 'Revenue - UER' AND xdl.source_distribution_id_num_1 = pdr.project_id AND xdl.source_distribution_id_num_2 = pdr.draft_revenue_num AND aeh.balance_type_code = 'A' AND aeh.accounting_entry_status_code = 'F' AND pdr.event_id IS NOT NULL UNION ALL SELECT DISTINCT jel.code_combination_id CODE_COMBINATION_ID, 'UBR' LINE_TYPE, jeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, null TRANSACTION_TYPE, to_date(null) TRANSACTION_DATE, to_number(null) TASK_ID, null TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, to_number(null) RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, pdr.unbilled_receivable_dr DEBIT_AMOUNT, to_number(null) CREDIT_AMOUNT FROM gl_je_sources jes, pa_projects prj, pa_draft_Revenues_all pdr, gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb WHERE prj.project_id = pdr.project_id AND pdr.transfer_status_code||'' = 'A' AND pdr.project_id = prj.project_id AND jeh.je_header_id = jel.je_header_id AND jeh.je_batch_id = jeb.je_batch_id AND jeh.reversed_je_header_id is null AND pdr.unbilled_batch_name = jel.reference_1 AND jel.code_combination_id = pdr.unbilled_code_combination_id AND jes.je_source_name = 'Project Accounting' AND pdr.event_id IS NULL UNION ALL SELECT DISTINCT ael.code_combination_id CODE_COMBINATION_ID, 'UBR' LINE_TYPE, aeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, null TRANSACTION_TYPE, to_date(null) TRANSACTION_DATE, to_number(null) TASK_ID, null TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, to_number(null) RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, pdr.unbilled_receivable_dr DEBIT_AMOUNT, to_number(null) CREDIT_AMOUNT FROM pa_projects prj, pa_draft_Revenues_all pdr, xla_ae_lines ael, xla_ae_headers aeh, xla_distribution_links xdl WHERE prj.project_id = pdr.project_id AND pdr.transfer_status_code||'' = 'A' AND pdr.project_id = prj.project_id AND ael.code_combination_id = pdr.unbilled_code_combination_id(+) AND aeh.ae_header_id = ael.ae_header_id AND aeh.event_id = pdr.event_id AND aeh.application_id = 275 AND xdl.ae_header_id = aeh.ae_header_id AND xdl.ae_line_num = ael.ae_line_num AND xdl.event_id = pdr.event_id AND xdl.source_distribution_type = 'Revenue - UBR' AND xdl.source_distribution_id_num_1 = pdr.project_id AND xdl.source_distribution_id_num_2 = pdr.draft_revenue_num AND aeh.balance_type_code = 'A' AND aeh.accounting_entry_status_code = 'F' AND pdr.event_id IS NOT NULL UNION ALL SELECT DISTINCT jel.code_combination_id CODE_COMBINATION_ID, 'RLZD-GAIN' LINE_TYPE, jeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, null TRANSACTION_TYPE, to_date(null) TRANSACTION_DATE, to_number(null) TASK_ID, null TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, to_number(null) RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, -1*pdr.unearned_revenue_cr DEBIT_AMOUNT, to_number(null) CREDIT_AMOUNT FROM gl_je_sources jes, pa_projects prj, pa_draft_Revenues_all pdr, gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb WHERE prj.project_id = pdr.project_id AND pdr.transfer_status_code||'' = 'A' AND jeh.je_header_id = jel.je_header_id AND jeh.je_batch_id = jeb.je_batch_id AND jeh.reversed_je_header_id is null AND pdr.realized_gains_batch_name = jel.reference_1 AND jel.code_combination_id = pdr.realized_gains_ccid AND jes.je_source_name = 'Project Accounting' AND pdr.event_id IS NULL UNION ALL SELECT DISTINCT ael.code_combination_id CODE_COMBINATION_ID, 'RLZD-GAIN' LINE_TYPE, aeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, null TRANSACTION_TYPE, to_date(null) TRANSACTION_DATE, to_number(null) TASK_ID, null TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, to_number(null) RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, -1*pdr.unearned_revenue_cr DEBIT_AMOUNT, to_number(null) CREDIT_AMOUNT FROM pa_projects prj, pa_draft_Revenues_all pdr, xla_ae_lines ael, xla_ae_headers aeh, xla_distribution_links xdl WHERE prj.project_id = pdr.project_id AND pdr.transfer_status_code||'' = 'A' AND ael.code_combination_id = pdr.realized_gains_ccid(+) AND aeh.ae_header_id = ael.ae_header_id AND aeh.event_id = pdr.event_id AND aeh.application_id = 275 AND xdl.ae_header_id = aeh.ae_header_id AND xdl.ae_line_num = ael.ae_line_num AND xdl.event_id = pdr.event_id AND xdl.source_distribution_type = 'Revenue - Realized Gains' AND xdl.source_distribution_id_num_1 = pdr.project_id AND xdl.source_distribution_id_num_2 = pdr.draft_revenue_num AND aeh.balance_type_code = 'A' AND aeh.accounting_entry_status_code = 'F' AND pdr.event_id IS NOT NULL UNION ALL SELECT DISTINCT jel.code_combination_id CODE_COMBINATION_ID, 'RLZD-LOSS' LINE_TYPE, jeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, null TRANSACTION_TYPE, to_date(null) TRANSACTION_DATE, to_number(null) TASK_ID, null TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, to_number(null) RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, -1*pdr.unearned_revenue_cr DEBIT_AMOUNT, to_number(null) CREDIT_AMOUNT FROM gl_je_sources jes, pa_projects prj, pa_draft_Revenues_all pdr, gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb WHERE prj.project_id = pdr.project_id AND pdr.transfer_status_code||'' = 'A' AND jeh.je_header_id = jel.je_header_id AND jeh.je_batch_id = jeb.je_batch_id AND jeh.reversed_je_header_id is null AND pdr.realized_losses_batch_name = jel.reference_1 AND jel.code_combination_id = pdr.realized_losses_ccid AND jes.je_source_name = 'Project Accounting' AND pdr.event_id IS NULL UNION ALL SELECT DISTINCT ael.code_combination_id CODE_COMBINATION_ID, 'RLZD-LOSS' LINE_TYPE, aeh.period_name PERIOD_NAME, pdr.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, pdr.draft_revenue_num DRAFT_REV_NUMBER, pdr.transferred_date TRANSFERRED_DATE, pdr.gl_date GL_DATE, null TRANSACTION_TYPE, to_date(null) TRANSACTION_DATE, to_number(null) TASK_ID, null TASK_NUMBER, null EMP_OR_ORG_NAME, to_number(null) EXPENDITURE_ITEM_ID, to_number(null) RDL_LINE_NUM, to_number(null) RDL_EVENT_NUM, -1*pdr.unearned_revenue_cr DEBIT_AMOUNT, to_number(null) CREDIT_AMOUNT FROM pa_projects prj, pa_draft_Revenues_all pdr, xla_ae_lines ael, xla_ae_headers aeh, xla_distribution_links xdl WHERE prj.project_id = pdr.project_id AND pdr.transfer_status_code||'' = 'A' AND ael.code_combination_id = pdr.realized_losses_ccid(+) AND aeh.ae_header_id = ael.ae_header_id AND aeh.event_id = pdr.event_id AND aeh.application_id = 275 AND xdl.ae_header_id = aeh.ae_header_id AND xdl.ae_line_num = ael.ae_line_num AND xdl.event_id = pdr.event_id AND xdl.source_distribution_type = 'Revenue - Realized Losses' AND xdl.source_distribution_id_num_1 = pdr.project_id AND xdl.source_distribution_id_num_2 = pdr.draft_revenue_num AND aeh.balance_type_code = 'A' AND aeh.accounting_entry_status_code = 'F' AND pdr.event_id IS NOT NULL

View Text - HTML Formatted

SELECT DISTINCT JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'ERDL' LINE_TYPE
, JEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, PE.EVENT_TYPE TRANSACTION_TYPE
, PE.COMPLETION_DATE TRANSACTION_DATE
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, RDL.LINE_NUM RDL_LINE_NUM
, RDL.EVENT_NUM RDL_EVENT_NUM
, TO_NUMBER(NULL) DEBIT_AMOUNT
, RDL.AMOUNT CREDIT_AMOUNT
FROM GL_JE_SOURCES JES
, PA_PROJECTS PRJ
, PA_TASKS TASK
, PA_EVENTS PE
, PA_CUST_EVENT_RDL_ALL RDL
, PA_DRAFT_REVENUES_ALL PDR
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_JE_BATCHES JEB
WHERE PRJ.PROJECT_ID = PE.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND RDL.PROJECT_ID = PDR.PROJECT_ID
AND RDL.DRAFT_REVENUE_NUM = PDR.DRAFT_REVENUE_NUM
AND RDL.PROJECT_ID = PE.PROJECT_ID
AND NVL(RDL.TASK_ID
, -1) = NVL(PE.TASK_ID
, -1)
AND RDL.EVENT_NUM = PE.EVENT_NUM
AND TASK.TASK_ID (+) = PE.TASK_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID
AND JEH.REVERSED_JE_HEADER_ID IS NULL
AND RDL.BATCH_NAME = JEL.REFERENCE_1 || ''
AND JEL.CODE_COMBINATION_ID = RDL.CODE_COMBINATION_ID
AND JES.JE_SOURCE_NAME = 'PROJECT ACCOUNTING'
AND PDR.EVENT_ID IS NULL UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'ERDL' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, PE.EVENT_TYPE TRANSACTION_TYPE
, PE.COMPLETION_DATE TRANSACTION_DATE
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, RDL.LINE_NUM RDL_LINE_NUM
, RDL.EVENT_NUM RDL_EVENT_NUM
, TO_NUMBER(NULL) DEBIT_AMOUNT
, RDL.AMOUNT CREDIT_AMOUNT
FROM PA_PROJECTS PRJ
, PA_TASKS TASK
, PA_EVENTS PE
, PA_CUST_EVENT_RDL_ALL RDL
, PA_DRAFT_REVENUES_ALL PDR
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = PE.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND RDL.PROJECT_ID = PDR.PROJECT_ID
AND RDL.DRAFT_REVENUE_NUM = PDR.DRAFT_REVENUE_NUM
AND RDL.PROJECT_ID = PE.PROJECT_ID
AND NVL(RDL.TASK_ID
, -1) = NVL(PE.TASK_ID
, -1)
AND RDL.EVENT_NUM = PE.EVENT_NUM
AND TASK.TASK_ID (+) = PE.TASK_ID
AND AEL.CODE_COMBINATION_ID = RDL.CODE_COMBINATION_ID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = PDR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND AEH.BALANCE_TYPE_CODE = 'A'
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - EVENT REVENUE'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PE.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = RDL.LINE_NUM
AND PDR.EVENT_ID IS NOT NULL UNION ALL SELECT DISTINCT JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RDL' LINE_TYPE
, JEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, EI.EXPENDITURE_TYPE TRANSACTION_TYPE
, EI.EXPENDITURE_ITEM_DATE TRANSACTION_DATE
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, DECODE(EMP.FULL_NAME
, NULL
, ORG.NAME
, EMP.FULL_NAME ) EMP_OR_ORG_NAME
, RDL.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, RDL.LINE_NUM RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, TO_NUMBER(NULL) DEBIT_AMOUNT
, RDL.AMOUNT CREDIT_AMOUNT
FROM GL_JE_SOURCES JES
, HR_ORGANIZATION_UNITS ORG
, PER_PEOPLE_F EMP
, PA_TASKS TASK
, PA_PROJECTS PRJ
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL EXP
, PA_CUST_REV_DIST_LINES_ALL RDL
, PA_DRAFT_REVENUES_ALL PDR
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_JE_BATCHES JEB
WHERE TASK.PROJECT_ID = PRJ.PROJECT_ID
AND EI.TASK_ID = TASK.TASK_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND RDL.PROJECT_ID = PDR.PROJECT_ID
AND RDL.DRAFT_REVENUE_NUM = PDR.DRAFT_REVENUE_NUM
AND RDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND EI.EXPENDITURE_ID = EXP.EXPENDITURE_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID
AND JEH.REVERSED_JE_HEADER_ID IS NULL
AND RDL.BATCH_NAME = JEL.REFERENCE_1 ||''
AND JEL.CODE_COMBINATION_ID = RDL.CODE_COMBINATION_ID
AND JES.JE_SOURCE_NAME = 'PROJECT ACCOUNTING'
AND JES.JE_SOURCE_NAME = JEH.JE_SOURCE
AND DECODE(EI.OVERRIDE_TO_ORGANIZATION_ID
, NULL
, EXP.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID) = ORG.ORGANIZATION_ID
AND EXP.INCURRED_BY_PERSON_ID = EMP.PERSON_ID (+)
AND ( EI.EXPENDITURE_ITEM_DATE BETWEEN NVL(EMP.EFFECTIVE_START_DATE
, EI.EXPENDITURE_ITEM_DATE)
AND NVL(EMP.EFFECTIVE_END_DATE
, EI.EXPENDITURE_ITEM_DATE ) )
AND PDR.EVENT_ID IS NULL UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RDL' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, EI.EXPENDITURE_TYPE TRANSACTION_TYPE
, EI.EXPENDITURE_ITEM_DATE TRANSACTION_DATE
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, DECODE(EMP.FULL_NAME
, NULL
, ORG.NAME
, EMP.FULL_NAME ) EMP_OR_ORG_NAME
, RDL.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, RDL.LINE_NUM RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, TO_NUMBER(NULL) DEBIT_AMOUNT
, RDL.AMOUNT CREDIT_AMOUNT
FROM HR_ORGANIZATION_UNITS ORG
, PER_PEOPLE_F EMP
, PA_TASKS TASK
, PA_PROJECTS PRJ
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL EXP
, PA_CUST_REV_DIST_LINES_ALL RDL
, PA_DRAFT_REVENUES_ALL PDR
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE TASK.PROJECT_ID = PRJ.PROJECT_ID
AND EI.TASK_ID = TASK.TASK_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND RDL.PROJECT_ID = PDR.PROJECT_ID
AND RDL.DRAFT_REVENUE_NUM = PDR.DRAFT_REVENUE_NUM
AND RDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND EI.EXPENDITURE_ID = EXP.EXPENDITURE_ID
AND AEL.CODE_COMBINATION_ID = RDL.CODE_COMBINATION_ID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = PDR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND AEH.BALANCE_TYPE_CODE = 'A'
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND XDL.EVENT_ID = PDR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - NORMAL REVENUE'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RDL.EXPENDITURE_ITEM_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = RDL.LINE_NUM
AND DECODE(EI.OVERRIDE_TO_ORGANIZATION_ID
, NULL
, EXP.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID) = ORG.ORGANIZATION_ID
AND EXP.INCURRED_BY_PERSON_ID = EMP.PERSON_ID (+)
AND ( EI.EXPENDITURE_ITEM_DATE BETWEEN NVL(EMP.EFFECTIVE_START_DATE
, EI.EXPENDITURE_ITEM_DATE)
AND NVL(EMP.EFFECTIVE_END_DATE
, EI.EXPENDITURE_ITEM_DATE ) )
AND PDR.EVENT_ID IS NOT NULL UNION ALL SELECT DISTINCT JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'UER' LINE_TYPE
, JEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*PDR.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
FROM GL_JE_SOURCES JES
, PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL PDR
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_JE_BATCHES JEB
WHERE PRJ.PROJECT_ID = PDR.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID
AND JEH.REVERSED_JE_HEADER_ID IS NULL
AND PDR.UNEARNED_BATCH_NAME = JEL.REFERENCE_1
AND JEL.CODE_COMBINATION_ID = PDR.UNEARNED_CODE_COMBINATION_ID
AND JES.JE_SOURCE_NAME = 'PROJECT ACCOUNTING'
AND PDR.EVENT_ID IS NULL UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'UER' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*PDR.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
FROM PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL PDR
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = PDR.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND AEL.CODE_COMBINATION_ID = PDR.UNEARNED_CODE_COMBINATION_ID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = PDR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = PDR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - UER'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PDR.PROJECT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = PDR.DRAFT_REVENUE_NUM
AND AEH.BALANCE_TYPE_CODE = 'A'
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND PDR.EVENT_ID IS NOT NULL UNION ALL SELECT DISTINCT JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'UBR' LINE_TYPE
, JEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, PDR.UNBILLED_RECEIVABLE_DR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
FROM GL_JE_SOURCES JES
, PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL PDR
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_JE_BATCHES JEB
WHERE PRJ.PROJECT_ID = PDR.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND PDR.PROJECT_ID = PRJ.PROJECT_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID
AND JEH.REVERSED_JE_HEADER_ID IS NULL
AND PDR.UNBILLED_BATCH_NAME = JEL.REFERENCE_1
AND JEL.CODE_COMBINATION_ID = PDR.UNBILLED_CODE_COMBINATION_ID
AND JES.JE_SOURCE_NAME = 'PROJECT ACCOUNTING'
AND PDR.EVENT_ID IS NULL UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'UBR' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, PDR.UNBILLED_RECEIVABLE_DR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
FROM PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL PDR
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = PDR.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND PDR.PROJECT_ID = PRJ.PROJECT_ID
AND AEL.CODE_COMBINATION_ID = PDR.UNBILLED_CODE_COMBINATION_ID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = PDR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = PDR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - UBR'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PDR.PROJECT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = PDR.DRAFT_REVENUE_NUM
AND AEH.BALANCE_TYPE_CODE = 'A'
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND PDR.EVENT_ID IS NOT NULL UNION ALL SELECT DISTINCT JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RLZD-GAIN' LINE_TYPE
, JEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*PDR.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
FROM GL_JE_SOURCES JES
, PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL PDR
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_JE_BATCHES JEB
WHERE PRJ.PROJECT_ID = PDR.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID
AND JEH.REVERSED_JE_HEADER_ID IS NULL
AND PDR.REALIZED_GAINS_BATCH_NAME = JEL.REFERENCE_1
AND JEL.CODE_COMBINATION_ID = PDR.REALIZED_GAINS_CCID
AND JES.JE_SOURCE_NAME = 'PROJECT ACCOUNTING'
AND PDR.EVENT_ID IS NULL UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RLZD-GAIN' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*PDR.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
FROM PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL PDR
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = PDR.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND AEL.CODE_COMBINATION_ID = PDR.REALIZED_GAINS_CCID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = PDR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = PDR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - REALIZED GAINS'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PDR.PROJECT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = PDR.DRAFT_REVENUE_NUM
AND AEH.BALANCE_TYPE_CODE = 'A'
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND PDR.EVENT_ID IS NOT NULL UNION ALL SELECT DISTINCT JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RLZD-LOSS' LINE_TYPE
, JEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*PDR.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
FROM GL_JE_SOURCES JES
, PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL PDR
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_JE_BATCHES JEB
WHERE PRJ.PROJECT_ID = PDR.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID
AND JEH.REVERSED_JE_HEADER_ID IS NULL
AND PDR.REALIZED_LOSSES_BATCH_NAME = JEL.REFERENCE_1
AND JEL.CODE_COMBINATION_ID = PDR.REALIZED_LOSSES_CCID
AND JES.JE_SOURCE_NAME = 'PROJECT ACCOUNTING'
AND PDR.EVENT_ID IS NULL UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RLZD-LOSS' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, PDR.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PDR.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, PDR.TRANSFERRED_DATE TRANSFERRED_DATE
, PDR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*PDR.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
FROM PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL PDR
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = PDR.PROJECT_ID
AND PDR.TRANSFER_STATUS_CODE||'' = 'A'
AND AEL.CODE_COMBINATION_ID = PDR.REALIZED_LOSSES_CCID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = PDR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = PDR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - REALIZED LOSSES'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PDR.PROJECT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = PDR.DRAFT_REVENUE_NUM
AND AEH.BALANCE_TYPE_CODE = 'A'
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND PDR.EVENT_ID IS NOT NULL