VIEW: APPS.WIP_INST_EQP_IMM_DISPATCH_V

Source

View Text - Preformatted

SELECT we.WIP_ENTITY_NAME || ':' || wo.OPERATION_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, decode(count(wor.resource_seq_num), 1, sum(wor.resource_seq_num), null) resource_seq_num, wor.resource_id, wor.resource_code, wori.instance_id, msik.concatenated_segments, wori.serial_number, ml1.meaning "COUNTPOINT_TYPE", ml2.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, PJM_PROJECT.ALL_PROJ_IDTONAME(wdj.project_id), PJM_PROJECT.ALL_TASK_IDTONAME(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 ml1, mfg_lookups ml2, bom_standard_operations bso, bom_departments bd, mtl_system_items_kfv msi, wip_discrete_jobs wdj, wip_entities we, wip_operation_resources_v wor, wip_operations wo, wip_op_resource_instances wori, bom_resource_equipments breq, mtl_system_items_kfv msik, bom_resources br WHERE wor.organization_id = wo.organization_id and wor.wip_entity_id =wo.wip_entity_id and wor.operation_seq_num = wo.operation_seq_num and nvl(wor.department_id, 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 ml1.lookup_code = wo.count_point_type and ml1.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = wo.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 (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 and wor.wip_entity_id = wori.wip_entity_id and wor.operation_seq_num = wori.operation_seq_num and wor.resource_seq_num = wori.resource_seq_num and wori.instance_id = breq.instance_id and msik.inventory_item_id = breq.inventory_item_id and msik.organization_id = breq.organization_id and br.resource_id = wor.resource_id and br.resource_type = 1 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, wor.resource_id, wor.resource_code, ml1.meaning, ml2.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, wori.instance_id, msik.concatenated_segments, wori.serial_number

View Text - HTML Formatted

SELECT WE.WIP_ENTITY_NAME || ':' || WO.OPERATION_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
, DECODE(COUNT(WOR.RESOURCE_SEQ_NUM)
, 1
, SUM(WOR.RESOURCE_SEQ_NUM)
, NULL) RESOURCE_SEQ_NUM
, WOR.RESOURCE_ID
, WOR.RESOURCE_CODE
, WORI.INSTANCE_ID
, MSIK.CONCATENATED_SEGMENTS
, WORI.SERIAL_NUMBER
, ML1.MEANING "COUNTPOINT_TYPE"
, ML2.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
, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID)
, PJM_PROJECT.ALL_TASK_IDTONAME(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 ML1
, MFG_LOOKUPS ML2
, BOM_STANDARD_OPERATIONS BSO
, BOM_DEPARTMENTS BD
, MTL_SYSTEM_ITEMS_KFV MSI
, WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, WIP_OPERATION_RESOURCES_V WOR
, WIP_OPERATIONS WO
, WIP_OP_RESOURCE_INSTANCES WORI
, BOM_RESOURCE_EQUIPMENTS BREQ
, MTL_SYSTEM_ITEMS_KFV MSIK
, BOM_RESOURCES BR
WHERE WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WOR.WIP_ENTITY_ID =WO.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND NVL(WOR.DEPARTMENT_ID
, 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 ML1.LOOKUP_CODE = WO.COUNT_POINT_TYPE
AND ML1.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML2.LOOKUP_CODE = WO.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 (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
AND WOR.WIP_ENTITY_ID = WORI.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WORI.OPERATION_SEQ_NUM
AND WOR.RESOURCE_SEQ_NUM = WORI.RESOURCE_SEQ_NUM
AND WORI.INSTANCE_ID = BREQ.INSTANCE_ID
AND MSIK.INVENTORY_ITEM_ID = BREQ.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = BREQ.ORGANIZATION_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND BR.RESOURCE_TYPE = 1 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
, WOR.RESOURCE_ID
, WOR.RESOURCE_CODE
, ML1.MEANING
, ML2.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
, WORI.INSTANCE_ID
, MSIK.CONCATENATED_SEGMENTS
, WORI.SERIAL_NUMBER