View: WIP_OPERATIONS_INQ_V

Product:	WIP - Work in Process
Description:	In-queue quantities for operations
Implementation/DBA Data:	APPS.WIP_OPERATIONS_INQ_V

View Text

SELECT WO1.ROWID ROW_ID
, WO1.WIP_ENTITY_ID
, WO1.OPERATION_SEQ_NUM
, WO1.ORGANIZATION_ID
, WO1.REPETITIVE_SCHEDULE_ID
, DECODE(WE.ENTITY_TYPE
, 2
, NULL
, WE.WIP_ENTITY_NAME) "JOB_NAME"
, DECODE(WE.ENTITY_TYPE
, 2
, NULL
, WE.DESCRIPTION) "JOB_DESCRIPTION"
, WE.PRIMARY_ITEM_ID
, NVL(WDJ.STATUS_TYPE
, WRS.STATUS_TYPE) "STATUS_TYPE"
, MF1.MEANING "STATUS_CODE"
, WRS.LINE_ID
, WL.LINE_CODE
, NVL(WRS.FIRST_UNIT_START_DATE
, WDJ.SCHEDULED_START_DATE)
, WO1.LAST_UPDATE_DATE
, WO1.LAST_UPDATED_BY
, WO1.CREATION_DATE
, WO1.CREATED_BY
, WO1.LAST_UPDATE_LOGIN
, WO1.REQUEST_ID
, WO1.PROGRAM_APPLICATION_ID
, WO1.PROGRAM_ID
, WO1.PROGRAM_UPDATE_DATE
, WO1.OPERATION_SEQUENCE_ID
, WO1.STANDARD_OPERATION_ID
, BSO.OPERATION_CODE
, WO1.DEPARTMENT_ID
, BD1.DEPARTMENT_CODE
, BD1.LOCATION_ID
, WO1.DESCRIPTION
, WO1.SCHEDULED_QUANTITY
, DECODE(WO1.QUANTITY_IN_QUEUE
, 0
, NULL
, WO1.QUANTITY_IN_QUEUE)
, DECODE(WO1.QUANTITY_RUNNING
, 0
, NULL
, WO1.QUANTITY_RUNNING)
, DECODE(WO1.QUANTITY_WAITING_TO_MOVE
, 0
, NULL
, WO1.QUANTITY_WAITING_TO_MOVE)
, DECODE(WO1.QUANTITY_REJECTED
, 0
, NULL
, WO1.QUANTITY_REJECTED)
, DECODE(WO1.QUANTITY_SCRAPPED
, 0
, NULL
, WO1.QUANTITY_SCRAPPED)
, DECODE(WO1.QUANTITY_COMPLETED
, 0
, NULL
, WO1.QUANTITY_COMPLETED)
, DECODE( NVL(WDJ.STATUS_TYPE
, WRS.STATUS_TYPE)
, 1
, WO1.SCHEDULED_QUANTITY
, DECODE(NVL(WO1.QUANTITY_IN_QUEUE
, 0) + NVL(WO1.QUANTITY_RUNNING
, 0) + NVL(SUM(WO3.QUANTITY_IN_QUEUE)
, 0) + NVL(SUM(WO3.QUANTITY_RUNNING)
, 0) + NVL(SUM(WO3.QUANTITY_WAITING_TO_MOVE)
, 0) + NVL(SUM(WO3.QUANTITY_REJECTED)
, 0)
, 0
, NULL
, NVL(WO1.QUANTITY_IN_QUEUE
, 0) + NVL(WO1.QUANTITY_RUNNING
, 0) + NVL(SUM(WO3.QUANTITY_IN_QUEUE)
, 0) + NVL(SUM(WO3.QUANTITY_RUNNING)
, 0) + NVL(SUM(WO3.QUANTITY_WAITING_TO_MOVE)
, 0) + NVL(SUM(WO3.QUANTITY_REJECTED)
, 0))) "QUANTITY_REMAINING"
, WO1.FIRST_UNIT_START_DATE
, WO1.FIRST_UNIT_COMPLETION_DATE
, WO1.LAST_UNIT_START_DATE
, WO1.LAST_UNIT_COMPLETION_DATE
, WO1.PREVIOUS_OPERATION_SEQ_NUM
, WO1.NEXT_OPERATION_SEQ_NUM
, WO1.COUNT_POINT_TYPE
, DECODE(WO1.COUNT_POINT_TYPE
, 1
, 1
, 2) "COUNT_POINT_FLAG"
, DECODE(WO1.COUNT_POINT_TYPE
, 3
, 2
, 1) "AUTOCHARGE_FLAG"
, WO1.BACKFLUSH_FLAG
, WO1.MINIMUM_TRANSFER_QUANTITY
, WO1.DATE_LAST_MOVED
, WO1.ATTRIBUTE_CATEGORY
, WO1.ATTRIBUTE1
, WO1.ATTRIBUTE2
, WO1.ATTRIBUTE3
, WO1.ATTRIBUTE4
, WO1.ATTRIBUTE5
, WO1.ATTRIBUTE6
, WO1.ATTRIBUTE7
, WO1.ATTRIBUTE8
, WO1.ATTRIBUTE9
, WO1.ATTRIBUTE10
, WO1.ATTRIBUTE11
, WO1.ATTRIBUTE12
, WO1.ATTRIBUTE13
, WO1.ATTRIBUTE14
, WO1.ATTRIBUTE15
, BD2.DEPARTMENT_CODE "PREV_DEPARTMENT"
, WO2.LAST_UNIT_COMPLETION_DATE "PREV_LUCD"
, DECODE(WO2.QUANTITY_IN_QUEUE
, 0
, NULL
, WO2.QUANTITY_IN_QUEUE) "PREV_QUANTITY_IN_QUEUE"
, DECODE(WO2.QUANTITY_RUNNING
, 0
, NULL
, WO2.QUANTITY_RUNNING) "PREV_QUANTITY_RUNNING"
, DECODE(WO2.QUANTITY_COMPLETED
, 0
, NULL
, WO2.QUANTITY_COMPLETED) "PREV_QUANTITY_COMPLETED"
, DECODE(WO2.QUANTITY_REJECTED
, 0
, NULL
, WO2.QUANTITY_REJECTED) "PREV_QUANTITY_TO_MOVE"
, WO1.PROGRESS_PERCENTAGE
FROM MFG_LOOKUPS MF1
, WIP_REPETITIVE_SCHEDULES WRS
, WIP_DISCRETE_JOBS WDJ
, WIP_LINES WL
, WIP_ENTITIES WE
, BOM_DEPARTMENTS BD1
, BOM_DEPARTMENTS BD2
, BOM_STANDARD_OPERATIONS BSO
, WIP_OPERATIONS WO1
, WIP_OPERATIONS WO2
, WIP_OPERATIONS WO3
WHERE MF1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND MF1.LOOKUP_CODE = NVL(WDJ.STATUS_TYPE
, WRS.STATUS_TYPE)
AND WO1.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WO1.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WE.ENTITY_TYPE NOT IN (6
, 7)
AND BD1.DEPARTMENT_ID = WO1.DEPARTMENT_ID
AND BSO.STANDARD_OPERATION_ID(+) = WO1.STANDARD_OPERATION_ID
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WO1.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID(+) = WO1.ORGANIZATION_ID
AND WL.LINE_ID(+) = WRS.LINE_ID
AND WDJ.ORGANIZATION_ID(+) = WO1.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID(+) = WO1.WIP_ENTITY_ID
AND WL.ORGANIZATION_ID(+) = WRS.ORGANIZATION_ID
AND WO2.OPERATION_SEQ_NUM(+) = WO1.PREVIOUS_OPERATION_SEQ_NUM
AND WO2.ORGANIZATION_ID(+) = WO1.ORGANIZATION_ID
AND WO2.WIP_ENTITY_ID(+) = WO1.WIP_ENTITY_ID
AND NVL(WO2.REPETITIVE_SCHEDULE_ID(+)
, -1) = NVL(WO1.REPETITIVE_SCHEDULE_ID
, -1)
AND BD2.DEPARTMENT_ID(+) = WO2.DEPARTMENT_ID
AND WO3.ORGANIZATION_ID(+) = WO1.ORGANIZATION_ID
AND WO3.WIP_ENTITY_ID(+) = WO1.WIP_ENTITY_ID
AND NVL(WO3.REPETITIVE_SCHEDULE_ID(+)
, -1) = NVL(WO1.REPETITIVE_SCHEDULE_ID
, -1)
AND WO3.OPERATION_SEQ_NUM(+) < WO1.OPERATION_SEQ_NUM
AND NVL(BSO.OPERATION_TYPE
, 1) = 1
AND BSO.LINE_ID IS NULL GROUP BY WO1.ROWID
, WO1.WIP_ENTITY_ID
, WO1.OPERATION_SEQ_NUM
, WO1.ORGANIZATION_ID
, WO1.REPETITIVE_SCHEDULE_ID
, DECODE(WE.ENTITY_TYPE
, 2
, NULL
, WE.WIP_ENTITY_NAME)
, DECODE(WE.ENTITY_TYPE
, 2
, NULL
, WE.DESCRIPTION)
, WE.PRIMARY_ITEM_ID
, NVL(WDJ.STATUS_TYPE
, WRS.STATUS_TYPE)
, MF1.MEANING
, WRS.LINE_ID
, WL.LINE_CODE
, NVL(WRS.FIRST_UNIT_START_DATE
, WDJ.SCHEDULED_START_DATE)
, WO1.LAST_UPDATE_DATE
, WO1.LAST_UPDATED_BY
, WO1.CREATION_DATE
, WO1.CREATED_BY
, WO1.LAST_UPDATE_LOGIN
, WO1.REQUEST_ID
, WO1.PROGRAM_APPLICATION_ID
, WO1.PROGRAM_ID
, WO1.PROGRAM_UPDATE_DATE
, WO1.OPERATION_SEQUENCE_ID
, WO1.STANDARD_OPERATION_ID
, BSO.OPERATION_CODE
, WO1.DEPARTMENT_ID
, BD1.DEPARTMENT_CODE
, BD1.LOCATION_ID
, WO1.DESCRIPTION
, WO1.SCHEDULED_QUANTITY
, WO1.QUANTITY_IN_QUEUE
, WO1.QUANTITY_RUNNING
, WO1.QUANTITY_WAITING_TO_MOVE
, WO1.QUANTITY_REJECTED
, WO1.QUANTITY_SCRAPPED
, WO1.QUANTITY_COMPLETED
, WO1.FIRST_UNIT_START_DATE
, WO1.FIRST_UNIT_COMPLETION_DATE
, WO1.LAST_UNIT_START_DATE
, WO1.LAST_UNIT_COMPLETION_DATE
, WO1.PREVIOUS_OPERATION_SEQ_NUM
, WO1.NEXT_OPERATION_SEQ_NUM
, WO1.COUNT_POINT_TYPE
, DECODE(WO1.COUNT_POINT_TYPE
, 1
, 1
, 2)
, DECODE(WO1.COUNT_POINT_TYPE
, 3
, 2
, 1)
, WO1.BACKFLUSH_FLAG
, WO1.MINIMUM_TRANSFER_QUANTITY
, WO1.DATE_LAST_MOVED
, WO1.ATTRIBUTE_CATEGORY
, WO1.ATTRIBUTE1
, WO1.ATTRIBUTE2
, WO1.ATTRIBUTE3
, WO1.ATTRIBUTE4
, WO1.ATTRIBUTE5
, WO1.ATTRIBUTE6
, WO1.ATTRIBUTE7
, WO1.ATTRIBUTE8
, WO1.ATTRIBUTE9
, WO1.ATTRIBUTE10
, WO1.ATTRIBUTE11
, WO1.ATTRIBUTE12
, WO1.ATTRIBUTE13
, WO1.ATTRIBUTE14
, WO1.ATTRIBUTE15
, BD2.DEPARTMENT_CODE
, WO2.LAST_UNIT_COMPLETION_DATE
, WO2.QUANTITY_IN_QUEUE
, WO2.QUANTITY_RUNNING
, WO2.QUANTITY_COMPLETED
, WO2.QUANTITY_REJECTED
, WO1.PROGRESS_PERCENTAGE

Columns