[Home] [Help]
PACKAGE BODY: APPS.PER_RI_CONFIG_TECH_SUMMARY
Source
4 g_config_effective_date date := TRUNC(TO_DATE('1951/01/01', 'YYYY/MM/DD'));
1 PACKAGE BODY per_ri_config_tech_summary AS
2 /* $Header: perricts.pkb 120.16 2012/01/19 09:57:47 rpahune ship $*/
3
5 g_config_effective_end_date date := TRUNC(TO_DATE('4712/12/31', 'YYYY/MM/DD'));
6 g_package varchar2(30) := 'per_ri_config_tech_summary.';
7
8
9 FUNCTION get_clob_locator(p_table_name in varchar2)
10 return clob IS
11
12 l_clob_rec per_ri_config_tech_summary.l_clob_rec_type;
13 l_xmldoc_loc clob;
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;
23
24 hr_utility.set_location('Leaving: '|| l_proc, 20);
25
26 return l_xmldoc_loc;
27 EXCEPTION
28 when no_data_found then
29 null;
30 when others then
31 null;
32 END get_clob_locator;
33
34 FUNCTION get_business_grp_sql (p_business_grp_tab in out nocopy per_ri_config_tech_summary.bg_tab)
35 return clob IS
36
37 l_bg_clob clob;
38 l_ret_bg_clob clob;
39 l_temp_sql varchar2(2000);
40 queryCtx number;
41 l_proc varchar2(200) := 'get_business_grp_sql';
42 l_style varchar2(10);
43 i number(8) := 0;
44 l_bg_prejoin_sql varchar2(2000);
45 l_bg_postjoin_sql varchar2(2000);
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
55 -- dbms_lob.writeappend(l_bg_clob,length('<BusinessGroups>'),'<BusinessGroups>');
56 l_bg_prejoin_sql := ' select Effective_Date,terr.TERRITORY_SHORT_NAME CountryName,Short_Name, '
57 ||' lookup1.meaning app_gen_method_val, '
58 ||' lookup2.meaning emp_gen_method_val,lookup3.meaning cwk_gen_method_val, '
59 ||' curr.name CurrencyName , grade_flex_stru_code,group_flex_stru_code,'
60 ||' job_flex_stru_code,cost_flex_stru_code, '
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
70
71 l_temp_sql:= ' SELECT '||
72 '''' || p_business_grp_tab(i).effective_date ||''''||' effective_date ,'||
76 '''' || p_business_grp_tab(i).type ||''''||' type ,'||
73 '''' || p_business_grp_tab(i).language_code ||''''||' language_code ,'||
74 '''' || p_business_grp_tab(i).date_from ||''''||' date_from ,'||
75 '''' || REPLACE(p_business_grp_tab(i).name, '''', '''''') ||''''||' name ,'||
77 '''' || p_business_grp_tab(i).internal_external_flag ||''''||' internal_external_flag ,'||
78 '''' || REPLACE(p_business_grp_tab(i).short_name, '''', '''''') ||''''||' short_name ,'||
79 '''' || p_business_grp_tab(i).emp_gen_method ||''''||' emp_gen_method ,'||
80 '''' || p_business_grp_tab(i).app_gen_method ||''''||' app_gen_method ,'||
81 '''' || p_business_grp_tab(i).cwk_gen_method ||''''||' cwk_gen_method ,'||
82 '''' || p_business_grp_tab(i).legislation_code ||''''||' legislation_code ,'||
83 '''' || p_business_grp_tab(i).currency_code ||''''||' currency_code ,'||
84 '''' || p_business_grp_tab(i).fiscal_year_start ||''''||' fiscal_year_start ,'||
85 '''' || p_business_grp_tab(i).min_work_age ||''''||' min_work_age ,'||
86 '''' || p_business_grp_tab(i).max_work_age ||''''||' max_work_age ,'||
87 '''' || p_business_grp_tab(i).location_code ||''''||' location_code ,'||
88 '''' || p_business_grp_tab(i).grade_flex_stru_code ||''''||' grade_flex_stru_code ,'||
89 '''' || p_business_grp_tab(i).group_flex_stru_code ||''''||' group_flex_stru_code ,'||
90 '''' || p_business_grp_tab(i).job_flex_stru_code ||''''||' job_flex_stru_code ,'||
91 '''' || p_business_grp_tab(i).cost_flex_stru_code ||''''||' cost_flex_stru_code ,'||
92 '''' || p_business_grp_tab(i).position_flex_stru_code ||''''||' position_flex_stru_code ,'||
93 '''' || p_business_grp_tab(i).security_group_name ||''''||' security_group_name ,'||
94 '''' || p_business_grp_tab(i).competence_flex_stru_code||''''||' competence_flex_stru_code '||
95 ' FROM DUAL UNION';
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 , '
105 ||' fnd_currencies_vl curr '
106 ||' where terr.TERRITORY_CODE = X.Legislation_code '
107 ||' and lookup1.lookup_code= app_gen_method '
108 ||' and lookup2.lookup_code = emp_gen_method '
109 ||' and lookup3.lookup_code= cwk_gen_method '
110 ||' and lookup1.lookup_type = ''APL_NUM_GEN_METHOD'' '
111 ||' and lookup2.lookup_type = ''EMP_NUM_GEN_METHOD'' '
112 ||' and lookup3.lookup_type = ''CWK_NUM_GEN_METHOD'' '
113 ||' and curr.currency_code = X.Currency_code '
114 ||' and lookup1.language = userenv(''LANG'')'
115 ||' and lookup2.language = userenv(''LANG'')'
116 ||' and lookup3.language = userenv(''LANG'')'
117 ||' order by CountryName desc' ;
118
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
128
129 FUNCTION get_org_sql ( p_org_ent_tab in out nocopy per_ri_config_tech_summary.org_ent_tab
130 ,p_org_oc_tab in out nocopy per_ri_config_tech_summary.org_oc_tab
131 ,p_org_le_tab in out nocopy per_ri_config_tech_summary.org_le_tab)
132 return clob IS
133
134 l_org_clob clob;
135 l_ret_org_clob clob;
136 l_temp_sql varchar2(2000);
137 queryCtx number;
138 l_proc varchar2(200) := 'get_org_ent_sql';
139 l_style varchar2(10);
140 i number(8) := 0;
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 ';
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
156
157 l_temp_sql:= ' SELECT '||
158 '''' || p_org_ent_tab(i).effective_date ||''''||' effective_date ,'||
159 '''' || p_org_ent_tab(i).date_from ||''''||' date_from ,'||
160 '''' || REPLACE( p_org_ent_tab(i).business_grp_name, '''', '''''') ||''''||' business_grp_name ,'||
161 '''' || REPLACE( p_org_ent_tab(i).name , '''', '''''') ||''''||' name ,'||
162 '''' || p_org_ent_tab(i).location_code ||''''||' location_code ,'||
163 '''' || p_org_ent_tab(i).internal_external_flag ||''''||' internal_external_flag '||
164 ' FROM DUAL UNION';
165
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 ..
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 ,'||
184 '''' || p_org_oc_tab(i).internal_external_flag ||''''||' internal_external_flag '||
181 '''' || REPLACE(p_org_oc_tab(i).business_grp_name , '''', '''''') ||''''||' business_grp_name ,'||
182 '''' || REPLACE(p_org_oc_tab(i).name , '''', '''''') ||''''||' name ,'||
183 '''' || p_org_oc_tab(i).location_code ||''''||' location_code ,'||
185 ' FROM DUAL UNION';
186
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 ..
196 p_org_le_tab.last loop
197
198 l_temp_sql:= ' SELECT '||
199 '''' || p_org_le_tab(i).effective_date ||''''||' effective_date ,'||
200 '''' || p_org_le_tab(i).date_from ||''''||' date_from ,'||
201 '''' || REPLACE(p_org_le_tab(i).business_grp_name , '''', '''''') ||''''||' business_grp_name ,'||
202 '''' || REPLACE (p_org_le_tab(i).name, '''', '''''') ||''''||' name ,'||
203 '''' || p_org_le_tab(i).location_code ||''''||' location_code ,'||
204 '''' || p_org_le_tab(i).internal_external_flag ||''''||' internal_external_flag '||
205 ' FROM DUAL UNION';
206
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');
216 return l_ret_org_clob;
217 end get_org_sql;
218
219 FUNCTION get_org_class_sql ( p_org_ent_class_tab in out nocopy per_ri_config_tech_summary.org_ent_class_tab
220 ,p_org_oc_class_tab in out nocopy per_ri_config_tech_summary.org_oc_class_tab
221 ,p_org_le_class_tab in out nocopy per_ri_config_tech_summary.org_le_class_tab)
222 return clob IS
223
224 l_org_class_clob clob;
225 l_ret_org_class_clob clob;
226 l_temp_sql varchar2(2000);
227 queryCtx number;
228 l_proc varchar2(200) := 'get_org_class_sql';
229 l_style varchar2(10);
230 i number(8) := 0;
231 l_enabled varchar2(8);
232
233
234 l_class_prejoin_sql varchar2(2000);
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);
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,'
250 ||' org_classif_code,'
251 ||' organization_name,'
252 ||' hrlkp.meaning org_classif_val,'
253 ||' ''Yes'' Enabled '
254 ||' from ( ';
255
256 dbms_lob.writeappend(l_org_class_clob,length(l_class_prejoin_sql),l_class_prejoin_sql);
257
258 IF p_org_ent_class_tab.count > 0 THEN
259 for i in p_org_ent_class_tab.first ..
260 p_org_ent_class_tab.last loop
261
262 l_temp_sql:= ' SELECT '||
263 '''' || p_org_ent_class_tab(i).effective_date ||''''||' effective_date ,'||
264 '''' || p_org_ent_class_tab(i).date_from ||''''||' date_from ,'||
265 '''' || REPLACE(p_org_ent_class_tab(i).business_grp_name, '''', '''''') ||''''||' business_grp_name ,'||
266 '''' || p_org_ent_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
267 '''' || REPLACE(p_org_ent_class_tab(i).organization_name, '''', '''''') ||''''||' organization_name '||
268 ' FROM DUAL UNION';
269
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 ..
279 p_org_oc_class_tab.last loop
280
281 l_temp_sql:= ' SELECT '||
282 '''' || p_org_oc_class_tab(i).effective_date ||''''||' effective_date ,'||
283 '''' || p_org_oc_class_tab(i).date_from ||''''||' date_from ,'||
284 '''' || REPLACE(p_org_oc_class_tab(i).business_grp_name, '''', '''''') ||''''||' business_grp_name ,'||
285 '''' || p_org_oc_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
286 '''' || REPLACE(p_org_oc_class_tab(i).organization_name, '''', '''''') ||''''||' organization_name '||
287 ' FROM DUAL UNION';
288
292
289 dbms_lob.writeappend(l_org_class_clob,length(l_temp_sql),l_temp_sql);
290 end loop;
291 end if;
293 hr_utility.set_location('Leaving OC Loop',40);
297 for i in p_org_le_class_tab.first ..
294 hr_utility.set_location('Entering LE Loop',50);
295
296 IF p_org_le_class_tab.count > 0 THEN
298 p_org_le_class_tab.last loop
299
300 l_temp_sql:= ' SELECT '||
301 '''' || p_org_le_class_tab(i).effective_date ||''''||' effective_date ,'||
302 '''' || p_org_le_class_tab(i).date_from ||''''||' date_from ,'||
303 '''' || REPLACE(p_org_le_class_tab(i).business_grp_name, '''', '''''') ||''''||' business_grp_name ,'||
304 '''' || p_org_le_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
305 '''' || REPLACE (p_org_le_class_tab(i).organization_name, '''', '''''') ||''''||' organization_name '||
306 ' FROM DUAL UNION';
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'''
316 ||' and hrlkp.lookup_code = org_classif_code ';
317
318 dbms_lob.writeappend(l_org_class_clob,length(l_class_postjoin_sql),l_class_postjoin_sql);
319
320 l_ret_org_class_clob := fetch_clob(l_org_class_clob,'OrgClassification','OrgClassifications');
321 return l_ret_org_class_clob;
322
323 END get_org_class_sql;
324
325 FUNCTION get_org_class_sql_for_pv ( p_org_ent_tab in per_ri_config_tech_summary.org_ent_tab
326 ,p_org_oc_tab in per_ri_config_tech_summary.org_oc_tab
327 ,p_org_le_tab in per_ri_config_tech_summary.org_le_tab
328 ,p_org_ent_class_tab in per_ri_config_tech_summary.org_ent_class_tab
329 ,p_org_oc_class_tab in per_ri_config_tech_summary.org_oc_class_tab
330 ,p_org_le_class_tab in per_ri_config_tech_summary.org_le_class_tab)
331 return clob IS
332
333 l_org_class_clob_for_pv clob;
334 l_org_class_append_clob_for_pv clob;
335 l_temp_sql varchar2(32000);
336 queryCtx number;
337 l_proc varchar2(200) := 'get_org_class_sql_for_pv';
338 l_style varchar2(10);
339 i number(8) := 0;
340 l_enabled varchar2(8);
341 l_orderby varchar2(200);
342
343 l_org_classif_val varchar2(80);
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');
353 dbms_lob.createtemporary(l_org_class_clob_for_pv,TRUE);
354
355 --For every org find all classifications
356 IF p_org_ent_tab.count > 0 THEN
357 for j in p_org_ent_tab.first ..
358 p_org_ent_tab.last loop
359
360 l_classification_exists := false;
361 l_temp_sql:= ' SELECT '||
362 '''' || p_org_ent_tab(j).effective_date ||''''||' effective_org_date ,'||
363 '''' || p_org_ent_tab(j).date_from ||''''||' org_date_from ,'||
364 '''' || p_org_ent_tab(j).location_code ||''''||' location_code ,'||
365 '''' || p_org_ent_tab(j).internal_external_flag ||''''||' internal_external_flag ';
366
367 IF p_org_ent_class_tab.count > 0 THEN
368 for i in p_org_ent_class_tab.first ..
369 p_org_ent_class_tab.last loop
370
371 if p_org_ent_tab(j).business_grp_name = p_org_ent_class_tab(i).business_grp_name
372 and p_org_ent_tab(j).name = p_org_ent_class_tab(i).organization_name then
373
374 l_org_classif_val := p_org_ent_class_tab(i).org_classif_code;
375 if p_org_ent_class_tab(i).org_classif_code is not null then
376 begin
377 select meaning into l_org_classif_val from hr_lookups where lookup_type= 'ORG_CLASS' and lookup_code = p_org_ent_class_tab(i).org_classif_code;
378 exception
379 when no_data_found then
380 null;
381 end;
382 end if;
383 l_classification_exists := true;
384 l_temp_sql := l_temp_sql || ',' ||
385 '''' || p_org_ent_class_tab(i).effective_date ||''''||' effective_date ,'||
386 '''' || p_org_ent_class_tab(i).date_from ||''''||' date_from ,'||
387 '''' || REPLACE(p_org_ent_class_tab(i).business_grp_name, '''', '''''') ||''''||' business_grp_name ,'||
388 '''' || p_org_ent_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
389 '''' || REPLACE(p_org_ent_class_tab(i).organization_name, '''', '''''') ||''''||' organization_name, '||
390 '''' || l_enabled ||''''||' enabled, '||
391 '''' || l_org_classif_val ||''''||' org_classif_val ';
392
393 end if;
394 end loop;
395 l_temp_sql := l_temp_sql || ' FROM DUAL';
396 if l_classification_exists then
397 dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
398 l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
399 dbms_lob.trim(l_org_class_clob_for_pv,0);
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
409 for j in p_org_oc_tab.first ..
410 p_org_oc_tab.last loop
411
415 '''' || p_org_oc_tab(j).location_code ||''''||' location_code ,'||
412 l_temp_sql:= ' SELECT '||
413 '''' || p_org_oc_tab(j).effective_date ||''''||' effective_org_date ,'||
414 '''' || p_org_oc_tab(j).date_from ||''''||' org_date_from ,'||
416 '''' || p_org_oc_tab(j).internal_external_flag ||''''||' internal_external_flag ';
417
418 IF p_org_oc_class_tab.count > 0 THEN
419 for i in p_org_oc_class_tab.first ..
420 p_org_oc_class_tab.last loop
421
422 if p_org_oc_tab(j).business_grp_name = p_org_oc_class_tab(i).business_grp_name
423 and p_org_oc_tab(j).name = p_org_oc_class_tab(i).organization_name then
424
425 l_org_classif_val := p_org_oc_class_tab(i).org_classif_code;
429 exception
426 if p_org_oc_class_tab(i).org_classif_code is not null then
427 begin
428 select meaning into l_org_classif_val from hr_lookups where lookup_type= 'ORG_CLASS' and lookup_code = p_org_oc_class_tab(i).org_classif_code;
430 when no_data_found then
431 null;
432 end;
433 end if;
437 '''' || p_org_oc_class_tab(i).effective_date ||''''||' effective_date ,'||
434 l_classification_exists := true;
435
436 l_temp_sql := l_temp_sql || ',' ||
438 '''' || p_org_oc_class_tab(i).date_from ||''''||' date_from ,'||
439 '''' || REPLACE(p_org_oc_class_tab(i).business_grp_name, '''', '''''') ||''''||' business_grp_name ,'||
440 '''' || p_org_oc_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
441 '''' || REPLACE(p_org_oc_class_tab(i).organization_name, '''', '''''') ||''''||' organization_name, '||
442 '''' || l_enabled ||''''||' enabled, '||
443 '''' || l_org_classif_val ||''''||' org_classif_val ';
444
445 end if;
446 end loop;
447
448 l_temp_sql := l_temp_sql || ' FROM DUAL';
449 if l_classification_exists then
450 dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
451 l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
452 dbms_lob.trim(l_org_class_clob_for_pv,0);
453 end if;
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
463 for j in p_org_le_tab.first ..
464 p_org_le_tab.last loop
465
466 l_temp_sql:= ' SELECT '||
467 '''' || p_org_le_tab(j).effective_date ||''''||' effective_org_date ,'||
468 '''' || p_org_le_tab(j).date_from ||''''||' org_date_from ,'||
469 '''' || p_org_le_tab(j).location_code ||''''||' location_code ,'||
470 '''' || p_org_le_tab(j).internal_external_flag ||''''||' internal_external_flag ';
471
472 IF p_org_le_class_tab.count > 0 THEN
473 for i in p_org_le_class_tab.first ..
474 p_org_le_class_tab.last loop
475
476 if p_org_le_tab(j).business_grp_name = p_org_le_class_tab(i).business_grp_name
477 and p_org_le_tab(j).name = p_org_le_class_tab(i).organization_name then
478
479 l_org_classif_val := p_org_le_class_tab(i).org_classif_code;
480
481 if p_org_le_class_tab(i).org_classif_code is not null then
482 begin
483 select meaning into l_org_classif_val from hr_lookups where lookup_type= 'ORG_CLASS' and lookup_code = p_org_le_class_tab(i).org_classif_code;
484 exception
485 when no_data_found then
486 null;
487 end;
488
489 end if;
490 l_classification_exists := true;
491 l_temp_sql := l_temp_sql || ',' ||
492 '''' || p_org_le_class_tab(i).effective_date ||''''||' effective_date,'||
493 '''' || p_org_le_class_tab(i).date_from ||''''||' date_from ,'||
494 '''' || REPLACE (p_org_le_class_tab(i).business_grp_name , '''', '''''') ||''''||' business_grp_name ,'||
495 '''' || p_org_le_class_tab(i).org_classif_code ||''''||' org_classif_code ,'||
496 '''' || REPLACE (p_org_le_class_tab(i).organization_name , '''', '''''') ||''''||' organization_name, '||
497 '''' || l_enabled ||''''||' enabled, '||
498 '''' || l_org_classif_val ||''''||' org_classif_val ';
499 end if;
500 end loop;
501 l_temp_sql := l_temp_sql || ' FROM DUAL';
502 if l_classification_exists then
503 dbms_lob.writeappend(l_org_class_clob_for_pv,length(l_temp_sql),l_temp_sql);
504 l_org_class_append_clob_for_pv := l_org_class_append_clob_for_pv || fetch_clob(l_org_class_clob_for_pv,'OrgClassificationForPV','OrgClassificationsForPV');
505 dbms_lob.trim(l_org_class_clob_for_pv,0);
506 end if;
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
516 FUNCTION get_locations_sql (p_location_tab in out nocopy per_ri_config_tech_summary.location_tab)
517 return clob IS
518
519 l_user_column_name varchar2(200);
520 l_proc varchar2(72) := g_package || 'get_locations_sql';
521 i number(8) := 0;
522 l_ret_location_clob clob;
523 l_temp_sql varchar2(2000);
524 l_location_clob clob;
525 queryCtx number(8) := 0;
526 l_style varchar2(10);
527 l_prejoin_sql varchar2(2000);
528 l_postjoin_sql varchar2(2000);
529 l_orderby varchar2(200);
530 l_style_val varchar2(30);
531 l_country varchar2(80);
532
533 cursor csr_get_prm(cp_style in varchar2
534 ,cp_app_col_name in varchar2) IS
535
536 select end_user_column_name
537 from fnd_descr_flex_col_usage_vl
538 where descriptive_flexfield_name= 'Address Location'
539 and descriptive_flex_context_code = cp_style
540 and application_column_name = cp_app_col_name;
541
542 cursor csr_get_style(cp_style in varchar2) IS
543 select terr.territory_short_name Address_Style
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 '
553 ||' from ( ';
557 if p_location_tab.count > 0 THEN
554 l_postjoin_sql := ') A, fnd_territories_vl terr where terr.territory_code(+) = A.style ';
555 l_orderby := ' order by style desc ,location_code desc ';
556
558 for i in p_location_tab.first ..
559 p_location_tab.last loop
560
561 l_style := p_location_tab(i).style;
562
563 if(l_style = 'GENERIC')
564 then
565 l_style_val := 'Generic';
566 else
567 open csr_get_style(l_style);
568 fetch csr_get_style into l_style_val;
569 close csr_get_style;
570 end if;
571
572 open csr_get_style(p_location_tab(i).country);
573 fetch csr_get_style into l_country;
574 if csr_get_style%NOTFOUND then
578
575 l_country := p_location_tab(i).country;
576 end if;
577 close csr_get_style;
579 l_temp_sql:=' SELECT '||
580 '''' || replace(p_location_tab(i).location_code,'''','''''') ||''''||' LOCATION_CODE ,'||
581 '''' || replace(p_location_tab(i).description,'''','''''') ||''''||' DESCRIPTION ,'||
582 '''' || l_style_val ||''''||' STYLE ,'||
583 '''' || replace(p_location_tab(i).address_line_1,'''','''''') ||''''||' ADDRESS_LINE_1 ,'||
584 '''' || per_ri_config_utilities.get_location_prompt(l_style,'ADDRESS_LINE_1') ||''''||' ADDRESS_LINE1_PROMPT ,'||
585 '''' || nvl(replace(p_location_tab(i).address_line_2,'''',''''''),' ') ||'''' ||' ADDRESS_LINE_2 ,'||
586 '''' || per_ri_config_utilities.get_location_prompt(l_style,'ADDRESS_LINE_2') ||''''||' ADDRESS_LINE2_PROMPT ,'||
587 '''' || nvl(replace(p_location_tab(i).address_line_3,'''',''''''),' ') ||''''||' ADDRESS_LINE_3 ,'||
588 '''' || per_ri_config_utilities.get_location_prompt(l_style,'ADDRESS_LINE_3') ||''''||' ADDRESS_LINE3_PROMPT ,'||
589 '''' || nvl(replace(p_location_tab(i).town_or_city,'''',''''''),' ') ||''''||' TOWN_OR_CITY ,'||
590 '''' || per_ri_config_utilities.get_location_prompt(l_style,'TOWN_OR_CITY') ||''''||' TOWN_OR_CITY_PROMPT ,'||
591 '''' || nvl(replace(l_country,'''',''''''),' ') ||''''||' COUNTRY ,'||
592 '''' || per_ri_config_utilities.get_location_prompt(l_style,'COUNTRY') ||''''||' COUNTRY_PROMPT ,'||
593 '''' || nvl(replace(p_location_tab(i).postal_code,'''',''''''),' ') ||''''||' POSTAL_CODE ,'||
594 '''' || per_ri_config_utilities.get_location_prompt(l_style,'POSTAL_CODE') ||''''||' POSTAL_CODE_PROMPT ,'||
595 '''' || nvl(replace(p_location_tab(i).region_1,'''',''''''),' ') ||''''||' REGION1 ,'||
596 '''' || per_ri_config_utilities.get_location_prompt(l_style,'REGION_1') ||''''||' REGION1_PROMPT ,'||
597 '''' || nvl(replace(p_location_tab(i).region_2,'''',''''''),' ') ||''''||' REGION2 ,'||
598 '''' || per_ri_config_utilities.get_location_prompt(l_style,'REGION_2') ||''''||' REGION2_PROMPT ,'||
599 '''' || nvl(replace(p_location_tab(i).region_3,'''',''''''),' ') ||''''||' REGION3 ,'||
600 '''' || per_ri_config_utilities.get_location_prompt(l_style,'REGION_3') ||''''||' REGION3_PROMPT ,'||
601 '''' || nvl(replace(p_location_tab(i).telephone_number_1,'''',''''''),' ')||''''||' TELEPHONE_NUMBER_1 ,'||
602 '''' || per_ri_config_utilities.get_location_prompt(l_style,'TELEPHONE_NUMBER_1')||''''||' TELEPHONE_NUMBER1_PROMPT ,'||
603 '''' || nvl(replace(p_location_tab(i).telephone_number_2,'''',''''''),' ')||''''||' TELEPHONE_NUMBER_2 ,'||
604 '''' || per_ri_config_utilities.get_location_prompt(l_style,'TELEPHONE_NUMBER_2')||''''||' TELEPHONE_NUMBER2_PROMPT ,'||
605 '''' || nvl(replace(p_location_tab(i).telephone_number_3,'''',''''''),' ')||''''||' TELEPHONE_NUMBER_3 ,'||
606 '''' || per_ri_config_utilities.get_location_prompt(l_style,'TELEPHONE_NUMBER_3')||''''||' TELEPHONE_NUMBER3_PROMPT ,'||
607 '''' || nvl(replace(p_location_tab(i).loc_information13,'''',''''''),' ')||''''||' LOC_INFORMATION13 ,'||
608 '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION13') ||''''||' LOC_INFORMATION13_PROMPT ,'||
609 '''' || nvl(replace(p_location_tab(i).loc_information14,'''',''''''),' ')||''''||' LOC_INFORMATION14 ,'||
610 '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION14') ||''''||' LOC_INFORMATION14_PROMPT ,'||
611 '''' || nvl(replace(p_location_tab(i).loc_information15,'''',''''''),' ')||''''||' LOC_INFORMATION15 ,'||
612 '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION15') ||''''||' LOC_INFORMATION15_PROMPT ,'||
613 '''' || nvl(replace(p_location_tab(i).loc_information16,'''',''''''),' ')||''''||' LOC_INFORMATION16 ,'||
614 '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION16') ||''''||' LOC_INFORMATION16_PROMPT ,'||
615 '''' || nvl(replace(p_location_tab(i).loc_information17,'''',''''''),' ')||''''||' LOC_INFORMATION17 ,'||
619 '''' || nvl(replace(p_location_tab(i).loc_information19,'''',''''''),' ')||''''||' LOC_INFORMATION19 ,'||
616 '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION17')||''''||' LOC_INFORMATION17_PROMPT ,'||
617 '''' || nvl(replace(p_location_tab(i).loc_information18,'''',''''''),' ')||''''||' LOC_INFORMATION18 ,'||
618 '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION18')||''''||' LOC_INFORMATION18_PROMPT ,'||
620 '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION19')||''''||' LOC_INFORMATION19_PROMPT ,'||
621 '''' || nvl(replace(p_location_tab(i).loc_information20,'''',''''''),' ')||''''||' LOC_INFORMATION20 ,'||
622 '''' || per_ri_config_utilities.get_location_prompt(l_style,'LOC_INFORMATION20')||''''||' LOC_INFORMATION20_PROMPT '||
623 ' FROM DUAL UNION';
624
625 dbms_lob.writeappend(l_location_clob,length(l_temp_sql),l_temp_sql);
626
627 end loop;
628 end if;
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
638 FUNCTION get_user_sql (p_user_tab in out nocopy per_ri_config_tech_summary.user_tab)
639 return clob IS
640
641 l_user_column_name varchar2(200);
642 l_proc varchar2(72) := g_package || 'get_user_sql';
643 i number(8) := 0;
644 l_ret_user_clob clob;
645 l_temp_sql varchar2(2000);
646 l_user_clob clob;
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
656 for i in p_user_tab.first ..
657 p_user_tab.last loop
658
659 l_temp_sql:= ' SELECT ' ||
660 '''' || p_user_tab(i).user_name ||''''||' user_name ,' ||
661 '''' || p_user_tab(i).start_date ||''''||' start_date ,'||
662 '''' || p_user_tab(i).description ||''''||' description '||
663 ' FROM DUAL UNION';
664
665 dbms_lob.writeappend(l_user_clob,length(l_temp_sql),l_temp_sql);
666 end loop;
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;
676
673
674 END get_user_sql;
675
677 FUNCTION get_resp_sql (
678 p_resp_tab in out nocopy per_ri_config_tech_summary.resp_tab
679 ,p_hrms_resp_tab in out nocopy per_ri_config_tech_summary.hrms_resp_tab
680 ,p_hrms_misc_resp_tab in out nocopy per_ri_config_tech_summary.hrms_resp_tab
681 )
682 return clob IS
683
684 l_user_column_name varchar2(200);
685 l_proc varchar2(72) := g_package || 'get_resp_sql';
686 i number(8) := 0;
687 l_ret_resp_clob clob;
688 l_temp_sql varchar2(2000);
689 l_resp_clob clob;
690 queryCtx number(8) := 0;
691 l_prejoin_sql varchar2(2000);
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 ,'
701 ||' decode(A.security_group,''STANDARD'',''Standard'',A.security_group) SecurityGroupName, '
702 ||' app.APPLICATION_NAME ApplicationName, A.start_date from ( ';
703
704 dbms_lob.writeappend(l_resp_clob,length(l_prejoin_sql),l_prejoin_sql);
705
706 if p_resp_tab.count > 0 THEN
707 for i in p_resp_tab.first ..
708 p_resp_tab.last loop
709
710 l_temp_sql:= ' SELECT ' ||
711 '''' || p_resp_tab(i).user_name ||''''||' user_name ,' ||
712 '''' || p_resp_tab(i).resp_key ||''''||' resp_key ,' ||
713 '''' || p_resp_tab(i).app_short_name ||''''||' app_short_name, '||
714 '''' || p_resp_tab(i).security_group ||''''||' security_group,'||
715 '''' || p_resp_tab(i).owner ||''''||' owner,' ||
716 '''' || g_config_effective_date ||''''||' start_date,'||
717 '''' || p_resp_tab(i).end_date ||''''||' end_date,' ||
718 '''' || p_resp_tab(i).description ||''''||' description' ||
719 ' FROM DUAL UNION';
720
724
721 dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);
722 end loop;
723 end if;
725 if p_hrms_resp_tab.count > 0 THEN
726 for i in p_hrms_resp_tab.first ..
727 p_hrms_resp_tab.last loop
728
729 l_temp_sql:= ' SELECT ' ||
730 '''' || p_hrms_resp_tab(i).user_name ||''''||' user_name ,' ||
731 '''' || p_hrms_resp_tab(i).resp_key ||''''||' resp_key ,' ||
732 '''' || nvl(p_hrms_resp_tab(i).app_short_name,'PER') ||''''||' app_short_name, ' ||
733 '''' || p_hrms_resp_tab(i).security_group ||''''||' security_group,' ||
734 '''' || p_hrms_resp_tab(i).owner ||''''||' owner,' ||
735 '''' || g_config_effective_date ||''''||' start_date,' ||
736 '''' || p_hrms_resp_tab(i).end_date ||''''||' end_date,' ||
737 '''' || p_hrms_resp_tab(i).description ||''''||' description' ||
738 ' FROM DUAL UNION';
739
740 dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);
741 end loop;
742 end if;
743
744 /*if p_hrms_misc_resp_tab.count > 0 THEN
745 for i in p_hrms_misc_resp_tab.first ..
746 p_hrms_misc_resp_tab.last loop
747
748 l_temp_sql:= ' SELECT ' ||
749 '''' || p_hrms_misc_resp_tab(i).user_name ||''''||' user_name ,' ||
750 '''' || p_hrms_misc_resp_tab(i).resp_key ||''''||' resp_key ,' ||
751 '''' || nvl(p_hrms_misc_resp_tab(i).app_short_name,'PER') ||''''||' app_short_name, ' ||
752 '''' || p_hrms_misc_resp_tab(i).security_group ||''''||' security_group,' ||
753 '''' || p_hrms_misc_resp_tab(i).owner ||''''||' owner,' ||
754 '''' || g_config_effective_date ||''''||' start_date,' ||
755 '''' || p_hrms_misc_resp_tab(i).end_date ||''''||' end_date,' ||
756 '''' || p_hrms_misc_resp_tab(i).description ||''''||' description' ||
757 ' FROM DUAL UNION';
758
759 dbms_lob.writeappend(l_resp_clob,length(l_temp_sql),l_temp_sql);
760 end loop;
761 end if;*/
762
763 dbms_lob.trim(l_resp_clob,length(l_resp_clob)-5);
764 l_postjoin_sql := ' )A ,fnd_responsibility_vl resp,FND_SECURITY_GROUPS_VL secgrp ,fnd_application_vl app '
765 ||' where '
766 ||' A.app_short_name = app.application_short_name '
767 ||' order by ResponsibilityName ,ApplicationName , SecurityGroupName ';
768
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
778 --For site level profiles.
779 FUNCTION get_profile_sql (
780 p_profile_tab in out nocopy per_ri_config_tech_summary.profile_tab,
781 p_profile_dpe_ent_tab in out nocopy per_ri_config_tech_summary.profile_dpe_ent_tab
782 )
783 return clob IS
784
785 l_user_column_name varchar2(200);
786 l_proc varchar2(72) := g_package || 'get_profile_sql';
787 i number(8) := 0;
788 l_ret_profile_clob clob;
789 l_temp_sql varchar2(2000);
790 l_profile_clob clob;
791 queryCtx number(8) := 0;
792 l_prejoin_sql varchar2(2000);
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,'||
802 ' decode(A.profile_option_value, ''Y'',''Yes'',''N'',''No'',A.profile_option_value) ProfileValue, '||
803 ' fapp.Application_Name, A.level_value from (';
804
805 l_postjoin_sql :=' )A, FND_PROFILE_OPTIONS_VL fprf '||
806 ' ,fnd_application_vl fapp '||
807 ' where fprf.profile_option_name = A.profile_name '||
808 ' and fapp.APPLICATION_SHORT_NAME = A.level_value_app '||
809 ' and level_x = ''1001'' '|| --only for Site level profiles
810 ' order by UserProfileName desc';
811
812 dbms_lob.writeappend(l_profile_clob,length(l_prejoin_sql),l_prejoin_sql);
813
814 if p_profile_tab.count > 0 THEN
815 for i in p_profile_tab.first ..
816 p_profile_tab.last loop
817
818 --Hardcoding for now
819 if p_profile_tab(i).profile_name = 'BIS_WORKFORCE_MEASUREMENT_TYPE'
820 and p_profile_tab(i).profile_option_value = 'HEAD' then
821 select meaning into l_translated_prof_opt_value from hr_lookups where lookup_type = 'BUDGET_MEASUREMENT_TYPE' and lookup_code = 'HEAD';
822 elsif p_profile_tab(i).profile_name = 'HR_GENERATE_GL_ORGS'
823 and p_profile_tab(i).profile_option_value = 'CCHR' then
824 select meaning into l_translated_prof_opt_value from hr_lookups where lookup_type like 'HR_GEN_GL_ORG' and lookup_code like 'CCHR';
825
826 else
827 l_translated_prof_opt_value := p_profile_tab(i).profile_option_value;
828 end if;
829
830 l_temp_sql:= ' SELECT ' ||
831 '''' || p_profile_tab(i).level ||''''||' level_x,' ||
835 '''' || l_translated_prof_opt_value ||''''||' profile_option_value ' ||
832 '''' || p_profile_tab(i).level_value ||''''||' level_value,' ||
833 '''' || p_profile_tab(i).level_value_app ||''''||' level_value_app,' ||
834 '''' || p_profile_tab(i).profile_name ||''''||' profile_name,' ||
836 ' FROM DUAL UNION';
837
838 dbms_lob.writeappend(l_profile_clob,length(l_temp_sql),l_temp_sql);
839 end loop;
840 end if;
841
842 if p_profile_dpe_ent_tab.count > 0 THEN
843 for i in p_profile_dpe_ent_tab.first ..
844 p_profile_dpe_ent_tab.last loop
845
846 --Hardcoding for now
847 if p_profile_tab(i).profile_name = 'BIS_WORKFORCE_MEASUREMENT_TYPE'
848 and p_profile_tab(i).profile_option_value = 'HEAD' then
849 select meaning into l_translated_prof_opt_value from hr_lookups where lookup_type = 'BUDGET_MEASUREMENT_TYPE' and lookup_code = 'HEAD';
850 else
851 l_translated_prof_opt_value := p_profile_tab(i).profile_option_value;
852 end if;
853
854
855 l_temp_sql:= ' SELECT ' ||
856 '''' || p_profile_dpe_ent_tab(i).level ||''''||' level_x,' ||
857 '''' || p_profile_dpe_ent_tab(i).level_value ||''''||' level_value,' ||
858 '''' || p_profile_dpe_ent_tab(i).level_value_app ||''''||' level_value_app,' ||
859 '''' || p_profile_dpe_ent_tab(i).profile_name ||''''||' profile_name,' ||
860 '''' || l_translated_prof_opt_value ||''''||' profile_option_value ' ||
861 ' FROM DUAL UNION';
862
863 dbms_lob.writeappend(l_profile_clob,length(l_temp_sql),l_temp_sql);
864 end loop;
865 end if;
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
875
876
877 FUNCTION get_profile_apps_sql (
878 p_profile_apps_tab in out nocopy per_ri_config_tech_summary.profile_apps_tab
879 )
880 return clob IS
881
882 l_user_column_name varchar2(200);
883 l_proc varchar2(72) := g_package || 'get_profile_apps_sql';
884 i number(8) := 0;
885 l_ret_profile_apps_clob clob;
886 l_temp_sql varchar2(2000);
887 l_profile_apps_clob clob;
888 queryCtx number(8) := 0;
889 l_prejoin_sql varchar2(2000);
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,'||
899 ' decode(A.profile_option_value, ''Y'',''Yes'',''N'',''No'',A.profile_option_value) ProfileValue, '||
900 ' fapp.Application_Name, A.level_value from (';
901
902 l_postjoin_sql :=' )A, FND_PROFILE_OPTIONS_VL fprf '||
903 ' ,fnd_application_vl fapp '||
904 ' where fprf.profile_option_name = A.profile_name '||
905 ' and fapp.APPLICATION_SHORT_NAME = A.level_value_app ';
906
907 dbms_lob.writeappend(l_profile_apps_clob,length(l_prejoin_sql),l_prejoin_sql);
908
909 if p_profile_apps_tab.count > 0 THEN
910 for i in p_profile_apps_tab.first ..
911 p_profile_apps_tab.last loop
912
913 l_temp_sql:= ' SELECT ' ||
914 '''' || p_profile_apps_tab(i).level ||''''||' level_x,' ||
915 '''' || p_profile_apps_tab(i).level_value ||''''||' level_value,' ||
916 '''' || p_profile_apps_tab(i).level_value_app ||''''||' level_value_app,' ||
917 '''' || p_profile_apps_tab(i).profile_name ||''''||' profile_name,' ||
918 '''' || p_profile_apps_tab(i).profile_option_value ||''''||' profile_option_value ' ||
919 ' FROM DUAL UNION';
920
921 dbms_lob.writeappend(l_profile_apps_clob,length(l_temp_sql),l_temp_sql);
922 end loop;
923 end if;
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
933 FUNCTION get_profile_resp_sql (
934 p_profile_resp_tab in out nocopy per_ri_config_tech_summary.profile_resp_tab
935 )
936 return clob IS
937
938 l_user_column_name varchar2(200);
939 l_proc varchar2(72) := g_package || 'get_profile_resp_sql';
940 i number(8) := 0;
941 l_ret_profile_resp_clob clob;
942 l_temp_sql varchar2(2000);
943 l_profile_resp_clob clob;
944 queryCtx number(8) := 0;
945 l_prejoin_sql varchar2(2000);
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);
956 ' fapp.Application_Name, A.level_value from (';
953
954 l_prejoin_sql := ' select A.level_x , fprf.user_profile_option_name UserProfileName,'||
955 ' decode(A.profile_option_value, ''Y'',''Yes'',''N'',''No'',A.profile_option_value) ProfileValue, '||
957
958 l_postjoin_sql :=' )A, FND_PROFILE_OPTIONS_VL fprf '||
959 ' ,fnd_application_vl fapp '||
960 ' where fprf.profile_option_name = A.profile_name '||
961 ' and fapp.APPLICATION_SHORT_NAME = A.level_value_app ';
962
963 dbms_lob.writeappend(l_profile_resp_clob,length(l_prejoin_sql),l_prejoin_sql);
964
965 if p_profile_resp_tab.count > 0 THEN
966 for i in p_profile_resp_tab.first ..
967 p_profile_resp_tab.last loop
968
969 if p_profile_resp_tab(i).profile_name = 'HR_USER_TYPE' then
970 select meaning into l_prof_opt_value from fnd_lookup_values where lookup_type = 'HR_USER_TYPE' and language = userenv('LANG') and lookup_code like p_profile_resp_tab(i).profile_option_value;
971 else
972 l_prof_opt_value := p_profile_resp_tab(i).profile_option_value;
973 end if;
974
975 l_temp_sql:= ' SELECT ' ||
976 '''' || p_profile_resp_tab(i).level ||''''||' level_x,' ||
977 '''' || p_profile_resp_tab(i).level_value ||''''||' level_value,' ||
978 '''' || p_profile_resp_tab(i).level_value_app ||''''||' level_value_app,' ||
979 '''' || p_profile_resp_tab(i).profile_name ||''''||' profile_name,' ||
980 '''' || l_prof_opt_value ||''''||' profile_option_value ' ||
981 ' FROM DUAL UNION';
982
983
984 dbms_lob.writeappend(l_profile_resp_clob,length(l_temp_sql),l_temp_sql);
985 end loop;
986 end if;
987
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
997 FUNCTION get_keyflex_structure_sql
998 (
999 p_kf_job_tab in out nocopy per_ri_config_tech_summary.kf_job_tab,
1000 p_kf_job_rv_tab in out nocopy per_ri_config_tech_summary.kf_job_rv_tab,
1001 p_kf_job_no_rv_tab in out nocopy per_ri_config_tech_summary.kf_job_no_rv_tab,
1002 p_kf_pos_tab in out nocopy per_ri_config_tech_summary.kf_pos_tab,
1003 p_kf_pos_rv_tab in out nocopy per_ri_config_tech_summary.kf_pos_rv_tab,
1004 p_kf_pos_no_rv_tab in out nocopy per_ri_config_tech_summary.kf_pos_no_rv_tab,
1005 p_kf_grd_tab in out nocopy per_ri_config_tech_summary.kf_grd_tab,
1006 p_kf_grd_rv_tab in out nocopy per_ri_config_tech_summary.kf_grd_rv_tab,
1007 p_kf_grd_no_rv_tab in out nocopy per_ri_config_tech_summary.kf_grd_no_rv_tab,
1008 p_kf_cmp_tab in out nocopy per_ri_config_tech_summary.kf_cmp_tab,
1009 p_kf_grp_tab in out nocopy per_ri_config_tech_summary.kf_grp_tab,
1010 p_kf_cost_tab in out nocopy per_ri_config_tech_summary.kf_cost_tab,
1011 p_kf_job_str_clob out nocopy clob,
1012 p_kf_job_rv_str_clob out nocopy clob,
1013 p_kf_job_no_rv_str_clob out nocopy clob,
1014 p_kf_pos_str_clob out nocopy clob,
1015 p_kf_pos_rv_str_clob out nocopy clob,
1016 p_kf_pos_no_rv_str_clob out nocopy clob,
1017 p_kf_grd_str_clob out nocopy clob,
1018 p_kf_cmp_str_clob out nocopy clob,
1019 p_kf_grp_str_clob out nocopy clob,
1020 p_kf_cost_str_clob out nocopy clob
1021 )
1022 return clob IS
1023 l_proc varchar2(72) := g_package || 'get_keyflex_structure_sql';
1024 i number(8) := 0;
1025 l_ret_kf_str_clob clob;
1026 l_temp_sql varchar2(2000);
1027 l_kf_str_clob clob;
1028 queryCtx number(8) := 0;
1029 l_prejoin_sql varchar2(2000);
1030 l_postjoin_sql varchar2(2000);
1031 l_allow_dynamic varchar2(8) := 'Y';
1032 l_segment_separator varchar2(20) := 'Period (.)';
1033 l_enabled varchar2(8) := 'Y';
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;
1043
1044 l_kf_str_clob := get_clob_locator('JobKeyFlexStruct');
1045 dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1046
1047 l_prejoin_sql := ' select A.appl_short_name , A.flex_code ,A.structure_code,A.structure_title,A.description, A.allow_dynamic_inserts,A.segment_separator,A.enabled,A.freeze_flex_def,'||
1048 ' fapp.Application_Name from( ';
1049
1050 l_postjoin_sql := ' )A, fnd_application_vl fapp '||
1051 ' where fapp.APPLICATION_SHORT_NAME = A.appl_short_name '||
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
1061 l_temp_sql:= ' SELECT ' ||
1065 '''' || p_kf_job_tab(i).structure_title ||''''||' structure_title,' ||
1062 '''' || p_kf_job_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1063 '''' || p_kf_job_tab(i).flex_code ||''''||' flex_code,' ||
1064 '''' || p_kf_job_tab(i).structure_code ||''''||' structure_code,' ||
1066 '''' || p_kf_job_tab(i).description ||''''||' description, ' ||
1067 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1068 '''' || l_segment_separator ||''''||' segment_separator,' ||
1069 '''' || l_freeze_flex_def ||''''||' freeze_flex_def, ' ||
1070 '''' || l_enabled ||''''||' enabled ' ||
1071 ' FROM DUAL UNION';
1072
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
1082
1083 l_temp_sql:= ' SELECT ' ||
1084 '''' || p_kf_job_rv_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1085 '''' || p_kf_job_rv_tab(i).flex_code ||''''||' flex_code,' ||
1086 '''' || p_kf_job_rv_tab(i).structure_code ||''''||' structure_code,' ||
1087 '''' || p_kf_job_rv_tab(i).structure_title ||''''||' structure_title,' ||
1088 '''' || p_kf_job_rv_tab(i).description ||''''||' description, ' ||
1089 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1090 '''' || l_segment_separator ||''''||' segment_separator,' ||
1091 '''' || l_freeze_flex_def ||''''||' freeze_flex_def, ' ||
1092 '''' || l_enabled ||''''||' enabled ' ||
1093 ' FROM DUAL UNION';
1094
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
1104
1105 l_temp_sql:= ' SELECT ' ||
1106 '''' || p_kf_job_no_rv_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1107 '''' || p_kf_job_no_rv_tab(i).flex_code ||''''||' flex_code,' ||
1108 '''' || p_kf_job_no_rv_tab(i).structure_code ||''''||' structure_code,' ||
1109 '''' || p_kf_job_no_rv_tab(i).structure_title ||''''||' structure_title,' ||
1110 '''' || p_kf_job_no_rv_tab(i).description ||''''||' description, ' ||
1111 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1112 '''' || l_segment_separator ||''''||' segment_separator,' ||
1113 '''' || l_freeze_flex_def ||''''||' freeze_flex_def, ' ||
1114 '''' || l_enabled ||''''||' enabled ' ||
1115 ' FROM DUAL UNION';
1116
1117 dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1118 end loop;
1119 end if;
1120
1121 dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1122 dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1123
1124 if p_kf_job_tab.count > 0 or p_kf_job_rv_tab.count > 0 or p_kf_job_no_rv_tab.count > 0 THEN
1125 p_kf_job_str_clob := fetch_clob(l_kf_str_clob,'JobKeyFlexStruct','JobKeyFlexStructures');
1126 end if;
1127
1128 l_kf_str_clob := null;
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
1138
1139 l_temp_sql:= ' SELECT ' ||
1140 '''' || p_kf_pos_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1141 '''' || p_kf_pos_tab(i).flex_code ||''''||' flex_code,' ||
1142 '''' || p_kf_pos_tab(i).structure_code ||''''||' structure_code,' ||
1143 '''' || p_kf_pos_tab(i).structure_title ||''''||' structure_title,' ||
1144 '''' || p_kf_pos_tab(i).description ||''''||' description, ' ||
1145 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1146 '''' || l_segment_separator ||''''||' segment_separator,' ||
1147 '''' || l_enabled ||''''||' enabled, ' ||
1148 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1149 ' FROM DUAL UNION';
1150
1151
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
1161 l_temp_sql:= ' SELECT ' ||
1162 '''' || p_kf_pos_rv_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1163 '''' || p_kf_pos_rv_tab(i).flex_code ||''''||' flex_code,' ||
1164 '''' || p_kf_pos_rv_tab(i).structure_code ||''''||' structure_code,' ||
1165 '''' || p_kf_pos_rv_tab(i).structure_title ||''''||' structure_title,' ||
1166 '''' || p_kf_pos_rv_tab(i).description ||''''||' description, ' ||
1167 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1168 '''' || l_segment_separator ||''''||' segment_separator,' ||
1169 '''' || l_enabled ||''''||' enabled, ' ||
1170 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1171 ' FROM DUAL UNION';
1172
1173
1177
1174 dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1175 end loop;
1176 end if;
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
1183
1184 l_temp_sql:= ' SELECT ' ||
1185 '''' || p_kf_pos_no_rv_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1186 '''' || p_kf_pos_no_rv_tab(i).flex_code ||''''||' flex_code,' ||
1187 '''' || p_kf_pos_no_rv_tab(i).structure_code ||''''||' structure_code,' ||
1188 '''' || p_kf_pos_no_rv_tab(i).structure_title ||''''||' structure_title,' ||
1189 '''' || p_kf_pos_no_rv_tab(i).description ||''''||' description, ' ||
1190 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1191 '''' || l_segment_separator ||''''||' segment_separator,' ||
1192 '''' || l_enabled ||''''||' enabled, ' ||
1193 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1194 ' FROM DUAL UNION';
1195
1196
1197 dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1198 end loop;
1199 end if;
1200
1201 dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1202 dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1203
1204 if p_kf_pos_tab.count > 0 or p_kf_pos_rv_tab.count > 0 or p_kf_pos_no_rv_tab.count > 0 then
1205 p_kf_pos_str_clob := fetch_clob(l_kf_str_clob,'PosKeyFlexStruct','PosKeyFlexStructures');
1206 end if;
1207
1208 l_kf_str_clob := null;
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
1218
1219 l_temp_sql:= ' SELECT ' ||
1220 '''' || p_kf_grd_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1221 '''' || p_kf_grd_tab(i).flex_code ||''''||' flex_code,' ||
1222 '''' || p_kf_grd_tab(i).structure_code ||''''||' structure_code,' ||
1223 '''' || p_kf_grd_tab(i).structure_title ||''''||' structure_title,' ||
1224 '''' || p_kf_grd_tab(i).description ||''''||' description,' ||
1225 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1226 '''' || l_segment_separator ||''''||' segment_separator,' ||
1227 '''' || l_enabled ||''''||' enabled, ' ||
1228 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1229 ' FROM DUAL UNION';
1230
1231
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
1241
1242 l_temp_sql:= ' SELECT ' ||
1243 '''' || p_kf_grd_rv_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1244 '''' || p_kf_grd_rv_tab(i).flex_code ||''''||' flex_code,' ||
1245 '''' || p_kf_grd_rv_tab(i).structure_code ||''''||' structure_code,' ||
1246 '''' || p_kf_grd_rv_tab(i).structure_title ||''''||' structure_title,' ||
1247 '''' || p_kf_grd_rv_tab(i).description ||''''||' description,' ||
1248 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1249 '''' || l_segment_separator ||''''||' segment_separator,' ||
1250 '''' || l_enabled ||''''||' enabled, ' ||
1251 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1252 ' FROM DUAL UNION';
1253
1254
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
1264
1265 l_temp_sql:= ' SELECT ' ||
1266 '''' || p_kf_grd_no_rv_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1267 '''' || p_kf_grd_no_rv_tab(i).flex_code ||''''||' flex_code,' ||
1268 '''' || p_kf_grd_no_rv_tab(i).structure_code ||''''||' structure_code,' ||
1269 '''' || p_kf_grd_no_rv_tab(i).structure_title ||''''||' structure_title,' ||
1270 '''' || p_kf_grd_no_rv_tab(i).description ||''''||' description,' ||
1271 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1272 '''' || l_segment_separator ||''''||' segment_separator,' ||
1273 '''' || l_enabled ||''''||' enabled, ' ||
1274 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1275 ' FROM DUAL UNION';
1276
1277 dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1278 end loop;
1279 end if;
1280
1281 dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1282 dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1283
1284 if p_kf_grd_tab.count > 0 or p_kf_grd_rv_tab.count > 0 or p_kf_grd_no_rv_tab.count > 0 then
1285 p_kf_grd_str_clob := fetch_clob(l_kf_str_clob,'GrdKeyFlexStruct','GrdKeyFlexStructures');
1286 end if;
1287
1288 l_kf_str_clob := null;
1289 l_kf_str_clob := get_clob_locator('CmpKeyFlexStruct');
1290 dbms_lob.createtemporary(l_kf_str_clob,TRUE);
1294
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);
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
1298
1299 l_temp_sql:= ' SELECT ' ||
1300 '''' || p_kf_cmp_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1301 '''' || p_kf_cmp_tab(i).flex_code ||''''||' flex_code,' ||
1302 '''' || p_kf_cmp_tab(i).structure_code ||''''||' structure_code,' ||
1303 '''' || p_kf_cmp_tab(i).structure_title ||''''||' structure_title,' ||
1304 '''' || p_kf_cmp_tab(i).description ||''''||' description, ' ||
1305 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1306 '''' || l_segment_separator ||''''||' segment_separator,' ||
1307 '''' || l_enabled ||''''||' enabled, ' ||
1308 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1309 ' FROM DUAL UNION';
1310
1311
1312 dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1313 end loop;
1314 end if;
1315
1316 dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1317 dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1318
1319 if p_kf_cmp_tab.count > 0 then
1320 p_kf_cmp_str_clob := fetch_clob(l_kf_str_clob,'CmpKeyFlexStruct','CmpKeyFlexStructures');
1321 end if;
1322
1323 l_kf_str_clob := null;
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
1333
1334 l_temp_sql:= ' SELECT ' ||
1335 '''' || p_kf_grp_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1336 '''' || p_kf_grp_tab(i).flex_code ||''''||' flex_code,' ||
1337 '''' || p_kf_grp_tab(i).structure_code ||''''||' structure_code,' ||
1338 '''' || p_kf_grp_tab(i).structure_title ||''''||' structure_title,' ||
1339 '''' || p_kf_grp_tab(i).description ||''''||' description,' ||
1340 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1341 '''' || l_segment_separator ||''''||' segment_separator,' ||
1342 '''' || l_enabled ||''''||' enabled, ' ||
1343 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1344 ' FROM DUAL UNION';
1345
1346
1347 dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1348 end loop;
1349 end if;
1350
1351 dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1355 p_kf_grp_str_clob := fetch_clob(l_kf_str_clob,'GrpKeyFlexStruct','GrpKeyFlexStructures');
1352 dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1353
1354 if p_kf_grp_tab.count > 0 then
1356 end if;
1357
1358 l_kf_str_clob := null;
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
1368
1369 l_temp_sql:= ' SELECT ' ||
1370 '''' || p_kf_cost_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1371 '''' || p_kf_cost_tab(i).flex_code ||''''||' flex_code,' ||
1372 '''' || p_kf_cost_tab(i).structure_code ||''''||' structure_code,' ||
1373 '''' || p_kf_cost_tab(i).structure_title ||''''||' structure_title,' ||
1374 '''' || p_kf_cost_tab(i).description ||''''||' description,' ||
1375 '''' || l_allow_dynamic ||''''||' allow_dynamic_inserts,' ||
1376 '''' || l_segment_separator ||''''||' segment_separator,' ||
1377 '''' || l_enabled ||''''||' enabled, ' ||
1378 '''' || l_freeze_flex_def ||''''||' freeze_flex_def ' ||
1379 ' FROM DUAL UNION';
1380
1381
1382 dbms_lob.writeappend(l_kf_str_clob,length(l_temp_sql),l_temp_sql);
1383 end loop;
1384 end if;
1385
1386 dbms_lob.trim(l_kf_str_clob,length(l_kf_str_clob)-5);
1387 dbms_lob.writeappend(l_kf_str_clob,length(l_postjoin_sql),l_postjoin_sql);
1388
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);
1397
1394 return l_ret_kf_str_clob;
1395
1396 END get_keyflex_structure_sql;
1398
1399 FUNCTION get_keyflex_segment_sql
1400 (
1401 p_kf_job_seg_tab in out nocopy per_ri_config_tech_summary.kf_job_seg_tab,
1402 p_kf_job_rv_seg_tab in out nocopy per_ri_config_tech_summary.kf_job_rv_seg_tab,
1403 p_kf_job_no_rv_seg_tab in out nocopy per_ri_config_tech_summary.kf_job_no_rv_seg_tab,
1404 p_kf_pos_seg_tab in out nocopy per_ri_config_tech_summary.kf_pos_seg_tab,
1405 p_kf_pos_rv_seg_tab in out nocopy per_ri_config_tech_summary.kf_pos_rv_seg_tab,
1406 p_kf_pos_no_rv_seg_tab in out nocopy per_ri_config_tech_summary.kf_pos_no_rv_seg_tab,
1407 p_kf_grd_seg_tab in out nocopy per_ri_config_tech_summary.kf_grd_seg_tab,
1408 p_kf_grd_rv_seg_tab in out nocopy per_ri_config_tech_summary.kf_grd_rv_seg_tab,
1409 p_kf_grd_no_rv_seg_tab in out nocopy per_ri_config_tech_summary.kf_grd_no_rv_seg_tab,
1410 p_kf_grp_seg_tab in out nocopy per_ri_config_tech_summary.kf_grp_seg_tab,
1411 p_kf_cmp_seg_tab in out nocopy per_ri_config_tech_summary.kf_cmp_seg_tab,
1412 p_kf_cost_seg_tab in out nocopy per_ri_config_tech_summary.kf_cost_seg_tab,
1413 p_kf_job_seg_clob out nocopy clob,
1414 p_kf_job_rv_seg_clob out nocopy clob,
1415 p_kf_job_no_rv_seg_clob out nocopy clob,
1416 p_kf_pos_seg_clob out nocopy clob,
1417 p_kf_pos_rv_seg_clob out nocopy clob,
1418 p_kf_pos_no_rv_seg_clob out nocopy clob,
1419 p_kf_grd_seg_clob out nocopy clob,
1420 p_kf_grd_rv_seg_clob out nocopy clob,
1421 p_kf_grd_no_rv_seg_clob out nocopy clob,
1422 p_kf_grp_seg_clob out nocopy clob,
1423 p_kf_cmp_seg_clob out nocopy clob,
1424 p_kf_cost_seg_clob out nocopy clob
1425 )
1426 return clob IS
1427
1428 l_proc varchar2(72) := g_package || 'get_keyflex_segment_sql';
1429 i number(8) := 0;
1430 l_ret_kf_seg_clob clob;
1431 l_temp_sql varchar2(2000);
1432 l_kf_seg_clob clob;
1433 queryCtx number(8) := 0;
1434
1435 l_required varchar2(8);
1436 l_display varchar2(8);
1437 l_enabled varchar2(8);
1438
1439 l_orderby varchar2(200);
1440 l_vs_security_available varchar2(80);
1441 l_vs_enable_longlist varchar2(80);
1442 l_vs_format_type varchar2(80);
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;
1452 select meaning into l_vs_validation_type from fnd_lookups where lookup_type = 'SEG_VAL_TYPES' and lookup_code = 'I';
1453
1454
1455 l_kf_seg_clob := get_clob_locator('JobKeyFlexSegment');
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
1465 if p_kf_job_seg_tab(i).vs_security_available is not null then
1466 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_job_seg_tab(i).vs_security_available;
1467 end if;
1468 if p_kf_job_seg_tab(i).vs_enable_longlist is not null then
1469 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_job_seg_tab(i).vs_enable_longlist;
1470 end if;
1471 if p_kf_job_seg_tab(i).vs_format_type is not null then
1472 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_job_seg_tab(i).vs_format_type;
1473 end if;
1474
1475 l_temp_sql:= ' SELECT ' ||
1476 '''' || p_kf_job_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1477 '''' || p_kf_job_seg_tab(i).flex_code ||''''||' flex_code,' ||
1478 '''' || p_kf_job_seg_tab(i).structure_code ||''''||' structure_code,' ||
1479 '''' || p_kf_job_seg_tab(i).segment_name ||''''||' segment_name,' ||
1480 '''' || p_kf_job_seg_tab(i).column_name ||''''||' column_name,' ||
1481 '''' || p_kf_job_seg_tab(i).segment_number ||''''||' segment_number,' ||
1482 '''' || p_kf_job_seg_tab(i).value_set ||''''||' value_set,' ||
1483 '''' || p_kf_job_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1484 '''' || p_kf_job_seg_tab(i).segment_type ||''''||' segment_type,' ||
1485 '''' || p_kf_job_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1486 '''' || l_required ||''''||' required,' ||
1487 '''' || l_display ||''''||' display,' ||
1488 '''' || l_enabled ||''''||' enabled, ' ||
1492 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1489 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1490 '''' || p_kf_job_seg_tab(i).vs_description ||''''||' vs_description,' ||
1491 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1493 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1494 '''' || p_kf_job_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1495 '''' || p_kf_job_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1496 '''' || p_kf_job_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1497 '''' || p_kf_job_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1498 '''' || p_kf_job_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1499 '''' || p_kf_job_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1500 '''' || p_kf_job_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1501 '''' || p_kf_job_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1502 ' FROM DUAL UNION';
1503
1504 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1505 end loop;
1506 end if;
1507
1508
1509 if p_kf_job_rv_seg_tab.count > 0 THEN
1510 for i in p_kf_job_rv_seg_tab.first ..
1511 p_kf_job_rv_seg_tab.last loop
1512
1513 if p_kf_job_rv_seg_tab(i).vs_security_available is not null then
1514 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_job_rv_seg_tab(i).vs_security_available;
1515 end if;
1516 if p_kf_job_rv_seg_tab(i).vs_enable_longlist is not null then
1517 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_job_rv_seg_tab(i).vs_enable_longlist;
1518 end if;
1519 if p_kf_job_rv_seg_tab(i).vs_format_type is not null then
1520 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_job_rv_seg_tab(i).vs_format_type;
1521 end if;
1522
1523 l_temp_sql:= ' SELECT ' ||
1524 '''' || p_kf_job_rv_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1525 '''' || p_kf_job_rv_seg_tab(i).flex_code ||''''||' flex_code,' ||
1526 '''' || p_kf_job_rv_seg_tab(i).structure_code ||''''||' structure_code,' ||
1527 '''' || p_kf_job_rv_seg_tab(i).segment_name ||''''||' segment_name,' ||
1528 '''' || p_kf_job_rv_seg_tab(i).column_name ||''''||' column_name,' ||
1529 '''' || p_kf_job_rv_seg_tab(i).segment_number ||''''||' segment_number,' ||
1530 '''' || p_kf_job_rv_seg_tab(i).value_set ||''''||' value_set,' ||
1531 '''' || p_kf_job_rv_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1532 '''' || p_kf_job_rv_seg_tab(i).segment_type ||''''||' segment_type,' ||
1533 '''' || p_kf_job_rv_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1534 '''' || l_required ||''''||' required,' ||
1535 '''' || l_display ||''''||' display,' ||
1536 '''' || l_enabled ||''''||' enabled, ' ||
1537 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1538 '''' || p_kf_job_rv_seg_tab(i).vs_description ||''''||' vs_description,' ||
1539 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1540 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1541 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1542 '''' || p_kf_job_rv_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1543 '''' || p_kf_job_rv_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1544 '''' || p_kf_job_rv_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1545 '''' || p_kf_job_rv_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1546 '''' || p_kf_job_rv_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1547 '''' || p_kf_job_rv_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1548 '''' || p_kf_job_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1549 '''' || p_kf_job_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1550 ' FROM DUAL UNION';
1551
1552 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1553 end loop;
1554 end if;
1555
1556 if p_kf_job_no_rv_seg_tab.count > 0 THEN
1557 for i in p_kf_job_no_rv_seg_tab.first ..
1561 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_job_no_rv_seg_tab(i).vs_security_available;
1558 p_kf_job_no_rv_seg_tab.last loop
1559
1560 if p_kf_job_no_rv_seg_tab(i).vs_security_available is not null then
1562 end if;
1563 if p_kf_job_no_rv_seg_tab(i).vs_enable_longlist is not null then
1564 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_job_no_rv_seg_tab(i).vs_enable_longlist;
1565 end if;
1566 if p_kf_job_no_rv_seg_tab(i).vs_format_type is not null then
1567 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_job_no_rv_seg_tab(i).vs_format_type;
1568 end if;
1569
1570 l_temp_sql:= ' SELECT ' ||
1571 '''' || p_kf_job_no_rv_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1572 '''' || p_kf_job_no_rv_seg_tab(i).flex_code ||''''||' flex_code,' ||
1573 '''' || p_kf_job_no_rv_seg_tab(i).structure_code ||''''||' structure_code,' ||
1574 '''' || p_kf_job_no_rv_seg_tab(i).segment_name ||''''||' segment_name,' ||
1575 '''' || p_kf_job_no_rv_seg_tab(i).column_name ||''''||' column_name,' ||
1576 '''' || p_kf_job_no_rv_seg_tab(i).segment_number ||''''||' segment_number,' ||
1577 '''' || p_kf_job_no_rv_seg_tab(i).value_set ||''''||' value_set,' ||
1578 '''' || p_kf_job_no_rv_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1579 '''' || p_kf_job_no_rv_seg_tab(i).segment_type ||''''||' segment_type,' ||
1580 '''' || p_kf_job_no_rv_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1581 '''' || l_required ||''''||' required,' ||
1582 '''' || l_display ||''''||' display,' ||
1583 '''' || l_enabled ||''''||' enabled,' ||
1584 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1585 '''' || p_kf_job_no_rv_seg_tab(i).vs_description ||''''||' vs_description,' ||
1586 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1587 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1588 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1589 '''' || p_kf_job_no_rv_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1593 '''' || p_kf_job_no_rv_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1590 '''' || p_kf_job_no_rv_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1591 '''' || p_kf_job_no_rv_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1592 '''' || p_kf_job_no_rv_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1594 '''' || p_kf_job_no_rv_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1595 '''' || p_kf_job_no_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1596 '''' || p_kf_job_no_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1597 ' FROM DUAL UNION';
1598
1599 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1600 end loop;
1601 end if;
1602
1603 if p_kf_job_seg_tab.count > 0 or p_kf_job_rv_seg_tab.count > 0 or p_kf_job_no_rv_seg_tab.count > 0 then
1604 dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1605
1606 --Added to order the segments display in technical summary report for Job ff
1607 l_temp_sql := ' ORDER BY column_name ';
1608 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
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');
1618 dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1619
1620 if p_kf_pos_seg_tab.count > 0 THEN
1621 for i in p_kf_pos_seg_tab.first ..
1622 p_kf_pos_seg_tab.last loop
1623
1624 if p_kf_pos_seg_tab(i).vs_security_available is not null then
1625 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_pos_seg_tab(i).vs_security_available;
1626 end if;
1627 if p_kf_pos_seg_tab(i).vs_enable_longlist is not null then
1628 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_pos_seg_tab(i).vs_enable_longlist;
1629 end if;
1630 if p_kf_pos_seg_tab(i).vs_format_type is not null then
1631 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_pos_seg_tab(i).vs_format_type;
1632 end if;
1633
1634 l_temp_sql:= ' SELECT ' ||
1635 '''' || p_kf_pos_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1636 '''' || p_kf_pos_seg_tab(i).flex_code ||''''||' flex_code,' ||
1637 '''' || p_kf_pos_seg_tab(i).structure_code ||''''||' structure_code,' ||
1638 '''' || p_kf_pos_seg_tab(i).segment_name ||''''||' segment_name,' ||
1639 '''' || p_kf_pos_seg_tab(i).column_name ||''''||' column_name,' ||
1640 '''' || p_kf_pos_seg_tab(i).segment_number ||''''||' segment_number,' ||
1641 '''' || p_kf_pos_seg_tab(i).value_set ||''''||' value_set,' ||
1642 '''' || p_kf_pos_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1643 '''' || p_kf_pos_seg_tab(i).segment_type ||''''||' segment_type,' ||
1644 '''' || p_kf_pos_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1645 '''' || l_required ||''''||' required,' ||
1646 '''' || l_display ||''''||' display,' ||
1647 '''' || l_enabled ||''''||' enabled, ' ||
1648 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1649 '''' || p_kf_pos_seg_tab(i).vs_description ||''''||' vs_description,' ||
1650 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1651 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1652 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1653 '''' || p_kf_pos_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1654 '''' || p_kf_pos_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1655 '''' || p_kf_pos_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1656 '''' || p_kf_pos_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1657 '''' || p_kf_pos_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1658 '''' || p_kf_pos_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1659 '''' || p_kf_pos_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1660 '''' || p_kf_pos_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1661 ' FROM DUAL UNION';
1662
1663 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1664 end loop;
1665 end if;
1666
1667 if p_kf_pos_rv_seg_tab.count > 0 THEN
1668 for i in p_kf_pos_rv_seg_tab.first ..
1669 p_kf_pos_rv_seg_tab.last loop
1670
1671 if p_kf_pos_rv_seg_tab(i).vs_security_available is not null then
1672 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_pos_rv_seg_tab(i).vs_security_available;
1673 end if;
1674 if p_kf_pos_rv_seg_tab(i).vs_enable_longlist is not null then
1678 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_pos_rv_seg_tab(i).vs_format_type;
1675 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_pos_rv_seg_tab(i).vs_enable_longlist;
1676 end if;
1677 if p_kf_pos_rv_seg_tab(i).vs_format_type is not null then
1679 end if;
1680
1681 l_temp_sql:= ' SELECT ' ||
1682 '''' || p_kf_pos_rv_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1683 '''' || p_kf_pos_rv_seg_tab(i).flex_code ||''''||' flex_code,' ||
1684 '''' || p_kf_pos_rv_seg_tab(i).structure_code ||''''||' structure_code,' ||
1685 '''' || p_kf_pos_rv_seg_tab(i).segment_name ||''''||' segment_name,' ||
1686 '''' || p_kf_pos_rv_seg_tab(i).column_name ||''''||' column_name,' ||
1687 '''' || p_kf_pos_rv_seg_tab(i).segment_number ||''''||' segment_number,' ||
1688 '''' || p_kf_pos_rv_seg_tab(i).value_set ||''''||' value_set,' ||
1689 '''' || p_kf_pos_rv_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1690 '''' || p_kf_pos_rv_seg_tab(i).segment_type ||''''||' segment_type,' ||
1691 '''' || p_kf_pos_rv_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1692 '''' || l_required ||''''||' required,' ||
1693 '''' || l_display ||''''||' display,' ||
1694 '''' || l_enabled ||''''||' enabled, ' ||
1695 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1696 '''' || p_kf_pos_rv_seg_tab(i).vs_description ||''''||' vs_description, ' ||
1697 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1698 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1699 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1700 '''' || p_kf_pos_rv_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1701 '''' || p_kf_pos_rv_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1702 '''' || p_kf_pos_rv_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1703 '''' || p_kf_pos_rv_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1704 '''' || p_kf_pos_rv_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1705 '''' || p_kf_pos_rv_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1706 '''' || p_kf_pos_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1707 '''' || p_kf_pos_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1708 ' FROM DUAL UNION';
1709
1710 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1711 end loop;
1712 end if;
1713
1714 if p_kf_pos_no_rv_seg_tab.count > 0 THEN
1715 for i in p_kf_pos_no_rv_seg_tab.first ..
1716 p_kf_pos_no_rv_seg_tab.last loop
1717
1718 if p_kf_pos_no_rv_seg_tab(i).vs_security_available is not null then
1719 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_pos_no_rv_seg_tab(i).vs_security_available;
1720 end if;
1721 if p_kf_pos_no_rv_seg_tab(i).vs_enable_longlist is not null then
1722 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_pos_no_rv_seg_tab(i).vs_enable_longlist;
1723 end if;
1724 if p_kf_pos_no_rv_seg_tab(i).vs_format_type is not null then
1725 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_pos_no_rv_seg_tab(i).vs_format_type;
1726 end if;
1727
1728 l_temp_sql:= ' SELECT ' ||
1729 '''' || p_kf_pos_no_rv_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1730 '''' || p_kf_pos_no_rv_seg_tab(i).flex_code ||''''||' flex_code,' ||
1731 '''' || p_kf_pos_no_rv_seg_tab(i).structure_code ||''''||' structure_code,' ||
1732 '''' || p_kf_pos_no_rv_seg_tab(i).segment_name ||''''||' segment_name,' ||
1733 '''' || p_kf_pos_no_rv_seg_tab(i).column_name ||''''||' column_name,' ||
1734 '''' || p_kf_pos_no_rv_seg_tab(i).segment_number ||''''||' segment_number,' ||
1735 '''' || p_kf_pos_no_rv_seg_tab(i).value_set ||''''||' value_set,' ||
1736 '''' || p_kf_pos_no_rv_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1737 '''' || p_kf_pos_no_rv_seg_tab(i).segment_type ||''''||' segment_type,' ||
1738 '''' || p_kf_pos_no_rv_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1739 '''' || l_required ||''''||' required,' ||
1740 '''' || l_display ||''''||' display,' ||
1741 '''' || l_enabled ||''''||' enabled, ' ||
1742 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1743 '''' || p_kf_pos_no_rv_seg_tab(i).vs_description ||''''||' vs_description, ' ||
1747 '''' || p_kf_pos_no_rv_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1744 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1745 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1746 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1748 '''' || p_kf_pos_no_rv_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1749 '''' || p_kf_pos_no_rv_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1750 '''' || p_kf_pos_no_rv_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1751 '''' || p_kf_pos_no_rv_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1752 '''' || p_kf_pos_no_rv_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1753 '''' || p_kf_pos_no_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1754 '''' || p_kf_pos_no_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1755 ' FROM DUAL UNION';
1756
1757 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1758 end loop;
1759 end if;
1760
1761 if p_kf_pos_seg_tab.count > 0 or p_kf_pos_rv_seg_tab.count > 0 or p_kf_pos_no_rv_seg_tab.count > 0 then
1762 dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1763
1764 --Added to order the segments display in technical summary report for position ff
1765 l_temp_sql := ' ORDER BY column_name ';
1766 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1767
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');
1777 dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1778
1779 if p_kf_grd_seg_tab.count > 0 THEN
1780 for i in p_kf_grd_seg_tab.first ..
1781 p_kf_grd_seg_tab.last loop
1782
1783 if p_kf_grd_seg_tab(i).vs_security_available is not null then
1784 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_grd_seg_tab(i).vs_security_available;
1785 end if;
1786 if p_kf_grd_seg_tab(i).vs_enable_longlist is not null then
1787 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_grd_seg_tab(i).vs_enable_longlist;
1788 end if;
1789 if p_kf_grd_seg_tab(i).vs_format_type is not null then
1790 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_grd_seg_tab(i).vs_format_type;
1791 end if;
1792
1793 l_temp_sql:= ' SELECT ' ||
1794 '''' || p_kf_grd_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1795 '''' || p_kf_grd_seg_tab(i).flex_code ||''''||' flex_code,' ||
1796 '''' || p_kf_grd_seg_tab(i).structure_code ||''''||' structure_code,' ||
1797 '''' || p_kf_grd_seg_tab(i).segment_name ||''''||' segment_name,' ||
1798 '''' || p_kf_grd_seg_tab(i).column_name ||''''||' column_name,' ||
1799 '''' || p_kf_grd_seg_tab(i).segment_number ||''''||' segment_number,' ||
1800 '''' || p_kf_grd_seg_tab(i).value_set ||''''||' value_set,' ||
1801 '''' || p_kf_grd_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1802 '''' || p_kf_grd_seg_tab(i).segment_type ||''''||' segment_type,' ||
1803 '''' || p_kf_grd_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1804 '''' || l_required ||''''||' required,' ||
1805 '''' || l_display ||''''||' display,' ||
1806 '''' || l_enabled ||''''||' enabled ,' ||
1807 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1808 '''' || p_kf_grd_seg_tab(i).vs_description ||''''||' vs_description,' ||
1809 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1810 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1811 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1812 '''' || p_kf_grd_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1813 '''' || p_kf_grd_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1814 '''' || p_kf_grd_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1815 '''' || p_kf_grd_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1819 '''' || p_kf_grd_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1816 '''' || p_kf_grd_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1817 '''' || p_kf_grd_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1818 '''' || p_kf_grd_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1820 ' FROM DUAL UNION';
1821
1822 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1823 end loop;
1824 end if;
1825
1826 if p_kf_grd_rv_seg_tab.count > 0 THEN
1827 for i in p_kf_grd_rv_seg_tab.first ..
1828 p_kf_grd_rv_seg_tab.last loop
1829
1830 if p_kf_grd_rv_seg_tab(i).vs_security_available is not null then
1831 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_grd_rv_seg_tab(i).vs_security_available;
1832 end if;
1833 if p_kf_grd_rv_seg_tab(i).vs_enable_longlist is not null then
1834 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_grd_rv_seg_tab(i).vs_enable_longlist;
1835 end if;
1836 if p_kf_grd_rv_seg_tab(i).vs_format_type is not null then
1840 l_temp_sql:= ' SELECT ' ||
1837 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_grd_rv_seg_tab(i).vs_format_type;
1838 end if;
1839
1841 '''' || p_kf_grd_rv_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1842 '''' || p_kf_grd_rv_seg_tab(i).flex_code ||''''||' flex_code,' ||
1843 '''' || p_kf_grd_rv_seg_tab(i).structure_code ||''''||' structure_code,' ||
1844 '''' || p_kf_grd_rv_seg_tab(i).segment_name ||''''||' segment_name,' ||
1845 '''' || p_kf_grd_rv_seg_tab(i).column_name ||''''||' column_name,' ||
1846 '''' || p_kf_grd_rv_seg_tab(i).segment_number ||''''||' segment_number,' ||
1847 '''' || p_kf_grd_rv_seg_tab(i).value_set ||''''||' value_set,' ||
1848 '''' || p_kf_grd_rv_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1849 '''' || p_kf_grd_rv_seg_tab(i).segment_type ||''''||' segment_type,' ||
1850 '''' || p_kf_grd_rv_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1851 '''' || l_required ||''''||' required,' ||
1852 '''' || l_display ||''''||' display,' ||
1853 '''' || l_enabled ||''''||' enabled, ' ||
1854 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1855 '''' || p_kf_grd_rv_seg_tab(i).vs_description ||''''||' vs_description, ' ||
1856 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1857 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1858 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1859 '''' || p_kf_grd_rv_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1860 '''' || p_kf_grd_rv_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1861 '''' || p_kf_grd_rv_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1862 '''' || p_kf_grd_rv_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1863 '''' || p_kf_grd_rv_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1864 '''' || p_kf_grd_rv_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1865 '''' || p_kf_grd_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1866 '''' || p_kf_grd_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1867 ' FROM DUAL UNION';
1868
1869 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1870
1871 end loop;
1872 end if;
1873
1874 if p_kf_grd_no_rv_seg_tab.count > 0 THEN
1875 for i in p_kf_grd_no_rv_seg_tab.first ..
1876 p_kf_grd_no_rv_seg_tab.last loop
1877
1878 if p_kf_grd_no_rv_seg_tab(i).vs_security_available is not null then
1879 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_grd_no_rv_seg_tab(i).vs_security_available;
1880 end if;
1881 if p_kf_grd_no_rv_seg_tab(i).vs_enable_longlist is not null then
1882 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_grd_no_rv_seg_tab(i).vs_enable_longlist;
1883 end if;
1884 if p_kf_grd_no_rv_seg_tab(i).vs_format_type is not null then
1885 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_grd_no_rv_seg_tab(i).vs_format_type;
1886 end if;
1887
1888 l_temp_sql:= ' SELECT ' ||
1889 '''' || p_kf_grd_no_rv_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1890 '''' || p_kf_grd_no_rv_seg_tab(i).flex_code ||''''||' flex_code,' ||
1891 '''' || p_kf_grd_no_rv_seg_tab(i).structure_code ||''''||' structure_code,' ||
1892 '''' || p_kf_grd_no_rv_seg_tab(i).segment_name ||''''||' segment_name,' ||
1893 '''' || p_kf_grd_no_rv_seg_tab(i).column_name ||''''||' column_name,' ||
1894 '''' || p_kf_grd_no_rv_seg_tab(i).segment_number ||''''||' segment_number,' ||
1895 '''' || p_kf_grd_no_rv_seg_tab(i).value_set ||''''||' value_set,' ||
1896 '''' || p_kf_grd_no_rv_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1897 '''' || p_kf_grd_no_rv_seg_tab(i).segment_type ||''''||' segment_type,' ||
1898 '''' || p_kf_grd_no_rv_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1899 '''' || l_required ||''''||' required,' ||
1900 '''' || l_display ||''''||' display,' ||
1901 '''' || l_enabled ||''''||' enabled,' ||
1902 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1903 '''' || p_kf_grd_no_rv_seg_tab(i).vs_description ||''''||' vs_description,' ||
1904 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1905 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1906 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1907 '''' || p_kf_grd_no_rv_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1908 '''' || p_kf_grd_no_rv_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1909 '''' || p_kf_grd_no_rv_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1910 '''' || p_kf_grd_no_rv_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1911 '''' || p_kf_grd_no_rv_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1912 '''' || p_kf_grd_no_rv_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1913 '''' || p_kf_grd_no_rv_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1914 '''' || p_kf_grd_no_rv_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1915 ' FROM DUAL UNION';
1916
1917 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1918
1919 end loop;
1920 end if;
1921
1922
1923 if p_kf_grd_seg_tab.count > 0 or p_kf_grd_rv_seg_tab.count > 0 or p_kf_grd_no_rv_seg_tab.count > 0 then
1924 dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1925
1926 --Added to order the segments display in technical summary report for grade ff
1927 l_temp_sql := ' ORDER BY column_name ';
1928 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
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');
1938 dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
1939
1940
1941 if p_kf_grp_seg_tab.count > 0 THEN
1945 if p_kf_grp_seg_tab(i).vs_security_available is not null then
1942 for i in p_kf_grp_seg_tab.first ..
1943 p_kf_grp_seg_tab.last loop
1944
1946 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_grp_seg_tab(i).vs_security_available;
1947 end if;
1948 if p_kf_grp_seg_tab(i).vs_enable_longlist is not null then
1949 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_grp_seg_tab(i).vs_enable_longlist;
1950 end if;
1951 if p_kf_grp_seg_tab(i).vs_format_type is not null then
1952 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_grp_seg_tab(i).vs_format_type;
1953 end if;
1954
1955 l_temp_sql:= ' SELECT ' ||
1956 '''' || p_kf_grp_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
1957 '''' || p_kf_grp_seg_tab(i).flex_code ||''''||' flex_code,' ||
1958 '''' || p_kf_grp_seg_tab(i).structure_code ||''''||' structure_code,' ||
1959 '''' || p_kf_grp_seg_tab(i).segment_name ||''''||' segment_name,' ||
1960 '''' || p_kf_grp_seg_tab(i).column_name ||''''||' column_name,' ||
1961 '''' || p_kf_grp_seg_tab(i).segment_number ||''''||' segment_number,' ||
1962 '''' || p_kf_grp_seg_tab(i).value_set ||''''||' value_set,' ||
1963 '''' || p_kf_grp_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
1964 '''' || p_kf_grp_seg_tab(i).segment_type ||''''||' segment_type,' ||
1965 '''' || p_kf_grp_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
1966 '''' || l_required ||''''||' required,' ||
1967 '''' || l_display ||''''||' display,' ||
1968 '''' || l_enabled ||''''||' enabled, ' ||
1969 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
1970 '''' || p_kf_grp_seg_tab(i).vs_description ||''''||' vs_description,' ||
1971 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
1972 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
1973 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
1974 '''' || p_kf_grp_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
1975 '''' || p_kf_grp_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
1976 '''' || p_kf_grp_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
1977 '''' || p_kf_grp_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
1978 '''' || p_kf_grp_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
1979 '''' || p_kf_grp_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
1980 '''' || p_kf_grp_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
1981 '''' || p_kf_grp_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
1982 ' FROM DUAL UNION';
1983
1984 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1985 end loop;
1986 end if;
1987
1988 if p_kf_grp_seg_tab.count > 0 then
1989 dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
1990
1991 --Added to order the segments display in technical summary report for grp ff
1992 l_temp_sql := ' ORDER BY column_name ';
1993 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
1994
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');
2004 dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
2005
2006 if p_kf_cmp_seg_tab.count > 0 THEN
2007 for i in p_kf_cmp_seg_tab.first ..
2008 p_kf_cmp_seg_tab.last loop
2009
2010 if p_kf_cmp_seg_tab(i).vs_security_available is not null then
2011 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_cmp_seg_tab(i).vs_security_available;
2012 end if;
2013 if p_kf_cmp_seg_tab(i).vs_enable_longlist is not null then
2014 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_cmp_seg_tab(i).vs_enable_longlist;
2015 end if;
2016 if p_kf_cmp_seg_tab(i).vs_format_type is not null then
2017 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_cmp_seg_tab(i).vs_format_type;
2018 end if;
2019
2020 l_temp_sql:= ' SELECT ' ||
2021 '''' || p_kf_cmp_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
2022 '''' || p_kf_cmp_seg_tab(i).flex_code ||''''||' flex_code,' ||
2023 '''' || p_kf_cmp_seg_tab(i).structure_code ||''''||' structure_code,' ||
2024 '''' || p_kf_cmp_seg_tab(i).segment_name ||''''||' segment_name,' ||
2025 '''' || p_kf_cmp_seg_tab(i).column_name ||''''||' column_name,' ||
2026 '''' || p_kf_cmp_seg_tab(i).segment_number ||''''||' segment_number,' ||
2027 '''' || p_kf_cmp_seg_tab(i).value_set ||''''||' value_set,' ||
2028 '''' || p_kf_cmp_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
2032 '''' || l_display ||''''||' display,' ||
2029 '''' || p_kf_cmp_seg_tab(i).segment_type ||''''||' segment_type,' ||
2030 '''' || p_kf_cmp_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
2031 '''' || l_required ||''''||' required,' ||
2033 '''' || l_enabled ||''''||' enabled, ' ||
2034 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
2035 '''' || p_kf_cmp_seg_tab(i).vs_description ||''''||' vs_description, ' ||
2036 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
2037 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
2038 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
2039 '''' || p_kf_cmp_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
2040 '''' || p_kf_cmp_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
2041 '''' || p_kf_cmp_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
2042 '''' || p_kf_cmp_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
2043 '''' || p_kf_cmp_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
2044 '''' || p_kf_cmp_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
2045 '''' || p_kf_cmp_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
2046 '''' || p_kf_cmp_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
2047 ' FROM DUAL UNION';
2048
2049 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
2050 end loop;
2051 end if;
2052
2053 if p_kf_cmp_seg_tab.count > 0 then
2054 dbms_lob.trim(l_kf_seg_clob,length(l_kf_seg_clob)-5);
2055
2056 --Added to order the segments display in technical summary report for cmp ff
2057 l_temp_sql := ' ORDER BY column_name ';
2058 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
2059
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');
2069 dbms_lob.createtemporary(l_kf_seg_clob,TRUE);
2070
2071 if p_kf_cost_seg_tab.count > 0 THEN
2072 for i in p_kf_cost_seg_tab.first ..
2073 p_kf_cost_seg_tab.last loop
2074
2075 if p_kf_cost_seg_tab(i).vs_security_available is not null then
2076 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_cost_seg_tab(i).vs_security_available;
2077 end if;
2078 if p_kf_cost_seg_tab(i).vs_enable_longlist is not null then
2079 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_cost_seg_tab(i).vs_enable_longlist;
2080 end if;
2081 if p_kf_cost_seg_tab(i).vs_format_type is not null then
2082 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_cost_seg_tab(i).vs_format_type;
2083 end if;
2084
2085 l_temp_sql:= ' SELECT ' ||
2086 '''' || p_kf_cost_seg_tab(i).appl_short_name ||''''||' appl_short_name,' ||
2087 '''' || p_kf_cost_seg_tab(i).flex_code ||''''||' flex_code,' ||
2088 '''' || p_kf_cost_seg_tab(i).structure_code ||''''||' structure_code,' ||
2089 '''' || p_kf_cost_seg_tab(i).segment_name ||''''||' segment_name,' ||
2090 '''' || p_kf_cost_seg_tab(i).column_name ||''''||' column_name,' ||
2091 '''' || p_kf_cost_seg_tab(i).segment_number ||''''||' segment_number,' ||
2092 '''' || p_kf_cost_seg_tab(i).value_set ||''''||' value_set,' ||
2093 '''' || p_kf_cost_seg_tab(i).lov_prompt ||''''||' lov_prompt,' ||
2094 '''' || p_kf_cost_seg_tab(i).segment_type ||''''||' segment_type,' ||
2095 '''' || p_kf_cost_seg_tab(i).window_prompt ||''''||' window_prompt,' ||
2096 '''' || l_required ||''''||' required,' ||
2097 '''' || l_display ||''''||' display,' ||
2098 '''' || l_enabled ||''''||' enabled, ' ||
2099 '''' || l_vs_validation_type ||''''||' vs_validation_type, ' ||
2100 '''' || p_kf_cost_seg_tab(i).vs_description ||''''||' vs_description,' ||
2101 '''' || l_vs_security_available ||''''||' vs_security_available,' ||
2102 '''' || l_vs_enable_longlist ||''''||' vs_list_type,' || --List type
2103 '''' || l_vs_format_type ||''''||' vs_format_type,' || --validation type
2104 '''' || p_kf_cost_seg_tab(i).vs_maximum_size ||''''||' vs_maximum_size,' ||
2105 '''' || p_kf_cost_seg_tab(i).vs_precision ||''''||' vs_precision,' ||
2106 '''' || p_kf_cost_seg_tab(i).vs_numbers_only ||''''||' vs_numbers_only,' ||
2107 '''' || p_kf_cost_seg_tab(i).vs_uppercase_only ||''''||' vs_uppercase_only,' ||
2108 '''' || p_kf_cost_seg_tab(i).vs_right_justify_zero_fill ||''''||' vs_right_justify_zero_fill,' ||
2109 '''' || p_kf_cost_seg_tab(i).vs_min_value ||''''||' vs_min_value,' ||
2113
2110 '''' || p_kf_cost_seg_tab(i).vs_max_value ||''''||' vs_max_value,' ||
2111 '''' || p_kf_cost_seg_tab(i).vs_value_set_name ||''''||' value_set_name' ||
2112 ' FROM DUAL UNION';
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
2123 --Added to order the segments display in technical summary report for cost ff
2127 p_kf_cost_seg_clob := fetch_clob(l_kf_seg_clob,'CostKeyFlexSegment','CostKeyFlexSegments');
2124 l_temp_sql := ' ORDER BY column_name ';
2125 dbms_lob.writeappend(l_kf_seg_clob,length(l_temp_sql),l_temp_sql);
2126
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
2136 FUNCTION get_keyflex_str_seg_sql_for_pv (p_kf_job_tab in per_ri_config_tech_summary.kf_job_tab,
2137 p_kf_job_rv_tab in per_ri_config_tech_summary.kf_job_rv_tab,
2138 p_kf_job_no_rv_tab in per_ri_config_tech_summary.kf_job_no_rv_tab,
2139 p_kf_pos_tab in per_ri_config_tech_summary.kf_pos_tab,
2140 p_kf_pos_rv_tab in per_ri_config_tech_summary.kf_pos_rv_tab,
2141 p_kf_pos_no_rv_tab in per_ri_config_tech_summary.kf_pos_no_rv_tab,
2142 p_kf_grd_tab in per_ri_config_tech_summary.kf_grd_tab,
2143 p_kf_grd_rv_tab in per_ri_config_tech_summary.kf_grd_rv_tab,
2144 p_kf_grd_no_rv_tab in per_ri_config_tech_summary.kf_grd_no_rv_tab,
2145 p_kf_cmp_tab in per_ri_config_tech_summary.kf_cmp_tab,
2146 p_kf_grp_tab in per_ri_config_tech_summary.kf_grp_tab,
2147 p_kf_cost_tab in per_ri_config_tech_summary.kf_cost_tab,
2148 p_kf_job_seg_tab in per_ri_config_tech_summary.kf_job_seg_tab,
2149 p_kf_job_rv_seg_tab in per_ri_config_tech_summary.kf_job_rv_seg_tab,
2150 p_kf_job_no_rv_seg_tab in per_ri_config_tech_summary.kf_job_no_rv_seg_tab,
2151 p_kf_pos_seg_tab in per_ri_config_tech_summary.kf_pos_seg_tab,
2152 p_kf_pos_rv_seg_tab in per_ri_config_tech_summary.kf_pos_rv_seg_tab,
2153 p_kf_pos_no_rv_seg_tab in per_ri_config_tech_summary.kf_pos_no_rv_seg_tab,
2154 p_kf_grd_seg_tab in per_ri_config_tech_summary.kf_grd_seg_tab,
2155 p_kf_grd_rv_seg_tab in per_ri_config_tech_summary.kf_grd_rv_seg_tab,
2156 p_kf_grd_no_rv_seg_tab in per_ri_config_tech_summary.kf_grd_no_rv_seg_tab,
2157 p_kf_grp_seg_tab in per_ri_config_tech_summary.kf_grp_seg_tab,
2158 p_kf_cmp_seg_tab in per_ri_config_tech_summary.kf_cmp_seg_tab,
2159 p_kf_cost_seg_tab in per_ri_config_tech_summary.kf_cost_seg_tab)
2160 return clob IS
2161 l_str_seg_append_clob_for_pv clob;
2162 l_proc varchar2(200) := 'get_keyflex_str_seg_sql_for_pv';
2163 l_structure_tab per_ri_config_tech_summary.kf_structure_tab;
2164 l_segment_tab per_ri_config_tech_summary.kf_segment_tab;
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 ..
2174 p_kf_job_tab.last loop
2175 l_structure_tab(j).appl_short_name := p_kf_job_tab(j).appl_short_name;
2176 l_structure_tab(j).flex_code := p_kf_job_tab(j).flex_code;
2177 l_structure_tab(j).structure_code := p_kf_job_tab(j).structure_code;
2178 l_structure_tab(j).structure_title := p_kf_job_tab(j).structure_title;
2179 l_structure_tab(j).description := p_kf_job_tab(j).description;
2180 end loop;
2181 end if;
2182 IF p_kf_job_seg_tab.count > 0 THEN
2183 for j in p_kf_job_seg_tab.first ..
2184 p_kf_job_seg_tab.last loop
2185
2186 l_segment_tab(j).appl_short_name := p_kf_job_seg_tab(j).appl_short_name;
2187 l_segment_tab(j).flex_code := p_kf_job_seg_tab(j).flex_code;
2188 l_segment_tab(j).structure_code := p_kf_job_seg_tab(j).structure_code;
2189 l_segment_tab(j).segment_name := p_kf_job_seg_tab(j).segment_name;
2190 l_segment_tab(j).column_name := p_kf_job_seg_tab(j).column_name;
2191 l_segment_tab(j).segment_number := p_kf_job_seg_tab(j).segment_number;
2192 l_segment_tab(j).value_set := p_kf_job_seg_tab(j).value_set;
2193 l_segment_tab(j).lov_prompt := p_kf_job_seg_tab(j).lov_prompt;
2194 l_segment_tab(j).segment_type := p_kf_job_seg_tab(j).segment_type;
2195 l_segment_tab(j).window_prompt := p_kf_job_seg_tab(j).window_prompt;
2199 l_segment_tab(j).vs_enable_longlist := p_kf_job_seg_tab(j).vs_enable_longlist;
2196 l_segment_tab(j).vs_value_set_name := p_kf_job_seg_tab(j).vs_value_set_name;
2197 l_segment_tab(j).vs_description := p_kf_job_seg_tab(j).vs_description;
2198 l_segment_tab(j).vs_security_available := p_kf_job_seg_tab(j).vs_security_available;
2200 l_segment_tab(j).vs_format_type := p_kf_job_seg_tab(j).vs_format_type;
2201 l_segment_tab(j).vs_maximum_size := p_kf_job_seg_tab(j).vs_maximum_size;
2202 l_segment_tab(j).vs_precision := p_kf_job_seg_tab(j).vs_precision;
2203 l_segment_tab(j).vs_numbers_only := p_kf_job_seg_tab(j).vs_numbers_only;
2204 l_segment_tab(j).vs_uppercase_only := p_kf_job_seg_tab(j).vs_uppercase_only;
2205 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_job_seg_tab(j).vs_right_justify_zero_fill;
2206 l_segment_tab(j).vs_min_value := p_kf_job_seg_tab(j).vs_min_value;
2207 l_segment_tab(j).vs_max_value := p_kf_job_seg_tab(j).vs_max_value;
2208
2209 end loop;
2210 end if;
2211
2212 IF p_kf_job_tab.count > 0 THEN
2213 l_str_seg_append_clob_for_pv := get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Job');
2214 END if;
2215 l_structure_tab.delete;
2216 l_segment_tab.delete;
2217 ----------------------------------------------------------------------------------------------------------------------------------
2218
2219 /* make a copy of job rv before calling*/
2220 IF p_kf_job_rv_tab.count > 0 THEN
2221 for j in p_kf_job_rv_tab.first ..
2222 p_kf_job_rv_tab.last loop
2223 l_structure_tab(j).appl_short_name := p_kf_job_rv_tab(j).appl_short_name;
2224 l_structure_tab(j).flex_code := p_kf_job_rv_tab(j).flex_code;
2225 l_structure_tab(j).structure_code := p_kf_job_rv_tab(j).structure_code;
2226 l_structure_tab(j).structure_title := p_kf_job_rv_tab(j).structure_title;
2227 l_structure_tab(j).description := p_kf_job_rv_tab(j).description;
2228 end loop;
2229 end if;
2230 IF p_kf_job_rv_seg_tab.count > 0 THEN
2231 for j in p_kf_job_rv_seg_tab.first ..
2232 p_kf_job_rv_seg_tab.last loop
2233
2234 l_segment_tab(j).appl_short_name := p_kf_job_rv_seg_tab(j).appl_short_name;
2235 l_segment_tab(j).flex_code := p_kf_job_rv_seg_tab(j).flex_code;
2236 l_segment_tab(j).structure_code := p_kf_job_rv_seg_tab(j).structure_code;
2237 l_segment_tab(j).segment_name := p_kf_job_rv_seg_tab(j).segment_name;
2238 l_segment_tab(j).column_name := p_kf_job_rv_seg_tab(j).column_name;
2239 l_segment_tab(j).segment_number := p_kf_job_rv_seg_tab(j).segment_number;
2240 l_segment_tab(j).value_set := p_kf_job_rv_seg_tab(j).value_set;
2244 l_segment_tab(j).vs_value_set_name := p_kf_job_rv_seg_tab(j).vs_value_set_name;
2241 l_segment_tab(j).lov_prompt := p_kf_job_rv_seg_tab(j).lov_prompt;
2242 l_segment_tab(j).segment_type := p_kf_job_rv_seg_tab(j).segment_type;
2243 l_segment_tab(j).window_prompt := p_kf_job_rv_seg_tab(j).window_prompt;
2245 l_segment_tab(j).vs_description := p_kf_job_rv_seg_tab(j).vs_description;
2246 l_segment_tab(j).vs_security_available := p_kf_job_rv_seg_tab(j).vs_security_available;
2247 l_segment_tab(j).vs_enable_longlist := p_kf_job_rv_seg_tab(j).vs_enable_longlist;
2248 l_segment_tab(j).vs_format_type := p_kf_job_rv_seg_tab(j).vs_format_type;
2249 l_segment_tab(j).vs_maximum_size := p_kf_job_rv_seg_tab(j).vs_maximum_size;
2250 l_segment_tab(j).vs_precision := p_kf_job_rv_seg_tab(j).vs_precision;
2251 l_segment_tab(j).vs_numbers_only := p_kf_job_rv_seg_tab(j).vs_numbers_only;
2252 l_segment_tab(j).vs_uppercase_only := p_kf_job_rv_seg_tab(j).vs_uppercase_only;
2253 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_job_rv_seg_tab(j).vs_right_justify_zero_fill;
2254 l_segment_tab(j).vs_min_value := p_kf_job_rv_seg_tab(j).vs_min_value;
2255 l_segment_tab(j).vs_max_value := p_kf_job_rv_seg_tab(j).vs_max_value;
2256
2257 end loop;
2258 end if;
2259
2260 IF p_kf_job_rv_tab.count > 0 THEN
2261 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv || get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Job');
2262 END if;
2263
2264 l_structure_tab.delete;
2265 l_segment_tab.delete;
2266
2267 ----------------------------------------------------------------------------------------------------------------------------------
2268
2269 /* make a copy of job no rv before calling*/
2270 IF p_kf_job_no_rv_tab.count > 0 THEN
2271 for j in p_kf_job_no_rv_tab.first ..
2272 p_kf_job_no_rv_tab.last loop
2273 l_structure_tab(j).appl_short_name := p_kf_job_no_rv_tab(j).appl_short_name;
2274 l_structure_tab(j).flex_code := p_kf_job_no_rv_tab(j).flex_code;
2275 l_structure_tab(j).structure_code := p_kf_job_no_rv_tab(j).structure_code;
2276 l_structure_tab(j).structure_title := p_kf_job_no_rv_tab(j).structure_title;
2277 l_structure_tab(j).description := p_kf_job_no_rv_tab(j).description;
2278 end loop;
2279 end if;
2283
2280 IF p_kf_job_no_rv_seg_tab.count > 0 THEN
2281 for j in p_kf_job_no_rv_seg_tab.first ..
2282 p_kf_job_no_rv_seg_tab.last loop
2284 l_segment_tab(j).appl_short_name := p_kf_job_no_rv_seg_tab(j).appl_short_name;
2285 l_segment_tab(j).flex_code := p_kf_job_no_rv_seg_tab(j).flex_code;
2286 l_segment_tab(j).structure_code := p_kf_job_no_rv_seg_tab(j).structure_code;
2287 l_segment_tab(j).segment_name := p_kf_job_no_rv_seg_tab(j).segment_name;
2288 l_segment_tab(j).column_name := p_kf_job_no_rv_seg_tab(j).column_name;
2289 l_segment_tab(j).segment_number := p_kf_job_no_rv_seg_tab(j).segment_number;
2290 l_segment_tab(j).value_set := p_kf_job_no_rv_seg_tab(j).value_set;
2291 l_segment_tab(j).lov_prompt := p_kf_job_no_rv_seg_tab(j).lov_prompt;
2292 l_segment_tab(j).segment_type := p_kf_job_no_rv_seg_tab(j).segment_type;
2293 l_segment_tab(j).window_prompt := p_kf_job_no_rv_seg_tab(j).window_prompt;
2294 l_segment_tab(j).vs_value_set_name := p_kf_job_no_rv_seg_tab(j).vs_value_set_name;
2295 l_segment_tab(j).vs_description := p_kf_job_no_rv_seg_tab(j).vs_description;
2296 l_segment_tab(j).vs_security_available := p_kf_job_no_rv_seg_tab(j).vs_security_available;
2297 l_segment_tab(j).vs_enable_longlist := p_kf_job_no_rv_seg_tab(j).vs_enable_longlist;
2298 l_segment_tab(j).vs_format_type := p_kf_job_no_rv_seg_tab(j).vs_format_type;
2299 l_segment_tab(j).vs_maximum_size := p_kf_job_no_rv_seg_tab(j).vs_maximum_size;
2300 l_segment_tab(j).vs_precision := p_kf_job_no_rv_seg_tab(j).vs_precision;
2301 l_segment_tab(j).vs_numbers_only := p_kf_job_no_rv_seg_tab(j).vs_numbers_only;
2302 l_segment_tab(j).vs_uppercase_only := p_kf_job_no_rv_seg_tab(j).vs_uppercase_only;
2303 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_job_no_rv_seg_tab(j).vs_right_justify_zero_fill;
2304 l_segment_tab(j).vs_min_value := p_kf_job_no_rv_seg_tab(j).vs_min_value;
2305 l_segment_tab(j).vs_max_value := p_kf_job_no_rv_seg_tab(j).vs_max_value;
2306
2307 end loop;
2308 end if;
2309
2310 IF p_kf_job_no_rv_tab.count > 0 THEN
2311 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv || get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Job');
2312 END if;
2313
2314 l_structure_tab.delete;
2315 l_segment_tab.delete;
2316
2317
2318 --------------------------------------------------------------------------------------------------------------------------------------------
2319
2320 /* make a copy of pos before calling*/
2321 IF p_kf_pos_tab.count > 0 THEN
2322 for j in p_kf_pos_tab.first ..
2323 p_kf_pos_tab.last loop
2324 l_structure_tab(j).appl_short_name := p_kf_pos_tab(j).appl_short_name;
2325 l_structure_tab(j).flex_code := p_kf_pos_tab(j).flex_code;
2326 l_structure_tab(j).structure_code := p_kf_pos_tab(j).structure_code;
2327 l_structure_tab(j).structure_title := p_kf_pos_tab(j).structure_title;
2328 l_structure_tab(j).description := p_kf_pos_tab(j).description;
2329 end loop;
2330 end if;
2331 IF p_kf_pos_seg_tab.count > 0 THEN
2332 for j in p_kf_pos_seg_tab.first ..
2333 p_kf_pos_seg_tab.last loop
2334 l_segment_tab(j).appl_short_name := p_kf_pos_seg_tab(j).appl_short_name;
2335 l_segment_tab(j).flex_code := p_kf_pos_seg_tab(j).flex_code;
2336 l_segment_tab(j).structure_code := p_kf_pos_seg_tab(j).structure_code;
2337 l_segment_tab(j).segment_name := p_kf_pos_seg_tab(j).segment_name;
2338 l_segment_tab(j).column_name := p_kf_pos_seg_tab(j).column_name;
2339 l_segment_tab(j).segment_number := p_kf_pos_seg_tab(j).segment_number;
2340 l_segment_tab(j).value_set := p_kf_pos_seg_tab(j).value_set;
2341 l_segment_tab(j).lov_prompt := p_kf_pos_seg_tab(j).lov_prompt;
2342 l_segment_tab(j).segment_type := p_kf_pos_seg_tab(j).segment_type;
2343 l_segment_tab(j).window_prompt := p_kf_pos_seg_tab(j).window_prompt;
2344 l_segment_tab(j).vs_value_set_name := p_kf_pos_seg_tab(j).vs_value_set_name;
2345 l_segment_tab(j).vs_description := p_kf_pos_seg_tab(j).vs_description;
2346 l_segment_tab(j).vs_security_available := p_kf_pos_seg_tab(j).vs_security_available;
2347 l_segment_tab(j).vs_enable_longlist := p_kf_pos_seg_tab(j).vs_enable_longlist;
2348 l_segment_tab(j).vs_format_type := p_kf_pos_seg_tab(j).vs_format_type;
2349 l_segment_tab(j).vs_maximum_size := p_kf_pos_seg_tab(j).vs_maximum_size;
2350 l_segment_tab(j).vs_precision := p_kf_pos_seg_tab(j).vs_precision;
2351 l_segment_tab(j).vs_numbers_only := p_kf_pos_seg_tab(j).vs_numbers_only;
2352 l_segment_tab(j).vs_uppercase_only := p_kf_pos_seg_tab(j).vs_uppercase_only;
2353 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_pos_seg_tab(j).vs_right_justify_zero_fill;
2354 l_segment_tab(j).vs_min_value := p_kf_pos_seg_tab(j).vs_min_value;
2355 l_segment_tab(j).vs_max_value := p_kf_pos_seg_tab(j).vs_max_value;
2356
2360 IF p_kf_pos_tab.count > 0 THEN
2357 end loop;
2358 end if;
2359
2361 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Pos');
2362 END if;
2363 l_structure_tab.delete;
2364 l_segment_tab.delete;
2365
2366 ----------------------------------------------------------------------------------------------------------------------------------
2367
2368 /* make a copy of pos rv before calling*/
2369 IF p_kf_pos_rv_tab.count > 0 THEN
2370 for j in p_kf_pos_rv_tab.first ..
2371 p_kf_pos_rv_tab.last loop
2372 l_structure_tab(j).appl_short_name := p_kf_pos_rv_tab(j).appl_short_name;
2373 l_structure_tab(j).flex_code := p_kf_pos_rv_tab(j).flex_code;
2374 l_structure_tab(j).structure_code := p_kf_pos_rv_tab(j).structure_code;
2375 l_structure_tab(j).structure_title := p_kf_pos_rv_tab(j).structure_title;
2376 l_structure_tab(j).description := p_kf_pos_rv_tab(j).description;
2377 end loop;
2378 end if;
2379 IF p_kf_pos_rv_seg_tab.count > 0 THEN
2380 for j in p_kf_pos_rv_seg_tab.first ..
2381 p_kf_pos_rv_seg_tab.last loop
2382 l_segment_tab(j).appl_short_name := p_kf_pos_rv_seg_tab(j).appl_short_name;
2383 l_segment_tab(j).flex_code := p_kf_pos_rv_seg_tab(j).flex_code;
2384 l_segment_tab(j).structure_code := p_kf_pos_rv_seg_tab(j).structure_code;
2385 l_segment_tab(j).segment_name := p_kf_pos_rv_seg_tab(j).segment_name;
2386 l_segment_tab(j).column_name := p_kf_pos_rv_seg_tab(j).column_name;
2387 l_segment_tab(j).segment_number := p_kf_pos_rv_seg_tab(j).segment_number;
2388 l_segment_tab(j).value_set := p_kf_pos_rv_seg_tab(j).value_set;
2389 l_segment_tab(j).lov_prompt := p_kf_pos_rv_seg_tab(j).lov_prompt;
2390 l_segment_tab(j).segment_type := p_kf_pos_rv_seg_tab(j).segment_type;
2391 l_segment_tab(j).window_prompt := p_kf_pos_rv_seg_tab(j).window_prompt;
2392 l_segment_tab(j).vs_value_set_name := p_kf_pos_rv_seg_tab(j).vs_value_set_name;
2393 l_segment_tab(j).vs_description := p_kf_pos_rv_seg_tab(j).vs_description;
2394 l_segment_tab(j).vs_security_available := p_kf_pos_rv_seg_tab(j).vs_security_available;
2395 l_segment_tab(j).vs_enable_longlist := p_kf_pos_rv_seg_tab(j).vs_enable_longlist;
2396 l_segment_tab(j).vs_format_type := p_kf_pos_rv_seg_tab(j).vs_format_type;
2397 l_segment_tab(j).vs_maximum_size := p_kf_pos_rv_seg_tab(j).vs_maximum_size;
2398 l_segment_tab(j).vs_precision := p_kf_pos_rv_seg_tab(j).vs_precision;
2399 l_segment_tab(j).vs_numbers_only := p_kf_pos_rv_seg_tab(j).vs_numbers_only;
2400 l_segment_tab(j).vs_uppercase_only := p_kf_pos_rv_seg_tab(j).vs_uppercase_only;
2401 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_pos_rv_seg_tab(j).vs_right_justify_zero_fill;
2402 l_segment_tab(j).vs_min_value := p_kf_pos_rv_seg_tab(j).vs_min_value;
2403 l_segment_tab(j).vs_max_value := p_kf_pos_rv_seg_tab(j).vs_max_value;
2404
2405 end loop;
2406 end if;
2407
2408 IF p_kf_pos_rv_tab.count > 0 THEN
2409 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv || get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Pos');
2410 END if;
2411 l_structure_tab.delete;
2412 l_segment_tab.delete;
2413
2414 ----------------------------------------------------------------------------------------------------------------------------------
2415
2416 /* make a copy of pos no rv before calling*/
2417 IF p_kf_pos_no_rv_tab.count > 0 THEN
2418 for j in p_kf_pos_no_rv_tab.first ..
2419 p_kf_pos_no_rv_tab.last loop
2420 l_structure_tab(j).appl_short_name := p_kf_pos_no_rv_tab(j).appl_short_name;
2421 l_structure_tab(j).flex_code := p_kf_pos_no_rv_tab(j).flex_code;
2422 l_structure_tab(j).structure_code := p_kf_pos_no_rv_tab(j).structure_code;
2423 l_structure_tab(j).structure_title := p_kf_pos_no_rv_tab(j).structure_title;
2424 l_structure_tab(j).description := p_kf_pos_no_rv_tab(j).description;
2425 end loop;
2426 end if;
2427 IF p_kf_pos_no_rv_seg_tab.count > 0 THEN
2428 for j in p_kf_pos_no_rv_seg_tab.first ..
2429 p_kf_pos_no_rv_seg_tab.last loop
2430 l_segment_tab(j).appl_short_name := p_kf_pos_no_rv_seg_tab(j).appl_short_name;
2431 l_segment_tab(j).flex_code := p_kf_pos_no_rv_seg_tab(j).flex_code;
2432 l_segment_tab(j).structure_code := p_kf_pos_no_rv_seg_tab(j).structure_code;
2433 l_segment_tab(j).segment_name := p_kf_pos_no_rv_seg_tab(j).segment_name;
2434 l_segment_tab(j).column_name := p_kf_pos_no_rv_seg_tab(j).column_name;
2435 l_segment_tab(j).segment_number := p_kf_pos_no_rv_seg_tab(j).segment_number;
2436 l_segment_tab(j).value_set := p_kf_pos_no_rv_seg_tab(j).value_set;
2437 l_segment_tab(j).lov_prompt := p_kf_pos_no_rv_seg_tab(j).lov_prompt;
2438 l_segment_tab(j).segment_type := p_kf_pos_no_rv_seg_tab(j).segment_type;
2439 l_segment_tab(j).window_prompt := p_kf_pos_no_rv_seg_tab(j).window_prompt;
2440 l_segment_tab(j).vs_value_set_name := p_kf_pos_no_rv_seg_tab(j).vs_value_set_name;
2441 l_segment_tab(j).vs_description := p_kf_pos_no_rv_seg_tab(j).vs_description;
2442 l_segment_tab(j).vs_security_available := p_kf_pos_no_rv_seg_tab(j).vs_security_available;
2443 l_segment_tab(j).vs_enable_longlist := p_kf_pos_no_rv_seg_tab(j).vs_enable_longlist;
2444 l_segment_tab(j).vs_format_type := p_kf_pos_no_rv_seg_tab(j).vs_format_type;
2445 l_segment_tab(j).vs_maximum_size := p_kf_pos_no_rv_seg_tab(j).vs_maximum_size;
2446 l_segment_tab(j).vs_precision := p_kf_pos_no_rv_seg_tab(j).vs_precision;
2447 l_segment_tab(j).vs_numbers_only := p_kf_pos_no_rv_seg_tab(j).vs_numbers_only;
2448 l_segment_tab(j).vs_uppercase_only := p_kf_pos_no_rv_seg_tab(j).vs_uppercase_only;
2449 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_pos_no_rv_seg_tab(j).vs_right_justify_zero_fill;
2450 l_segment_tab(j).vs_min_value := p_kf_pos_no_rv_seg_tab(j).vs_min_value;
2451 l_segment_tab(j).vs_max_value := p_kf_pos_no_rv_seg_tab(j).vs_max_value;
2452
2453 end loop;
2457 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv || get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Pos');
2454 end if;
2455
2456 IF p_kf_pos_no_rv_tab.count > 0 THEN
2458 END if;
2459 l_structure_tab.delete;
2460 l_segment_tab.delete;
2461
2462 --------------------------------------------------------------------------------------------------------------------------------------------
2463
2464 /* make a copy of grd before calling*/
2465 IF p_kf_grd_tab.count > 0 THEN
2466 for j in p_kf_grd_tab.first ..
2467 p_kf_grd_tab.last loop
2468 l_structure_tab(j).appl_short_name := p_kf_grd_tab(j).appl_short_name;
2469 l_structure_tab(j).flex_code := p_kf_grd_tab(j).flex_code;
2470 l_structure_tab(j).structure_code := p_kf_grd_tab(j).structure_code;
2471 l_structure_tab(j).structure_title := p_kf_grd_tab(j).structure_title;
2472 l_structure_tab(j).description := p_kf_grd_tab(j).description;
2473 end loop;
2474 end if;
2475 IF p_kf_grd_seg_tab.count > 0 THEN
2476 for j in p_kf_grd_seg_tab.first ..
2477 p_kf_grd_seg_tab.last loop
2478 l_segment_tab(j).appl_short_name := p_kf_grd_seg_tab(j).appl_short_name;
2479 l_segment_tab(j).flex_code := p_kf_grd_seg_tab(j).flex_code;
2480 l_segment_tab(j).structure_code := p_kf_grd_seg_tab(j).structure_code;
2481 l_segment_tab(j).segment_name := p_kf_grd_seg_tab(j).segment_name;
2482 l_segment_tab(j).column_name := p_kf_grd_seg_tab(j).column_name;
2483 l_segment_tab(j).segment_number := p_kf_grd_seg_tab(j).segment_number;
2484 l_segment_tab(j).value_set := p_kf_grd_seg_tab(j).value_set;
2485 l_segment_tab(j).lov_prompt := p_kf_grd_seg_tab(j).lov_prompt;
2486 l_segment_tab(j).segment_type := p_kf_grd_seg_tab(j).segment_type;
2487 l_segment_tab(j).window_prompt := p_kf_grd_seg_tab(j).window_prompt;
2488 l_segment_tab(j).vs_value_set_name := p_kf_grd_seg_tab(j).vs_value_set_name;
2489 l_segment_tab(j).vs_description := p_kf_grd_seg_tab(j).vs_description;
2490 l_segment_tab(j).vs_security_available := p_kf_grd_seg_tab(j).vs_security_available;
2491 l_segment_tab(j).vs_enable_longlist := p_kf_grd_seg_tab(j).vs_enable_longlist;
2492 l_segment_tab(j).vs_format_type := p_kf_grd_seg_tab(j).vs_format_type;
2493 l_segment_tab(j).vs_maximum_size := p_kf_grd_seg_tab(j).vs_maximum_size;
2494 l_segment_tab(j).vs_precision := p_kf_grd_seg_tab(j).vs_precision;
2495 l_segment_tab(j).vs_numbers_only := p_kf_grd_seg_tab(j).vs_numbers_only;
2496 l_segment_tab(j).vs_uppercase_only := p_kf_grd_seg_tab(j).vs_uppercase_only;
2497 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_grd_seg_tab(j).vs_right_justify_zero_fill;
2498 l_segment_tab(j).vs_min_value := p_kf_grd_seg_tab(j).vs_min_value;
2499 l_segment_tab(j).vs_max_value := p_kf_grd_seg_tab(j).vs_max_value;
2500
2501 end loop;
2502 end if;
2503
2504 IF p_kf_grd_tab.count > 0 THEN
2505 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Grd');
2506 END if;
2507 l_structure_tab.delete;
2508 l_segment_tab.delete;
2509
2510 ----------------------------------------------------------------------------------------------------------------------------------
2511
2512 /* make a copy of grd rv before calling*/
2513 IF p_kf_grd_rv_tab.count > 0 THEN
2514 for j in p_kf_grd_rv_tab.first ..
2515 p_kf_grd_rv_tab.last loop
2516 l_structure_tab(j).appl_short_name := p_kf_grd_rv_tab(j).appl_short_name;
2517 l_structure_tab(j).flex_code := p_kf_grd_rv_tab(j).flex_code;
2518 l_structure_tab(j).structure_code := p_kf_grd_rv_tab(j).structure_code;
2519 l_structure_tab(j).structure_title := p_kf_grd_rv_tab(j).structure_title;
2520 l_structure_tab(j).description := p_kf_grd_rv_tab(j).description;
2521 end loop;
2522 end if;
2523 IF p_kf_grd_rv_seg_tab.count > 0 THEN
2524 for j in p_kf_grd_rv_seg_tab.first ..
2525 p_kf_grd_rv_seg_tab.last loop
2526 l_segment_tab(j).appl_short_name := p_kf_grd_rv_seg_tab(j).appl_short_name;
2527 l_segment_tab(j).flex_code := p_kf_grd_rv_seg_tab(j).flex_code;
2528 l_segment_tab(j).structure_code := p_kf_grd_rv_seg_tab(j).structure_code;
2529 l_segment_tab(j).segment_name := p_kf_grd_rv_seg_tab(j).segment_name;
2530 l_segment_tab(j).column_name := p_kf_grd_rv_seg_tab(j).column_name;
2531 l_segment_tab(j).segment_number := p_kf_grd_rv_seg_tab(j).segment_number;
2532 l_segment_tab(j).value_set := p_kf_grd_rv_seg_tab(j).value_set;
2533 l_segment_tab(j).lov_prompt := p_kf_grd_rv_seg_tab(j).lov_prompt;
2534 l_segment_tab(j).segment_type := p_kf_grd_rv_seg_tab(j).segment_type;
2535 l_segment_tab(j).window_prompt := p_kf_grd_rv_seg_tab(j).window_prompt;
2536 l_segment_tab(j).vs_value_set_name := p_kf_grd_rv_seg_tab(j).vs_value_set_name;
2537 l_segment_tab(j).vs_description := p_kf_grd_rv_seg_tab(j).vs_description;
2538 l_segment_tab(j).vs_security_available := p_kf_grd_rv_seg_tab(j).vs_security_available;
2539 l_segment_tab(j).vs_enable_longlist := p_kf_grd_rv_seg_tab(j).vs_enable_longlist;
2540 l_segment_tab(j).vs_format_type := p_kf_grd_rv_seg_tab(j).vs_format_type;
2541 l_segment_tab(j).vs_maximum_size := p_kf_grd_rv_seg_tab(j).vs_maximum_size;
2542 l_segment_tab(j).vs_precision := p_kf_grd_rv_seg_tab(j).vs_precision;
2543 l_segment_tab(j).vs_numbers_only := p_kf_grd_rv_seg_tab(j).vs_numbers_only;
2544 l_segment_tab(j).vs_uppercase_only := p_kf_grd_rv_seg_tab(j).vs_uppercase_only;
2545 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_grd_rv_seg_tab(j).vs_right_justify_zero_fill;
2546 l_segment_tab(j).vs_min_value := p_kf_grd_rv_seg_tab(j).vs_min_value;
2547 l_segment_tab(j).vs_max_value := p_kf_grd_rv_seg_tab(j).vs_max_value;
2548
2549 end loop;
2550 end if;
2551
2552 IF p_kf_grd_rv_tab.count > 0 THEN
2553 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv || get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Grd');
2554 END if;
2555 l_structure_tab.delete;
2559
2556 l_segment_tab.delete;
2557
2558 ----------------------------------------------------------------------------------------------------------------------------------
2560 /* make a copy of grd no rv before calling*/
2561 IF p_kf_grd_no_rv_tab.count > 0 THEN
2562 for j in p_kf_grd_no_rv_tab.first ..
2563 p_kf_grd_no_rv_tab.last loop
2564 l_structure_tab(j).appl_short_name := p_kf_grd_no_rv_tab(j).appl_short_name;
2565 l_structure_tab(j).flex_code := p_kf_grd_no_rv_tab(j).flex_code;
2566 l_structure_tab(j).structure_code := p_kf_grd_no_rv_tab(j).structure_code;
2567 l_structure_tab(j).structure_title := p_kf_grd_no_rv_tab(j).structure_title;
2568 l_structure_tab(j).description := p_kf_grd_no_rv_tab(j).description;
2569 end loop;
2570 end if;
2571 IF p_kf_grd_no_rv_seg_tab.count > 0 THEN
2572 for j in p_kf_grd_no_rv_seg_tab.first ..
2573 p_kf_grd_no_rv_seg_tab.last loop
2574 l_segment_tab(j).appl_short_name := p_kf_grd_no_rv_seg_tab(j).appl_short_name;
2575 l_segment_tab(j).flex_code := p_kf_grd_no_rv_seg_tab(j).flex_code;
2576 l_segment_tab(j).structure_code := p_kf_grd_no_rv_seg_tab(j).structure_code;
2577 l_segment_tab(j).segment_name := p_kf_grd_no_rv_seg_tab(j).segment_name;
2578 l_segment_tab(j).column_name := p_kf_grd_no_rv_seg_tab(j).column_name;
2579 l_segment_tab(j).segment_number := p_kf_grd_no_rv_seg_tab(j).segment_number;
2580 l_segment_tab(j).value_set := p_kf_grd_no_rv_seg_tab(j).value_set;
2581 l_segment_tab(j).lov_prompt := p_kf_grd_no_rv_seg_tab(j).lov_prompt;
2582 l_segment_tab(j).segment_type := p_kf_grd_no_rv_seg_tab(j).segment_type;
2583 l_segment_tab(j).window_prompt := p_kf_grd_no_rv_seg_tab(j).window_prompt;
2584 l_segment_tab(j).vs_value_set_name := p_kf_grd_no_rv_seg_tab(j).vs_value_set_name;
2585 l_segment_tab(j).vs_description := p_kf_grd_no_rv_seg_tab(j).vs_description;
2586 l_segment_tab(j).vs_security_available := p_kf_grd_no_rv_seg_tab(j).vs_security_available;
2587 l_segment_tab(j).vs_enable_longlist := p_kf_grd_no_rv_seg_tab(j).vs_enable_longlist;
2588 l_segment_tab(j).vs_format_type := p_kf_grd_no_rv_seg_tab(j).vs_format_type;
2589 l_segment_tab(j).vs_maximum_size := p_kf_grd_no_rv_seg_tab(j).vs_maximum_size;
2590 l_segment_tab(j).vs_precision := p_kf_grd_no_rv_seg_tab(j).vs_precision;
2591 l_segment_tab(j).vs_numbers_only := p_kf_grd_no_rv_seg_tab(j).vs_numbers_only;
2592 l_segment_tab(j).vs_uppercase_only := p_kf_grd_no_rv_seg_tab(j).vs_uppercase_only;
2593 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_grd_no_rv_seg_tab(j).vs_right_justify_zero_fill;
2594 l_segment_tab(j).vs_min_value := p_kf_grd_no_rv_seg_tab(j).vs_min_value;
2595 l_segment_tab(j).vs_max_value := p_kf_grd_no_rv_seg_tab(j).vs_max_value;
2596
2597 end loop;
2598 end if;
2599
2600 IF p_kf_grd_no_rv_tab.count > 0 THEN
2601 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv || get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Grd');
2602 END if;
2603 l_structure_tab.delete;
2604 l_segment_tab.delete;
2605
2606 --------------------------------------------------------------------------------------------------------------------------------------------
2607
2608 /* make a copy of grp before calling*/
2609 IF p_kf_grp_tab.count > 0 THEN
2610 for j in p_kf_grp_tab.first ..
2611 p_kf_grp_tab.last loop
2612 l_structure_tab(j).appl_short_name := p_kf_grp_tab(j).appl_short_name;
2613 l_structure_tab(j).flex_code := p_kf_grp_tab(j).flex_code;
2614 l_structure_tab(j).structure_code := p_kf_grp_tab(j).structure_code;
2615 l_structure_tab(j).structure_title := p_kf_grp_tab(j).structure_title;
2616 l_structure_tab(j).description := p_kf_grp_tab(j).description;
2617 end loop;
2618 end if;
2619 IF p_kf_grp_seg_tab.count > 0 THEN
2620 for j in p_kf_grp_seg_tab.first ..
2621 p_kf_grp_seg_tab.last loop
2622 l_segment_tab(j).appl_short_name := p_kf_grp_seg_tab(j).appl_short_name;
2623 l_segment_tab(j).flex_code := p_kf_grp_seg_tab(j).flex_code;
2624 l_segment_tab(j).structure_code := p_kf_grp_seg_tab(j).structure_code;
2625 l_segment_tab(j).segment_name := p_kf_grp_seg_tab(j).segment_name;
2626 l_segment_tab(j).column_name := p_kf_grp_seg_tab(j).column_name;
2627 l_segment_tab(j).segment_number := p_kf_grp_seg_tab(j).segment_number;
2628 l_segment_tab(j).value_set := p_kf_grp_seg_tab(j).value_set;
2629 l_segment_tab(j).lov_prompt := p_kf_grp_seg_tab(j).lov_prompt;
2630 l_segment_tab(j).segment_type := p_kf_grp_seg_tab(j).segment_type;
2631 l_segment_tab(j).window_prompt := p_kf_grp_seg_tab(j).window_prompt;
2632 l_segment_tab(j).vs_value_set_name := p_kf_grp_seg_tab(j).vs_value_set_name;
2633 l_segment_tab(j).vs_description := p_kf_grp_seg_tab(j).vs_description;
2634 l_segment_tab(j).vs_security_available := p_kf_grp_seg_tab(j).vs_security_available;
2635 l_segment_tab(j).vs_enable_longlist := p_kf_grp_seg_tab(j).vs_enable_longlist;
2636 l_segment_tab(j).vs_format_type := p_kf_grp_seg_tab(j).vs_format_type;
2637 l_segment_tab(j).vs_maximum_size := p_kf_grp_seg_tab(j).vs_maximum_size;
2638 l_segment_tab(j).vs_precision := p_kf_grp_seg_tab(j).vs_precision;
2639 l_segment_tab(j).vs_numbers_only := p_kf_grp_seg_tab(j).vs_numbers_only;
2640 l_segment_tab(j).vs_uppercase_only := p_kf_grp_seg_tab(j).vs_uppercase_only;
2641 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_grp_seg_tab(j).vs_right_justify_zero_fill;
2642 l_segment_tab(j).vs_min_value := p_kf_grp_seg_tab(j).vs_min_value;
2643 l_segment_tab(j).vs_max_value := p_kf_grp_seg_tab(j).vs_max_value;
2644
2645 end loop;
2646 end if;
2647
2648 IF p_kf_grp_tab.count > 0 THEN
2649 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Grp');
2650 END if;
2651 l_structure_tab.delete;
2652 l_segment_tab.delete;
2653
2654 --------------------------------------------------------------------------------------------------------------------------------------------
2655
2659 p_kf_cmp_tab.last loop
2656 /* make a copy of cmp before calling*/
2657 IF p_kf_cmp_tab.count > 0 THEN
2658 for j in p_kf_cmp_tab.first ..
2660 l_structure_tab(j).appl_short_name := p_kf_cmp_tab(j).appl_short_name;
2661 l_structure_tab(j).flex_code := p_kf_cmp_tab(j).flex_code;
2662 l_structure_tab(j).structure_code := p_kf_cmp_tab(j).structure_code;
2663 l_structure_tab(j).structure_title := p_kf_cmp_tab(j).structure_title;
2664 l_structure_tab(j).description := p_kf_cmp_tab(j).description;
2665 end loop;
2666 end if;
2667 IF p_kf_cmp_seg_tab.count > 0 THEN
2668 for j in p_kf_cmp_seg_tab.first ..
2669 p_kf_cmp_seg_tab.last loop
2670 l_segment_tab(j).appl_short_name := p_kf_cmp_seg_tab(j).appl_short_name;
2671 l_segment_tab(j).flex_code := p_kf_cmp_seg_tab(j).flex_code;
2672 l_segment_tab(j).structure_code := p_kf_cmp_seg_tab(j).structure_code;
2673 l_segment_tab(j).segment_name := p_kf_cmp_seg_tab(j).segment_name;
2674 l_segment_tab(j).column_name := p_kf_cmp_seg_tab(j).column_name;
2675 l_segment_tab(j).segment_number := p_kf_cmp_seg_tab(j).segment_number;
2676 l_segment_tab(j).value_set := p_kf_cmp_seg_tab(j).value_set;
2677 l_segment_tab(j).lov_prompt := p_kf_cmp_seg_tab(j).lov_prompt;
2678 l_segment_tab(j).segment_type := p_kf_cmp_seg_tab(j).segment_type;
2679 l_segment_tab(j).window_prompt := p_kf_cmp_seg_tab(j).window_prompt;
2680 l_segment_tab(j).vs_value_set_name := p_kf_cmp_seg_tab(j).vs_value_set_name;
2681 l_segment_tab(j).vs_description := p_kf_cmp_seg_tab(j).vs_description;
2682 l_segment_tab(j).vs_security_available := p_kf_cmp_seg_tab(j).vs_security_available;
2683 l_segment_tab(j).vs_enable_longlist := p_kf_cmp_seg_tab(j).vs_enable_longlist;
2684 l_segment_tab(j).vs_format_type := p_kf_cmp_seg_tab(j).vs_format_type;
2685 l_segment_tab(j).vs_maximum_size := p_kf_cmp_seg_tab(j).vs_maximum_size;
2686 l_segment_tab(j).vs_precision := p_kf_cmp_seg_tab(j).vs_precision;
2687 l_segment_tab(j).vs_numbers_only := p_kf_cmp_seg_tab(j).vs_numbers_only;
2688 l_segment_tab(j).vs_uppercase_only := p_kf_cmp_seg_tab(j).vs_uppercase_only;
2689 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_cmp_seg_tab(j).vs_right_justify_zero_fill;
2690 l_segment_tab(j).vs_min_value := p_kf_cmp_seg_tab(j).vs_min_value;
2691 l_segment_tab(j).vs_max_value := p_kf_cmp_seg_tab(j).vs_max_value;
2692
2693 end loop;
2694 end if;
2695
2696 IF p_kf_cmp_tab.count > 0 THEN
2697 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Cmp');
2698 END if;
2702 --------------------------------------------------------------------------------------------------------------------------------------------
2699 l_structure_tab.delete;
2700 l_segment_tab.delete;
2701
2703
2704 /* make a copy of cost before calling*/
2705 IF p_kf_cost_tab.count > 0 THEN
2706 for j in p_kf_cost_tab.first ..
2707 p_kf_cost_tab.last loop
2708 l_structure_tab(j).appl_short_name := p_kf_cost_tab(j).appl_short_name;
2709 l_structure_tab(j).flex_code := p_kf_cost_tab(j).flex_code;
2710 l_structure_tab(j).structure_code := p_kf_cost_tab(j).structure_code;
2711 l_structure_tab(j).structure_title := p_kf_cost_tab(j).structure_title;
2712 l_structure_tab(j).description := p_kf_cost_tab(j).description;
2713 end loop;
2714 end if;
2715 IF p_kf_cost_seg_tab.count > 0 THEN
2716 for j in p_kf_cost_seg_tab.first ..
2717 p_kf_cost_seg_tab.last loop
2718 l_segment_tab(j).appl_short_name := p_kf_cost_seg_tab(j).appl_short_name;
2719 l_segment_tab(j).flex_code := p_kf_cost_seg_tab(j).flex_code;
2720 l_segment_tab(j).structure_code := p_kf_cost_seg_tab(j).structure_code;
2721 l_segment_tab(j).segment_name := p_kf_cost_seg_tab(j).segment_name;
2722 l_segment_tab(j).column_name := p_kf_cost_seg_tab(j).column_name;
2723 l_segment_tab(j).segment_number := p_kf_cost_seg_tab(j).segment_number;
2724 l_segment_tab(j).value_set := p_kf_cost_seg_tab(j).value_set;
2725 l_segment_tab(j).lov_prompt := p_kf_cost_seg_tab(j).lov_prompt;
2726 l_segment_tab(j).segment_type := p_kf_cost_seg_tab(j).segment_type;
2727 l_segment_tab(j).window_prompt := p_kf_cost_seg_tab(j).window_prompt;
2728 l_segment_tab(j).vs_value_set_name := p_kf_cost_seg_tab(j).vs_value_set_name;
2729 l_segment_tab(j).vs_description := p_kf_cost_seg_tab(j).vs_description;
2730 l_segment_tab(j).vs_security_available := p_kf_cost_seg_tab(j).vs_security_available;
2731 l_segment_tab(j).vs_enable_longlist := p_kf_cost_seg_tab(j).vs_enable_longlist;
2732 l_segment_tab(j).vs_format_type := p_kf_cost_seg_tab(j).vs_format_type;
2733 l_segment_tab(j).vs_maximum_size := p_kf_cost_seg_tab(j).vs_maximum_size;
2734 l_segment_tab(j).vs_precision := p_kf_cost_seg_tab(j).vs_precision;
2735 l_segment_tab(j).vs_numbers_only := p_kf_cost_seg_tab(j).vs_numbers_only;
2736 l_segment_tab(j).vs_uppercase_only := p_kf_cost_seg_tab(j).vs_uppercase_only;
2740
2737 l_segment_tab(j).vs_right_justify_zero_fill := p_kf_cost_seg_tab(j).vs_right_justify_zero_fill;
2738 l_segment_tab(j).vs_min_value := p_kf_cost_seg_tab(j).vs_min_value;
2739 l_segment_tab(j).vs_max_value := p_kf_cost_seg_tab(j).vs_max_value;
2741 end loop;
2742 end if;
2743
2744 IF p_kf_cost_tab.count > 0 THEN
2745 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||get_keyflex_str_seg_dat_for_pv ( l_structure_tab, l_segment_tab ,'Cost');
2746 END If;
2747 l_structure_tab.delete;
2748 l_segment_tab.delete;
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
2758 FUNCTION get_keyflex_str_seg_dat_for_pv
2759 ( p_kf_structure_tab in per_ri_config_tech_summary.kf_structure_tab,
2760 p_kf_segment_tab in per_ri_config_tech_summary.kf_segment_tab,
2761 p_keyflex_name in varchar2
2762 ) return clob IS
2763
2764 l_str_seg_append_clob_for_pv clob;
2765 l_temp_sql varchar2(32000);
2766 queryCtx number;
2767 l_proc varchar2(200) := 'get_keyflex_str_seg_data_for_pv';
2768 l_style varchar2(10);
2769 i number(8) := 0;
2770 j number(8) := 0;
2771 l_orderby varchar2(200);
2772
2773 l_allow_dynamic varchar2(8) := 'Y';
2774 l_segment_separator varchar2(20) := 'Period (.)';
2775 l_enabled varchar2(8) := 'Y';
2776 l_required varchar2(8) := 'Y';
2777 l_display varchar2(8) := 'Y';
2778 l_freeze_flex_def varchar2(8) := 'Y';
2779 l_vs_validation_type varchar2(80);
2780
2781
2782 l_vs_security_available varchar2(80);
2783 l_vs_enable_longlist varchar2(80);
2784
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;
2794 l_required := l_allow_dynamic;
2795 l_display := l_allow_dynamic;
2796 l_freeze_flex_def := l_allow_dynamic;
2797
2798 select application_name into l_appl_name from fnd_application_vl where application_short_name = p_kf_structure_tab(j).appl_short_name;
2799
2800 --For every cost Structure find all Segments
2801 IF p_kf_structure_tab.count > 0 THEN
2802 for j in p_kf_structure_tab.first ..
2803 p_kf_structure_tab.last loop
2804
2805 -- Start the topmost structure node
2806 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2807 form_xml('CS',p_keyflex_name||'KeyflexStructure','');
2808
2809 -- Start populating the structure nodes and their values
2810 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2811 form_xml('CS','APPL_SHORT_NAME','') ||
2812 form_xml('D','',l_appl_name) ||
2813 form_xml('CE','APPL_SHORT_NAME','');
2814
2815 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2816 form_xml('CS','FLEX_CODE','') ||
2817 form_xml('D','',p_kf_structure_tab(j).flex_code)||
2818 form_xml('CE','FLEX_CODE','');
2819
2820 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2821 form_xml('CS','STRUCTURE_CODE','') ||
2822 form_xml('D','',p_kf_structure_tab(j).structure_code)||
2823 form_xml('CE','STRUCTURE_CODE','');
2824
2825 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2826 form_xml('CS','STRUCTURE_TITLE','') ||
2827 form_xml('D','',p_kf_structure_tab(j).structure_title)||
2828 form_xml('CE','STRUCTURE_TITLE','');
2829
2830 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2831 form_xml('CS','DESCRIPTION','') ||
2832 form_xml('D','',p_kf_structure_tab(j).description)||
2833 form_xml('CE','DESCRIPTION','');
2834
2835 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2836 form_xml('CS','ALLOW_DYNAMIC_INSERTS','') ||
2837 form_xml('D','',l_allow_dynamic)||
2838 form_xml('CE','ALLOW_DYNAMIC_INSERTS','');
2839
2840 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2841 form_xml('CS','SEGMENT_SEPARATOR','') ||
2842 form_xml('D','',l_segment_separator)||
2843 form_xml('CE','SEGMENT_SEPARATOR','');
2844
2845 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2846 form_xml('CS','ENABLED','') ||
2847 form_xml('D','',l_enabled)||
2848 form_xml('CE','ENABLED','');
2849
2850 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2851 form_xml('CS','FREEZE_FLEX_DEF','') ||
2852 form_xml('D','',l_freeze_flex_def)||
2853 form_xml('CE','FREEZE_FLEX_DEF','');
2854
2855
2856 if p_kf_segment_tab.count > 0 THEN
2857 for i in p_kf_segment_tab.first ..
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
2867
2871 select meaning into l_vs_security_available from hr_lookups where lookup_type = 'YES_NO' and lookup_code = p_kf_segment_tab(i).vs_security_available;
2868 -- When there is no data found error in the following places then do nothing
2869 begin
2870 if p_kf_segment_tab(i).vs_security_available is not null then
2872 end if;
2873 if p_kf_segment_tab(i).vs_enable_longlist is not null then
2874 select meaning into l_vs_enable_longlist from fnd_lookups where lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG' and lookup_code = p_kf_segment_tab(i).vs_enable_longlist;
2875 end if;
2876 if p_kf_segment_tab(i).vs_format_type is not null then
2877 select meaning into l_vs_format_type from fnd_lookups where lookup_type = 'FIELD_TYPE' and lookup_code = p_kf_segment_tab(i).vs_format_type;
2878 end if;
2879 exception
2880 when no_data_found then
2881 null;
2882 end;
2883
2884 -- Here open a segment node start
2885 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2886 form_xml('CS',p_keyflex_name||'KeyflexSegment','');
2887
2888
2889 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2890 form_xml('CS','SEGMENT_NAME','') ||
2891 form_xml('D','',p_kf_segment_tab(i).segment_name)||
2892 form_xml('CE','SEGMENT_NAME','');
2893
2894 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2895 form_xml('CS','COLUMN_NAME','') ||
2896 form_xml('D','',p_kf_segment_tab(i).column_name)||
2897 form_xml('CE','COLUMN_NAME','');
2898
2899 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2900 form_xml('CS','SEGMENT_NUMBER','') ||
2901 form_xml('D','',p_kf_segment_tab(i).segment_number)||
2902 form_xml('CE','SEGMENT_NUMBER','');
2903
2904 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2905 form_xml('CS','VALUE_SET','') ||
2906 form_xml('D','',p_kf_segment_tab(i).value_set)||
2907 form_xml('CE','VALUE_SET','');
2908
2909 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2910 form_xml('CS','LOV_PROMPT','') ||
2911 form_xml('D','',p_kf_segment_tab(i).lov_prompt)||
2912 form_xml('CE','LOV_PROMPT','');
2913
2914 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2915 form_xml('CS','SEGMENT_TYPE','') ||
2916 form_xml('D','',p_kf_segment_tab(i).segment_type)||
2917 form_xml('CE','SEGMENT_TYPE','');
2918
2919 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2920 form_xml('CS','WINDOW_PROMPT','') ||
2921 form_xml('D','',p_kf_segment_tab(i).window_prompt)||
2922 form_xml('CE','WINDOW_PROMPT','');
2923
2924 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2925 form_xml('CS','REQUIRED','') ||
2926 form_xml('D','',l_required)||
2927 form_xml('CE','REQUIRED','');
2928
2929 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2930 form_xml('CS','DISPLAY','') ||
2931 form_xml('D','',l_display)||
2932 form_xml('CE','DISPLAY','') ;
2933
2934 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2935 form_xml('CS','SEG_ENABLED','') ||
2936 form_xml('D','',l_enabled)||
2937 form_xml('CE','SEG_ENABLED','');
2938
2939 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2940 form_xml('CS','VS_DESCRIPTION','') ||
2941 form_xml('D','',p_kf_segment_tab(i).vs_description)||
2942 form_xml('CE','VS_DESCRIPTION','');
2943
2944 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2945 form_xml('CS','VS_SECURITY_AVAILABLE','') ||
2946 form_xml('D','',l_vs_security_available)||
2947 form_xml('CE','VS_SECURITY_AVAILABLE','');
2948
2949 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2950 form_xml('CS','VS_LIST_TYPE','') ||
2951 form_xml('D','',l_vs_enable_longlist)||
2952 form_xml('CE','VS_LIST_TYPE','');
2953
2954 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2955 form_xml('CS','VS_FORMAT_TYPE','') ||
2956 form_xml('D','',l_vs_format_type)||
2957 form_xml('CE','VS_FORMAT_TYPE','');
2958
2959 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2960 form_xml('CS','VS_VALIDATION_TYPE','') ||
2961 form_xml('D','',l_vs_validation_type)||
2962 form_xml('CE','VS_VALIDATION_TYPE','');
2963
2964 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2965 form_xml('CS','VS_MAXIMUM_SIZE','') ||
2966 form_xml('D','',p_kf_segment_tab(i).vs_maximum_size )||
2967 form_xml('CE','VS_MAXIMUM_SIZE','');
2968
2969 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2970 form_xml('CS','VS_PRECISION','') ||
2971 form_xml('D','',p_kf_segment_tab(i).vs_precision )||
2972 form_xml('CE','VS_PRECISION','');
2973
2974 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2975 form_xml('CS','VS_NUMBERS_ONLY','') ||
2976 form_xml('D','',p_kf_segment_tab(i).vs_numbers_only )||
2977 form_xml('CE','VS_NUMBERS_ONLY','');
2978
2979 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2980 form_xml('CS','VS_UPPERCASE_ONLY','') ||
2981 form_xml('D','',p_kf_segment_tab(i).vs_uppercase_only )||
2982 form_xml('CE','VS_UPPERCASE_ONLY','');
2983
2984 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2988
2985 form_xml('CS','VS_RIGHT_JUSTIFY_ZERO_FILL','') ||
2986 form_xml('D','',p_kf_segment_tab(i).vs_right_justify_zero_fill)||
2987 form_xml('CE','VS_RIGHT_JUSTIFY_ZERO_FILL','');
2989 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2990 form_xml('CS','VS_MIN_VALUE','') ||
2991 form_xml('D','',p_kf_segment_tab(i).vs_min_value)||
2992 form_xml('CE','VS_MIN_VALUE','');
2993
2994 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
2995 form_xml('CS','VS_MAX_VALUE','') ||
2996 form_xml('D','',p_kf_segment_tab(i).vs_max_value)||
2997 form_xml('CE','VS_MAX_VALUE','');
2998
2999 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
3000 form_xml('CS','VALUE_SET_NAME','') ||
3001 form_xml('D','',p_kf_segment_tab(i).vs_value_set_name)||
3002 form_xml('CE','VALUE_SET_NAME','');
3003
3004 -- End one segment detail
3005 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
3006 form_xml('CE',p_keyflex_name||'KeyflexSegment','');
3007
3008
3009 end if;
3010 end loop;
3011
3012 --End the structure Loop
3013 l_str_seg_append_clob_for_pv := l_str_seg_append_clob_for_pv ||
3014 form_xml('CE',p_keyflex_name||'KeyflexStructure','');
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
3024 FUNCTION form_xml(P_NODE_TYPE IN varchar2, -- Indicates the node type (i.e start_tag/end_tag/value)
3025 P_NODE IN VARCHAR2, -- Indicates the node value
3026 P_DATA IN VARCHAR2) -- Indicates the data value
3027 return clob
3028 IS
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 := '</'||p_node||'>';
3038 ELSIF p_node_type = 'D' THEN
3039 l_data := REPLACE (p_data, '&', '&');
3040 l_data := REPLACE (l_data, '>', '>');
3041 l_data := REPLACE (l_data, '<', '<');
3042 l_data := REPLACE (l_data, '''', ''');
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
3052
3053
3054 FUNCTION get_int_hrms_setup_sql (
3055 p_int_hrms_setup_tab in out nocopy per_ri_config_tech_summary.int_hrms_setup_tab
3056 )
3057 return clob IS
3058
3059
3060 l_proc varchar2(72) := g_package || 'get_int_hrms_setup_sql';
3061 i number(8) := 0;
3062 l_ret_kf_int_hr_clob clob ;
3063 l_temp_sql varchar2(2000);
3064 l_kf_int_hr_clob clob;
3065 queryCtx number(8) := 0;
3066 l_prejoin_sql varchar2(2000);
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, '
3076 ||' lookup.meaning Install_Tax_Unit_Val '
3077 ||' from ( ';
3078
3079 l_postjoin_sql := ' )A , FND_TERRITORIES_VL terr ,fnd_currencies_vl curr,hr_lookups lookup '
3080 ||' where terr.TERRITORY_CODE = A.legislation_code '
3081 ||' and curr.currency_code = A.currency_code '
3082 ||' and lookup.lookup_code = A.install_tax_unit '
3083 ||' and lookup.lookup_type=''YES_NO'' and lookup.application_id=800 '
3084 ||' order by Legislation desc';
3085
3086 dbms_lob.writeappend(l_kf_int_hr_clob,length(l_prejoin_sql),l_prejoin_sql);
3087
3088 if p_int_hrms_setup_tab.count > 0 THEN
3089 for i in p_int_hrms_setup_tab.first ..
3090 p_int_hrms_setup_tab.last loop
3091
3092 l_temp_sql:= ' SELECT ' ||
3093 '''' || p_int_hrms_setup_tab(i).legislation_code ||''''||' legislation_code,' ||
3094 '''' || p_int_hrms_setup_tab(i).currency_code ||''''||' currency_code,' ||
3095 '''' || p_int_hrms_setup_tab(i).tax_start_date ||''''||' tax_start_date,' ||
3096 '''' || p_int_hrms_setup_tab(i).install_tax_unit ||''''||' install_tax_unit' ||
3097 ' FROM DUAL UNION';
3098
3099 dbms_lob.writeappend(l_kf_int_hr_clob,length(l_temp_sql),l_temp_sql);
3100 end loop;
3101 end if;
3102
3103 if (length(l_kf_int_hr_clob)>length(l_prejoin_sql)+ 5)
3104 then
3105
3106 dbms_lob.trim(l_kf_int_hr_clob,length(l_kf_int_hr_clob)-5);
3107 dbms_lob.writeappend(l_kf_int_hr_clob,length(l_postjoin_sql),l_postjoin_sql);
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 (
3117 p_security_profile_tab in out nocopy per_ri_config_tech_summary.sg_tab
3118 )
3119 return clob IS
3120
3121 l_proc varchar2(72) := g_package || ' get_security_profile_sql';
3125 l_ret_secprf_clob clob;
3122 i number(8) := 0;
3123 l_secprf_clob clob ;
3124 l_temp_sql varchar2(2000);
3126 queryCtx number(8) := 0;
3127 l_option varchar2(20) := 'All';
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 ';
3137
3138 if p_security_profile_tab.count > 0 THEN
3139 for i in p_security_profile_tab.first ..
3140 p_security_profile_tab.last loop
3141
3142 l_temp_sql:= ' SELECT ' ||
3143 '''' || p_security_profile_tab(i).security_group_name ||''''||' security_group_name, ' ||
3147 '''' ||l_option ||''''||' Applicants, ' ||
3144 '''' || p_security_profile_tab(i).business_group_name ||''''||' business_group_name, ' ||
3145 '''' ||l_option ||''''||' Employees, ' ||
3146 '''' ||l_option ||''''||' Contingent_Workers, ' ||
3148 '''' ||l_option ||''''||' Contact ' ||
3149 ' FROM DUAL UNION';
3150
3151
3152
3153 dbms_lob.writeappend(l_secprf_clob,length(l_temp_sql),l_temp_sql);
3154 end loop;
3155 end if;
3156
3157 if length(l_secprf_clob)> 5
3158 then
3159 dbms_lob.trim(l_secprf_clob,length(l_secprf_clob)-5);
3160 dbms_lob.writeappend(l_secprf_clob,length(l_orderby),l_orderby);
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
3170
3171 FUNCTION get_org_hierarchy_sql (
3172 p_org_hierarchy_tab in out nocopy per_ri_config_tech_summary.org_hierarchy_tab
3173 )
3174 return clob IS
3175
3176 l_proc varchar2(72) := g_package || ' get_org_hierarchy_sql';
3177 i number(8) := 0;
3178 l_org_hier_clob clob ;
3179 l_temp_sql varchar2(2000);
3180 l_ret_org_hier_clob clob;
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
3190 for i in p_org_hierarchy_tab.first ..
3191 p_org_hierarchy_tab.last loop
3192
3193 l_temp_sql:= ' SELECT ' ||
3194 '''' || p_org_hierarchy_tab(i).name ||''''||' name , ' ||
3195 '''' || p_org_hierarchy_tab(i).org_structure_version_id ||''''||' org_structure_version_id ' ||
3196 ' FROM DUAL UNION';
3197
3198 dbms_lob.writeappend(l_org_hier_clob,length(l_temp_sql),l_temp_sql);
3199 end loop;
3200 end if;
3201
3202 if length(l_org_hier_clob)> 5
3203 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
3213 FUNCTION get_org_hierarchy_ele_sql (
3214 p_org_hierarchy_ele_oc_tab in out nocopy per_ri_config_tech_summary.org_hierarchy_ele_oc_tab
3215 ,p_org_hierarchy_ele_le_tab in out nocopy per_ri_config_tech_summary.org_hierarchy_ele_le_tab
3219 i number(8) := 0;
3216 )
3217 return clob IS
3218 l_proc varchar2(72) := g_package || ' get_org_hierarchy_ele_sql';
3220 l_org_hier_ele_clob clob ;
3221 l_temp_sql varchar2(2000);
3222 l_ret_org_hier_ele_clob clob;
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
3232 for i in p_org_hierarchy_ele_oc_tab.first ..
3233 p_org_hierarchy_ele_oc_tab.last loop
3234
3235 l_temp_sql:= ' SELECT ' ||
3236 '''' || p_org_hierarchy_ele_oc_tab(i).org_structure_version_id ||''''||' org_structure_version_id,'||
3237 '''' || REPLACE ( p_org_hierarchy_ele_oc_tab(i).parent_organization_name , '''', '''''') ||''''||' parent_organization_name,'||
3238 '''' || REPLACE (p_org_hierarchy_ele_oc_tab(i).child_organization_name, '''', '''''') ||''''||' child_organization_name '||
3239 ' FROM DUAL UNION';
3240
3241 dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
3242 end loop;
3243 end if;
3244
3245 if p_org_hierarchy_ele_le_tab.count > 0 THEN
3246 for i in p_org_hierarchy_ele_le_tab.first ..
3247 p_org_hierarchy_ele_le_tab.last loop
3248
3249 l_temp_sql:= ' SELECT ' ||
3250 '''' || p_org_hierarchy_ele_le_tab(i).org_structure_version_id ||''''||' org_structure_version_id,'||
3251 '''' || REPLACE ( p_org_hierarchy_ele_le_tab(i).parent_organization_name , '''', '''''') ||''''||' parent_organization_name,'||
3252 '''' || REPLACE ( p_org_hierarchy_ele_le_tab(i).child_organization_name, '''','''''') ||''''||' child_organization_name '||
3253 ' FROM DUAL UNION';
3254
3255 dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
3256 end loop;
3257 end if;
3258
3259 if length(l_org_hier_ele_clob)> 5
3260 then
3261 dbms_lob.trim(l_org_hier_ele_clob,length(l_org_hier_ele_clob)-5);
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
3271 FUNCTION get_org_hier_ele_sql_for_pv (
3272 p_org_hierarchy_ele_oc_tab in out nocopy per_ri_config_tech_summary.org_hierarchy_ele_oc_tab
3273 ,p_org_hierarchy_ele_le_tab in out nocopy per_ri_config_tech_summary.org_hierarchy_ele_le_tab
3274 )
3275 return clob IS
3276 l_proc varchar2(72) := g_package || ' get_org_hierarchy_ele_sql_for_pv';
3277 i number(8) := 0;
3278 j number(8) := 0;
3279 l_org_hier_ele_clob clob ;
3280 l_org_hier_append_clob clob ;
3281 l_temp_clob clob ;
3282 l_temp_sql varchar2(32000);
3283 l_parent_org_name varchar2(2000);
3284 l_ret_org_hier_ele_clob clob;
3285 l_oc_name varchar2(60) := '';
3286 queryCtx number(8) := 0;
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);
3296
3297 if p_org_hierarchy_ele_oc_tab.count > 0 THEN
3298
3299 l_parent_org_name := ' SELECT ' ||
3300 '''' || REPLACE ( p_org_hierarchy_ele_oc_tab(0).parent_organization_name , '''', '''''') ||''''||' parent_organization_name';
3301
3305 p_org_hierarchy_ele_oc_tab.last loop
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 ..
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
3315
3316 -- Case when LE is under an OC
3320 '''' ||REPLACE ( p_org_hierarchy_ele_le_tab(j).child_organization_name , '''', '''''') ||''''||' le_name';
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 || ',' ||
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';
3326 dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
3327 l_org_hier_append_clob := l_org_hier_append_clob || fetch_clob(l_org_hier_ele_clob,'OrgHierarchyEleForPV','OrgHierarchyElementsForPV');
3328 dbms_lob.trim(l_org_hier_ele_clob,0);
3329 end loop;
3330
3331 l_temp_sql := l_parent_org_name;
3332
3333 if p_org_hierarchy_ele_le_tab.count > 0 THEN
3334 for j in p_org_hierarchy_ele_le_tab.first ..
3335 p_org_hierarchy_ele_le_tab.last loop
3336
3337 -- Case when LE is directly under an org
3338 if p_org_hierarchy_ele_oc_tab(i).parent_organization_name = p_org_hierarchy_ele_le_tab(j).parent_organization_name THEN
3339
3340 l_temp_sql := l_temp_sql || ',' ||
3341 '''' ||REPLACE ( p_org_hierarchy_ele_le_tab(j).child_organization_name , '''', '''''') ||''''||' le_name';
3342
3343 l_le_exists := true;
3344 end if;
3345
3346 end loop;
3347 end if;
3348 l_temp_sql := l_temp_sql ||
3349 ' FROM DUAL';
3350
3351 if l_le_exists then
3352 dbms_lob.writeappend(l_org_hier_ele_clob,length(l_temp_sql),l_temp_sql);
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
3362
3363 FUNCTION get_post_install_sql (
3364 p_post_install_tab in out nocopy per_ri_config_tech_summary.post_install_tab
3365 )
3366 return clob IS
3367 l_proc varchar2(72) := g_package || 'get_post_install_sql';
3368 i number(8) := 0;
3369 l_ret_post_install_clob clob ;
3370 l_temp_sql varchar2(2000);
3371 l_post_install_clob clob;
3372 queryCtx number(8) := 0;
3373 l_prejoin_sql varchar2(2000);
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 , '
3383 ||' fapp.application_name '
3384 ||' from ( ';
3385
3386 l_postjoin_sql := ' )A , FND_TERRITORIES_VL terr ,fnd_application_vl fapp '
3387 ||' where terr.TERRITORY_CODE = A.legislation_code '
3388 ||' and fapp.application_short_name = A.application_short_name '
3389 ||' order by Legislation desc , fapp.application_name desc';
3390
3391 dbms_lob.writeappend(l_post_install_clob,length(l_prejoin_sql),l_prejoin_sql);
3392
3393 if p_post_install_tab.count > 0 THEN
3394 for i in p_post_install_tab.first ..
3395 p_post_install_tab.last loop
3396
3397 l_temp_sql:= ' SELECT ' ||
3398 '''' || p_post_install_tab(i).legislation_code ||''''||' legislation_code,' ||
3399 '''' || p_post_install_tab(i).applicaton_short_name ||''''||' application_short_name ' ||
3400 ' FROM DUAL UNION';
3401
3402 dbms_lob.writeappend(l_post_install_clob,length(l_temp_sql),l_temp_sql);
3403 end loop;
3404 end if;
3405
3406 if (length(l_post_install_clob)>length(l_prejoin_sql)+ 5)
3407 then
3408 dbms_lob.trim(l_post_install_clob,length(l_post_install_clob)-5);
3409 dbms_lob.writeappend(l_post_install_clob,length(l_postjoin_sql),l_postjoin_sql);
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
3419 FUNCTION fetch_clob(p_in_clob IN CLOB, p_row_tag IN VARCHAR2, p_row_set_tag IN VARCHAR2)
3420 return clob IS
3421 l_in_clob clob := p_in_clob;
3422 l_ret_in_clob clob;
3423 queryCtx number(8) := 0;
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
3433 FROM fnd_lookup_values
3434 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
3435 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
3436 INSTR(USERENV('LANGUAGE'), '.') + 1)
3437 AND language = 'US';
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
3450
3447 IF l_encoding_tag IS NOT null THEN
3448 DBMS_XMLQUERY.setEncodingTag(queryCtx, l_encoding_tag);
3449 END IF;
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;