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_REP_OPEN_ALLOCATIONS_V

Source

View Text - Preformatted


SELECT distinct we.wip_entity_name, we.organization_id, we.wip_entity_id, wrs.repetitive_schedule_id, we.entity_type wip_entity_type, wrs.description entity_description, msik.concatenated_segments primary_item, msitl.description primary_item_description, msik.primary_uom_code, wri.primary_item_id, wrs.status_type status_type, ml.meaning status, min(wro.date_required) first_open_requirements_date, wrs.first_unit_start_date start_date, wrs.last_unit_completion_date completion_date, wl.line_code, wrs.daily_production_rate, (wrs.daily_production_rate * wrs.processing_work_days) total_quantity from wip_entities we, mtl_system_items_kfv msik, wip_repetitive_schedules wrs, wip_repetitive_items wri, wip_requirement_operations wro, mfg_lookups ml, wip_parameters wp, wip_lines wl, wip_operations wo, mtl_system_items_tl msitl where we.wip_entity_id = wrs.wip_entity_id and wrs.repetitive_schedule_id = wro.repetitive_schedule_id and we.wip_entity_id = wri.wip_entity_id and we.primary_item_id = msik.inventory_item_id and wrs.status_type = ml.lookup_code and wrs.status_type in (3,4) and wrs.line_id = wl.line_id and wri.line_id = wl.line_id and ml.lookup_type = 'WIP_JOB_STATUS' and msik.organization_id = we.organization_id and msitl.organization_id = we.organization_id and msitl.inventory_item_id = we.primary_item_id and msitl.language = userenv('LANG') and wro.wip_entity_id = we.wip_entity_id and wp.organization_id = we.organization_id and wro.wip_entity_id = wo.wip_entity_id (+) and wro.repetitive_schedule_id = wo.repetitive_schedule_id (+) and wro.operation_seq_num = wo.operation_seq_num(+) and (wo.count_point_type in (1,2) or wo.count_point_type is null) /* Modified for bug 5504661. */ and ((wro.supply_subinventory is null and (wro.required_quantity - (nvl(wro.quantity_issued,0) + 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 reference_id = wro.repetitive_schedule_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 (wro.required_quantity - (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 reference_id = wro.repetitive_schedule_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)) 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, wrs.repetitive_schedule_id, we.entity_type , wrs.description , msik.concatenated_segments , msitl.description , msik.primary_uom_code, wri.primary_item_id, wrs.status_type , ml.meaning , wrs.first_unit_start_date , wrs.last_unit_completion_date , wl.line_code, wrs.daily_production_rate, (wrs.daily_production_rate * wrs.processing_work_days)

View Text - HTML Formatted

SELECT DISTINCT WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, WE.WIP_ENTITY_ID
, WRS.REPETITIVE_SCHEDULE_ID
, WE.ENTITY_TYPE WIP_ENTITY_TYPE
, WRS.DESCRIPTION ENTITY_DESCRIPTION
, MSIK.CONCATENATED_SEGMENTS PRIMARY_ITEM
, MSITL.DESCRIPTION PRIMARY_ITEM_DESCRIPTION
, MSIK.PRIMARY_UOM_CODE
, WRI.PRIMARY_ITEM_ID
, WRS.STATUS_TYPE STATUS_TYPE
, ML.MEANING STATUS
, MIN(WRO.DATE_REQUIRED) FIRST_OPEN_REQUIREMENTS_DATE
, WRS.FIRST_UNIT_START_DATE START_DATE
, WRS.LAST_UNIT_COMPLETION_DATE COMPLETION_DATE
, WL.LINE_CODE
, WRS.DAILY_PRODUCTION_RATE
, (WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS) TOTAL_QUANTITY
FROM WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_KFV MSIK
, WIP_REPETITIVE_SCHEDULES WRS
, WIP_REPETITIVE_ITEMS WRI
, WIP_REQUIREMENT_OPERATIONS WRO
, MFG_LOOKUPS ML
, WIP_PARAMETERS WP
, WIP_LINES WL
, WIP_OPERATIONS WO
, MTL_SYSTEM_ITEMS_TL MSITL
WHERE WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND WRS.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID
AND WE.WIP_ENTITY_ID = WRI.WIP_ENTITY_ID
AND WE.PRIMARY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND WRS.STATUS_TYPE = ML.LOOKUP_CODE
AND WRS.STATUS_TYPE IN (3
, 4)
AND WRS.LINE_ID = WL.LINE_ID
AND WRI.LINE_ID = WL.LINE_ID
AND ML.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND MSIK.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND MSITL.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND MSITL.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND MSITL.LANGUAGE = USERENV('LANG')
AND WRO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WP.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WO.WIP_ENTITY_ID (+)
AND WRO.REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID (+)
AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM(+)
AND (WO.COUNT_POINT_TYPE IN (1
, 2) OR WO.COUNT_POINT_TYPE IS NULL) /* MODIFIED FOR BUG 5504661. */
AND ((WRO.SUPPLY_SUBINVENTORY IS NULL
AND (WRO.REQUIRED_QUANTITY - (NVL(WRO.QUANTITY_ISSUED
, 0) + 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 REFERENCE_ID = WRO.REPETITIVE_SCHEDULE_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 (WRO.REQUIRED_QUANTITY - (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 REFERENCE_ID = WRO.REPETITIVE_SCHEDULE_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))
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
, WRS.REPETITIVE_SCHEDULE_ID
, WE.ENTITY_TYPE
, WRS.DESCRIPTION
, MSIK.CONCATENATED_SEGMENTS
, MSITL.DESCRIPTION
, MSIK.PRIMARY_UOM_CODE
, WRI.PRIMARY_ITEM_ID
, WRS.STATUS_TYPE
, ML.MEANING
, WRS.FIRST_UNIT_START_DATE
, WRS.LAST_UNIT_COMPLETION_DATE
, WL.LINE_CODE
, WRS.DAILY_PRODUCTION_RATE
, (WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS)