VIEW: APPS.PA_ALLOC_TRANSACTIONS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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