eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.WIP_LOTJOB_OPEN_ALLOCATIONS_V

Source

View Text - Preformatted


SELECT distinct we.wip_entity_name, we.organization_id, we.wip_entity_id, wdj.description job_description, msik.concatenated_segments primary_item, msik.description primary_item_description, we.primary_item_id, wdj.status_type status_type, ml.meaning status, min(wro.date_required) first_open_requirements_date, wdj.scheduled_start_date start_date, wdj.scheduled_completion_date completion_date, wdj.end_item_unit_number, wdj.project_id, wdj.task_id, wl.line_code, wsg.schedule_group_name from wip_entities we, mtl_system_items_kfv msik, wip_discrete_jobs wdj, wip_requirement_operations wro, mfg_lookups ml, wip_parameters wp, wip_lines wl, wip_operations wo, wip_schedule_groups wsg where wo.wip_entity_id = we.wip_entity_id and wo.operation_seq_num = wro.operation_seq_num and we.wip_entity_id = wdj.wip_entity_id and we.entity_type = 5 and wdj.schedule_group_id = wsg.schedule_group_id (+) and wdj.organization_id = wsg.organization_id (+) and we.primary_item_id = msik.inventory_item_id and wdj.status_type = ml.lookup_code and wdj.status_type in (3,4) and wdj.line_id = wl.line_id (+) and ml.lookup_type = 'WIP_JOB_STATUS' and msik.organization_id = we.organization_id and wro.wip_entity_id = we.wip_entity_id and wp.organization_id = we.organization_id /* Replaced wro.quantity_allocated: and (wo.quantity_in_queue + quantity_running + wo.quantity_waiting_to_move + wo.quantity_rejected + wo.quantity_scrapped + wo.quantity_completed) * wro.quantity_per_assembly > wro.quantity_issued + nvl(quantity_allocated, 0) With this: Modified for bug 5504661. */ and ((wro.supply_subinventory is null and ((wo.quantity_in_queue + quantity_running + wo.quantity_waiting_to_move + wo.quantity_rejected + wo.quantity_scrapped + wo.quantity_completed) * wro.quantity_per_assembly - (wro.quantity_issued + nvl( (select sum(nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0)) from mtl_txn_request_lines mtrl where mtrl.txn_source_id = wro.wip_entity_id and mtrl.txn_source_line_id = wro.operation_seq_num and mtrl.organization_id = wro.organization_id and inventory_item_id = wro.inventory_item_id and mtrl.line_status in 7 and mtrl.transaction_type_id = 35 group by mtrl.organization_id, mtrl.txn_source_id, mtrl.txn_source_line_id, mtrl.inventory_item_id), 0)))>= 0.00001) or (wro.supply_subinventory is not null and ((wo.quantity_in_queue + quantity_running + wo.quantity_waiting_to_move + wo.quantity_rejected + wo.quantity_scrapped + wo.quantity_completed) * wro.quantity_per_assembly - (greatest(nvl(wro.quantity_issued,0), nvl( (select sum(nvl(mtrl.quantity,0)) from mtl_txn_request_lines mtrl where mtrl.txn_source_id = wro.wip_entity_id and mtrl.txn_source_line_id = wro.operation_seq_num and mtrl.organization_id = wro.organization_id and inventory_item_id = wro.inventory_item_id and mtrl.line_status in (5,7) and mtrl.transaction_type_id = 51 group by mtrl.organization_id, mtrl.txn_source_id, mtrl.txn_source_line_id, mtrl.inventory_item_id), 0))))>= 0.00001)) /* End Replacement*/ and ( ( wp.allocate_backflush_components = 'Y' and wro.wip_supply_type in (1,2,3) ) or ( ( wp.allocate_backflush_components = 'N' or wp.allocate_backflush_components is null ) and wro.wip_supply_type = 1 ) ) group by we.wip_entity_name, we.organization_id, we.wip_entity_id, wdj.description , msik.concatenated_segments , msik.description , we.primary_item_id, wdj.status_type , ml.meaning , wdj.scheduled_start_date , wdj.scheduled_completion_date , wdj.end_item_unit_number, wdj.project_id, wdj.task_id, wl.line_code, wsg.schedule_group_name

View Text - HTML Formatted

