View: PA_ALLOC_TRANSACTIONS_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_ALLOC_TRANSACTIONS_V

View Text

SELECT ATD.ALLOC_TXN_ID
, ATD.RUN_ID
, ATD.RULE_ID
, ATD.TRANSACTION_TYPE
, PL1.MEANING
, ATD.LINE_NUM
, ATD.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, ATD.TASK_ID
, PT.TASK_NUMBER
, PT.TASK_NAME
, ATD.EXPENDITURE_TYPE
, ATD.CINT_SOURCE_TASK_ID
, ATD.CINT_EXP_ORG_ID
, PT2.TASK_NUMBER
, PT2.TASK_NAME
, HRTL1.NAME
, MAX(ATD.CINT_RATE_MULTIPLIER)
, MAX(ATD.CINT_PRIOR_BASIS_AMT)
, MAX(ATD.CINT_CURRENT_BASIS_AMT)
, MAX(ATD.CINT_PRIOR_BASIS_AMT + ATD.CINT_CURRENT_BASIS_AMT)
, SUM(ABD.BASIS_PERCENT)
, MAX(ABD.LINE_PERCENT)
, SUM(ABD.BASIS_PERCENT)*MAX(ABD.LINE_PERCENT)/100
, MAX(ATD.TOTAL_ALLOCATION)
, MAX(ATD.PREVIOUS_ALLOCATION)
, MAX(ATD.CURRENT_ALLOCATION)
, MAX(ATD.REJECTION_CODE)
, MAX(PL2.MEANING)
FROM PA_PROJECTS_ALL PP
, PA_TASKS PT
, PA_LOOKUPS PL1
, PA_LOOKUPS PL2
, PA_ALLOC_RUN_BASIS_DET ABD
, PA_ALLOC_TXN_DETAILS ATD
, PA_TASKS PT2
, HR_ALL_ORGANIZATION_UNITS HR1
, HR_ALL_ORGANIZATION_UNITS_TL HRTL1
WHERE ATD.RUN_ID = ABD.RUN_ID (+)
AND ATD.LINE_NUM = ABD.LINE_NUM (+)
AND ATD.PROJECT_ID = ABD.PROJECT_ID (+)
AND ATD.TASK_ID = ABD.TASK_ID (+)
AND ATD.PROJECT_ID = PP.PROJECT_ID
AND ATD.TASK_ID = PT.TASK_ID AND ATD.REJECTION_CODE IS NOT NULL
AND ATD.REJECTION_CODE = PL2.LOOKUP_CODE
AND PL2.LOOKUP_TYPE IN ('TRANSACTION REJECTION REASON'
, 'TRANSACTION USER REJ REASON')
AND ATD.TRANSACTION_TYPE = PL1.LOOKUP_CODE(+)
AND PL1.LOOKUP_TYPE = 'ALLOC_TXN_TYPE'
AND ATD.CINT_SOURCE_TASK_ID = PT2.TASK_ID(+)
AND ATD.CINT_EXP_ORG_ID = HR1.ORGANIZATION_ID(+)
AND HR1.ORGANIZATION_ID = HRTL1.ORGANIZATION_ID(+)
AND NVL(HRTL1.LANGUAGE
, USERENV('LANG'))=USERENV('LANG') GROUP BY ATD.ALLOC_TXN_ID
, ATD.RUN_ID
, ATD.RULE_ID
, ATD.TRANSACTION_TYPE
, PL1.MEANING
, ATD.LINE_NUM
, ATD.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, ATD.TASK_ID
, PT.TASK_NUMBER
, PT.TASK_NAME
, ATD.EXPENDITURE_TYPE
, ATD.CINT_SOURCE_TASK_ID
, ATD.CINT_EXP_ORG_ID
, PT2.TASK_NUMBER
, PT2.TASK_NAME
, HRTL1.NAME UNION SELECT ATD.ALLOC_TXN_ID
, ATD.RUN_ID
, ATD.RULE_ID
, ATD.TRANSACTION_TYPE
, PL1.MEANING
, ATD.LINE_NUM
, ATD.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, ATD.TASK_ID
, PT.TASK_NUMBER
, PT.TASK_NAME
, ATD.EXPENDITURE_TYPE
, ATD.CINT_SOURCE_TASK_ID
, ATD.CINT_EXP_ORG_ID
, PT2.TASK_NUMBER
, PT2.TASK_NAME
, HRTL1.NAME
, MAX(ATD.CINT_RATE_MULTIPLIER)
, MAX(ATD.CINT_PRIOR_BASIS_AMT)
, MAX(ATD.CINT_CURRENT_BASIS_AMT)
, MAX(ATD.CINT_PRIOR_BASIS_AMT + ATD.CINT_CURRENT_BASIS_AMT)
, SUM(ABD.BASIS_PERCENT)
, MAX(ABD.LINE_PERCENT)
, SUM(ABD.BASIS_PERCENT)*MAX(ABD.LINE_PERCENT)/100
, MAX(ATD.TOTAL_ALLOCATION)
, MAX(ATD.PREVIOUS_ALLOCATION)
, MAX(ATD.CURRENT_ALLOCATION)
, MAX(ATD.REJECTION_CODE)
, NULL
FROM PA_PROJECTS_ALL PP
, PA_TASKS PT
, PA_LOOKUPS PL1
, PA_ALLOC_RUN_BASIS_DET ABD
, PA_ALLOC_TXN_DETAILS ATD
, PA_TASKS PT2
, HR_ALL_ORGANIZATION_UNITS HR1
, HR_ALL_ORGANIZATION_UNITS_TL HRTL1
WHERE ATD.RUN_ID = ABD.RUN_ID (+)
AND ATD.LINE_NUM = ABD.LINE_NUM (+)
AND ATD.PROJECT_ID = ABD.PROJECT_ID (+)
AND ATD.TASK_ID = ABD.TASK_ID (+)
AND ATD.PROJECT_ID = PP.PROJECT_ID
AND ATD.TASK_ID = PT.TASK_ID
AND ATD.REJECTION_CODE IS NULL
AND ATD.TRANSACTION_TYPE = PL1.LOOKUP_CODE(+)
AND PL1.LOOKUP_TYPE = 'ALLOC_TXN_TYPE'
AND ATD.CINT_SOURCE_TASK_ID = PT2.TASK_ID(+)
AND ATD.CINT_EXP_ORG_ID = HR1.ORGANIZATION_ID(+)
AND HR1.ORGANIZATION_ID = HRTL1.ORGANIZATION_ID(+)
AND NVL(HRTL1.LANGUAGE
, USERENV('LANG'))=USERENV('LANG') GROUP BY ATD.ALLOC_TXN_ID
, ATD.RUN_ID
, ATD.RULE_ID
, ATD.TRANSACTION_TYPE
, PL1.MEANING
, ATD.LINE_NUM
, ATD.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, ATD.TASK_ID
, PT.TASK_NUMBER
, PT.TASK_NAME
, ATD.EXPENDITURE_TYPE
, ATD.CINT_SOURCE_TASK_ID
, ATD.CINT_EXP_ORG_ID
, PT2.TASK_NUMBER
, PT2.TASK_NAME
, HRTL1.NAME

Columns