Product: | HXT - Time and Labor |
---|---|
Description: | |
Implementation/DBA Data: | APPS.HXT_TIMECARDS_FMV |
SELECT /*+ CARDINALITY(FCL2 5) */ TIM.ROWID ROW_ID
, TIM.ID
, TIM.EFFECTIVE_START_DATE
, TIM.EFFECTIVE_END_DATE
, TIM.FOR_PERSON_ID
, PER.EMPLOYEE_NUMBER
, PER.FULL_NAME
, TIM.TIME_PERIOD_ID
, PTP.END_DATE PERIOD_END_DATE
, PTP.START_DATE PERIOD_START_DATE
, TIM.AUTO_GEN_FLAG
, FCL.MEANING TIMECARD_SOURCE
, TIM.BATCH_ID
, HBS.STATUS BATCH_STATUS_CD
, FCL2.MEANING BATCH_STATUS
, PBH.BATCH_NAME BATCH_NAME
, TIM.APPROV_PERSON_ID
, PER2.FULL_NAME APPROVER_NAME
, TIM.APPROVED_TIMESTAMP
, TIM.CREATED_BY
, TIM.CREATION_DATE
, TIM.LAST_UPDATED_BY
, TIM.LAST_UPDATE_DATE
, TIM.LAST_UPDATE_LOGIN
, TIM.PAYROLL_ID
, PAY.PAYROLL_NAME
, PAY.BUSINESS_GROUP_ID PAY_BUS_GRP_ID
, TIM.ATTRIBUTE1
, TIM.ATTRIBUTE2
, TIM.ATTRIBUTE3
, TIM.ATTRIBUTE4
, TIM.ATTRIBUTE5
, TIM.ATTRIBUTE6
, TIM.ATTRIBUTE7
, TIM.ATTRIBUTE8
, TIM.ATTRIBUTE9
, TIM.ATTRIBUTE10
, TIM.ATTRIBUTE11
, TIM.ATTRIBUTE12
, TIM.ATTRIBUTE13
, TIM.ATTRIBUTE14
, TIM.ATTRIBUTE15
, TIM.ATTRIBUTE16
, TIM.ATTRIBUTE17
, TIM.ATTRIBUTE18
, TIM.ATTRIBUTE19
, TIM.ATTRIBUTE20
, TIM.ATTRIBUTE21
, TIM.ATTRIBUTE22
, TIM.ATTRIBUTE23
, TIM.ATTRIBUTE24
, TIM.ATTRIBUTE25
, TIM.ATTRIBUTE26
, TIM.ATTRIBUTE27
, TIM.ATTRIBUTE28
, TIM.ATTRIBUTE29
, TIM.ATTRIBUTE30
, TIM.ATTRIBUTE_CATEGORY
FROM PER_TIME_PERIODS PTP
, PER_PEOPLE_F PER
, PER_PEOPLE_F PER2
, PAY_PAYROLLS_F PAY
, HXT_BATCH_STATES HBS
, PAY_BATCH_HEADERS PBH
, HR_LOOKUPS FCL
, HR_LOOKUPS FCL2
, HXT_TIMECARDS TIM
WHERE TIM.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND TIM.FOR_PERSON_ID = PER.PERSON_ID
AND PTP.END_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND TIM.PAYROLL_ID = PAY.PAYROLL_ID
AND PTP.END_DATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE
AND TIM.BATCH_ID = HBS.BATCH_ID
AND TIM.BATCH_ID = PBH.BATCH_ID(+)
AND FCL.APPLICATION_ID = 808
AND FCL.LOOKUP_TYPE = 'HXT_TIMECARD_SOURCE'
AND FCL.LOOKUP_CODE = TIM.AUTO_GEN_FLAG
AND FCL2.APPLICATION_ID = 808
AND FCL2.LOOKUP_TYPE = 'HXT_BATCH_STATUS'
AND FCL2.LOOKUP_CODE = HBS.STATUS
AND TIM.APPROV_PERSON_ID = PER2.PERSON_ID(+)
AND TIM.APPROVED_TIMESTAMP BETWEEN PER2.EFFECTIVE_START_DATE(+)
AND PER2.EFFECTIVE_END_DATE(+)