SELECT DISTINCT WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, WE.WIP_ENTITY_ID
, WDJ.DESCRIPTION JOB_DESCRIPTION
, MSIK.CONCATENATED_SEGMENTS PRIMARY_ITEM
, MSIK.DESCRIPTION PRIMARY_ITEM_DESCRIPTION
, WE.PRIMARY_ITEM_ID
, WDJ.STATUS_TYPE STATUS_TYPE
, ML.MEANING STATUS
, MIN(WRO.DATE_REQUIRED) FIRST_OPEN_REQUIREMENTS_DATE
, WDJ.SCHEDULED_START_DATE START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE COMPLETION_DATE
, WDJ.END_ITEM_UNIT_NUMBER
, WDJ.PROJECT_ID
, WDJ.TASK_ID
, WL.LINE_CODE
, WSG.SCHEDULE_GROUP_NAME
FROM WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_KFV MSIK
, WIP_DISCRETE_JOBS WDJ
, WIP_REQUIREMENT_OPERATIONS WRO
, MFG_LOOKUPS ML
, WIP_PARAMETERS WP
, WIP_LINES WL
, WIP_OPERATIONS WO
, WIP_SCHEDULE_GROUPS WSG
WHERE WO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WRO.OPERATION_SEQ_NUM
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ENTITY_TYPE = 5
AND WDJ.SCHEDULE_GROUP_ID = WSG.SCHEDULE_GROUP_ID (+)
AND WDJ.ORGANIZATION_ID = WSG.ORGANIZATION_ID (+)
AND WE.PRIMARY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND WDJ.STATUS_TYPE = ML.LOOKUP_CODE
AND WDJ.STATUS_TYPE IN (3
, 4)
AND WDJ.LINE_ID = WL.LINE_ID (+)
AND ML.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND MSIK.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WP.ORGANIZATION_ID = WE.ORGANIZATION_ID /* REPLACED WRO.QUANTITY_ALLOCATED:
AND (WO.QUANTITY_IN_QUEUE + QUANTITY_RUNNING + WO.QUANTITY_WAITING_TO_MOVE + WO.QUANTITY_REJECTED + WO.QUANTITY_SCRAPPED + WO.QUANTITY_COMPLETED) * WRO.QUANTITY_PER_ASSEMBLY > WRO.QUANTITY_ISSUED + NVL(QUANTITY_ALLOCATED
, 0) WITH THIS: MODIFIED FOR BUG 5504661. */
AND ((WRO.SUPPLY_SUBINVENTORY IS NULL
AND ((WO.QUANTITY_IN_QUEUE + QUANTITY_RUNNING + WO.QUANTITY_WAITING_TO_MOVE + WO.QUANTITY_REJECTED + WO.QUANTITY_SCRAPPED + WO.QUANTITY_COMPLETED) * WRO.QUANTITY_PER_ASSEMBLY - (WRO.QUANTITY_ISSUED + NVL( (SELECT SUM(NVL(MTRL.QUANTITY
, 0)-NVL(MTRL.QUANTITY_DELIVERED
, 0))
FROM MTL_TXN_REQUEST_LINES MTRL
WHERE MTRL.TXN_SOURCE_ID = WRO.WIP_ENTITY_ID
AND MTRL.TXN_SOURCE_LINE_ID = WRO.OPERATION_SEQ_NUM
AND MTRL.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MTRL.LINE_STATUS IN 7
AND MTRL.TRANSACTION_TYPE_ID = 35 GROUP BY MTRL.ORGANIZATION_ID
, MTRL.TXN_SOURCE_ID
, MTRL.TXN_SOURCE_LINE_ID
, MTRL.INVENTORY_ITEM_ID)
, 0)))>= 0.00001) OR (WRO.SUPPLY_SUBINVENTORY IS NOT NULL
AND ((WO.QUANTITY_IN_QUEUE + QUANTITY_RUNNING + WO.QUANTITY_WAITING_TO_MOVE + WO.QUANTITY_REJECTED + WO.QUANTITY_SCRAPPED + WO.QUANTITY_COMPLETED) * WRO.QUANTITY_PER_ASSEMBLY - (GREATEST(NVL(WRO.QUANTITY_ISSUED
, 0)
, NVL( (SELECT SUM(NVL(MTRL.QUANTITY
, 0))
FROM MTL_TXN_REQUEST_LINES MTRL
WHERE MTRL.TXN_SOURCE_ID = WRO.WIP_ENTITY_ID
AND MTRL.TXN_SOURCE_LINE_ID = WRO.OPERATION_SEQ_NUM
AND MTRL.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MTRL.LINE_STATUS IN (5
, 7)
AND MTRL.TRANSACTION_TYPE_ID = 51 GROUP BY MTRL.ORGANIZATION_ID
, MTRL.TXN_SOURCE_ID
, MTRL.TXN_SOURCE_LINE_ID
, MTRL.INVENTORY_ITEM_ID)
, 0))))>= 0.00001)) /* END REPLACEMENT*/
AND ( ( WP.ALLOCATE_BACKFLUSH_COMPONENTS = 'Y'
AND WRO.WIP_SUPPLY_TYPE IN (1
, 2
, 3) ) OR ( ( WP.ALLOCATE_BACKFLUSH_COMPONENTS = 'N' OR WP.ALLOCATE_BACKFLUSH_COMPONENTS IS NULL )
AND WRO.WIP_SUPPLY_TYPE = 1 ) ) GROUP BY WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, WE.WIP_ENTITY_ID
, WDJ.DESCRIPTION
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, WE.PRIMARY_ITEM_ID
, WDJ.STATUS_TYPE
, ML.MEANING
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.END_ITEM_UNIT_NUMBER
, WDJ.PROJECT_ID
, WDJ.TASK_ID
, WL.LINE_CODE
, WSG.SCHEDULE_GROUP_NAME