[Home] [Help]
1: PACKAGE BODY csi_ii_relationships_pvt AS
2: /* $Header: csiviirb.pls 120.12 2008/03/08 01:08:26 fli 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.12 2008/03/08 01:08:26 fli 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:
14: /* Cyclic Relationships */
15:
289: attribute13,
290: attribute14,
291: attribute15,
292: object_version_number
293: FROM csi_ii_relationships
294: WHERE (subject_id = p_instance_id OR object_id = p_instance_id )
295: AND relationship_type_code = 'CONNECTED-TO'
296: AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
297: 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) ;
397: CURSOR neighbor_cur (p_instance_id IN NUMBER,
398: c_sysdate IN DATE )
399: IS
400: SELECT subject_id instance_id
401: FROM csi_ii_relationships
402: WHERE (subject_id = p_instance_id OR object_id = p_instance_id)
403: AND relationship_type_code = 'CONNECTED-TO'
404: AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
405: 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: AND nvl(active_start_date,creation_date) <= NVL(l_time_stamp,nvl(active_start_date,creation_date))
405: 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)
406: UNION ALL
407: SELECT object_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) ;
623: l_table_name VARCHAR2(30);
624: BEGIN
625:
626: x_select_cl := 'SELECT relationship_id,relationship_type_code,object_id,subject_id'
627: ||',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,'
628: ||'position_reference,active_start_date,active_end_date,display_order,mandatory_flag,context,attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,'
629: ||'attribute8,attribute9,attribute10,attribute11,attribute12,attribute13,attribute14'
630: ||',attribute15,object_version_number FROM csi_ii_relationships ciir';
631:
626: x_select_cl := 'SELECT relationship_id,relationship_type_code,object_id,subject_id'
627: ||',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,'
628: ||'position_reference,active_start_date,active_end_date,display_order,mandatory_flag,context,attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,'
629: ||'attribute8,attribute9,attribute10,attribute11,attribute12,attribute13,attribute14'
630: ||',attribute15,object_version_number FROM csi_ii_relationships ciir';
631:
632:
633: END gen_select;
634:
745:
746: END gen_relations_where;
747:
748: /*--------------------------------------------------------------------------------------------------------------+
749: | <-- csi_ii_relationships_h --> |
750: |full_dump_flag |
751: | y y y y y y |
752: | c |---------------------------------------------------------------------------- c |
753: | a | | | | | | | 11-may-01 a |
774: BEGIN
775:
776: SELECT max(min(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss')))
777: INTO l_f_date
778: FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
779: WHERE b.relationship_id = c.relationship_id
780: AND c.transaction_id = a.transaction_id
781: AND c.full_dump_flag = 'Y'
782: AND a.transaction_date <= p_time_stamp
789: FROM_time_stamp:=l_f_date;
790: BEGIN
791: SELECT max(min(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss')))
792: INTO l_t_date
793: FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
794: WHERE b.relationship_id = c.relationship_id
795: AND c.transaction_id = a.transaction_id
796: AND a.transaction_date<= p_time_stamp
797: AND c.relationship_id = p_relationship_id
859: ,c.new_attribute14
860: ,c.old_attribute15
861: ,c.new_attribute15
862: ,c.full_dump_flag
863: FROM csi_transactions a,csi_ii_relationships b,csi_ii_relationships_h c
864: WHERE b.relationship_id = c.relationship_id
865: AND c.transaction_id = a.transaction_id
866: AND c.relationship_id = p_relationship_id
867: -- 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')
880: to_time_stamp => l_t_time_stamp);
881:
882: SELECT max(to_char(a.transaction_date,'dd-mon-rr hh24:mi:ss'))
883: INTO l_to_date
884: FROM csi_transactions a,csi_ii_relationships_h b
885: WHERE a.transaction_id=b.transaction_id
886: AND b.relationship_id=p_rel_rec.relationship_id;
887:
888: -- 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
1141: active_start_date,active_end_date,display_order,mandatory_flag,context,
1142: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1143: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1144: object_version_number
1145: from CSI_II_RELATIONSHIPS cir
1146: where cir.object_id = p_object_id;
1147: --
1148: l_ctr NUMBER := 0;
1149: BEGIN
1284: active_start_date,active_end_date,display_order,mandatory_flag,context,
1285: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1286: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1287: object_version_number
1288: from CSI_II_RELATIONSHIPS cir
1289: where cir.object_id = p_object_id
1290: and cir.relationship_type_code = p_rel_type_code
1291: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1292: and EXISTS (select 'x'
1299: active_start_date,active_end_date,display_order,mandatory_flag,context,
1300: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1301: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1302: object_version_number
1303: from CSI_II_RELATIONSHIPS cir
1304: where cir.object_id = p_object_id
1305: and cir.relationship_type_code = p_rel_type_code
1306: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1307: and EXISTS (select 'x'
1317: active_start_date,active_end_date,display_order,mandatory_flag,context,
1318: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1319: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1320: object_version_number
1321: from CSI_II_RELATIONSHIPS cir
1322: where cir.object_id = p_object_id
1323: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1324: and EXISTS (select 'x'
1325: from CSI_ITEM_INSTANCES csi
1331: active_start_date,active_end_date,display_order,mandatory_flag,context,
1332: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1333: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1334: object_version_number
1335: from CSI_II_RELATIONSHIPS cir
1336: where cir.subject_id = p_subject_id
1337: and cir.relationship_type_code = p_rel_type_code
1338: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1339: and EXISTS (select 'x'
1346: active_start_date,active_end_date,display_order,mandatory_flag,context,
1347: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1348: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1349: object_version_number
1350: from CSI_II_RELATIONSHIPS cir
1351: where cir.relationship_id = p_relationship_id
1352: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1353: and EXISTS (select 'x'
1354: from CSI_ITEM_INSTANCES csi
1360: active_start_date,active_end_date,display_order,mandatory_flag,context,
1361: attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,
1362: attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15,
1363: object_version_number
1364: from CSI_II_RELATIONSHIPS cir
1365: where cir.relationship_id = NVL(p_relationship_id,cir.relationship_id)
1366: and cir.object_id = NVL(p_object_id,cir.object_id)
1367: and cir.relationship_type_code = NVL(p_rel_type_code,cir.relationship_type_code)
1368: and cir.subject_id = NVL(p_subject_id,cir.subject_id)
1427: --
1428: Begin
1429: select 'Y'
1430: into p_rel_tbl(l_ctr).subject_has_child
1431: from CSI_II_RELATIONSHIPS
1432: where object_id = rel.subject_id
1433: and relationship_type_code = rel.relationship_type_code
1434: and rownum = 1;
1435: Exception
1471: --
1472: Begin
1473: select 'Y'
1474: into p_rel_tbl(l_ctr).subject_has_child
1475: from CSI_II_RELATIONSHIPS
1476: where object_id = rel.subject_id
1477: and relationship_type_code = rel.relationship_type_code
1478: and rownum = 1;
1479: Exception
1521: --
1522: Begin
1523: select 'Y'
1524: into p_rel_tbl(l_ctr).subject_has_child
1525: from CSI_II_RELATIONSHIPS
1526: where object_id = rel.subject_id
1527: and relationship_type_code = rel.relationship_type_code
1528: and rownum = 1;
1529: Exception
1569: --
1570: Begin
1571: select 'Y'
1572: into p_rel_tbl(l_ctr).subject_has_child
1573: from CSI_II_RELATIONSHIPS
1574: where object_id = rel.subject_id
1575: and relationship_type_code = rel.relationship_type_code
1576: and rownum = 1;
1577: Exception
1617: --
1618: Begin
1619: select 'Y'
1620: into p_rel_tbl(l_ctr).subject_has_child
1621: from CSI_II_RELATIONSHIPS
1622: where object_id = rel.subject_id
1623: and relationship_type_code = rel.relationship_type_code
1624: and rownum = 1;
1625: Exception
1661: --
1662: Begin
1663: select 'Y'
1664: into p_rel_tbl(l_ctr).subject_has_child
1665: from CSI_II_RELATIONSHIPS
1666: where object_id = rel.subject_id
1667: and relationship_type_code = rel.relationship_type_code
1668: and rownum = 1;
1669: Exception
1684: p_active_instances_only IN VARCHAR2,
1685: p_config_only IN VARCHAR2, -- if true will retrieve instances with config keys
1686: p_time_stamp IN DATE,
1687: p_get_dfs IN VARCHAR2,
1688: p_ii_relationship_level_tbl OUT NOCOPY csi_ii_relationships_pvt.ii_relationship_level_tbl,
1689: x_return_status OUT NOCOPY VARCHAR2,
1690: x_msg_count OUT NOCOPY NUMBER,
1691: x_msg_data OUT NOCOPY VARCHAR2
1692: ) IS
1930: l_object_id := p_subject_id;
1931: Begin
1932: select object_id
1933: into l_object_id
1934: from CSI_II_RELATIONSHIPS
1935: where subject_id = p_subject_id
1936: and relationship_type_code = p_rel_type_code
1937: and ((active_end_date is null) or (active_end_date > sysdate));
1938: Exception
1968: LOOP
1969: Begin
1970: select object_id
1971: into l_object_id
1972: from CSI_II_RELATIONSHIPS
1973: where subject_id = l_subject_id
1974: and relationship_type_code = p_rel_type_code
1975: and ((active_end_date is null) or (active_end_date > sysdate));
1976: --
2020: x_msg_data OUT NOCOPY VARCHAR2
2021: )
2022: IS
2023: CURSOR config_csr (instance_id NUMBER) IS
2024: SELECT * FROM csi_ii_relationships
2025: WHERE object_id=instance_id;
2026:
2027: l_api_name CONSTANT VARCHAR2(30) := 'get_relationships';
2028: l_api_version CONSTANT NUMBER := 1.0;
2064: --
2065: /*
2066: CURSOR CHILD_CUR(p_instance_id IN NUMBER) IS
2067: SELECT subject_id
2068: from csi_ii_relationships
2069: connect by prior subject_id = object_id
2070: start with subject_id = p_instance_id;
2071: */
2072: -- Added for bug 2999353
2073: /*
2074: CURSOR expired_cur (p_object_id IN number) IS
2075: SELECT relationship_id
2076: ,active_end_date
2077: FROM csi_ii_relationships
2078: WHERE relationship_type_code='COMPONENT-OF'
2079: START WITH object_id = p_object_id
2080: CONNECT BY object_id = PRIOR subject_id;
2081: */
2088: l_found2 BOOLEAN;
2089: l_expire BOOLEAN;
2090: l_exp_count NUMBER:=0;
2091: l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
2092: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
2093:
2094: BEGIN
2095: -- standard start of api savepoint
2096: -- SAVEPOINT get_relationships_pvt;
2185: l_instance_id := p_relationship_query_rec.object_id;
2186: END IF;
2187: END IF;
2188:
2189: csi_ii_relationships_pvt.get_cyclic_relationships(
2190: p_api_version => p_api_version,
2191: p_commit => fnd_api.g_false,
2192: p_init_msg_list => p_init_msg_list,
2193: p_validation_level => p_validation_level,
2251: THEN
2252: BEGIN
2253: SELECT 'Y'
2254: INTO l_relationship_tbl(i).subject_has_child
2255: FROM CSI_II_RELATIONSHIPS
2256: WHERE OBJECT_ID = l_relationship_tbl(i).subject_id
2257: AND CREATION_DATE <= p_time_stamp
2258: AND (ACTIVE_END_DATE IS NULL OR ACTIVE_END_DATE >= p_time_stamp)
2259: AND ROWNUM < 2;
2516: THEN
2517: l_relationship_query_rec.object_id:=l_relationship_tbl(l_returned_rec_count).subject_id;
2518: l_relationship_query_rec.subject_id:=fnd_api.g_miss_num;
2519: csi_gen_utility_pvt.put_line('Into recurrsive call for get_relationships. ');
2520: csi_ii_relationships_pvt.get_relationships
2521: ( p_api_version => p_api_version
2522: ,p_commit => p_commit
2523: ,p_init_msg_list => p_init_msg_list
2524: ,p_validation_level => p_validation_level
2573: -- Do not add the current record by default
2574: BEGIN
2575: SELECT 'x'
2576: INTO l_found
2577: FROM csi_ii_relationships_h
2578: WHERE relationship_id=l_rel_tbl(p_time_csr).relationship_id;
2579: EXCEPTION
2580: WHEN NO_DATA_FOUND THEN
2581: l_relationship_tbl(l_returned_rec_count) :=l_rel_tbl(p_time_csr);
2591: THEN
2592: BEGIN
2593: SELECT 'Y'
2594: INTO l_relationship_tbl(l_returned_rec_count).subject_has_child
2595: FROM CSI_II_RELATIONSHIPS
2596: WHERE OBJECT_ID = l_relationship_tbl(l_returned_rec_count).subject_id
2597: AND CREATION_DATE <= p_time_stamp
2598: AND (ACTIVE_END_DATE IS NULL OR ACTIVE_END_DATE >= p_time_stamp)
2599: AND ROWNUM < 2;
2984: l_configurator_id NUMBER;
2985: BEGIN
2986: /* SELECT configurator_id
2987: INTO l_configurator_id
2988: FROM csi_ii_relationships
2989: WHERE relationship_id=p_relationship_id;
2990: */
2991: IF l_configurator_id is not null
2992: THEN
3021: IF p_mode='CREATE'
3022: THEN
3023: SELECT 'x'
3024: INTO l_dummy
3025: FROM csi_ii_relationships
3026: WHERE (( subject_id=p_object_id AND object_id=p_subject_id)
3027: OR (subject_id=p_subject_id AND object_id=p_object_id))
3028: AND relationship_type_code = p_relship_type_code
3029: AND (active_end_date IS NULL OR active_end_date > SYSDATE)
3041: ELSIF p_mode='UPDATE'
3042: THEN
3043: SELECT 'x'
3044: INTO l_dummy
3045: FROM csi_ii_relationships
3046: WHERE (( subject_id=p_object_id AND object_id=p_subject_id)
3047: OR (subject_id=p_subject_id AND object_id=p_object_id))
3048: AND relationship_type_code = p_relship_type_code
3049: AND (active_end_date IS NULL OR active_end_date > SYSDATE)
3084: IF p_mode='CREATE'
3085: THEN
3086: SELECT COUNT(*)
3087: INTO l_count
3088: FROM csi_ii_relationships
3089: WHERE (subject_id=p_instance_id OR object_id=p_instance_id)
3090: AND relationship_type_code = 'CONNECTED-TO'
3091: AND (active_end_date IS NULL OR active_end_date > SYSDATE);
3092: ELSIF p_mode='UPDATE'
3094: -- Code for update will check other than itself
3095: -- and during unexpiring of an expired relationship.
3096: SELECT COUNT(*)
3097: INTO l_count
3098: FROM csi_ii_relationships
3099: WHERE (subject_id=p_instance_id OR object_id=p_instance_id)
3100: AND relationship_type_code = 'CONNECTED-TO'
3101: AND relationship_id <> p_relationship_id
3102: AND (active_end_date IS NULL OR active_end_date > SYSDATE);
3162: THEN
3163: BEGIN
3164: SELECT 'x'
3165: INTO l_dummy
3166: FROM csi_ii_relationships
3167: WHERE subject_id=p_subject_id
3168: AND relationship_type_code = p_relship_type_code
3169: AND (active_end_date IS NULL OR active_end_date > SYSDATE)
3170: --AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE))
3186:
3187: BEGIN
3188: SELECT 'x'
3189: INTO l_dummy
3190: FROM csi_ii_relationships
3191: WHERE subject_id=p_subject_id
3192: AND relationship_type_code = p_relship_type_code
3193: AND relationship_id <> p_relationship_id
3194: AND (active_end_date IS NULL OR active_end_date > SYSDATE)
3227: RETURN TRUE;
3228: ELSE
3229: SELECT subject_id,relationship_type_code
3230: INTO l_subject_id,l_relship_type_code
3231: FROM csi_ii_relationships
3232: WHERE relationship_id=p_relationship_id
3233: AND subject_id =p_subject_id;
3234:
3235: IF SQL%FOUND THEN
3263: l_dummy VARCHAR2(1) :=NULL;
3264: BEGIN
3265: SELECT object_id,relationship_type_code
3266: INTO l_object_id,l_relship_type_code
3267: FROM csi_ii_relationships
3268: WHERE relationship_id=p_relationship_id;
3269:
3270: IF SQL%FOUND THEN
3271: IF ( (l_object_id<>p_object_id) OR (l_relship_type_code<>p_relship_type_code) ) THEN
3303: )
3304: IS
3305: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
3306: p_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
3307: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
3308: /* CURSOR chk_obj_csr IS
3309: SELECT 'x'
3310: FROM csi_ii_relationships
3311: WHERE subject_id = p_object_id
3306: p_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
3307: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
3308: /* CURSOR chk_obj_csr IS
3309: SELECT 'x'
3310: FROM csi_ii_relationships
3311: WHERE subject_id = p_object_id
3312: AND relationship_type_code = p_relationship_type_code
3313: START WITH object_id = p_subject_id
3314: CONNECT BY object_id = PRIOR subject_id;
3329: p_relationship_query_rec.object_id := p_subject_id; -- To check for Loop
3330: p_relationship_query_rec.relationship_type_code := p_relationship_type_code;
3331: --
3332: IF p_subject_id <> p_object_id THEN
3333: csi_ii_relationships_pvt.Get_Children
3334: ( p_relationship_query_rec => p_relationship_query_rec,
3335: p_rel_tbl => l_rel_tbl,
3336: p_depth => NULL,
3337: p_active_relationship_only => FND_API.G_TRUE,
3424: ,old_attribute15
3425: ,new_attribute15
3426: ,full_dump_flag
3427: ,object_version_number
3428: FROM csi_ii_relationships_h
3429: WHERE csi_ii_relationships_h.relationship_history_id = p_rel_hist_id
3430: FOR UPDATE OF object_version_number;
3431:
3432: l_rel_hist_csr rel_hist_csr%ROWTYPE;
3425: ,new_attribute15
3426: ,full_dump_flag
3427: ,object_version_number
3428: FROM csi_ii_relationships_h
3429: WHERE csi_ii_relationships_h.relationship_history_id = p_rel_hist_id
3430: FOR UPDATE OF object_version_number;
3431:
3432: l_rel_hist_csr rel_hist_csr%ROWTYPE;
3433: l_rel_hist_id NUMBER;
3455: -- Start of modifications for Bug#2547034 on 09/20/02 - rtalluri
3456: BEGIN
3457: SELECT relationship_history_id
3458: INTO l_rel_hist_id
3459: FROM csi_ii_relationships_h h
3460: WHERE h.transaction_id = p_transaction_id
3461: AND h.relationship_id = p_old_relship_rec.relationship_id;
3462:
3463: OPEN rel_hist_csr(l_rel_hist_id);
3465: CLOSE rel_hist_csr;
3466:
3467: IF l_rel_hist_csr.full_dump_flag = 'Y'
3468: THEN
3469: csi_ii_relationships_h_pkg.update_row(
3470: p_relationship_history_id => l_rel_hist_id ,
3471: p_relationship_id => fnd_api.g_miss_num ,
3472: p_transaction_id => fnd_api.g_miss_num ,
3473: p_old_subject_id => fnd_api.g_miss_num ,
3836: l_rel_hist_csr.new_attribute15 := l_new_relship_rec.attribute15;
3837: END IF;
3838: --
3839:
3840: csi_ii_relationships_h_pkg.update_row(
3841: p_relationship_history_id => l_rel_hist_id ,
3842: p_relationship_id => fnd_api.g_miss_num ,
3843: p_transaction_id => fnd_api.g_miss_num ,
3844: p_old_subject_id => l_rel_hist_csr.old_subject_id ,
3895: EXCEPTION
3896: WHEN NO_DATA_FOUND THEN
3897: IF MOD(l_old_relship_rec.object_version_number+1,l_full_dump)=0 THEN
3898:
3899: csi_ii_relationships_h_pkg.insert_row(
3900: px_relationship_history_id => l_relship_hist_rec.relationship_history_id,
3901: p_relationship_id => l_old_relship_rec.relationship_id,
3902: p_transaction_id => l_transaction_id,
3903: p_old_subject_id => l_old_relship_rec.subject_id,
4166: THEN
4167: l_relship_hist_rec.new_active_end_date := p_sysdate;
4168: END IF;
4169:
4170: csi_ii_relationships_h_pkg.insert_row(
4171: px_relationship_history_id => l_relship_hist_rec.relationship_history_id,
4172: p_relationship_id => l_old_relship_rec.relationship_id,
4173: p_transaction_id => l_transaction_id,
4174: p_old_subject_id => l_relship_hist_rec.old_subject_id,
4253: IS
4254:
4255: /* CURSOR completeness_csr (p_subject_id IN NUMBER) IS
4256: SELECT object_id
4257: FROM csi_ii_relationships
4258: START WITH subject_id = p_subject_id
4259: CONNECT BY subject_id = PRIOR object_id; */
4260:
4261: l_ii_relationship_rec csi_datastructures_pub.ii_relationship_rec;
4301: l_ii_relationship_rec.subject_id = FND_API.G_MISS_NUM THEN
4302: Begin
4303: select subject_id
4304: into l_ii_relationship_rec.subject_id
4305: from CSI_II_RELATIONSHIPS
4306: where relationship_id = l_ii_relationship_rec.relationship_id;
4307: End;
4308: END IF;
4309: --
4311: l_ii_relationship_rec.object_id = FND_API.G_MISS_NUM THEN
4312: Begin
4313: select object_id
4314: into l_ii_relationship_rec.object_id
4315: from CSI_II_RELATIONSHIPS
4316: where relationship_id = l_ii_relationship_rec.relationship_id;
4317: End;
4318: END IF;
4319: END IF;
4424:
4425: --To handle Case 1
4426: BEGIN
4427: --FOR update_instance_csr IN completeness_csr(l_ii_relationship_rec.subject_id)
4428: csi_ii_relationships_pvt.Get_Immediate_Parents
4429: ( p_subject_id => l_ii_relationship_rec.subject_id,
4430: p_rel_type_code => 'COMPONENT-OF',
4431: p_rel_tbl => l_rel_tbl
4432: );
4435: l_subject_id:=NULL;
4436: BEGIN
4437: SELECT subject_id
4438: INTO l_subject_id
4439: FROM csi_ii_relationships
4440: WHERE object_id = l_rel_tbl(j).object_id
4441: AND mandatory_flag = 'Y'
4442: AND relationship_type_code='COMPONENT-OF'
4443: AND ROWNUM=1;
4451: SELECT COUNT(*)
4452: INTO l_dummy
4453: FROM csi_item_instances
4454: WHERE instance_id IN ( SELECT subject_id
4455: FROM csi_ii_relationships
4456: WHERE object_id = l_rel_tbl(j).object_id
4457: AND mandatory_flag = 'Y'
4458: AND relationship_type_code='COMPONENT-OF'
4459: )
4799: END IF;
4800:
4801: IF ((x_return_status=fnd_api.g_ret_sts_success) AND
4802: (p_relationship_tbl(l_count).object_id IS NOT NULL) ) THEN
4803: csi_ii_relationships_pvt.check_for_object
4804: (p_subject_id =>p_relationship_tbl(l_count).subject_id,
4805: p_object_id =>p_relationship_tbl(l_count).object_id,
4806: p_relationship_type_code =>p_relationship_tbl(l_count).relationship_type_code,
4807: x_return_status =>x_return_status,
4827: x_return_status:=fnd_api.g_ret_sts_error;
4828: RAISE fnd_api.g_exc_error;
4829: END IF;
4830: -- Start of att enhancements by sguthiva
4831: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => p_relationship_tbl(l_count).object_id )
4832: THEN
4833: IF csi_ii_relationships_pvt.relationship_for_link
4834: ( p_instance_id => p_relationship_tbl(l_count).object_id
4835: ,p_mode => 'CREATE'
4829: END IF;
4830: -- Start of att enhancements by sguthiva
4831: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => p_relationship_tbl(l_count).object_id )
4832: THEN
4833: IF csi_ii_relationships_pvt.relationship_for_link
4834: ( p_instance_id => p_relationship_tbl(l_count).object_id
4835: ,p_mode => 'CREATE'
4836: ,p_relationship_id => NULL )
4837: THEN
4841: RAISE fnd_api.g_exc_error;
4842: END IF;
4843: END IF;
4844:
4845: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => p_relationship_tbl(l_count).subject_id )
4846: THEN
4847: IF csi_ii_relationships_pvt.relationship_for_link
4848: ( p_instance_id => p_relationship_tbl(l_count).subject_id
4849: ,p_mode => 'CREATE'
4843: END IF;
4844:
4845: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => p_relationship_tbl(l_count).subject_id )
4846: THEN
4847: IF csi_ii_relationships_pvt.relationship_for_link
4848: ( p_instance_id => p_relationship_tbl(l_count).subject_id
4849: ,p_mode => 'CREATE'
4850: ,p_relationship_id => NULL )
4851: THEN
4887: LOOP
4888: x_msg_data := FND_MSG_PUB.GET
4889: ( l_msg_index,
4890: FND_API.G_FALSE );
4891: csi_gen_utility_pvt.put_line( ' Error from CSI_II_RELATIONSHIPS_PVT.CREATE_RELATIONSHIP');
4892: csi_gen_utility_pvt.put_line( ' Call to update_instance has errored ....');
4893: csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
4894: l_msg_index := l_msg_index + 1;
4895: l_msg_count := l_msg_count - 1;
4900:
4901:
4902:
4903: -- Added by sk on 9-Apr-02 for bug 2304221
4904: -- invoke table handler(csi_ii_relationships_pkg.insert_row)
4905: csi_ii_relationships_pkg.insert_row(
4906: px_relationship_id => p_relationship_tbl(l_count).relationship_id,
4907: p_relationship_type_code => p_relationship_tbl(l_count).relationship_type_code,
4908: p_object_id => p_relationship_tbl(l_count).object_id,
4901:
4902:
4903: -- Added by sk on 9-Apr-02 for bug 2304221
4904: -- invoke table handler(csi_ii_relationships_pkg.insert_row)
4905: csi_ii_relationships_pkg.insert_row(
4906: px_relationship_id => p_relationship_tbl(l_count).relationship_id,
4907: p_relationship_type_code => p_relationship_tbl(l_count).relationship_type_code,
4908: p_object_id => p_relationship_tbl(l_count).object_id,
4909: p_subject_id => p_relationship_tbl(l_count).subject_id,
4984: IF x_return_status = fnd_api.g_ret_sts_success THEN
4985:
4986: l_relship_history_id := NULL;
4987:
4988: csi_ii_relationships_h_pkg.insert_row(
4989: px_relationship_history_id => l_relship_history_id,
4990: p_relationship_id => p_relationship_tbl(l_count).relationship_id,
4991: p_transaction_id => p_txn_rec.transaction_id,
4992: p_old_subject_id => NULL,
5201: attribute13,
5202: attribute14,
5203: attribute15,
5204: object_version_number
5205: FROM csi_ii_relationships
5206: WHERE relationship_id=relship_id
5207: FOR UPDATE OF object_version_number ;
5208:
5209:
5239: l_exists VARCHAR2(1);
5240: -- Added for cascade ownership change bug 2972082
5241: l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
5242: l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
5243: l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
5244: l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
5245: l_party_tbl csi_datastructures_pub.party_tbl;
5246: l_account_tbl csi_datastructures_pub.party_account_tbl;
5247: l_temp_party_tbl csi_datastructures_pub.party_tbl;
5368: l_subject_lock:=NULL;
5369: BEGIN
5370: SELECT subject_id
5371: INTO l_subject_lock
5372: FROM csi_ii_relationships
5373: WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
5374: IF csi_item_instance_pvt.check_item_instance_lock
5375: ( p_instance_id => l_subject_lock)
5376: THEN
5444: -- check whether the Inverse Active Relationship exists
5445: Begin
5446: select 'x'
5447: into l_exists
5448: from csi_ii_relationships
5449: where object_id = decode(p_relationship_tbl(l_count).subject_id,fnd_api.g_miss_num,l_relship_csr.subject_id,
5450: p_relationship_tbl(l_count).subject_id)
5451: and subject_id = decode(p_relationship_tbl(l_count).object_id,fnd_api.g_miss_num,l_relship_csr.object_id,
5452: p_relationship_tbl(l_count).object_id)
5474: l_obj_id:=NULL;
5475: BEGIN
5476: SELECT object_id
5477: INTO l_obj_id
5478: FROM csi_ii_relationships
5479: WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
5480: EXCEPTION
5481: WHEN OTHERS THEN
5482: NULL;
5491: l_sub_id:=NULL;
5492: BEGIN
5493: SELECT subject_id
5494: INTO l_sub_id
5495: FROM csi_ii_relationships
5496: WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
5497: EXCEPTION
5498: WHEN OTHERS THEN
5499: NULL;
5501: ELSE
5502: l_sub_id := p_relationship_tbl(l_count).subject_id;
5503: END IF;
5504: --
5505: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => l_obj_id )
5506: THEN
5507: IF csi_ii_relationships_pvt.relationship_for_link
5508: ( p_instance_id => l_obj_id
5509: ,p_mode => 'UPDATE'
5503: END IF;
5504: --
5505: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => l_obj_id )
5506: THEN
5507: IF csi_ii_relationships_pvt.relationship_for_link
5508: ( p_instance_id => l_obj_id
5509: ,p_mode => 'UPDATE'
5510: ,p_relationship_id => p_relationship_tbl(l_count).relationship_id )
5511: THEN
5515: RAISE fnd_api.g_exc_error;
5516: END IF;
5517: END IF;
5518: --
5519: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => l_sub_id )
5520: THEN
5521: IF csi_ii_relationships_pvt.relationship_for_link
5522: ( p_instance_id => l_sub_id
5523: ,p_mode => 'UPDATE'
5517: END IF;
5518: --
5519: IF csi_ii_relationships_pvt.Is_link_type (p_instance_id => l_sub_id )
5520: THEN
5521: IF csi_ii_relationships_pvt.relationship_for_link
5522: ( p_instance_id => l_sub_id
5523: ,p_mode => 'UPDATE'
5524: ,p_relationship_id => p_relationship_tbl(l_count).relationship_id )
5525: THEN
5552: )
5553: THEN
5554: l_object_id := NULL;
5555: l_found:=FALSE;
5556: csi_ii_relationships_pvt.Get_Top_Most_Parent
5557: ( p_subject_id => l_relship_csr.subject_id,
5558: p_rel_type_code => 'COMPONENT-OF',
5559: p_object_id => l_object_id
5560: );
5668: LOOP
5669: x_msg_data := FND_MSG_PUB.GET
5670: ( l_msg_index,
5671: FND_API.G_FALSE );
5672: csi_gen_utility_pvt.put_line( ' Error from CSI_II_RELATIONSHIPS_PVT.UPDATE_RELATIONSHIP');
5673: csi_gen_utility_pvt.put_line( ' Call to update_instance has errored ....');
5674: csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
5675: l_msg_index := l_msg_index + 1;
5676: l_msg_count := l_msg_count - 1;
5678: RAISE FND_API.G_EXC_ERROR;
5679: END IF;
5680: END IF;
5681: --
5682: csi_ii_relationships_pkg.update_row(
5683: p_relationship_id => p_relationship_tbl(l_count).relationship_id,
5684: p_relationship_type_code => p_relationship_tbl(l_count).relationship_type_code,
5685: p_object_id => p_relationship_tbl(l_count).object_id,
5686: p_subject_id => p_relationship_tbl(l_count).subject_id,
5716: END IF;
5717:
5718: /*
5719: ELSE
5720: csi_ii_relationships_pvt.expire_relationship
5721: (p_api_version => p_api_version,
5722: p_commit => fnd_api.g_false,
5723: p_init_msg_list => p_init_msg_list,
5724: p_validation_level => p_validation_level,
5740: l_relationship_tbl(1).subject_has_child := 'Y' ;
5741: l_relationship_tbl(1).active_start_date := SYSDATE ;
5742: l_relationship_tbl(1).object_version_number:=1;
5743:
5744: csi_ii_relationships_pvt.create_relationship(
5745: p_api_version => p_api_version,
5746: p_commit => fnd_api.g_false,
5747: p_init_msg_list => p_init_msg_list,
5748: p_validation_level => p_validation_level,
5893: ELSE
5894: BEGIN
5895: SELECT object_id
5896: INTO l_inst_rec.instance_id
5897: FROM csi_ii_relationships
5898: WHERE relationship_id=p_relationship_tbl(l_count).relationship_id;
5899: EXCEPTION
5900: WHEN OTHERS
5901: THEN
5899: EXCEPTION
5900: WHEN OTHERS
5901: THEN
5902: csi_gen_utility_pvt.put_line( 'Error from update relationship API.');
5903: csi_gen_utility_pvt.put_line( 'Object_id not found in csi_ii_relationships to cascade ownership');
5904: RAISE fnd_api.g_exc_error;
5905: END;
5906: END IF;
5907:
6049: attribute13,
6050: attribute14,
6051: attribute15,
6052: object_version_number
6053: FROM csi_ii_relationships
6054: WHERE relationship_id=relship_id
6055: FOR UPDATE OF object_version_number ;
6056:
6057: l_relship_csr relship_csr%ROWTYPE;
6152: THEN
6153: BEGIN
6154: l_object_id := NULL;
6155: l_found:=FALSE;
6156: csi_ii_relationships_pvt.Get_Top_Most_Parent
6157: ( p_subject_id => l_relship_csr.subject_id,
6158: p_rel_type_code => 'COMPONENT-OF',
6159: p_object_id => l_object_id
6160: );
6251: -- End addition for MACD lock functionality
6252:
6253:
6254:
6255: csi_ii_relationships_pkg.update_row(
6256: p_relationship_id => p_relationship_rec.relationship_id,
6257: p_relationship_type_code => fnd_api.g_miss_char,
6258: p_object_id => fnd_api.g_miss_num,
6259: p_subject_id => fnd_api.g_miss_num,
6467: THEN
6468: BEGIN
6469: SELECT 'x'
6470: INTO l_dummy
6471: FROM csi_ii_relationships
6472: WHERE relationship_id=p_relationship_id
6473: AND ROWNUM=1;
6474: fnd_message.set_name('CSI','CSI_INVALID_RELSHIPID');
6475: fnd_message.set_token('relationship_id',p_relationship_id);
6490: BEGIN
6491: /* Added the condition 'AND ACTIVE_END_DATE IS NULL' to avoid updating expired relationship */
6492: SELECT 'x'
6493: INTO l_dummy
6494: FROM csi_ii_relationships
6495: WHERE relationship_id=p_relationship_id;
6496: -- AND ( ACTIVE_END_DATE IS NULL
6497: -- OR ACTIVE_END_DATE >SYSDATE)
6498: -- AND ROWNUM=1;
7249: cih.object_version_number ,
7250: cir.relationship_type_code ,
7251: cir.object_id ,
7252: cih.creation_date
7253: FROM csi_ii_relationships_h cih ,
7254: csi_ii_relationships cir
7255: WHERE cih.transaction_id = i_transaction_id
7256: AND cih.relationship_id = cir.relationship_id;
7257:
7250: cir.relationship_type_code ,
7251: cir.object_id ,
7252: cih.creation_date
7253: FROM csi_ii_relationships_h cih ,
7254: csi_ii_relationships cir
7255: WHERE cih.transaction_id = i_transaction_id
7256: AND cih.relationship_id = cir.relationship_id;
7257:
7258: BEGIN
7398: i := i + 1;
7399: END LOOP;
7400: -- srramakr moved outside the loop
7401: -- Resolve the id columns
7402: csi_ii_relationships_pvt.Resolve_id_columns(x_rel_history_tbl);
7403:
7404: -- End of API body
7405:
7406: -- Standard check of p_commit.
7485: l_adj_node NUMBER;
7486: l_rel_tbl_final csi_datastructures_pub.ii_relationship_tbl;
7487: l_api_name CONSTANT VARCHAR2(50) := 'get_cyclic_node';
7488: --
7489: l_rel_color_tbl csi_ii_relationships_pvt.REL_COLOR_TBL;
7490: l_rel_color_ctr NUMBER := 0;
7491: --
7492: COMP_ERROR EXCEPTION;
7493: BEGIN
7499: IF (l_debug_level > 0) THEN
7500: CSI_gen_utility_pvt.put_line( 'Get_Cyclic_Node');
7501: END IF;
7502: --
7503: csi_ii_relationships_pvt.get_cyclic_relationships(
7504: p_api_version => 1.0,
7505: p_commit => fnd_api.g_false,
7506: p_init_msg_list => fnd_api.g_true,
7507: p_validation_level => fnd_api.g_valid_level_full,
7543: -- hitting the cyclic node.
7544: --
7545: -- There is no concept of Parent-Child in a graph structure. All we do in a DFS is, given a
7546: -- vertex, we try to get its adjacent node and walk thru' the graph.
7547: -- Since in CSI_II_RELATIONSHIPS table, they are identified as Object_id and Subject_id
7548: -- we pick up the adjacent node in whichever form it appears (object or subject).
7549: --
7550: -- The following LOOP does this.
7551: --
7705:
7706: Begin
7707: select object_id
7708: into l_object_id
7709: from CSI_II_RELATIONSHIPS
7710: where subject_id = p_subject_id
7711: and relationship_type_code = p_rel_type_code
7712: and ((active_end_date is null) or (active_end_date > sysdate));
7713: Exception
7724: return l_root_object_id;
7725: END Get_Root_Parent;
7726: -- End Add Code for Siebel Genesis Project
7727:
7728: END csi_ii_relationships_pvt;