View: WIP_DEPT_IMM_DISPATCH_V

Product:	WIP - Work in Process
Description:	Department immediate dispatch list
Implementation/DBA Data:	APPS.WIP_DEPT_IMM_DISPATCH_V

View Text

SELECT WE.WIP_ENTITY_NAME || ':' || WO.OPERATION_SEQ_NUM /* || ':' || WOR.RESOURCE_SEQ_NUM */ "JOB_OP_NAME"
, WO.WIP_ENTITY_ID
, WO.ORGANIZATION_ID
, WE.WIP_ENTITY_NAME
, MSI.CONCATENATED_SEGMENTS "ITEM_NAME"
, WO.OPERATION_SEQ_NUM
, BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, ML.MEANING "BACKFLUSH_FLAG"
, WO.SCHEDULED_QUANTITY
, WO.QUANTITY_IN_QUEUE
, WO.QUANTITY_RUNNING
, WO.QUANTITY_WAITING_TO_MOVE
, WO.QUANTITY_REJECTED
, WO.QUANTITY_SCRAPPED
, /* FIX FOR BUG 1042071: REGARD ONLY QUANTITIES THAT HAVE MADE IT TO THE NEXT OP'S QUEUE
, OR INTO INVENTORY
, AS COMPLETE. */ (WO.QUANTITY_COMPLETED - WO.QUANTITY_WAITING_TO_MOVE - WO.QUANTITY_REJECTED - WO.QUANTITY_SCRAPPED) AS QUANTITY_COMPLETED
, WDJ.STATUS_TYPE
, WO.FIRST_UNIT_START_DATE
, WO.FIRST_UNIT_COMPLETION_DATE
, WO.LAST_UNIT_START_DATE
, WO.LAST_UNIT_COMPLETION_DATE
, BSO.OPERATION_CODE
, MSI.DESCRIPTION "ITEM_DESCRIPTION"
, MSI.PRIMARY_UOM_CODE
, WO.NEXT_OPERATION_SEQ_NUM
, WO.PREVIOUS_OPERATION_SEQ_NUM
, WO.MINIMUM_TRANSFER_QUANTITY
, /* BUG 5172100 - FETCHING PROJECT
AND TASK NUMBERS INSTEAD OF NAMES PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID)
, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID)
, BUG 5172100 ADDED NEXT TWO SELECT COLUMNS */ PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID)
, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID)
, WO.DESCRIPTION
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, DECODE(WDJ.STATUS_TYPE
, 6
, 'RED'
, NULL) NODE_TEXT_COLOR
, DECODE(WDJ.STATUS_TYPE
, 6
, 'WPJOBOPH.GIF'
, NULL) NODE_ICON
, WDJ.SERIALIZATION_START_OP
FROM MFG_LOOKUPS ML
, BOM_STANDARD_OPERATIONS BSO
, BOM_DEPARTMENTS BD
, MTL_SYSTEM_ITEMS_KFV MSI
, WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, WIP_OPERATIONS WO
WHERE /* YOU NEED NOT JOIN REPETITIVE SCHEDULE AS IT'S NOT REQUIRED */ WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND WO.ORGANIZATION_ID = BD.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WE.ENTITY_TYPE = 1 /* ONLY DISCRETE JOBS */
AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN (3
, 4
, 6) /* RELEASED
AND COMPLETE CHARGES ALLOWED
, ON HOLD */
AND ML.LOOKUP_CODE = WO.BACKFLUSH_FLAG
AND ML.LOOKUP_TYPE = 'SYS_YES_NO'
AND MSI.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND MSI.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND (WO.QUANTITY_WAITING_TO_MOVE+WO.QUANTITY_IN_QUEUE+WO.QUANTITY_RUNNING) > 0
AND BSO.STANDARD_OPERATION_ID (+) = WO.STANDARD_OPERATION_ID
AND NVL(BSO.OPERATION_TYPE
, 1) = 1
AND BSO.LINE_ID IS NULL GROUP BY WO.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WO.ORGANIZATION_ID
, MSI.CONCATENATED_SEGMENTS
, WO.OPERATION_SEQ_NUM
, BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, ML.MEANING
, WO.SCHEDULED_QUANTITY
, WO.QUANTITY_IN_QUEUE
, WO.QUANTITY_RUNNING
, WO.QUANTITY_WAITING_TO_MOVE
, WO.QUANTITY_REJECTED
, WO.QUANTITY_SCRAPPED
, WO.QUANTITY_COMPLETED
, WDJ.STATUS_TYPE
, WO.FIRST_UNIT_START_DATE
, WO.FIRST_UNIT_COMPLETION_DATE
, WO.LAST_UNIT_START_DATE
, WO.LAST_UNIT_COMPLETION_DATE
, BSO.OPERATION_CODE
, MSI.DESCRIPTION
, MSI.PRIMARY_UOM_CODE
, WO.NEXT_OPERATION_SEQ_NUM
, WO.PREVIOUS_OPERATION_SEQ_NUM
, WO.MINIMUM_TRANSFER_QUANTITY
, WDJ.PROJECT_ID
, WDJ.TASK_ID
, WO.DESCRIPTION
, WDJ.STATUS_TYPE
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.SERIALIZATION_START_OP WITH READ ONLY

Columns