[Home] [Help]
1237: CSI.lot_number,
1238: CSI.quantity,
1239: CSI.unit_of_measure,
1240: MTL.concatenated_segments
1241: FROM CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL
1242: WHERE CSI.inv_master_organization_id = c_item_mstr_org_id
1243: AND CSI.inv_organization_id = c_item_inv_org_id
1244: AND CSI.inventory_item_id = c_inv_item_id
1245: AND CSI.instance_id = c_instance_id
1272: -- cursor to check the repair batch
1273: CURSOR chk_rpr_batch_csr (c_rpr_batch_name VARCHAR2,
1274: c_inv_item_id NUMBER) IS
1275: SELECT 'X'
1276: FROM AHL_VISIT_TASKS_B TSK, CSI_ITEM_INSTANCES CSI
1277: WHERE TSK.repair_batch_name = c_rpr_batch_name
1278: AND TSK.instance_id = CSI.instance_id
1279: AND CSI.inventory_item_id = c_inv_item_id
1280: AND TSK.status_code NOT IN ('CANCELLED', 'CLOSED', 'DELETED');
1339: -- NOTE: will be used for non-serialized non-lot-controlled item
1340: CURSOR get_locator_inst_id_csr (c_inv_locator_id NUMBER,
1341: c_inv_item_id NUMBER) IS
1342: SELECT instance_id
1343: FROM CSI_ITEM_INSTANCES
1344: WHERE inv_locator_id = c_inv_locator_id
1345: AND inventory_item_id = c_inv_item_id
1346: AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
1347: AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
1350: -- NOTE: will be used for non-serialized non-lot-controlled item
1351: CURSOR get_locator_inst_qty_csr (c_inv_locator_id NUMBER,
1352: c_inv_item_id NUMBER) IS
1353: SELECT quantity
1354: FROM CSI_ITEM_INSTANCES
1355: WHERE inv_locator_id = c_inv_locator_id
1356: AND inventory_item_id = c_inv_item_id
1357: AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
1358: AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
2314: MTL.concatenated_segments,
2315: CSI.inv_master_organization_id,
2316: CSI.inv_organization_id,
2317: CSI.inventory_item_id
2318: FROM CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL
2319: WHERE CSI.instance_id = p_instance_id_csr
2320: AND MTL.inventory_item_id = CSI.inventory_item_id
2321: AND MTL.organization_id = CSI.inv_master_organization_id
2322: AND TRUNC(NVL(CSI.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
2373:
2374:
2375: -- Get the new instance from the in-repair locator for a given repair batch
2376: CURSOR get_in_repair_instance_csr(p_inrepair_locator_id NUMBER) IS
2377: SELECT instance_id FROM csi_item_instances WHERE
2378: inv_locator_id = p_inrepair_locator_id;
2379: -- cursor to get the concatenated segments for visit's physical locator
2380: CURSOR get_locator_segments_csr (p_inv_locator_id_csr NUMBER) IS
2381: SELECT MTK.concatenated_segments,
2386:
2387: -- get the object version number for instance updation
2388: CURSOR get_csi_obj_ver_num(c_instance_id NUMBER) IS
2389: SELECT object_version_number
2390: FROM csi_item_instances
2391: WHERE instance_id = c_instance_id
2392: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2393: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2394:
2421: FROM AHL_VISIT_TASKS_VL TSK,
2422: AHL_VISITS_B VST
2423: WHERE TSK.repair_batch_name = p_rprbatch_name_csr
2424: AND VST.visit_id = TSK.visit_id) VISIT_TASK,
2425: MTL_ITEM_LOCATIONS_KFV INRPR_LOC, CSI_ITEM_INSTANCES csi
2426: WHERE csi.inv_locator_id = INRPR_LOC.inventory_location_id
2427: AND INRPR_LOC.physical_location_id = VISIT_TASK.comp_inrepair_loc_id
2428: AND INRPR_LOC.project_id = VISIT_TASK.project_id
2429: AND INRPR_LOC.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VISIT_TASK.project_id,VISIT_TASK.project_task_id,VISIT_TASK.visit_id)
2729: RAISE FND_API.G_EXC_ERROR;
2730: END IF;
2731:
2732: -- 6) Expire the dummy instance
2733: --UPDATE CSI_ITEM_INSTANCES set ACTIVE_END_DATE = TRUNC(SYSDATE) WHERE
2734: --instance_id = l_new_instance_id;
2735: -- Call CSI PUB API to expire the instance
2736: -- get the transaction type
2737: AHL_UTIL_UC_PKG.getcsi_transaction_id('CMRO_MAINTENANCE_WORK_ORDER',l_csi_transaction_type_id, l_return_value);
2866: c.inventory_revision,
2867: p.party_id,
2868: p.party_source_table,
2869: p.instance_party_id
2870: FROM csi_item_instances c, csi_i_parties p
2871: WHERE c.instance_id = c_csi_instance_id
2872: AND p.instance_id = c.instance_id
2873: AND p.relationship_type_code = 'OWNER'
2874: AND trunc(nvl(c.active_start_date, SYSDATE)) <= trunc(SYSDATE)