VIEW: APPS.WIP_OPERATIONS_INQ_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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