eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: APPS.MTH_EQUIP_SHFT_DD_MV#

Source

View Text - Preformatted


SELECT
TIME_D.DAY_ID,
TIME_D.CALENDAR_MONTH_ID,
DEPT_HIER.LEVEL8_LEVEL_KEY DEPT_KEY,
DEPT_HIER.LEVEL8_EFFECTIVE_DATE DEPT_EFF_DATE,
CUST_HIER.LEVEL9_LEVEL_KEY CUST_LEVEL9_KEY,
CUST_HIER.LEVEL9_EFFECTIVE_DATE CUST_LEVEL9_EFF_DATE,
SUM((EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME,
COUNT((EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_COUNT,
SUM(EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_VAL,
COUNT(EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_VAL_COUNT,
SUM(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN (EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME,
COUNT(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN (EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_COUNT,
SUM(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_VAL,
COUNT(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_VAL_COUNT,
COUNT(*)TOTAL_COUNT
FROM
MTH_EQUIPMENT_SHIFTS_D EQ_SHIFTS,
MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D,
MTH_EQUIPMENT_DENORM_D DEPT_HIER,
MTH_EQUIPMENT_DENORM_D CUST_HIER
WHERE  EQ_SHIFTS.ENTITY_TYPE='EQUIPMENT' AND 
EQ_SHIFTS.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY
AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2
AND DEPT_HIER.EQUIPMENT_FK_KEY = EQ_SHIFTS.EQUIPMENT_FK_KEY
AND TIME_D.FROM_DATE BETWEEN DEPT_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(DEPT_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY'))
AND CUST_HIER.EQUIPMENT_HIERARCHY_KEY = -3
AND CUST_HIER.EQUIPMENT_FK_KEY = EQ_SHIFTS.EQUIPMENT_FK_KEY
AND TIME_D.FROM_DATE BETWEEN CUST_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(CUST_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY'))
GROUP BY DEPT_HIER.LEVEL8_LEVEL_KEY, DEPT_HIER.LEVEL8_EFFECTIVE_DATE, TIME_D.DAY_ID,TIME_D.CALENDAR_MONTH_ID,CUST_HIER.LEVEL9_LEVEL_KEY,CUST_HIER.LEVEL9_EFFECTIVE_DATE

View Text - HTML Formatted

SELECT TIME_D.DAY_ID
, TIME_D.CALENDAR_MONTH_ID
, DEPT_HIER.LEVEL8_LEVEL_KEY DEPT_KEY
, DEPT_HIER.LEVEL8_EFFECTIVE_DATE DEPT_EFF_DATE
, CUST_HIER.LEVEL9_LEVEL_KEY CUST_LEVEL9_KEY
, CUST_HIER.LEVEL9_EFFECTIVE_DATE CUST_LEVEL9_EFF_DATE
, SUM((EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME
, COUNT((EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_COUNT
, SUM(EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_VAL
, COUNT(EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_VAL_COUNT
, SUM(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN (EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME
, COUNT(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN (EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_COUNT
, SUM(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_VAL
, COUNT(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_VAL_COUNT
, COUNT(*)TOTAL_COUNT FROM MTH_EQUIPMENT_SHIFTS_D EQ_SHIFTS
, MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D
, MTH_EQUIPMENT_DENORM_D DEPT_HIER
, MTH_EQUIPMENT_DENORM_D CUST_HIER WHERE EQ_SHIFTS.ENTITY_TYPE='EQUIPMENT'
AND EQ_SHIFTS.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2 AND DEPT_HIER.EQUIPMENT_FK_KEY = EQ_SHIFTS.EQUIPMENT_FK_KEY AND TIME_D.FROM_DATE BETWEEN DEPT_HIER.EQUIPMENT_EFFECTIVE_DATE
AND NVL(DEPT_HIER.EQUIPMENT_EXPIRATION_DATE
, TO_DATE('01/01/2100'
, 'MM/DD/YYYY')) AND CUST_HIER.EQUIPMENT_HIERARCHY_KEY = -3 AND CUST_HIER.EQUIPMENT_FK_KEY = EQ_SHIFTS.EQUIPMENT_FK_KEY AND TIME_D.FROM_DATE BETWEEN CUST_HIER.EQUIPMENT_EFFECTIVE_DATE
AND NVL(CUST_HIER.EQUIPMENT_EXPIRATION_DATE
, TO_DATE('01/01/2100'
, 'MM/DD/YYYY')) GROUP BY DEPT_HIER.LEVEL8_LEVEL_KEY
, DEPT_HIER.LEVEL8_EFFECTIVE_DATE
, TIME_D.DAY_ID
, TIME_D.CALENDAR_MONTH_ID
, CUST_HIER.LEVEL9_LEVEL_KEY
, CUST_HIER.LEVEL9_EFFECTIVE_DATE