[Home] [Help]
1: PACKAGE BODY csi_ii_relationships_pvt AS
2: /* $Header: csiviirb.pls 120.29.12020000.4 2012/09/13 11:20:02 sjawaji ship $ */
3: -- start of comments
4: -- package name : csi_ii_relationships_pvt
5: -- purpose :
1: PACKAGE BODY csi_ii_relationships_pvt AS
2: /* $Header: csiviirb.pls 120.29.12020000.4 2012/09/13 11:20:02 sjawaji ship $ */
3: -- start of comments
4: -- package name : csi_ii_relationships_pvt
5: -- purpose :
6: -- history :
7: -- note :
8: -- END of comments
7: -- note :
8: -- END of comments
9:
10:
11: g_pkg_name CONSTANT VARCHAR2(30) := 'csi_ii_relationships_pvt';
12: g_file_name CONSTANT VARCHAR2(12) := 'csiviirb.pls';
13: p_rel_glbl_tbl csi_datastructures_pub.ii_relationship_tbl;
14: p_glbl_ctr NUMBER := 0;
15:
12: g_file_name CONSTANT VARCHAR2(12) := 'csiviirb.pls';
13: p_rel_glbl_tbl csi_datastructures_pub.ii_relationship_tbl;
14: p_glbl_ctr NUMBER := 0;
15:
16: p_ii_rel_level_glbl_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl; --bug 10321217
17:
18:
19: /* Cyclic Relationships */
20:
296: attribute13,
297: attribute14,
298: attribute15,
299: object_version_number
300: FROM csi_ii_relationships
301: WHERE (subject_id = p_instance_id OR object_id = p_instance_id )
302: AND relationship_type_code = 'CONNECTED-TO'
303: AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
304: AND DECODE(p_active_relationship_only,FND_API.G_TRUE,NVL((active_end_date),c_sysdate+1),c_sysdate+1) > NVL((l_time_stamp),c_sysdate) ;
404: CURSOR neighbor_cur (p_instance_id IN NUMBER,
405: c_sysdate IN DATE )
406: IS
407: SELECT subject_id instance_id
408: FROM csi_ii_relationships
409: WHERE (subject_id = p_instance_id OR object_id = p_instance_id)
410: AND relationship_type_code = 'CONNECTED-TO'
411: AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
412: AND DECODE(p_active_relationship_only,FND_API.G_TRUE,NVL((active_end_date),c_sysdate+1),c_sysdate+1) > NVL((l_time_stamp),c_sysdate)
411: AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
412: AND DECODE(p_active_relationship_only,FND_API.G_TRUE,NVL((active_end_date),c_sysdate+1),c_sysdate+1) > NVL((l_time_stamp),c_sysdate)
413: UNION ALL
414: SELECT object_id instance_id
415: FROM csi_ii_relationships
416: WHERE (subject_id = p_instance_id OR object_id = p_instance_id)
417: AND relationship_type_code = 'CONNECTED-TO'
418: AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
419: AND DECODE(p_active_relationship_only,FND_API.G_TRUE,NVL((active_end_date),c_sysdate+1),c_sysdate+1) > NVL((l_time_stamp),c_sysdate) ;
630: l_table_name VARCHAR2(30);
631: BEGIN
632:
633: x_select_cl := 'SELECT relationship_id,relationship_type_code,object_id,subject_id'
634: ||',nvl((select '||'''Y'''|| ' from csi_ii_relationships where object_id = ciir.subject_id and relationship_type_code = ciir.relationship_type_code and rownum=1), '||'''N'''||') subject_has_child,'
635: ||'position_reference,active_start_date,active_end_date,display_order,mandatory_flag,context,attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,'
636: ||'attribute8,attribute9,attribute10,attribute11,attribute12,attribute13,attribute14'
637: ||',attribute15,object_version_number FROM csi_ii_relationships ciir';
638:
633: x_select_cl := 'SELECT relationship_id,relationship_type_code,object_id,subject_id'
634: ||',nvl((select '||'''Y'''|| ' from csi_ii_relationships where object_id = ciir.subject_id and relationship_type_code = ciir.relationship_type_code and rownum=1), '||'''N'''||') subject_has_child,'
635: ||'position_reference,active_start_date,active_end_date,display_order,mandatory_flag,context,attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,'
636: ||'attribute8,attribute9,attribute10,attribute11,attribute12,attribute13,attribute14'
637: ||',attribute15,object_version_number FROM csi_ii_relationships ciir';
638:
639:
640: END gen_select;
641:
752:
753: END gen_relations_where;
754:
755: /*--------------------------------------------------------------------------------------------------------------+
756: | <-- csi_ii_relationships_h --> |
757: |full_dump_flag |
758: | y y y y y y |
759: | c |---------------------------------------------------------------------------- c |
760: | a | | | | | | | 11-may-01 a |
781: BEGIN
782:
783: SELECT max(min(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss')))
784: INTO l_f_date
785: FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
786: WHERE b.relationship_id = c.relationship_id
787: AND c.transaction_id = a.transaction_id
788: AND c.full_dump_flag = 'Y'
789: AND a.transaction_date <= p_time_stamp
796: FROM_time_stamp:=l_f_date;
797: BEGIN
798: SELECT max(min(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss')))
799: INTO l_t_date
800: FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
801: WHERE b.relationship_id = c.relationship_id
802: AND c.transaction_id = a.transaction_id
803: AND a.transaction_date<= p_time_stamp
804: AND c.relationship_id = p_relationship_id
866: ,c.new_attribute14
867: ,c.old_attribute15
868: ,c.new_attribute15
869: ,c.full_dump_flag
870: FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
871: WHERE b.relationship_id = c.relationship_id
872: AND c.transaction_id = a.transaction_id
873: AND c.relationship_id = p_relationship_id
874: -- AND a.transaction_date BETWEEN fnd_date.canonical_to_date(p_f_time_stamp) --to_date(p_f_time_stamp,'dd-mon-rr hh24:mi:ss')
887: to_time_stamp => l_t_time_stamp);
888:
889: SELECT max(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss'))
890: INTO l_to_date
891: FROM csi_transactions a,csi_ii_relationships_h b
892: WHERE a.transaction_id=b.transaction_id
893: AND b.relationship_id=p_rel_rec.relationship_id;
894:
895: -- IF ( (l_f_time_stamp IS NOT NULL) AND (p_time_stamp>fnd_date.canonical_to_date(l_to_date) ) )-- to_date(l_to_date,'dd-mon-rr hh24:mi:ss')) ) THEN
1148: active_start_date,active_end_date,display_order,mandatory_flag,context,
1149: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1150: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1151: object_version_number
1152: from CSI_II_RELATIONSHIPS cir
1153: where cir.object_id = p_object_id;
1154: --
1155: l_ctr NUMBER := 0;
1156: BEGIN
1291: active_start_date,active_end_date,display_order,mandatory_flag,context,
1292: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1293: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1294: object_version_number
1295: from CSI_II_RELATIONSHIPS cir
1296: where cir.object_id = p_object_id
1297: and cir.relationship_type_code = p_rel_type_code
1298: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1299: and EXISTS (select 'x'
1306: active_start_date,active_end_date,display_order,mandatory_flag,context,
1307: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1308: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1309: object_version_number
1310: from CSI_II_RELATIONSHIPS cir
1311: where cir.object_id = p_object_id
1312: and cir.relationship_type_code = p_rel_type_code
1313: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1314: and EXISTS (select 'x'
1324: active_start_date,active_end_date,display_order,mandatory_flag,context,
1325: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1326: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1327: object_version_number
1328: from CSI_II_RELATIONSHIPS cir
1329: where cir.object_id = p_object_id
1330: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1331: and EXISTS (select 'x'
1332: from CSI_ITEM_INSTANCES csi
1338: active_start_date,active_end_date,display_order,mandatory_flag,context,
1339: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1340: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1341: object_version_number
1342: from CSI_II_RELATIONSHIPS cir
1343: where cir.subject_id = p_subject_id
1344: and cir.relationship_type_code = p_rel_type_code
1345: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1346: and EXISTS (select 'x'
1353: active_start_date,active_end_date,display_order,mandatory_flag,context,
1354: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1355: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1356: object_version_number
1357: from CSI_II_RELATIONSHIPS cir
1358: where cir.relationship_id = p_relationship_id
1359: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1360: and EXISTS (select 'x'
1361: from CSI_ITEM_INSTANCES csi
1367: active_start_date,active_end_date,display_order,mandatory_flag,context,
1368: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1369: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1370: object_version_number
1371: from CSI_II_RELATIONSHIPS cir
1372: where cir.relationship_id = NVL(p_relationship_id,cir.relationship_id)
1373: and cir.object_id = NVL(p_object_id,cir.object_id)
1374: and cir.relationship_type_code = NVL(p_rel_type_code,cir.relationship_type_code)
1375: and cir.subject_id = NVL(p_subject_id,cir.subject_id)
1434: --
1435: Begin
1436: select 'Y'
1437: into p_rel_tbl(l_ctr).subject_has_child
1438: from CSI_II_RELATIONSHIPS
1439: where object_id = rel.subject_id
1440: and relationship_type_code = rel.relationship_type_code
1441: and rownum = 1;
1442: Exception
1478: --
1479: Begin
1480: select 'Y'
1481: into p_rel_tbl(l_ctr).subject_has_child
1482: from CSI_II_RELATIONSHIPS
1483: where object_id = rel.subject_id
1484: and relationship_type_code = rel.relationship_type_code
1485: and rownum = 1;
1486: Exception
1528: --
1529: Begin
1530: select 'Y'
1531: into p_rel_tbl(l_ctr).subject_has_child
1532: from CSI_II_RELATIONSHIPS
1533: where object_id = rel.subject_id
1534: and relationship_type_code = rel.relationship_type_code
1535: and rownum = 1;
1536: Exception
1576: --
1577: Begin
1578: select 'Y'
1579: into p_rel_tbl(l_ctr).subject_has_child
1580: from CSI_II_RELATIONSHIPS
1581: where object_id = rel.subject_id
1582: and relationship_type_code = rel.relationship_type_code
1583: and rownum = 1;
1584: Exception
1624: --
1625: Begin
1626: select 'Y'
1627: into p_rel_tbl(l_ctr).subject_has_child
1628: from CSI_II_RELATIONSHIPS
1629: where object_id = rel.subject_id
1630: and relationship_type_code = rel.relationship_type_code
1631: and rownum = 1;
1632: Exception
1668: --
1669: Begin
1670: select 'Y'
1671: into p_rel_tbl(l_ctr).subject_has_child
1672: from CSI_II_RELATIONSHIPS
1673: where object_id = rel.subject_id
1674: and relationship_type_code = rel.relationship_type_code
1675: and rownum = 1;
1676: Exception
1765: p_active_instances_only IN VARCHAR2,
1766: p_config_only IN VARCHAR2, -- if true will retrieve instances with config keys
1767: p_time_stamp IN DATE,
1768: p_get_dfs IN VARCHAR2,
1769: p_ii_relationship_level_tbl OUT NOCOPY csi_ii_relationships_pvt.ii_relationship_level_tbl,
1770: x_return_status OUT NOCOPY VARCHAR2,
1771: x_msg_count OUT NOCOPY NUMBER,
1772: x_msg_data OUT NOCOPY VARCHAR2
1773: ) IS
2055: l_object_id := p_subject_id;
2056: Begin
2057: select object_id
2058: into l_object_id
2059: from CSI_II_RELATIONSHIPS
2060: where subject_id = p_subject_id
2061: and relationship_type_code = p_rel_type_code
2062: and ((active_end_date is null) or (active_end_date > sysdate));
2063: Exception
2093: LOOP
2094: Begin
2095: select object_id
2096: into l_object_id
2097: from CSI_II_RELATIONSHIPS
2098: where subject_id = l_subject_id
2099: and relationship_type_code = p_rel_type_code
2100: and ((active_end_date is null) or (active_end_date > sysdate));
2101: --
2145: x_msg_data OUT NOCOPY VARCHAR2
2146: )
2147: IS
2148: CURSOR config_csr (instance_id NUMBER) IS
2149: SELECT * FROM csi_ii_relationships
2150: WHERE object_id=instance_id;
2151:
2152: l_api_name CONSTANT VARCHAR2(30) := 'get_relationships';
2153: l_api_version CONSTANT NUMBER := 1.0;
2189: --
2190: /*
2191: CURSOR CHILD_CUR(p_instance_id IN NUMBER) IS
2192: SELECT subject_id
2193: from csi_ii_relationships
2194: connect by prior subject_id = object_id
2195: start with subject_id = p_instance_id;
2196: */
2197: -- Added for bug 2999353
2198: /*
2199: CURSOR expired_cur (p_object_id IN number) IS
2200: SELECT relationship_id
2201: ,active_end_date
2202: FROM csi_ii_relationships
2203: WHERE relationship_type_code='COMPONENT-OF'
2204: START WITH object_id = p_object_id
2205: CONNECT BY object_id = PRIOR subject_id;
2206: */
2213: l_found2 BOOLEAN;
2214: l_expire BOOLEAN;
2215: l_exp_count NUMBER:=0;
2216: l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
2217: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
2218:
2219: BEGIN
2220: -- standard start of api savepoint
2221: -- SAVEPOINT get_relationships_pvt;
2310: l_instance_id := p_relationship_query_rec.object_id;
2311: END IF;
2312: END IF;
2313:
2314: csi_ii_relationships_pvt.get_cyclic_relationships(
2315: p_api_version => p_api_version,
2316: p_commit => fnd_api.g_false,
2317: p_init_msg_list => p_init_msg_list,
2318: p_validation_level => p_validation_level,
2376: THEN
2377: BEGIN
2378: SELECT 'Y'
2379: INTO l_relationship_tbl(i).subject_has_child
2380: FROM CSI_II_RELATIONSHIPS
2381: WHERE OBJECT_ID = l_relationship_tbl(i).subject_id
2382: AND CREATION_DATE <= p_time_stamp
2383: AND (ACTIVE_END_DATE IS NULL OR ACTIVE_END_DATE >= p_time_stamp)
2384: AND ROWNUM < 2;
2643: THEN
2644: l_relationship_query_rec.object_id:=l_relationship_tbl(l_returned_rec_count).subject_id;
2645: l_relationship_query_rec.subject_id:=fnd_api.g_miss_num;
2646: csi_gen_utility_pvt.put_line('Into recurrsive call for get_relationships. ');
2647: csi_ii_relationships_pvt.get_relationships
2648: ( p_api_version => p_api_version
2649: ,p_commit => p_commit
2650: ,p_init_msg_list => p_init_msg_list
2651: ,p_validation_level => p_validation_level
2700: -- Do not add the current record by default
2701: BEGIN
2702: SELECT 'x'
2703: INTO l_found
2704: FROM csi_ii_relationships_h
2705: WHERE relationship_id=l_rel_tbl(p_time_csr).relationship_id;
2706: EXCEPTION
2707: WHEN NO_DATA_FOUND THEN
2708: l_relationship_tbl(l_returned_rec_count) :=l_rel_tbl(p_time_csr);
2718: THEN
2719: BEGIN
2720: SELECT 'Y'
2721: INTO l_relationship_tbl(l_returned_rec_count).subject_has_child
2722: FROM CSI_II_RELATIONSHIPS
2723: WHERE OBJECT_ID = l_relationship_tbl(l_returned_rec_count).subject_id
2724: AND CREATION_DATE <= p_time_stamp
2725: AND (ACTIVE_END_DATE IS NULL OR ACTIVE_END_DATE >= p_time_stamp)
2726: AND ROWNUM < 2;
3115: l_configurator_id NUMBER;
3116: BEGIN
3117: /* SELECT configurator_id
3118: INTO l_configurator_id
3119: FROM csi_ii_relationships
3120: WHERE relationship_id=p_relationship_id;
3121: */
3122: IF l_configurator_id is not null
3123: THEN
3155: --SELECT 'x'
3156: --INTO l_dummy
3157: SELECT relationship_type_code
3158: INTO l_relship_type_code
3159: FROM csi_ii_relationships
3160: WHERE (( subject_id=p_object_id AND object_id=p_subject_id)
3161: OR (subject_id=p_subject_id AND object_id=p_object_id)
3162: OR (subject_id=p_subject_id ))
3163: --AND relationship_type_code = p_relship_type_code
3179: --SELECT 'x'
3180: --INTO l_dummy
3181: SELECT relationship_type_code
3182: INTO l_relship_type_code
3183: FROM csi_ii_relationships
3184: WHERE (( subject_id=p_object_id AND object_id=p_subject_id)
3185: OR (subject_id=p_subject_id AND object_id=p_object_id)
3186: OR (subject_id=p_subject_id ))
3187: --AND relationship_type_code = p_relship_type_code
3223: IF p_mode='CREATE'
3224: THEN
3225: SELECT COUNT(*)
3226: INTO l_count
3227: FROM csi_ii_relationships
3228: WHERE (subject_id=p_instance_id OR object_id=p_instance_id)
3229: AND relationship_type_code = 'CONNECTED-TO'
3230: AND (active_end_date IS NULL OR active_end_date > SYSDATE);
3231: ELSIF p_mode='UPDATE'
3233: -- Code for update will check other than itself
3234: -- and during unexpiring of an expired relationship.
3235: SELECT COUNT(*)
3236: INTO l_count
3237: FROM csi_ii_relationships
3238: WHERE (subject_id=p_instance_id OR object_id=p_instance_id)
3239: AND relationship_type_code = 'CONNECTED-TO'
3240: AND relationship_id <> p_relationship_id
3241: AND (active_end_date IS NULL OR active_end_date > SYSDATE);
3307:
3308: --SELECT 'x'
3309: SELECT relationship_type_code
3310: INTO l_relship_type_code
3311: FROM csi_ii_relationships
3312: WHERE subject_id=p_subject_id
3313: --AND relationship_type_code = p_relship_type_code
3314: AND (active_end_date IS NULL OR active_end_date > SYSDATE)
3315: --AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE))
3334:
3335: --SELECT 'x'
3336: SELECT relationship_type_code
3337: INTO l_relship_type_code
3338: FROM csi_ii_relationships
3339: WHERE subject_id=p_subject_id
3340: --AND relationship_type_code = p_relship_type_code
3341: AND relationship_id <> p_relationship_id
3342: AND (active_end_date IS NULL OR active_end_date > SYSDATE)
3375: RETURN TRUE;
3376: ELSE
3377: SELECT subject_id,relationship_type_code
3378: INTO l_subject_id,l_relship_type_code
3379: FROM csi_ii_relationships
3380: WHERE relationship_id=p_relationship_id
3381: AND subject_id =p_subject_id;
3382:
3383: IF SQL%FOUND THEN
3411: l_dummy VARCHAR2(1) :=NULL;
3412: BEGIN
3413: SELECT object_id,relationship_type_code
3414: INTO l_object_id,l_relship_type_code
3415: FROM csi_ii_relationships
3416: WHERE relationship_id=p_relationship_id;
3417:
3418: IF SQL%FOUND THEN
3419: IF ( (l_object_id<>p_object_id) OR (l_relship_type_code<>p_relship_type_code) ) THEN
3451: )
3452: IS
3453: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
3454: p_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
3455: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
3456: /* CURSOR chk_obj_csr IS
3457: SELECT 'x'
3458: FROM csi_ii_relationships
3459: WHERE subject_id = p_object_id
3454: p_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
3455: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
3456: /* CURSOR chk_obj_csr IS
3457: SELECT 'x'
3458: FROM csi_ii_relationships
3459: WHERE subject_id = p_object_id
3460: AND relationship_type_code = p_relationship_type_code
3461: START WITH object_id = p_subject_id
3462: CONNECT BY object_id = PRIOR subject_id;
3477: p_relationship_query_rec.object_id := p_subject_id; -- To check for Loop
3478: p_relationship_query_rec.relationship_type_code := p_relationship_type_code;
3479: --
3480: IF p_subject_id <> p_object_id THEN
3481: csi_ii_relationships_pvt.Get_Children
3482: ( p_relationship_query_rec => p_relationship_query_rec,
3483: p_rel_tbl => l_rel_tbl,
3484: p_depth => NULL,
3485: p_active_relationship_only => FND_API.G_TRUE,
3572: ,old_attribute15
3573: ,new_attribute15
3574: ,full_dump_flag
3575: ,object_version_number
3576: FROM csi_ii_relationships_h
3577: WHERE csi_ii_relationships_h.relationship_history_id = p_rel_hist_id
3578: FOR UPDATE OF object_version_number;
3579:
3580: l_rel_hist_csr rel_hist_csr%ROWTYPE;
3573: ,new_attribute15
3574: ,full_dump_flag
3575: ,object_version_number
3576: FROM csi_ii_relationships_h
3577: WHERE csi_ii_relationships_h.relationship_history_id = p_rel_hist_id
3578: FOR UPDATE OF object_version_number;
3579:
3580: l_rel_hist_csr rel_hist_csr%ROWTYPE;
3581: l_rel_hist_id NUMBER;
3603: -- Start of modifications for Bug#2547034 on 09/20/02 - rtalluri
3604: BEGIN
3605: SELECT relationship_history_id
3606: INTO l_rel_hist_id
3607: FROM csi_ii_relationships_h h
3608: WHERE h.transaction_id = p_transaction_id
3609: AND h.relationship_id = p_old_relship_rec.relationship_id;
3610:
3611: OPEN rel_hist_csr(l_rel_hist_id);
3613: CLOSE rel_hist_csr;
3614:
3615: IF l_rel_hist_csr.full_dump_flag = 'Y'
3616: THEN
3617: csi_ii_relationships_h_pkg.update_row(
3618: p_relationship_history_id => l_rel_hist_id ,
3619: p_relationship_id => fnd_api.g_miss_num ,
3620: p_transaction_id => fnd_api.g_miss_num ,
3621: p_old_subject_id => fnd_api.g_miss_num ,
3984: l_rel_hist_csr.new_attribute15 := l_new_relship_rec.attribute15;
3985: END IF;
3986: --
3987:
3988: csi_ii_relationships_h_pkg.update_row(
3989: p_relationship_history_id => l_rel_hist_id ,
3990: p_relationship_id => fnd_api.g_miss_num ,
3991: p_transaction_id => fnd_api.g_miss_num ,
3992: p_old_subject_id => l_rel_hist_csr.old_subject_id ,
4043: EXCEPTION
4044: WHEN NO_DATA_FOUND THEN
4045: IF MOD(l_old_relship_rec.object_version_number+1,l_full_dump)=0 THEN
4046:
4047: csi_ii_relationships_h_pkg.insert_row(
4048: px_relationship_history_id => l_relship_hist_rec.relationship_history_id,
4049: p_relationship_id => l_old_relship_rec.relationship_id,
4050: p_transaction_id => l_transaction_id,
4051: p_old_subject_id => l_old_relship_rec.subject_id,
4101: ELSE
4102:
4103: IF (l_new_relship_rec.subject_id = fnd_api.g_miss_num) OR
4104: NVL(l_old_relship_rec.subject_id,fnd_api.g_miss_num) = NVL(l_new_relship_rec.subject_id,fnd_api.g_miss_num) THEN
4105: --Modified code for bug 8516781, FP bug 8551918 old and new Subject_ID are always populated in CSI_II_RELATIONSHIPS_H such that
4106: --the parent's history always shows the subject ID which modified
4107: l_relship_hist_rec.old_subject_id := l_old_relship_rec.subject_id;
4108: l_relship_hist_rec.new_subject_id := l_old_relship_rec.subject_id;
4109: ELSIF
4316: THEN
4317: l_relship_hist_rec.new_active_end_date := p_sysdate;
4318: END IF;
4319:
4320: csi_ii_relationships_h_pkg.insert_row(
4321: px_relationship_history_id => l_relship_hist_rec.relationship_history_id,
4322: p_relationship_id => l_old_relship_rec.relationship_id,
4323: p_transaction_id => l_transaction_id,
4324: p_old_subject_id => l_relship_hist_rec.old_subject_id,
4403: IS
4404:
4405: /* CURSOR completeness_csr (p_subject_id IN NUMBER) IS
4406: SELECT object_id
4407: FROM csi_ii_relationships
4408: START WITH subject_id = p_subject_id
4409: CONNECT BY subject_id = PRIOR object_id; */
4410:
4411: l_ii_relationship_rec csi_datastructures_pub.ii_relationship_rec;
4451: l_ii_relationship_rec.subject_id = FND_API.G_MISS_NUM THEN
4452: Begin
4453: select subject_id
4454: into l_ii_relationship_rec.subject_id
4455: from CSI_II_RELATIONSHIPS
4456: where relationship_id = l_ii_relationship_rec.relationship_id;
4457: End;
4458: END IF;
4459: --
4461: l_ii_relationship_rec.object_id = FND_API.G_MISS_NUM THEN
4462: Begin
4463: select object_id
4464: into l_ii_relationship_rec.object_id
4465: from CSI_II_RELATIONSHIPS
4466: where relationship_id = l_ii_relationship_rec.relationship_id;
4467: End;
4468: END IF;
4469: END IF;
4584:
4585: --To handle Case 1
4586: BEGIN
4587: --FOR update_instance_csr IN completeness_csr(l_ii_relationship_rec.subject_id)
4588: csi_ii_relationships_pvt.Get_Immediate_Parents
4589: ( p_subject_id => l_ii_relationship_rec.subject_id,
4590: p_rel_type_code => 'COMPONENT-OF',
4591: p_rel_tbl => l_rel_tbl
4592: );
4595: l_subject_id:=NULL;
4596: BEGIN
4597: SELECT subject_id
4598: INTO l_subject_id
4599: FROM csi_ii_relationships
4600: WHERE object_id = l_rel_tbl(j).object_id
4601: AND mandatory_flag = 'Y'
4602: AND relationship_type_code='COMPONENT-OF'
4603: AND ROWNUM=1;
4611: SELECT COUNT(*)
4612: INTO l_dummy
4613: FROM csi_item_instances
4614: WHERE instance_id IN ( SELECT subject_id
4615: FROM csi_ii_relationships
4616: WHERE object_id = l_rel_tbl(j).object_id
4617: AND mandatory_flag = 'Y'
4618: AND relationship_type_code='COMPONENT-OF'
4619: )
4967: END IF;
4968:
4969: IF ((x_return_status=fnd_api.g_ret_sts_success) AND
4970: (p_relationship_tbl(l_count).object_id IS NOT NULL) ) THEN
4971: csi_ii_relationships_pvt.check_for_object
4972: (p_subject_id =>p_relationship_tbl(l_count).subject_id,
4973: p_object_id =>p_relationship_tbl(l_count).object_id,
4974: p_relationship_type_code =>p_relationship_tbl(l_count).relationship_type_code,
4975: x_return_status =>x_return_status,
4995: x_return_status:=fnd_api.g_ret_sts_error;
4996: RAISE fnd_api.g_exc_error;
4997: END IF;
4998: -- Start of att enhancements by sguthiva
4999: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => p_relationship_tbl(l_count).object_id )
5000: THEN
5001: IF csi_ii_relationships_pvt.relationship_for_link
5002: ( p_instance_id => p_relationship_tbl(l_count).object_id
5003: ,p_mode => 'CREATE'
4997: END IF;
4998: -- Start of att enhancements by sguthiva
4999: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => p_relationship_tbl(l_count).object_id )
5000: THEN
5001: IF csi_ii_relationships_pvt.relationship_for_link
5002: ( p_instance_id => p_relationship_tbl(l_count).object_id
5003: ,p_mode => 'CREATE'
5004: ,p_relationship_id => NULL )
5005: THEN
5009: RAISE fnd_api.g_exc_error;
5010: END IF;
5011: END IF;
5012:
5013: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => p_relationship_tbl(l_count).subject_id )
5014: THEN
5015: IF csi_ii_relationships_pvt.relationship_for_link
5016: ( p_instance_id => p_relationship_tbl(l_count).subject_id
5017: ,p_mode => 'CREATE'
5011: END IF;
5012:
5013: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => p_relationship_tbl(l_count).subject_id )
5014: THEN
5015: IF csi_ii_relationships_pvt.relationship_for_link
5016: ( p_instance_id => p_relationship_tbl(l_count).subject_id
5017: ,p_mode => 'CREATE'
5018: ,p_relationship_id => NULL )
5019: THEN
5055: LOOP
5056: x_msg_data := FND_MSG_PUB.GET
5057: ( l_msg_index,
5058: FND_API.G_FALSE );
5059: csi_gen_utility_pvt.put_line( ' Error from CSI_II_RELATIONSHIPS_PVT.CREATE_RELATIONSHIP');
5060: csi_gen_utility_pvt.put_line( ' Call to update_instance has errored ....');
5061: csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
5062: l_msg_index := l_msg_index + 1;
5063: l_msg_count := l_msg_count - 1;
5068:
5069:
5070:
5071: -- Added by sk on 9-Apr-02 for bug 2304221
5072: -- invoke table handler(csi_ii_relationships_pkg.insert_row)
5073: csi_ii_relationships_pkg.insert_row(
5074: px_relationship_id => p_relationship_tbl(l_count).relationship_id,
5075: p_relationship_type_code => p_relationship_tbl(l_count).relationship_type_code,
5076: p_object_id => p_relationship_tbl(l_count).object_id,
5069:
5070:
5071: -- Added by sk on 9-Apr-02 for bug 2304221
5072: -- invoke table handler(csi_ii_relationships_pkg.insert_row)
5073: csi_ii_relationships_pkg.insert_row(
5074: px_relationship_id => p_relationship_tbl(l_count).relationship_id,
5075: p_relationship_type_code => p_relationship_tbl(l_count).relationship_type_code,
5076: p_object_id => p_relationship_tbl(l_count).object_id,
5077: p_subject_id => p_relationship_tbl(l_count).subject_id,
5152: IF x_return_status = fnd_api.g_ret_sts_success THEN
5153:
5154: l_relship_history_id := NULL;
5155:
5156: csi_ii_relationships_h_pkg.insert_row(
5157: px_relationship_history_id => l_relship_history_id,
5158: p_relationship_id => p_relationship_tbl(l_count).relationship_id,
5159: p_transaction_id => p_txn_rec.transaction_id,
5160: p_old_subject_id => NULL,
5373: attribute13,
5374: attribute14,
5375: attribute15,
5376: object_version_number
5377: FROM csi_ii_relationships
5378: WHERE relationship_id=relship_id
5379: FOR UPDATE OF object_version_number ;
5380:
5381:
5411: l_exists VARCHAR2(1);
5412: -- Added for cascade ownership change bug 2972082
5413: l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
5414: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
5415: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
5416: l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
5417: l_party_tbl csi_datastructures_pub.party_tbl;
5418: l_account_tbl csi_datastructures_pub.party_account_tbl;
5419: l_temp_party_tbl csi_datastructures_pub.party_tbl;
5545: l_subject_lock:=NULL;
5546: BEGIN
5547: SELECT subject_id
5548: INTO l_subject_lock
5549: FROM csi_ii_relationships
5550: WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
5551: IF csi_item_instance_pvt.check_item_instance_lock
5552: ( p_instance_id => l_subject_lock)
5553: THEN
5621: -- check whether the Inverse Active Relationship exists
5622: Begin
5623: select 'x'
5624: into l_exists
5625: from csi_ii_relationships
5626: where object_id = decode(p_relationship_tbl(l_count).subject_id,fnd_api.g_miss_num,l_relship_csr.subject_id,
5627: p_relationship_tbl(l_count).subject_id)
5628: and subject_id = decode(p_relationship_tbl(l_count).object_id,fnd_api.g_miss_num,l_relship_csr.object_id,
5629: p_relationship_tbl(l_count).object_id)
5651: l_obj_id:=NULL;
5652: BEGIN
5653: SELECT object_id
5654: INTO l_obj_id
5655: FROM csi_ii_relationships
5656: WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
5657: EXCEPTION
5658: WHEN OTHERS THEN
5659: NULL;
5668: l_sub_id:=NULL;
5669: BEGIN
5670: SELECT subject_id
5671: INTO l_sub_id
5672: FROM csi_ii_relationships
5673: WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
5674: EXCEPTION
5675: WHEN OTHERS THEN
5676: NULL;
5678: ELSE
5679: l_sub_id := p_relationship_tbl(l_count).subject_id;
5680: END IF;
5681: --
5682: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => l_obj_id )
5683: THEN
5684: IF csi_ii_relationships_pvt.relationship_for_link
5685: ( p_instance_id => l_obj_id
5686: ,p_mode => 'UPDATE'
5680: END IF;
5681: --
5682: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => l_obj_id )
5683: THEN
5684: IF csi_ii_relationships_pvt.relationship_for_link
5685: ( p_instance_id => l_obj_id
5686: ,p_mode => 'UPDATE'
5687: ,p_relationship_id => p_relationship_tbl(l_count).relationship_id )
5688: THEN
5692: RAISE fnd_api.g_exc_error;
5693: END IF;
5694: END IF;
5695: --
5696: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => l_sub_id )
5697: THEN
5698: IF csi_ii_relationships_pvt.relationship_for_link
5699: ( p_instance_id => l_sub_id
5700: ,p_mode => 'UPDATE'
5694: END IF;
5695: --
5696: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => l_sub_id )
5697: THEN
5698: IF csi_ii_relationships_pvt.relationship_for_link
5699: ( p_instance_id => l_sub_id
5700: ,p_mode => 'UPDATE'
5701: ,p_relationship_id => p_relationship_tbl(l_count).relationship_id )
5702: THEN
5729: )
5730: THEN
5731: l_object_id := NULL;
5732: l_found:=FALSE;
5733: csi_ii_relationships_pvt.Get_Top_Most_Parent
5734: ( p_subject_id => l_relship_csr.subject_id,
5735: p_rel_type_code => 'COMPONENT-OF',
5736: p_object_id => l_object_id
5737: );
5845: LOOP
5846: x_msg_data := FND_MSG_PUB.GET
5847: ( l_msg_index,
5848: FND_API.G_FALSE );
5849: csi_gen_utility_pvt.put_line( ' Error from CSI_II_RELATIONSHIPS_PVT.UPDATE_RELATIONSHIP');
5850: csi_gen_utility_pvt.put_line( ' Call to update_instance has errored ....');
5851: csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
5852: l_msg_index := l_msg_index + 1;
5853: l_msg_count := l_msg_count - 1;
5855: RAISE FND_API.G_EXC_ERROR;
5856: END IF;
5857: END IF;
5858: --
5859: csi_ii_relationships_pkg.update_row(
5860: p_relationship_id => p_relationship_tbl(l_count).relationship_id,
5861: p_relationship_type_code => p_relationship_tbl(l_count).relationship_type_code,
5862: p_object_id => p_relationship_tbl(l_count).object_id,
5863: p_subject_id => p_relationship_tbl(l_count).subject_id,
5893: END IF;
5894:
5895: /*
5896: ELSE
5897: csi_ii_relationships_pvt.expire_relationship
5898: (p_api_version => p_api_version,
5899: p_commit => fnd_api.g_false,
5900: p_init_msg_list => p_init_msg_list,
5901: p_validation_level => p_validation_level,
5917: l_relationship_tbl(1).subject_has_child := 'Y' ;
5918: l_relationship_tbl(1).active_start_date := SYSDATE ;
5919: l_relationship_tbl(1).object_version_number:=1;
5920:
5921: csi_ii_relationships_pvt.create_relationship(
5922: p_api_version => p_api_version,
5923: p_commit => fnd_api.g_false,
5924: p_init_msg_list => p_init_msg_list,
5925: p_validation_level => p_validation_level,
5976:
5977: l_is_a_child := 'X';
5978: BEGIN
5979: SELECT 'Y' INTO l_is_a_child
5980: FROM csi_ii_relationships WHERE subject_id = l_relship_csr.subject_id
5981: AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
5982: AND Nvl(active_end_date, SYSDATE +1 ) > SYSDATE;
5983: EXCEPTION
5984: WHEN no_data_found THEN
6094: ELSE
6095: BEGIN
6096: SELECT object_id
6097: INTO l_inst_rec.instance_id
6098: FROM csi_ii_relationships
6099: WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
6100: EXCEPTION
6101: WHEN OTHERS
6102: THEN
6100: EXCEPTION
6101: WHEN OTHERS
6102: THEN
6103: csi_gen_utility_pvt.put_line( 'Error from update relationship API.');
6104: csi_gen_utility_pvt.put_line( 'Object_id not found in csi_ii_relationships to cascade ownership');
6105: RAISE fnd_api.g_exc_error;
6106: END;
6107: END IF;
6108:
6254: attribute13,
6255: attribute14,
6256: attribute15,
6257: object_version_number
6258: FROM csi_ii_relationships
6259: WHERE relationship_id=relship_id
6260: FOR UPDATE OF object_version_number ;
6261:
6262: l_relship_csr relship_csr%ROWTYPE;
6361: THEN
6362: BEGIN
6363: l_object_id := NULL;
6364: l_found:=FALSE;
6365: csi_ii_relationships_pvt.Get_Top_Most_Parent
6366: ( p_subject_id => l_relship_csr.subject_id,
6367: p_rel_type_code => 'COMPONENT-OF',
6368: p_object_id => l_object_id
6369: );
6500: -- End addition for MACD lock functionality
6501:
6502:
6503:
6504: csi_ii_relationships_pkg.update_row(
6505: p_relationship_id => p_relationship_rec.relationship_id,
6506: p_relationship_type_code => fnd_api.g_miss_char,
6507: p_object_id => fnd_api.g_miss_num,
6508: p_subject_id => fnd_api.g_miss_num,
6728: THEN
6729: BEGIN
6730: SELECT 'x'
6731: INTO l_dummy
6732: FROM csi_ii_relationships
6733: WHERE relationship_id=p_relationship_id
6734: AND ROWNUM=1;
6735: fnd_message.set_name('CSI','CSI_INVALID_RELSHIPID');
6736: fnd_message.set_token('relationship_id',p_relationship_id);
6751: BEGIN
6752: /* Added the condition 'AND ACTIVE_END_DATE IS NULL' to avoid updating expired relationship */
6753: SELECT 'x'
6754: INTO l_dummy
6755: FROM csi_ii_relationships
6756: WHERE relationship_id=p_relationship_id;
6757: -- AND ( ACTIVE_END_DATE IS NULL
6758: -- OR ACTIVE_END_DATE >SYSDATE)
6759: -- AND ROWNUM=1;
7516: cih.object_version_number ,
7517: cir.relationship_type_code ,
7518: cir.object_id ,
7519: cih.creation_date
7520: FROM csi_ii_relationships_h cih ,
7521: csi_ii_relationships cir
7522: WHERE cih.transaction_id = i_transaction_id
7523: AND cih.relationship_id = cir.relationship_id;
7524:
7517: cir.relationship_type_code ,
7518: cir.object_id ,
7519: cih.creation_date
7520: FROM csi_ii_relationships_h cih ,
7521: csi_ii_relationships cir
7522: WHERE cih.transaction_id = i_transaction_id
7523: AND cih.relationship_id = cir.relationship_id;
7524:
7525: BEGIN
7665: i := i + 1;
7666: END LOOP;
7667: -- srramakr moved outside the loop
7668: -- Resolve the id columns
7669: csi_ii_relationships_pvt.Resolve_id_columns(x_rel_history_tbl);
7670:
7671: -- End of API body
7672:
7673: -- Standard check of p_commit.
7756: l_adj_node NUMBER;
7757: l_rel_tbl_final csi_datastructures_pub.ii_relationship_tbl;
7758: l_api_name CONSTANT VARCHAR2(50) := 'get_cyclic_node';
7759: --
7760: l_rel_color_tbl csi_ii_relationships_pvt.REL_COLOR_TBL;
7761: l_rel_color_ctr NUMBER := 0;
7762: --
7763: COMP_ERROR EXCEPTION;
7764: BEGIN
7770: IF (l_debug_level > 0) THEN
7771: CSI_gen_utility_pvt.put_line( 'Get_Cyclic_Node');
7772: END IF;
7773: --
7774: csi_ii_relationships_pvt.get_cyclic_relationships(
7775: p_api_version => 1.0,
7776: p_commit => fnd_api.g_false,
7777: p_init_msg_list => fnd_api.g_true,
7778: p_validation_level => fnd_api.g_valid_level_full,
7814: -- hitting the cyclic node.
7815: --
7816: -- There is no concept of Parent-Child in a graph structure. All we do in a DFS is, given a
7817: -- vertex, we try to get its adjacent node and walk thru' the graph.
7818: -- Since in CSI_II_RELATIONSHIPS table, they are identified as Object_id and Subject_id
7819: -- we pick up the adjacent node in whichever form it appears (object or subject).
7820: --
7821: -- The following LOOP does this.
7822: --
7979:
7980: Begin
7981: select object_id
7982: into l_object_id
7983: from CSI_II_RELATIONSHIPS
7984: where subject_id = p_subject_id
7985: and relationship_type_code = p_rel_type_code
7986: and ((active_end_date is null) or (active_end_date > sysdate));
7987: Exception
7998: return l_root_object_id;
7999: END Get_Root_Parent;
8000: -- End Add Code for Siebel Genesis Project
8001:
8002: END csi_ii_relationships_pvt;