VIEW: APPS.PA_GL_COST_XFER_AUDIT_V

Source

View Text - Preformatted

SELECT ei.expenditure_item_id EXPENDITURE_ITEM_ID, cdl.line_num CDL_LINE_NUM, ael.code_combination_id CODE_COMBINATION_ID, prj.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, task.task_id TASK_ID, task.task_number TASK_NUMBER, ei.expenditure_type EXPENDITURE_TYPE, ei.expenditure_item_date EXPENDITURE_ITEM_DATE, DECODE(emp.full_name, null,hr.name, emp.full_name ) EMP_OR_ORG_NAME, aeh.period_name PERIOD_NAME, ael.ae_line_num JE_LINE_NUMBER, cdl.gl_date GL_DATE, cdl.transferred_date TRANSFERRED_DATE, xdl.UNROUNDED_ACCOUNTED_DR GL_DR_AMOUNT, xdl.UNROUNDED_ACCOUNTED_CR GL_CR_AMOUNT, DECODE(xpap.program_code, 'PA_POSTACCOUNTING_CREDIT',ael.code_combination_id, 'PA_POSTACCOUNTING_DEBIT', (select ael1.code_combination_id from xla_ae_lines ael1, xla_distribution_links xdl1 WHERE xdl1.application_id = 275 and xdl1.source_distribution_id_num_1 = xdl.source_distribution_id_num_1 and xdl1.source_distribution_id_num_2 = xdl.source_distribution_id_num_2 and xdl1.source_distribution_type = xdl.source_distribution_type and ael1.ae_header_id = xdl1.ae_header_id and ael1.ae_line_num = xdl1.ae_line_num and ael1.ae_header_id = ael.ae_header_id and ael1.rowid <> ael.rowid ) ) cr_code_combination_id, exp.org_id FROM hr_organization_units hr, per_people_f emp, pa_projects_all prj, pa_tasks task, pa_expenditures exp, pa_expenditure_items_all ei, pa_cost_distribution_lines_all cdl, xla_ae_headers aeh, xla_ae_lines ael, xla_distribution_links xdl, pa_implementations_all imp, xla_acct_class_assgns xaca, xla_assignment_defns_b xad, xla_post_acct_progs_b xpap WHERE prj.project_id = task.project_id AND ei.task_id = task.task_id AND cdl.expenditure_item_id = ei.expenditure_item_id AND cdl.transfer_status_code||'' = 'A' AND cdl.acct_event_id is not null AND cdl.org_id = imp.org_id AND xdl.application_id = 275 AND xdl.source_distribution_id_num_1 = ei.expenditure_item_id AND cdl.line_num = xdl.source_distribution_id_num_2 AND xdl.source_distribution_type = cdl.line_type AND xdl.ae_header_id = aeh.ae_header_id AND xdl.ae_line_num = ael.ae_line_num AND xdl.ae_header_id = ael.ae_header_id AND aeh.application_id = ael.application_id AND ael.application_id = xdl.application_id AND aeh.balance_type_code = 'A' AND xpap.program_code in ( 'PA_POSTACCOUNTING_DEBIT', 'PA_POSTACCOUNTING_CREDIT') AND xpap.program_owner_code = 'S' AND xpap.application_id = 275 AND xad.program_owner_code = xpap.program_owner_code AND xad.program_code = xpap.program_code AND xad.enabled_flag = 'Y' AND (xad.ledger_id IS NULL OR xad.ledger_id = imp.set_of_books_id) AND xaca.program_owner_code = xad.program_owner_code AND xaca.program_code = xad.program_code AND xaca.assignment_code = xad.assignment_code AND xaca.assignment_owner_code = xad.assignment_owner_code AND ael.accounting_class_code = xaca.accounting_class_code AND aeh.accounting_entry_status_code = 'F' AND aeh.ledger_id = imp.set_of_books_id AND exp.expenditure_id = ei.expenditure_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 decode(ei.override_to_organization_id, null, exp.incurred_by_organization_id, ei.override_to_organization_id) = hr.organization_id UNION ALL SELECT ei.expenditure_item_id EXPENDITURE_ITEM_ID, cdl.line_num CDL_LINE_NUM, jel.code_combination_id CODE_COMBINATION_ID, prj.project_id PROJECT_ID, prj.segment1 PROJECT_NUMBER, task.task_id TASK_ID, task.task_number TASK_NUMBER, ei.expenditure_type EXPENDITURE_TYPE, ei.expenditure_item_date EXPENDITURE_ITEM_DATE, DECODE(emp.full_name, null,hr.name, emp.full_name ) EMP_OR_ORG_NAME, jeh.period_name PERIOD_NAME, jel.je_line_num JE_LINE_NUMBER, cdl.gl_date GL_DATE, cdl.transferred_date TRANSFERRED_DATE, DECODE(glref.reference_3, 'Liability', to_number(null), DECODE(ei.system_linkage_function,'BTC', DECODE(cdl.line_type,'R',(cdl.acct_burdened_cost+nvl(cdl.acct_burdened_change,0)), cdl.acct_raw_cost), cdl.acct_raw_cost)) GL_DR_AMOUNT, DECODE(glref.reference_3, 'Cost', to_number(null), decode(ei.system_linkage_function,'BTC', DECODE(cdl.line_type,'R',(cdl.acct_burdened_cost+nvl(cdl.acct_burdened_change,0)), decode(cdl.line_type,'C',-cdl.acct_raw_cost,cdl.acct_raw_cost)), decode(cdl.line_type,'C',-cdl.acct_raw_cost,cdl.acct_raw_cost))) GL_CR_AMOUNT ,cdl.cr_code_combination_id cr_code_combination_id ,exp.org_id FROM hr_organization_units hr, per_people_f emp, pa_projects_all prj, pa_tasks task, pa_expenditures exp, pa_expenditure_items_all ei, pa_cost_distribution_lines_all cdl, gl_import_references glref, gl_je_lines jel, gl_je_headers jeh, pa_system_linkages syslink WHERE prj.project_id = task.project_id AND ei.task_id = task.task_id AND jeh.je_category = decode(cdl.line_type,'R',syslink.cost_je_category_name,jeh.je_category) AND ei.system_linkage_function = syslink.function AND cdl.expenditure_item_id = ei.expenditure_item_id AND cdl.transfer_status_code||'' = 'A' AND cdl.acct_event_id is NULL AND cdl.batch_name = glref.reference_1 AND ((CDL.DR_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID AND JEL.REFERENCE_3 = 'Cost' AND EXISTS (SELECT 1 FROM GL_JE_LINES JEL1 WHERE JEL1.JE_HEADER_ID= JEL.JE_HEADER_ID AND JEL1.REFERENCE_1 = JEL.REFERENCE_1 AND JEL1.CODE_COMBINATION_ID = CDL.CR_CODE_COMBINATION_ID ) ) OR (CDL.CR_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID AND JEL.REFERENCE_3 = 'Liability' AND EXISTS (SELECT 1 FROM GL_JE_LINES JEL1 WHERE JEL1.JE_HEADER_ID = JEL.JE_HEADER_ID AND JEL1.REFERENCE_1 = JEL.REFERENCE_1 AND JEL1.CODE_COMBINATION_ID = CDL.DR_CODE_COMBINATION_ID ) )) AND jel.reference_3 in ( 'Cost', 'Liability') AND glref.je_header_id = jel.je_header_id AND glref.je_line_num = jel.je_line_num AND jeh.je_header_id = jel.je_header_id AND jeh.reversed_je_header_id is null AND exp.expenditure_id = ei.expenditure_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 decode(ei.override_to_organization_id, null, exp.incurred_by_organization_id, ei.override_to_organization_id) = hr.organization_id

