[Home] [Help]
148: l_children_no NUMBER;
149: l_route_mtl_req_tbl ahl_ltp_mtl_req_pvt.route_mtl_req_tbl_type;
150: l_unit_instance_id NUMBER;
151: l_installed_inst_id NUMBER;
152: l_serial_number csi_item_instances.serial_number%TYPE;
153: l_lot_number csi_item_instances.lot_number%TYPE;
154: l_inv_item_id NUMBER;
155: l_master_org_id NUMBER;
156: l_last_vld_org_id NUMBER;
149: l_route_mtl_req_tbl ahl_ltp_mtl_req_pvt.route_mtl_req_tbl_type;
150: l_unit_instance_id NUMBER;
151: l_installed_inst_id NUMBER;
152: l_serial_number csi_item_instances.serial_number%TYPE;
153: l_lot_number csi_item_instances.lot_number%TYPE;
154: l_inv_item_id NUMBER;
155: l_master_org_id NUMBER;
156: l_last_vld_org_id NUMBER;
157: l_disp_org_id NUMBER;
227: lot_number,
228: inventory_item_id,
229: last_vld_organization_id,
230: inv_master_organization_id
231: FROM csi_item_instances
232: WHERE instance_id = c_instance_id;
233:
234: BEGIN
235: --Initialize API return status to success
1360: SELECT 'x' FROM mtl_lot_numbers
1361: WHERE lot_number = p_lot_number
1362: AND inventory_item_id = p_inventory_item_id
1363: UNION
1364: SELECT 'x' FROM csi_item_instances csi
1365: WHERE lot_number = p_lot_number
1366: AND inventory_item_id = p_inventory_item_id;
1367:
1368: CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
1369: SELECT 'x' FROM mtl_serial_numbers
1370: WHERE serial_number = p_serial_number
1371: AND inventory_item_id = p_inventory_item_id
1372: UNION
1373: SELECT 'x' FROM csi_item_instances csi
1374: WHERE serial_number = p_serial_number
1375: AND inventory_item_id = p_inventory_item_id;
1376:
1377: CURSOR item_revisions_csr (p_revision IN VARCHAR2, p_item_id IN NUMBER, p_organization_id IN NUMBER) IS
1671: FND_MSG_PUB.ADD;
1672: ELSIF (l_disposition_rec.instance_id IS NULL AND p_x_disposition_rec.instance_id IS NOT NULL) THEN
1673: BEGIN
1674: SELECT 'X' INTO l_dummy_char
1675: FROM csi_item_instances
1676: WHERE instance_id = p_x_disposition_rec.instance_id
1677: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1678: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1679: EXCEPTION
2307:
2308: -- Cursor to check whether removed instance is in job or not.
2309: CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
2310: SELECT 'Y'
2311: FROM ahl_workorders awo, csi_item_instances csi
2312: WHERE awo.wip_entity_id = csi.wip_job_id
2313: AND awo.workorder_id = p_workorder_id
2314: AND csi.instance_id = p_instance_id;
2315:
2534: WHERE wo.workorder_id = p_workorder_id
2535: AND wo.visit_id = vi.visit_id;
2536:
2537: CURSOR get_instance_from_serial(p_item_id IN NUMBER, p_serial_num IN VARCHAR2) IS
2538: SELECT instance_id FROM CSI_ITEM_INSTANCES
2539: WHERE inventory_item_id = p_item_id AND serial_number = p_serial_num;
2540:
2541: CURSOR get_instance_from_lot(p_item_id IN NUMBER, p_lot_num IN VARCHAR2) IS
2542: SELECT instance_id FROM CSI_ITEM_INSTANCES
2538: SELECT instance_id FROM CSI_ITEM_INSTANCES
2539: WHERE inventory_item_id = p_item_id AND serial_number = p_serial_num;
2540:
2541: CURSOR get_instance_from_lot(p_item_id IN NUMBER, p_lot_num IN VARCHAR2) IS
2542: SELECT instance_id FROM CSI_ITEM_INSTANCES
2543: WHERE inventory_item_id = p_item_id AND lot_number = p_lot_num;
2544:
2545: CURSOR instance_csr(p_instance_id IN NUMBER) IS
2546: SELECT inventory_item_id,
2550: inv_master_organization_id,
2551: serial_number,
2552: lot_number,
2553: inventory_revision
2554: from csi_item_instances
2555: WHERE instance_id = p_instance_id;
2556:
2557:
2558: instance_rec instance_csr%ROWTYPE;
2849: -- For non-tracked items, serial or lot numbers
2850: -- need to be validated only against MTL tables.
2851: /******
2852: CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2853: SELECT 'x' FROM csi_item_instances csi
2854: WHERE lot_number = p_lot_number
2855: AND inventory_item_id = p_inventory_item_id;
2856:
2857: CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2854: WHERE lot_number = p_lot_number
2855: AND inventory_item_id = p_inventory_item_id;
2856:
2857: CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2858: SELECT 'x' FROM csi_item_instances csi
2859: WHERE serial_number = p_serial_number
2860: AND inventory_item_id = p_inventory_item_id;
2861: ******/
2862: CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2863: SELECT 'x' FROM mtl_lot_numbers
2864: WHERE lot_number = p_lot_number
2865: AND inventory_item_id = p_inventory_item_id
2866: UNION
2867: SELECT 'x' FROM csi_item_instances csi
2868: WHERE lot_number = p_lot_number
2869: AND inventory_item_id = p_inventory_item_id;
2870:
2871: CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2872: SELECT 'x' FROM mtl_serial_numbers
2873: WHERE serial_number = p_serial_number
2874: AND inventory_item_id = p_inventory_item_id
2875: UNION
2876: SELECT 'x' FROM csi_item_instances csi
2877: WHERE serial_number = p_serial_number
2878: AND inventory_item_id = p_inventory_item_id;
2879: /* End Fix for 4075758 on Dec 21. 2004 */
2880:
2878: AND inventory_item_id = p_inventory_item_id;
2879: /* End Fix for 4075758 on Dec 21. 2004 */
2880:
2881: CURSOR instance_quantity_csr(p_instance_id IN NUMBER) IS
2882: SELECT quantity from csi_item_instances WHERE instance_id = p_instance_id;
2883:
2884: CURSOR instance_uom_csr(p_instance_id IN NUMBER) IS
2885: SELECT unit_of_measure from csi_item_instances WHERE instance_id = p_instance_id;
2886:
2881: CURSOR instance_quantity_csr(p_instance_id IN NUMBER) IS
2882: SELECT quantity from csi_item_instances WHERE instance_id = p_instance_id;
2883:
2884: CURSOR instance_uom_csr(p_instance_id IN NUMBER) IS
2885: SELECT unit_of_measure from csi_item_instances WHERE instance_id = p_instance_id;
2886:
2887: CURSOR item_class_uom_csr(p_uom_code IN VARCHAR2, p_inventory_item_id NUMBER) IS
2888: /*
2889: SELECT 'X' FROM ahl_item_class_uom_v
2905: CURSOR val_Collection_id_csr(p_collection_id IN NUMBER) IS
2906: SELECT 'x' FROM qa_results WHERE collection_id = p_collection_id;
2907:
2908: CURSOR get_item_id_csr(p_instance_id IN NUMBER) IS
2909: SELECT inventory_item_id from csi_item_instances WHERE instance_id = p_instance_id;
2910:
2911: CURSOR val_uom_csr(p_uom IN VARCHAR2) IS
2912: SELECT 'x' FROM mtl_units_of_measure_vl
2913: WHERE uom_code = p_uom;
3504: p_x_prd_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type
3505: ) IS
3506:
3507: CURSOR instance_id_csr(p_instance_number IN VARCHAR2) IS
3508: SELECT instance_id from csi_item_instances
3509: WHERE instance_number = p_instance_number;
3510:
3511: CURSOR item_group_id_csr(p_item_group_name IN VARCHAR2) IS
3512: SELECT item_group_id from ahl_item_groups_b
3857:
3858: PROCEDURE validate_instance(p_instance_id IN NUMBER, p_workorder_id IN NUMBER, p_path_position_id IN NUMBER, p_part_change_id IN NUMBER) IS
3859:
3860: CURSOR instance_csr(p_instance_id IN NUMBER) IS
3861: SELECT 'x' from csi_item_instances
3862: where instance_id = p_instance_id;
3863:
3864: CURSOR instance_in_wip_csr(p_instance_id IN NUMBER, p_workorder_id IN NUMBER) IS
3865: SELECT 'x' from csi_item_instances csi, ahl_workorders wo
3861: SELECT 'x' from csi_item_instances
3862: where instance_id = p_instance_id;
3863:
3864: CURSOR instance_in_wip_csr(p_instance_id IN NUMBER, p_workorder_id IN NUMBER) IS
3865: SELECT 'x' from csi_item_instances csi, ahl_workorders wo
3866: WHERE instance_id = p_instance_id
3867: and wo.wip_entity_id = csi.wip_job_id
3868: and csi.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
3869: and trunc(sysdate) between trunc(nvl(csi.active_start_date, sysdate)) and trunc(nvl(csi.active_end_date, sysdate));
3886: -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
3887: -- Cursor to check whether the disposition item is serialized or not.
3888: CURSOR chk_non_serialized_csr(p_instance_id NUMBER) IS
3889: SELECT 'X'
3890: FROM mtl_system_items_b mtl, csi_item_instances csi
3891: WHERE csi.instance_id = p_instance_id
3892: AND csi.inventory_item_id = mtl.inventory_item_id
3893: AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
3894: AND mtl.serial_number_control_code = 1;
4041:
4042: -- Cursor to get the disposition instance details.
4043: CURSOR get_inst_dtls_csr(c_instance_id IN NUMBER) IS
4044: SELECT SERIAL_NUMBER, INVENTORY_ITEM_ID, QUANTITY
4045: FROM CSI_ITEM_INSTANCES
4046: WHERE INSTANCE_ID = c_instance_id;
4047:
4048: l_removed_instance_id NUMBER;
4049: l_installed_instance_id NUMBER;
5078:
5079: -- For getting the the updated object_version number from csi_item_isntances
5080: CURSOR ahl_obj_ver_csr IS
5081: select object_version_number
5082: from csi_item_instances
5083: where instance_id = p_instance_id;
5084:
5085: -- For getting the wip_location_id to populate csi_transaction record
5086: CURSOR ahl_wip_location_csr IS
5122: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5123: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Not changing the location of the instance to the NR WO because the work order is not released.');
5124: END IF;
5125: ELSE
5126: -- Get the object_version number from csi_item_instances
5127: OPEN ahl_obj_ver_csr;
5128: FETCH ahl_obj_ver_csr INTO l_instance_rec.object_version_number;
5129: IF (ahl_obj_ver_csr%NOTFOUND) THEN
5130: FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_REMOVED_INSTANCE_INVALID');