[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ISDF_SS_PKG
Source
1 package body pay_jp_isdf_ss_pkg as
2 /* $Header: pyjpisfs.pkb 120.9 2007/11/29 08:41:54 keyazawa noship $ */
3 --
4 c_package constant varchar2(30) := 'pay_jp_isdf_ss_pkg.';
5 g_debug boolean := hr_utility.debug_enabled;
6 --
7 c_isdf_ins_elm constant varchar2(80) := 'YEA_INS_PREM_EXM_DECLARE_INFO';
8 c_isdf_ins_elm_id constant number := hr_jp_id_pkg.element_type_id(c_isdf_ins_elm, null, 'JP');
9 c_life_gen_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'GEN_LIFE_INS_PREM');
10 c_life_pens_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'INDIVIDUAL_PENSION_PREM');
11 c_nonlife_long_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'LONG_TERM_NONLIFE_INS_PREM');
12 c_nonlife_short_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'SHORT_TERM_NONLIFE_INS_PREM');
13 c_earthquake_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_ins_elm_id, 'EARTHQUAKE_INS_PREM');
14 --
15 c_isdf_is_elm constant varchar2(80) := 'YEA_INS_PREM_SPOUSE_SP_EXM_INFO';
16 c_isdf_is_elm_id constant number := hr_jp_id_pkg.element_type_id(c_isdf_is_elm, null, 'JP');
17 c_social_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'DECLARE_SI_PREM');
18 c_mutual_aid_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'SMALL_COMPANY_MUTUAL_AID_PREM');
19 c_spouse_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'SPOUSE_INCOME');
20 c_national_pens_iv_id constant number := hr_jp_id_pkg.input_value_id(c_isdf_is_elm_id, 'NATIONAL_PENSION_PREM');
21 --
22 c_com_calc_dpnt_elm_id constant number := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_PROC', null, 'JP');
23 c_sp_type_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_calc_dpnt_elm_id, 'SPOUSE_TYPE');
24 c_widow_type_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_calc_dpnt_elm_id, 'WIDOW_TYPE');
25 --
26 c_com_itax_info_elm_id constant number := hr_jp_id_pkg.element_type_id('COM_ITX_INFO', null, 'JP');
27 c_tax_type_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_itax_info_elm_id, 'ITX_TYPE');
28 --
29 c_st_upd_date_2007 constant date := to_date('2007/01/01','YYYY/MM/DD');
30 --
31 -- -------------------------------------------------------------------------
32 -- check_submission_period
33 -- -------------------------------------------------------------------------
34 function check_submission_period(
35 p_action_information_id in number)
36 return date
37 is
38 --
39 l_submission_date date;
40 --
41 cursor csr_pact
42 is
43 select /*+ ORDERED */
44 pact.submission_period_status,
45 pact.submission_start_date,
46 pact.submission_end_date
47 from pay_jp_isdf_assact_v assact,
48 pay_assignment_actions paa,
49 pay_jp_isdf_pact_v pact
50 where assact.action_information_id = p_action_information_id
51 and paa.assignment_action_id = assact.assignment_action_id
52 and pact.payroll_action_id = paa.payroll_action_id;
53 --
54 l_csr_pact csr_pact%rowtype;
55 --
56 begin
57 --
58 open csr_pact;
59 fetch csr_pact into l_csr_pact;
60 close csr_pact;
61 --
62 if l_csr_pact.submission_period_status = 'C' then
63 fnd_message.set_name('PAY','PAY_JP_DEF_PERIOD_CLOSED');
64 fnd_message.raise_error;
65 end if;
66 --
67 l_submission_date := sysdate;
68 --
69 if l_submission_date < nvl(l_csr_pact.submission_start_date,l_submission_date) then
70 fnd_message.set_name('PAY','PAY_JP_DEF_PERIOD_NOT_STARTED');
71 fnd_message.raise_error;
72 end if;
73 --
74 if l_submission_date > nvl(l_csr_pact.submission_end_date,l_submission_date) then
75 fnd_message.set_name('PAY','PAY_JP_DEF_PERIOD_EXPIRED');
76 fnd_message.raise_error;
77 end if;
78 --
79 return l_submission_date;
80 --
81 end check_submission_period;
82 --
83 -- -------------------------------------------------------------------------
84 -- get_spouse_type
85 -- -------------------------------------------------------------------------
86 function get_spouse_type(
87 p_assignment_id in number,
88 p_effective_date in date,
89 p_payroll_id in number)
90 return varchar2
91 is
92 --
93 l_spouse_type pay_element_entry_values_f.screen_entry_value%type;
94 l_tax_type pay_element_entry_values_f.screen_entry_value%type;
95 l_bg_itax_dpnt_ref_type varchar2(150);
96 --
97 cursor csr_bg_itax_dpnt_ref_type
98 is
99 select /*+ ORDERED */
100 nvl(nvl(pp.prl_information1, hoi.org_information2),'CTR_EE')
101 from pay_all_payrolls_f pp,
102 hr_organization_information hoi
103 where pp.payroll_id = p_payroll_id
104 and p_effective_date
105 between pp.effective_start_date and pp.effective_end_date
106 and hoi.organization_id(+) = pp.business_group_id
107 and hoi.org_information_context(+) = 'JP_BUSINESS_GROUP_INFO';
108 --
109 begin
110 --
111 l_spouse_type := pay_jp_balance_pkg.get_entry_value_char(c_sp_type_iv_id,p_assignment_id,p_effective_date);
112 --
113 if l_spouse_type is null then
114 --
115 open csr_bg_itax_dpnt_ref_type;
116 fetch csr_bg_itax_dpnt_ref_type into l_bg_itax_dpnt_ref_type;
117 close csr_bg_itax_dpnt_ref_type;
118 --
119 if l_bg_itax_dpnt_ref_type = 'CEI' then
120 --
121 l_tax_type := pay_jp_balance_pkg.get_entry_value_char(c_tax_type_iv_id,p_assignment_id,p_effective_date);
122 l_spouse_type := per_jp_ctr_utility_pkg.get_itax_spouse_type(p_assignment_id,l_tax_type,p_effective_date);
123 --
124 end if;
125 --
126 end if;
127 --
128 return l_spouse_type;
129 --
130 end get_spouse_type;
131 --
132 -- -------------------------------------------------------------------------
133 -- get_widow_type
134 -- -------------------------------------------------------------------------
135 function get_widow_type(
136 p_assignment_id in number,
137 p_effective_date in date)
138 return varchar2
139 is
140 l_widow_type pay_element_entry_values_f.screen_entry_value%type;
141 begin
142 --
143 l_widow_type := pay_jp_balance_pkg.get_entry_value_char(c_widow_type_iv_id,p_assignment_id,p_effective_date);
144 --
145 return l_widow_type;
146 --
147 end get_widow_type;
148 --
149 -- -------------------------------------------------------------------------
150 -- set_form_pg_prompt
151 -- -------------------------------------------------------------------------
152 procedure set_form_pg_prompt(
153 p_action_information_id in number)
154 is
155 --
156 l_proc varchar2(80) := c_package||'get_formpg_prompt';
157 --
158 l_payroll_action_id number;
159 l_business_group_id number;
160 l_effective_date date;
161 --
162 l_legislation_code varchar2(2);
163 l_rate pay_user_column_instances_f.value%type;
164 l_add_adj pay_user_column_instances_f.value%type;
165 l_lnonlife_calc3 pay_user_column_instances_f.value%type;
166 l_snonlife_calc3 pay_user_column_instances_f.value%type;
167 l_sp_calc_other_inc_calc_rate pay_user_column_instances_f.value%type;
168 l_dct_cnt number := 0;
169 l_nonlife_max pay_user_column_instances_f.value%type;
170 l_nonlife_max_2007 pay_user_column_instances_f.value%type;
171 --
172 type t_sp_calc_rec is record(
173 range_a pay_user_rows_f.row_low_range_or_name%type,
174 range_b pay_user_rows_f.row_low_range_or_name%type,
175 val pay_user_column_instances_f.value%type);
176 type t_sp_calc_tbl is table of t_sp_calc_rec index by binary_integer;
177 l_sp_calc_tbl t_sp_calc_tbl;
178 --
179 cursor csr_pact
180 is
181 select /* +ORDERED */
182 ppa.payroll_action_id,
183 ppa.business_group_id,
184 ppa.effective_date
185 from pay_jp_isdf_assact_v pjia,
186 pay_assignment_actions paa,
187 pay_payroll_actions ppa
188 where pjia.action_information_id = p_action_information_id
189 and paa.assignment_action_id = pjia.assignment_action_id
190 and ppa.payroll_action_id = paa.payroll_action_id;
191 --
192 cursor csr_udt_row(
193 p_udt_name in varchar2,
194 p_effective_date in date)
195 is
196 select /* +ORDERED */
197 put.user_table_id,
198 pur.user_row_id,
199 pur.display_sequence,
200 pur.row_low_range_or_name,
201 pur.row_high_range
202 from pay_user_tables put,
203 pay_user_rows_f pur
204 where put.user_table_name = p_udt_name
205 and nvl(put.legislation_code,'X') = nvl(l_legislation_code,nvl(put.legislation_code,'X'))
206 and pur.user_table_id = put.user_table_id
207 and p_effective_date
208 between pur.effective_start_date and pur.effective_end_date
209 order by 3, fnd_number.canonical_to_number(pur.row_low_range_or_name);
210 --
211 cursor csr_udt_val(
212 p_user_table_id in number,
213 p_row_id in number,
214 p_effective_date in date)
215 is
216 select /* +ORDERED */
217 puc.user_column_name,
218 puci.value
219 from pay_user_columns puc,
220 pay_user_column_instances_f puci
221 where puc.user_table_id = p_user_table_id
222 and puci.user_column_id = puc.user_column_id
223 and puci.user_row_id = p_row_id
224 and p_effective_date
225 between puci.effective_start_date and puci.effective_end_date
226 order by 1;
227 --
228 l_csr_udt_row csr_udt_row%rowtype;
229 l_csr_udt_val csr_udt_val%rowtype;
230 --
231 begin
232 --
233 if g_debug then
234 hr_utility.set_location(l_proc,0);
235 end if;
236 --
237 open csr_pact;
238 fetch csr_pact into l_payroll_action_id, l_business_group_id, l_effective_date;
239 close csr_pact;
240 --
241 -- cache in case payroll_action_id is same.
242 if g_payroll_action_id is null
243 or (g_payroll_action_id <> l_payroll_action_id
244 and l_payroll_action_id is not null) then
245 --
246 g_payroll_action_id := l_payroll_action_id;
247 g_business_group_id := l_business_group_id;
248 g_effective_date := l_effective_date;
249 --
250 l_legislation_code := hr_jp_id_pkg.legislation_code(g_business_group_id);
251 --
252 --
253 -- life_ins prompt fetch
254 --
255 l_effective_date := g_effective_date;
256 --
257 open csr_udt_row(c_life_gen_calc_udt,l_effective_date);
258 loop
259 --
260 fetch csr_udt_row into l_csr_udt_row;
261 exit when csr_udt_row%notfound;
262 --
263 if csr_udt_row%rowcount = 1 then
264 --
265 g_life_range1b := l_csr_udt_row.row_high_range;
266 g_life_range1b := to_char(to_number(g_life_range1b),fnd_currency.get_format_mask('JPY',40));
267 --
268 elsif csr_udt_row%rowcount = 2 then
269 --
270 g_life_range2a := l_csr_udt_row.row_low_range_or_name;
271 g_life_range2b := l_csr_udt_row.row_high_range;
272 g_life_range2a := to_char(to_number(g_life_range2a),fnd_currency.get_format_mask('JPY',40));
273 g_life_range2b := to_char(to_number(g_life_range2b),fnd_currency.get_format_mask('JPY',40));
274 --
275 open csr_udt_val(l_csr_udt_row.user_table_id,l_csr_udt_row.user_row_id,l_effective_date);
276 loop
277 --
278 fetch csr_udt_val into l_csr_udt_val;
279 exit when csr_udt_val%notfound;
280 --
281 if l_csr_udt_val.user_column_name = c_add_adj_udtcol then
282 l_add_adj := l_csr_udt_val.value;
283 elsif l_csr_udt_val.user_column_name = c_rate_udtcol then
284 l_rate := l_csr_udt_val.value;
285 end if;
286 --
287 end loop;
288 close csr_udt_val;
289 --
290 if fnd_number.canonical_to_number(l_rate) = 0.5 then
291 l_rate := '1/2';
292 elsif fnd_number.canonical_to_number(l_rate) = 0.25 then
293 l_rate := '1/4';
294 end if;
295 --
296 g_life_calc2 := 'x '||l_rate||' + '||to_char(to_number(l_add_adj),fnd_currency.get_format_mask('JPY',40));
297 --
298 elsif csr_udt_row%rowcount = 3 then
299 --
300 g_life_range3a := l_csr_udt_row.row_low_range_or_name;
301 g_life_range3b := l_csr_udt_row.row_high_range;
302 g_life_range3a := to_char(to_number(g_life_range3a),fnd_currency.get_format_mask('JPY',40));
303 g_life_range3b := to_char(to_number(g_life_range3b),fnd_currency.get_format_mask('JPY',40));
304 --
305 open csr_udt_val(l_csr_udt_row.user_table_id,l_csr_udt_row.user_row_id,l_effective_date);
306 loop
307 --
308 fetch csr_udt_val into l_csr_udt_val;
309 exit when csr_udt_val%notfound;
310 --
311 if l_csr_udt_val.user_column_name = c_add_adj_udtcol then
312 l_add_adj := l_csr_udt_val.value;
313 elsif l_csr_udt_val.user_column_name = c_rate_udtcol then
314 l_rate := l_csr_udt_val.value;
315 end if;
316 --
317 end loop;
318 close csr_udt_val;
319 --
320 if fnd_number.canonical_to_number(l_rate) = 0.5 then
321 l_rate := '1/2';
322 elsif fnd_number.canonical_to_number(l_rate) = 0.25 then
323 l_rate := '1/4';
324 end if;
325 --
326 g_life_calc3 := 'x '||l_rate||' + '||to_char(to_number(l_add_adj),fnd_currency.get_format_mask('JPY',40));
327 --
328 elsif csr_udt_row%rowcount = 4 then
329 --
330 g_life_range4a := l_csr_udt_row.row_low_range_or_name;
331 g_life_range4a := to_char(to_number(g_life_range4a),fnd_currency.get_format_mask('JPY',40));
332 --
333 open csr_udt_val(l_csr_udt_row.user_table_id,l_csr_udt_row.user_row_id,l_effective_date);
334 loop
335 --
336 fetch csr_udt_val into l_csr_udt_val;
337 exit when csr_udt_val%notfound;
338 --
339 if l_csr_udt_val.user_column_name = c_add_adj_udtcol then
340 l_add_adj := l_csr_udt_val.value;
341 end if;
342 --
343 end loop;
344 close csr_udt_val;
345 --
346 g_life_calc4 := l_add_adj;
347 --
348 end if;
349 --
350 end loop;
351 close csr_udt_row;
352 --
353 g_life_gen_max := g_life_calc4;
354 g_life_pens_max := g_life_calc4;
355 g_life_ins_max := to_char(to_number(g_life_gen_max) + to_number(g_life_pens_max));
356 --
357 g_life_calc4 := to_char(to_number(g_life_calc4),fnd_currency.get_format_mask('JPY',40));
358 g_life_gen_max := to_char(to_number(g_life_gen_max),fnd_currency.get_format_mask('JPY',40));
359 g_life_pens_max := to_char(to_number(g_life_pens_max),fnd_currency.get_format_mask('JPY',40));
360 g_life_ins_max := to_char(to_number(g_life_ins_max),fnd_currency.get_format_mask('JPY',40));
361 --
362 --
363 -- nonlife_ins prompt fetch
364 --
365 -- + long term
366 --
367 l_effective_date := g_effective_date;
368 --
369 open csr_udt_row(c_nonlife_long_calc_udt,l_effective_date);
370 loop
371 --
372 fetch csr_udt_row into l_csr_udt_row;
373 exit when csr_udt_row%notfound;
374 --
375 if csr_udt_row%rowcount = 1 then
376 --
377 g_lnonlife_range1b := l_csr_udt_row.row_high_range;
378 g_lnonlife_range1b := to_char(to_number(g_lnonlife_range1b),fnd_currency.get_format_mask('JPY',40));
379 --
380 elsif csr_udt_row%rowcount = 2 then
381 --
382 open csr_udt_val(l_csr_udt_row.user_table_id,l_csr_udt_row.user_row_id,l_effective_date);
383 loop
384 --
385 fetch csr_udt_val into l_csr_udt_val;
386 exit when csr_udt_val%notfound;
387 --
388 if l_csr_udt_val.user_column_name = c_add_adj_udtcol then
389 l_add_adj := l_csr_udt_val.value;
390 elsif l_csr_udt_val.user_column_name = c_rate_udtcol then
391 l_rate := l_csr_udt_val.value;
392 end if;
393 --
394 end loop;
395 close csr_udt_val;
396 --
397 if fnd_number.canonical_to_number(l_rate) = 0.5 then
398 l_rate := '1/2';
399 elsif fnd_number.canonical_to_number(l_rate) = 0.25 then
400 l_rate := '1/4';
401 end if;
402 --
403 g_lnonlife_calc2 := 'x '||l_rate||' + '||to_char(to_number(l_add_adj),fnd_currency.get_format_mask('JPY',40));
404 --
405 elsif csr_udt_row%rowcount = 3 then
406 --
407 open csr_udt_val(l_csr_udt_row.user_table_id,l_csr_udt_row.user_row_id,l_effective_date);
408 loop
409 --
410 fetch csr_udt_val into l_csr_udt_val;
411 exit when csr_udt_val%notfound;
412 --
413 if l_csr_udt_val.user_column_name = c_add_adj_udtcol then
414 l_add_adj := l_csr_udt_val.value;
415 end if;
416 --
417 end loop;
418 close csr_udt_val;
419 --
420 l_lnonlife_calc3 := l_add_adj;
421 --
422 end if;
423 --
424 end loop;
425 close csr_udt_row;
426 --
427 -- + short term
428 --
429 -- value always should be fetched.
430 if g_effective_date >= c_st_upd_date_2007 then
431 --
432 l_effective_date := c_st_upd_date_2007 - 1;
433 --
434 else
435 --
436 l_effective_date := g_effective_date;
437 --
438 end if;
439 --
440 open csr_udt_row(c_nonlife_short_calc_udt,l_effective_date);
441 loop
442 --
443 fetch csr_udt_row into l_csr_udt_row;
444 exit when csr_udt_row%notfound;
445 --
446 if csr_udt_row%rowcount = 1 then
447 --
448 g_snonlife_range1b := l_csr_udt_row.row_high_range;
449 g_snonlife_range1b := to_char(to_number(g_snonlife_range1b),fnd_currency.get_format_mask('JPY',40));
450 --
451 elsif csr_udt_row%rowcount = 2 then
452 --
453 open csr_udt_val(l_csr_udt_row.user_table_id,l_csr_udt_row.user_row_id,l_effective_date);
454 loop
455 --
456 fetch csr_udt_val into l_csr_udt_val;
457 exit when csr_udt_val%notfound;
458 --
459 if l_csr_udt_val.user_column_name = c_add_adj_udtcol then
460 l_add_adj := l_csr_udt_val.value;
461 elsif l_csr_udt_val.user_column_name = c_rate_udtcol then
462 l_rate := l_csr_udt_val.value;
463 end if;
464 --
465 end loop;
466 close csr_udt_val;
467 --
468 if fnd_number.canonical_to_number(l_rate) = 0.5 then
469 l_rate := '1/2';
470 elsif fnd_number.canonical_to_number(l_rate) = 0.25 then
471 l_rate := '1/4';
472 end if;
473 --
474 g_snonlife_calc2 := 'x '||l_rate||' + '||to_char(to_number(l_add_adj),fnd_currency.get_format_mask('JPY',40));
475 --
476 elsif csr_udt_row%rowcount = 3 then
477 --
478 open csr_udt_val(l_csr_udt_row.user_table_id,l_csr_udt_row.user_row_id,l_effective_date);
479 loop
480 --
481 fetch csr_udt_val into l_csr_udt_val;
482 exit when csr_udt_val%notfound;
483 --
484 if l_csr_udt_val.user_column_name = c_add_adj_udtcol then
485 l_add_adj := l_csr_udt_val.value;
486 end if;
487 --
488 end loop;
489 close csr_udt_val;
490 --
491 l_snonlife_calc3 := l_add_adj;
492 --
493 end if;
494 --
495 end loop;
496 close csr_udt_row;
497 --
498 -- + earthquake
499 --
500 if g_effective_date >= c_st_upd_date_2007 then
501 --
502 l_effective_date := g_effective_date;
503 --
504 -- value always should be fetched.
505 else
506 --
507 l_effective_date := c_st_upd_date_2007;
508 --
509 end if;
510 --
511 if c_earthquake_max is null then
512 c_earthquake_max := to_number(hruserdt.get_table_value(
513 g_business_group_id,
514 c_yea_calc_max_udt,
515 c_max_udtcol,
516 c_earthquake_udtrow,
517 l_effective_date));
518 end if;
519 --
520 g_earthquake_max := c_earthquake_max;
521 g_earthquake_max := to_char(to_number(g_earthquake_max),fnd_currency.get_format_mask('JPY',40));
522 --
523 g_lnonlife_year := to_char(c_nonlife_long_year);
524 g_lnonlife_max := l_lnonlife_calc3;
525 g_snonlife_max := l_snonlife_calc3;
526 g_lnonlife_max := to_char(to_number(g_lnonlife_max),fnd_currency.get_format_mask('JPY',40));
527 g_snonlife_max := to_char(to_number(g_snonlife_max),fnd_currency.get_format_mask('JPY',40));
528 --
529 -- + nonlife dct
530 --
531 -- value always should be fetched.
532 if g_effective_date >= c_st_upd_date_2007 then
533 --
534 l_effective_date := c_st_upd_date_2007 - 1;
535 --
536 else
537 --
538 l_effective_date := g_effective_date;
539 --
540 end if;
541 --
542 l_nonlife_max := to_number(hruserdt.get_table_value(
543 g_business_group_id,
544 c_yea_calc_max_udt,
545 c_max_udtcol,
546 c_nonlife_udtrow,
547 l_effective_date));
548 --
549 -- value always should be fetched.
550 if g_effective_date >= c_st_upd_date_2007 then
551 --
552 l_effective_date := g_effective_date;
553 --
554 else
555 --
556 l_effective_date := c_st_upd_date_2007 - 1;
557 --
558 end if;
559 --
560 l_nonlife_max_2007 := to_number(hruserdt.get_table_value(
561 g_business_group_id,
562 c_yea_calc_max_udt,
563 c_max_udtcol,
564 c_nonlife_udtrow,
565 l_effective_date));
566 --
567 -- need always reset for each time
568 if g_effective_date >= c_st_upd_date_2007 then
569 --
570 c_nonlife_max := l_nonlife_max_2007;
571 --
572 else
573 --
574 c_nonlife_max := l_nonlife_max;
575 --
576 end if;
577 --
578 g_nonlife_max := l_nonlife_max;
579 g_nonlife_max_2007 := l_nonlife_max_2007;
580 g_nonlife_max := to_char(to_number(g_nonlife_max),fnd_currency.get_format_mask('JPY',40));
581 g_nonlife_max_2007 := to_char(to_number(g_nonlife_max_2007),fnd_currency.get_format_mask('JPY',40));
582 --
583 --
584 -- spouse prompt fetch
585 --
586 g_sp_calc_unit := c_sp_calc_unit;
587 --
588 l_effective_date := g_effective_date;
589 --
590 if c_emp_income_max is null then
591 --
592 c_emp_income_max := to_number(hruserdt.get_table_value(
593 g_business_group_id,
594 c_yea_calc_max_udt,
595 c_max_udtcol,
596 c_sp_emp_income_udtrow,
597 l_effective_date));
598 end if;
599 --
600 g_sp_emp_inc_max := c_emp_income_max/c_sp_calc_unit;
601 g_sp_emp_inc_max := to_char(to_number(g_sp_emp_inc_max),fnd_currency.get_format_mask('JPY',40));
602 --
603 l_effective_date := g_effective_date;
604 --
605 if c_inc_spouse_dct_max is null then
606 --
607 c_inc_spouse_dct_max := to_number(hruserdt.get_table_value(
608 g_business_group_id,
609 c_yea_calc_max_udt,
610 c_max_udtcol,
611 c_sp_dctable_sp_income_udtrow,
612 l_effective_date));
613 end if;
614 --
615 g_sp_spdct_max := c_inc_spouse_dct_max/c_sp_calc_unit;
616 g_sp_spdct_max := to_char(to_number(g_sp_spdct_max),fnd_currency.get_format_mask('JPY',40));
617 --
618 l_effective_date := g_effective_date;
619 --
620 if c_spouse_income_max is null then
621 c_spouse_income_max := to_number(hruserdt.get_table_value(
622 g_business_group_id,
623 c_yea_calc_max_udt,
624 c_max_udtcol,
625 c_sp_spouse_income_udtrow,
626 l_effective_date));
627 end if;
628 --
629 g_sp_spinc_max := c_spouse_income_max/c_sp_calc_unit;
630 g_sp_spinc_max := to_char(to_number(g_sp_spinc_max),fnd_currency.get_format_mask('JPY',40));
631 --
632 --
633 -- spouse_calc prompt fetch
634 --
635 g_sp_calc_exp1b := c_sp_earned_inc_exp;
636 g_sp_calc_exp1b_fmt := to_char(g_sp_calc_exp1b);
637 g_sp_calc_exp1b_fmt := to_char(to_number(g_sp_calc_exp1b_fmt),fnd_currency.get_format_mask('JPY',40));
638 g_sp_calc_cal1 := to_char(c_sp_calc_earned_inc_calc1);
639 --
640 if fnd_number.canonical_to_number(c_sp_calc_other_inc_calc_rate) = 0.5 then
641 l_sp_calc_other_inc_calc_rate := '1/2';
642 elsif fnd_number.canonical_to_number(c_sp_calc_other_inc_calc_rate) = 0.25 then
643 l_sp_calc_other_inc_calc_rate := '1/4';
644 end if;
645 --
646 g_sp_calc_cal6 := 'x '||l_sp_calc_other_inc_calc_rate;
647 --
648 l_effective_date := g_effective_date;
649 --
650 open csr_udt_row(c_spouse_calc_udt,l_effective_date);
651 loop
652 --
653 fetch csr_udt_row into l_csr_udt_row;
654 exit when csr_udt_row%notfound;
655 --
656 if fnd_number.canonical_to_number(l_csr_udt_row.row_low_range_or_name) > c_inc_spouse_dct_max then
657 --
658 l_dct_cnt := l_dct_cnt + 1;
659 l_sp_calc_tbl(l_dct_cnt).range_a := l_csr_udt_row.row_low_range_or_name;
660 l_sp_calc_tbl(l_dct_cnt).range_b := l_csr_udt_row.row_high_range;
661 --
662 open csr_udt_val(l_csr_udt_row.user_table_id,l_csr_udt_row.user_row_id,l_effective_date);
663 loop
664 --
665 fetch csr_udt_val into l_csr_udt_val;
666 exit when csr_udt_val%notfound;
667 --
668 if l_csr_udt_val.user_column_name = c_dct_udtcol then
669 --l_sp_calc_tbl(l_dct_cnt).val := to_char(fnd_number.canonical_to_number(l_csr_udt_val.value)/c_sp_calc_unit);
670 l_sp_calc_tbl(l_dct_cnt).val := l_csr_udt_val.value;
671 end if;
672 --
673 end loop;
674 close csr_udt_val;
675 --
676 end if;
677 --
678 end loop;
679 close csr_udt_row;
680 --
681 if l_sp_calc_tbl.count >= 9 then
682 --
683 g_sp_calc_dct_range1a := l_sp_calc_tbl(1).range_a;
684 g_sp_calc_dct_range1b := l_sp_calc_tbl(1).range_b;
685 g_sp_calc_dct1 := l_sp_calc_tbl(1).val;
686 g_sp_calc_dct_range2a := l_sp_calc_tbl(2).range_a;
687 g_sp_calc_dct_range2b := l_sp_calc_tbl(2).range_b;
688 g_sp_calc_dct2 := l_sp_calc_tbl(2).val;
689 g_sp_calc_dct_range3a := l_sp_calc_tbl(3).range_a;
690 g_sp_calc_dct_range3b := l_sp_calc_tbl(3).range_b;
691 g_sp_calc_dct3 := l_sp_calc_tbl(3).val;
692 g_sp_calc_dct_range4a := l_sp_calc_tbl(4).range_a;
693 g_sp_calc_dct_range4b := l_sp_calc_tbl(4).range_b;
694 g_sp_calc_dct4 := l_sp_calc_tbl(4).val;
695 g_sp_calc_dct_range5a := l_sp_calc_tbl(5).range_a;
696 g_sp_calc_dct_range5b := l_sp_calc_tbl(5).range_b;
697 g_sp_calc_dct5 := l_sp_calc_tbl(5).val;
698 g_sp_calc_dct_range6a := l_sp_calc_tbl(6).range_a;
699 g_sp_calc_dct_range6b := l_sp_calc_tbl(6).range_b;
700 g_sp_calc_dct6 := l_sp_calc_tbl(6).val;
701 g_sp_calc_dct_range7a := l_sp_calc_tbl(7).range_a;
702 g_sp_calc_dct_range7b := l_sp_calc_tbl(7).range_b;
703 g_sp_calc_dct7 := l_sp_calc_tbl(7).val;
704 g_sp_calc_dct_range8a := l_sp_calc_tbl(8).range_a;
705 g_sp_calc_dct_range8b := l_sp_calc_tbl(8).range_b;
706 g_sp_calc_dct8 := l_sp_calc_tbl(8).val;
707 g_sp_calc_dct_range9a := l_sp_calc_tbl(9).range_a;
708 g_sp_calc_dct_range9b := l_sp_calc_tbl(9).range_b;
709 g_sp_calc_dct9 := l_sp_calc_tbl(9).val;
710 --
711 g_sp_calc_dct_range1a := to_char(to_number(g_sp_calc_dct_range1a),fnd_currency.get_format_mask('JPY',40));
712 g_sp_calc_dct_range1b := to_char(to_number(g_sp_calc_dct_range1b),fnd_currency.get_format_mask('JPY',40));
713 g_sp_calc_dct1 := to_char(to_number(g_sp_calc_dct1),fnd_currency.get_format_mask('JPY',40));
714 g_sp_calc_dct_range2a := to_char(to_number(g_sp_calc_dct_range2a),fnd_currency.get_format_mask('JPY',40));
715 g_sp_calc_dct_range2b := to_char(to_number(g_sp_calc_dct_range2b),fnd_currency.get_format_mask('JPY',40));
716 g_sp_calc_dct2 := to_char(to_number(g_sp_calc_dct2),fnd_currency.get_format_mask('JPY',40));
717 g_sp_calc_dct_range3a := to_char(to_number(g_sp_calc_dct_range3a),fnd_currency.get_format_mask('JPY',40));
718 g_sp_calc_dct_range3b := to_char(to_number(g_sp_calc_dct_range3b),fnd_currency.get_format_mask('JPY',40));
719 g_sp_calc_dct3 := to_char(to_number(g_sp_calc_dct3),fnd_currency.get_format_mask('JPY',40));
720 g_sp_calc_dct_range4a := to_char(to_number(g_sp_calc_dct_range4a),fnd_currency.get_format_mask('JPY',40));
721 g_sp_calc_dct_range4b := to_char(to_number(g_sp_calc_dct_range4b),fnd_currency.get_format_mask('JPY',40));
722 g_sp_calc_dct4 := to_char(to_number(g_sp_calc_dct4),fnd_currency.get_format_mask('JPY',40));
723 g_sp_calc_dct_range5a := to_char(to_number(g_sp_calc_dct_range5a),fnd_currency.get_format_mask('JPY',40));
724 g_sp_calc_dct_range5b := to_char(to_number(g_sp_calc_dct_range5b),fnd_currency.get_format_mask('JPY',40));
725 g_sp_calc_dct5 := to_char(to_number(g_sp_calc_dct5),fnd_currency.get_format_mask('JPY',40));
726 g_sp_calc_dct_range6a := to_char(to_number(g_sp_calc_dct_range6a),fnd_currency.get_format_mask('JPY',40));
727 g_sp_calc_dct_range6b := to_char(to_number(g_sp_calc_dct_range6b),fnd_currency.get_format_mask('JPY',40));
728 g_sp_calc_dct6 := to_char(to_number(g_sp_calc_dct6),fnd_currency.get_format_mask('JPY',40));
729 g_sp_calc_dct_range7a := to_char(to_number(g_sp_calc_dct_range7a),fnd_currency.get_format_mask('JPY',40));
730 g_sp_calc_dct_range7b := to_char(to_number(g_sp_calc_dct_range7b),fnd_currency.get_format_mask('JPY',40));
731 g_sp_calc_dct7 := to_char(to_number(g_sp_calc_dct7),fnd_currency.get_format_mask('JPY',40));
732 g_sp_calc_dct_range8a := to_char(to_number(g_sp_calc_dct_range8a),fnd_currency.get_format_mask('JPY',40));
733 g_sp_calc_dct_range8b := to_char(to_number(g_sp_calc_dct_range8b),fnd_currency.get_format_mask('JPY',40));
734 g_sp_calc_dct8 := to_char(to_number(g_sp_calc_dct8),fnd_currency.get_format_mask('JPY',40));
735 g_sp_calc_dct_range9a := to_char(to_number(g_sp_calc_dct_range9a),fnd_currency.get_format_mask('JPY',40));
736 g_sp_calc_dct_range9b := to_char(to_number(g_sp_calc_dct_range9b),fnd_currency.get_format_mask('JPY',40));
737 g_sp_calc_dct9 := to_char(to_number(g_sp_calc_dct9),fnd_currency.get_format_mask('JPY',40));
738 --
739 end if;
740 --
741 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LIFE_RANGE_FIRST');
742 fnd_message.set_token('RANGE_B',g_life_range1b);
743 g_msg_life_range1 := fnd_message.get;
744 --
745 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LIFE_RANGE_MID');
746 fnd_message.set_token('RANGE_A',g_life_range2a);
747 fnd_message.set_token('RANGE_B',g_life_range2b);
748 g_msg_life_range2 := fnd_message.get;
749 --
750 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LIFE_RANGE_MID');
751 fnd_message.set_token('RANGE_A',g_life_range3a);
752 fnd_message.set_token('RANGE_B',g_life_range3b);
753 g_msg_life_range3 := fnd_message.get;
754 --
755 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LIFE_RANGE_LAST');
756 fnd_message.set_token('RANGE_A',g_life_range4a);
757 g_msg_life_range4 := fnd_message.get;
758 --
759 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LIFE_DCT_MID');
760 fnd_message.set_token('CALC',g_life_calc2);
761 g_msg_life_calc2 := fnd_message.get;
762 --
763 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LIFE_DCT_MID');
764 fnd_message.set_token('CALC',g_life_calc3);
765 g_msg_life_calc3 := fnd_message.get;
766 --
767 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LIFE_DCT_LAST');
768 fnd_message.set_token('CALC',g_life_calc4);
769 g_msg_life_calc4 := fnd_message.get;
770 --
771 fnd_message.set_name('PAY','PAY_JP_ISDF_P_MAX');
772 fnd_message.set_token('MAX_VAL',g_life_gen_max);
773 g_msg_life_gen_max := fnd_message.get;
774 --
775 fnd_message.set_name('PAY','PAY_JP_ISDF_P_MAX');
776 fnd_message.set_token('MAX_VAL',g_life_pens_max);
777 g_msg_life_pens_max := fnd_message.get;
778 --
779 fnd_message.set_name('PAY','PAY_JP_ISDF_P_MAX');
780 fnd_message.set_token('MAX_VAL',g_life_ins_max);
781 g_msg_life_ins_max := fnd_message.get;
782 --
783 fnd_message.set_name('PAY','PAY_JP_ISDF_P_NONLIFE_2007');
784 g_msg_nonlife_2007 := fnd_message.get;
785 --
786 fnd_message.set_name('PAY','PAY_JP_ISDF_P_NONLIFE_AP_2007');
787 g_msg_nonlife_ap_2007 := fnd_message.get;
788 --
789 fnd_message.set_name('PAY','PAY_JP_ISDF_P_EQNONLIFE_S_2007');
790 g_msg_eqnonlife_s_2007 := fnd_message.get;
791 --
792 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LNONLIFE_S_2007');
793 g_msg_lnonlife_s_2007 := fnd_message.get;
794 --
795 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LNONLIFE');
796 fnd_message.set_token('YEAR',g_lnonlife_year);
797 g_msg_lnonlife := fnd_message.get;
798 --
799 fnd_message.set_name('PAY','PAY_JP_ISDF_P_EQNONLIFE_2007');
800 g_msg_eqnonlife_2007 := fnd_message.get;
801 --
802 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LNONLIFE_2007');
803 g_msg_lnonlife_2007 := fnd_message.get;
804 --
805 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LNONLIFE_DCT');
806 fnd_message.set_token('RANGE_B',g_lnonlife_range1b);
807 fnd_message.set_token('CALC',g_lnonlife_calc2);
808 g_msg_lnonlife_dct := fnd_message.get;
809 --
810 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SNONLIFE_DCT');
811 fnd_message.set_token('RANGE_B',g_snonlife_range1b);
812 fnd_message.set_token('CALC',g_snonlife_calc2);
813 g_msg_snonlife_dct := fnd_message.get;
814 --
815 fnd_message.set_name('PAY','PAY_JP_ISDF_P_LNONL_DCT_2007');
816 fnd_message.set_token('RANGE_B',g_lnonlife_range1b);
817 fnd_message.set_token('CALC',g_lnonlife_calc2);
818 g_msg_lnonlife_dct_2007 := fnd_message.get;
819 --
820 fnd_message.set_name('PAY','PAY_JP_ISDF_P_MAX');
821 fnd_message.set_token('MAX_VAL',g_earthquake_max);
822 g_msg_earthquake_max := fnd_message.get;
823 --
824 fnd_message.set_name('PAY','PAY_JP_ISDF_P_MAX');
825 fnd_message.set_token('MAX_VAL',g_lnonlife_max);
826 g_msg_nonlife_long_max := fnd_message.get;
827 --
828 fnd_message.set_name('PAY','PAY_JP_ISDF_P_MAX');
829 fnd_message.set_token('MAX_VAL',g_snonlife_max);
830 g_msg_nonlife_short_max := fnd_message.get;
831 --
832 fnd_message.set_name('PAY','PAY_JP_ISDF_P_MAX');
833 fnd_message.set_token('MAX_VAL',g_nonlife_max);
834 g_msg_nonlife_ins_max := fnd_message.get;
835 --
836 fnd_message.set_name('PAY','PAY_JP_ISDF_P_MAX');
837 fnd_message.set_token('MAX_VAL',g_nonlife_max_2007);
838 g_msg_nonlife_ins_max_2007 := fnd_message.get;
839 --
840 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_EMP_INC_MAX');
841 fnd_message.set_token('EMP_INC_MAX',g_sp_emp_inc_max);
842 g_msg_sp_emp_inc_max := fnd_message.get;
843 --
844 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_SP_INC_MAX');
845 fnd_message.set_token('SP_DCT_MAX',g_sp_spdct_max);
846 fnd_message.set_token('SP_INC_MAX',g_sp_spinc_max);
847 g_msg_sp_sp_inc_max := fnd_message.get;
848 --
849 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_CALC_EXP1');
850 fnd_message.set_token('SP_CALC_EXP1',g_sp_calc_cal1);
851 g_msg_sp_calc_cal1 := fnd_message.get;
852 --
853 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_CALC_EXP6');
854 fnd_message.set_token('SP_CALC_EXP6',g_sp_calc_cal6);
855 g_msg_sp_calc_cal6 := fnd_message.get;
856 --
857 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
858 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range1a);
859 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range1b);
860 g_msg_sp_calc_dct_range1 := fnd_message.get;
861 --
862 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
863 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range2a);
864 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range2b);
865 g_msg_sp_calc_dct_range2 := fnd_message.get;
866 --
867 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
868 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range3a);
869 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range3b);
870 g_msg_sp_calc_dct_range3 := fnd_message.get;
871 --
872 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
873 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range4a);
874 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range4b);
875 g_msg_sp_calc_dct_range4 := fnd_message.get;
876 --
877 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
878 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range5a);
879 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range5b);
880 g_msg_sp_calc_dct_range5 := fnd_message.get;
881 --
882 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
883 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range6a);
884 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range6b);
885 g_msg_sp_calc_dct_range6 := fnd_message.get;
886 --
887 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
888 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range7a);
889 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range7b);
890 g_msg_sp_calc_dct_range7 := fnd_message.get;
891 --
892 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
893 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range8a);
894 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range8b);
895 g_msg_sp_calc_dct_range8 := fnd_message.get;
896 --
897 fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT_RANGE');
898 fnd_message.set_token('RANGE_A',g_sp_calc_dct_range9a);
899 fnd_message.set_token('RANGE_B',g_sp_calc_dct_range9b);
900 g_msg_sp_calc_dct_range9 := fnd_message.get;
901 --
902 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
903 --fnd_message.set_token('DCT',g_sp_calc_dct1);
904 --g_msg_sp_calc_dct1 := fnd_message.get;
905 g_msg_sp_calc_dct1 := g_sp_calc_dct1;
906 --
907 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
908 --fnd_message.set_token('DCT',g_sp_calc_dct2);
909 --g_msg_sp_calc_dct2 := fnd_message.get;
910 g_msg_sp_calc_dct2 := g_sp_calc_dct2;
911 --
912 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
913 --fnd_message.set_token('DCT',g_sp_calc_dct3);
914 --g_msg_sp_calc_dct3 := fnd_message.get;
915 g_msg_sp_calc_dct3 := g_sp_calc_dct3;
916 --
917 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
918 --fnd_message.set_token('DCT',g_sp_calc_dct4);
919 --g_msg_sp_calc_dct4 := fnd_message.get;
920 g_msg_sp_calc_dct4 := g_sp_calc_dct4;
921 --
922 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
923 --fnd_message.set_token('DCT',g_sp_calc_dct5);
924 --g_msg_sp_calc_dct5 := fnd_message.get;
925 g_msg_sp_calc_dct5 := g_sp_calc_dct5;
926 --
927 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
928 --fnd_message.set_token('DCT',g_sp_calc_dct6);
929 --g_msg_sp_calc_dct6 := fnd_message.get;
930 g_msg_sp_calc_dct6 := g_sp_calc_dct6;
931 --
932 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
933 --fnd_message.set_token('DCT',g_sp_calc_dct7);
934 --g_msg_sp_calc_dct7 := fnd_message.get;
935 g_msg_sp_calc_dct7 := g_sp_calc_dct7;
936 --
937 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
938 --fnd_message.set_token('DCT',g_sp_calc_dct8);
939 --g_msg_sp_calc_dct8 := fnd_message.get;
940 g_msg_sp_calc_dct8 := g_sp_calc_dct8;
941 --
942 --fnd_message.set_name('PAY','PAY_JP_ISDF_P_SP_DCT');
943 --fnd_message.set_token('DCT',g_sp_calc_dct9);
944 --g_msg_sp_calc_dct9 := fnd_message.get;
945 g_msg_sp_calc_dct9 := g_sp_calc_dct9;
946 --
947 end if;
948 --
949 if g_debug then
950 hr_utility.trace('g_life_range1b : '||g_life_range1b);
951 hr_utility.trace('g_life_range2a : '||g_life_range2a);
952 hr_utility.trace('g_life_range2b : '||g_life_range2b);
953 hr_utility.trace('g_life_range3a : '||g_life_range3a);
954 hr_utility.trace('g_life_range3b : '||g_life_range3b);
955 hr_utility.trace('g_life_range4a : '||g_life_range4a);
956 hr_utility.trace('g_life_calc2 : '||g_life_calc2);
957 hr_utility.trace('g_life_calc3 : '||g_life_calc3);
958 hr_utility.trace('g_life_calc4 : '||g_life_calc4);
959 hr_utility.trace('g_life_gen_max : '||g_life_gen_max);
960 hr_utility.trace('g_life_pens_max : '||g_life_pens_max);
961 hr_utility.trace('g_life_ins_max : '||g_life_ins_max);
962 hr_utility.trace('g_earthquake_max : '||g_earthquake_max);
963 hr_utility.trace('g_lnonlife_range1b : '||g_lnonlife_range1b);
964 hr_utility.trace('g_lnonlife_calc2 : '||g_lnonlife_calc2);
965 hr_utility.trace('g_lnonlife_year : '||g_lnonlife_year);
966 hr_utility.trace('g_snonlife_range1b : '||g_snonlife_range1b);
967 hr_utility.trace('g_snonlife_calc2 : '||g_snonlife_calc2);
968 hr_utility.trace('g_lnonlife_max : '||g_lnonlife_max);
969 hr_utility.trace('g_snonlife_max : '||g_snonlife_max);
970 hr_utility.trace('g_nonlife_max : '||g_nonlife_max);
971 hr_utility.trace('g_sp_calc_unit : '||g_sp_calc_unit);
972 hr_utility.trace('g_sp_emp_inc_max : '||g_sp_emp_inc_max);
973 hr_utility.trace('g_sp_spdct_max : '||g_sp_spdct_max);
974 hr_utility.trace('g_sp_spinc_max : '||g_sp_spinc_max);
975 hr_utility.trace('g_sp_calc_exp1b : '||g_sp_calc_exp1b);
976 hr_utility.trace('g_sp_calc_exp1b_fmt : '||g_sp_calc_exp1b_fmt);
977 hr_utility.trace('g_sp_calc_cal1 : '||g_sp_calc_cal1);
978 hr_utility.trace('g_sp_calc_cal6 : '||g_sp_calc_cal6);
979 hr_utility.trace('g_sp_calc_dct_range1a : '||g_sp_calc_dct_range1a);
980 hr_utility.trace('g_sp_calc_dct_range1b : '||g_sp_calc_dct_range1b);
981 hr_utility.trace('g_sp_calc_dct1 : '||g_sp_calc_dct1);
982 hr_utility.trace('g_sp_calc_dct_range2a : '||g_sp_calc_dct_range2a);
983 hr_utility.trace('g_sp_calc_dct_range2b : '||g_sp_calc_dct_range2b);
984 hr_utility.trace('g_sp_calc_dct2 : '||g_sp_calc_dct2);
985 hr_utility.trace('g_sp_calc_dct_range3a : '||g_sp_calc_dct_range3a);
986 hr_utility.trace('g_sp_calc_dct_range3b : '||g_sp_calc_dct_range3b);
987 hr_utility.trace('g_sp_calc_dct3 : '||g_sp_calc_dct3);
988 hr_utility.trace('g_sp_calc_dct_range4a : '||g_sp_calc_dct_range4a);
989 hr_utility.trace('g_sp_calc_dct_range4b : '||g_sp_calc_dct_range4b);
990 hr_utility.trace('g_sp_calc_dct4 : '||g_sp_calc_dct4);
991 hr_utility.trace('g_sp_calc_dct_range5a : '||g_sp_calc_dct_range5a);
992 hr_utility.trace('g_sp_calc_dct_range5b : '||g_sp_calc_dct_range5b);
993 hr_utility.trace('g_sp_calc_dct5 : '||g_sp_calc_dct5);
994 hr_utility.trace('g_sp_calc_dct_range6a : '||g_sp_calc_dct_range6a);
995 hr_utility.trace('g_sp_calc_dct_range6b : '||g_sp_calc_dct_range6b);
996 hr_utility.trace('g_sp_calc_dct6 : '||g_sp_calc_dct6);
997 hr_utility.trace('g_sp_calc_dct_range7a : '||g_sp_calc_dct_range7a);
998 hr_utility.trace('g_sp_calc_dct_range7b : '||g_sp_calc_dct_range7b);
999 hr_utility.trace('g_sp_calc_dct7 : '||g_sp_calc_dct7);
1000 hr_utility.trace('g_sp_calc_dct_range8a : '||g_sp_calc_dct_range8a);
1001 hr_utility.trace('g_sp_calc_dct_range8b : '||g_sp_calc_dct_range8b);
1002 hr_utility.trace('g_sp_calc_dct8 : '||g_sp_calc_dct8);
1003 hr_utility.trace('g_sp_calc_dct_range9a : '||g_sp_calc_dct_range9a);
1004 hr_utility.trace('g_sp_calc_dct_range9b : '||g_sp_calc_dct_range9b);
1005 hr_utility.trace('g_sp_calc_dct9 : '||g_sp_calc_dct9);
1006 end if;
1007 --
1008 if g_debug then
1009 hr_utility.trace('g_msg_life_range1 : '||g_msg_life_range1);
1010 hr_utility.trace('g_msg_life_range2 : '||g_msg_life_range2);
1011 hr_utility.trace('g_msg_life_range3 : '||g_msg_life_range3);
1012 hr_utility.trace('g_msg_life_range4 : '||g_msg_life_range4);
1013 hr_utility.trace('g_msg_life_calc2 : '||g_msg_life_calc2);
1014 hr_utility.trace('g_msg_life_calc3 : '||g_msg_life_calc3);
1015 hr_utility.trace('g_msg_life_calc4 : '||g_msg_life_calc4);
1016 hr_utility.trace('g_msg_life_gen_max : '||g_msg_life_gen_max);
1017 hr_utility.trace('g_msg_life_pens_max : '||g_msg_life_pens_max);
1018 hr_utility.trace('g_msg_life_ins_max : '||g_msg_life_ins_max);
1019 hr_utility.trace('g_msg_nonlife_2007 : '||g_msg_nonlife_2007);
1020 hr_utility.trace('g_msg_nonlife_ap_2007 : '||g_msg_nonlife_ap_2007);
1021 hr_utility.trace('g_msg_eqnonlife_s_2007 : '||g_msg_eqnonlife_s_2007);
1022 hr_utility.trace('g_msg_lnonlife_s_2007 : '||g_msg_lnonlife_s_2007);
1023 hr_utility.trace('g_msg_lnonlife : '||g_msg_lnonlife);
1024 hr_utility.trace('g_msg_eqnonlife_2007 : '||g_msg_eqnonlife_2007);
1025 hr_utility.trace('g_msg_lnonlife_2007 : '||g_msg_lnonlife_2007);
1026 hr_utility.trace('g_msg_lnonlife_dct : '||g_msg_lnonlife_dct);
1027 hr_utility.trace('g_msg_snonlife_dct : '||g_msg_snonlife_dct);
1028 hr_utility.trace('g_msg_lnonlife_dct_2007 : '||g_msg_lnonlife_dct_2007);
1029 hr_utility.trace('g_msg_earthquake_max : '||g_msg_earthquake_max);
1030 hr_utility.trace('g_msg_nonlife_long_max : '||g_msg_nonlife_long_max);
1031 hr_utility.trace('g_msg_nonlife_short_max : '||g_msg_nonlife_short_max);
1032 hr_utility.trace('g_msg_nonlife_ins_max : '||g_msg_nonlife_ins_max);
1033 hr_utility.trace('g_msg_nonlife_ins_max_2007 : '||g_msg_nonlife_ins_max_2007);
1034 hr_utility.trace('g_msg_sp_emp_inc_max : '||g_msg_sp_emp_inc_max);
1035 hr_utility.trace('g_msg_sp_sp_inc_max : '||g_msg_sp_sp_inc_max);
1036 hr_utility.trace('g_msg_sp_calc_cal1 : '||g_msg_sp_calc_cal1);
1037 hr_utility.trace('g_msg_sp_calc_cal6 : '||g_msg_sp_calc_cal6);
1038 hr_utility.trace('g_msg_sp_calc_dct_range1 : '||g_msg_sp_calc_dct_range1);
1039 hr_utility.trace('g_msg_sp_calc_dct_range2 : '||g_msg_sp_calc_dct_range2);
1040 hr_utility.trace('g_msg_sp_calc_dct_range3 : '||g_msg_sp_calc_dct_range3);
1041 hr_utility.trace('g_msg_sp_calc_dct_range4 : '||g_msg_sp_calc_dct_range4);
1042 hr_utility.trace('g_msg_sp_calc_dct_range5 : '||g_msg_sp_calc_dct_range5);
1043 hr_utility.trace('g_msg_sp_calc_dct_range6 : '||g_msg_sp_calc_dct_range6);
1044 hr_utility.trace('g_msg_sp_calc_dct_range7 : '||g_msg_sp_calc_dct_range7);
1045 hr_utility.trace('g_msg_sp_calc_dct_range8 : '||g_msg_sp_calc_dct_range8);
1046 hr_utility.trace('g_msg_sp_calc_dct_range9 : '||g_msg_sp_calc_dct_range9);
1047 hr_utility.trace('g_msg_sp_calc_dct1 : '||g_msg_sp_calc_dct1);
1048 hr_utility.trace('g_msg_sp_calc_dct2 : '||g_msg_sp_calc_dct2);
1049 hr_utility.trace('g_msg_sp_calc_dct3 : '||g_msg_sp_calc_dct3);
1050 hr_utility.trace('g_msg_sp_calc_dct4 : '||g_msg_sp_calc_dct4);
1051 hr_utility.trace('g_msg_sp_calc_dct5 : '||g_msg_sp_calc_dct5);
1052 hr_utility.trace('g_msg_sp_calc_dct6 : '||g_msg_sp_calc_dct6);
1053 hr_utility.trace('g_msg_sp_calc_dct7 : '||g_msg_sp_calc_dct7);
1054 hr_utility.trace('g_msg_sp_calc_dct8 : '||g_msg_sp_calc_dct8);
1055 hr_utility.trace('g_msg_sp_calc_dct9 : '||g_msg_sp_calc_dct9);
1056 end if;
1057 --
1058 if g_debug then
1059 hr_utility.set_location(l_proc,1000);
1060 end if;
1061 --
1062 end set_form_pg_prompt;
1063 --
1064 -- -------------------------------------------------------------------------
1065 -- do_new
1066 -- -------------------------------------------------------------------------
1067 procedure do_new(
1068 p_action_information_id in number,
1069 p_object_version_number in out nocopy number)
1070 is
1071 --
1072 l_proc varchar2(80) := c_package||'do_new';
1073 l_submission_date date;
1074 l_assact_rec pay_jp_isdf_assact_v%rowtype;
1075 l_payroll_action_id number;
1076 --
1077 cursor csr_pact
1078 is
1079 select paa.payroll_action_id
1080 from pay_assignment_actions paa
1081 where paa.assignment_action_id = l_assact_rec.assignment_action_id;
1082 --
1083 begin
1084 --
1085 if g_debug then
1086 hr_utility.set_location(l_proc,0);
1087 end if;
1088 --
1089 l_submission_date := check_submission_period(p_action_information_id);
1090 --
1091 if g_debug then
1092 hr_utility.set_location(l_proc,10);
1093 hr_utility.trace('submission_date : '||fnd_date.date_to_canonical(l_submission_date));
1094 end if;
1095 --
1096 pay_jp_isdf_dml_pkg.lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
1097 --
1098 if l_assact_rec.transaction_status not in ('U', 'N') then
1099 fnd_message.set_name('PAY','PAY_JP_DEF_INVALID_TXN_STATUS');
1100 fnd_message.raise_error;
1101 end if;
1102 --
1103 if g_debug then
1104 hr_utility.set_location(l_proc,20);
1105 hr_utility.trace('action_information_id : '||p_action_information_id);
1106 hr_utility.trace('object_version_number : '||p_object_version_number);
1107 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
1108 hr_utility.trace('start delete preset archive');
1109 end if;
1110 --
1111 -- recreate archive data (available for existing data of transaction_status N or U)
1112 --
1113 delete
1114 from pay_action_information
1115 where action_context_id = l_assact_rec.assignment_action_id
1116 and action_context_type = 'AAP'
1117 and action_information_category <> 'JP_ISDF_ASSACT';
1118 --
1119 if g_debug then
1120 hr_utility.trace('end delete preset archive');
1121 hr_utility.set_location(l_proc,30);
1122 hr_utility.trace('start archive_assact');
1123 end if;
1124 --
1125 -- set global argument of pact in pay_jp_isdf_archive_pkg
1126 open csr_pact;
1127 fetch csr_pact into l_payroll_action_id;
1128 close csr_pact;
1129 --
1130 if g_debug then
1131 hr_utility.set_location(l_proc,40);
1132 end if;
1133 --
1134 pay_jp_isdf_archive_pkg.init_pact(
1135 p_payroll_action_id => l_payroll_action_id);
1136 --
1137 -- reset to force archive because of concurrent parameter might be N
1138 pay_jp_isdf_archive_pkg.g_archive_default_flag := 'Y';
1139 --
1140 if g_debug then
1141 hr_utility.set_location(l_proc,50);
1142 end if;
1143 --
1144 -- set global argument of assact in pay_jp_isdf_archive_pkg
1145 pay_jp_isdf_archive_pkg.init_assact(
1146 p_assignment_action_id => l_assact_rec.assignment_action_id,
1147 p_assignment_id => l_assact_rec.assignment_id);
1148 --
1149 if g_debug then
1150 hr_utility.set_location(l_proc,60);
1151 end if;
1152 --
1153 pay_jp_isdf_archive_pkg.archive_assact(
1154 p_assignment_action_id => l_assact_rec.assignment_action_id,
1155 p_assignment_id => l_assact_rec.assignment_id);
1156 --
1157 if g_debug then
1158 hr_utility.trace('end archive_assact');
1159 hr_utility.set_location(l_proc,70);
1160 hr_utility.trace('start update_assact');
1161 end if;
1162 --
1163 p_object_version_number := l_assact_rec.object_version_number + 1;
1164 --
1165 --api is disable because assact has been locked.
1166 --pay_jp_isdf_dml_pkg.update_assact(
1167 -- p_action_information_id => l_assact_rec.assignment_action_id,
1168 -- p_object_version_number => p_object_version_number,
1169 -- p_transaction_status => 'N',
1170 -- p_finalized_date => l_assact_rec.finalized_date,
1171 -- p_finalized_by => l_assact_rec.finalized_by,
1172 -- p_user_comments => l_assact_rec.user_comments,
1173 -- p_admin_comments => l_assact_rec.admin_comments,
1174 -- p_transfer_status => l_assact_rec.transfer_status,
1175 -- p_transfer_date => l_assact_rec.transfer_date,
1176 -- p_expiry_date => l_assact_rec.expiry_date);
1177 update pay_jp_isdf_assact_dml_v
1178 set object_version_number = p_object_version_number,
1179 transaction_status = 'N'
1180 where row_id = l_assact_rec.row_id;
1181 --
1182 if g_debug then
1183 hr_utility.trace('end update_assact');
1184 hr_utility.set_location(l_proc,1000);
1185 end if;
1186 --
1187 end do_new;
1188 --
1189 -- -------------------------------------------------------------------------
1190 -- do_apply
1191 -- -------------------------------------------------------------------------
1192 procedure do_apply(
1193 p_action_information_id in number,
1194 p_object_version_number in out nocopy number)
1195 is
1196 l_proc varchar2(80) := c_package||'do_apply';
1197 l_submission_date date;
1198 l_assact_rec pay_jp_isdf_assact_v%rowtype;
1199 begin
1200 --
1201 if g_debug then
1202 hr_utility.set_location(l_proc,0);
1203 end if;
1204 --
1205 l_submission_date := check_submission_period(p_action_information_id);
1206 --
1207 if g_debug then
1208 hr_utility.set_location(l_proc,10);
1209 hr_utility.trace('submission_date : '||fnd_date.date_to_canonical(l_submission_date));
1210 end if;
1211 --
1212 pay_jp_isdf_dml_pkg.lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
1213 --
1214 if l_assact_rec.transaction_status <> 'N' then
1215 fnd_message.set_name('PAY','PAY_JP_DEF_INVALID_TXN_STATUS');
1216 fnd_message.raise_error;
1217 end if;
1218 --
1219 if g_debug then
1220 hr_utility.set_location(l_proc,20);
1221 hr_utility.set_location(l_proc,20);
1222 hr_utility.trace('action_information_id : '||p_action_information_id);
1223 hr_utility.trace('object_version_number : '||p_object_version_number);
1224 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
1225 hr_utility.trace('start update_assact');
1226 end if;
1227 --
1228 p_object_version_number := l_assact_rec.object_version_number + 1;
1229 --
1230 --api is disable because assact has been locked.
1231 --pay_jp_isdf_dml_pkg.update_assact(
1232 -- p_action_information_id => l_assact_rec.assignment_action_id,
1233 -- p_object_version_number => p_object_version_number,
1234 -- p_transaction_status => l_assact_rec.transaction_status,
1235 -- p_finalized_date => l_assact_rec.finalized_date,
1236 -- p_finalized_by => l_assact_rec.finalized_by,
1237 -- p_user_comments => l_assact_rec.user_comments,
1238 -- p_admin_comments => l_assact_rec.admin_comments,
1239 -- p_transfer_status => l_assact_rec.transfer_status,
1240 -- p_transfer_date => l_assact_rec.transfer_date,
1241 -- p_expiry_date => l_assact_rec.expiry_date);
1242 update pay_jp_isdf_assact_dml_v
1243 set object_version_number = p_object_version_number
1244 where row_id = l_assact_rec.row_id;
1245 --
1246 if g_debug then
1247 hr_utility.trace('end update_assact');
1248 hr_utility.set_location(l_proc,1000);
1249 end if;
1250 --
1251 end do_apply;
1252 --
1253 -- -------------------------------------------------------------------------
1254 -- calc_total
1255 -- -------------------------------------------------------------------------
1256 procedure calc_total(
1257 p_assignment_action_id in number,
1258 p_calc_total_rec out nocopy t_calc_total_rec)
1259 is
1260 --
1261 l_proc varchar2(80) := c_package||'calc_total';
1262 l_action_info_tbl t_action_info_tbl;
1263 --
1264 l_archive_cnt number := 0;
1265 --
1266 cursor csr_archive_data
1267 is
1268 select action_information_id,
1269 action_context_id,
1270 action_context_type,
1271 object_version_number,
1272 action_information_category,
1273 action_information1,
1274 action_information2,
1275 action_information3,
1276 action_information4,
1277 action_information5,
1278 action_information6,
1279 action_information7,
1280 action_information8,
1281 action_information9,
1282 action_information10,
1283 action_information11,
1284 action_information12,
1285 action_information13,
1286 action_information14,
1287 action_information15,
1288 action_information16,
1289 action_information17,
1290 action_information18,
1291 action_information19,
1292 action_information20,
1293 action_information21,
1294 action_information22,
1295 action_information23,
1296 action_information24,
1297 action_information25,
1298 action_information26,
1299 action_information27,
1300 action_information28,
1301 action_information29,
1302 action_information30,
1303 effective_date,
1304 assignment_id
1305 from pay_action_information pai
1306 where pai.action_context_id = p_assignment_action_id
1307 and pai.action_context_type = 'AAP'
1308 and pai.action_information_category in ('JP_ISDF_LIFE_GEN',
1309 'JP_ISDF_LIFE_PENS',
1310 'JP_ISDF_NONLIFE',
1311 'JP_ISDF_SOCIAL',
1312 'JP_ISDF_MUTUAL_AID',
1313 'JP_ISDF_SPOUSE',
1314 'JP_ISDF_SPOUSE_INC')
1315 and pai.action_information1 <> 'D';
1316 --
1317 begin
1318 --
1319 if g_debug then
1320 hr_utility.set_location(l_proc,0);
1321 hr_utility.trace('csr_archive_data bulk collect start');
1322 end if;
1323 --
1324 -- #2243411 bulk collect bug fix is available from 9.2
1325 open csr_archive_data;
1326 --fetch csr_archive_data bulk collect into l_action_info_tbl;
1327 loop
1328 --
1329 l_archive_cnt := l_archive_cnt + 1;
1330 --
1331 fetch csr_archive_data into l_action_info_tbl(l_archive_cnt);
1332 exit when csr_archive_data%notfound;
1333 --
1334 end loop;
1335 close csr_archive_data;
1336 --
1337 if g_debug then
1338 hr_utility.set_location(l_proc,10);
1339 hr_utility.trace('csr_archive_data bulk collect end');
1340 hr_utility.trace('csr_archive_data count : '||l_action_info_tbl.count);
1341 end if;
1342 --
1343 p_calc_total_rec.life_gen := 0;
1344 p_calc_total_rec.life_pens := 0;
1345 p_calc_total_rec.earthquake := 0;
1346 p_calc_total_rec.nonlife_long := 0;
1347 p_calc_total_rec.nonlife_short := 0;
1348 p_calc_total_rec.national_pens := 0;
1349 p_calc_total_rec.social := 0;
1350 p_calc_total_rec.mutual_aid_ec := 0;
1351 p_calc_total_rec.mutual_aid_p := 0;
1352 p_calc_total_rec.mutual_aid_dsc := 0;
1353 p_calc_total_rec.sp_emp_inc := 0;
1354 p_calc_total_rec.sp_spouse_inc := 0;
1355 p_calc_total_rec.sp_sp_type := null;
1356 p_calc_total_rec.sp_wid_type := null;
1357 p_calc_total_rec.sp_dct_exc := null;
1358 p_calc_total_rec.sp_inc_cnt := 0;
1359 p_calc_total_rec.sp_earned_inc := 0;
1360 p_calc_total_rec.sp_earned_inc_exp := 0;
1361 p_calc_total_rec.sp_business_inc := 0;
1362 p_calc_total_rec.sp_business_inc_exp := 0;
1363 p_calc_total_rec.sp_miscellaneous_inc := 0;
1364 p_calc_total_rec.sp_miscellaneous_inc_exp := 0;
1365 p_calc_total_rec.sp_dividend_inc := 0;
1366 p_calc_total_rec.sp_dividend_inc_exp := 0;
1367 p_calc_total_rec.sp_real_estate_inc := 0;
1368 p_calc_total_rec.sp_real_estate_inc_exp := 0;
1369 p_calc_total_rec.sp_retirement_inc := 0;
1370 p_calc_total_rec.sp_retirement_inc_exp := 0;
1371 p_calc_total_rec.sp_other_inc := 0;
1372 p_calc_total_rec.sp_other_inc_exp := 0;
1373 p_calc_total_rec.sp_other_inc_exp_dct := 0;
1374 p_calc_total_rec.sp_other_inc_exp_tmp := 0;
1375 p_calc_total_rec.sp_other_inc_exp_tmp_exp := 0;
1376 --
1377 if g_debug then
1378 hr_utility.set_location(l_proc,20);
1379 end if;
1380 --
1381 for i in 1..l_action_info_tbl.count loop
1382 --
1383 if l_action_info_tbl(i).action_information_category = 'JP_ISDF_LIFE_GEN' then
1384 --
1385 p_calc_total_rec.life_gen := nvl(p_calc_total_rec.life_gen,0) + nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information12),0);
1386 --
1387 elsif l_action_info_tbl(i).action_information_category = 'JP_ISDF_LIFE_PENS' then
1388 --
1389 p_calc_total_rec.life_pens := nvl(p_calc_total_rec.life_pens,0) + nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information13),0);
1390 --
1391 elsif l_action_info_tbl(i).action_information_category = 'JP_ISDF_NONLIFE' then
1392 --
1393 -- non support calc for negative amount since deduction from multiple type is acceptable, it is not feasible in system.
1394 if nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information10),0) > 0 then
1395 --
1396 if l_action_info_tbl(i).action_information2 = 'EQ' then
1397 p_calc_total_rec.earthquake := nvl(p_calc_total_rec.earthquake,0) + nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information10),0);
1398 elsif l_action_info_tbl(i).action_information2 = 'L' then
1399 p_calc_total_rec.nonlife_long := nvl(p_calc_total_rec.nonlife_long,0) + nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information10),0);
1400 elsif l_action_info_tbl(i).action_information2 = 'S' then
1401 p_calc_total_rec.nonlife_short := nvl(p_calc_total_rec.nonlife_short,0) + nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information10),0);
1402 end if;
1403 --
1404 end if;
1405 --
1406 elsif l_action_info_tbl(i).action_information_category = 'JP_ISDF_SOCIAL' then
1407 --
1408 if l_action_info_tbl(i).action_information7 = 'Y' then
1409 p_calc_total_rec.national_pens := nvl(p_calc_total_rec.national_pens,0) + nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information6),0);
1410 end if;
1411 --
1412 p_calc_total_rec.social := nvl(p_calc_total_rec.social,0) + nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information6),0);
1413 --
1414 elsif l_action_info_tbl(i).action_information_category = 'JP_ISDF_MUTUAL_AID' then
1415 --
1416 p_calc_total_rec.mutual_aid_ec := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information2),0);
1417 p_calc_total_rec.mutual_aid_p := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information3),0);
1418 p_calc_total_rec.mutual_aid_dsc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information4),0);
1419 --
1420 elsif l_action_info_tbl(i).action_information_category = 'JP_ISDF_SPOUSE' then
1421 --
1422 p_calc_total_rec.sp_emp_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information6),0);
1423 p_calc_total_rec.sp_spouse_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information10),0);
1424 p_calc_total_rec.sp_sp_type := l_action_info_tbl(i).action_information7;
1425 p_calc_total_rec.sp_wid_type := l_action_info_tbl(i).action_information8;
1426 p_calc_total_rec.sp_dct_exc := l_action_info_tbl(i).action_information9;
1427 --
1428 elsif l_action_info_tbl(i).action_information_category = 'JP_ISDF_SPOUSE_INC' then
1429 --
1430 p_calc_total_rec.sp_inc_cnt := p_calc_total_rec.sp_inc_cnt + 1;
1431 p_calc_total_rec.sp_earned_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information2),0);
1432 p_calc_total_rec.sp_earned_inc_exp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information3),c_sp_earned_inc_exp);
1433 p_calc_total_rec.sp_business_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information4),0);
1434 p_calc_total_rec.sp_business_inc_exp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information5),0);
1435 p_calc_total_rec.sp_miscellaneous_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information6),0);
1436 p_calc_total_rec.sp_miscellaneous_inc_exp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information7),0);
1437 p_calc_total_rec.sp_dividend_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information8),0);
1438 p_calc_total_rec.sp_dividend_inc_exp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information9),0);
1439 p_calc_total_rec.sp_real_estate_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information10),0);
1440 p_calc_total_rec.sp_real_estate_inc_exp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information11),0);
1441 p_calc_total_rec.sp_retirement_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information12),0);
1442 p_calc_total_rec.sp_retirement_inc_exp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information13),0);
1443 p_calc_total_rec.sp_other_inc := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information14),0);
1444 p_calc_total_rec.sp_other_inc_exp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information15),0);
1445 p_calc_total_rec.sp_other_inc_exp_dct := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information16),0);
1446 p_calc_total_rec.sp_other_inc_exp_tmp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information17),0);
1447 p_calc_total_rec.sp_other_inc_exp_tmp_exp := nvl(fnd_number.canonical_to_number(l_action_info_tbl(i).action_information18),0);
1448 --
1449 end if;
1450 --
1451 end loop;
1452 --
1453 if g_debug then
1454 hr_utility.trace('life_gen : '||to_char(p_calc_total_rec.life_gen));
1455 hr_utility.trace('life_pens : '||to_char(p_calc_total_rec.life_pens));
1456 hr_utility.trace('earthquake : '||to_char(p_calc_total_rec.earthquake));
1457 hr_utility.trace('nonlife_long : '||to_char(p_calc_total_rec.nonlife_long));
1458 hr_utility.trace('nonlife_short : '||to_char(p_calc_total_rec.nonlife_short));
1459 hr_utility.trace('national_pens : '||to_char(p_calc_total_rec.national_pens));
1460 hr_utility.trace('social : '||to_char(p_calc_total_rec.social));
1461 hr_utility.trace('mutual_aid_ec : '||to_char(p_calc_total_rec.mutual_aid_ec));
1462 hr_utility.trace('mutual_aid_p : '||to_char(p_calc_total_rec.mutual_aid_p));
1463 hr_utility.trace('mutual_aid_dsc : '||to_char(p_calc_total_rec.mutual_aid_dsc));
1464 hr_utility.trace('sp_emp_inc : '||to_char(p_calc_total_rec.sp_emp_inc));
1465 hr_utility.trace('sp_spouse_inc : '||to_char(p_calc_total_rec.sp_spouse_inc));
1466 hr_utility.trace('sp_type : '||p_calc_total_rec.sp_sp_type);
1467 hr_utility.trace('sp_wid_type : '||p_calc_total_rec.sp_wid_type);
1468 hr_utility.trace('sp_dct_exc : '||p_calc_total_rec.sp_dct_exc);
1469 hr_utility.trace('sp_inc_cnt : '||to_char(p_calc_total_rec.sp_inc_cnt));
1470 hr_utility.trace('sp_earned_inc : '||to_char(p_calc_total_rec.sp_earned_inc));
1471 hr_utility.trace('sp_earned_inc_exp : '||to_char(p_calc_total_rec.sp_earned_inc_exp));
1472 hr_utility.trace('sp_business_inc : '||to_char(p_calc_total_rec.sp_business_inc));
1473 hr_utility.trace('sp_business_inc_exp : '||to_char(p_calc_total_rec.sp_business_inc_exp));
1474 hr_utility.trace('sp_miscellaneous_inc : '||to_char(p_calc_total_rec.sp_miscellaneous_inc));
1475 hr_utility.trace('sp_miscellaneous_inc_exp : '||to_char(p_calc_total_rec.sp_miscellaneous_inc_exp));
1476 hr_utility.trace('sp_dividend_inc : '||to_char(p_calc_total_rec.sp_dividend_inc));
1477 hr_utility.trace('sp_dividend_inc_exp : '||to_char(p_calc_total_rec.sp_dividend_inc_exp));
1478 hr_utility.trace('sp_real_estate_inc : '||to_char(p_calc_total_rec.sp_real_estate_inc));
1479 hr_utility.trace('sp_real_estate_inc_exp : '||to_char(p_calc_total_rec.sp_real_estate_inc_exp));
1480 hr_utility.trace('sp_retirement_inc : '||to_char(p_calc_total_rec.sp_retirement_inc));
1481 hr_utility.trace('sp_retirement_inc_exp : '||to_char(p_calc_total_rec.sp_retirement_inc_exp));
1482 hr_utility.trace('sp_other_inc : '||to_char(p_calc_total_rec.sp_other_inc));
1483 hr_utility.trace('sp_other_inc_exp : '||to_char(p_calc_total_rec.sp_other_inc_exp));
1484 hr_utility.trace('sp_other_inc_exp_dct : '||to_char(p_calc_total_rec.sp_other_inc_exp_dct));
1485 hr_utility.trace('sp_other_inc_exp_tmp : '||to_char(p_calc_total_rec.sp_other_inc_exp_tmp));
1486 hr_utility.trace('sp_other_inc_exp_tmp_exp : '||to_char(p_calc_total_rec.sp_other_inc_exp_tmp_exp));
1487 hr_utility.set_location(l_proc,1000);
1488 end if;
1489 --
1490 end calc_total;
1491 --
1492 -- -------------------------------------------------------------------------
1493 -- calc_life_ins_dct
1494 -- -------------------------------------------------------------------------
1495 procedure calc_life_ins_dct(
1496 p_life_gen_i in number,
1497 p_life_pens_i in number,
1498 p_business_group_id in number,
1499 p_effective_date in date,
1500 p_life_ins_dct_o out nocopy number,
1501 p_life_gen_o out nocopy number,
1502 p_life_pens_o out nocopy number)
1503 is
1504 --
1505 l_proc varchar2(80) := c_package||'calc_total';
1506 i_life_gen number := nvl(p_life_gen_i,0);
1507 i_life_pens number := nvl(p_life_pens_i,0);
1508 o_life_pens number := 0;
1509 o_life_gen number := 0;
1510 --
1511 begin
1512 --
1513 if g_debug then
1514 hr_utility.set_location(l_proc,0);
1515 hr_utility.trace('p_life_gen_i : '||to_char(p_life_gen_i));
1516 hr_utility.trace('p_life_pens_i : '||to_char(p_life_pens_i));
1517 hr_utility.trace('p_business_group_id : '||to_char(p_business_group_id));
1518 hr_utility.trace('p_effective_date : '||to_char(p_effective_date,'YYYY/MM/DD'));
1519 hr_utility.trace('i_life_gen : '||to_char(i_life_gen));
1520 hr_utility.trace('i_life_pens : '||to_char(i_life_pens));
1521 end if;
1522 --
1523 -- formula datetrack
1524 if (p_effective_date >= hr_api.g_sot
1525 and p_effective_date <= hr_api.g_eot) then
1526 --
1527 if g_debug then
1528 hr_utility.set_location(l_proc,10);
1529 end if;
1530 --
1531 if i_life_gen > 0 then
1532 --
1533 if g_debug then
1534 hr_utility.set_location(l_proc,20);
1535 end if;
1536 --
1537 -- udt satisfy validation of max value
1538 o_life_gen := round(i_life_gen
1539 * to_number(hruserdt.get_table_value(
1540 p_business_group_id,
1541 c_life_gen_calc_udt,
1542 c_rate_udtcol,
1543 to_char(i_life_gen),
1544 p_effective_date))
1545 + to_number(hruserdt.get_table_value(
1546 p_business_group_id,
1547 c_life_gen_calc_udt,
1548 c_add_adj_udtcol,
1549 to_char(i_life_gen),
1550 p_effective_date)));
1551 --
1552 if g_debug then
1553 hr_utility.set_location(l_proc,30);
1554 end if;
1555 --
1556 end if;
1557 --
1558 if g_debug then
1559 hr_utility.set_location(l_proc,40);
1560 end if;
1561 --
1562 if i_life_pens > 0 then
1563 --
1564 if g_debug then
1565 hr_utility.set_location(l_proc,50);
1566 end if;
1567 --
1568 -- udt satisfy validation of max value
1569 o_life_pens := round(i_life_pens
1570 * to_number(hruserdt.get_table_value(
1571 p_business_group_id,
1572 c_life_pens_calc_udt,
1573 c_rate_udtcol,
1574 to_char(i_life_pens),
1575 p_effective_date))
1576 + to_number(hruserdt.get_table_value(
1577 p_business_group_id,
1578 c_life_pens_calc_udt,
1579 c_add_adj_udtcol,
1580 to_char(i_life_pens),
1581 p_effective_date)));
1582 --
1583 if g_debug then
1584 hr_utility.set_location(l_proc,60);
1585 end if;
1586 --
1587 end if;
1588 --
1589 end if;
1590 --
1591 p_life_gen_o := o_life_gen;
1592 p_life_pens_o := o_life_pens;
1593 p_life_ins_dct_o := o_life_gen + o_life_pens;
1594 --
1595 if g_debug then
1596 hr_utility.trace('o_life_gen : '||to_char(o_life_gen));
1597 hr_utility.trace('o_life_pens : '||to_char(o_life_pens));
1598 hr_utility.trace('p_life_gen_o : '||to_char(p_life_gen_o));
1599 hr_utility.trace('p_life_pens_o : '||to_char(p_life_pens_o));
1600 hr_utility.trace('p_life_ins_dct_o : '||to_char(p_life_ins_dct_o));
1601 hr_utility.set_location(l_proc,1000);
1602 end if;
1603 --
1604 end calc_life_ins_dct;
1605 --
1606 -- -------------------------------------------------------------------------
1607 -- calc_nonlife
1608 -- -------------------------------------------------------------------------
1609 procedure calc_nonlife_dct(
1610 p_earthquake_i in number,
1611 p_nonlife_long_i in number,
1612 p_nonlife_short_i in number,
1613 p_business_group_id in number,
1614 p_effective_date in date,
1615 p_nonlife_dct_o out nocopy number,
1616 p_earthquake_o out nocopy number,
1617 p_nonlife_long_o out nocopy number,
1618 p_nonlife_short_o out nocopy number)
1619 is
1620 --
1621 l_proc varchar2(80) := c_package||'calc_total';
1622 i_earthquake number := nvl(p_earthquake_i,0);
1623 i_nonlife_long number := nvl(p_nonlife_long_i,0);
1624 i_nonlife_short number := nvl(p_nonlife_short_i,0);
1625 o_earthquake number := 0;
1626 o_nonlife_long number := 0;
1627 o_nonlife_short number := 0;
1628 o_nonlife_dct number;
1629 --
1630 begin
1631 --
1632 if g_debug then
1633 hr_utility.set_location(l_proc,0);
1634 hr_utility.trace('p_earthquake_i : '||to_char(p_earthquake_i));
1635 hr_utility.trace('p_nonlife_long_i : '||to_char(p_nonlife_long_i));
1636 hr_utility.trace('p_nonlife_long_i : '||to_char(p_nonlife_short_i));
1637 hr_utility.trace('p_business_group_id : '||to_char(p_business_group_id));
1638 hr_utility.trace('p_effective_date : '||to_char(p_effective_date,'YYYY/MM/DD'));
1639 hr_utility.trace('i_earthquake : '||to_char(i_earthquake));
1640 hr_utility.trace('i_nonlife_long : '||to_char(i_nonlife_long));
1641 hr_utility.trace('i_nonlife_short : '||to_char(i_nonlife_short));
1642 end if;
1643 --
1644 -- formula datetrack
1645 if (p_effective_date >= hr_api.g_sot
1646 and p_effective_date < c_st_upd_date_2007) then
1647 --
1648 if g_debug then
1649 hr_utility.set_location(l_proc,10);
1650 end if;
1651 --
1652 o_earthquake := null;
1653 --
1654 if i_nonlife_long > 0 then
1655 --
1656 if g_debug then
1657 hr_utility.set_location(l_proc,20);
1658 end if;
1659 --
1660 -- udt satisfy validation of max value
1661 o_nonlife_long := round(i_nonlife_long
1662 * to_number(hruserdt.get_table_value(
1663 p_business_group_id,
1664 c_nonlife_long_calc_udt,
1665 c_rate_udtcol,
1666 to_char(i_nonlife_long),
1667 p_effective_date))
1668 + to_number(hruserdt.get_table_value(
1669 p_business_group_id,
1670 c_nonlife_long_calc_udt,
1671 c_add_adj_udtcol,
1672 to_char(i_nonlife_long),
1673 p_effective_date)));
1674 --
1675 if g_debug then
1676 hr_utility.set_location(l_proc,30);
1677 end if;
1678 --
1679 end if;
1680 --
1681 if g_debug then
1682 hr_utility.set_location(l_proc,40);
1683 end if;
1684 --
1685 if i_nonlife_short > 0 then
1686 --
1687 if g_debug then
1688 hr_utility.set_location(l_proc,50);
1689 end if;
1690 --
1691 -- udt satisfy validation of max value
1692 o_nonlife_short := round(i_nonlife_short
1693 * to_number(hruserdt.get_table_value(
1694 p_business_group_id,
1695 c_nonlife_short_calc_udt,
1696 c_rate_udtcol,
1697 to_char(i_nonlife_short),
1698 p_effective_date))
1699 + to_number(hruserdt.get_table_value(
1700 p_business_group_id,
1701 c_nonlife_short_calc_udt,
1702 c_add_adj_udtcol,
1703 to_char(i_nonlife_short),
1704 p_effective_date)));
1705 --
1706 if g_debug then
1707 hr_utility.set_location(l_proc,60);
1708 end if;
1709 --
1710 end if;
1711 --
1712 o_nonlife_dct := o_nonlife_long + o_nonlife_short;
1713 --
1714 if g_effective_date <> p_effective_date
1715 or g_effective_date is null
1716 or c_nonlife_max is null then
1717 --
1718 -- need always reset cache has problem in case date is switched between 2006 and 2007.
1719 c_nonlife_max := to_number(hruserdt.get_table_value(
1720 p_business_group_id,
1721 c_yea_calc_max_udt,
1722 c_max_udtcol,
1723 c_nonlife_udtrow,
1724 p_effective_date));
1725 --
1726 end if;
1727 --
1728 if g_debug then
1729 hr_utility.set_location(l_proc,70);
1730 hr_utility.trace('o_nonlife_dct : '||to_char(o_nonlife_dct));
1731 hr_utility.trace('c_nonlife_max : '||to_char(c_nonlife_max));
1732 end if;
1733 --
1734 if o_nonlife_dct > c_nonlife_max then
1735 o_nonlife_dct := c_nonlife_max;
1736 end if;
1737 --
1738 if g_debug then
1739 hr_utility.set_location(l_proc,80);
1740 end if;
1741 --
1742 elsif (p_effective_date >= c_st_upd_date_2007
1743 and p_effective_date <= hr_api.g_eot) then
1744 --
1745 if g_debug then
1746 hr_utility.set_location(l_proc,90);
1747 end if;
1748 --
1749 o_nonlife_short := null;
1750 --
1751 if i_earthquake > 0 then
1752 --
1753 if g_debug then
1754 hr_utility.set_location(l_proc,100);
1755 end if;
1756 --
1757 o_earthquake := round(i_earthquake);
1758 if c_earthquake_max is null then
1759 c_earthquake_max := to_number(hruserdt.get_table_value(
1760 p_business_group_id,
1761 c_yea_calc_max_udt,
1762 c_max_udtcol,
1763 c_earthquake_udtrow,
1764 p_effective_date));
1765 end if;
1766 --
1767 if g_debug then
1768 hr_utility.set_location(l_proc,110);
1769 hr_utility.trace('o_earthquake : '||to_char(o_earthquake));
1770 hr_utility.trace('c_earthquake_max : '||to_char(c_earthquake_max));
1771 end if;
1772 --
1773 if o_earthquake > c_earthquake_max then
1774 o_earthquake := c_earthquake_max;
1775 end if;
1776 --
1777 if g_debug then
1778 hr_utility.set_location(l_proc,120);
1779 end if;
1780 --
1781 end if;
1782 --
1783 if i_nonlife_long > 0 then
1784 --
1785 if g_debug then
1786 hr_utility.set_location(l_proc,130);
1787 end if;
1788 --
1789 -- udt satisfy validation of max value
1790 o_nonlife_long := round(i_nonlife_long
1791 * to_number(hruserdt.get_table_value(
1792 p_business_group_id,
1793 c_nonlife_long_calc_udt,
1794 c_rate_udtcol,
1795 to_char(i_nonlife_long),
1796 p_effective_date))
1797 + to_number(hruserdt.get_table_value(
1798 p_business_group_id,
1799 c_nonlife_long_calc_udt,
1800 c_add_adj_udtcol,
1801 to_char(i_nonlife_long),
1802 p_effective_date)));
1803 --
1804 if g_debug then
1805 hr_utility.set_location(l_proc,140);
1806 end if;
1807 --
1808 end if;
1809 --
1810 if g_debug then
1811 hr_utility.set_location(l_proc,150);
1812 end if;
1813 --
1814 o_nonlife_dct := o_earthquake + o_nonlife_long;
1815 --
1816 if g_effective_date <> p_effective_date
1817 or g_effective_date is null
1818 or c_nonlife_max is null then
1819 --
1820 -- need always reset cache has problem in case date is switched between 2006 and 2007.
1821 c_nonlife_max := to_number(hruserdt.get_table_value(
1822 p_business_group_id,
1823 c_yea_calc_max_udt,
1824 c_max_udtcol,
1825 c_nonlife_udtrow,
1826 p_effective_date));
1827 --
1828 end if;
1829 --
1830 if g_debug then
1831 hr_utility.set_location(l_proc,160);
1832 hr_utility.trace('o_nonlife_dct : '||to_char(o_nonlife_dct));
1833 hr_utility.trace('c_nonlife_max : '||to_char(c_nonlife_max));
1834 end if;
1835 --
1836 if o_nonlife_dct > c_nonlife_max then
1837 o_nonlife_dct := c_nonlife_max;
1838 end if;
1839 --
1840 if g_debug then
1841 hr_utility.set_location(l_proc,170);
1842 end if;
1843 --
1844 end if;
1845 --
1846 p_earthquake_o := o_earthquake;
1847 p_nonlife_long_o := o_nonlife_long;
1848 p_nonlife_short_o := o_nonlife_short;
1849 p_nonlife_dct_o := o_nonlife_dct;
1850 --
1851 if g_debug then
1852 hr_utility.trace('p_earthquake_o : '||to_char(p_earthquake_o));
1853 hr_utility.trace('p_nonlife_long_o : '||to_char(p_nonlife_long_o));
1854 hr_utility.trace('p_nonlife_short_o : '||to_char(p_nonlife_short_o));
1855 hr_utility.trace('p_nonlife_dct_o : '||to_char(p_nonlife_dct_o));
1856 hr_utility.trace('o_nonlife_long : '||to_char(o_nonlife_long));
1857 hr_utility.trace('o_nonlife_short : '||to_char(o_nonlife_short));
1858 hr_utility.trace('o_nonlife_dct : '||to_char(o_nonlife_dct));
1859 hr_utility.set_location(l_proc,1000);
1860 end if;
1861 --
1862 end calc_nonlife_dct;
1863 --
1864 -- -------------------------------------------------------------------------
1865 -- calc_nonlife_dct
1866 -- -------------------------------------------------------------------------
1867 -- wrapper, activate since 2007 statutory update
1868 procedure calc_nonlife_dct(
1869 p_earthquake_i in number,
1870 p_nonlife_long_i in number,
1871 p_business_group_id in number,
1872 p_effective_date in date,
1873 p_nonlife_dct_o out nocopy number,
1874 p_earthquake_o out nocopy number,
1875 p_nonlife_long_o out nocopy number)
1876 is
1877 --
1878 l_proc varchar2(80) := c_package||'calc_nonlife_dct';
1879 o_earthquake number;
1880 o_nonlife_long number;
1881 o_nonlife_short number;
1882 o_nonlife_dct number;
1883 --
1884 begin
1885 --
1886 if g_debug then
1887 hr_utility.set_location(l_proc,0);
1888 hr_utility.trace('calc_nonlife_dct wrapper');
1889 end if;
1890 --
1891 if (p_effective_date >= hr_api.g_sot
1892 and p_effective_date < c_st_upd_date_2007) then
1893 --
1894 if g_debug then
1895 hr_utility.set_location(l_proc,10);
1896 end if;
1897 --
1898 calc_nonlife_dct(
1899 p_earthquake_i => p_earthquake_i,
1900 p_nonlife_long_i => p_nonlife_long_i,
1901 p_nonlife_short_i => null,
1902 p_business_group_id => p_business_group_id,
1903 p_effective_date => p_effective_date,
1904 p_nonlife_dct_o => o_nonlife_dct,
1905 p_earthquake_o => o_earthquake,
1906 p_nonlife_long_o => o_nonlife_long,
1907 p_nonlife_short_o => o_nonlife_short);
1908 --
1909 end if;
1910 --
1911 p_earthquake_o := o_earthquake;
1912 p_nonlife_long_o := o_nonlife_long;
1913 p_nonlife_dct_o := o_nonlife_dct;
1914 --
1915 if g_debug then
1916 hr_utility.set_location(l_proc,1000);
1917 end if;
1918 --
1919 end calc_nonlife_dct;
1920 --
1921 -- -------------------------------------------------------------------------
1922 -- calc_social_dct
1923 -- -------------------------------------------------------------------------
1924 procedure calc_social_dct(
1925 p_social_i in number,
1926 p_business_group_id in number,
1927 p_effective_date in date,
1928 p_social_dct_o out nocopy number)
1929 is
1930 --
1931 l_proc varchar2(80) := c_package||'calc_total';
1932 i_social number := nvl(p_social_i,0);
1933 o_social_dct number;
1934 --
1935 begin
1936 --
1937 if g_debug then
1938 hr_utility.set_location(l_proc,0);
1939 hr_utility.trace('p_social_i : '||to_char(p_social_i));
1940 hr_utility.trace('p_business_group_id : '||to_char(p_business_group_id));
1941 hr_utility.trace('p_effective_date : '||to_char(p_effective_date,'YYYY/MM/DD'));
1942 hr_utility.trace('i_social : '||to_char(i_social));
1943 end if;
1944 --
1945 -- formula datetrack
1946 if (p_effective_date >= hr_api.g_sot
1947 and p_effective_date <= hr_api.g_eot) then
1948 --
1949 if g_debug then
1950 hr_utility.set_location(l_proc,10);
1951 end if;
1952 --
1953 if i_social < 0 then
1954 --
1955 if g_debug then
1956 hr_utility.set_location(l_proc,20);
1957 end if;
1958 --
1959 o_social_dct := 0;
1960 --
1961 else
1962 --
1963 o_social_dct := round(i_social);
1964 --
1965 if g_debug then
1966 hr_utility.set_location(l_proc,30);
1967 end if;
1968 --
1969 end if;
1970 --
1971 end if;
1972 --
1973 p_social_dct_o := o_social_dct;
1974 --
1975 if g_debug then
1976 hr_utility.trace('p_social_dct_o : '||to_char(p_social_dct_o));
1977 hr_utility.trace('o_social_dct : '||to_char(o_social_dct));
1978 hr_utility.set_location(l_proc,1000);
1979 end if;
1980 --
1981 end calc_social_dct;
1982 --
1983 -- -------------------------------------------------------------------------
1984 -- calc_mutual_aid_dct
1985 -- -------------------------------------------------------------------------
1986 procedure calc_mutual_aid_dct(
1987 p_mutual_aid_ec_i in number,
1988 p_mutual_aid_p_i in number,
1989 p_mutual_aid_dsc_i in number,
1990 p_business_group_id in number,
1991 p_effective_date in date,
1992 p_mutual_aid_dct_o out nocopy number)
1993 is
1994 --
1995 l_proc varchar2(80) := c_package||'calc_total';
1996 i_mutual_aid_ec number := nvl(p_mutual_aid_ec_i,0);
1997 i_mutual_aid_p number := nvl(p_mutual_aid_p_i,0);
1998 i_mutual_aid_dsc number := nvl(p_mutual_aid_dsc_i,0);
1999 o_mutual_aid_dct number;
2000 --
2001 begin
2002 --
2003 if g_debug then
2004 hr_utility.set_location(l_proc,0);
2005 hr_utility.trace('p_mutual_aid_ec_i : '||to_char(p_mutual_aid_ec_i));
2006 hr_utility.trace('p_mutual_aid_p_i : '||to_char(p_mutual_aid_p_i));
2007 hr_utility.trace('p_mutual_aid_dsc_i : '||to_char(p_mutual_aid_dsc_i));
2008 hr_utility.trace('p_business_group_id : '||to_char(p_business_group_id));
2009 hr_utility.trace('p_effective_date : '||to_char(p_effective_date,'YYYY/MM/DD'));
2010 hr_utility.trace('i_mutual_aid_ec : '||to_char(i_mutual_aid_ec));
2011 hr_utility.trace('i_mutual_aid_p : '||to_char(i_mutual_aid_p));
2012 hr_utility.trace('i_mutual_aid_dsc : '||to_char(i_mutual_aid_dsc));
2013 end if;
2014 --
2015 -- formula datetrack
2016 if (p_effective_date >= hr_api.g_sot
2017 and p_effective_date <= hr_api.g_eot) then
2018 --
2019 if g_debug then
2020 hr_utility.set_location(l_proc,10);
2021 end if;
2022 --
2023 o_mutual_aid_dct := round(i_mutual_aid_ec + i_mutual_aid_p + i_mutual_aid_dsc);
2024 --
2025 end if;
2026 --
2027 p_mutual_aid_dct_o := o_mutual_aid_dct;
2028 --
2029 if g_debug then
2030 hr_utility.trace('p_mutual_aid_dct_o : '||to_char(p_mutual_aid_dct_o));
2031 hr_utility.trace('o_mutual_aid_dct : '||to_char(o_mutual_aid_dct));
2032 hr_utility.set_location(l_proc,1000);
2033 end if;
2034 --
2035 end calc_mutual_aid_dct;
2036 --
2037 -- -------------------------------------------------------------------------
2038 -- calc_spouse_dct
2039 -- -------------------------------------------------------------------------
2040 procedure calc_spouse_dct(
2041 p_spouse_income_i in number,
2042 p_emp_income_i in number,
2043 p_sp_type_i in varchar2,
2044 p_wid_type_i in varchar2,
2045 p_dct_exc_i in varchar2,
2046 p_business_group_id in number,
2047 p_effective_date in date,
2048 p_spouse_dct_o out nocopy number)
2049 is
2050 --
2051 l_proc varchar2(80) := c_package||'calc_total';
2052 i_spouse_income number := nvl(p_spouse_income_i,0);
2053 i_emp_income number := nvl(p_emp_income_i,0);
2054 o_spouse_dct number := 0;
2055 --
2056 l_bg_itax_dpnt_ref_type varchar2(150);
2057 c_emp_income_max number;
2058 c_inc_spouse_dct_ma number;
2059 --
2060 begin
2061 --
2062 -- spouse_type validation is unnecessary. (just show it on form for confirmation)
2063 -- employer can distinguish the data validity between ss entry data and source data
2064 -- because employer can see archived spouse_type (source data) with ss entry data.
2065 -- spouse_type is fetched yea non-recurring entry so that it might not be setup
2066 -- data at the time when employer make archive data.
2067 -- spouse deduction on form will be calculated by based on ss entry data
2068 -- without message, even if ss entry data is not matched with source data,
2069 -- because employee cannot change spouse_type (source data) by themselves,
2070 -- specially in case when spouse_type is derived from eev (contact data can be changed).
2071 -- this might cause inconsistence between pay run result and form data,
2072 -- though employer should reject(ask employee to amend) ss entry data before pay run.
2073 --
2074 -- However, in this calculation, the calculated deduction is just information
2075 -- but as much as possible result should be same with actual yea run result,
2076 -- additionally spouse_type and widow_type are stored in recurring element,
2077 -- (dct_exc_flag is in non-recurring element), it means they probably will not be changed at yea run time
2078 -- and employer might has already setup the transferred override element for this ss form by manual,
2079 -- (actually this step is not desired.).
2080 -- finally the spouse_type, widow_type(set when spouse is inserted newly from ss
2081 -- or eev has been existed), dct_exc_flag(only case eev has been existed)
2082 -- their conditions are now included in current calculation logic like yea run formula.
2083 -- (At the calculated time, system cannot know final eev data at the yea runtime,
2084 -- so the result by this calculation might be different from final yea run result.)
2085 --
2086 if g_debug then
2087 hr_utility.set_location(l_proc,0);
2088 hr_utility.trace('p_spouse_income_i : '||to_char(p_spouse_income_i));
2089 hr_utility.trace('p_emp_income_i : '||to_char(p_emp_income_i));
2090 hr_utility.trace('p_business_group_id : '||to_char(p_business_group_id));
2091 hr_utility.trace('p_effective_date : '||to_char(p_effective_date,'YYYY/MM/DD'));
2092 hr_utility.trace('i_spouse_income : '||to_char(i_spouse_income));
2093 hr_utility.trace('i_emp_income : '||to_char(i_emp_income));
2094 hr_utility.trace('following are just information');
2095 hr_utility.trace('p_sp_type_i : '||p_sp_type_i);
2096 hr_utility.trace('p_wid_type_i : '||p_wid_type_i);
2097 hr_utility.trace('p_dct_exc_i : '||p_dct_exc_i);
2098 end if;
2099 --
2100 -- formula datetrack
2101 if (p_effective_date >= hr_api.g_sot
2102 and p_effective_date <= hr_api.g_eot) then
2103 --
2104 if g_debug then
2105 hr_utility.set_location(l_proc,10);
2106 end if;
2107 --
2108 if nvl(p_spouse_income_i,0) > 0
2109 and nvl(p_dct_exc_i,'N') = 'N' then
2110 --
2111 if g_debug then
2112 hr_utility.set_location(l_proc,20);
2113 end if;
2114 --
2115 if c_emp_income_max is null then
2116 --
2117 c_emp_income_max := to_number(hruserdt.get_table_value(
2118 p_business_group_id,
2119 c_yea_calc_max_udt,
2120 c_max_udtcol,
2121 c_sp_emp_income_udtrow,
2122 p_effective_date));
2123 --
2124 if g_debug then
2125 hr_utility.set_location(l_proc,30);
2126 hr_utility.trace('c_emp_income_max : '||to_char(c_emp_income_max));
2127 hr_utility.trace('i_emp_income : '||to_char(i_emp_income));
2128 end if;
2129 --
2130 end if;
2131 --
2132 if i_emp_income <= c_emp_income_max
2133 and nvl(p_wid_type_i,'0') = '0'
2134 -- calculate when sp_type is null since eev might be set in future.
2135 and nvl(p_sp_type_i,'1') <> '0' then
2136 --
2137 if g_debug then
2138 hr_utility.set_location(l_proc,40);
2139 end if;
2140 --
2141 if c_inc_spouse_dct_max is null then
2142 --
2143 c_inc_spouse_dct_max := to_number(hruserdt.get_table_value(
2144 p_business_group_id,
2145 c_yea_calc_max_udt,
2146 c_max_udtcol,
2147 c_sp_dctable_sp_income_udtrow,
2148 p_effective_date));
2149 --
2150 if g_debug then
2151 hr_utility.set_location(l_proc,50);
2152 hr_utility.trace('c_inc_spouse_dct_max : '||to_char(c_inc_spouse_dct_max));
2153 hr_utility.trace('i_spouse_income : '||to_char(i_spouse_income));
2154 end if;
2155 --
2156 end if;
2157 --
2158 -- even spouse_type is 2,3, if over inc_spouse_dct_max, they can be deductive for sp_spouse_dct.
2159 if i_spouse_income > c_inc_spouse_dct_max then
2160 --
2161 if g_debug then
2162 hr_utility.set_location(l_proc,60);
2163 end if;
2164 --
2165 if c_spouse_income_max is null then
2166 --
2167 c_spouse_income_max := to_number(hruserdt.get_table_value(
2168 p_business_group_id,
2169 c_yea_calc_max_udt,
2170 c_max_udtcol,
2171 c_sp_spouse_income_udtrow,
2172 p_effective_date));
2173 --
2174 end if;
2175 --
2176 if g_debug then
2177 hr_utility.set_location(l_proc,70);
2178 hr_utility.trace('c_spouse_income_max : '||to_char(c_spouse_income_max));
2179 hr_utility.trace('i_spouse_income : '||to_char(i_spouse_income));
2180 end if;
2181 --
2182 if i_spouse_income < c_spouse_income_max then
2183 --
2184 o_spouse_dct := round(to_number(hruserdt.get_table_value(
2185 p_business_group_id,
2186 c_spouse_calc_udt,
2187 c_dct_udtcol,
2188 to_char(i_spouse_income),
2189 p_effective_date)));
2190 --
2191 if g_debug then
2192 hr_utility.set_location(l_proc,80);
2193 hr_utility.trace('o_spouse_dct : '||to_char(o_spouse_dct));
2194 end if;
2195 --
2196 end if;
2197 --
2198 end if;
2199 --
2200 end if;
2201 --
2202 end if;
2203 --
2204 end if;
2205 --
2206 p_spouse_dct_o := o_spouse_dct;
2207 --
2208 if g_debug then
2209 hr_utility.trace('p_spouse_dct_o : '||to_char(p_spouse_dct_o));
2210 hr_utility.trace('o_spouse_dct : '||to_char(o_spouse_dct));
2211 hr_utility.set_location(l_proc,1000);
2212 end if;
2213 --
2214 end calc_spouse_dct;
2215 --
2216 -- -------------------------------------------------------------------------
2217 -- calc_spouse_inc
2218 -- -------------------------------------------------------------------------
2219 procedure calc_spouse_inc(
2220 p_sp_earned_inc_i in number,
2221 p_sp_earned_inc_exp_i in number,
2222 p_sp_business_inc_i in number,
2223 p_sp_business_inc_exp_i in number,
2224 p_sp_miscellaneous_inc_i in number,
2225 p_sp_miscellaneous_inc_exp_i in number,
2226 p_sp_dividend_inc_i in number,
2227 p_sp_dividend_inc_exp_i in number,
2228 p_sp_real_estate_inc_i in number,
2229 p_sp_real_estate_inc_exp_i in number,
2230 p_sp_retirement_inc_i in number,
2231 p_sp_retirement_inc_exp_i in number,
2232 p_sp_other_inc_i in number,
2233 p_sp_other_inc_exp_i in number,
2234 p_sp_other_inc_exp_dct_i in number,
2235 p_sp_other_inc_exp_tmp_i in number,
2236 p_sp_other_inc_exp_tmp_exp_i in number,
2237 p_sp_inc_cnt_i in number,
2238 p_ent_spouse_inc_i in number,
2239 p_business_group_id in number,
2240 p_effective_date in date,
2241 p_calc_spouse_inc_rec out nocopy t_calc_spouse_inc_rec,
2242 p_spouse_inc_o out nocopy number)
2243 is
2244 --
2245 l_proc varchar2(80) := c_package||'calc_total';
2246 o_spouse_inc number := 0;
2247 --
2248 begin
2249 --
2250 if g_debug then
2251 hr_utility.set_location(l_proc,0);
2252 hr_utility.trace('p_sp_earned_inc_i : '||to_char(p_sp_earned_inc_i));
2253 hr_utility.trace('p_sp_earned_inc_exp_i : '||to_char(p_sp_earned_inc_exp_i));
2254 hr_utility.trace('p_sp_business_inc_i : '||to_char(p_sp_business_inc_i));
2255 hr_utility.trace('p_sp_business_inc_exp_i : '||to_char(p_sp_business_inc_exp_i));
2256 hr_utility.trace('p_sp_miscellaneous_inc_i : '||to_char(p_sp_miscellaneous_inc_i));
2257 hr_utility.trace('p_sp_miscellaneous_inc_exp_i : '||to_char(p_sp_miscellaneous_inc_exp_i));
2258 hr_utility.trace('p_sp_dividend_inc_i : '||to_char(p_sp_dividend_inc_i));
2259 hr_utility.trace('p_sp_dividend_inc_exp_i : '||to_char(p_sp_dividend_inc_exp_i));
2260 hr_utility.trace('p_sp_real_estate_inc_i : '||to_char(p_sp_real_estate_inc_i));
2261 hr_utility.trace('p_sp_real_estate_inc_exp_i : '||to_char(p_sp_real_estate_inc_exp_i));
2262 hr_utility.trace('p_sp_retirement_inc_i : '||to_char(p_sp_retirement_inc_i));
2263 hr_utility.trace('p_sp_retirement_inc_exp_i : '||to_char(p_sp_retirement_inc_exp_i));
2264 hr_utility.trace('p_sp_other_inc_i : '||to_char(p_sp_other_inc_i));
2265 hr_utility.trace('p_sp_other_inc_exp_i : '||to_char(p_sp_other_inc_exp_i));
2266 hr_utility.trace('p_sp_other_inc_exp_dct_i : '||to_char(p_sp_other_inc_exp_dct_i));
2267 hr_utility.trace('p_sp_other_inc_exp_tmp_i : '||to_char(p_sp_other_inc_exp_tmp_i));
2268 hr_utility.trace('p_sp_other_inc_exp_tmp_exp_i : '||to_char(p_sp_other_inc_exp_tmp_exp_i));
2269 hr_utility.trace('p_sp_inc_cnt_i : '||to_char(p_sp_inc_cnt_i));
2270 hr_utility.trace('p_ent_spouse_inc_i : '||to_char(p_ent_spouse_inc_i));
2271 end if;
2272 --
2273 p_calc_spouse_inc_rec.sp_earned_inc_calc := 0;
2274 p_calc_spouse_inc_rec.sp_business_inc_calc := 0;
2275 p_calc_spouse_inc_rec.sp_miscellaneous_inc_calc := 0;
2276 p_calc_spouse_inc_rec.sp_dividend_inc_calc := 0;
2277 p_calc_spouse_inc_rec.sp_real_estate_inc_calc := 0;
2278 p_calc_spouse_inc_rec.sp_retirement_inc_calc := 0;
2279 p_calc_spouse_inc_rec.sp_other_inc_calc := 0;
2280 p_calc_spouse_inc_rec.sp_inc_calc := 0;
2281 --
2282 -- formula datetrack
2283 if (p_effective_date >= hr_api.g_sot
2284 and p_effective_date <= hr_api.g_eot) then
2285 --
2286 p_calc_spouse_inc_rec.sp_earned_inc_calc := p_sp_earned_inc_i - p_sp_earned_inc_exp_i;
2287 if p_calc_spouse_inc_rec.sp_earned_inc_calc < 0 then
2288 p_calc_spouse_inc_rec.sp_earned_inc_calc := 0;
2289 end if;
2290 --
2291 p_calc_spouse_inc_rec.sp_business_inc_calc := p_sp_business_inc_i - p_sp_business_inc_exp_i;
2292 -- basically this case is not happened.
2293 if p_calc_spouse_inc_rec.sp_business_inc_calc < 0 then
2294 p_calc_spouse_inc_rec.sp_business_inc_calc := 0;
2295 end if;
2296 --
2297 p_calc_spouse_inc_rec.sp_miscellaneous_inc_calc := p_sp_miscellaneous_inc_i - p_sp_miscellaneous_inc_exp_i;
2298 -- basically this case is not happened.
2299 if p_calc_spouse_inc_rec.sp_miscellaneous_inc_calc < 0 then
2300 p_calc_spouse_inc_rec.sp_miscellaneous_inc_calc := 0;
2301 end if;
2302 --
2303 p_calc_spouse_inc_rec.sp_dividend_inc_calc := p_sp_dividend_inc_i - p_sp_dividend_inc_exp_i;
2304 -- basically this case is not happened.
2305 if p_calc_spouse_inc_rec.sp_dividend_inc_calc < 0 then
2306 p_calc_spouse_inc_rec.sp_dividend_inc_calc := 0;
2307 end if;
2308 --
2309 p_calc_spouse_inc_rec.sp_real_estate_inc_calc := p_sp_real_estate_inc_i - p_sp_real_estate_inc_exp_i;
2310 -- basically this case is not happened.
2311 if p_calc_spouse_inc_rec.sp_real_estate_inc_calc < 0 then
2312 p_calc_spouse_inc_rec.sp_real_estate_inc_calc := 0;
2313 end if;
2314 --
2315 p_calc_spouse_inc_rec.sp_retirement_inc_calc := trunc((p_sp_retirement_inc_i - p_sp_retirement_inc_exp_i) / 2);
2316 -- basically this case is not happened.
2317 if p_calc_spouse_inc_rec.sp_retirement_inc_calc < 0 then
2318 p_calc_spouse_inc_rec.sp_retirement_inc_calc := 0;
2319 end if;
2320 --
2321 -- currently sp_other_inc_exp_tmp_i and sp_other_inc_exp_tmp_exp is not supported on FormPG.
2322 if (p_sp_other_inc_exp_tmp_i - p_sp_other_inc_exp_tmp_exp_i) / 2 < 0 then
2323 p_calc_spouse_inc_rec.sp_other_inc_calc := p_sp_other_inc_i - p_sp_other_inc_exp_i;
2324 else
2325 if p_sp_other_inc_i - p_sp_other_inc_exp_tmp_i < 0 then
2326 p_calc_spouse_inc_rec.sp_other_inc_calc := 0;
2327 else
2328 if p_sp_other_inc_exp_i - p_sp_other_inc_exp_tmp_exp_i < 0 then
2329 p_calc_spouse_inc_rec.sp_other_inc_calc := 0;
2330 else
2331 p_calc_spouse_inc_rec.sp_other_inc_calc := ((p_sp_other_inc_i - p_sp_other_inc_exp_tmp_i)
2332 - (p_sp_other_inc_exp_i - p_sp_other_inc_exp_tmp_exp_i))
2333 + trunc((p_sp_other_inc_exp_tmp_i - p_sp_other_inc_exp_tmp_exp_i) / 2);
2334 end if;
2335 end if;
2336 end if;
2337 if p_calc_spouse_inc_rec.sp_other_inc_calc < 0 then
2338 p_calc_spouse_inc_rec.sp_other_inc_calc := 0;
2339 end if;
2340 --
2341 p_calc_spouse_inc_rec.sp_inc_calc := p_calc_spouse_inc_rec.sp_earned_inc_calc
2342 + p_calc_spouse_inc_rec.sp_business_inc_calc
2343 + p_calc_spouse_inc_rec.sp_miscellaneous_inc_calc
2344 + p_calc_spouse_inc_rec.sp_dividend_inc_calc
2345 + p_calc_spouse_inc_rec.sp_real_estate_inc_calc
2346 + p_calc_spouse_inc_rec.sp_retirement_inc_calc
2347 + p_calc_spouse_inc_rec.sp_other_inc_calc;
2348 --
2349 end if;
2350 --
2351 -- basically use the calculated spouse inc, but use entry spouse inc if no record of sp_inc
2352 if p_sp_inc_cnt_i > 0 then
2353 --
2354 o_spouse_inc := p_calc_spouse_inc_rec.sp_inc_calc;
2355 --
2356 else
2357 --
2358 o_spouse_inc := p_ent_spouse_inc_i;
2359 --
2360 end if;
2361 --
2362 if g_debug then
2363 hr_utility.trace('p_calc_spouse_inc_rec.sp_earned_inc_calc : '||to_char(p_calc_spouse_inc_rec.sp_earned_inc_calc));
2364 hr_utility.trace('p_calc_spouse_inc_rec.sp_business_inc_calc : '||to_char(p_calc_spouse_inc_rec.sp_business_inc_calc));
2365 hr_utility.trace('p_calc_spouse_inc_rec.sp_miscellaneous_inc_calc : '||to_char(p_calc_spouse_inc_rec.sp_miscellaneous_inc_calc));
2366 hr_utility.trace('p_calc_spouse_inc_rec.sp_dividend_inc_calc : '||to_char(p_calc_spouse_inc_rec.sp_dividend_inc_calc));
2367 hr_utility.trace('p_calc_spouse_inc_rec.sp_real_estate_inc_calc : '||to_char(p_calc_spouse_inc_rec.sp_real_estate_inc_calc));
2368 hr_utility.trace('p_calc_spouse_inc_rec.sp_retirement_inc_calc : '||to_char(p_calc_spouse_inc_rec.sp_retirement_inc_calc));
2369 hr_utility.trace('p_calc_spouse_inc_rec.sp_other_inc_calc : '||to_char(p_calc_spouse_inc_rec.sp_other_inc_calc));
2370 hr_utility.trace('p_sp_inc_cnt_i : '||to_char(p_sp_inc_cnt_i));
2371 hr_utility.trace('p_calc_spouse_inc_rec.sp_inc_calc : '||to_char(p_calc_spouse_inc_rec.sp_inc_calc));
2372 hr_utility.trace('p_ent_spouse_inc_i : '||to_char(p_ent_spouse_inc_i));
2373 hr_utility.trace('o_spouse_inc : '||to_char(o_spouse_inc));
2374 hr_utility.set_location(l_proc,1000);
2375 end if;
2376 --
2377 p_spouse_inc_o := o_spouse_inc;
2378 --
2379 end calc_spouse_inc;
2380 --
2381 -- -------------------------------------------------------------------------
2382 -- calc_dct
2383 -- -------------------------------------------------------------------------
2384 procedure calc_dct(
2385 p_assignment_action_id in number,
2386 p_calc_dct_rec out nocopy t_calc_dct_rec)
2387 is
2388 --
2389 l_proc varchar2(80) := c_package||'calc_ins';
2390 l_calc_total_rec t_calc_total_rec;
2391 l_calc_spouse_inc_rec t_calc_spouse_inc_rec;
2392 --
2393 l_payroll_action_id number;
2394 l_business_group_id number;
2395 l_effective_date date;
2396 --
2397 cursor csr_pact
2398 is
2399 select /* +ORDERED */
2400 ppa.payroll_action_id,
2401 ppa.business_group_id,
2402 ppa.effective_date
2403 from pay_assignment_actions paa,
2404 pay_payroll_actions ppa
2405 where paa.assignment_action_id = p_assignment_action_id
2406 and ppa.payroll_action_id = paa.payroll_action_id;
2407 --
2408 begin
2409 --
2410 if g_debug then
2411 hr_utility.set_location(l_proc,0);
2412 end if;
2413 --
2414 calc_total(
2415 p_assignment_action_id => p_assignment_action_id,
2416 p_calc_total_rec => l_calc_total_rec);
2417 --
2418 p_calc_dct_rec.life_gen_ins_prem := l_calc_total_rec.life_gen;
2419 p_calc_dct_rec.life_pens_ins_prem := l_calc_total_rec.life_pens;
2420 p_calc_dct_rec.earthquake_ins_prem := l_calc_total_rec.earthquake;
2421 p_calc_dct_rec.nonlife_long_ins_prem := l_calc_total_rec.nonlife_long;
2422 p_calc_dct_rec.nonlife_short_ins_prem := l_calc_total_rec.nonlife_short;
2423 p_calc_dct_rec.national_pens_ins_prem := l_calc_total_rec.national_pens;
2424 p_calc_dct_rec.social_ins_prem := l_calc_total_rec.social;
2425 --
2426 open csr_pact;
2427 fetch csr_pact into l_payroll_action_id, l_business_group_id, l_effective_date;
2428 close csr_pact;
2429 --
2430 calc_spouse_inc(
2431 p_sp_earned_inc_i => l_calc_total_rec.sp_earned_inc,
2432 p_sp_earned_inc_exp_i => l_calc_total_rec.sp_earned_inc_exp,
2433 p_sp_business_inc_i => l_calc_total_rec.sp_business_inc,
2434 p_sp_business_inc_exp_i => l_calc_total_rec.sp_business_inc_exp,
2435 p_sp_miscellaneous_inc_i => l_calc_total_rec.sp_miscellaneous_inc,
2436 p_sp_miscellaneous_inc_exp_i => l_calc_total_rec.sp_miscellaneous_inc_exp,
2437 p_sp_dividend_inc_i => l_calc_total_rec.sp_dividend_inc,
2438 p_sp_dividend_inc_exp_i => l_calc_total_rec.sp_dividend_inc_exp,
2439 p_sp_real_estate_inc_i => l_calc_total_rec.sp_real_estate_inc,
2440 p_sp_real_estate_inc_exp_i => l_calc_total_rec.sp_real_estate_inc_exp,
2441 p_sp_retirement_inc_i => l_calc_total_rec.sp_retirement_inc,
2442 p_sp_retirement_inc_exp_i => l_calc_total_rec.sp_retirement_inc_exp,
2443 p_sp_other_inc_i => l_calc_total_rec.sp_other_inc,
2444 p_sp_other_inc_exp_i => l_calc_total_rec.sp_other_inc_exp,
2445 p_sp_other_inc_exp_dct_i => l_calc_total_rec.sp_other_inc_exp_dct,
2446 p_sp_other_inc_exp_tmp_i => l_calc_total_rec.sp_other_inc_exp_tmp,
2447 p_sp_other_inc_exp_tmp_exp_i => l_calc_total_rec.sp_other_inc_exp_tmp_exp,
2448 p_business_group_id => l_business_group_id,
2449 p_effective_date => l_effective_date,
2450 p_sp_inc_cnt_i => l_calc_total_rec.sp_inc_cnt,
2451 p_ent_spouse_inc_i => l_calc_total_rec.sp_spouse_inc,
2452 p_calc_spouse_inc_rec => l_calc_spouse_inc_rec,
2453 p_spouse_inc_o => p_calc_dct_rec.spouse_inc);
2454 --
2455 p_calc_dct_rec.sp_earned_inc_calc := l_calc_spouse_inc_rec.sp_earned_inc_calc;
2456 p_calc_dct_rec.sp_business_inc_calc := l_calc_spouse_inc_rec.sp_business_inc_calc;
2457 p_calc_dct_rec.sp_miscellaneous_inc_calc := l_calc_spouse_inc_rec.sp_miscellaneous_inc_calc;
2458 p_calc_dct_rec.sp_dividend_inc_calc := l_calc_spouse_inc_rec.sp_dividend_inc_calc;
2459 p_calc_dct_rec.sp_real_estate_inc_calc := l_calc_spouse_inc_rec.sp_real_estate_inc_calc;
2460 p_calc_dct_rec.sp_retirement_inc_calc := l_calc_spouse_inc_rec.sp_retirement_inc_calc;
2461 p_calc_dct_rec.sp_other_inc_calc := l_calc_spouse_inc_rec.sp_other_inc_calc;
2462 p_calc_dct_rec.sp_inc_calc := l_calc_spouse_inc_rec.sp_inc_calc;
2463 --
2464 calc_life_ins_dct(
2465 p_life_gen_i => l_calc_total_rec.life_gen,
2466 p_life_pens_i => l_calc_total_rec.life_pens,
2467 p_business_group_id => l_business_group_id,
2468 p_effective_date => l_effective_date,
2469 p_life_ins_dct_o => p_calc_dct_rec.life_ins_deduction,
2470 p_life_gen_o => p_calc_dct_rec.life_gen_ins_calc_prem,
2471 p_life_pens_o => p_calc_dct_rec.life_pens_ins_calc_prem);
2472 --
2473 calc_nonlife_dct(
2474 p_earthquake_i => l_calc_total_rec.earthquake,
2475 p_nonlife_long_i => l_calc_total_rec.nonlife_long,
2476 p_nonlife_short_i => l_calc_total_rec.nonlife_short,
2477 p_business_group_id => l_business_group_id,
2478 p_effective_date => l_effective_date,
2479 p_nonlife_dct_o => p_calc_dct_rec.nonlife_ins_deduction,
2480 p_earthquake_o => p_calc_dct_rec.earthquake_ins_calc_prem,
2481 p_nonlife_long_o => p_calc_dct_rec.nonlife_long_ins_calc_prem,
2482 p_nonlife_short_o => p_calc_dct_rec.nonlife_short_ins_calc_prem);
2483 --
2484 calc_social_dct(
2485 p_social_i => l_calc_total_rec.social,
2486 p_business_group_id => l_business_group_id,
2487 p_effective_date => l_effective_date,
2488 p_social_dct_o => p_calc_dct_rec.social_ins_deduction);
2489 --
2490 calc_mutual_aid_dct(
2491 p_mutual_aid_ec_i => l_calc_total_rec.mutual_aid_ec,
2492 p_mutual_aid_p_i => l_calc_total_rec.mutual_aid_p,
2493 p_mutual_aid_dsc_i => l_calc_total_rec.mutual_aid_dsc,
2494 p_business_group_id => l_business_group_id,
2495 p_effective_date => l_effective_date,
2496 p_mutual_aid_dct_o => p_calc_dct_rec.mutual_aid_deduction);
2497 --
2498 calc_spouse_dct(
2499 p_spouse_income_i => p_calc_dct_rec.spouse_inc,
2500 p_emp_income_i => l_calc_total_rec.sp_emp_inc,
2501 p_sp_type_i => l_calc_total_rec.sp_sp_type,
2502 p_wid_type_i => l_calc_total_rec.sp_wid_type,
2503 p_dct_exc_i => l_calc_total_rec.sp_dct_exc,
2504 p_business_group_id => l_business_group_id,
2505 p_effective_date => l_effective_date,
2506 p_spouse_dct_o => p_calc_dct_rec.spouse_deduction);
2507 --
2508 -- cache in case payroll_action_id is same.
2509 if g_payroll_action_id is null
2510 or (g_payroll_action_id <> l_payroll_action_id
2511 and l_payroll_action_id is not null) then
2512 --
2513 g_payroll_action_id := l_payroll_action_id;
2514 g_business_group_id := l_business_group_id;
2515 g_effective_date := l_effective_date;
2516 --
2517 end if;
2518 --
2519 if g_debug then
2520 hr_utility.trace('end update_assact');
2521 hr_utility.set_location(l_proc,1000);
2522 end if;
2523 --
2524 end calc_dct;
2525 --
2526 -- -------------------------------------------------------------------------
2527 -- do_calculate
2528 -- -------------------------------------------------------------------------
2529 procedure do_calculate(
2530 p_action_information_id in number,
2531 p_object_version_number in out nocopy number)
2532 is
2533 --
2534 l_proc varchar2(80) := c_package||'do_calculate';
2535 l_submission_date date;
2536 l_assact_rec pay_jp_isdf_assact_v%rowtype;
2537 l_calc_dct_rec t_calc_dct_rec;
2538 l_action_information_id number;
2539 l_object_version_number number;
2540 --
2541 cursor csr_calc_dct
2542 is
2543 select *
2544 from pay_jp_isdf_calc_dct_v
2545 where assignment_action_id = l_assact_rec.assignment_action_id
2546 and action_context_type = 'AAP'
2547 and action_information_category = 'JP_ISDF_CALC_DCT'
2548 and status <> 'D';
2549 --
2550 l_csr_calc_dct csr_calc_dct%rowtype;
2551 --
2552 begin
2553 --
2554 if g_debug then
2555 hr_utility.set_location(l_proc,0);
2556 end if;
2557 --
2558 l_submission_date := check_submission_period(p_action_information_id);
2559 --
2560 if g_debug then
2561 hr_utility.set_location(l_proc,10);
2562 hr_utility.trace('submission_date : '||fnd_date.date_to_canonical(l_submission_date));
2563 end if;
2564 --
2565 -- unnecessary to lock
2566 select *
2567 into l_assact_rec
2568 from pay_jp_isdf_assact_v
2569 where action_information_id = p_action_information_id;
2570 --
2571 if l_assact_rec.transaction_status <> 'N' then
2572 fnd_message.set_name('PAY','PAY_JP_DEF_INVALID_TXN_STATUS');
2573 fnd_message.raise_error;
2574 end if;
2575 --
2576 if g_debug then
2577 hr_utility.set_location(l_proc,20);
2578 hr_utility.trace('action_information_id : '||p_action_information_id);
2579 hr_utility.trace('object_version_number : '||p_object_version_number);
2580 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
2581 hr_utility.trace('start calc_ins');
2582 end if;
2583 --
2584 calc_dct(
2585 p_assignment_action_id => l_assact_rec.assignment_action_id,
2586 p_calc_dct_rec => l_calc_dct_rec);
2587 --
2588 open csr_calc_dct;
2589 fetch csr_calc_dct into l_csr_calc_dct;
2590 close csr_calc_dct;
2591 --
2592 if l_csr_calc_dct.action_information_id is null then
2593 --
2594 select pay_action_information_s.nextval
2595 into l_action_information_id
2596 from dual;
2597 --
2598 pay_jp_isdf_dml_pkg.create_calc_dct(
2599 p_action_information_id => l_action_information_id,
2600 p_assignment_action_id => l_assact_rec.assignment_action_id,
2601 p_action_context_type => 'AAP',
2602 p_assignment_id => l_assact_rec.assignment_id,
2603 p_effective_date => l_assact_rec.effective_date,
2604 p_action_information_category => 'JP_ISDF_CALC_DCT',
2605 p_status => 'I',
2606 p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
2607 p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
2608 p_life_gen_ins_calc_prem => l_calc_dct_rec.life_gen_ins_calc_prem,
2609 p_life_pens_ins_calc_prem => l_calc_dct_rec.life_pens_ins_calc_prem,
2610 p_life_ins_deduction => l_calc_dct_rec.life_ins_deduction,
2611 p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
2612 p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
2613 p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
2614 p_nonlife_long_ins_calc_prem => l_calc_dct_rec.nonlife_long_ins_calc_prem,
2615 p_nonlife_short_ins_calc_prem => l_calc_dct_rec.nonlife_short_ins_calc_prem,
2616 p_earthquake_ins_calc_prem => l_calc_dct_rec.earthquake_ins_calc_prem,
2617 p_nonlife_ins_deduction => l_calc_dct_rec.nonlife_ins_deduction,
2618 p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
2619 p_social_ins_deduction => l_calc_dct_rec.social_ins_deduction,
2620 p_mutual_aid_deduction => l_calc_dct_rec.mutual_aid_deduction,
2621 p_sp_earned_income_calc => l_calc_dct_rec.sp_earned_inc_calc,
2622 p_sp_business_income_calc => l_calc_dct_rec.sp_business_inc_calc,
2623 p_sp_miscellaneous_income_calc => l_calc_dct_rec.sp_miscellaneous_inc_calc,
2624 p_sp_dividend_income_calc => l_calc_dct_rec.sp_dividend_inc_calc,
2625 p_sp_real_estate_income_calc => l_calc_dct_rec.sp_real_estate_inc_calc,
2626 p_sp_retirement_income_calc => l_calc_dct_rec.sp_retirement_inc_calc,
2627 p_sp_other_income_calc => l_calc_dct_rec.sp_other_inc_calc,
2628 p_sp_income_calc => l_calc_dct_rec.sp_inc_calc,
2629 p_spouse_income => l_calc_dct_rec.spouse_inc,
2630 p_spouse_deduction => l_calc_dct_rec.spouse_deduction,
2631 p_object_version_number => l_object_version_number);
2632 --
2633 else
2634 --
2635 l_action_information_id := l_csr_calc_dct.action_information_id;
2636 l_object_version_number := l_csr_calc_dct.object_version_number;
2637 --
2638 -- calc_dct is always insert mode because no initial archive data.
2639 pay_jp_isdf_dml_pkg.update_calc_dct(
2640 p_action_information_id => l_action_information_id,
2641 p_object_version_number => l_object_version_number,
2642 p_status => 'I',
2643 p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
2644 p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
2645 p_life_gen_ins_calc_prem => l_calc_dct_rec.life_gen_ins_calc_prem,
2646 p_life_pens_ins_calc_prem => l_calc_dct_rec.life_pens_ins_calc_prem,
2647 p_life_ins_deduction => l_calc_dct_rec.life_ins_deduction,
2648 p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
2649 p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
2650 p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
2651 p_nonlife_long_ins_calc_prem => l_calc_dct_rec.nonlife_long_ins_calc_prem,
2652 p_nonlife_short_ins_calc_prem => l_calc_dct_rec.nonlife_short_ins_calc_prem,
2653 p_earthquake_ins_calc_prem => l_calc_dct_rec.earthquake_ins_calc_prem,
2654 p_nonlife_ins_deduction => l_calc_dct_rec.nonlife_ins_deduction,
2655 p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
2656 p_social_ins_deduction => l_calc_dct_rec.social_ins_deduction,
2657 p_mutual_aid_deduction => l_calc_dct_rec.mutual_aid_deduction,
2658 p_sp_earned_income_calc => l_calc_dct_rec.sp_earned_inc_calc,
2659 p_sp_business_income_calc => l_calc_dct_rec.sp_business_inc_calc,
2660 p_sp_miscellaneous_income_calc => l_calc_dct_rec.sp_miscellaneous_inc_calc,
2661 p_sp_dividend_income_calc => l_calc_dct_rec.sp_dividend_inc_calc,
2662 p_sp_real_estate_income_calc => l_calc_dct_rec.sp_real_estate_inc_calc,
2663 p_sp_retirement_income_calc => l_calc_dct_rec.sp_retirement_inc_calc,
2664 p_sp_other_income_calc => l_calc_dct_rec.sp_other_inc_calc,
2665 p_sp_income_calc => l_calc_dct_rec.sp_inc_calc,
2666 p_spouse_income => l_calc_dct_rec.spouse_inc,
2667 p_spouse_deduction => l_calc_dct_rec.spouse_deduction);
2668 --
2669 end if;
2670 --
2671 if g_debug then
2672 hr_utility.trace('end update_assact');
2673 hr_utility.set_location(l_proc,1000);
2674 end if;
2675 --
2676 end do_calculate;
2677 --
2678 -- -------------------------------------------------------------------------
2679 -- do_finalize
2680 -- -------------------------------------------------------------------------
2681 procedure do_finalize(
2682 p_action_information_id in number,
2683 p_object_version_number in out nocopy number,
2684 p_user_comments in varchar2)
2685 is
2686 --
2687 l_proc varchar2(80) := c_package||'do_finalize';
2688 l_submission_date date;
2689 l_assact_rec pay_jp_isdf_assact_v%rowtype;
2690 l_calc_dct_rec t_calc_dct_rec;
2691 l_action_information_id number;
2692 l_object_version_number number;
2693 --
2694 cursor csr_calc_dct
2695 is
2696 select *
2697 from pay_jp_isdf_calc_dct_v
2698 where assignment_action_id = l_assact_rec.assignment_action_id
2699 and action_context_type = 'AAP'
2700 and action_information_category = 'JP_ISDF_CALC_DCT'
2701 and status <> 'D';
2702 --
2703 l_csr_calc_dct csr_calc_dct%rowtype;
2704 --
2705 cursor csr_entry
2706 is
2707 select *
2708 from pay_jp_isdf_entry_v
2709 where assignment_action_id = l_assact_rec.assignment_action_id
2710 and action_context_type = 'AAP'
2711 and action_information_category = 'JP_ISDF_ENTRY'
2712 and status <> 'D';
2713 --
2714 l_csr_entry csr_entry%rowtype;
2715 --
2716 begin
2717 --
2718 if g_debug then
2719 hr_utility.set_location(l_proc,0);
2720 end if;
2721 --
2722 l_submission_date := check_submission_period(p_action_information_id);
2723 --
2724 if g_debug then
2725 hr_utility.set_location(l_proc,10);
2726 hr_utility.trace('submission_date : '||fnd_date.date_to_canonical(l_submission_date));
2727 end if;
2728 --
2729 pay_jp_isdf_dml_pkg.lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
2730 --
2731 if l_assact_rec.transaction_status <> 'N' then
2732 fnd_message.set_name('PAY','PAY_JP_DEF_INVALID_TXN_STATUS');
2733 fnd_message.raise_error;
2734 end if;
2735 --
2736 if g_debug then
2737 hr_utility.set_location(l_proc,20);
2738 hr_utility.trace('action_information_id : '||p_action_information_id);
2739 hr_utility.trace('object_version_number : '||p_object_version_number);
2740 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
2741 hr_utility.trace('start calc_dct before finalize');
2742 end if;
2743 --
2744 calc_dct(
2745 p_assignment_action_id => l_assact_rec.assignment_action_id,
2746 p_calc_dct_rec => l_calc_dct_rec);
2747 --
2748 open csr_calc_dct;
2749 fetch csr_calc_dct into l_csr_calc_dct;
2750 close csr_calc_dct;
2751 --
2752 if l_csr_calc_dct.action_information_id is null then
2753 --
2754 select pay_action_information_s.nextval
2755 into l_action_information_id
2756 from dual;
2757 --
2758 pay_jp_isdf_dml_pkg.create_calc_dct(
2759 p_action_information_id => l_action_information_id,
2760 p_assignment_action_id => l_assact_rec.assignment_action_id,
2761 p_action_context_type => 'AAP',
2762 p_assignment_id => l_assact_rec.assignment_id,
2763 p_effective_date => l_assact_rec.effective_date,
2764 p_action_information_category => 'JP_ISDF_CALC_DCT',
2765 p_status => 'I',
2766 p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
2767 p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
2768 p_life_gen_ins_calc_prem => l_calc_dct_rec.life_gen_ins_calc_prem,
2769 p_life_pens_ins_calc_prem => l_calc_dct_rec.life_pens_ins_calc_prem,
2770 p_life_ins_deduction => l_calc_dct_rec.life_ins_deduction,
2771 p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
2772 p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
2773 p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
2774 p_nonlife_long_ins_calc_prem => l_calc_dct_rec.nonlife_long_ins_calc_prem,
2775 p_nonlife_short_ins_calc_prem => l_calc_dct_rec.nonlife_short_ins_calc_prem,
2776 p_earthquake_ins_calc_prem => l_calc_dct_rec.earthquake_ins_calc_prem,
2777 p_nonlife_ins_deduction => l_calc_dct_rec.nonlife_ins_deduction,
2778 p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
2779 p_social_ins_deduction => l_calc_dct_rec.social_ins_deduction,
2780 p_mutual_aid_deduction => l_calc_dct_rec.mutual_aid_deduction,
2781 p_sp_earned_income_calc => l_calc_dct_rec.sp_earned_inc_calc,
2782 p_sp_business_income_calc => l_calc_dct_rec.sp_business_inc_calc,
2783 p_sp_miscellaneous_income_calc => l_calc_dct_rec.sp_miscellaneous_inc_calc,
2784 p_sp_dividend_income_calc => l_calc_dct_rec.sp_dividend_inc_calc,
2785 p_sp_real_estate_income_calc => l_calc_dct_rec.sp_real_estate_inc_calc,
2786 p_sp_retirement_income_calc => l_calc_dct_rec.sp_retirement_inc_calc,
2787 p_sp_other_income_calc => l_calc_dct_rec.sp_other_inc_calc,
2788 p_sp_income_calc => l_calc_dct_rec.sp_inc_calc,
2789 p_spouse_income => l_calc_dct_rec.spouse_inc,
2790 p_spouse_deduction => l_calc_dct_rec.spouse_deduction,
2791 p_object_version_number => l_object_version_number);
2792 --
2793 else
2794 --
2795 l_action_information_id := l_csr_calc_dct.action_information_id;
2796 l_object_version_number := l_csr_calc_dct.object_version_number;
2797 --
2798 -- calc_dct is always insert mode because no initial archive data.
2799 pay_jp_isdf_dml_pkg.update_calc_dct(
2800 p_action_information_id => l_action_information_id,
2801 p_object_version_number => l_object_version_number,
2802 p_status => 'I',
2803 p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
2804 p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
2805 p_life_gen_ins_calc_prem => l_calc_dct_rec.life_gen_ins_calc_prem,
2806 p_life_pens_ins_calc_prem => l_calc_dct_rec.life_pens_ins_calc_prem,
2807 p_life_ins_deduction => l_calc_dct_rec.life_ins_deduction,
2808 p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
2809 p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
2810 p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
2811 p_nonlife_long_ins_calc_prem => l_calc_dct_rec.nonlife_long_ins_calc_prem,
2812 p_nonlife_short_ins_calc_prem => l_calc_dct_rec.nonlife_short_ins_calc_prem,
2813 p_earthquake_ins_calc_prem => l_calc_dct_rec.earthquake_ins_calc_prem,
2814 p_nonlife_ins_deduction => l_calc_dct_rec.nonlife_ins_deduction,
2815 p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
2816 p_social_ins_deduction => l_calc_dct_rec.social_ins_deduction,
2817 p_mutual_aid_deduction => l_calc_dct_rec.mutual_aid_deduction,
2818 p_sp_earned_income_calc => l_calc_dct_rec.sp_earned_inc_calc,
2819 p_sp_business_income_calc => l_calc_dct_rec.sp_business_inc_calc,
2820 p_sp_miscellaneous_income_calc => l_calc_dct_rec.sp_miscellaneous_inc_calc,
2821 p_sp_dividend_income_calc => l_calc_dct_rec.sp_dividend_inc_calc,
2822 p_sp_real_estate_income_calc => l_calc_dct_rec.sp_real_estate_inc_calc,
2823 p_sp_retirement_income_calc => l_calc_dct_rec.sp_retirement_inc_calc,
2824 p_sp_other_income_calc => l_calc_dct_rec.sp_other_inc_calc,
2825 p_sp_income_calc => l_calc_dct_rec.sp_inc_calc,
2826 p_spouse_income => l_calc_dct_rec.spouse_inc,
2827 p_spouse_deduction => l_calc_dct_rec.spouse_deduction);
2828 --
2829 end if;
2830 --
2831 -- Originally jp_isdf_entry should be made at the time of transfer
2832 -- because latest pre-set entry data in the transfer time is not same
2833 -- as the condition at the time of archive.
2834 -- However, finalize action is to fix all entry data except for _o prefex columns,
2835 -- so make jp_isdf_entry.
2836 --
2837 open csr_entry;
2838 fetch csr_entry into l_csr_entry;
2839 close csr_entry;
2840 --
2841 if l_csr_entry.action_information_id is null then
2842 --
2843 select pay_action_information_s.nextval
2844 into l_action_information_id
2845 from dual;
2846 --
2847 pay_jp_isdf_dml_pkg.create_entry(
2848 p_action_information_id => l_action_information_id,
2849 p_assignment_action_id => l_assact_rec.assignment_action_id,
2850 p_action_context_type => 'AAP',
2851 p_assignment_id => l_assact_rec.assignment_id,
2852 p_effective_date => l_assact_rec.effective_date,
2853 p_action_information_category => 'JP_ISDF_ENTRY',
2854 p_status => 'I',
2855 p_ins_datetrack_update_mode => null,
2856 p_ins_element_entry_id => null,
2857 p_ins_ee_object_version_number => null,
2858 p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
2859 p_life_gen_ins_prem_o => null,
2860 p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
2861 p_life_pens_ins_prem_o => null,
2862 p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
2863 p_nonlife_long_ins_prem_o => null,
2864 p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
2865 p_nonlife_short_ins_prem_o => null,
2866 p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
2867 p_earthquake_ins_prem_o => null,
2868 p_is_datetrack_update_mode => null,
2869 p_is_element_entry_id => null,
2870 p_is_ee_object_version_number => null,
2871 p_social_ins_prem => l_calc_dct_rec.social_ins_deduction,
2872 p_social_ins_prem_o => null,
2873 p_mutual_aid_prem => l_calc_dct_rec.mutual_aid_deduction,
2874 p_mutual_aid_prem_o => null,
2875 p_spouse_income => l_calc_dct_rec.spouse_inc,
2876 p_spouse_income_o => null,
2877 p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
2878 p_national_pens_ins_prem_o => null,
2879 p_object_version_number => l_object_version_number);
2880 --
2881 else
2882 --
2883 l_action_information_id := l_csr_entry.action_information_id;
2884 l_object_version_number := l_csr_entry.object_version_number;
2885 --
2886 -- if entry data was extracted from entry at the initial archive time,
2887 -- entry data has been set, otherwise, once the finalized entry data
2888 -- is changed to return status, then the data is finalized again in second time,
2889 -- it is not queried data and newly inserted in previous finalize time.
2890 -- so that the element_entry is not set, it means the previous finalized data.
2891 -- it can be overriden.
2892 --
2893 if l_csr_entry.ins_element_entry_id is not null
2894 or l_csr_entry.is_element_entry_id is not null then
2895 --
2896 pay_jp_isdf_dml_pkg.update_entry(
2897 p_action_information_id => l_action_information_id,
2898 p_object_version_number => l_object_version_number,
2899 p_status => 'Q',
2900 p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
2901 p_life_gen_ins_prem_o => l_csr_entry.life_gen_ins_prem,
2902 p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
2903 p_life_pens_ins_prem_o => l_csr_entry.life_pens_ins_prem,
2904 p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
2905 p_nonlife_long_ins_prem_o => l_csr_entry.nonlife_long_ins_prem,
2906 p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
2907 p_nonlife_short_ins_prem_o => l_csr_entry.nonlife_short_ins_prem,
2908 p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
2909 p_earthquake_ins_prem_o => l_csr_entry.earthquake_ins_prem,
2910 p_social_ins_prem => l_calc_dct_rec.social_ins_deduction,
2911 p_social_ins_prem_o => l_csr_entry.social_ins_prem,
2912 p_mutual_aid_prem => l_calc_dct_rec.mutual_aid_deduction,
2913 p_mutual_aid_prem_o => l_csr_entry.mutual_aid_prem,
2914 p_spouse_income => l_calc_dct_rec.spouse_inc,
2915 p_spouse_income_o => l_csr_entry.spouse_income,
2916 p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
2917 p_national_pens_ins_prem_o => l_csr_entry.national_pens_ins_prem);
2918 --
2919 else
2920 --
2921 pay_jp_isdf_dml_pkg.update_entry(
2922 p_action_information_id => l_action_information_id,
2923 p_object_version_number => l_object_version_number,
2924 p_status => 'I',
2925 p_life_gen_ins_prem => l_calc_dct_rec.life_gen_ins_prem,
2926 p_life_gen_ins_prem_o => null,
2927 p_life_pens_ins_prem => l_calc_dct_rec.life_pens_ins_prem,
2928 p_life_pens_ins_prem_o => null,
2929 p_nonlife_long_ins_prem => l_calc_dct_rec.nonlife_long_ins_prem,
2930 p_nonlife_long_ins_prem_o => null,
2931 p_nonlife_short_ins_prem => l_calc_dct_rec.nonlife_short_ins_prem,
2932 p_nonlife_short_ins_prem_o => null,
2933 p_earthquake_ins_prem => l_calc_dct_rec.earthquake_ins_prem,
2934 p_earthquake_ins_prem_o => null,
2935 p_social_ins_prem => l_calc_dct_rec.social_ins_deduction,
2936 p_social_ins_prem_o => null,
2937 p_mutual_aid_prem => l_calc_dct_rec.mutual_aid_deduction,
2938 p_mutual_aid_prem_o => null,
2939 p_spouse_income => l_calc_dct_rec.spouse_inc,
2940 p_spouse_income_o => null,
2941 p_national_pens_ins_prem => l_calc_dct_rec.national_pens_ins_prem,
2942 p_national_pens_ins_prem_o => null);
2943 --
2944 end if;
2945 --
2946 end if;
2947 --
2948 if g_debug then
2949 hr_utility.set_location(l_proc,20);
2950 hr_utility.trace('end calc_dct before finalize');
2951 end if;
2952 --
2953 if g_debug then
2954 hr_utility.set_location(l_proc,20);
2955 hr_utility.trace('action_information_id : '||p_action_information_id);
2956 hr_utility.trace('object_version_number : '||p_object_version_number);
2957 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
2958 hr_utility.trace('start update_assact');
2959 end if;
2960 --
2961 p_object_version_number := l_assact_rec.object_version_number + 1;
2962 --
2963 --api is disable because assact has been locked.
2964 --pay_jp_isdf_dml_pkg.update_assact(
2965 -- p_action_information_id => l_assact_rec.assignment_action_id,
2966 -- p_object_version_number => p_object_version_number,
2967 -- p_transaction_status => 'F',
2968 -- p_finalized_date => fnd_date.date_to_canonical(l_submission_date),
2969 -- p_finalized_by => fnd_number.number_to_canonical(fnd_global.user_id),
2970 -- p_user_comments => p_user_comments,
2971 -- p_admin_comments => l_assact_rec.admin_comments,
2972 -- p_transfer_status => l_assact_rec.transfer_status,
2973 -- p_transfer_date => l_assact_rec.transfer_date,
2974 -- p_expiry_date => l_assact_rec.expiry_date);
2975 update pay_jp_isdf_assact_dml_v
2976 set object_version_number = p_object_version_number,
2977 transaction_status = 'F',
2978 finalized_date = fnd_date.date_to_canonical(l_submission_date),
2979 finalized_by = fnd_number.number_to_canonical(fnd_global.user_id),
2980 user_comments = p_user_comments
2981 where row_id = l_assact_rec.row_id;
2982 --
2983 if g_debug then
2984 hr_utility.trace('end update_assact');
2985 hr_utility.set_location(l_proc,1000);
2986 end if;
2987 --
2988 end do_finalize;
2989 --
2990 -- -------------------------------------------------------------------------
2991 -- do_reject
2992 -- -------------------------------------------------------------------------
2993 procedure do_reject(
2994 p_action_information_id in number,
2995 p_object_version_number in out nocopy number,
2996 p_admin_comments in varchar2)
2997 is
2998 --
2999 l_proc varchar2(80) := c_package||'do_finalize';
3000 l_submission_date date;
3001 l_assact_rec pay_jp_isdf_assact_v%rowtype;
3002 --
3003 begin
3004 --
3005 if g_debug then
3006 hr_utility.set_location(l_proc,0);
3007 end if;
3008 --
3009 l_submission_date := check_submission_period(p_action_information_id);
3010 --
3011 if g_debug then
3012 hr_utility.set_location(l_proc,10);
3013 hr_utility.trace('submission_date : '||fnd_date.date_to_canonical(l_submission_date));
3014 end if;
3015 --
3016 pay_jp_isdf_dml_pkg.lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
3017 --
3018 if l_assact_rec.transaction_status not in ('F', 'A') then
3019 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
3020 fnd_message.raise_error;
3021 elsif l_assact_rec.transfer_status <> 'U' then
3022 fnd_message.set_name('PAY','PAY_JP_DEF_ALREADY_TRANSFERRED');
3023 fnd_message.raise_error;
3024 end if;
3025 --
3026 if g_debug then
3027 hr_utility.set_location(l_proc,20);
3028 hr_utility.trace('action_information_id : '||p_action_information_id);
3029 hr_utility.trace('object_version_number : '||p_object_version_number);
3030 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
3031 hr_utility.trace('start calc_dct before finalize');
3032 end if;
3033 --
3034 delete
3035 from pay_action_information
3036 where action_context_id = l_assact_rec.assignment_action_id
3037 and action_context_type = 'AAP'
3038 and action_information_category <> 'JP_ISDF_ASSACT';
3039 --
3040 if g_debug then
3041 hr_utility.set_location(l_proc,20);
3042 hr_utility.trace('action_information_id : '||p_action_information_id);
3043 hr_utility.trace('object_version_number : '||p_object_version_number);
3044 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
3045 hr_utility.trace('start update_assact');
3046 end if;
3047 --
3048 p_object_version_number := l_assact_rec.object_version_number + 1;
3049 --
3050 --api is disable because assact has been locked.
3051 --pay_jp_isdf_dml_pkg.update_assact(
3052 -- p_action_information_id => l_assact_rec.assignment_action_id,
3053 -- p_object_version_number => p_object_version_number,
3054 -- p_transaction_status => 'U',
3055 -- p_finalized_date => null,
3056 -- p_finalized_by => null,
3057 -- p_user_comments => l_assact_rec.user_comments,
3058 -- p_admin_comments => p_admin_comments,
3059 -- p_transfer_status => l_assact_rec.transfer_status,
3060 -- p_transfer_date => l_assact_rec.transfer_date,
3061 -- p_expiry_date => l_assact_rec.expiry_date);
3062 update pay_jp_isdf_assact_dml_v
3063 set object_version_number = p_object_version_number,
3064 transaction_status = 'U',
3065 finalized_date = null,
3066 finalized_by = null,
3067 admin_comments = p_admin_comments
3068 where row_id = l_assact_rec.row_id;
3069 --
3070 if g_debug then
3071 hr_utility.trace('end update_assact');
3072 hr_utility.set_location(l_proc,1000);
3073 end if;
3074 --
3075 end do_reject;
3076 --
3077 -- -------------------------------------------------------------------------
3078 -- do_return
3079 -- -------------------------------------------------------------------------
3080 procedure do_return(
3081 p_action_information_id in number,
3082 p_object_version_number in out nocopy number,
3083 p_admin_comments in varchar2)
3084 is
3085 --
3086 l_proc varchar2(80) := c_package||'do_return';
3087 l_submission_date date;
3088 l_assact_rec pay_jp_isdf_assact_v%rowtype;
3089 --
3090 begin
3091 --
3092 if g_debug then
3093 hr_utility.set_location(l_proc,0);
3094 end if;
3095 --
3096 l_submission_date := check_submission_period(p_action_information_id);
3097 --
3098 if g_debug then
3099 hr_utility.set_location(l_proc,10);
3100 hr_utility.trace('submission_date : '||fnd_date.date_to_canonical(l_submission_date));
3101 end if;
3102 --
3103 pay_jp_isdf_dml_pkg.lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
3104 --
3105 if l_assact_rec.transaction_status not in ('F', 'A') then
3106 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
3107 fnd_message.raise_error;
3108 elsif l_assact_rec.transfer_status <> 'U' then
3109 fnd_message.set_name('PAY','PAY_JP_DEF_ALREADY_TRANSFERRED');
3110 fnd_message.raise_error;
3111 end if;
3112 --
3113 if g_debug then
3114 hr_utility.set_location(l_proc,20);
3115 hr_utility.trace('action_information_id : '||p_action_information_id);
3116 hr_utility.trace('object_version_number : '||p_object_version_number);
3117 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
3118 hr_utility.trace('start calc_dct before finalize');
3119 end if;
3120 --
3121 if g_debug then
3122 hr_utility.set_location(l_proc,20);
3123 hr_utility.trace('action_information_id : '||p_action_information_id);
3124 hr_utility.trace('object_version_number : '||p_object_version_number);
3125 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
3126 hr_utility.trace('start update_assact');
3127 end if;
3128 --
3129 p_object_version_number := l_assact_rec.object_version_number + 1;
3130 --
3131 --api is disable because assact has been locked.
3132 --pay_jp_isdf_dml_pkg.update_assact(
3133 -- p_action_information_id => l_assact_rec.assignment_action_id,
3134 -- p_object_version_number => p_object_version_number,
3135 -- p_transaction_status => 'N',
3136 -- p_finalized_date => null,
3137 -- p_finalized_by => null,
3138 -- p_user_comments => l_assact_rec.user_comments,
3139 -- p_admin_comments => p_admin_comments,
3140 -- p_transfer_status => l_assact_rec.transfer_status,
3141 -- p_transfer_date => l_assact_rec.transfer_date,
3142 -- p_expiry_date => l_assact_rec.expiry_date);
3143 update pay_jp_isdf_assact_dml_v
3144 set object_version_number = p_object_version_number,
3145 transaction_status = 'N',
3146 finalized_date = null,
3147 finalized_by = null,
3148 admin_comments = p_admin_comments
3149 where row_id = l_assact_rec.row_id;
3150 --
3151 if g_debug then
3152 hr_utility.trace('end update_assact');
3153 hr_utility.set_location(l_proc,1000);
3154 end if;
3155 --
3156 end do_return;
3157 --
3158 -- -------------------------------------------------------------------------
3159 -- do_approve
3160 -- -------------------------------------------------------------------------
3161 procedure do_approve(
3162 p_action_information_id in number,
3163 p_object_version_number in out nocopy number)
3164 is
3165 --
3166 l_proc varchar2(80) := c_package||'do_approve';
3167 l_assact_rec pay_jp_isdf_assact_v%rowtype;
3168 --
3169 begin
3170 --
3171 if g_debug then
3172 hr_utility.set_location(l_proc,0);
3173 end if;
3174 --
3175 pay_jp_isdf_dml_pkg.lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
3176 --
3177 if l_assact_rec.transaction_status <> 'F' then
3178 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
3179 fnd_message.raise_error;
3180 end if;
3181 --
3182 if g_debug then
3183 hr_utility.set_location(l_proc,20);
3184 hr_utility.trace('action_information_id : '||p_action_information_id);
3185 hr_utility.trace('object_version_number : '||p_object_version_number);
3186 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
3187 hr_utility.trace('start calc_dct before finalize');
3188 end if;
3189 --
3190 if g_debug then
3191 hr_utility.set_location(l_proc,20);
3192 hr_utility.trace('action_information_id : '||p_action_information_id);
3193 hr_utility.trace('object_version_number : '||p_object_version_number);
3194 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
3195 hr_utility.trace('start update_assact');
3196 end if;
3197 --
3198 p_object_version_number := l_assact_rec.object_version_number + 1;
3199 --
3200 --api is disable because assact has been locked.
3201 --pay_jp_isdf_dml_pkg.update_assact(
3202 -- p_action_information_id => l_assact_rec.assignment_action_id,
3203 -- p_object_version_number => p_object_version_number,
3204 -- p_transaction_status => 'A',
3205 -- p_finalized_date => l_assact_rec.finalized_date,
3206 -- p_finalized_by => l_assact_rec.finalized_by,
3207 -- p_user_comments => l_assact_rec.user_comments,
3208 -- p_admin_comments => l_assact_rec.admin_comments,
3209 -- p_transfer_status => l_assact_rec.transfer_status,
3210 -- p_transfer_date => l_assact_rec.transfer_date,
3211 -- p_expiry_date => l_assact_rec.expiry_date);
3212 update pay_jp_isdf_assact_dml_v
3213 set object_version_number = p_object_version_number,
3214 transaction_status = 'A'
3215 where row_id = l_assact_rec.row_id;
3216 --
3217 if g_debug then
3218 hr_utility.trace('end update_assact');
3219 hr_utility.set_location(l_proc,1000);
3220 end if;
3221 --
3222 end do_approve;
3223 --
3224 -- -------------------------------------------------------------------------
3225 -- insert_session
3226 -- -------------------------------------------------------------------------
3227 procedure insert_session(
3228 p_effective_date in date)
3229 is
3230 --
3231 l_rowid rowid;
3232 --
3233 cursor csr_session
3234 is
3235 select rowid
3236 from fnd_sessions
3237 where session_id = userenv('sessionid')
3238 for update nowait;
3239 --
3240 begin
3241 --
3242 open csr_session;
3243 fetch csr_session into l_rowid;
3244 --
3245 if csr_session%notfound then
3246 --
3247 insert into fnd_sessions(
3248 session_id,
3249 effective_date)
3250 values(
3251 userenv('sessionid'),
3252 p_effective_date);
3253 --
3254 else
3255 --
3256 update fnd_sessions
3257 set effective_date = p_effective_date
3258 where rowid = l_rowid;
3259 --
3260 end if;
3261 --
3262 close csr_session;
3263 --
3264 end insert_session;
3265 --
3266 -- -------------------------------------------------------------------------
3267 -- delete_session
3268 -- -------------------------------------------------------------------------
3269 procedure delete_session
3270 is
3271 begin
3272 --
3273 delete
3274 from fnd_sessions
3275 where session_id = userenv('sessionid');
3276 --
3277 end delete_session;
3278 --
3279 -- -------------------------------------------------------------------------
3280 -- changed
3281 -- -------------------------------------------------------------------------
3282 function changed(
3283 value1 in varchar2,
3284 value2 in varchar2)
3285 return boolean
3286 is
3287 begin
3288 --
3289 if nvl(value1, hr_api.g_varchar2) <> nvl(value2, hr_api.g_varchar2) then
3290 return true;
3291 else
3292 return false;
3293 end if;
3294 --
3295 end changed;
3296 --
3297 function changed(
3298 value1 in number,
3299 value2 in number)
3300 return boolean
3301 is
3302 begin
3303 --
3304 if nvl(value1, hr_api.g_number) <> nvl(value2, hr_api.g_number) then
3305 return true;
3306 else
3307 return false;
3308 end if;
3309 --
3310 end changed;
3311 --
3312 function changed(
3313 value1 in date,
3314 value2 in date)
3315 return boolean
3316 is
3317 begin
3318 --
3319 if nvl(value1, hr_api.g_date) <> nvl(value2, hr_api.g_date) then
3320 return true;
3321 else
3322 return false;
3323 end if;
3324 --
3325 end changed;
3326 --
3327 -- -------------------------------------------------------------------------
3328 -- transfer_entry
3329 -- -------------------------------------------------------------------------
3330 procedure transfer_entry(
3331 p_rec in out nocopy pay_jp_isdf_entry_v%rowtype,
3332 p_effective_date in date,
3333 p_expire_after_transfer in varchar2)
3334 is
3335 --
3336 l_proc varchar2(80) := c_package||'transfer_entry';
3337 --
3338 l_effective_date date;
3339 l_esd date;
3340 l_eed date;
3341 l_warning boolean;
3342 l_ins_element_link_id number;
3343 l_is_element_link_id number;
3344 --
3345 l_ins_element_entry_id number;
3346 l_ins_ee_object_version_number number;
3347 l_ins_datetrack_update_mode pay_jp_isdf_entry_v.ins_datetrack_update_mode%type;
3348 l_is_element_entry_id number;
3349 l_is_ee_object_version_number number;
3350 l_is_datetrack_update_mode pay_jp_isdf_entry_v.is_datetrack_update_mode%type;
3351 l_status pay_jp_isdf_entry_v.status%type;
3352 --
3353 l_entry_rec pay_jp_isdf_archive_pkg.t_entry_rec;
3354 --
3355 cursor csr_pact
3356 is
3357 select /* +ORDERED */
3358 ppa.payroll_action_id,
3359 ppa.business_group_id,
3360 ppa.effective_date
3361 from pay_assignment_actions paa,
3362 pay_payroll_actions ppa
3363 where paa.assignment_action_id = p_rec.assignment_action_id
3364 and ppa.payroll_action_id = paa.payroll_action_id;
3365 --
3366 begin
3367 --
3368 if g_debug then
3369 hr_utility.set_location(l_proc,0);
3370 end if;
3371 --
3372 if p_effective_date is null then
3373 l_effective_date := p_rec.effective_date;
3374 else
3375 l_effective_date := p_effective_date;
3376 end if;
3377 --
3378 -- re-extract current entry data on transfer date
3379 -- since jp_isdf_entry data was extracted on archive date.
3380 --
3381 if g_business_group_id is null
3382 or g_effective_date is null
3383 or g_effective_date <> l_effective_date then
3384 --
3385 g_payroll_action_id := null;
3386 g_business_group_id := null;
3387 g_effective_date := null;
3388 --
3389 open csr_pact;
3390 fetch csr_pact into g_payroll_action_id, g_business_group_id, g_effective_date;
3391 close csr_pact;
3392 --
3393 end if;
3394 --
3395 pay_jp_isdf_archive_pkg.fetch_entry(
3396 p_assignment_id => p_rec.assignment_id,
3397 p_business_group_id => g_business_group_id,
3398 p_effective_date => l_effective_date,
3399 p_entry_rec => l_entry_rec);
3400 --
3401 if p_rec.status = 'I' then
3402 --
3403 -- even if rec status is 'I', eev might setup by manually at transfer run time.
3404 -- when entry exists, use new ovn, update mode at transfer time instead of stored data.
3405 if l_entry_rec.ins_entry_cnt > 0 then
3406 --
3407 pay_element_entry_api.update_element_entry(
3408 p_validate => false,
3409 p_effective_date => l_effective_date,
3410 p_business_group_id => null, -- not used
3411 p_datetrack_update_mode => l_entry_rec.ins_datetrack_update_mode,
3412 p_element_entry_id => l_entry_rec.ins_element_entry_id,
3413 p_object_version_number => l_entry_rec.ins_ee_object_version_number,
3414 p_input_value_id1 => c_life_gen_iv_id,
3415 p_input_value_id2 => c_life_pens_iv_id,
3416 p_input_value_id3 => c_nonlife_long_iv_id,
3417 p_input_value_id4 => c_nonlife_short_iv_id,
3418 p_input_value_id5 => c_earthquake_iv_id,
3419 p_entry_value1 => fnd_number.number_to_canonical(p_rec.life_gen_ins_prem),
3420 p_entry_value2 => fnd_number.number_to_canonical(p_rec.life_pens_ins_prem),
3421 p_entry_value3 => fnd_number.number_to_canonical(p_rec.nonlife_long_ins_prem),
3422 p_entry_value4 => fnd_number.number_to_canonical(p_rec.nonlife_short_ins_prem),
3423 p_entry_value5 => fnd_number.number_to_canonical(p_rec.earthquake_ins_prem),
3424 p_effective_start_date => l_esd,
3425 p_effective_end_date => l_eed,
3426 p_update_warning => l_warning);
3427 --
3428 p_rec.status := 'Q';
3429 l_ins_element_entry_id := l_entry_rec.ins_element_entry_id;
3430 l_ins_ee_object_version_number := l_entry_rec.ins_ee_object_version_number;
3431 l_ins_datetrack_update_mode := l_entry_rec.ins_datetrack_update_mode;
3432 --
3433 else
3434 --
3435 l_ins_element_link_id := hr_entry_api.get_link(
3436 p_assignment_id => p_rec.assignment_id,
3437 p_element_type_id => c_isdf_ins_elm_id,
3438 p_session_date => l_effective_date);
3439 --
3440 if l_ins_element_link_id is null then
3441 fnd_message.set_name('PAY', 'PAY_JP_ISDF_NO_ELE_LINK');
3442 fnd_message.set_token('ELE_NAME',c_isdf_ins_elm);
3443 fnd_message.raise_error;
3444 end if;
3445 --
3446 pay_element_entry_api.create_element_entry(
3447 p_validate => false,
3448 p_effective_date => l_effective_date,
3449 p_business_group_id => null, -- not used
3450 p_assignment_id => p_rec.assignment_id,
3451 p_element_link_id => l_ins_element_link_id,
3452 p_entry_type => 'E',
3453 p_input_value_id1 => c_life_gen_iv_id,
3454 p_input_value_id2 => c_life_pens_iv_id,
3455 p_input_value_id3 => c_nonlife_long_iv_id,
3456 p_input_value_id4 => c_nonlife_short_iv_id,
3457 p_input_value_id5 => c_earthquake_iv_id,
3458 p_entry_value1 => fnd_number.number_to_canonical(p_rec.life_gen_ins_prem),
3459 p_entry_value2 => fnd_number.number_to_canonical(p_rec.life_pens_ins_prem),
3460 p_entry_value3 => fnd_number.number_to_canonical(p_rec.nonlife_long_ins_prem),
3461 p_entry_value4 => fnd_number.number_to_canonical(p_rec.nonlife_short_ins_prem),
3462 p_entry_value5 => fnd_number.number_to_canonical(p_rec.earthquake_ins_prem),
3463 p_element_entry_id => p_rec.ins_element_entry_id,
3464 p_object_version_number => p_rec.ins_ee_object_version_number,
3465 p_effective_start_date => l_esd,
3466 p_effective_end_date => l_eed,
3467 p_create_warning => l_warning);
3468 --
3469 l_ins_element_entry_id := p_rec.ins_element_entry_id;
3470 l_ins_ee_object_version_number := p_rec.ins_ee_object_version_number;
3471 l_ins_datetrack_update_mode := pay_jp_isdf_archive_pkg.ee_datetrack_update_mode(p_rec.ins_element_entry_id,l_esd,l_eed,l_effective_date);
3472 --
3473 end if;
3474 --
3475 -- even if rec status is 'I', eev might setup by manually at transfer run time.
3476 -- when entry exists, use new ovn, update mode at transfer time instead of stored data.
3477 if l_entry_rec.is_entry_cnt > 0 then
3478 --
3479 pay_element_entry_api.update_element_entry(
3480 p_validate => false,
3481 p_effective_date => l_effective_date,
3482 p_business_group_id => null, -- not used
3483 p_datetrack_update_mode => l_entry_rec.is_datetrack_update_mode,
3484 p_element_entry_id => l_entry_rec.is_element_entry_id,
3485 p_object_version_number => l_entry_rec.is_ee_object_version_number,
3486 p_input_value_id5 => c_social_iv_id,
3487 p_input_value_id6 => c_mutual_aid_iv_id,
3488 p_input_value_id7 => c_spouse_iv_id,
3489 p_input_value_id9 => c_national_pens_iv_id,
3490 p_entry_value5 => fnd_number.number_to_canonical(p_rec.social_ins_prem),
3491 p_entry_value6 => fnd_number.number_to_canonical(p_rec.mutual_aid_prem),
3492 p_entry_value7 => fnd_number.number_to_canonical(p_rec.spouse_income),
3493 p_entry_value9 => fnd_number.number_to_canonical(p_rec.national_pens_ins_prem),
3494 p_effective_start_date => l_esd,
3495 p_effective_end_date => l_eed,
3496 p_update_warning => l_warning);
3497 --
3498 p_rec.status := 'Q';
3499 l_is_element_entry_id := l_entry_rec.is_element_entry_id;
3500 l_is_ee_object_version_number := l_entry_rec.is_ee_object_version_number;
3501 l_is_datetrack_update_mode := l_entry_rec.is_datetrack_update_mode;
3502 --
3503 else
3504 --
3505 l_is_element_link_id := hr_entry_api.get_link(
3506 p_assignment_id => p_rec.assignment_id,
3507 p_element_type_id => c_isdf_is_elm_id,
3508 p_session_date => l_effective_date);
3509 --
3510 if l_is_element_link_id is null then
3511 fnd_message.set_name('PAY', 'PAY_JP_ISDF_NO_ELE_LINK');
3512 fnd_message.set_token('ELE_NAME',c_isdf_is_elm);
3513 fnd_message.raise_error;
3514 end if;
3515 --
3516 pay_element_entry_api.create_element_entry(
3517 p_validate => false,
3518 p_effective_date => l_effective_date,
3519 p_business_group_id => null, -- not used
3520 p_assignment_id => p_rec.assignment_id,
3521 p_element_link_id => l_is_element_link_id,
3522 p_entry_type => 'E',
3523 p_input_value_id5 => c_social_iv_id,
3524 p_input_value_id6 => c_mutual_aid_iv_id,
3525 p_input_value_id7 => c_spouse_iv_id,
3526 p_input_value_id9 => c_national_pens_iv_id,
3527 p_entry_value5 => fnd_number.number_to_canonical(p_rec.social_ins_prem),
3528 p_entry_value6 => fnd_number.number_to_canonical(p_rec.mutual_aid_prem),
3529 p_entry_value7 => fnd_number.number_to_canonical(p_rec.spouse_income),
3530 p_entry_value9 => fnd_number.number_to_canonical(p_rec.national_pens_ins_prem),
3531 p_element_entry_id => p_rec.is_element_entry_id,
3532 p_object_version_number => p_rec.is_ee_object_version_number,
3533 p_effective_start_date => l_esd,
3534 p_effective_end_date => l_eed,
3535 p_create_warning => l_warning);
3536 --
3537 l_is_element_entry_id := p_rec.is_element_entry_id;
3538 l_is_ee_object_version_number := p_rec.is_ee_object_version_number;
3539 l_is_datetrack_update_mode := pay_jp_isdf_archive_pkg.ee_datetrack_update_mode(p_rec.is_element_entry_id,l_esd,l_eed,l_effective_date);
3540 --
3541 end if;
3542 --
3543 p_rec.object_version_number := p_rec.object_version_number + 1;
3544 --
3545 if p_expire_after_transfer = 'Y' then
3546 l_status := 'D';
3547 -- p_rec.status = 'I' or 'Q' is now 'Q' because eev exists.
3548 else
3549 l_status := 'Q';
3550 end if;
3551 --
3552 -- revised old data at archive time to the latest extracted data.
3553 if p_rec.status = 'Q' then
3554 --
3555 update pay_jp_isdf_entry_dml_v
3556 set object_version_number = p_rec.object_version_number,
3557 status = l_status,
3558 ins_datetrack_update_mode = l_ins_datetrack_update_mode,
3559 ins_element_entry_id = fnd_number.number_to_canonical(l_ins_element_entry_id),
3560 ins_ee_object_version_number = fnd_number.number_to_canonical(l_ins_ee_object_version_number),
3561 life_gen_ins_prem_o = l_entry_rec.life_gen_ins_prem,
3562 life_pens_ins_prem_o = l_entry_rec.life_pens_ins_prem,
3563 nonlife_long_ins_prem_o = l_entry_rec.nonlife_long_ins_prem,
3564 nonlife_short_ins_prem_o = l_entry_rec.nonlife_short_ins_prem,
3565 earthquake_ins_prem_o = l_entry_rec.earthquake_ins_prem,
3566 is_datetrack_update_mode = l_is_datetrack_update_mode,
3567 is_element_entry_id = fnd_number.number_to_canonical(l_is_element_entry_id),
3568 is_ee_object_version_number = fnd_number.number_to_canonical(l_is_ee_object_version_number),
3569 social_ins_prem_o = l_entry_rec.social_ins_prem,
3570 mutual_aid_prem_o = l_entry_rec.mutual_aid_prem,
3571 spouse_income_o = l_entry_rec.spouse_income,
3572 national_pens_ins_prem_o = l_entry_rec.national_pens_ins_prem
3573 where row_id = p_rec.row_id;
3574 --
3575 else
3576 --
3577 update pay_jp_isdf_entry_dml_v
3578 set object_version_number = p_rec.object_version_number,
3579 status = l_status,
3580 ins_datetrack_update_mode = l_ins_datetrack_update_mode,
3581 is_datetrack_update_mode = l_is_datetrack_update_mode,
3582 ins_element_entry_id = fnd_number.number_to_canonical(l_ins_element_entry_id),
3583 is_element_entry_id = fnd_number.number_to_canonical(l_is_element_entry_id),
3584 ins_ee_object_version_number = fnd_number.number_to_canonical(l_ins_ee_object_version_number),
3585 is_ee_object_version_number = fnd_number.number_to_canonical(l_is_ee_object_version_number)
3586 where row_id = p_rec.row_id;
3587 --
3588 end if;
3589 --
3590 elsif p_rec.status = 'Q' then
3591 --
3592 -- even if rec status is 'Q', eev might removed. specially if archive time is
3593 -- before december, the eev might not be set on december, transfer time.
3594 -- when entry exists, use new ovn, update mode at transfer time instead of stored data.
3595 if l_entry_rec.ins_entry_cnt > 0 then
3596 --
3597 if changed(p_rec.life_gen_ins_prem,l_entry_rec.life_gen_ins_prem)
3598 or changed(p_rec.life_pens_ins_prem,l_entry_rec.life_pens_ins_prem)
3599 or changed(p_rec.nonlife_long_ins_prem,l_entry_rec.nonlife_long_ins_prem)
3600 or ((l_effective_date < c_st_upd_date_2007 and changed(p_rec.nonlife_short_ins_prem,l_entry_rec.nonlife_short_ins_prem))
3601 or (l_effective_date >= c_st_upd_date_2007 and changed(p_rec.earthquake_ins_prem,l_entry_rec.earthquake_ins_prem))) then
3602 --
3603 pay_element_entry_api.update_element_entry(
3604 p_validate => false,
3605 p_effective_date => l_effective_date,
3606 p_business_group_id => null, -- not used
3607 p_datetrack_update_mode => l_entry_rec.ins_datetrack_update_mode,
3608 p_element_entry_id => l_entry_rec.ins_element_entry_id,
3609 p_object_version_number => l_entry_rec.ins_ee_object_version_number,
3610 p_input_value_id1 => c_life_gen_iv_id,
3611 p_input_value_id2 => c_life_pens_iv_id,
3612 p_input_value_id3 => c_nonlife_long_iv_id,
3613 p_input_value_id4 => c_nonlife_short_iv_id,
3614 p_input_value_id5 => c_earthquake_iv_id,
3615 p_entry_value1 => fnd_number.number_to_canonical(p_rec.life_gen_ins_prem),
3616 p_entry_value2 => fnd_number.number_to_canonical(p_rec.life_pens_ins_prem),
3617 p_entry_value3 => fnd_number.number_to_canonical(p_rec.nonlife_long_ins_prem),
3618 p_entry_value4 => fnd_number.number_to_canonical(p_rec.nonlife_short_ins_prem),
3619 p_entry_value5 => fnd_number.number_to_canonical(p_rec.earthquake_ins_prem),
3620 p_effective_start_date => l_esd,
3621 p_effective_end_date => l_eed,
3622 p_update_warning => l_warning);
3623 --
3624 end if;
3625 --
3626 l_ins_element_entry_id := l_entry_rec.ins_element_entry_id;
3627 l_ins_ee_object_version_number := l_entry_rec.ins_ee_object_version_number;
3628 l_ins_datetrack_update_mode := l_entry_rec.ins_datetrack_update_mode;
3629 --
3630 else
3631 --
3632 -- this status soonly will be changed to 'Q' after insert.
3633 p_rec.status := 'I';
3634 --
3635 l_ins_element_link_id := hr_entry_api.get_link(
3636 p_assignment_id => p_rec.assignment_id,
3637 p_element_type_id => c_isdf_ins_elm_id,
3638 p_session_date => l_effective_date);
3639 --
3640 if l_ins_element_link_id is null then
3641 fnd_message.set_name('PAY', 'PAY_JP_ISDF_NO_ELE_LINK');
3642 fnd_message.set_token('ELE_NAME',c_isdf_ins_elm);
3643 fnd_message.raise_error;
3644 end if;
3645 --
3646 pay_element_entry_api.create_element_entry(
3647 p_validate => false,
3648 p_effective_date => l_effective_date,
3649 p_business_group_id => null, -- not used
3650 p_assignment_id => p_rec.assignment_id,
3651 p_element_link_id => l_ins_element_link_id,
3652 p_entry_type => 'E',
3653 p_input_value_id1 => c_life_gen_iv_id,
3654 p_input_value_id2 => c_life_pens_iv_id,
3655 p_input_value_id3 => c_nonlife_long_iv_id,
3656 p_input_value_id4 => c_nonlife_short_iv_id,
3657 p_input_value_id5 => c_earthquake_iv_id,
3658 p_entry_value1 => fnd_number.number_to_canonical(p_rec.life_gen_ins_prem),
3659 p_entry_value2 => fnd_number.number_to_canonical(p_rec.life_pens_ins_prem),
3660 p_entry_value3 => fnd_number.number_to_canonical(p_rec.nonlife_long_ins_prem),
3661 p_entry_value4 => fnd_number.number_to_canonical(p_rec.nonlife_short_ins_prem),
3662 p_entry_value5 => fnd_number.number_to_canonical(p_rec.earthquake_ins_prem),
3663 p_element_entry_id => p_rec.ins_element_entry_id,
3664 p_object_version_number => p_rec.ins_ee_object_version_number,
3665 p_effective_start_date => l_esd,
3666 p_effective_end_date => l_eed,
3667 p_create_warning => l_warning);
3668 --
3669 l_ins_element_entry_id := p_rec.ins_element_entry_id;
3670 l_ins_ee_object_version_number := p_rec.ins_ee_object_version_number;
3671 l_ins_datetrack_update_mode := pay_jp_isdf_archive_pkg.ee_datetrack_update_mode(p_rec.ins_element_entry_id,l_esd,l_eed,l_effective_date);
3672 --
3673 end if;
3674 --
3675 -- even if rec status is 'Q', eev might removed. specially if archive time is
3676 -- before december, the eev might not be set on december, transfer time.
3677 -- when entry exists, use new ovn, update mode at transfer time instead of stored data.
3678 if l_entry_rec.is_entry_cnt > 0 then
3679 --
3680 if changed(p_rec.social_ins_prem,l_entry_rec.social_ins_prem)
3681 or changed(p_rec.mutual_aid_prem,l_entry_rec.mutual_aid_prem)
3682 or changed(p_rec.spouse_income,l_entry_rec.spouse_income)
3683 or changed(p_rec.national_pens_ins_prem,l_entry_rec.national_pens_ins_prem) then
3684 --
3685 pay_element_entry_api.update_element_entry(
3686 p_validate => false,
3687 p_effective_date => l_effective_date,
3688 p_business_group_id => null, -- not used
3689 p_datetrack_update_mode => l_entry_rec.is_datetrack_update_mode,
3690 p_element_entry_id => l_entry_rec.is_element_entry_id,
3691 p_object_version_number => l_entry_rec.is_ee_object_version_number,
3692 p_input_value_id5 => c_social_iv_id,
3693 p_input_value_id6 => c_mutual_aid_iv_id,
3694 p_input_value_id7 => c_spouse_iv_id,
3695 p_input_value_id9 => c_national_pens_iv_id,
3696 p_entry_value5 => fnd_number.number_to_canonical(p_rec.social_ins_prem),
3697 p_entry_value6 => fnd_number.number_to_canonical(p_rec.mutual_aid_prem),
3698 p_entry_value7 => fnd_number.number_to_canonical(p_rec.spouse_income),
3699 p_entry_value9 => fnd_number.number_to_canonical(p_rec.national_pens_ins_prem),
3700 p_effective_start_date => l_esd,
3701 p_effective_end_date => l_eed,
3702 p_update_warning => l_warning);
3703 --
3704 end if;
3705 --
3706 l_is_element_entry_id := l_entry_rec.is_element_entry_id;
3707 l_is_ee_object_version_number := l_entry_rec.is_ee_object_version_number;
3708 l_is_datetrack_update_mode := l_entry_rec.is_datetrack_update_mode;
3709 --
3710 else
3711 --
3712 -- this status soonly will be changed to 'Q' after insert.
3713 p_rec.status := 'I';
3714 --
3715 l_is_element_link_id := hr_entry_api.get_link(
3716 p_assignment_id => p_rec.assignment_id,
3717 p_element_type_id => c_isdf_is_elm_id,
3718 p_session_date => l_effective_date);
3719 --
3720 if l_is_element_link_id is null then
3721 fnd_message.set_name('PAY', 'PAY_JP_ISDF_NO_ELE_LINK');
3722 fnd_message.set_token('ELE_NAME',c_isdf_is_elm);
3723 fnd_message.raise_error;
3724 end if;
3725 --
3726 pay_element_entry_api.create_element_entry(
3727 p_validate => false,
3728 p_effective_date => l_effective_date,
3729 p_business_group_id => null, -- not used
3730 p_assignment_id => p_rec.assignment_id,
3731 p_element_link_id => l_is_element_link_id,
3732 p_entry_type => 'E',
3733 p_input_value_id5 => c_social_iv_id,
3734 p_input_value_id6 => c_mutual_aid_iv_id,
3735 p_input_value_id7 => c_spouse_iv_id,
3736 p_input_value_id9 => c_national_pens_iv_id,
3737 p_entry_value5 => fnd_number.number_to_canonical(p_rec.social_ins_prem),
3738 p_entry_value6 => fnd_number.number_to_canonical(p_rec.mutual_aid_prem),
3739 p_entry_value7 => fnd_number.number_to_canonical(p_rec.spouse_income),
3740 p_entry_value9 => fnd_number.number_to_canonical(p_rec.national_pens_ins_prem),
3741 p_element_entry_id => p_rec.is_element_entry_id,
3742 p_object_version_number => p_rec.is_ee_object_version_number,
3743 p_effective_start_date => l_esd,
3744 p_effective_end_date => l_eed,
3745 p_create_warning => l_warning);
3746 --
3747 l_is_element_entry_id := p_rec.is_element_entry_id;
3748 l_is_ee_object_version_number := p_rec.is_ee_object_version_number;
3749 l_is_datetrack_update_mode := pay_jp_isdf_archive_pkg.ee_datetrack_update_mode(p_rec.is_element_entry_id,l_esd,l_eed,l_effective_date);
3750 --
3751 end if;
3752 --
3753 p_rec.object_version_number := p_rec.object_version_number + 1;
3754 --
3755 if p_expire_after_transfer = 'Y' then
3756 l_status := 'D';
3757 else
3758 -- p_rec.status = 'I' or 'Q' is now 'Q' because eev exists.
3759 l_status := 'Q';
3760 end if;
3761 --
3762 -- revised old data at archive time to the latest extracted data.
3763 if p_rec.status = 'Q' then
3764 --
3765 update pay_jp_isdf_entry_dml_v
3766 set object_version_number = p_rec.object_version_number,
3767 status = l_status,
3768 ins_datetrack_update_mode = l_ins_datetrack_update_mode,
3769 ins_element_entry_id = fnd_number.number_to_canonical(l_ins_element_entry_id),
3770 ins_ee_object_version_number = fnd_number.number_to_canonical(l_ins_ee_object_version_number),
3771 life_gen_ins_prem_o = l_entry_rec.life_gen_ins_prem,
3772 life_pens_ins_prem_o = l_entry_rec.life_pens_ins_prem,
3773 nonlife_long_ins_prem_o = l_entry_rec.nonlife_long_ins_prem,
3774 nonlife_short_ins_prem_o = l_entry_rec.nonlife_short_ins_prem,
3775 earthquake_ins_prem_o = l_entry_rec.earthquake_ins_prem,
3776 is_datetrack_update_mode = l_is_datetrack_update_mode,
3777 is_element_entry_id = fnd_number.number_to_canonical(l_is_element_entry_id),
3778 is_ee_object_version_number = fnd_number.number_to_canonical(l_is_ee_object_version_number),
3779 social_ins_prem_o = l_entry_rec.social_ins_prem,
3780 mutual_aid_prem_o = l_entry_rec.mutual_aid_prem,
3781 spouse_income_o = l_entry_rec.spouse_income,
3782 national_pens_ins_prem_o = l_entry_rec.national_pens_ins_prem
3783 where row_id = p_rec.row_id;
3784 --
3785 else
3786 --
3787 update pay_jp_isdf_entry_dml_v
3788 set object_version_number = p_rec.object_version_number,
3789 status = l_status,
3790 ins_datetrack_update_mode = l_ins_datetrack_update_mode,
3791 ins_element_entry_id = fnd_number.number_to_canonical(l_ins_element_entry_id),
3792 ins_ee_object_version_number = fnd_number.number_to_canonical(l_ins_ee_object_version_number),
3793 life_gen_ins_prem_o = null,
3794 life_pens_ins_prem_o = null,
3795 nonlife_long_ins_prem_o = null,
3796 nonlife_short_ins_prem_o = null,
3797 earthquake_ins_prem_o = null,
3798 is_datetrack_update_mode = l_is_datetrack_update_mode,
3799 is_element_entry_id = fnd_number.number_to_canonical(l_is_element_entry_id),
3800 is_ee_object_version_number = fnd_number.number_to_canonical(l_is_ee_object_version_number),
3801 social_ins_prem_o = null,
3802 mutual_aid_prem_o = null,
3803 spouse_income_o = null,
3804 national_pens_ins_prem_o = null
3805 where row_id = p_rec.row_id;
3806 --
3807 end if;
3808 --
3809 end if;
3810 --
3811 if g_debug then
3812 hr_utility.trace('end update_assact');
3813 hr_utility.set_location(l_proc,1000);
3814 end if;
3815 --
3816 end transfer_entry;
3817 --
3818 -- -------------------------------------------------------------------------
3819 -- transfer_life_gen
3820 -- -------------------------------------------------------------------------
3821 procedure transfer_life_gen(
3822 p_rec in out nocopy pay_jp_isdf_life_gen_v%rowtype,
3823 p_effective_date in date,
3824 p_expire_after_transfer in varchar2)
3825 is
3826 --
3827 l_proc varchar2(80) := c_package||'transfer_entry';
3828 --
3829 cursor csr_aei
3830 is
3831 select *
3832 from per_assignment_extra_info
3833 where assignment_extra_info_id = p_rec.assignment_extra_info_id;
3834 --
3835 l_csr_aei csr_aei%rowtype;
3836 l_effective_date date;
3837 --
3838 begin
3839 --
3840 if g_debug then
3841 hr_utility.set_location(l_proc,0);
3842 end if;
3843 --
3844 if p_effective_date is null then
3845 l_effective_date := p_rec.effective_date;
3846 else
3847 l_effective_date := p_effective_date;
3848 end if;
3849 --
3850 -- currently this is not supported that newly inserted data can be transfered.
3851 if p_rec.status = 'I' then
3852 --
3853 -- validation is required
3854 -- to disable insert ins_class and comp_code into action table
3855 -- because those are managed in master Org DF and
3856 -- to disable to insert annual prem even ins_class is LINC
3857 -- because the column should be derived from LINC loading data
3858 -- the field is not for override basically.
3859 hr_assignment_extra_info_api.create_assignment_extra_info(
3860 p_validate => false,
3861 p_assignment_id => p_rec.assignment_id,
3862 p_information_type => 'JP_ASS_LIG_INFO',
3863 p_aei_information_category => 'JP_ASS_LIG_INFO',
3864 p_aei_information1 => p_rec.gen_ins_class,
3865 p_aei_information2 => p_rec.gen_ins_company_code,
3866 p_aei_information3 => fnd_date.date_to_canonical(l_effective_date),
3867 p_aei_information4 => '',
3868 p_aei_information5 => p_rec.ins_type,
3869 p_aei_information6 => p_rec.ins_period,
3870 p_aei_information7 => p_rec.contractor_name,
3871 p_aei_information8 => p_rec.beneficiary_name,
3872 p_aei_information9 => p_rec.beneficiary_relship,
3873 p_aei_information10 => '',
3874 p_assignment_extra_info_id => p_rec.assignment_extra_info_id,
3875 p_object_version_number => p_rec.aei_object_version_number);
3876 --
3877 p_rec.object_version_number := p_rec.object_version_number + 1;
3878 --
3879 if p_expire_after_transfer = 'Y' then
3880 p_rec.status := 'D';
3881 end if;
3882 --
3883 update pay_jp_isdf_life_gen_dml_v
3884 set object_version_number = p_rec.object_version_number,
3885 status = p_rec.status,
3886 assignment_extra_info_id = fnd_number.number_to_canonical(p_rec.assignment_extra_info_id),
3887 aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
3888 where row_id = p_rec.row_id;
3889 --
3890 -- currently only support case of update entry data (except for amendment of ins_class and code)
3891 -- additionally not support if eit has been removed at the transferred time
3892 -- even if the eit existed at the archive time.
3893 elsif p_rec.status = 'Q' then
3894 --
3895 open csr_aei;
3896 fetch csr_aei into l_csr_aei;
3897 close csr_aei;
3898 --
3899 -- support only update in case eit exists at the transfer time.
3900 if l_csr_aei.assignment_extra_info_id is not null then
3901 --
3902 if changed(p_rec.ins_type,l_csr_aei.aei_information5)
3903 or changed(p_rec.ins_period,l_csr_aei.aei_information6)
3904 or changed(p_rec.contractor_name,l_csr_aei.aei_information7)
3905 or changed(p_rec.beneficiary_name,l_csr_aei.aei_information8)
3906 or changed(p_rec.beneficiary_relship,l_csr_aei.aei_information9) then
3907 --
3908 -- validation is required
3909 -- to disable update ins_class and comp_code into action table
3910 -- because those are managed in master Org DF and
3911 -- to disable to update annual prem even ins_class is LINC
3912 -- because the column should be derived from LINC loading data
3913 -- the field is not for override basically.
3914 --
3915 hr_assignment_extra_info_api.update_assignment_extra_info(
3916 p_validate => false,
3917 p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
3918 p_object_version_number => l_csr_aei.object_version_number,
3919 p_aei_information_category => 'JP_ASS_LIG_INFO',
3920 p_aei_information1 => l_csr_aei.aei_information1,
3921 p_aei_information2 => l_csr_aei.aei_information2,
3922 p_aei_information3 => l_csr_aei.aei_information3,
3923 p_aei_information4 => l_csr_aei.aei_information4,
3924 p_aei_information5 => p_rec.ins_type,
3925 p_aei_information6 => p_rec.ins_period,
3926 p_aei_information7 => p_rec.contractor_name,
3927 p_aei_information8 => p_rec.beneficiary_name,
3928 p_aei_information9 => p_rec.beneficiary_relship,
3929 p_aei_information10 => l_csr_aei.aei_information10);
3930 --
3931 p_rec.object_version_number := p_rec.object_version_number + 1;
3932 --
3933 if p_expire_after_transfer = 'Y' then
3934 p_rec.status := 'D';
3935 end if;
3936 --
3937 -- since no storage for old eit data in view, unnecessary to change like entry.
3938 update pay_jp_isdf_life_gen_dml_v
3939 set object_version_number = p_rec.object_version_number,
3940 status = p_rec.status,
3941 aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
3942 where row_id = p_rec.row_id;
3943 --
3944 end if;
3945 --
3946 end if;
3947 --
3948 elsif p_rec.status = 'D' then
3949 --
3950 --if p_rec.delete_mode = 'ZAP' then
3951 -- hr_assignment_extra_info_api.delete_assignment_extra_info(
3952 -- p_validate => false,
3953 -- p_assignment_extra_info_id => p_rec.assignment_extra_info_id,
3954 -- p_object_version_number => p_rec.aei_object_version_number);
3955 --else
3956 --
3957 open csr_aei;
3958 fetch csr_aei into l_csr_aei;
3959 close csr_aei;
3960 --
3961 if l_csr_aei.assignment_extra_info_id is not null then
3962 --
3963 hr_assignment_extra_info_api.update_assignment_extra_info(
3964 p_validate => false,
3965 p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
3966 p_object_version_number => l_csr_aei.object_version_number,
3967 p_aei_information_category => 'JP_ASS_LIG_INFO',
3968 p_aei_information1 => l_csr_aei.aei_information1,
3969 p_aei_information2 => l_csr_aei.aei_information2,
3970 p_aei_information3 => l_csr_aei.aei_information3,
3971 p_aei_information4 => fnd_date.date_to_canonical(l_effective_date-1),
3972 p_aei_information5 => p_rec.ins_type,
3973 p_aei_information6 => p_rec.ins_period,
3974 p_aei_information7 => p_rec.contractor_name,
3975 p_aei_information8 => p_rec.beneficiary_name,
3976 p_aei_information9 => p_rec.beneficiary_relship,
3977 p_aei_information10 => l_csr_aei.aei_information10);
3978 --
3979 p_rec.object_version_number := p_rec.object_version_number + 1;
3980 --
3981 -- since no storage for old eit data in view, unnecessary to change like entry.
3982 update pay_jp_isdf_life_gen_dml_v
3983 set object_version_number = p_rec.object_version_number,
3984 aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
3985 where row_id = p_rec.row_id;
3986 --
3987 end if;
3988 --
3989 end if;
3990 --
3991 if g_debug then
3992 hr_utility.trace('end update_assact');
3993 hr_utility.set_location(l_proc,1000);
3994 end if;
3995 --
3996 end transfer_life_gen;
3997 --
3998 -- -------------------------------------------------------------------------
3999 -- transfer_life_pens
4000 -- -------------------------------------------------------------------------
4001 procedure transfer_life_pens(
4002 p_rec in out nocopy pay_jp_isdf_life_pens_v%rowtype,
4003 p_effective_date in date,
4004 p_expire_after_transfer in varchar2)
4005 is
4006 --
4007 l_proc varchar2(80) := c_package||'transfer_entry';
4008 l_effective_date date;
4009 --
4010 cursor csr_aei
4011 is
4012 select *
4013 from per_assignment_extra_info
4014 where assignment_extra_info_id = p_rec.assignment_extra_info_id;
4015 --
4016 l_csr_aei csr_aei%rowtype;
4017 --
4018 begin
4019 --
4020 if g_debug then
4021 hr_utility.set_location(l_proc,0);
4022 end if;
4023 --
4024 if p_effective_date is null then
4025 l_effective_date := p_rec.effective_date;
4026 else
4027 l_effective_date := p_effective_date;
4028 end if;
4029 --
4030 -- currently this is not supported that newly inserted data can be transfered.
4031 if p_rec.status = 'I' then
4032 --
4033 -- validation is required
4034 -- to disable insert ins_class and comp_code into action table
4035 -- because those are managed in master Org DF and
4036 -- to disable to insert annual prem even ins_class is LINC
4037 -- because the column should be derived from LINC loading data
4038 -- the field is not for override basically.
4039 hr_assignment_extra_info_api.create_assignment_extra_info(
4040 p_validate => false,
4041 p_assignment_id => p_rec.assignment_id,
4042 p_information_type => 'JP_ASS_LIP_INFO',
4043 p_aei_information_category => 'JP_ASS_LIP_INFO',
4044 p_aei_information1 => p_rec.pens_ins_class,
4045 p_aei_information2 => p_rec.pens_ins_company_code,
4046 p_aei_information3 => fnd_date.date_to_canonical(l_effective_date),
4047 p_aei_information4 => '',
4048 p_aei_information5 => p_rec.ins_type,
4049 p_aei_information6 => fnd_date.date_to_canonical(p_rec.ins_period_start_date),
4050 p_aei_information7 => p_rec.ins_period,
4051 p_aei_information8 => p_rec.contractor_name,
4052 p_aei_information9 => p_rec.beneficiary_name,
4053 p_aei_information10 => p_rec.beneficiary_relship,
4054 p_aei_information11 => '',
4055 p_assignment_extra_info_id => p_rec.assignment_extra_info_id,
4056 p_object_version_number => p_rec.aei_object_version_number);
4057 --
4058 p_rec.object_version_number := p_rec.object_version_number + 1;
4059 --
4060 if p_expire_after_transfer = 'Y' then
4061 p_rec.status := 'D';
4062 end if;
4063 --
4064 update pay_jp_isdf_life_pens_dml_v
4065 set object_version_number = p_rec.object_version_number,
4066 status = p_rec.status,
4067 assignment_extra_info_id = fnd_number.number_to_canonical(p_rec.assignment_extra_info_id),
4068 aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
4069 where row_id = p_rec.row_id;
4070 --
4071 -- currently only support case of update entry data (except for amendment of ins_class and code)
4072 -- additionally not support if eit has been removed at the transferred time
4073 -- even if the eit existed at the archive time.
4074 elsif p_rec.status = 'Q' then
4075 --
4076 open csr_aei;
4077 fetch csr_aei into l_csr_aei;
4078 close csr_aei;
4079 --
4080 -- support only update in case eit exists at the transfer time.
4081 if l_csr_aei.assignment_extra_info_id is not null then
4082 --
4083 if changed(p_rec.ins_type,l_csr_aei.aei_information5)
4084 or changed(fnd_date.date_to_canonical(p_rec.ins_period_start_date),l_csr_aei.aei_information6)
4085 or changed(p_rec.ins_period,l_csr_aei.aei_information7)
4086 or changed(p_rec.contractor_name,l_csr_aei.aei_information8)
4087 or changed(p_rec.beneficiary_name,l_csr_aei.aei_information9)
4088 or changed(p_rec.beneficiary_relship,l_csr_aei.aei_information10) then
4089 --
4090 -- validation is required
4091 -- to disable update ins_class and comp_code into action table
4092 -- because those are managed in master Org DF and
4093 -- to disable to update annual prem even ins_class is LINC
4094 -- because the column should be derived from LINC loading data
4095 -- the field is not for override basically.
4096 hr_assignment_extra_info_api.update_assignment_extra_info(
4097 p_validate => false,
4098 p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
4099 p_object_version_number => l_csr_aei.object_version_number,
4100 p_aei_information_category => 'JP_ASS_LIP_INFO',
4101 p_aei_information1 => l_csr_aei.aei_information1,
4102 p_aei_information2 => l_csr_aei.aei_information2,
4103 p_aei_information3 => l_csr_aei.aei_information3,
4104 p_aei_information4 => l_csr_aei.aei_information4,
4105 p_aei_information5 => p_rec.ins_type,
4106 p_aei_information6 => fnd_date.date_to_canonical(p_rec.ins_period_start_date),
4107 p_aei_information7 => p_rec.ins_period,
4108 p_aei_information8 => p_rec.contractor_name,
4109 p_aei_information9 => p_rec.beneficiary_name,
4110 p_aei_information10 => p_rec.beneficiary_relship,
4111 p_aei_information11 => l_csr_aei.aei_information11);
4112 --
4113 p_rec.object_version_number := p_rec.object_version_number + 1;
4114 --
4115 if p_expire_after_transfer = 'Y' then
4116 p_rec.status := 'D';
4117 end if;
4118 --
4119 -- since no storage for old eit data in view, unnecessary to change like entry.
4120 update pay_jp_isdf_life_pens_dml_v
4121 set object_version_number = p_rec.object_version_number,
4122 status = p_rec.status,
4123 aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
4124 where row_id = p_rec.row_id;
4125 --
4126 end if;
4127 --
4128 end if;
4129 --
4130 elsif p_rec.status = 'D' then
4131 --
4132 --if p_rec.delete_mode = 'ZAP' then
4133 -- hr_assignment_extra_info_api.delete_assignment_extra_info(
4134 -- p_validate => false,
4135 -- p_assignment_extra_info_id => p_rec.assignment_extra_info_id,
4136 -- p_object_version_number => p_rec.aei_object_version_number);
4137 --else
4138 --
4139 open csr_aei;
4140 fetch csr_aei into l_csr_aei;
4141 close csr_aei;
4142 --
4143 if l_csr_aei.assignment_extra_info_id is not null then
4144 --
4145 hr_assignment_extra_info_api.update_assignment_extra_info(
4146 p_validate => false,
4147 p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
4148 p_object_version_number => l_csr_aei.object_version_number,
4149 p_aei_information_category => 'JP_ASS_LIP_INFO',
4150 p_aei_information1 => l_csr_aei.aei_information1,
4151 p_aei_information2 => l_csr_aei.aei_information2,
4152 p_aei_information3 => l_csr_aei.aei_information3,
4153 p_aei_information4 => fnd_date.date_to_canonical(l_effective_date-1),
4154 p_aei_information5 => p_rec.ins_type,
4155 p_aei_information6 => fnd_date.date_to_canonical(p_rec.ins_period_start_date),
4156 p_aei_information7 => p_rec.ins_period,
4157 p_aei_information8 => p_rec.contractor_name,
4158 p_aei_information9 => p_rec.beneficiary_name,
4159 p_aei_information10 => p_rec.beneficiary_relship,
4160 p_aei_information11 => l_csr_aei.aei_information11);
4161 --
4162 p_rec.object_version_number := p_rec.object_version_number + 1;
4163 --
4164 -- since no storage for old eit data in view, unnecessary to change like entry.
4165 update pay_jp_isdf_life_pens_dml_v
4166 set object_version_number = p_rec.object_version_number,
4167 aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
4168 where row_id = p_rec.row_id;
4169 --
4170 end if;
4171 --
4172 end if;
4173 --
4174 if g_debug then
4175 hr_utility.trace('end update_assact');
4176 hr_utility.set_location(l_proc,1000);
4177 end if;
4178 --
4179 end transfer_life_pens;
4180 --
4181 -- -------------------------------------------------------------------------
4182 -- transfer_nonlife
4183 -- -------------------------------------------------------------------------
4184 procedure transfer_nonlife(
4185 p_rec in out nocopy pay_jp_isdf_nonlife_v%rowtype,
4186 p_effective_date in date,
4187 p_expire_after_transfer in varchar2)
4188 is
4189 --
4190 l_proc varchar2(80) := c_package||'transfer_entry';
4191 l_effective_date date;
4192 --
4193 cursor csr_aei
4194 is
4195 select *
4196 from per_assignment_extra_info
4197 where assignment_extra_info_id = p_rec.assignment_extra_info_id;
4198 --
4199 l_csr_aei csr_aei%rowtype;
4200 --
4201 begin
4202 --
4203 if g_debug then
4204 hr_utility.set_location(l_proc,0);
4205 end if;
4206 --
4207 if p_effective_date is null then
4208 l_effective_date := p_rec.effective_date;
4209 else
4210 l_effective_date := p_effective_date;
4211 end if;
4212 --
4213 -- currently this is not supported that newly inserted data can be transfered.
4214 if p_rec.status = 'I' then
4215 --
4216 -- validation is required
4217 -- to disable insert ins_class and comp_code into action table
4218 -- because those are managed in master Org DF and
4219 -- to disable to insert annual prem
4220 -- because the column should be derived from customer loading data
4221 -- the field is not for override basically.
4222 hr_assignment_extra_info_api.create_assignment_extra_info(
4223 p_validate => false,
4224 p_assignment_id => p_rec.assignment_id,
4225 p_information_type => 'JP_ASS_AI_INFO',
4226 p_aei_information_category => 'JP_ASS_AI_INFO',
4227 p_aei_information1 => p_rec.nonlife_ins_term_type,
4228 p_aei_information2 => p_rec.nonlife_ins_company_code,
4229 p_aei_information3 => fnd_date.date_to_canonical(l_effective_date),
4230 p_aei_information4 => '',
4231 p_aei_information5 => p_rec.ins_type,
4232 p_aei_information6 => p_rec.ins_period,
4233 p_aei_information7 => p_rec.contractor_name,
4234 p_aei_information8 => p_rec.beneficiary_name,
4235 p_aei_information9 => p_rec.beneficiary_relship,
4236 p_aei_information10 => p_rec.maturity_repayment,
4237 p_aei_information11 => '',
4238 p_aei_information13 => p_rec.nonlife_ins_class,
4239 p_assignment_extra_info_id => p_rec.assignment_extra_info_id,
4240 p_object_version_number => p_rec.aei_object_version_number);
4241 --
4242 p_rec.object_version_number := p_rec.object_version_number + 1;
4243 --
4244 if p_expire_after_transfer = 'Y' then
4245 p_rec.status := 'D';
4246 end if;
4247 --
4248 update pay_jp_isdf_nonlife_dml_v
4249 set object_version_number = p_rec.object_version_number,
4250 status = p_rec.status,
4251 assignment_extra_info_id = fnd_number.number_to_canonical(p_rec.assignment_extra_info_id),
4252 aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
4253 where row_id = p_rec.row_id;
4254 --
4255 -- currently only support case of update entry data (except for amendment of ins_class and code)
4256 -- additionally not support if eit has been removed at the transferred time
4257 -- even if the eit existed at the archive time.
4258 elsif p_rec.status = 'Q' then
4259 --
4260 open csr_aei;
4261 fetch csr_aei into l_csr_aei;
4262 close csr_aei;
4263 --
4264 -- support only update in case eit exists at the transfer time.
4265 if l_csr_aei.assignment_extra_info_id is not null then
4266 --
4267 if changed(p_rec.ins_type,l_csr_aei.aei_information5)
4268 or changed(p_rec.ins_period,l_csr_aei.aei_information6)
4269 or changed(p_rec.contractor_name,l_csr_aei.aei_information7)
4270 or changed(p_rec.beneficiary_name,l_csr_aei.aei_information8)
4271 or changed(p_rec.beneficiary_relship,l_csr_aei.aei_information9)
4272 or (l_effective_date < c_st_upd_date_2007 and changed(p_rec.maturity_repayment,l_csr_aei.aei_information10)) then
4273 --
4274 -- validation is required
4275 -- to disable update ins_class and comp_code into action table
4276 -- because those are managed in master Org DF and
4277 -- to disable to update annual prem even ins_class is LINC
4278 -- because the column should be derived from LINC loading data
4279 -- the field is not for override basically.
4280 hr_assignment_extra_info_api.update_assignment_extra_info(
4281 p_validate => false,
4282 p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
4283 p_object_version_number => l_csr_aei.object_version_number,
4284 p_aei_information_category => 'JP_ASS_AI_INFO',
4285 p_aei_information1 => l_csr_aei.aei_information1,
4286 p_aei_information2 => l_csr_aei.aei_information2,
4287 p_aei_information3 => l_csr_aei.aei_information3,
4288 p_aei_information4 => l_csr_aei.aei_information4,
4289 p_aei_information5 => p_rec.ins_type,
4290 p_aei_information6 => p_rec.ins_period,
4291 p_aei_information7 => p_rec.contractor_name,
4292 p_aei_information8 => p_rec.beneficiary_name,
4293 p_aei_information9 => p_rec.beneficiary_relship,
4294 p_aei_information10 => p_rec.maturity_repayment,
4295 p_aei_information11 => l_csr_aei.aei_information11,
4296 p_aei_information13 => l_csr_aei.aei_information13);
4297 --
4298 p_rec.object_version_number := p_rec.object_version_number + 1;
4299 --
4300 if p_expire_after_transfer = 'Y' then
4301 p_rec.status := 'D';
4302 end if;
4303 --
4304 -- since no storage for old eit data in view, unnecessary to change like entry.
4305 update pay_jp_isdf_nonlife_dml_v
4306 set object_version_number = p_rec.object_version_number,
4307 status = p_rec.status,
4308 aei_object_version_number = fnd_number.number_to_canonical(p_rec.aei_object_version_number)
4309 where row_id = p_rec.row_id;
4310 --
4311 end if;
4312 --
4313 end if;
4314 --
4315 elsif p_rec.status = 'D' then
4316 --
4317 --if p_rec.delete_mode = 'ZAP' then
4318 -- hr_assignment_extra_info_api.delete_assignment_extra_info(
4319 -- p_validate => false,
4320 -- p_assignment_extra_info_id => p_rec.assignment_extra_info_id,
4321 -- p_object_version_number => p_rec.aei_object_version_number);
4322 --else
4323 --
4324 open csr_aei;
4325 fetch csr_aei into l_csr_aei;
4326 close csr_aei;
4327 --
4328 if l_csr_aei.assignment_extra_info_id is not null then
4329 --
4330 hr_assignment_extra_info_api.update_assignment_extra_info(
4331 p_validate => false,
4332 p_assignment_extra_info_id => l_csr_aei.assignment_extra_info_id,
4333 p_object_version_number => l_csr_aei.object_version_number,
4334 p_aei_information_category => 'JP_ASS_AI_INFO',
4335 p_aei_information1 => l_csr_aei.aei_information1,
4336 p_aei_information2 => l_csr_aei.aei_information2,
4337 p_aei_information3 => l_csr_aei.aei_information3,
4338 p_aei_information4 => fnd_date.date_to_canonical(l_effective_date-1),
4339 p_aei_information5 => p_rec.ins_type,
4340 p_aei_information6 => p_rec.ins_period,
4341 p_aei_information7 => p_rec.contractor_name,
4342 p_aei_information8 => p_rec.beneficiary_name,
4343 p_aei_information9 => p_rec.beneficiary_relship,
4344 p_aei_information10 => p_rec.maturity_repayment,
4345 p_aei_information11 => l_csr_aei.aei_information11,
4346 p_aei_information13 => l_csr_aei.aei_information13);
4347 --
4348 p_rec.object_version_number := p_rec.object_version_number + 1;
4349 --
4350 -- since no storage for old eit data in view, unnecessary to change like entry.
4351 update pay_jp_isdf_nonlife_dml_v
4352 set object_version_number = p_rec.object_version_number,
4353 aei_object_version_number = fnd_number.number_to_canonical(l_csr_aei.object_version_number)
4354 where row_id = p_rec.row_id;
4355 --
4356 end if;
4357 --
4358 end if;
4359 --
4360 if g_debug then
4361 hr_utility.trace('end update_assact');
4362 hr_utility.set_location(l_proc,1000);
4363 end if;
4364 --
4365 end transfer_nonlife;
4366 --
4367 -- -------------------------------------------------------------------------
4368 -- do_transfer
4369 -- -------------------------------------------------------------------------
4370 procedure do_transfer(
4371 p_action_information_id in number,
4372 p_object_version_number in out nocopy number,
4373 p_transfer_date in date,
4374 p_create_session in boolean default true,
4375 p_expire_after_transfer in varchar2 default 'N')
4376 is
4377 --
4378 l_proc varchar2(80) := c_package||'do_transfer';
4379 l_assact_rec pay_jp_isdf_assact_v%rowtype;
4380 l_effective_date date;
4381 l_year_end_date date;
4382 l_dec_first_date date;
4383 --
4384 cursor csr_entry
4385 is
4386 select *
4387 from pay_jp_isdf_entry_v
4388 where assignment_action_id = l_assact_rec.assignment_action_id
4389 and status <> 'D'
4390 for update nowait;
4391 --
4392 -- ass eit exclude PC data, take only GIP/LINC
4393 cursor csr_life_gen_del
4394 is
4395 select *
4396 from pay_jp_isdf_life_gen_v
4397 where assignment_action_id = l_assact_rec.assignment_action_id
4398 and gen_ins_class <> 'PC'
4399 and status = 'D'
4400 for update nowait;
4401 --
4402 -- status U is only case when archive was transfered
4403 cursor csr_life_gen_upd
4404 is
4405 select *
4406 from pay_jp_isdf_life_gen_v
4407 where assignment_action_id = l_assact_rec.assignment_action_id
4408 and gen_ins_class <> 'PC'
4409 and status = 'Q'
4410 for update nowait;
4411 --
4412 cursor csr_life_gen_ins
4413 is
4414 select *
4415 from pay_jp_isdf_life_gen_v
4416 where assignment_action_id = l_assact_rec.assignment_action_id
4417 and gen_ins_class <> 'PC'
4418 and status = 'I'
4419 for update nowait;
4420 --
4421 -- ass eit exclude PC data, take only GIP/LINC
4422 cursor csr_life_pens_del
4423 is
4424 select *
4425 from pay_jp_isdf_life_pens_v
4426 where assignment_action_id = l_assact_rec.assignment_action_id
4427 and pens_ins_class <> 'PC'
4428 and status = 'D'
4429 for update nowait;
4430 --
4431 -- status U is only case when archive was transfered
4432 cursor csr_life_pens_upd
4433 is
4434 select *
4435 from pay_jp_isdf_life_pens_v
4436 where assignment_action_id = l_assact_rec.assignment_action_id
4437 and pens_ins_class <> 'PC'
4438 and status = 'Q'
4439 for update nowait;
4440 --
4441 cursor csr_life_pens_ins
4442 is
4443 select *
4444 from pay_jp_isdf_life_pens_v
4445 where assignment_action_id = l_assact_rec.assignment_action_id
4446 and pens_ins_class <> 'PC'
4447 and status = 'I'
4448 for update nowait;
4449 --
4450 -- ass eit exclude PC data, take only AP
4451 cursor csr_nonlife_del
4452 is
4453 select *
4454 from pay_jp_isdf_nonlife_v
4455 where assignment_action_id = l_assact_rec.assignment_action_id
4456 and nonlife_ins_class <> 'PC'
4457 and status = 'D'
4458 for update nowait;
4459 --
4460 -- status U is only case when archive was transfered
4461 cursor csr_nonlife_upd
4462 is
4463 select *
4464 from pay_jp_isdf_nonlife_v
4465 where assignment_action_id = l_assact_rec.assignment_action_id
4466 and nonlife_ins_class <> 'PC'
4467 and status = 'Q'
4468 for update nowait;
4469 --
4470 cursor csr_nonlife_ins
4471 is
4472 select *
4473 from pay_jp_isdf_nonlife_v
4474 where assignment_action_id = l_assact_rec.assignment_action_id
4475 and nonlife_ins_class <> 'PC'
4476 and status = 'I'
4477 for update nowait;
4478 --
4479 begin
4480 --
4481 if g_debug then
4482 hr_utility.set_location(l_proc,0);
4483 end if;
4484 --
4485 hr_api.mandatory_arg_error(l_proc, 'transfer_date', p_transfer_date);
4486 --
4487 pay_jp_isdf_dml_pkg.lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
4488 --
4489 if l_assact_rec.transaction_status <> 'A' then
4490 fnd_message.set_name('PAY', 'PAY_JP_DEF_INVALID_TXN_STATUS');
4491 fnd_message.raise_error;
4492 elsif l_assact_rec.transfer_status <> 'U' then
4493 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_TRANSFERRED');
4494 fnd_message.raise_error;
4495 end if;
4496 --
4497 l_year_end_date := add_months(trunc(l_assact_rec.effective_date, 'YYYY'), 12) - 1;
4498 l_dec_first_date := trunc(l_year_end_date,'MM');
4499 --
4500 if p_transfer_date is null then
4501 l_effective_date := l_assact_rec.effective_date;
4502 else
4503 l_effective_date := p_transfer_date;
4504 end if;
4505 --
4506 -- actually if l_dec_first_date <= p_transfer_date <= l_year_end_date,
4507 -- insert is ok because nonrecurring element (unnecessary to validate transfer_date < effective_date)
4508 -- but basically transfer should be done after archive process date.
4509 if l_effective_date < l_assact_rec.effective_date
4510 or l_effective_date < l_dec_first_date
4511 or l_effective_date > l_year_end_date then
4512 fnd_message.set_name('PAY', 'PAY_JP_ISDF_INVALID_TRANS_DATE');
4513 fnd_message.set_token('EFFECTIVE_DATE', fnd_date.date_to_chardate(l_assact_rec.effective_date));
4514 fnd_message.set_token('DEC_FIRST_DATE', fnd_date.date_to_chardate(l_dec_first_date));
4515 fnd_message.set_token('YEAR_END_DATE', fnd_date.date_to_chardate(l_year_end_date));
4516 fnd_message.raise_error;
4517 end if;
4518 --
4519 -- for api use
4520 if p_create_session then
4521 insert_session(l_effective_date);
4522 end if;
4523 --
4524 if g_debug then
4525 hr_utility.set_location(l_proc,20);
4526 hr_utility.trace('action_information_id : '||p_action_information_id);
4527 hr_utility.trace('object_version_number : '||p_object_version_number);
4528 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
4529 hr_utility.trace('start calc_dct before finalize');
4530 end if;
4531 --
4532 -- Transfer the followings.
4533 --
4534 -- Transfer JP_ISDF_ENTRY to PAY_ELEMENT_ENTRIES_F
4535 --
4536 for l_rec in csr_entry loop
4537 -- boolean is not supported in jrad.
4538 transfer_entry(l_rec,l_effective_date,p_expire_after_transfer);
4539 end loop;
4540 --
4541 -- Transfer the followings.
4542 --
4543 -- Transfer JP_ISDF_LIFE_GEN.GEN_INS_CLASS=GIP/LINC to PER_ASSIGNMENT_EXTRA_INFO.JP_ASS_LIG_INFO
4544 -- Transfer JP_ISDF_LIFE_PENS.PENS_INS_CLASS=GIP/LINC to PER_ASSIGNMENT_EXTRA_INFO.JP_ASS_LIP_INFO
4545 --
4546 -- Disable to delete
4547 -- because GIP and LINC data are relevant to deducted monthly element entry
4548 -- which is used in custom formula for monthly salary
4549 -- so that employer needs to care dependency for deletion of EIT with custom element entry setup.
4550 -- But we allow to delete GIP/LINC archive data on FormPG,
4551 -- it means that makes inconsistence between Report data and EIT data.
4552 -- employee can exclude LINC/GIP data from subjection of deduction,
4553 -- but this action is not same to remove LING/GIP from EIT.
4554 -- delete phase
4555 --for l_life_gen_rec in csr_life_gen_del loop
4556 -- transfer_life_gen(l_life_gen_rec,l_effective_date,p_expire_after_transfer);
4557 --end loop;
4558 --
4559 --for l_life_pens_rec in csr_life_pens_del loop
4560 -- transfer_life_pens(l_life_pens_rec,l_effective_date,p_expire_after_transfer);
4561 --end loop;
4562 --
4563 --for l_nonlife_rec in csr_nonlife_del loop
4564 -- transfer_nonlife(l_nonlife_rec,l_effective_date,p_expire_after_transfer);
4565 --end loop;
4566 --
4567 -- update phase
4568 for l_life_gen_rec in csr_life_gen_upd loop
4569 transfer_life_gen(l_life_gen_rec,l_effective_date,p_expire_after_transfer);
4570 end loop;
4571 --
4572 for l_life_pens_rec in csr_life_pens_upd loop
4573 transfer_life_pens(l_life_pens_rec,l_effective_date,p_expire_after_transfer);
4574 end loop;
4575 --
4576 for l_nonlife_rec in csr_nonlife_upd loop
4577 transfer_nonlife(l_nonlife_rec,l_effective_date,p_expire_after_transfer);
4578 end loop;
4579 --
4580 -- Disable to insert
4581 -- because GIP and LINC data are relevant to deducted monthly element entry
4582 -- which is used in custom formula for monthly salary
4583 -- so that employer needs to care dependency for insertion of EIT with custom element entry setup.
4584 -- insert phase
4585 --for l_life_gen_rec in csr_life_gen_ins loop
4586 -- transfer_life_gen(l_life_gen_rec,l_effective_date,p_expire_after_transfer);
4587 --end loop;
4588 --
4589 --for l_life_pens_rec in csr_life_pens_ins loop
4590 -- transfer_life_pens(l_life_pens_rec,l_effective_date,p_expire_after_transfer);
4591 --end loop;
4592 --
4593 --for l_nonlife_rec in csr_nonlife_ins loop
4594 -- transfer_nonlife(l_nonlife_rec,l_effective_date,p_expire_after_transfer);
4595 --end loop;
4596 --
4597 if p_create_session then
4598 delete_session;
4599 end if;
4600 --
4601 p_object_version_number := l_assact_rec.object_version_number + 1;
4602 --
4603 --api is disable because assact has been locked.
4604 --pay_jp_isdf_dml_pkg.update_assact(
4605 -- p_action_information_id => l_assact_rec.assignment_action_id,
4606 -- p_object_version_number => p_object_version_number,
4607 -- p_transaction_status => l_assact_rec.transaction_status,
4608 -- p_finalized_date => l_assact_rec.finalized_date,
4609 -- p_finalized_by => l_assact_rec.finalized_by,
4610 -- p_user_comments => l_assact_rec.user_comments,
4611 -- p_admin_comments => l_assact_rec.admin_comments,
4612 -- p_transfer_status => 'T',
4613 -- p_transfer_date => fnd_date.date_to_canonical(l_effective_date),
4614 -- p_expiry_date => l_assact_rec.expiry_date);
4615 update pay_jp_isdf_assact_dml_v
4616 set object_version_number = p_object_version_number,
4617 transfer_status = 'T',
4618 transfer_date = fnd_date.date_to_canonical(l_effective_date)
4619 where row_id = l_assact_rec.row_id;
4620 --
4621 if g_debug then
4622 hr_utility.trace('end update_assact');
4623 hr_utility.set_location(l_proc,1000);
4624 end if;
4625 --
4626 end do_transfer;
4627 --
4628 -- -------------------------------------------------------------------------
4629 -- do_expire
4630 -- -------------------------------------------------------------------------
4631 procedure do_expire(
4632 p_action_information_id in number,
4633 p_object_version_number in out nocopy number,
4634 p_expiry_date in date,
4635 p_create_session in boolean default true,
4636 p_mode in varchar2 default null)
4637 is
4638 --
4639 -- p_mode: DELETE: change archive status to D
4640 -- ZAP : remove archive data of status D
4641 -- N/A : nothing to do. (original)
4642 --
4643 l_proc varchar2(80) := c_package||'do_expire';
4644 l_assact_rec pay_jp_isdf_assact_v%rowtype;
4645 l_effective_date date;
4646 l_dec_first_date date;
4647 l_year_end_date date;
4648 l_esd date;
4649 l_eed date;
4650 l_warning boolean;
4651 l_object_version_number number;
4652 --
4653 cursor csr_entry
4654 is
4655 select *
4656 from pay_jp_isdf_entry_v
4657 where assignment_action_id = l_assact_rec.assignment_action_id
4658 for update nowait;
4659 --
4660 l_csr_entry csr_entry%rowtype;
4661 --
4662 cursor csr_del
4663 is
4664 select rowid row_id,
4665 action_information_id,
4666 object_version_number,
4667 action_information_category
4668 from pay_action_information
4669 where action_context_id = l_assact_rec.assignment_action_id
4670 and action_context_type = 'AAP'
4671 and action_information_category <> 'JP_ISDF_ASSACT'
4672 and action_information1 <> 'D';
4673 --
4674 l_csr_del csr_del%rowtype;
4675 --
4676 begin
4677 --
4678 if g_debug then
4679 hr_utility.set_location(l_proc,0);
4680 end if;
4681 --
4682 hr_api.mandatory_arg_error(l_proc,'expiry_date',p_expiry_date);
4683 --
4684 pay_jp_isdf_dml_pkg.lock_assact(p_action_information_id, p_object_version_number, l_assact_rec);
4685 --
4686 if l_assact_rec.transaction_status = 'U' then
4687 fnd_message.set_name('PAY', 'PAY_JP_DEF_NOT_TRANSFERRED_YET');
4688 fnd_message.raise_error;
4689 elsif l_assact_rec.transfer_status = 'E' then
4690 fnd_message.set_name('PAY', 'PAY_JP_DEF_ALREADY_EXPIRED');
4691 fnd_message.raise_error;
4692 end if;
4693 --
4694 if g_debug then
4695 hr_utility.set_location(l_proc,20);
4696 hr_utility.trace('action_information_id : '||p_action_information_id);
4697 hr_utility.trace('object_version_number : '||p_object_version_number);
4698 hr_utility.trace('assignment_action_id : '||l_assact_rec.assignment_action_id);
4699 hr_utility.trace('start calc_dct before finalize');
4700 end if;
4701 --
4702 l_year_end_date := add_months(trunc(l_assact_rec.effective_date, 'YYYY'), 12) - 1;
4703 l_dec_first_date := trunc(l_year_end_date,'MM');
4704 --
4705 -- actually if l_dec_first_date (first day of l_transfer_date) <= p_expiry_date <= l_year_end_date,
4706 -- delete is ok because nonrecurring element (unnecessary to validate expiry_date < transfer_date)
4707 -- but basically expiry should be done after transfer process date.
4708 -- (transfer_date is always set since transfer_status check has been done)
4709 if p_expiry_date < l_assact_rec.effective_date
4710 or p_expiry_date < l_assact_rec.transfer_date
4711 or p_expiry_date > l_year_end_date then
4712 fnd_message.set_name('PAY', 'PAY_JP_ISDF_INVALID_EXP_DATE');
4713 fnd_message.set_token('TRANSFER_DATE', fnd_date.date_to_chardate(l_assact_rec.transfer_date));
4714 fnd_message.set_token('YEAR_END_DATE', fnd_date.date_to_chardate(l_year_end_date));
4715 fnd_message.raise_error;
4716 end if;
4717 --
4718 -- l_dec_first_date or effective_date <= l_transfer_date <= l_year_end_date
4719 -- l_transfer_date <= l_expirty_date <= l_year_end_date
4720 -- since delete mode is not allowed to delete on the same day of last eev eed,
4721 -- if l_expiry_date is end period of transffered date (= l_year_end_date),
4722 -- set delete validation start_date (p_effective_date) to l_year_end_date - 1.
4723 if p_expiry_date = l_year_end_date then
4724 l_effective_date := l_year_end_date - 1;
4725 else
4726 l_effective_date := p_expiry_date;
4727 end if;
4728 --
4729 -- for api use.
4730 if p_create_session then
4731 insert_session(l_effective_date);
4732 end if;
4733 --
4734 open csr_entry;
4735 loop
4736 fetch csr_entry into l_csr_entry;
4737 exit when csr_entry%notfound;
4738 --
4739 pay_element_entry_api.delete_element_entry(
4740 p_validate => false,
4741 p_effective_date => l_effective_date,
4742 p_datetrack_delete_mode => 'DELETE',
4743 p_element_entry_id => l_csr_entry.ins_element_entry_id,
4744 p_object_version_number => l_csr_entry.ins_ee_object_version_number,
4745 p_effective_start_date => l_esd,
4746 p_effective_end_date => l_eed,
4747 p_delete_warning => l_warning);
4748 --
4749 pay_element_entry_api.delete_element_entry(
4750 p_validate => false,
4751 p_effective_date => l_effective_date,
4752 p_datetrack_delete_mode => 'DELETE',
4753 p_element_entry_id => l_csr_entry.is_element_entry_id,
4754 p_object_version_number => l_csr_entry.is_ee_object_version_number,
4755 p_effective_start_date => l_esd,
4756 p_effective_end_date => l_eed,
4757 p_delete_warning => l_warning);
4758 --
4759 update pay_jp_isdf_entry_dml_v
4760 set object_version_number = l_csr_entry.object_version_number + 1,
4761 ins_ee_object_version_number = fnd_number.number_to_canonical(l_csr_entry.ins_ee_object_version_number),
4762 is_ee_object_version_number = fnd_number.number_to_canonical(l_csr_entry.is_ee_object_version_number)
4763 where row_id = l_csr_entry.row_id;
4764 --
4765 end loop;
4766 close csr_entry;
4767 --
4768 if p_mode = 'ZAP' then
4769 --
4770 delete
4771 from pay_action_information
4772 where action_context_id = l_assact_rec.assignment_action_id
4773 and action_context_type = 'AAP'
4774 and action_information_category <> 'JP_ISDF_ASSACT';
4775 --
4776 elsif p_mode = 'DELETE' then
4777 --
4778 open csr_del;
4779 loop
4780 --
4781 fetch csr_del into l_csr_del;
4782 exit when csr_del%notfound;
4783 --
4784 -- ovn already updated above.
4785 if l_csr_del.action_information_category = 'JP_ISDF_ENTRY' then
4786 l_object_version_number := l_csr_del.object_version_number;
4787 else
4788 l_object_version_number := l_csr_del.object_version_number + 1;
4789 end if;
4790 --
4791 update pay_action_information
4792 set object_version_number = l_object_version_number,
4793 action_information1 = 'D'
4794 where rowid = l_csr_del.row_id;
4795 --
4796 end loop;
4797 close csr_del;
4798 --
4799 end if;
4800 --
4801 if p_create_session then
4802 delete_session;
4803 end if;
4804 --
4805 p_object_version_number := l_assact_rec.object_version_number + 1;
4806 --
4807 --api is disable because assact has been locked.
4808 --pay_jp_isdf_dml_pkg.update_assact(
4809 -- p_action_information_id => l_assact_rec.assignment_action_id,
4810 -- p_object_version_number => p_object_version_number,
4811 -- p_transaction_status => l_assact_rec.transaction_status,
4812 -- p_finalized_date => l_assact_rec.finalized_date,
4813 -- p_finalized_by => l_assact_rec.finalized_by,
4814 -- p_user_comments => l_assact_rec.user_comments,
4815 -- p_admin_comments => l_assact_rec.admin_comments,
4816 -- p_transfer_status => 'E',
4817 -- p_transfer_date => l_assact_rec.transfer_date,
4818 -- p_expiry_date => fnd_date.date_to_canonical(p_expiry_date));
4819 update pay_jp_isdf_assact_dml_v
4820 set object_version_number = p_object_version_number,
4821 transfer_status = 'E',
4822 expiry_date = fnd_date.date_to_canonical(p_expiry_date)
4823 where row_id = l_assact_rec.row_id;
4824 --
4825 if g_debug then
4826 hr_utility.trace('end update_assact');
4827 hr_utility.set_location(l_proc,1000);
4828 end if;
4829 --
4830 end do_expire;
4831 --
4832 -- -------------------------------------------------------------------------
4833 -- get_sqlerrm (use multiple transaction)
4834 -- -------------------------------------------------------------------------
4835 function get_sqlerrm
4836 return varchar2
4837 is
4838 begin
4839 --
4840 if sqlcode = -20001 then
4841 --
4842 declare
4843 l_sqlerrm varchar2(2000) := fnd_message.get;
4844 begin
4845 if l_sqlerrm is not null then
4846 return l_sqlerrm;
4847 else
4848 return sqlerrm;
4849 end if;
4850 end;
4851 --
4852 else
4853 return sqlerrm;
4854 end if;
4855 --
4856 end get_sqlerrm;
4857 --
4858 -- -------------------------------------------------------------------------
4859 -- do_finalize (Multiple Transaction for internal use only)
4860 -- -------------------------------------------------------------------------
4861 procedure do_finalize(
4862 errbuf out nocopy varchar2,
4863 retcode out nocopy varchar2,
4864 p_payroll_action_id in number,
4865 p_user_comments in varchar2)
4866 is
4867 --
4868 l_effective_date date;
4869 --
4870 cursor csr_assact
4871 is
4872 select /*+ ORDERED */
4873 assact.action_information_id,
4874 assact.object_version_number,
4875 pp.full_name,
4876 pa.assignment_number
4877 from pay_assignment_actions paa,
4878 pay_jp_isdf_assact_v assact,
4879 per_all_assignments_f pa,
4880 per_all_people_f pp
4881 where paa.payroll_action_id = p_payroll_action_id
4882 and paa.action_status = 'C'
4883 and assact.assignment_action_id = paa.assignment_action_id
4884 and assact.transaction_status = 'N'
4885 and pa.assignment_id = assact.assignment_id
4886 and assact.effective_date
4887 between pa.effective_start_date and pa.effective_end_date
4888 and pp.person_id = pa.person_id
4889 and assact.effective_date
4890 between pp.effective_start_date and pp.effective_end_date
4891 order by lpad(pa.assignment_number,10,' '),
4892 pp.full_name;
4893 --
4894 begin
4895 --
4896 select effective_date
4897 into l_effective_date
4898 from pay_jp_isdf_pact_v
4899 where payroll_action_id = p_payroll_action_id;
4900 --
4901 insert_session(l_effective_date);
4902 commit;
4903 --
4904 fnd_file.put_line(fnd_file.output, 'Full Name Assignment Number');
4905 fnd_file.put_line(fnd_file.output, '---------------------------------------- ------------------------------');
4906 fnd_file.put_line(fnd_file.log, 'Full Name Assignment Number');
4907 fnd_file.put_line(fnd_file.log, '---------------------------------------- ------------------------------');
4908 --
4909 for l_rec in csr_assact loop
4910 --
4911 begin
4912 --
4913 do_finalize(
4914 p_action_information_id => l_rec.action_information_id,
4915 p_object_version_number => l_rec.object_version_number,
4916 p_user_comments => p_user_comments);
4917 --
4918 commit;
4919 --
4920 fnd_file.put_line(fnd_file.output, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
4921 --
4922 exception
4923 when others then
4924 --
4925 fnd_file.put_line(fnd_file.log, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
4926 fnd_file.put_line(fnd_file.log, get_sqlerrm);
4927 --
4928 end;
4929 --
4930 end loop;
4931 --
4932 delete_session;
4933 commit;
4934 --
4935 -- retcode
4936 -- 0 : Success
4937 -- 1 : Warning
4938 -- 2 : Error
4939 --
4940 retcode := 0;
4941 --
4942 end do_finalize;
4943 --
4944 -- -------------------------------------------------------------------------
4945 -- do_approve (Multiple Transaction)
4946 -- -------------------------------------------------------------------------
4947 procedure do_approve(
4948 errbuf out nocopy varchar2,
4949 retcode out nocopy varchar2,
4950 p_payroll_action_id in number)
4951 is
4952 --
4953 l_effective_date date;
4954 --
4955 cursor csr_assact
4956 is
4957 select /*+ ORDERED */
4958 assact.action_information_id,
4959 assact.object_version_number,
4960 pp.full_name,
4961 pa.assignment_number
4962 from pay_assignment_actions paa,
4963 pay_jp_isdf_assact_v assact,
4964 per_all_assignments_f pa,
4965 per_all_people_f pp
4966 where paa.payroll_action_id = p_payroll_action_id
4967 and paa.action_status = 'C'
4968 and assact.assignment_action_id = paa.assignment_action_id
4969 and assact.transaction_status = 'F'
4970 and pa.assignment_id = assact.assignment_id
4971 and assact.effective_date
4972 between pa.effective_start_date and pa.effective_end_date
4973 and pp.person_id = pa.person_id
4974 and assact.effective_date
4975 between pp.effective_start_date and pp.effective_end_date
4976 order by lpad(pa.assignment_number,10,' '),
4977 pp.full_name;
4978 --
4979 begin
4980 --
4981 select effective_date
4982 into l_effective_date
4983 from pay_jp_isdf_pact_v
4984 where payroll_action_id = p_payroll_action_id;
4985 --
4986 insert_session(l_effective_date);
4987 commit;
4988 --
4989 fnd_file.put_line(fnd_file.output, 'Full Name Assignment Number');
4990 fnd_file.put_line(fnd_file.output, '---------------------------------------- ------------------------------');
4991 fnd_file.put_line(fnd_file.log, 'Full Name Assignment Number');
4992 fnd_file.put_line(fnd_file.log, '---------------------------------------- ------------------------------');
4993 --
4994 for l_rec in csr_assact loop
4995 --
4996 begin
4997 --
4998 do_approve(
4999 p_action_information_id => l_rec.action_information_id,
5000 p_object_version_number => l_rec.object_version_number);
5001 --
5002 commit;
5003 --
5004 fnd_file.put_line(fnd_file.output, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
5005 --
5006 exception
5007 when others then
5008 --
5009 fnd_file.put_line(fnd_file.log, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
5010 fnd_file.put_line(fnd_file.log, get_sqlerrm);
5011 --
5012 end;
5013 --
5014 end loop;
5015 --
5016 delete_session;
5017 commit;
5018 --
5019 -- retcode
5020 -- 0 : Success
5021 -- 1 : Warning
5022 -- 2 : Error
5023 --
5024 retcode := 0;
5025 --
5026 end do_approve;
5027 --
5028 -- -------------------------------------------------------------------------
5029 -- do_transfer (Multiple Transaction)
5030 -- -------------------------------------------------------------------------
5031 procedure do_transfer(
5032 errbuf out nocopy varchar2,
5033 retcode out nocopy varchar2,
5034 p_payroll_action_id in number,
5035 p_transfer_date in varchar2,
5036 p_expire_after_transfer in varchar2 default 'N')
5037 is
5038 --
5039 l_effective_date date;
5040 --
5041 cursor csr_assact
5042 is
5043 select /*+ ORDERED */
5044 assact.action_information_id,
5045 assact.object_version_number,
5046 pp.full_name,
5047 pa.assignment_number
5048 from pay_assignment_actions paa,
5049 pay_jp_isdf_assact_v assact,
5050 per_all_assignments_f pa,
5051 per_all_people_f pp
5052 where paa.payroll_action_id = p_payroll_action_id
5053 and paa.action_status = 'C'
5054 and assact.assignment_action_id = paa.assignment_action_id
5055 and assact.transaction_status = 'A'
5056 and assact.transfer_status = 'U'
5057 and pa.assignment_id = assact.assignment_id
5058 and assact.effective_date
5059 between pa.effective_start_date and pa.effective_end_date
5060 and pp.person_id = pa.person_id
5061 and assact.effective_date
5062 between pp.effective_start_date and pp.effective_end_date
5063 order by lpad(pa.assignment_number,10,' '),
5064 pp.full_name;
5065 --
5066 begin
5067 --
5068 select effective_date
5069 into l_effective_date
5070 from pay_jp_isdf_pact_v
5071 where payroll_action_id = p_payroll_action_id;
5072 --
5073 if p_transfer_date is not null then
5074 l_effective_date := fnd_date.canonical_to_date(p_transfer_date);
5075 end if;
5076 --
5077 insert_session(l_effective_date);
5078 commit;
5079 --
5080 fnd_file.put_line(fnd_file.output, 'Full Name Assignment Number');
5081 fnd_file.put_line(fnd_file.output, '---------------------------------------- ------------------------------');
5082 fnd_file.put_line(fnd_file.log, 'Full Name Assignment Number');
5083 fnd_file.put_line(fnd_file.log, '---------------------------------------- ------------------------------');
5084 --
5085 for l_rec in csr_assact loop
5086 --
5087 begin
5088 --
5089 do_transfer(
5090 p_action_information_id => l_rec.action_information_id,
5091 p_object_version_number => l_rec.object_version_number,
5092 p_transfer_date => l_effective_date,
5093 p_create_session => false,
5094 p_expire_after_transfer => p_expire_after_transfer);
5095 --
5096 commit;
5097 --
5098 fnd_file.put_line(fnd_file.output, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
5099 --
5100 exception
5101 when others then
5102 --
5103 fnd_file.put_line(fnd_file.log, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
5104 fnd_file.put_line(fnd_file.log, get_sqlerrm);
5105 --
5106 end;
5107 --
5108 end loop;
5109 --
5110 delete_session;
5111 commit;
5112 --
5113 -- retcode
5114 -- 0 : Success
5115 -- 1 : Warning
5116 -- 2 : Error
5117 --
5118 retcode := 0;
5119 --
5120 end do_transfer;
5121 --
5122 -- -------------------------------------------------------------------------
5123 -- do_expire (Multiple Transaction)
5124 -- -------------------------------------------------------------------------
5125 procedure do_expire(
5126 errbuf out nocopy varchar2,
5127 retcode out nocopy varchar2,
5128 p_payroll_action_id in number,
5129 p_expiry_date in varchar2,
5130 p_mode in varchar2 default null)
5131 is
5132 --
5133 l_effective_date date;
5134 --
5135 cursor csr_assact
5136 is
5137 select /*+ ORDERED */
5138 assact.action_information_id,
5139 assact.object_version_number,
5140 pp.full_name,
5141 pa.assignment_number
5142 from pay_assignment_actions paa,
5143 pay_jp_isdf_assact_v assact,
5144 per_all_assignments_f pa,
5145 per_all_people_f pp
5146 where paa.payroll_action_id = p_payroll_action_id
5147 and paa.action_status = 'C'
5148 and assact.assignment_action_id = paa.assignment_action_id
5149 and assact.transaction_status = 'A'
5150 and assact.transfer_status = 'T'
5151 and pa.assignment_id = assact.assignment_id
5152 and assact.effective_date
5153 between pa.effective_start_date and pa.effective_end_date
5154 and pp.person_id = pa.person_id
5155 and assact.effective_date
5156 between pp.effective_start_date and pp.effective_end_date
5157 order by lpad(pa.assignment_number,10,' '),
5158 pp.full_name;
5159 --
5160 begin
5161 --
5162 select effective_date
5163 into l_effective_date
5164 from pay_jp_isdf_pact_v
5165 where payroll_action_id = p_payroll_action_id;
5166 --
5167 if p_expiry_date is not null then
5168 l_effective_date := fnd_date.canonical_to_date(p_expiry_date);
5169 end if;
5170 --
5171 insert_session(l_effective_date);
5172 commit;
5173 --
5174 fnd_file.put_line(fnd_file.output, 'Full Name Assignment Number');
5175 fnd_file.put_line(fnd_file.output, '---------------------------------------- ------------------------------');
5176 fnd_file.put_line(fnd_file.log, 'Full Name Assignment Number');
5177 fnd_file.put_line(fnd_file.log, '---------------------------------------- ------------------------------');
5178 --
5179 for l_rec in csr_assact loop
5180 --
5181 begin
5182 --
5183 do_expire(
5184 p_action_information_id => l_rec.action_information_id,
5185 p_object_version_number => l_rec.object_version_number,
5186 p_expiry_date => l_effective_date,
5187 p_create_session => false,
5188 p_mode => p_mode);
5189 --
5190 commit;
5191 --
5192 fnd_file.put_line(fnd_file.output, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
5193 --
5194 exception
5195 when others then
5196 --
5197 fnd_file.put_line(fnd_file.log, rpad(l_rec.full_name, 40) || ' ' || rpad(l_rec.assignment_number, 30));
5198 fnd_file.put_line(fnd_file.log, get_sqlerrm);
5199 --
5200 end;
5201 --
5202 end loop;
5203 --
5204 delete_session;
5205 commit;
5206 --
5207 -- retcode
5208 -- 0 : Success
5209 -- 1 : Warning
5210 -- 2 : Error
5211 --
5212 retcode := 0;
5213 --
5214 end do_expire;
5215 --
5216 end pay_jp_isdf_ss_pkg;