[Home] [Help]
1: package body pqh_generic_hierarchy_package
2: /* $Header: pqghrpkg.pkb 120.2 2006/02/13 15:49:17 nsanghal noship $ */
3: as
4: --
5: g_debug boolean := hr_utility.debug_enabled;
6: g_package varchar2(72) := 'pqh_generic_hierarchy_package';
7: --
8: --
9: -- ----------------------------------------------------------------------------
29: --
30: l_return varchar2(10);
31: --
32: BEGIN
33: g_debug := hr_utility.debug_enabled;
34: if g_debug then
35: l_proc := g_package||'chk_type_context';
36: hr_utility.set_location('Entering '||l_proc,10);
37: end if;
32: BEGIN
33: g_debug := hr_utility.debug_enabled;
34: if g_debug then
35: l_proc := g_package||'chk_type_context';
36: hr_utility.set_location('Entering '||l_proc,10);
37: end if;
38:
39: Open csr_type_context;
40: --
48: --
49: Close csr_type_context;
50: --
51: if g_debug then
52: hr_utility.set_location('Leaving '||l_proc,20);
53: end if;
54: --
55: return l_return;
56: --
83: --
84: l_return varchar2(10);
85: --
86: BEGIN
87: g_debug := hr_utility.debug_enabled;
88: if g_debug then
89: l_proc := g_package||'chk_child_node_type_exists';
90: hr_utility.set_location('Entering '||l_proc,10);
91: end if;
86: BEGIN
87: g_debug := hr_utility.debug_enabled;
88: if g_debug then
89: l_proc := g_package||'chk_child_node_type_exists';
90: hr_utility.set_location('Entering '||l_proc,10);
91: end if;
92:
93: Open csr_child_node_types;
94: --
102: --
103: Close csr_child_node_types;
104: --
105: if g_debug then
106: hr_utility.set_location('Leaving '||l_proc,20);
107: end if;
108: --
109: return l_return;
110: --
130: l_valid varchar2(1);
131: --
132: Begin
133: --
134: g_debug := hr_utility.debug_enabled;
135: if g_debug then
136: l_proc := g_package||'node_value_set_dyn_query';
137: hr_utility.set_location('Entering:'||l_proc, 10);
138: end if;
133: --
134: g_debug := hr_utility.debug_enabled;
135: if g_debug then
136: l_proc := g_package||'node_value_set_dyn_query';
137: hr_utility.set_location('Entering:'||l_proc, 10);
138: end if;
139: l_value_set_id := Get_Value_Set_Id(p_child_node_type, p_parent_node_id, p_hierarchy_type);
140: --
141: if l_value_set_id = -2 then
140: --
141: if l_value_set_id = -2 then
142: l_dyn_query := 'INVALID';
143: if g_debug then
144: hr_utility.set_location('Value Set is Invalid', 20);
145: end if;
146: elsif l_value_set_id = -1 then
147: l_dyn_query := 'NULL';
148: if g_debug then
145: end if;
146: elsif l_value_set_id = -1 then
147: l_dyn_query := 'NULL';
148: if g_debug then
149: hr_utility.set_location('No Validation', 30);
150: end if;
151: else
152: l_dyn_query := get_sql_from_vset_id(l_value_set_id);
153: if g_debug then
150: end if;
151: else
152: l_dyn_query := get_sql_from_vset_id(l_value_set_id);
153: if g_debug then
154: hr_utility.set_location('Value Set Query Available, Checking Validity', 40);
155: end if;
156: l_valid := is_valid_sql(l_dyn_query);
157: if (l_valid = 'N') then
158: l_dyn_query := 'INVALID';
156: l_valid := is_valid_sql(l_dyn_query);
157: if (l_valid = 'N') then
158: l_dyn_query := 'INVALID';
159: if g_debug then
160: hr_utility.set_location('Value Set Query is Invalid', 50);
161: end if;
162: end if;
163: end if;
164: --
162: end if;
163: end if;
164: --
165: if g_debug then
166: hr_utility.set_location('Leaving:'||l_proc, 50);
167: end if;
168: --
169: Return l_dyn_query;
170: End Node_Value_Set_Dyn_Query;
200: l_proc varchar2(72);
201: --
202: Begin
203: --
204: g_debug := hr_utility.debug_enabled;
205: if g_debug then
206: l_proc := g_package||'get_node_value';
207: hr_utility.set_location('Entering:'||l_proc, 10);
208: end if;
203: --
204: g_debug := hr_utility.debug_enabled;
205: if g_debug then
206: l_proc := g_package||'get_node_value';
207: hr_utility.set_location('Entering:'||l_proc, 10);
208: end if;
209: --
210: if p_parent_node_id is not null then
211: l_hierarchy_type := Get_Hierarchy_Type(p_parent_node_id);
212: elsif p_child_node_id is not null then
213: l_hierarchy_type := Get_Hierarchy_Type(p_child_node_id);
214: else
215: if g_debug then
216: hr_utility.set_location('Both parent and child node ids are null',20);
217: end if;
218: End if;
219:
220: l_parent_node_type := Get_Node_Type(p_parent_node_id);
233: p_node_type_id => l_hier_node_type_id);
234: if (l_Node_Value = 'NULL') then
235: l_Node_Value := p_entity_id;
236: if g_debug then
237: hr_utility.set_location('No validation, Value same as entity id', 30);
238: end if;
239: end if;
240: --
241: if g_debug then
238: end if;
239: end if;
240: --
241: if g_debug then
242: hr_utility.set_location('Leaving:'||l_proc,40);
243: end if;
244: --
245: Return l_Node_Value;
246: --
246: --
247: Exception
248: When others then
249: if g_debug then
250: hr_utility.set_location('Unexpected Error', 50);
251: end if;
252: raise;
253: End Get_Node_Value;
254: --
270: l_proc Varchar2(72);
271: --
272: Begin
273: --
274: g_debug := hr_utility.debug_enabled;
275: if g_debug then
276: l_proc := g_package||'get_node_type';
277: hr_utility.set_location('Entering:'||l_proc, 10);
278: end if;
273: --
274: g_debug := hr_utility.debug_enabled;
275: if g_debug then
276: l_proc := g_package||'get_node_type';
277: hr_utility.set_location('Entering:'||l_proc, 10);
278: end if;
279: --
280: Open csr_node_type;
281: Fetch csr_node_type into l_node_type;
281: Fetch csr_node_type into l_node_type;
282: Close csr_node_type;
283: --
284: if g_debug then
285: hr_utility.set_location('Leaving:'||l_proc, 20);
286: end if;
287: Return l_node_type;
288: End Get_Node_Type;
289: --
313: l_proc Varchar2(72);
314: --
315: Begin
316: --
317: g_debug := hr_utility.debug_enabled;
318: if g_debug then
319: l_proc := g_package||'get_hierarchy_type';
320: hr_utility.set_location('Entering:'||l_proc, 10);
321: end if;
316: --
317: g_debug := hr_utility.debug_enabled;
318: if g_debug then
319: l_proc := g_package||'get_hierarchy_type';
320: hr_utility.set_location('Entering:'||l_proc, 10);
321: end if;
322: --
323: Open csr_hierarchy_type;
324: Fetch csr_hierarchy_type into l_hierarchy_type;
324: Fetch csr_hierarchy_type into l_hierarchy_type;
325: Close csr_hierarchy_type;
326: --
327: if g_debug then
328: hr_utility.set_location('Leaving:'||l_proc, 20);
329: end if;
330:
331: Return l_hierarchy_type;
332: --
368: l_proc Varchar2(72);
369: --
370: Begin
371: --
372: g_debug := hr_utility.debug_enabled;
373: if g_debug then
374: l_proc := g_package||'get_value_set_id';
375: hr_utility.set_location('Entering:'||l_proc, 10);
376: end if;
371: --
372: g_debug := hr_utility.debug_enabled;
373: if g_debug then
374: l_proc := g_package||'get_value_set_id';
375: hr_utility.set_location('Entering:'||l_proc, 10);
376: end if;
377: l_parent_node_type := Get_Node_Type (p_parent_node_id);
378: --
379: Open csr_value_set_name(l_parent_node_type);
381: Close csr_value_set_name;
382:
383: if (upper(l_value_set_name) = 'NULL') then
384: if g_debug then
385: hr_utility.set_location('No Validation', 20);
386: end if;
387: return -1;
388: end if;
389:
390: Open csr_value_set_id(l_value_set_name);
391: Fetch csr_value_set_id into l_value_set_id;
392: If csr_value_set_id%NotFound then
393: If g_debug then
394: hr_utility.set_location('Invalid Value Set', 30);
395: end if;
396: l_value_set_id := -2;
397: End if;
398:
398:
399: Close csr_value_set_id;
400: --
401: if g_debug then
402: hr_utility.set_location('Leaving:'||l_proc, 40);
403: end if;
404: Return l_value_set_id;
405:
406: End Get_Value_Set_Id;
418: l_proc varchar2(72);
419: --
420: BEGIN
421: --
422: g_debug := hr_utility.debug_enabled;
423: if g_debug then
424: l_proc := g_package||'get_sql_from_vset_id';
425: hr_utility.set_location('Entering:'||l_proc, 10);
426: end if;
421: --
422: g_debug := hr_utility.debug_enabled;
423: if g_debug then
424: l_proc := g_package||'get_sql_from_vset_id';
425: hr_utility.set_location('Entering:'||l_proc, 10);
426: end if;
427: fnd_vset.get_valueset(valueset_id => p_vset_id ,
428: valueset => l_v_r,
429: format => l_v_dr);
428: valueset => l_v_r,
429: format => l_v_dr);
430: --
431: if g_debug then
432: hr_utility.set_location('Got Valueset', 15);
433: end if;
434: --
435: l_whr := l_v_r.table_info.where_clause ;
436: l_str := rtrim('select '||l_v_r.table_info.id_column_name ||' Entityid, '
441: l_str := REPLACE(l_str,':$PROFILES$.PER_BUSINESS_GROUP_ID',fnd_profile.value('
442: PER_BUSINESS_GROUP_ID'));
443: --
444: if g_debug then
445: hr_utility.set_location('Leaving:'||l_proc, 20);
446: end if;
447: --
448: RETURN (l_str);
449: --
467: Where Hierarchy_Type = p_hierarchy_type;
468:
469: Begin
470: --
471: g_debug := hr_utility.debug_enabled;
472: if g_debug then
473: l_proc := g_package||'chk_if_structure_exists';
474: hr_utility.set_location('Entering:'||l_proc, 10);
475: end if;
470: --
471: g_debug := hr_utility.debug_enabled;
472: if g_debug then
473: l_proc := g_package||'chk_if_structure_exists';
474: hr_utility.set_location('Entering:'||l_proc, 10);
475: end if;
476: Open csr_chk_structure_exists;
477: Fetch csr_chk_structure_exists into l_exists;
478: if g_debug then
475: end if;
476: Open csr_chk_structure_exists;
477: Fetch csr_chk_structure_exists into l_exists;
478: if g_debug then
479: hr_utility.set_location('Leaving:'||l_proc, 20);
480: end if;
481: If csr_chk_structure_exists%NotFound then
482: Close csr_chk_structure_exists;
483: Return('N');
510: l_exists varchar2(1);
511: l_proc varchar2(72);
512: Begin
513: --
514: g_debug := hr_utility.debug_enabled;
515: if g_debug then
516: l_proc := g_package||'chk_version_exists';
517: hr_utility.set_location('Entering:'||l_proc, 10);
518: end if;
513: --
514: g_debug := hr_utility.debug_enabled;
515: if g_debug then
516: l_proc := g_package||'chk_version_exists';
517: hr_utility.set_location('Entering:'||l_proc, 10);
518: end if;
519: --
520: -- Initialize message pub
521: --
528: if(csr_version_exists%NotFound) then
529: -- Raise Error as we need atleast one version for the copy to happen.
530: close csr_version_exists;
531: if g_debug then
532: hr_utility.set_location('Error: No version found', 20);
533: end if;
534: fnd_message.set_name('PQH', 'PQH_GHR_EFF_DATE_NO_VERSION');
535: fnd_message.raise_error;
536: else
537: close csr_version_exists;
538: end if;
539: --
540: if g_debug then
541: hr_utility.set_location('Leaving:'||l_proc, 30);
542: end if;
543:
544: --
545: Exception
563: l_proc varchar2(80) ;
564: l_return_status varchar2(1) := 'N';
565: Begin
566: --
567: g_debug := hr_utility.debug_enabled;
568: if g_debug then
569: l_proc := g_package||'create_lookup_and_shared_type';
570: hr_utility.set_location('Entering:'||l_proc, 10);
571: end if;
566: --
567: g_debug := hr_utility.debug_enabled;
568: if g_debug then
569: l_proc := g_package||'create_lookup_and_shared_type';
570: hr_utility.set_location('Entering:'||l_proc, 10);
571: end if;
572:
573: --
574: -- Issue a Savepoint
587: --
588: if (l_return_status = 'Y') then
589: --
590: if g_debug then
591: hr_utility.set_location('Lookup Created', 20);
592: end if;
593: --
594: -- Create the shared type entry
595: --
596: create_shared_type(p_lookup_code => p_lookup_code,
597: p_meaning => p_meaning);
598: --
599: if g_debug then
600: hr_utility.set_location('Shared Type Created', 30);
601: end if;
602: end if;
603: --
604: if g_debug then
601: end if;
602: end if;
603: --
604: if g_debug then
605: hr_utility.set_location('Leaving:'||l_proc, 40);
606: end if;
607: --
608: Exception
609: When others
610: then
611: --
612: Rollback to lookup_and_shared_type;
613: if g_debug then
614: hr_utility.set_location('An Error has occurred:'||l_proc, 50);
615: end if;
616: --
617: --
618: End create_lookup_and_shared_type;
655: l_dummy varchar2(1);
656: --
657: Begin
658: --
659: g_debug := hr_utility.debug_enabled;
660: if g_debug then
661: l_proc := g_package||'create_lookup_value';
662: hr_utility.set_location('Entering:'|| l_proc, 10);
663: end if;
658: --
659: g_debug := hr_utility.debug_enabled;
660: if g_debug then
661: l_proc := g_package||'create_lookup_value';
662: hr_utility.set_location('Entering:'|| l_proc, 10);
663: end if;
664: --
665: -- Issue a savepoint
666: --
676: -- HIERARCHY_TYPE or HIERARCHY_NODE_TYPE only
677: --
678: if p_lookup_type not in ('HIERARCHY_TYPE','HIERARCHY_NODE_TYPE') then
679: if g_debug then
680: hr_utility.set_location('Error: Invalid lookup Type', 20);
681: end if;
682: fnd_message.set_name('PQH','PQH_GHR_INVALID_LOOKUP_TYPE');
683: hr_multi_message.add;
684: else
689: fetch csr_lookup_code_unique into l_dummy;
690: if csr_lookup_code_unique%found then
691: close csr_lookup_code_unique;
692: if g_debug then
693: hr_utility.set_location('Error: Lookup code already exists', 30);
694: end if;
695: -- raise error as the supplied lookup_code already exists
696: fnd_message.set_name('PQH','PQH_GHR_LOOKUP_CODE_EXISTS');
697: fnd_message.set_token('TYPE',p_lookup_type);
708: fetch csr_lookup_meaning_unique into l_dummy;
709: if csr_lookup_meaning_unique%found then
710: close csr_lookup_meaning_unique;
711: if g_debug then
712: hr_utility.set_location('Error: Meaning Already Exists', 40);
713: end if;
714: -- raise error as the supplied meaning already exists
715: fnd_message.set_name('PQH','PQH_GHR_LOOKUP_MEANING_EXISTS');
716: fnd_message.set_token('TYPE',p_lookup_type);
726: --
727: hr_multi_message.end_validation_set;
728: --
729: if g_debug then
730: hr_utility.set_location('No Validation Failures so far', 50);
731: end if;
732: -- Now we attempt to create an fnd_lookup_values record for the node using the
733: -- supplied code and user supplied values for p_node_name (meaning)
734: -- and p_description (description) for p_lookup_type (lookup type)
773: p_return_status := 'Y';
774: --
775: hr_multi_message.disable_message_list;
776: if g_debug then
777: hr_utility.set_location('Leaving:'||l_proc, 60);
778: end if;
779: exception
780: when hr_multi_message.error_message_exist then
781: --
784: rollback to create_lookup_value;
785: p_return_status := 'N';
786: hr_multi_message.disable_message_list;
787: if g_debug then
788: hr_utility.set_location('Errors Exist:'||l_proc, 70);
789: end if;
790: when others then
791: --
792: -- An unexpected error has occured
794: rollback to create_lookup_value;
795: p_return_status := 'N';
796: hr_multi_message.disable_message_list;
797: if g_debug then
798: hr_utility.set_location('Unexpected Error:'||l_proc, 80);
799: end if;
800: fnd_message.set_name('PQH','PQH_GHR_LOOKUP_INS_FAIL');
801: fnd_message.set_token('TYPE',p_lookup_type);
802: fnd_message.set_token('CODE',p_lookup_code);
819: l_dummy varchar2(1);
820: --
821: Begin
822: --
823: g_debug := hr_utility.debug_enabled;
824: if g_debug then
825: l_proc := g_package||'update_lookup_value';
826: hr_utility.set_location('Entering:'|| l_proc, 10);
827: end if;
822: --
823: g_debug := hr_utility.debug_enabled;
824: if g_debug then
825: l_proc := g_package||'update_lookup_value';
826: hr_utility.set_location('Entering:'|| l_proc, 10);
827: end if;
828:
829: fnd_lookup_values_pkg.UPDATE_ROW(X_LOOKUP_TYPE => p_lookup_type,
830: X_SECURITY_GROUP_ID => 0,
859:
860: exception
861: when others then
862: if g_debug then
863: hr_utility.set_location('Leaving:'|| l_proc, 20);
864: end if;
865: end update_lookup_value;
866: --
867: -- ----------------------------------------------------------------------------
892:
893: --
894: Begin
895: --
896: g_debug := hr_utility.debug_enabled;
897: if g_debug then
898: l_proc := g_package||'create_shared_type';
899: hr_utility.set_location('Entering:'|| l_proc, 10);
900: end if;
895: --
896: g_debug := hr_utility.debug_enabled;
897: if g_debug then
898: l_proc := g_package||'create_shared_type';
899: hr_utility.set_location('Entering:'|| l_proc, 10);
900: end if;
901: --
902: -- Issue a savepoint
903: --
916: fetch csr_shared_type_entry_unique into l_dummy;
917: if csr_shared_type_entry_unique%found then
918: close csr_shared_type_entry_unique;
919: if g_debug then
920: hr_utility.set_location('Error: Duplicate shared type', 20);
921: end if;
922: -- raise error as the supplied lookup_code already exists
923: fnd_message.set_name('PQH','PQH_GHR_SHARED_TYPE_DUP');
924: hr_multi_message.add;
930: --
931: hr_multi_message.end_validation_set;
932: --
933: if g_debug then
934: hr_utility.set_location('No Validation Errors So Far', 30);
935: end if;
936: -- Now we attempt to create a shared types entry using the
937: -- supplied code for the lookup_type 'HIERARCHY_TYPE'
938:
952: );
953:
954: hr_multi_message.disable_message_list;
955: if g_debug then
956: hr_utility.set_location('Leaving:'||l_proc, 40);
957: end if;
958: Exception
959: when hr_multi_message.error_message_exist then
960: --
962: --
963: rollback to create_shared_type;
964: hr_multi_message.disable_message_list;
965: if g_debug then
966: hr_utility.set_location('Error messages exist:'||l_proc, 50);
967: end if;
968: --
969: when others then
970: --
971: -- An unexpected error has occured
972: --
973: rollback to create_shared_type;
974: if g_debug then
975: hr_utility.set_location('Unexpected Error'||l_proc, 60);
976: end if;
977: hr_multi_message.disable_message_list;
978: fnd_message.set_name('PQH','PQH_GHR_SHARED_TYPE_INS_FAIL');
979: fnd_msg_pub.add;
1007:
1008: --
1009: Begin
1010: --
1011: g_debug := hr_utility.debug_enabled;
1012: if g_debug then
1013: l_proc := g_package||'update_shared_type';
1014: hr_utility.set_location('Entering:'|| l_proc, 10);
1015: end if;
1010: --
1011: g_debug := hr_utility.debug_enabled;
1012: if g_debug then
1013: l_proc := g_package||'update_shared_type';
1014: hr_utility.set_location('Entering:'|| l_proc, 10);
1015: end if;
1016: --
1017: -- Issue a savepoint
1018: --
1026: fetch csr_shared_type_entry into l_shared_type_id,l_object_version_number;
1027: if csr_shared_type_entry%notfound then
1028: close csr_shared_type_entry;
1029: if g_debug then
1030: hr_utility.set_location('Error: Lookup Code does not exist', 20);
1031: end if;
1032: -- raise error as the supplied lookup_code does not exist
1033: fnd_message.set_name('PQH','PQH_GHR_SHARED_TYPE_INV');
1034: fnd_message.raise_error;
1038: --
1039: -- Check that the information values are either 'Y' or 'N' only
1040: If ( (upper(p_information2) not in ('Y','N')) or (upper(p_information3) not in ('Y','N')) ) then
1041: if g_debug then
1042: hr_utility.set_location('Error: Info. Value is not y or n', 30);
1043: end if;
1044: --Raise error for invalid information value
1045: fnd_message.set_name('PQH','PQH_GHR_SHARED_TYPE_INV_INFO');
1046: fnd_message.raise_error;
1057: );
1058: End if;
1059: --
1060: if g_debug then
1061: hr_utility.set_location('Leaving:'||l_proc, 40);
1062: end if;
1063: exception
1064: when others then
1065: --
1066: -- An error has occured
1067: --
1068: rollback to update_shared_type;
1069: if g_debug then
1070: hr_utility.set_location('An Error has occured:', 50);
1071: end if;
1072: fnd_msg_pub.add;
1073: end update_shared_type;
1074: --
1121: l_sql varchar2(4000);
1122: --
1123: Begin
1124: --
1125: g_debug := hr_utility.debug_enabled;
1126: if g_debug then
1127: l_proc := g_package||'create_node_type';
1128: hr_utility.set_location('Entering:'||l_proc, 10);
1129: end if;
1124: --
1125: g_debug := hr_utility.debug_enabled;
1126: if g_debug then
1127: l_proc := g_package||'create_node_type';
1128: hr_utility.set_location('Entering:'||l_proc, 10);
1129: end if;
1130: --
1131: -- Issue a savepoint
1132: --
1140: Open csr_chk_duplicate;
1141: Fetch csr_chk_duplicate into l_exists;
1142: If csr_chk_duplicate%found then
1143: if g_debug then
1144: hr_utility.set_location('Error:Duplicate Node Type', 20);
1145: end if;
1146: -- This is a duplicate entry..so raise error.
1147: Close csr_chk_duplicate;
1148: fnd_message.set_name('PQH','PQH_GHR_DUPLICATE_NODE_TYPE');
1154: loop
1155: if c_rec.parent_node_type = p_child_node_type
1156: then
1157: if g_debug then
1158: hr_utility.set_location('Error:Recursive Structure', 30);
1159: end if;
1160: -- This will cause recursion
1161: fnd_message.set_name('PQH','PQH_GHR_RECURSIVE_NODE_TYPE');
1162: fnd_message.raise_error;
1165:
1166: if(p_parent_node_type = p_child_node_type)
1167: then
1168: if g_debug then
1169: hr_utility.set_location('Error:Recursive Structure', 40);
1170: end if;
1171: -- Again a recursive case
1172: fnd_message.set_name('PQH','PQH_GHR_RECURSIVE_NODE_TYPE');
1173: fnd_message.raise_error;
1191: per_gen_hier_node_types_s.nextval ,
1192: 1 )';
1193: --
1194: if g_debug then
1195: hr_utility.set_location('Sql Formed', 50);
1196: end if;
1197: Execute Immediate l_sql Using p_hierarchy_type, p_parent_node_type,
1198: p_child_node_type, p_child_value_set;
1199: if g_debug then
1196: end if;
1197: Execute Immediate l_sql Using p_hierarchy_type, p_parent_node_type,
1198: p_child_node_type, p_child_value_set;
1199: if g_debug then
1200: hr_utility.set_location('Leaving:'||l_proc, 60);
1201: end if;
1202: Exception
1203: when others
1204: then
1202: Exception
1203: when others
1204: then
1205: if g_debug then
1206: hr_utility.set_location('An Error has occured', 70);
1207: end if;
1208: fnd_msg_pub.add;
1209: rollback to create_node_type;
1210: end create_node_type;
1241: l_proc varchar2(72);
1242: --
1243: Begin
1244: --
1245: g_debug := hr_utility.debug_enabled;
1246: if g_debug then
1247: l_proc := g_package||'update_node_type';
1248: hr_utility.set_location('Entering:'||l_proc, 10);
1249: end if;
1244: --
1245: g_debug := hr_utility.debug_enabled;
1246: if g_debug then
1247: l_proc := g_package||'update_node_type';
1248: hr_utility.set_location('Entering:'||l_proc, 10);
1249: end if;
1250: --
1251: --Issue a Savepoint
1252: --
1263: If csr_node_type_entry%notfound
1264: then
1265: --
1266: if g_debug then
1267: hr_utility.set_location('Error: No Matching Node Type Entry', 20);
1268: end if;
1269: --
1270: Close csr_node_type_entry;
1271: fnd_message.set_name('PQH','PQH_GHR_NO_NODE_TYPE_ENTRY');
1276: If (p_object_version_number
1277: <> l_object_version_number) Then
1278: --
1279: if g_debug then
1280: hr_utility.set_location('Error:Invalid Object Version', 30);
1281: end if;
1282: --
1283: fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
1284: fnd_message.raise_error;
1299: (parent_node_type is null and :p_parent_node_type is null))
1300: and child_node_type = :p_child_node_type';
1301: --
1302: if g_debug then
1303: hr_utility.set_location('Sql Formed', 40);
1304: end if;
1305: --
1306: Execute immediate l_sql using p_child_value_set, p_object_version_number,
1307: p_hierarchy_type, p_parent_node_type,
1307: p_hierarchy_type, p_parent_node_type,
1308: p_parent_node_type,p_child_node_type;
1309: --
1310: if g_debug then
1311: hr_utility.set_location('Leaving:'||l_proc, 50);
1312: end if;
1313: --
1314: Exception
1315: When HR_Api.Object_Locked then
1317: -- The object is locked therefore we need to supply a meaningful
1318: -- error message.
1319: --
1320: if g_debug then
1321: hr_utility.set_location('Error: Object Locked', 60);
1322: end if;
1323: --
1324: fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1325: fnd_message.set_token('TABLE_NAME', 'per_gen_hier_node_types');
1326: fnd_msg_pub.add;
1327: When others
1328: then
1329: if g_debug then
1330: hr_utility.set_location('An Error has occured', 70);
1331: end if;
1332: rollback to update_node_type;
1333: fnd_msg_pub.add;
1334: end update_node_type;
1377: l_proc varchar2(72);
1378: --
1379: Begin
1380: --
1381: g_debug := hr_utility.debug_enabled;
1382: if g_debug then
1383: l_proc := g_package||'delete_node_type';
1384: hr_utility.set_location('Entering:'||l_proc, 10);
1385: end if;
1380: --
1381: g_debug := hr_utility.debug_enabled;
1382: if g_debug then
1383: l_proc := g_package||'delete_node_type';
1384: hr_utility.set_location('Entering:'||l_proc, 10);
1385: end if;
1386: --
1387: --Issue a Savepoint
1388: --
1400: If csr_node_type_entry%notfound
1401: then
1402: --
1403: if g_debug then
1404: hr_utility.set_location('Error: No Matching Node Type Entry', 20);
1405: end if;
1406: --
1407: Close csr_node_type_entry;
1408: fnd_message.set_name('PQH','PQH_GHR_NO_NODE_TYPE_ENTRY');
1422: Or
1423: (parent_node_type is null and :p_parent_node_type is null))
1424: And child_node_type = :p_child_node_type';
1425: if g_debug then
1426: hr_utility.set_location('Sql Formed', 30);
1427: end if;
1428:
1429: For c_rec in csr_node_type_hierarchy
1430: loop
1432: Execute immediate l_sql using p_hierarchy_type,
1433: c_rec.parent_node_type,c_rec.parent_node_type,
1434: c_rec.child_node_type;
1435: if g_debug then
1436: hr_utility.set_location('Deleted a Row, Trying another...', 40);
1437: end if;
1438: End loop;
1439:
1440: Exception
1443: -- The object is locked therefore we need to supply a meaningful
1444: -- error message.
1445: --
1446: if g_debug then
1447: hr_utility.set_location('Error: Object Locked', 50);
1448: end if;
1449: fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1450: fnd_message.set_token('TABLE_NAME', 'per_gen_hier_node_types');
1451: fnd_msg_pub.add;
1451: fnd_msg_pub.add;
1452: When others
1453: then
1454: if g_debug then
1455: hr_utility.set_location('An Error has Occured', 60);
1456: end if;
1457: rollback to delete_node_type;
1458: fnd_msg_pub.add;
1459: End delete_node_type;
1479: l_proc varchar2(72);
1480: --
1481: Begin
1482: --
1483: g_debug := hr_utility.debug_enabled;
1484: if g_debug then
1485: l_proc := g_package||'delete_type_structure';
1486: hr_utility.set_location('Entering:'||l_proc, 10);
1487: end if;
1482: --
1483: g_debug := hr_utility.debug_enabled;
1484: if g_debug then
1485: l_proc := g_package||'delete_type_structure';
1486: hr_utility.set_location('Entering:'||l_proc, 10);
1487: end if;
1488: --
1489: -- Initialize message pub
1490: --
1505: delete_node_type(p_hierarchy_type => p_hierarchy_type,
1506: p_parent_node_type => null,
1507: p_child_node_type => c_rec.child_node_type);
1508: if g_debug then
1509: hr_utility.set_location('Deleted a row.Trying another..', 20);
1510: end if;
1511: End loop;
1512: --
1513: if g_debug then
1510: end if;
1511: End loop;
1512: --
1513: if g_debug then
1514: hr_utility.set_location('Leaving:'||l_proc, 30);
1515: end if;
1516: --
1517: Exception
1518: When others
1517: Exception
1518: When others
1519: then
1520: if g_debug then
1521: hr_utility.set_location('An Error has occured', 40);
1522: end if;
1523: Rollback to delete_type_structure;
1524: fnd_msg_pub.add;
1525: End delete_type_structure;
1598: l_Object_version_Number Per_Gen_hierarchy.Object_Version_Number%TYPE;
1599:
1600: Begin
1601: --
1602: g_debug := hr_utility.debug_enabled;
1603: if g_debug then
1604: l_proc := g_package||'copy_hierarchy_version';
1605: hr_utility.set_location('Entering:'||l_proc, 10);
1606: end if;
1601: --
1602: g_debug := hr_utility.debug_enabled;
1603: if g_debug then
1604: l_proc := g_package||'copy_hierarchy_version';
1605: hr_utility.set_location('Entering:'||l_proc, 10);
1606: end if;
1607: --
1608: -- Initialize message pub
1609: --
1609: --
1610: fnd_msg_pub.initialize;
1611: --
1612: if g_debug then
1613: hr_utility.set_location('Calling copy proc. with p_type='||p_type, 20);
1614: end if;
1615: --
1616: PER_HIERARCHY_NODES_API.G_MODE := 'COPY' ;
1617: --
1785:
1786: End If;
1787: --
1788: if g_debug then
1789: hr_utility.set_location('Leaving:'||l_proc, 30);
1790: end if;
1791: --
1792: PER_HIERARCHY_NODES_API.G_MODE := null ;
1793: --
1798: --
1799: PER_HIERARCHY_NODES_API.G_MODE := null ;
1800: --
1801: if g_debug then
1802: hr_utility.set_location('An Error has occured', 40);
1803: end if;
1804: fnd_msg_pub.add();
1805: End copy_hierarchy_version;
1806: --
1818: l_proc Varchar2(72);
1819: Begin
1820: if g_debug then
1821: l_proc := g_package||'l_valid_sql';
1822: hr_utility.set_location('Entering:'||l_proc, 10);
1823: end if;
1824:
1825: l_sql := 'select ''Y'' from ('||p_sql||') where rownum < 1';
1826: --
1833: l_valid := 'N';
1834: End;
1835: --
1836: if g_debug then
1837: hr_utility.set_location('Leaving:'||l_proc, 10);
1838: end if;
1839: --
1840: Return l_valid;
1841: --
1865: Begin
1866: --
1867: if g_debug then
1868: l_proc := g_package||'validate_vets_hierarchy';
1869: hr_utility.set_location('Entering:'||l_proc, 10);
1870: end if;
1871: --
1872: for l_rec in csr_node_level loop
1873: --
1951: BEGIN
1952: --
1953: if g_debug then
1954: l_proc := g_package||'get_display_value';
1955: hr_utility.set_location('Entering:'||l_proc, 10);
1956: end if;
1957: --
1958: if (p_entity_id is not null and p_node_type_id is not null) then
1959: -- Open cursor to get the VS
1965:
1966: if l_value_set_id is not null then
1967: --
1968: if g_debug then
1969: hr_utility.set_location('Non Null Value Set Id Retrieved', 20);
1970: end if;
1971: --
1972:
1973: l_sql_statement := get_sql_from_vset_id(p_vset_id => l_value_set_id);
2002: l_sql_statement := REPLACE(l_sql_statement,':$PROFILES$.PER_BUSINESS_GROUP_ID'
2003: ,fnd_profile.value('PER_BUSINESS_GROUP_ID'));
2004: --
2005: if g_debug then
2006: hr_utility.set_location('Value Set Sql Retrieved and Processed', 30);
2007: end if;
2008: --
2009: BEGIN
2010: --
2010: --
2011: EXECUTE IMMEDIATE l_sql_statement INTO l_value_id, l_name USING p_entity_id;
2012: --
2013: if g_debug then
2014: hr_utility.set_location('Valid Sql: Display Value Found', 40);
2015: end if;
2016: --
2017: EXCEPTION
2018: --
2018: --
2019: WHEN OTHERS THEN
2020: --
2021: if g_debug then
2022: hr_utility.set_location('Invalid Entity Id or Value Set', 50);
2023: end if;
2024: --
2025: l_name := 'INVALID_VALUE_SET, vs_id :' || l_value_set_id || ', l_value_id: ' || p_entity_id;
2026: --
2028: --
2029: else
2030: --
2031: if g_debug then
2032: hr_utility.set_location('Value Set Id Not Found: Null', 60);
2033: end if;
2034: --
2035: end if;
2036: --
2034: --
2035: end if;
2036: --
2037: if g_debug then
2038: hr_utility.set_location('Leaving:'||l_proc, 70);
2039: end if;
2040: --
2041: RETURN l_name;
2042: --
2062: BEGIN
2063: --
2064: if g_debug then
2065: l_proc := g_package||'gen_hier_exists';
2066: hr_utility.set_location('Entering:'||l_proc, 10);
2067: end if;
2068: --
2069: open csr_hexist;
2070: fetch csr_hexist into l_return;
2070: fetch csr_hexist into l_return;
2071: close csr_hexist;
2072: --
2073: if g_debug then
2074: hr_utility.set_location('Leaving:'||l_proc||'with val:'||nvl(l_return,'N'),20);
2075: end if;
2076: RETURN nvl(l_return,'N');
2077: --
2078: END gen_hier_exists;
2098: Begin
2099: --
2100: if g_debug then
2101: l_proc := g_package||'chk_multiple_versions';
2102: hr_utility.set_location('Entering:'||l_proc, 10);
2103: end if;
2104: --
2105: for l_rec in csr_hier_versions loop
2106: if csr_hier_versions%ROWCOUNT = 2 then
2104: --
2105: for l_rec in csr_hier_versions loop
2106: if csr_hier_versions%ROWCOUNT = 2 then
2107: if g_debug then
2108: hr_utility.set_location('Leaving:'||l_proc||' with val:Y', 20);
2109: end if;
2110: return 'Y';
2111: end if;
2112: end loop;
2111: end if;
2112: end loop;
2113: --
2114: if g_debug then
2115: hr_utility.set_location('Leaving:'||l_proc||' with val:N', 30);
2116: end if;
2117: return 'N';
2118: --
2119: --