[Home] [Help]
14: l_proc varchar2(73) := g_package || 'get_clob_locator';
15:
16: BEGIN
17:
18: hr_utility.set_location('Entering: '|| l_proc, 10);
19:
20: l_clob_rec.table_name := p_table_name;
21: l_clob_rec.xmldoc := null;
22: l_xmldoc_loc := l_clob_rec.xmldoc;
20: l_clob_rec.table_name := p_table_name;
21: l_clob_rec.xmldoc := null;
22: l_xmldoc_loc := l_clob_rec.xmldoc;
23:
24: hr_utility.set_location('Leaving: '|| l_proc, 20);
25:
26: return l_xmldoc_loc;
27: EXCEPTION
28: when no_data_found then
46:
47:
48: BEGIN
49:
50: hr_utility.set_location('Entering ' ||l_proc ,10);
51:
52: l_bg_clob := get_clob_locator('BUSINESS_GROUPS');
53: dbms_lob.createtemporary(l_bg_clob,TRUE);
54:
61: ||' position_flex_stru_code,competence_flex_stru_code from (';
62:
63: dbms_lob.writeappend(l_bg_clob,length(l_bg_prejoin_sql),l_bg_prejoin_sql);
64:
65: hr_utility.set_location('Entering BG Loop'||l_proc,15);
66:
67: IF p_business_grp_tab.count > 0 THEN
68: for i in p_business_grp_tab.first ..
69: p_business_grp_tab.last loop
96:
97: dbms_lob.writeappend(l_bg_clob,length(l_temp_sql),l_temp_sql);
98: end loop;
99: end if;
100: hr_utility.set_location('Leaving BG Loop',20);
101: dbms_lob.trim(l_bg_clob,length(l_bg_clob)-5);
102:
103: l_bg_postjoin_sql := ' ) X ,FND_TERRITORIES_VL terr ,fnd_lookup_values lookup1, '
104: ||' fnd_lookup_values lookup2,fnd_lookup_values lookup3 , '
119: dbms_lob.writeappend(l_bg_clob,length(l_bg_postjoin_sql),l_bg_postjoin_sql);
120:
121: l_ret_bg_clob := fetch_clob(l_bg_clob,'BusinessGroup','BusinessGroups');
122:
123: hr_utility.set_location('Leaving ' ||l_proc ,30);
124:
125: return l_ret_bg_clob;
126: END get_business_grp_sql;
127:
141: l_orderby varchar2(200);
142:
143: begin
144:
145: hr_utility.set_location('Entering ' || l_proc ,10);
146:
147: l_org_clob := get_clob_locator('ORGANIZATIONS');
148: dbms_lob.createtemporary(l_org_clob,TRUE);
149: l_orderby := ' order by business_grp_name desc, name desc ';
147: l_org_clob := get_clob_locator('ORGANIZATIONS');
148: dbms_lob.createtemporary(l_org_clob,TRUE);
149: l_orderby := ' order by business_grp_name desc, name desc ';
150:
151: hr_utility.set_location('Before Org Enterprise Loop ' || l_proc ,10);
152:
153: IF p_org_ent_tab.count > 0 THEN
154: for i in p_org_ent_tab.first ..
155: p_org_ent_tab.last loop
166: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);
167: end loop;
168: end if;
169:
170: hr_utility.set_location('Leaving Ent Loop',20);
171: hr_utility.set_location('Entering OC Loop',30);
172:
173: IF p_org_oc_tab.count > 0 THEN
174: for i in p_org_oc_tab.first ..
167: end loop;
168: end if;
169:
170: hr_utility.set_location('Leaving Ent Loop',20);
171: hr_utility.set_location('Entering OC Loop',30);
172:
173: IF p_org_oc_tab.count > 0 THEN
174: for i in p_org_oc_tab.first ..
175: p_org_oc_tab.last loop
172:
173: IF p_org_oc_tab.count > 0 THEN
174: for i in p_org_oc_tab.first ..
175: p_org_oc_tab.last loop
176: hr_utility.set_location('Inside OC Loop',45);
177:
178: l_temp_sql:= ' SELECT '||
179: '''' || p_org_oc_tab(i).effective_date ||''''||' effective_date ,'||
180: '''' || p_org_oc_tab(i).date_from ||''''||' date_from ,'||
187: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);
188: end loop;
189: end if;
190:
191: hr_utility.set_location('Leaving OC Loop',40);
192: hr_utility.set_location('Entering LE Loop',50);
193:
194: IF p_org_le_tab.count > 0 THEN
195: for i in p_org_le_tab.first ..
188: end loop;
189: end if;
190:
191: hr_utility.set_location('Leaving OC Loop',40);
192: hr_utility.set_location('Entering LE Loop',50);
193:
194: IF p_org_le_tab.count > 0 THEN
195: for i in p_org_le_tab.first ..
196: p_org_le_tab.last loop
207: dbms_lob.writeappend(l_org_clob,length(l_temp_sql),l_temp_sql);
208: end loop;
209: end if;
210:
211: hr_utility.set_location('Leaving LE Loop',60);
212: dbms_lob.trim(l_org_clob,length(l_org_clob)-5);
213: dbms_lob.writeappend(l_org_clob,length(l_orderby),l_orderby);
214:
215: l_ret_org_clob := fetch_clob(l_org_clob,'Organization','Organizations');
235: l_class_postjoin_sql varchar2(2000);
236:
237: BEGIN
238:
239: hr_utility.set_location('Entering ' || l_proc,10);
240:
241: select meaning into l_enabled from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y';
242: l_org_class_clob := get_clob_locator('ORGANIZATIONS');
243: dbms_lob.createtemporary(l_org_class_clob,TRUE);
241: select meaning into l_enabled from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y';
242: l_org_class_clob := get_clob_locator('ORGANIZATIONS');
243: dbms_lob.createtemporary(l_org_class_clob,TRUE);
244:
245: hr_utility.set_location('Entering Enterprise Classification ',15);
246:
247: l_class_prejoin_sql:= ' select effective_date,'
248: ||' date_from,'
249: ||' business_grp_name,'
270: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);
271: end loop;
272: end if;
273:
274: hr_utility.set_location('Leaving Enterprise Classification ',20);
275: hr_utility.set_location('Entering OC Loop ',30);
276:
277: IF p_org_oc_class_tab.count > 0 THEN
278: for i in p_org_oc_class_tab.first ..
271: end loop;
272: end if;
273:
274: hr_utility.set_location('Leaving Enterprise Classification ',20);
275: hr_utility.set_location('Entering OC Loop ',30);
276:
277: IF p_org_oc_class_tab.count > 0 THEN
278: for i in p_org_oc_class_tab.first ..
279: p_org_oc_class_tab.last loop
289: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);
290: end loop;
291: end if;
292:
293: hr_utility.set_location('Leaving OC Loop',40);
294: hr_utility.set_location('Entering LE Loop',50);
295:
296: IF p_org_le_class_tab.count > 0 THEN
297: for i in p_org_le_class_tab.first ..
290: end loop;
291: end if;
292:
293: hr_utility.set_location('Leaving OC Loop',40);
294: hr_utility.set_location('Entering LE Loop',50);
295:
296: IF p_org_le_class_tab.count > 0 THEN
297: for i in p_org_le_class_tab.first ..
298: p_org_le_class_tab.last loop
307:
308: dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);
309: end loop;
310: end if;
311: hr_utility.set_location('Leaving LE Loop',60);
312: dbms_lob.trim(l_org_class_clob,length(l_org_class_clob)-5);
313:
314: l_class_postjoin_sql := ' ), hr_lookups hrlkp '
315: ||' where hrlkp.lookup_type= ''ORG_CLASS'''
344: l_classification_exists boolean := false;
345:
346:
347: BEGIN
348: hr_utility.set_location('Entering ' || l_proc,10);
349:
350: select meaning into l_enabled from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y';
351: l_orderby := ' order by business_grp_name desc, name desc ';
352: l_org_class_clob_for_pv := get_clob_locator('ORGANIZATION_CLASSIF');
400: end if;
401: end if;
402: end loop;
403: end if;
404: hr_utility.set_location('Leaving Loop',20);
405: hr_utility.set_location('Entering OC Loop',30);
406:
407: --For every org find all classifications
408: IF p_org_oc_tab.count > 0 THEN
401: end if;
402: end loop;
403: end if;
404: hr_utility.set_location('Leaving Loop',20);
405: hr_utility.set_location('Entering OC Loop',30);
406:
407: --For every org find all classifications
408: IF p_org_oc_tab.count > 0 THEN
409: for j in p_org_oc_tab.first ..
454: end if;
455: end loop;
456: end if;
457:
458: hr_utility.set_location('Leaving OC Loop',40);
459: hr_utility.set_location('Entering LE Loop',50);
460:
461: --For every org find all classifications
462: IF p_org_le_tab.count > 0 THEN
455: end loop;
456: end if;
457:
458: hr_utility.set_location('Leaving OC Loop',40);
459: hr_utility.set_location('Entering LE Loop',50);
460:
461: --For every org find all classifications
462: IF p_org_le_tab.count > 0 THEN
463: for j in p_org_le_tab.first ..
507: end if;
508: end loop;
509: end if;
510:
511: hr_utility.set_location('Leaving LE Loop',60);
512:
513: return l_org_class_append_clob_for_pv;
514: END get_org_class_sql_for_pv;
515:
544: from fnd_territories_vl terr
545: where terr.territory_code = cp_style;
546:
547: BEGIN
548: hr_utility.set_location('Entering ' || l_proc,10);
549: l_location_clob := get_clob_locator('LOCATIONS');
550: dbms_lob.createtemporary(l_location_clob,TRUE);
551:
552: l_prejoin_sql := ' select decode(A.style,''GENERIC'',''Generic'',terr.territory_short_name) Address_Style '
629:
630: dbms_lob.trim(l_location_clob,length(l_location_clob)-5);
631: dbms_lob.writeappend(l_location_clob,length(l_orderby),l_orderby);
632: l_ret_location_clob := fetch_clob(l_location_clob,'Location','Locations');
633: hr_utility.set_location('Leaving ' || l_proc,20);
634: return l_ret_location_clob;
635:
636: END get_locations_sql;
637:
647: queryCtx number(8) := 0;
648:
649: BEGIN
650:
651: hr_utility.set_location('Entering ' || l_proc,10);
652: l_user_clob := get_clob_locator('USERS');
653: dbms_lob.createtemporary(l_user_clob,TRUE);
654:
655: if p_user_tab.count > 0 THEN
667: end if;
668:
669: dbms_lob.trim(l_user_clob,length(l_user_clob)-5);
670: l_ret_user_clob := fetch_clob(l_user_clob,'User','Users');
671: hr_utility.set_location('Leaving ' || l_proc,20);
672: return l_ret_user_clob;
673:
674: END get_user_sql;
675:
692: l_postjoin_sql varchar2(2000);
693:
694: BEGIN
695:
696: hr_utility.set_location('Entering ' || l_proc,10);
697: l_resp_clob := get_clob_locator('RESPONSIBILITIES');
698: dbms_lob.createtemporary(l_resp_clob,TRUE);
699:
700: l_prejoin_sql := ' select distinct A.user_name , A.resp_key ResponsibilityName ,'
769:
770: dbms_lob.writeappend(l_resp_clob,length(l_postjoin_sql),l_postjoin_sql);
771: l_ret_resp_clob := fetch_clob(l_resp_clob,'Resp','Resps');
772:
773: hr_utility.set_location('Leaving ' || l_proc,20);
774: return l_ret_resp_clob;
775:
776: END get_resp_sql;
777:
793: l_postjoin_sql varchar2(2000);
794: l_translated_prof_opt_value hr_lookups.meaning%type;
795:
796: BEGIN
797: hr_utility.set_location('Entering ' || l_proc,10);
798: l_profile_clob := get_clob_locator('Profiles');
799: dbms_lob.createtemporary(l_profile_clob,TRUE);
800:
801: l_prejoin_sql := ' select A.level_x , fprf.user_profile_option_name UserProfileName,'||
866:
867: dbms_lob.trim(l_profile_clob,length(l_profile_clob)-5);
868: dbms_lob.writeappend(l_profile_clob,length(l_postjoin_sql),l_postjoin_sql);
869: l_ret_profile_clob := fetch_clob(l_profile_clob,'Profile','Profiles');
870: hr_utility.set_location('Leaving ' || l_proc,20);
871: return l_ret_profile_clob;
872:
873: END get_profile_sql;
874:
890: l_postjoin_sql varchar2(2000);
891:
892: BEGIN
893:
894: hr_utility.set_location('Entering ' || l_proc,10);
895: l_profile_apps_clob := get_clob_locator('profile_appss');
896: dbms_lob.createtemporary(l_profile_apps_clob,TRUE);
897:
898: l_prejoin_sql := ' select A.level_x , fprf.user_profile_option_name UserProfileName,'||
924:
925: dbms_lob.trim(l_profile_apps_clob,length(l_profile_apps_clob)-5);
926: dbms_lob.writeappend(l_profile_apps_clob,length(l_postjoin_sql),l_postjoin_sql);
927: l_ret_profile_apps_clob := fetch_clob(l_profile_apps_clob,'profile_apps','profile_apps');
928: hr_utility.set_location('Leaving ' || l_proc,20);
929: return l_ret_profile_apps_clob;
930:
931: END get_profile_apps_sql;
932:
946: l_postjoin_sql varchar2(2000);
947: l_prof_opt_value fnd_lookup_values.meaning%type;
948:
949: BEGIN
950: hr_utility.set_location('Entering ' || l_proc,10);
951: l_profile_resp_clob := get_clob_locator('profile_resp');
952: dbms_lob.createtemporary(l_profile_resp_clob,TRUE);
953:
954: l_prejoin_sql := ' select A.level_x , fprf.user_profile_option_name UserProfileName,'||
988: dbms_lob.trim(l_profile_resp_clob,length(l_profile_resp_clob)-5);
989: dbms_lob.writeappend(l_profile_resp_clob,length(l_postjoin_sql),l_postjoin_sql);
990: l_ret_profile_resp_clob := fetch_clob(l_profile_resp_clob,'profile_resp','profile_resp');
991:
992: hr_utility.set_location('Leaving ' || l_proc,20);
993: return l_ret_profile_resp_clob;
994:
995: END get_profile_resp_sql;
996:
1034: l_freeze_flex_def varchar2(8) := 'Y';
1035:
1036:
1037: BEGIN
1038: hr_utility.set_location('Entering ' || l_proc,10);
1039:
1040: select meaning into l_allow_dynamic from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y';
1041: l_enabled := l_allow_dynamic;
1042: l_freeze_flex_def := l_enabled;
1052: ' order by Application_Name desc,structure_title desc';
1053:
1054: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1055:
1056: hr_utility.set_location('Before Main Job Keyflex Structure ' || l_proc,20);
1057: if p_kf_job_tab.count > 0 THEN
1058: for i in p_kf_job_tab.first ..
1059: p_kf_job_tab.last loop
1060:
1073: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1074: end loop;
1075: end if;
1076:
1077: hr_utility.set_location('Before RV Job Keyflex Structure ' || l_proc,30);
1078:
1079: if p_kf_job_rv_tab.count > 0 THEN
1080: for i in p_kf_job_rv_tab.first ..
1081: p_kf_job_rv_tab.last loop
1095: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1096: end loop;
1097: end if;
1098:
1099: hr_utility.set_location('Before No RV Job Keyflex Structure ' || l_proc,40);
1100:
1101: if p_kf_job_no_rv_tab.count > 0 THEN
1102: for i in p_kf_job_no_rv_tab.first ..
1103: p_kf_job_no_rv_tab.last loop
1129: l_kf_str_clob := get_clob_locator('PosKeyFlexStruct');
1130: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1131: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1132:
1133: hr_utility.set_location('Before Main Pos Keyflex Structure ' || l_proc,50);
1134:
1135: if p_kf_pos_tab.count > 0 THEN
1136: for i in p_kf_pos_tab.first ..
1137: p_kf_pos_tab.last loop
1152: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1153: end loop;
1154: end if;
1155:
1156: hr_utility.set_location('Before RV Pos Keyflex Structure ' || l_proc,60);
1157: if p_kf_pos_rv_tab.count > 0 THEN
1158: for i in p_kf_pos_rv_tab.first ..
1159: p_kf_pos_rv_tab.last loop
1160:
1174: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1175: end loop;
1176: end if;
1177:
1178: hr_utility.set_location('Before NO RV Pos Keyflex Structure ' || l_proc,70);
1179:
1180: if p_kf_pos_no_rv_tab.count > 0 THEN
1181: for i in p_kf_pos_no_rv_tab.first ..
1182: p_kf_pos_no_rv_tab.last loop
1209: l_kf_str_clob := get_clob_locator('GrdKeyFlexStruct');
1210: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1211: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1212:
1213: hr_utility.set_location('Before Main Grd Keyflex Structure ' || l_proc,80);
1214:
1215: if p_kf_grd_tab.count > 0 THEN
1216: for i in p_kf_grd_tab.first ..
1217: p_kf_grd_tab.last loop
1232: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1233: end loop;
1234: end if;
1235:
1236: hr_utility.set_location('Before RV Grd Keyflex Structure ' || l_proc,90);
1237:
1238: if p_kf_grd_rv_tab.count > 0 THEN
1239: for i in p_kf_grd_rv_tab.first ..
1240: p_kf_grd_rv_tab.last loop
1255: dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1256: end loop;
1257: end if;
1258:
1259: hr_utility.set_location('Before NO RV Grd Keyflex Structure ' || l_proc,100);
1260:
1261: if p_kf_grd_no_rv_tab.count > 0 THEN
1262: for i in p_kf_grd_no_rv_tab.first ..
1263: p_kf_grd_no_rv_tab.last loop
1289: l_kf_str_clob := get_clob_locator('CmpKeyFlexStruct');
1290: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1291: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1292:
1293: hr_utility.set_location('Before Cmp Keyflex Structure ' || l_proc,110);
1294:
1295: if p_kf_cmp_tab.count > 0 THEN
1296: for i in p_kf_cmp_tab.first ..
1297: p_kf_cmp_tab.last loop
1324: l_kf_str_clob := get_clob_locator('GrpKeyFlexStruct');
1325: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1326: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1327:
1328: hr_utility.set_location('Before Grp Keyflex Structure ' || l_proc,120);
1329:
1330: if p_kf_grp_tab.count > 0 THEN
1331: for i in p_kf_grp_tab.first ..
1332: p_kf_grp_tab.last loop
1359: l_kf_str_clob := get_clob_locator('CostKeyFlexStruct');
1360: dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1361: dbms_lob.writeappend(l_kf_str_clob,length(l_prejoin_sql),l_prejoin_sql);
1362:
1363: hr_utility.set_location('Before Cost Keyflex Structure ' || l_proc,130);
1364:
1365: if p_kf_cost_tab.count > 0 THEN
1366: for i in p_kf_cost_tab.first ..
1367: p_kf_cost_tab.last loop
1389: if p_kf_cost_tab.count > 0 then
1390: p_kf_cost_str_clob := fetch_clob(l_kf_str_clob,'CostKeyFlexStruct','CostKeyFlexStructures');
1391: end if;
1392:
1393: hr_utility.set_location('Leaving ' ||l_proc,140);
1394: return l_ret_kf_str_clob;
1395:
1396: END get_keyflex_structure_sql;
1397:
1443: l_vs_validation_type varchar2(80);
1444:
1445:
1446: BEGIN
1447: hr_utility.set_location('Entering ' ||l_proc,20);
1448:
1449: select meaning into l_required from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y';
1450: l_display := l_required;
1451: l_enabled := l_required;
1456: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1457:
1458: l_orderby := ' order by segment_number ';
1459:
1460: hr_utility.set_location('Before Job Keyflex Segment ' ||l_proc,20);
1461:
1462: if p_kf_job_seg_tab.count > 0 THEN
1463: for i in p_kf_job_seg_tab.first ..
1464: p_kf_job_seg_tab.last loop
1609:
1610: p_kf_job_seg_clob := fetch_clob(l_kf_seg_clob,'JobKeyFlexSegment','JobKeyFlexSegments');
1611: end if;
1612:
1613: hr_utility.set_location('Done with Job Keyflex Segment ' ||l_proc,20);
1614: hr_utility.set_location('Before Pos Keyflex Segment ' ||l_proc,20);
1615:
1616: l_kf_seg_clob := null;
1617: l_kf_seg_clob := get_clob_locator('PosKeyFlexSegment');
1610: p_kf_job_seg_clob := fetch_clob(l_kf_seg_clob,'JobKeyFlexSegment','JobKeyFlexSegments');
1611: end if;
1612:
1613: hr_utility.set_location('Done with Job Keyflex Segment ' ||l_proc,20);
1614: hr_utility.set_location('Before Pos Keyflex Segment ' ||l_proc,20);
1615:
1616: l_kf_seg_clob := null;
1617: l_kf_seg_clob := get_clob_locator('PosKeyFlexSegment');
1618: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1768: p_kf_pos_seg_clob := fetch_clob(l_kf_seg_clob,'PosNoRVKeyFlexSegment','PosNoRVKeyFlexSegments');
1769: end if;
1770:
1771:
1772: hr_utility.set_location('Done with Pos Keyflex Segment ' ||l_proc,20);
1773: hr_utility.set_location('Before Grade Keyflex Segment ' ||l_proc,20);
1774:
1775: l_kf_seg_clob := null;
1776: l_kf_seg_clob := get_clob_locator('GrdKeyFlexSegment');
1769: end if;
1770:
1771:
1772: hr_utility.set_location('Done with Pos Keyflex Segment ' ||l_proc,20);
1773: hr_utility.set_location('Before Grade Keyflex Segment ' ||l_proc,20);
1774:
1775: l_kf_seg_clob := null;
1776: l_kf_seg_clob := get_clob_locator('GrdKeyFlexSegment');
1777: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1929:
1930: p_kf_grd_seg_clob := fetch_clob(l_kf_seg_clob,'GrdNoRVKeyFlexSegment','GrdNoRVKeyFlexSegments');
1931: end if;
1932:
1933: hr_utility.set_location('Done with Grade Keyflex Segment ' ||l_proc,20);
1934: hr_utility.set_location('Before Grp Keyflex Segment ' ||l_proc,20);
1935:
1936: l_kf_seg_clob := null;
1937: l_kf_seg_clob := get_clob_locator('GrpKeyFlexSegment');
1930: p_kf_grd_seg_clob := fetch_clob(l_kf_seg_clob,'GrdNoRVKeyFlexSegment','GrdNoRVKeyFlexSegments');
1931: end if;
1932:
1933: hr_utility.set_location('Done with Grade Keyflex Segment ' ||l_proc,20);
1934: hr_utility.set_location('Before Grp Keyflex Segment ' ||l_proc,20);
1935:
1936: l_kf_seg_clob := null;
1937: l_kf_seg_clob := get_clob_locator('GrpKeyFlexSegment');
1938: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1995: p_kf_grp_seg_clob := fetch_clob(l_kf_seg_clob,'GrpKeyFlexSegment','GrpKeyFlexSegment');
1996: end if;
1997:
1998:
1999: hr_utility.set_location('Done with Grp Keyflex Segment ' ||l_proc,20);
2000: hr_utility.set_location('Before Cmp Keyflex Segment ' ||l_proc,20);
2001:
2002: l_kf_seg_clob := null;
2003: l_kf_seg_clob := get_clob_locator('CmpKeyFlexSegment');
1996: end if;
1997:
1998:
1999: hr_utility.set_location('Done with Grp Keyflex Segment ' ||l_proc,20);
2000: hr_utility.set_location('Before Cmp Keyflex Segment ' ||l_proc,20);
2001:
2002: l_kf_seg_clob := null;
2003: l_kf_seg_clob := get_clob_locator('CmpKeyFlexSegment');
2004: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
2060: p_kf_cmp_seg_clob := fetch_clob(l_kf_seg_clob,'CmpKeyFlexSegment','CmpKeyFlexSegments');
2061: end if;
2062:
2063:
2064: hr_utility.set_location('Done with Cmp Keyflex Segment ' ||l_proc,20);
2065: hr_utility.set_location('Before Cost Keyflex Segment ' ||l_proc,20);
2066:
2067: l_kf_seg_clob := null;
2068: l_kf_seg_clob := get_clob_locator('CostKeyFlexSegment');
2061: end if;
2062:
2063:
2064: hr_utility.set_location('Done with Cmp Keyflex Segment ' ||l_proc,20);
2065: hr_utility.set_location('Before Cost Keyflex Segment ' ||l_proc,20);
2066:
2067: l_kf_seg_clob := null;
2068: l_kf_seg_clob := get_clob_locator('CostKeyFlexSegment');
2069: dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
2114: dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
2115: end loop;
2116: end if;
2117:
2118: hr_utility.set_location('Done with Cost Keyflex Segment ' ||l_proc,20);
2119:
2120: if p_kf_cost_seg_tab.count > 0 then
2121: dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
2122:
2127: p_kf_cost_seg_clob := fetch_clob(l_kf_seg_clob,'CostKeyFlexSegment','CostKeyFlexSegments');
2128: end if;
2129:
2130:
2131: hr_utility.set_location('Leaving ' ||l_proc,20);
2132: return l_ret_kf_seg_clob;
2133:
2134: END get_keyflex_segment_sql;
2135:
2165:
2166:
2167:
2168: BEGIN
2169: hr_utility.set_location('Entering ' || l_proc,10);
2170:
2171: /* make a copy of job before calling*/
2172: IF p_kf_job_tab.count > 0 THEN
2173: for j in p_kf_job_tab.first ..
2749:
2750: --insert into temp(data) values (l_str_seg_append_clob_for_pv);
2751: --commit;
2752:
2753: hr_utility.set_location('Leaving ' ||l_proc ,20);
2754: return l_str_seg_append_clob_for_pv;
2755:
2756: END get_keyflex_str_seg_sql_for_pv;
2757:
2785: l_vs_format_type varchar2(80);
2786: l_appl_name fnd_application_vl.application_name%type;
2787:
2788: BEGIN
2789: hr_utility.set_location('Entering ' ||l_proc,10);
2790:
2791: select meaning into l_allow_dynamic from hr_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y';
2792: select meaning into l_vs_validation_type from fnd_lookups where lookup_type = 'SEG_VAL_TYPES' and lookup_code = 'I';
2793: l_enabled := l_allow_dynamic;
2858: p_kf_segment_tab.last loop
2859:
2860: -- When segment belongs to a structure
2861:
2862: hr_utility.set_location(p_kf_structure_tab(j).structure_code || ' ' ||p_kf_segment_tab(i).structure_code ,10);
2863:
2864: if p_kf_structure_tab(j).appl_short_name = p_kf_segment_tab(i).appl_short_name
2865: and p_kf_structure_tab(j).flex_code = p_kf_segment_tab(i).flex_code
2866: and p_kf_structure_tab(j).structure_code = p_kf_segment_tab(i).structure_code then
3015: end if;
3016: end loop;
3017:
3018: end if;
3019: hr_utility.set_location('Leaving ' || l_proc,20);
3020: return l_str_seg_append_clob_for_pv;
3021:
3022: END get_keyflex_str_seg_dat_for_pv;
3023:
3029: l_ret_clob CLOB;
3030: l_data varchar2(2000);
3031: l_proc varchar2(100) := 'form_xml';
3032: BEGIN
3033: hr_utility.set_location('Entering ' || l_proc,10);
3034: IF p_node_type = 'CS' THEN
3035: l_ret_clob := '<'||p_node||'>';
3036: ELSIF p_node_type = 'CE' THEN
3037: l_ret_clob := '';
3043: l_data := REPLACE (l_data, '"', '"');
3044: END IF;
3045:
3046: l_ret_clob := l_ret_clob || l_data;
3047: hr_utility.set_location('Leaving ' || l_proc,20);
3048: return l_ret_clob;
3049:
3050: END form_xml;
3051:
3067: l_postjoin_sql varchar2(2000);
3068:
3069: BEGIN
3070:
3071: hr_utility.set_location('Entering ' ||l_proc,10);
3072: l_kf_int_hr_clob := get_clob_locator('IntlHRMSSetup');
3073: dbms_lob.createtemporary(l_kf_int_hr_clob,TRUE);
3074:
3075: l_prejoin_sql := ' select terr.TERRITORY_SHORT_NAME Legislation , curr.name Currency, A.Tax_Start_Date, '
3108: l_ret_kf_int_hr_clob := fetch_clob(l_kf_int_hr_clob,'IntlHRMSSetup','IntlHRMSSetups');
3109:
3110: end if;
3111:
3112: hr_utility.set_location('Leaving '|| l_proc,20);
3113: return l_ret_kf_int_hr_clob;
3114: END get_int_hrms_setup_sql;
3115:
3116: FUNCTION get_security_profile_sql (
3128: l_orderby varchar2(200);
3129:
3130: BEGIN
3131:
3132: hr_utility.set_location('Entering ' ||l_proc,10);
3133: l_secprf_clob := get_clob_locator('SecurityProfile');
3134: dbms_lob.createtemporary(l_secprf_clob,TRUE);
3135:
3136: l_orderby := ' order by business_group_name desc ,security_group_name desc ';
3161:
3162: l_ret_secprf_clob := fetch_clob(l_secprf_clob,'SecurityProfile','SecurityProfiles');
3163: end if;
3164:
3165: hr_utility.set_location('Leaving ' ||l_proc,20);
3166: return l_ret_secprf_clob;
3167:
3168: END get_security_profile_sql;
3169:
3181: queryCtx number(8) := 0;
3182:
3183: BEGIN
3184:
3185: hr_utility.set_location('Entering ' ||l_proc,10);
3186: l_org_hier_clob := get_clob_locator('OrgHierarchy');
3187: dbms_lob.createtemporary(l_org_hier_clob,TRUE);
3188:
3189: if p_org_hierarchy_tab.count > 0 THEN
3204: dbms_lob.trim(l_org_hier_clob,length(l_org_hier_clob)-5);
3205: l_ret_org_hier_clob := fetch_clob(l_org_hier_clob,'OrgHierarchy','OrgHierarchies');
3206:
3207: end if;
3208: hr_utility.set_location('Leaving ' ||l_proc,20);
3209: return l_ret_org_hier_clob;
3210:
3211: END get_org_hierarchy_sql;
3212:
3223: queryCtx number(8) := 0;
3224:
3225: BEGIN
3226:
3227: hr_utility.set_location('Entering '||l_proc,10);
3228: l_org_hier_ele_clob := get_clob_locator('OrgHierarchyEle');
3229: dbms_lob.createtemporary(l_org_hier_ele_clob,TRUE);
3230:
3231: if p_org_hierarchy_ele_oc_tab.count > 0 THEN
3262: l_ret_org_hier_ele_clob := fetch_clob(l_org_hier_ele_clob,'OrgHierarchyEle','OrgHierarchyEles');
3263:
3264: end if;
3265:
3266: hr_utility.set_location('Leaving '||l_proc,20);
3267: return l_ret_org_hier_ele_clob;
3268:
3269: END get_org_hierarchy_ele_sql;
3270:
3287: l_le_exists boolean := false;
3288:
3289: BEGIN
3290:
3291: hr_utility.set_location('Entering '||l_proc,10);
3292:
3293: l_org_hier_ele_clob := get_clob_locator('OrgHierarchyEleForPV');
3294: l_temp_clob := get_clob_locator('OrgHierarchyEleForPV');
3295: dbms_lob.createtemporary(l_org_hier_ele_clob,TRUE);
3298:
3299: l_parent_org_name := ' SELECT ' ||
3300: '''' || REPLACE ( p_org_hierarchy_ele_oc_tab(0).parent_organization_name , '''', '''''') ||''''||' parent_organization_name';
3301:
3302: --hr_utility.set_location('parent_organization_name : ' ||p_org_hierarchy_ele_oc_tab(0).parent_organization_name,6000);
3303:
3304: for i in p_org_hierarchy_ele_oc_tab.first ..
3305: p_org_hierarchy_ele_oc_tab.last loop
3306:
3306:
3307: l_temp_sql := l_parent_org_name || ',' ||
3308: '''' ||REPLACE ( p_org_hierarchy_ele_oc_tab(i).child_organization_name , '''', '''''') ||''''||' oc_name';
3309:
3310: --hr_utility.set_location('oc_name : ' ||p_org_hierarchy_ele_oc_tab(i).child_organization_name,6000);
3311:
3312: if p_org_hierarchy_ele_le_tab.count > 0 THEN
3313: for j in p_org_hierarchy_ele_le_tab.first ..
3314: p_org_hierarchy_ele_le_tab.last loop
3317: if p_org_hierarchy_ele_oc_tab(i).child_organization_name = p_org_hierarchy_ele_le_tab(j).parent_organization_name THEN
3318:
3319: l_temp_sql := l_temp_sql || ',' ||
3320: '''' ||REPLACE ( p_org_hierarchy_ele_le_tab(j).child_organization_name , '''', '''''') ||''''||' le_name';
3321: --hr_utility.set_location('le_name : ' ||p_org_hierarchy_ele_le_tab(j).child_organization_name,6000);
3322: end if;
3323: end loop;
3324: end if;
3325: l_temp_sql := l_temp_sql || ' FROM DUAL';
3353: l_org_hier_append_clob := l_org_hier_append_clob || fetch_clob(l_org_hier_ele_clob,'OrgHierarchyEleForPV','OrgHierarchyElementsForPV');
3354: end if;
3355: end if;
3356:
3357: hr_utility.set_location('Leaving '||l_proc,20);
3358: return l_org_hier_append_clob;
3359:
3360: END get_org_hier_ele_sql_for_pv;
3361:
3374: l_postjoin_sql varchar2(2000);
3375:
3376: BEGIN
3377:
3378: hr_utility.set_location('Entering ' ||l_proc,10);
3379: l_post_install_clob := get_clob_locator('PostInstall');
3380: dbms_lob.createtemporary(l_post_install_clob,TRUE);
3381:
3382: l_prejoin_sql := ' select decode(A.legislation_code,''BF'',''International'',terr.TERRITORY_SHORT_NAME) Legislation , '
3410: l_ret_post_install_clob := fetch_clob(l_post_install_clob,'PostInstall','PostInstalls');
3411:
3412: end if;
3413:
3414: hr_utility.set_location('Leaving ' || l_proc,20);
3415: return l_ret_post_install_clob;
3416:
3417: END get_post_install_sql;
3418:
3424: l_proc varchar2(73) := g_package || 'fetch_clob';
3425: l_encoding_tag varchar2(100);
3426:
3427: BEGIN
3428: hr_utility.set_location('Entering ' || l_proc,10);
3429: if p_in_clob is not null then
3430:
3431: BEGIN
3432: SELECT tag INTO l_encoding_tag
3438: EXCEPTION
3439: WHEN NO_DATA_FOUND THEN
3440: l_encoding_tag := null;
3441: WHEN OTHERS THEN
3442: hr_utility.raise_error;
3443: END;
3444:
3445: queryCtx := DBMS_XMLQuery.newContext(l_in_clob);
3446:
3451: DBMS_XMLQuery.setRowtag(queryCtx,p_row_tag);
3452: DBMS_XMLQuery.setRowSettag(queryCtx,p_row_set_tag);
3453: l_ret_in_clob := DBMS_XMLQuery.getXML(queryCtx);
3454: end if;
3455: hr_utility.set_location('Leaving ' || l_proc,20);
3456: return l_ret_in_clob;
3457: END fetch_clob;
3458:
3459: END per_ri_config_tech_summary;