[Home] [Help]
PACKAGE BODY: APPS.HR_JP_DATA_MIGRATION_PKG
Source
1 PACKAGE BODY HR_JP_DATA_MIGRATION_PKG AS
2 /* $Header: hrjpdtmg.pkb 120.9 2010/11/02 20:46:03 keyazawa ship $ */
3 --
4 --
5 c_package constant varchar2(31) := 'hr_jp_data_migration_pkg.';
6 --
7 c_legislation_code constant varchar2(2) := 'JP';
8 c_commit_num constant number := 1000;
9 c_skip_warning varchar2(10) := 'FALSE';
10 --
11 c_mig_smr_sd date := to_date('2007/04/01','YYYY/MM/DD');
12 c_mig_dep_sd date := to_date('2011/01/01','YYYY/MM/DD');
13 --
14 c_com_hi_smr_elm pay_element_types_f.element_name%type := 'COM_HI_SMR_INFO';
15 c_am_iv pay_input_values_f.name%type := 'APPLY_MTH';
16 c_mr_iv pay_input_values_f.name%type := 'REVISED_MR';
17 c_mr_o_iv pay_input_values_f.name%type := 'PRIOR_MR';
18 c_smr_iv pay_input_values_f.name%type := 'REVISED_SMR';
19 c_smr_o_iv pay_input_values_f.name%type := 'PRIOR_SMR';
20 c_at_iv pay_input_values_f.name%type := 'APPLY_TYPE';
21 --
22 c_com_hi_smr_elm_id number;
23 c_am_iv_id number;
24 c_mr_iv_id number;
25 c_mr_o_iv_id number;
26 c_smr_iv_id number;
27 c_smr_o_iv_id number;
28 c_at_iv_id number;
29 --
30 c_yea_dep_exm_elm_id number;
31 c_sec_sal_iv_id number;
32 --
33 c_com_smr_tbl pay_user_tables.user_table_name%type := 'T_COM_SMR';
34 c_hi_smr_col pay_user_columns.user_column_name%type := 'HI_SMR';
35 --
36 c_new_smr_min_high number := 93000;
37 c_new_smr_max_low number := 1005000;
38 --
39 c_fut_exist_mesg fnd_new_messages.message_text%type;
40 c_already_upd_mesg fnd_new_messages.message_text%type;
41 c_fut_am_mesg fnd_new_messages.message_text%type;
42 c_am_null_mesg fnd_new_messages.message_text%type;
43 c_mr_null_mesg fnd_new_messages.message_text%type;
44 --
45 c_dep_fut_cei_exist_mesg fnd_new_messages.message_text%type;
46 c_dep_fut_cei_oe_exist_mesg fnd_new_messages.message_text%type;
47 c_dep_already_ee_upd_mesg fnd_new_messages.message_text%type;
48 c_dep_fut_ee_exist_mesg fnd_new_messages.message_text%type;
49 --
50 g_dml_num number;
51 --
52 g_qualify_ini_ass_id number;
53 g_migrate_ini_ass_id number;
54 --
55 c_yea_dep_exm_elm pay_element_types_f.element_name%type := 'YEA_DEP_EXM_INFO';
56 c_sec_sal_iv pay_input_values_f.name%type := 'SECOND_SAL_SUBMIT_FLAG';
57 --
58 g_ass_info t_ass_hi_smr_rec;
59 --
60 g_qualify_hi_smr_ass_tbl t_ass_hi_smr_tbl;
61 --
62 g_qualify_dep_ass_tbl t_ass_dep_tbl;
63 --
64 --------------------------------------------------------------------------------
65 -- p_mode is ELE_RR_COPY_TO then copy record pay_run_results and pay_run_result_values
66 -- from existing element to new element
67
68 PROCEDURE element_run_result_copy(
69 p_mode IN VARCHAR2,
70 p_parameter_name IN VARCHAR2,
71 p_parameter_value IN NUMBER)
72 --------------------------------------------------------------------------------
73 IS
74 l_element_type_id_from NUMBER;
75 l_element_type_id_to NUMBER;
76
77 CURSOR csr_related_iv IS
78 select hjp1.parameter_value iv_mode,
79 hjp2.parameter_name iv_name,
80 hjp2.parameter_value iv_id_to,
81 hjp3.parameter_value iv_id_from
82 from hr_jp_parameters hjp1,
83 hr_jp_parameters hjp2,
84 hr_jp_parameters hjp3
85 where hjp1.owner = p_parameter_name
86 and hjp2.owner = hjp1.parameter_value
87 and hjp2.parameter_name = hjp1.parameter_name
88 and hjp3.owner(+) = 'IV_COPY_FROM'
89 and hjp3.parameter_name(+) = hjp2.parameter_name;
90
91 --
92 BEGIN
93
94 hr_utility.set_location('Start ' || p_mode || p_parameter_name, 5);
95 if p_mode = 'ELE_RR_COPY_TO' then
96 -- find ELE_RR_COPY_FROM element_type_id
97 l_element_type_id_to := p_parameter_value;
98 --
99 l_element_type_id_from := hr_jp_parameters_pkg.get_parameter_value('ELE_RR_COPY_FROM',p_parameter_name);
100
101 if l_element_type_id_from is not null then
102 -- copy run result
103
104 insert into pay_run_results (
105 RUN_RESULT_ID,
106 ELEMENT_TYPE_ID,
107 ASSIGNMENT_ACTION_ID,
108 ENTRY_TYPE,
109 SOURCE_ID,
110 SOURCE_TYPE,
111 STATUS)
112 select /*+ INDEX(PRR_FROM PAY_RUN_RESULTS_N1) */
113 pay_run_results_s.nextval,
114 l_element_type_id_to,
115 prr_from.assignment_action_id,
116 prr_from.entry_type,
117 prr_from.source_id,
118 prr_from.source_type,
119 prr_from.status
120 from pay_run_results prr_from
121 where prr_from.element_type_id = l_element_type_id_from
122 and not exists(
123 select /*+ INDEX(PRR_TO PAY_RUN_RESULTS_N50) */
124 NULL
125 from pay_run_results prr_to
126 where prr_to.assignment_action_id = prr_from.assignment_action_id
127 and prr_to.element_type_id = l_element_type_id_to);
128
129 hr_utility.trace('Successefully created run_result');
130
131 -- get related input values
132 for rec_related_iv in csr_related_iv
133 loop
134 if rec_related_iv.iv_mode = 'IV_COPY_TO' then
135 insert into pay_run_result_values (
136 INPUT_VALUE_ID,
137 RUN_RESULT_ID,
138 RESULT_VALUE)
139 select /*+ ORDERED
140 INDEX(FROM_ELE_PRR PAY_RUN_RESULTS_N1)
141 INDEX(PRRV_FROM PAY_RUN_RESULT_VALUES_PK)
142 INDEX(TO_ELE_PRR PAY_RUN_RESULTS_N50)
143 USE_NL(prrv_from to_ele_prr) */
144 rec_related_iv.iv_id_to,
145 to_ele_prr.run_result_id,
146 prrv_from.result_value
147 from pay_run_results from_ele_prr,
148 pay_run_result_values prrv_from,
149 pay_run_results to_ele_prr
150 where from_ele_prr.element_type_id = l_element_type_id_from
151 and prrv_from.run_result_id = from_ele_prr.run_result_id
152 and prrv_from.input_value_id = rec_related_iv.iv_id_from
153 and to_ele_prr.assignment_action_id = from_ele_prr.assignment_action_id
154 and to_ele_prr.element_type_id = l_element_type_id_to
155 and not exists(
156 select NULL
157 from pay_run_result_values prrv_to
158 where prrv_to.run_result_id = to_ele_prr.run_result_id
159 and prrv_to.input_value_id = rec_related_iv.iv_id_to);
160
161 end if;
162 if rec_related_iv.iv_mode = 'ADD_NEW_IV' then
163 insert into pay_run_result_values (
164 INPUT_VALUE_ID,
165 RUN_RESULT_ID,
166 RESULT_VALUE)
167 select /*+ INDEX(TO_ELE_PRR PAY_RUN_RESULTS_N1) */
168 rec_related_iv.iv_id_to,
169 to_ele_prr.run_result_id,
170 NULL
171 from pay_run_results to_ele_prr
172 where to_ele_prr.element_type_id = l_element_type_id_to
173 and not exists(
174 select NULL
175 from pay_run_result_values prrv_to
176 where prrv_to.run_result_id = to_ele_prr.run_result_id
177 and prrv_to.input_value_id = rec_related_iv.iv_id_to);
178
179 end if;
180 end loop;
181 hr_utility.trace('Successefully created run_result_value');
182 end if;
183 end if;
184 END;
185
186 --------------------------------------------------------------------------------
187 -- p_mode is ADD_NEW_IV then insert pay_link_input_value and pay_element_entry_values_f
188 --
189 PROCEDURE add_new_input_value(
190 p_mode IN VARCHAR2,
191 p_parameter_name IN VARCHAR2,
192 p_parameter_value IN NUMBER)
193 --------------------------------------------------------------------------------
194 IS
195 l_input_value_id_to NUMBER;
196 l_costed_flag VARCHAR2(30);
197 l_total_upd_actions NUMBER := 0;
198
199 CURSOR csr_run_result_id IS
200 select /*+ ORDERED
201 INDEX(PIV PAY_INPUT_VALUES_F_PK)
202 INDEX(PET PAY_ELEMENT_TYPES_F_PK)
203 INDEX(PRR PAY_RUN_RESULTS_N1) */
204 prr.run_result_id run_result_id
205 from pay_input_values_f piv,
206 pay_element_types_f pet,
207 pay_run_results prr
208 where piv.input_value_id = p_parameter_value
209 and pet.element_type_id = piv.element_type_id
210 and piv.effective_start_date
211 between pet.effective_start_date and pet.effective_end_date
212 and prr.element_type_id = pet.element_type_id
213 and not exists(
214 select /*+ INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) */
215 NULL
216 from pay_run_result_values prrv
217 where prrv.run_result_id=prr.run_result_id
218 and prrv.input_value_id=l_input_value_id_to);
219
220 CURSOR csr_element_link_id IS
221 select /*+ ORDERED
222 INDEX(PIV PAY_INPUT_VALUES_F_PK)
223 INDEX(PET PAY_ELEMENT_TYPES_F_PK)
224 INDEX(PEL PAY_ELEMENT_LINKS_F_N7) */
225 pel.rowid row_id,
226 pel.element_link_id element_link_id,
227 pel.costable_type costable_type,
228 piv.name input_value_name,
229 piv.effective_start_date effective_start_date,
230 piv.effective_end_date effective_end_date,
231 piv.default_value default_value,
232 piv.max_value max_value,
233 piv.min_value min_value,
234 piv.warning_or_error warning_or_error
235 from pay_input_values_f piv,
236 pay_element_types_f pet,
237 pay_element_links_f pel
238 where piv.input_value_id = p_parameter_value
239 and pet.element_type_id = piv.element_type_id
240 and piv.effective_start_date
241 between pet.effective_start_date and pet.effective_end_date
242 and pel.element_type_id = pet.element_type_id
243 and pel.effective_start_date <= piv.effective_end_date
244 and pel.effective_end_date >= piv.effective_start_date;
245
246 CURSOR csr_element_entry_id(
247 p_element_link_id IN NUMBER,
248 p_input_value_id IN NUMBER)
249 IS
250 select /*+ INDEX(PEE PAY_ELEMENT_ENTRIES_F_N4) */
251 pee.rowid row_id,
252 pee.element_entry_id element_entry_id
253 from pay_element_entries_f pee
254 where pee.element_link_id = p_element_link_id
255 and not exists(select /*+ INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
256 NULL
257 from pay_element_entry_values_f peev
258 where peev.element_entry_id = pee.element_entry_id
259 and peev.input_value_id = p_input_value_id
260 and peev.effective_start_date = pee.effective_start_date
261 and peev.effective_end_date = pee.effective_end_date);
262
263 --
264 BEGIN
265
266 hr_utility.set_location('Start procedure in the hr_jp_data_migration_pkg' || p_mode || p_parameter_name, 5);
267 if p_mode = 'ADD_NEW_IV' then
268 l_input_value_id_to := p_parameter_value;
269
270 -- find element_link_id of parent element
271 for rec_element_link in csr_element_link_id
272 loop
273 if rec_element_link.costable_type in ('F', 'C', 'D')
274 and rec_element_link.input_value_name = hr_general.pay_value then
275 l_costed_flag := 'Y';
276 else
277 l_costed_flag := 'N';
278 end if;
279 insert into pay_link_input_values_f
280 (LINK_INPUT_VALUE_ID,
281 EFFECTIVE_START_DATE,
282 EFFECTIVE_END_DATE,
283 ELEMENT_LINK_ID,
284 INPUT_VALUE_ID,
285 COSTED_FLAG,
286 DEFAULT_VALUE,
287 MAX_VALUE,
288 MIN_VALUE,
289 WARNING_OR_ERROR,
290 LAST_UPDATE_DATE,
291 LAST_UPDATED_BY,
292 LAST_UPDATE_LOGIN,
293 CREATED_BY,
294 CREATION_DATE)
295 select PAY_LINK_INPUT_VALUES_S.nextval,
296 greatest(pel.effective_start_date,rec_element_link.effective_start_date),
297 least(pel.effective_end_date,rec_element_link.effective_end_date),
298 rec_element_link.element_link_id,
299 l_input_value_id_to,
300 l_costed_flag,
301 rec_element_link.default_value,
302 rec_element_link.max_value,
303 rec_element_link.min_value,
304 rec_element_link.warning_or_error,
305 pel.last_update_date,
306 pel.last_updated_by,
307 pel.last_update_login,
308 NULL,
309 pel.creation_date
310 from pay_element_links_f pel
311 where pel.rowid=rec_element_link.row_id
312 and not exists(
313 select null
314 from pay_link_input_values_f
315 where element_link_id = rec_element_link.element_link_id
316 and input_value_id = l_input_value_id_to);
317 hr_utility.trace('Successefully created link_input_value '|| to_char(rec_element_link.element_link_id));
318
319 for rec_element_entry in csr_element_entry_id(rec_element_link.element_link_id,l_input_value_id_to) loop
320 insert into pay_element_entry_values_f(
321 ELEMENT_ENTRY_VALUE_ID,
322 EFFECTIVE_START_DATE,
323 EFFECTIVE_END_DATE,
324 INPUT_VALUE_ID,
325 ELEMENT_ENTRY_ID,
326 SCREEN_ENTRY_VALUE)
327 select pay_element_entry_values_s.nextval,
328 pee.effective_start_date,
329 pee.effective_end_date,
330 l_input_value_id_to,
331 pee.element_entry_id,
332 NULL
333 from pay_element_entries_f pee
334 where pee.rowid=rec_element_entry.row_id;
335
336 l_total_upd_actions := l_total_upd_actions + 1;
337 if l_total_upd_actions > 1000 then
338 commit;
339 l_total_upd_actions := 0;
340 end if;
341 end loop;
342 hr_utility.trace('Successefully created element_entry_value');
343 if l_total_upd_actions > 0 then
344 l_total_upd_actions := 0;
345 commit;
346 end if;
347 end loop;
348
349 -- find run_result_id of parent element
350 for rec_run_result_id in csr_run_result_id
351 loop
352 insert into pay_run_result_values (
353 INPUT_VALUE_ID,
354 RUN_RESULT_ID,
355 RESULT_VALUE)
356 values(l_input_value_id_to,
357 rec_run_result_id.run_result_id,
358 NULL);
359
360 l_total_upd_actions := l_total_upd_actions + 1;
361 if l_total_upd_actions > 1000 then
362 commit;
363 l_total_upd_actions := 0;
364 end if;
365 end loop;
366 hr_utility.trace('Successefully created run_result_value '|| to_char(l_input_value_id_to));
367
368 if l_total_upd_actions > 0 then
369 commit;
370 end if;
371 end if;
372 END;
373
374 --------------------------------------------------------------------------------
375 -- This procedure will update or delete element entry for obsolete elements in R11i.
376 -- -If the element entry exist on sysdate then effective_end_date set to sysdate.
377 -- -If the element entry exist later than sysdate then these records will be purged.
378 --
379 PROCEDURE end_element_entry(
380 p_mode IN VARCHAR2,
381 p_parameter_name IN VARCHAR2,
382 p_parameter_value IN NUMBER,
383 p_session_date IN DATE)
384 --------------------------------------------------------------------------------
385 IS
386 l_element_type_id NUMBER;
387 l_delete_mode VARCHAR2(10);
388 l_target_date DATE;
389 l_total_upd_actions NUMBER := 0;
390 v_entry_id NUMBER;
391
392 CURSOR csr_element_link_id IS
393 select element_link_id
394 from pay_element_links_f
395 where element_type_id=l_element_type_id;
396
397 CURSOR csr_element_entry_id(
398 p_element_link_id IN NUMBER,
399 p_session_date IN DATE)
400 IS
401 select pee.element_entry_id,
402 pee.effective_start_date
403 from pay_element_entries_f pee
404 where pee.element_link_id=p_element_link_id
405 and p_session_date<pee.effective_end_date;
406
407 CURSOR csr_chk_entry(
408 p_element_entry_id IN NUMBER,
409 p_session_date IN DATE)
410 IS
411 select pee.element_entry_id
412 from pay_element_entries_f pee
413 where pee.element_entry_id=p_element_entry_id
414 and p_session_date
415 between pee.effective_start_date and pee.effective_end_date;
416
417 BEGIN
418 hr_utility.set_location('Start procedure in the hr_jp_data_migration_pkg' || p_mode || p_parameter_name, 5);
419 if p_mode = 'ELE_END_ENTRY' then
420 l_element_type_id := p_parameter_value;
421 for rec_element_link in csr_element_link_id
422 loop
423 for rec_element_entry in csr_element_entry_id(rec_element_link.element_link_id,p_session_date) loop
424 open csr_chk_entry(rec_element_entry.element_entry_id,rec_element_entry.effective_start_date);
425 fetch csr_chk_entry into v_entry_id;
426 if csr_chk_entry%found then
427 close csr_chk_entry;
428 if rec_element_entry.effective_start_date > p_session_date then
429 l_delete_mode := 'ZAP';
430 l_target_date := rec_element_entry.effective_start_date;
431 else
432 l_delete_mode := 'DELETE';
433 l_target_date := p_session_date;
434 end if;
435 hr_entry_api.delete_element_entry(l_delete_mode,l_target_date,rec_element_entry.element_entry_id);
436 l_total_upd_actions := l_total_upd_actions + 1;
437 else
438 close csr_chk_entry;
439 end if;
440 if l_total_upd_actions > 1000 then
441 commit;
442 l_total_upd_actions := 0;
443 end if;
444 end loop;
445 if l_total_upd_actions > 0 then
446 commit;
447 end if;
448 end loop;
449 end if;
450 END;
451 --
452 -- -------------------------------------------------------------------------
453 -- get_ass_info
454 -- -------------------------------------------------------------------------
455 function get_ass_info(
456 p_assignment_id in number,
457 p_effective_date in date)
458 return t_ass_hi_smr_rec
459 is
460 --
461 l_proc varchar2(80) := c_package||'get_ass_info';
462 --
463 cursor csr_ass
464 is
465 select /*+ ORDERED
466 INDEX(PA PER_ASSIGNMENTS_F_PK) */
467 pbg.business_group_id bg_id,
468 pbg.name bg_name,
469 pa.assignment_number ass_num
470 from per_all_assignments_f pa,
471 per_business_groups_perf pbg
472 where pa.assignment_id = p_assignment_id
473 and p_effective_date
474 between pa.effective_start_date and pa.effective_end_date
475 and pbg.business_group_id = pa.business_group_id;
476 --
477 l_csr_ass csr_ass%rowtype;
478 --
479 begin
480 --
481 if g_debug then
482 hr_utility.set_location(l_proc,0);
483 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
484 end if;
485 --
486 if g_ass_info.ass_id <> p_assignment_id
487 or g_ass_info.ass_id is null then
488 --
489 open csr_ass;
490 fetch csr_ass into l_csr_ass;
491 close csr_ass;
492 --
493 g_ass_info.ass_id := p_assignment_id;
494 g_ass_info.ass_num := l_csr_ass.ass_num;
495 g_ass_info.bg_id := l_csr_ass.bg_id;
496 g_ass_info.bg_name := l_csr_ass.bg_name;
497 --
498 end if;
499 --
500 if g_debug then
501 hr_utility.set_location(l_proc,1000);
502 end if;
503 --
504 return g_ass_info;
505 --
506 end get_ass_info;
507 --
508 -- -------------------------------------------------------------------------
509 -- set_hi_smr_id
510 -- -------------------------------------------------------------------------
511 procedure set_hi_smr_id
512 is
513 --
514 l_proc varchar2(80) := c_package||'set_hi_smr_id';
515 --
516 begin
517 --
518 if g_debug then
519 hr_utility.set_location(l_proc,0);
520 end if;
521 --
522 c_com_hi_smr_elm_id := hr_jp_id_pkg.element_type_id(c_com_hi_smr_elm,null,c_legislation_code,c_skip_warning);
523 c_am_iv_id := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_am_iv,c_skip_warning);
524 c_mr_iv_id := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_mr_iv,c_skip_warning);
525 c_mr_o_iv_id := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_mr_o_iv,c_skip_warning);
526 c_smr_iv_id := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_smr_iv,c_skip_warning);
527 c_smr_o_iv_id := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_smr_o_iv,c_skip_warning);
528 c_at_iv_id := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_at_iv,c_skip_warning);
529 --
530 if g_debug then
531 hr_utility.trace('c_com_hi_smr_elm_id : '||to_char(c_com_hi_smr_elm_id));
532 hr_utility.trace('c_am_iv_id : '||to_char(c_am_iv_id));
533 hr_utility.trace('c_mr_iv_id : '||to_char(c_mr_iv_id));
534 hr_utility.trace('c_mr_o_iv_id : '||to_char(c_mr_o_iv_id));
535 hr_utility.trace('c_smr_iv_id : '||to_char(c_smr_iv_id));
536 hr_utility.trace('c_smr_o_iv_id : '||to_char(c_smr_o_iv_id));
537 hr_utility.trace('c_at_iv_id : '||to_char(c_at_iv_id));
538 hr_utility.set_location(l_proc,1000);
539 end if;
540 --
541 end set_hi_smr_id;
542 --
543 -- -------------------------------------------------------------------------
544 -- set_hi_smr_mesg
545 -- -------------------------------------------------------------------------
546 procedure set_hi_smr_mesg
547 is
548 --
549 l_proc varchar2(80) := c_package||'set_hi_smr_mesg';
550 --
551 begin
552 --
553 if g_debug then
554 hr_utility.set_location(l_proc,0);
555 end if;
556 --
557 fnd_message.set_name('PAY','PAY_JP_MIG_SMR_FUT_EXIST');
558 c_fut_exist_mesg := fnd_message.get;
559 --
560 fnd_message.set_name('PAY','PAY_JP_MIG_SMR_ALREADY_UPD');
561 c_already_upd_mesg := fnd_message.get;
562 --
563 fnd_message.set_name('PAY','PAY_JP_MIG_SMR_FUT_AM');
564 c_fut_am_mesg := fnd_message.get;
565 --
566 fnd_message.set_name('PAY','PAY_JP_MIG_SMR_AM_NULL');
567 c_am_null_mesg := fnd_message.get;
568 --
569 fnd_message.set_name('PAY','PAY_JP_MIG_SMR_MR_NULL');
570 c_mr_null_mesg := fnd_message.get;
571 --
572 if g_debug then
573 hr_utility.trace('c_fut_exist_mesg : '||c_fut_exist_mesg);
574 hr_utility.trace('c_already_upd_mesg : '||c_already_upd_mesg);
575 hr_utility.trace('c_fut_am_mesg : '||c_fut_am_mesg);
576 hr_utility.trace('c_am_null_mesg : '||c_am_null_mesg);
577 hr_utility.trace('c_mr_null_mesg : '||c_mr_null_mesg);
578 hr_utility.set_location(l_proc,1000);
579 end if;
580 --
581 end set_hi_smr_mesg;
582 --
583 -- -------------------------------------------------------------------------
584 -- set_dep_id
585 -- -------------------------------------------------------------------------
586 procedure set_dep_id
587 is
588 --
589 l_proc varchar2(80) := c_package||'set_dep_id';
590 --
591 begin
592 --
593 if g_debug then
594 hr_utility.set_location(l_proc,0);
595 end if;
596 --
597 c_yea_dep_exm_elm_id := hr_jp_id_pkg.element_type_id(c_yea_dep_exm_elm,null,c_legislation_code,c_skip_warning);
598 c_sec_sal_iv_id := hr_jp_id_pkg.input_value_id(c_yea_dep_exm_elm_id,c_sec_sal_iv,c_skip_warning);
599 --
600 if g_debug then
601 hr_utility.trace('c_yea_dep_exm_elm_id : '||to_char(c_yea_dep_exm_elm_id));
602 hr_utility.trace('c_sec_sal_iv_id : '||to_char(c_sec_sal_iv_id));
603 hr_utility.set_location(l_proc,1000);
604 end if;
605 --
606 end set_dep_id;
607 --
608 -- -------------------------------------------------------------------------
609 -- set_dep_mesg
610 -- -------------------------------------------------------------------------
611 procedure set_dep_mesg
612 is
613 --
614 l_proc varchar2(80) := c_package||'set_dep_mesg';
615 --
616 begin
617 --
618 if g_debug then
619 hr_utility.set_location(l_proc,0);
620 end if;
621 --
622 fnd_message.set_name('PAY','PAY_JP_MIG_CEI_FUT_EXIST');
623 c_dep_fut_cei_exist_mesg := fnd_message.get;
624 --
625 fnd_message.set_name('PAY','PAY_JP_MIG_CEI_OE_FUT_EXIST');
626 c_dep_fut_cei_oe_exist_mesg := fnd_message.get;
627 --
628 fnd_message.set_name('PAY','PAY_JP_MIG_SMR_ALREADY_UPD');
629 c_dep_already_ee_upd_mesg := fnd_message.get;
630 --
631 fnd_message.set_name('PAY','PAY_JP_MIG_SMR_FUT_EXIST');
632 c_dep_fut_ee_exist_mesg := fnd_message.get;
633 --
634 if g_debug then
635 hr_utility.trace('c_dep_fut_cei_exist_mesg : '||c_dep_fut_cei_exist_mesg);
636 hr_utility.trace('c_dep_fut_cei_oe_exist_mesg : '||c_dep_fut_cei_oe_exist_mesg);
637 hr_utility.trace('c_dep_already_ee_upd_mesg : '||c_dep_already_ee_upd_mesg);
638 hr_utility.trace('c_dep_fut_ee_exist_mesg : '||c_dep_fut_ee_exist_mesg);
639 hr_utility.set_location(l_proc,1000);
640 end if;
641 --
642 end set_dep_mesg;
643 --
644 -- -------------------------------------------------------------------------
645 -- get_sqlerrm
646 -- -------------------------------------------------------------------------
647 function get_sqlerrm
648 return varchar2
649 is
650 begin
651 --
652 if sqlcode = -20001 then
653 --
654 declare
655 l_sqlerrm varchar2(2000) := fnd_message.get;
656 begin
657 if l_sqlerrm is not null then
658 return l_sqlerrm;
659 else
660 return sqlerrm;
661 end if;
662 end;
663 --
664 else
665 return sqlerrm;
666 end if;
667 --
668 end get_sqlerrm;
669 --
670 -- -------------------------------------------------------------------------
671 -- get_mig_date
672 -- -------------------------------------------------------------------------
673 function get_mig_date
674 return date
675 is
676 --
677 l_proc varchar2(80) := c_package||'get_mig_date';
678 --
679 begin
680 --
681 if g_debug then
682 hr_utility.set_location(l_proc,0);
683 hr_utility.trace('g_mig_date : '||to_char(g_mig_date,'YYYY/MM/DD'));
684 end if;
685 --
686 if g_debug then
687 hr_utility.set_location(l_proc,1000);
688 end if;
689 --
690 return g_mig_date;
691 --
692 end get_mig_date;
693 --
694 -- -------------------------------------------------------------------------
695 -- insert_session
696 -- -------------------------------------------------------------------------
697 procedure insert_session(
698 p_effective_date in date)
699 is
700 --
701 l_rowid rowid;
702 --
703 cursor csr_session
704 is
705 select rowid
706 from fnd_sessions
707 where session_id = userenv('sessionid')
708 for update nowait;
709 --
710 begin
711 --
712 open csr_session;
713 fetch csr_session into l_rowid;
714 --
715 if csr_session%notfound then
716 --
717 insert into fnd_sessions(
718 session_id,
719 effective_date)
720 values(
721 userenv('sessionid'),
722 p_effective_date);
723 --
724 else
725 --
726 update fnd_sessions
727 set effective_date = p_effective_date
728 where rowid = l_rowid;
729 --
730 end if;
731 --
732 close csr_session;
733 --
734 end insert_session;
735 --
736 -- -------------------------------------------------------------------------
737 -- delete_session
738 -- -------------------------------------------------------------------------
739 procedure delete_session
740 is
741 begin
742 --
743 delete
744 from fnd_sessions
745 where session_id = userenv('sessionid');
746 --
747 end delete_session;
748 --
749 -- -------------------------------------------------------------------------
750 -- qualify_hi_smr_hd
751 -- -------------------------------------------------------------------------
752 -- run by qualify_hi_smr_data
753 procedure qualify_hi_smr_hd(
754 p_assignment_id in number)
755 is
756 --
757 l_proc varchar2(80) := c_package||'qualify_hi_smr_hd';
758 --
759 begin
760 --
761 if g_debug then
762 hr_utility.set_location(l_proc,0);
763 hr_utility.trace('g_qualify_ini_ass_id : '||to_char(g_qualify_ini_ass_id));
764 end if;
765 --
766 if g_qualify_ini_ass_id is null
767 or g_qualify_ini_ass_id = p_assignment_id then
768 --
769 fnd_file.put_line(fnd_file.log, 'Business Group Name Assignment Number Message');
770 fnd_file.put_line(fnd_file.log, '------------------------------ ------------------------------ --------------------------------------------------------------------------------');
771 --
772 g_qualify_ini_ass_id := p_assignment_id;
773 --
774 end if;
775 --
776 if g_debug then
777 hr_utility.trace('g_qualify_ini_ass_id : '||to_char(g_qualify_ini_ass_id));
778 hr_utility.set_location(l_proc,1000);
779 end if;
780 --
781 end qualify_hi_smr_hd;
782 --
783 -- -------------------------------------------------------------------------
784 -- migrate_hi_smr_hd
785 -- -------------------------------------------------------------------------
786 -- run by migrate_hi_smr_data
787 procedure migrate_hi_smr_hd(
788 p_assignment_id in number)
789 is
790 --
791 l_proc varchar2(80) := c_package||'migrate_hi_smr_hd';
792 --
793 begin
794 --
795 if g_debug then
796 hr_utility.set_location(l_proc,0);
797 hr_utility.trace('g_migrate_ini_ass_id : '||to_char(g_migrate_ini_ass_id));
798 end if;
799 --
800 if g_migrate_ini_ass_id is null
801 or g_migrate_ini_ass_id = p_assignment_id then
802 --
803 fnd_file.put_line(fnd_file.output, 'Business Group Name Assignment Number MR SMR Exp SMR');
804 fnd_file.put_line(fnd_file.output, '------------------------------ ------------------------------ ------------ ---------- ----------');
805 --
806 g_migrate_ini_ass_id := p_assignment_id;
807 --
808 end if;
809 --
810 if g_debug then
811 hr_utility.trace('g_migrate_ini_ass_id : '||to_char(g_migrate_ini_ass_id));
812 hr_utility.set_location(l_proc,1000);
813 end if;
814 --
815 end migrate_hi_smr_hd;
816 --
817 -- -------------------------------------------------------------------------
818 -- init_def_hi_smr_data
819 -- -------------------------------------------------------------------------
820 -- run by qualify_hi_smr_data
821 procedure init_def_hi_smr_data
822 is
823 --
824 l_proc varchar2(80) := c_package||'init_def_hi_smr_data';
825 --
826 begin
827 --
828 if g_debug then
829 hr_utility.set_location(l_proc,0);
830 hr_utility.trace('g_legislation_code : '||g_legislation_code);
831 end if;
832 --
833 -- temporary solution because of no initialize.
834 -- run once for each thread.
835 if g_legislation_code is null
836 or g_legislation_code <> c_legislation_code then
837 --
838 -- no support of legsilative parameter at this moment.
839 g_skip_qualify := 'N';
840 g_upd_mode := 'UPDATE';
841 g_mig_date := c_mig_smr_sd;
842 --
843 set_hi_smr_id;
844 set_hi_smr_mesg;
845 --
846 g_legislation_code := c_legislation_code;
847 --
848 end if;
849 --
850 if g_debug then
851 hr_utility.trace('g_legislation_code : '||g_legislation_code);
852 hr_utility.set_location(l_proc,1000);
853 end if;
854 --
855 end init_def_hi_smr_data;
856 --
857 -- -------------------------------------------------------------------------
858 -- val_mig_smr_assact
859 -- -------------------------------------------------------------------------
860 procedure val_mig_smr_assact(
861 p_business_group_id in number,
862 p_business_group_name in varchar2,
863 p_assignment_id in number,
864 p_assignment_number in varchar2,
865 p_session_date in date,
866 p_valid_delete in out nocopy varchar2)
867 is
868 --
869 l_proc varchar2(80) := c_package||'val_mig_smr_assact';
870 --
871 l_am_eev pay_element_entry_values_f.screen_entry_value%type;
872 l_mr_eev pay_element_entry_values_f.screen_entry_value%type;
873 l_ee_esd date;
874 l_ee_upd_id number;
875 l_ft_ee_esd date;
876 l_am_date date;
877 --
878 l_valid_delete varchar2(1) := 'N';
879 --
880 cursor csr_ee_esd
881 is
882 select /*+ ORDERED
883 USE_NL(PLIV, PEE)
884 INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
885 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
886 pee.effective_start_date,
887 pee.updating_action_id
888 from pay_link_input_values_f pliv,
889 pay_element_entries_f pee
890 where pliv.input_value_id = c_smr_iv_id
891 and p_session_date
892 between pliv.effective_start_date and pliv.effective_end_date
893 and pee.element_link_id = pliv.element_link_id
894 and pee.assignment_id = p_assignment_id
895 and p_session_date
896 between pee.effective_start_date and pee.effective_end_date;
897 --
898 cursor csr_ft_ee
899 is
900 select /*+ ORDERED
901 USE_NL(PLIV, PEE)
902 INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
903 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
904 pee.effective_start_date
905 from pay_link_input_values_f pliv,
906 pay_element_entries_f pee
907 where pliv.input_value_id = c_smr_iv_id
908 and p_session_date
909 between pliv.effective_start_date and pliv.effective_end_date
910 and pee.element_link_id = pliv.element_link_id
911 and pee.assignment_id = p_assignment_id
912 and pee.effective_start_date > p_session_date;
913 --
914 begin
915 --
916 if g_detail_debug then
917 hr_utility.set_location(l_proc,0);
918 hr_utility.trace(p_business_group_id ||' : '||p_assignment_id||'('||p_assignment_number||')');
919 end if;
920 --
921 if p_valid_delete = 'N' then
922 --
923 -- skip ee not exist.
924 -- skip already updated (manual update)
925 --
926 open csr_ee_esd;
927 fetch csr_ee_esd into l_ee_esd, l_ee_upd_id;
928 close csr_ee_esd;
929 --
930 if l_ee_esd is not null then
931 --
932 l_valid_delete := 'Y';
933 --
934 end if;
935 --
936 if g_skip_manual_upd = 'Y' then
937 --
938 if l_ee_esd = p_session_date
939 and l_ee_esd is not null
940 and l_ee_upd_id is null then
941 --
942 l_valid_delete := 'N';
943 --
944 if g_sql_run = 'Y' then
945 --
946 if g_log = 'Y' then
947 --
948 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_already_upd_mesg);
949 --
950 end if;
951 --
952 else
953 --
954 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_already_upd_mesg);
955 --
956 end if;
957 --
958 end if;
959 --
960 end if;
961 --
962 if g_detail_debug then
963 hr_utility.set_location(l_proc,10);
964 hr_utility.trace('skip manual upd : l_valid_delete : '||l_valid_delete);
965 end if;
966 --
967 -- skip future entry exists
968 --
969 if l_valid_delete = 'Y' then
970 --
971 open csr_ft_ee;
972 fetch csr_ft_ee into l_ft_ee_esd;
973 close csr_ft_ee;
974 --
975 if l_ft_ee_esd is not null then
976 --
977 l_valid_delete := 'N';
978 --
979 if g_sql_run = 'Y' then
980 --
981 if g_log = 'Y' then
982 --
983 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_fut_exist_mesg);
984 --
985 end if;
986 --
987 else
988 --
989 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_fut_exist_mesg);
990 --
991 end if;
992 --
993 end if;
994 --
995 end if;
996 --
997 if g_detail_debug then
998 hr_utility.set_location(l_proc,20);
999 hr_utility.trace('skip future entry : l_valid_delete : '||l_valid_delete);
1000 end if;
1001 --
1002 -- skip in update mode applied month is future (>= p_session_date)
1003 --
1004 if l_valid_delete = 'Y'
1005 and g_upd_mode <> 'OVERRIDE' then
1006 --
1007 l_am_eev := pay_jp_balance_pkg.get_entry_value_char(
1008 p_input_value_id => c_am_iv_id,
1009 p_assignment_id => p_assignment_id,
1010 p_effective_date => p_session_date);
1011 --
1012 if l_am_eev is not null then
1013 --
1014 l_am_date := to_date(l_am_eev||'01','YYYYMMDD');
1015 --
1016 if trunc(l_am_date,'DD') >= trunc(p_session_date,'DD') then
1017 --
1018 l_valid_delete := 'N';
1019 --
1020 if g_sql_run = 'Y' then
1021 --
1022 if g_log = 'Y' then
1023 --
1024 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_fut_am_mesg);
1025 --
1026 end if;
1027 --
1028 else
1029 --
1030 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_fut_am_mesg);
1031 --
1032 end if;
1033 --
1034 end if;
1035 --
1036 else
1037 --
1038 l_valid_delete := 'N';
1039 --
1040 if g_sql_run = 'Y' then
1041 --
1042 if g_log = 'Y' then
1043 --
1044 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_am_null_mesg);
1045 --
1046 end if;
1047 --
1048 else
1049 --
1050 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_am_null_mesg);
1051 --
1052 end if;
1053 --
1054 end if;
1055 --
1056 end if;
1057 --
1058 if g_detail_debug then
1059 hr_utility.set_location(l_proc,30);
1060 hr_utility.trace('skip applied month in future : l_valid_delete : '||l_valid_delete);
1061 end if;
1062 --
1063 -- skip mr is null.
1064 -- target only assignment, who belongs to new mr range.
1065 --
1066 if l_valid_delete = 'Y' then
1067 --
1068 l_mr_eev := pay_jp_balance_pkg.get_entry_value_char(
1069 p_input_value_id => c_mr_iv_id,
1070 p_assignment_id => p_assignment_id,
1071 p_effective_date => p_session_date);
1072 --
1073 if l_mr_eev is null then
1074 --
1075 l_valid_delete := 'N';
1076 --
1077 if g_sql_run = 'Y' then
1078 --
1079 if g_log = 'Y' then
1080 --
1081 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_mr_null_mesg);
1082 --
1083 end if;
1084 --
1085 else
1086 --
1087 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_mr_null_mesg);
1088 --
1089 end if;
1090 --
1091 else
1092 --
1093 if g_skip_out_range_upd = 'Y' then
1094 --
1095 if (to_number(l_mr_eev) >= c_new_smr_min_high
1096 and to_number(l_mr_eev) < c_new_smr_max_low) then
1097 --
1098 l_valid_delete := 'N';
1099 --
1100 end if;
1101 --
1102 end if;
1103 --
1104 end if;
1105 --
1106 end if;
1107 --
1108 if g_detail_debug then
1109 hr_utility.set_location(l_proc,40);
1110 hr_utility.trace('skip mr is null or out range : l_valid_delete : '||l_valid_delete);
1111 end if;
1112 --
1113 if l_valid_delete = 'Y' then
1114 --
1115 p_valid_delete := 'Y';
1116 --
1117 end if;
1118 --
1119 end if;
1120 --
1121 if g_detail_debug then
1122 hr_utility.set_location(l_proc,1000);
1123 end if;
1124 --
1125 exception
1126 when others then
1127 --
1128 if g_debug then
1129 hr_utility.set_location(l_proc,-1000);
1130 hr_utility.trace(to_char(p_assignment_id)||':'||get_sqlerrm);
1131 end if;
1132 --
1133 end val_mig_smr_assact;
1134 --
1135 -- -------------------------------------------------------------------------
1136 -- mig_smr_assact
1137 -- -------------------------------------------------------------------------
1138 procedure mig_smr_assact(
1139 p_business_group_id in number,
1140 p_business_group_name in varchar2,
1141 p_assignment_id in number,
1142 p_assignment_number in varchar2,
1143 p_session_date in date,
1144 p_hi_mr in varchar2)
1145 is
1146 --
1147 l_proc varchar2(80) := c_package||'mig_smr_assact';
1148 --
1149 l_mr_eev pay_element_entry_values_f.screen_entry_value%type;
1150 l_smr_eev pay_element_entry_values_f.screen_entry_value%type;
1151 l_mr_o_eev pay_element_entry_values_f.screen_entry_value%type;
1152 l_smr_o_eev pay_element_entry_values_f.screen_entry_value%type;
1153 l_exp_mr_eev pay_element_entry_values_f.screen_entry_value%type;
1154 l_exp_smr_eev pay_element_entry_values_f.screen_entry_value%type;
1155 l_exp_mr_o_eev pay_element_entry_values_f.screen_entry_value%type;
1156 l_exp_smr_o_eev pay_element_entry_values_f.screen_entry_value%type;
1157 --
1158 l_ovn number;
1159 l_esd date;
1160 l_eed date;
1161 l_warning boolean;
1162 l_upd varchar2(1) := 'Y';
1163 --
1164 cursor csr_entry
1165 is
1166 select /*+ ORDERED
1167 USE_NL(PEL, PEE, PEEV)
1168 INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
1169 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51)
1170 INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
1171 pee.element_entry_id,
1172 pee.effective_start_date,
1173 pee.effective_end_date,
1174 pee.object_version_number,
1175 peev.input_value_id,
1176 peev.screen_entry_value
1177 from pay_element_links_f pel,
1178 pay_element_entries_f pee,
1179 pay_element_entry_values_f peev
1180 where pel.element_type_id = c_com_hi_smr_elm_id
1181 and pel.business_group_id + 0 = p_business_group_id
1182 and p_session_date
1183 between pel.effective_start_date and pel.effective_end_date
1184 and pee.assignment_id = p_assignment_id
1185 and pee.element_link_id = pel.element_link_id
1186 and p_session_date
1187 between pee.effective_start_date and pee.effective_end_date
1188 and pee.entry_type = 'E'
1189 and peev.element_entry_id = pee.element_entry_id
1190 and peev.effective_start_date = pee.effective_start_date
1191 and peev.effective_end_date = pee.effective_end_date
1192 for update of peev.element_entry_value_id nowait;
1193 --
1194 l_csr_entry csr_entry%rowtype;
1195 --
1196 begin
1197 --
1198 if g_detail_debug then
1199 hr_utility.set_location(l_proc,0);
1200 hr_utility.trace(p_business_group_id ||' : '||p_assignment_id||'('||p_assignment_number||')');
1201 end if;
1202 --
1203 open csr_entry;
1204 loop
1205 --
1206 fetch csr_entry into l_csr_entry;
1207 exit when csr_entry%notfound;
1208 --
1209 if l_csr_entry.input_value_id = c_smr_iv_id then
1210 --
1211 l_smr_eev := l_csr_entry.screen_entry_value;
1212 --
1213 elsif l_csr_entry.input_value_id = c_smr_o_iv_id then
1214 --
1215 l_smr_o_eev := l_csr_entry.screen_entry_value;
1216 --
1217 elsif l_csr_entry.input_value_id = c_mr_iv_id then
1218 --
1219 l_mr_eev := l_csr_entry.screen_entry_value;
1220 --
1221 elsif l_csr_entry.input_value_id = c_mr_o_iv_id then
1222 --
1223 l_mr_o_eev := l_csr_entry.screen_entry_value;
1224 --
1225 end if;
1226 --
1227 end loop;
1228 close csr_entry;
1229 --
1230 if g_detail_debug then
1231 hr_utility.set_location(l_proc,10);
1232 hr_utility.trace('l_smr_eev : '||l_smr_eev||', l_smr_o_eev : '||l_smr_o_eev||', l_mr_eev : '||l_mr_eev||', l_mr_o_eev : '||l_mr_o_eev);
1233 end if;
1234 --
1235 if p_hi_mr is not null then
1236 --
1237 l_exp_mr_eev := p_hi_mr;
1238 --
1239 else
1240 --
1241 l_exp_mr_eev := l_mr_eev;
1242 --
1243 end if;
1244 --
1245 l_exp_smr_eev := substrb(ltrim(rtrim(hruserdt.get_table_value(
1246 p_bus_group_id => p_business_group_id,
1247 p_table_name => c_com_smr_tbl,
1248 p_col_name => c_hi_smr_col,
1249 p_row_value => l_exp_mr_eev,
1250 p_effective_date => p_session_date))),0,60);
1251 --
1252 if g_detail_debug then
1253 hr_utility.set_location(l_proc,20);
1254 hr_utility.trace('l_exp_mr_eev : '||l_exp_mr_eev||', l_exp_smr_eev : '||l_exp_smr_eev);
1255 end if;
1256 --
1257 if g_exc_match_exp_smr = 'Y' then
1258 --
1259 if l_smr_eev = l_exp_smr_eev then
1260 --
1261 l_upd := 'N';
1262 --
1263 end if;
1264 --
1265 end if;
1266 --
1267 if g_upd_mode = 'OVERRIDE' then
1268 --
1269 l_exp_smr_o_eev := l_smr_o_eev;
1270 l_exp_mr_o_eev := l_mr_o_eev;
1271 --
1272 else
1273 --
1274 l_exp_smr_o_eev := l_smr_eev;
1275 l_exp_mr_o_eev := l_mr_eev;
1276 --
1277 end if;
1278 --
1279 if g_detail_debug then
1280 hr_utility.set_location(l_proc,30);
1281 hr_utility.trace('l_exp_smr_o_eev : '||l_exp_smr_o_eev||', l_exp_mr_o_eev : '||l_exp_mr_o_eev);
1282 end if;
1283 --
1284 l_ovn := l_csr_entry.object_version_number;
1285 --
1286 if l_upd = 'Y' then
1287 --
1288 if g_valid = 'N' then
1289 --
1290 pay_element_entry_api.update_element_entry(
1291 p_validate => false,
1292 p_effective_date => p_session_date,
1293 p_business_group_id => p_business_group_id,
1294 p_datetrack_update_mode => 'UPDATE',
1295 p_element_entry_id => l_csr_entry.element_entry_id,
1296 p_object_version_number => l_ovn,
1297 p_input_value_id1 => c_am_iv_id,
1298 p_input_value_id2 => c_smr_iv_id,
1299 p_input_value_id3 => c_smr_o_iv_id,
1300 p_input_value_id4 => c_at_iv_id,
1301 p_input_value_id5 => c_mr_iv_id,
1302 p_input_value_id6 => c_mr_o_iv_id,
1303 p_entry_value1 => to_char(p_session_date,'YYYYMM'),
1304 p_entry_value2 => l_exp_smr_eev,
1305 p_entry_value3 => l_exp_smr_o_eev,
1306 p_entry_value4 => 'O',
1307 p_entry_value5 => l_exp_mr_eev,
1308 p_entry_value6 => l_exp_mr_o_eev,
1309 p_effective_start_date => l_esd,
1310 p_effective_end_date => l_eed,
1311 p_update_warning => l_warning);
1312 --
1313 if g_detail_debug then
1314 hr_utility.set_location(l_proc,40);
1315 end if;
1316 --
1317 end if;
1318 --
1319 if g_sql_run = 'Y' then
1320 --
1321 if g_log = 'Y' then
1322 --
1323 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||lpad(l_exp_mr_eev,10)||' '||lpad(nvl(l_smr_eev,' '),10)||' '||lpad(l_exp_smr_eev,10));
1324 --
1325 end if;
1326 --
1327 else
1328 --
1329 fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||lpad(l_exp_mr_eev,10)||' '||lpad(nvl(l_smr_eev,' '),10)||' '||lpad(l_exp_smr_eev,10));
1330 --
1331 end if;
1332 --
1333 else
1334 --
1335 if g_sql_run = 'Y' then
1336 --
1337 if g_log = 'Y' then
1338 --
1339 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' X '||lpad(l_exp_mr_eev,10)||' '||lpad(nvl(l_smr_eev,' '),10)||' '||lpad(l_exp_smr_eev,10));
1340 --
1341 end if;
1342 --
1343 else
1344 --
1345 fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' X '||lpad(l_exp_mr_eev,10)||' '||lpad(nvl(l_smr_eev,' '),10)||' '||lpad(l_exp_smr_eev,10));
1346 --
1347 end if;
1348 --
1349 if g_detail_debug then
1350 hr_utility.set_location(l_proc,45);
1351 end if;
1352 --
1353 end if;
1354 --
1355 if g_detail_debug then
1356 hr_utility.set_location(l_proc,1000);
1357 end if;
1358 --
1359 exception
1360 when hr_api.object_locked then
1361 --
1362 if g_debug then
1363 hr_utility.set_location(l_proc,-1000);
1364 hr_utility.trace(to_char(p_assignment_id)||':'||fnd_message.get_string('FND','FND_LOCK_RECORD_ERROR'));
1365 end if;
1366 --
1367 if g_sql_run = 'Y' then
1368 --
1369 if g_log = 'Y' then
1370 --
1371 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||fnd_message.get_string('FND','FND_LOCK_RECORD_ERROR'));
1372 --
1373 end if;
1374 --
1375 else
1376 --
1377 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||fnd_message.get_string('FND','FND_LOCK_RECORD_ERROR'));
1378 --
1379 end if;
1380 --
1381 when others then
1382 --
1383 if g_debug then
1384 hr_utility.set_location(l_proc,-1000);
1385 hr_utility.trace(to_char(p_assignment_id)||':'||get_sqlerrm);
1386 end if;
1387 --
1388 if g_sql_run = 'Y' then
1389 --
1390 if g_log = 'Y' then
1391 --
1392 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||get_sqlerrm);
1393 --
1394 end if;
1395 --
1396 else
1397 --
1398 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||get_sqlerrm);
1399 --
1400 end if;
1401 --
1402 end mig_smr_assact;
1403 --
1404 -- -------------------------------------------------------------------------
1405 -- run_mig_smr
1406 -- -------------------------------------------------------------------------
1407 -- this is for manual run by script, recommend to use generic upgrade instead of this.
1408 procedure run_mig_smr
1409 is
1410 --
1411 l_proc varchar2(80) := c_package||'run_mig_smr';
1412 --
1413 l_range_ass_tbl_cnt number := 0;
1414 l_qualify_ass_tbl_cnt number := 0;
1415 l_qualify_valid_update varchar2(1) := 'N';
1416 --
1417 l_mig_cnt number := 0;
1418 l_mig_commit_cnt number := 1;
1419 --
1420 cursor csr_range_ass
1421 is
1422 select /*+ ORDERED
1423 INDEX(PA PER_ASSIGNMENTS_F_FK1) */
1424 pbg.business_group_id,
1425 pbg.name bg_name,
1426 pa.assignment_id,
1427 pa.assignment_number
1428 from per_business_groups_perf pbg,
1429 per_all_assignments_f pa
1430 where pbg.legislation_code = g_legislation_code
1431 and pa.business_group_id = pbg.business_group_id
1432 and pa.effective_start_date = (
1433 select /*+ INDEX(PA2 PER_ASSIGNMENTS_F_PK) */
1434 max(pa2.effective_start_date)
1435 from per_all_assignments_f pa2
1436 where pa2.assignment_id = pa.assignment_id);
1437 --
1438 l_csr_range_ass csr_range_ass%rowtype;
1439 --
1440 begin
1441 --
1442 -- --
1443 -- init
1444 -- --
1445 --
1446 g_sql_run := 'Y';
1447 --
1448 if g_sql_run = 'Y'
1449 and g_log = 'Y' then
1450 g_debug := false;
1451 end if;
1452 --
1453 if g_debug then
1454 hr_utility.set_location(l_proc,0);
1455 end if;
1456 --
1457 if g_skip_qualify is null then
1458 g_skip_qualify := 'N';
1459 end if;
1460 --
1461 if g_upd_mode is null then
1462 g_upd_mode := 'UPDATE';
1463 end if;
1464 --
1465 if g_mig_date is null then
1466 g_mig_date:= c_mig_smr_sd;
1467 end if;
1468 --
1469 -- can set g_range_ass_hi_smr_tbl by out of this procedure
1470 -- in case g_legislation_code is set.
1471 if g_legislation_code is null
1472 or g_legislation_code <> c_legislation_code then
1473 --
1474 g_range_ass_hi_smr_tbl.delete;
1475 g_legislation_code := c_legislation_code;
1476 --
1477 end if;
1478 --
1479 g_dml_num := null;
1480 g_qualify_hi_smr_ass_tbl.delete;
1481 l_range_ass_tbl_cnt := g_range_ass_hi_smr_tbl.count;
1482 --
1483 set_hi_smr_id;
1484 set_hi_smr_mesg;
1485 --
1486 -- for api use
1487 insert_session(g_mig_date);
1488 --
1489 if g_debug then
1490 hr_utility.set_location(l_proc,10);
1491 end if;
1492 --
1493 -- --
1494 -- range
1495 -- --
1496 --
1497 if l_range_ass_tbl_cnt = 0 then
1498 --
1499 open csr_range_ass;
1500 loop
1501 --
1502 fetch csr_range_ass into l_csr_range_ass;
1503 exit when csr_range_ass%notfound;
1504 --
1505 g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).bg_id := l_csr_range_ass.business_group_id;
1506 g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).bg_name := l_csr_range_ass.bg_name;
1507 g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).ass_id := l_csr_range_ass.assignment_id;
1508 g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).ass_num := l_csr_range_ass.assignment_number;
1509 g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).del_done := 'N';
1510 --
1511 l_range_ass_tbl_cnt := l_range_ass_tbl_cnt + 1;
1512 --
1513 end loop;
1514 close csr_range_ass;
1515 --
1516 end if;
1517 --
1518 if g_debug then
1519 --
1520 hr_utility.set_location(l_proc,15);
1521 hr_utility.trace('g_range_ass_hi_smr_tbl.count : '||to_char(g_range_ass_hi_smr_tbl.count));
1522 --
1523 if g_range_ass_hi_smr_tbl.count > 0 then
1524 --
1525 for i in 0..g_range_ass_hi_smr_tbl.count - 1 loop
1526 --
1527 if g_detail_debug then
1528 --
1529 hr_utility.trace(to_char(g_range_ass_hi_smr_tbl(i).bg_id)
1530 ||':'||g_range_ass_hi_smr_tbl(i).ass_num
1531 ||'('||to_char(g_range_ass_hi_smr_tbl(i).ass_id)
1532 ||').'||g_range_ass_hi_smr_tbl(i).del_done);
1533 --
1534 end if;
1535 --
1536 end loop;
1537 --
1538 end if;
1539 --
1540 end if;
1541 --
1542 if g_debug then
1543 hr_utility.set_location(l_proc,20);
1544 end if;
1545 --
1546 -- --
1547 -- qualify
1548 -- --
1549 --
1550 if g_log = 'Y' then
1551 --
1552 hr_utility.trace('Business Group Name Assignment Number Message');
1553 hr_utility.trace('------------------------------ ------------------------------ --------------------------------------------------------------------------------');
1554 --
1555 end if;
1556 --
1557 if g_skip_qualify = 'N' then
1558 --
1559 if g_range_ass_hi_smr_tbl.count > 0 then
1560 --
1561 for j in 0..g_range_ass_hi_smr_tbl.count - 1 loop
1562 --
1563 val_mig_smr_assact(
1564 p_business_group_id => g_range_ass_hi_smr_tbl(j).bg_id,
1565 p_business_group_name => g_range_ass_hi_smr_tbl(j).bg_name,
1566 p_assignment_id => g_range_ass_hi_smr_tbl(j).ass_id,
1567 p_assignment_number => g_range_ass_hi_smr_tbl(j).ass_num,
1568 p_session_date => g_mig_date,
1569 p_valid_delete => g_range_ass_hi_smr_tbl(j).del_done);
1570 --
1571 if l_qualify_valid_update = 'N'
1572 and g_range_ass_hi_smr_tbl(j).del_done = 'Y' then
1573 l_qualify_valid_update := 'Y';
1574 end if;
1575 --
1576 end loop;
1577 --
1578 end if;
1579 --
1580 else
1581 --
1582 l_qualify_valid_update := 'Y';
1583 --
1584 end if;
1585 --
1586 if l_qualify_valid_update = 'Y' then
1587 --
1588 if g_range_ass_hi_smr_tbl.count > 0 then
1589 --
1590 for k in 0..g_range_ass_hi_smr_tbl.count - 1 loop
1591 --
1592 if g_range_ass_hi_smr_tbl(k).del_done = 'Y'
1593 or g_skip_qualify = 'Y' then
1594 --
1595 g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).bg_id := g_range_ass_hi_smr_tbl(k).bg_id;
1596 g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).bg_name := g_range_ass_hi_smr_tbl(k).bg_name;
1597 g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).ass_id := g_range_ass_hi_smr_tbl(k).ass_id;
1598 g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).ass_num := g_range_ass_hi_smr_tbl(k).ass_num;
1599 g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).del_done := 'N';
1600 g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).hi_mr := g_range_ass_hi_smr_tbl(k).hi_mr;
1601 --
1602 l_qualify_ass_tbl_cnt := l_qualify_ass_tbl_cnt + 1;
1603 --
1604 end if;
1605 --
1606 end loop;
1607 --
1608 end if;
1609 --
1610 if g_debug then
1611 --
1612 if g_qualify_hi_smr_ass_tbl.count > 0 then
1613 --
1614 for l in 0..g_qualify_hi_smr_ass_tbl.count - 1 loop
1615 --
1616 if g_detail_debug then
1617 --
1618 hr_utility.trace(to_char(g_qualify_hi_smr_ass_tbl(l).bg_id)
1619 ||':'||g_qualify_hi_smr_ass_tbl(l).ass_num
1620 ||'('||to_char(g_qualify_hi_smr_ass_tbl(l).ass_id)
1621 ||').'||g_qualify_hi_smr_ass_tbl(l).del_done);
1622 --
1623 end if;
1624 --
1625 end loop;
1626 --
1627 end if;
1628 --
1629 end if;
1630 --
1631 end if;
1632 --
1633 if g_debug then
1634 hr_utility.set_location(l_proc,30);
1635 end if;
1636 --
1637 -- --
1638 -- upgrade
1639 -- --
1640 --
1641 if g_log = 'Y' then
1642 --
1643 hr_utility.trace('----------------------------------------------------------------------------------------------------------------------------------------------');
1644 hr_utility.trace('Business Group Name Assignment Number E MR SMR Exp SMR');
1645 hr_utility.trace('------------------------------ ------------------------------ - ---------- ---------- ----------');
1646 --
1647 end if;
1648 --
1649 if l_qualify_valid_update = 'Y' then
1650 --
1651 if g_qualify_hi_smr_ass_tbl.count > 0 then
1652 --
1653 for m in 0..g_qualify_hi_smr_ass_tbl.count - 1 loop
1654 --
1655 mig_smr_assact(
1656 p_business_group_id => g_qualify_hi_smr_ass_tbl(m).bg_id,
1657 p_business_group_name => g_qualify_hi_smr_ass_tbl(m).bg_name,
1658 p_assignment_id => g_qualify_hi_smr_ass_tbl(m).ass_id,
1659 p_assignment_number => g_qualify_hi_smr_ass_tbl(m).ass_num,
1660 p_session_date => g_mig_date,
1661 p_hi_mr => g_qualify_hi_smr_ass_tbl(m).hi_mr);
1662 --
1663 l_mig_cnt := l_mig_cnt + 1;
1664 --
1665 if g_sql_run = 'Y' then
1666 --
1667 g_dml_num := g_dml_num + (l_mig_cnt - c_commit_num * (l_mig_commit_cnt - 1));
1668 --
1669 if g_dml_num > c_commit_num then
1670 commit;
1671 g_dml_num := 0;
1672 l_mig_commit_cnt := l_mig_commit_cnt + 1;
1673 end if;
1674 --
1675 end if;
1676 --
1677 end loop;
1678 --
1679 end if;
1680 --
1681 end if;
1682 --
1683 if g_debug then
1684 hr_utility.set_location(l_proc,40);
1685 end if;
1686 --
1687 -- --
1688 -- deinitialize
1689 -- --
1690 --
1691 if g_log = 'Y' then
1692 --
1693 hr_utility.trace('----------------------------------------------------------------------------------------------------------------------------------------------');
1694 --
1695 end if;
1696 --
1697 delete_session;
1698 --
1699 if l_qualify_valid_update = 'Y' then
1700 --
1701 commit;
1702 --
1703 end if;
1704 --
1705 if g_debug then
1706 hr_utility.set_location(l_proc,1000);
1707 end if;
1708 --
1709 end run_mig_smr;
1710 --
1711 -- -------------------------------------------------------------------------
1712 -- qualify_dep_hd
1713 -- -------------------------------------------------------------------------
1714 -- run by qualify_dep_data
1715 procedure qualify_dep_hd(
1716 p_assignment_id in number)
1717 is
1718 --
1719 l_proc varchar2(80) := c_package||'qualify_dep_hd';
1720 --
1721 begin
1722 --
1723 if g_debug then
1724 hr_utility.set_location(l_proc,0);
1725 hr_utility.trace('g_qualify_ini_ass_id : '||to_char(g_qualify_ini_ass_id));
1726 end if;
1727 --
1728 if g_qualify_ini_ass_id is null
1729 or g_qualify_ini_ass_id = p_assignment_id then
1730 --
1731 fnd_file.put_line(fnd_file.log, 'Business Group Name Assignment Number Message');
1732 fnd_file.put_line(fnd_file.log, '------------------------------ ------------------------------ --------------------------------------------------------------------------------');
1733 --
1734 g_qualify_ini_ass_id := p_assignment_id;
1735 --
1736 end if;
1737 --
1738 if g_debug then
1739 hr_utility.trace('g_qualify_ini_ass_id : '||to_char(g_qualify_ini_ass_id));
1740 hr_utility.set_location(l_proc,1000);
1741 end if;
1742 --
1743 end qualify_dep_hd;
1744 --
1745 -- -------------------------------------------------------------------------
1746 -- migrate_dep_hd
1747 -- -------------------------------------------------------------------------
1748 -- run by migrate_dep_data
1749 procedure migrate_dep_hd(
1750 p_assignment_id in number)
1751 is
1752 --
1753 l_proc varchar2(80) := c_package||'migrate_dep_hd';
1754 --
1755 begin
1756 --
1757 if g_debug then
1758 hr_utility.set_location(l_proc,0);
1759 hr_utility.trace('g_migrate_ini_ass_id : '||to_char(g_migrate_ini_ass_id));
1760 end if;
1761 --
1762 if g_migrate_ini_ass_id is null
1763 or g_migrate_ini_ass_id = p_assignment_id then
1764 --
1765 fnd_file.put_line(fnd_file.output, 'Business Group Name Assignment Number Age Type Contact');
1766 fnd_file.put_line(fnd_file.output, '------------------------------ ------------------------- ---- ------ ------------------------------------------------------------');
1767 --
1768 g_migrate_ini_ass_id := p_assignment_id;
1769 --
1770 end if;
1771 --
1772 if g_debug then
1773 hr_utility.trace('g_migrate_ini_ass_id : '||to_char(g_migrate_ini_ass_id));
1774 hr_utility.set_location(l_proc,1000);
1775 end if;
1776 --
1777 end migrate_dep_hd;
1778 --
1779 -- -------------------------------------------------------------------------
1780 -- init_def_dep_data
1781 -- -------------------------------------------------------------------------
1782 -- run by qualify_dep_data
1783 procedure init_def_dep_data
1784 is
1785 --
1786 l_proc varchar2(80) := c_package||'init_def_dep_data';
1787 --
1788 begin
1789 --
1790 if g_debug then
1791 hr_utility.set_location(l_proc,0);
1792 hr_utility.trace('g_legislation_code : '||g_legislation_code);
1793 end if;
1794 --
1795 -- temporary solution because of no initialize.
1796 -- run once for each thread.
1797 if g_legislation_code is null
1798 or g_legislation_code <> c_legislation_code then
1799 --
1800 -- no support of legsilative parameter at this moment.
1801 g_skip_qualify := 'N';
1802 g_mig_date := c_mig_dep_sd;
1803 --
1804 set_dep_id;
1805 set_dep_mesg;
1806 --
1807 g_legislation_code := c_legislation_code;
1808 --
1809 end if;
1810 --
1811 if g_debug then
1812 hr_utility.trace('g_legislation_code : '||g_legislation_code);
1813 hr_utility.set_location(l_proc,1000);
1814 end if;
1815 --
1816 end init_def_dep_data;
1817 --
1818 -- -------------------------------------------------------------------------
1819 -- val_mig_dep_assact
1820 -- -------------------------------------------------------------------------
1821 procedure val_mig_dep_assact(
1822 p_business_group_id in number,
1823 p_business_group_name in varchar2,
1824 p_assignment_id in number,
1825 p_assignment_number in varchar2,
1826 p_session_date in date,
1827 p_valid_update in out nocopy varchar2)
1828 is
1829 --
1830 l_proc varchar2(80) := c_package||'val_mig_dep_assact';
1831 --
1832 l_cei_target_id number;
1833 l_cei_oe_target_id number;
1834 l_ee_target_id number;
1835 --
1836 l_ft_cei_id number;
1837 l_ft_cei_oe_id number;
1838 l_ee_esd date;
1839 --l_ee_upd_id number;
1840 l_ft_ee_esd date;
1841 --
1842 l_valid_update varchar2(1) := 'N';
1843 --
1844 -- 1. under 16 dep_type 0, set 5
1845 -- 2. 16 - 18 dep_type 10, set 0
1846 -- exc null date_of_birth
1847 cursor csr_cei_target
1848 is
1849 select /*+ ORDERED */
1850 pp_c.person_id
1851 from per_all_assignments_f pa,
1852 per_contact_relationships pcr,
1853 per_contact_extra_info_f pcei,
1854 per_all_people_f pp_c
1855 where pa.assignment_id = p_assignment_id
1856 and p_session_date
1857 between pa.effective_start_date and pa.effective_end_date
1858 and pcr.person_id = pa.person_id
1859 and pcei.contact_relationship_id = pcr.contact_relationship_id
1860 and pcei.cei_information_category = 'JP_ITAX_DEPENDENT'
1861 and p_session_date
1862 between pcei.effective_start_date and pcei.effective_end_date
1863 and pp_c.person_id = pcr.contact_person_id
1864 and p_session_date
1865 between pp_c.effective_start_date and pp_c.effective_end_date
1866 and ((nvl(trunc(months_between(
1867 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
1868 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
1869 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
1870 pp_c.date_of_birth) / 12),16) < 16
1871 and nvl(pcei.cei_information8,'0') = '0')
1872 or (nvl(trunc(months_between(
1873 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
1874 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
1875 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
1876 pp_c.date_of_birth) / 12),0)
1877 between 16 and 18
1878 and nvl(pcei.cei_information8,'10') = '10'));
1879 --
1880 -- depend on g_expire_cei_dep_oe (default Y)
1881 -- because system cannot know if dep is non target for under 16 but disability so target.
1882 -- 1. g_expire_cei_dep_oe = Y
1883 -- oe cei and under 16, end cei
1884 -- exc null date_of_birth
1885 cursor csr_cei_oe_target
1886 is
1887 select /*+ ORDERED */
1888 pp_c.person_id
1889 from per_all_assignments_f pa,
1890 per_contact_relationships pcr,
1891 per_contact_extra_info_f pcei,
1892 per_all_people_f pp_c
1893 where pa.assignment_id = p_assignment_id
1894 and p_session_date
1895 between pa.effective_start_date and pa.effective_end_date
1896 and pcr.person_id = pa.person_id
1897 and pcei.contact_relationship_id = pcr.contact_relationship_id
1898 and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
1899 and p_session_date
1900 between pcei.effective_start_date and pcei.effective_end_date
1901 and pp_c.person_id = pcr.contact_person_id
1902 and p_session_date
1903 between pp_c.effective_start_date and pp_c.effective_end_date
1904 and nvl(trunc(months_between(
1905 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
1906 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
1907 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
1908 pp_c.date_of_birth) / 12),16) < 16;
1909 --
1910 -- 1. os cei, set ee
1911 cursor csr_ee_target
1912 is
1913 select /*+ ORDERED */
1914 pcei.contact_extra_info_id
1915 from per_all_assignments_f pa,
1916 per_contact_relationships pcr,
1917 per_contact_extra_info_f pcei
1918 where pa.assignment_id = p_assignment_id
1919 and p_session_date
1920 between pa.effective_start_date and pa.effective_end_date
1921 and pcr.person_id = pa.person_id
1922 and pcei.contact_relationship_id = pcr.contact_relationship_id
1923 and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_PAY'
1924 and p_session_date
1925 between pcei.effective_start_date and pcei.effective_end_date;
1926 --
1927 cursor csr_ft_cei
1928 is
1929 select /*+ ORDERED */
1930 pcei.contact_extra_info_id
1931 from per_all_assignments_f pa,
1932 per_contact_relationships pcr,
1933 per_contact_extra_info_f pcei,
1934 per_all_people_f pp_c
1935 where pa.assignment_id = p_assignment_id
1936 and p_session_date
1937 between pa.effective_start_date and pa.effective_end_date
1938 and pcr.person_id = pa.person_id
1939 and pcei.contact_relationship_id = pcr.contact_relationship_id
1940 and pcei.cei_information_category = 'JP_ITAX_DEPENDENT'
1941 and pcei.effective_start_date > p_session_date
1942 and pp_c.person_id = pcr.contact_person_id
1943 and p_session_date
1944 between pp_c.effective_start_date and pp_c.effective_end_date
1945 and nvl(trunc(months_between(
1946 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
1947 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
1948 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
1949 pp_c.date_of_birth) / 12),16) < 16;
1950 --
1951 cursor csr_ft_cei_oe
1952 is
1953 select /*+ ORDERED */
1954 pcei.contact_extra_info_id
1955 from per_all_assignments_f pa,
1956 per_contact_relationships pcr,
1957 per_contact_extra_info_f pcei,
1958 per_all_people_f pp_c
1959 where pa.assignment_id = p_assignment_id
1960 and p_session_date
1961 between pa.effective_start_date and pa.effective_end_date
1962 and pcr.person_id = pa.person_id
1963 and pcei.contact_relationship_id = pcr.contact_relationship_id
1964 and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
1965 and pcei.effective_start_date > p_session_date
1966 and pp_c.person_id = pcr.contact_person_id
1967 and p_session_date
1968 between pp_c.effective_start_date and pp_c.effective_end_date
1969 and nvl(trunc(months_between(
1970 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
1971 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
1972 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
1973 pp_c.date_of_birth) / 12),16) < 16;
1974 --
1975 cursor csr_ee
1976 is
1977 select /*+ ORDERED
1978 USE_NL(PLIV, PEE)
1979 INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
1980 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
1981 pee.effective_start_date
1982 -- pee.updating_action_id
1983 from pay_link_input_values_f pliv,
1984 pay_element_entries_f pee
1985 where pliv.input_value_id = c_sec_sal_iv_id
1986 and p_session_date
1987 between pliv.effective_start_date and pliv.effective_end_date
1988 and pee.element_link_id = pliv.element_link_id
1989 and pee.assignment_id = p_assignment_id
1990 and p_session_date
1991 between pee.effective_start_date and pee.effective_end_date;
1992 --
1993 cursor csr_ft_ee
1994 is
1995 select /*+ ORDERED
1996 USE_NL(PLIV, PEE)
1997 INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
1998 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
1999 pee.effective_start_date
2000 from pay_link_input_values_f pliv,
2001 pay_element_entries_f pee
2002 where pliv.input_value_id = c_sec_sal_iv_id
2003 and p_session_date
2004 between pliv.effective_start_date and pliv.effective_end_date
2005 and pee.element_link_id = pliv.element_link_id
2006 and pee.assignment_id = p_assignment_id
2007 and pee.effective_start_date > p_session_date;
2008 --
2009 begin
2010 --
2011 if g_detail_debug then
2012 hr_utility.set_location(l_proc,0);
2013 hr_utility.trace(p_business_group_id ||' : '||p_assignment_id||'('||p_assignment_number||')');
2014 end if;
2015 --
2016 if p_valid_update = 'N' then
2017 --
2018 -- set valid update when the at least one exist either cei target, cei oe target or ee target
2019 --
2020 -- target for cei update
2021 --
2022 open csr_cei_target;
2023 fetch csr_cei_target into l_cei_target_id;
2024 close csr_cei_target;
2025 --
2026 -- exclude no target assignment
2027 if l_cei_target_id is not null then
2028 --
2029 l_valid_update := 'Y';
2030 --
2031 end if;
2032 --
2033 if g_detail_debug then
2034 hr_utility.set_location(l_proc,10);
2035 hr_utility.trace('l_cei_target_id : '||to_char(l_cei_target_id));
2036 hr_utility.trace('g_expire_cei_dep_oe : '||g_expire_cei_dep_oe);
2037 end if;
2038 --
2039 -- target for cei oe update
2040 --
2041 if nvl(g_expire_cei_dep_oe,'Y') = 'Y' then
2042 --
2043 open csr_cei_oe_target;
2044 fetch csr_cei_oe_target into l_cei_oe_target_id;
2045 close csr_cei_oe_target;
2046 --
2047 -- exclude no target assignment
2048 if l_cei_oe_target_id is not null then
2049 --
2050 l_valid_update := 'Y';
2051 --
2052 end if;
2053 --
2054 if g_detail_debug then
2055 hr_utility.set_location(l_proc,20);
2056 hr_utility.trace('l_cei_oe_target_id : '||to_char(l_cei_oe_target_id));
2057 end if;
2058 --
2059 end if;
2060 --
2061 -- target for ee update
2062 --
2063 open csr_ee_target;
2064 fetch csr_ee_target into l_ee_target_id;
2065 close csr_ee_target;
2066 --
2067 -- exclude no target assignment
2068 if l_ee_target_id is not null then
2069 --
2070 l_valid_update := 'Y';
2071 --
2072 end if;
2073 --
2074 if g_detail_debug then
2075 hr_utility.set_location(l_proc,30);
2076 hr_utility.trace('l_ee_target_id : '||to_char(l_ee_target_id));
2077 end if;
2078 --
2079 -- validation for each target, if there is one no valid_update, the target will be skipped.
2080 --
2081 if l_cei_target_id is not null then
2082 --
2083 -- skip future cei exists (cei validation)
2084 --
2085 if l_valid_update = 'Y' then
2086 --
2087 open csr_ft_cei;
2088 fetch csr_ft_cei into l_ft_cei_id;
2089 close csr_ft_cei;
2090 --
2091 if l_ft_cei_id is not null then
2092 --
2093 l_valid_update := 'N';
2094 --
2095 if g_sql_run = 'Y' then
2096 --
2097 if g_log = 'Y' then
2098 --
2099 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_dep_fut_cei_exist_mesg);
2100 --
2101 end if;
2102 --
2103 else
2104 --
2105 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_dep_fut_cei_exist_mesg);
2106 --
2107 end if;
2108 --
2109 end if;
2110 --
2111 end if;
2112 --
2113 if g_detail_debug then
2114 hr_utility.set_location(l_proc,40);
2115 hr_utility.trace('skip future cei : l_valid_update : '||l_valid_update);
2116 end if;
2117 --
2118 end if;
2119 --
2120 if l_cei_oe_target_id is not null then
2121 --
2122 -- skip future cei oe exists (cei validation)
2123 --
2124 if l_valid_update = 'Y' then
2125 --
2126 open csr_ft_cei_oe;
2127 fetch csr_ft_cei_oe into l_ft_cei_oe_id;
2128 close csr_ft_cei_oe;
2129 --
2130 if l_ft_cei_oe_id is not null then
2131 --
2132 l_valid_update := 'N';
2133 --
2134 if g_sql_run = 'Y' then
2135 --
2136 if g_log = 'Y' then
2137 --
2138 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_dep_fut_cei_oe_exist_mesg);
2139 --
2140 end if;
2141 --
2142 else
2143 --
2144 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_dep_fut_cei_oe_exist_mesg);
2145 --
2146 end if;
2147 --
2148 end if;
2149 --
2150 end if;
2151 --
2152 if g_detail_debug then
2153 hr_utility.set_location(l_proc,50);
2154 hr_utility.trace('skip future cei : l_valid_update : '||l_valid_update);
2155 end if;
2156 --
2157 end if;
2158 --
2159 if l_ee_target_id is not null then
2160 --
2161 -- skip already ee updated (manual update) (ee validation)
2162 --
2163 if l_valid_update = 'Y' then
2164 --
2165 open csr_ee;
2166 --fetch csr_ee into l_ee_esd, l_ee_upd_id;
2167 fetch csr_ee into l_ee_esd;
2168 close csr_ee;
2169 --
2170 if g_skip_manual_upd = 'Y' then
2171 --
2172 if l_ee_esd = p_session_date
2173 and l_ee_esd is not null then
2174 --and l_ee_upd_id is null then
2175 --
2176 l_valid_update := 'N';
2177 --
2178 if g_sql_run = 'Y' then
2179 --
2180 if g_log = 'Y' then
2181 --
2182 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_dep_already_ee_upd_mesg);
2183 --
2184 end if;
2185 --
2186 else
2187 --
2188 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_dep_already_ee_upd_mesg);
2189 --
2190 end if;
2191 --
2192 end if;
2193 --
2194 end if;
2195 --
2196 end if;
2197 --
2198 if g_detail_debug then
2199 hr_utility.set_location(l_proc,60);
2200 hr_utility.trace('skip manual upd entry : l_valid_update : '||l_valid_update);
2201 end if;
2202 --
2203 -- skip future entry exists (ee validation)
2204 --
2205 if l_valid_update = 'Y' then
2206 --
2207 open csr_ft_ee;
2208 fetch csr_ft_ee into l_ft_ee_esd;
2209 close csr_ft_ee;
2210 --
2211 if l_ft_ee_esd is not null then
2212 --
2213 l_valid_update := 'N';
2214 --
2215 if g_sql_run = 'Y' then
2216 --
2217 if g_log = 'Y' then
2218 --
2219 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_dep_fut_ee_exist_mesg);
2220 --
2221 end if;
2222 --
2223 else
2224 --
2225 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_dep_fut_ee_exist_mesg);
2226 --
2227 end if;
2228 --
2229 end if;
2230 --
2231 end if;
2232 --
2233 if g_detail_debug then
2234 hr_utility.set_location(l_proc,70);
2235 hr_utility.trace('skip future entry : l_valid_update : '||l_valid_update);
2236 end if;
2237 --
2238 end if;
2239 --
2240 if l_valid_update = 'Y' then
2241 --
2242 p_valid_update := 'Y';
2243 --
2244 end if;
2245 --
2246 end if;
2247 --
2248 if g_detail_debug then
2249 hr_utility.set_location(l_proc,1000);
2250 end if;
2251 --
2252 exception
2253 when others then
2254 --
2255 if g_debug then
2256 hr_utility.set_location(l_proc,-1000);
2257 hr_utility.trace(to_char(p_assignment_id)||':'||get_sqlerrm);
2258 end if;
2259 --
2260 end val_mig_dep_assact;
2261 --
2262 -- -------------------------------------------------------------------------
2263 -- mig_dep_assact
2264 -- -------------------------------------------------------------------------
2265 procedure mig_dep_assact(
2266 p_business_group_id in number,
2267 p_business_group_name in varchar2,
2268 p_assignment_id in number,
2269 p_assignment_number in varchar2,
2270 p_session_date in date)
2271 is
2272 --
2273 l_proc varchar2(80) := c_package||'mig_dep_assact';
2274 --
2275 l_dep_type per_contact_extra_info_f.cei_information8%type;
2276 --
2277 l_ee_id number;
2278 --
2279 l_element_link_id number;
2280 l_element_entry_id number;
2281 l_ovn number;
2282 l_esd date;
2283 l_eed date;
2284 l_warning boolean;
2285 --
2286 -- need to validate target again to know which type, cei, cei oe, ee of upgrade is required.
2287 --
2288 -- 1. under 16 dep_type 0, set 5
2289 -- 2. 16 - 18 dep_type 10, set 0
2290 -- exc null date_of_birth
2291 cursor csr_cei
2292 is
2293 select /*+ ORDERED */
2294 pcei.contact_extra_info_id,
2295 pcr.contact_relationship_id,
2296 pcei.object_version_number,
2297 pcei.cei_information8 dep_type,
2298 pp_c.last_name||' '||pp_c.first_name c_full_name,
2299 trunc(months_between(
2300 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
2301 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
2302 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
2303 pp_c.date_of_birth) / 12) c_age
2304 from per_all_assignments_f pa,
2305 per_contact_relationships pcr,
2306 per_contact_extra_info_f pcei,
2307 per_all_people_f pp_c
2308 where pa.assignment_id = p_assignment_id
2309 and p_session_date
2310 between pa.effective_start_date and pa.effective_end_date
2311 and pcr.person_id = pa.person_id
2312 and pcei.contact_relationship_id = pcr.contact_relationship_id
2313 and pcei.cei_information_category = 'JP_ITAX_DEPENDENT'
2314 and p_session_date
2315 between pcei.effective_start_date and pcei.effective_end_date
2316 and pp_c.person_id = pcr.contact_person_id
2317 and p_session_date
2318 between pp_c.effective_start_date and pp_c.effective_end_date
2319 and ((nvl(trunc(months_between(
2320 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
2321 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
2322 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
2323 pp_c.date_of_birth) / 12),16) < 16
2324 and nvl(pcei.cei_information8,'0') = '0')
2325 or (nvl(trunc(months_between(
2326 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
2327 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
2328 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
2329 pp_c.date_of_birth) / 12),0)
2330 between 16 and 18
2331 and nvl(pcei.cei_information8,'10') = '10'))
2332 for update of pcei.contact_extra_info_id nowait;
2333 --
2334 -- depend on g_expire_cei_dep_oe (default Y)
2335 -- because system cannot know if dep is non target for under 16 but disability so target.
2336 -- 1. g_expire_cei_dep_oe = Y
2337 -- oe cei and under 16, end cei
2338 -- exc null date_of_birth
2339 cursor csr_cei_oe
2340 is
2341 select /*+ ORDERED */
2342 pcei.contact_extra_info_id,
2343 pcr.contact_relationship_id,
2344 pcei.object_version_number,
2345 pp_c.last_name||' '||pp_c.first_name c_full_name,
2346 trunc(months_between(
2347 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
2348 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
2349 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
2350 pp_c.date_of_birth) / 12) c_age
2351 from per_all_assignments_f pa,
2352 per_contact_relationships pcr,
2353 per_contact_extra_info_f pcei,
2354 per_all_people_f pp_c
2355 where pa.assignment_id = p_assignment_id
2356 and p_session_date
2357 between pa.effective_start_date and pa.effective_end_date
2358 and pcr.person_id = pa.person_id
2359 and pcei.contact_relationship_id = pcr.contact_relationship_id
2360 and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
2361 and p_session_date
2362 between pcei.effective_start_date and pcei.effective_end_date
2363 and pp_c.person_id = pcr.contact_person_id
2364 and p_session_date
2365 between pp_c.effective_start_date and pp_c.effective_end_date
2366 and nvl(trunc(months_between(
2367 to_date(decode(to_char(nvl(least(pp_c.date_of_death,p_session_date),p_session_date),'YYYY/MM/DD'),
2368 to_char(pp_c.date_of_death,'YYYY/MM/DD'),to_char(pp_c.date_of_death,'YYYY/MM/DD'),
2369 to_char((add_months(trunc(p_session_date,'YYYY'),12) - 1),'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
2370 pp_c.date_of_birth) / 12),16) < 16
2371 for update of pcei.contact_extra_info_id nowait;
2372 --
2373 -- 1. os cei, set ee
2374 cursor csr_ee
2375 is
2376 select /*+ ORDERED */
2377 pcei.contact_extra_info_id
2378 from per_all_assignments_f pa,
2379 per_contact_relationships pcr,
2380 per_contact_extra_info_f pcei
2381 where pa.assignment_id = p_assignment_id
2382 and p_session_date
2383 between pa.effective_start_date and pa.effective_end_date
2384 and pcr.person_id = pa.person_id
2385 and pcei.contact_relationship_id = pcr.contact_relationship_id
2386 and pcei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_PAY'
2387 and p_session_date
2388 between pcei.effective_start_date and pcei.effective_end_date;
2389 --
2390 cursor csr_entry
2391 is
2392 select /*+ ORDERED
2393 USE_NL(PLIV, PEE, PEEV)
2394 INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
2395 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51)
2396 INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
2397 pee.element_entry_id,
2398 pee.object_version_number,
2399 peev.screen_entry_value sec_sal
2400 from pay_link_input_values_f pliv,
2401 pay_element_entries_f pee,
2402 pay_element_entry_values_f peev
2403 where pliv.input_value_id = c_sec_sal_iv_id
2404 and p_session_date
2405 between pliv.effective_start_date and pliv.effective_end_date
2406 and pee.element_link_id = pliv.element_link_id
2407 and pee.assignment_id = p_assignment_id
2408 and p_session_date
2409 between pee.effective_start_date and pee.effective_end_date
2410 and pee.entry_type = 'E'
2411 and peev.input_value_id = pliv.input_value_id
2412 and peev.element_entry_id = pee.element_entry_id
2413 and peev.effective_start_date = pee.effective_start_date
2414 and peev.effective_end_date = pee.effective_end_date
2415 for update of peev.element_entry_value_id nowait;
2416 --
2417 l_csr_cei csr_cei%rowtype;
2418 l_csr_cei_oe csr_cei_oe%rowtype;
2419 l_csr_entry csr_entry%rowtype;
2420 --
2421 begin
2422 --
2423 if g_detail_debug then
2424 hr_utility.set_location(l_proc,0);
2425 hr_utility.trace('g_expire_cei_dep_oe : '||g_expire_cei_dep_oe);
2426 hr_utility.trace(p_business_group_id ||' : '||p_assignment_id||'('||p_assignment_number||')');
2427 end if;
2428 -- ---------------------------
2429 -- upgrade cei
2430 -- ---------------------------
2431 open csr_cei;
2432 loop
2433 --
2434 fetch csr_cei into l_csr_cei;
2435 exit when csr_cei%notfound;
2436 --
2437 l_dep_type := '0';
2438 if l_csr_cei.c_age < 16 then
2439 --
2440 l_dep_type := '5';
2441 --
2442 end if;
2443 --
2444 l_ovn := l_csr_cei.object_version_number;
2445 --
2446 if g_valid = 'N' then
2447 --
2448 hr_contact_extra_info_api.update_contact_extra_info(
2449 p_validate => false,
2450 p_effective_date => p_session_date,
2451 p_datetrack_update_mode => 'UPDATE',
2452 p_contact_extra_info_id => l_csr_cei.contact_extra_info_id,
2453 p_object_version_number => l_ovn,
2454 p_cei_information8 => l_dep_type,
2455 p_effective_start_date => l_esd,
2456 p_effective_end_date => l_eed);
2457 --
2458 if g_detail_debug then
2459 hr_utility.set_location(l_proc,10);
2460 end if;
2461 --
2462 if g_sql_run = 'Y' then
2463 --
2464 if g_log = 'Y' then
2465 --
2466 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,25)||' '||rpad(l_csr_cei.c_age,4)||' CEI '||rpad(l_csr_cei.c_full_name,60));
2467 --
2468 end if;
2469 --
2470 else
2471 --
2472 fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,25)||' '||rpad(l_csr_cei.c_age,4)||' CEI '||rpad(l_csr_cei.c_full_name,60));
2473 --
2474 end if;
2475 --
2476 else
2477 --
2478 if g_sql_run = 'Y' then
2479 --
2480 if g_log = 'Y' then
2481 --
2482 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,25)||' '||rpad(l_csr_cei.c_age,4)||' CEI '||rpad(l_csr_cei.c_full_name,60));
2483 --
2484 end if;
2485 --
2486 else
2487 --
2488 fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,25)||' '||rpad(l_csr_cei.c_age,4)||' CEI '||rpad(l_csr_cei.c_full_name,60));
2489 --
2490 end if;
2491 --
2492 end if;
2493 --
2494 end loop;
2495 close csr_cei;
2496 --
2497 if g_detail_debug then
2498 hr_utility.set_location(l_proc,20);
2499 end if;
2500 --
2501 -- ---------------------------
2502 -- upgrade cei oe
2503 -- ---------------------------
2504 if nvl(g_expire_cei_dep_oe,'Y') = 'Y' then
2505 --
2506 open csr_cei_oe;
2507 loop
2508 --
2509 fetch csr_cei_oe into l_csr_cei_oe;
2510 exit when csr_cei_oe%notfound;
2511 --
2512 l_ovn := l_csr_cei_oe.object_version_number;
2513 --
2514 if g_valid = 'N' then
2515 --
2516 hr_contact_extra_info_api.delete_contact_extra_info(
2517 p_validate => false,
2518 p_effective_date => p_session_date - 1,
2519 p_datetrack_delete_mode => 'DELETE',
2520 p_contact_extra_info_id => l_csr_cei_oe.contact_extra_info_id,
2521 p_object_version_number => l_ovn,
2522 p_effective_start_date => l_esd,
2523 p_effective_end_date => l_eed);
2524 --
2525 if g_detail_debug then
2526 hr_utility.set_location(l_proc,30);
2527 end if;
2528 --
2529 if g_sql_run = 'Y' then
2530 --
2531 if g_log = 'Y' then
2532 --
2533 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,25)||' '||rpad(l_csr_cei_oe.c_age,4)||' CEI OE '||rpad(l_csr_cei_oe.c_full_name,60));
2534 --
2535 end if;
2536 --
2537 else
2538 --
2539 fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,25)||' '||rpad(l_csr_cei_oe.c_age,4)||' CEI OE '||rpad(l_csr_cei_oe.c_full_name,60));
2540 --
2541 end if;
2542 --
2543 else
2544 --
2545 if g_sql_run = 'Y' then
2546 --
2547 if g_log = 'Y' then
2548 --
2549 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,25)||' '||rpad(l_csr_cei_oe.c_age,4)||' CEI OE '||rpad(l_csr_cei_oe.c_full_name,60));
2550 --
2551 end if;
2552 --
2553 else
2554 --
2555 fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,25)||' '||rpad(l_csr_cei_oe.c_age,4)||' CEI OE '||rpad(l_csr_cei_oe.c_full_name,60));
2556 --
2557 end if;
2558 --
2559 end if;
2560 --
2561 end loop;
2562 close csr_cei_oe;
2563 --
2564 end if;
2565 --
2566 if g_detail_debug then
2567 hr_utility.set_location(l_proc,40);
2568 end if;
2569 --
2570 -- ---------------------------
2571 -- upgrade ee
2572 -- ---------------------------
2573 open csr_ee;
2574 fetch csr_ee into l_ee_id;
2575 close csr_ee;
2576 --
2577 if g_detail_debug then
2578 hr_utility.set_location(l_proc,50);
2579 hr_utility.trace('l_ee_id : '||to_char(l_ee_id));
2580 end if;
2581 --
2582 if l_ee_id is not null then
2583 --
2584 open csr_entry;
2585 fetch csr_entry into l_csr_entry;
2586 close csr_entry;
2587 --
2588 if g_detail_debug then
2589 hr_utility.set_location(l_proc,60);
2590 hr_utility.trace('sec_sal : '||l_csr_entry.sec_sal);
2591 end if;
2592 --
2593 --if l_csr_entry.element_entry_id is not null then
2594 ----
2595 -- l_ovn := l_csr_entry.object_version_number;
2596 ----
2597 -- if g_valid = 'N' then
2598 -- --
2599 -- pay_element_entry_api.update_element_entry(
2600 -- p_validate => false,
2601 -- p_effective_date => p_session_date,
2602 -- p_business_group_id => p_business_group_id,
2603 -- p_datetrack_update_mode => 'CORRECTION',
2604 -- p_element_entry_id => l_csr_entry.element_entry_id,
2605 -- p_object_version_number => l_ovn,
2606 -- p_input_value_id1 => c_sec_sal_iv_id,
2607 -- p_entry_value1 => 'Y',
2608 -- p_effective_start_date => l_esd,
2609 -- p_effective_end_date => l_eed,
2610 -- p_update_warning => l_warning);
2611 -- --
2612 -- if g_detail_debug then
2613 -- hr_utility.set_location(l_proc,70);
2614 -- end if;
2615 -- --
2616 -- if g_sql_run = 'Y' then
2617 -- --
2618 -- if g_log = 'Y' then
2619 -- --
2620 -- hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' EE');
2621 -- --
2622 -- end if;
2623 -- --
2624 -- else
2625 -- --
2626 -- fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' EE');
2627 -- --
2628 -- end if;
2629 -- --
2630 -- else
2631 -- --
2632 -- if g_sql_run = 'Y' then
2633 -- --
2634 -- if g_log = 'Y' then
2635 -- --
2636 -- hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' EE');
2637 -- --
2638 -- end if;
2639 -- --
2640 -- else
2641 -- --
2642 -- fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' EE');
2643 -- --
2644 -- end if;
2645 -- --
2646 -- end if;
2647 ----
2648 --else
2649 if l_csr_entry.element_entry_id is null then
2650 --
2651 if g_valid = 'N' then
2652 --
2653 l_element_link_id := hr_entry_api.get_link(
2654 p_assignment_id => p_assignment_id,
2655 p_element_type_id => c_yea_dep_exm_elm_id,
2656 p_session_date => p_session_date);
2657 --
2658 pay_element_entry_api.create_element_entry(
2659 p_validate => false,
2660 p_effective_date => p_session_date,
2661 p_business_group_id => p_business_group_id,
2662 p_assignment_id => p_assignment_id,
2663 p_element_link_id => l_element_link_id,
2664 p_entry_type => 'E',
2665 p_input_value_id1 => c_sec_sal_iv_id,
2666 p_entry_value1 => 'Y',
2667 p_element_entry_id => l_element_entry_id,
2668 p_object_version_number => l_ovn,
2669 p_effective_start_date => l_esd,
2670 p_effective_end_date => l_eed,
2671 p_create_warning => l_warning);
2672 --
2673 if g_detail_debug then
2674 hr_utility.set_location(l_proc,80);
2675 end if;
2676 --
2677 if g_sql_run = 'Y' then
2678 --
2679 if g_log = 'Y' then
2680 --
2681 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' EE');
2682 --
2683 end if;
2684 --
2685 else
2686 --
2687 fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' EE');
2688 --
2689 end if;
2690 --
2691 else
2692 --
2693 if g_sql_run = 'Y' then
2694 --
2695 if g_log = 'Y' then
2696 --
2697 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' EE');
2698 --
2699 end if;
2700 --
2701 else
2702 --
2703 fnd_file.put_line(fnd_file.output, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' EE');
2704 --
2705 end if;
2706 --
2707 end if;
2708 --
2709 end if;
2710 --
2711 end if;
2712 --
2713 if g_detail_debug then
2714 hr_utility.set_location(l_proc,1000);
2715 end if;
2716 --
2717 exception
2718 when hr_api.object_locked then
2719 --
2720 if g_debug then
2721 hr_utility.set_location(l_proc,-1000);
2722 hr_utility.trace(to_char(p_assignment_id)||':'||fnd_message.get_string('FND','FND_LOCK_RECORD_ERROR'));
2723 end if;
2724 --
2725 if g_sql_run = 'Y' then
2726 --
2727 if g_log = 'Y' then
2728 --
2729 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||fnd_message.get_string('FND','FND_LOCK_RECORD_ERROR'));
2730 --
2731 end if;
2732 --
2733 else
2734 --
2735 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||fnd_message.get_string('FND','FND_LOCK_RECORD_ERROR'));
2736 --
2737 end if;
2738 --
2739 when others then
2740 --
2741 if g_debug then
2742 hr_utility.set_location(l_proc,-1000);
2743 hr_utility.trace(to_char(p_assignment_id)||':'||get_sqlerrm);
2744 end if;
2745 --
2746 if g_sql_run = 'Y' then
2747 --
2748 if g_log = 'Y' then
2749 --
2750 hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||get_sqlerrm);
2751 --
2752 end if;
2753 --
2754 else
2755 --
2756 fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||get_sqlerrm);
2757 --
2758 end if;
2759 --
2760 end mig_dep_assact;
2761 --
2762 -- -------------------------------------------------------------------------
2763 -- run_mig_dep
2764 -- -------------------------------------------------------------------------
2765 -- this is for manual run by script, recommend to use generic upgrade instead of this.
2766 procedure run_mig_dep
2767 is
2768 --
2769 l_proc varchar2(80) := c_package||'run_mig_dep';
2770 --
2771 l_range_ass_tbl_cnt number := 0;
2772 l_qualify_ass_tbl_cnt number := 0;
2773 l_qualify_valid_update varchar2(1) := 'N';
2774 --
2775 l_mig_cnt number := 0;
2776 l_mig_commit_cnt number := 1;
2777 --
2778 cursor csr_range_ass
2779 is
2780 select /*+ ORDERED
2781 INDEX(PA PER_ASSIGNMENTS_F_FK1) */
2782 pbg.business_group_id,
2783 pbg.name bg_name,
2784 pa.assignment_id,
2785 pa.assignment_number
2786 from per_business_groups_perf pbg,
2787 per_all_assignments_f pa
2788 where pbg.legislation_code = g_legislation_code
2789 and pa.business_group_id = pbg.business_group_id
2790 and pa.effective_start_date = (
2791 select /*+ INDEX(PA2 PER_ASSIGNMENTS_F_PK) */
2792 max(pa2.effective_start_date)
2793 from per_all_assignments_f pa2
2794 where pa2.assignment_id = pa.assignment_id);
2795 --
2796 l_csr_range_ass csr_range_ass%rowtype;
2797 --
2798 begin
2799 --
2800 -- --
2801 -- init
2802 -- --
2803 --
2804 g_sql_run := 'Y';
2805 --
2806 if g_sql_run = 'Y'
2807 and g_log = 'Y' then
2808 g_debug := false;
2809 end if;
2810 --
2811 if g_debug then
2812 hr_utility.set_location(l_proc,0);
2813 end if;
2814 --
2815 if g_skip_qualify is null then
2816 g_skip_qualify := 'N';
2817 end if;
2818 --
2819 if g_mig_date is null then
2820 g_mig_date:= c_mig_dep_sd;
2821 end if;
2822 --
2823 -- can set g_range_ass_dep_tbl by out of this procedure
2824 -- in case g_legislation_code is set.
2825 if g_legislation_code is null
2826 or g_legislation_code <> c_legislation_code then
2827 --
2828 g_range_ass_dep_tbl.delete;
2829 g_legislation_code := c_legislation_code;
2830 --
2831 end if;
2832 --
2833 g_dml_num := null;
2834 g_qualify_dep_ass_tbl.delete;
2835 l_range_ass_tbl_cnt := g_range_ass_dep_tbl.count;
2836 --
2837 set_dep_id;
2838 set_dep_mesg;
2839 --
2840 -- for api use
2841 insert_session(g_mig_date);
2842 --
2843 if g_debug then
2844 hr_utility.set_location(l_proc,10);
2845 end if;
2846 --
2847 -- --
2848 -- range
2849 -- --
2850 --
2851 if l_range_ass_tbl_cnt = 0 then
2852 --
2853 open csr_range_ass;
2854 loop
2855 --
2856 fetch csr_range_ass into l_csr_range_ass;
2857 exit when csr_range_ass%notfound;
2858 --
2859 g_range_ass_dep_tbl(l_range_ass_tbl_cnt).bg_id := l_csr_range_ass.business_group_id;
2860 g_range_ass_dep_tbl(l_range_ass_tbl_cnt).bg_name := l_csr_range_ass.bg_name;
2861 g_range_ass_dep_tbl(l_range_ass_tbl_cnt).ass_id := l_csr_range_ass.assignment_id;
2862 g_range_ass_dep_tbl(l_range_ass_tbl_cnt).ass_num := l_csr_range_ass.assignment_number;
2863 g_range_ass_dep_tbl(l_range_ass_tbl_cnt).upd_done := 'N';
2864 --
2865 l_range_ass_tbl_cnt := l_range_ass_tbl_cnt + 1;
2866 --
2867 end loop;
2868 close csr_range_ass;
2869 --
2870 end if;
2871 --
2872 if g_debug then
2873 --
2874 hr_utility.set_location(l_proc,15);
2875 hr_utility.trace('g_range_ass_dep_tbl.count : '||to_char(g_range_ass_dep_tbl.count));
2876 --
2877 if g_range_ass_dep_tbl.count > 0 then
2878 --
2879 for i in 0..g_range_ass_dep_tbl.count - 1 loop
2880 --
2881 if g_detail_debug then
2882 --
2883 hr_utility.trace(to_char(g_range_ass_dep_tbl(i).bg_id)
2884 ||':'||g_range_ass_dep_tbl(i).ass_num
2885 ||'('||to_char(g_range_ass_dep_tbl(i).ass_id)
2886 ||').'||g_range_ass_dep_tbl(i).upd_done);
2887 --
2888 end if;
2889 --
2890 end loop;
2891 --
2892 end if;
2893 --
2894 end if;
2895 --
2896 if g_debug then
2897 hr_utility.set_location(l_proc,20);
2898 end if;
2899 --
2900 -- --
2901 -- qualify
2902 -- --
2903 --
2904 if g_log = 'Y' then
2905 --
2906 hr_utility.trace('Business Group Name Assignment Number Message');
2907 hr_utility.trace('------------------------------ ------------------------------ --------------------------------------------------------------------------------');
2908 --
2909 end if;
2910 --
2911 if g_skip_qualify = 'N' then
2912 --
2913 if g_range_ass_dep_tbl.count > 0 then
2914 --
2915 for j in 0..g_range_ass_dep_tbl.count - 1 loop
2916 --
2917 val_mig_dep_assact(
2918 p_business_group_id => g_range_ass_dep_tbl(j).bg_id,
2919 p_business_group_name => g_range_ass_dep_tbl(j).bg_name,
2920 p_assignment_id => g_range_ass_dep_tbl(j).ass_id,
2921 p_assignment_number => g_range_ass_dep_tbl(j).ass_num,
2922 p_session_date => g_mig_date,
2923 p_valid_update => g_range_ass_dep_tbl(j).upd_done);
2924 --
2925 if l_qualify_valid_update = 'N'
2926 and g_range_ass_dep_tbl(j).upd_done = 'Y' then
2927 l_qualify_valid_update := 'Y';
2928 end if;
2929 --
2930 end loop;
2931 --
2932 end if;
2933 --
2934 else
2935 --
2936 l_qualify_valid_update := 'Y';
2937 --
2938 end if;
2939 --
2940 if l_qualify_valid_update = 'Y' then
2941 --
2942 if g_range_ass_dep_tbl.count > 0 then
2943 --
2944 for k in 0..g_range_ass_dep_tbl.count - 1 loop
2945 --
2946 if g_range_ass_dep_tbl(k).upd_done = 'Y'
2947 or g_skip_qualify = 'Y' then
2948 --
2949 g_qualify_dep_ass_tbl(l_qualify_ass_tbl_cnt).bg_id := g_range_ass_dep_tbl(k).bg_id;
2950 g_qualify_dep_ass_tbl(l_qualify_ass_tbl_cnt).bg_name := g_range_ass_dep_tbl(k).bg_name;
2951 g_qualify_dep_ass_tbl(l_qualify_ass_tbl_cnt).ass_id := g_range_ass_dep_tbl(k).ass_id;
2952 g_qualify_dep_ass_tbl(l_qualify_ass_tbl_cnt).ass_num := g_range_ass_dep_tbl(k).ass_num;
2953 g_qualify_dep_ass_tbl(l_qualify_ass_tbl_cnt).upd_done := 'N';
2954 --
2955 l_qualify_ass_tbl_cnt := l_qualify_ass_tbl_cnt + 1;
2956 --
2957 end if;
2958 --
2959 end loop;
2960 --
2961 end if;
2962 --
2963 if g_debug then
2964 --
2965 if g_qualify_dep_ass_tbl.count > 0 then
2966 --
2967 for l in 0..g_qualify_dep_ass_tbl.count - 1 loop
2968 --
2969 if g_detail_debug then
2970 --
2971 hr_utility.trace(to_char(g_qualify_dep_ass_tbl(l).bg_id)
2972 ||':'||g_qualify_dep_ass_tbl(l).ass_num
2973 ||'('||to_char(g_qualify_dep_ass_tbl(l).ass_id)
2974 ||').'||g_qualify_dep_ass_tbl(l).upd_done);
2975 --
2976 end if;
2977 --
2978 end loop;
2979 --
2980 end if;
2981 --
2982 end if;
2983 --
2984 end if;
2985 --
2986 if g_debug then
2987 hr_utility.set_location(l_proc,30);
2988 end if;
2989 --
2990 -- --
2991 -- upgrade
2992 -- --
2993 --
2994 if g_log = 'Y' then
2995 --
2996 hr_utility.trace('----------------------------------------------------------------------------------------------------------------------------------------------');
2997 hr_utility.trace('Business Group Name Assignment Number Age Type Contact');
2998 hr_utility.trace('------------------------------ ------------------------- ---- ------ ------------------------------------------------------------');
2999 --
3000 end if;
3001 --
3002 if l_qualify_valid_update = 'Y' then
3003 --
3004 if g_qualify_dep_ass_tbl.count > 0 then
3005 --
3006 for m in 0..g_qualify_dep_ass_tbl.count - 1 loop
3007 --
3008 mig_dep_assact(
3009 p_business_group_id => g_qualify_dep_ass_tbl(m).bg_id,
3010 p_business_group_name => g_qualify_dep_ass_tbl(m).bg_name,
3011 p_assignment_id => g_qualify_dep_ass_tbl(m).ass_id,
3012 p_assignment_number => g_qualify_dep_ass_tbl(m).ass_num,
3013 p_session_date => g_mig_date);
3014 --
3015 l_mig_cnt := l_mig_cnt + 1;
3016 --
3017 if g_sql_run = 'Y' then
3018 --
3019 g_dml_num := g_dml_num + (l_mig_cnt - c_commit_num * (l_mig_commit_cnt - 1));
3020 --
3021 if g_dml_num > c_commit_num then
3022 commit;
3023 g_dml_num := 0;
3024 l_mig_commit_cnt := l_mig_commit_cnt + 1;
3025 end if;
3026 --
3027 end if;
3028 --
3029 end loop;
3030 --
3031 end if;
3032 --
3033 end if;
3034 --
3035 if g_debug then
3036 hr_utility.set_location(l_proc,40);
3037 end if;
3038 --
3039 -- --
3040 -- deinitialize
3041 -- --
3042 --
3043 if g_log = 'Y' then
3044 --
3045 hr_utility.trace('----------------------------------------------------------------------------------------------------------------------------------------------');
3046 --
3047 end if;
3048 --
3049 delete_session;
3050 --
3051 if l_qualify_valid_update = 'Y' then
3052 --
3053 commit;
3054 --
3055 end if;
3056 --
3057 if g_debug then
3058 hr_utility.set_location(l_proc,1000);
3059 end if;
3060 --
3061 end run_mig_dep;
3062 --
3063 END HR_JP_DATA_MIGRATION_PKG;
3064 --