[Home] [Help]
View: WIP_FLOW_MIX_LINEARITY_V
View Text
SELECT SUBVIEW.ORGANIZATION_ID
, SUBVIEW.LINE_ID
, SUBVIEW.CDATE "SCHEDULED_COMPLETION_DATE"
, SUM(SUBVIEW.PLANNED) "TOTAL_PLANNED_QTY"
, SUM(SUBVIEW.COMPLETED) "TOTAL_QTY_COMPLETED"
, SUM(ABS(SUBVIEW.PLANNED - SUBVIEW.COMPLETED)) "SUM_ITEM_DIFFERENCE"
, GREATEST( DECODE(SUM(SUBVIEW.PLANNED)
, 0
, DECODE(SUM(ABS(SUBVIEW.PLANNED - SUBVIEW.COMPLETED))
, 0
, 100
, 0)
, ( (1 - SUM(ABS(SUBVIEW.PLANNED - SUBVIEW.COMPLETED)) / SUM(SUBVIEW.PLANNED)) * 100 ))
, 0) "MIX_PERCENT"
FROM (SELECT TRUNC(WIP_SFCB_UTILITIES.SDATE_TO_CDATE(WFS.SCHEDULED_COMPLETION_DATE)) CDATE
, WFS.ORGANIZATION_ID
, WFS.LINE_ID
, WFS.PRIMARY_ITEM_ID
, SUM(WFS.PLANNED_QUANTITY) PLANNED
, SUM(WFS.QUANTITY_COMPLETED) COMPLETED
FROM WIP_FLOW_SCHEDULES WFS GROUP BY WFS.ORGANIZATION_ID
, WFS.LINE_ID
, TRUNC(WIP_SFCB_UTILITIES.SDATE_TO_CDATE(WFS.SCHEDULED_COMPLETION_DATE))
, WFS.PRIMARY_ITEM_ID ) SUBVIEW GROUP BY SUBVIEW.ORGANIZATION_ID
, SUBVIEW.LINE_ID
, SUBVIEW.CDATE
Columns
Name |
ORGANIZATION_ID |
LINE_ID |
SCHEDULED_COMPLETION_DATE |
TOTAL_PLANNED_QTY |
TOTAL_QTY_COMPLETED |
SUM_ITEM_DIFFERENCE |
MIX_PERCENT |