View Text - HTML Formatted

SELECT EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, CDL.LINE_NUM CDL_LINE_NUM
, AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, PRJ.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, DECODE(EMP.FULL_NAME
, NULL
, HR.NAME
, EMP.FULL_NAME ) EMP_OR_ORG_NAME
, AEH.PERIOD_NAME PERIOD_NAME
, AEL.AE_LINE_NUM JE_LINE_NUMBER
, CDL.GL_DATE GL_DATE
, CDL.TRANSFERRED_DATE TRANSFERRED_DATE
, XDL.UNROUNDED_ACCOUNTED_DR GL_DR_AMOUNT
, XDL.UNROUNDED_ACCOUNTED_CR GL_CR_AMOUNT
, DECODE(XPAP.PROGRAM_CODE
, 'PA_POSTACCOUNTING_CREDIT'
, AEL.CODE_COMBINATION_ID
, 'PA_POSTACCOUNTING_DEBIT'
, (SELECT AEL1.CODE_COMBINATION_ID
FROM XLA_AE_LINES AEL1
, XLA_DISTRIBUTION_LINKS XDL1
WHERE XDL1.APPLICATION_ID = 275
AND XDL1.SOURCE_DISTRIBUTION_ID_NUM_1 = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XDL1.SOURCE_DISTRIBUTION_ID_NUM_2 = XDL.SOURCE_DISTRIBUTION_ID_NUM_2
AND XDL1.SOURCE_DISTRIBUTION_TYPE = XDL.SOURCE_DISTRIBUTION_TYPE
AND AEL1.AE_HEADER_ID = XDL1.AE_HEADER_ID
AND AEL1.AE_LINE_NUM = XDL1.AE_LINE_NUM
AND AEL1.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEL1.ROWID <> AEL.ROWID ) ) CR_CODE_COMBINATION_ID
, EXP.ORG_ID
FROM HR_ORGANIZATION_UNITS HR
, PER_PEOPLE_F EMP
, PA_PROJECTS_ALL PRJ
, PA_TASKS TASK
, PA_EXPENDITURES EXP
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_COST_DISTRIBUTION_LINES_ALL CDL
, XLA_AE_HEADERS AEH
, XLA_AE_LINES AEL
, XLA_DISTRIBUTION_LINKS XDL
, PA_IMPLEMENTATIONS_ALL IMP
, XLA_ACCT_CLASS_ASSGNS XACA
, XLA_ASSIGNMENT_DEFNS_B XAD
, XLA_POST_ACCT_PROGS_B XPAP
WHERE PRJ.PROJECT_ID = TASK.PROJECT_ID
AND EI.TASK_ID = TASK.TASK_ID
AND CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CDL.TRANSFER_STATUS_CODE||'' = 'A'
AND CDL.ACCT_EVENT_ID IS NOT NULL
AND CDL.ORG_ID = IMP.ORG_ID
AND XDL.APPLICATION_ID = 275
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = EI.EXPENDITURE_ITEM_ID
AND CDL.LINE_NUM = XDL.SOURCE_DISTRIBUTION_ID_NUM_2
AND XDL.SOURCE_DISTRIBUTION_TYPE = CDL.LINE_TYPE
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.APPLICATION_ID = AEL.APPLICATION_ID
AND AEL.APPLICATION_ID = XDL.APPLICATION_ID
AND AEH.BALANCE_TYPE_CODE = 'A'
AND XPAP.PROGRAM_CODE IN ( 'PA_POSTACCOUNTING_DEBIT'
, 'PA_POSTACCOUNTING_CREDIT')
AND XPAP.PROGRAM_OWNER_CODE = 'S'
AND XPAP.APPLICATION_ID = 275
AND XAD.PROGRAM_OWNER_CODE = XPAP.PROGRAM_OWNER_CODE
AND XAD.PROGRAM_CODE = XPAP.PROGRAM_CODE
AND XAD.ENABLED_FLAG = 'Y'
AND (XAD.LEDGER_ID IS NULL OR XAD.LEDGER_ID = IMP.SET_OF_BOOKS_ID)
AND XACA.PROGRAM_OWNER_CODE = XAD.PROGRAM_OWNER_CODE
AND XACA.PROGRAM_CODE = XAD.PROGRAM_CODE
AND XACA.ASSIGNMENT_CODE = XAD.ASSIGNMENT_CODE
AND XACA.ASSIGNMENT_OWNER_CODE = XAD.ASSIGNMENT_OWNER_CODE
AND AEL.ACCOUNTING_CLASS_CODE = XACA.ACCOUNTING_CLASS_CODE
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND AEH.LEDGER_ID = IMP.SET_OF_BOOKS_ID
AND EXP.EXPENDITURE_ID = EI.EXPENDITURE_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 DECODE(EI.OVERRIDE_TO_ORGANIZATION_ID
, NULL
, EXP.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID) = HR.ORGANIZATION_ID UNION ALL SELECT EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, CDL.LINE_NUM CDL_LINE_NUM
, JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, PRJ.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, DECODE(EMP.FULL_NAME
, NULL
, HR.NAME
, EMP.FULL_NAME ) EMP_OR_ORG_NAME
, JEH.PERIOD_NAME PERIOD_NAME
, JEL.JE_LINE_NUM JE_LINE_NUMBER
, CDL.GL_DATE GL_DATE
, CDL.TRANSFERRED_DATE TRANSFERRED_DATE
, DECODE(GLREF.REFERENCE_3
, 'LIABILITY'
, TO_NUMBER(NULL)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'BTC'
, DECODE(CDL.LINE_TYPE
, 'R'
, (CDL.ACCT_BURDENED_COST+NVL(CDL.ACCT_BURDENED_CHANGE
, 0))
, CDL.ACCT_RAW_COST)
, CDL.ACCT_RAW_COST)) GL_DR_AMOUNT
, DECODE(GLREF.REFERENCE_3
, 'COST'
, TO_NUMBER(NULL)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'BTC'
, DECODE(CDL.LINE_TYPE
, 'R'
, (CDL.ACCT_BURDENED_COST+NVL(CDL.ACCT_BURDENED_CHANGE
, 0))
, DECODE(CDL.LINE_TYPE
, 'C'
, -CDL.ACCT_RAW_COST
, CDL.ACCT_RAW_COST))
, DECODE(CDL.LINE_TYPE
, 'C'
, -CDL.ACCT_RAW_COST
, CDL.ACCT_RAW_COST))) GL_CR_AMOUNT
, CDL.CR_CODE_COMBINATION_ID CR_CODE_COMBINATION_ID
, EXP.ORG_ID
FROM HR_ORGANIZATION_UNITS HR
, PER_PEOPLE_F EMP
, PA_PROJECTS_ALL PRJ
, PA_TASKS TASK
, PA_EXPENDITURES EXP
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_COST_DISTRIBUTION_LINES_ALL CDL
, GL_IMPORT_REFERENCES GLREF
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, PA_SYSTEM_LINKAGES SYSLINK
WHERE PRJ.PROJECT_ID = TASK.PROJECT_ID
AND EI.TASK_ID = TASK.TASK_ID
AND JEH.JE_CATEGORY = DECODE(CDL.LINE_TYPE
, 'R'
, SYSLINK.COST_JE_CATEGORY_NAME
, JEH.JE_CATEGORY)
AND EI.SYSTEM_LINKAGE_FUNCTION = SYSLINK.FUNCTION
AND CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CDL.TRANSFER_STATUS_CODE||'' = 'A'
AND CDL.ACCT_EVENT_ID IS NULL
AND CDL.BATCH_NAME = GLREF.REFERENCE_1
AND ((CDL.DR_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND JEL.REFERENCE_3 = 'COST'
AND EXISTS (SELECT 1
FROM GL_JE_LINES JEL1
WHERE JEL1.JE_HEADER_ID= JEL.JE_HEADER_ID
AND JEL1.REFERENCE_1 = JEL.REFERENCE_1
AND JEL1.CODE_COMBINATION_ID = CDL.CR_CODE_COMBINATION_ID ) ) OR (CDL.CR_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND JEL.REFERENCE_3 = 'LIABILITY'
AND EXISTS (SELECT 1
FROM GL_JE_LINES JEL1
WHERE JEL1.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEL1.REFERENCE_1 = JEL.REFERENCE_1
AND JEL1.CODE_COMBINATION_ID = CDL.DR_CODE_COMBINATION_ID ) ))
AND JEL.REFERENCE_3 IN ( 'COST'
, 'LIABILITY')
AND GLREF.JE_HEADER_ID = JEL.JE_HEADER_ID
AND GLREF.JE_LINE_NUM = JEL.JE_LINE_NUM
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.REVERSED_JE_HEADER_ID IS NULL
AND EXP.EXPENDITURE_ID = EI.EXPENDITURE_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 DECODE(EI.OVERRIDE_TO_ORGANIZATION_ID
, NULL
, EXP.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID) = HR.ORGANIZATION_ID