SELECT ALL
ASG.ORGANIZATION_ID,
ORG.NAME ORG_NAME,
PER.EMPLOYEE_NUMBER,
SHT.STANDARD_START,
PER.FULL_NAME,
DET.DATE_WORKED,
DET.TIME_IN,
TIM.PAYROLL_ID,
AEIV.HXT_ROTATION_PLAN,
SHT.NAME SHIFT_NAME,
HXT_HXT959A_XMLP_PKG.cf_tot_varformula(SHT.STANDARD_START, DET.TIME_IN) CF_TOT_VAR,
HXT_HXT959A_XMLP_PKG.cf_l_vlformula(SHT.STANDARD_START, DET.TIME_IN) CF_L_VL
FROM
PER_ASSIGNMENTS_F ASG,
PER_PEOPLE_F PER,
HXT_TIMECARDS_X TIM,
HXT_DET_HOURS_WORKED_X DET,
HR_ORGANIZATION_UNITS ORG,
HXT_PER_AEI_DDF_V AEIV,
HXT_ROTATION_PLANS ROT,
HXT_ROTATION_SCHEDULES RSC,
HXT_WEEKLY_WORK_SCHEDULES WWS,
HXT_WORK_SHIFTS WSH, HXT_SHIFTS SHT
WHERE
SHT.NAME <> 'Off'
and DET.TIME_IN is NOT NULL
and TIM.PAYROLL_ID = :P_PAYROLL_ID
and DET.DATE_WORKED >=:START_DATE
and DET.DATE_WORKED <= :END_DATE
and (ROT.ID=AEIV.HXT_ROTATION_PLAN)
AND (RSC.RTP_ID=ROT.ID)
AND RSC.START_DATE=
(SELECT
MAX(RSC.START_DATE)
FROM
HXT_ROTATION_SCHEDULES
WHERE
((RSC.RTP_ID=AEIV.HXT_ROTATION_PLAN)
AND RSC.START_DATE<=DET.DATE_WORKED))
AND (RSC.TWS_ID=WWS.ID)
and WWS.DATE_FROM<=DET.DATE_WORKED
AND ((WWS.DATE_TO IS NULL)
OR (WWS.DATE_TO>DET.DATE_WORKED))
AND WSH.WEEK_DAY=to_char(DET.DATE_WORKED,'DY')
AND (WSH.TWS_ID=WWS.ID)
AND ((SHT.ID=WSH.SHT_ID))
AND (DET.DATE_WORKED >= ORG.DATE_FROM)
AND ((ORG.DATE_TO is NULL) or (ORG.DATE_TO >=DET.DATE_WORKED))
and
(DET.DATE_WORKED >= ASG.EFFECTIVE_START_DATE)
and ((ASG.EFFECTIVE_END_DATE is NULL) or (ASG.EFFECTIVE_END_DATE >=DET.DATE_WORKED))
and
(DET.DATE_WORKED >= PER.EFFECTIVE_START_DATE)
and ((PER.EFFECTIVE_END_DATE is NULL) or (PER.EFFECTIVE_END_DATE >=DET.DATE_WORKED))
AND ((DET.TIM_ID=TIM.ID)
AND (TIM.FOR_PERSON_ID=PER.PERSON_ID)
AND (ASG.ASSIGNMENT_ID=AEIV.ASSIGNMENT_ID)
AND DET.DATE_WORKED BETWEEN AEIV.EFFECTIVE_START_DATE
AND AEIV.EFFECTIVE_END_DATE
AND (DET.ASSIGNMENT_ID=ASG.ASSIGNMENT_ID)
AND (ORG.ORGANIZATION_ID=ASG.ORGANIZATION_ID))
order by SHIFT_NAME |