314: );
315: END IF;
316:
317: IF p_planning_level = 1 THEN -- PAR level Count
318: /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
319: 'MTL_SYSTEM_ITEMS_VL'.*/
320: OPEN x_replenish_count_lines
321: FOR
322: SELECT msiv.inventory_item_id item_id
315: END IF;
316:
317: IF p_planning_level = 1 THEN -- PAR level Count
318: /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
319: 'MTL_SYSTEM_ITEMS_VL'.*/
320: OPEN x_replenish_count_lines
321: FOR
322: SELECT msiv.inventory_item_id item_id
323: , msiv.concatenated_segments item
327: , ml.meaning count_type
328: , mrl.replenishment_line_id replenishment_line_id
329: , msiv.description item_description
330: , msiv.primary_uom_code primary_uom_code
331: FROM mtl_item_locations mil, mtl_system_items_vl msiv, mtl_secondary_locators msl, mtl_replenish_lines mrl, mfg_lookups ml
332: WHERE msl.inventory_item_id = msiv.inventory_item_id
333: AND msl.organization_id = msiv.organization_id
334: AND msl.secondary_locator = mil.inventory_location_id
335: AND msl.organization_id = mil.organization_id
352: AND mrl.count_quantity IS NULL
353: AND mrl.error_flag IS NULL
354: ORDER BY DECODE(p_use_loc_pick_seq, 'YES', mil.picking_order, replenishment_line_id), item;
355: ELSE -- Subinventory Level Count
356: /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
357: 'MTL_SYSTEM_ITEMS_VL'.*/
358: OPEN x_replenish_count_lines
359: FOR
360: SELECT mis.inventory_item_id item_id
353: AND mrl.error_flag IS NULL
354: ORDER BY DECODE(p_use_loc_pick_seq, 'YES', mil.picking_order, replenishment_line_id), item;
355: ELSE -- Subinventory Level Count
356: /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
357: 'MTL_SYSTEM_ITEMS_VL'.*/
358: OPEN x_replenish_count_lines
359: FOR
360: SELECT mis.inventory_item_id item_id
361: , msiv.concatenated_segments item
365: , ml.meaning count_type
366: , mrl.replenishment_line_id replenishment_line_id
367: , msiv.description item_description
368: , msiv.primary_uom_code primary_uom_code
369: FROM mtl_system_items_vl msiv, mtl_item_sub_inventories mis, mtl_replenish_lines mrl, mfg_lookups ml
370: WHERE mis.inventory_item_id = msiv.inventory_item_id
371: AND mis.organization_id = msiv.organization_id
372: AND mis.organization_id = p_organization_id
373: AND mis.secondary_inventory = p_subinventory_code
554: locator_id IS NULL
555: OR count_type_code = 3
556: OR NOT EXISTS(
557: SELECT maximum_quantity
558: FROM mtl_secondary_locators msl, mtl_item_locations mil, mtl_system_items msi
559: WHERE msl.inventory_item_id = mrl.inventory_item_id
560: AND msl.secondary_locator = mrl.locator_id
561: AND msl.organization_id = mrl.organization_id
562: AND msi.inventory_item_id = msl.inventory_item_id
568: AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE))
569: OR(
570: EXISTS(
571: SELECT maximum_quantity
572: FROM mtl_secondary_locators msl, mtl_item_locations mil, mtl_system_items msi
573: WHERE msl.inventory_item_id = mrl.inventory_item_id
574: AND msl.secondary_locator = mrl.locator_id
575: AND msl.organization_id = mrl.organization_id
576: AND msi.inventory_item_id = msl.inventory_item_id
592: OR count_type_code = 4
593: OR(
594: NOT EXISTS(
595: SELECT mis.inventory_planning_code
596: FROM mtl_item_sub_inventories mis, mtl_system_items msi
597: WHERE mis.inventory_item_id = mrl.inventory_item_id
598: AND mis.secondary_inventory = p_subinventory_code
599: AND mis.organization_id = mrl.organization_id
600: AND msi.inventory_item_id = mis.inventory_item_id
605: )
606: OR(
607: EXISTS(
608: SELECT mis.inventory_planning_code
609: FROM mtl_item_sub_inventories mis, mtl_system_items msi
610: WHERE mis.inventory_item_id = mrl.inventory_item_id
611: AND mis.secondary_inventory = p_subinventory_code
612: AND mis.organization_id = mrl.organization_id
613: AND msi.inventory_item_id = mis.inventory_item_id