[Home] [Help]
737: l_dmd_loc_id,
738: l_uom
739: FROM mtl_system_items_b ITEMS,
740: ahl_schedule_materials ASM,
741: ahl_visits_b VST,
742: mtl_item_locations LOC
743: WHERE ITEMS.inventory_item_id = ASM.inventory_item_id
744: AND ITEMS.organization_id = ASM.organization_id
745: AND ASM.visit_id = VST.visit_id
2199: -- Get the demand sub inventory code and locator id value
2200: BEGIN
2201: SELECT MILK.SUBINVENTORY_CODE
2202: INTO L_DEMAND_SUBINV_CODE--, L_DEMAND_LOCATOR_ID
2203: FROM AHL_VISITS_B AVB, MTL_ITEM_LOCATIONS_KFV MILK, AHL_SCHEDULE_MATERIALS ASM
2204: WHERE AVB.INV_LOCATOR_ID = MILK.INVENTORY_LOCATION_ID(+)
2205: AND ASM.VISIT_ID = AVB.VISIT_ID
2206: and ASM.SCHEDULED_MATERIAL_ID = P_SCH_MTL_ID;
2207: EXCEPTION
5545: AHL_MR_INTERVALS INTV,
5546: CS_COUNTERS CNTR,
5547: MTL_UNITS_OF_MEASURE_VL UOM,
5548: AHL_BUILD_GOALS BG,
5549: AHL_VISITS_B AVB,
5550: AHL_MR_HEADERS_B MR,
5551: AHL_MR_EFFECTIVITIES EFF,
5552: AHL_MC_PATH_POSITION_NODES NODE,
5553: AHL_MC_HEADERS_B MC,
5710: l_mc_header_id,
5711: l_pos_key,
5712: l_uc_header_id
5713: FROM ahl_schedule_materials ASM,
5714: ahl_visits_b VST,
5715: ahl_visit_tasks_b TSK
5716: WHERE ASM.visit_id = VST.visit_id
5717: AND ASM.visit_task_id = TSK.visit_task_id
5718: AND ASM.scheduled_material_id = p_demand_Record.Scheduled_Material_ID;
5919: || ' (SELECT CSIII.serial_number,'
5920: || ' WO.WIP_ENTITY_ID'
5921: || ' FROM AHL_WORKORDERS WO,'
5922: || ' AHL_VISIT_TASKS_B ATSK,'
5923: || ' AHL_VISITS_B AVST,'
5924: || ' CSI_ITEM_INSTANCES CSIII'
5925: || ' WHERE WO.visit_task_id = ATSK.visit_task_id'
5926: || ' AND WO.STATUS_CODE NOT IN (7, 22, 17, 12, 18, 21, 4, 5)'
5927: || ' AND ATSK.return_to_supply_flag = ''Y'''
5980: END IF;
5981:
5982: --Base criteria for Work Order Supply
5983: l_base_query := l_base_query || ' AND EXISTS (SELECT 1'
5984: || ' FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST'
5985: || ' WHERE WO.visit_task_id = TSK.visit_task_id'
5986: || ' AND WO.STATUS_CODE NOT IN (7, 22, 17, 12, 18, 21, 4, 5)'
5987: || ' AND TSK.return_to_supply_flag = ''Y'' '
5988: || ' AND TSK.visit_id = VST.visit_id'
6030: --Beginning of base criteria for Cannibalization Supply
6031: l_base_query := l_base_query || ' AND INST.instance_id IN'
6032: || ' (WITH base_inst AS'
6033: || ' (SELECT item_instance_id'
6034: || ' FROM ahl_visits_b'
6035: || ' WHERE item_instance_id IS NOT NULL'
6036: || ' AND status_code IN (''RELEASED'',''PARTIALLY RELEASED'')';
6037:
6038: --Visit Number filter