VIEW: APPS.MTH_EQUIP_PROD_SCH_DD_MV#

Source

View Text - Preformatted

SELECT
TIME_D.DAY_ID,
TIME_D.CALENDAR_MONTH_ID,
SUM(EQUIP_PROD.PLANNED_QTY_G)PLANNED_QTY_G,
COUNT(EQUIP_PROD.PLANNED_QTY_G)PLANNED_QTY_G_COUNT,
SUM(EQUIP_PROD.PLANNED_QTY_SG)PLANNED_QTY_SG,
COUNT(EQUIP_PROD.PLANNED_QTY_SG)PLANNED_QTY_SG_COUNT,
SUM(EQUIP_PROD.ITEM_COST*EQUIP_PROD.PLANNED_QTY_G)SHIFT_SCH_PROD,
COUNT(EQUIP_PROD.ITEM_COST*EQUIP_PROD.PLANNED_QTY_G)SHIFT_SCH_PROD_COUNT,
SUM(NVL(EQUIP_PROD.PLANNED_QTY_G/STANDARD_RATE_1,0))HRS_SCH_QTY,
COUNT(NVL(EQUIP_PROD.PLANNED_QTY_G/STANDARD_RATE_1,0))HRS_SCH_QTY_COUNT,
COUNT(*) TOTAL_COUNT,
EQUIP_PROD.STATUS_CODE,
IT_DENORM.LEVEL9_FK_KEY PRODUCT_LEVEL9_KEY,
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
FROM
MTH_EQUIP_PROD_SCHEDULE_F EQUIP_PROD,
MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D,
MTH_ITEM_DENORM_D IT_DENORM,
MTH_EQUIPMENT_DENORM_D DEPT_HIER,
MTH_EQUIPMENT_DENORM_D CUST_HIER
WHERE
EQUIP_PROD.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY AND
IT_DENORM.HIERARCHY_ID = -5 AND
IT_DENORM.ITEM_FK_KEY = EQUIP_PROD.ITEM_FK_KEY AND
DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2 AND
DEPT_HIER.EQUIPMENT_FK_KEY = EQUIP_PROD.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 = EQUIP_PROD.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,IT_DENORM.LEVEL9_FK_KEY, CUST_HIER.LEVEL9_LEVEL_KEY,CUST_HIER.LEVEL9_EFFECTIVE_DATE,STATUS_CODE

View Text - HTML Formatted

SELECT TIME_D.DAY_ID
, TIME_D.CALENDAR_MONTH_ID
, SUM(EQUIP_PROD.PLANNED_QTY_G)PLANNED_QTY_G
, COUNT(EQUIP_PROD.PLANNED_QTY_G)PLANNED_QTY_G_COUNT
, SUM(EQUIP_PROD.PLANNED_QTY_SG)PLANNED_QTY_SG
, COUNT(EQUIP_PROD.PLANNED_QTY_SG)PLANNED_QTY_SG_COUNT
, SUM(EQUIP_PROD.ITEM_COST*EQUIP_PROD.PLANNED_QTY_G)SHIFT_SCH_PROD
, COUNT(EQUIP_PROD.ITEM_COST*EQUIP_PROD.PLANNED_QTY_G)SHIFT_SCH_PROD_COUNT
, SUM(NVL(EQUIP_PROD.PLANNED_QTY_G/STANDARD_RATE_1
, 0))HRS_SCH_QTY
, COUNT(NVL(EQUIP_PROD.PLANNED_QTY_G/STANDARD_RATE_1
, 0))HRS_SCH_QTY_COUNT
, COUNT(*) TOTAL_COUNT
, EQUIP_PROD.STATUS_CODE
, IT_DENORM.LEVEL9_FK_KEY PRODUCT_LEVEL9_KEY
, 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 FROM MTH_EQUIP_PROD_SCHEDULE_F EQUIP_PROD
, MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D
, MTH_ITEM_DENORM_D IT_DENORM
, MTH_EQUIPMENT_DENORM_D DEPT_HIER
, MTH_EQUIPMENT_DENORM_D CUST_HIER WHERE EQUIP_PROD.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY AND IT_DENORM.HIERARCHY_ID = -5 AND IT_DENORM.ITEM_FK_KEY = EQUIP_PROD.ITEM_FK_KEY AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2 AND DEPT_HIER.EQUIPMENT_FK_KEY = EQUIP_PROD.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 = EQUIP_PROD.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
, IT_DENORM.LEVEL9_FK_KEY
, CUST_HIER.LEVEL9_LEVEL_KEY
, CUST_HIER.LEVEL9_EFFECTIVE_DATE
, STATUS_CODE