View: HXCFV_APPROVAL_REPORT

View Text

SELECT HXC_APPROVAL_REPORT_PKG.GET_BUSINESS_GROUP_NAME(HAP.RESOURCE_ID) BUSINESS_GROUP_NAME
, HAP.APPROVAL_STATUS APPROVAL_STATUS
, HAP.START_TIME APPROVAL_START_TIME
, HAP.STOP_TIME APPROVAL_STOP_TIME
, HTDAY.DAY_START_TIME DAY
, HTDAY.DAY_STOP_TIME DAY_STOP_TIME
, HTDETAIL.DETAIL_MEASURE HOURS
, TO_CHAR(HTDETAIL.DETAIL_START_TIME
, 'HH:MI:SS') TIME_IN
, TO_CHAR(HTDETAIL.DETAIL_STOP_TIME
, 'HH:MI:SS') TIME_OUT
, PPF.FULL_NAME EMPLOYEE_NAME
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, NVL(PPF2.FULL_NAME
, ' ') SUPERVISOR_NAME
, POU.NAME ORGANIZATION_NAME
, HXC_APPROVAL_REPORT_PKG.GET_APPLICATION_NAME(HTA.ATTRIBUTE1) APPLICATION
, HXC_APPROVAL_REPORT_PKG.GET_ELEMENT_NAME( SUBSTR(HXC_TIMECARD_ATTRIBUTES_PKG.GET_TIMECARD_ATTRIBUTE(HTDETAIL.DETAIL_TIMECARD_ID
, HTDETAIL.DETAIL_OBJECT_VERSION
, 'OTL DEPOSIT PROCESS MAPPING'
, 'DUMMY ELEMENT CONTEXT')
, 11
, 20)) HOURS_TYPE
, PCAK.CONCATENATED_SEGMENTS COST_CENTER
, PAPF.PAYROLL_NAME PAYROLL_NAME
, HXC_APPROVAL_REPORT_PKG.GET_EMPLOYEE_NAME(HTA.ATTRIBUTE3) NEXT_APPROVER
, HXC_APPROVAL_REPORT_PKG.GET_EMPLOYEE_NAME (HXC_APPROVAL_REPORT_PKG.GET_LAST_APRV (HAP.TIME_BUILDING_BLOCK_ID
, HAP1.OBJECT_VERSION_NUMBER)) LAST_APPROVER
, HXC_TIMECARD_ATTRIBUTES_PKG.GET_TIMECARD_ATTRIBUTE (HTDETAIL.DETAIL_TIMECARD_ID
, HTDETAIL.DETAIL_OBJECT_VERSION
, 'OTL DEPOSIT PROCESS MAPPING'
, 'PROJECT_ID') PROJECT_NAME
, HXC_APPROVAL_REPORT_PKG.GET_PROJECT_NAME (HXC_TIMECARD_ATTRIBUTES_PKG.GET_TIMECARD_ATTRIBUTE (HTDETAIL.DETAIL_TIMECARD_ID
, HTDETAIL.DETAIL_OBJECT_VERSION
, 'OTL DEPOSIT PROCESS MAPPING'
, 'PROJECT_ID')) PROJECT_NUMBER
, HXC_TIMECARD_ATTRIBUTES_PKG.GET_TIMECARD_ATTRIBUTE (HTDETAIL.DETAIL_TIMECARD_ID
, HTDETAIL.DETAIL_OBJECT_VERSION
, 'OTL DEPOSIT PROCESS MAPPING'
, 'TASK_ID') TASK_NAME
, HXC_APPROVAL_REPORT_PKG.GET_TASK_NAME( HXC_TIMECARD_ATTRIBUTES_PKG.GET_TIMECARD_ATTRIBUTE( HTDETAIL.DETAIL_TIMECARD_ID
, HTDETAIL.DETAIL_OBJECT_VERSION
, 'OTL DEPOSIT PROCESS MAPPING'
, 'TASK_ID')) TASK_NUMBER
, HXC_TIMECARD_ATTRIBUTES_PKG.GET_TIMECARD_ATTRIBUTE( HTDETAIL.DETAIL_TIMECARD_ID
, HTDETAIL.DETAIL_OBJECT_VERSION
, 'OTL DEPOSIT PROCESS MAPPING'
, 'EXPENDITURE_TYPE') EXPENDITURE_TYPE
, HAP.TIME_BUILDING_BLOCK_ID TIMECARD_ID
, HAP.OBJECT_VERSION_NUMBER TIMECARD_OVN
, HAP.RESOURCE_ID RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS HAP
, HXC_TIME_ATTRIBUTE_USAGES HTAU
, HXC_TIME_ATTRIBUTES HTA
, HXC_TIME_BUILDING_BLOCKS HAP1
, HXC_TIMECARD_DAYS_V HTDAY
, HXC_TIMECARD_DETAIL_HOURS_V HTDETAIL
, HXC_TIMECARDS_V HT
, PER_ALL_PEOPLE_F PPF
, PER_ALL_PEOPLE_F PPF2
, PER_ALL_ASSIGNMENTS_F PAAF
, PAY_COST_ALLOCATION_KEYFLEX PCAK
, PAY_ALL_PAYROLLS_F PAPF
, PER_ORGANIZATION_UNITS POU
WHERE PAAF.PERSON_ID=PPF.PERSON_ID
AND PAAF.PRIMARY_FLAG='Y'
AND PPF2.PERSON_ID(+)=PAAF.SUPERVISOR_ID
AND PCAK.COST_ALLOCATION_KEYFLEX_ID(+)=PAAF.SOFT_CODING_KEYFLEX_ID
AND PAPF.PAYROLL_ID(+) = PAAF.PAYROLL_ID
AND POU.ORGANIZATION_ID(+) = PAAF.ORGANIZATION_ID
AND HAP.SCOPE = 'APPLICATION_PERIOD'
AND HAP.TYPE = 'RANGE'
AND HAP.DATE_TO = HR_GENERAL.END_OF_TIME
AND HAP.RESOURCE_TYPE = 'PERSON'
AND HTAU.TIME_ATTRIBUTE_USAGE_ID= ( SELECT MAX(TIME_ATTRIBUTE_USAGE_ID)
FROM HXC_TIME_ATTRIBUTE_USAGES
WHERE TIME_BUILDING_BLOCK_ID = HAP.TIME_BUILDING_BLOCK_ID
AND TIME_BUILDING_BLOCK_OVN = HAP1.OBJECT_VERSION_NUMBER )
AND HTA.TIME_ATTRIBUTE_ID = HTAU.TIME_ATTRIBUTE_ID
AND HAP.OBJECT_VERSION_NUMBER = (SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE TIME_BUILDING_BLOCK_ID = HAP.TIME_BUILDING_BLOCK_ID )
AND HAP1.TIME_BUILDING_BLOCK_ID = HAP.TIME_BUILDING_BLOCK_ID
AND HAP1.APPROVAL_STATUS = 'SUBMITTED'
AND HAP1.OBJECT_VERSION_NUMBER = (SELECT MAX (OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE TIME_BUILDING_BLOCK_ID= HAP1.TIME_BUILDING_BLOCK_ID
AND APPROVAL_STATUS = 'SUBMITTED')
AND HTDAY.DAY_START_TIME >= HAP.START_TIME
AND HTDAY.DAY_START_TIME <= HAP.STOP_TIME
AND HTDAY.DAY_RESOURCE_ID = HAP.RESOURCE_ID
AND HTDETAIL.DETAIL_PARENT_TIMECARD_ID = HTDAY.DAY_TIMECARD_ID
AND HTDETAIL.DETAIL_PARENT_OBJECT_VERSION = HTDAY.DAY_TIMECARD_OVN
AND HT.TCRD_TIME_BUILDING_BLOCK_ID = HTDAY.DAY_PARENT_ID
AND HT.TCRD_OBJECT_VERSION_NUMBER = HTDAY.DAY_PARENT_OVN
AND HR_GENERAL.GET_BUSINESS_GROUP_ID = NVL(PPF.BUSINESS_GROUP_ID
, 0)
AND PPF.PERSON_ID = HAP.RESOURCE_ID WITH READ ONLY

Columns