[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ISDF_ARCHIVE_PKG
Source
1 package body pay_jp_isdf_archive_pkg as
2 /* $Header: pyjpisfc.pkb 120.13.12020000.2 2012/09/14 09:31:56 dduvvuri ship $ */
3 --
4 c_package constant varchar2(31) := 'pay_jp_isdf_archive_pkg.';
5 c_org_iv_name constant ff_database_items.user_name%type := 'COM_ITX_INFO_WITHHOLD_AGENT_ENTRY_VALUE';
6 c_tax_type_iv_name constant ff_database_items.user_name%type := 'COM_ITX_INFO_ITX_TYPE_ENTRY_VALUE';
7 --
8 c_com_calc_dpnt_elm_id constant number := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_PROC', null, 'JP');
9 c_sp_type_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_calc_dpnt_elm_id, 'SPOUSE_TYPE');
10 c_widow_type_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_calc_dpnt_elm_id, 'WIDOW_TYPE');
11 --
12 c_com_itax_info_elm_id constant number := hr_jp_id_pkg.element_type_id('COM_ITX_INFO', null, 'JP');
13 c_tax_type_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_itax_info_elm_id, 'ITX_TYPE');
14 --
15 c_isdf_ins_elm_id constant number := hr_jp_id_pkg.element_type_id('YEA_INS_PREM_EXM_DECLARE_INFO', null, 'JP');
16 c_life_gen_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'GEN_LIFE_INS_PREM');
17 c_life_pens_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'INDIVIDUAL_PENSION_PREM');
18 c_nonlife_long_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'LONG_TERM_NONLIFE_INS_PREM');
19 c_nonlife_short_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'SHORT_TERM_NONLIFE_INS_PREM');
20 c_earthquake_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'EARTHQUAKE_INS_PREM');
21 c_life_gen_new_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'GEN_LIFE_INS_PREM_2012');
22 c_life_pens_new_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'INDIVIDUAL_PENSION_PREM_2012');
23 c_care_med_new_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'CARE_INS_PREM_2012');
24 --
25 c_isdf_is_elm_id constant number := hr_jp_id_pkg.element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', null, 'JP');
26 c_social_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'DECLARE_SI_PREM');
27 c_mutual_aid_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'SMALL_COMPANY_MUTUAL_AID_PREM');
28 c_spouse_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'SPOUSE_INCOME');
29 c_sp_dct_exclude_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'SPOUSE_SP_EXM_EXCLUDE_FLAG');
30 c_national_pens_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'NATIONAL_PENSION_PREM');
31 --
32 c_st_upd_date_2007 constant date := to_date('2007/01/01','YYYY/MM/DD');
33 --
34 g_debug boolean := hr_utility.debug_enabled;
35 g_business_group_id number;
36 g_legislation_code per_business_groups_perf.legislation_code%type;
37 g_payroll_action_id number;
38 g_assignment_action_id number;
39 g_assignment_id number;
40 g_effective_date date;
41 g_payroll_id number;
42 g_organization_id number;
43 g_assignment_set_id number;
44 g_process_assignments_flag varchar2(1);
45 g_bg_itax_dpnt_ref_type varchar2(150);
46 g_asg_rec hr_jp_ast_utility_pkg.t_asg_rec;
47 --
48 type t_number_tbl is table of number index by binary_integer;
49 --
50 -- sequence of process.
51 -- 1. range_cursor/deinitialization_code (inc. init_pact, archive_pact) <= deinitialization_code is invoked in mark-for-retry instead of range_cursor
52 -- 2. assignment_action_creation (inc. init_pact) <= invoked by each population, reset global variable in case of multiple threads.
53 -- 3. archinit (inc. init_pact) <= invoked by end process of each threads)
54 -- 4. archive_data (inc. init_assact, archive_assact, post_assact) <= invoked by each population
55 -- -------------------------------------------------------------------------
56 -- init_pact
57 -- -------------------------------------------------------------------------
58 procedure init_pact(
59 p_payroll_action_id in number)
60 is
61 --
62 l_proc varchar2(80) := c_package||'init_pact';
63 --
64 cursor csr_action
65 is
66 select /*+ ORDERED */
67 ppa.business_group_id,
68 ppa.effective_date,
69 ppa.legislative_parameters,
70 pbg.legislation_code
71 from pay_payroll_actions ppa,
72 per_business_groups_perf pbg
73 where ppa.payroll_action_id = p_payroll_action_id
74 and pbg.business_group_id = ppa.business_group_id;
75 --
76 cursor csr_bg_itax_dpnt_ref_type
77 is
78 select /*+ ORDERED */
79 nvl(nvl(pp.prl_information1, hoi.org_information2),'CTR_EE')
80 from /* Payroll and Business Group details */
81 pay_all_payrolls_f pp,
82 hr_organization_information hoi
83 where pp.payroll_id = g_payroll_id
84 and g_effective_date
85 between pp.effective_start_date and pp.effective_end_date
86 and hoi.organization_id(+) = pp.business_group_id
87 and hoi.org_information_context(+) = 'JP_BUSINESS_GROUP_INFO';
88 --
89 l_csr_action csr_action%rowtype;
90 --
91 begin
92 --
93 if g_debug then
94 hr_utility.set_location(l_proc,0);
95 end if;
96 --
97 if g_payroll_action_id is null
98 or g_payroll_action_id <> p_payroll_action_id then
99 --
100 if g_debug then
101 hr_utility.set_location(l_proc,10);
102 hr_utility.trace('no cache : g_pact_id('||g_payroll_action_id||'),p_pact_id('||p_payroll_action_id||')');
103 end if;
104 --
105 open csr_action;
106 fetch csr_action into l_csr_action;
107 if csr_action%notfound then
108 close csr_action;
109 fnd_message.set_name('PAY','PAY_34985_INVALID_PAY_ACTION');
110 fnd_message.raise_error;
111 end if;
112 close csr_action;
113 --
114 g_payroll_action_id := p_payroll_action_id;
115 g_effective_date := l_csr_action.effective_date;
116 g_business_group_id := l_csr_action.business_group_id;
117 g_legislation_code := l_csr_action.legislation_code;
118 g_payroll_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ID',l_csr_action.legislative_parameters));
119 g_organization_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ORGANIZATION_ID',l_csr_action.legislative_parameters));
120 g_assignment_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',l_csr_action.legislative_parameters));
121 g_process_assignments_flag := pay_core_utils.get_parameter('PROCESS_ASSIGNMENTS_FLAG',l_csr_action.legislative_parameters);
122 g_archive_default_flag := pay_core_utils.get_parameter('ARCHIVE_DEFAULT_FLAG',l_csr_action.legislative_parameters);
123 g_copy_archive_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('COPY_ARCHIVE_PACT_ID',l_csr_action.legislative_parameters));
124 --
125 open csr_bg_itax_dpnt_ref_type;
126 fetch csr_bg_itax_dpnt_ref_type into g_bg_itax_dpnt_ref_type;
127 close csr_bg_itax_dpnt_ref_type;
128 --
129 if g_assignment_set_id is not null then
130 --
131 if g_debug then
132 hr_utility.set_location(l_proc,20);
133 hr_utility.trace('assignment set : '||g_assignment_set_id);
134 end if;
135 --
136 hr_jp_ast_utility_pkg.pay_asgs(
137 p_payroll_id => g_payroll_id,
138 p_effective_date => g_effective_date,
139 p_start_date => g_effective_date,
140 p_end_date => g_effective_date,
141 p_assignment_set_id => g_assignment_set_id,
142 p_asg_rec => g_asg_rec);
143 --
144 if g_debug then
145 hr_utility.set_location(l_proc,30);
146 hr_utility.trace('inclusive assignment count : '||g_asg_rec.assignment_id_tbl.count);
147 end if;
148 --
149 end if;
150 --
151 end if;
152 --
153 if g_debug then
154 hr_utility.trace('payroll_action_id : '||g_payroll_action_id);
155 hr_utility.trace('business_group_id : '||g_business_group_id);
156 hr_utility.trace('effective_date : '||g_effective_date);
157 hr_utility.trace('legislation_code : '||g_legislation_code);
158 hr_utility.trace('payroll_id : '||g_payroll_id);
159 hr_utility.trace('organization_id : '||g_organization_id);
160 hr_utility.trace('assignment_set_id : '||g_assignment_set_id);
161 hr_utility.trace('process_assignments_flag : '||g_process_assignments_flag);
162 hr_utility.trace('archive_default_flag : '||g_archive_default_flag);
163 hr_utility.trace('copy_archive_pact_id : '||g_copy_archive_pact_id);
164 hr_utility.trace('bg_itax_dpnt_ref_type : '||g_bg_itax_dpnt_ref_type);
165 end if;
166 --
167 if g_debug then
168 hr_utility.set_location(l_proc,1000);
169 end if;
170 --
171 end init_pact;
172 --
173 -- -------------------------------------------------------------------------
174 -- archive_pact
175 -- -------------------------------------------------------------------------
176 procedure archive_pact(
177 p_payroll_action_id in number)
178 is
179 --
180 l_proc varchar2(80) := c_package||'archive_pact';
181 --
182 l_object_version_number number;
183 l_validate_pact varchar2(1);
184 --
185 cursor csr_validate_pact
186 is
187 select 'Y'
188 from pay_jp_isdf_pact_v
189 where payroll_action_id = p_payroll_action_id;
190 --
191 cursor csr_org
192 is
193 select /*+ ORDERED */
194 hoi2.org_information1 tax_office_name,
195 hoi1.org_information1 salary_payer_name,
196 hoi1.org_information6||hoi1.org_information7||hoi1.org_information8 salary_payer_address
197 from hr_all_organization_units hou,
198 hr_organization_information hoi1,
199 hr_organization_information hoi2
200 where hou.organization_id = g_organization_id
201 and hoi1.organization_id(+) = hou.organization_id
202 and hoi1.org_information_context(+) = 'JP_TAX_SWOT_INFO'
203 and hoi2.organization_id(+) = hou.organization_id
204 and hoi2.org_information_context(+) = 'JP_ITAX_WITHHELD_INFO';
205 --
206 l_csr_org csr_org%rowtype;
207 --
208 begin
209 --
210 if g_debug then
211 hr_utility.set_location(l_proc,0);
212 end if;
213 --
214 -- no create pact in mark for retry.
215 --
216 open csr_validate_pact;
217 fetch csr_validate_pact into l_validate_pact;
218 --
219 if csr_validate_pact%notfound then
220 --
221 if g_debug then
222 hr_utility.set_location(l_proc,10);
223 end if;
224 --
225 open csr_org;
226 fetch csr_org into l_csr_org;
227 --
228 if csr_org%notfound then
229 fnd_message.set_name('PAY','PAY_JP_INVALID_SWOT');
230 fnd_message.raise_error;
231 end if;
232 --
233 close csr_org;
234 --
235 if g_debug then
236 hr_utility.set_location(l_proc,20);
237 hr_utility.trace('tax_office_name : '||l_csr_org.tax_office_name);
238 hr_utility.trace('salary_payer_name : '||l_csr_org.salary_payer_name);
239 hr_utility.trace('salary_payer_address : '||l_csr_org.salary_payer_address);
240 hr_utility.trace('start create_pact');
241 end if;
242 --
243 pay_jp_isdf_dml_pkg.create_pact(
244 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
245 p_payroll_action_id => p_payroll_action_id,
246 p_action_context_type => 'PA',
247 p_effective_date => g_effective_date,
248 p_action_information_category => 'JP_ISDF_PACT',
249 p_payroll_id => g_payroll_id,
250 p_organization_id => g_organization_id,
251 p_assignment_set_id => g_assignment_set_id,
252 p_submission_period_status => 'C',
253 p_submission_start_date => null,
254 p_submission_end_date => null,
255 p_tax_office_name => l_csr_org.tax_office_name,
256 p_salary_payer_name => l_csr_org.salary_payer_name,
257 p_salary_payer_address => l_csr_org.salary_payer_address,
258 p_object_version_number => l_object_version_number);
259 --
260 if g_debug then
261 hr_utility.trace('end create_pact');
262 hr_utility.set_location(l_proc,30);
263 end if;
264 --
265 end if;
266 close csr_validate_pact;
267 --
268 if g_debug then
269 hr_utility.set_location(l_proc,1000);
270 end if;
271 --
272 end archive_pact;
273 --
274 -- -------------------------------------------------------------------------
275 -- range_cursor
276 -- -------------------------------------------------------------------------
277 procedure range_cursor(
278 p_payroll_action_id in number,
279 p_sqlstr out nocopy varchar2)
280 is
281 --
282 l_proc varchar2(80) := c_package||'range_cursor';
283 --
284 begin
285 --
286 if g_debug then
287 hr_utility.set_location(l_proc,0);
288 end if;
289 --
290 init_pact(p_payroll_action_id);
291 --
292 if g_debug then
293 hr_utility.set_location(l_proc,10);
294 end if;
295 --
296 archive_pact(p_payroll_action_id);
297 --
298 if g_debug then
299 hr_utility.set_location(l_proc,20);
300 end if;
301 --
302 -- no create assact when process assignments flag is set.
303 --
304 if g_process_assignments_flag = 'N' then
305 --
306 if g_debug then
307 hr_utility.set_location(l_proc,30);
308 end if;
309 --
310 p_sqlstr :=
311 'select 1
312 from dual
313 where :payroll_action_id < 0';
314 --
315 else
316 --
317 if g_debug then
318 hr_utility.set_location(l_proc,40);
319 end if;
320 --
321 p_sqlstr :=
322 'select /*+ ORDERED */
323 distinct pp.person_id
324 from pay_payroll_actions ppa,
325 per_all_people_f pp
326 where ppa.payroll_action_id = :payroll_action_id
327 and pp.business_group_id = ppa.business_group_id + 0
328 order by pp.person_id';
329 --
330 end if;
331 --
332 if g_debug then
333 hr_utility.set_location(l_proc,1000);
334 end if;
335 --
336 end range_cursor;
337 --
338 -- -------------------------------------------------------------------------
339 -- assignment_action_creation
340 -- -------------------------------------------------------------------------
341 procedure assignment_action_creation(
342 p_payroll_action_id in number,
343 p_start_person_id in number,
344 p_end_person_id in number,
345 p_chunk_number in number)
346 is
347 --
348 l_proc varchar2(80) := c_package||'assignment_action_creation';
349 l_debug_cnt number := 0;
350 --
351 l_tax_type pay_element_entry_values_f.screen_entry_value%type;
352 l_organization_id number;
353 l_assignment_action_id number;
354 l_assignment_id number;
355 --
356 cursor csr_proc_ass
357 is
358 select /*+ ORDERED */
359 pa.assignment_id
360 from per_periods_of_service ppos,
361 per_all_assignments_f pa
362 where ppos.person_id
363 between p_start_person_id and p_end_person_id
364 and ppos.business_group_id + 0 = g_business_group_id
365 and g_effective_date
366 between ppos.date_start and nvl(ppos.final_process_date,g_effective_date)
367 and pa.period_of_service_id = ppos.period_of_service_id
368 and pa.primary_flag = 'Y' /*Added by JSAJJA as per Bug No 8435426*/
369 and g_effective_date
370 between pa.effective_start_date and pa.effective_end_date
371 and pa.payroll_id + 0 = g_payroll_id;
372 --
373 l_csr_proc_ass csr_proc_ass%rowtype;
374 --
375 begin
376 --
377 if g_debug then
378 hr_utility.set_location(l_proc,0);
379 end if;
380 --
381 -- Reset global variable in case of multiple threads.
382 init_pact(p_payroll_action_id);
383 --
384 if g_debug then
385 hr_utility.set_location(l_proc,10);
386 end if;
387 --
388 open csr_proc_ass;
389 loop
390 --
391 fetch csr_proc_ass into l_csr_proc_ass;
392 exit when csr_proc_ass%notfound;
393 --
394 l_assignment_id := null;
395 if g_assignment_set_id is not null then
396 --
397 if g_debug then
398 hr_utility.set_location(l_proc,20);
399 hr_utility.trace('assignment set : '||g_assignment_set_id);
400 end if;
401 --
402 <<ass_exist>>
403 for i in 1..g_asg_rec.assignment_id_tbl.count loop
404 --
405 if l_csr_proc_ass.assignment_id = g_asg_rec.assignment_id_tbl(i) then
406 l_assignment_id := l_csr_proc_ass.assignment_id;
407 exit ass_exist;
408 end if;
409 --
410 end loop ass_exist;
411 --
412 if g_debug then
413 hr_utility.set_location(l_proc,30);
414 hr_utility.trace('assignment id : '||l_assignment_id);
415 end if;
416 --
417 else
418 --
419 l_assignment_id := l_csr_proc_ass.assignment_id;
420 --
421 if g_debug then
422 hr_utility.set_location(l_proc,40);
423 hr_utility.trace('assignment id : '||l_assignment_id);
424 end if;
425 --
426 end if;
427 --
428 if l_assignment_id is not null then
429 --
430 if g_debug then
431 hr_utility.set_location(l_proc,50);
432 end if;
433 --
434 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(g_effective_date));
435 pay_balance_pkg.set_context('ASSIGNMENT_ID',fnd_number.number_to_canonical(l_assignment_id));
436 l_organization_id := pay_balance_pkg.run_db_item(c_org_iv_name,g_business_group_id,g_legislation_code);
437 --
438 if g_debug and l_debug_cnt < 1 then
439 l_debug_cnt := l_debug_cnt + 1;
440 hr_utility.set_location(l_proc,60);
441 hr_utility.trace('diff org : g_org_id('||g_organization_id||'),l_org_id('||l_organization_id||')');
442 end if;
443 --
444 if l_organization_id = g_organization_id then
445 --
446 l_tax_type := pay_balance_pkg.run_db_item(c_tax_type_iv_name,g_business_group_id,g_legislation_code);
447 --
448 if g_debug then
449 hr_utility.set_location(l_proc,70);
450 hr_utility.trace('tax type : '||l_tax_type);
451 end if;
452 --
453 -- target only kou by legislative rule.
454 if l_tax_type in ('M_KOU','D_KOU') then
455 --
456 if g_debug then
457 hr_utility.set_location(l_proc,80);
458 hr_utility.trace('assignment_id : '||l_assignment_id);
459 end if;
460 --
461 select pay_assignment_actions_s.nextval
462 into l_assignment_action_id
463 from dual;
464 --
465 hr_nonrun_asact.insact(
466 lockingactid => l_assignment_action_id,
467 assignid => l_assignment_id,
468 pactid => p_payroll_action_id,
469 chunk => p_chunk_number,
470 greid => null);
471 --
472 if g_debug then
473 hr_utility.set_location(l_proc,90);
474 hr_utility.trace('assignment_action_id : '||l_assignment_action_id);
475 end if;
476 --
477 end if;
478 --
479 end if;
480 --
481 end if;
482 --
483 end loop;
484 close csr_proc_ass;
485 --
486 if g_debug then
487 hr_utility.set_location(l_proc,1000);
488 end if;
489 --
490 end assignment_action_creation;
491 --
492 -- -------------------------------------------------------------------------
493 -- archinit
494 -- -------------------------------------------------------------------------
495 procedure archinit(
496 p_payroll_action_id in number)
497 is
498 l_proc varchar2(80) := c_package||'archinit';
499 begin
500 --
501 if g_debug then
502 hr_utility.set_location(l_proc,0);
503 end if;
504 --
505 init_pact(p_payroll_action_id);
506 ff_exec.reset_caches;
507 --
508 if g_debug then
509 hr_utility.set_location(l_proc,1000);
510 end if;
511 --
512 end archinit;
513 --
514 -- -------------------------------------------------------------------------
515 -- init_assact
516 -- -------------------------------------------------------------------------
517 procedure init_assact(
518 p_assignment_action_id in number,
519 p_assignment_id in number)
520 is
521 --
522 l_proc varchar2(80) := c_package||'init_assact';
523 --
524 begin
525 --
526 if g_debug then
527 hr_utility.set_location(l_proc,0);
528 end if;
529 --
530 if g_assignment_action_id is null
531 or g_assignment_action_id <> p_assignment_action_id then
532 --
533 if g_debug then
534 hr_utility.set_location(l_proc,10);
535 hr_utility.trace('no cache : g_assact_id('||g_assignment_action_id||'),p_assact_id('||p_assignment_action_id||')');
536 end if;
537 --
538 g_assignment_action_id := p_assignment_action_id;
539 g_assignment_id := p_assignment_id;
540 --
541 end if;
542 --
543 if g_debug then
544 hr_utility.trace('assignment_action_id : '||g_assignment_action_id);
545 hr_utility.trace('assignment_id : '||g_assignment_id);
546 hr_utility.set_location(l_proc,1000);
547 end if;
548 --
549 end init_assact;
550 --
551 -- -------------------------------------------------------------------------
552 -- calc_li_annual_prem
553 -- -------------------------------------------------------------------------
554 procedure calc_li_annual_prem(
555 p_ins_info_rec in t_li_info_rec,
556 p_lig_prem out nocopy number,
557 p_lip_prem out nocopy number,
558 p_cmi_prem out nocopy number,
559 p_message out nocopy varchar2)
560 is
561 --
562 l_proc varchar2(80) := c_package||'calc_li_annual_prem';
563 --
564 l_inputs ff_exec.inputs_t;
565 l_outputs ff_exec.outputs_t;
566 l_formula_id number;
567 --
568 cursor csr_ff
569 is
570 select ff.formula_id
571 from ff_formulas_f ff
572 where ff.formula_name = p_ins_info_rec.calc_prem_ff
573 and nvl(ff.business_group_id,g_business_group_id) = g_business_group_id
574 and nvl(ff.legislation_code,g_legislation_code) = g_legislation_code
575 and g_effective_date
576 between ff.effective_start_date and ff.effective_end_date;
577 --
578 begin
579 --
580 if g_debug then
581 hr_utility.set_location(l_proc,0);
582 end if;
583 --
584 open csr_ff;
585 fetch csr_ff into l_formula_id;
586 close csr_ff;
587 --
588 if g_debug then
589 hr_utility.set_location(l_proc,10);
590 hr_utility.trace('formula_id : '||l_formula_id);
591 end if;
592 --
593 if l_formula_id is not null then
594 --
595 ff_exec.init_formula
596 (p_formula_id => l_formula_id,
597 p_effective_date => g_effective_date,
598 p_inputs => l_inputs,
599 p_outputs => l_outputs);
600 --
601 if g_debug then
602 hr_utility.set_location(l_proc,20);
603 end if;
604 --
605 if l_inputs.count > 1 then
606 --
607 for i in l_inputs.first..l_inputs.last loop
608 --
609 if l_inputs(i).name = 'BUSINESS_GROUP_ID' then
610 l_inputs(i).value := fnd_number.number_to_canonical(g_business_group_id);
611 elsif l_inputs(i).name = 'PAYROLL_ID' then
612 l_inputs(i).value := fnd_number.number_to_canonical(g_payroll_id);
613 elsif l_inputs(i).name = 'PAYROLL_ACTION_ID' then
614 l_inputs(i).value := fnd_number.number_to_canonical(g_payroll_action_id);
615 elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
616 l_inputs(i).value := fnd_number.number_to_canonical(g_assignment_id);
617 elsif l_inputs(i).name = 'ASSIGNMENT_ACTION_ID' then
618 l_inputs(i).value := fnd_number.number_to_canonical(g_assignment_action_id);
619 elsif l_inputs(i).name = 'DATE_EARNED' then
620 l_inputs(i).value := fnd_date.date_to_canonical(g_effective_date);
621 --
622 elsif l_inputs(i).name = 'I_ASSIGNMENT_ID' then
623 l_inputs(i).value := fnd_number.number_to_canonical(g_assignment_id);
624 elsif l_inputs(i).name = 'I_ASSIGNMENT_ACTION_ID' then
625 l_inputs(i).value := fnd_number.number_to_canonical(g_assignment_action_id);
626 elsif l_inputs(i).name = 'I_INFO_TYPE' then
627 l_inputs(i).value := p_ins_info_rec.info_type;
628 elsif l_inputs(i).name = 'I_INS_CLASS' then
629 l_inputs(i).value := p_ins_info_rec.ins_class;
630 elsif l_inputs(i).name = 'I_INS_COMP_CODE' then
631 l_inputs(i).value := p_ins_info_rec.ins_comp_code;
632 elsif l_inputs(i).name = 'I_LIG_PREM_BAL' then
633 l_inputs(i).value := p_ins_info_rec.lig_prem_bal;
634 elsif l_inputs(i).name = 'I_LIG_PREM_MTH_ELE' then
635 l_inputs(i).value := p_ins_info_rec.lig_prem_mth_ele;
636 elsif l_inputs(i).name = 'I_LIG_PREM_BON_ELE' then
637 l_inputs(i).value := p_ins_info_rec.lig_prem_bon_ele;
638 elsif l_inputs(i).name = 'I_LIP_PREM_BAL' then
639 l_inputs(i).value := p_ins_info_rec.lip_prem_bal;
640 elsif l_inputs(i).name = 'I_LIP_PREM_MTH_ELE' then
641 l_inputs(i).value := p_ins_info_rec.lip_prem_mth_ele;
642 elsif l_inputs(i).name = 'I_LIP_PREM_BON_ELE' then
643 l_inputs(i).value := p_ins_info_rec.lip_prem_bon_ele;
644 elsif l_inputs(i).name = 'I_CMI_PREM_BAL' then
645 l_inputs(i).value := p_ins_info_rec.cmi_prem_bal;
646 elsif l_inputs(i).name = 'I_CMI_PREM_MTH_ELE' then
647 l_inputs(i).value := p_ins_info_rec.cmi_prem_mth_ele;
648 elsif l_inputs(i).name = 'I_CMI_PREM_BON_ELE' then
649 l_inputs(i).value := p_ins_info_rec.cmi_prem_bon_ele;
650 elsif l_inputs(i).name = 'I_LINC_PREM' then
651 l_inputs(i).value := fnd_number.number_to_canonical(p_ins_info_rec.linc_prem);
652 end if;
653 --
654 end loop;
655 --
656 if g_debug then
657 hr_utility.set_location(l_proc,30);
658 hr_utility.trace('business_group_id : '||g_business_group_id);
659 hr_utility.trace('payroll_id : '||g_payroll_id);
660 hr_utility.trace('payroll_action_id : '||g_payroll_action_id);
661 hr_utility.trace('assignment_id : '||g_assignment_id);
662 hr_utility.trace('assignment_action_id : '||g_assignment_action_id);
663 hr_utility.trace('effective_date : '||fnd_date.date_to_canonical(g_effective_date));
664 hr_utility.trace('i_info_type : '||p_ins_info_rec.info_type);
665 hr_utility.trace('i_ins_class : '||p_ins_info_rec.ins_class);
666 hr_utility.trace('i_ins_comp_code : '||p_ins_info_rec.ins_comp_code);
667 hr_utility.trace('i_lig_prem_bal : '||p_ins_info_rec.lig_prem_bal);
668 hr_utility.trace('i_lig_prem_mth_ele : '||p_ins_info_rec.lig_prem_mth_ele);
669 hr_utility.trace('i_lig_prem_bon_ele : '||p_ins_info_rec.lig_prem_bon_ele);
670 hr_utility.trace('i_lip_prem_bal : '||p_ins_info_rec.lip_prem_bal);
671 hr_utility.trace('i_lip_prem_mth_ele : '||p_ins_info_rec.lip_prem_mth_ele);
672 hr_utility.trace('i_lip_prem_bon_ele : '||p_ins_info_rec.lip_prem_bon_ele);
673 hr_utility.trace('i_cmi_prem_bal : '||p_ins_info_rec.cmi_prem_bal);
674 hr_utility.trace('i_cmi_prem_mth_ele : '||p_ins_info_rec.cmi_prem_mth_ele);
675 hr_utility.trace('i_cmi_prem_bon_ele : '||p_ins_info_rec.cmi_prem_bon_ele);
676 hr_utility.trace('i_linc_prem : '||p_ins_info_rec.linc_prem);
677 end if;
678 --
679 end if;
680 --
681 ff_exec.run_formula(
682 p_inputs => l_inputs,
683 p_outputs => l_outputs);
684 --
685 if g_debug then
686 hr_utility.set_location(l_proc,40);
687 end if;
688 --
689 if l_outputs.count > 1 then
690 --
691 for j in l_outputs.first..l_outputs.last loop
692 --
693 if l_outputs(j).name = 'O_LIG_PREM' then
694 p_lig_prem := fnd_number.canonical_to_number(ltrim(rtrim(l_outputs(j).value)));
695 elsif l_outputs(j).name = 'O_LIP_PREM' then
696 p_lip_prem := fnd_number.canonical_to_number(ltrim(rtrim(l_outputs(j).value)));
697 elsif l_outputs(j).name = 'O_CMI_PREM' then
698 p_cmi_prem := fnd_number.canonical_to_number(ltrim(rtrim(l_outputs(j).value)));
699 elsif l_outputs(j).name = 'O_MESSAGE' then
700 p_message := ltrim(rtrim(l_outputs(j).value));
701 end if;
702 --
703 end loop;
704 --
705 if g_debug then
706 hr_utility.set_location(l_proc,50);
707 hr_utility.trace('lig_prem : '||p_lig_prem);
708 hr_utility.trace('lip_prem : '||p_lip_prem);
709 hr_utility.trace('cmi_prem : '||p_cmi_prem);
710 hr_utility.trace('message : '||substrb(p_message,1,300));
711 end if;
712 --
713 if p_message is not null then
714 --
715 if g_debug then
716 hr_utility.set_location(l_proc,60);
717 end if;
718 --
719 fnd_file.put_line(fnd_file.output,'Assignment Id : '||fnd_number.number_to_canonical(g_assignment_id));
720 fnd_file.put_line(fnd_file.output,'----------------------------------------------------------------------------------------------------');
721 fnd_file.put_line(fnd_file.output,p_message);
722 fnd_file.put_line(fnd_file.output,' ');
723 --
724 if g_debug then
725 hr_utility.set_location(l_proc,70);
726 end if;
727 --
728 end if;
729 --
730 end if;
731 --
732 end if;
733 --
734 if g_debug then
735 hr_utility.set_location(l_proc,1000);
736 end if;
737 --
738 end calc_li_annual_prem;
739 --
740 -- -------------------------------------------------------------------------
741 -- calc_ai_annual_prem
742 -- -------------------------------------------------------------------------
743 procedure calc_ai_annual_prem(
744 p_ins_info_rec in t_ai_info_rec,
745 p_eqi_prem out nocopy number,
746 p_ai_prem out nocopy number,
747 p_message out nocopy varchar2)
748 is
749 --
750 l_proc varchar2(80) := c_package||'calc_ai_annual_prem';
751 --
752 l_inputs ff_exec.inputs_t;
753 l_outputs ff_exec.outputs_t;
754 l_formula_id number;
755 --
756 cursor csr_ff
757 is
758 select ff.formula_id
759 from ff_formulas_f ff
760 where ff.formula_name = p_ins_info_rec.calc_prem_ff
761 and nvl(ff.business_group_id,g_business_group_id) = g_business_group_id
762 and nvl(ff.legislation_code,g_legislation_code) = g_legislation_code
763 and g_effective_date
764 between ff.effective_start_date and ff.effective_end_date;
765 --
766 begin
767 --
768 if g_debug then
769 hr_utility.set_location(l_proc,0);
770 end if;
771 --
772 open csr_ff;
773 fetch csr_ff into l_formula_id;
774 close csr_ff;
775 --
776 if g_debug then
777 hr_utility.set_location(l_proc,10);
778 hr_utility.trace('formula_id : '||l_formula_id);
779 end if;
780 --
781 if l_formula_id is not null then
782 --
783 ff_exec.init_formula
784 (p_formula_id => l_formula_id,
785 p_effective_date => g_effective_date,
786 p_inputs => l_inputs,
787 p_outputs => l_outputs);
788 --
789 if g_debug then
790 hr_utility.set_location(l_proc,20);
791 end if;
792 --
793 if l_inputs.count > 1 then
794 --
795 for i in l_inputs.first..l_inputs.last loop
796 --
797 if l_inputs(i).name = 'BUSINESS_GROUP_ID' then
798 l_inputs(i).value := fnd_number.number_to_canonical(g_business_group_id);
799 elsif l_inputs(i).name = 'PAYROLL_ID' then
800 l_inputs(i).value := fnd_number.number_to_canonical(g_payroll_id);
801 elsif l_inputs(i).name = 'PAYROLL_ACTION_ID' then
802 l_inputs(i).value := fnd_number.number_to_canonical(g_payroll_action_id);
803 elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
804 l_inputs(i).value := fnd_number.number_to_canonical(g_assignment_id);
805 elsif l_inputs(i).name = 'ASSIGNMENT_ACTION_ID' then
806 l_inputs(i).value := fnd_number.number_to_canonical(g_assignment_action_id);
807 elsif l_inputs(i).name = 'DATE_EARNED' then
808 l_inputs(i).value := fnd_date.date_to_canonical(g_effective_date);
809 --
810 elsif l_inputs(i).name = 'I_ASSIGNMENT_ID' then
811 l_inputs(i).value := fnd_number.number_to_canonical(g_assignment_id);
812 elsif l_inputs(i).name = 'I_ASSIGNMENT_ACTION_ID' then
813 l_inputs(i).value := fnd_number.number_to_canonical(g_assignment_action_id);
814 elsif l_inputs(i).name = 'I_INFO_TYPE' then
815 l_inputs(i).value := p_ins_info_rec.info_type;
816 elsif l_inputs(i).name = 'I_INS_CLASS' then
817 l_inputs(i).value := p_ins_info_rec.ins_class;
818 elsif l_inputs(i).name = 'I_INS_TERM_TYPE' then
819 l_inputs(i).value := p_ins_info_rec.ins_term_type;
820 elsif l_inputs(i).name = 'I_INS_COMP_CODE' then
821 l_inputs(i).value := p_ins_info_rec.ins_comp_code;
822 elsif l_inputs(i).name = 'I_EQI_PREM_BAL' then
823 l_inputs(i).value := p_ins_info_rec.eqi_prem_bal;
824 elsif l_inputs(i).name = 'I_EQI_PREM_MTH_ELE' then
825 l_inputs(i).value := p_ins_info_rec.eqi_prem_mth_ele;
826 elsif l_inputs(i).name = 'I_EQI_PREM_BON_ELE' then
827 l_inputs(i).value := p_ins_info_rec.eqi_prem_bon_ele;
828 elsif l_inputs(i).name = 'I_AI_PREM_BAL' then
829 l_inputs(i).value := p_ins_info_rec.ai_prem_bal;
830 elsif l_inputs(i).name = 'I_AI_PREM_MTH_ELE' then
831 l_inputs(i).value := p_ins_info_rec.ai_prem_mth_ele;
832 elsif l_inputs(i).name = 'I_AI_PREM_BON_ELE' then
833 l_inputs(i).value := p_ins_info_rec.ai_prem_bon_ele;
834 elsif l_inputs(i).name = 'I_AI_PREM' then
835 l_inputs(i).value := fnd_number.number_to_canonical(p_ins_info_rec.annual_prem);
836 end if;
837 --
838 end loop;
839 --
840 if g_debug then
841 hr_utility.set_location(l_proc,30);
842 hr_utility.trace('business_group_id : '||g_business_group_id);
843 hr_utility.trace('payroll_id : '||g_payroll_id);
844 hr_utility.trace('payroll_action_id : '||g_payroll_action_id);
845 hr_utility.trace('assignment_id : '||g_assignment_id);
846 hr_utility.trace('assignment_action_id : '||g_assignment_action_id);
847 hr_utility.trace('effective_date : '||fnd_date.date_to_canonical(g_effective_date));
848 hr_utility.trace('i_ins_class : '||p_ins_info_rec.ins_class);
849 hr_utility.trace('i_ins_term_type : '||p_ins_info_rec.ins_term_type);
850 hr_utility.trace('i_ins_comp_code : '||p_ins_info_rec.ins_comp_code);
851 hr_utility.trace('i_eqi_prem_bal : '||p_ins_info_rec.eqi_prem_bal);
852 hr_utility.trace('i_eqi_prem_mth_ele : '||p_ins_info_rec.eqi_prem_mth_ele);
853 hr_utility.trace('i_eqi_prem_bon_ele : '||p_ins_info_rec.eqi_prem_bon_ele);
854 hr_utility.trace('i_ai_prem_bal : '||p_ins_info_rec.ai_prem_bal);
855 hr_utility.trace('i_ai_prem_mth_ele : '||p_ins_info_rec.ai_prem_mth_ele);
856 hr_utility.trace('i_ai_prem_bon_ele : '||p_ins_info_rec.ai_prem_bon_ele);
857 hr_utility.trace('i_ai_prem : '||p_ins_info_rec.annual_prem);
858 end if;
859 --
860 end if;
861 --
862 ff_exec.run_formula(
863 p_inputs => l_inputs,
864 p_outputs => l_outputs);
865 --
866 if g_debug then
867 hr_utility.set_location(l_proc,40);
868 end if;
869 --
870 if l_outputs.count >= 1 then
871 --
872 for j in l_outputs.first..l_outputs.last loop
873 --
874 if l_outputs(j).name = 'O_EQI_PREM' then
875 p_eqi_prem := fnd_number.canonical_to_number(ltrim(rtrim(l_outputs(j).value)));
876 elsif l_outputs(j).name = 'O_AI_PREM' then
877 p_ai_prem := fnd_number.canonical_to_number(ltrim(rtrim(l_outputs(j).value)));
878 elsif l_outputs(j).name = 'O_MESSAGE' then
879 p_message := ltrim(rtrim(l_outputs(j).value));
880 end if;
881 --
882 end loop;
883 --
884 if g_debug then
885 hr_utility.set_location(l_proc,50);
886 hr_utility.trace('eqi_prem : '||p_eqi_prem);
887 hr_utility.trace('ai_prem : '||p_ai_prem);
888 hr_utility.trace('message : '||substrb(p_message,1,300));
889 end if;
890 --
891 if p_message is not null then
892 --
893 if g_debug then
894 hr_utility.set_location(l_proc,60);
895 end if;
896 --
897 fnd_file.put_line(fnd_file.output,'Assignment Id : '||fnd_number.number_to_canonical(g_assignment_id));
898 fnd_file.put_line(fnd_file.output,'----------------------------------------------------------------------------------------------------');
899 fnd_file.put_line(fnd_file.output,p_message);
900 fnd_file.put_line(fnd_file.output,' ');
901 --
902 if g_debug then
903 hr_utility.set_location(l_proc,70);
904 end if;
905 --
906 end if;
907 --
908 end if;
909 --
910 end if;
911 --
912 if g_debug then
913 hr_utility.set_location(l_proc,1000);
914 end if;
915 --
916 end calc_ai_annual_prem;
917 --
918 -- -------------------------------------------------------------------------
919 -- ee_datetrack_update_mode (for non-reccurring)
920 -- -------------------------------------------------------------------------
921 function ee_datetrack_update_mode(
922 p_element_entry_id in number,
923 p_effective_start_date in date,
924 p_effective_end_date in date,
925 p_effective_date in date)
926 return varchar2
927 --
928 is
929 --
930 l_datetrack_mode varchar2(30);
931 l_exists varchar2(1);
932 --
933 --cursor csr_future_exists
934 --is
935 --select 'Y'
936 --from dual
937 --where exists(
938 -- select null
939 -- from pay_element_entries_f
940 -- where element_entry_id = p_element_entry_id
941 -- and effective_start_date = p_effective_end_date + 1);
942 --
943 begin
944 --
945 -- always CORRECTION in case of non-recurring.
946 if p_effective_start_date = trunc(p_effective_date,'MM') then
947 l_datetrack_mode := 'CORRECTION';
948 end if;
949 --
950 --if p_effective_start_date = p_effective_date then
951 -- l_datetrack_mode := 'CORRECTION';
952 --else
953 ----
954 -- open csr_future_exists;
955 -- fetch csr_future_exists into l_exists;
956 ----
957 -- if csr_future_exists%notfound then
958 -- l_datetrack_mode := 'UPDATE';
959 -- else
960 -- l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
961 -- end if;
962 ----
963 --end if;
964 --
965 return l_datetrack_mode;
966 --
967 end ee_datetrack_update_mode;
968 --
969 -- -------------------------------------------------------------------------
970 -- fetch_entry
971 -- -------------------------------------------------------------------------
972 procedure fetch_entry(
973 p_assignment_id in number,
974 p_business_group_id in number,
975 p_effective_date in date,
976 p_entry_rec out nocopy t_entry_rec)
977 is
978 --
979 l_proc varchar2(80) := c_package||'fetch_entry';
980 --
981 cursor csr_entry(p_element_type_id in number)
982 is
983 select /*+ ORDERED */
984 pee.element_entry_id,
985 pee.effective_start_date,
986 pee.effective_end_date,
987 pee.object_version_number,
988 peev.input_value_id,
989 peev.screen_entry_value
990 from pay_element_links_f pel,
991 pay_element_entries_f pee,
992 pay_element_entry_values_f peev
993 where pel.element_type_id = p_element_type_id
994 and pel.business_group_id + 0 = p_business_group_id
995 and p_effective_date
996 between pel.effective_start_date and pel.effective_end_date
997 and pee.assignment_id = p_assignment_id
998 and pee.element_link_id = pel.element_link_id
999 and p_effective_date
1000 between pee.effective_start_date and pee.effective_end_date
1001 and pee.entry_type = 'E'
1002 and peev.element_entry_id = pee.element_entry_id
1003 and peev.effective_start_date = pee.effective_start_date
1004 and peev.effective_end_date = pee.effective_end_date;
1005 --
1006 l_csr_entry csr_entry%rowtype;
1007 --
1008 begin
1009 --
1010 if g_debug then
1011 hr_utility.set_location(l_proc,0);
1012 end if;
1013 --
1014 p_entry_rec.ins_entry_cnt := 0;
1015 p_entry_rec.ins_datetrack_update_mode := null;
1016 p_entry_rec.ins_element_entry_id := null;
1017 p_entry_rec.ins_ee_object_version_number := null;
1018 p_entry_rec.life_gen_ins_prem := null;
1019 p_entry_rec.life_pens_ins_prem := null;
1020 p_entry_rec.life_gen_ins_prem_new := null;
1021 p_entry_rec.life_pens_ins_prem_new := null;
1022 p_entry_rec.care_med_ins_prem_new := null;
1023 p_entry_rec.nonlife_long_ins_prem := null;
1024 p_entry_rec.nonlife_short_ins_prem := null;
1025 p_entry_rec.earthquake_ins_prem := null;
1026 p_entry_rec.is_entry_cnt := 0;
1027 p_entry_rec.is_datetrack_update_mode := null;
1028 p_entry_rec.is_element_entry_id := null;
1029 p_entry_rec.is_ee_object_version_number := null;
1030 p_entry_rec.social_ins_prem := null;
1031 p_entry_rec.mutual_aid_prem := null;
1032 p_entry_rec.spouse_income := null;
1033 p_entry_rec.sp_dct_exclude := null;
1034 p_entry_rec.national_pens_ins_prem := null;
1035 --
1036 open csr_entry(c_isdf_ins_elm_id);
1037 loop
1038 --
1039 fetch csr_entry into l_csr_entry;
1040 exit when csr_entry%notfound;
1041 --
1042 if csr_entry%rowcount = 1 then
1043 p_entry_rec.ins_datetrack_update_mode := ee_datetrack_update_mode(l_csr_entry.element_entry_id,l_csr_entry.effective_start_date,l_csr_entry.effective_end_date,p_effective_date);
1044 p_entry_rec.ins_element_entry_id := l_csr_entry.element_entry_id;
1045 p_entry_rec.ins_ee_object_version_number := l_csr_entry.object_version_number;
1046 p_entry_rec.ins_entry_cnt := p_entry_rec.ins_entry_cnt + 1;
1047 end if;
1048 --
1049 if l_csr_entry.input_value_id = c_life_gen_iv_id then
1050 --
1051 p_entry_rec.life_gen_ins_prem := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1052 --
1053 elsif l_csr_entry.input_value_id = c_life_pens_iv_id then
1054 --
1055 p_entry_rec.life_pens_ins_prem := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1056 --
1057 elsif l_csr_entry.input_value_id = c_nonlife_long_iv_id then
1058 --
1059 p_entry_rec.nonlife_long_ins_prem := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1060 --
1061 elsif l_csr_entry.input_value_id = c_nonlife_short_iv_id then
1062 --
1063 p_entry_rec.nonlife_short_ins_prem := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1064 --
1065 elsif l_csr_entry.input_value_id = c_earthquake_iv_id then
1066 --
1067 p_entry_rec.earthquake_ins_prem := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1068 --
1069 elsif l_csr_entry.input_value_id = c_life_gen_new_iv_id then
1070 --
1071 p_entry_rec.life_gen_ins_prem_new := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1072 --
1073 elsif l_csr_entry.input_value_id = c_life_pens_new_iv_id then
1074 --
1075 p_entry_rec.life_pens_ins_prem_new := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1076 --
1077 elsif l_csr_entry.input_value_id = c_care_med_new_iv_id then
1078 --
1079 p_entry_rec.care_med_ins_prem_new := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1080 --
1081 end if;
1082 --
1083 end loop;
1084 close csr_entry;
1085 --
1086 --
1087 open csr_entry(c_isdf_is_elm_id);
1088 loop
1089 --
1090 fetch csr_entry into l_csr_entry;
1091 exit when csr_entry%notfound;
1092 --
1093 if csr_entry%rowcount = 1 then
1094 p_entry_rec.is_datetrack_update_mode := ee_datetrack_update_mode(l_csr_entry.element_entry_id,l_csr_entry.effective_start_date,l_csr_entry.effective_end_date,p_effective_date);
1095 p_entry_rec.is_element_entry_id := l_csr_entry.element_entry_id;
1096 p_entry_rec.is_ee_object_version_number := l_csr_entry.object_version_number;
1097 p_entry_rec.is_entry_cnt := p_entry_rec.is_entry_cnt + 1;
1098 end if;
1099 --
1100 if l_csr_entry.input_value_id = c_social_iv_id then
1101 --
1102 p_entry_rec.social_ins_prem := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1103 --
1104 elsif l_csr_entry.input_value_id = c_mutual_aid_iv_id then
1105 --
1106 p_entry_rec.mutual_aid_prem := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1107 --
1108 elsif l_csr_entry.input_value_id = c_spouse_iv_id then
1109 --
1110 p_entry_rec.spouse_income := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1111 --
1112 elsif l_csr_entry.input_value_id = c_sp_dct_exclude_iv_id then
1113 --
1114 p_entry_rec.sp_dct_exclude := l_csr_entry.screen_entry_value;
1115 --
1116 elsif l_csr_entry.input_value_id = c_national_pens_iv_id then
1117 --
1118 p_entry_rec.national_pens_ins_prem := fnd_number.canonical_to_number(l_csr_entry.screen_entry_value);
1119 --
1120 end if;
1121 --
1122 end loop;
1123 close csr_entry;
1124 --
1125 if g_debug then
1126 hr_utility.set_location(l_proc,1000);
1127 end if;
1128 --
1129 end fetch_entry;
1130 --
1131 -- -------------------------------------------------------------------------
1132 -- archive_assact
1133 -- -------------------------------------------------------------------------
1134 procedure archive_assact(
1135 p_assignment_action_id in number,
1136 p_assignment_id in number)
1137 is
1138 --
1139 l_proc varchar2(80) := c_package||'archive_assact';
1140 --
1141 l_lig_prem number;
1142 l_lip_prem number;
1143 l_cmi_prem number;
1144 l_ai_prem number;
1145 --
1146 l_eqi_prem number;
1147 l_nli_prem number;
1148 --
1149 l_message varchar2(2000);
1150 l_object_version_number number;
1151 --
1152 l_li_info_rec t_li_info_rec;
1153 l_ai_info_rec t_ai_info_rec;
1154 l_spouse_rec t_spouse_rec;
1155 --
1156 l_entry_rec t_entry_rec;
1157 l_tax_type pay_element_entry_values_f.screen_entry_value%type;
1158 --
1159 l_copy_archive_assact_id number;
1160 --
1161 cursor csr_emp
1162 is
1163 select /*+ ORDERED */
1164 pp.person_id,
1165 pp.business_group_id,
1166 pp.employee_number employee_number,
1167 pp.last_name last_name_kana,
1168 pp.first_name first_name_kana,
1169 pp.per_information18 last_name,
1170 pp.per_information19 first_name,
1171 pp.per_information18||' '||pp.per_information19 full_name,
1172 decode(par.address_id,null,pac.postal_code,par.postal_code) postal_code,
1173 trim(substrb(decode(par.address_id,null,
1174 pac.address_line1||pac.address_line2||pac.address_line3,
1175 par.address_line1||par.address_line2||par.address_line3),1,240)) address
1176 from per_all_assignments_f pa,
1177 per_all_people_f pp,
1178 per_addresses par,
1179 per_addresses pac
1180 where pa.assignment_id = p_assignment_id
1181 and g_effective_date
1182 between pa.effective_start_date and pa.effective_end_date
1183 and pp.person_id = pa.person_id
1184 and g_effective_date
1185 between pp.effective_start_date and pp.effective_end_date
1186 and par.person_id(+) = pp.person_id
1187 and par.address_type(+) = 'JP_R'
1188 and g_effective_date
1189 between par.date_from(+) and nvl(par.date_to(+),g_effective_date)
1190 and pac.person_id(+) = pp.person_id
1191 and pac.address_type(+) = 'JP_C'
1192 and g_effective_date
1193 between pac.date_from(+) and nvl(pac.date_to(+),g_effective_date);
1194 --
1195 cursor csr_gen
1196 is
1197 select /*+ ORDERED */
1198 paei.assignment_extra_info_id,
1199 paei.object_version_number aei_object_version_number,
1200 paei.information_type info_type,
1201 paei.aei_information1 gen_ins_class,
1202 paei.aei_information2 gen_ins_company_code,
1203 nvl(paei.aei_information12,'O') gen_ins_old_new,
1204 hoi.org_information2 ins_company_name,
1205 hoi.org_information3 calc_prem_ff,
1206 hoi.org_information4 lig_prem_bal,
1207 hoi.org_information5 lig_prem_mth_ele,
1208 hoi.org_information6 lig_prem_bon_ele,
1209 null lip_prem_bal,
1210 null lip_prem_mth_ele,
1211 null lip_prem_bon_ele,
1212 null cmi_prem_bal,
1213 null cmi_prem_mth_ele,
1214 null cmi_prem_bon_ele,
1215 fnd_date.canonical_to_date(paei.aei_information3) start_date,
1216 fnd_date.canonical_to_date(paei.aei_information4) end_date,
1217 paei.aei_information5 ins_type,
1218 null ins_period_start_date,
1219 paei.aei_information6 ins_period,
1220 paei.aei_information7 contractor_name,
1221 paei.aei_information8 beneficiary_name,
1222 paei.aei_information9 beneficiary_relship,
1223 fnd_number.canonical_to_number(paei.aei_information10) linc_prem
1224 from per_assignment_extra_info paei,
1225 hr_organization_information hoi
1226 where paei.assignment_id = p_assignment_id
1227 and paei.information_type = 'JP_ASS_LIG_INFO'
1228 -- include PC for customized valuset
1229 --and paei.aei_information1 in ('GIP','LINC')
1230 and g_effective_date
1231 between nvl(fnd_date.canonical_to_date(paei.aei_information3),nvl(fnd_date.canonical_to_date(paei.aei_information4)+1,hr_api.g_sot))
1232 and nvl(fnd_date.canonical_to_date(paei.aei_information4),hr_api.g_eot)
1233 and hoi.org_information1 = paei.aei_information2
1234 and nvl(hoi.ORG_INFORMATION10,'O') = nvl(paei.AEI_INFORMATION12,'O')
1235 and hoi.org_information_context
1236 = decode(paei.aei_information1,'GIP','JP_LI_GIP_INFO','LINC','JP_LI_LINC_INFO','X')
1237 and hoi.organization_id = g_organization_id
1238 -- irregular case for duplicate org
1239 and not exists(
1240 select null
1241 from hr_organization_information hoi2
1242 where hoi2.org_information1 = hoi.org_information1
1243 and hoi2.org_information_context = hoi.org_information_context
1244 and hoi2.organization_id = hoi.organization_id
1245 and nvl(hoi2.ORG_INFORMATION10,'O') = nvl(hoi.ORG_INFORMATION10,'O')
1246 and hoi2.org_information_id < hoi.org_information_id)
1247 order by
1248 decode(paei.aei_information1,'GIP',1,2),
1249 paei.aei_information2;
1250 --
1251 cursor csr_cmi
1252 is
1253 select /*+ ORDERED */
1254 paei.assignment_extra_info_id,
1255 paei.object_version_number aei_object_version_number,
1256 paei.information_type info_type,
1257 paei.aei_information1 cmi_ins_class,
1258 paei.aei_information2 cmi_ins_company_code,
1259 'N' care_ins_old_new,
1260 hoi.org_information2 ins_company_name,
1261 hoi.org_information3 calc_prem_ff,
1262 null lig_prem_bal,
1263 null lig_prem_mth_ele,
1264 null lig_prem_bon_ele,
1265 null lip_prem_bal,
1266 null lip_prem_mth_ele,
1267 null lip_prem_bon_ele,
1268 hoi.org_information11 cmi_prem_bal,
1269 hoi.org_information12 cmi_prem_mth_ele,
1270 hoi.org_information13 cmi_prem_bon_ele,
1271 fnd_date.canonical_to_date(paei.aei_information3) start_date,
1272 fnd_date.canonical_to_date(paei.aei_information4) end_date,
1273 paei.aei_information5 ins_type,
1274 null ins_period_start_date,
1275 paei.aei_information6 ins_period,
1276 paei.aei_information7 contractor_name,
1277 paei.aei_information8 beneficiary_name,
1278 paei.aei_information9 beneficiary_relship,
1279 fnd_number.canonical_to_number(paei.aei_information10) linc_prem
1280 from per_assignment_extra_info paei,
1281 hr_organization_information hoi
1282 where paei.assignment_id = p_assignment_id
1283 and paei.information_type = 'JP_ASS_CMI_INFO'
1284 -- include PC for customized valuset
1285 --and paei.aei_information1 in ('GIP','LINC')
1286 and g_effective_date
1287 between nvl(fnd_date.canonical_to_date(paei.aei_information3),nvl(fnd_date.canonical_to_date(paei.aei_information4)+1,hr_api.g_sot))
1288 and nvl(fnd_date.canonical_to_date(paei.aei_information4),hr_api.g_eot)
1289 and hoi.org_information1 = paei.aei_information2
1290 and nvl(hoi.ORG_INFORMATION10,'N') = 'N'
1291 and hoi.org_information_context
1292 = decode(paei.aei_information1,'GIP','JP_LI_GIP_INFO','LINC','JP_LI_LINC_INFO','X')
1293 and hoi.organization_id = g_organization_id
1294 -- irregular case for duplicate org
1295 and not exists(
1296 select null
1297 from hr_organization_information hoi2
1298 where hoi2.org_information1 = hoi.org_information1
1299 and hoi2.org_information_context = hoi.org_information_context
1300 and hoi2.organization_id = hoi.organization_id
1301 and nvl(hoi2.ORG_INFORMATION10,'N') = nvl(hoi.ORG_INFORMATION10,'N')
1302 and hoi2.org_information_id < hoi.org_information_id)
1303 order by
1304 decode(paei.aei_information1,'GIP',1,2),
1305 paei.aei_information2;
1306 --
1307 cursor csr_pens
1308 is
1309 select /*+ ORDERED */
1310 paei.assignment_extra_info_id,
1311 paei.object_version_number aei_object_version_number,
1312 paei.information_type info_type,
1313 paei.aei_information1 pens_ins_class,
1314 paei.aei_information2 pens_ins_company_code,
1315 nvl(paei.aei_information13,'O') pens_ins_old_new,
1316 hoi.org_information2 ins_company_name,
1317 hoi.org_information3 calc_prem_ff,
1318 null lig_prem_bal,
1319 null lig_prem_mth_ele,
1320 null lig_prem_bon_ele,
1321 hoi.org_information7 lip_prem_bal,
1322 hoi.org_information8 lip_prem_mth_ele,
1323 hoi.org_information9 lip_prem_bon_ele,
1324 null cmi_prem_bal,
1325 null cmi_prem_mth_ele,
1326 null cmi_prem_bon_ele,
1327 fnd_date.canonical_to_date(paei.aei_information3) start_date,
1328 fnd_date.canonical_to_date(paei.aei_information4) end_date,
1329 paei.aei_information5 ins_type,
1330 fnd_date.canonical_to_date(paei.aei_information6) ins_period_start_date,
1331 paei.aei_information7 ins_period,
1332 paei.aei_information8 contractor_name,
1333 paei.aei_information9 beneficiary_name,
1334 paei.aei_information10 beneficiary_relship,
1335 fnd_number.canonical_to_number(paei.aei_information11) linc_prem
1336 from per_assignment_extra_info paei,
1337 hr_organization_information hoi
1338 where paei.assignment_id = p_assignment_id
1339 and paei.information_type = 'JP_ASS_LIP_INFO'
1340 -- include PC for customized valuset
1341 --and paei.aei_information1 in ('GIP','LINC')
1342 and g_effective_date
1343 between nvl(fnd_date.canonical_to_date(paei.aei_information3),nvl(fnd_date.canonical_to_date(paei.aei_information4)+1,hr_api.g_sot))
1344 and nvl(fnd_date.canonical_to_date(paei.aei_information4),hr_api.g_eot)
1345 and hoi.org_information1 = paei.aei_information2
1346 and nvl(hoi.ORG_INFORMATION10,'O') = nvl(paei.AEI_INFORMATION13,'O')
1347 and hoi.org_information_context
1348 = decode(paei.aei_information1,'GIP','JP_LI_GIP_INFO','LINC','JP_LI_LINC_INFO','X')
1349 and hoi.organization_id = g_organization_id
1350 -- irregular case for duplicate org
1351 and not exists(
1352 select null
1353 from hr_organization_information hoi2
1354 where hoi2.org_information1 = hoi.org_information1
1355 and hoi2.org_information_context = hoi.org_information_context
1356 and hoi2.organization_id = hoi.organization_id
1357 and nvl(hoi2.ORG_INFORMATION10,'O') = nvl(hoi.ORG_INFORMATION10,'O')
1358 and hoi2.org_information_id < hoi.org_information_id)
1359 order by
1360 decode(paei.aei_information1,'GIP',1,2),
1361 paei.aei_information2;
1362 --
1363 cursor csr_nonlife
1364 is
1365 select /*+ ORDERED */
1366 paei.assignment_extra_info_id,
1367 paei.object_version_number aei_object_version_number,
1368 paei.information_type info_type,
1369 paei.aei_information13 nonlife_ins_class,
1370 paei.aei_information1 nonlife_ins_term_type,
1371 paei.aei_information2 nonlife_ins_company_code,
1372 hoi.org_information2 ins_company_name,
1373 hoi.org_information3 calc_prem_ff,
1374 hoi.org_information7 eqi_prem_bal,
1375 hoi.org_information8 eqi_prem_mth_ele,
1376 hoi.org_information9 eqi_prem_bon_ele,
1377 hoi.org_information4 ai_prem_bal,
1378 hoi.org_information5 ai_prem_mth_ele,
1379 hoi.org_information6 ai_prem_bon_ele,
1380 fnd_date.canonical_to_date(paei.aei_information3) start_date,
1381 fnd_date.canonical_to_date(paei.aei_information4) end_date,
1382 paei.aei_information5 ins_type,
1383 paei.aei_information6 ins_period,
1384 paei.aei_information7 contractor_name,
1385 paei.aei_information8 beneficiary_name,
1386 paei.aei_information9 beneficiary_relship,
1387 decode(to_char(sign(g_effective_date - c_st_upd_date_2007)),'-1',paei.aei_information10,null) maturity_repayment,
1388 fnd_number.canonical_to_number(paei.aei_information11) annual_prem
1389 from per_assignment_extra_info paei,
1390 hr_organization_information hoi
1391 where paei.assignment_id = p_assignment_id
1392 and paei.information_type = 'JP_ASS_AI_INFO'
1393 -- include PC for customized valuset
1394 --and paei.aei_information13 = 'AP'
1395 and paei.aei_information1 <> decode(to_char(sign(g_effective_date - c_st_upd_date_2007)),'-1','EQ','S')
1396 and g_effective_date
1397 between nvl(fnd_date.canonical_to_date(paei.aei_information3),nvl(fnd_date.canonical_to_date(paei.aei_information4)+1,hr_api.g_sot))
1398 and nvl(fnd_date.canonical_to_date(paei.aei_information4),hr_api.g_eot)
1399 and hoi.org_information1 = paei.aei_information2
1400 and hoi.org_information_context = 'JP_ACCIDENT_INS_INFO'
1401 and hoi.organization_id = g_organization_id
1402 -- irregular case for duplicate org
1403 and not exists(
1404 select null
1405 from hr_organization_information hoi2
1406 where hoi2.org_information1 = hoi.org_information1
1407 and hoi2.org_information_context = hoi.org_information_context
1408 and hoi2.organization_id = hoi.organization_id
1409 and hoi2.org_information_id < hoi.org_information_id)
1410 order by paei.aei_information13,
1411 decode(paei.aei_information1,'EQ',1,'L',2,3),
1412 paei.aei_information2;
1413 --
1414 cursor csr_copy_assact
1415 is
1416 select /*+ ORDERED */
1417 assact.assignment_action_id
1418 from pay_assignment_actions paa,
1419 pay_jp_isdf_assact_v assact
1420 where paa.payroll_action_id = g_copy_archive_pact_id
1421 and paa.assignment_id = p_assignment_id
1422 and paa.action_status = 'C'
1423 and assact.assignment_action_id = paa.assignment_action_id
1424 and assact.transfer_status <> 'E'
1425 and assact.transaction_status in ('A','F');
1426 --
1427 cursor csr_copy_life_gen
1428 is
1429 select *
1430 from pay_jp_isdf_life_gen_v
1431 where assignment_action_id = l_copy_archive_assact_id
1432 and status <> 'D';
1433 --
1434 cursor csr_copy_life_pens
1435 is
1436 select *
1437 from pay_jp_isdf_life_pens_v
1438 where assignment_action_id = l_copy_archive_assact_id
1439 and status <> 'D';
1440 --
1441 cursor csr_copy_care_med
1442 is
1443 select *
1444 from pay_jp_isdf_care_med_v
1445 where assignment_action_id = l_copy_archive_assact_id
1446 and status <> 'D';
1447 --
1448 cursor csr_copy_nonlife
1449 is
1450 select *
1451 from pay_jp_isdf_nonlife_v
1452 where assignment_action_id = l_copy_archive_assact_id
1453 and status <> 'D';
1454 --
1455 cursor csr_copy_social
1456 is
1457 select *
1458 from pay_jp_isdf_social_v
1459 where assignment_action_id = l_copy_archive_assact_id
1460 and status <> 'D';
1461 --
1462 cursor csr_copy_mutual_aid
1463 is
1464 select *
1465 from pay_jp_isdf_mutual_aid_v
1466 where assignment_action_id = l_copy_archive_assact_id
1467 and status <> 'D';
1468 --
1469 cursor csr_copy_spouse
1470 is
1471 select *
1472 from pay_jp_isdf_spouse_v
1473 where assignment_action_id = l_copy_archive_assact_id
1474 and status <> 'D';
1475 --
1476 cursor csr_copy_spouse_inc
1477 is
1478 select *
1479 from pay_jp_isdf_spouse_inc_v
1480 where assignment_action_id = l_copy_archive_assact_id
1481 and status <> 'D';
1482 --
1483 l_csr_emp csr_emp%rowtype;
1484 --
1485 l_csr_copy_life_gen csr_copy_life_gen%rowtype;
1486 l_csr_copy_life_pens csr_copy_life_pens%rowtype;
1487 l_csr_copy_care_med csr_copy_care_med%rowtype;
1488 l_csr_copy_nonlife csr_copy_nonlife%rowtype;
1489 l_csr_copy_social csr_copy_social%rowtype;
1490 l_csr_copy_mutual_aid csr_copy_mutual_aid%rowtype;
1491 l_csr_copy_spouse csr_copy_spouse%rowtype;
1492 l_csr_copy_spouse_inc csr_copy_spouse_inc%rowtype;
1493 --
1494 begin
1495 --
1496 if g_debug then
1497 hr_utility.set_location(l_proc,0);
1498 end if;
1499 --
1500 if g_archive_default_flag = 'Y' then
1501 --
1502 if g_debug then
1503 hr_utility.set_location(l_proc,10);
1504 end if;
1505 --
1506 open csr_emp;
1507 fetch csr_emp into l_csr_emp;
1508 close csr_emp;
1509 --
1510 if g_debug then
1511 hr_utility.set_location(l_proc,20);
1512 hr_utility.trace('employee_number : '||l_csr_emp.employee_number);
1513 hr_utility.trace('last_name_kana : '||l_csr_emp.last_name_kana);
1514 hr_utility.trace('first_name_kana : '||l_csr_emp.first_name_kana);
1515 hr_utility.trace('last_name : '||l_csr_emp.last_name);
1516 hr_utility.trace('first_name : '||l_csr_emp.first_name);
1517 hr_utility.trace('full_name : '||l_csr_emp.full_name);
1518 hr_utility.trace('postal_code : '||l_csr_emp.postal_code);
1519 hr_utility.trace('address : '||l_csr_emp.address);
1520 end if;
1521 --
1522 if g_debug then
1523 hr_utility.trace('start create_emp');
1524 end if;
1525 --
1526 pay_jp_isdf_dml_pkg.create_emp(
1527 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
1528 p_assignment_action_id => p_assignment_action_id,
1529 p_action_context_type => 'AAP',
1530 p_assignment_id => p_assignment_id,
1531 p_effective_date => g_effective_date,
1532 p_action_information_category => 'JP_ISDF_EMP',
1533 p_employee_number => l_csr_emp.employee_number,
1534 p_last_name_kana => l_csr_emp.last_name_kana,
1535 p_first_name_kana => l_csr_emp.first_name_kana,
1536 p_last_name => l_csr_emp.last_name,
1537 p_first_name => l_csr_emp.first_name,
1538 p_postal_code => l_csr_emp.postal_code,
1539 p_address => l_csr_emp.address,
1540 p_object_version_number => l_object_version_number);
1541 --
1542 if g_debug then
1543 hr_utility.trace('end create_emp');
1544 hr_utility.set_location(l_proc,30);
1545 hr_utility.trace('start create_entry');
1546 end if;
1547 --
1548 -- jp_isdf_entry is fetched in the transfer process since transfer date can be specified as different from archive date,
1549 -- archive data in this archive time will be meaningless in the transfer time.
1550 -- However, finalize action is to fix all entry data except for _o prefex columns,
1551 -- so whatever, this make jp_isdf_entry archive.
1552 --
1553 fetch_entry(
1554 p_assignment_id => p_assignment_id,
1555 p_business_group_id => g_business_group_id,
1556 p_effective_date => g_effective_date,
1557 p_entry_rec => l_entry_rec);
1558 --
1559 if l_entry_rec.ins_entry_cnt + l_entry_rec.is_entry_cnt > 0 then
1560 --
1561 pay_jp_isdf_dml_pkg.create_entry(
1562 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
1563 p_assignment_action_id => p_assignment_action_id,
1564 p_action_context_type => 'AAP',
1565 p_assignment_id => p_assignment_id,
1566 p_effective_date => g_effective_date,
1567 p_action_information_category => 'JP_ISDF_ENTRY',
1568 p_status => 'Q',
1569 p_ins_datetrack_update_mode => l_entry_rec.ins_datetrack_update_mode,
1570 p_ins_element_entry_id => l_entry_rec.ins_element_entry_id,
1571 p_ins_ee_object_version_number => l_entry_rec.ins_ee_object_version_number,
1572 p_life_gen_ins_prem => l_entry_rec.life_gen_ins_prem,
1573 p_life_gen_ins_prem_o => null,
1574 p_life_pens_ins_prem => l_entry_rec.life_pens_ins_prem,
1575 p_life_pens_ins_prem_o => null,
1576 p_nonlife_long_ins_prem => l_entry_rec.nonlife_long_ins_prem,
1577 p_nonlife_long_ins_prem_o => null,
1578 p_nonlife_short_ins_prem => l_entry_rec.nonlife_short_ins_prem,
1579 p_nonlife_short_ins_prem_o => null,
1580 p_earthquake_ins_prem => l_entry_rec.earthquake_ins_prem,
1581 p_earthquake_ins_prem_o => null,
1582 p_is_datetrack_update_mode => l_entry_rec.is_datetrack_update_mode,
1583 p_is_element_entry_id => l_entry_rec.is_element_entry_id,
1584 p_is_ee_object_version_number => l_entry_rec.is_ee_object_version_number,
1585 p_social_ins_prem => l_entry_rec.social_ins_prem,
1586 p_social_ins_prem_o => null,
1587 p_mutual_aid_prem => l_entry_rec.mutual_aid_prem,
1588 p_mutual_aid_prem_o => null,
1589 p_spouse_income => l_entry_rec.spouse_income,
1590 p_spouse_income_o => null,
1591 p_national_pens_ins_prem => l_entry_rec.national_pens_ins_prem,
1592 p_national_pens_ins_prem_o => null,
1593 p_life_gen_ins_prem_new => l_entry_rec.life_gen_ins_prem_new,
1594 p_life_pens_ins_prem_new => l_entry_rec.life_pens_ins_prem_new,
1595 p_care_med_ins_prem_new => l_entry_rec.care_med_ins_prem_new,
1596 p_object_version_number => l_object_version_number);
1597 --
1598 end if;
1599 --
1600 if g_debug then
1601 hr_utility.trace('end create_entry');
1602 hr_utility.set_location(l_proc,40);
1603 end if;
1604 --
1605 if g_copy_archive_pact_id is null then
1606 --
1607 if g_debug then
1608 hr_utility.trace('start create_life_gen');
1609 hr_utility.set_location(l_proc,50);
1610 end if;
1611 --
1612 open csr_gen;
1613 loop
1614 --
1615 fetch csr_gen into l_li_info_rec;
1616 exit when csr_gen%notfound;
1617 --
1618 if g_debug then
1619 hr_utility.set_location(l_proc,60);
1620 hr_utility.trace('assignment_extra_info_id : '||l_li_info_rec.assignment_extra_info_id);
1621 hr_utility.trace('aei_object_version_number : '||l_li_info_rec.aei_object_version_number);
1622 hr_utility.trace('info_type : '||l_li_info_rec.info_type);
1623 hr_utility.trace('gen_ins_class : '||l_li_info_rec.ins_class);
1624 hr_utility.trace('gen_ins_company_code : '||l_li_info_rec.ins_comp_code);
1625 hr_utility.trace('gen_ins_class_old_or_new : '||l_li_info_rec.ins_class_old_new);
1626 hr_utility.trace('ins_company_name : '||l_li_info_rec.ins_comp_name);
1627 hr_utility.trace('calc_prem_ff : '||l_li_info_rec.calc_prem_ff);
1628 hr_utility.trace('lig_prem_bal : '||l_li_info_rec.lig_prem_bal);
1629 hr_utility.trace('lig_prem_mth_ele : '||l_li_info_rec.lig_prem_mth_ele);
1630 hr_utility.trace('lig_prem_bon_ele : '||l_li_info_rec.lig_prem_bon_ele);
1631 hr_utility.trace('start_date : '||fnd_date.date_to_canonical(l_li_info_rec.start_date));
1632 hr_utility.trace('end_date : '||fnd_date.date_to_canonical(l_li_info_rec.end_date));
1633 hr_utility.trace('ins_type : '||l_li_info_rec.ins_type);
1634 hr_utility.trace('ins_period_start_date : '||fnd_date.date_to_canonical(l_li_info_rec.ins_period_start_date));
1635 hr_utility.trace('ins_period : '||l_li_info_rec.ins_period);
1636 hr_utility.trace('contractor_name : '||l_li_info_rec.contractor_name);
1637 hr_utility.trace('beneficiary_name : '||l_li_info_rec.beneficiary_name);
1638 hr_utility.trace('beneficiary_relship : '||l_li_info_rec.beneficiary_relship);
1639 hr_utility.trace('linc_prem : '||fnd_number.number_to_canonical(l_li_info_rec.linc_prem));
1640 end if;
1641 --
1642 l_lig_prem := null;
1643 --
1644 if l_li_info_rec.ins_class <> 'PC' then
1645 --
1646 calc_li_annual_prem(
1647 p_ins_info_rec => l_li_info_rec,
1648 p_lig_prem => l_lig_prem,
1649 p_lip_prem => l_lip_prem,
1650 p_cmi_prem => l_cmi_prem,
1651 p_message => l_message);
1652 --
1653 end if;
1654 --
1655 if g_debug then
1656 hr_utility.set_location(l_proc,70);
1657 hr_utility.trace('annual_prem : '||l_lig_prem);
1658 end if;
1659 --
1660 pay_jp_isdf_dml_pkg.create_life_gen(
1661 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
1662 p_assignment_action_id => p_assignment_action_id,
1663 p_action_context_type => 'AAP',
1664 p_assignment_id => p_assignment_id,
1665 p_effective_date => g_effective_date,
1666 p_action_information_category => 'JP_ISDF_LIFE_GEN',
1667 p_status => 'Q',
1668 p_assignment_extra_info_id => l_li_info_rec.assignment_extra_info_id,
1669 p_aei_object_version_number => l_li_info_rec.aei_object_version_number,
1670 p_gen_ins_class => l_li_info_rec.ins_class,
1671 p_gen_ins_company_code => l_li_info_rec.ins_comp_code,
1672 p_ins_company_name => l_li_info_rec.ins_comp_name,
1673 p_ins_type => l_li_info_rec.ins_type,
1674 p_ins_period => l_li_info_rec.ins_period,
1675 p_contractor_name => l_li_info_rec.contractor_name,
1676 p_beneficiary_name => l_li_info_rec.beneficiary_name,
1677 p_beneficiary_relship => l_li_info_rec.beneficiary_relship,
1678 p_annual_prem => l_lig_prem,
1679 p_object_version_number => l_object_version_number,
1680 p_gen_ins_old_new => l_li_info_rec.ins_class_old_new);
1681 --
1682 if g_debug then
1683 hr_utility.set_location(l_proc,80);
1684 end if;
1685 --
1686 end loop;
1687 close csr_gen;
1688 --
1689
1690 if g_debug then
1691 hr_utility.trace('end create_life_gen');
1692 hr_utility.set_location(l_proc,85);
1693 hr_utility.trace('start create_care_med');
1694 end if;
1695
1696 open csr_cmi;
1697 loop
1698 --
1699 fetch csr_cmi into l_li_info_rec;
1700 exit when csr_cmi%notfound;
1701 --
1702 if g_debug then
1703 hr_utility.set_location(l_proc,86);
1704 hr_utility.trace('assignment_extra_info_id : '||l_li_info_rec.assignment_extra_info_id);
1705 hr_utility.trace('aei_object_version_number : '||l_li_info_rec.aei_object_version_number);
1706 hr_utility.trace('info_type : '||l_li_info_rec.info_type);
1707 hr_utility.trace('care_ins_class : '||l_li_info_rec.ins_class);
1708 hr_utility.trace('care_ins_company_code : '||l_li_info_rec.ins_comp_code);
1709 hr_utility.trace('care_ins_class_old_or_new : '||l_li_info_rec.ins_class_old_new);
1710 hr_utility.trace('care_ins_company_name : '||l_li_info_rec.ins_comp_name);
1711 hr_utility.trace('calc_prem_ff : '||l_li_info_rec.calc_prem_ff);
1712 hr_utility.trace('cmi_prem_bal : '||l_li_info_rec.cmi_prem_bal);
1713 hr_utility.trace('cmi_prem_mth_ele : '||l_li_info_rec.cmi_prem_mth_ele);
1714 hr_utility.trace('cmi_prem_bon_ele : '||l_li_info_rec.cmi_prem_bon_ele);
1715 hr_utility.trace('start_date : '||fnd_date.date_to_canonical(l_li_info_rec.start_date));
1716 hr_utility.trace('end_date : '||fnd_date.date_to_canonical(l_li_info_rec.end_date));
1717 hr_utility.trace('ins_type : '||l_li_info_rec.ins_type);
1718 hr_utility.trace('ins_period_start_date : '||fnd_date.date_to_canonical(l_li_info_rec.ins_period_start_date));
1719 hr_utility.trace('ins_period : '||l_li_info_rec.ins_period);
1720 hr_utility.trace('contractor_name : '||l_li_info_rec.contractor_name);
1721 hr_utility.trace('beneficiary_name : '||l_li_info_rec.beneficiary_name);
1722 hr_utility.trace('beneficiary_relship : '||l_li_info_rec.beneficiary_relship);
1723 hr_utility.trace('linc_prem : '||fnd_number.number_to_canonical(l_li_info_rec.linc_prem));
1724 end if;
1725 --
1726 l_cmi_prem := null;
1727 --
1728 if l_li_info_rec.ins_class <> 'PC' then
1729 --
1730 calc_li_annual_prem(
1731 p_ins_info_rec => l_li_info_rec,
1732 p_lig_prem => l_lig_prem,
1733 p_lip_prem => l_lip_prem,
1734 p_cmi_prem => l_cmi_prem,
1735 p_message => l_message);
1736 --
1737 end if;
1738 --
1739 if g_debug then
1740 hr_utility.set_location(l_proc,87);
1741 hr_utility.trace('annual_prem : '||l_cmi_prem);
1742 end if;
1743 --
1744 pay_jp_isdf_dml_pkg.create_care_med(
1745 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
1746 p_assignment_action_id => p_assignment_action_id,
1747 p_action_context_type => 'AAP',
1748 p_assignment_id => p_assignment_id,
1749 p_effective_date => g_effective_date,
1750 p_action_information_category => 'JP_ISDF_CARE_MED',
1751 p_status => 'Q',
1752 p_assignment_extra_info_id => l_li_info_rec.assignment_extra_info_id,
1753 p_aei_object_version_number => l_li_info_rec.aei_object_version_number,
1754 p_care_ins_class => l_li_info_rec.ins_class,
1755 p_care_ins_company_code => l_li_info_rec.ins_comp_code,
1756 p_ins_company_name => l_li_info_rec.ins_comp_name,
1757 p_ins_type => l_li_info_rec.ins_type,
1758 p_ins_period => l_li_info_rec.ins_period,
1759 p_contractor_name => l_li_info_rec.contractor_name,
1760 p_beneficiary_name => l_li_info_rec.beneficiary_name,
1761 p_beneficiary_relship => l_li_info_rec.beneficiary_relship,
1762 p_annual_prem => l_cmi_prem,
1763 p_object_version_number => l_object_version_number,
1764 p_care_ins_old_new => l_li_info_rec.ins_class_old_new);
1765 --
1766 if g_debug then
1767 hr_utility.set_location(l_proc,88);
1768 end if;
1769 --
1770 end loop;
1771 close csr_cmi;
1772 --
1773
1774 if g_debug then
1775 hr_utility.trace('end create_care_med');
1776 hr_utility.set_location(l_proc,90);
1777 hr_utility.trace('start create_life_pens');
1778 end if;
1779 --
1780 open csr_pens;
1781 loop
1782 --
1783 fetch csr_pens into l_li_info_rec;
1784 exit when csr_pens%notfound;
1785 --
1786 if g_debug then
1787 hr_utility.set_location(l_proc,100);
1788 hr_utility.trace('assignment_extra_info_id : '||l_li_info_rec.assignment_extra_info_id);
1789 hr_utility.trace('aei_object_version_number : '||l_li_info_rec.aei_object_version_number);
1790 hr_utility.trace('info_type : '||l_li_info_rec.info_type);
1791 hr_utility.trace('pens_ins_class : '||l_li_info_rec.ins_class);
1792 hr_utility.trace('pens_ins_company_code : '||l_li_info_rec.ins_comp_code);
1793 hr_utility.trace('pens_ins_class_old_or_new : '||l_li_info_rec.ins_class_old_new);
1794 hr_utility.trace('ins_company_name : '||l_li_info_rec.ins_comp_name);
1795 hr_utility.trace('calc_prem_ff : '||l_li_info_rec.calc_prem_ff);
1796 hr_utility.trace('lip_prem_bal : '||l_li_info_rec.lip_prem_bal);
1797 hr_utility.trace('lip_prem_mth_ele : '||l_li_info_rec.lip_prem_mth_ele);
1798 hr_utility.trace('lip_prem_bon_ele : '||l_li_info_rec.lip_prem_bon_ele);
1799 hr_utility.trace('start_date : '||fnd_date.date_to_canonical(l_li_info_rec.start_date));
1800 hr_utility.trace('end_date : '||fnd_date.date_to_canonical(l_li_info_rec.end_date));
1801 hr_utility.trace('ins_type : '||l_li_info_rec.ins_type);
1802 hr_utility.trace('ins_period_start_date : '||fnd_date.date_to_canonical(l_li_info_rec.ins_period_start_date));
1803 hr_utility.trace('ins_period : '||l_li_info_rec.ins_period);
1804 hr_utility.trace('contractor_name : '||l_li_info_rec.contractor_name);
1805 hr_utility.trace('beneficiary_name : '||l_li_info_rec.beneficiary_name);
1806 hr_utility.trace('beneficiary_relship : '||l_li_info_rec.beneficiary_relship);
1807 hr_utility.trace('linc_prem : '||fnd_number.number_to_canonical(l_li_info_rec.linc_prem));
1808 end if;
1809 --
1810 l_lip_prem := null;
1811 --
1812 if l_li_info_rec.ins_class <> 'PC' then
1813 --
1814 calc_li_annual_prem(
1815 p_ins_info_rec => l_li_info_rec,
1816 p_lig_prem => l_lig_prem,
1817 p_lip_prem => l_lip_prem,
1818 p_cmi_prem => l_cmi_prem,
1819 p_message => l_message);
1820 --
1821 end if;
1822 --
1823 if g_debug then
1824 hr_utility.set_location(l_proc,110);
1825 hr_utility.trace('annual_prem : '||l_lip_prem);
1826 end if;
1827 --
1828 pay_jp_isdf_dml_pkg.create_life_pens(
1829 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
1830 p_assignment_action_id => p_assignment_action_id,
1831 p_action_context_type => 'AAP',
1832 p_assignment_id => p_assignment_id,
1833 p_effective_date => g_effective_date,
1834 p_action_information_category => 'JP_ISDF_LIFE_PENS',
1835 p_status => 'Q',
1836 p_assignment_extra_info_id => l_li_info_rec.assignment_extra_info_id,
1837 p_aei_object_version_number => l_li_info_rec.aei_object_version_number,
1838 p_pens_ins_class => l_li_info_rec.ins_class,
1839 p_pens_ins_company_code => l_li_info_rec.ins_comp_code,
1840 p_ins_company_name => l_li_info_rec.ins_comp_name,
1841 p_ins_type => l_li_info_rec.ins_type,
1842 p_ins_period_start_date => l_li_info_rec.ins_period_start_date,
1843 p_ins_period => l_li_info_rec.ins_period,
1844 p_contractor_name => l_li_info_rec.contractor_name,
1845 p_beneficiary_name => l_li_info_rec.beneficiary_name,
1846 p_beneficiary_relship => l_li_info_rec.beneficiary_relship,
1847 p_annual_prem => l_lip_prem,
1848 p_object_version_number => l_object_version_number,
1849 p_pens_ins_old_new => l_li_info_rec.ins_class_old_new);
1850 --
1851 if g_debug then
1852 hr_utility.set_location(l_proc,120);
1853 end if;
1854 --
1855 end loop;
1856 close csr_pens;
1857 --
1858 if g_debug then
1859 hr_utility.trace('end create_life_pens');
1860 hr_utility.set_location(l_proc,130);
1861 hr_utility.trace('start create_nonlife');
1862 end if;
1863 --
1864 open csr_nonlife;
1865 loop
1866 --
1867 fetch csr_nonlife into l_ai_info_rec;
1868 exit when csr_nonlife%notfound;
1869 --
1870 if g_debug then
1871 hr_utility.set_location(l_proc,140);
1872 hr_utility.trace('assignment_extra_info_id : '||l_ai_info_rec.assignment_extra_info_id);
1873 hr_utility.trace('aei_object_version_number : '||l_ai_info_rec.aei_object_version_number);
1874 hr_utility.trace('info_type : '||l_ai_info_rec.info_type);
1875 hr_utility.trace('nonlife_ins_class : '||l_ai_info_rec.ins_class);
1876 hr_utility.trace('nonlife_term_type : '||l_ai_info_rec.ins_term_type);
1877 hr_utility.trace('nonlife_ins_company_code : '||l_ai_info_rec.ins_comp_code);
1878 hr_utility.trace('ins_company_name : '||l_ai_info_rec.ins_comp_name);
1879 hr_utility.trace('calc_prem_ff : '||l_ai_info_rec.calc_prem_ff);
1880 hr_utility.trace('eqi_prem_bal : '||l_ai_info_rec.eqi_prem_bal);
1881 hr_utility.trace('eqi_prem_mth_ele : '||l_ai_info_rec.eqi_prem_mth_ele);
1882 hr_utility.trace('eqi_prem_bon_ele : '||l_ai_info_rec.eqi_prem_bon_ele);
1883 hr_utility.trace('ai_prem_bal : '||l_ai_info_rec.ai_prem_bal);
1884 hr_utility.trace('ai_prem_mth_ele : '||l_ai_info_rec.ai_prem_mth_ele);
1885 hr_utility.trace('ai_prem_bon_ele : '||l_ai_info_rec.ai_prem_bon_ele);
1886 hr_utility.trace('start_date : '||fnd_date.date_to_canonical(l_ai_info_rec.start_date));
1887 hr_utility.trace('end_date : '||fnd_date.date_to_canonical(l_ai_info_rec.end_date));
1888 hr_utility.trace('ins_type : '||l_ai_info_rec.ins_type);
1889 hr_utility.trace('ins_period : '||l_ai_info_rec.ins_period);
1890 hr_utility.trace('contractor_name : '||l_ai_info_rec.contractor_name);
1891 hr_utility.trace('beneficiary_name : '||l_ai_info_rec.beneficiary_name);
1892 hr_utility.trace('beneficiary_relship : '||l_ai_info_rec.beneficiary_relship);
1893 hr_utility.trace('maturity_repayment : '||l_ai_info_rec.maturity_repayment);
1894 hr_utility.trace('annual_prem : '||fnd_number.number_to_canonical(l_ai_info_rec.annual_prem));
1895 end if;
1896 --
1897 l_ai_prem := null;
1898 l_eqi_prem := null;
1899 l_nli_prem := null;
1900 --
1901 if l_ai_info_rec.ins_class <> 'PC' then
1902 --
1903 calc_ai_annual_prem(
1904 p_ins_info_rec => l_ai_info_rec,
1905 p_eqi_prem => l_eqi_prem,
1906 p_ai_prem => l_nli_prem,
1907 p_message => l_message);
1908 --
1909 if l_ai_info_rec.ins_term_type = 'EQ' then
1910 l_ai_prem := l_eqi_prem;
1911 else
1912 l_ai_prem := l_nli_prem;
1913 end if;
1914 --
1915 end if;
1916 --
1917 if g_debug then
1918 hr_utility.set_location(l_proc,150);
1919 hr_utility.trace('ai annual_prem : '||l_ai_prem);
1920 hr_utility.trace('eqi annual_prem : '||l_eqi_prem);
1921 hr_utility.trace('nli annual_prem : '||l_nli_prem);
1922 end if;
1923 --
1924 pay_jp_isdf_dml_pkg.create_nonlife(
1925 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
1926 p_assignment_action_id => p_assignment_action_id,
1927 p_action_context_type => 'AAP',
1928 p_assignment_id => p_assignment_id,
1929 p_effective_date => g_effective_date,
1930 p_action_information_category => 'JP_ISDF_NONLIFE',
1931 p_status => 'Q',
1932 p_assignment_extra_info_id => l_ai_info_rec.assignment_extra_info_id,
1933 p_aei_object_version_number => l_ai_info_rec.aei_object_version_number,
1934 p_nonlife_ins_class => l_ai_info_rec.ins_class,
1935 p_nonlife_ins_term_type => l_ai_info_rec.ins_term_type,
1936 p_nonlife_ins_company_code => l_ai_info_rec.ins_comp_code,
1937 p_ins_company_name => l_ai_info_rec.ins_comp_name,
1938 p_ins_type => l_ai_info_rec.ins_type,
1939 p_ins_period => l_ai_info_rec.ins_period,
1940 p_contractor_name => l_ai_info_rec.contractor_name,
1941 p_beneficiary_name => l_ai_info_rec.beneficiary_name,
1942 p_beneficiary_relship => l_ai_info_rec.beneficiary_relship,
1943 p_maturity_repayment => l_ai_info_rec.maturity_repayment,
1944 p_annual_prem => l_ai_prem,
1945 p_object_version_number => l_object_version_number);
1946 --
1947 if g_debug then
1948 hr_utility.set_location(l_proc,160);
1949 end if;
1950 --
1951 end loop;
1952 close csr_nonlife;
1953 --
1954 if g_debug then
1955 hr_utility.trace('end create_nonlife');
1956 hr_utility.set_location(l_proc,170);
1957 hr_utility.trace('start create_spouse');
1958 end if;
1959 --
1960 l_spouse_rec.spouse_type := null;
1961 l_spouse_rec.widow_type := null;
1962 l_spouse_rec.spouse_dct_exclude := null;
1963 l_spouse_rec.spouse_income_entry := null;
1964 --
1965 l_spouse_rec.spouse_income_entry := l_entry_rec.spouse_income;
1966 -- l_spouse_rec.spouse_income_entry := pay_jp_balance_pkg.get_entry_value_number(c_spouse_iv_id,p_assignment_id,g_effective_date);
1967 --
1968 if l_spouse_rec.spouse_income_entry is not null then
1969 --
1970 l_spouse_rec.widow_type := pay_jp_balance_pkg.get_entry_value_char(c_widow_type_iv_id,p_assignment_id,g_effective_date);
1971 l_spouse_rec.spouse_type := pay_jp_balance_pkg.get_entry_value_char(c_sp_type_iv_id,p_assignment_id,g_effective_date);
1972 l_spouse_rec.spouse_dct_exclude := l_entry_rec.sp_dct_exclude;
1973 -- l_spouse_rec.spouse_dct_exclude := pay_jp_balance_pkg.get_entry_value_char(c_sp_dct_exclude_iv_id,p_assignment_id,g_effective_date);
1974 --
1975 if l_spouse_rec.spouse_type is null then
1976 --
1977 if g_bg_itax_dpnt_ref_type = 'CEI' then
1978 --
1979 l_tax_type := pay_jp_balance_pkg.get_entry_value_char(c_tax_type_iv_id,p_assignment_id,g_effective_date);
1980 l_spouse_rec.spouse_type := per_jp_ctr_utility_pkg.get_itax_spouse_type(p_assignment_id,l_tax_type,g_effective_date);
1981 --
1982 end if;
1983 --
1984 end if;
1985 --
1986 pay_jp_isdf_dml_pkg.create_spouse(
1987 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
1988 p_assignment_action_id => p_assignment_action_id,
1989 p_action_context_type => 'AAP',
1990 p_assignment_id => p_assignment_id,
1991 p_effective_date => g_effective_date,
1992 p_action_information_category => 'JP_ISDF_SPOUSE',
1993 p_status => 'Q',
1994 p_full_name_kana => null,
1995 --p_last_name_kana => null,
1996 --p_first_name_kana => null,
1997 p_full_name => null,
1998 --p_last_name => null,
1999 --p_first_name => null,
2000 p_postal_code => null,
2001 p_address => null,
2002 p_emp_income => null,
2003 p_spouse_type => l_spouse_rec.spouse_type,
2004 p_widow_type => l_spouse_rec.widow_type,
2005 p_spouse_dct_exclude => l_spouse_rec.spouse_dct_exclude,
2006 p_spouse_income_entry => l_spouse_rec.spouse_income_entry,
2007 p_object_version_number => l_object_version_number);
2008 --
2009 end if;
2010 --
2011 if g_debug then
2012 hr_utility.trace('end create_spouse');
2013 hr_utility.set_location(l_proc,180);
2014 end if;
2015 --
2016 -- copy previous archive information to current archive
2017 -- this function will be useful in case of re-yea
2018 -- rather than remaking personal (revised) data by ss employee.
2019 --
2020 else
2021 --
2022 if g_debug then
2023 hr_utility.set_location(l_proc,190);
2024 hr_utility.trace('copy pact id : '||g_copy_archive_pact_id);
2025 end if;
2026 --
2027 open csr_copy_assact;
2028 fetch csr_copy_assact into l_copy_archive_assact_id;
2029 close csr_copy_assact;
2030 --
2031 if g_debug then
2032 hr_utility.set_location(l_proc,200);
2033 hr_utility.trace('copy assact id : '||l_copy_archive_assact_id);
2034 end if;
2035 --
2036 if l_copy_archive_assact_id is not null then
2037 --
2038 if g_debug then
2039 hr_utility.set_location(l_proc,210);
2040 hr_utility.trace('start copy life_gen');
2041 end if;
2042 --
2043 open csr_copy_life_gen;
2044 loop
2045 --
2046 fetch csr_copy_life_gen into l_csr_copy_life_gen;
2047 exit when csr_copy_life_gen%notfound;
2048 --
2049 pay_jp_isdf_dml_pkg.create_life_gen(
2050 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
2051 p_assignment_action_id => p_assignment_action_id,
2052 p_action_context_type => 'AAP',
2053 p_assignment_id => p_assignment_id,
2054 p_effective_date => g_effective_date,
2055 p_action_information_category => 'JP_ISDF_LIFE_GEN',
2056 p_status => l_csr_copy_life_gen.status,
2057 p_assignment_extra_info_id => l_csr_copy_life_gen.assignment_extra_info_id,
2058 p_aei_object_version_number => l_csr_copy_life_gen.aei_object_version_number,
2059 p_gen_ins_class => l_csr_copy_life_gen.gen_ins_class,
2060 p_gen_ins_company_code => l_csr_copy_life_gen.gen_ins_company_code,
2061 p_ins_company_name => l_csr_copy_life_gen.ins_company_name,
2062 p_ins_type => l_csr_copy_life_gen.ins_type,
2063 p_ins_period => l_csr_copy_life_gen.ins_period,
2064 p_contractor_name => l_csr_copy_life_gen.contractor_name,
2065 p_beneficiary_name => l_csr_copy_life_gen.beneficiary_name,
2066 p_beneficiary_relship => l_csr_copy_life_gen.beneficiary_relship,
2067 p_annual_prem => l_csr_copy_life_gen.annual_prem,
2068 p_object_version_number => l_object_version_number,
2069 p_gen_ins_old_new => l_csr_copy_life_gen.gen_ins_class_old_new);
2070 --
2071 end loop;
2072 close csr_copy_life_gen;
2073 --
2074 if g_debug then
2075 hr_utility.trace('end copy life_gen');
2076 hr_utility.set_location(l_proc,219);
2077 hr_utility.trace('start copy care_med');
2078 end if;
2079 --
2080 open csr_copy_care_med;
2081 loop
2082 --
2083 fetch csr_copy_care_med into l_csr_copy_care_med;
2084 exit when csr_copy_care_med%notfound;
2085 --
2086 pay_jp_isdf_dml_pkg.create_care_med(
2087 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
2088 p_assignment_action_id => p_assignment_action_id,
2089 p_action_context_type => 'AAP',
2090 p_assignment_id => p_assignment_id,
2091 p_effective_date => g_effective_date,
2092 p_action_information_category => 'JP_ISDF_CARE_MED',
2093 p_status => l_csr_copy_care_med.status,
2094 p_assignment_extra_info_id => l_csr_copy_care_med.assignment_extra_info_id,
2095 p_aei_object_version_number => l_csr_copy_care_med.aei_object_version_number,
2096 p_care_ins_class => l_csr_copy_care_med.care_ins_class,
2097 p_care_ins_company_code => l_csr_copy_care_med.care_ins_company_code,
2098 p_ins_company_name => l_csr_copy_care_med.ins_company_name,
2099 p_ins_type => l_csr_copy_care_med.ins_type,
2100 p_ins_period => l_csr_copy_care_med.ins_period,
2101 p_contractor_name => l_csr_copy_care_med.contractor_name,
2102 p_beneficiary_name => l_csr_copy_care_med.beneficiary_name,
2103 p_beneficiary_relship => l_csr_copy_care_med.beneficiary_relship,
2104 p_annual_prem => l_csr_copy_care_med.annual_prem,
2105 p_object_version_number => l_object_version_number,
2106 p_care_ins_old_new => l_csr_copy_care_med.care_ins_class_old_new);
2107 --
2108 end loop;
2109 close csr_copy_care_med;
2110
2111 if g_debug then
2112 hr_utility.trace('end copy care_med');
2113 hr_utility.set_location(l_proc,220);
2114 hr_utility.trace('start copy life_pens');
2115 end if;
2116 --
2117
2118 open csr_copy_life_pens;
2119 loop
2120 --
2121 fetch csr_copy_life_pens into l_csr_copy_life_pens;
2122 exit when csr_copy_life_pens%notfound;
2123 --
2124 pay_jp_isdf_dml_pkg.create_life_pens(
2125 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
2126 p_assignment_action_id => p_assignment_action_id,
2127 p_action_context_type => 'AAP',
2128 p_assignment_id => p_assignment_id,
2129 p_effective_date => g_effective_date,
2130 p_action_information_category => 'JP_ISDF_LIFE_PENS',
2131 p_status => l_csr_copy_life_pens.status,
2132 p_assignment_extra_info_id => l_csr_copy_life_pens.assignment_extra_info_id,
2133 p_aei_object_version_number => l_csr_copy_life_pens.aei_object_version_number,
2134 p_pens_ins_class => l_csr_copy_life_pens.pens_ins_class,
2135 p_pens_ins_company_code => l_csr_copy_life_pens.pens_ins_company_code,
2136 p_ins_company_name => l_csr_copy_life_pens.ins_company_name,
2137 p_ins_type => l_csr_copy_life_pens.ins_type,
2138 p_ins_period_start_date => l_csr_copy_life_pens.ins_period_start_date,
2139 p_ins_period => l_csr_copy_life_pens.ins_period,
2140 p_contractor_name => l_csr_copy_life_pens.contractor_name,
2141 p_beneficiary_name => l_csr_copy_life_pens.beneficiary_name,
2142 p_beneficiary_relship => l_csr_copy_life_pens.beneficiary_relship,
2143 p_annual_prem => l_csr_copy_life_pens.annual_prem,
2144 p_object_version_number => l_object_version_number,
2145 p_pens_ins_old_new => l_csr_copy_life_pens.pens_ins_class_old_new);
2146 --
2147 end loop;
2148 close csr_copy_life_pens;
2149 --
2150 if g_debug then
2151 hr_utility.trace('end copy life_pens');
2152 hr_utility.set_location(l_proc,230);
2153 hr_utility.trace('start copy nonlife');
2154 end if;
2155 --
2156 open csr_copy_nonlife;
2157 loop
2158 --
2159 fetch csr_copy_nonlife into l_csr_copy_nonlife;
2160 exit when csr_copy_nonlife%notfound;
2161 --
2162 pay_jp_isdf_dml_pkg.create_nonlife(
2163 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
2164 p_assignment_action_id => p_assignment_action_id,
2165 p_action_context_type => 'AAP',
2166 p_assignment_id => p_assignment_id,
2167 p_effective_date => g_effective_date,
2168 p_action_information_category => 'JP_ISDF_NONLIFE',
2169 p_status => l_csr_copy_nonlife.status,
2170 p_assignment_extra_info_id => l_csr_copy_nonlife.assignment_extra_info_id,
2171 p_aei_object_version_number => l_csr_copy_nonlife.aei_object_version_number,
2172 p_nonlife_ins_class => l_csr_copy_nonlife.nonlife_ins_class,
2173 p_nonlife_ins_term_type => l_csr_copy_nonlife.nonlife_ins_term_type,
2174 p_nonlife_ins_company_code => l_csr_copy_nonlife.nonlife_ins_company_code,
2175 p_ins_company_name => l_csr_copy_nonlife.ins_company_name,
2176 p_ins_type => l_csr_copy_nonlife.ins_type,
2177 p_ins_period => l_csr_copy_nonlife.ins_period,
2178 p_contractor_name => l_csr_copy_nonlife.contractor_name,
2179 p_beneficiary_name => l_csr_copy_nonlife.beneficiary_name,
2180 p_beneficiary_relship => l_csr_copy_nonlife.beneficiary_relship,
2181 p_maturity_repayment => l_csr_copy_nonlife.maturity_repayment,
2182 p_annual_prem => l_csr_copy_nonlife.annual_prem,
2183 p_object_version_number => l_object_version_number);
2184 --
2185 end loop;
2186 close csr_copy_nonlife;
2187 --
2188 if g_debug then
2189 hr_utility.trace('end copy nonlife');
2190 hr_utility.set_location(l_proc,240);
2191 hr_utility.trace('start copy social');
2192 end if;
2193 --
2194 open csr_copy_social;
2195 loop
2196 --
2197 fetch csr_copy_social into l_csr_copy_social;
2198 exit when csr_copy_social%notfound;
2199 --
2200 pay_jp_isdf_dml_pkg.create_social(
2201 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
2202 p_assignment_action_id => p_assignment_action_id,
2203 p_action_context_type => 'AAP',
2204 p_assignment_id => p_assignment_id,
2205 p_effective_date => g_effective_date,
2206 p_action_information_category => 'JP_ISDF_SOCIAL',
2207 p_status => l_csr_copy_social.status,
2208 p_ins_type => l_csr_copy_social.ins_type,
2209 p_ins_payee_name => l_csr_copy_social.ins_payee_name,
2210 p_debtor_name => l_csr_copy_social.debtor_name,
2211 p_beneficiary_relship => l_csr_copy_social.beneficiary_relship,
2212 p_annual_prem => l_csr_copy_social.annual_prem,
2213 p_national_pens_flag => l_csr_copy_social.national_pens_flag,
2214 p_object_version_number => l_object_version_number);
2215 --
2216 end loop;
2217 close csr_copy_social;
2218 --
2219 if g_debug then
2220 hr_utility.trace('end copy social');
2221 hr_utility.set_location(l_proc,250);
2222 hr_utility.trace('start copy mutual_aid');
2223 end if;
2224 --
2225 open csr_copy_mutual_aid;
2226 loop
2227 --
2228 fetch csr_copy_mutual_aid into l_csr_copy_mutual_aid;
2229 exit when csr_copy_mutual_aid%notfound;
2230 --
2231 pay_jp_isdf_dml_pkg.create_mutual_aid(
2232 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
2233 p_assignment_action_id => p_assignment_action_id,
2234 p_action_context_type => 'AAP',
2235 p_assignment_id => p_assignment_id,
2236 p_effective_date => g_effective_date,
2237 p_action_information_category => 'JP_ISDF_MUTUAL_AID',
2238 p_status => l_csr_copy_mutual_aid.status,
2239 p_enterprise_contract_prem => l_csr_copy_mutual_aid.enterprise_contract_prem,
2240 p_pension_prem => l_csr_copy_mutual_aid.pension_prem,
2241 p_disable_sup_contract_prem => l_csr_copy_mutual_aid.disable_sup_contract_prem,
2242 p_object_version_number => l_object_version_number);
2243 --
2244 end loop;
2245 close csr_copy_mutual_aid;
2246 --
2247 if g_debug then
2248 hr_utility.trace('end copy mutual_aid');
2249 hr_utility.set_location(l_proc,260);
2250 hr_utility.trace('start copy spouse');
2251 end if;
2252 --
2253 open csr_copy_spouse;
2254 loop
2255 --
2256 fetch csr_copy_spouse into l_csr_copy_spouse;
2257 exit when csr_copy_spouse%notfound;
2258 --
2259 pay_jp_isdf_dml_pkg.create_spouse(
2260 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
2261 p_assignment_action_id => p_assignment_action_id,
2262 p_action_context_type => 'AAP',
2263 p_assignment_id => p_assignment_id,
2264 p_effective_date => g_effective_date,
2265 p_action_information_category => 'JP_ISDF_SPOUSE',
2266 p_status => l_csr_copy_spouse.status,
2267 p_full_name_kana => l_csr_copy_spouse.full_name_kana,
2268 p_full_name => l_csr_copy_spouse.full_name,
2269 p_postal_code => l_csr_copy_spouse.postal_code,
2270 p_address => l_csr_copy_spouse.address,
2271 p_emp_income => l_csr_copy_spouse.emp_income,
2272 p_spouse_type => l_csr_copy_spouse.spouse_type,
2273 p_widow_type => l_csr_copy_spouse.widow_type,
2274 p_spouse_dct_exclude => l_csr_copy_spouse.spouse_dct_exclude,
2275 p_spouse_income_entry => l_csr_copy_spouse.spouse_income_entry,
2276 p_object_version_number => l_object_version_number);
2277 --
2278 end loop;
2279 close csr_copy_spouse;
2280 --
2281 if g_debug then
2282 hr_utility.trace('end copy spouse');
2283 hr_utility.set_location(l_proc,270);
2284 hr_utility.trace('start copy spouse_inc');
2285 end if;
2286 --
2287 open csr_copy_spouse_inc;
2288 loop
2289 --
2290 fetch csr_copy_spouse_inc into l_csr_copy_spouse_inc;
2291 exit when csr_copy_spouse_inc%notfound;
2292 --
2293 pay_jp_isdf_dml_pkg.create_spouse_inc(
2294 p_action_information_id => pay_jp_isdf_dml_pkg.next_action_information_id,
2295 p_assignment_action_id => p_assignment_action_id,
2296 p_action_context_type => 'AAP',
2297 p_assignment_id => p_assignment_id,
2298 p_effective_date => g_effective_date,
2299 p_action_information_category => 'JP_ISDF_SPOUSE_INC',
2300 p_status => l_csr_copy_spouse_inc.status,
2301 p_sp_earned_income => l_csr_copy_spouse_inc.sp_earned_income,
2302 p_sp_earned_income_exp => l_csr_copy_spouse_inc.sp_earned_income_exp,
2303 p_sp_business_income => l_csr_copy_spouse_inc.sp_business_income,
2304 p_sp_business_income_exp => l_csr_copy_spouse_inc.sp_business_income_exp,
2305 p_sp_miscellaneous_income => l_csr_copy_spouse_inc.sp_miscellaneous_income,
2306 p_sp_miscellaneous_income_exp => l_csr_copy_spouse_inc.sp_miscellaneous_income_exp,
2307 p_sp_dividend_income => l_csr_copy_spouse_inc.sp_dividend_income,
2308 p_sp_dividend_income_exp => l_csr_copy_spouse_inc.sp_dividend_income_exp,
2309 p_sp_real_estate_income => l_csr_copy_spouse_inc.sp_real_estate_income,
2310 p_sp_real_estate_income_exp => l_csr_copy_spouse_inc.sp_real_estate_income_exp,
2311 p_sp_retirement_income => l_csr_copy_spouse_inc.sp_retirement_income,
2312 p_sp_retirement_income_exp => l_csr_copy_spouse_inc.sp_retirement_income_exp,
2313 p_sp_other_income => l_csr_copy_spouse_inc.sp_other_income,
2314 p_sp_other_income_exp => l_csr_copy_spouse_inc.sp_other_income_exp,
2315 p_sp_other_income_exp_dct => l_csr_copy_spouse_inc.sp_other_income_exp_dct,
2316 p_sp_other_income_exp_temp => l_csr_copy_spouse_inc.sp_other_income_exp_temp,
2317 p_sp_other_income_exp_temp_exp=> l_csr_copy_spouse_inc.sp_other_income_exp_temp_exp,
2318 p_object_version_number => l_object_version_number);
2319 --
2320 end loop;
2321 close csr_copy_spouse_inc;
2322 --
2323 if g_debug then
2324 hr_utility.trace('end copy spouse_inc');
2325 hr_utility.set_location(l_proc,280);
2326 end if;
2327 --
2328 end if;
2329 --
2330 if g_debug then
2331 hr_utility.set_location(l_proc,290);
2332 end if;
2333 --
2334 end if;
2335 --
2336 if g_debug then
2337 hr_utility.set_location(l_proc,300);
2338 end if;
2339 --
2340 end if;
2341 --
2342 if g_debug then
2343 hr_utility.set_location(l_proc,1000);
2344 end if;
2345 --
2346 end archive_assact;
2347 --
2348 -- -------------------------------------------------------------------------
2349 -- post_assact
2350 -- -------------------------------------------------------------------------
2351 procedure post_assact(
2352 p_action_information_id in number,
2353 p_object_version_number in out nocopy number)
2354 is
2355 --
2356 l_proc varchar2(80) := c_package||'post_assact';
2357 --
2358 begin
2359 --
2360 if g_debug then
2361 hr_utility.set_location(l_proc,0);
2362 end if;
2363 --
2364 if g_archive_default_flag = 'Y' then
2365 --
2366 if g_debug then
2367 hr_utility.set_location(l_proc,10);
2368 hr_utility.trace('action_information_id : '||p_action_information_id);
2369 hr_utility.trace('object_version_number : '||p_object_version_number);
2370 end if;
2371 --
2372 -- include lock assact (only transaction,ovn will be changed, others same as new condition)
2373 pay_jp_isdf_dml_pkg.update_assact(
2374 p_action_information_id => p_action_information_id,
2375 p_object_version_number => p_object_version_number,
2376 p_transaction_status => 'N',
2377 p_finalized_date => null,
2378 p_finalized_by => null,
2379 p_user_comments => null,
2380 p_admin_comments => null,
2381 p_transfer_status => 'U',
2382 p_transfer_date => null,
2383 p_expiry_date => null);
2384 --
2385 if g_debug then
2386 hr_utility.set_location(l_proc,20);
2387 end if;
2388 --
2389 end if;
2390 --
2391 if g_debug then
2392 hr_utility.set_location(l_proc,1000);
2393 end if;
2394 --
2395 end post_assact;
2396 --
2397 -- -------------------------------------------------------------------------
2398 -- archive_data
2399 -- -------------------------------------------------------------------------
2400 procedure archive_data(
2401 p_assignment_action_id in number,
2402 p_effective_date in date)
2403 is
2404 --
2405 l_proc varchar2(80) := c_package||'archive_data';
2406 --
2407 l_assignment_id number;
2408 l_tax_type pay_element_entry_values_f.screen_entry_value%type;
2409 --
2410 l_action_information_id number;
2411 l_object_version_number number;
2412 --
2413 begin
2414 --
2415 if g_debug then
2416 hr_utility.set_location(l_proc,0);
2417 end if;
2418 --
2419 select assignment_id
2420 into l_assignment_id
2421 from pay_assignment_actions
2422 where assignment_action_id = p_assignment_action_id;
2423 --
2424 if g_debug then
2425 hr_utility.set_location(l_proc,10);
2426 end if;
2427 --
2428 -- set context.
2429 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_effective_date));
2430 pay_balance_pkg.set_context('ASSIGNMENT_ID',fnd_number.number_to_canonical(l_assignment_id));
2431 l_tax_type := pay_balance_pkg.run_db_item(c_tax_type_iv_name,g_business_group_id,g_legislation_code);
2432 --
2433 if g_debug then
2434 hr_utility.set_location(l_proc,20);
2435 hr_utility.trace('date_earned : '||fnd_date.date_to_canonical(p_effective_date));
2436 hr_utility.trace('assignment_id : '||l_assignment_id);
2437 hr_utility.trace('tax_type : '||l_tax_type);
2438 end if;
2439 --
2440 if l_tax_type not in ('M_KOU','D_KOU') then
2441 --
2442 fnd_message.set_name('PAY','PAY_JP_INVALID_TAX_TYPE');
2443 fnd_message.raise_error;
2444 --
2445 else
2446 --
2447 if g_debug then
2448 hr_utility.set_location(l_proc,30);
2449 hr_utility.trace('start create_assact');
2450 end if;
2451 --
2452 l_action_information_id := pay_jp_isdf_dml_pkg.next_action_information_id;
2453 --
2454 pay_jp_isdf_dml_pkg.create_assact(
2455 p_action_information_id => l_action_information_id,
2456 p_assignment_action_id => p_assignment_action_id,
2457 p_action_context_type => 'AAP',
2458 p_assignment_id => l_assignment_id,
2459 p_effective_date => p_effective_date,
2460 p_action_information_category => 'JP_ISDF_ASSACT',
2461 p_tax_type => l_tax_type,
2462 p_transaction_status => 'U',
2463 p_finalized_date => null,
2464 p_finalized_by => null,
2465 p_user_comments => null,
2466 p_admin_comments => null,
2467 p_transfer_status => 'U',
2468 p_transfer_date => null,
2469 p_expiry_date => null,
2470 p_object_version_number => l_object_version_number);
2471 --
2472 if g_debug then
2473 hr_utility.trace('end create_assact');
2474 hr_utility.set_location(l_proc,40);
2475 end if;
2476 --
2477 init_assact(
2478 p_assignment_action_id => p_assignment_action_id,
2479 p_assignment_id => l_assignment_id);
2480 --
2481 if g_debug then
2482 hr_utility.set_location(l_proc,50);
2483 end if;
2484 --
2485 archive_assact(
2486 p_assignment_action_id => p_assignment_action_id,
2487 p_assignment_id => l_assignment_id);
2488 --
2489 if g_debug then
2490 hr_utility.set_location(l_proc,60);
2491 hr_utility.trace('assignment_action_id : '||p_assignment_action_id);
2492 hr_utility.trace('action_information_id : '||l_action_information_id);
2493 hr_utility.trace('object_version_number : '||l_object_version_number);
2494 end if;
2495 --
2496 -- update transaction status from U to N because archive has been made.
2497 post_assact(
2498 p_action_information_id => l_action_information_id,
2499 p_object_version_number => l_object_version_number);
2500 --
2501 if g_debug then
2502 hr_utility.set_location(l_proc,70);
2503 end if;
2504 --
2505 end if;
2506 --
2507 if g_debug then
2508 hr_utility.set_location(l_proc,1000);
2509 end if;
2510 --
2511 end archive_data;
2512 --
2513 -- -------------------------------------------------------------------------
2514 -- deinitialize_code
2515 -- -------------------------------------------------------------------------
2516 procedure deinitialize_code(
2517 p_payroll_action_id in number)
2518 is
2519 --
2520 l_proc varchar2(80) := c_package||'deinitialize_code';
2521 --
2522 begin
2523 --
2524 if g_debug then
2525 hr_utility.set_location(l_proc,0);
2526 end if;
2527 --
2528 -- invoke in case of mark for retry.
2529 init_pact(p_payroll_action_id);
2530 --
2531 if g_debug then
2532 hr_utility.set_location(l_proc,10);
2533 end if;
2534 --
2535 archive_pact(p_payroll_action_id);
2536 --
2537 if g_debug then
2538 hr_utility.set_location(l_proc,1000);
2539 end if;
2540 --
2541 end deinitialize_code;
2542 --
2543 end pay_jp_isdf_archive_pkg;