[Home] [Help]
View: HXC_RESOURCE_TOTAL_TIME_V
View Text
SELECT HTB.TIME_BUILDING_BLOCK_ID TIME_ID
, HTB.RESOURCE_ID
, HTB_DAY.TIME_BUILDING_BLOCK_ID DAY_ID
, HTB_DETAIL.TIME_BUILDING_BLOCK_ID DETAIL_ID
, HTB.APPROVAL_STATUS STATUS_CODE
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('HXC_APPROVAL_STATUS'
, HTB.APPROVAL_STATUS)
, 1
, 80) STATUS_NAME
, TO_DATE(TO_CHAR(HTB_DAY.START_TIME
, 'DD-MON-YYYY')
, 'DD-MON-YYYY') START_DATE
, TO_DATE(TO_CHAR(HTB_DAY.STOP_TIME
, 'DD-MON-YYYY')
, 'DD-MON-YYYY') STOP_DATE
, SUM(NVL(HXC_TIME_CATEGORY_UTILS_PKG.CATEGORY_DETAIL_HRS( HTB_DETAIL.TIME_BUILDING_BLOCK_ID
, HTB_DETAIL.OBJECT_VERSION_NUMBER)
, 0)) HOURS_WORKED FROM HXC_TIME_BUILDING_BLOCKS HTB
, HXC_TIME_BUILDING_BLOCKS HTB_DAY
, HXC_TIME_BUILDING_BLOCKS HTB_DETAIL
WHERE HTB_DAY.TIME_BUILDING_BLOCK_ID=HTB_DETAIL.PARENT_BUILDING_BLOCK_ID AND HTB.TIME_BUILDING_BLOCK_ID=HTB_DAY.PARENT_BUILDING_BLOCK_ID AND HTB_DETAIL.SCOPE='DETAIL'
AND HTB_DETAIL.DATE_TO=HR_GENERAL.END_OF_TIME AND HTB_DAY.SCOPE='DAY' AND HTB_DAY.TYPE='RANGE'
AND HTB_DAY.DATE_TO=HR_GENERAL.END_OF_TIME AND HTB.SCOPE='TIMECARD' AND HTB.RESOURCE_TYPE='PERSON' AND HTB.TYPE='RANGE' AND HTB.DATE_TO=HR_GENERAL.END_OF_TIME GROUP BY HTB.TIME_BUILDING_BLOCK_ID
, HTB.RESOURCE_ID
, HTB_DAY.TIME_BUILDING_BLOCK_ID
, HTB_DETAIL.TIME_BUILDING_BLOCK_ID
, HTB.APPROVAL_STATUS
, HR_GENERAL.DECODE_LOOKUP('HXC_APPROVAL_STATUS'
, HTB.APPROVAL_STATUS)
, TO_DATE(TO_CHAR(HTB_DAY.START_TIME
, 'DD-MON-YYYY')
, 'DD-MON-YYYY')
, TO_DATE(TO_CHAR(HTB_DAY.STOP_TIME
, 'DD-MON-YYYY')
, 'DD-MON-YYYY')
Columns
Name |
TIME_ID |
RESOURCE_ID |
DAY_ID |
DETAIL_ID |
STATUS_CODE |
STATUS_NAME |
START_DATE |
STOP_DATE |
HOURS_WORKED |