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