[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;
229: lot_number,
230: inventory_item_id,
231: last_vld_organization_id,
232: inv_master_organization_id
233: FROM csi_item_instances
234: WHERE instance_id = c_instance_id;
235:
236: BEGIN
237: --Initialize API return status to success
989: -- Cursor to check whether removed instance is in job or not.
990: /**
991: CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
992: SELECT 'Y'
993: FROM ahl_workorders awo, csi_item_instances csi
994: WHERE awo.wip_entity_id = csi.wip_job_id
995: AND awo.workorder_id = p_workorder_id
996: AND csi.instance_id = p_instance_id;
997: **/
999: -- Changed by jaramana on 31-MAR-2011 for bug 11856373 to compare using
1000: -- the item and quantity of the disposition rather than the actual instance id; sorao for backporting
1001: CURSOR chk_disp_inst_job_csr (p_disposition_id NUMBER, p_workorder_id NUMBER) IS
1002: SELECT 'Y'
1003: FROM ahl_workorders awo, csi_item_instances csi, ahl_prd_dispositions_b disp
1004: WHERE awo.workorder_id = p_workorder_id
1005: AND csi.wip_job_id = awo.wip_entity_id
1006: AND csi.INVENTORY_ITEM_ID = disp.INVENTORY_ITEM_ID
1007: AND csi.QUANTITY = disp.QUANTITY;
1024:
1025: -- Added by jaramana on 17-JUN-2011 for bug 10195920
1026: CURSOR instance_lock_csr(c_instance_id IN NUMBER) IS
1027: SELECT 'X'
1028: FROM csi_item_instances
1029: WHERE instance_id = c_instance_id
1030: AND serial_number IS NOT NULL
1031: FOR UPDATE OF instance_id;
1032:
1520: SELECT 'x' FROM mtl_lot_numbers
1521: WHERE lot_number = p_lot_number
1522: AND inventory_item_id = p_inventory_item_id
1523: UNION
1524: SELECT 'x' FROM csi_item_instances csi
1525: WHERE lot_number = p_lot_number
1526: AND inventory_item_id = p_inventory_item_id;
1527:
1528: CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
1529: SELECT 'x' FROM mtl_serial_numbers
1530: WHERE serial_number = p_serial_number
1531: AND inventory_item_id = p_inventory_item_id
1532: UNION
1533: SELECT 'x' FROM csi_item_instances csi
1534: WHERE serial_number = p_serial_number
1535: AND inventory_item_id = p_inventory_item_id;
1536:
1537: CURSOR item_revisions_csr (p_revision IN VARCHAR2, p_item_id IN NUMBER, p_organization_id IN NUMBER) IS
1595: -- Cursor to check whether removed instance is in the given workorder or not.
1596: /**
1597: CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
1598: SELECT 'Y'
1599: FROM ahl_workorders awo, csi_item_instances csi
1600: WHERE awo.wip_entity_id = csi.wip_job_id
1601: AND awo.workorder_id = p_workorder_id
1602: AND csi.instance_id = p_instance_id;
1603: **/
1605: -- Changed by jaramana on 31-MAR-2011 for bug 11856373 to compare using
1606: -- the item and quantity of the disposition rather than the actual instance id
1607: CURSOR chk_disp_inst_job_csr (p_disposition_id NUMBER, p_workorder_id NUMBER) IS
1608: SELECT 'Y'
1609: FROM ahl_workorders awo, csi_item_instances csi, ahl_prd_dispositions_b disp
1610: WHERE awo.workorder_id = p_workorder_id
1611: AND csi.wip_job_id = awo.wip_entity_id
1612: AND csi.INVENTORY_ITEM_ID = disp.INVENTORY_ITEM_ID
1613: AND csi.QUANTITY = disp.QUANTITY;
1859: FND_MSG_PUB.ADD;
1860: ELSIF (l_disposition_rec.instance_id IS NULL AND p_x_disposition_rec.instance_id IS NOT NULL) THEN
1861: BEGIN
1862: SELECT 'X' INTO l_dummy_char
1863: FROM csi_item_instances
1864: WHERE instance_id = p_x_disposition_rec.instance_id
1865: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1866: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1867: EXCEPTION
2609:
2610: -- Cursor to check whether removed instance is in job or not.
2611: CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
2612: SELECT 'Y'
2613: FROM ahl_workorders awo, csi_item_instances csi
2614: WHERE awo.wip_entity_id = csi.wip_job_id
2615: AND awo.workorder_id = p_workorder_id
2616: AND csi.instance_id = p_instance_id;
2617:
2957: WHERE wo.workorder_id = p_workorder_id
2958: AND wo.visit_id = vi.visit_id;
2959:
2960: CURSOR get_instance_from_serial(p_item_id IN NUMBER, p_serial_num IN VARCHAR2) IS
2961: SELECT instance_id FROM CSI_ITEM_INSTANCES
2962: WHERE inventory_item_id = p_item_id AND serial_number = p_serial_num;
2963:
2964: CURSOR get_instance_from_lot(p_item_id IN NUMBER, p_lot_num IN VARCHAR2) IS
2965: SELECT instance_id FROM CSI_ITEM_INSTANCES
2961: SELECT instance_id FROM CSI_ITEM_INSTANCES
2962: WHERE inventory_item_id = p_item_id AND serial_number = p_serial_num;
2963:
2964: CURSOR get_instance_from_lot(p_item_id IN NUMBER, p_lot_num IN VARCHAR2) IS
2965: SELECT instance_id FROM CSI_ITEM_INSTANCES
2966: WHERE inventory_item_id = p_item_id AND lot_number = p_lot_num;
2967:
2968: CURSOR instance_csr(p_instance_id IN NUMBER) IS
2969: SELECT inventory_item_id,
2973: inv_master_organization_id,
2974: serial_number,
2975: lot_number,
2976: inventory_revision
2977: from csi_item_instances
2978: WHERE instance_id = p_instance_id;
2979:
2980:
2981: instance_rec instance_csr%ROWTYPE;
3272: -- For non-tracked items, serial or lot numbers
3273: -- need to be validated only against MTL tables.
3274: /******
3275: CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
3276: SELECT 'x' FROM csi_item_instances csi
3277: WHERE lot_number = p_lot_number
3278: AND inventory_item_id = p_inventory_item_id;
3279:
3280: CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
3277: WHERE lot_number = p_lot_number
3278: AND inventory_item_id = p_inventory_item_id;
3279:
3280: CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
3281: SELECT 'x' FROM csi_item_instances csi
3282: WHERE serial_number = p_serial_number
3283: AND inventory_item_id = p_inventory_item_id;
3284: ******/
3285: CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
3286: SELECT 'x' FROM mtl_lot_numbers
3287: WHERE lot_number = p_lot_number
3288: AND inventory_item_id = p_inventory_item_id
3289: UNION
3290: SELECT 'x' FROM csi_item_instances csi
3291: WHERE lot_number = p_lot_number
3292: AND inventory_item_id = p_inventory_item_id;
3293:
3294: CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
3295: SELECT 'x' FROM mtl_serial_numbers
3296: WHERE serial_number = p_serial_number
3297: AND inventory_item_id = p_inventory_item_id
3298: UNION
3299: SELECT 'x' FROM csi_item_instances csi
3300: WHERE serial_number = p_serial_number
3301: AND inventory_item_id = p_inventory_item_id;
3302: /* End Fix for 4075758 on Dec 21. 2004 */
3303:
3301: AND inventory_item_id = p_inventory_item_id;
3302: /* End Fix for 4075758 on Dec 21. 2004 */
3303:
3304: CURSOR instance_quantity_csr(p_instance_id IN NUMBER) IS
3305: SELECT quantity from csi_item_instances WHERE instance_id = p_instance_id;
3306:
3307: CURSOR instance_uom_csr(p_instance_id IN NUMBER) IS
3308: SELECT unit_of_measure from csi_item_instances WHERE instance_id = p_instance_id;
3309:
3304: CURSOR instance_quantity_csr(p_instance_id IN NUMBER) IS
3305: SELECT quantity from csi_item_instances WHERE instance_id = p_instance_id;
3306:
3307: CURSOR instance_uom_csr(p_instance_id IN NUMBER) IS
3308: SELECT unit_of_measure from csi_item_instances WHERE instance_id = p_instance_id;
3309:
3310: CURSOR item_class_uom_csr(p_uom_code IN VARCHAR2, p_inventory_item_id NUMBER) IS
3311: /*
3312: SELECT 'X' FROM ahl_item_class_uom_v
3328: CURSOR val_Collection_id_csr(p_collection_id IN NUMBER) IS
3329: SELECT 'x' FROM qa_results WHERE collection_id = p_collection_id;
3330:
3331: CURSOR get_item_id_csr(p_instance_id IN NUMBER) IS
3332: SELECT inventory_item_id from csi_item_instances WHERE instance_id = p_instance_id;
3333:
3334: CURSOR val_uom_csr(p_uom IN VARCHAR2) IS
3335: SELECT 'x' FROM mtl_units_of_measure_vl
3336: WHERE uom_code = p_uom;
3937: p_x_prd_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type
3938: ) IS
3939:
3940: CURSOR instance_id_csr(p_instance_number IN VARCHAR2) IS
3941: SELECT instance_id from csi_item_instances
3942: WHERE instance_number = p_instance_number;
3943:
3944: CURSOR item_group_id_csr(p_item_group_name IN VARCHAR2) IS
3945: SELECT item_group_id from ahl_item_groups_b
4317:
4318: 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
4319:
4320: CURSOR instance_csr(p_instance_id IN NUMBER) IS
4321: SELECT 'x' from csi_item_instances
4322: where instance_id = p_instance_id;
4323:
4324: CURSOR instance_in_wip_csr(p_instance_id IN NUMBER, p_workorder_id IN NUMBER) IS
4325: SELECT 'x' from csi_item_instances csi, ahl_workorders wo
4321: SELECT 'x' from csi_item_instances
4322: where instance_id = p_instance_id;
4323:
4324: CURSOR instance_in_wip_csr(p_instance_id IN NUMBER, p_workorder_id IN NUMBER) IS
4325: SELECT 'x' from csi_item_instances csi, ahl_workorders wo
4326: WHERE instance_id = p_instance_id
4327: and wo.wip_entity_id = csi.wip_job_id
4328: and csi.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
4329: and trunc(sysdate) between trunc(nvl(csi.active_start_date, sysdate)) and trunc(nvl(csi.active_end_date, sysdate));
4346: -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
4347: -- Cursor to check whether the disposition item is serialized or not.
4348: CURSOR chk_non_serialized_csr(p_instance_id NUMBER) IS
4349: SELECT 'X'
4350: FROM mtl_system_items_b mtl, csi_item_instances csi
4351: WHERE csi.instance_id = p_instance_id
4352: AND csi.inventory_item_id = mtl.inventory_item_id
4353: AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
4354: AND mtl.serial_number_control_code = 1;
4501:
4502: -- Cursor to get the disposition instance details.
4503: CURSOR get_inst_dtls_csr(c_instance_id IN NUMBER) IS
4504: SELECT SERIAL_NUMBER, INVENTORY_ITEM_ID, QUANTITY
4505: FROM CSI_ITEM_INSTANCES
4506: WHERE INSTANCE_ID = c_instance_id;
4507:
4508: l_removed_instance_id NUMBER;
4509: l_installed_instance_id NUMBER;
5542:
5543: -- For getting the the updated object_version number from csi_item_isntances
5544: CURSOR ahl_obj_ver_csr IS
5545: select object_version_number
5546: from csi_item_instances
5547: where instance_id = p_instance_id;
5548:
5549: -- For getting the wip_location_id to populate csi_transaction record
5550: CURSOR ahl_wip_location_csr IS
5586: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5587: 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.');
5588: END IF;
5589: ELSE
5590: -- Get the object_version number from csi_item_instances
5591: OPEN ahl_obj_ver_csr;
5592: FETCH ahl_obj_ver_csr INTO l_instance_rec.object_version_number;
5593: IF (ahl_obj_ver_csr%NOTFOUND) THEN
5594: FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_REMOVED_INSTANCE_INVALID');