[Home] [Help]
| Product: | MRP - Master Scheduling/MRP |
|---|---|
| Description: | An Integration Toolkit view supporting information regarding discrete jobs for the planned items. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT JOBS.PRIMARY_ITEM_ID
, ITEMS.ITEM_NUMBER
, JOBS.ORGANIZATION_ID
, JOBS.WIP_ENTITY_ID
, WIP.WIP_ENTITY_NAME
, JOBS.ALTERNATE_BOM_DESIGNATOR
, JOBS.ALTERNATE_ROUTING_DESIGNATOR
, JOBS.SCHEDULED_START_DATE
, JOBS.SCHEDULED_COMPLETION_DATE
, JOBS.START_QUANTITY
, JOBS.NET_QUANTITY
, GREATEST(0
, (JOBS.NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED))
, JOBS.QUANTITY_SCRAPPED
, JOBS.QUANTITY_COMPLETED
, GREATEST(0
, DECODE(JOBS.JOB_TYPE
, 1
, (JOBS.NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, NVL(ITEMS.SHRINKAGE_RATE
, 0))
, 0))
, JOBS.FIRM_PLANNED_FLAG
, JOBS.BOM_REVISION
, JOBS.STATUS_TYPE
, JOBS.JOB_TYPE
, LUP.MEANING
, TO_NUMBER(1)
, JOBS.ATTRIBUTE_CATEGORY
, JOBS.ATTRIBUTE1
, JOBS.ATTRIBUTE2
, JOBS.ATTRIBUTE3
, JOBS.ATTRIBUTE4
, JOBS.ATTRIBUTE5
, JOBS.ATTRIBUTE6
, JOBS.ATTRIBUTE7
, JOBS.ATTRIBUTE8
, JOBS.ATTRIBUTE9
, JOBS.ATTRIBUTE10
, JOBS.ATTRIBUTE11
, JOBS.ATTRIBUTE12
, JOBS.ATTRIBUTE13
, JOBS.ATTRIBUTE14
, JOBS.ATTRIBUTE15
FROM WIP_ENTITIES WIP
, WIP_DISCRETE_JOBS JOBS
, MFG_LOOKUPS LUP
, MRP_RHX_PLANNED_ITEMS_V ITEMS
WHERE ITEMS.INVENTORY_ITEM_ID = JOBS.PRIMARY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = JOBS.ORGANIZATION_ID
AND WIP.ORGANIZATION_ID = JOBS.ORGANIZATION_ID
AND WIP.WIP_ENTITY_ID = JOBS.WIP_ENTITY_ID
AND LUP.LOOKUP_CODE = JOBS.JOB_TYPE
AND LUP.LOOKUP_TYPE = 'WIP_DISCRETE_JOB'
AND JOBS.STATUS_TYPE IN (1
, 3
, 6) UNION SELECT WFS.PRIMARY_ITEM_ID
, ITEMS.ITEM_NUMBER
, ITEMS.ORGANIZATION_ID
, WFS.WIP_ENTITY_ID
, WIP.WIP_ENTITY_NAME
, WFS.ALTERNATE_BOM_DESIGNATOR
, WFS.ALTERNATE_ROUTING_DESIGNATOR
, WFS.SCHEDULED_START_DATE
, WFS.SCHEDULED_COMPLETION_DATE
, WFS.PLANNED_QUANTITY
, WFS.MPS_NET_QUANTITY
, WFS.PLANNED_QUANTITY
, TO_NUMBER(NULL)
, WFS.QUANTITY_COMPLETED
, TO_NUMBER(NULL)
, LKUP.LOOKUP_CODE
, WFS.BOM_REVISION
, WFS.STATUS
, TO_NUMBER(NULL)
, LUP.MEANING
, TO_NUMBER(2)
, WFS.ATTRIBUTE_CATEGORY
, WFS.ATTRIBUTE1
, WFS.ATTRIBUTE2
, WFS.ATTRIBUTE3
, WFS.ATTRIBUTE4
, WFS.ATTRIBUTE5
, WFS.ATTRIBUTE6
, WFS.ATTRIBUTE7
, WFS.ATTRIBUTE8
, WFS.ATTRIBUTE9
, WFS.ATTRIBUTE10
, WFS.ATTRIBUTE11
, WFS.ATTRIBUTE12
, WFS.ATTRIBUTE13
, WFS.ATTRIBUTE14
, WFS.ATTRIBUTE15
FROM WIP_ENTITIES WIP
, WIP_FLOW_SCHEDULES WFS
, MRP_RHX_PLANNED_ITEMS_V ITEMS
, MFG_LOOKUPS LUP
, MFG_LOOKUPS LKUP
WHERE ITEMS.INVENTORY_ITEM_ID = WFS.PRIMARY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = WFS.ORGANIZATION_ID
AND WIP.ORGANIZATION_ID = WFS.ORGANIZATION_ID
AND WIP.WIP_ENTITY_ID = WFS.WIP_ENTITY_ID
AND LUP.LOOKUP_TYPE='WIP_ENTITY'
AND LUP.LOOKUP_CODE=4
AND LKUP.LOOKUP_TYPE='SYS_YES_NO'
AND LKUP.MEANING='YES'