[Home] [Help]
3275: ' csi_ip_accounts_h c where a.instance_party_id = b.instance_party_id '||
3276: ' and b.ip_account_id = c.ip_account_id and c.transaction_id = :p_transaction_id UNION '||
3277: ' SELECT b.instance_id FROM csi_iea_values_h a, csi_iea_values b '||
3278: ' WHERE a.attribute_value_id = b.attribute_value_id AND a.transaction_id = :p_transaction_id '||
3279: ' UNION SELECT b.subject_id FROM csi_ii_relationships_h a, csi_ii_relationships b '||
3280: ' WHERE a.relationship_id = b.relationship_id AND a.transaction_id = :p_transaction_id '||
3281: ' UNION SELECT a.instance_id FROM csi_i_org_assignments a, csi_i_org_assignments_h b '||
3282: ' WHERE a.instance_ou_id = b.instance_ou_id and b.transaction_id = :p_transaction_id '||
3283: ' UNION SELECT a.instance_id FROM csi_i_pricing_attribs a , csi_i_pricing_attribs_h b '||
7463: l_last_vld_organization_id NUMBER;
7464: l_child_temp VARCHAR2(1);
7465: p_rel_query_rec csi_datastructures_pub.relationship_query_rec;
7466: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
7467: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
7468: l_vld_org_id NUMBER;
7469: PROCESS_NEXT EXCEPTION;
7470: l_instance_status_id NUMBER;
7471: l_system_id NUMBER;
7566: IF p_rel_query_rec.object_id IS NOT NULL AND
7567: p_rel_query_rec.object_id <> FND_API.G_MISS_NUM THEN
7568: IF p_unexpire = FND_API.G_TRUE THEN
7569: csi_gen_utility_pvt.put_line ( 'Calling Get_Children for Un-expiry...');
7570: csi_ii_relationships_pvt.Get_Children
7571: ( p_relationship_query_rec => p_rel_query_rec,
7572: p_rel_tbl => l_rel_tbl,
7573: p_depth => NULL,
7574: p_active_relationship_only => FND_API.G_TRUE,
7587: RAISE FND_API.G_EXC_ERROR;
7588: END IF;
7589: ELSE
7590: csi_gen_utility_pvt.put_line ( 'Calling Get_Children for Regular Inheritance...');
7591: csi_ii_relationships_pvt.Get_Children
7592: ( p_relationship_query_rec => p_rel_query_rec,
7593: p_rel_tbl => l_rel_tbl,
7594: p_depth => NULL,
7595: p_active_relationship_only => FND_API.G_TRUE,
7928:
7929: --Added call to update instance event for bug 7699277
7930: --As the integration design requires a create event to create the asset
7931: --and an update event to create the relationship hierarchy
7932: l_root_asset_id := csi_ii_relationships_pvt.Get_Root_Parent(l_rel_tbl(j).subject_id,
7933: l_rel_tbl(j).relationship_type_code,
7934: l_rel_tbl(j).object_id);
7935: csi_gen_utility_pvt.put_line('l_rel_tbl(' || j || ').subject_id : ' || l_rel_tbl(j).subject_id);
7936: csi_gen_utility_pvt.put_line('l_rel_tbl(' || j || ').relationship_type_code : ' || l_rel_tbl(j).relationship_type_code);
10995: AND INSTANCE_ID=p_ins_id AND TRANSACTION_ID<>p_txn_id
10996: UNION ALL
10997:
10998: SELECT CIRH.TRANSACTION_ID TRANSACTION_ID, CIR.SUBJECT_ID INSTANCE_ID
10999: FROM CSI_II_RELATIONSHIPS_H CIRH, CSI_II_RELATIONSHIPS CIR
11000: WHERE CIRH.RELATIONSHIP_ID = CIR.RELATIONSHIP_ID
11001: AND subject_ID=p_ins_id AND TRANSACTION_ID<>p_txn_id
11002: UNION ALL
11003:
11001: AND subject_ID=p_ins_id AND TRANSACTION_ID<>p_txn_id
11002: UNION ALL
11003:
11004: SELECT CIRH.TRANSACTION_ID TRANSACTION_ID, CIR.OBJECT_ID INSTANCE_ID
11005: FROM CSI_II_RELATIONSHIPS_H CIRH, CSI_II_RELATIONSHIPS CIR
11006: WHERE CIRH.RELATIONSHIP_ID = CIR.RELATIONSHIP_ID
11007: AND object_ID=p_ins_id AND TRANSACTION_ID<>p_txn_id
11008: UNION ALL
11009:
12154:
12155: -- Added cursor for bug 8516781, FP bug 8551918
12156: CURSOR relationship_csr (p_subject_id IN NUMBER) IS
12157: SELECT relationship_id,object_version_number
12158: FROM csi_ii_relationships
12159: WHERE (subject_id = p_subject_id
12160: or object_id = p_subject_id) -- added bug 8661671
12161: AND (active_end_date IS NULL OR active_end_date > SYSDATE); --Bug 9207673 FP Bug 9287115
12162:
14050:
14051: -- Start of bug fix 2151750 by sk on 02/04/02
14052:
14053: -- If location_type_code has been changing from something to INVENTORY
14054: -- then check if this record has been existing as a subject in csi_ii_relationships
14055: -- of COMPONENT-OF relationship_type_code and if exists then if
14056: -- l_get_instance_rec.instance_usage_code is not equal to
14057: -- IN_RELATIONSHIP then raise an error.
14058:
14062: l_temp_rec := null;
14063: BEGIN
14064: SELECT 'x'
14065: INTO l_temp_rec
14066: FROM csi_ii_relationships
14067: WHERE relationship_type_code = 'COMPONENT-OF'
14068: AND subject_id = p_instance_rec.instance_id
14069: AND (active_end_date IS NULL OR active_end_date > SYSDATE);
14070: EXCEPTION
14099: l_temp_rec1 := NULL;
14100: BEGIN
14101: SELECT 'x'
14102: INTO l_temp_rec1
14103: FROM csi_ii_relationships
14104: WHERE relationship_type_code = 'COMPONENT-OF'
14105: AND subject_id = p_instance_rec.instance_id
14106: AND (active_end_date IS NULL OR active_end_date > SYSDATE);
14107:
18968: RAISE fnd_api.g_exc_error;
18969: END IF;
18970: END IF;
18971: -- Calling eam API to log instance update event
18972: -- Check for any child instances in the csi ii relationships table by passing the parent instance id. If there exists
18973: -- any child instances then inherit the changed attributes with the changes in parent.
18974:
18975: IF l_curr_instance_rec.active_end_date IS NULL
18976: THEN l_curr_instance_rec.active_end_date:=FND_API.G_MISS_DATE;
19206: l_exp_rltns_rec.relationship_id := l_inst_relationship_rec.relationship_id;
19207: l_exp_rltns_rec.object_version_number := l_inst_relationship_rec.object_version_number;
19208:
19209: csi_t_gen_utility_pvt.dump_api_info(
19210: p_pkg_name => 'csi_ii_relationships_pub',
19211: p_api_name => 'expire_relationship');
19212:
19213: csi_ii_relationships_pub.expire_relationship(
19214: p_api_version => 1.0,
19209: csi_t_gen_utility_pvt.dump_api_info(
19210: p_pkg_name => 'csi_ii_relationships_pub',
19211: p_api_name => 'expire_relationship');
19212:
19213: csi_ii_relationships_pub.expire_relationship(
19214: p_api_version => 1.0,
19215: p_commit => fnd_api.g_false,
19216: p_init_msg_list => fnd_api.g_false,
19217: p_validation_level => fnd_api.g_valid_level_full,
19352: l_mod_value NUMBER;
19353: l_dump_flag VARCHAR2(1);
19354: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
19355: p_rel_query_rec csi_datastructures_pub.relationship_query_rec;
19356: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
19357:
19358: CURSOR curr_instance_rec(p_instance_id IN NUMBER) IS
19359: SELECT
19360: INSTANCE_ID,
19533: p_rel_query_rec.relationship_type_code := 'COMPONENT-OF';
19534: --
19535: IF p_rel_query_rec.object_id IS NOT NULL AND
19536: p_rel_query_rec.object_id <> FND_API.G_MISS_NUM THEN
19537: csi_ii_relationships_pvt.Get_Children
19538: ( p_relationship_query_rec => p_rel_query_rec,
19539: p_rel_tbl => l_rel_tbl,
19540: p_depth => NULL,
19541: p_active_relationship_only => FND_API.G_TRUE,
20589:
20590: -- Added cursor for bug 6788183
20591: CURSOR relationship_csr (p_subject_id IN NUMBER) IS
20592: SELECT relationship_id,object_version_number
20593: FROM csi_ii_relationships
20594: WHERE (subject_id = p_subject_id
20595: or object_id = p_subject_id) -- added bug 8661671
20596: AND (active_end_date IS NULL OR active_end_date > SYSDATE); --Bug 9207673 FP Bug 9287115
20597:
20691: RAISE FND_API.G_EXC_ERROR;
20692: END IF;
20693: CLOSE get_instance_rec;
20694:
20695: -- checking for any unexpired children in csi_ii_relationships table
20696:
20697: -- Added by sk for bug 2151760
20698: IF (( p_instance_rec.active_end_date IS NOT NULL) AND (p_instance_rec.active_end_date < SYSDATE))
20699: AND p_instance_rec.active_end_date <> fnd_api.g_miss_date
20718: BEGIN
20719:
20720: SELECT 'Y'
20721: INTO l_parent_found
20722: FROM csi_ii_relationships
20723: WHERE (subject_id = p_instance_rec.instance_id
20724: OR object_id = p_instance_rec.instance_id)
20725: AND relationship_type_code = 'COMPONENT_OF'
20726: AND sysdate between nvl(active_start_date, sysdate -1)
20760: l_dummy NUMBER;
20761: l_has_unexpired_children BOOLEAN;
20762: p_rel_query_rec csi_datastructures_pub.relationship_query_rec;
20763: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
20764: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
20765: BEGIN
20766: p_rel_query_rec.object_id := p_instance_rec.instance_id;
20767: p_rel_query_rec.relationship_type_code := 'COMPONENT-OF';
20768: --
20767: p_rel_query_rec.relationship_type_code := 'COMPONENT-OF';
20768: --
20769: IF p_rel_query_rec.object_id IS NOT NULL AND
20770: p_rel_query_rec.object_id <> FND_API.G_MISS_NUM THEN
20771: csi_ii_relationships_pvt.Get_Children
20772: ( p_relationship_query_rec => p_rel_query_rec,
20773: p_rel_tbl => l_rel_tbl,
20774: p_depth => NULL,
20775: p_active_relationship_only => FND_API.G_TRUE,
20885: l_exp_rltns_rec.relationship_id := l_inst_relationship_rec.relationship_id;
20886: l_exp_rltns_rec.object_version_number := l_inst_relationship_rec.object_version_number;
20887:
20888: csi_t_gen_utility_pvt.dump_api_info(
20889: p_pkg_name => 'csi_ii_relationships_pub',
20890: p_api_name => 'expire_relationship');
20891:
20892: csi_ii_relationships_pub.expire_relationship(
20893: p_api_version => 1.0,
20888: csi_t_gen_utility_pvt.dump_api_info(
20889: p_pkg_name => 'csi_ii_relationships_pub',
20890: p_api_name => 'expire_relationship');
20891:
20892: csi_ii_relationships_pub.expire_relationship(
20893: p_api_version => 1.0,
20894: p_commit => fnd_api.g_false,
20895: p_init_msg_list => fnd_api.g_false,
20896: p_validation_level => fnd_api.g_valid_level_full,
22256: ,l_relationship_tbl(1).attribute12
22257: ,l_relationship_tbl(1).attribute13
22258: ,l_relationship_tbl(1).attribute14
22259: ,l_relationship_tbl(1).attribute15
22260: FROM csi_ii_relationships
22261: WHERE subject_id=p_source_instance_rec.instance_id
22262: AND relationship_type_code='COMPONENT-OF'
22263: AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
22264: EXCEPTION
22270: IF l_relationship_tbl(1).object_id IS NOT NULL
22271: AND l_relationship_tbl(1).object_id<>fnd_api.g_miss_num
22272: THEN
22273: l_relationship_tbl(1).subject_id:=l_new_instance_tbl(1).instance_id;
22274: csi_ii_relationships_pub.create_relationship(
22275: p_api_version => 1.0,
22276: p_commit => fnd_api.g_false,
22277: p_init_msg_list => fnd_api.g_true,
22278: p_validation_level => fnd_api.g_valid_level_full,
22746: ,l_relationship_tbl(1).attribute12
22747: ,l_relationship_tbl(1).attribute13
22748: ,l_relationship_tbl(1).attribute14
22749: ,l_relationship_tbl(1).attribute15
22750: FROM csi_ii_relationships
22751: WHERE subject_id=p_source_instance_rec.instance_id
22752: AND relationship_type_code='COMPONENT-OF'
22753: AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
22754: EXCEPTION
22761: AND l_relationship_tbl(1).object_id<>fnd_api.g_miss_num
22762: THEN
22763: l_relationship_tbl(1).relationship_id:=fnd_api.g_miss_num;
22764: l_relationship_tbl(1).subject_id:=l_new_instance_tbl(1).instance_id;
22765: csi_ii_relationships_pub.create_relationship(
22766: p_api_version => 1.0,
22767: p_commit => fnd_api.g_false,
22768: p_init_msg_list => fnd_api.g_true,
22769: p_validation_level => fnd_api.g_valid_level_full,
28705: l_new_instance_rec.instance_usage_code := p_source_instance_rec.instance_usage_code;
28706: END IF;
28707: ELSE
28708: l_obj_id:=NULL;
28709: csi_ii_relationships_pvt.Get_Top_Most_Parent
28710: ( p_subject_id => p_source_instance_rec.instance_id,
28711: p_rel_type_code => 'COMPONENT-OF',
28712: p_object_id => l_obj_id
28713: );
30286: End Loop;
30287: --
30288: IF x_rel_tbl.count > 0 THEN
30289: csi_gen_utility_pvt.put_line('Calling II Relationship PUB...');
30290: csi_ii_relationships_pub.create_relationship
30291: (
30292: p_api_version => 1.0,
30293: p_commit => fnd_api.g_false,
30294: p_init_msg_list => fnd_api.g_true,
32777: l_version_label VARCHAR2(30);
32778: l_version_label_rec csi_datastructures_pub.version_label_rec;
32779: p_rel_query_rec csi_datastructures_pub.relationship_query_rec;
32780: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
32781: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
32782: l_obj_id NUMBER;
32783: --
32784: px_oks_txn_inst_tbl oks_ibint_pub.txn_instance_tbl;
32785: l_batch_type VARCHAR2(50);
33711: END IF;
33712:
33713: ELSE
33714: l_obj_id:=NULL;
33715: csi_ii_relationships_pvt.Get_Top_Most_Parent
33716: ( p_subject_id => p_source_instance_rec.instance_id,
33717: p_rel_type_code => 'COMPONENT-OF',
33718: p_object_id => l_obj_id
33719: );
34545: p_rel_query_rec.relationship_type_code := 'COMPONENT-OF';
34546: --
34547: IF p_rel_query_rec.object_id IS NOT NULL AND
34548: p_rel_query_rec.object_id <> FND_API.G_MISS_NUM THEN
34549: csi_ii_relationships_pvt.Get_Children
34550: ( p_relationship_query_rec => p_rel_query_rec,
34551: p_rel_tbl => l_rel_tbl,
34552: p_depth => NULL,
34553: p_active_relationship_only => FND_API.G_TRUE,
34656:
34657: -- Create the new configuration
34658: IF l_new_ii_relationship_tbl.count > 0
34659: THEN
34660: csi_ii_relationships_pvt.create_relationship
34661: ( p_api_version => p_api_version
34662: ,p_commit => p_commit
34663: ,p_init_msg_list => p_init_msg_list
34664: ,p_validation_level => p_validation_level
35460: l_root_node NUMBER;
35461: l_config_tbl csi_cz_int.config_tbl := px_config_tbl;
35462: l_count NUMBER;
35463: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
35464: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
35465: l_rel_query_rec csi_datastructures_pub.relationship_query_rec;
35466: l_lock_tbl CSI_ITEM_INSTANCE_PVT.lock_instances_tbl; --csi_cz_int.config_tbl;
35467: l_root NUMBER:=0;
35468: l_found VARCHAR2(1):='N';
35643:
35644: IF l_found='N'
35645: THEN
35646:
35647: csi_ii_relationships_pvt.get_top_most_parent
35648: ( p_subject_id => px_config_tbl(i).instance_id,
35649: p_rel_type_code => l_rel_type_code,
35650: p_object_id => l_root_node
35651: );
35653:
35654: l_rel_query_rec.object_id := l_root_node;
35655: l_rel_query_rec.relationship_type_code := l_rel_type_code;
35656:
35657: csi_ii_relationships_pvt.get_children
35658: ( p_relationship_query_rec => l_rel_query_rec,
35659: p_rel_tbl => l_rel_tbl,
35660: p_depth => NULL,
35661: p_active_relationship_only => FND_API.G_TRUE,
36234: l_api_name CONSTANT VARCHAR2(30) := 'UNLOCK_ITEM_INSTANCES';
36235: l_api_version CONSTANT NUMBER := 1.0;
36236: l_csi_debug_level NUMBER;
36237: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
36238: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
36239: l_rel_query_rec csi_datastructures_pub.relationship_query_rec;
36240: l_rel_type_code VARCHAR2(30) := 'COMPONENT-OF';
36241: l_unlock_tbl csi_item_instance_pvt.lock_instances_tbl;
36242: l_unlock_ins_tbl csi_item_instance_pvt.lock_instances_tbl;