[Home] [Help]
227: ,cii.install_location_id install_location_id
228: ,cii.install_location_type_code install_location_type_code
229: ,cip.party_id party_id
230: ,cip.party_source_table party_source_table
231: FROM csi_item_instances cii
232: ,csi_i_parties cip
233: WHERE cii.instance_id = p_instance_id
234: AND cip.instance_id = cii.instance_id
235: AND cip.relationship_type_code = 'OWNER';
484: ,nvl(ctld.install_location_type_code, FND_API.G_MISS_CHAR) install_location_type_code
485: ,cii.object_version_number
486: ,cii.instance_usage_code
487: FROM csi_t_txn_line_details ctld,
488: csi_item_instances cii
489: WHERE ctld.transaction_line_id = p_txn_line_id
490: AND ctld.INSTANCE_ID is not null
491: AND ctld.preserve_detail_flag = 'Y'
492: AND cii.instance_id = ctld.instance_id;
2748: -- Cursors
2749: CURSOR distinct_system_cur (txn_line_id IN NUMBER) IS
2750: SELECT distinct cii.system_id system_id
2751: FROM csi_t_txn_line_details ctld,
2752: csi_item_instances cii
2753: WHERE ctld.transaction_line_id = txn_line_id
2754: AND ctld.INSTANCE_ID is not null
2755: AND ctld.preserve_detail_flag = 'Y'
2756: AND cii.instance_id = ctld.instance_id;
2771: IF l_mu_systems_csr.system_id IS NOT NULL THEN
2772: -- To get the count of active item instances in the system
2773: SELECT COUNT(1)
2774: INTO l_active_instance_count
2775: FROM csi_item_instances cisv,
2776: CSI_SYSTEMS_B csb
2777: WHERE cisv.SYSTEM_ID = l_mu_systems_csr.system_id
2778: AND cisv.system_id = csb.system_id
2779: AND NVL(cisv.ACTIVE_END_DATE,sysdate+1) > sysdate
2784: -- To get total instances included in the mass update batch belonging to the system
2785: SELECT COUNT(1)
2786: INTO l_mu_instance_count
2787: FROM csi_t_txn_line_details ctld,
2788: csi_item_instances cii
2789: WHERE ctld.transaction_line_id = p_txn_line_id
2790: AND cii.system_id = l_mu_systems_csr.system_id
2791: AND ctld.INSTANCE_ID IS NOT NULL
2792: AND ctld.preserve_detail_flag = 'Y'
3673: l_par_instance_id := null;
3674: IF(l_instance.PARENT_INSTANCE IS NOT NULL) THEN
3675: BEGIN
3676: SELECT instance_id INTO l_par_instance_id
3677: FROM csi_item_instances
3678: WHERE instance_number = l_instance.PARENT_INSTANCE;
3679: EXCEPTION
3680: when no_data_found then
3681: debug_out('In the uploaded create record #' || xls_idx ||
4948: --populating few paramters which are retrieved from the database.
4949: BEGIN
4950: Select OBJECT_VERSION_NUMBER, LAST_VLD_ORGANIZATION_ID, LOCATION_TYPE_CODE
4951: into l_object_version_number, l_last_vld_organization_id, l_location_type_code
4952: from csi_item_instances
4953: where instance_id = l_instance.INSTANCE_ID;
4954: EXCEPTION
4955: WHEN others THEN
4956: debug_out('In the uploaded update record #' || xls_idx ||
5015: l_par_instance_id := null;
5016: IF(l_instance.PARENT_INSTANCE IS NOT NULL) THEN
5017: BEGIN
5018: SELECT instance_id INTO l_par_instance_id
5019: FROM csi_item_instances
5020: WHERE instance_number = l_instance.PARENT_INSTANCE;
5021: EXCEPTION
5022: when no_data_found then
5023: debug_out('In the uploaded update record #' || xls_idx ||
5030: l_old_par_instance_id := null;
5031: IF(l_instance.OLD_PARENT_INSTANCE IS NOT NULL) THEN
5032: BEGIN
5033: SELECT instance_id INTO l_old_par_instance_id
5034: FROM csi_item_instances
5035: WHERE instance_number = l_instance.OLD_PARENT_INSTANCE;
5036: EXCEPTION
5037: when no_data_found then
5038: debug_out('In the uploaded update record #' || xls_idx ||
5050:
5051: --Validating Operational Status Code
5052: BEGIN
5053: SELECT operational_status_code INTO l_operational_status_code
5054: FROM csi_item_instances
5055: WHERE instance_id = l_instance.INSTANCE_ID;
5056: EXCEPTION
5057: when no_data_found then
5058: debug_out('In the uploaded update record #' || xls_idx ||
5736: IF u_instance_tbl.EXISTS(inst_tab_row) THEN
5737: BEGIN
5738: SELECT instance_number
5739: INTO l_instance_number
5740: FROM csi_item_instances
5741: WHERE instance_id = u_instance_tbl(inst_tab_row).Instance_id; EXCEPTION
5742: WHEN No_Data_Found THEN
5743: debug('Instance not found for ID: ' ||
5744: u_instance_tbl(inst_tab_row).Instance_id);
5796: IF u_grp_upd_error_tbl.EXISTS(inst_tab_row) THEN
5797: BEGIN
5798: SELECT instance_number
5799: INTO l_instance_number
5800: FROM csi_item_instances
5801: WHERE instance_id = u_grp_upd_error_tbl(inst_tab_row).Instance_id;
5802:
5803: debug_out('Update of Instance ' || l_instance_number || ' : Failed with the error ' ||
5804: u_grp_upd_error_tbl(inst_tab_row).Entity_Name || '- ' || u_grp_upd_error_tbl(inst_tab_row).Error_Message );
6017: THEN
6018: BEGIN
6019: SELECT instance_number
6020: INTO l_instance_number
6021: FROM csi_item_instances
6022: WHERE instance_id = c_pricing_attribs_tbl(tab_row).instance_id; EXCEPTION
6023: WHEN No_Data_Found THEN
6024: debug('Instance not found, while debug for create_pricing_attribs' ||
6025: ' for ID: ' || c_pricing_attribs_tbl(tab_row).instance_id);
6065: THEN
6066: BEGIN
6067: SELECT instance_number
6068: INTO l_instance_number
6069: FROM csi_item_instances
6070: WHERE instance_id = u_pricing_attribs_tbl(tab_row).instance_id; EXCEPTION
6071: WHEN No_Data_Found THEN
6072: debug('Instance not found, while debug for update_pricing_attribs' ||
6073: ' for ID: ' || u_pricing_attribs_tbl(tab_row).instance_id);
6234: --INSTANCE_ID
6235: BEGIN
6236: UPDATE csi_webadi_staging cws SET INSTANCE_ID =
6237: (
6238: SELECT INSTANCE_ID FROM csi_item_instances cii
6239: WHERE cii.instance_number = cws.PRODUCT_NUMBER
6240: )
6241: WHERE cws.batch_id = p_Entry_id;
6242: debug('Populated INSTANCE_ID column');
6494: BEGIN
6495: UPDATE csi_webadi_staging cws SET OLD_PARENT_INSTANCE =
6496: (
6497: select cii.instance_number
6498: from csi_ii_relationships cir, csi_item_instances cii
6499: where cir.subject_id = cws.INSTANCE_ID
6500: and cir.object_id = cii.instance_id
6501: and Nvl(cir.ACTIVE_END_DATE, SYSDATE + 1) > sysdate
6502: )