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