[Home] [Help]
48: l_internal_party_id NUMBER;
49: l_party_id NUMBER;
50: l_version_label VARCHAR2(30);
51: l_version_label_rec csi_datastructures_pub.version_label_rec;
52: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
53: l_location_tbl csi_item_instance_pvt.location_tbl;
54: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
55: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
56: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
49: l_party_id NUMBER;
50: l_version_label VARCHAR2(30);
51: l_version_label_rec csi_datastructures_pub.version_label_rec;
52: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
53: l_location_tbl csi_item_instance_pvt.location_tbl;
54: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
55: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
56: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
57: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
50: l_version_label VARCHAR2(30);
51: l_version_label_rec csi_datastructures_pub.version_label_rec;
52: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
53: l_location_tbl csi_item_instance_pvt.location_tbl;
54: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
55: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
56: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
57: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
58: l_asset_count_rec csi_asset_pvt.asset_count_rec;
51: l_version_label_rec csi_datastructures_pub.version_label_rec;
52: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
53: l_location_tbl csi_item_instance_pvt.location_tbl;
54: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
55: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
56: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
57: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
58: l_asset_count_rec csi_asset_pvt.asset_count_rec;
59: l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
52: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
53: l_location_tbl csi_item_instance_pvt.location_tbl;
54: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
55: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
56: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
57: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
58: l_asset_count_rec csi_asset_pvt.asset_count_rec;
59: l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
60: l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
306: END LOOP;
307: END IF;
308:
309:
310: csi_item_instance_pvt.create_item_instance
311: (
312: p_api_version => p_api_version
313: ,p_commit => fnd_api.g_false
314: ,p_init_msg_list => p_init_msg_list
332: WHILE l_msg_count > 0 LOOP
333: x_msg_data := FND_MSG_PUB.GET
334: ( l_msg_index,
335: FND_API.G_FALSE );
336: csi_gen_utility_pvt.put_line( ' Error from CSI_ITEM_INSTANCE_PVT.. ');
337: csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
338: l_msg_index := l_msg_index + 1;
339: l_msg_count := l_msg_count - 1;
340: END LOOP;
687: l_version_label_rec.date_time_stamp := SYSDATE;
688: l_version_label_rec.active_start_date := p_instance_rec.active_start_date;
689:
690:
691: csi_item_instance_pvt.create_version_label
692: (
693: p_api_version => p_api_version
694: ,p_commit => fnd_api.g_false
695: ,p_init_msg_list => p_init_msg_list
701: ,x_msg_data => x_msg_data
702: );
703:
704: IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
705: csi_gen_utility_pvt.put_line( ' Error from CSI_ITEM_INSTANCE_PVT.CREATE_VERSION_LABEL..');
706: csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
707: FND_MESSAGE.SET_NAME('CSI','CSI_FAILED_TO_CREATE_VERSION');
708: FND_MESSAGE.SET_TOKEN('API_ERROR', 'CREATE_VERSION_LABEL');
709: FND_MSG_PUB.Add;
743: -- End addition by sk for fixing bug 2245976
744: l_transaction_type:= 'NEW';
745: IF (p_instance_rec.call_contracts <> fnd_api.g_false) --added by radha on 04/04/02
746: THEN
747: csi_item_instance_pvt.Call_to_Contracts(
748: p_transaction_type => l_transaction_type
749: ,p_instance_id => p_instance_rec.instance_id
750: ,p_new_instance_id => NULL
751: ,p_vld_org_id => p_instance_rec.vld_organization_id
872: THEN
873: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
874: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
875: l_config_instance_rec.config_valid_status:='1'; --INVALID
876: csi_item_instance_pvt.update_item_instance
877: (
878: p_api_version => p_api_version
879: ,p_commit => fnd_api.g_false
880: ,p_init_msg_list => p_init_msg_list
924: THEN
925: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
926: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
927: l_config_instance_rec.config_valid_status:='0'; --VALID
928: csi_item_instance_pvt.update_item_instance
929: (
930: p_api_version => p_api_version
931: ,p_commit => fnd_api.g_false
932: ,p_init_msg_list => p_init_msg_list
1256: l_party_id NUMBER;
1257: l_active_end_date DATE;
1258: l_dummy VARCHAR2(1);
1259: l_version_label_rec csi_datastructures_pub.version_label_rec;
1260: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1261: l_location_tbl csi_item_instance_pvt.location_tbl;
1262: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1263: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1264: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1257: l_active_end_date DATE;
1258: l_dummy VARCHAR2(1);
1259: l_version_label_rec csi_datastructures_pub.version_label_rec;
1260: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1261: l_location_tbl csi_item_instance_pvt.location_tbl;
1262: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1263: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1264: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1265: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1258: l_dummy VARCHAR2(1);
1259: l_version_label_rec csi_datastructures_pub.version_label_rec;
1260: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1261: l_location_tbl csi_item_instance_pvt.location_tbl;
1262: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1263: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1264: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1265: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1266: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1259: l_version_label_rec csi_datastructures_pub.version_label_rec;
1260: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1261: l_location_tbl csi_item_instance_pvt.location_tbl;
1262: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1263: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1264: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1265: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1266: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1267: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1260: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1261: l_location_tbl csi_item_instance_pvt.location_tbl;
1262: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1263: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1264: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1265: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1266: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1267: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1268: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1264: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1265: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1266: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1267: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1268: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1269: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
1270: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
1271: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
1272: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1265: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1266: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1267: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1268: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1269: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
1270: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
1271: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
1272: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1273: l_asset_count_rec csi_asset_pvt.asset_count_rec;
1266: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1267: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1268: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1269: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
1270: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
1271: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
1272: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1273: l_asset_count_rec csi_asset_pvt.asset_count_rec;
1274: l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1267: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1268: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1269: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
1270: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
1271: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
1272: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1273: l_asset_count_rec csi_asset_pvt.asset_count_rec;
1274: l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1275: l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1488: csi_gen_utility_pvt.put_line('p_instance_rec = '||to_char(p_instance_rec.instance_id));
1489: -- End Add Code for Siebel Genesis Project
1490:
1491: --Added for MACD lock functionality
1492: IF NOT (csi_Item_Instance_Pvt.Anything_To_Update(p_instance_rec => p_instance_rec ))
1493: THEN
1494: -- If Anything is getting updated then lock check will be made in pvt.
1495: IF p_instance_rec.instance_id IS NOT NULL AND
1496: p_instance_rec.instance_id <> fnd_api.g_miss_num
1494: -- If Anything is getting updated then lock check will be made in pvt.
1495: IF p_instance_rec.instance_id IS NOT NULL AND
1496: p_instance_rec.instance_id <> fnd_api.g_miss_num
1497: THEN
1498: csi_item_instance_pvt.get_instance_lock_status
1499: ( p_instance_id => p_instance_rec.instance_id ,
1500: p_lock_status => l_lock_status
1501: );
1502: IF (p_txn_rec.transaction_type_id = 401 AND
1773: END IF;
1774: -- End fetching old instance data for the purpose of contracts
1775: */ -- End commentation by sguthiva for bug 2307804
1776: -- IF any of the instance columns are changing then call the update_item_instance private api
1777: IF (csi_Item_Instance_Pvt.Anything_To_Update(p_instance_rec => p_instance_rec ))
1778: THEN
1779:
1780: l_new_instance_rec := p_instance_rec;
1781:
1881: l_updated:=TRUE;
1882: END IF;
1883: END IF;
1884: -- Call the update_item_instance private API to update the instances
1885: csi_item_instance_pvt.update_item_instance
1886: (
1887: p_api_version => p_api_version
1888: ,p_commit => fnd_api.g_false
1889: ,p_init_msg_list => p_init_msg_list
1925: IF l_owner_party_id IS NOT NULL AND
1926: l_owner_party_id <> l_internal_party_id THEN
1927: IF p_txn_rec.transaction_type_id <> 7 -- Added for bug 3973706
1928: THEN
1929: CSI_Item_Instance_Pvt.Call_to_Contracts
1930: ( p_transaction_type => 'UPD'
1931: ,p_instance_id => p_instance_rec.instance_id
1932: ,p_new_instance_id => NULL
1933: ,p_vld_org_id => p_instance_rec.vld_organization_id
1969: p_instance_rec.install_date <> FND_API.G_MISS_DATE AND
1970: nvl(l_old_ins_csr.install_date,FND_API.G_MISS_DATE) <> p_instance_rec.install_date) ) THEN
1971: IF l_owner_party_id IS NOT NULL AND
1972: l_owner_party_id <> l_internal_party_id THEN
1973: CSI_Item_Instance_Pvt.Call_to_Contracts
1974: ( p_transaction_type => 'IDC'
1975: ,p_instance_id => p_instance_rec.instance_id
1976: ,p_new_instance_id => NULL
1977: ,p_vld_org_id => p_instance_rec.vld_organization_id
2530: IF ((p_ext_attrib_values_tbl(tab_row).attribute_value_id IS NULL)
2531: OR
2532: (p_ext_attrib_values_tbl(tab_row).attribute_value_id = FND_API.G_MISS_NUM))
2533: THEN
2534: csi_item_instance_pvt.create_extended_attrib_values
2535: ( p_api_version => p_api_version
2536: ,p_commit => fnd_api.g_false
2537: ,p_init_msg_list => p_init_msg_list
2538: ,p_validation_level => p_validation_level
2547: ,p_ext_cat_tbl => l_ext_cat_tbl
2548: );
2549: ELSE
2550: -- call the update extended attributes api
2551: csi_item_instance_pvt.update_extended_attrib_values
2552: ( p_api_version => p_api_version
2553: ,p_commit => fnd_api.g_false
2554: ,p_init_msg_list => p_init_msg_list
2555: ,p_validation_level => p_validation_level
2764: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
2765: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
2766: l_config_instance_rec.config_valid_status:='1'; --INVALID
2767:
2768: csi_item_instance_pvt.update_item_instance
2769: (
2770: p_api_version => p_api_version
2771: ,p_commit => fnd_api.g_false
2772: ,p_init_msg_list => p_init_msg_list
2815: THEN
2816: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
2817: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
2818: l_config_instance_rec.config_valid_status:='0'; --VALID
2819: csi_item_instance_pvt.update_item_instance
2820: (
2821: p_api_version => p_api_version
2822: ,p_commit => fnd_api.g_false
2823: ,p_init_msg_list => p_init_msg_list
2885: WHEN NO_DATA_FOUND THEN
2886: NULL;
2887: END;
2888: -- Call the update_item_instance private API to update the instances
2889: csi_item_instance_pvt.update_item_instance
2890: (
2891: p_api_version => p_api_version
2892: ,p_commit => fnd_api.g_false
2893: ,p_init_msg_list => p_init_msg_list
2974: l_version_label_rec.version_label := p_instance_rec.version_label;
2975: l_version_label_rec.description := p_instance_rec.version_label_description;
2976: l_version_label_rec.date_time_stamp := SYSDATE;
2977: -- calling create version label api
2978: csi_item_instance_pvt.create_version_label
2979: ( p_api_version => p_api_version
2980: ,p_commit => p_commit
2981: ,p_init_msg_list => p_init_msg_list
2982: ,p_validation_level => p_validation_level
3082: IF (l_new_instance_rec.call_contracts <> fnd_api.g_false) --added by radha on 04/04/02
3083: THEN
3084: IF p_txn_rec.transaction_type_id <> 7 -- Added for bug 3973706
3085: THEN
3086: csi_item_instance_pvt.Call_to_Contracts
3087: ( p_transaction_type => l_transaction_type
3088: ,p_instance_id => p_instance_rec.instance_id
3089: ,p_new_instance_id => NULL
3090: ,p_vld_org_id => p_instance_rec.vld_organization_id
3522: l_config_temp_rec csi_datastructures_pub.instance_rec ;
3523: l_batch_hdr_id NUMBER;
3524: l_batch_rev_nbr NUMBER;
3525: l_instance_id_lst csi_datastructures_pub.id_tbl;
3526: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3527: l_location_tbl csi_item_instance_pvt.location_tbl;
3528: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3529: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3530: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3523: l_batch_hdr_id NUMBER;
3524: l_batch_rev_nbr NUMBER;
3525: l_instance_id_lst csi_datastructures_pub.id_tbl;
3526: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3527: l_location_tbl csi_item_instance_pvt.location_tbl;
3528: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3529: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3530: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3531: l_config_ins_rec csi_datastructures_pub.instance_rec;
3524: l_batch_rev_nbr NUMBER;
3525: l_instance_id_lst csi_datastructures_pub.id_tbl;
3526: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3527: l_location_tbl csi_item_instance_pvt.location_tbl;
3528: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3529: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3530: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3531: l_config_ins_rec csi_datastructures_pub.instance_rec;
3532: l_config_tmp_rec csi_datastructures_pub.instance_rec;
3525: l_instance_id_lst csi_datastructures_pub.id_tbl;
3526: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3527: l_location_tbl csi_item_instance_pvt.location_tbl;
3528: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3529: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3530: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3531: l_config_ins_rec csi_datastructures_pub.instance_rec;
3532: l_config_tmp_rec csi_datastructures_pub.instance_rec;
3533: --
3526: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3527: l_location_tbl csi_item_instance_pvt.location_tbl;
3528: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3529: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3530: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3531: l_config_ins_rec csi_datastructures_pub.instance_rec;
3532: l_config_tmp_rec csi_datastructures_pub.instance_rec;
3533: --
3534: px_oks_txn_inst_tbl OKS_IBINT_PUB.TXN_INSTANCE_TBL;
3664: RAISE FND_API.G_EXC_ERROR;
3665: END;
3666: -- End PRE User Hooks
3667: --
3668: csi_item_instance_pvt.expire_item_instance
3669: (
3670: p_api_version => p_api_version
3671: ,p_commit => fnd_api.g_false
3672: ,p_init_msg_list => p_init_msg_list
3846: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
3847: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
3848: l_config_instance_rec.config_valid_status:='1'; --INVALID
3849: --
3850: csi_item_instance_pvt.update_item_instance
3851: (
3852: p_api_version => p_api_version
3853: ,p_commit => fnd_api.g_false
3854: ,p_init_msg_list => p_init_msg_list
3897: THEN
3898: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
3899: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
3900: l_config_instance_rec.config_valid_status:='0'; --VALID
3901: csi_item_instance_pvt.update_item_instance
3902: (
3903: p_api_version => p_api_version
3904: ,p_commit => fnd_api.g_false
3905: ,p_init_msg_list => p_init_msg_list
4231:
4232: END IF;
4233:
4234: -- Generate the where clause
4235: csi_item_instance_pvt.Gen_Inst_Where_Clause
4236: ( p_instance_query_rec => p_instance_query_rec,
4237: p_party_query_rec => p_party_query_rec,
4238: p_pty_acct_query_rec => p_account_query_rec,
4239: p_transaction_id => p_transaction_id,
4246: --Parse the select statement
4247: dbms_sql.parse(l_cur_get_inst_rel, l_select_stmt , dbms_sql.native);
4248:
4249: -- Bind the variables
4250: csi_item_instance_pvt.Bind_inst_variable(p_instance_query_rec,
4251: p_party_query_rec,
4252: p_account_query_rec,
4253: p_transaction_id,
4254: l_cur_get_inst_rel);
4253: p_transaction_id,
4254: l_cur_get_inst_rel);
4255:
4256: -- Define output variables
4257: csi_item_instance_pvt.Define_Inst_Columns(l_cur_get_inst_rel,
4258: p_instance_query_rec );
4259:
4260: -- execute the select statement
4261: l_rows_processed := dbms_sql.execute(l_cur_get_inst_rel);
4263: -- x_instance_header_tbl is constructed. This is basically to l_instance_id_list
4264: -- with the bind variable p_instance_id.
4265: LOOP
4266: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_inst_rel) = 0;
4267: csi_item_instance_pvt.Get_Inst_Column_Values(l_cur_get_inst_rel,
4268: l_instance_id );
4269: l_count := l_count + 1;
4270: csi_item_instance_pvt.Construct_inst_header_rec(l_instance_id,
4271: x_instance_header_tbl);
4266: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_inst_rel) = 0;
4267: csi_item_instance_pvt.Get_Inst_Column_Values(l_cur_get_inst_rel,
4268: l_instance_id );
4269: l_count := l_count + 1;
4270: csi_item_instance_pvt.Construct_inst_header_rec(l_instance_id,
4271: x_instance_header_tbl);
4272: /***** COMMENTED FOR Bug # 2636868 IF l_instance_id_list IS NULL THEN
4273: l_instance_id_list := to_char(l_instance_id);
4274: ELSE
4279: -- Close the cursor
4280: DBMS_SQL.CLOSE_CURSOR(l_cur_get_inst_rel);
4281:
4282: /***** COMMENTED FOR Bug # 2636868 IF l_instance_id_list IS NOT NULL THEN
4283: csi_item_instance_pvt.Construct_inst_header_rec(l_instance_id_list,
4284: x_instance_header_tbl);
4285: END IF; *****/
4286: --
4287: -- srramakr Get_Item_Instances is a frequently called API. Unless there is a requirement,
4291: -- Resolve_id routine has already been modified to get the version label meaning.
4292: --
4293: IF p_resolve_id_columns = fnd_api.g_true THEN
4294: IF x_instance_header_tbl.count > 0 THEN
4295: csi_item_instance_pvt.resolve_id_columns
4296: (p_instance_header_tbl => x_instance_header_tbl);
4297:
4298: -- Added by sguthiva for att enhancements
4299: csi_Item_Instance_Vld_pvt.get_link_locations
4507: --Parse the select statement
4508: dbms_sql.parse(l_cur_get_instance_rel, l_select_stmt , dbms_sql.native);
4509:
4510: -- Bind the variables
4511: csi_item_instance_pvt.Bind_instance_variable(p_instance_rec,
4512: l_cur_get_instance_rel);
4513:
4514: -- Define output variables
4515: csi_item_instance_pvt.Define_Instance_Columns(l_cur_get_instance_rel);
4511: csi_item_instance_pvt.Bind_instance_variable(p_instance_rec,
4512: l_cur_get_instance_rel);
4513:
4514: -- Define output variables
4515: csi_item_instance_pvt.Define_Instance_Columns(l_cur_get_instance_rel);
4516:
4517: -- execute the select statement
4518: l_rows_processed := dbms_sql.execute(l_cur_get_instance_rel);
4519:
4518: l_rows_processed := dbms_sql.execute(l_cur_get_instance_rel);
4519:
4520: LOOP
4521: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_instance_rel) = 0;
4522: csi_item_instance_pvt.Get_Instance_Col_Values(l_cur_get_instance_rel,
4523: l_instance_rec
4524: );
4525: p_instance_rec := l_instance_rec;
4526: END LOOP;
4559: END IF;
4560: IF (p_time_stamp <= sysdate) THEN
4561: -- construct from the history if the p_time_stamp
4562: -- is < than sysdate
4563: csi_item_instance_pvt.Construct_inst_from_hist(p_instance_rec, l_time_stamp);
4564: ELSE
4565: FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
4566: FND_MSG_PUB.ADD;
4567: RAISE FND_API.G_EXC_ERROR;
4578: IF p_resolve_id_columns = fnd_api.g_true THEN
4579:
4580: l_instance_header_tbl(1) := p_instance_rec;
4581:
4582: csi_item_instance_pvt.resolve_id_columns
4583: (p_instance_header_tbl => l_instance_header_tbl);
4584:
4585: p_instance_rec := l_instance_header_tbl(1);
4586: END IF;
4959: RAISE FND_API.G_EXC_ERROR;
4960: END IF;
4961:
4962: -- Generate the where clause
4963: csi_item_instance_pvt.Gen_Ver_Where_Clause
4964: ( p_ver_label_query_rec => p_version_label_query_rec,
4965: x_where_clause => l_where_clause );
4966:
4967: -- Build the select statement
4973: --Parse the select statement
4974: dbms_sql.parse(l_get_ver_cursor_id, l_select_stmt , dbms_sql.native);
4975:
4976: -- Bind the variables
4977: csi_item_instance_pvt.Bind_Ver_variable(p_version_label_query_rec, l_get_ver_cursor_id);
4978:
4979: -- Define output variables
4980: csi_item_instance_pvt.Define_Ver_Columns(l_get_ver_cursor_id);
4981:
4976: -- Bind the variables
4977: csi_item_instance_pvt.Bind_Ver_variable(p_version_label_query_rec, l_get_ver_cursor_id);
4978:
4979: -- Define output variables
4980: csi_item_instance_pvt.Define_Ver_Columns(l_get_ver_cursor_id);
4981:
4982: -- execute the select statement
4983: l_rows_processed := dbms_sql.execute(l_get_ver_cursor_id);
4984:
4983: l_rows_processed := dbms_sql.execute(l_get_ver_cursor_id);
4984:
4985: LOOP
4986: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_get_ver_cursor_id) = 0;
4987: csi_item_instance_pvt.Get_Ver_Column_Values(l_get_ver_cursor_id, l_version_label_rec);
4988: l_count := l_count + 1;
4989: x_version_label_tbl(l_count) := l_version_label_rec;
4990: END LOOP;
4991:
4992: -- Close the cursor
4993: DBMS_SQL.CLOSE_CURSOR(l_get_ver_cursor_id);
4994:
4995: IF p_time_stamp < sysdate THEN
4996: csi_item_instance_pvt.Construct_ver_from_hist(x_version_label_tbl, p_time_stamp);
4997: ELSE
4998: If (p_time_stamp <> fnd_api.g_miss_date and p_time_stamp > sysdate) THEN
4999: FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
5000: FND_MSG_PUB.ADD;
5138: FOR l_count IN p_version_label_tbl.FIRST..p_version_label_tbl.LAST
5139: LOOP
5140: IF p_version_label_tbl.EXISTS(l_count) THEN
5141:
5142: csi_item_instance_pvt.create_version_label
5143: ( p_api_version => p_api_version
5144: ,p_commit => p_commit
5145: ,p_init_msg_list => p_init_msg_list
5146: ,p_validation_level => p_validation_level
5304: FOR l_count IN p_version_label_tbl.FIRST..p_version_label_tbl.LAST
5305: LOOP
5306: IF p_version_label_tbl.EXISTS(l_count) THEN
5307:
5308: csi_item_instance_pvt.update_version_label
5309: ( p_api_version => p_api_version
5310: ,p_commit => p_commit
5311: ,p_init_msg_list => p_init_msg_list
5312: ,p_validation_level => p_validation_level
5463: FOR l_count IN p_version_label_tbl.FIRST..p_version_label_tbl.LAST
5464: LOOP
5465: IF p_version_label_tbl.EXISTS(l_count) THEN
5466:
5467: csi_item_instance_pvt.expire_version_label
5468: ( p_api_version => p_api_version
5469: ,p_commit => p_commit
5470: ,p_init_msg_list => p_init_msg_list
5471: ,p_validation_level => p_validation_level
5789: END IF;
5790:
5791:
5792: -- Generate the where clause
5793: csi_item_instance_pvt.Gen_ext_Where_Clause
5794: ( p_ext_query_rec => p_ext_attribs_query_rec,
5795: x_where_clause => l_where_clause );
5796:
5797: -- Build the select statement
5831: --Parse the select statement
5832: dbms_sql.parse(l_cur_get_ext, l_select_stmt , dbms_sql.native);
5833:
5834: -- Bind the variables
5835: csi_item_instance_pvt.Bind_ext_variable
5836: ( p_ext_attribs_query_rec,
5837: l_cur_get_ext );
5838:
5839: -- Define output variables
5836: ( p_ext_attribs_query_rec,
5837: l_cur_get_ext );
5838:
5839: -- Define output variables
5840: csi_item_instance_pvt.Define_ext_Columns(l_cur_get_ext);
5841:
5842: -- execute the select statement
5843: l_rows_processed := dbms_sql.execute(l_cur_get_ext);
5844:
5844:
5845:
5846: LOOP
5847: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_ext) = 0;
5848: csi_item_instance_pvt.Get_ext_Column_Values(l_cur_get_ext, l_ext_rec);
5849: l_count := l_count + 1;
5850: x_ext_attrib_tbl(l_count) := l_ext_rec;
5851: END LOOP;
5852:
5854: DBMS_SQL.CLOSE_CURSOR(l_cur_get_ext);
5855:
5856: IF (p_time_stamp IS NOT NULL) AND (p_time_stamp <> FND_API.G_MISS_DATE) THEN
5857: IF p_time_stamp <= sysdate THEN
5858: csi_item_instance_pvt.Construct_ext_From_Hist(x_ext_attrib_tbl, p_time_stamp);
5859: ELSE
5860: FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
5861: FND_MSG_PUB.ADD;
5862: RAISE FND_API.G_EXC_ERROR;
5991: l_debug_level NUMBER;
5992: l_msg_index NUMBER;
5993: l_msg_count NUMBER;
5994: l_trace_enable_flag VARCHAR2(1) :='N';
5995: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
5996: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
5997: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
5998: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
5999: BEGIN
5992: l_msg_index NUMBER;
5993: l_msg_count NUMBER;
5994: l_trace_enable_flag VARCHAR2(1) :='N';
5995: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
5996: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
5997: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
5998: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
5999: BEGIN
6000:
5993: l_msg_count NUMBER;
5994: l_trace_enable_flag VARCHAR2(1) :='N';
5995: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
5996: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
5997: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
5998: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
5999: BEGIN
6000:
6001: -- Standard Start of API savepoint
5994: l_trace_enable_flag VARCHAR2(1) :='N';
5995: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
5996: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
5997: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
5998: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
5999: BEGIN
6000:
6001: -- Standard Start of API savepoint
6002: SAVEPOINT create_extended_attrib_values;
6061: IF p_ext_attrib_tbl.COUNT > 0 THEN
6062: FOR tab_row IN p_ext_attrib_tbl.FIRST .. p_ext_attrib_tbl.LAST
6063: LOOP
6064: IF p_ext_attrib_tbl.EXISTS(tab_row) THEN
6065: csi_item_instance_pvt.create_extended_attrib_values
6066: ( p_api_version => p_api_version
6067: ,p_commit => fnd_api.g_false
6068: ,p_init_msg_list => p_init_msg_list
6069: ,p_validation_level => p_validation_level
6184: l_debug_level NUMBER;
6185: l_msg_index NUMBER;
6186: l_msg_count NUMBER;
6187: l_trace_enable_flag VARCHAR2(1) :='N';
6188: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6189: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6190: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6191: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6192: BEGIN
6185: l_msg_index NUMBER;
6186: l_msg_count NUMBER;
6187: l_trace_enable_flag VARCHAR2(1) :='N';
6188: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6189: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6190: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6191: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6192: BEGIN
6193:
6186: l_msg_count NUMBER;
6187: l_trace_enable_flag VARCHAR2(1) :='N';
6188: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6189: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6190: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6191: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6192: BEGIN
6193:
6194: -- Standard Start of API savepoint
6187: l_trace_enable_flag VARCHAR2(1) :='N';
6188: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6189: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6190: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6191: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6192: BEGIN
6193:
6194: -- Standard Start of API savepoint
6195: SAVEPOINT update_extended_attrib_values;
6251: IF p_ext_attrib_tbl.COUNT > 0 THEN
6252: FOR tab_row IN p_ext_attrib_tbl.FIRST .. p_ext_attrib_tbl.LAST
6253: LOOP
6254: IF p_ext_attrib_tbl.EXISTS(tab_row) THEN
6255: csi_item_instance_pvt.update_extended_attrib_values
6256: (p_api_version => p_api_version
6257: ,p_commit => fnd_api.g_false
6258: ,p_init_msg_list => p_init_msg_list
6259: ,p_validation_level => p_validation_level
6437: FOR tab_row IN p_ext_attrib_tbl.FIRST .. p_ext_attrib_tbl.LAST
6438: LOOP
6439: IF p_ext_attrib_tbl.EXISTS(tab_row) THEN
6440:
6441: csi_item_instance_pvt.expire_extended_attrib_values
6442: ( p_api_version => p_api_version
6443: ,p_commit => fnd_api.g_false
6444: ,p_init_msg_list => p_init_msg_list
6445: ,p_validation_level => p_validation_level
6628: END IF;
6629:
6630: -- Start API body
6631:
6632: csi_item_instance_pvt.copy_item_instance
6633: ( p_api_version => p_api_version
6634: ,p_commit => fnd_api.g_false
6635: ,p_init_msg_list => p_init_msg_list
6636: ,p_validation_level => p_validation_level