SELECT PPS.DISTRIBUTION_BASIS_CODE,
PET.ELEMENT_NAME ,
PPS.COST_TYPE_CODE ,
PPD.DENOM_RAW_COST ,
PROJ1.segment1 PAYROLL_PROJECT ,
PROJ2.segment1 TC_PROJECT_ID ,
SUBSTR(hr1.name,1,30) PAYROLL_ORGANIZATION ,
SUBSTR(hr2.name,1,30) TC_ORGANIZATION ,
TASK1.task_number PAYROLL_TASK ,
TASK2.task_number TC_TASK_ID ,
DECODE (PPD.system_linkage_function,'ST','ST',
'OT','OT',
'PJ','Misc',
'BTC','Burden' )system_linkage_function ,
PPD.TARGET_EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID ,
PPD.EXPENDITURE_ITEM_DATE ,
'PA_PAY_' || Decode( PPS.DISTRIBUTION_BASIS_CODE,
'ST_AMT', 'AMT' ,
'OT_AMT', 'AMT',
'ST_OT_AMT' ,'AMT',
'STHOURS', decode(pps.timecard_element,'YES','HRY','HRN'),
'OTHOURS', Decode(PPS.TIMECARD_ELEMENT,'YES','HRY','HRN'),
'TOTAL_HOURS' ,Decode(PPS.TIMECARD_ELEMENT,'YES','HRY','HRN'),
'NONE' ,'NONE')
||(CASE WHEN DISTRIBUTION_BASIS_CODE <> 'NONE' THEN
Decode(PPD.system_linkage_function ,
'ST','_TC',
'OT','_TC',
'PJ','_PJ',
'BTC','_BTC'
)
END)
||(CASE WHEN DISTRIBUTION_BASIS_CODE <> 'NONE' THEN
'_' || PRIORITY
END) MSG
FROM PA_PAY_SOURCE_AMOUNTS PPS,
PA_PROJECTS PROJ1,
PA_PROJECTS PROJ2,
PA_TASKS TASK1,
PA_TASKS TASK2,
PA_PAY_DIST_LINES PPD,
PA_PAY_AUDIT PPA ,
PAY_ELEMENT_TYPES_F PET,
hr_organization_units hr1,
hr_organization_units hr2
WHERE PPA.INTERFACE_RUN_ID = :P_INTERFACE_RUN_ID
AND PPA.INT_EXT_INDICATOR = 'INT'
AND PPS.person_id = :P_PERSON_ID
AND PPA.interface_run_id = pps.interface_run_id
AND PPS.INTERFACE_RUN_ID = PPD.INTERFACE_RUN_ID
AND PPS.PAY_SOURCE_ID = PPD.PAY_SOURCE_ID
AND PPD.DENOM_CURRENCY_CODE = :CURRENCY
and ppd.pay_element_type_id = pet.element_type_id
and pay_source_project_id = proj1.project_id(+)
and ppd.project_id = proj2.project_id(+)
and pay_source_task_id = task1.task_id(+)
and ppd.task_id = task2.task_id(+)
and pay_source_organization_id = hr1.organization_id(+)
AND PPD.ORGANIZATION_ID = hr2.organization_id(+)
UNION
SELECT PPS.DISTRIBUTION_BASIS_CODE,
PET.LOOKUP_CODE ELEMENT_NAME ,
PPS.COST_TYPE_CODE ,
PPD.DENOM_RAW_COST ,
PROJ1.segment1 PAYROLL_PROJECT ,
PROJ2.segment1 TC_PROJECT_ID ,
SUBSTR(hr1.name,1,30) PAYROLL_ORGANIZATION ,
SUBSTR(hr2.name,1,30) TC_ORGANIZATION ,
TASK1.task_number PAYROLL_TASK ,
TASK2.task_number TC_TASK_ID ,
DECODE (PPD.system_linkage_function,'ST','ST',
'OT','OT',
'PJ','Misc',
'BTC','Burden' )system_linkage_function ,
PPD.TARGET_EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID ,
PPD.EXPENDITURE_ITEM_DATE ,
'PA_PAY_' || Decode( PPS.DISTRIBUTION_BASIS_CODE,
'ST_AMT', 'AMT' ,
'OT_AMT', 'AMT',
'ST_OT_AMT' ,'AMT',
'STHOURS', decode(pps.timecard_element,'YES','HRY','HRN'),
'OTHOURS', Decode(PPS.TIMECARD_ELEMENT,'YES','HRY','HRN'),
'TOTAL_HOURS' ,Decode(PPS.TIMECARD_ELEMENT,'YES','HRY','HRN'),
'NONE' ,'NONE')
||(CASE WHEN DISTRIBUTION_BASIS_CODE <> 'NONE' THEN
Decode(PPD.system_linkage_function ,
'ST','_TC',
'OT','_TC',
'PJ','_PJ',
'BTC','_BTC'
)
END)
||(CASE WHEN DISTRIBUTION_BASIS_CODE <> 'NONE' THEN
'_' || PRIORITY
END) MSG
FROM PA_PAY_SOURCE_AMOUNTS PPS,
PA_PROJECTS PROJ1,
PA_PROJECTS PROJ2,
PA_TASKS TASK1,
PA_TASKS TASK2,
PA_PAY_DIST_LINES PPD,
PA_PAY_AUDIT PPA ,
(SELECT LOOKUP_TYPE,
LOOKUP_CODE,
MEANING
FROM pa_lookups
WHERE lookup_type = 'PA_THIRD_PARTY_PAY_ELEMENTS'
) PET,
hr_organization_units hr1,
hr_organization_units hr2
WHERE PPA.INTERFACE_RUN_ID = :P_INTERFACE_RUN_ID
AND PPS.person_id = :P_PERSON_ID
AND PPA.INT_EXT_INDICATOR = 'EXT'
AND PPA.interface_run_id = pps.interface_run_id
AND PPS.INTERFACE_RUN_ID = PPD.INTERFACE_RUN_ID
AND PPS.PAY_SOURCE_ID = PPD.PAY_SOURCE_ID
AND PPD.DENOM_CURRENCY_CODE = :CURRENCY
AND PPD.PAY_ELEMENT_TYPE_CODE = PET.LOOKUP_CODE
and pay_source_project_id = proj1.project_id(+)
and ppd.project_id = proj2.project_id(+)
AND PAY_SOURCE_TASK_ID = TASK1.task_id(+)
and ppd.task_id = task2.task_id(+)
and pay_source_organization_id = hr1.organization_id(+)
AND PPD.ORGANIZATION_ID = hr2.organization_id(+)
UNION
SELECT PPS.DISTRIBUTION_BASIS_CODE,
PET.ELEMENT_NAME ,
PPS.COST_TYPE_CODE ,
NULL DENOM_RAW_COST,
PROJ1.segment1 PAYROLL_PROJECT ,
NULL TC_PROJECT_ID ,
SUBSTR(hr1.name,1,30) PAYROLL_ORGANIZATION ,
NULL TC_ORGANIZATION,
TASK1.task_number PAYROLL_TASK ,
NULL TC_TASK_ID ,
NULL SYSTEM_LINKAGE_FUNCTION,
NULL EXPENDITURE_ITEM_ID,
NULL EXPENDITURE_ITEM_DATE ,
REJ.REJECTION_CODE MSG
FROM PA_PAY_SOURCE_AMOUNTS PPS,
PA_PROJECTS PROJ1,
PA_TASKS TASK1,
pa_pay_audit ppa ,
pa_pay_rejections_all REJ,
PAY_ELEMENT_TYPES_F PET,
hr_organization_units hr1
WHERE PPA.INTERFACE_RUN_ID = :P_INTERFACE_RUN_ID
AND PPS.person_id = :P_PERSON_ID
AND PPA.INT_EXT_INDICATOR = 'INT'
AND PPA.interface_run_id = pps.interface_run_id
AND PPS.INTERFACE_RUN_ID = REJ.INTERFACE_RUN_ID
AND PPS.PAY_SOURCE_ID = REJ.PAY_SOURCE_ID
AND PPS.PAY_CURRENCY_CODE = :CURRENCY
AND PPA.INTERFACE_RUN_ID = REJ.INTERFACE_RUN_ID
and pet.element_type_id = rej.pay_element_type_id
AND PAY_SOURCE_PROJECT_ID = PROJ1.project_id(+)
and pay_source_task_id = task1.task_id(+)
AND PAY_SOURCE_ORGANIZATION_ID = hr1.organization_id(+)
UNION
SELECT PPS.DISTRIBUTION_BASIS_CODE,
PET.LOOKUP_CODE ELEMENT_NAME ,
PPS.COST_TYPE_CODE ,
NULL DENOM_RAW_COST,
PROJ1.segment1 PAYROLL_PROJECT ,
NULL TC_PROJECT_ID ,
SUBSTR(hr1.name,1,30) PAYROLL_ORGANIZATION ,
NULL TC_ORGANIZATION,
TASK1.task_number PAYROLL_TASK ,
NULL TC_TASK_ID,
NULL SYSTEM_LINKAGE_FUNCTION,
NULL EXPENDITURE_ITEM_ID,
NULL EXPENDITURE_ITEM_DATE ,
REJ.REJECTION_CODE MSG
FROM PA_PAY_SOURCE_AMOUNTS PPS,
PA_PROJECTS PROJ1,
PA_TASKS TASK1,
PA_PAY_AUDIT PPA ,
pa_pay_rejections_all REJ,
(SELECT LOOKUP_TYPE,
LOOKUP_CODE,
MEANING
FROM pa_lookups
WHERE lookup_type = 'PA_THIRD_PARTY_PAY_ELEMENTS'
) PET,
hr_organization_units hr1
WHERE PPA.INTERFACE_RUN_ID = :P_INTERFACE_RUN_ID
AND PPS.person_id = :P_PERSON_ID
AND PPA.INT_EXT_INDICATOR = 'EXT'
AND PPA.interface_run_id = pps.interface_run_id
AND PPS.INTERFACE_RUN_ID = REJ.INTERFACE_RUN_ID
AND PPS.PAY_SOURCE_ID = REJ.PAY_SOURCE_ID
AND pps.pay_currency_code = :CURRENCY
AND pet.lookup_code = rej.pay_element_type_code
and pay_source_project_id = proj1.project_id(+)
and pay_source_task_id = task1.task_id(+)
AND PAY_SOURCE_ORGANIZATION_ID = hr1.organization_id(+) |
