[Home] [Help]
PACKAGE BODY: APPS.PAY_USER_COLUMN_INSTA_MATRIX
Source
1 package body PAY_USER_COLUMN_INSTA_MATRIX as
2 /* $Header: pydputil.pkb 120.2 2005/06/14 02:24 mkataria noship $ */
3
4 type ref_cursor_type is ref cursor;
5
6 --
7 -- To be used with WebADI only
8 --
9 procedure create_batch_header
10 (
11 p_batch_name in varchar2,
12 p_business_group_name in varchar2 default null,
13 p_reference in varchar2 default null,
14 p_batch_id out nocopy number
15 ) is
16 begin
17 p_batch_id := hr_pump_utils.create_batch_header(p_batch_name,
18 p_business_group_name,
19 p_reference);
20 exception
21 when others then
22 hr_utility.set_message(801,'PAY_33283_INVALID_DP_HEAD');
23 hr_utility.raise_error;
24 end create_batch_header;
25
26 --
27 --
28 --
29 function check_if_future_rows_exist
30 (
31 p_user_column_instance_id number
32 ,p_effective_date date
33 ) return boolean
34 is
35
36 l_future_row_exists number;
37 cursor csr_check_future_rows (c_user_column_instance_id number, c_effective_date date)
38 is
39 select 1
40 from pay_user_column_instances_f puci
41 where user_column_instance_id = c_user_column_instance_id
42 and c_effective_date between effective_start_date and effective_end_date
43 and exists( select null
44 from pay_user_column_instances_f pui
45 where pui.user_column_instance_id = puci.user_column_instance_id
46 and pui.effective_start_date > puci.effective_end_date
47 );
48
49 begin
50 open csr_check_future_rows(p_user_column_instance_id,p_effective_date);
51 fetch csr_check_future_rows into l_future_row_exists;
52 if csr_check_future_rows%found then
53 close csr_check_future_rows;
54 return true;
55 else
56 close csr_check_future_rows;
57 return false;
58 end if;
59
60 end check_if_future_rows_exist;
61
62
63 --
64 --
65 --
66
67
68 function get_batch_line_id
69 ( p_user_row_user_key varchar2
70 ,p_user_column_user_key varchar2
71 ,p_mode varchar2
72 ) return number
73 is
74 l_batch_line_id number;
75 l_statement varchar2(2000);
76 ref_csr_get_batch_line_id ref_cursor_type;
77
78 begin
79 if p_mode = 'CREATE' then
80 l_statement := 'select batch_line_id from hrdpv_create_user_column_insta where
81 p_user_row_user_key = :1 and p_user_column_user_key = :2';
82 else
83 l_statement := 'select batch_line_id from hrdpv_update_user_column_insta where
84 p_user_row_user_key = :1 and p_user_column_user_key = :2';
85 end if;
86
87 open ref_csr_get_batch_line_id for l_statement using p_user_row_user_key,p_user_column_user_key;
88 fetch ref_csr_get_batch_line_id into l_batch_line_id;
89 close ref_csr_get_batch_line_id;
90
91 return l_batch_line_id;
92 end get_batch_line_id;
93
94
95 --
96 --
97 --
98
99 function update_live_table_value
100 (
101 p_user_row_name varchar2
102 ,p_user_column_name varchar2
103 ,p_user_table_name varchar2
104 ,p_effective_date date
105 ,p_business_group_id number
106 ,p_value varchar2
107 ,p_batch_id number
108 ,p_link_value number
109 ) return boolean
110 is
111
112 l_user_table_id number;
113 l_user_row_id number;
114 l_user_column_id number;
115 l_user_column_instance_id number;
116 l_update_mode varchar2(30);
117 l_record_start_date date;
118 l_record_business_group_id number;
119 l_record_legislation_code varchar2(10);
120 l_user_row_user_key varchar2(240);
121 l_user_column_user_key varchar2(240);
122 l_business_group_name varchar2(240);
123 l_batch_line_id number;
124 l_statement varchar2(32000);
125
126 cursor csr_get_user_column_insta_id(c_user_row_id number,c_user_column_id number,c_effective_date date)
127 is
128 select user_column_instance_id,effective_start_date,business_group_id,legislation_code
129 from pay_user_column_instances_f
130 where user_row_id = c_user_row_id
131 and user_column_id = c_user_column_id
132 and c_effective_date between effective_start_date and effective_end_date;
133
134 cursor csr_get_user_table_id( c_user_table_name varchar2,c_business_group_id number)
135 is
136 select user_table_id
137 from pay_user_tables
138 where user_table_name = c_user_table_name
139 and (business_group_id is null or business_group_id = c_business_group_id)
140 and (legislation_code is null or legislation_code = hr_api.return_legislation_code(c_business_group_id));
141
142
143 cursor csr_get_user_column_id( c_user_column_name varchar2,c_user_table_id number)
144 is
145 select user_column_id
146 from pay_user_columns
147 where user_table_id = c_user_table_id
148 and user_column_name = c_user_column_name;
149
150 cursor csr_get_user_row_id( c_user_row_name varchar2,c_user_table_id number,c_effective_date date)
151 is
152 select user_row_id
153 from pay_user_rows_f
154 where row_low_range_or_name = c_user_row_name
155 and user_table_id = c_user_table_id
156 and c_effective_date between effective_start_date and effective_end_date;
157
158 cursor csr_get_business_group_name (c_business_group_id number)
159 is
160 select name
161 from per_business_groups
162 where business_group_id = c_business_group_id;
163
164 begin
165 open csr_get_user_table_id(p_user_table_name,p_business_group_id);
166 fetch csr_get_user_table_id into l_user_table_id;
167 close csr_get_user_table_id;
168
169 open csr_get_user_row_id(p_user_row_name,l_user_table_id,p_effective_date);
170 fetch csr_get_user_row_id into l_user_row_id;
171 close csr_get_user_row_id;
172
173 open csr_get_user_column_id(p_user_column_name,l_user_table_id);
174 fetch csr_get_user_column_id into l_user_column_id;
175 close csr_get_user_column_id;
176
177 open csr_get_user_column_insta_id(l_user_row_id,l_user_column_id, p_effective_date);
178 fetch csr_get_user_column_insta_id into l_user_column_instance_id,l_record_start_date,
179 l_record_business_group_id,l_record_legislation_code;
180
181 if (csr_get_user_column_insta_id%found) then
182 close csr_get_user_column_insta_id;
183
184 if (l_record_business_group_id is null and l_record_legislation_code is null ) then
185 fnd_message.set_name('PER', 'PER_289140_STARTUP_GEN_MOD_ERR');
186 fnd_message.raise_error;
187
188 elsif( l_record_business_group_id is null and l_record_legislation_code is not null) then
189 fnd_message.set_name('PER', 'PER_289142_STARTUP_ST_MODE_ERR');
190 fnd_message.raise_error;
191 end if;
192
193
194
195 open csr_get_business_group_name(p_business_group_id);
196 fetch csr_get_business_group_name into l_business_group_name;
197 close csr_get_business_group_name;
198
199 l_user_row_user_key := 'PAY_USER_ROW:' || p_user_table_name ||':'|| l_business_group_name || '#' ||p_user_row_name;
200 l_user_column_user_key := 'PAY_USER_COLUMN:'||p_user_table_name ||':'|| l_business_group_name || '#' ||p_user_column_name;
201
202 l_batch_line_id := get_batch_line_id(p_user_row_user_key =>l_user_row_user_key
203 ,p_user_column_user_key => l_user_column_user_key
204 ,p_mode => 'UPDATE');
205
206 if (check_if_future_rows_exist(l_user_column_instance_id,p_effective_date) ) then
207 l_update_mode := 'UPDATE_CHANGE_INSERT';
208 else
209 if l_record_start_date = p_effective_date then
210 l_update_mode := 'CORRECTION';
211 else
212 l_update_mode := 'UPDATE';
213 end if;
214 end if;
215
216 l_statement := 'BEGIN '||
217 'hrdpp_update_user_column_insta.insert_batch_lines( ' ||
218 'p_batch_id => :1 ' ||
219 ',p_data_pump_batch_line_id => :2 '||
220 ',p_data_pump_business_grp_name => :3 '||
221 ',p_user_sequence => :4 '||
222 ',p_link_value => :5 '||
223 ',p_effective_date => :6 '||
224 ',P_DATETRACK_UPDATE_MODE => :7 '||
225 ',P_VALUE => :8 '||
226 ',P_USER_COLUMN_USER_KEY => :9 '||
227 ',P_USER_ROW_USER_KEY => :10 );' ||
228 'END;';
229
230 execute immediate l_statement using p_batch_id,l_batch_line_id,l_business_group_name,40,p_link_value,p_effective_date,
231 l_update_mode,p_value,l_user_column_user_key,l_user_row_user_key;
232
233
234 return false;
235
236 else
237 close csr_get_user_column_insta_id;
238 return true;
239 end if;
240
241 end update_live_table_value;
242
243 --
244 --
245 --
246
247
248 procedure create_data_pump_batch_lines
249 (
250 p_batch_id in number
251 ,p_data_pump_batch_line_id in number default null
252 ,p_data_pump_business_grp_name in varchar2
253 ,p_effective_date in date
254 ,p_row_low_range_or_name in varchar2
255 ,p_user_table_name in varchar2
256 ,p_row_high_range in varchar2 default null
257 ,p_value1 in varchar2 default null
258 ,p_value2 in varchar2 default null
259 ,p_value3 in varchar2 default null
260 ,p_value4 in varchar2 default null
261 ,p_value5 in varchar2 default null
262 ,p_value6 in varchar2 default null
263 ,p_value7 in varchar2 default null
264 ,p_value8 in varchar2 default null
265 ,p_value9 in varchar2 default null
266 ,p_value10 in varchar2 default null
267 ,p_value11 in varchar2 default null
268 ,p_value12 in varchar2 default null
269 ,p_value13 in varchar2 default null
270 ,p_value14 in varchar2 default null
271 ,p_value15 in varchar2 default null
272 ,p_value16 in varchar2 default null
273 ,p_value17 in varchar2 default null
274 ,p_value18 in varchar2 default null
275 ,p_value19 in varchar2 default null
276 ,p_value20 in varchar2 default null
277 ,p_value21 in varchar2 default null
278 ,p_value22 in varchar2 default null
279 ,p_value23 in varchar2 default null
280 ,p_value24 in varchar2 default null
281 ,p_value25 in varchar2 default null
282 )
283 is
284
285
286 l_statement varchar2(32000);
287 l_user_column_user_keys userkeys;
288 l_user_row_user_key varchar2(240);
289 l_user_table_user_key varchar2(240);
290 l_user_column_user_key varchar2(240);
291 l_user_column_name varchar2(80);
292 l_old_value varchar2(80);
293 l_row_qualifier varchar2(30);
294 l_link_value number;
295 l_string_of_values varchar2(4000);
296 l_qualifier varchar2(30);
297 l_business_group_id number;
298 l_ret_status number;
299 l_batch_line_id number;
300 l_create_line boolean;
301
302 csr_ref_user_key ref_cursor_type;
303
304 counter number;
305 cur number;
306
307 cursor csr_get_business_group_id (c_business_group_name per_business_groups.name%type)
308 is
309 select business_group_id
310 from per_business_groups
311 where name = c_business_group_name;
312
313
314 begin
315 --
316 -- Form user keys from the supplied user name and row name.
317 --
318 l_user_table_user_key := 'PAY_USER_TABLE:' || p_data_pump_business_grp_name || '#' || p_user_table_name;
319 l_user_row_user_key := 'PAY_USER_ROW:' || p_user_table_name || ':' || p_data_pump_business_grp_name || '#' ||p_row_low_range_or_name;
320
321
322 open csr_get_business_group_id (p_data_pump_business_grp_name);
323 fetch csr_get_business_group_id into l_business_group_id;
324 close csr_get_business_group_id;
325
326
327 l_statement := 'select 1 from pay_user_rows_f pur,pay_user_tables put where row_low_range_or_name = :1 and
328 (put.business_group_id is null or put.business_group_id = :2)
329 and (put.legislation_code is null or put.legislation_code = hr_api.return_legislation_code(:3))
330 and put.user_table_id = pur.user_table_id and
331 put.user_table_name = :4';
332
333 open csr_ref_user_key for l_statement using p_row_low_range_or_name,l_business_group_id,l_business_group_id,p_user_table_name;
334 fetch csr_ref_user_key into l_ret_status;
335
336 if csr_ref_user_key%notfound then
337 l_row_qualifier := 'TEMP';
338 else
339 l_row_qualifier := 'LIVE';
340 end if;
341
342 close csr_ref_user_key;
343
344 --
345 -- Get the link value for the rows to be inserted.
346 --
347
348 l_link_value := get_link_value(null,p_data_pump_business_grp_name,l_user_row_user_key,null);
349
350 --
351 -- Get all the User Column User Keys corresponding to the passed table in
352 -- a pl/sql table.
353
354
355 l_statement :=
356 'select qualifier ,p_user_column_user_key
357 from (select ''LIVE'' qualifier,''PAY_USER_COLUMN:'' || :1 || '':'' || :2 || ''#'' ||user_column_name p_user_column_user_key
358 from pay_user_columns puc,pay_user_tables put
359 where put.user_table_name= :3
360 and puc.user_table_id=put.user_table_id
361 and (put.business_group_id is null or put.business_group_id = :4)
362 and (put.legislation_code is null or put.legislation_code = hr_api.return_legislation_code(:5))
363 Union
364 select ''TEMP'' qualifier,p_user_column_user_key
365 from hrdpv_create_user_column
366 where p_user_table_user_key= ''PAY_USER_TABLE:'' || :6 ||''#'' || :7 and
367 line_status <> ''C'')
368 order by upper(substr(p_user_column_user_key,instr(p_user_column_user_key,''#'',1,1)+1))';
369
370 counter:= 0;
371 open csr_ref_user_key for l_statement using p_user_table_name,p_data_pump_business_grp_name,
372 p_user_table_name,l_business_group_id,l_business_group_id,p_data_pump_business_grp_name,p_user_table_name;
373
374 loop
375 fetch csr_ref_user_key into l_qualifier,l_user_column_user_key;
376 exit when csr_ref_user_key%notfound;
377 counter := counter+1;
378 l_user_column_user_keys(counter).user_column_key := l_user_column_user_key;
379 l_user_column_user_keys(counter).qualifier := l_qualifier;
380 end loop;
381
382 close csr_ref_user_key;
383
384
385 --
386 -- Get the values corresponding to the user columns.
387 --
388 l_user_column_user_keys(1).user_column_value := p_value1;
389 l_user_column_user_keys(2).user_column_value := p_value2;
390 l_user_column_user_keys(3).user_column_value := p_value3;
391 l_user_column_user_keys(4).user_column_value := p_value4;
392 l_user_column_user_keys(5).user_column_value := p_value5;
393 l_user_column_user_keys(6).user_column_value := p_value6;
394 l_user_column_user_keys(7).user_column_value := p_value7;
395 l_user_column_user_keys(8).user_column_value := p_value8;
396 l_user_column_user_keys(9).user_column_value := p_value9;
397 l_user_column_user_keys(10).user_column_value := p_value10;
398 l_user_column_user_keys(11).user_column_value := p_value11;
399 l_user_column_user_keys(12).user_column_value := p_value12;
400 l_user_column_user_keys(13).user_column_value := p_value13;
401 l_user_column_user_keys(14).user_column_value := p_value14;
402 l_user_column_user_keys(15).user_column_value := p_value15;
403 l_user_column_user_keys(16).user_column_value := p_value16;
404 l_user_column_user_keys(17).user_column_value := p_value17;
405 l_user_column_user_keys(18).user_column_value := p_value18;
406 l_user_column_user_keys(19).user_column_value := p_value19;
407 l_user_column_user_keys(20).user_column_value := p_value20;
408 l_user_column_user_keys(21).user_column_value := p_value21;
409 l_user_column_user_keys(22).user_column_value := p_value22;
410 l_user_column_user_keys(23).user_column_value := p_value23;
411 l_user_column_user_keys(24).user_column_value := p_value24;
412 l_user_column_user_keys(25).user_column_value := p_value25;
413
414
415
416 l_string_of_values := get_matrix_row_values
417 (
418 p_batch_id => p_batch_id
419 ,p_user_table_name => p_user_table_name
420 ,p_row_low_range_or_name => p_row_low_range_or_name
421 ,p_business_group_id => l_business_group_id
422 );
423
424 --
425 -- Open the cursor before we enter the loop so that it doesn't get
426 -- opened multiple times.
427 --
428 cur:= dbms_sql.open_cursor;
429
430 --
431 -- Go through all the values and if any value has changed then only take some action
432 --
433
434 for i in 1..counter loop
438 l_user_column_name := substr(l_user_column_user_keys(i).user_column_key,instr(l_user_column_user_keys(i).user_column_key,'#',1,1)+1);
435 l_old_value
436 := substr(l_string_of_values,instr(l_string_of_values,'$',1,i)+1,instr(l_string_of_values,'$',1,i+1)-1 - instr(l_string_of_values,'$',1,i));
437
439
440 if ( nvl(l_user_column_user_keys(i).user_column_value, '<NULL>') <> nvl(l_old_value,'<NULL>') ) then
441
442 if ( l_user_column_user_keys(i).qualifier = 'LIVE' and l_row_qualifier ='LIVE') then
443 l_create_line := update_live_table_value(p_row_low_range_or_name
444 ,l_user_column_name
445 ,p_user_table_name
446 ,p_effective_date
447 ,l_business_group_id
448 ,l_user_column_user_keys(i).user_column_value
449 ,p_batch_id
450 ,l_link_value
451 );
452 -- if l_create_line then
453 insert_user_key(p_business_group => p_data_pump_business_grp_name
454 ,p_user_row_name => p_row_low_range_or_name
455 ,p_user_table_name => p_user_table_name
456 ,p_effective_date => p_effective_date);
457
458 insert_user_key(p_business_group => p_data_pump_business_grp_name
459 ,p_user_column_name => l_user_column_name
460 ,p_user_table_name => p_user_table_name);
461 -- end if;
462
463 else
464
465 l_create_line := true;
466
467 if ( l_user_column_user_keys(i).qualifier = 'TEMP' and l_row_qualifier = 'LIVE' ) then
468 insert_user_key(p_business_group => p_data_pump_business_grp_name
469 ,p_user_row_name => p_row_low_range_or_name
470 ,p_user_table_name => p_user_table_name
471 ,p_effective_date => p_effective_date);
472
473 elsif (l_user_column_user_keys(i).qualifier = 'LIVE' and l_row_qualifier = 'TEMP') then
474 insert_user_key(p_business_group => p_data_pump_business_grp_name
475 ,p_user_column_name => substr(l_user_column_user_keys(i).user_column_key,
476 instr(l_user_column_user_keys(i).user_column_key,'#',1,1)+1)
477 ,p_user_table_name => p_user_table_name);
478 end if;
479 end if;
480 l_batch_line_id := get_batch_line_id
481 (p_user_row_user_key => l_user_row_user_key
482 ,p_user_column_user_key => l_user_column_user_keys(i).user_column_key
483 ,p_mode => 'CREATE');
484 --
485 -- Call hrdpp_create_user_column_insta for each user column instance entered for a
486 -- column in batch lines table.
487 --
488 if (l_create_line) then
489
490 l_statement := 'BEGIN ' ||
491 'hrdpp_create_user_column_insta.insert_batch_lines' ||
492 '(p_batch_id => :batch_id ' ||
493 ',p_data_pump_batch_line_id => :data_pump_batch_line_id ' ||
494 ',p_user_sequence => :user_sequence '||
495 ',p_link_value => :link_value ' ||
496 ',p_data_pump_business_grp_name => :data_pump_business_grp_name '||
497 ',P_EFFECTIVE_DATE => :EFFECTIVE_DATE ' ||
498 ',P_VALUE => :VALUE '||
499 ',P_USER_ROW_USER_KEY => :USER_ROW_USER_KEY ' ||
500 ',P_USER_COLUMN_USER_KEY => :USER_COLUMN_USER_KEY );' ||
501 ' END;' ;
502
503 dbms_sql.parse(cur, l_statement,DBMS_SQL.NATIVE);
504 dbms_sql.bind_variable(cur,'batch_id',p_batch_id);
505 dbms_sql.bind_variable(cur,'data_pump_batch_line_id',l_batch_line_id);
506 dbms_sql.bind_variable(cur,'user_sequence',40);
507 dbms_sql.bind_variable(cur,'link_value',l_link_value);
508 dbms_sql.bind_variable(cur,'data_pump_business_grp_name',p_data_pump_business_grp_name);
509 dbms_sql.bind_variable(cur,'EFFECTIVE_DATE',P_EFFECTIVE_DATE);
510 dbms_sql.bind_variable(cur,'VALUE',l_user_column_user_keys(i).user_column_value);
511 dbms_sql.bind_variable(cur,'USER_ROW_USER_KEY',l_user_row_user_key);
512 dbms_sql.bind_variable(cur,'USER_COLUMN_USER_KEY',l_user_column_user_keys(i).user_column_key);
513
514 l_ret_status := dbms_sql.execute (cur);
515 end if;
516 end if;
517 end loop;
518 dbms_sql.close_cursor (cur);
519 commit;
520 end create_data_pump_batch_lines;
521
522 --
523 --
524 --
525
526 function batch_overall_status (p_batch_id number) return varchar2 is
527 --
528 -- Derives the overall status of the batch header, control totals and lines
529 --
530 --
531 l_batch_status varchar2(2);
532 cursor csr_status is
533 select batch_status status
534 from hr_pump_batch_headers
535 where batch_id = p_batch_id;
536 --
537 begin
538 --
539 open csr_status;
540 fetch csr_status into l_batch_status;
541 close csr_status;
542
543 if l_batch_status = 'C' then
544 return 'T';
545 else
546 return l_batch_status;
547 end if;
548
549 end batch_overall_status;
550
551
552
553 function get_link_value
554 (
555 p_batch_line_id number
556 ,p_business_group_name varchar2
557 ,p_user_row_user_key varchar2
558 ,p_user_table_user_key varchar2
559 ) return number
560 is
561
562 l_link_value number;
563 l_user_table_user_key varchar2(240);
564 l_user_table_name varchar2(240);
565 l_statement varchar2(4000);
566 ref_link_value ref_cursor_type;
567
568
569 begin
570
571 If (p_batch_line_id is not null)
572 then
573
574 select link_value into l_link_value
575 from hr_pump_batch_lines
576 where batch_line_id = p_batch_line_id;
577
578 else
579
580 if (p_user_row_user_key is not null)
581 then
582
586 substr(p_user_row_user_key, instr(p_user_row_user_key,':',1,1)+1,instr(p_user_row_user_key,':',1,2) - instr(p_user_row_user_key,':',1,1)-1);
583 --Derive the user table user key using business group name and user row user key.
584
585 l_user_table_user_key := 'PAY_USER_TABLE:' || p_business_group_name || '#' ||
587
588 else
589
590 l_user_table_user_key := p_user_table_user_key;
591 end if;
592
593 l_user_table_name := substr(l_user_table_user_key,instr(l_user_table_user_key,'#',1)+1);
594
595 l_statement :=
596 'select link_value
597 from hrdpv_create_user_table
598 where p_user_table_user_key = :1
599 and link_value is not null
600 Union
601 select link_value
602 From hrdpv_create_user_row
603 where p_user_table_user_key = :1
604 and link_value is not null
605 Union
606 select link_value
607 from hrdpv_create_user_column
608 where p_user_table_user_key = :1
609 and link_value is not null
610 Union
611 select link_value
612 from hrdpv_create_user_column_insta
613 where substr( p_user_row_user_key, (instr(p_user_row_user_key, '':'' , 1)+1),
614 (instr(p_user_row_user_key, ''#'' ,1)) - (instr(p_user_row_user_key, '':'' , 1)+1) )
615 = :2 || '':'' || :3
616 and link_value is not null';
617
618 open ref_link_value for l_statement
619 using l_user_table_user_key,l_user_table_user_key,l_user_table_user_key,l_user_table_name,p_business_group_name;
620 fetch ref_link_value into l_link_value;
621 if ref_link_value%notfound
622 then
623 select nvl(max(link_value),0)+1 into l_link_value from hr_pump_batch_lines;
624 end if;
625 close ref_link_value;
626 end if;
627
628 return l_link_value;
629 end get_link_value;
630
631 --
632 --
633 --
634
635 procedure insert_user_key
636 (
637 p_business_group varchar2
638 ,p_user_column_name varchar2
639 ,p_user_table_name varchar2
640 )
641 is
642
643 l_user_key_value varchar2(240);
644 l_user_column_id pay_user_columns.user_column_id%type;
645 l_user_key_id number;
646
647
648 cursor csr_get_user_column_id (c_business_group hr_organization_units.name%type
649 ,c_user_column_name pay_user_columns.user_column_name%type
650 ,c_user_key_value varchar2
651 ,c_user_table_name pay_user_tables.user_table_name%type)
652 is
653 select puc.user_column_id
654 from pay_user_columns puc,
655 per_business_groups pbg,
656 pay_user_tables put
657 where (put.business_group_id is null or put.business_group_id = pbg.business_group_id)
658 and (put.legislation_code is null or put.legislation_code= hr_api.return_legislation_code(pbg.business_group_id))
659 and pbg.name = c_business_group
660 and puc.user_column_name = c_user_column_name
661 and puc.user_table_id = put.user_table_id
662 and put.user_table_name = c_user_table_name
663 and not exists(select null
664 from hr_pump_batch_line_user_keys
665 where user_key_value = c_user_key_value
666 and unique_key_id = puc.user_column_id);
667
668
669
670 cursor csr_check_user_key_exists(c_unique_key_id number)
671 is
672 select user_key_id
673 from hr_pump_batch_line_user_keys
674 where user_key_value like 'PAY_USER_COLUMN%'
675 and unique_key_id = c_unique_key_id;
676
677
678 begin
679 l_user_key_value := 'PAY_USER_COLUMN:'||p_user_table_name||':'||p_business_group||'#'||p_user_column_name;
680
681 open csr_get_user_column_id(p_business_group,p_user_column_name,l_user_key_value,p_user_table_name);
682 fetch csr_get_user_column_id into l_user_column_id;
683
684 if csr_get_user_column_id%found then
685
686 close csr_get_user_column_id;
687 open csr_check_user_key_exists(l_user_column_id);
688 fetch csr_check_user_key_exists into l_user_key_id;
689
690 if csr_check_user_key_exists%found then
691 delete from hr_pump_batch_line_user_keys where user_key_id = l_user_key_id;
692 end if;
693
694 close csr_check_user_key_exists;
695 hr_pump_utils.add_user_key
696 (
697 p_user_key_value => l_user_key_value
698 ,p_unique_key_id => l_user_column_id
699 );
700
701 else
702 close csr_get_user_column_id;
703 end if;
704 end insert_user_key;
705
706 --
707 --
708 --
709
710 procedure insert_user_key
711 (
712 p_business_group varchar2
713 ,p_user_row_name varchar2
714 ,p_user_table_name varchar2
715 ,p_effective_date date
716 )
717 is
718
719 l_user_key_value varchar2(240);
720 l_user_row_id pay_user_rows_f.user_row_id%type;
721 l_user_key_id number;
722
723 cursor csr_get_user_row_id(
724 c_business_group hr_organization_units.name%type
725 ,c_user_row_name pay_user_rows_f.row_low_range_or_name%type
726 ,c_effective_date date
727 ,c_user_key_value varchar2
728 ,c_user_table_name pay_user_tables.user_table_name%type)
729 is
730 select pur.user_row_id
731 from pay_user_rows_f pur,
732 per_business_groups pbg,
733 pay_user_tables put
734 where (put.business_group_id is null or put.business_group_id= pbg.business_group_id)
735 and (put.legislation_code is null or put.legislation_code = hr_api.return_legislation_code(pbg.business_group_id))
736 and pbg.name = c_business_group
737 and c_effective_date between pur.effective_start_date and pur.effective_end_date
741 and not exists(select null
738 and pur.row_low_range_or_name = c_user_row_name
739 and pur.user_table_id = put.user_table_id
740 and put.user_table_name = c_user_table_name
742 from hr_pump_batch_line_user_keys
743 where user_key_value = c_user_key_value
744 and unique_key_id = pur.user_row_id);
745
746
747 cursor csr_check_user_key_exists(c_unique_key_id number)
748 is
749 select user_key_id
750 from hr_pump_batch_line_user_keys
751 where user_key_value like 'PAY_USER_ROW%'
752 and unique_key_id = c_unique_key_id;
753
754
755
756 begin
757 l_user_key_value := 'PAY_USER_ROW:'||p_user_table_name||':'||p_business_group||'#'||p_user_row_name;
758
759 open csr_get_user_row_id(p_business_group,
760 p_user_row_name,
761 p_effective_date,
762 l_user_key_value,
763 p_user_table_name);
764 fetch csr_get_user_row_id into l_user_row_id;
765
766 if csr_get_user_row_id%found then
767
768 close csr_get_user_row_id;
769 open csr_check_user_key_exists(l_user_row_id);
770 fetch csr_check_user_key_exists into l_user_key_id;
771
772 if csr_check_user_key_exists%found then
773 delete from hr_pump_batch_line_user_keys where user_key_id = l_user_key_id;
774 end if;
775
776 close csr_check_user_key_exists;
777 hr_pump_utils.add_user_key
778 (
779 p_user_key_value => l_user_key_value
780 ,p_unique_key_id => l_user_row_id
781 );
782
783 else
784 close csr_get_user_row_id;
785 end if;
786 end insert_user_key;
787
788
789 --
790 --
791 --
792
793
794 procedure insert_user_key
795 (
796 p_business_group varchar2
797 ,p_user_table_name varchar2
798 )
799 is
800
801 l_user_table_id pay_user_tables.user_table_id%type;
802 l_user_key_value varchar2(240);
803 l_user_key_id number;
804
805 cursor csr_get_user_table_id( c_user_key_value varchar2
806 ,c_business_group hr_organization_units.name%type
807 ,c_user_table_name pay_user_tables.user_table_name%type)
808 is
809 select put.user_table_id
810 from pay_user_tables put,per_business_groups pbg
811 where user_table_name = c_user_table_name
812 and pbg.name = c_business_group
813 and (put.business_group_id is null or put.business_group_id = pbg.business_group_id)
814 and (put.legislation_code is null or put.legislation_code= hr_api.return_legislation_code(pbg.business_group_id));
815
816 cursor csr_check_user_key_exists(c_unique_key_id number)
817 is
818 select user_key_id
819 from hr_pump_batch_line_user_keys
820 where user_key_value like 'PAY_USER_TABLE%'
821 and unique_key_id = c_unique_key_id;
822 begin
823
824 l_user_key_value := 'PAY_USER_TABLE:'||p_business_group||'#'||p_user_table_name;
825
826 open csr_get_user_table_id(l_user_key_value,p_business_group,p_user_table_name);
827 fetch csr_get_user_table_id into l_user_table_id;
828
829
830 if csr_get_user_table_id%found then
831
832 close csr_get_user_table_id;
833 open csr_check_user_key_exists(l_user_table_id);
834 fetch csr_check_user_key_exists into l_user_key_id;
835
836 if csr_check_user_key_exists%found then
837 delete from hr_pump_batch_line_user_keys where user_key_id = l_user_key_id;
838 end if;
839
840 close csr_check_user_key_exists;
841 hr_pump_utils.add_user_key
842 (
843 p_user_key_value => l_user_key_value
844 ,p_unique_key_id => l_user_table_id
845 );
846
847 else
848 close csr_get_user_table_id;
849 end if;
850
851 end insert_user_key;
852
853 --
854 --
855 --
856
857
858 function get_matrix_row_values
859 (
860 p_batch_id number
861 ,p_user_table_name varchar2
862 ,p_row_low_range_or_name varchar2
863 ,p_business_group_id number
864 )return varchar2
865 is
866
867 type values_type is varray(25) of varchar2(80);
868 type ordered_columns_type is record(column_name varchar2(80), order_num number);
869 type ordered_columns_table is table of ordered_columns_type index by binary_integer;
870
871 l_user_table_id number;
872 l_ordered_columns ordered_columns_table;
873 l_values values_type;
874 l_business_group_name varchar2(240);
875 l_user_row_user_key varchar2(240);
876 l_return_string varchar2(32000);
877 l_counter number;
878 l_qualified_value varchar2(160);
879 l_extracted_value varchar2(80);
880 l_statement varchar2(32000);
881 l_column_name varchar2(80);
882 l_column_order_num number;
883
884 ref_csr_user_columns ref_cursor_type;
885 ref_csr_user_row_values ref_cursor_type;
886
887
888
889 cursor csr_get_user_table_id (c_business_group_id number, c_user_table_name varchar2)
890 is
891 select user_table_id
892 from pay_user_tables
893 where (business_group_id is null or business_group_id = c_business_group_id )
894 and (legislation_code is null or legislation_code = hr_api.return_legislation_code(c_business_group_id))
895 and user_table_name = c_user_table_name;
896
897 cursor csr_get_business_group_name(c_business_group_id number)
898 is
899 select name
900 from per_business_groups
901 where business_group_id = c_business_group_id;
902
903
904
905 begin
906
907 open csr_get_business_group_name(p_business_group_id);
908 fetch csr_get_business_group_name into l_business_group_name;
909 close csr_get_business_group_name;
910 --
911
915
912 open csr_get_user_table_id(p_business_group_id,p_user_table_name);
913 fetch csr_get_user_table_id into l_user_table_id ;
914 close csr_get_user_table_id;
916 --
917 l_values := values_type(null,null,null,null,null,null,null,null,null,null,null,null,null,null,
918 null,null,null,null,null,null,null,null,null,null,null);
919 for i in 1..25 loop
920 l_ordered_columns(i).column_name := null;
921 l_ordered_columns(i).order_num := null;
922 end loop;
923 --
924
925
926 l_statement :='select user_column_name, rownum
927 from (select * from
928 (select user_column_name
929 from pay_user_columns puc
930 where puc.user_table_id = :1
931
932 Union
933
934 select p_user_column_name
935 from hrdpv_create_user_column
936 where p_user_table_user_key=''PAY_USER_TABLE:'' || :2 ||''#'' || :3 and line_status <> ''C'')
937 order by upper(user_column_name))';
938 --
939 l_counter := 0;
940 open ref_csr_user_columns for l_statement using l_user_table_id,l_business_group_name, p_user_table_name;
941 loop
942 l_counter:= l_counter + 1;
943 fetch ref_csr_user_columns into l_column_name,l_column_order_num;
944 exit when ref_csr_user_columns%notfound;
945
946 l_ordered_columns(l_counter).column_name := l_column_name;
947 l_ordered_columns(l_counter).order_num :=l_column_order_num;
948
949 end loop;
950 close ref_csr_user_columns;
951
952 --
953
954 l_user_row_user_key := 'PAY_USER_ROW:'|| p_user_table_name || ':' || l_business_group_name || '#'
955 || p_row_low_range_or_name;
956
957 l_statement := '(select puc.user_column_name || '':'' || pui.value qualified_value
958 from pay_user_column_instances_f pui,
959 pay_user_columns puc,
960 pay_user_rows_f pur
961 where pur.row_low_range_or_name = :1
962 and pur.user_table_id = :2
963 and puc.user_table_id = pur.user_table_id
964 and pui.user_column_id = puc.user_column_id
965 and pui.user_row_id = pur.user_row_id
966 and sysdate between pui.effective_start_date and pui.effective_end_date
967 Union
968 select substr(p_user_column_user_key,instr(p_user_column_user_key,''#'',1,1)+1) || '':'' || p_value
969 qualified_value
970 from hrdpv_create_user_column_insta
971 where p_user_row_user_key = :3
972 and line_status <>''C'')';
973
974
975 open ref_csr_user_row_values for l_statement using p_row_low_range_or_name,l_user_table_id,
976 l_user_row_user_key;
977
978 loop
979 fetch ref_csr_user_row_values into l_qualified_value;
980 exit when ref_csr_user_row_values%notfound;
981 l_extracted_value:= substr(l_qualified_value,1,instr(l_qualified_value,':',1,1)-1);
982 for i in 1..l_ordered_columns.count loop
983 if l_extracted_value = l_ordered_columns(i).column_name then
984 l_values(l_ordered_columns(i).order_num) :=
985 substr(l_qualified_value,instr(l_qualified_value,':',1,1)+1);
986 end if;
987 end loop;
988 end loop;
989
990 close ref_csr_user_row_values;
991
992 l_return_string := '$';
993
994 for i in 1..25 loop
995 l_return_string := l_return_string || l_values(i) || '$';
996 end loop;
997
998 return l_return_string;
999
1000 end get_matrix_row_values;
1001
1002 --
1003 --
1004 --
1005
1006 end pay_user_column_insta_matrix;