VIEW: APPS.WIP_PCB_EMP_IMM_DISPATCH_V

Source

View Text - Preformatted

SELECT wo.organization_id , bd.department_id , decode(count(wor.resource_seq_num), 1, sum(wor.resource_seq_num), null) resource_seq_num , wor.resource_id , wori.instance_id, papf.full_name, brem.person_id, wo.WIP_ENTITY_ID , wo.OPERATION_SEQ_NUM , we.WIP_ENTITY_NAME || ':' || wo.OPERATION_SEQ_NUM /* || ':' || wor.resource_seq_num */ "JOB_OP_NAME" , decode(wdj.status_type, 6, 'Red',NULL) NODE_TEXT_COLOR , decode(wdj.status_type, 6, 'wpjoboph.gif', NULL) NODE_ICON , wo.FIRST_UNIT_START_DATE FROM bom_standard_operations bso, bom_departments bd , wip_discrete_jobs wdj , wip_entities we , wip_operation_resources wor , wip_operations wo, wip_op_resource_instances wori, bom_resource_employees brem, per_all_people_f papf, 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 /* you need not join repetitive schedule as it's not required */ 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 wdj.primary_item_id is not null and ((fnd_profile.Value('WIP_DISPLAY_SCRAP_REJECT') = 'Y' and (wo.quantity_waiting_to_move+wo.quantity_in_queue+wo.quantity_running + wo.quantity_rejected + wo.quantity_scrapped) > 0 ) or(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 = brem.instance_id and papf.effective_start_date = brem.effective_start_date and papf.effective_end_date = brem.effective_end_date and papf.person_id = brem.person_id and br.resource_id = wor.resource_id and br.resource_type = 2 GROUP BY wo.WIP_ENTITY_ID, we.WIP_ENTITY_NAME, wo.organization_id, wo.OPERATION_SEQ_NUM, bd.department_id, wor.resource_id, wdj.status_type, wo.FIRST_UNIT_START_DATE, wori.instance_id, papf.full_name, brem.person_id with read only

View Text - HTML Formatted

SELECT WO.ORGANIZATION_ID
, BD.DEPARTMENT_ID
, DECODE(COUNT(WOR.RESOURCE_SEQ_NUM)
, 1
, SUM(WOR.RESOURCE_SEQ_NUM)
, NULL) RESOURCE_SEQ_NUM
, WOR.RESOURCE_ID
, WORI.INSTANCE_ID
, PAPF.FULL_NAME
, BREM.PERSON_ID
, WO.WIP_ENTITY_ID
, WO.OPERATION_SEQ_NUM
, WE.WIP_ENTITY_NAME || ':' || WO.OPERATION_SEQ_NUM /* || ':' || WOR.RESOURCE_SEQ_NUM */ "JOB_OP_NAME"
, DECODE(WDJ.STATUS_TYPE
, 6
, 'RED'
, NULL) NODE_TEXT_COLOR
, DECODE(WDJ.STATUS_TYPE
, 6
, 'WPJOBOPH.GIF'
, NULL) NODE_ICON
, WO.FIRST_UNIT_START_DATE
FROM BOM_STANDARD_OPERATIONS BSO
, BOM_DEPARTMENTS BD
, WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, WIP_OPERATION_RESOURCES WOR
, WIP_OPERATIONS WO
, WIP_OP_RESOURCE_INSTANCES WORI
, BOM_RESOURCE_EMPLOYEES BREM
, PER_ALL_PEOPLE_F PAPF
, 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 /* YOU NEED NOT JOIN REPETITIVE SCHEDULE AS IT'S NOT REQUIRED */
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 WDJ.PRIMARY_ITEM_ID IS NOT NULL
AND ((FND_PROFILE.VALUE('WIP_DISPLAY_SCRAP_REJECT') = 'Y'
AND (WO.QUANTITY_WAITING_TO_MOVE+WO.QUANTITY_IN_QUEUE+WO.QUANTITY_RUNNING + WO.QUANTITY_REJECTED + WO.QUANTITY_SCRAPPED) > 0 ) OR(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 = BREM.INSTANCE_ID
AND PAPF.EFFECTIVE_START_DATE = BREM.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE = BREM.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = BREM.PERSON_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND BR.RESOURCE_TYPE = 2 GROUP BY WO.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WO.ORGANIZATION_ID
, WO.OPERATION_SEQ_NUM
, BD.DEPARTMENT_ID
, WOR.RESOURCE_ID
, WDJ.STATUS_TYPE
, WO.FIRST_UNIT_START_DATE
, WORI.INSTANCE_ID
, PAPF.FULL_NAME
, BREM.PERSON_ID WITH READ ONLY