View: MRP_AP_WIP_JOBS_V

View Text

SELECT WDJ.PRIMARY_ITEM_ID INVENTORY_ITEM_ID
, WDJ.ORGANIZATION_ID
, WDJ.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WDJ.MPS_NET_QUANTITY
, GREATEST(0
, (WDJ.MPS_NET_QUANTITY - WDJ.QUANTITY_COMPLETED - WDJ.QUANTITY_SCRAPPED)) MPS_JOB_QUANTITY
, WDJ.NET_QUANTITY NET_QUANTITY
, GREATEST(0
, (WDJ.NET_QUANTITY - WDJ.QUANTITY_COMPLETED - WDJ.QUANTITY_SCRAPPED)) JOB_QUANTITY
, TRUNC(WDJ.MPS_SCHEDULED_COMPLETION_DATE) MPS_SCHEDULED_COMPLETION_DATE
, TRUNC(WDJ.SCHEDULED_COMPLETION_DATE) SCHEDULED_COMPLETION_DATE
, WDJ.QUANTITY_SCRAPPED
, WDJ.QUANTITY_COMPLETED
, GREATEST(0
, DECODE(WDJ.JOB_TYPE
, 1
, (WDJ.MPS_NET_QUANTITY - WDJ.QUANTITY_COMPLETED - WDJ.QUANTITY_SCRAPPED) * DECODE(SIGN(MSI.SHRINKAGE_RATE)
, -1
, 0
, NVL(MSI.SHRINKAGE_RATE
, 0))
, 0)) MPS_EXPECTED_SCRAP_QUANTITY
, GREATEST(0
, DECODE(WDJ.JOB_TYPE
, 1
, (WDJ.NET_QUANTITY - WDJ.QUANTITY_COMPLETED - WDJ.QUANTITY_SCRAPPED) * DECODE(SIGN(MSI.SHRINKAGE_RATE)
, -1
, 0
, NVL( MSI.SHRINKAGE_RATE
, 0))
, 0)) EXPECTED_SCRAP_QUANTITY
, WDJ.FIRM_PLANNED_FLAG FIRM_PLANNED_STATUS_TYPE
, TRUNC(WDJ.SCHEDULED_START_DATE) START_DATE
, WDJ.BOM_REVISION REVISION
, WDJ.STATUS_TYPE STATUS_CODE
, WDJ.JOB_TYPE WIP_JOB_TYPE
, WDJ.DEMAND_CLASS
, WDJ.PROJECT_ID
, WDJ.TASK_ID
, WDJ.SCHEDULE_GROUP_ID
, WDJ.BUILD_SEQUENCE
, WDJ.LINE_ID
, WDJ.ALTERNATE_ROUTING_DESIGNATOR
, WDJ.ALTERNATE_BOM_DESIGNATOR
, 1 ENTITY_TYPE
, WDJ.END_ITEM_UNIT_NUMBER
, WSG.SCHEDULE_GROUP_NAME
, WDJ.RN RN3
, MSI.RN RN2
, MP.RN RN1
FROM WIP_SCHEDULE_GROUPS WSG
, WIP_ENTITIES WE
, MRP_AP_SYS_ITEMS_SN MSI
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_DSCR_JOBS_SN WDJ
WHERE WSG.SCHEDULE_GROUP_ID(+)= WDJ.SCHEDULE_GROUP_ID
AND MP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND MSI.ORGANIZATION_ID= WDJ.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID= WDJ.PRIMARY_ITEM_ID UNION ALL SELECT WFS.PRIMARY_ITEM_ID INVENTORY_ITEM_ID
, WFS.ORGANIZATION_ID
, WFS.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WFS.MPS_NET_QUANTITY
, GREATEST(0
, (WFS.PLANNED_QUANTITY - WFS.QUANTITY_COMPLETED)) MPS_JOB_QUANTITY
, GREATEST(0
, (WFS.PLANNED_QUANTITY -WFS.QUANTITY_COMPLETED)) NET_QUANTITY
, GREATEST(0
, (WFS.PLANNED_QUANTITY - WFS.QUANTITY_COMPLETED)) JOB_QUANTITY
, TRUNC(WFS.MPS_SCHEDULED_COMPLETION_DATE) MPS_SCHEDULED_COMPLETION_DATE
, TRUNC(WFS.SCHEDULED_COMPLETION_DATE) SCHEDULED_COMPLETION_DATE
, 0 QUANTITY_SCRAPPED
, WFS.QUANTITY_COMPLETED
, GREATEST(0
, ((WFS.MPS_NET_QUANTITY - WFS.QUANTITY_COMPLETED) * DECODE(SIGN(MSI.SHRINKAGE_RATE)
, -1
, 0
, NVL(MSI.SHRINKAGE_RATE
, 0)))) EXPECTED_SCRAP_QUANTITY
, GREATEST(0
, ((WFS.PLANNED_QUANTITY - WFS.QUANTITY_COMPLETED) * DECODE(SIGN(MSI.SHRINKAGE_RATE)
, -1
, 0
, NVL(MSI.SHRINKAGE_RATE
, 0)))) FIRM_PLANNED_STATUS_TYPE
, 2 FIRM_PLANNED_STATUS_TYPE
, TRUNC(WFS.SCHEDULED_START_DATE) START_DATE
, WFS.BOM_REVISION REVISION
, WFS.STATUS STATUS_CODE
, TO_NUMBER(NULL) WIP_JOB_TYPE
, WFS.DEMAND_CLASS
, WFS.PROJECT_ID
, WFS.TASK_ID
, WFS.SCHEDULE_GROUP_ID
, WFS.BUILD_SEQUENCE
, WFS.LINE_ID
, WFS.ALTERNATE_ROUTING_DESIGNATOR
, WFS.ALTERNATE_BOM_DESIGNATOR
, 4 ENTITY_TYPE
, WFS.END_ITEM_UNIT_NUMBER
, WSG.SCHEDULE_GROUP_NAME
, WFS.RN RN3
, MSI.RN RN2
, MP.RN RN1
FROM WIP_SCHEDULE_GROUPS WSG
, WIP_ENTITIES WE
, MRP_AP_SYS_ITEMS_SN MSI
, MRP_AP_MRP_PARAS_SN MP
, MRP_AP_FLOW_SCHDS_SN WFS
WHERE WSG.SCHEDULE_GROUP_ID(+)= WFS.SCHEDULE_GROUP_ID
AND MP.ORGANIZATION_ID = WFS.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = WFS.WIP_ENTITY_ID
AND MSI.ORGANIZATION_ID= WFS.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID= WFS.PRIMARY_ITEM_ID

Columns