1 PACKAGE BODY pqp_update_work_pattern_table AS
2 /* $Header: pquwprow.pkb 120.1 2005/08/24 05:01 akarmaka noship $ */
3
4 g_package_name VARCHAR2(61) := 'pqp_update_work_pattern_table.' ;
5 g_debug BOOLEAN ;
6 g_legislation_code VARCHAR2(30) ;
7 g_table_name pay_user_tables.user_table_name%TYPE
8 :='PQP_COMPANY_WORK_PATTERNS' ;
9 g_row_ids t_row_ids ;
10
11 -- to cache the row_ids/name of the 3 seeded rows
12 -- 1. 'Average Working Days Per Week'
13 -- 2. 'Total Number of Days'
14 -- 3. 'Number of Working Days'
15 g_row_names_seed t_row_names ;
16 g_row_ids_seed t_row_details ;
17
18
19 --
20 --
21 --
22 PROCEDURE debug(
23 p_trace_message IN VARCHAR2
24 ,p_trace_location IN NUMBER DEFAULT NULL
25 )
26 IS
27 BEGIN
28 pqp_utilities.debug(p_trace_message, p_trace_location) ;
29 END debug;
30 --
31 --
32 --
33 PROCEDURE debug(p_trace_number IN NUMBER)
34 IS
35 BEGIN
36 pqp_utilities.debug(p_trace_number) ;
37 END debug;
38
39 --
40 --
41 --
42 PROCEDURE debug(p_trace_date IN DATE)
43 IS
44 BEGIN
45 pqp_utilities.debug(p_trace_date) ;
46 END debug;
47
48 --
49 --
50 --
51 PROCEDURE debug_enter(
52 p_proc_name IN VARCHAR2
53 ,p_trace_on IN VARCHAR2 DEFAULT NULL
54 )
55 IS
56 BEGIN
57 pqp_utilities.debug_enter(p_proc_name, p_trace_on) ;
58 END debug_enter;
59
60 --
61 --
62 --
63 PROCEDURE debug_exit(
64 p_proc_name IN VARCHAR2
65 ,p_trace_off IN VARCHAR2 DEFAULT NULL
66 )
67 IS
68 BEGIN
69 pqp_utilities.debug_exit(p_proc_name, p_trace_off) ;
70 END debug_exit;
71
72 --
73 --
74 --
75 PROCEDURE debug_others(
76 p_proc_name IN VARCHAR2
77 ,p_proc_step IN NUMBER DEFAULT NULL
78 )
79 IS
80 BEGIN
81 pqp_utilities.debug_others(p_proc_name, p_proc_step) ;
82 END debug_others;
83 --
84 --
85 --
86 PROCEDURE check_error_code
87 (p_error_code IN NUMBER
88 ,p_error_message IN VARCHAR2
89 )
90 IS
91 BEGIN
92 pqp_utilities.check_error_code(p_error_code, p_error_message) ;
93 END;
94 --
95 --
96 --
97 PROCEDURE clear_cache
98 IS
99 BEGIN
100 NULL;
101 END;
102 --
103 --
104 --
105
106 PROCEDURE update_working_days_in_week (
107 errbuf OUT NOCOPY VARCHAR2
108 ,retcode OUT NOCOPY NUMBER
109 ,p_column_name IN VARCHAR2
110 ,p_business_group_id IN NUMBER
111 ,p_overwrite_if_exists IN VARCHAR2
112 )
113 IS
114
115
116 CURSOR csr_get_table_id (
117 p_table_name IN VARCHAR2
118 ,p_legislation_code IN VARCHAR2
119 ,p_business_group_id IN NUMBER )
120 IS
121 SELECT user_table_id
122 FROM pay_user_tables
123 WHERE user_table_name = p_table_name
124 AND ( legislation_code = p_legislation_code
125 OR business_group_id = p_business_group_id ) ;
126
127
128 CURSOR csr_user_columns (
129 p_user_table_id IN NUMBER
130 ,p_column_name IN VARCHAR2
131 ,p_business_group_id IN NUMBER )
132 IS
133 SELECT puc.user_column_name user_column_name
134 ,puc.user_column_id user_column_id
135 FROM pay_user_columns puc
136 WHERE puc.user_table_id = p_user_table_id
137 AND (puc.business_group_id = p_business_group_id
138 OR puc.legislation_code = g_legislation_code )
139 AND ( puc.user_column_name LIKE p_column_name
140 OR p_column_name IS NULL );
141
142
143 CURSOR csr_get_row_id (
144 p_user_table_id IN NUMBER
145 ,p_user_row_name IN VARCHAR2
146 ,p_business_group_id IN NUMBER
147 ,p_legislation_code IN VARCHAR2 )
148 IS
149 SELECT pur.USER_ROW_ID
150 ,pur.effective_start_date
151 ,pur.effective_end_date
152 FROM pay_user_rows_f pur
153 WHERE user_table_id = p_user_table_id
154 AND row_low_range_or_name = p_user_row_name
155 AND ( pur.business_group_id = p_business_group_id
156 OR pur.legislation_code = p_legislation_code )
157 ;
158
159
160 CURSOR csr_get_day01_eff_date (
161 p_user_column_id IN NUMBER
162 ,p_user_row_id IN NUMBER
163 ,p_business_group_id IN NUMBER
164 ) IS
165 SELECT puci.effective_start_date effective_start_date
166 ,puci.effective_end_date effective_end_date
167 FROM pay_user_column_instances_f puci
168 WHERE ( puci.business_group_id = p_business_group_id
169 OR puci.legislation_code = g_legislation_code )
170 AND puci.user_column_id = p_user_column_id
171 AND puci.user_row_id = p_user_row_id ;
172
173
174 l_user_table_id pay_user_tables.user_table_id%TYPE ;
175 l_row_effective_start_date DATE ;
176 l_row_effective_end_date DATE ;
177
178 l_row_values t_row_values ; --Collection to keep values of 3 rows.
179
180 idx NUMBER := 0 ; -- Index for g_row_ids_seed and g_row_names_seed
181 -- and l_row_values collections
182 l_proc_step NUMBER(20,10);
183 l_proc_name VARCHAR2(200) := g_package_name||
184 'update_working_days_in_week';
185
186
187 l_day01_row_id pay_user_rows_f.user_row_id%TYPE ;
188 l_day01_details csr_get_row_id%ROWTYPE ;
189
190 l_day01_eff_start_date date ;
191 l_day01_eff_end_date date ;
192
193 BEGIN
194
195 g_debug := hr_utility.debug_enabled ;
196
197 IF g_debug THEN
198 debug_enter(l_proc_name);
199 debug('p_column_name:'||p_column_name);
200 debug('p_business_group_id:'||p_business_group_id);
201 debug('g_legislation_code:'||g_legislation_code);
202 END IF ;
203
204 --Get the legislation code
205 g_legislation_code := pqp_utilities.pqp_get_legislation_code(
206 p_business_group_id => p_business_group_id);
207
208
209
210 -- Cache the names of the rows to be used later.
211 IF g_row_names_seed.COUNT = 0 THEN
212
213 l_proc_step := 20;
214 IF g_debug THEN
215 debug(l_proc_name,l_proc_step);
216 END IF;
217 --Populate the collection with the seeded row names.
218 g_row_names_seed(1) := 'Average Working Days Per Week' ;
219 g_row_names_seed(2) := 'Total Number of Days' ;
220 g_row_names_seed(3) := 'Number of Working Days' ;
221
222 ELSE -- IF g_row_names_seed.COUNT = 0 THEN
223
224 l_proc_step := 25 ;
225 IF g_debug THEN
226 debug(l_proc_name,l_proc_step) ;
227 debug('Row Names already cached') ;
228 END IF;
229 END IF ; --IF g_row_names_seed.COUNT = 0 THEN
230
231 l_proc_step := 30;
232 IF g_debug THEN
233 debug(l_proc_name,l_proc_step);
234 END IF;
235
236 --Get table ID
237 OPEN csr_get_table_id ( p_table_name => g_table_name
238 ,p_legislation_code => g_legislation_code
239 ,p_business_group_id => p_business_group_id );
240 FETCH csr_get_table_id INTO l_user_table_id ;
241 CLOSE csr_get_table_id ;
242 IF g_debug THEN
243 debug('l_user_table_id : '||l_user_table_id);
244 END IF;
245
246 -- cache the row ID's for the 3 rows seeded to keep the WP information
247 -- that is being used all over.
248 l_proc_step := 40;
249 IF g_debug THEN
250 debug('g_row_ids_seed.COUNT '|| to_char(g_row_ids_seed.COUNT));
251 END IF;
252 IF g_row_ids_seed.COUNT = 0 THEN
253
254 idx := g_row_names_seed.FIRST;
255
256 IF g_debug THEN
257 debug(l_proc_name,l_proc_step) ;
258 debug_enter('WHILE idx IS NOT NULL LOOP') ;
259 END IF ;
260
261 WHILE idx IS NOT NULL LOOP
262
263 l_proc_step := 40 + idx/10000;
264 IF g_debug THEN
265 debug(l_proc_name,l_proc_step);
266 debug('idx:'||idx);
267 END IF ;
268
269 OPEN csr_get_row_id ( p_user_table_id => l_user_table_id
270 ,p_user_row_name => g_row_names_seed(idx)
271 ,p_business_group_id => p_business_group_id
272 ,p_legislation_code => g_legislation_code ) ;
273
274 FETCH csr_get_row_id INTO g_row_ids_seed(idx).user_row_id
275 ,g_row_ids_seed(idx).effective_start_date
276 ,g_row_ids_seed(idx).effective_end_date ;
277 CLOSE csr_get_row_id ;
278
279 l_proc_step := 50 + idx/10000;
280 IF g_debug THEN
281 debug(l_proc_name,l_proc_step);
282 debug('user_row_id:'||g_row_ids_seed(idx).user_row_id);
283 debug('effective_start_date:'||g_row_ids_seed(idx).effective_start_date);
284 debug('effective_end_date:'||g_row_ids_seed(idx).effective_end_date);
285 END IF;
286
287 idx := g_row_names_seed.NEXT(idx) ;
288
289 END LOOP ; -- WHILE idx IS NOT NULL LOOP
290
291 l_proc_step := 55 ;
292 IF g_debug THEN
293 debug(l_proc_name,l_proc_step) ;
294 debug_exit('WHILE idx IS NOT NULL LOOP') ;
295 END IF ;
296
297 ELSE -- IF g_row_ids_seed.COUNT = 0 THEN
298 IF g_debug THEN
299 debug(l_proc_name,l_proc_step) ;
300 debug('Row IDs already cached') ;
301 END IF;
302 END IF ; -- IF g_row_ids_seed.COUNT = 0 THEN
303
304
305 -- The effective date for the calculation of Average Days per week is
306 -- the date "Day 01" value is defined in that work pattern.
307 -- below code gets the row id for the Day 01
308
309 -- here the effective date should be the efective date of the value set for 'Day 01'
310
311 OPEN csr_get_row_id ( p_user_table_id => l_user_table_id
312 ,p_user_row_name => 'Day 01'
313 ,p_business_group_id => p_business_group_id
314 ,p_legislation_code => g_legislation_code ) ;
315 FETCH csr_get_row_id INTO l_day01_details ;
316 l_day01_row_id := l_day01_details.user_row_id ;
317 CLOSE csr_get_row_id ;
318
319
320
321 -- get the column id for all the work patterns
322 -- get all the WP and process them one by one
323 -- calculate the vlaue for each row
324 -- check if the row already exists
325 -- if yes, check if it qualifies for updation, then update it
326 -- if no, insert a new row.
327
328 l_proc_step := 70;
329 IF g_debug THEN
330 debug(l_proc_name,l_proc_step);
331 debug_enter('FOR l_user_columns IN csr_user_columns') ;
332 END IF;
333
334 FOR l_user_columns IN csr_user_columns(p_user_table_id => l_user_table_id
335 ,p_column_name => p_column_name
336 ,p_business_group_id => p_business_group_id )
337 LOOP
338 l_proc_step := 72;
339 IF g_debug THEN
340 debug(l_proc_name,l_proc_step);
341 debug('l_user_columns.user_column_id:'||l_user_columns.user_column_id);
342 debug('l_user_columns.user_column_name:'||l_user_columns.user_column_name);
343 debug('l_user_table_id:'||l_user_table_id);
344 debug('p_business_group_id:'||p_business_group_id);
345 END IF;
346
347
348 -- for the 'Day 01' get the effective date of value defined
349 -- and pass that down to calculate Average Number of Days per week
350 OPEN csr_get_day01_eff_date (
351 p_user_column_id => l_user_columns.user_column_id
352 ,p_user_row_id => l_day01_row_id
353 ,p_business_group_id => p_business_group_id ) ;
354
355 FETCH csr_get_day01_eff_date INTO l_day01_eff_start_date,l_day01_eff_end_date ;
356 CLOSE csr_get_day01_eff_date ;
357
358 --Calculate the Values to update
359 l_row_values(1) := get_avg_working_days_in_week (
360 p_business_group_id => p_business_group_id
361 ,p_effective_date => l_day01_eff_start_date
362 ,p_user_column_id => l_user_columns.user_column_id
363 ,p_user_table_id => l_user_table_id
364 ,p_total_days_defined => l_row_values(2) --OUT -- total_days_defined
365 ,p_total_working_days_defined => l_row_values(3) ) ;--OUT -- total_working_days_defined ) ;
366
367 l_proc_step := 110;
368 IF g_debug THEN
369 debug(l_proc_name,l_proc_step);
370 debug('avg_work_days_in_week:'||l_row_values(1));
371 debug('total_days_defined:'||l_row_values(2));
372 debug('total_working_days_defined:'||l_row_values(3));
373 END IF ;
374
375 -- update/insert value for seeded rows one by one
376 -- in the table pay_user_column_instances_f.
377
378 -- If the value for the said column already exists and
379 -- the user has not updated the row manually, then
380 -- update the value, else leave it as it is.
381 -- but if the value does not exists for the column,
382 -- insert a new row in the table pay_user_column_instances_f.
383
384 -- if user has explicitly given the name of the WP, update the row
385 -- even if it is updated by user manually.
386
387 --FOR idx IN 1..3 LOOP
388 idx := g_row_names_seed.FIRST;
389 l_proc_step := 120;
390 IF g_debug THEN
391 debug(l_proc_name,l_proc_step);
392 debug_enter('WHILE idx IS NOT NULL LOOP') ;
393 END IF;
394
395 WHILE idx IS NOT NULL LOOP
396
397 l_proc_step := l_proc_step + idx/10000;
398 IF g_debug THEN
399 debug_enter('update_insert for loop');
400 debug(l_proc_name,l_proc_step);
401 debug('g_row_names_seed:'||g_row_names_seed(idx));
402 debug('g_row_ids_seed:'||g_row_ids_seed(idx).user_row_id);
403 debug('l_row_values:'||l_row_values(idx));
404 END IF ;
405
406 update_insert_row(
407 p_user_column_id => l_user_columns.user_column_id
408 ,p_user_row_id => g_row_ids_seed(idx).user_row_id
409 ,p_effective_date => l_day01_eff_start_date
410 ,p_row_effective_start_date => l_day01_eff_start_date
411 ,p_row_effective_end_date => l_day01_eff_end_date
412 ,p_business_group_id => p_business_group_id
413 ,p_value_to_update => l_row_values(idx)
414 ,p_overwrite_if_exists => p_overwrite_if_exists
415 ) ;
416
417 IF g_debug THEN
418 debug_exit('update_insert for loop');
419 END IF;
420
421 idx := g_row_names_seed.NEXT(idx) ;
422 END LOOP ; -- WHILE idx IS NOT NULL LOOP
423
424 IF g_debug THEN
425 debug(l_proc_name,l_proc_step);
426 debug_exit('WHILE idx IS NOT NULL LOOP') ;
427 END IF;
428
429 END LOOP ; --FOR l_user_columns IN csr_user_columns
430 l_proc_step := 160;
431 IF g_debug THEN
432 debug(l_proc_name,l_proc_step);
433 debug_exit('FOR l_user_columns IN csr_user_columns') ;
434 debug_exit(l_proc_name) ;
435 END IF;
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 clear_cache;
440 errbuf := SQLERRM;
441 retcode := SQLCODE;
442 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
443 debug_others(l_proc_name,l_proc_step);
444 IF g_debug THEN
445 debug('Leaving: '||l_proc_name,-999);
446 END IF;
447 fnd_message.raise_error;
448 ELSE
449 RAISE;
450 END IF;
451
452 END update_working_days_in_week ;
453
454 -----------------------------------------------------------------------------------------
455 -- Function to calculate the values for
456 -- 1. "Average Working Days Per Week" -- As Return Value
457 -- 2. "Total Number of Days" -- As OUT Parameter p_total_days_defined
458 -- 3. "TNumber of Working Days" -- As OUT Parameter p_total_working_days_defined
459
460 FUNCTION get_avg_working_days_in_week (
461 p_business_group_id IN NUMBER
462 ,p_effective_date IN DATE
463 ,p_user_column_id IN pay_user_columns.user_column_id%TYPE
464 ,p_user_table_id IN pay_user_tables.user_table_id%TYPE
465 ,p_total_days_defined OUT NOCOPY NUMBER
466 ,p_total_working_days_defined OUT NOCOPY NUMBER
467 ) RETURN NUMBER IS
468
469
470 CURSOR csr_get_row_ids IS
471 SELECT user_row_id
472 FROM pay_user_rows_f pur
473 WHERE user_table_id = p_user_table_id
474 AND row_low_range_or_name LIKE 'Day __' -- this is hard coded as it is the seeded data.
475 AND p_effective_date BETWEEN pur.effective_start_date
476 AND pur.effective_end_date ;
477
478
479 l_no_of_days_defined NUMBER := 0 ;
480 l_no_of_working_days NUMBER := 0 ;
481 l_no_of_working_days_in_week NUMBER := 0 ;
482
483
484 l_value NUMBER ;
485 l_row_id VARCHAR2(200) ;
486 l_user_column_instance_id pay_user_column_instances_f.user_column_instance_id%TYPE ;
487 idx NUMBER := 1 ; --index for the rows
488
489 l_proc_step NUMBER(20,10);
490 l_proc_name VARCHAR2(200) := g_package_name||
491 'get_avg_working_days_in_week';
492 l_effective_start_date DATE;
493 l_effective_end_date DATE;
494
495 BEGIN
496
497 g_debug := hr_utility.debug_enabled;
498 IF g_debug THEN
499 debug_enter(l_proc_name);
500 debug('p_business_group_id:'||p_business_group_id) ;
501 debug('p_effective_date:'||p_effective_date) ;
502 debug('p_user_column_id:'||p_user_column_id) ;
503 debug('p_user_table_id:'||p_user_table_id) ;
504 END IF ;
505
506 -- Cache the rows into a collection
507
508 IF g_row_ids.COUNT = 0 THEN
509 l_proc_step := 75;
510 IF g_debug THEN
511 debug(l_proc_name,l_proc_step);
512 END IF ;
513
514 -- Get all the row Ids into the collection g_row_ids
515 OPEN csr_get_row_ids ;
516 FETCH csr_get_row_ids BULK COLLECT INTO g_row_ids ;
517 CLOSE csr_get_row_ids ;
518
519 l_proc_step := 80 ;
520 IF g_debug THEN
521 debug(l_proc_name,l_proc_step);
522 END IF;
523 ELSE
524 l_proc_step := 82;
525 IF g_debug THEN
526 debug(l_proc_name,l_proc_step);
527 debug('Row IDs already cached') ;
528 END IF ;
529 END IF ; --IF g_row_ids.COUNT = 0 THEN
530
531 idx := g_row_ids.FIRST ;
532
533 l_proc_step := 85;
534 IF g_debug THEN
535 debug(l_proc_name,l_proc_step);
536 debug(' index :'||idx);
537 debug_enter('WHILE idx IS NOT NULL LOOP' ) ;
538 END IF ;
539
540 WHILE idx IS NOT NULL LOOP
541 l_proc_step := 90 + idx/10000 ;
542 IF g_debug THEN
543 debug(l_proc_name,l_proc_step) ;
544 debug('index:'||idx) ;
545 debug('count: '|| to_char(g_row_ids.COUNT) );
546 debug('g_row_ids(idx):'|| g_row_ids(idx) ) ;
547 END IF ;
548
549 OPEN csr_get_value ( p_user_column_id => p_user_column_id
550 ,p_user_row_id => g_row_ids(idx)
551 ,p_effective_date => p_effective_date
552 ,p_business_group_id => p_business_group_id
553 ,p_legislation_code => g_legislation_code ) ;
554
555 FETCH csr_get_value INTO l_value, l_user_column_instance_id, l_row_id
556 ,l_effective_start_date, l_effective_end_date ;
557
558 IF g_debug THEN
559 debug('l_value:'||l_value);
560 debug('l_user_column_instance_id:'||l_user_column_instance_id);
561 debug('l_row_id:'||l_row_id);
562 END IF ;
563
564 IF csr_get_value%NOTFOUND THEN
565 l_proc_step := 95;
566 IF g_debug THEN
567 debug(l_proc_name,l_proc_step);
568 debug('csr_get_value in NOT FOUND') ;
569 END IF;
570 -- commented out the following code as it
571 -- was exiting from the loop on the first row_id
572 -- which returned no value
573 -- details in BUG 4570501
574
575 -- CLOSE csr_get_value ;
576 -- EXIT ;
577 ELSE --if csr_get_value%NOTFOUND
578 -- if cursor returned a row that means the UDT
579 -- has a row value defined for Work Pattern Column
580 -- hence, increment the defined days count.
581 l_no_of_days_defined := l_no_of_days_defined + 1 ;
582 END IF;
583 CLOSE csr_get_value ;
584
585
586
587 IF NVL(l_value,0) > 0 THEN
588 l_no_of_working_days := l_no_of_working_days + 1 ;
589 END IF ;
590
591 idx := g_row_ids.NEXT(idx) ;
592 -- reset the value for next iteration
593 l_value :=NULL;
594
595
596 l_proc_step := 100 + idx/10000;
597 IF g_debug THEN
598 debug(l_proc_name,l_proc_step);
599 debug('index:'||idx);
600 debug('l_no_of_days_defined:'||l_no_of_days_defined);
601 debug('l_no_of_working_days:'||l_no_of_working_days);
602 END IF;
603
604 END LOOP ; --WHILE idx IS NOT NULL LOOP
605
606 l_proc_step := 102;
607 IF g_debug THEN
608 debug_exit('WHILE idx IS NOT NULL LOOP') ;
609 debug(l_proc_name,l_proc_step);
610 debug(' l_no_of_working_days_in_week :'||l_no_of_working_days_in_week);
611 debug(' l_no_of_working_days :'||l_no_of_working_days);
612 debug(' l_no_of_days_defined :'||l_no_of_days_defined);
613 END IF;
614
615 IF l_no_of_days_defined > 0 THEN
616 l_no_of_working_days_in_week := (l_no_of_working_days * 7 )/
617 l_no_of_days_defined ;
618 END IF;
619
620 p_total_days_defined := l_no_of_days_defined ;
621 p_total_working_days_defined := l_no_of_working_days ;
622
623 l_proc_step := 105;
624
625 IF g_debug THEN
626 debug(l_proc_name,l_proc_step);
627 debug(' p_total_days_defined :'||p_total_days_defined);
628 debug(' p_total_working_days_defined :'||p_total_working_days_defined);
629 debug(' l_no_of_working_days_in_week :'||l_no_of_working_days_in_week);
630 debug_exit(l_proc_name);
631 END IF;
632
633 RETURN l_no_of_working_days_in_week ;
634
635 END get_avg_working_days_in_week ;
636
637 -- Procedure to modularize the process of update/insert of row.
638 -- the Procedure checks -
639
640 -- If the value for the said column already exists and
641 -- user has passed the WP name explicitly (without wild characters)
642 -- then update the row (not done yet)
643 -- else if the user has not updated the row manually, then
644 -- update the row, else leave it as it is.
645
646 -- but if the value does not exists for the column,
647 -- insert a new row in the table pay_user_column_instances_f.
648
649 PROCEDURE update_insert_row(
650 p_user_column_id IN NUMBER
651 ,p_user_row_id IN NUMBER
652 ,p_effective_date IN DATE
653 ,p_row_effective_start_date IN DATE
654 ,p_row_effective_end_date IN DATE
655 ,p_business_group_id IN NUMBER
656 ,p_value_to_update IN NUMBER
657 ,p_overwrite_if_exists IN VARCHAR2
658 )IS
659
660 l_value pay_user_column_instances.value%TYPE ;
661 l_row_id VARCHAR2(200) ;
662 l_column_instances_id pay_user_column_instances_f.
663 user_column_instance_id%TYPE ;
664 l_return_row_id VARCHAR2(200) ;
665 l_proc_step NUMBER(20,10);
666 l_proc_name VARCHAR2(200):=g_package_name||'update_insert_row';
667 l_effective_start_date DATE ;
668 l_effective_end_date DATE ;
669
670 BEGIN
671
672 g_debug := hr_utility.debug_enabled;
673 IF g_debug THEN
674 debug_enter(l_proc_name);
675 debug('p_user_column_id:'||p_user_column_id);
676 debug('p_user_row_id:'||p_user_row_id);
677 debug('p_effective_date:'||p_effective_date);
678 debug('p_row_effective_start_date:'||p_row_effective_start_date);
679 debug('p_row_effective_end_date:'||p_row_effective_end_date);
680 debug('p_business_group_id:'||p_business_group_id);
681 debug('p_value_to_update:'||p_value_to_update);
682 END IF ;
683
684 l_proc_step := 115;
685 IF g_debug THEN
686 debug(l_proc_name,l_proc_step);
687 END IF ;
688
689 OPEN csr_get_value ( p_user_column_id => p_user_column_id
690 ,p_user_row_id => p_user_row_id
691 ,p_effective_date => p_effective_date
692 ,p_business_group_id => p_business_group_id
693 ,p_legislation_code => g_legislation_code ) ;
694
695 FETCH csr_get_value INTO l_value, l_column_instances_id, l_row_id
696 ,l_effective_start_date,l_effective_end_date ;
697
698 l_proc_step := 120;
699 IF g_debug THEN
700 debug(l_proc_name,l_proc_step);
701 debug('Value that is defined for the row:'||l_value);
702 debug('l_column_instances_id:'||l_column_instances_id);
703 debug('l_row_id:'||l_row_id);
704 END IF ;
705
706
707 IF csr_get_value%NOTFOUND THEN -- No rows defined ,
708
709 l_proc_step := 125;
710 IF g_debug THEN
711 debug(l_proc_name,l_proc_step) ;
712 debug('Row is not Defined') ;
713 END IF ;
714
715 --Insert new Row in the table pay_user_column_instances_f
716 pay_user_column_instances_pkg.insert_row(
717 p_rowid => l_return_row_id --IN OUT
718 ,p_user_column_instance_id => l_column_instances_id --IN OUT
719 ,p_effective_start_date => p_row_effective_start_date
720 ,p_effective_end_date => p_row_effective_end_date
721 ,p_user_row_id => p_user_row_id
722 ,p_user_column_id => p_user_column_id
723 ,p_business_group_id => p_business_group_id
724 ,p_legislation_code => NULL
725 ,p_legislation_subgroup => NULL
726 ,p_value => p_value_to_update ) ;
727
728 l_proc_step := 130;
729 IF g_debug THEN
730 debug(l_proc_name,l_proc_step) ;
731 debug('l_return_row_id:' || l_return_row_id);
732 debug('l_column_instances_id:' || l_column_instances_id);
733 END IF ;
734
735 ELSE -- IF csr_get_value%NOTFOUND THEN
736 -- update the existing rows, provided,
737 -- user has not overridden the data manually
738 l_proc_step := 135;
739 IF g_debug THEN
740 debug(l_proc_name,l_proc_step) ;
741 END IF ;
742
743
744 IF l_value is NULL OR p_overwrite_if_exists = 'Y' THEN
745 IF g_debug THEN
746 debug('Row is Defined But Value is null');
747 END IF ;
748
749
750 l_proc_step := 140;
751 IF g_debug THEN
752 debug(l_proc_name,l_proc_step) ;
753 debug(' Row is defined, p_value_to_updat:'||p_value_to_update);
754 END IF ;
755
756 -- Call method from pay_user_column_instances_pkg Package to updaet the row.
757 pay_user_column_instances_pkg.update_row(
758 p_rowid => l_row_id
759 ,p_user_column_instance_id => l_column_instances_id
760 ,p_effective_start_date => p_row_effective_start_date -- changed from l_effective_start_date for BUG :4078709
761 ,p_effective_end_date => p_row_effective_end_date -- changed from l_effective_end_date for BUG :4078709
762 ,p_user_row_id => p_user_row_id
763 ,p_user_column_id => p_user_column_id
764 ,p_business_group_id => p_business_group_id
765 ,p_legislation_code => NULL
766 ,p_legislation_subgroup => NULL
767 ,p_value => p_value_to_update );
768
769 l_proc_step := 145;
770 IF g_debug THEN
771 debug(l_proc_name,l_proc_step) ;
772 debug('l_return_row_id:' || l_return_row_id);
773 debug('l_column_instances_id:' || l_column_instances_id);
774 END IF ;
775 END IF; -- IF l_value is NULL THEN
776
777 END IF ; -- csr_get_value%NOTFOUND THEN
778
779 CLOSE csr_get_value ;
780 IF g_debug THEN
781 debug_exit(l_proc_name) ;
782 END IF ;
783
784 END update_insert_row ;
785
786 END pqp_update_work_pattern_table ;
[Home] [Help]