View: WIP_RES_UPS_DISPATCH_V

Product:	WIP - Work in Process
Description:	Resource upstream dispatch list
Implementation/DBA Data:	APPS.WIP_RES_UPS_DISPATCH_V

View Text

SELECT DECODE(SUBSTR(WE.WIP_ENTITY_NAME
, 1
, 1)
, 'R'
, NULL
, 1) DUMMY_NULL_UK_COLUMN
, WE.WIP_ENTITY_NAME || ':' || WO1.OPERATION_SEQ_NUM /* || ':' || WOR.RESOURCE_SEQ_NUM */ "JOB_OP_NAME"
, WO1.WIP_ENTITY_ID
, WO1.ORGANIZATION_ID
, WE.WIP_ENTITY_NAME "WIP_ENTITY_NAME"
, MSI.CONCATENATED_SEGMENTS "ITEM_NAME"
, WO1.OPERATION_SEQ_NUM "OPERATION_SEQ_NUM"
, BD1.DEPARTMENT_ID
, BD1.DEPARTMENT_CODE
, DECODE(COUNT(WOR.RESOURCE_SEQ_NUM)
, 1
, SUM(WOR.RESOURCE_SEQ_NUM)
, NULL) RESOURCE_SEQ_NUM
, WOR.RESOURCE_ID
, WOR.RESOURCE_CODE
, WO2.OPERATION_SEQ_NUM "FROM_OPERATION_SEQ_NUM"
, BD2.DEPARTMENT_ID "FROM_DEPARTMENT_ID"
, BD2.DEPARTMENT_CODE "FROM_DEPARTMENT_CODE"
, ML1.MEANING "COUNT_POINT"
, ML2.MEANING "BACKFLUSH_FLAG"
, WO1.SCHEDULED_QUANTITY "SCHEDULED_QUANTITY"
, WO1.QUANTITY_IN_QUEUE "QUANTITY_IN_QUEUE"
, WO1.QUANTITY_RUNNING "QUANTITY_RUNNING"
, WO1.QUANTITY_WAITING_TO_MOVE "QUANTITY_WAITING_TO_MOVE"
, WO1.QUANTITY_REJECTED "QUANTITY_REJECTED"
, WO1.QUANTITY_SCRAPPED "QUANTITY_SCRAPPED"
, WO1.QUANTITY_COMPLETED "QUANTITY_COMPLETED"
, WO1.FIRST_UNIT_START_DATE
, WO1.FIRST_UNIT_COMPLETION_DATE
, WO1.LAST_UNIT_START_DATE
, WO1.LAST_UNIT_COMPLETION_DATE
, BSO.OPERATION_CODE
, MSI.DESCRIPTION "ITEM_DESCRIPTION"
, MSI.PRIMARY_UOM_CODE
, WO1.NEXT_OPERATION_SEQ_NUM
, WO1.PREVIOUS_OPERATION_SEQ_NUM
, WO1.MINIMUM_TRANSFER_QUANTITY
, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID)
, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID)
, WO1.DESCRIPTION
, WDJ.STATUS_TYPE
, DECODE(WDJ.STATUS_TYPE
, 6
, 'RED'
, NULL) NODE_TEXT_COLOR
, DECODE(WDJ.STATUS_TYPE
, 6
, 'WPJOBOPH.GIF'
, NULL) NODE_ICON
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.SERIALIZATION_START_OP
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, BOM_STANDARD_OPERATIONS BSO
, BOM_DEPARTMENTS BD1
, BOM_DEPARTMENTS BD2
, MTL_SYSTEM_ITEMS_KFV MSI
, WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, WIP_OPERATION_RESOURCES_V WOR
, WIP_OPERATIONS WO1
, WIP_OPERATIONS WO2
WHERE WOR.ORGANIZATION_ID = WO1.ORGANIZATION_ID
AND WOR.WIP_ENTITY_ID = WO1.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WO1.OPERATION_SEQ_NUM /* YOU DONOT HAVE TO WIRRY ABOUT REPETITIVE SCHEDULES */
AND WE.WIP_ENTITY_ID = WO1.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = WO1.ORGANIZATION_ID
AND WE.ENTITY_TYPE = 1 /* ENTITY TYPE */
AND WDJ.WIP_ENTITY_ID = WO1.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WO1.ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN (3
, 4
, 6) /* RELEASED
, COMPLETE CHARGES ALLOWED
,
AND HOLD */
AND ML1.LOOKUP_CODE = WO1.COUNT_POINT_TYPE
AND ML1.LOOKUP_TYPE = 'BOM_COUNT_POINT_TYPE'
AND ML2.LOOKUP_CODE = WO1.BACKFLUSH_FLAG
AND ML2.LOOKUP_TYPE = 'SYS_YES_NO'
AND MSI.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND MSI.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND BD1.DEPARTMENT_ID = NVL(WOR.DEPARTMENT_ID
, WO1.DEPARTMENT_ID)
AND BD1.ORGANIZATION_ID =WO1.ORGANIZATION_ID
AND WO2.NEXT_OPERATION_SEQ_NUM = WO1.OPERATION_SEQ_NUM
AND WO2.WIP_ENTITY_ID = WO1.WIP_ENTITY_ID
AND WO2.ORGANIZATION_ID = WO1.ORGANIZATION_ID
AND WO2.NEXT_OPERATION_SEQ_NUM = WO1.OPERATION_SEQ_NUM
AND WO2.WIP_ENTITY_ID = WO1.WIP_ENTITY_ID
AND WO2.ORGANIZATION_ID = WO1.ORGANIZATION_ID
AND (WO2.QUANTITY_WAITING_TO_MOVE+WO2.QUANTITY_IN_QUEUE+WO2.QUANTITY_RUNNING)>0
AND BD2.DEPARTMENT_ID = NVL(WOR.DEPARTMENT_ID
, WO2.DEPARTMENT_ID)
AND BD2.ORGANIZATION_ID = WO2.ORGANIZATION_ID
AND BSO.STANDARD_OPERATION_ID (+) = WO1.STANDARD_OPERATION_ID
AND NVL(BSO.OPERATION_TYPE
, 1) = 1
AND BSO.LINE_ID IS NULL GROUP BY WE.WIP_ENTITY_NAME
, WO1.WIP_ENTITY_ID
, WO1.ORGANIZATION_ID
, MSI.CONCATENATED_SEGMENTS
, WO1.OPERATION_SEQ_NUM
, BD1.DEPARTMENT_ID
, BD1.DEPARTMENT_CODE
, WOR.RESOURCE_ID
, WOR.RESOURCE_CODE
, WO2.OPERATION_SEQ_NUM
, BD2.DEPARTMENT_ID
, BD2.DEPARTMENT_CODE
, ML1.MEANING
, ML2.MEANING
, 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
, BSO.OPERATION_CODE
, MSI.DESCRIPTION
, MSI.PRIMARY_UOM_CODE
, WO1.NEXT_OPERATION_SEQ_NUM
, WO1.PREVIOUS_OPERATION_SEQ_NUM
, WO1.MINIMUM_TRANSFER_QUANTITY
, WDJ.PROJECT_ID
, WDJ.TASK_ID
, WO1.DESCRIPTION
, WDJ.STATUS_TYPE
, WDJ.STATUS_TYPE
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.SERIALIZATION_START_OP

Columns