[Home] [Help]
97: SELECT csi.instance_id,
98: msn.serial_number,
99: csi.inv_subinventory_name subinventory_code
100: FROM csi_item_instances csi,
101: mtl_serial_numbers msn
102: WHERE trunc(sysdate) >= trunc(nvl(CSI.active_start_date,sysdate))
103: AND trunc(sysdate) < trunc(nvl(CSI.active_end_date,sysdate+1))
104: AND msn.current_status = 3 -- inventory
105: AND msn.reservation_id is null
1258: **/
1259: CURSOR get_instance_dtls_csr (c_inventory_itme_id IN NUMBER, c_serial_number IN VARCHAR2, c_organization_id IN NUMBER)
1260: IS
1261: SELECT csi.instance_id, msn.serial_number,csi.inv_subinventory_name subinventory_code
1262: FROM csi_item_instances csi,mtl_serial_numbers msn
1263: WHERE csi.inventory_item_id = c_inventory_itme_id
1264: AND csi.serial_number = c_serial_number
1265: AND csi.last_vld_organization_id = c_organization_id
1266: AND csi.inventory_item_id = msn.inventory_item_id;
2000: -- Get all the Serial Numbers reserved for this material req
2001: BEGIN
2002: SELECT serial_number
2003: BULK COLLECT INTO serial_num_tbl
2004: FROM mtl_serial_numbers
2005: WHERE reservation_id = l_reservation_id
2006: AND INVENTORY_ITEM_ID = l_get_upd_rsv_rec.inventory_item_id
2007: AND CURRENT_ORGANIZATION_ID = l_get_upd_rsv_rec.organization_id;
2008: EXCEPTION
2684: l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2685:
2686: CURSOR get_reservation_csr IS
2687: SELECT mrsv.reservation_id, mrsv.primary_reservation_quantity
2688: FROM mtl_reservations mrsv, ahl_schedule_materials asmt, mtl_serial_numbers msn
2689: WHERE mrsv.demand_source_line_detail = p_scheduled_material_id
2690: AND mrsv.external_source_code = 'AHL'
2691: AND msn.serial_number = p_serial_number
2692: AND mrsv.organization_id = msn.current_organization_id