[Home] [Help]
PACKAGE BODY: APPS.HR_SUMMARY_UTIL
Source
1 package body hr_summary_util as
2 /* $Header: hrbsutil.pkb 120.0 2005/05/30 23:06:11 appldev noship $ */
3 --
4 l_item_value_id number;
5 l_key_value_id number;
6 l_object_version_number number;
7 --
8 function create_other_kv(p_business_group_id number
9 ,p_key_type_id number) return boolean is
10 begin
11 --
12 hr_utility.set_location('Entering: hr_summary_util.create_other_kv', 10);
13 --
14 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
15 ,p_business_group_id => p_business_group_id
16 ,p_object_version_number => l_object_version_number
17 ,p_key_type_id => p_key_type_id
18 ,p_item_value_id => zero_item_value_id
19 ,p_name => OTHER);
20 --
21 hr_utility.set_location('Leaving: hr_summary_util.create_other_kv', 20);
22 --
23 return TRUE;
24 end;
25 --
26 function get_lookup_values(p_lookup_type varchar2
27 ,p_db_column varchar2
28 ,p_key_type_id number) return varchar2 is
29 --
30 cursor c_lookup(p_lookup_type varchar2) is
31 select lookup_code
32 from hr_lookups
33 where lookup_type = p_lookup_type;
34 --
35 i number;
36 l_clause varchar2(32000);
37 --
38 begin
39 --
40 hr_utility.set_location('Entering: hr_summary_util.get_lookup_values', 10);
41 --
42 i := 0;
43 for v in c_lookup(p_lookup_type) loop
44 if i > 0 then
45 l_clause := l_clause ||',';
46 else
47 l_clause := 'decode('||p_db_column||',';
48 end if;
49 --
50 if v.lookup_code is not null then
51 l_clause := l_clause || '''' ||
52 v.lookup_code||''','''||v.lookup_code|| '''';
53 ---------------------------------------------------------------
54 if store_data then
55 --
56 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
57 ,p_business_group_id => g_business_group_id
58 ,p_object_version_number => l_object_version_number
59 ,p_key_type_id => p_key_type_id
60 ,p_item_value_id => zero_item_value_id
61 ,p_name => v.lookup_code);
62 --
63 end if;
64 ---------------------------------------------------------------
65 end if;
66 --
67 i := i + 1;
68 end loop;
69 --
70 if l_clause is null then
71 l_clause := '''GSP_OTHER''';
72 else
73 l_clause := l_clause ||',''GSP_OTHER'')';
74 end if;
75 ---------------------------------------------------------------
76 --
77 hr_utility.set_location('Leaving: hr_summary_util.get_lookup_values', 20);
78 --
79 return l_clause;
80 end get_lookup_values;
81 --
82 function get_alternate_values(p_table_name varchar2
83 ,p_column varchar2
84 ,p_db_column varchar2
85 ,p_key_type_id number) return varchar2 is
86 --
87 cursor c_user_table(p_table_name varchar2
88 ,p_column varchar2) is
89 select ur.ROW_LOW_RANGE_OR_NAME name
90 , uci1.value value
91 from pay_user_column_instances_f uci1
92 , pay_user_rows_f ur
93 , pay_user_columns uc1
94 , pay_user_tables t
95 where uci1.USER_ROW_ID = ur.USER_ROW_ID
96 and sysdate between ur.effective_start_date and ur.effective_end_date
97 and uci1.USER_COLUMN_ID = uc1.USER_COLUMN_ID
98 and sysdate between uci1.effective_start_date and uci1.effective_end_date
99 and uc1.USER_COLUMN_NAME = p_column
100 and ur.user_table_id = t.user_table_id
101 and uc1.user_table_id = t.user_table_id
102 and t.USER_TABLE_NAME = p_table_name
103 and uci1.value is not null
104 and t.business_group_id = g_business_group_id;
105 --
106 i number;
107 l_clause varchar2(32000);
108 --
109 begin
110 --
111 hr_utility.set_location('Entering: hr_summary_util.get_alternate_values', 10);
112 --
113 i := 0;
114 for v in c_user_table(p_table_name
115 ,p_column) loop
116 if i > 0 then
117 l_clause := l_clause ||',';
118 else
119 l_clause := 'decode('||p_db_column||',';
120 end if;
121 --
122 if v.value is not null then
123 l_clause := l_clause || '''' ||
124 v.name||''','''||v.value|| '''';
125 ---------------------------------------------------------------
126 if store_data then
127
128 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
129 ,p_business_group_id => g_business_group_id
130 ,p_object_version_number => l_object_version_number
131 ,p_key_type_id => p_key_type_id
132 ,p_item_value_id => zero_item_value_id
133 ,p_name => v.value);
134
135 end if;
136 ---------------------------------------------------------------
137 end if;
138 --
139 i := i + 1;
140 end loop;
141 --
142 if l_clause is null then
143 l_clause := ''''||OTHER||'''';
144 else
145 l_clause := l_clause ||','||''''||OTHER||''''||')';
146 end if;
147 ---------------------------------------------------------------
148 --
149 hr_utility.set_location('Leaving: hr_summary_util.get_alternate_values', 20);
150 --
151 return l_clause;
152 end;
153 --
154 --
155 function get_band_values(p_table_name varchar2
156 ,p_low_column varchar2
157 ,p_high_column varchar2
158 ,p_db_column varchar2
159 ,p_key_type_id number) return varchar2 is
160 --
161 l_legislation_code per_business_groups.legislation_code%type;
162 --
163 cursor c_user_table(p_table_name varchar2
164 ,p_low_column varchar2
165 ,p_high_column varchar2) is
166 select ur.ROW_LOW_RANGE_OR_NAME name
167 , uci1.value low_value
168 , uci2.value high_value
169 , ur.display_sequence
170 from pay_user_column_instances_f uci1
171 , pay_user_column_instances_f uci2
172 , pay_user_rows_f ur
173 , pay_user_columns uc1
174 , pay_user_columns uc2
175 , pay_user_tables t
176 where uci1.USER_ROW_ID = ur.USER_ROW_ID
177 and sysdate between ur.effective_start_date and ur.effective_end_date
178 and uci1.USER_COLUMN_ID = uc1.USER_COLUMN_ID
179 and sysdate between uci1.effective_start_date and uci1.effective_end_date
180 and uci2.USER_ROW_ID = ur.USER_ROW_ID
181 and uci2.USER_COLUMN_ID = uc2.USER_COLUMN_ID
182 and sysdate between uci2.effective_start_date and uci2.effective_end_date
183 and uc1.USER_COLUMN_NAME = p_low_column
184 and uc2.USER_COLUMN_NAME = p_high_column
185 and ur.user_table_id = t.user_table_id
186 and uc1.user_table_id = t.user_table_id
187 and uc2.user_table_id = t.user_table_id
188 and t.USER_TABLE_NAME = p_table_name
189 and nvl(to_char(t.business_group_id),t.legislation_code) = decode(t.business_group_id,null,l_legislation_code,to_char(g_business_group_id));
190 --
191 i number;
192 l_clause varchar2(32000);
193 --
194 begin
195 --
196 hr_utility.set_location('Entering: hr_summary_util.get_band_values', 10);
197 --
198 select legislation_code
199 into l_legislation_code
200 from per_business_groups
201 where business_group_id = g_business_group_id;
202 --
203 i := 0;
204 for v in c_user_table(p_table_name,p_low_column,p_high_column) loop
205 if i > 0 then
206 l_clause := l_clause ||',';
207 else
208 l_clause := 'decode(2,';
209 end if;
210 --
211 l_clause := l_clause ||
212 'decode(sign('||p_db_column||'-'||v.low_value||'),1,1,0,1,0) + '||
213 'decode(sign('||v.high_value||'-'||p_db_column||'),1,1,0,1,0)'||
214 ','''||v.name||'''';
215 --
216 i := i + 1;
217 ---------------------------------------------------------------
218 if store_data then
219
220 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
221 ,p_business_group_id => g_business_group_id
222 ,p_object_version_number => l_object_version_number
223 ,p_key_type_id => p_key_type_id
224 ,p_item_value_id => zero_item_value_id
225 ,p_name => v.name);
226
227 end if;
228 ---------------------------------------------------------------
229 end loop;
230 --
231 if l_clause is null then
232 l_clause := ''''||OTHER||'''';
233 else
234 l_clause := l_clause || ','''||OTHER||''')';
235 end if;
236 --
237 hr_utility.set_location('Leaving: hr_summary_util.get_band_values', 20);
238 --
239 return l_clause;
240 End;
241 --
242 procedure initialize_run(p_store_data boolean
243 ,p_business_group_id number
244 ,p_template_id number
245 ,p_process_run_name varchar2
246 ,p_process_type varchar2
247 ,p_parameters prmTabType) is
248 --
249 l_process_run_id number;
250 l_parameter_id number;
251 begin
252 --
253 hr_utility.set_location('Entering: hr_summary_util.initialize_run', 10);
254 --
255 if p_store_data then
256 hr_summary_api.create_process_run (p_process_run_id => l_process_run_id
257 ,p_business_group_id => p_business_group_id
258 ,p_object_version_number => l_object_version_number
259 ,p_name => p_process_run_name
260 ,p_template_id => p_template_id
261 ,p_process_type => p_process_type);
262 --
263 if p_parameters.count > 0 then
264 for i in p_parameters.first..p_parameters.last loop
265 hr_summary_api.create_parameter (p_parameter_id => l_parameter_id
266 ,p_business_group_id => p_business_group_id
267 ,p_object_version_number => l_object_version_number
268 ,p_process_run_id => l_process_run_id
269 ,p_name => p_parameters(i).name
270 ,p_value => p_parameters(i).value);
271 end loop;
272 end if;
273 --
274 process_run_id := l_process_run_id;
275 end if;
276 --
277 g_business_group_id := p_business_group_id;
278 --
279 hr_utility.set_location('Leaving: hr_summary_util.initialize_run', 20);
280 --
281 end;
282 --
283 procedure initialize_procedure (p_business_group_id number) is
284 begin
285 --
286 hr_utility.set_location('Entering: hr_summary_util.initialize_procedure', 10);
287 --
288 if store_data then
289 hr_summary_api.create_item_value(p_item_value_id => zero_item_value_id
290 ,p_business_group_id => p_business_group_id
291 ,p_object_version_number => l_object_version_number
292 ,p_process_run_id => process_run_id
293 ,p_item_type_usage_id => item_type_usage_id
294 ,p_textvalue => null
295 ,p_numvalue1 => 0
296 ,p_numvalue2 => null
297 ,p_datevalue => null);
298
299 end if;
300 --
301 hr_utility.set_location('Leaving: hr_summary_util.initialize_procedure', 20);
302 --
303 end;
304 --
305 procedure load_item_value(p_business_group_id number
306 ,p_value number) is
307 begin
308 --
309 hr_utility.set_location('Entering: hr_summary_util.load_item_value', 10);
310 --
311 hr_summary_api.create_item_value(p_item_value_id => l_item_value_id
312 ,p_business_group_id => p_business_group_id
313 ,p_object_version_number => l_object_version_number
314 ,p_process_run_id => process_run_id
315 ,p_item_type_usage_id => item_type_usage_id
316 ,p_textvalue => null
317 ,p_numvalue1 => p_value
318 ,p_numvalue2 => null
319 ,p_datevalue => null);
320 --
321 hr_utility.set_location('Leaving: hr_summary_util.load_item_value', 20);
322 --
323 --dbms_output.put_line(l_item_value_id ||' '||item_type_usage_id||' '||p_value);
324 end load_item_value;
325 --
326 /*
327 procedure load_item_key_value(p_business_group_id number
328 ,p_key_type_id number
329 ,p_other_entry IN OUT boolean
330 ,p_value varchar2) is
331 begin
332 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
333 ,p_business_group_id => p_business_group_id
334 ,p_object_version_number => l_object_version_number
335 ,p_key_type_id => p_key_type_id
336 ,p_item_value_id => l_item_value_id
337 ,p_name => p_value);
338 --
339 if p_value = 'Other' and not p_other_entry then
340 p_other_entry := create_other_kv(p_business_group_id
341 ,p_key_type_id);
342 end if;
343 end load_item_key_value;
344 */
345 --
346 function get_cagr_values (p_key_type_id in number
347 ,p_db_column in varchar2
348 ,p_table_name in varchar2
349 ,p_column_name in varchar2) return varchar2 is
350 cursor csr_get_info is
351 select fsv.id_flex_num id_flex_num
352 , fsv.id_flex_structure_name structure_name
353 , uci1.value segment_value
354 , ur.display_sequence
355 from pay_user_column_instances_f uci1
356 , pay_user_rows_f ur
357 , pay_user_columns uc1
358 , pay_user_tables t
359 , fnd_id_flex_structures_vl fsv
360 where uci1.USER_ROW_ID = ur.USER_ROW_ID
361 and sysdate between ur.effective_start_date and ur.effective_end_date
362 and uci1.USER_COLUMN_ID = uc1.USER_COLUMN_ID
363 and sysdate between uci1.effective_start_date and uci1.effective_end_date
364 and uc1.USER_COLUMN_NAME = p_column_name
365 and ur.user_table_id = t.user_table_id
366 and uc1.user_table_id = t.user_table_id
367 and t.USER_TABLE_NAME = p_table_name
368 and ur.ROW_LOW_RANGE_OR_NAME = fsv.id_flex_structure_name
369 and fsv.id_flex_code = 'CAGR'
370 and fsv.application_id = 800
371 and t.business_group_id = g_business_group_id;
372 --
373 l_clause varchar2(32000) :=NULL;
374 l_seg_string varchar2(1000);
375 l_stmt varchar2(32000);
376 l_key_value hr_summary_key_value.name%type;
377 source_cursor integer;
378 ignore integer;
379 --
380 begin
381 --
382 hr_utility.set_location('Entering: hr_summary_util.get_cagr_values', 10);
383 --
384 for l_rec in csr_get_info loop
385 l_seg_string := REPLACE(l_rec.segment_value,'SEGMENT','cagr_def.SEGMENT');
386 l_seg_string := REPLACE(l_seg_string,',','||'' ''||');
387 l_seg_string := l_rec.id_flex_num||'||'' ''||'||''''||l_rec.structure_name||''''||'||'' : ''||'||l_seg_string;
388 if csr_get_info%rowcount = 1 then
389 l_clause := 'decode('||p_db_column;
390 l_clause := l_clause||','||l_rec.id_flex_num||','||l_seg_string;
391 else
392 l_clause := l_clause||','||l_rec.id_flex_num||','||l_seg_string;
393 end if;
394 --
395 l_stmt := 'select distinct '||l_seg_string||
396 ' from per_cagr_grades_def cagr_def '||
397 ' , per_cagr_grades cgr'||
398 ' , per_cagr_grade_structures cgs'||
399 ' , per_collective_agreements cag'||
400 ' where cgs.id_flex_num = '||l_rec.id_flex_num||
401 ' and cagr_def.cagr_grade_def_id = cgr.cagr_grade_def_id'||
402 ' and cgr.cagr_grade_structure_id = cgs.cagr_grade_structure_id'||
403 ' and cgs.collective_agreement_id = cag.collective_agreement_id'||
404 ' and cag.business_group_id = '||to_char(g_business_group_id);
405 --
406 source_cursor := dbms_sql.open_cursor;
407 dbms_sql.parse(source_cursor,l_stmt,dbms_sql.v7);
408 dbms_sql.define_column(source_cursor,1,l_key_value,80);
409 ignore := dbms_sql.execute(source_cursor);
410 --
411 loop
412 if dbms_sql.fetch_rows(source_cursor) > 0 then
413 dbms_sql.column_value(source_cursor,1,l_key_value);
414 if store_data then
415 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
416 ,p_business_group_id => g_business_group_id
417 ,p_object_version_number => l_object_version_number
418 ,p_key_type_id => p_key_type_id
419 ,p_item_value_id => zero_item_value_id
420 ,p_name => l_key_value);
421 end if;
422 else
423 exit;
424 end if;
425 end loop;
426 dbms_sql.close_cursor(source_cursor);
427 --
428 end loop;
429 --
430 if l_clause is null then
431 l_clause := ''''||OTHER||'''';
432 else
433 l_clause := l_clause ||','||''''||OTHER||''''||')';
434 end if;
435 --
436 hr_utility.set_location('Leaving: hr_summary_util.get_cagr_values', 20);
437 --
438 return (l_clause);
439 --
440 end get_cagr_values;
441 --
442 function get_month (p_key_type_id in number
443 ,p_db_column in varchar2) return varchar2 is
444 --
445 l_month varchar2(100);
446 l_stmt varchar2(1000);
447 source_cursor integer;
448 ignore integer;
449 l_key_value hr_summary_key_value.name%type;
450 --
451 begin
452 --
453 hr_utility.set_location('Entering: hr_summary_util.get_month', 10);
454 --
455 l_month := 'to_char(to_date('||''''||'P_YEAR'||p_db_column||'01'||''''||',''YYYYMMDD''),''MON'')';
456 --
457 for i in 1..12 loop
458 l_stmt := 'select to_char(to_date('||''''||'1999'||lpad(to_char(i),2,'0')||'01'||''''||',''YYYYMMDD''),''MON'') from dual';
459 --
460 source_cursor := dbms_sql.open_cursor;
461 dbms_sql.parse(source_cursor,l_stmt,dbms_sql.v7);
462 dbms_sql.define_column(source_cursor,1,l_key_value,80);
463 ignore := dbms_sql.execute(source_cursor);
464 --
465 if dbms_sql.fetch_rows(source_cursor) > 0 then
466 dbms_sql.column_value(source_cursor,1,l_key_value);
467 end if;
468 if store_data then
469 hr_summary_api.create_key_value(p_key_value_id => l_key_value_id
470 ,p_business_group_id => g_business_group_id
471 ,p_object_version_number => l_object_version_number
472 ,p_key_type_id => p_key_type_id
473 ,p_item_value_id => zero_item_value_id
474 ,p_name => l_key_value);
475 end if;
476 end loop;
477 dbms_sql.close_cursor(source_cursor);
478 --
479 hr_utility.set_location('Leaving: hr_summary_util.get_month', 20);
480 --
481 return l_month;
482 --
483 end get_month;
484 end;