VIEW: APPS.WIP_MATERIAL_SHORTAGES_V

Source

View Text - Preformatted

SELECT we.wip_entity_name, wro.organization_id, wro.wip_entity_id, we.entity_type wip_entity_type, wro.repetitive_schedule_id, wrs.line_id repetitive_line_id, wro.operation_seq_num, wro.department_id, wro.inventory_item_id, wro.quantity_backordered, msi.primary_uom_code uom_code, decode(wro.wip_supply_type, 1, wro.supply_subinventory, nvl(wro.supply_subinventory, wp.default_pull_supply_subinv)) subinventory_code, decode(wro.wip_supply_type, 1, wro.supply_locator_id, decode(wro.supply_subinventory, null, wp.default_pull_supply_locator_id, wro.supply_locator_id)) locator_id, decode(wro.wip_supply_type, 1, decode(wro.supply_subinventory, null, 'Y', 'N'), 'N') wip_issue_flag, wdj2.project_id, wdj2.task_id, wro.date_required, wro.wip_supply_type from wip_requirement_operations wro, wip_entities we, mtl_system_items msi, wip_discrete_jobs wdj2, wip_repetitive_schedules wrs, wip_parameters wp where we.wip_entity_id = wro.wip_entity_id and we.wip_entity_id = wdj2.wip_entity_id(+) and wro.repetitive_schedule_id = wrs.repetitive_schedule_id (+) and wro.inventory_item_id = msi.inventory_item_id and wro.organization_id = msi.organization_id and wro.organization_id = wp.organization_id and wro.quantity_backordered > 0 and ( ( we.entity_type in (1,5,6) AND exists(select 'transactable job' from wip_discrete_jobs wdj where wro.wip_entity_id = wdj.wip_entity_id and wdj.status_type in (3,4) ) ) OR ( we.entity_type = 2 AND exists(select 'transactable schedule' from wip_repetitive_schedules wrs2 where wro.repetitive_schedule_id = wrs2.repetitive_schedule_id and wrs2.status_type in (3,4) ) ) )

View Text - HTML Formatted

SELECT WE.WIP_ENTITY_NAME
, WRO.ORGANIZATION_ID
, WRO.WIP_ENTITY_ID
, WE.ENTITY_TYPE WIP_ENTITY_TYPE
, WRO.REPETITIVE_SCHEDULE_ID
, WRS.LINE_ID REPETITIVE_LINE_ID
, WRO.OPERATION_SEQ_NUM
, WRO.DEPARTMENT_ID
, WRO.INVENTORY_ITEM_ID
, WRO.QUANTITY_BACKORDERED
, MSI.PRIMARY_UOM_CODE UOM_CODE
, DECODE(WRO.WIP_SUPPLY_TYPE
, 1
, WRO.SUPPLY_SUBINVENTORY
, NVL(WRO.SUPPLY_SUBINVENTORY
, WP.DEFAULT_PULL_SUPPLY_SUBINV)) SUBINVENTORY_CODE
, DECODE(WRO.WIP_SUPPLY_TYPE
, 1
, WRO.SUPPLY_LOCATOR_ID
, DECODE(WRO.SUPPLY_SUBINVENTORY
, NULL
, WP.DEFAULT_PULL_SUPPLY_LOCATOR_ID
, WRO.SUPPLY_LOCATOR_ID)) LOCATOR_ID
, DECODE(WRO.WIP_SUPPLY_TYPE
, 1
, DECODE(WRO.SUPPLY_SUBINVENTORY
, NULL
, 'Y'
, 'N')
, 'N') WIP_ISSUE_FLAG
, WDJ2.PROJECT_ID
, WDJ2.TASK_ID
, WRO.DATE_REQUIRED
, WRO.WIP_SUPPLY_TYPE
FROM WIP_REQUIREMENT_OPERATIONS WRO
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS MSI
, WIP_DISCRETE_JOBS WDJ2
, WIP_REPETITIVE_SCHEDULES WRS
, WIP_PARAMETERS WP
WHERE WE.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID = WDJ2.WIP_ENTITY_ID(+)
AND WRO.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID (+)
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND WRO.ORGANIZATION_ID = WP.ORGANIZATION_ID
AND WRO.QUANTITY_BACKORDERED > 0
AND ( ( WE.ENTITY_TYPE IN (1
, 5
, 6)
AND EXISTS(SELECT 'TRANSACTABLE JOB'
FROM WIP_DISCRETE_JOBS WDJ
WHERE WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE IN (3
, 4) ) ) OR ( WE.ENTITY_TYPE = 2
AND EXISTS(SELECT 'TRANSACTABLE SCHEDULE'
FROM WIP_REPETITIVE_SCHEDULES WRS2
WHERE WRO.REPETITIVE_SCHEDULE_ID = WRS2.REPETITIVE_SCHEDULE_ID
AND WRS2.STATUS_TYPE IN (3
, 4) ) ) )