[Home] [Help]
PACKAGE BODY: APPS.BEN_PRE_DATAPUMP_PROCESS
Source
1 package body ben_pre_datapump_process as
2 /* $Header: benripmp.pkb 120.7 2006/05/03 09:40:12 nkkrishn noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Enrollment Process
13 Purpose
14 This is a wrapper procedure for Benefits enrollments,
15 dependents and beneficiaries designation for Enrollments conversion,
16 ongoing mass updates and IVR Process.
17 History
18 Date Who Version What?
19 ---- --- ------- -----
20 01 Nov 05 ikasire 115.0 Created
21 24 Jan 06 ikasired 115.2 Added update for
22 ATOMIC_LINKED_CALLS
23 08 Feb 06 ikasired 115.3 Added code for full_name and
24 DOB Validation
25 01 Mar 06 ikasired 115.4 Exclude C records
26 02 Mar 06 ikasired 115.5 get_pl_id error fix
27 23 Mar 06 ikasire 115.6 Added stub for Beneficiaries
28 future validations if required
29 for multirows edits
30 24 Mar 06 ikasired 115.7 Deleting the POST records to
31 avoid accidental deletion of the
32 records.
33 13 Apr 06 nkkrishn 115.8 Summary row elimination changes
34 19 Apr 06 ikasired 115.9 removed the DELETE for POST rows
35 as we are not creating any new
36 rows
37 02 May 06 nkkrishn 115.11 Fixed Beneficiary upload
38 */
39 --
40 --Globals
41 --
42 g_debug boolean := hr_utility.debug_enabled;
43 --
44 -- --------------------------------------------------------------------------------
45 -- |-----------------------------< INSERT_CREATE_ENROLLMENT >-------------------------|
46 -- -------------------------------------------------------------------------------+
47 --
48 procedure insert_create_enrollment
49 (
50 BATCH_ID NUMBER
51 ,API_MODULE_ID NUMBER
52 ,USER_SEQUENCE NUMBER
53 ,LINK_VALUE NUMBER
54 ,BUSINESS_GROUP_NAME VARCHAR2
55 ,P_LIFE_EVENT_DATE VARCHAR2
56 ,P_EFFECTIVE_DATE VARCHAR2
57 ,P_PROC_CD VARCHAR2
58 ,P_PROGRAM VARCHAR2
59 ,P_PROGRAM_NUM VARCHAR2
60 ,P_PLAN VARCHAR2
61 ,P_PLAN_NUM VARCHAR2
62 ,P_LIFE_EVENT_REASON VARCHAR2
63 ,P_EMPLOYEE_NUMBER VARCHAR2
64 ,P_NATIONAL_IDENTIFIER VARCHAR2
65 ,P_FULL_NAME VARCHAR2
66 ,P_DATE_OF_BIRTH VARCHAR2
67 ,P_PERSON_NUM VARCHAR2
68 ) is
69 begin
70 INSERT INTO hrdpv_create_enrollment (
71 BATCH_ID
72 ,BATCH_LINE_ID
73 ,API_MODULE_ID
74 ,LINE_STATUS
75 ,USER_SEQUENCE
76 ,LINK_VALUE
77 ,BUSINESS_GROUP_NAME
78 ,P_LIFE_EVENT_DATE
79 ,P_EFFECTIVE_DATE
80 ,P_PROC_CD
81 ,P_RECORD_TYP_CD
82 ,P_PROGRAM
83 ,P_PROGRAM_NUM
84 ,P_PLAN
85 ,P_PLAN_NUM
86 ,P_LIFE_EVENT_REASON
87 ,P_EMPLOYEE_NUMBER
88 ,P_NATIONAL_IDENTIFIER
89 ,P_FULL_NAME
90 ,P_DATE_OF_BIRTH
91 ,P_PERSON_NUM )
92 VALUES
93 (BATCH_ID
94 ,hr_pump_batch_lines_s.NEXTVAL
95 ,API_MODULE_ID
96 ,'U'
97 ,USER_SEQUENCE
98 ,LINK_VALUE
99 ,BUSINESS_GROUP_NAME
100 ,P_LIFE_EVENT_DATE
101 ,P_EFFECTIVE_DATE
102 ,P_PROC_CD
103 ,'POST'
104 ,P_PROGRAM
105 ,P_PROGRAM_NUM
106 ,P_PLAN
107 ,P_PLAN_NUM
108 ,P_LIFE_EVENT_REASON
109 ,P_EMPLOYEE_NUMBER
110 ,P_NATIONAL_IDENTIFIER
111 ,P_FULL_NAME
112 ,P_DATE_OF_BIRTH
113 ,P_PERSON_NUM
114 ) ;
115 end insert_create_enrollment ;
116 --
117 procedure insert_process_dependent
118 (
119 BATCH_ID NUMBER
120 ,API_MODULE_ID NUMBER
121 ,USER_SEQUENCE NUMBER
122 ,LINK_VALUE NUMBER
123 ,BUSINESS_GROUP_NAME VARCHAR2
124 ,P_LIFE_EVENT_DATE VARCHAR2
125 ,P_EFFECTIVE_DATE VARCHAR2
126 ,P_PROGRAM VARCHAR2
127 ,P_PROGRAM_NUM VARCHAR2
128 ,P_PLAN VARCHAR2
129 ,P_PLAN_NUM VARCHAR2
130 ,P_OPTION VARCHAR2
131 ,P_OPTION_NUM VARCHAR2
132 ,P_LIFE_EVENT_REASON VARCHAR2
133 ,P_EMPLOYEE_NUMBER VARCHAR2
134 ,P_NATIONAL_IDENTIFIER VARCHAR2
135 ,P_FULL_NAME VARCHAR2
136 ,P_DATE_OF_BIRTH VARCHAR2
137 ,P_PERSON_NUM VARCHAR2
138 ) is
139 begin
140 INSERT INTO hrdpv_process_dependent (
141 BATCH_ID
142 ,BATCH_LINE_ID
143 ,API_MODULE_ID
144 ,LINE_STATUS
145 ,USER_SEQUENCE
146 ,LINK_VALUE
147 ,BUSINESS_GROUP_NAME
148 ,P_LIFE_EVENT_DATE
149 ,P_EFFECTIVE_DATE
150 ,P_RECORD_TYP_CD
151 ,P_PROGRAM
152 ,P_PROGRAM_NUM
153 ,P_PLAN
154 ,P_PLAN_NUM
155 ,P_OPTION
156 ,P_OPTION_NUM
157 ,P_LIFE_EVENT_REASON
158 ,P_EMPLOYEE_NUMBER
159 ,P_NATIONAL_IDENTIFIER
160 ,P_FULL_NAME
161 ,P_DATE_OF_BIRTH
162 ,P_PERSON_NUM )
163 VALUES
164 (BATCH_ID
165 ,hr_pump_batch_lines_s.NEXTVAL
166 ,API_MODULE_ID
167 ,'U'
168 ,USER_SEQUENCE
169 ,LINK_VALUE
170 ,BUSINESS_GROUP_NAME
171 ,P_LIFE_EVENT_DATE
172 ,P_EFFECTIVE_DATE
173 ,'POST'
174 ,P_PROGRAM
175 ,P_PROGRAM_NUM
176 ,P_PLAN
177 ,P_PLAN_NUM
178 ,P_OPTION
179 ,P_OPTION_NUM
180 ,P_LIFE_EVENT_REASON
181 ,P_EMPLOYEE_NUMBER
182 ,P_NATIONAL_IDENTIFIER
183 ,P_FULL_NAME
184 ,P_DATE_OF_BIRTH
185 ,P_PERSON_NUM
186 ) ;
187 end insert_process_dependent ;
188 --
189 procedure pre_create_enrollment
190 (p_batch_id in number default null,
191 p_validate in varchar2 default 'N'
192 ) is
193 --
194 cursor c_choices is
195 select ch.*
196 from hrdpv_create_enrollment ch
197 where ch.batch_id = p_batch_id
198 and ch.line_status <> 'C'
199 order by p_person_num,
200 p_employee_number,
201 p_national_identifier,
202 p_full_name,
203 p_date_of_birth,
204 p_program,
205 p_program_num,
206 p_plan,
207 p_plan_num,
208 p_record_typ_cd
209 for update;
210 --
211 l_link_value hrdpv_create_enrollment.link_value%TYPE := 0 ;
212 l_sequence hrdpv_create_enrollment.user_sequence%TYPE := 1 ;
213 l_person_num hrdpv_create_enrollment.p_person_num%TYPE;
214 l_emp_num hrdpv_create_enrollment.p_employee_number%TYPE;
215 l_ssn hrdpv_create_enrollment.p_national_identifier%TYPE;
216 l_full_name hrdpv_create_enrollment.p_full_name%TYPE;
217 l_dob hrdpv_create_enrollment.p_date_of_birth%TYPE;
218 l_program hrdpv_create_enrollment.p_program%TYPE;
219 l_program_num hrdpv_create_enrollment.p_program_num%TYPE;
220 l_plan hrdpv_create_enrollment.p_plan%TYPE;
221 l_plan_num hrdpv_create_enrollment.p_plan_num%TYPE;
222 l_record_typ hrdpv_create_enrollment.p_record_typ_cd%TYPE ;
223
224 l_person_change boolean := true;
225 --
226 l_prev_rec c_choices%ROWTYPE;
227 l_curr_rec c_choices%ROWTYPE;
228 l_prev_link hrdpv_create_enrollment.link_value%TYPE ;
229 --
230 begin
231 --
232 /* IMPORTANT to enforce the following assumptions. Otherwise we need to enhance our
233 code to handle those cases.
234 --In a batch data needs to be consistent which means
235 --Person Data - Use either employee_number or SSN or Fullname plus DOB or Person num
236 --Plan design - Use either comp object name or num but can't use num for some plans
237 and name for some other plans.
238 */
239 fnd_file.put_line
240 (which => fnd_file.log,
241 buff => 'Entering - module_name :pre_create_enrollment' );
242 --
243 fnd_file.put_line
244 (which => fnd_file.log,
245 buff => 'Batch ID: '||p_batch_id );
246 --
247 --Update Header for ATOMIC_LINKED_CALLS
248 --we need patch 4665288 applied for this.
249 UPDATE hr_pump_batch_headers bh
250 SET bh.ATOMIC_LINKED_CALLS = 'Y'
251 WHERE bh.batch_id = p_batch_id ;
252 --
253 --
254 UPDATE hrdpv_create_enrollment
255 SET P_RECORD_TYP_CD = 'ENROLL'
256 WHERE batch_id = p_batch_id;
257 --
258 --
259 fnd_file.put_line
260 (which => fnd_file.log,
261 buff => 'Updated Header for ATOMIC_LINKED_CALLS');
262 --
263 for i in c_choices loop
264 --
265 l_curr_rec := i;
266 --
267 l_person_change := true;
268 --
269 IF i.p_person_num IS NOT NULL THEN
270 --
271 IF i.p_person_num = l_person_num THEN
272 l_person_change := false;
273 END IF;
274 --
275 ELSIF i.p_employee_number IS NOT NULL THEN
276 --
277 IF i.p_employee_number = l_emp_num THEN
278 l_person_change := false;
279 END IF;
280 --
281 ELSIF i.p_national_identifier IS NOT NULL THEN
282 --
283 IF i.p_national_identifier = l_ssn THEN
284 l_person_change := false;
285 END IF;
286 --
287 ELSIF i.p_full_name IS NOT NULL AND i.p_date_of_birth IS NOT NULL THEN
288 --
289 IF i.p_full_name = l_prev_rec.p_full_name AND
290 i.p_date_of_birth = l_prev_rec.p_date_of_birth THEN
291 --
292 l_person_change := false;
293 --
294 END IF;
295 --
296 END IF;
297 --
298 IF l_person_change THEN
299 --
300 --Now check if the last person record exists and if the record type is not
301 --POST then create on record for post with the previous record information.
302 --
303 l_link_value := l_link_value + 1;
304 --
305 ELSIF i.p_program is NOT NULL OR i.p_program_num IS NOT NULL THEN
306 --
307 IF i.p_program IS NOT NULL THEN
308 --
309 IF i.p_program <> l_program THEN
310 --If Program is changing and the last record type is not POST
311 --then create a record for POST
312 --
313 l_link_value := l_link_value + 1;
314 --
315 END IF;
316 --
317 ELSE
318 --
319 IF i.p_program_num <> l_program_num THEN
320 --
321 l_link_value := l_link_value + 1;
322 --
323 END IF;
324 --
325 END IF;
326 --
327 ELSE
328 --
329 IF i.p_plan IS NOT NULL THEN
330 --
331 IF i.p_plan <> l_plan THEN
332 --
333 l_link_value := l_link_value + 1;
334 --
335 END IF;
336 --
337 ELSE
338 --
339 IF i.p_plan_num <> l_plan_num THEN
340 --
341 l_link_value := l_link_value + 1;
342 --
343 END IF;
344 --
345 END IF;
346 --
347 END IF;
348 --
349 IF l_prev_link <> l_link_value AND l_prev_rec.P_RECORD_TYP_CD <> 'POST' THEN
350 --
351 IF l_prev_rec.P_PROGRAM IS NOT NULL OR
352 l_prev_rec.P_PROGRAM_NUM IS NOT NULL THEN
353 --
354 l_prev_rec.P_PLAN := null;
355 l_prev_rec.P_PLAN_NUM := null;
356 --
357 END IF ;
358 --NK
359 --Changes to eliminate summary row in Enrollment Upload Spreadsheet.
360 --Instead of inserting summary row, change the record type of the
361 --last record of the group from 'ENROLL' to 'POST'.
362 /*
363 --
364 INSERT_CREATE_ENROLLMENT
365 (BATCH_ID => p_batch_id
366 ,API_MODULE_ID => l_prev_rec.api_module_id
367 ,USER_SEQUENCE => l_sequence
368 ,LINK_VALUE => l_prev_link
369 ,BUSINESS_GROUP_NAME => l_prev_rec.BUSINESS_GROUP_NAME
370 ,P_LIFE_EVENT_DATE => l_prev_rec.P_LIFE_EVENT_DATE
371 ,P_EFFECTIVE_DATE => l_prev_rec.P_EFFECTIVE_DATE
372 ,P_PROC_CD => l_prev_rec.P_PROC_CD
373 ,P_PROGRAM => l_prev_rec.P_PROGRAM
374 ,P_PROGRAM_NUM => l_prev_rec.P_PROGRAM_NUM
375 ,P_PLAN => l_prev_rec.P_PLAN
376 ,P_PLAN_NUM => l_prev_rec.P_PLAN_NUM
377 ,P_LIFE_EVENT_REASON => l_prev_rec.P_LIFE_EVENT_REASON
378 ,P_EMPLOYEE_NUMBER => l_prev_rec.P_EMPLOYEE_NUMBER
379 ,P_NATIONAL_IDENTIFIER => l_prev_rec.P_NATIONAL_IDENTIFIER
380 ,P_FULL_NAME => l_prev_rec.P_FULL_NAME
381 ,P_DATE_OF_BIRTH => l_prev_rec.P_DATE_OF_BIRTH
382 ,P_PERSON_NUM => l_prev_rec.P_PERSON_NUM
383 );
384 --
385 l_sequence := l_sequence + 1;
386 --
387 */
388 update hrdpv_create_enrollment
389 set p_record_typ_cd = 'POST'
390 where batch_id = p_batch_id
391 and batch_line_id = l_prev_rec.batch_line_id;
392 --
393 END IF;
394 --UPDATE STATEMENT
395 --
396 update hrdpv_create_enrollment
397 set user_sequence = l_sequence ,
398 link_value = l_link_value
399 where batch_id = p_batch_id
403 l_sequence := l_sequence + 1 ;
400 and batch_line_id = i.batch_line_id ;
401 --
402 --
404 --
405 l_prev_link := l_link_value ;
406 l_person_num := i.p_person_num;
407 l_emp_num := i.p_employee_number;
408 l_ssn := i.p_national_identifier;
409 l_full_name := i.p_full_name;
410 l_dob := i.p_date_of_birth;
411 l_program := i.p_program;
412 l_program_num := i.p_program_num;
413 l_plan := i.p_plan;
414 l_plan_num := i.p_plan_num;
415 l_record_typ := i.p_record_typ_cd;
416 --
417 l_prev_rec := l_curr_rec ;
418 --
419 end loop;
420 --
421 IF l_prev_rec.P_RECORD_TYP_CD <> 'POST' THEN
422 --
423 IF l_prev_rec.P_PROGRAM IS NOT NULL OR
424 l_prev_rec.P_PROGRAM_NUM IS NOT NULL THEN
425 --
426 l_prev_rec.P_PLAN := null;
427 l_prev_rec.P_PLAN_NUM := null;
428 --
429 END IF ;
430 --
431 --NK
432 --Changes to eliminate summary row in Enrollment Upload Spreadsheet.
433 --Instead of inserting summary row, change the record type of the
434 --last record of the group from 'ENROLL' to 'POST'.
435 /*
436 INSERT_CREATE_ENROLLMENT
437 (BATCH_ID => p_batch_id
438 ,API_MODULE_ID => l_prev_rec.api_module_id
439 ,USER_SEQUENCE => l_sequence
440 ,LINK_VALUE => l_prev_link
441 ,BUSINESS_GROUP_NAME => l_prev_rec.BUSINESS_GROUP_NAME
442 ,P_LIFE_EVENT_DATE => l_prev_rec.P_LIFE_EVENT_DATE
443 ,P_EFFECTIVE_DATE => l_prev_rec.P_EFFECTIVE_DATE
444 ,P_PROC_CD => l_prev_rec.P_PROC_CD
445 ,P_PROGRAM => l_prev_rec.P_PROGRAM
446 ,P_PROGRAM_NUM => l_prev_rec.P_PROGRAM_NUM
447 ,P_PLAN => l_prev_rec.P_PLAN
448 ,P_PLAN_NUM => l_prev_rec.P_PLAN_NUM
449 ,P_LIFE_EVENT_REASON => l_prev_rec.P_LIFE_EVENT_REASON
450 ,P_EMPLOYEE_NUMBER => l_prev_rec.P_EMPLOYEE_NUMBER
451 ,P_NATIONAL_IDENTIFIER => l_prev_rec.P_NATIONAL_IDENTIFIER
452 ,P_FULL_NAME => l_prev_rec.P_FULL_NAME
453 ,P_DATE_OF_BIRTH => l_prev_rec.P_DATE_OF_BIRTH
454 ,P_PERSON_NUM => l_prev_rec.P_PERSON_NUM
455 );
456 */
457 update hrdpv_create_enrollment
458 set p_record_typ_cd = 'POST'
459 where batch_id = p_batch_id
460 and batch_line_id = l_prev_rec.batch_line_id;
461 --
462 END IF;
463 --
464 fnd_file.put_line
465 (which => fnd_file.log,
466 buff => 'Leaving - module_name :pre_create_enrollment' );
467 --
468 end pre_create_enrollment ;
469 --
470 procedure pre_process_dependent
471 (p_batch_id in number default null,
472 p_validate in varchar2 default 'N'
473 ) is
474 --
475 cursor c_choices is
476 select ch.*
477 from hrdpv_process_dependent ch
478 where ch.batch_id = p_batch_id
479 and ch.line_status <> 'C'
480 order by p_person_num,
481 p_employee_number,
482 p_national_identifier,
483 p_full_name,
484 p_date_of_birth,
485 p_program,
486 p_program_num,
487 p_plan,
488 p_plan_num,
489 p_option,
490 p_option_num,
491 p_record_typ_cd
492 for update;
493 --
494 l_link_value hrdpv_process_dependent.link_value%TYPE := 0 ;
495 l_sequence hrdpv_process_dependent.user_sequence%TYPE := 1 ;
496 l_person_num hrdpv_process_dependent.p_person_num%TYPE;
497 l_emp_num hrdpv_process_dependent.p_employee_number%TYPE;
498 l_ssn hrdpv_process_dependent.p_national_identifier%TYPE;
499 l_full_name hrdpv_process_dependent.p_full_name%TYPE;
500 l_dob hrdpv_process_dependent.p_date_of_birth%TYPE;
501 l_program hrdpv_process_dependent.p_program%TYPE;
502 l_program_num hrdpv_process_dependent.p_program_num%TYPE;
503 l_plan hrdpv_process_dependent.p_plan%TYPE;
504 l_plan_num hrdpv_process_dependent.p_plan_num%TYPE;
505 l_record_typ hrdpv_process_dependent.p_record_typ_cd%TYPE ;
506 --
507 l_person_change boolean := true;
508 --
509 l_prev_rec c_choices%ROWTYPE;
510 l_curr_rec c_choices%ROWTYPE;
511 l_prev_link hrdpv_process_dependent.link_value%TYPE ;
512 --
513 begin
514 --
515 /* IMPORTANT to enforce the following assumptions. Otherwise we need to enhance our
516 code to handle those cases.
517 --In a batch data needs to be consistent which means
518 --Person Data - Use either employee_number or SSN or Fullname plus DOB or Person num
519 --Plan design - Use either comp object name or num but can't use num for some plans
520 and name for some other plans.
521 */
522 fnd_file.put_line
523 (which => fnd_file.log,
524 buff => 'Entering - module_name :pre_process_dependent' );
525 --
526 fnd_file.put_line
527 (which => fnd_file.log,
528 buff => 'Batch ID: '||p_batch_id );
529 --
530 --Update Header for ATOMIC_LINKED_CALLS
531 --we need patch 4665288 applied for this.
532 UPDATE hr_pump_batch_headers bh
533 SET bh.ATOMIC_LINKED_CALLS = 'Y'
537 (which => fnd_file.log,
534 WHERE bh.batch_id = p_batch_id ;
535 --
536 fnd_file.put_line
538 buff => 'Updated Header for ATOMIC_LINKED_CALLS');
539 --
540 --
541 UPDATE hrdpv_process_dependent
542 SET P_RECORD_TYP_CD = 'ENROLL'
543 WHERE batch_id = p_batch_id;
544 --
545 --
546
547 for i in c_choices loop
548 --
549 l_curr_rec := i;
550 --
551 l_person_change := true;
552 --
553 IF i.p_person_num IS NOT NULL THEN
554 --
555 IF i.p_person_num = l_person_num THEN
556 l_person_change := false;
557 END IF;
558 --
559 ELSIF i.p_employee_number IS NOT NULL THEN
560 --
561 IF i.p_employee_number = l_emp_num THEN
562 l_person_change := false;
563 END IF;
564 --
565 ELSIF i.p_national_identifier IS NOT NULL THEN
566 --
567 IF i.p_national_identifier = l_ssn THEN
568 l_person_change := false;
569 END IF;
570 --
571 ELSIF i.p_full_name IS NOT NULL AND i.p_date_of_birth IS NOT NULL THEN
572 --
573 IF i.p_full_name = l_prev_rec.p_full_name AND
574 i.p_date_of_birth = l_prev_rec.p_date_of_birth THEN
575 --
576 l_person_change := false;
577 --
578 END IF;
579 --
580 END IF;
581 --
582 IF l_person_change THEN
583 --
584 --Now check if the last person record exists and if the record type is not
585 --POST then create on record for post with the previous record information.
586 --
587 l_link_value := l_link_value + 1;
588 --
589 ELSIF i.p_program is NOT NULL OR i.p_program_num IS NOT NULL THEN
590 --
591 IF i.p_program IS NOT NULL THEN
592 --
593 IF i.p_program <> l_program THEN
594 --If Program is changing and the last record type is not POST
595 --then create a record for POST
596 --
597 l_link_value := l_link_value + 1;
598 --
599 ELSE
600 --
601 IF i.p_plan <> l_prev_rec.p_plan THEN
602 l_link_value := l_link_value + 1;
603 ELSE
604 IF i.p_option IS NOT NULL OR i.p_option_num IS NOT NULL THEN
605 IF i.p_option IS NOT NULL THEN
606 IF i.p_option <> l_prev_rec.p_option THEN
607 l_link_value := l_link_value + 1;
608 END IF;
609 ELSE
610 IF i.p_option_num <> l_prev_rec.p_option_num THEN
611 l_link_value := l_link_value + 1;
612 END IF;
613 END IF;
614 END IF;
615 END IF;
616 --
617 END IF;
618 --
619 ELSE
620 --
621 IF i.p_program_num <> l_program_num THEN
622 --
623 l_link_value := l_link_value + 1;
624 --
625 ELSE
626 --
627 IF i.p_plan <> l_plan THEN
628 l_link_value := l_link_value + 1;
629 ELSE
630 IF i.p_option IS NOT NULL OR i.p_option_num IS NOT NULL THEN
631 IF i.p_option IS NOT NULL THEN
632 IF i.p_option <> l_prev_rec.p_option THEN
633 l_link_value := l_link_value + 1;
634 END IF;
635 ELSE
636 IF i.p_option_num <> l_prev_rec.p_option_num THEN
637 l_link_value := l_link_value + 1;
638 END IF;
639 END IF;
640 END IF;
641 END IF;
642 --
643 END IF;
644 --
645 END IF;
646 --
647 ELSE
648 --
649 IF i.p_plan IS NOT NULL THEN
650 --
651 IF i.p_plan <> l_plan THEN
652 --
653 l_link_value := l_link_value + 1;
654 --
655 ELSE
656 IF i.p_option IS NOT NULL OR i.p_option_num IS NOT NULL THEN
657 IF i.p_option IS NOT NULL THEN
658 IF i.p_option <> l_prev_rec.p_option THEN
659 l_link_value := l_link_value + 1;
660 END IF;
661 ELSE
662 IF i.p_option_num <> l_prev_rec.p_option_num THEN
663 l_link_value := l_link_value + 1;
664 END IF;
665 END IF;
666 END IF;
667 END IF;
668 --
669 ELSE
670 --
671 IF i.p_plan_num <> l_plan_num THEN
672 --
673 l_link_value := l_link_value + 1;
674 --
675 ELSE
676 IF i.p_option IS NOT NULL OR i.p_option_num IS NOT NULL THEN
677 IF i.p_option IS NOT NULL THEN
678 IF i.p_option <> l_prev_rec.p_option THEN
679 l_link_value := l_link_value + 1;
680 END IF;
681 ELSE
682 IF i.p_option_num <> l_prev_rec.p_option_num THEN
683 l_link_value := l_link_value + 1;
684 END IF;
685 END IF;
686 END IF;
687 --
688 END IF;
689 --
690 END IF;
691 --
692 END IF;
693 --
697 --NK
694 IF l_prev_link <> l_link_value AND l_prev_rec.P_RECORD_TYP_CD <> 'POST' THEN
695 --
696 --
698 --Changes to eliminate summary row in Dependent Upload Spreadsheet.
699 --Instead of inserting summary row, change the record type of the
700 --last record of the group from 'ENROLL' to 'POST'.
701 /*
702 INSERT_PROCESS_DEPENDENT
703 (BATCH_ID => p_batch_id
704 ,API_MODULE_ID => l_prev_rec.api_module_id
705 ,USER_SEQUENCE => l_sequence
706 ,LINK_VALUE => l_prev_link
707 ,BUSINESS_GROUP_NAME => l_prev_rec.BUSINESS_GROUP_NAME
708 ,P_LIFE_EVENT_DATE => l_prev_rec.P_LIFE_EVENT_DATE
709 ,P_EFFECTIVE_DATE => l_prev_rec.P_EFFECTIVE_DATE
710 ,P_PROGRAM => l_prev_rec.P_PROGRAM
711 ,P_PROGRAM_NUM => l_prev_rec.P_PROGRAM_NUM
712 ,P_PLAN => l_prev_rec.P_PLAN
713 ,P_PLAN_NUM => l_prev_rec.P_PLAN_NUM
714 ,P_OPTION => l_prev_rec.P_OPTION
715 ,P_OPTION_NUM => l_prev_rec.P_OPTION_NUM
716 ,P_LIFE_EVENT_REASON => l_prev_rec.P_LIFE_EVENT_REASON
717 ,P_EMPLOYEE_NUMBER => l_prev_rec.P_EMPLOYEE_NUMBER
718 ,P_NATIONAL_IDENTIFIER => l_prev_rec.P_NATIONAL_IDENTIFIER
719 ,P_FULL_NAME => l_prev_rec.P_FULL_NAME
720 ,P_DATE_OF_BIRTH => l_prev_rec.P_DATE_OF_BIRTH
721 ,P_PERSON_NUM => l_prev_rec.P_PERSON_NUM
722 );
723 --
724 l_sequence := l_sequence + 1;
725 --
726 */
727 update hrdpv_process_dependent
728 set p_record_typ_cd = 'POST'
729 where batch_id = p_batch_id
730 and batch_line_id = l_prev_rec.batch_line_id;
731 --
732 END IF;
733 --UPDATE STATEMENT
734 --
735 update hrdpv_process_dependent
736 set user_sequence = l_sequence ,
737 link_value = l_link_value
738 where batch_id = p_batch_id
739 and batch_line_id = i.batch_line_id ;
740 --
741 --
742 l_sequence := l_sequence + 1 ;
743 --
744 l_prev_link := l_link_value ;
745 l_person_num := i.p_person_num;
746 l_emp_num := i.p_employee_number;
747 l_ssn := i.p_national_identifier;
748 l_full_name := i.p_full_name;
749 l_dob := i.p_date_of_birth;
750 l_program := i.p_program;
751 l_program_num := i.p_program_num;
752 l_plan := i.p_plan;
753 l_plan_num := i.p_plan_num;
754 l_record_typ := i.p_record_typ_cd;
755 --
756 l_prev_rec := l_curr_rec ;
757 --
758 end loop;
759 --
760 --NK
761 --Changes to eliminate summary row in Dependent Upload Spreadsheet.
762 --Instead of inserting summary row, change the record type of the
763 --last record of the group from 'ENROLL' to 'POST'.
764
765 IF l_prev_rec.P_RECORD_TYP_CD <> 'POST' THEN
766 /*
767 --
768 --
769 -- IF l_prev_rec.P_PROGRAM IS NOT NULL OR
770 -- l_prev_rec.P_PROGRAM_NUM IS NOT NULL THEN
771 -- --
772 -- l_prev_rec.P_PLAN := null;
773 -- l_prev_rec.P_PLAN_NUM := null;
774 -- --
775 -- END IF ;
776 --
777 --
778 INSERT_PROCESS_DEPENDENT
779 (BATCH_ID => p_batch_id
780 ,API_MODULE_ID => l_prev_rec.api_module_id
781 ,USER_SEQUENCE => l_sequence
782 ,LINK_VALUE => l_prev_link
783 ,BUSINESS_GROUP_NAME => l_prev_rec.BUSINESS_GROUP_NAME
784 ,P_LIFE_EVENT_DATE => l_prev_rec.P_LIFE_EVENT_DATE
785 ,P_EFFECTIVE_DATE => l_prev_rec.P_EFFECTIVE_DATE
786 ,P_PROGRAM => l_prev_rec.P_PROGRAM
787 ,P_PROGRAM_NUM => l_prev_rec.P_PROGRAM_NUM
788 ,P_PLAN => l_prev_rec.P_PLAN
789 ,P_PLAN_NUM => l_prev_rec.P_PLAN_NUM
790 ,P_OPTION => l_prev_rec.P_OPTION
791 ,P_OPTION_NUM => l_prev_rec.P_OPTION_NUM
792 ,P_LIFE_EVENT_REASON => l_prev_rec.P_LIFE_EVENT_REASON
793 ,P_EMPLOYEE_NUMBER => l_prev_rec.P_EMPLOYEE_NUMBER
794 ,P_NATIONAL_IDENTIFIER => l_prev_rec.P_NATIONAL_IDENTIFIER
795 ,P_FULL_NAME => l_prev_rec.P_FULL_NAME
796 ,P_DATE_OF_BIRTH => l_prev_rec.P_DATE_OF_BIRTH
797 ,P_PERSON_NUM => l_prev_rec.P_PERSON_NUM
798 );
799 */
800 update hrdpv_process_dependent
801 set p_record_typ_cd = 'POST'
802 where batch_id = p_batch_id
803 and batch_line_id = l_prev_rec.batch_line_id;
804 --
805 END IF;
806 --
807
808 fnd_file.put_line
809 (which => fnd_file.log,
810 buff => 'Leaving - module_name :pre_process_dependent' );
811 --
812 end pre_process_dependent ;
813 --
814 -- --------------------------------------------------------------------------------
815 -- |-----------------------------< PROCESS_BENEFICIARY >-------------------------|
816 -- -------------------------------------------------------------------------------+
817 procedure pre_process_beneficiary
818 (p_batch_id in number default null,
819 p_validate in varchar2 default 'N'
820 ) is
821 --
825 where ch.batch_id = p_batch_id
822 cursor c_choices is
823 select ch.*
824 from hrdpv_process_beneficiary ch
826 and ch.line_status <> 'C'
827 order by p_person_num,
828 p_employee_number,
829 p_national_identifier,
830 p_full_name,
831 p_date_of_birth,
832 p_program,
833 p_program_num,
834 p_plan,
835 p_plan_num,
836 p_option,
837 p_option_num,
838 p_record_typ_cd
839 for update;
840 --
841 l_link_value hrdpv_process_dependent.link_value%TYPE := 0 ;
842 l_sequence hrdpv_process_dependent.user_sequence%TYPE := 1 ;
843 l_person_num hrdpv_process_dependent.p_person_num%TYPE;
844 l_emp_num hrdpv_process_dependent.p_employee_number%TYPE;
845 l_ssn hrdpv_process_dependent.p_national_identifier%TYPE;
846 l_full_name hrdpv_process_dependent.p_full_name%TYPE;
847 l_dob hrdpv_process_dependent.p_date_of_birth%TYPE;
848 l_program hrdpv_process_dependent.p_program%TYPE;
849 l_program_num hrdpv_process_dependent.p_program_num%TYPE;
850 l_plan hrdpv_process_dependent.p_plan%TYPE;
851 l_plan_num hrdpv_process_dependent.p_plan_num%TYPE;
852 l_record_typ hrdpv_process_dependent.p_record_typ_cd%TYPE ;
853 --
854 l_person_change boolean := true;
855 --
856 l_prev_rec c_choices%ROWTYPE;
857 l_curr_rec c_choices%ROWTYPE;
858 l_prev_link hrdpv_process_dependent.link_value%TYPE ;
859 --
860 begin
861 --
862 /* IMPORTANT to enforce the following assumptions. Otherwise we need to enhance our
863 code to handle those cases.
864 --In a batch data needs to be consistent which means
865 --Person Data - Use either employee_number or SSN or Fullname plus DOB or Person num
866 --Plan design - Use either comp object name or num but can't use num for some plans
867 and name for some other plans.
868 */
869 fnd_file.put_line
870 (which => fnd_file.log,
871 buff => 'Entering - module_name :pre_process_beneficiary' );
872 --
873 fnd_file.put_line
874 (which => fnd_file.log,
875 buff => 'Batch ID: '||p_batch_id );
876 --
877 --Update Header for ATOMIC_LINKED_CALLS
878 --we need patch 4665288 applied for this.
879 UPDATE hr_pump_batch_headers bh
880 SET bh.ATOMIC_LINKED_CALLS = 'Y'
881 WHERE bh.batch_id = p_batch_id ;
882 --
883 fnd_file.put_line
884 (which => fnd_file.log,
885 buff => 'Updated Header for ATOMIC_LINKED_CALLS');
886 --
887 --
888 UPDATE hrdpv_process_beneficiary
889 SET P_RECORD_TYP_CD = 'ENROLL'
890 WHERE batch_id = p_batch_id;
891 --
892 --
893
894 for i in c_choices loop
895 --
896 l_curr_rec := i;
897 --
898 l_person_change := true;
899 --
900 IF i.p_person_num IS NOT NULL THEN
901 --
902 IF i.p_person_num = l_person_num THEN
903 l_person_change := false;
904 END IF;
905 --
906 ELSIF i.p_employee_number IS NOT NULL THEN
907 --
908 IF i.p_employee_number = l_emp_num THEN
909 l_person_change := false;
910 END IF;
911 --
912 ELSIF i.p_national_identifier IS NOT NULL THEN
913 --
914 IF i.p_national_identifier = l_ssn THEN
915 l_person_change := false;
916 END IF;
917 --
918 ELSIF i.p_full_name IS NOT NULL AND i.p_date_of_birth IS NOT NULL THEN
919 --
920 IF i.p_full_name = l_prev_rec.p_full_name AND
921 i.p_date_of_birth = l_prev_rec.p_date_of_birth THEN
922 --
923 l_person_change := false;
924 --
925 END IF;
926 --
927 END IF;
928 --
929 IF l_person_change THEN
930 --
931 --Now check if the last person record exists and if the record type is not
932 --POST then create on record for post with the previous record information.
933 --
934 l_link_value := l_link_value + 1;
935 --
936 ELSIF i.p_program is NOT NULL OR i.p_program_num IS NOT NULL THEN
937 --
938 IF i.p_program IS NOT NULL THEN
939 --
940 IF i.p_program <> l_program THEN
941 --If Program is changing and the last record type is not POST
942 --then create a record for POST
943 --
944 l_link_value := l_link_value + 1;
945 --
946 ELSE
947 --
948 IF i.p_plan <> l_prev_rec.p_plan THEN
949 l_link_value := l_link_value + 1;
950 ELSE
951 IF i.p_option IS NOT NULL OR i.p_option_num IS NOT NULL THEN
952 IF i.p_option IS NOT NULL THEN
953 IF i.p_option <> l_prev_rec.p_option THEN
954 l_link_value := l_link_value + 1;
955 END IF;
956 ELSE
957 IF i.p_option_num <> l_prev_rec.p_option_num THEN
958 l_link_value := l_link_value + 1;
959 END IF;
960 END IF;
961 END IF;
962 END IF;
963 --
964 END IF;
965 --
966 ELSE
967 --
968 IF i.p_program_num <> l_program_num THEN
969 --
970 l_link_value := l_link_value + 1;
971 --
975 l_link_value := l_link_value + 1;
972 ELSE
973 --
974 IF i.p_plan <> l_plan THEN
976 ELSE
977 IF i.p_option IS NOT NULL OR i.p_option_num IS NOT NULL THEN
978 IF i.p_option IS NOT NULL THEN
979 IF i.p_option <> l_prev_rec.p_option THEN
980 l_link_value := l_link_value + 1;
981 END IF;
982 ELSE
983 IF i.p_option_num <> l_prev_rec.p_option_num THEN
984 l_link_value := l_link_value + 1;
985 END IF;
986 END IF;
987 END IF;
988 END IF;
989 --
990 END IF;
991 --
992 END IF;
993 --
994 ELSE
995 --
996 IF i.p_plan IS NOT NULL THEN
997 --
998 IF i.p_plan <> l_plan THEN
999 --
1000 l_link_value := l_link_value + 1;
1001 --
1002 ELSE
1003 IF i.p_option IS NOT NULL OR i.p_option_num IS NOT NULL THEN
1004 IF i.p_option IS NOT NULL THEN
1005 IF i.p_option <> l_prev_rec.p_option THEN
1006 l_link_value := l_link_value + 1;
1007 END IF;
1008 ELSE
1009 IF i.p_option_num <> l_prev_rec.p_option_num THEN
1010 l_link_value := l_link_value + 1;
1011 END IF;
1012 END IF;
1013 END IF;
1014 END IF;
1015 --
1016 ELSE
1017 --
1018 IF i.p_plan_num <> l_plan_num THEN
1019 --
1020 l_link_value := l_link_value + 1;
1021 --
1022 ELSE
1023 IF i.p_option IS NOT NULL OR i.p_option_num IS NOT NULL THEN
1024 IF i.p_option IS NOT NULL THEN
1025 IF i.p_option <> l_prev_rec.p_option THEN
1026 l_link_value := l_link_value + 1;
1027 END IF;
1028 ELSE
1029 IF i.p_option_num <> l_prev_rec.p_option_num THEN
1030 l_link_value := l_link_value + 1;
1031 END IF;
1032 END IF;
1033 END IF;
1034 --
1035 END IF;
1036 --
1037 END IF;
1038 --
1039 END IF;
1040 --
1041 IF l_prev_link <> l_link_value AND l_prev_rec.P_RECORD_TYP_CD <> 'POST' THEN
1042 --
1043 update hrdpv_process_beneficiary
1044 set p_record_typ_cd = 'POST'
1045 where batch_id = p_batch_id
1046 and batch_line_id = l_prev_rec.batch_line_id;
1047 --
1048 END IF;
1049 --UPDATE STATEMENT
1050 --
1051 update hrdpv_process_beneficiary
1052 set user_sequence = l_sequence ,
1053 link_value = l_link_value
1054 where batch_id = p_batch_id
1055 and batch_line_id = i.batch_line_id ;
1056 --
1057 --
1058 l_sequence := l_sequence + 1 ;
1059 --
1060 l_prev_link := l_link_value ;
1061 l_person_num := i.p_person_num;
1062 l_emp_num := i.p_employee_number;
1063 l_ssn := i.p_national_identifier;
1064 l_full_name := i.p_full_name;
1065 l_dob := i.p_date_of_birth;
1066 l_program := i.p_program;
1067 l_program_num := i.p_program_num;
1068 l_plan := i.p_plan;
1069 l_plan_num := i.p_plan_num;
1070 l_record_typ := i.p_record_typ_cd;
1071 --
1072 l_prev_rec := l_curr_rec ;
1073 --
1074 end loop;
1075 --
1076 IF l_prev_rec.P_RECORD_TYP_CD <> 'POST' THEN
1077 --
1078 update hrdpv_process_beneficiary
1079 set p_record_typ_cd = 'POST'
1080 where batch_id = p_batch_id
1081 and batch_line_id = l_prev_rec.batch_line_id;
1082 --
1083 END IF;
1084 --
1085
1086 fnd_file.put_line
1087 (which => fnd_file.log,
1088 buff => 'Leaving - module_name :pre_process_beneficiary' );
1089 --
1090 end pre_process_beneficiary;
1091 --
1092 end ben_pre_datapump_process;