VIEW: APPS.HXT_TIMECARDS_FMV

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)