[Home] [Help]
PACKAGE BODY: APPS.BEN_TCS_STMT_PROCESS
Source
1 PACKAGE BODY BEN_TCS_STMT_PROCESS as
2 /* $Header: bentcssg.pkb 120.23.12020000.3 2013/02/05 06:48:16 sgnanama ship $ */
3 g_package VARCHAR2 (80) := 'BEN_TCS_STMT_PROCESS';
4 g_proc VARCHAR2 (80);
5 g_actn VARCHAR2 (2000);
6 g_run_type VARCHAR2(30);
7 g_validate VARCHAR2(1);
8 l_rep_rec g_rep_rec_tab ;
9
10 item_hrchy_values BEN_TCS_STMT_VALID_HRCHY.cat_item_hrchy_table ;
11 subcat_hrchy_values BEN_TCS_STMT_VALID_HRCHY.cat_subcat_hrchy_table ;
12
13 TYPE g_number_type IS VARRAY (200) OF NUMBER;
14 TYPE g_exceution_params_rec IS RECORD (
15 Number_Of_BGs NUMBER (15),
16 stmt_errors NUMBER (15),
17 persons_selected NUMBER (15), -- PER_SLCTD
18 persons_proc_succ NUMBER (15), -- PER_PROC_SUCC
19 persons_errored NUMBER (15), -- PER_ERR
20 business_group_id NUMBER (15),
21 benefit_action_id NUMBER (15),
22 start_date DATE,
23 end_date DATE,
24 start_time VARCHAR (90),
25 end_time VARCHAR (90)
26 );
27
28 g_exec_param_rec g_exceution_params_rec;
29
30 CURSOR suc_ppl ( v_benefit_action_id IN NUMBER) IS
31 SELECT
32 COUNT(UNIQUE(person_id)) cnt_ppl
33 FROM ben_tcs_report_details
34 WHERE benefit_action_id = v_benefit_action_id
35 AND stmt_created ='Y' ;
36
37 CURSOR err_ppl ( v_benefit_action_id IN NUMBER) IS
38 SELECT
39 COUNT(UNIQUE(person_id)) cnt_ppl
40 FROM ben_tcs_report_details
41 WHERE benefit_action_id = v_benefit_action_id
42 AND stmt_created ='E' ;
43
44 --
45 --
46 -- ============================================================================
47 -- <<WRITE>>
48 -- ============================================================================
49 --
50 PROCEDURE WRITE (p_string IN VARCHAR2)
51 IS
52 BEGIN
53 ben_batch_utils.WRITE (p_string);
54 END WRITE ;
55
56
57 --
58 --
59 -- ============================================================================
60 -- <<check_multiple_stmt>>
61 -- ============================================================================
62 --
63 FUNCTION check_multiple_stmt (
64 p_person_id IN NUMBER DEFAULT NULL,
65 p_period_start_date IN DATE,
66 p_period_end_date IN DATE,
67 p_stmt_id IN NUMBER
68 )RETURN BOOLEAN
69 IS
70
71 CURSOR c_person_stmt( p_person_id IN NUMBER DEFAULT NULL,
72 p_period_start_date IN DATE,
73 p_period_end_date IN DATE,
74 p_stmt_id IN NUMBER)
75 IS
76 SELECT per_perd.stmt_id
77 FROM ben_tcs_per_stmt_perd per_perd, ben_tcs_stmt_perd perd
78 WHERE per_perd.stmt_id <> p_stmt_id
79 AND per_perd.person_id = p_person_id
80 AND per_perd.end_date = p_period_end_date
81 AND perd.stmt_perd_id = per_perd.stmt_perd_id
82 AND perd.start_date = p_period_start_date ;
83
84 BEGIN
85 g_proc := 'check_multiple_stmt';
86 hr_utility.set_location('Entering '||g_proc,10);
87 g_actn := 'check_multiple_stmt: ' ||p_person_id||'statement id : '|| p_stmt_id
88 || 'period start date :' ||p_period_start_date || 'period end date : ' || p_period_end_date ;
89 WRITE (g_actn );
90 WRITE('In procedure :' || g_proc);
91
92 OPEN c_person_stmt (p_person_id , p_period_start_date ,p_period_end_date ,p_stmt_id);
93 IF c_person_stmt%FOUND THEN
94 hr_utility.set_location('return True From '||g_proc,10.1);
95 return TRUE;
96 ELSE
97 hr_utility.set_location('return False From '||g_proc,10.2);
98 return false;
99 END IF;
100
101 hr_utility.set_location('Leaving '||g_proc,11);
102 WRITE('Leaving check_multiple_stmt');
103 END check_multiple_stmt;
104
105 --
106 --
107 -- ============================================================================
108 -- <<get_name>>
109 -- ============================================================================
110 --
111
112 PROCEDURE get_name(
113 p_bg_id IN NUMBER ,
114 v_ee_id IN NUMBER DEFAULT NULL ,
115 v_period_end_date IN DATE,
116 p_bg_name OUT NOCOPY VARCHAR2 ,
117 p_ee_name OUT NOCOPY VARCHAR2 )
118 IS
119 BEGIN
120 g_proc := 'get_name';
121 WRITE('In procedure :' || g_proc);
122 hr_utility.set_location('Entering '||g_proc,20);
123
124 SELECT bg.name bg_name
125 INTO p_bg_name
126 FROM per_business_groups_perf bg
127 WHERE
128 bg.business_group_id = p_bg_id
129 AND v_period_end_date >= bg.date_from
130 AND ( bg.date_to IS NULL OR bg.date_to >= v_period_end_date);
131
132 IF (v_ee_id is not null ) THEN
133 SELECT eligy.name
134 INTO p_ee_name
135 FROM BEN_ELIGY_PRFL_F eligy
136 WHERE
137 eligy.ELIGY_PRFL_ID(+) = v_ee_id
138 AND v_period_end_date >= eligy.effective_start_date(+)
139 AND (eligy.effective_end_date IS NULL OR eligy.effective_end_date >= v_period_end_date) ;
140 END IF;
141
142 hr_utility.set_location('Leaving '||g_proc,21);
143 WRITE('Leaving get_name');
144 END get_name;
145
146 --
147 --
148 -- ============================================================================
149 -- <<get_emp_detail>>
150 -- ============================================================================
151 --
152
153 PROCEDURE get_emp_detail(
154 p_assignment_id IN NUMBER DEFAULT NULL ,
155 p_person_id IN NUMBER DEFAULT NULL ,
156 p_period_end_date IN DATE,
157 p_job OUT NOCOPY VARCHAR2 ,
158 p_emp_name OUT NOCOPY VARCHAR2,
159 p_emp_num OUT NOCOPY VARCHAR2,
160 p_bg OUT NOCOPY VARCHAR2 )
161 IS
162 BEGIN
163 g_proc := 'get_emp_detail';
164 WRITE('In procedure :' || g_proc);
165 hr_utility.set_location('Entering '||g_proc,30);
166
167 IF ( p_assignment_id IS NOT NULL) THEN
168 SELECT jobs.name
169 INTO p_job
170 FROM per_jobs_tl jobs , per_all_assignments_f assign,
171 ( SELECT assignment.assignment_id , max(assignment.effective_end_date)end_date
172 FROM per_all_assignments_f assignment
173 WHERE assignment.assignment_id = p_assignment_id GROUP BY assignment_id) b
174 WHERE
175 assign.effective_end_date = b.end_date
176 AND assign.assignment_id = b.assignment_id
177 AND assign.job_id=jobs.job_id(+)
178 AND jobs.language (+) = userenv('lang')
179 ORDER BY jobs.name;
180 END IF;
181
182 SELECT employee_number ,full_name ,bg.name
183 INTO p_emp_num , p_emp_name , p_bg
184 FROM per_all_people_f ppl ,per_business_groups_perf bg
185 WHERE
186 ppl.person_id = p_person_id
187 AND trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
188 AND bg.business_group_id = ppl.business_group_id
189 AND p_period_end_date >= bg.date_from
190 AND ( bg.date_to IS NULL OR bg.date_to >= p_period_end_date);
191
192 WRITE('Leaving get_emp_detail');
193 hr_utility.set_location('Leaving '||g_proc,31);
194 END get_emp_detail;
195
196 --
197 -- ============================================================================
198 -- <<print_cache>>
199 -- ============================================================================
200 --
201
202
203 PROCEDURE print_cache
204 IS
205 l_evaluated NUMBER (9) := 0;
206 l_successful NUMBER (9) := 0;
207 l_error NUMBER (9) := 0;
208 l_closed_le NUMBER (9) := 0;
209 l_open_le NUMBER (9) := 0;
210 l_previous NUMBER := -1;
211 BEGIN
212 g_proc := 'print_cache';
213 WRITE ('Time before printing cache '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
214 WRITE ('In Procedure' ||g_proc);
215 hr_utility.set_location('Entering '||g_proc,40);
216
217 FOR i IN 1 .. l_rep_rec.COUNT
218 LOOP
219 IF (l_rep_rec(i).P_TYPE = -1) THEN
220 INSERT INTO BEN_TCS_REPORT_DETAILS(
221 BENEFIT_ACTION_ID,
222 BUSINESS_GROUP_ID,
223 BUSINESS_GROUP_NAME,
224 ELIGY_ID,
225 ELIGY_PROF_NAME,
226 STMT_ID,
227 STMT_NAME,
228 SETUP_VALID,
229 TOTAL_PERSONS,
230 BEN_TCS_RPT_DET_ID)
231 VALUES
232 (
233 l_rep_rec(i).BENEFIT_ACTION_ID,
234 l_rep_rec(i).BUSINESS_GROUP_ID,
235 l_rep_rec(i).BUSINESS_GROUP_NAME,
236 l_rep_rec(i).ELIGY_ID,
237 l_rep_rec(i).ELIGY_PROF_NAME,
238 l_rep_rec(i).STMT_ID,
239 l_rep_rec(i).STMT_NAME,
240 l_rep_rec(i).SETUP_VALID,
241 l_rep_rec(i).TOTAL_PERSONS,
242 BEN_TCS_REPORT_DETAILS_S.NEXTVAL
243 );
244 ELSE
245 INSERT INTO BEN_TCS_REPORT_DETAILS(
246 BENEFIT_ACTION_ID,
247 BUSINESS_GROUP_ID ,
248 BUSINESS_GROUP_NAME,
249 ASSIGNMENT_NUMBER,
250 STMT_CREATED,
251 ASSIGNMENT_ID ,
252 PERSON_ID,
253 STMT_ID,
254 STMT_NAME,
255 FULL_NAME,
256 EMPLOYEE_NUMBER,
257 JOB_NAME,
258 BEN_TCS_RPT_DET_ID,
259 ERROR
260 )
261 VALUES
262 (
263 l_rep_rec(i).BENEFIT_ACTION_ID,
264 l_rep_rec(i).BUSINESS_GROUP_ID,
265 l_rep_rec(i).BUSINESS_GROUP_NAME,
266 l_rep_rec(i).ASSIGNMENT_NUMBER,
267 l_rep_rec(i).STMT_CREATED,
268 l_rep_rec(i).ASSIGNMENT_ID,
269 l_rep_rec(i).PERSON_ID,
270 l_rep_rec(i).STMT_ID,
271 l_rep_rec(i).STMT_NAME,
272 l_rep_rec(i).FULL_NAME,
273 l_rep_rec(i).EMPLOYEE_NUMBER,
274 l_rep_rec(i).JOB_NAME,
275 BEN_TCS_REPORT_DETAILS_S.NEXTVAL,
276 l_rep_rec(i).ERROR
277 );
278 END IF;
279 END LOOP;
280 hr_utility.set_location('Leaving '||g_proc,41);
281 WRITE('Leaving print_cache');
282 END print_cache;
283
284
285 --
286 -- ============================================================================
287 -- <<hrchy_set>>
288 -- ============================================================================
289 --
290 PROCEDURE hrchy_set(p_benefit_action_id IN NUMBER)
291 IS
292
293 TYPE num_table IS TABLE OF NUMBER
294 INDEX BY BINARY_INTEGER;
295
296
297 item_id_found NUMBER;
298 item_entry_found NUMBER;
299 processed_cat num_table;
300 row_in_cat num_table;
301 all_obj_cat num_table;
302 cat_id_found VARCHAR2(1);
303 cat_entry_found NUMBER;
304 l_stmt_id NUMBER;
305 l_perd_id NUMBER;
306 cat_cnt NUMBER:= 1;
307 row_cnt NUMBER :=1;
308 obj_cnt NUMBER := 1;
309 l_row_id NUMBER;
310 l_obj_id NUMBER;
311 cat_type VARCHAR2(30);
312
313 CURSOR c_get_item (v_item_id IN NUMBER , v_period_id IN NUMBER)
314 IS
315 SELECT DISTINCT item_id
316 FROM ben_tcs_per_item
317 WHERE item_id = v_item_id
318 AND stmt_perd_id = v_period_id ;
319
320 CURSOR c_stmt_period (v_stmt_id IN NUMBER , v_perd_id IN NUMBER )
321 IS
322 SELECT stmt_id ,stmt_perd_id
323 FROM ben_tcs_per_stmt_perd
324 WHERE stmt_id = v_stmt_id
325 AND stmt_perd_id = v_perd_id;
326 stmt_perd_rec c_stmt_period%ROWTYPE;
327
328 CURSOR c_per_stmt_period (v_perd_id IN NUMBER)
329 IS
330 SELECT stmt_perd_id
331 FROM ben_tcs_per_stmt_perd
332 WHERE stmt_perd_id = v_perd_id ;
333
334 CURSOR c_per_stmt (v_stmt_id IN NUMBER)
335 IS
336 SELECT stmt_id
337 FROM ben_tcs_per_stmt_perd
338 WHERE stmt_id = v_stmt_id ;
339
340 CURSOR c_item_hrchy_val(v_period_id IN NUMBER)
341 IS
342 SELECT row_in_cat_id , all_objects_in_cat_id
343 FROM ben_tcs_cat_item_hrchy
344 WHERE stmt_perd_id = v_period_id
345 AND lvl_num = 1;
346
347 item_hrchy_val_rec c_item_hrchy_val%ROWTYPE;
348
349 CURSOR c_cat_hrchy_val(v_period_id IN NUMBER)
350 IS
351 SELECT row_in_cat_id row_cat_id
352 FROM ben_tcs_cat_subcat_hrchy
353 WHERE stmt_perd_id = v_period_id
354 AND lvl_num = 1;
355
356 cat_hrchy_val_rec c_cat_hrchy_val%ROWTYPE;
357
358 CURSOR c_row_cat_id( row_id IN NUMBER , v_period_id IN NUMBER)
359 IS
360 SELECT distinct nvl(item.stmt_perd_id,cat.stmt_perd_id )row_cat_id
361 FROM ben_tcs_cat_item_hrchy item ,ben_tcs_cat_subcat_hrchy cat
362 WHERE (item.row_in_cat_id =row_id
363 AND item.lvl_num =1
364 AND item.stmt_perd_id <> v_period_id )
365 OR( cat.row_in_cat_id =row_id
366 AND cat.lvl_num =1 AND cat.stmt_perd_id <> v_period_id);
367
368
369 CURSOR c_obj_cat_id( obj_id IN NUMBER , v_period_id IN NUMBER)
370 IS
371 SELECT all_objects_in_cat_id
372 FROM ben_tcs_cat_item_hrchy item
373 WHERE item.all_objects_in_cat_id =obj_id
374 AND item.lvl_num =1
375 AND item.stmt_perd_id <> v_period_id;
376
377
378 BEGIN
379 g_proc := 'Hierarchy set - hrchy_set';
380 WRITE('In procedure :' || g_proc);
381 hr_utility.set_location('Entering '||g_proc,50);
382
383 OPEN suc_ppl(p_benefit_action_id ) ;
384 FETCH suc_ppl INTO g_exec_param_rec.persons_proc_succ ;
385 CLOSE suc_ppl;
386
387 OPEN err_ppl(p_benefit_action_id ) ;
388 FETCH err_ppl INTO g_exec_param_rec.persons_errored ;
389 CLOSE err_ppl;
390
391 SAVEPOINT HRCHY_GEN;
392 IF (g_run_type = 'GEN') THEN
393 IF ( g_exec_param_rec.persons_proc_succ > 0) THEN
394 processed_cat.DELETE;
395 hr_utility.set_location('Inside Hierarchy Generation ',51);
396 FOR i IN 1 .. l_rep_rec.COUNT
397 LOOP
398 l_stmt_id := null;
399 IF (l_rep_rec(i).P_TYPE = -1) THEN
400 OPEN c_stmt_period (l_rep_rec(i).STMT_ID, l_rep_rec(i).PERIOD_ID );
401 FETCH c_stmt_period
402 INTO stmt_perd_rec ;
403 IF (c_stmt_period%FOUND) THEN
404 IF (stmt_perd_rec.stmt_id IS NOT NULL ) THEN
405 -- WRITE('Updating the statement generated flag .Stmt Id :'|| stmt_perd_rec.stmt_id );
406 hr_utility.set_location('Updating the statement generated flag .Stmt Id : ',52);
407 UPDATE BEN_TCS_STMT
408 SET stmt_generated_flag = 'Y'
409 WHERE stmt_id = stmt_perd_rec.stmt_id ;
410 END IF;
411 IF (stmt_perd_rec.stmt_perd_id IS NOT NULL ) THEN
412 --WRITE('Updating the statement generated flag .Stmt Period Id :'|| stmt_perd_rec.stmt_perd_id );
413 hr_utility.set_location('Updating the statement generated flag .Stmt Period Id : ',53);
414 UPDATE BEN_TCS_STMT_PERD
415 SET stmt_generated_flag = 'STMTGEN'
416 WHERE stmt_perd_id = stmt_perd_rec.stmt_perd_id ;
417 END IF;
418 END IF;
419 CLOSE c_stmt_period ;
420
421 END IF;
422 END LOOP;
423 hr_utility.set_location('before deleting from cat_item_hrchy: ',54);
424 FOR i IN 1.. item_hrchy_values.COUNT
425 LOOP
426 OPEN c_get_item (item_hrchy_values(i).item_id, item_hrchy_values(i).perd_id );
427 FETCH c_get_item
428 INTO item_id_found ;
429 IF ( c_get_item%FOUND) THEN
430 DELETE
431 FROM ben_tcs_cat_item_hrchy
432 WHERE item_id = item_hrchy_values(i).item_id
433 AND stmt_perd_id = item_hrchy_values(i).perd_id ;
434 END IF;
435 CLOSE c_get_item ;
436 END LOOP;
437 hr_utility.set_location('before inserting into cat_item_hrchy: ',55);
438 FOR i IN 1.. item_hrchy_values.COUNT
439 LOOP
440 item_id_found := null;
441 OPEN c_get_item (item_hrchy_values(i).item_id, item_hrchy_values(i).perd_id );
442 FETCH c_get_item
443 INTO item_id_found ;
444 IF ( c_get_item%FOUND) THEN
445 INSERT INTO ben_tcs_cat_item_hrchy
446 (stmt_id, cat_id,
447 item_id,
448 lvl_num ,cntr_cd, row_in_cat_id , all_objects_in_cat_id, stmt_perd_id
449 )
450 VALUES (item_hrchy_values(i).stmt_id,
451 item_hrchy_values(i).cat_id,
452 item_hrchy_values(i).item_id,
453 item_hrchy_values(i).lvl_num,
454 item_hrchy_values(i).cntr_cd ,
455 item_hrchy_values(i).row_cat_id,
456 item_hrchy_values(i).all_objects_id,
457 item_hrchy_values(i).perd_id
458 );
459
460 processed_cat(cat_cnt) :=item_hrchy_values(i).cat_id;
461 cat_cnt := cat_cnt +1;
462
463 IF (item_hrchy_values(i).lvl_num = 1) THEN
464
465 hr_utility.set_location('Updating the statement generated flag .all_objects_in_cat_id:'|| item_hrchy_values(i).all_objects_id ,56);
466 UPDATE BEN_TCS_ALL_OBJECTS_IN_CAT
467 SET stmt_generated_flag = 'Y'
468 WHERE all_objects_in_cat_id = item_hrchy_values(i).all_objects_id;
469
470 hr_utility.set_location('Updating the statement generated flag .row_in_cat_id:'|| item_hrchy_values(i).row_cat_id,57);
471 UPDATE BEN_TCS_ROW_IN_CAT
472 SET stmt_generated = 'Y'
473 WHERE row_in_cat_id =item_hrchy_values(i).row_cat_id;
474 END IF;
475 END IF;
476 CLOSE c_get_item ;
477
478 END LOOP;
479 hr_utility.set_location('after inserting into cat_item_hrchy: ',58);
480 FOR i IN 1.. subcat_hrchy_values.COUNT
481 LOOP
482
483 SELECT cat_type_cd into cat_type
484 FROM BEN_TCS_CAT
485 WHERE CAT_ID = subcat_hrchy_values(i).subcat_id;
486 IF (cat_type = 'STKOPTEXT') THEN
487 processed_cat(cat_cnt) :=subcat_hrchy_values(i).subcat_id ;
488 cat_cnt := cat_cnt +1;
489 END IF;
490
491 END LOOP;
492 hr_utility.set_location('before deleting from cat_subcat_hrchy: ',59);
493 FOR i IN 1.. subcat_hrchy_values.COUNT
494 LOOP
495 cat_id_found := null;
496 FOR j IN 1.. processed_cat.COUNT
497 LOOP
498 IF ( processed_cat(j) = subcat_hrchy_values(i).subcat_id ) THEN
499 cat_id_found := 'Y';
500 EXIT;
501 END IF;
502 END LOOP;
503
504 IF( cat_id_found ='Y')THEN
505
506 DELETE
507 FROM ben_tcs_cat_subcat_hrchy
508 WHERE sub_cat_id =subcat_hrchy_values(i).subcat_id
509 AND stmt_perd_id = subcat_hrchy_values(i).perd_id ;
510 END IF;
511 END LOOP;
512 hr_utility.set_location('before inserting into cat_subcat_hrchy: ',60);
513 FOR i IN 1.. subcat_hrchy_values.COUNT
514 LOOP
515 cat_id_found := null;
516 cat_entry_found := null;
517 FOR j IN 1.. processed_cat.COUNT
518 LOOP
519 IF ( processed_cat(j) = subcat_hrchy_values(i).subcat_id ) THEN
520 cat_id_found := 'Y';
521 EXIT;
522 END IF;
523 END LOOP;
524
525 IF( cat_id_found ='Y')THEN
526 INSERT INTO ben_tcs_cat_subcat_hrchy
527 (stmt_id, cat_id,
528 sub_cat_id,
529 lvl_num,row_in_cat_id ,stmt_perd_id
530 )
531 VALUES (subcat_hrchy_values(i).stmt_id,
532 subcat_hrchy_values(i).cat_id,
533 subcat_hrchy_values(i).subcat_id,
534 subcat_hrchy_values(i).lvl_num,
535 subcat_hrchy_values(i).row_cat_id,
536 subcat_hrchy_values(i).perd_id );
537 IF ( subcat_hrchy_values(i).lvl_num =1) THEN
538 hr_utility.set_location('Updating the statement generated flag :sub_cat .row_in_cat_id:'|| subcat_hrchy_values(i).row_cat_id, 61);
539 UPDATE BEN_TCS_ROW_IN_CAT
540 SET stmt_generated = 'Y'
541 WHERE row_in_cat_id = subcat_hrchy_values(i).row_cat_id;
542 END IF;
543 END IF;
544 END LOOP;
545 END IF;
546 IF (g_validate = 'Y') THEN
547 ROLLBACK to HRCHY_GEN;
548 END IF;
549 END IF;
550 IF (g_run_type = 'PURGE') THEN
551 IF ( g_exec_param_rec.persons_proc_succ > 0) THEN
552 FOR i IN 1 .. l_rep_rec.COUNT
553 LOOP
554 l_perd_id := null;
555 l_stmt_id := null;
556 row_in_cat.DELETE ;
557 all_obj_cat.DELETE ;
558 row_cnt := 1;
559 obj_cnt := 1;
560 IF (l_rep_rec(i).P_TYPE = -1) THEN
561 OPEN c_per_stmt_period (l_rep_rec(i).PERIOD_ID );
562 FETCH c_per_stmt_period
563 INTO l_perd_id ;
564 IF (c_per_stmt_period%NOTFOUND) THEN
565 hr_utility.set_location('Statement Generated Flag reset for period' || l_rep_rec(i).PERIOD_ID ,62);
566 UPDATE BEN_TCS_STMT_PERD
567 SET stmt_generated_flag = 'STMTNGEN'
568 WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID ;
569
570 OPEN c_item_hrchy_val ( l_rep_rec(i).PERIOD_ID);
571 LOOP
572 FETCH c_item_hrchy_val
573 INTO item_hrchy_val_rec;
574 EXIT WHEN c_item_hrchy_val%NOTFOUND;
575 row_in_cat(row_cnt) :=item_hrchy_val_rec.row_in_cat_id;
576 row_cnt := row_cnt +1;
577 all_obj_cat(obj_cnt) := item_hrchy_val_rec.all_objects_in_cat_id;
578 obj_cnt := obj_cnt +1;
579 END LOOP;
580 CLOSE c_item_hrchy_val;
581
582 OPEN c_cat_hrchy_val ( l_rep_rec(i).PERIOD_ID);
583 LOOP
584 FETCH c_cat_hrchy_val
585 INTO cat_hrchy_val_rec;
586 EXIT WHEN c_cat_hrchy_val%NOTFOUND;
587 row_in_cat(row_cnt) :=cat_hrchy_val_rec.row_cat_id;
588 row_cnt := row_cnt +1;
589 END LOOP;
590 CLOSE c_cat_hrchy_val;
591
592 hr_utility.set_location('Deleting Item Hrchy for period' || l_rep_rec(i).PERIOD_ID ,63);
593 DELETE
594 FROM ben_tcs_cat_item_hrchy
595 WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID;
596
597 hr_utility.set_location('Deleting Subcat Hrchy for period' || l_rep_rec(i).PERIOD_ID ,64);
598 DELETE
599 FROM ben_tcs_cat_subcat_hrchy
600 WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID;
601
602 FOR j IN 1..row_in_cat.COUNT
603 LOOP
604 OPEN c_row_cat_id(row_in_cat(j) ,l_rep_rec(i).PERIOD_ID);
605 FETCH c_row_cat_id INTO l_row_id;
606 IF (c_row_cat_id % NOTFOUND) THEN
607 hr_utility.set_location('Stmt Generated flag reset for row' || row_in_cat(j),65);
608 UPDATE ben_tcs_row_in_cat
609 SET stmt_generated = null
610 WHERE row_in_cat_id = row_in_cat(j);
611 END IF;
612 CLOSE c_row_cat_id;
613 END LOOP;
614 FOR j IN 1..all_obj_cat.COUNT
615 LOOP
616 OPEN c_obj_cat_id(all_obj_cat(j),l_rep_rec(i).PERIOD_ID);
617 FETCH c_obj_cat_id INTO l_obj_id;
618 IF (c_obj_cat_id % NOTFOUND) THEN
619 hr_utility.set_location('Stmt Generated flag reset for obj' || all_obj_cat(j),66);
620 UPDATE ben_tcs_all_objects_in_cat
621 SET stmt_generated_flag = null
622 WHERE all_objects_in_cat_id = all_obj_cat(j);
623 END IF;
624 CLOSE c_obj_cat_id ;
625 END LOOP;
626
627 END IF;
628
629 CLOSE c_per_stmt_period ;
630 OPEN c_per_stmt(l_rep_rec(i).STMT_ID );
631 FETCH c_per_stmt
632 INTO l_stmt_id ;
633 IF (c_per_stmt%NOTFOUND) THEN
634 hr_utility.set_location('Stmt Generated flag reset for stmt' || l_rep_rec(i).STMT_ID,67);
635 UPDATE BEN_TCS_STMT
636 SET stmt_generated_flag = 'N'
637 WHERE stmt_id = l_rep_rec(i).STMT_ID;
638 END IF;
639 CLOSE c_per_stmt;
640 END IF;
641 END LOOP;
642 END IF;
643 IF (g_validate = 'Y') THEN
644 ROLLBACK to HRCHY_GEN;
645 END IF;
646 END IF;
647 hr_utility.set_location('Leaving '||g_proc,68);
648 WRITE('Leaving hrchy_set');
649 END hrchy_set;
650
651 PROCEDURE delete_hrchy
652 IS
653 BEGIN
654 DELETE FROM ben_tcs_cat_item_hrchy
655 WHERE cat_id = -999 and lvl_num = -1;
656
657 END delete_hrchy;
658
659 --
660 -- ============================================================================
661 -- <<End_process>>
662 -- ============================================================================
663 --
664 PROCEDURE end_process (
665 p_benefit_action_id IN NUMBER,
666 p_person_selected IN NUMBER,
667 p_business_group_id IN NUMBER DEFAULT NULL
668 )
669 IS
670 l_batch_proc_id NUMBER;
671 l_object_version_number NUMBER;
672
673 BEGIN
674 g_proc := 'end_process';
675 hr_utility.set_location('Leaving '||g_proc,69);
676 --
677 -- Get totals for unprocessed, processed successfully and errored
678 --
679
680 OPEN suc_ppl(p_benefit_action_id ) ;
681 FETCH suc_ppl INTO g_exec_param_rec.persons_proc_succ ;
682 CLOSE suc_ppl;
683
684 OPEN err_ppl(p_benefit_action_id ) ;
685 FETCH err_ppl INTO g_exec_param_rec.persons_errored ;
686 CLOSE err_ppl;
687 WRITE('benefit action Id : ' || p_benefit_action_id);
688
689 ben_batch_proc_info_api.create_batch_proc_info
690 (p_validate => FALSE
691 , p_batch_proc_id => l_batch_proc_id
692 , p_benefit_action_id => p_benefit_action_id
693 , p_strt_dt => TRUNC
694 (g_exec_param_rec.start_date
695 )
696 , p_end_dt => TRUNC (SYSDATE)
697 , p_strt_tm => TO_CHAR
698 (g_exec_param_rec.start_date
699 , 'HH24:MI:SS'
700 )
701 , p_end_tm => TO_CHAR (SYSDATE
702 , 'HH24:MI:SS'
703 )
704 , p_elpsd_tm => fnd_number.number_to_canonical
705 ((DBMS_UTILITY.get_time
706 - g_exec_param_rec.start_time
707 )
708 / 100
709 )
710
711 , p_per_slctd => p_person_selected
712 , p_per_proc => g_exec_param_rec.Number_Of_BGs
713 , p_per_unproc => g_exec_param_rec.stmt_errors
714 , p_per_proc_succ => g_exec_param_rec.persons_proc_succ
715 , p_per_err => g_exec_param_rec.persons_errored
716 , p_business_group_id => nvl(p_business_group_id,HR_GENERAL.GET_BUSINESS_GROUP_ID)
717 , p_object_version_number => l_object_version_number);
718
719 -- print_cache;
720 delete_hrchy;
721 COMMIT;
722
723 hr_utility.set_location('Leaving '||g_proc,70);
724 WRITE('Leaving end_process');
725 END end_process;
726
727
728
729
730 --
731 -- ============================================================================
732 -- <<purge_person_stmt>>
733 -- ============================================================================
734 --
735
736 PROCEDURE purge_person_stmt (
737 p_validate IN VARCHAR2,
738 p_person_id IN NUMBER DEFAULT NULL,
739 p_stmt_id IN NUMBER DEFAULT NULL,
740 p_stmt_perd_id IN NUMBER DEFAULT NULL,
741 p_person_action_id IN NUMBER DEFAULT NULL,
742 p_benefit_action_id IN NUMBER DEFAULT NULL ,
743 p_business_group IN NUMBER DEFAULT NULL,
744 p_period_end_date IN DATE DEFAULT NULL,
745 p_run_type IN VARCHAR2
746 )
747 IS
748 l_per_stmt_perd NUMBER;
749 l_per_item NUMBER;
750 p_job VARCHAR2(700);
751 p_emp_name VARCHAR2(240);
752 p_emp_num VARCHAR2(240) ;
753 p_bg VARCHAR2(240);
754 rep_count NUMBER;
755
756 CURSOR c_asg_stmt ( v_per_stmt_perd_id IN NUMBER)
757 IS
758 SELECT ASG_STMT_ID ,assignment_number, assignment_id
759 FROM ben_tcs_asg_stmt
760 WHERE per_stmt_perd_id = v_per_stmt_perd_id;
761
762 CURSOR c_per_item(v_asg_stmt_id IN NUMBER)
763 IS
764 SELECT per_item_id
765 FROM ben_tcs_per_item
766 WHERE ASG_STMT_ID = v_asg_stmt_id;
767
768 CURSOR c_per_stmt_perd
769 IS
770 SELECT per_stmt_perd_id
771 FROM ben_tcs_per_stmt_perd
772 WHERE
773 stmt_id = p_stmt_id
774 AND stmt_perd_id = p_stmt_perd_id
775 AND person_id = p_person_id ;
776
777 asg_rec c_asg_stmt%ROWTYPE;
778 BEGIN
779 g_proc := 'purge_person_stmt';
780 WRITE ('In Procedure' ||g_proc);
781 hr_utility.set_location('Entering '||g_proc,71);
782
783 WRITE ('===========purge statement for the person============');
784 WRITE ('||Person Id ' || p_person_id);
785 WRITE ('||Statement id ' || p_stmt_id);
786 WRITE ('||Period id ' || p_stmt_perd_id);
787 WRITE ('||Person Action id ' || p_person_action_id);
788 WRITE ('=======================================================');
789
790 SAVEPOINT Purge ;
791 --vkodedal delete duplicates records if any --9702694
792 OPEN c_per_stmt_perd;
793 LOOP
794
795 FETCH c_per_stmt_perd INTO l_per_stmt_perd;
796 EXIT WHEN c_per_stmt_perd %NOTFOUND;
797
798 hr_utility.set_location ( 'delete per_stmt_perd_id entry for per_stmt_perd_id :' || l_per_stmt_perd,72);
799
800 DELETE
801 FROM ben_tcs_per_stmt_perd
802 WHERE per_stmt_perd_id = l_per_stmt_perd;
803
804 OPEN c_asg_stmt( l_per_stmt_perd);
805 LOOP
806
807 FETCH c_asg_stmt
808 INTO asg_rec;
809 EXIT WHEN c_asg_stmt%NOTFOUND;
810
811 OPEN c_per_item (asg_rec.asg_stmt_id) ;
812 LOOP
813 FETCH c_per_item
814 INTO l_per_item ;
815 EXIT WHEN c_per_item%NOTFOUND;
816
817 hr_utility.set_location ( 'delete ben_tcs_per_item_value and ben_tcs_per_item entry for per_item_id :' || l_per_item,73);
818 DELETE FROM ben_tcs_per_item_value
819 WHERE per_item_id = l_per_item;
820
821 DELETE FROM ben_tcs_per_item
822 WHERE per_item_id = l_per_item;
823 END LOOP;
824
825 hr_utility.set_location ( 'delete ben_tcs_asg_stmt entry for ASG_STMT_ID :' || asg_rec.asg_stmt_id,74);
826 DELETE FROM
827 ben_tcs_asg_stmt
828 WHERE ASG_STMT_ID = asg_rec.asg_stmt_id;
829
830 IF (p_run_type = 'PURGE') THEN
831
832 get_emp_detail(
833 asg_rec.assignment_id ,
834 p_person_id ,
835 p_period_end_date,
836 p_job ,
837 p_emp_name ,
838 p_emp_num ,
839 p_bg ) ;
840
841 rep_count := l_rep_rec.COUNT +1;
842 l_rep_rec(rep_count ).p_TYPE := 0;
843 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=p_benefit_action_id ;
844 l_rep_rec(rep_count ).ASSIGNMENT_NUMBER :=asg_rec.assignment_number;
845 l_rep_rec(rep_count ).ASSIGNMENT_ID := asg_rec.assignment_id;
846 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := p_business_group;
847 l_rep_rec(rep_count ).PERSON_ID := p_person_id;
848 l_rep_rec(rep_count ).STMT_ID := p_stmt_id;
849 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg;
850 l_rep_rec(rep_count ).EMPLOYEE_NUMBER := p_emp_num;
851 l_rep_rec(rep_count ).FULL_NAME := p_emp_name;
852 l_rep_rec(rep_count ).JOB_NAME := substr(p_job, 1, 35);
853 l_rep_rec(rep_count ).STMT_CREATED := 'Y';
854
855 END IF;
856 CLOSE c_per_item;
857 END LOOP;
858 CLOSE c_asg_stmt;
859 END LOOP;
860 CLOSE c_per_stmt_perd;
861 IF (p_validate = 'Y')
862 THEN
863 g_actn := 'Running in rollback mode, person rolled back...';
864 WRITE (g_actn);
865
866 ROLLBACK TO Purge;
867 END IF;
868
869 WRITE('leaving purge ...');
870 hr_utility.set_location('Leaving '||g_proc,75);
871 EXCEPTION
872 WHEN no_data_found THEN
873 WRITE('No stmt yet generated');
874 WRITE ('Leaving purge_person_stmt');
875 END purge_person_stmt;
876
877 --
878 -- ============================================================================
879 -- <<stmt_generation>>
880 -- ============================================================================
881 --
882
883 PROCEDURE stmt_generation (
884 p_validate IN VARCHAR2,
885 p_person_id IN NUMBER DEFAULT NULL,
886 p_person_action_id IN NUMBER DEFAULT NULL,
887 p_stmt_id IN NUMBER DEFAULT NULL,
888 p_stmt_perd_id IN NUMBER DEFAULT NULL,
889 p_period_end_date IN DATE,
890 p_benefit_action_id IN NUMBER ,
891 p_business_group IN NUMBER DEFAULT NULL,
892 p_run_type IN VARCHAR2,
893 p_start_date IN DATE
894 )
895 IS
896
897 CURSOR c_assignment_selection ( v_person_id IN NUMBER,
898 v_period_start_date IN DATE,
899 v_period_end_date IN DATE)
900 IS
901 SELECT DISTINCT(assignment_id) , assignment_number FROM
902 per_all_assignments_f assign ,
903 PER_ASSIGNMENT_STATUS_TYPES status
904 WHERE assignment_type IN ('B','C','E')
905 AND assign.person_id = v_person_id
906 AND nvl(status.business_group_id,assign.business_group_id) = assign.business_group_id
907 AND status.active_flag = 'Y'
908 AND assign.ASSIGNMENT_STATUS_TYPE_ID = status.ASSIGNMENT_STATUS_TYPE_ID
909 AND status.per_system_status IN ('ACTIVE_ASSIGN' , 'ACTIVE_CWK')
910 AND assign.effective_end_date > v_period_start_date
911 AND assign.effective_start_date < v_period_end_date;
912
913 CURSOR c_stk_opts (v_person_id IN NUMBER ,
914 v_stmt_perd_id IN NUMBER,
915 v_period_end_date IN DATE,
916 v_business_group IN NUMBER,
917 p_emp_num IN VARCHAR2 )
918 IS
919 SELECT sum(current_shares_outstanding) total
920 FROM
921 ben_cwb_stock_optn_dtls
922 WHERE ( person_id = v_person_id
923 OR (business_group_id = v_business_group
924 AND employee_number = p_emp_num) )
925 AND ( grant_date BETWEEN ( SELECT START_DATE FROM ben_tcs_stmt_perd
926 WHERE stmt_perd_id = v_stmt_perd_id )
927 AND v_period_end_date );
928
929 stk_opts_rec c_stk_opts%ROWTYPE ;
930
931 l_assign c_assignment_selection%ROWTYPE;
932 TYPE item_ids is TABLE of NUMBER;
933 l_per_stmt_perd_id NUMBER;
934 l_asg_stmt_id NUMBER;
935 l_item_cnt NUMBER := 1;
936 l_source_cd VARCHAR2(30);
937 l_source_key NUMBER;
938 l_uom VARCHAR2(30);
939 l_comp_type_cd VARCHAR2(30);
940 l_nnmntry_uom VARCHAR(30);
941 l_status VARCHAR2(3);
942 l_result BEN_TCS_COMPENSATION.period_table;
943 l_count number := 0;
944 item_u_cnt NUMBER :=0;
945 item item_ids := null;
946 item_cnt NUMBER :=0;
947 gen Number :=0;
948 p_job VARCHAR2(700);
949 p_emp_name VARCHAR2(240);
950 p_emp_num VARCHAR2(240) ;
951 p_bg VARCHAR2(240);
952 msg VARCHAR2(240);
953 item_name VARCHAR2(240);
954 UMBRELLA_API_ERR Exception;
955 l_person_inc NUMBER := 0;
956 l_initial_count NUMBER := l_rep_rec.COUNT+1;
957 Rollback_Person VARCHAR(1) := 'N';
958 Record_Created VARCHAR(1) := 'N';
959 cntr_item VARCHAR(1) := 'N';
960 rep_count NUMBER ;
961
962 person_created_now VARCHAR(1) := 'N';
963
964 CURSOR c_item (v_stmt_id IN NUMBER) IS
965 SELECT item_id,cntr_cd
966 FROM ben_tcs_cat_item_hrchy
967 WHERE cat_id = -999
968 AND stmt_id = v_stmt_id;
969
970 item_id_process c_item%ROWTYPE;
971 BEGIN
972
973 g_proc := 'stmt_generation';
974 WRITE ('In Procedure' ||g_proc);
975 hr_utility.set_location('Entering '||g_proc,76);
976
977 WRITE ('===========statement generation for the person============');
978 WRITE ('||Person Id ' || p_person_id);
979 WRITE ('||Statement id ' || p_stmt_id);
980 WRITE ('||Period id ' || p_stmt_perd_id);
981 WRITE ('||Person Action id ' || p_person_action_id);
982 WRITE ('=======================================================');
983
984 SAVEPOINT generation;
985
986 hr_utility.set_location( 'calling purge ...',77 );
987 purge_person_stmt(
988 p_validate => p_validate ,
989 p_person_id => p_person_id,
990 p_stmt_id => p_stmt_id,
991 p_stmt_perd_id => p_stmt_perd_id,
992 p_person_action_id => p_person_action_id ,
993 p_benefit_action_id => p_benefit_action_id,
994 p_business_group =>p_business_group,
995 p_period_end_date => p_period_end_date,
996 p_run_type =>p_run_type);
997
998 hr_utility.set_location('Calling check_multiple_stmt ',78);
999 IF (check_multiple_stmt (
1000 p_person_id => p_person_id,
1001 p_stmt_id => p_stmt_id ,
1002 p_period_start_date => p_start_date,
1003 p_period_end_date => p_period_end_date) = true )
1004 THEN
1005 WRITE('Statement already exists for this period date');
1006 ELSE
1007 hr_utility.set_location('Before assignment cursor',79);
1008 OPEN c_assignment_selection(p_person_id,p_start_date,p_period_end_date);
1009 hr_utility.set_location('After assignment cursor',80 );
1010 LOOP
1011 person_created_now := 'N';
1012 Record_Created := 'N';
1013 cntr_item := 'N';
1014 FETCH c_assignment_selection
1015 INTO l_assign;
1016 EXIT WHEN c_assignment_selection%NOTFOUND;
1017 SAVEPOINT assign_details;
1018 hr_utility.set_location('Processing assignment_number id '|| l_assign.assignment_id,81);
1019 gen := 0;
1020 OPEN c_item (p_stmt_id ) ;
1021 LOOP
1022 FETCH c_item INTO item_id_process ;
1023 EXIT WHEN c_item%NOTFOUND;
1024 IF ( item_id_process.item_id <> -1 ) THEN
1025 hr_utility.set_location('Processing item_id ' ||item_id_process.item_id,82);
1026 SELECT source_cd , source_key ,uom ,nnmntry_uom,comp_type_cd,name
1027 INTO l_source_cd , l_source_key ,l_uom ,l_nnmntry_uom,l_comp_type_cd ,item_name
1028 FROM ben_tcs_item
1029 WHERE item_id = item_id_process.item_id;
1030
1031 hr_utility.set_location('Before BEN_TCS_COMPENSATION.get_value_for_item call',83);
1032 hr_utility.set_location('Assignment Id'|| l_assign.assignment_id ,84) ;
1033 hr_utility.set_location('Person Id' || p_person_id,85);
1034 -- hr_utility.trace_on(null,'tcs');
1035 BEN_TCS_COMPENSATION.get_value_for_item(p_source_cd => l_source_cd,
1036 p_source_key => l_source_key,
1037 p_perd_st_dt => p_start_date,
1038 p_perd_en_dt => p_period_end_date,
1039 p_person_id => p_person_id,
1040 p_assignment_id => l_assign.assignment_id ,
1041 p_comp_typ_cd => l_comp_type_cd,
1042 p_currency_cd => l_uom ,
1043 p_uom => l_nnmntry_uom ,
1044 p_effective_date => p_period_end_date ,
1045 p_result => l_result,
1046 p_status => l_status);
1047
1048 hr_utility.set_location('After BEN_TCS_COMPENSATION.get_value_for_item call',86);
1049 -- hr_utility.trace_off;
1050 IF ( l_status = '0') THEN
1051 IF (l_result.count > 0) THEN
1052 IF (item_id_process.cntr_cd = 'ER' OR item_id_process.cntr_cd = 'EE' ) THEN
1053 cntr_item := 'Y';
1054 END IF;
1055 END IF;
1056
1057 SAVEPOINT insertion;
1058 FOR i IN 1..l_result.count
1059 LOOP
1060 IF (i =1 ) THEN
1061 IF l_count = 0 AND gen = 0 THEN
1062 hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
1063 p_person_id|| p_stmt_perd_id||p_stmt_id,87);
1064
1065 l_person_inc := 1;
1066 l_count := l_count + 1;
1067
1068 INSERT INTO ben_tcs_per_stmt_perd
1069 ( per_stmt_perd_id ,
1070 stmt_id ,
1071 stmt_perd_id,
1072 person_id,
1073 show_wlcm_pg_flag,
1074 end_date )
1075 VALUES
1076 ( ben_tcs_per_stmt_perd_s.NEXTVAL ,
1077 p_stmt_id,
1078 p_stmt_perd_id,
1079 p_person_id,
1080 null,
1081 p_period_end_date);
1082
1083 person_created_now := 'Y';
1084
1085 hr_utility.set_location('After inserting INTO ben_tcs_per_stmt_perd ',88);
1086
1087 END IF;
1088
1089 IF gen = 0 THEN
1090
1091 hr_utility.set_location('Inserting INTO ben_tcs_asg_stmt assignment_id '||l_assign.assignment_id,89);
1092 INSERT INTO ben_tcs_asg_stmt(
1093 asg_stmt_id ,
1094 stmt_id ,
1095 assignment_id,
1096 per_stmt_perd_id,
1097 assignment_number )
1098 VALUES
1099 ( ben_tcs_asg_stmt_s.NEXTVAL ,
1100 p_stmt_id,
1101 l_assign.assignment_id,
1102 ben_tcs_per_stmt_perd_s.CURRVAL,
1103 l_assign.assignment_number);
1104
1105 hr_utility.set_location('After inserting INTO ben_tcs_asg_stmt ',90);
1106 IF (Record_Created ='N') THEN
1107 get_emp_detail(
1108 l_assign.assignment_id ,
1109 p_person_id ,
1110 p_period_end_date,
1111 p_job ,
1112 p_emp_name ,
1113 p_emp_num ,
1114 p_bg ) ;
1115
1116 rep_count := l_rep_rec.COUNT +1;
1117 l_rep_rec(rep_count ).p_TYPE := 0;
1118 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=p_benefit_action_id ;
1119 l_rep_rec(rep_count ).ASSIGNMENT_NUMBER :=l_assign.assignment_number;
1120 l_rep_rec(rep_count ).ASSIGNMENT_ID := l_assign.assignment_id;
1121 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := p_business_group;
1122 l_rep_rec(rep_count ).PERSON_ID := p_person_id;
1123 l_rep_rec(rep_count ).STMT_ID := p_stmt_id;
1124 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg;
1125 l_rep_rec(rep_count ).EMPLOYEE_NUMBER := p_emp_num;
1126 l_rep_rec(rep_count ).FULL_NAME := p_emp_name;
1127 l_rep_rec(rep_count ).JOB_NAME := substr(p_job, 1, 35);
1128 l_rep_rec(rep_count ).STMT_CREATED := 'Y';
1129 Record_Created := 'Y';
1130
1131 END IF;
1132 gen := 1;
1133 END IF ;
1134
1135 l_uom := l_result(i).currency_cd;
1136 l_nnmntry_uom := l_result(i).uom;
1137
1138 hr_utility.set_location('Inserting INTO ben_tcs_per_item item_id '||item_id_process.item_id,91);
1139
1140 INSERT INTO ben_tcs_per_item
1141 ( per_item_id,
1142 asg_stmt_id ,
1143 stmt_id ,
1144 item_id,
1145 person_id,
1146 stmt_perd_id ,
1147 assignment_id,
1148 uom ,
1149 nnmntry_uom )
1150 VALUES
1151 ( ben_tcs_per_item_s.NEXTVAL ,
1152 ben_tcs_asg_stmt_s.CURRVAL ,
1153 p_stmt_id,
1154 item_id_process.item_id,
1155 p_person_id,
1156 p_stmt_perd_id,
1157 l_assign.assignment_id ,
1158 l_uom ,
1159 l_nnmntry_uom );
1160
1161 hr_utility.set_location(' After inserting INTO ben_tcs_per_item ',92);
1162 END IF;
1163 IF (l_source_cd = 'BB' OR l_source_cd = 'RULE' OR l_source_cd = 'PAYCOSTG' ) THEN
1164 hr_utility.set_location(' Inserting INTO ben_tcs_per_item_value ' ,92.1);
1165 IF (l_comp_type_cd = 'DATE') THEN
1166 IF( l_source_cd = 'PAYCOSTG' OR l_source_cd = 'BB' ) THEN
1167 WRITE('Data Type Mismatch for the item :' || item_name ||
1168 'Expected Type : ' || l_comp_type_cd|| ' Actual type :' || l_result(i).actual_uom);
1169 fnd_message.set_name('BEN','BEN_94667_TCS_CON_ERR_DATA_M');
1170 fnd_message.set_token('ITEM_NAME', item_name );
1171 msg := fnd_message.get ;
1172 l_rep_rec(rep_count ).STMT_CREATED := 'E';
1173 IF (l_rep_rec(rep_count ).ERROR IS NULL)THEN
1174 l_rep_rec(rep_count ).ERROR := msg;
1175 ELSE
1176 l_rep_rec(rep_count ).ERROR := l_rep_rec(rep_count ).ERROR||'; ' || msg;
1177 END IF;
1178 Rollback_Person := 'Y' ;
1179 EXIT ;
1180 ELSE
1181 BEGIN
1182 INSERT INTO ben_tcs_per_item_value
1183 ( per_item_value_id,
1184 per_item_id ,
1185 source_from_date ,
1186 source_to_date,
1187 seq_num,
1188 date_value,
1189 source_key ,
1190 source_cd,
1191 display_date)
1192 VALUES
1193 ( ben_tcs_per_item_value_s.NEXTVAL ,
1194 ben_tcs_per_item_s.CURRVAL ,
1195 l_result(i).start_date,
1196 l_result(i).end_date ,
1197 i,
1198 to_date(l_result(i).value,'yyyy/mm/dd'),
1199 l_result(i).output_key,
1200 l_source_cd ,l_result(i).start_date);
1201
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 WRITE('Data Type Mismatch for the item :' || item_name ||
1205 'Expected Type : ' || l_comp_type_cd|| ' Actual type :' || l_result(i).actual_uom);
1206 fnd_message.set_name('BEN','BEN_94667_TCS_CON_ERR_DATA_M');
1207 fnd_message.set_token('ITEM_NAME', item_name );
1208 msg := fnd_message.get ;
1209 l_rep_rec(rep_count ).STMT_CREATED := 'E';
1210 IF (l_rep_rec(rep_count ).ERROR IS NULL)THEN
1211 l_rep_rec(rep_count ).ERROR := msg;
1212 ELSE
1213 l_rep_rec(rep_count ).ERROR := l_rep_rec(rep_count ).ERROR||'; ' || msg;
1214 END IF;
1215 Rollback_Person := 'Y' ;
1216 EXIT ;
1217 END;
1218 END IF ;
1219 ELSIF (l_comp_type_cd = 'TEXT') THEN
1220
1221 INSERT INTO ben_tcs_per_item_value
1222 ( per_item_value_id,
1223 per_item_id ,
1224 source_from_date ,
1225 source_to_date,
1226 seq_num,
1227 text_value,
1228 source_key ,
1229 source_cd,
1230 display_date)
1231 VALUES
1232 ( ben_tcs_per_item_value_s.NEXTVAL ,
1233 ben_tcs_per_item_s.CURRVAL ,
1234 l_result(i).start_date,
1235 l_result(i).end_date ,
1236 i,
1237 l_result(i).value,
1238 l_result(i).output_key,
1239 l_source_cd ,
1240 l_result(i).start_date);
1241 ELSE
1242
1243
1244 BEGIN
1245 INSERT INTO ben_tcs_per_item_value
1246 ( per_item_value_id,
1247 per_item_id ,
1248 source_from_date ,
1249 source_to_date,
1250 seq_num,
1251 num_value,
1252 source_key ,
1253 source_cd,
1254 display_date)
1255 VALUES
1256 ( ben_tcs_per_item_value_s.NEXTVAL ,
1257 ben_tcs_per_item_s.CURRVAL ,
1258 l_result(i).start_date,
1259 l_result(i).end_date ,
1260 i,
1261 fnd_number.canonical_to_number(l_result(i).value),
1262 l_result(i).output_key,
1263 l_source_cd ,
1264 l_result(i).start_date);
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 WRITE('Data Type Mismatch for the item :' || item_name ||
1268 'Expected Type : ' || l_comp_type_cd|| ' Actual type :' || l_result(i).actual_uom);
1269 fnd_message.set_name('BEN','BEN_94667_TCS_CON_ERR_DATA_M');
1270 fnd_message.set_token('ITEM_NAME', item_name );
1271 msg := fnd_message.get ;
1272 l_rep_rec(rep_count ).STMT_CREATED := 'E';
1273 IF (l_rep_rec(rep_count ).ERROR IS NULL)THEN
1274 l_rep_rec(rep_count ).ERROR := msg;
1275 ELSE
1276 l_rep_rec(rep_count ).ERROR := l_rep_rec(rep_count ).ERROR||'; ' || msg;
1277 END IF;
1278 Rollback_Person := 'Y' ;
1279 EXIT ;
1280 END;
1281 END IF;
1282
1283 hr_utility.set_location(' After inserting INTO ben_tcs_per_item_value ',93 );
1284 --vkodedal 7012521 Run Result ER
1285 ELSIF (l_source_cd = 'THRDPTYPAY' OR l_source_cd = 'EE' or l_source_cd = 'RR') THEN
1286
1287 hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
1288 hr_utility.set_location('l_comp_type_cd'||l_comp_type_cd,95.1);
1289 hr_utility.set_location('l_result(i).value'||l_result(i).value,96.1);
1290 hr_utility.set_location('actual'||l_result(i).actual_uom,97.1);
1291 IF (l_comp_type_cd = 'DATE') THEN
1292 IF (l_result(i).actual_uom IS NOT NULL AND l_result(i).actual_uom <>'D') THEN
1293
1294 WRITE('Data Type Mismatch for the item :' || item_name ||
1295 'Expected Type : ' || l_comp_type_cd|| ' Actual type :' || l_result(i).actual_uom);
1296 fnd_message.set_name('BEN','BEN_94667_TCS_CON_ERR_DATA_M');
1297 fnd_message.set_token('ITEM_NAME', item_name );
1298 msg := fnd_message.get ;
1299 l_rep_rec(rep_count ).STMT_CREATED := 'E';
1300 IF (l_rep_rec(rep_count ).ERROR IS NULL)THEN
1301 l_rep_rec(rep_count ).ERROR := msg;
1302 ELSE
1303 l_rep_rec(rep_count ).ERROR := l_rep_rec(rep_count ).ERROR||'; ' || msg;
1304 END IF;
1305 Rollback_Person := 'Y' ;
1306 EXIT;
1307 ELSE
1308
1309 INSERT INTO ben_tcs_per_item_value
1310 ( per_item_value_id,
1311 per_item_id ,
1312 source_from_date ,
1313 source_to_date,
1314 seq_num,
1315 date_value,
1316 source_key ,
1317 source_cd,
1318 display_date)
1319 VALUES
1320 ( ben_tcs_per_item_value_s.NEXTVAL ,
1321 ben_tcs_per_item_s.CURRVAL ,
1322 l_result(i).start_date,
1323 l_result(i).end_date ,
1324 i,
1325 fnd_date.canonical_to_date(l_result(i).value),
1326 l_result(i).output_key,
1327 l_source_cd ,
1328 l_result(i).end_date);
1329 END IF ;
1330
1331 ELSIF (l_comp_type_cd = 'TEXT') THEN
1332
1333 INSERT INTO ben_tcs_per_item_value
1334 ( per_item_value_id,
1335 per_item_id ,
1336 source_from_date ,
1337 source_to_date,
1338 seq_num,
1339 text_value,
1340 source_key ,
1341 source_cd,
1342 display_date)
1343 VALUES
1344 ( ben_tcs_per_item_value_s.NEXTVAL ,
1345 ben_tcs_per_item_s.CURRVAL ,
1346 l_result(i).start_date,
1347 l_result(i).end_date ,
1348 i,
1349 l_result(i).value,
1350 l_result(i).output_key,
1351 l_source_cd ,
1352 l_result(i).end_date);
1353 ELSE
1354 IF (l_result(i).actual_uom IS NOT NULL AND
1355 (l_result(i).actual_uom NOT IN('I','M','N','ND','H_HH','H_DECIMAL1','H_DECIMAL2','H_DECIMAL3'))) THEN
1356
1357 WRITE('Data Type Mismatch for the item :' || item_name ||
1358 'Expected Type : ' || l_comp_type_cd|| ' Actual Unit :' || l_result(i).actual_uom);
1359
1360 fnd_message.set_name('BEN','BEN_94667_TCS_CON_ERR_DATA_M');
1361 fnd_message.set_token('ITEM_NAME', item_name );
1362 msg := fnd_message.get ;
1363 l_rep_rec(rep_count ).STMT_CREATED := 'E';
1364 IF (l_rep_rec(rep_count ).ERROR IS NULL)THEN
1365 l_rep_rec(rep_count ).ERROR := msg;
1366 ELSE
1367 l_rep_rec(rep_count ).ERROR := l_rep_rec(rep_count ).ERROR||'; ' || msg;
1368 END IF;
1369 Rollback_Person := 'Y' ;
1370 EXIT;
1371 ELSE
1372
1373 INSERT INTO ben_tcs_per_item_value
1374 ( per_item_value_id,
1375 per_item_id ,
1376 source_from_date ,
1377 source_to_date,
1378 seq_num,num_value,
1379 source_key ,
1380 source_cd,
1381 display_date)
1382 VALUES
1383 ( ben_tcs_per_item_value_s.NEXTVAL ,
1384 ben_tcs_per_item_s.CURRVAL ,
1385 l_result(i).start_date,
1386 l_result(i).end_date ,
1387 i,
1388 fnd_number.canonical_to_number(l_result(i).value),
1389 l_result(i).output_key,
1390 l_source_cd ,
1391 l_result(i).end_date);
1392 END IF;
1393 END IF ;
1394 END IF ;
1395 END LOOP;
1396 ELSE
1397 IF (Record_Created ='N') THEN
1398 get_emp_detail(
1399 l_assign.assignment_id ,
1400 p_person_id ,
1401 p_period_end_date,
1402 p_job ,
1403 p_emp_name ,
1404 p_emp_num ,
1405 p_bg ) ;
1406
1407 rep_count := l_rep_rec.COUNT +1;
1408 l_rep_rec(rep_count ).p_TYPE := 0;
1409 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=p_benefit_action_id ;
1410 l_rep_rec(rep_count ).ASSIGNMENT_NUMBER :=l_assign.assignment_number;
1411 l_rep_rec(rep_count ).ASSIGNMENT_ID := l_assign.assignment_id;
1412 l_rep_rec(rep_count ). BUSINESS_GROUP_ID := p_business_group;
1413 l_rep_rec(rep_count ).PERSON_ID := p_person_id;
1414 l_rep_rec(rep_count ).STMT_ID := p_stmt_id;
1415 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg;
1416 l_rep_rec(rep_count ).EMPLOYEE_NUMBER := p_emp_num;
1417 l_rep_rec(rep_count ).FULL_NAME := p_emp_name;
1418 l_rep_rec(rep_count ).JOB_NAME := substr(p_job, 1, 35);
1419 Record_Created := 'Y';
1420 END IF;
1421
1422 l_rep_rec(rep_count ).STMT_CREATED := 'E';
1423 IF( l_status = '1B' ) THEN
1424 fnd_message.set_name('BEN','BEN_94669_TCS_CON_ERR_SB_NF');
1425 ELSIF( l_status = '1C' ) THEN
1426 fnd_message.set_name('BEN','BEN_94670_TCS_CON_ERR_PAY_NF');
1427 ELSIF( l_status = '6' ) THEN
1428 msg := item_name || ' : ' || 'Invalid Source code ';
1429 ELSIF( l_status = '5' ) THEN
1430 fnd_message.set_name('BEN','BEN_94671_TCS_RULE_DT_FORMAT');
1431 END IF ;
1432 fnd_message.set_token('ITEM_NAME', item_name );
1433 msg := fnd_message.get ;
1434 IF (l_rep_rec(rep_count ).ERROR IS NULL)THEN
1435 l_rep_rec(rep_count ).ERROR := msg;
1436 ELSE
1437 l_rep_rec(rep_count ).ERROR := l_rep_rec(rep_count ).ERROR||'; ' || msg;
1438 END IF;
1439 Rollback_Person := 'Y' ;
1440 END IF;
1441 ELSE
1442 hr_utility.set_location('stock options extended subcategory',94);
1443 get_emp_detail(
1444 l_assign.assignment_id ,
1445 p_person_id ,
1446 p_period_end_date,
1447 p_job ,
1448 p_emp_name ,
1449 p_emp_num ,
1450 p_bg ) ;
1451 OPEN c_stk_opts (p_person_id , p_stmt_perd_id , p_period_end_date , p_business_group,p_emp_num );
1452 FETCH c_stk_opts INTO stk_opts_rec ;
1453 IF ( stk_opts_rec.total IS NOT NULL ) THEN
1454 hr_utility.set_location('Person has value for stock options extended subcategory',95);
1455 cntr_item := 'Y';
1456 IF l_count = 0 AND gen = 0 THEN
1457 hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
1458 p_person_id|| p_stmt_perd_id||p_stmt_id ||' subcategory part' ,96);
1459
1460 l_person_inc := 1;
1461 l_count := l_count + 1;
1462
1463 INSERT INTO ben_tcs_per_stmt_perd
1464 ( per_stmt_perd_id ,
1465 stmt_id ,
1466 stmt_perd_id,
1467 person_id,
1468 show_wlcm_pg_flag,
1469 end_date )
1470 VALUES
1471 ( ben_tcs_per_stmt_perd_s.NEXTVAL ,
1472 p_stmt_id,
1473 p_stmt_perd_id,
1474 p_person_id,
1475 null,
1476 p_period_end_date);
1477
1478 hr_utility.set_location('After inserting INTO ben_tcs_per_stmt_perd ',97);
1479 END IF;
1480
1481 IF gen = 0 THEN
1482
1483 hr_utility.set_location('inserting INTO ben_tcs_asg_stmt assignment_id '||l_assign.assignment_id||' subcategory',98);
1484 INSERT INTO ben_tcs_asg_stmt(
1485 asg_stmt_id ,
1486 stmt_id ,
1487 assignment_id,
1488 per_stmt_perd_id,
1489 assignment_number )
1490 VALUES
1491 ( ben_tcs_asg_stmt_s.NEXTVAL ,
1492 p_stmt_id,
1493 l_assign.assignment_id,
1494 ben_tcs_per_stmt_perd_s.CURRVAL,
1495 l_assign.assignment_number);
1496
1497 hr_utility.set_location('After inserting INTO ben_tcs_asg_stmt ',99);
1498 IF (Record_Created ='N') THEN
1499 rep_count := l_rep_rec.COUNT +1;
1500 l_rep_rec(rep_count ).p_TYPE := 0;
1501 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=p_benefit_action_id ;
1502 l_rep_rec(rep_count ).ASSIGNMENT_NUMBER :=l_assign.assignment_number;
1503 l_rep_rec(rep_count ).ASSIGNMENT_ID := l_assign.assignment_id;
1504 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := p_business_group;
1505 l_rep_rec(rep_count ).PERSON_ID := p_person_id;
1506 l_rep_rec(rep_count ).STMT_ID := p_stmt_id;
1507 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg;
1508 l_rep_rec(rep_count ).EMPLOYEE_NUMBER := p_emp_num;
1509 l_rep_rec(rep_count ).FULL_NAME := p_emp_name;
1510 l_rep_rec(rep_count ).JOB_NAME := substr(p_job, 1, 35);
1511 l_rep_rec(rep_count ).STMT_CREATED := 'Y';
1512 Record_Created := 'Y';
1513
1514 END IF;
1515 gen := 1;
1516 END IF ;
1517 END IF;
1518 CLOSE c_stk_opts ;
1519 END IF;
1520 END LOOP;
1521
1522 IF (Record_Created ='N') THEN
1523
1524 hr_utility.set_location(' no report',100);
1525 get_emp_detail(
1526 l_assign.assignment_id ,
1527 p_person_id ,
1528 p_period_end_date,
1529 p_job ,
1530 p_emp_name ,
1531 p_emp_num ,
1532 p_bg ) ;
1533
1534 rep_count := l_rep_rec.COUNT +1;
1535 l_rep_rec(rep_count ).p_TYPE := 0;
1536 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=p_benefit_action_id ;
1537 l_rep_rec(rep_count ).ASSIGNMENT_NUMBER :=l_assign.assignment_number;
1538 l_rep_rec(rep_count ).ASSIGNMENT_ID := l_assign.assignment_id;
1539 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := p_business_group;
1540 l_rep_rec(rep_count ).PERSON_ID := p_person_id;
1541 l_rep_rec(rep_count ).STMT_ID := p_stmt_id;
1542 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg;
1543 l_rep_rec(rep_count ).EMPLOYEE_NUMBER := p_emp_num;
1544 l_rep_rec(rep_count ).FULL_NAME := p_emp_name;
1545 l_rep_rec(rep_count ).JOB_NAME := substr(p_job, 1, 35);
1546 l_rep_rec(rep_count ).STMT_CREATED := 'N';
1547 ELSE
1548 IF ( cntr_item = 'N' AND l_rep_rec(rep_count ).STMT_CREATED = 'Y') THEN
1549 l_rep_rec(rep_count ).STMT_CREATED := 'N';
1550 WRITE('No Compensation For Employer Contribution and Employee Contribution');
1551 IF (person_created_now = 'Y') THEN
1552 l_count := 0;
1553 END IF;
1554 ROLLBACK TO assign_details ;
1555 END IF;
1556 END IF;
1557
1558 CLOSE c_item;
1559
1560 END LOOP;
1561 END IF ;
1562 CLOSE c_assignment_selection;
1563
1564
1565 IF (p_validate = 'Y')
1566 THEN
1567 g_actn := 'Running in rollback mode, person rolled back...';
1568 WRITE (g_actn);
1569 ROLLBACK TO generation;
1570 END IF;
1571
1572 IF ( Rollback_Person = 'Y') THEN
1573 ROLLBACK TO generation;
1574
1575 hr_utility.set_location('initial cnt' || l_initial_count,101);
1576 hr_utility.set_location(' cnt' || l_rep_rec.COUNT,102);
1577
1578 FOR i in l_initial_count .. l_rep_rec.COUNT
1579 LOOP
1580 IF ( l_rep_rec(i).STMT_CREATED = 'Y' OR l_rep_rec(i).STMT_CREATED = 'N' ) THEN
1581 l_rep_rec(i).STMT_CREATED := 'H';
1582 END IF;
1583 END LOOP;
1584
1585 WRITE('person rolled back due to error ');
1586 END If;
1587
1588 WRITE ('Leaving stmt_generation');
1589 --hr_utility.trace_off;
1590 hr_utility.set_location('Leaving '||g_proc,103);
1591
1592 EXCEPTION
1593
1594 WHEN OTHERS
1595 THEN
1596 -- hr_utility.trace_off;
1597 IF (Record_Created ='N') THEN
1598 get_emp_detail(
1599 l_assign.assignment_id ,
1600 p_person_id ,
1601 p_period_end_date,
1602 p_job ,
1603 p_emp_name ,
1604 p_emp_num ,
1605 p_bg ) ;
1606
1607 rep_count := l_rep_rec.COUNT +1;
1608 l_rep_rec(rep_count ).p_TYPE := 0;
1609 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=p_benefit_action_id ;
1610 l_rep_rec(rep_count ).ASSIGNMENT_NUMBER :=l_assign.assignment_number;
1611 l_rep_rec(rep_count ).ASSIGNMENT_ID := l_assign.assignment_id;
1612 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := p_business_group;
1613 l_rep_rec(rep_count ).PERSON_ID := p_person_id;
1614 l_rep_rec(rep_count ).STMT_ID := p_stmt_id;
1615 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg;
1616 l_rep_rec(rep_count ).EMPLOYEE_NUMBER := p_emp_num;
1617 l_rep_rec(rep_count ).FULL_NAME := p_emp_name;
1618 l_rep_rec(rep_count ).JOB_NAME := substr(p_job, 1, 35);
1619 Record_Created := 'Y';
1620 END IF;
1621 l_rep_rec(rep_count ).STMT_CREATED := 'E';
1622
1623 ROLLBACK TO generation;
1624
1625 hr_utility.set_location('initial cnt' || l_initial_count,104);
1626 hr_utility.set_location(' cnt' || l_rep_rec.COUNT,105);
1627
1628 FOR i IN l_initial_count .. l_rep_rec.COUNT
1629 LOOP
1630 IF ( l_rep_rec(i).STMT_CREATED = 'N' OR l_rep_rec(i).STMT_CREATED = 'Y' ) THEN
1631 l_rep_rec(i).STMT_CREATED := 'H';
1632 END IF;
1633 END LOOP;
1634
1635 WRITE('person rolled back due to some error in the generation ');
1636 WRITE (SQLERRM);
1637
1638 END stmt_generation;
1639
1640 --
1641 -- ============================================================================
1642 -- <<set_wlcm_flag>>
1643 -- ============================================================================
1644 --
1645
1646 PROCEDURE set_wlcm_flag (
1647 p_validate IN VARCHAR2,
1648 p_person_id IN NUMBER DEFAULT NULL,
1649 p_stmt_id IN NUMBER DEFAULT NULL,
1650 p_stmt_perd_id IN NUMBER DEFAULT NULL,
1651 p_person_action_id IN NUMBER DEFAULT NULL,
1652 p_benefit_action_id IN NUMBER DEFAULT NULL ,
1653 p_business_group IN NUMBER DEFAULT NULL,
1654 p_period_end_date IN DATE DEFAULT NULL,
1655 p_run_type IN VARCHAR2
1656 )
1657 IS
1658 wlcm_flag VARCHAR2(10) ;
1659 rep_count NUMBER;
1660 p_job VARCHAR2(700);
1661 p_emp_name VARCHAR2(240);
1662 p_emp_num VARCHAR2(240) ;
1663 p_bg VARCHAR2(240);
1664
1665 BEGIN
1666
1667 g_proc := 'set_wlcm_flag';
1668 hr_utility.set_location('Entering '||g_proc,106);
1669 g_actn := 'updating ben_tcs_per_stmt_perd' ;
1670
1671 WRITE ('In Procedure' ||g_proc);
1672 WRITE ('===========Reset welcome flag for the person============');
1673 WRITE ('||Person Id ' || p_person_id);
1674 WRITE ('||Statement id ' || p_stmt_id);
1675 WRITE ('||Period id ' || p_stmt_perd_id);
1676 WRITE ('||Person Action id ' || p_person_action_id);
1677 WRITE ('=======================================================');
1678
1679 SAVEPOINT setFlag;
1680
1681 hr_utility.set_location('statement id : '||p_stmt_id || ' statement period id :' ||p_stmt_perd_id
1682 || 'person id :' ||p_person_id,107);
1683
1684 SELECT show_wlcm_pg_flag
1685 INTO wlcm_flag
1686 FROM ben_tcs_per_stmt_perd
1687 WHERE stmt_id = p_stmt_id
1688 AND stmt_perd_id = p_stmt_perd_id
1689 AND person_id = p_person_id ;
1690
1691 IF (wlcm_flag = 'N') THEN
1692 hr_utility.set_location('Updating the welcome flag for person id : ' || p_person_id || ' : ' ||p_period_end_date ,108);
1693
1694 get_emp_detail(
1695 null ,
1696 p_person_id ,
1697 p_period_end_date,
1698 p_job ,
1699 p_emp_name ,
1700 p_emp_num ,
1701 p_bg ) ;
1702
1703 rep_count := l_rep_rec.COUNT +1;
1704 l_rep_rec(rep_count ).p_TYPE := 0;
1705 l_rep_rec(rep_count ).BENEFIT_ACTION_ID := p_benefit_action_id ;
1706 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := p_business_group;
1707 l_rep_rec(rep_count ).PERSON_ID := p_person_id;
1708 l_rep_rec(rep_count ).STMT_ID := p_stmt_id;
1709 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg;
1710 l_rep_rec(rep_count ).EMPLOYEE_NUMBER := p_emp_num;
1711 l_rep_rec(rep_count ).FULL_NAME := p_emp_name;
1712 l_rep_rec(rep_count ).STMT_CREATED := 'Y';
1713
1714 UPDATE ben_tcs_per_stmt_perd
1715 SET show_wlcm_pg_flag = null
1716 WHERE stmt_id = p_stmt_id
1717 AND stmt_perd_id = p_stmt_perd_id
1718 AND person_id = p_person_id ;
1719 WRITE ('Reopened Welcome Page');
1720 END IF;
1721
1722 IF (p_validate = 'Y')
1723 THEN
1724 g_actn := 'Running in rollback mode, person rolled back...';
1725 WRITE (g_actn);
1726 ROLLBACK TO setFlag;
1727 END IF;
1728
1729 WRITE ('Leaving set_wlcm_flag');
1730 hr_utility.set_location('Leaving '||g_proc,109);
1731 EXCEPTION
1732 WHEN OTHERS
1733 THEN
1734 l_rep_rec(rep_count ).STMT_CREATED := 'E';
1735 WRITE (SQLERRM);
1736
1737 END set_wlcm_flag;
1738
1739 -- ============================================================================
1740 -- << Procedure: Do_Multithread >>
1741 -- Description:
1742 -- this is a main procedure to invoke the Total Compensation Statement
1743 -- process.
1744 -- ============================================================================
1745 PROCEDURE do_multithread (
1746 errbuf OUT NOCOPY VARCHAR2,
1747 retcode OUT NOCOPY NUMBER,
1748 p_validate IN VARCHAR2 DEFAULT 'N',
1749 p_benefit_action_id IN NUMBER,
1750 p_thread_id IN NUMBER,
1751 p_effective_date IN VARCHAR2,
1752 p_audit_log IN VARCHAR2 DEFAULT 'N',
1753 p_run_type IN VARCHAR2,
1754 p_start_date IN DATE,
1755 p_end_date IN DATE
1756
1757 )
1758 IS
1759
1760 CURSOR c_range_for_thread (v_benefit_action_id IN NUMBER)
1761 IS
1762 SELECT ran.range_id, ran.starting_person_action_id,
1763 ran.ending_person_action_id
1764 FROM ben_batch_ranges ran
1765 WHERE ran.range_status_cd = 'U'
1766 AND ran.benefit_action_id = v_benefit_action_id
1767 AND ROWNUM < 2
1768 FOR UPDATE OF ran.range_status_cd;
1769
1770 CURSOR c_person_for_thread (
1771 v_benefit_action_id IN NUMBER,
1772 v_start_person_action_id IN NUMBER,
1773 v_end_person_action_id IN NUMBER
1774 )
1775 IS
1776 SELECT ben.person_id, ben.person_action_id, ben.object_version_number,
1777 ben.chunk_number ,ben.ler_id , ben.non_person_cd
1778 FROM ben_person_actions ben
1779 WHERE ben.benefit_action_id = v_benefit_action_id
1780 AND ben.action_status_cd <> 'P'
1781 AND ben.person_action_id BETWEEN v_start_person_action_id
1782 AND v_end_person_action_id
1783 ORDER BY ben.person_action_id;
1784
1785 CURSOR c_parameter (v_benefit_action_id IN NUMBER)
1786 IS
1787 SELECT ben.*
1788 FROM ben_benefit_actions ben
1789 WHERE ben.benefit_action_id = v_benefit_action_id;
1790
1791 l_parm c_parameter%ROWTYPE;
1792 l_commit NUMBER;
1793 l_range_id NUMBER;
1794 l_record_number NUMBER := 0;
1795 l_start_person_action_id NUMBER := 0;
1796 l_end_person_action_id NUMBER := 0;
1797 l_effective_date DATE;
1798 l_loop_cnt NUMBER :=1;
1799
1800 TYPE g_cache_person_process_object IS RECORD (
1801 person_id ben_person_actions.person_id%TYPE,
1802 person_action_id ben_person_actions.person_action_id%TYPE,
1803 object_version_number ben_person_actions.object_version_number%TYPE,
1804 perd_id ben_person_actions.ler_id%TYPE ,
1805 stmt_id ben_person_actions.chunk_number%TYPE,
1806 bg_id ben_person_actions.non_person_cd%TYPE
1807
1808
1809 );
1810
1811 TYPE g_cache_person_process_rec IS TABLE OF g_cache_person_process_object
1812 INDEX BY BINARY_INTEGER;
1813
1814 g_cache_person_process g_cache_person_process_rec;
1815
1816 BEGIN
1817
1818 g_actn := 'Started do_multithread for the thread ' || p_thread_id;
1819 g_proc := 'do_multithread';
1820 hr_utility.set_location('Entering '||g_proc,110);
1821
1822 benutils.g_benefit_action_id := p_benefit_action_id;
1823
1824 WRITE ('procedure :' ||g_proc );
1825 hr_utility.set_location (g_actn,111);
1826 WRITE ('=====================do_multithread=============');
1827 WRITE ('||Parameter Description ');
1828 WRITE ('||p_effective_dates - ' || p_effective_date);
1829 WRITE ('||p_validate - ' || p_validate);
1830 WRITE ('||p_benefit_action_id - ' || p_benefit_action_id);
1831 WRITE ('||p_thread_id - ' || p_thread_id);
1832 WRITE ('||p_audit_log - ' || p_audit_log);
1833 WRITE ('================================================');
1834
1835 l_effective_date :=
1836 TRUNC (TO_DATE (p_effective_date, 'yyyy/mm/dd'));
1837 hr_utility.set_location ('l_effective_date is ' || l_effective_date,112);
1838 g_actn := 'Put row in fnd_sessions...';
1839 hr_utility.set_location (g_actn,113);
1840 hr_utility.set_location ('dt_fndate.change_ses_date with ' || l_effective_date,114);
1841 dt_fndate.change_ses_date (p_ses_date => l_effective_date,
1842 p_commit => l_commit
1843 );
1844 -- need to check .
1845 IF (l_commit = 1)
1846 THEN
1847 WRITE ('The session date is committed...');
1848 COMMIT;
1849 END IF;
1850
1851 OPEN c_parameter (p_benefit_action_id);
1852 FETCH c_parameter
1853 INTO l_parm;
1854 CLOSE c_parameter;
1855
1856
1857 WRITE ( 'Time before processing the ranges '
1858 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
1859 );
1860
1861 LOOP
1862 OPEN c_range_for_thread (p_benefit_action_id);
1863 FETCH c_range_for_thread
1864 INTO l_range_id, l_start_person_action_id, l_end_person_action_id;
1865 EXIT WHEN c_range_for_thread%NOTFOUND;
1866 CLOSE c_range_for_thread;
1867
1868
1869 IF (l_range_id IS NOT NULL)
1870 THEN
1871 WRITE ( 'Range with range_id '
1872 || l_range_id
1873 || ' with Starting person action id '
1874 || l_start_person_action_id
1875 );
1876 WRITE ( ' and Ending Person Action id '
1877 || l_end_person_action_id
1878 || ' is selected'
1879 );
1880 g_actn :=
1881 'Marking ben_batch_ranges for range_id '
1882 || l_range_id
1883 || ' as processed...';
1884 WRITE (g_actn);
1885
1886 UPDATE ben_batch_ranges ran
1887 SET ran.range_status_cd = 'P'
1888 WHERE ran.range_id = l_range_id;
1889
1890 COMMIT;
1891 END IF;
1892
1893 --g_cache_person_process.DELETE;
1894 g_actn := 'Loading person data into g_cache_person_process cache...';
1895 hr_utility.set_location (g_actn,114);
1896 hr_utility.set_location ('Time' || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'),115);
1897
1898 OPEN c_person_for_thread (p_benefit_action_id,
1899 l_start_person_action_id,
1900 l_end_person_action_id
1901 );
1902 l_record_number := 0;
1903 LOOP
1904 FETCH c_person_for_thread
1905 INTO g_cache_person_process (l_record_number + 1).person_id,
1906 g_cache_person_process (l_record_number + 1).person_action_id,
1907 g_cache_person_process (l_record_number + 1).object_version_number,
1908 g_cache_person_process (l_record_number + 1).stmt_id,
1909 g_cache_person_process (l_record_number + 1).perd_id,
1910 g_cache_person_process (l_record_number + 1).bg_id;
1911 EXIT WHEN c_person_for_thread%NOTFOUND;
1912 l_record_number := l_record_number + 1;
1913 END LOOP;
1914 CLOSE c_person_for_thread;
1915
1916 WRITE ('Time ' || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
1917 WRITE ( 'Number of Persons selected in this range '
1918 || g_cache_person_process.COUNT
1919 );
1920 WRITE ('======Parameters required for processing this person ====');
1921 WRITE ('||l_parm.business_group_id ' || l_parm.business_group_id);
1922 WRITE ('||l_parm.debug_messages_flag '
1923 || l_parm.debug_messages_flag
1924 );
1925 WRITE ('||l_parm.bft_attribute1 ' || l_parm.bft_attribute1);
1926 WRITE ('=======================================================');
1927 WRITE ('Time ' || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
1928
1929 IF l_record_number > 0
1930 THEN
1931 FOR l_cnt IN 1 .. l_record_number
1932 LOOP
1933 BEGIN
1934 g_actn := 'Calling the process for the person id ...'||g_cache_person_process (l_cnt).person_id ;
1935 hr_utility.set_location(g_actn,116);
1936 IF ( p_run_type = 'GEN') THEN
1937 hr_utility.set_location (' calling stmt_generation..',117);
1938 stmt_generation (
1939 p_validate => p_validate,
1940 p_person_id => g_cache_person_process (l_cnt).person_id ,
1941 p_person_action_id => g_cache_person_process (l_cnt).person_action_id ,
1942 p_stmt_id => g_cache_person_process (l_cnt).stmt_id ,
1943 p_stmt_perd_id => g_cache_person_process (l_cnt).perd_id ,
1944 p_period_end_date => p_end_date ,
1945 p_benefit_action_id => p_benefit_action_id,
1946 p_business_group => g_cache_person_process (l_cnt).bg_id,
1947 p_run_type => p_run_type,
1948 p_start_date => p_start_date );
1949
1950 hr_utility.set_location('After the statement generation for the person id '|| g_cache_person_process (l_cnt).person_id,118);
1951
1952 ELSIF (p_run_type = 'WLCM_SET') THEN
1953 g_actn := 'calling set_wlcm_flag..';
1954 hr_utility.set_location(g_actn,119);
1955
1956 set_wlcm_flag ( p_validate => p_validate,
1957 p_person_id => g_cache_person_process (l_cnt).person_id ,
1958 p_stmt_id => g_cache_person_process (l_cnt).stmt_id ,
1959 p_stmt_perd_id => g_cache_person_process (l_cnt).perd_id,
1960 p_person_action_id => g_cache_person_process (l_cnt).person_action_id,
1961 p_benefit_action_id => p_benefit_action_id ,
1962 p_business_group => g_cache_person_process (l_cnt).bg_id ,
1963 p_period_end_date => p_end_date,
1964 p_run_type =>p_run_type
1965 ) ;
1966
1967 hr_utility.set_location('After the set welcome flag for the person id '|| g_cache_person_process (l_cnt).person_id,120);
1968
1969 ELSIF (p_run_type = 'PURGE') THEN
1970
1971 g_actn := 'calling purge_person_stmt..';
1972 hr_utility.set_location(g_actn,121);
1973
1974 purge_person_stmt(p_validate => p_validate,
1975 p_person_id => g_cache_person_process (l_cnt).person_id ,
1976 p_stmt_id => g_cache_person_process (l_cnt).stmt_id ,
1977 p_stmt_perd_id => g_cache_person_process (l_cnt).perd_id ,
1978 p_person_action_id => g_cache_person_process (l_cnt).person_action_id ,
1979 p_benefit_action_id => p_benefit_action_id ,
1980 p_business_group => g_cache_person_process (l_cnt).bg_id ,
1981 p_period_end_date => p_end_date,
1982 p_run_type =>p_run_type);
1983
1984 hr_utility.set_location('After the purge for the person id '|| g_cache_person_process (l_cnt).person_id,122);
1985 END IF;
1986 EXCEPTION
1987 WHEN OTHERS
1988 THEN
1989 WRITE ( SQLERRM || ' in multithread, caught in process_person call');
1990 END;
1991 END LOOP;
1992
1993 g_actn := 'Time after processing the ranges..';
1994 WRITE ( 'Time after processing the ranges '
1995 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
1996 ELSE
1997
1998 g_actn := 'Erroring out since no person is found in range...';
1999 fnd_message.set_name ('BEN', 'BEN_91709_PER_NOT_FND_IN_RNG');
2000 fnd_message.set_token ('PROCEDURE', g_proc);
2001 fnd_message.raise_error;
2002 END IF;
2003
2004 g_actn := 'before commit';
2005 WRITE (g_actn);
2006
2007 COMMIT;
2008
2009 g_actn := 'after commit';
2010 WRITE (g_actn);
2011
2012 END LOOP;
2013 print_cache;
2014 WRITE ('Leaving do_multithread');
2015 hr_utility.set_location('Leaving '||g_proc,130);
2016 EXCEPTION
2017 WHEN OTHERS
2018 THEN
2019 WRITE (SQLERRM);
2020 g_actn := g_actn || SQLERRM;
2021 print_cache;
2022 COMMIT;
2023 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2024 fnd_message.set_token ('PROCEDURE', g_proc);
2025 fnd_message.set_token ('STEP', g_actn);
2026 fnd_message.raise_error;
2027
2028 END do_multithread;
2029
2030 --
2031 -- ============================================================================
2032 -- <<insert_person_actions>>
2033 -- ============================================================================
2034 --
2035
2036 PROCEDURE insert_person_actions (
2037 p_per_actn_id_array IN g_number_type,
2038 p_per_id IN g_number_type,
2039 p_benefit_action_id IN NUMBER,
2040 p_perd_id IN g_number_type,
2041 p_stmt_id IN g_number_type,
2042 p_bg_id IN g_number_type
2043 )
2044 IS
2045 l_num_rows NUMBER := p_per_actn_id_array.COUNT;
2046 BEGIN
2047 g_proc := 'insert_person_actions';
2048 WRITE ('In Procedure' ||g_proc);
2049 hr_utility.set_location('Entering '||g_proc,131);
2050 WRITE ( 'Time before inserting person actions '
2051 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
2052
2053 FORALL l_count IN 1 .. p_per_actn_id_array.COUNT
2054
2055 INSERT INTO ben_person_actions
2056 (person_action_id,
2057 person_id,
2058 benefit_action_id,
2059 action_status_cd,
2060 chunk_number,
2061 LER_ID,
2062 non_person_cd ,
2063 object_version_number)
2064 VALUES (
2065 p_per_actn_id_array (l_count),
2066 p_per_id (l_count),
2067 p_benefit_action_id,
2068 'U',
2069 p_stmt_id (l_count),
2070 p_perd_id (l_count),
2071 p_bg_id (l_count),
2072 1);
2073
2074 WRITE ( 'Time before inserting ben batch ranges '
2075 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
2076
2077 INSERT INTO ben_batch_ranges
2078 (range_id,
2079 benefit_action_id,
2080 range_status_cd,
2081 starting_person_action_id,
2082 ending_person_action_id,
2083 object_version_number)
2084 VALUES (
2085 ben_batch_ranges_s.NEXTVAL,
2086 p_benefit_action_id,
2087 'U',
2088 p_per_actn_id_array (1),
2089 p_per_actn_id_array (l_num_rows),
2090 1);
2091
2092 WRITE ( 'Time at end of insert person actions '
2093 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
2094
2095 WRITE ('Leaving insert_person_actions');
2096 hr_utility.set_location('Leaving '||g_proc,132);
2097
2098 END insert_person_actions;
2099
2100 --
2101 -- ============================================================================
2102 -- <<process>>
2103 -- ============================================================================
2104 --
2105
2106 PROCEDURE process (
2107 errbuf OUT NOCOPY VARCHAR2,
2108 retcode OUT NOCOPY NUMBER,
2109 p_validate IN VARCHAR2 DEFAULT 'N',
2110 p_run_type IN VARCHAR2,
2111 p_stmt_name IN VARCHAR2,
2112 p_stmt_id IN VARCHAR2 ,
2113 p_person_id IN VARCHAR2 DEFAULT NULL,
2114 p_period_id IN VARCHAR2,
2115 p_partial_end IN VARCHAR2 DEFAULT NULL,
2116 p_audit_log IN VARCHAR2 DEFAULT 'Y',
2117 p_business_group_id IN NUMBER DEFAULT NULL,
2118 p_org_id IN NUMBER DEFAULT NULL,
2119 p_location_id IN NUMBER DEFAULT NULL,
2120 p_ben_grp_id IN NUMBER DEFAULT NULL,
2121 p_payroll_id IN NUMBER DEFAULT NULL,
2122 p_job_id IN NUMBER DEFAULT NULL,
2123 p_position_id IN NUMBER DEFAULT NULL,
2124 p_supervisor_id IN NUMBER DEFAULT NULL
2125 )
2126 IS
2127 --
2128 -- local variable declaration.
2129 --
2130 l_max_errors_allowed NUMBER (9) := 200;
2131 l_elig_return_status BOOLEAN;
2132 l_loop_cnt NUMBER := 1;
2133 l_count NUMBER := 0;
2134 l_chunk_size NUMBER;
2135 l_request_id NUMBER;
2136 l_threads NUMBER;
2137 l_benefit_action_id NUMBER;
2138 l_object_version_number NUMBER;
2139 l_business_group_id NUMBER := NULL;
2140 l_num_ranges NUMBER := 0;
2141 l_num_persons NUMBER := 0;
2142 l_silent_error EXCEPTION;
2143 l_num_rows NUMBER := 0;
2144 l_period_start_date DATE;
2145 l_period_end_date DATE;
2146 l_effective_date DATE := sysdate;
2147 temp_count NUMBER :=1 ;
2148 temp_ee_id NUMBER ;
2149 l_stmt_name VARCHAR2(240);
2150 l_actual_end_date DATE;
2151
2152
2153 CURSOR c_person_selection (
2154 v_stmt_id IN NUMBER,
2155 v_period_start_date IN DATE,
2156 v_period_end_date IN DATE,
2157 v_bg_id IN NUMBER,
2158 --vkodedal added args 14-sep-2007 ER
2159 v_ben_grp_id IN NUMBER,
2160 v_position_id IN NUMBER,
2161 v_job_id IN NUMBER,
2162 v_payroll_id IN NUMBER,
2163 v_location_id IN NUMBER,
2164 v_supervisor_id IN NUMBER,
2165 v_org_id IN NUMBER
2166 )
2167 IS
2168
2169 SELECT DISTINCT papf.person_id
2170 --- ,papf.full_name name 9702694
2171 FROM per_all_people_f papf,
2172 per_all_assignments_f paaf
2173 WHERE papf.business_group_id = v_bg_id
2174 AND EXISTS (
2175 SELECT 'x'
2176 FROM per_person_type_usages_f ptu, per_person_types ppt
2177 WHERE ptu.person_id = papf.person_id
2178 AND ptu.effective_start_date <= v_period_end_date --period end date
2179 AND ptu.effective_end_date >= v_period_start_date --period start date
2180 AND ptu.person_type_id = ppt.person_type_id
2181 AND ( ppt.system_person_type IN ('EMP', 'CWK')
2182 OR ( ppt.system_person_type = 'EX_EMP'
2183 AND EXISTS (
2184 SELECT NULL
2185 FROM per_periods_of_service pps
2186 WHERE papf.person_id = pps.person_id
2187 AND pps.date_start <= v_period_end_date --period end date
2188 AND pps.final_process_date >=
2189 v_period_start_date
2190 --period start date
2191 )
2192 )
2193 OR ( ppt.system_person_type = 'EX_CWK'
2194 AND EXISTS (
2195 SELECT NULL
2196 FROM per_periods_of_placement pps
2197 WHERE papf.person_id = pps.person_id
2198 AND pps.date_start <= v_period_end_date --period end date
2199 AND pps.final_process_date >=
2200 v_period_start_date
2201 --period start date
2202 )
2203 )
2204 ))
2205 AND paaf.person_id = papf.person_id
2206 AND paaf.primary_flag = 'Y'
2207 AND paaf.assignment_type IN ('E', 'C')
2208 AND paaf.effective_end_date >= v_period_start_date
2209 AND paaf.effective_start_date <= v_period_end_date
2210 AND papf.effective_start_date <= v_period_end_date
2211 AND papf.effective_end_date >= v_period_start_date
2212 AND paaf.effective_start_date <= trunc(sysdate) --Bug 9557823
2213 AND (v_ben_grp_id IS NULL OR papf.benefit_group_id=v_ben_grp_id)
2214 AND (v_position_id IS NULL OR paaf.position_id=v_position_id)
2215 AND (v_job_id IS NULL OR paaf.job_id=v_job_id)
2216 AND (v_payroll_id IS NULL OR paaf.payroll_id=v_payroll_id)
2217 AND (v_location_id IS NULL OR paaf.location_id=v_location_id)
2218 AND (v_supervisor_id IS NULL OR paaf.supervisor_id=v_supervisor_id)
2219 AND (v_org_id IS NULL OR paaf.organization_id=v_org_id);
2220
2221
2222 CURSOR c_stmt_id (v_stmt_id NUMBER,v_period_id NUMBER )
2223 IS
2224 SELECT stmt.stmt_id stmt_id, v_period_id period_id ,stmt.ee_profile_id ee_id,'Y' valid_flag,
2225 stmt.business_group_id bg_id
2226 FROM ben_tcs_stmt stmt
2227 WHERE stmt.stmt_id = v_stmt_id ;
2228
2229
2230 stmt_rec c_stmt_id%ROWTYPE;
2231
2232 CURSOR c_stmt_id_bg_id (v_stmt_name VARCHAR2, v_period_start_date IN DATE,v_period_end_date IN DATE)
2233 IS
2234 SELECT stmt.stmt_id stmt_id, period.stmt_perd_id period_id ,stmt.ee_profile_id ee_id,'Y' valid_flag,
2235 stmt.business_group_id bg_id
2236 FROM ben_tcs_stmt stmt , ben_tcs_stmt_perd period
2237 WHERE stmt.NAME = v_stmt_name
2238 AND period.start_date = v_period_start_date
2239 AND period.end_date = v_period_end_date
2240 AND period.stmt_id = stmt.stmt_id
2241 AND stmt.stat_cd = 'CO';
2242
2243 stmt_rec2 c_stmt_id_bg_id%ROWTYPE;
2244
2245 CURSOR c_stmt_id_bg_id1 (v_stmt_name VARCHAR2, v_period_start_date IN DATE,v_period_end_date IN DATE)
2246 IS
2247 SELECT stmt.stmt_id stmt_id, period.stmt_perd_id period_id ,stmt.business_group_id bg_id
2248 FROM ben_tcs_stmt stmt , ben_tcs_stmt_perd period
2249 WHERE stmt.NAME = v_stmt_name
2250 AND period.start_date = v_period_start_date
2251 AND period.end_date = v_period_end_date
2252 AND period.stmt_id = stmt.stmt_id;
2253
2254 stmt_rec1 c_stmt_id_bg_id1%ROWTYPE;
2255
2256 CURSOR c_person_valid_emp ( v_person_id IN NUMBER ,
2257 v_period_st_dt IN DATE,
2258 v_period_end_dt IN DATE
2259 )
2260 IS
2261 SELECT DISTINCT papf.person_id ----, papf.full_name NAME 9702694
2262 FROM per_all_people_f papf,
2263 per_all_assignments_f paaf
2264 WHERE papf.person_id = v_person_id
2265 AND EXISTS (
2266 SELECT 'x'
2267 FROM per_person_type_usages_f ptu, per_person_types ppt
2268 WHERE ptu.person_id = papf.person_id
2269 AND ptu.effective_start_date <= v_period_end_dt --period end date
2270 AND ptu.effective_end_date >= v_period_st_dt --period start date
2271 AND ptu.person_type_id = ppt.person_type_id
2272 AND ( ppt.system_person_type IN ('EMP', 'CWK')
2273 OR ( ppt.system_person_type = 'EX_EMP'
2274 AND EXISTS (
2275 SELECT NULL
2276 FROM per_periods_of_service pps
2277 WHERE papf.person_id = pps.person_id
2278 AND pps.date_start <= v_period_end_dt --period end date
2279 AND pps.final_process_date >=
2280 v_period_st_dt
2281 --period start date
2282 )
2283 )
2284 OR ( ppt.system_person_type = 'EX_CWK'
2285 AND EXISTS (
2286 SELECT NULL
2287 FROM per_periods_of_placement pps
2288 WHERE papf.person_id = pps.person_id
2289 AND pps.date_start <= v_period_end_dt --period end date
2290 AND pps.final_process_date >=
2291 v_period_st_dt
2292 --period start date
2293 )
2294 )
2295 ))
2296 AND paaf.person_id = papf.person_id
2297 AND paaf.primary_flag = 'Y'
2298 AND paaf.assignment_type IN ('E', 'C')
2299 AND paaf.effective_end_date >= v_period_st_dt
2300 AND paaf.effective_start_date <= v_period_end_dt
2301 AND papf.effective_start_date <= v_period_end_dt
2302 AND papf.effective_end_date >= v_period_st_dt
2303 AND paaf.effective_start_date <= trunc(sysdate); --Bug 9557823
2304
2305
2306
2307 TYPE stmt_record IS RECORD (
2308 statement_rec c_stmt_id_bg_id%ROWTYPE
2309 );
2310
2311 TYPE stmt_record_tab IS TABLE OF stmt_record
2312 INDEX BY BINARY_INTEGER;
2313 stmt_record_rec stmt_record_tab;
2314
2315 TYPE stmt_record1 IS RECORD (
2316 statement_rec1 c_stmt_id_bg_id1%ROWTYPE
2317 );
2318
2319 TYPE stmt_record_tab1 IS TABLE OF stmt_record1
2320 INDEX BY BINARY_INTEGER;
2321 stmt_record_rec1 stmt_record_tab1;
2322
2323 CURSOR c_bus_grp_id (v_stmt_id IN NUMBER)
2324 IS
2325 SELECT business_group_id
2326 FROM ben_tcs_stmt stmt
2327 WHERE stmt.stmt_id = v_stmt_id;
2328
2329 CURSOR c_check_stmt_person(v_person_id IN NUMBER ,v_stmt_id IN NUMBER ,v_perd_id IN NUMBER)
2330 IS
2331 SELECT stmt.person_id ,per.full_name name
2332 FROM BEN_TCS_PER_STMT_PERD stmt,per_all_people_f per
2333 WHERE stmt.stmt_id = v_stmt_id
2334 AND stmt.stmt_perd_id = v_perd_id
2335 AND stmt.person_id = v_person_id
2336 and per.person_id = stmt.person_id
2337 AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
2338
2339 stmt_per_rec c_check_stmt_person%ROWTYPE;
2340
2341 CURSOR c_check_stmt_avail(v_stmt_id IN NUMBER ,v_perd_id IN NUMBER)
2342 IS
2343 SELECT stmt.person_id --- , per.full_name name 9702694
2344 FROM BEN_TCS_PER_STMT_PERD stmt,per_all_people_f per
2345 WHERE stmt.stmt_id = v_stmt_id
2346 AND stmt.stmt_perd_id = v_perd_id
2347 AND per.person_id = stmt.person_id
2348 AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
2349
2350 CURSOR c_check_stmt_avail_1(v_stmt_id IN NUMBER ,v_perd_id IN NUMBER,v_person_id IN NUMBER)
2351 IS
2352 SELECT stmt.person_id --- , per.full_name name 9702694
2353 FROM BEN_TCS_PER_STMT_PERD stmt,per_all_people_f per
2354 WHERE stmt.stmt_id = v_stmt_id
2355 AND stmt.stmt_perd_id = v_perd_id
2356 AND per.person_id = stmt.person_id
2357 AND per.person_id = v_person_id
2358 AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
2359
2360 per_rec c_person_selection%ROWTYPE;
2361
2362 l_person_action_ids g_number_type := g_number_type();
2363 l_person_ids g_number_type := g_number_type();
2364 l_stmt_ids g_number_type := g_number_type();
2365 l_perd_ids g_number_type := g_number_type();
2366 l_bg_ids g_number_type := g_number_type();
2367 l_score_tab ben_evaluate_elig_profiles.scoretab;
2368 l_item_cnt NUMBER :=1;
2369 t_prof_tbl ben_evaluate_elig_profiles.proftab;
2370 l_status BOOLEAN;
2371 All_Bg VARCHAR(10) := 'N';
2372 l_person_temp NUMBER := 0 ;
2373 p_bg_name VARCHAR(240);
2374 p_ee_name VARCHAR(240);
2375 rep_count NUMBER;
2376 hrchy_cnt NUMBER;
2377 extend_cnt NUMBER;
2378
2379
2380
2381 BEGIN
2382 g_actn := 'Stating the Total Compensation Statement process : ' ;
2383 -- hr_utility.trace_on(null,'tcs');
2384 hr_utility.set_location('Entering '||g_actn,150);
2385
2386 IF (p_run_type = 'GEN') THEN
2387 g_actn := g_actn || ' Statement Generation.' ;
2388 ELSIF (p_run_type = 'PURGE') THEN
2389 g_actn := g_actn || ' Statement Purge.' ;
2390 ELSE
2391 g_actn := g_actn || ' Reopen welcome Page .' ;
2392 END IF ;
2393 WRITE (g_actn);
2394
2395 l_business_group_id := p_business_group_id;
2396 IF (l_business_group_id is null ) THEN
2397 All_Bg := 'Y';
2398 ELSE
2399 All_Bg := 'N';
2400 END IF;
2401
2402 g_proc := g_package || '.process';
2403
2404 SELECT start_date , end_date
2405 INTO l_period_start_date,
2406 l_actual_end_date
2407 FROM ben_tcs_stmt_perd
2408 WHERE stmt_perd_id = p_period_id ;
2409
2410 WRITE ('=====================process====================');
2411 WRITE ('||Parameter Description ');
2412 WRITE ('||Validate - ' || p_validate);
2413 WRITE ('||Run Type - ' || p_run_type);
2414 WRITE ('||statement Name - ' || p_stmt_name);
2415 WRITE ('||statement Id - ' || p_stmt_id);
2416 WRITE ('||Statement Period Id - ' || p_period_id);
2417 WRITE ('||Period Start Date - ' || l_period_start_date);
2418 WRITE ('||Period End Date - ' || l_actual_end_date);
2419 WRITE ('||Interim End Date - ' || p_partial_end);
2420 WRITE ('||Person Id - ' || p_person_id);
2421 WRITE ('||Organization Id - ' || p_org_id);
2422 WRITE ('||Location Id - ' || p_location_id);
2423 WRITE ('||Benefits Group Id - ' || p_ben_grp_id);
2424 WRITE ('||Payroll Id - ' || p_payroll_id);
2425 WRITE ('||Job Id - ' || p_job_id);
2426 WRITE ('||Position Id - ' || p_position_id);
2427 WRITE ('||Supervisor Id - ' || p_supervisor_id);
2428 WRITE ('||Audit Log - ' || p_audit_log);
2429 WRITE ('||Business Group Id - ' || p_business_group_id);
2430 WRITE ('||All Business Groups - ' || All_Bg );
2431 WRITE ('================================================');
2432
2433 IF ( p_partial_end IS NOT NULL) THEN
2434 l_period_end_date := to_date(p_partial_end,'DD/MM/YYYY') ;
2435 ELSE
2436 l_period_end_date := l_actual_end_date ;
2437 END IF;
2438
2439 g_run_type := p_run_type;
2440
2441 WRITE ('================== process Mode ====================');
2442
2443 IF (p_person_id IS NOT NULL) THEN
2444 g_actn := 'Processing single person ..';
2445 ELSIF(l_business_group_id IS NOT NULL) THEN
2446 g_actn := 'Processing single Business group ..';
2447 else
2448 g_actn := 'Processing all Business groups ..';
2449 END IF;
2450
2451 WRITE(g_actn);
2452 WRITE ('=====================================================');
2453
2454 g_actn := 'initializing the process parameters';
2455 WRITE (g_actn);
2456
2457 g_exec_param_rec.persons_selected := 0;
2458 g_exec_param_rec.stmt_errors := 0;
2459 g_exec_param_rec.persons_proc_succ := 0;
2460 g_exec_param_rec.persons_errored := 0;
2461 g_exec_param_rec.business_group_id := p_business_group_id;
2462 g_exec_param_rec.start_date := SYSDATE;
2463 g_exec_param_rec.start_time := DBMS_UTILITY.get_time;
2464 g_validate := p_validate ;
2465
2466 g_actn := 'Calling ben_batch_utils.ini...';
2467 hr_utility.set_location (g_actn,151);
2468 hr_utility.set_location ('ben_batch_utils.ini with PROC_INFO',152);
2469 ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
2470
2471 g_actn := 'Calling benutils.get_parameter...';
2472 WRITE (g_actn);
2473 hr_utility.set_location(g_actn,153);
2474 WRITE ( 'benutils.get_parameter with '
2475 || p_business_group_id
2476 || ' '
2477 || 'BENTCSSP'
2478 || ' '
2479 || l_max_errors_allowed
2480 );
2481
2482 benutils.get_parameter (p_business_group_id => nvl(l_business_group_id,HR_GENERAL.GET_BUSINESS_GROUP_ID),
2483 p_batch_exe_cd => 'BENTCSSP',
2484 p_threads => l_threads,
2485 p_chunk_size => l_chunk_size,
2486 p_max_errors => l_max_errors_allowed
2487 );
2488
2489 WRITE ( 'Values of l_threads is '
2490 || l_threads
2491 || ' and l_chunk_size is '
2492 || l_chunk_size
2493 );
2494
2495 benutils.g_thread_id := 99; -- need to investigate why this is needed
2496
2497 g_actn := 'Creating benefit actions...';
2498 WRITE (g_actn);
2499 WRITE ('Time' || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
2500 WRITE ('=====================Benefit Actions=======================');
2501 WRITE ('||Parameter value ');
2502 WRITE ('||p_request_id- ' || fnd_global.conc_request_id);
2503 WRITE ('||p_program_application_id- ' || fnd_global.prog_appl_id);
2504 WRITE ('||p_program_id- ' || fnd_global.conc_program_id);
2505 WRITE ('==========================================================');
2506
2507 ben_benefit_actions_api.create_perf_benefit_actions
2508 (p_benefit_action_id => l_benefit_action_id,
2509 p_process_date => l_effective_date,
2510 p_mode_cd => 'W',
2511 p_derivable_factors_flag => 'NONE',
2512 p_validate_flag => nvl(p_validate,'N'),
2513 p_debug_messages_flag => 'N' ,
2514 p_business_group_id => nvl(l_business_group_id,HR_GENERAL.GET_BUSINESS_GROUP_ID),
2515 p_no_programs_flag => 'N',
2516 p_no_plans_flag => 'N',
2517 p_audit_log_flag => nvl(p_audit_log,'N'),
2518 p_pgm_id => -100,
2519 p_person_id => p_person_id,
2520 p_object_version_number => l_object_version_number,
2521 p_effective_date => l_effective_date,
2522 p_request_id => fnd_global.conc_request_id,
2523 p_program_application_id => fnd_global.prog_appl_id,
2524 p_program_id => fnd_global.conc_program_id,
2525 p_program_update_date => SYSDATE,
2526 p_bft_attribute1 => p_run_type,
2527 p_uneai_effective_date => to_date(p_partial_end,'DD/MM/YYYY'),
2528 p_bft_attribute3 => p_stmt_name,
2529 p_bft_attribute4 => p_period_id,
2530 p_bft_attribute7 => All_Bg,
2531 p_per_sel_dt_from => l_period_start_date,
2532 p_per_sel_dt_to => l_actual_end_date
2533 );
2534
2535 WRITE('Benefit Action Id is ' || l_benefit_action_id);
2536 benutils.g_benefit_action_id := l_benefit_action_id;
2537 g_actn := 'Inserting Person Actions...';
2538 WRITE (g_actn);
2539 WRITE ( 'Time before processing the person selections '
2540 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2541 );
2542
2543 g_actn := 'Before Processing statement id and Eligibility Id .';
2544 WRITE (g_actn);
2545
2546 IF (p_run_type ='GEN') THEN
2547 IF ( p_person_id IS NOT NULL ) THEN
2548 OPEN c_stmt_id (p_stmt_id,
2549 p_period_id);
2550 FETCH c_stmt_id
2551 INTO stmt_rec;
2552 CLOSE c_stmt_id;
2553
2554 g_exec_param_rec.Number_Of_BGs :=1;
2555
2556 ELSIF (l_business_group_id IS NULL) THEN
2557 --IF( FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP')='Y' ) THEN
2558 SELECT name
2559 INTO l_stmt_name
2560 FROM BEN_TCS_STMT
2561 WHERE stmt_id = p_stmt_id
2562 AND name = p_stmt_name;
2563 IF ( l_stmt_name IS NULL ) THEN
2564 WRITE('The statement Name has been changed ');
2565 fnd_message.set_name ('BEN', 'BEN_TCS_STMT_NAME_CHG');
2566 fnd_message.raise_error;
2567 END IF;
2568 OPEN c_stmt_id_bg_id (p_stmt_name , l_period_start_date ,l_actual_end_date);
2569 LOOP
2570 FETCH c_stmt_id_bg_id
2571 INTO stmt_rec2;
2572 EXIT WHEN c_stmt_id_bg_id%NOTFOUND;
2573
2574 l_count := l_count + 1;
2575 stmt_record_rec (l_count).statement_rec := stmt_rec2;
2576 END LOOP;
2577
2578 hr_utility.set_location( 'Number of statement ids to be processed '||l_count,154);
2579 g_exec_param_rec.Number_Of_BGs :=l_count;
2580 CLOSE c_stmt_id_bg_id;
2581 /* ELSE
2582 l_business_group_id := HR_GENERAL.GET_BUSINESS_GROUP_ID ;
2583 g_exec_param_rec.Number_Of_BGs :=1;
2584 OPEN c_stmt_id (p_stmt_id, p_period_id);
2585 FETCH c_stmt_id
2586 INTO stmt_rec;
2587 CLOSE c_stmt_id;
2588 END IF;*/
2589
2590 ELSE
2591
2592 g_exec_param_rec.Number_Of_BGs :=1;
2593 OPEN c_stmt_id (p_stmt_id, p_period_id);
2594 FETCH c_stmt_id
2595 INTO stmt_rec;
2596 CLOSE c_stmt_id;
2597
2598 END IF;
2599 g_actn := 'After Processing statement id and Eligibility Id .';
2600 hr_utility.set_location( g_actn,155);
2601
2602 IF (l_business_group_id IS NOT NULL OR p_person_id IS NOT NULL ) THEN
2603 WRITE ('****************statement validation ************');
2604 g_actn := 'Before statement validation ...' ;
2605 hr_utility.set_location(g_actn,157);
2606 g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2607 hr_utility.set_location (g_actn,158);
2608 WRITE ( 'Time before validating the stataement '
2609 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2610 );
2611 hr_utility.set_location (g_actn,159);
2612
2613
2614 ben_tcs_stmt_valid_hrchy.stmt_gen_valid_process (
2615 stmt_rec.stmt_id,
2616 stmt_rec.bg_id ,
2617 p_period_id ,
2618 item_hrchy_values,
2619 subcat_hrchy_values,
2620 l_status) ;
2621
2622 g_actn := 'After statement validation ...' ;
2623 hr_utility.set_location (g_actn,160);
2624 WRITE ( 'Time after validating the stataement '
2625 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2626 );
2627 WRITE (g_actn);
2628
2629 IF (l_status = true )THEN
2630 WRITE ('valid statement setup...');
2631 stmt_rec.valid_flag := 'Y';
2632 ELSE
2633 g_exec_param_rec.stmt_errors := g_exec_param_rec.stmt_errors +1;
2634 stmt_rec.valid_flag := 'N';
2635 get_name(
2636 l_business_group_id ,
2637 stmt_rec.ee_id ,
2638 l_period_end_date ,
2639 p_bg_name ,
2640 p_ee_name);
2641
2642 rep_count := l_rep_rec.COUNT +1;
2643 l_rep_rec(rep_count ).p_TYPE := -1;
2644 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
2645 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := l_business_group_id;
2646 l_rep_rec(rep_count ).ELIGY_ID := stmt_rec.ee_id;
2647 l_rep_rec(rep_count ).STMT_ID := stmt_rec.stmt_id ;
2648 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
2649 l_rep_rec(rep_count ).ELIGY_PROF_NAME := p_ee_name;
2650 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
2651 l_rep_rec(rep_count ).SETUP_VALID := 'N';
2652
2653 WRITE ('invalid statement setup ...');
2654 fnd_message.set_name ('BEN', 'BEN_TCS_CON_INVALID_STMT');
2655 fnd_message.raise_error;
2656
2657 END IF;
2658 WRITE ('********************************************');
2659
2660
2661 ELSE
2662
2663 WRITE('Total number of statements to be processed : '||l_count);
2664 IF l_count = 0 THEN
2665 WRITE( 'No statement exist ' );
2666 fnd_message.set_name ('BEN', 'BEN_TCS_CON_NO_STMT');
2667 fnd_message.raise_error;
2668 END IF;
2669 WHILE (l_loop_cnt <= l_count)
2670 LOOP
2671 g_actn := 'Processing the statement :'|| stmt_record_rec (l_loop_cnt).statement_rec.stmt_id;
2672 hr_utility.set_location (g_actn,161);
2673 WRITE ('****************statement validation ************');
2674 g_actn := 'Before statement validation ...' ;
2675 hr_utility.set_location (g_actn,162);
2676 g_actn := 'Statement id to be validated :' || stmt_rec.stmt_id;
2677 WRITE (g_actn);
2678 WRITE ( 'Time before validating the stataement '
2679 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2680 );
2681
2682 ben_tcs_stmt_valid_hrchy.stmt_gen_valid_process(
2683 stmt_record_rec (l_loop_cnt).statement_rec.stmt_id,
2684 stmt_record_rec (l_loop_cnt).statement_rec.bg_id ,
2685 stmt_record_rec (l_loop_cnt).statement_rec.period_id ,
2686 item_hrchy_values, subcat_hrchy_values,
2687 l_status);
2688
2689 g_actn := 'After statement validation ...' ;
2690 hr_utility.set_location (g_actn,163);
2691 WRITE ( 'Time after validating the stataement '
2692 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2693 );
2694 WRITE (g_actn);
2695 IF (l_status = true) THEN
2696
2697 WRITE ('valid statement setup...');
2698 stmt_record_rec (l_loop_cnt).statement_rec.valid_flag := 'Y';
2699 IF (stmt_record_rec (l_loop_cnt).statement_rec.ee_id IS NOT NULL ) THEN
2700 SELECT ELIGY_PRFL_ID INTO stmt_record_rec (l_loop_cnt).statement_rec.ee_id
2701 FROM BEN_ELIGY_PRFL_F
2702 WHERE STAT_CD='A'
2703 AND ELIGY_PRFL_ID = stmt_record_rec (l_loop_cnt).statement_rec.ee_id
2704 AND l_period_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
2705
2706 END IF;
2707
2708
2709 ELSE
2710 BEGIN
2711 WRITE ('Invalid statement setup...');
2712
2713 g_exec_param_rec.stmt_errors := g_exec_param_rec.stmt_errors +1;
2714 stmt_record_rec (l_loop_cnt).statement_rec.valid_flag := 'N';
2715 get_name(stmt_record_rec (l_loop_cnt).statement_rec.bg_id ,
2716 stmt_record_rec (l_loop_cnt).statement_rec.ee_id ,
2717 l_period_end_date ,
2718 p_bg_name ,
2719 p_ee_name);
2720 rep_count := l_rep_rec.COUNT +1;
2721 l_rep_rec(rep_count ).p_TYPE := -1;
2722 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
2723 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := stmt_record_rec (l_loop_cnt).statement_rec.bg_id;
2724 l_rep_rec(rep_count ).ELIGY_ID :=stmt_record_rec (l_loop_cnt).statement_rec.ee_id ;
2725 l_rep_rec(rep_count ).STMT_ID := stmt_record_rec (l_loop_cnt).statement_rec.stmt_id ;
2726 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
2727 l_rep_rec(rep_count ).ELIGY_PROF_NAME := p_ee_name;
2728 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
2729 l_rep_rec(rep_count ).SETUP_VALID := 'N';
2730
2731 EXCEPTION
2732 WHEN others THEN
2733 WRITE ( 'The statement id:'|| stmt_record_rec (l_loop_cnt).statement_rec.stmt_id
2734 ||':'|| stmt_record_rec (l_loop_cnt).statement_rec.bg_id || 'is not valid');
2735 END;
2736 END IF;
2737 WRITE ('********************************************');
2738 l_loop_cnt := l_loop_cnt + 1;
2739 END LOOP;
2740 END IF;
2741
2742 WRITE ('****************processing the person ************');
2743 g_actn := 'before processing person ids..' ;
2744 hr_utility.set_location (g_actn,164);
2745 WRITE ( 'Time before processing person ids .. '
2746 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
2747 );
2748 WRITE (g_actn);
2749
2750 IF ( p_person_id IS NOT NULL AND stmt_rec.valid_flag = 'Y') THEN
2751 OPEN c_person_valid_emp ( p_person_id ,l_period_start_date,l_period_end_date);
2752 hr_utility.set_location( 'processing the person id ' ||p_person_id,165);
2753 FETCH c_person_valid_emp into per_rec;
2754 IF c_person_valid_emp%NOTFOUND THEN
2755
2756 get_name(l_business_group_id ,
2757 stmt_rec.ee_id ,
2758 l_period_end_date ,
2759 p_bg_name ,
2760 p_ee_name);
2761
2762 rep_count := l_rep_rec.COUNT +1;
2763 l_rep_rec(rep_count ).p_TYPE := -1;
2764 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
2765 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := l_business_group_id;
2766 l_rep_rec(rep_count ).ELIGY_ID :=temp_ee_id ;
2767 l_rep_rec(rep_count ).STMT_ID := stmt_rec.stmt_id;
2768 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
2769 l_rep_rec(rep_count ).ELIGY_PROF_NAME := p_ee_name;
2770 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
2771 l_rep_rec(rep_count ).SETUP_VALID := 'Y';
2772 l_rep_rec(rep_count ).TOTAL_PERSONS := 0 ;
2773
2774 hr_utility.set_location('The person id :'||p_person_id || 'is not valid for this period' ||stmt_rec.stmt_id,165);
2775 fnd_message.set_name ('BEN', 'BEN_TCS_CON_NO_VALID_PERSON');
2776 fnd_message.raise_error;
2777
2778 ELSE
2779 t_prof_tbl (1).mndtry_flag := 'N';
2780 t_prof_tbl (1).compute_score_flag := 'N';
2781 t_prof_tbl (1).trk_scr_for_inelg_flag := 'N';
2782
2783 IF ( stmt_rec.ee_id IS NOT NULL ) THEN
2784 hr_utility.set_location('checking the eligibity of the person_id id :'|| p_person_id ||'.Eligibility profile id is
2785 '||stmt_rec.ee_id ,166 );
2786
2787 SELECT ELIGY_PRFL_ID INTO temp_ee_id
2788 FROM BEN_ELIGY_PRFL_F
2789 WHERE STAT_CD='A'
2790 AND ELIGY_PRFL_ID = stmt_rec.ee_id
2791 AND l_period_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
2792
2793 t_prof_tbl (1).eligy_prfl_id := stmt_rec.ee_id ;
2794 hr_utility.set_location( 'valid eligy id'|| temp_ee_id ,166) ;
2795 BEGIN
2796 IF (temp_ee_id is not null ) THEN
2797 hr_utility.set_location('before calling ben_evaluate_elig_profiles.eligible...',167);
2798
2799 ben_env_object.init(
2800 p_business_group_id=>l_business_group_id,
2801 p_effective_date =>l_period_end_date,
2802 p_thread_id => 99 ,
2803 p_chunk_size => l_chunk_size ,
2804 p_threads => l_threads ,
2805 p_max_errors => l_max_errors_allowed,
2806 p_benefit_action_id => l_benefit_action_id
2807 );
2808
2809 l_elig_return_status :=
2810 ben_evaluate_elig_profiles.eligible
2811 (p_person_id => p_person_id,
2812 p_business_group_id => l_business_group_id,
2813 p_effective_date => l_period_end_date,
2814 p_eligprof_tab => t_prof_tbl,
2815 p_comp_obj_mode => FALSE,
2816 p_score_tab => l_score_tab
2817 );
2818
2819 hr_utility.set_location('After ben_evaluate_elig_profiles.eligible...',168);
2820 ELSE
2821 l_elig_return_status := TRUE ;
2822 END IF;
2823 EXCEPTION
2824 WHEN OTHERS THEN
2825 hr_utility.set_location('Exception : '||SQLERRM,10);
2826 l_elig_return_status := FALSE ;
2827 END;
2828 ELSE
2829 hr_utility.set_location('No eligibility profile attached to the statement' ||stmt_rec.stmt_id ||
2830 'The person id '||p_person_id|| 'is eligible for processing .',169 );
2831
2832 l_elig_return_status := TRUE ;
2833 END IF;
2834
2835 IF l_elig_return_status THEN
2836
2837 l_num_rows := l_num_rows + 1;
2838 l_num_persons := l_num_persons + 1;
2839 l_person_action_ids.EXTEND (1);
2840 l_person_ids.EXTEND (1);
2841 l_stmt_ids.EXTEND (1);
2842 l_perd_ids.EXTEND (1);
2843 l_bg_ids.EXTEND (1);
2844 get_name( l_business_group_id ,
2845 temp_ee_id ,
2846 l_period_end_date ,
2847 p_bg_name ,
2848 p_ee_name);
2849 rep_count := l_rep_rec.COUNT+1 ;
2850 l_rep_rec(rep_count ).p_TYPE := -1;
2851 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
2852 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := l_business_group_id;
2853 l_rep_rec(rep_count ).ELIGY_ID :=temp_ee_id;
2854 l_rep_rec(rep_count ).STMT_ID := stmt_rec.stmt_id;
2855 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
2856 l_rep_rec(rep_count ).ELIGY_PROF_NAME := p_ee_name;
2857 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
2858 l_rep_rec(rep_count ).SETUP_VALID := 'Y';
2859 l_rep_rec(rep_count ).TOTAL_PERSONS := 1 ;
2860 l_rep_rec(rep_count ).PERIOD_ID := stmt_rec.period_id;
2861
2862 SELECT ben_person_actions_s.NEXTVAL
2863 INTO l_person_action_ids (l_num_rows)
2864 FROM DUAL;
2865
2866 l_person_ids (l_num_rows) := p_person_id;
2867 l_stmt_ids (l_num_rows) := stmt_rec.stmt_id;
2868 l_perd_ids(l_num_rows) := stmt_rec.period_id;
2869 l_bg_ids(l_num_rows) := l_business_group_id;
2870
2871 WRITE ('=====================Person Header====================');
2872 --9702694 WRITE ('||Person Name -' || per_rec.name);
2873 WRITE ('||Business Group -' || p_bg_name ) ;
2874 WRITE ('||Person Id -' || p_person_id);
2875 WRITE ('||Business Group Id -' || l_business_group_id ) ;
2876 WRITE ('||stmt_id -' || stmt_rec.stmt_id);
2877 WRITE ('||Person Action id -' || l_person_action_ids (l_num_rows));
2878 WRITE ('=======================================================');
2879
2880 IF l_num_rows = l_chunk_size THEN
2881 l_num_ranges := l_num_ranges + 1;
2882 hr_utility.set_location('inserting INTO person actions :..',170);
2883 g_actn := 'inserting INTO person actions : person id ' || p_person_id ;
2884 hr_utility.set_location(g_actn,171);
2885
2886 insert_person_actions
2887 (p_per_actn_id_array => l_person_action_ids,
2888 p_per_id => l_person_ids,
2889 p_benefit_action_id => l_benefit_action_id,
2890 p_stmt_id =>l_stmt_ids,
2891 p_perd_id =>l_perd_ids,
2892 p_bg_id =>l_bg_ids
2893 );
2894 l_num_rows := 0;
2895 l_person_action_ids.DELETE;
2896 l_person_ids.DELETE;
2897 l_stmt_ids.DELETE;
2898 l_bg_ids.DELETE;
2899 l_perd_ids.DELETE;
2900 END IF;
2901 ELSE
2902 hr_utility.set_location('The person id :'||p_person_id || 'is not eligible for the statement' ||stmt_rec.stmt_id ,171);
2903 --fnd_message.set_name ('BEN', 'BEN_TCS_CON_NO_ELIG_PERSON');
2904 --fnd_message.raise_error;
2905 get_name(l_business_group_id ,
2906 temp_ee_id ,
2907 l_period_end_date ,
2908 p_bg_name ,
2909 p_ee_name);
2910 rep_count := l_rep_rec.COUNT +1;
2911 l_rep_rec(rep_count ).p_TYPE := -1;
2912 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
2913 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := l_business_group_id;
2914 l_rep_rec(rep_count ).ELIGY_ID :=temp_ee_id ;
2915 l_rep_rec(rep_count ).STMT_ID := stmt_rec.stmt_id;
2916 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
2917 l_rep_rec(rep_count ).ELIGY_PROF_NAME := p_ee_name;
2918 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
2919 l_rep_rec(rep_count ).SETUP_VALID := 'Y';
2920 l_rep_rec(rep_count ).TOTAL_PERSONS := 0 ;
2921
2922 END IF;
2923 END IF;
2924
2925 ELSIF (l_business_group_id IS NOT NULL AND stmt_rec.valid_flag = 'Y' ) THEN
2926
2927 g_actn := 'processing person ids for the business group id ' || l_business_group_id ;
2928 hr_utility.set_location(g_actn,172);
2929 temp_ee_id :=null;
2930 IF (stmt_rec.ee_id IS NOT NULL ) THEN
2931 SELECT ELIGY_PRFL_ID INTO temp_ee_id
2932 FROM BEN_ELIGY_PRFL_F
2933 WHERE STAT_CD='A'
2934 AND ELIGY_PRFL_ID = stmt_rec.ee_id
2935 AND l_period_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
2936 END IF;
2937 OPEN c_person_selection (stmt_rec.stmt_id,
2938 l_period_start_date,
2939 l_period_end_date ,
2940 l_business_group_id,
2941 p_ben_grp_id,
2942 p_position_id,
2943 p_job_id,
2944 p_payroll_id,
2945 p_location_id,
2946 p_supervisor_id,
2947 p_org_id
2948 );
2949
2950 t_prof_tbl (1).mndtry_flag := 'N';
2951 t_prof_tbl (1).compute_score_flag := 'N';
2952 t_prof_tbl (1).trk_scr_for_inelg_flag := 'N';
2953 get_name(l_business_group_id ,
2954 temp_ee_id ,
2955 l_period_end_date ,
2956 p_bg_name ,
2957 p_ee_name);
2958 LOOP
2959 FETCH c_person_selection
2960 INTO per_rec;
2961 EXIT WHEN c_person_selection%NOTFOUND;
2962
2963 g_actn := 'processing the person id ' || per_rec.person_id ;
2964 hr_utility.set_location(g_actn,173);
2965 BEGIN
2966 IF (stmt_rec.ee_id IS NOT NULL ) THEN
2967 t_prof_tbl (1).eligy_prfl_id := stmt_rec.ee_id;
2968 IF (temp_ee_id is not null ) THEN
2969
2970 ben_env_object.init(
2971 p_business_group_id => stmt_rec.bg_id,
2972 p_effective_date => l_period_end_date,
2973 p_thread_id => 99 ,
2974 p_chunk_size => l_chunk_size ,
2975 p_threads => l_threads ,
2976 p_max_errors => l_max_errors_allowed,
2977 p_benefit_action_id => l_benefit_action_id
2978 );
2979
2980 hr_utility.set_location('before calling ben_evaluate_elig_profiles.eligible...' ,174);
2981 l_elig_return_status :=
2982 ben_evaluate_elig_profiles.eligible
2983 (p_person_id => per_rec.person_id,
2984 p_business_group_id => stmt_rec.bg_id ,
2985 p_effective_date => l_period_end_date,
2986 p_eligprof_tab => t_prof_tbl,
2987 p_comp_obj_mode => FALSE,
2988 p_score_tab => l_score_tab
2989 );
2990 hr_utility.set_location('after ben_evaluate_elig_profiles.eligible...',175);
2991 ELSE
2992 l_elig_return_status := TRUE ;
2993 END IF;
2994
2995 ELSE
2996 l_elig_return_status := TRUE ;
2997 END IF;
2998 EXCEPTION
2999 WHEN OTHERS THEN
3000 hr_utility.set_location('Exception : '||SQLERRM,10);
3001 l_elig_return_status := FALSE ;
3002 END;
3003 IF l_elig_return_status THEN
3004
3005 hr_utility.set_location( 'The person id ' || per_rec.person_id || ' is eligible for the statement ' ||
3006 stmt_rec.stmt_id,176);
3007
3008 l_num_rows := l_num_rows + 1;
3009 l_num_persons := l_num_persons + 1;
3010 l_person_action_ids.EXTEND (1);
3011 l_person_ids.EXTEND (1);
3012 l_stmt_ids.EXTEND (1);
3013 l_perd_ids.EXTEND (1);
3014 l_bg_ids.EXTEND (1);
3015
3016 hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,177);
3017
3018 SELECT ben_person_actions_s.NEXTVAL
3019 INTO l_person_action_ids (l_num_rows)
3020 FROM DUAL;
3021
3022 l_person_ids (l_num_rows) := per_rec.person_id;
3023 l_stmt_ids (l_num_rows) := stmt_rec.stmt_id;
3024 l_perd_ids(l_num_rows) := stmt_rec.period_id;
3025 l_bg_ids(l_num_rows) := stmt_rec.bg_id;
3026
3027
3028 WRITE ('=====================Person Header====================');
3029 --9702694 WRITE ('||Person Name -' || per_rec.name);
3030 WRITE ('||Business Group -' || p_bg_name ) ;
3031 WRITE ('||Person Id -' || per_rec.person_id);
3032 WRITE ('||Business Group Id -' || stmt_rec.bg_id ) ;
3033 WRITE ('||stmt_id -' || stmt_rec.stmt_id);
3034 WRITE ('||Person Action id -' || l_person_action_ids (l_num_rows));
3035 WRITE ('=======================================================');
3036
3037 IF l_num_rows = l_chunk_size
3038 THEN
3039 g_actn := 'inserting INTO person actions ';
3040 hr_utility.set_location(g_actn,178);
3041
3042 l_num_ranges := l_num_ranges + 1;
3043 insert_person_actions
3044 (p_per_actn_id_array => l_person_action_ids,
3045 p_per_id => l_person_ids,
3046 p_benefit_action_id => l_benefit_action_id,
3047 p_stmt_id =>l_stmt_ids,
3048 p_perd_id =>l_perd_ids,
3049 p_bg_id =>l_bg_ids
3050 );
3051 l_num_rows := 0;
3052 l_person_action_ids.DELETE;
3053 l_person_ids.DELETE;
3054 l_stmt_ids.DELETE;
3055 l_perd_ids.DELETE;
3056 l_bg_ids.DELETE;
3057 END IF;
3058 ELSE
3059 hr_utility.set_location( 'The person id ' || per_rec.person_id || ' is not eligible for the statement ' ||
3060 stmt_rec.stmt_id,179);
3061 END IF;
3062 END LOOP;
3063
3064 rep_count := l_rep_rec.COUNT +1;
3065 l_rep_rec(rep_count ).p_TYPE := -1;
3066 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
3067 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := l_business_group_id;
3068 l_rep_rec(rep_count ).ELIGY_ID :=temp_ee_id ;
3069 l_rep_rec(rep_count ).STMT_ID := stmt_rec.stmt_id;
3070 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
3071 l_rep_rec(rep_count ).ELIGY_PROF_NAME := p_ee_name;
3072 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
3073 l_rep_rec(rep_count ).SETUP_VALID := 'Y';
3074 l_rep_rec(rep_count ).TOTAL_PERSONS := l_num_persons ;
3075 l_rep_rec(rep_count ).PERIOD_ID := stmt_rec.period_id;
3076
3077 CLOSE c_person_selection;
3078
3079 ELSIF (l_business_group_id IS NULL ) THEN
3080
3081 l_loop_cnt := 1;
3082 t_prof_tbl (1).mndtry_flag := 'N';
3083 t_prof_tbl (1).compute_score_flag := 'N';
3084 t_prof_tbl (1).trk_scr_for_inelg_flag := 'N';
3085
3086 WHILE (l_loop_cnt <= l_count)
3087 LOOP
3088 l_person_temp := 0;
3089
3090 g_actn := 'processing the stmt id ' || stmt_record_rec (l_loop_cnt).statement_rec.stmt_id ;
3091 hr_utility.set_location(g_actn,180);
3092
3093 IF (stmt_record_rec (l_loop_cnt).statement_rec.valid_flag = 'Y' ) THEN
3094 OPEN c_person_selection (stmt_record_rec (l_loop_cnt).statement_rec.stmt_id,
3095 l_period_start_date,
3096 l_period_end_date ,
3097 stmt_record_rec (l_loop_cnt).statement_rec.bg_id,
3098 p_ben_grp_id,
3099 p_position_id,
3100 p_job_id,
3101 p_payroll_id,
3102 p_location_id,
3103 p_supervisor_id,
3104 p_org_id);
3105 get_name(stmt_record_rec (l_loop_cnt).statement_rec.bg_id ,
3106 stmt_record_rec (l_loop_cnt).statement_rec.ee_id ,
3107 l_period_end_date ,
3108 p_bg_name ,
3109 p_ee_name);
3110 LOOP
3111 FETCH c_person_selection
3112 INTO per_rec;
3113 EXIT WHEN c_person_selection%NOTFOUND;
3114
3115 g_actn := 'processing the person id ' || per_rec.person_id ;
3116 hr_utility.set_location(g_actn,181);
3117 BEGIN
3118 IF (stmt_record_rec (l_loop_cnt).statement_rec.ee_id IS NOT NULL ) THEN
3119
3120 hr_utility.set_location ('checking the eligibility of the person id ' ||per_rec.person_id,182 );
3121
3122 t_prof_tbl (1).eligy_prfl_id := stmt_record_rec (l_loop_cnt).statement_rec.ee_id;
3123
3124 ben_env_object.init(
3125 p_business_group_id => stmt_record_rec (l_loop_cnt).statement_rec.bg_id ,
3126 p_effective_date => l_period_end_date,
3127 p_thread_id => 99 ,
3128 p_chunk_size => l_chunk_size ,
3129 p_threads => l_threads ,
3130 p_max_errors => l_max_errors_allowed,
3131 p_benefit_action_id => l_benefit_action_id
3132 );
3133 hr_utility.set_location('Before calling ben_evaluate_elig_profiles.eligible...',183);
3134 l_elig_return_status :=
3135 ben_evaluate_elig_profiles.eligible
3136 (p_person_id => per_rec.person_id,
3137 p_business_group_id => stmt_record_rec (l_loop_cnt).statement_rec.bg_id ,
3138 p_effective_date => l_period_end_date,
3139 p_eligprof_tab => t_prof_tbl,
3140 p_comp_obj_mode => FALSE,
3141 p_score_tab => l_score_tab
3142 );
3143 ELSE
3144 l_elig_return_status := TRUE ;
3145 END IF;
3146 EXCEPTION
3147 WHEN OTHERS THEN
3148 hr_utility.set_location('Exception : '||SQLERRM,10);
3149 l_elig_return_status := FALSE ;
3150 END;
3151 IF l_elig_return_status THEN
3152
3153 l_num_rows := l_num_rows + 1;
3154 l_num_persons := l_num_persons + 1;
3155 l_person_temp := l_person_temp + 1;
3156 l_person_action_ids.EXTEND (1);
3157 l_person_ids.EXTEND (1);
3158 l_stmt_ids.EXTEND (1);
3159 l_perd_ids.EXTEND (1);
3160 l_bg_ids.EXTEND (1);
3161
3162 hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,184);
3163
3164 SELECT ben_person_actions_s.NEXTVAL
3165 INTO l_person_action_ids (l_num_rows)
3166 FROM DUAL;
3167
3168 l_person_ids (l_num_rows) := per_rec.person_id;
3169 l_stmt_ids (l_num_rows) := stmt_record_rec (l_loop_cnt).statement_rec.stmt_id;
3170 l_perd_ids(l_num_rows) := stmt_record_rec (l_loop_cnt).statement_rec.period_id;
3171 l_bg_ids(l_num_rows) :=stmt_record_rec (l_loop_cnt).statement_rec.bg_id;
3172
3173 WRITE ('=====================Person Header====================');
3174 --9702694 WRITE ('||Person Name -' || per_rec.name);
3175 WRITE ('||Business Group -' || p_bg_name ) ;
3176 WRITE ('||Person Id -' || per_rec.person_id);
3177 WRITE ('||Business Group Id -' || stmt_record_rec (l_loop_cnt).statement_rec.bg_id ) ;
3178 WRITE ('||stmt_id -' || stmt_record_rec (l_loop_cnt).statement_rec.stmt_id);
3179 WRITE ('||Person Action id -' || l_person_action_ids (l_num_rows));
3180 WRITE ('=======================================================');
3181
3182
3183 IF l_num_rows = l_chunk_size THEN
3184 g_actn := 'inserting INTO person actions ';
3185 hr_utility.set_location(g_actn,185);
3186 l_num_ranges := l_num_ranges + 1;
3187 insert_person_actions
3188 (p_per_actn_id_array => l_person_action_ids,
3189 p_per_id => l_person_ids,
3190 p_benefit_action_id => l_benefit_action_id,
3191 p_stmt_id =>l_stmt_ids,
3192 p_perd_id =>l_perd_ids,
3193 p_bg_id =>l_bg_ids
3194 );
3195 l_num_rows := 0;
3196 l_person_action_ids.DELETE;
3197 l_person_ids.DELETE;
3198 l_stmt_ids.DELETE;
3199 l_bg_ids.DELETE;
3200 l_perd_ids.DELETE;
3201 END IF;
3202 ELSE
3203 hr_utility.set_location ('The person : ' ||per_rec.person_id || 'is not eligible',186);
3204 END IF;
3205 END LOOP;
3206
3207
3208 rep_count := l_rep_rec.COUNT+1 ;
3209 l_rep_rec(rep_count ).p_TYPE := -1;
3210 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
3211 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := stmt_record_rec (l_loop_cnt).statement_rec.bg_id;
3212 l_rep_rec(rep_count ).ELIGY_ID :=stmt_record_rec (l_loop_cnt).statement_rec.ee_id ;
3213 l_rep_rec(rep_count ).STMT_ID :=stmt_record_rec (l_loop_cnt).statement_rec.stmt_id ;
3214 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
3215 l_rep_rec(rep_count ).ELIGY_PROF_NAME := p_ee_name;
3216 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
3217 l_rep_rec(rep_count ).SETUP_VALID := 'Y';
3218 l_rep_rec(rep_count ).TOTAL_PERSONS := l_person_temp ;
3219 l_rep_rec(rep_count ).PERIOD_ID := stmt_record_rec (l_loop_cnt).statement_rec.period_id;
3220
3221 END IF;
3222 l_loop_cnt := l_loop_cnt + 1;
3223 CLOSE c_person_selection;
3224 END LOOP;
3225 END IF;
3226
3227 ELSE
3228 IF (l_business_group_id IS NULL) THEN
3229 -- IF( FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP')='Y' ) THEN
3230 SELECT name
3231 INTO l_stmt_name
3232 FROM BEN_TCS_STMT
3233 WHERE stmt_id = p_stmt_id
3234 AND name = p_stmt_name;
3235 IF ( l_stmt_name IS NULL ) THEN
3236 WRITE('The statement Name has been changed ');
3237 fnd_message.set_name ('BEN', 'BEN_TCS_STMT_NAME_CHG');
3238 fnd_message.raise_error;
3239 END IF;
3240 OPEN c_stmt_id_bg_id1 (p_stmt_name , l_period_start_date ,l_actual_end_date);
3241 LOOP
3242 FETCH c_stmt_id_bg_id1
3243 INTO stmt_rec1;
3244 EXIT WHEN c_stmt_id_bg_id1%NOTFOUND;
3245
3246 l_count := l_count + 1;
3247 stmt_record_rec1 (l_count).statement_rec1 := stmt_rec1;
3248 END LOOP;
3249
3250 hr_utility.set_location ('Number of statement ids to be processed '||l_count,187);
3251 g_exec_param_rec.Number_Of_BGs :=l_count;
3252 CLOSE c_stmt_id_bg_id1;
3253 /* ELSE
3254 l_business_group_id := HR_GENERAL.GET_BUSINESS_GROUP_ID ;
3255 g_exec_param_rec.Number_Of_BGs :=1;
3256 END IF;*/
3257 END IF;
3258
3259 IF ( p_person_id IS NOT NULL)THEN
3260
3261 hr_utility.set_location('checking for the person_id id ... '||p_person_id,188);
3262 g_exec_param_rec.Number_Of_BGs :=1;
3263 OPEN c_check_stmt_person(p_person_id ,p_stmt_id, p_period_id ) ;
3264
3265 FETCH c_check_stmt_person into stmt_per_rec;
3266
3267 IF c_check_stmt_person%NOTFOUND THEN
3268 get_name( p_bg_id=> l_business_group_id ,
3269 v_period_end_date => l_period_end_date ,
3270 p_bg_name =>p_bg_name ,
3271 p_ee_name => p_ee_name);
3272 rep_count := l_rep_rec.COUNT +1;
3273 l_rep_rec(rep_count ).p_TYPE := -1;
3274 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
3275 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := l_business_group_id;
3276 l_rep_rec(rep_count ).STMT_ID := p_stmt_id;
3277 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
3278 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
3279 l_rep_rec(rep_count ).TOTAL_PERSONS := 0;
3280 l_rep_rec(rep_count ).PERIOD_ID := p_period_id ;
3281
3282 WRITE('The person id :'||p_person_id || 'doesnt have the statement' ||stmt_rec.stmt_id );
3283 fnd_message.set_name ('BEN', 'BEN_TCS_STMT_DOESNT_EXIST');
3284 fnd_message.raise_error;
3285 ELSE
3286
3287 t_prof_tbl (1).mndtry_flag := 'N';
3288 t_prof_tbl (1).compute_score_flag := 'N';
3289 t_prof_tbl (1).trk_scr_for_inelg_flag := 'N';
3290 l_num_rows := l_num_rows + 1;
3291 l_num_persons := l_num_persons + 1;
3292 l_person_action_ids.EXTEND (1);
3293 l_person_ids.EXTEND (1);
3294 l_stmt_ids.EXTEND (1);
3295 l_perd_ids.EXTEND (1);
3296 l_bg_ids.EXTEND (1);
3297
3298 SELECT ben_person_actions_s.NEXTVAL
3299 INTO l_person_action_ids (l_num_rows)
3300 FROM DUAL;
3301
3302 l_person_ids (l_num_rows) := p_person_id;
3303 l_perd_ids(l_num_rows) := p_period_id;
3304 l_bg_ids(l_num_rows) := l_business_group_id;
3305 l_stmt_ids(l_num_rows) := p_stmt_id;
3306
3307 WRITE ('=====================Person Header====================');
3308 WRITE ('||Person Name -' || stmt_per_rec.name);
3309 WRITE ('||Business Group -' || p_bg_name ) ;
3310 WRITE ('||Person Id -' || p_person_id);
3311 WRITE ('||Business Group Id -' || l_business_group_id ) ;
3312 WRITE ('||stmt_id -' || p_stmt_id);
3313 WRITE ('||Person Action id -' || l_person_action_ids (l_num_rows));
3314 WRITE ('=======================================================');
3315
3316 IF l_num_rows = l_chunk_size THEN
3317 l_num_ranges := l_num_ranges + 1;
3318 g_actn := 'inserting INTO person actions : person id ' || p_person_id;
3319 hr_utility.set_location(g_actn,189);
3320 insert_person_actions
3321 (p_per_actn_id_array => l_person_action_ids,
3322 p_per_id => l_person_ids,
3323 p_benefit_action_id => l_benefit_action_id,
3324 p_stmt_id =>l_stmt_ids,
3325 p_perd_id =>l_perd_ids,
3326 p_bg_id =>l_bg_ids
3327 );
3328 l_num_rows := 0;
3329 l_person_action_ids.DELETE;
3330 l_person_ids.DELETE;
3331 l_stmt_ids.DELETE;
3332 l_perd_ids.DELETE;
3333 l_bg_ids.DELETE;
3334 END IF;
3335 get_name(p_bg_id=> l_business_group_id ,
3336 v_period_end_date => l_period_end_date ,
3337 p_bg_name =>p_bg_name ,
3338 p_ee_name => p_ee_name);
3339 rep_count := l_rep_rec.COUNT+1 ;
3340
3341 l_rep_rec(rep_count ).p_TYPE := -1;
3342 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
3343 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := l_business_group_id;
3344 l_rep_rec(rep_count ).STMT_ID := p_stmt_id ;
3345 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
3346 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
3347 l_rep_rec(rep_count ).TOTAL_PERSONS := 1;
3348 l_rep_rec(rep_count ).PERIOD_ID := p_period_id ;
3349 END IF;
3350 CLOSE c_check_stmt_person ;
3351 ELSIF(l_business_group_id IS NOT NULL) THEN
3352 g_actn := 'processing the business group id ' || l_business_group_id ;
3353 hr_utility.set_location(g_actn,190);
3354 g_exec_param_rec.Number_Of_BGs :=1;
3355
3356 t_prof_tbl (1).mndtry_flag := 'N';
3357 t_prof_tbl (1).compute_score_flag := 'N';
3358 t_prof_tbl (1).trk_scr_for_inelg_flag := 'N';
3359 get_name(p_bg_id=> l_business_group_id ,
3360 v_period_end_date => l_period_end_date ,
3361 p_bg_name =>p_bg_name ,
3362 p_ee_name => p_ee_name);
3363 --16276324
3364 FOR l_per_rec in c_person_selection (p_stmt_id,
3365 l_period_start_date,
3366 l_period_end_date ,
3367 l_business_group_id,
3368 p_ben_grp_id,
3369 p_position_id,
3370 p_job_id,
3371 p_payroll_id,
3372 p_location_id,
3373 p_supervisor_id,
3374 p_org_id
3375 )
3376 LOOP
3377
3378 OPEN c_check_stmt_avail_1(p_stmt_id , p_period_id,l_per_rec.person_id ) ;
3379 LOOP
3380 FETCH c_check_stmt_avail_1
3381 INTO per_rec;
3382 EXIT WHEN c_check_stmt_avail_1%NOTFOUND;
3383 g_actn := 'processing the person id ' || per_rec.person_id ;
3384 hr_utility.set_location(g_actn,191);
3385
3386 l_num_rows := l_num_rows + 1;
3387 l_num_persons := l_num_persons + 1;
3388 l_person_action_ids.EXTEND (1);
3389 l_person_ids.EXTEND (1);
3390 l_stmt_ids.EXTEND (1);
3391 l_perd_ids.EXTEND (1);
3392 l_bg_ids.EXTEND (1);
3393
3394
3395 SELECT ben_person_actions_s.NEXTVAL
3396 INTO l_person_action_ids (l_num_rows)
3397 FROM DUAL;
3398
3399 l_person_ids (l_num_rows) := per_rec.person_id;
3400 l_stmt_ids (l_num_rows) := p_stmt_id;
3401 l_perd_ids(l_num_rows) := p_period_id;
3402 l_bg_ids(l_num_rows) := l_business_group_id;
3403
3404 WRITE ('=====================Person Header====================');
3405 --9702694 WRITE ('||Person Name -' || per_rec.name);
3406 WRITE ('||Business Group -' || p_bg_name ) ;
3407 WRITE ('||Person Id -' || per_rec.person_id);
3408 WRITE ('||Business Group Id -' || l_business_group_id ) ;
3409 WRITE ('||stmt_id -' || p_stmt_id);
3410 WRITE ('||Person Action id -' || l_person_action_ids (l_num_rows));
3411 WRITE ('=======================================================');
3412
3413 IF l_num_rows = l_chunk_size
3414 THEN
3415 g_actn := 'inserting INTO person actions ';
3416 hr_utility.set_location(g_actn,192);
3417 l_num_ranges := l_num_ranges + 1;
3418 insert_person_actions
3419 (p_per_actn_id_array => l_person_action_ids,
3420 p_per_id => l_person_ids,
3421 p_benefit_action_id => l_benefit_action_id,
3422 p_stmt_id =>l_stmt_ids,
3423 p_perd_id =>l_perd_ids,
3424 p_bg_id =>l_bg_ids
3425 );
3426
3427 l_num_rows := 0;
3428 l_person_action_ids.DELETE;
3429 l_person_ids.DELETE;
3430 l_stmt_ids.DELETE;
3431 l_perd_ids.DELETE;
3432 l_bg_ids.DELETE;
3433 END IF;
3434 END LOOP;
3435 CLOSE c_check_stmt_avail_1;
3436 END LOOP;
3437
3438 rep_count := l_rep_rec.COUNT +1;
3439 l_rep_rec(rep_count ).p_TYPE := -1;
3440 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
3441 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := l_business_group_id;
3442 l_rep_rec(rep_count ).STMT_ID := p_stmt_id ;
3443 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
3444 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
3445 l_rep_rec(rep_count ).TOTAL_PERSONS := l_num_persons;
3446 l_rep_rec(rep_count ).PERIOD_ID := p_period_id ;
3447
3448 ELSIF (l_business_group_id is null ) THEN
3449
3450 l_loop_cnt := 1;
3451 t_prof_tbl (1).mndtry_flag := 'N';
3452 t_prof_tbl (1).compute_score_flag := 'N';
3453 t_prof_tbl (1).trk_scr_for_inelg_flag := 'N';
3454 WHILE (l_loop_cnt <= l_count)
3455 LOOP
3456 l_person_temp := 0;
3457 g_actn := 'processing the stmt id ' || stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id
3458 || 'period id :'||stmt_record_rec1 (l_loop_cnt).statement_rec1.period_id;
3459 hr_utility.set_location(g_actn,193);
3460 OPEN c_check_stmt_avail(stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id ,stmt_record_rec1 (l_loop_cnt).statement_rec1.period_id ) ;
3461 get_name(p_bg_id=> stmt_record_rec1 (l_loop_cnt).statement_rec1.bg_id ,
3462 v_period_end_date => l_period_end_date ,
3463 p_bg_name =>p_bg_name ,
3464 p_ee_name => p_ee_name);
3465 LOOP
3466 WRITE ( 'getting person ids for the stmt id '||stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id );
3467 FETCH c_check_stmt_avail
3468 INTO per_rec;
3469 EXIT WHEN c_check_stmt_avail%NOTFOUND;
3470
3471 l_num_rows := l_num_rows + 1;
3472 l_num_persons := l_num_persons + 1;
3473 l_person_temp := l_person_temp +1;
3474 l_person_action_ids.EXTEND (1);
3475 l_person_ids.EXTEND (1);
3476 l_stmt_ids.EXTEND (1);
3477 l_perd_ids.EXTEND (1);
3478 l_bg_ids.EXTEND (1);
3479
3480 hr_utility.set_location ('Adding the person id :' ||per_rec.person_id ,193);
3481
3482
3483 SELECT ben_person_actions_s.NEXTVAL
3484 INTO l_person_action_ids (l_num_rows)
3485 FROM DUAL;
3486
3487 l_person_ids (l_num_rows) := per_rec.person_id;
3488 l_stmt_ids (l_num_rows) := stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id;
3489 l_perd_ids(l_num_rows) := stmt_record_rec1 (l_loop_cnt).statement_rec1.period_id;
3490 l_bg_ids(l_num_rows) := stmt_record_rec1 (l_loop_cnt).statement_rec1.bg_id;
3491
3492 WRITE ('=====================Person Header====================');
3493 --9702694 WRITE ('||Person Name -' || per_rec.name);
3494 WRITE ('||Business Group -' || p_bg_name ) ;
3495 WRITE ('||Person Id -' || per_rec.person_id);
3496 WRITE ('||Business Group Id -' || stmt_record_rec1 (l_loop_cnt).statement_rec1.bg_id ) ;
3497 WRITE ('||stmt_id -' || stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id);
3498 WRITE ('||Person Action id -' || l_person_action_ids (l_num_rows));
3499 WRITE ('=======================================================');
3500
3501
3502 IF l_num_rows = l_chunk_size THEN
3503 g_actn := 'inserting INTO person actions ';
3504 hr_utility.set_location(g_actn,194);
3505 l_num_ranges := l_num_ranges + 1;
3506 insert_person_actions
3507 (p_per_actn_id_array => l_person_action_ids,
3508 p_per_id => l_person_ids,
3509 p_benefit_action_id => l_benefit_action_id,
3510 p_stmt_id =>l_stmt_ids,
3511 p_perd_id =>l_perd_ids,
3512 p_bg_id =>l_bg_ids
3513 );
3514
3515 l_num_rows := 0;
3516 l_person_action_ids.DELETE;
3517 l_person_ids.DELETE;
3518 l_stmt_ids.DELETE;
3519 l_perd_ids.DELETE;
3520 l_bg_ids.DELETE;
3521 END IF;
3522
3523 END LOOP;
3524
3525
3526 rep_count := l_rep_rec.COUNT +1;
3527 l_rep_rec(rep_count ).p_TYPE := -1;
3528 l_rep_rec(rep_count ).BENEFIT_ACTION_ID :=l_benefit_action_id ;
3529 l_rep_rec(rep_count ).BUSINESS_GROUP_ID := stmt_record_rec1 (l_loop_cnt).statement_rec1.bg_id;
3530 l_rep_rec(rep_count ).STMT_ID := stmt_record_rec1 (l_loop_cnt).statement_rec1.stmt_id;
3531 l_rep_rec(rep_count ).BUSINESS_GROUP_NAME := p_bg_name;
3532 l_rep_rec(rep_count ).STMT_NAME := p_stmt_name;
3533 l_rep_rec(rep_count ).TOTAL_PERSONS := l_person_temp;
3534 l_rep_rec(rep_count ).PERIOD_ID := stmt_record_rec1 (l_loop_cnt).statement_rec1.period_id ;
3535 l_loop_cnt := l_loop_cnt + 1;
3536 CLOSE c_check_stmt_avail;
3537 END LOOP;
3538 END IF;
3539 END IF;
3540
3541
3542 WRITE ('Total no of person selected - ' || l_num_persons);
3543 g_actn := 'Inserting the last range of persons IF exists...';
3544 WRITE (g_actn);
3545 WRITE ( 'Time after processing the person selections '
3546 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
3547
3548 IF l_num_rows <> 0 THEN
3549 l_num_ranges := l_num_ranges + 1;
3550 hr_utility.set_location('l_num_ranges '||l_num_ranges,500);
3551 insert_person_actions
3552 (p_per_actn_id_array => l_person_action_ids,
3553 p_per_id => l_person_ids,
3554 p_benefit_action_id => l_benefit_action_id,
3555 p_stmt_id =>l_stmt_ids,
3556 p_perd_id =>l_perd_ids,
3557 p_bg_id =>l_bg_ids
3558 );
3559 l_num_rows := 0;
3560 l_person_action_ids.DELETE;
3561 l_person_ids.DELETE;
3562 l_stmt_ids.DELETE;
3563 l_perd_ids.DELETE;
3564 l_bg_ids.DELETE;
3565 END IF;
3566 COMMIT;
3567
3568 g_actn := 'Submitting job to con-current manager...';
3569 WRITE (g_actn);
3570 g_actn := 'Preparing for launching concurrent requests';
3571 WRITE (g_actn);
3572
3573 ben_batch_utils.g_num_processes := 0;
3574 ben_batch_utils.g_processes_tbl.DELETE;
3575 WRITE ( 'Time before launching the threads '
3576 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
3577
3578 IF l_num_ranges > 1
3579 THEN
3580 hr_utility.set_location('L threads '||l_threads, 29);
3581 WRITE('l_threads'||l_threads );
3582 WRITE('l_num_ranges'||l_num_ranges );
3583 FOR loop_count IN 1 .. LEAST (l_threads, l_num_ranges) - 1
3584 LOOP
3585
3586 WRITE
3587 ('=====================Request Parameters===================');
3588 WRITE
3589 ('||Parameter value ');
3590 WRITE ('||argument2- ' || l_benefit_action_id);
3591 WRITE ('||argument3- ' || loop_count);
3592 WRITE
3593 ('==========================================================');
3594 l_request_id :=
3595 fnd_request.submit_request (application => 'BEN',
3596 program => 'BENTCSMT',
3597 description => NULL,
3598 sub_request => FALSE,
3599 argument1 => p_validate,
3600 argument2 => l_benefit_action_id,
3601 argument3 => loop_count,
3602 argument4 => to_char(sysdate,'yyyy/mm/dd'),
3603 argument5 => p_audit_log,
3604 argument6 => p_run_type,
3605 argument7 => l_period_start_date ,
3606 argument8 => l_period_end_date
3607 );
3608 ben_batch_utils.g_num_processes :=
3609 ben_batch_utils.g_num_processes + 1;
3610 ben_batch_utils.g_processes_tbl (ben_batch_utils.g_num_processes) :=
3611 l_request_id;
3612 hr_utility.set_location ('request id for this thread ' || l_request_id,160);
3613 COMMIT;
3614 END LOOP;
3615 ELSIF l_num_ranges = 0
3616 THEN
3617 WRITE ('<< No Person to process>>');
3618 RAISE l_silent_error;
3619 END IF;
3620
3621 WRITE ( 'Time after launching the threads '
3622 || TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
3623 );
3624 WRITE ('=====================do_multithread in Process============');
3625 WRITE ('||Parameter value ');
3626 WRITE ('||p_benefit_action_id- ' || l_benefit_action_id);
3627 WRITE ('||p_thread_id- ' || (l_threads + 1));
3628 WRITE ('==========================================================');
3629 hr_utility.set_location('L threads before calling do_mutithread'||l_threads, 29);
3630
3631 do_multithread (errbuf => errbuf,
3632 retcode => retcode,
3633 p_validate => p_validate,
3634 p_benefit_action_id => l_benefit_action_id,
3635 p_thread_id => l_threads + 1,
3636 p_effective_date => to_char(sysdate,'yyyy/mm/dd'),
3637 p_audit_log => p_audit_log,
3638 p_run_type => p_run_type,
3639 p_start_date => l_period_start_date,
3640 p_end_date => l_period_end_date
3641 );
3642
3643 g_actn := 'Calling ben_batch_utils.check_all_slaves_finished...';
3644 WRITE (g_actn);
3645 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
3646
3647 WRITE ('=====================End Process==========');
3648 WRITE ('||Parameter value ');
3649 WRITE ('||p_benefit_action_id- ' || l_benefit_action_id);
3650 WRITE ('||p_person_selected- ' || l_num_persons);
3651 WRITE ('==========================================================');
3652 WRITE ('=====================Summary==========');
3653 WRITE ('||Parameter value ');
3654 WRITE ('||Number of business groups processed - ' || g_exec_param_rec.Number_Of_BGs );
3655 WRITE ('||Number of persons processed - ' || l_num_persons);
3656 WRITE ('==========================================================');
3657
3658 g_actn := 'Calling delete hrchy ...';
3659 hr_utility.set_location (g_actn,161);
3660 delete_hrchy;
3661 g_actn := 'Calling Hierarchy Set...';
3662 hr_utility.set_location (g_actn,162);
3663 hrchy_set(l_benefit_action_id);
3664 g_actn := 'Calling end_process...';
3665 hr_utility.set_location (g_actn,163);
3666 end_process (p_benefit_action_id => l_benefit_action_id
3667 , p_person_selected => l_num_persons
3668 , p_business_group_id => l_business_group_id
3669 );
3670 g_actn := 'Finished Process Procedure...';
3671 hr_utility.set_location (g_actn,164);
3672
3673 IF (p_run_type ='GEN') THEN
3674 IF (g_validate = 'N') THEN
3675 IF (l_business_group_id IS NOT NULL OR p_person_id IS NOT NULL ) THEN
3676 IF (stmt_rec.stmt_id IS NOT NULL AND stmt_rec.period_id IS NOT NULL) THEN
3677 IF ( fnd_request.submit_request ( application => 'BEN',
3678 program => 'BENTCSTP',
3679 description => NULL,
3680 start_time => SYSDATE,
3681 sub_request => FALSE,
3682 argument1 => p_stmt_id,
3683 argument2 => p_period_id,
3684 argument3 => p_stmt_name,
3685 argument4 => p_person_id) = 0)
3686 THEN
3687 WRITE(' Printable page Process Errored ');
3688 END IF ;
3689 END IF;
3690 ELSE
3691 temp_count := 1;
3692 WHILE (temp_count <= l_count)
3693 LOOP
3694 IF (stmt_record_rec (temp_count).statement_rec.valid_flag = 'Y') THEN
3695 IF ( fnd_request.submit_request ( application => 'BEN',
3696 program => 'BENTCSTP',
3697 description => NULL,
3698 start_time => SYSDATE,
3699 sub_request => FALSE,
3700 argument1 => stmt_record_rec (temp_count).statement_rec.stmt_id ,
3701 argument2 => stmt_record_rec (temp_count).statement_rec.period_id,
3702 argument3 => p_stmt_name) = 0)
3703 THEN
3704 WRITE(' Printable page Process Errored ');
3705 END IF ;
3706 END IF;
3707 temp_count := temp_count+1;
3708 END LOOP;
3709 END IF;
3710 END IF;
3711 END IF;
3712 -- hr_utility.trace_off;
3713 EXCEPTION
3714 --
3715 WHEN l_silent_error
3716 THEN
3717 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
3718 delete_hrchy;
3719 end_process (p_benefit_action_id => l_benefit_action_id
3720 , p_person_selected => l_num_persons
3721 , p_business_group_id => l_business_group_id
3722 );
3723 print_cache;
3724 WHEN OTHERS
3725 THEN
3726 WRITE (fnd_message.get);
3727 WRITE (SQLERRM);
3728 WRITE ('Error Occurred');
3729
3730 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
3731 end_process (p_benefit_action_id => l_benefit_action_id
3732 , p_person_selected => l_num_persons
3733 , p_business_group_id => l_business_group_id
3734 );
3735
3736 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3737 fnd_message.set_token ('PROCEDURE', g_proc);
3738 fnd_message.set_token ('STEP', g_actn);
3739 fnd_message.raise_error;
3740 END;
3741
3742
3743
3744
3745 end BEN_TCS_STMT_PROCESS;