VIEW: APPS.WIP_RES_UPS_DISPATCH_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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