View: PA_CC_GL_COST_XFER_AUDIT_V

Product:	PA - Projects
Description:	View for Cross Charge Audit by GL Account showing the Cross Charge Distribution Lines and the corresponding entries in Oracle General Ledger
Implementation/DBA Data:	APPS.PA_CC_GL_COST_XFER_AUDIT_V

View Text

SELECT CCD.CC_DIST_LINE_ID CC_DIST_LINE_ID
, CCD.LINE_TYPE CC_DIST_LINE_TYPE
, EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, CCD.LINE_NUM CC_DIST_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
, AEH.AE_HEADER_ID JE_HEADER_ID
, AEL.AE_LINE_NUM JE_LINE_NUMBER
, CCD.GL_DATE GL_DATE
, CCD.TRANSFERRED_DATE TRANSFERRED_DATE
, XDL.UNROUNDED_ACCOUNTED_DR GL_DR_AMOUNT
, XDL.UNROUNDED_ACCOUNTED_CR GL_CR_AMOUNT
FROM PA_CC_DIST_LINES_ALL CCD
, PA_PROJECTS_ALL PRJ
, PA_TASKS TASK
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL EXP
, PER_PEOPLE_F EMP
, HR_ALL_ORGANIZATION_UNITS_TL HR
, PA_IMPLEMENTATIONS IMP
, XLA_ACCT_CLASS_ASSGNS XACA
, XLA_ASSIGNMENT_DEFNS_B XAD
, XLA_POST_ACCT_PROGS_B XPAP
WHERE PRJ.PROJECT_ID = CCD.PROJECT_ID
AND CCD.TASK_ID = TASK.TASK_ID
AND CCD.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CCD.TRANSFER_STATUS_CODE||'' = 'A'
AND CCD.ACCT_EVENT_ID IS NOT NULL
AND CCD.ORG_ID = IMP.ORG_ID
AND XDL.APPLICATION_ID = 275
AND EI.EXPENDITURE_ITEM_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND CCD.LINE_NUM = XDL.SOURCE_DISTRIBUTION_ID_NUM_2
AND XDL.SOURCE_DISTRIBUTION_TYPE = CCD.LINE_TYPE
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 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 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 NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, EXP.INCURRED_BY_ORGANIZATION_ID) = HR.ORGANIZATION_ID
AND ((HR.LANGUAGE = USERENV('LANG')
AND HR.ORGANIZATION_ID IS NOT NULL) OR (HR.ORGANIZATION_ID IS NULL)) UNION ALL SELECT CCD.CC_DIST_LINE_ID CC_DIST_LINE_ID
, CCD.LINE_TYPE CC_DIST_LINE_TYPE
, EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, CCD.LINE_NUM CC_DIST_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
, JEH.JE_HEADER_ID JE_HEADER_ID
, JEL.JE_LINE_NUM JE_LINE_NUMBER
, CCD.GL_DATE GL_DATE
, CCD.TRANSFERRED_DATE TRANSFERRED_DATE
, DECODE(GLREF.REFERENCE_3
, 'CROSS CHARGE DEBIT'
, CCD.AMOUNT
, TO_NUMBER(NULL)) GL_DR_AMOUNT
, DECODE(GLREF.REFERENCE_3
, 'CROSS CHARGE CREDIT'
, CCD.AMOUNT
, TO_NUMBER(NULL)) GL_CR_AMOUNT
FROM PA_CC_DIST_LINES_ALL CCD
, PA_PROJECTS_ALL PRJ
, PA_TASKS TASK
, GL_IMPORT_REFERENCES GLREF
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES EXP
, PER_PEOPLE_F EMP
, HR_ALL_ORGANIZATION_UNITS_TL HR
WHERE PRJ.PROJECT_ID = CCD.PROJECT_ID
AND CCD.TASK_ID = TASK.TASK_ID
AND CCD.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CCD.TRANSFER_STATUS_CODE||'' = 'A'
AND CCD.ACCT_EVENT_ID IS NULL
AND CCD.GL_BATCH_NAME = GLREF.REFERENCE_1 || ''
AND ((CCD.DR_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND GLREF.REFERENCE_3 = 'CROSS CHARGE DEBIT') OR (CCD.CR_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND GLREF.REFERENCE_3 = 'CROSS CHARGE CREDIT'))
AND GLREF.REFERENCE_3 IN ( 'CROSS CHARGE DEBIT'
, 'CROSS CHARGE CREDIT')
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.CURRENCY_CODE = CCD.DENOM_TP_CURRENCY_CODE
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 NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, EXP.INCURRED_BY_ORGANIZATION_ID) = HR.ORGANIZATION_ID
AND ((HR.LANGUAGE = USERENV('LANG')
AND HR.ORGANIZATION_ID IS NOT NULL) OR (HR.ORGANIZATION_ID IS NULL))

Columns