[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;
307: END LOOP;
308: END IF;
309:
310:
311: csi_item_instance_pvt.create_item_instance
312: (
313: p_api_version => p_api_version
314: ,p_commit => fnd_api.g_false
315: ,p_init_msg_list => p_init_msg_list
333: WHILE l_msg_count > 0 LOOP
334: x_msg_data := FND_MSG_PUB.GET
335: ( l_msg_index,
336: FND_API.G_FALSE );
337: csi_gen_utility_pvt.put_line( ' Error from CSI_ITEM_INSTANCE_PVT.. ');
338: csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
339: l_msg_index := l_msg_index + 1;
340: l_msg_count := l_msg_count - 1;
341: END LOOP;
688: l_version_label_rec.date_time_stamp := SYSDATE;
689: l_version_label_rec.active_start_date := p_instance_rec.active_start_date;
690:
691:
692: csi_item_instance_pvt.create_version_label
693: (
694: p_api_version => p_api_version
695: ,p_commit => fnd_api.g_false
696: ,p_init_msg_list => p_init_msg_list
702: ,x_msg_data => x_msg_data
703: );
704:
705: IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
706: csi_gen_utility_pvt.put_line( ' Error from CSI_ITEM_INSTANCE_PVT.CREATE_VERSION_LABEL..');
707: csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
708: FND_MESSAGE.SET_NAME('CSI','CSI_FAILED_TO_CREATE_VERSION');
709: FND_MESSAGE.SET_TOKEN('API_ERROR', 'CREATE_VERSION_LABEL');
710: FND_MSG_PUB.Add;
755: -- End DEBUG section
756:
757: IF (p_instance_rec.call_contracts <> fnd_api.g_false AND p_instance_rec.call_contracts <> 'N') --added by radha on 04/04/02 --added by HYONLEE on 10/30/09
758: THEN
759: csi_item_instance_pvt.Call_to_Contracts(
760: p_transaction_type => l_transaction_type
761: ,p_instance_id => p_instance_rec.instance_id
762: ,p_new_instance_id => NULL
763: ,p_vld_org_id => p_instance_rec.vld_organization_id
884: THEN
885: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
886: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
887: l_config_instance_rec.config_valid_status:='1'; --INVALID
888: csi_item_instance_pvt.update_item_instance
889: (
890: p_api_version => p_api_version
891: ,p_commit => fnd_api.g_false
892: ,p_init_msg_list => p_init_msg_list
936: THEN
937: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
938: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
939: l_config_instance_rec.config_valid_status:='0'; --VALID
940: csi_item_instance_pvt.update_item_instance
941: (
942: p_api_version => p_api_version
943: ,p_commit => fnd_api.g_false
944: ,p_init_msg_list => p_init_msg_list
1274: l_party_id NUMBER;
1275: l_active_end_date DATE;
1276: l_dummy VARCHAR2(1);
1277: l_version_label_rec csi_datastructures_pub.version_label_rec;
1278: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1279: l_location_tbl csi_item_instance_pvt.location_tbl;
1280: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1281: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1282: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1275: l_active_end_date DATE;
1276: l_dummy VARCHAR2(1);
1277: l_version_label_rec csi_datastructures_pub.version_label_rec;
1278: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1279: l_location_tbl csi_item_instance_pvt.location_tbl;
1280: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1281: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1282: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1283: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1276: l_dummy VARCHAR2(1);
1277: l_version_label_rec csi_datastructures_pub.version_label_rec;
1278: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1279: l_location_tbl csi_item_instance_pvt.location_tbl;
1280: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1281: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1282: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1283: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1284: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1277: l_version_label_rec csi_datastructures_pub.version_label_rec;
1278: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1279: l_location_tbl csi_item_instance_pvt.location_tbl;
1280: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1281: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1282: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1283: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1284: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1285: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1278: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1279: l_location_tbl csi_item_instance_pvt.location_tbl;
1280: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1281: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1282: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1283: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1284: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1285: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1286: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1282: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1283: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1284: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1285: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1286: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1287: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
1288: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
1289: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
1290: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1283: l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
1284: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1285: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1286: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1287: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
1288: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
1289: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
1290: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1291: l_asset_count_rec csi_asset_pvt.asset_count_rec;
1284: l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
1285: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1286: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1287: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
1288: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
1289: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
1290: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1291: l_asset_count_rec csi_asset_pvt.asset_count_rec;
1292: l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1285: l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
1286: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
1287: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
1288: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
1289: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
1290: l_asset_lookup_tbl csi_asset_pvt.lookup_tbl;
1291: l_asset_count_rec csi_asset_pvt.asset_count_rec;
1292: l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1293: l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1555: csi_gen_utility_pvt.put_line('l_create_event_called : '||l_create_event_called);
1556: END IF;
1557:
1558: --Added for MACD lock functionality
1559: IF NOT (csi_Item_Instance_Pvt.Anything_To_Update(p_instance_rec => p_instance_rec ))
1560: THEN
1561: -- If Anything is getting updated then lock check will be made in pvt.
1562: IF p_instance_rec.instance_id IS NOT NULL AND
1563: p_instance_rec.instance_id <> fnd_api.g_miss_num
1561: -- If Anything is getting updated then lock check will be made in pvt.
1562: IF p_instance_rec.instance_id IS NOT NULL AND
1563: p_instance_rec.instance_id <> fnd_api.g_miss_num
1564: THEN
1565: csi_item_instance_pvt.get_instance_lock_status
1566: ( p_instance_id => p_instance_rec.instance_id ,
1567: p_lock_status => l_lock_status
1568: );
1569: IF (p_txn_rec.transaction_type_id = 401 AND
1855: END IF;
1856: -- End fetching old instance data for the purpose of contracts
1857: */ -- End commentation by sguthiva for bug 2307804
1858: -- IF any of the instance columns are changing then call the update_item_instance private api
1859: IF (csi_Item_Instance_Pvt.Anything_To_Update(p_instance_rec => p_instance_rec ))
1860: THEN
1861:
1862: l_new_instance_rec := p_instance_rec;
1863:
1963: l_updated:=TRUE;
1964: END IF;
1965: END IF;
1966: -- Call the update_item_instance private API to update the instances
1967: csi_item_instance_pvt.update_item_instance
1968: (
1969: p_api_version => p_api_version
1970: ,p_commit => fnd_api.g_false
1971: ,p_init_msg_list => p_init_msg_list
2007: IF l_owner_party_id IS NOT NULL AND
2008: l_owner_party_id <> l_internal_party_id THEN
2009: IF p_txn_rec.transaction_type_id <> 7 -- Added for bug 3973706
2010: THEN
2011: CSI_Item_Instance_Pvt.Call_to_Contracts
2012: ( p_transaction_type => 'UPD'
2013: ,p_instance_id => p_instance_rec.instance_id
2014: ,p_new_instance_id => NULL
2015: ,p_vld_org_id => p_instance_rec.vld_organization_id
2051: p_instance_rec.install_date <> FND_API.G_MISS_DATE AND
2052: nvl(l_old_ins_csr.install_date,FND_API.G_MISS_DATE) <> p_instance_rec.install_date) ) THEN
2053: IF l_owner_party_id IS NOT NULL AND
2054: l_owner_party_id <> l_internal_party_id THEN
2055: CSI_Item_Instance_Pvt.Call_to_Contracts
2056: ( p_transaction_type => 'IDC'
2057: ,p_instance_id => p_instance_rec.instance_id
2058: ,p_new_instance_id => NULL
2059: ,p_vld_org_id => p_instance_rec.vld_organization_id
2613: IF ((p_ext_attrib_values_tbl(tab_row).attribute_value_id IS NULL)
2614: OR
2615: (p_ext_attrib_values_tbl(tab_row).attribute_value_id = FND_API.G_MISS_NUM))
2616: THEN
2617: csi_item_instance_pvt.create_extended_attrib_values
2618: ( p_api_version => p_api_version
2619: ,p_commit => fnd_api.g_false
2620: ,p_init_msg_list => p_init_msg_list
2621: ,p_validation_level => p_validation_level
2630: ,p_ext_cat_tbl => l_ext_cat_tbl
2631: );
2632: ELSE
2633: -- call the update extended attributes api
2634: csi_item_instance_pvt.update_extended_attrib_values
2635: ( p_api_version => p_api_version
2636: ,p_commit => fnd_api.g_false
2637: ,p_init_msg_list => p_init_msg_list
2638: ,p_validation_level => p_validation_level
2847: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
2848: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
2849: l_config_instance_rec.config_valid_status:='1'; --INVALID
2850:
2851: csi_item_instance_pvt.update_item_instance
2852: (
2853: p_api_version => p_api_version
2854: ,p_commit => fnd_api.g_false
2855: ,p_init_msg_list => p_init_msg_list
2898: THEN
2899: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
2900: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
2901: l_config_instance_rec.config_valid_status:='0'; --VALID
2902: csi_item_instance_pvt.update_item_instance
2903: (
2904: p_api_version => p_api_version
2905: ,p_commit => fnd_api.g_false
2906: ,p_init_msg_list => p_init_msg_list
2968: WHEN NO_DATA_FOUND THEN
2969: NULL;
2970: END;
2971: -- Call the update_item_instance private API to update the instances
2972: csi_item_instance_pvt.update_item_instance
2973: (
2974: p_api_version => p_api_version
2975: ,p_commit => fnd_api.g_false
2976: ,p_init_msg_list => p_init_msg_list
3057: l_version_label_rec.version_label := p_instance_rec.version_label;
3058: l_version_label_rec.description := p_instance_rec.version_label_description;
3059: l_version_label_rec.date_time_stamp := SYSDATE;
3060: -- calling create version label api
3061: csi_item_instance_pvt.create_version_label
3062: ( p_api_version => p_api_version
3063: ,p_commit => p_commit
3064: ,p_init_msg_list => p_init_msg_list
3065: ,p_validation_level => p_validation_level
3189: IF (l_new_instance_rec.call_contracts <> fnd_api.g_false AND l_new_instance_rec.call_contracts <> 'N') --added by radha on 04/04/02
3190: THEN
3191: IF p_txn_rec.transaction_type_id <> 7 -- Added for bug 3973706
3192: THEN
3193: csi_item_instance_pvt.Call_to_Contracts
3194: ( p_transaction_type => l_transaction_type
3195: ,p_instance_id => p_instance_rec.instance_id
3196: ,p_new_instance_id => NULL
3197: ,p_vld_org_id => p_instance_rec.vld_organization_id
3559: l_config_temp_rec csi_datastructures_pub.instance_rec ;
3560: l_batch_hdr_id NUMBER;
3561: l_batch_rev_nbr NUMBER;
3562: l_instance_id_lst csi_datastructures_pub.id_tbl;
3563: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3564: l_location_tbl csi_item_instance_pvt.location_tbl;
3565: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3566: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3567: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3560: l_batch_hdr_id NUMBER;
3561: l_batch_rev_nbr NUMBER;
3562: l_instance_id_lst csi_datastructures_pub.id_tbl;
3563: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3564: l_location_tbl csi_item_instance_pvt.location_tbl;
3565: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3566: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3567: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3568: l_config_ins_rec csi_datastructures_pub.instance_rec;
3561: l_batch_rev_nbr NUMBER;
3562: l_instance_id_lst csi_datastructures_pub.id_tbl;
3563: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3564: l_location_tbl csi_item_instance_pvt.location_tbl;
3565: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3566: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3567: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3568: l_config_ins_rec csi_datastructures_pub.instance_rec;
3569: l_config_tmp_rec csi_datastructures_pub.instance_rec;
3562: l_instance_id_lst csi_datastructures_pub.id_tbl;
3563: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3564: l_location_tbl csi_item_instance_pvt.location_tbl;
3565: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3566: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3567: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3568: l_config_ins_rec csi_datastructures_pub.instance_rec;
3569: l_config_tmp_rec csi_datastructures_pub.instance_rec;
3570: --
3563: l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
3564: l_location_tbl csi_item_instance_pvt.location_tbl;
3565: l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
3566: l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
3567: l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
3568: l_config_ins_rec csi_datastructures_pub.instance_rec;
3569: l_config_tmp_rec csi_datastructures_pub.instance_rec;
3570: --
3571: px_oks_txn_inst_tbl OKS_IBINT_PUB.TXN_INSTANCE_TBL;
3701: RAISE FND_API.G_EXC_ERROR;
3702: END;
3703: -- End PRE User Hooks
3704: --
3705: csi_item_instance_pvt.expire_item_instance
3706: (
3707: p_api_version => p_api_version
3708: ,p_commit => fnd_api.g_false
3709: ,p_init_msg_list => p_init_msg_list
3884: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
3885: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
3886: l_config_instance_rec.config_valid_status:='1'; --INVALID
3887: --
3888: csi_item_instance_pvt.update_item_instance
3889: (
3890: p_api_version => p_api_version
3891: ,p_commit => fnd_api.g_false
3892: ,p_init_msg_list => p_init_msg_list
3935: THEN
3936: l_config_instance_rec.instance_id:=l_instance_key.instance_id;
3937: l_config_instance_rec.object_version_number:=l_instance_key.object_version_number;
3938: l_config_instance_rec.config_valid_status:='0'; --VALID
3939: csi_item_instance_pvt.update_item_instance
3940: (
3941: p_api_version => p_api_version
3942: ,p_commit => fnd_api.g_false
3943: ,p_init_msg_list => p_init_msg_list
4273:
4274: END IF;
4275:
4276: -- Generate the where clause
4277: csi_item_instance_pvt.Gen_Inst_Where_Clause
4278: ( p_instance_query_rec => p_instance_query_rec,
4279: p_party_query_rec => p_party_query_rec,
4280: p_pty_acct_query_rec => p_account_query_rec,
4281: p_transaction_id => p_transaction_id,
4288: --Parse the select statement
4289: dbms_sql.parse(l_cur_get_inst_rel, l_select_stmt , dbms_sql.native);
4290:
4291: -- Bind the variables
4292: csi_item_instance_pvt.Bind_inst_variable(p_instance_query_rec,
4293: p_party_query_rec,
4294: p_account_query_rec,
4295: p_transaction_id,
4296: l_cur_get_inst_rel);
4295: p_transaction_id,
4296: l_cur_get_inst_rel);
4297:
4298: -- Define output variables
4299: csi_item_instance_pvt.Define_Inst_Columns(l_cur_get_inst_rel,
4300: p_instance_query_rec );
4301:
4302: -- execute the select statement
4303: l_rows_processed := dbms_sql.execute(l_cur_get_inst_rel);
4305: -- x_instance_header_tbl is constructed. This is basically to l_instance_id_list
4306: -- with the bind variable p_instance_id.
4307: LOOP
4308: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_inst_rel) = 0;
4309: csi_item_instance_pvt.Get_Inst_Column_Values(l_cur_get_inst_rel,
4310: l_instance_id );
4311: l_count := l_count + 1;
4312: csi_item_instance_pvt.Construct_inst_header_rec(l_instance_id,
4313: x_instance_header_tbl);
4308: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_inst_rel) = 0;
4309: csi_item_instance_pvt.Get_Inst_Column_Values(l_cur_get_inst_rel,
4310: l_instance_id );
4311: l_count := l_count + 1;
4312: csi_item_instance_pvt.Construct_inst_header_rec(l_instance_id,
4313: x_instance_header_tbl);
4314: /***** COMMENTED FOR Bug # 2636868 IF l_instance_id_list IS NULL THEN
4315: l_instance_id_list := to_char(l_instance_id);
4316: ELSE
4321: -- Close the cursor
4322: DBMS_SQL.CLOSE_CURSOR(l_cur_get_inst_rel);
4323:
4324: /***** COMMENTED FOR Bug # 2636868 IF l_instance_id_list IS NOT NULL THEN
4325: csi_item_instance_pvt.Construct_inst_header_rec(l_instance_id_list,
4326: x_instance_header_tbl);
4327: END IF; *****/
4328: --
4329: -- srramakr Get_Item_Instances is a frequently called API. Unless there is a requirement,
4333: -- Resolve_id routine has already been modified to get the version label meaning.
4334: --
4335: IF p_resolve_id_columns = fnd_api.g_true THEN
4336: IF x_instance_header_tbl.count > 0 THEN
4337: csi_item_instance_pvt.resolve_id_columns
4338: (p_instance_header_tbl => x_instance_header_tbl);
4339:
4340: -- Added by sguthiva for att enhancements
4341: csi_Item_Instance_Vld_pvt.get_link_locations
4553: --Parse the select statement
4554: dbms_sql.parse(l_cur_get_instance_rel, l_select_stmt , dbms_sql.native);
4555:
4556: -- Bind the variables
4557: csi_item_instance_pvt.Bind_instance_variable(p_instance_rec,
4558: l_cur_get_instance_rel);
4559:
4560: -- Define output variables
4561: csi_item_instance_pvt.Define_Instance_Columns(l_cur_get_instance_rel);
4557: csi_item_instance_pvt.Bind_instance_variable(p_instance_rec,
4558: l_cur_get_instance_rel);
4559:
4560: -- Define output variables
4561: csi_item_instance_pvt.Define_Instance_Columns(l_cur_get_instance_rel);
4562:
4563: -- execute the select statement
4564: l_rows_processed := dbms_sql.execute(l_cur_get_instance_rel);
4565:
4564: l_rows_processed := dbms_sql.execute(l_cur_get_instance_rel);
4565:
4566: LOOP
4567: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_instance_rel) = 0;
4568: csi_item_instance_pvt.Get_Instance_Col_Values(l_cur_get_instance_rel,
4569: l_instance_rec
4570: );
4571: p_instance_rec := l_instance_rec;
4572: END LOOP;
4605: END IF;
4606: IF (p_time_stamp <= sysdate) THEN
4607: -- construct from the history if the p_time_stamp
4608: -- is < than sysdate
4609: csi_item_instance_pvt.Construct_inst_from_hist(p_instance_rec, l_time_stamp);
4610: ELSE
4611: FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
4612: FND_MSG_PUB.ADD;
4613: RAISE FND_API.G_EXC_ERROR;
4624: IF p_resolve_id_columns = fnd_api.g_true THEN
4625:
4626: l_instance_header_tbl(1) := p_instance_rec;
4627:
4628: csi_item_instance_pvt.resolve_id_columns
4629: (p_instance_header_tbl => l_instance_header_tbl);
4630:
4631: p_instance_rec := l_instance_header_tbl(1);
4632: END IF;
5011: RAISE FND_API.G_EXC_ERROR;
5012: END IF;
5013:
5014: -- Generate the where clause
5015: csi_item_instance_pvt.Gen_Ver_Where_Clause
5016: ( p_ver_label_query_rec => p_version_label_query_rec,
5017: x_where_clause => l_where_clause );
5018:
5019: -- Build the select statement
5025: --Parse the select statement
5026: dbms_sql.parse(l_get_ver_cursor_id, l_select_stmt , dbms_sql.native);
5027:
5028: -- Bind the variables
5029: csi_item_instance_pvt.Bind_Ver_variable(p_version_label_query_rec, l_get_ver_cursor_id);
5030:
5031: -- Define output variables
5032: csi_item_instance_pvt.Define_Ver_Columns(l_get_ver_cursor_id);
5033:
5028: -- Bind the variables
5029: csi_item_instance_pvt.Bind_Ver_variable(p_version_label_query_rec, l_get_ver_cursor_id);
5030:
5031: -- Define output variables
5032: csi_item_instance_pvt.Define_Ver_Columns(l_get_ver_cursor_id);
5033:
5034: -- execute the select statement
5035: l_rows_processed := dbms_sql.execute(l_get_ver_cursor_id);
5036:
5035: l_rows_processed := dbms_sql.execute(l_get_ver_cursor_id);
5036:
5037: LOOP
5038: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_get_ver_cursor_id) = 0;
5039: csi_item_instance_pvt.Get_Ver_Column_Values(l_get_ver_cursor_id, l_version_label_rec);
5040: l_count := l_count + 1;
5041: x_version_label_tbl(l_count) := l_version_label_rec;
5042: END LOOP;
5043:
5044: -- Close the cursor
5045: DBMS_SQL.CLOSE_CURSOR(l_get_ver_cursor_id);
5046:
5047: IF p_time_stamp < sysdate THEN
5048: csi_item_instance_pvt.Construct_ver_from_hist(x_version_label_tbl, p_time_stamp);
5049: ELSE
5050: If (p_time_stamp <> fnd_api.g_miss_date and p_time_stamp > sysdate) THEN
5051: FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
5052: FND_MSG_PUB.ADD;
5194: FOR l_count IN p_version_label_tbl.FIRST..p_version_label_tbl.LAST
5195: LOOP
5196: IF p_version_label_tbl.EXISTS(l_count) THEN
5197:
5198: csi_item_instance_pvt.create_version_label
5199: ( p_api_version => p_api_version
5200: ,p_commit => p_commit
5201: ,p_init_msg_list => p_init_msg_list
5202: ,p_validation_level => p_validation_level
5364: FOR l_count IN p_version_label_tbl.FIRST..p_version_label_tbl.LAST
5365: LOOP
5366: IF p_version_label_tbl.EXISTS(l_count) THEN
5367:
5368: csi_item_instance_pvt.update_version_label
5369: ( p_api_version => p_api_version
5370: ,p_commit => p_commit
5371: ,p_init_msg_list => p_init_msg_list
5372: ,p_validation_level => p_validation_level
5527: FOR l_count IN p_version_label_tbl.FIRST..p_version_label_tbl.LAST
5528: LOOP
5529: IF p_version_label_tbl.EXISTS(l_count) THEN
5530:
5531: csi_item_instance_pvt.expire_version_label
5532: ( p_api_version => p_api_version
5533: ,p_commit => p_commit
5534: ,p_init_msg_list => p_init_msg_list
5535: ,p_validation_level => p_validation_level
5857: END IF;
5858:
5859:
5860: -- Generate the where clause
5861: csi_item_instance_pvt.Gen_ext_Where_Clause
5862: ( p_ext_query_rec => p_ext_attribs_query_rec,
5863: x_where_clause => l_where_clause );
5864:
5865: -- Build the select statement
5899: --Parse the select statement
5900: dbms_sql.parse(l_cur_get_ext, l_select_stmt , dbms_sql.native);
5901:
5902: -- Bind the variables
5903: csi_item_instance_pvt.Bind_ext_variable
5904: ( p_ext_attribs_query_rec,
5905: l_cur_get_ext );
5906:
5907: -- Define output variables
5904: ( p_ext_attribs_query_rec,
5905: l_cur_get_ext );
5906:
5907: -- Define output variables
5908: csi_item_instance_pvt.Define_ext_Columns(l_cur_get_ext);
5909:
5910: -- execute the select statement
5911: l_rows_processed := dbms_sql.execute(l_cur_get_ext);
5912:
5912:
5913:
5914: LOOP
5915: EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cur_get_ext) = 0;
5916: csi_item_instance_pvt.Get_ext_Column_Values(l_cur_get_ext, l_ext_rec);
5917: l_count := l_count + 1;
5918: x_ext_attrib_tbl(l_count) := l_ext_rec;
5919: END LOOP;
5920:
5922: DBMS_SQL.CLOSE_CURSOR(l_cur_get_ext);
5923:
5924: IF (p_time_stamp IS NOT NULL) AND (p_time_stamp <> FND_API.G_MISS_DATE) THEN
5925: IF p_time_stamp <= sysdate THEN
5926: csi_item_instance_pvt.Construct_ext_From_Hist(x_ext_attrib_tbl, p_time_stamp);
5927: ELSE
5928: FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_HIST_PARAMS');
5929: FND_MSG_PUB.ADD;
5930: RAISE FND_API.G_EXC_ERROR;
6063: l_debug_level NUMBER;
6064: l_msg_index NUMBER;
6065: l_msg_count NUMBER;
6066: l_trace_enable_flag VARCHAR2(1) :='N';
6067: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6068: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6069: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6070: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6071: BEGIN
6064: l_msg_index NUMBER;
6065: l_msg_count NUMBER;
6066: l_trace_enable_flag VARCHAR2(1) :='N';
6067: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6068: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6069: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6070: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6071: BEGIN
6072:
6065: l_msg_count NUMBER;
6066: l_trace_enable_flag VARCHAR2(1) :='N';
6067: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6068: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6069: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6070: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6071: BEGIN
6072:
6073: -- Standard Start of API savepoint
6066: l_trace_enable_flag VARCHAR2(1) :='N';
6067: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6068: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6069: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6070: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6071: BEGIN
6072:
6073: -- Standard Start of API savepoint
6074: SAVEPOINT create_extended_attrib_values;
6133: IF p_ext_attrib_tbl.COUNT > 0 THEN
6134: FOR tab_row IN p_ext_attrib_tbl.FIRST .. p_ext_attrib_tbl.LAST
6135: LOOP
6136: IF p_ext_attrib_tbl.EXISTS(tab_row) THEN
6137: csi_item_instance_pvt.create_extended_attrib_values
6138: ( p_api_version => p_api_version
6139: ,p_commit => fnd_api.g_false
6140: ,p_init_msg_list => p_init_msg_list
6141: ,p_validation_level => p_validation_level
6260: l_debug_level NUMBER;
6261: l_msg_index NUMBER;
6262: l_msg_count NUMBER;
6263: l_trace_enable_flag VARCHAR2(1) :='N';
6264: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6265: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6266: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6267: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6268: BEGIN
6261: l_msg_index NUMBER;
6262: l_msg_count NUMBER;
6263: l_trace_enable_flag VARCHAR2(1) :='N';
6264: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6265: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6266: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6267: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6268: BEGIN
6269:
6262: l_msg_count NUMBER;
6263: l_trace_enable_flag VARCHAR2(1) :='N';
6264: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6265: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6266: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6267: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6268: BEGIN
6269:
6270: -- Standard Start of API savepoint
6263: l_trace_enable_flag VARCHAR2(1) :='N';
6264: l_ext_id_tbl csi_item_instance_pvt.ext_id_tbl;
6265: l_ext_count_rec csi_item_instance_pvt.ext_count_rec;
6266: l_ext_attr_tbl csi_item_instance_pvt.ext_attr_tbl;
6267: l_ext_cat_tbl csi_item_instance_pvt.ext_cat_tbl;
6268: BEGIN
6269:
6270: -- Standard Start of API savepoint
6271: SAVEPOINT update_extended_attrib_values;
6327: IF p_ext_attrib_tbl.COUNT > 0 THEN
6328: FOR tab_row IN p_ext_attrib_tbl.FIRST .. p_ext_attrib_tbl.LAST
6329: LOOP
6330: IF p_ext_attrib_tbl.EXISTS(tab_row) THEN
6331: csi_item_instance_pvt.update_extended_attrib_values
6332: (p_api_version => p_api_version
6333: ,p_commit => fnd_api.g_false
6334: ,p_init_msg_list => p_init_msg_list
6335: ,p_validation_level => p_validation_level
6517: FOR tab_row IN p_ext_attrib_tbl.FIRST .. p_ext_attrib_tbl.LAST
6518: LOOP
6519: IF p_ext_attrib_tbl.EXISTS(tab_row) THEN
6520:
6521: csi_item_instance_pvt.expire_extended_attrib_values
6522: ( p_api_version => p_api_version
6523: ,p_commit => fnd_api.g_false
6524: ,p_init_msg_list => p_init_msg_list
6525: ,p_validation_level => p_validation_level
6712: END IF;
6713:
6714: -- Start API body
6715:
6716: csi_item_instance_pvt.copy_item_instance
6717: ( p_api_version => p_api_version
6718: ,p_commit => fnd_api.g_false
6719: ,p_init_msg_list => p_init_msg_list
6720: ,p_validation_level => p_validation_level