eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HR_JP_DATA_MIGRATION_PKG

Source


1 PACKAGE BODY HR_JP_DATA_MIGRATION_PKG AS
2 /* $Header: hrjpdtmg.pkb 120.0.12000000.6 2007/03/13 07:26:46 keyazawa ship $ */
3 --
4 c_package	constant varchar2(31) := 'hr_jp_data_migration_pkg.';
5 --
6 c_legislation_code constant varchar2(2) := 'JP';
7 c_commit_num constant number := 1000;
8 c_skip_warning varchar2(10) := 'FALSE';
9 --
10 c_mig_smr_sd date := to_date('2007/04/01','YYYY/MM/DD');
11 --
12 c_com_hi_smr_elm pay_element_types_f.element_name%type := 'COM_HI_SMR_INFO';
13 c_am_iv    pay_input_values_f.name%type := 'APPLY_MTH';
14 c_mr_iv    pay_input_values_f.name%type := 'REVISED_MR';
15 c_mr_o_iv  pay_input_values_f.name%type := 'PRIOR_MR';
16 c_smr_iv   pay_input_values_f.name%type := 'REVISED_SMR';
17 c_smr_o_iv pay_input_values_f.name%type := 'PRIOR_SMR';
18 c_at_iv    pay_input_values_f.name%type := 'APPLY_TYPE';
19 --
20 c_com_hi_smr_elm_id number;
21 c_am_iv_id    number;
22 c_mr_iv_id    number;
23 c_mr_o_iv_id  number;
24 c_smr_iv_id   number;
25 c_smr_o_iv_id number;
26 c_at_iv_id    number;
27 --
28 c_com_smr_tbl pay_user_tables.user_table_name%type  := 'T_COM_SMR';
29 c_hi_smr_col pay_user_columns.user_column_name%type := 'HI_SMR';
30 --
31 c_new_smr_min_high number := 93000;
32 c_new_smr_max_low  number := 1005000;
33 --
34 c_fut_exist_mesg   fnd_new_messages.message_text%type;
35 c_already_upd_mesg fnd_new_messages.message_text%type;
36 c_fut_am_mesg      fnd_new_messages.message_text%type;
37 c_am_null_mesg     fnd_new_messages.message_text%type;
38 c_mr_null_mesg     fnd_new_messages.message_text%type;
39 --
40 g_dml_num number;
41 --
42 g_qualify_ini_ass_id number;
43 g_migrate_ini_ass_id number;
44 --
45 g_ass_info t_ass_hi_smr_rec;
46 --
47 g_qualify_hi_smr_ass_tbl t_ass_hi_smr_tbl;
48 --
49 --------------------------------------------------------------------------------
50 -- p_mode is ELE_RR_COPY_TO then copy record pay_run_results and pay_run_result_values
51 --      from existing element to new element
52 
53   PROCEDURE element_run_result_copy(
54     p_mode        IN  VARCHAR2,
55     p_parameter_name  IN  VARCHAR2,
56     p_parameter_value IN  NUMBER)
57 --------------------------------------------------------------------------------
58 IS
59   l_element_type_id_from    NUMBER;
60   l_element_type_id_to    NUMBER;
61 
62   CURSOR  csr_related_iv IS
63     select  hjp1.parameter_value  iv_mode,
64         hjp2.parameter_name   iv_name,
65         hjp2.parameter_value  iv_id_to,
66         hjp3.parameter_value  iv_id_from
67     from  hr_jp_parameters hjp1,
68         hr_jp_parameters hjp2,
69         hr_jp_parameters hjp3
70     where hjp1.owner = p_parameter_name
71     and   hjp2.owner = hjp1.parameter_value
72     and   hjp2.parameter_name = hjp1.parameter_name
73     and   hjp3.owner(+) = 'IV_COPY_FROM'
74     and   hjp3.parameter_name(+) = hjp2.parameter_name;
75 
76 --
77 BEGIN
78 
79   hr_utility.set_location('Start ' || p_mode || p_parameter_name, 5);
80   if p_mode = 'ELE_RR_COPY_TO' then
81     -- find ELE_RR_COPY_FROM element_type_id
82     l_element_type_id_to := p_parameter_value;
83   --
84     l_element_type_id_from := hr_jp_parameters_pkg.get_parameter_value('ELE_RR_COPY_FROM',p_parameter_name);
85 
86     if l_element_type_id_from is not null then
87       -- copy run result
88 
89       insert into pay_run_results (
90         RUN_RESULT_ID,
91         ELEMENT_TYPE_ID,
92         ASSIGNMENT_ACTION_ID,
93         ENTRY_TYPE,
94         SOURCE_ID,
95         SOURCE_TYPE,
96         STATUS)
97       select  /*+ INDEX(PRR_FROM PAY_RUN_RESULTS_N1) */
98         pay_run_results_s.nextval,
99         l_element_type_id_to,
100         prr_from.assignment_action_id,
101         prr_from.entry_type,
102         prr_from.source_id,
103         prr_from.source_type,
104         prr_from.status
105       from  pay_run_results prr_from
106       where prr_from.element_type_id = l_element_type_id_from
107       and not exists(
108           select  /*+ INDEX(PRR_TO PAY_RUN_RESULTS_N50) */
109             NULL
110           from  pay_run_results prr_to
111           where prr_to.assignment_action_id = prr_from.assignment_action_id
112           and prr_to.element_type_id = l_element_type_id_to);
113 
114       hr_utility.trace('Successefully  created run_result');
115 
116       -- get related input values
117       for rec_related_iv in csr_related_iv
118       loop
119         if rec_related_iv.iv_mode = 'IV_COPY_TO' then
120           insert into pay_run_result_values (
121               INPUT_VALUE_ID,
122               RUN_RESULT_ID,
123               RESULT_VALUE)
124           select  /*+ ORDERED
125             INDEX(FROM_ELE_PRR PAY_RUN_RESULTS_N1)
126             INDEX(PRRV_FROM PAY_RUN_RESULT_VALUES_PK)
127             INDEX(TO_ELE_PRR PAY_RUN_RESULTS_N50)
128             USE_NL(prrv_from to_ele_prr) */
129             rec_related_iv.iv_id_to,
130             to_ele_prr.run_result_id,
131             prrv_from.result_value
132           from  pay_run_results   from_ele_prr,
133             pay_run_result_values prrv_from,
134             pay_run_results   to_ele_prr
135           where from_ele_prr.element_type_id = l_element_type_id_from
136           and prrv_from.run_result_id = from_ele_prr.run_result_id
137           and prrv_from.input_value_id = rec_related_iv.iv_id_from
138           and to_ele_prr.assignment_action_id = from_ele_prr.assignment_action_id
139           and to_ele_prr.element_type_id = l_element_type_id_to
140           and not exists(
141               select  NULL
142               from  pay_run_result_values prrv_to
143               where prrv_to.run_result_id = to_ele_prr.run_result_id
144               and prrv_to.input_value_id = rec_related_iv.iv_id_to);
145 
146         end if;
147         if rec_related_iv.iv_mode = 'ADD_NEW_IV' then
148           insert into pay_run_result_values (
149             INPUT_VALUE_ID,
150             RUN_RESULT_ID,
151             RESULT_VALUE)
152           select  /*+ INDEX(TO_ELE_PRR PAY_RUN_RESULTS_N1) */
153             rec_related_iv.iv_id_to,
154             to_ele_prr.run_result_id,
155             NULL
156           from  pay_run_results to_ele_prr
157           where to_ele_prr.element_type_id = l_element_type_id_to
158           and not exists(
159               select  NULL
160               from  pay_run_result_values prrv_to
161               where prrv_to.run_result_id = to_ele_prr.run_result_id
162               and prrv_to.input_value_id = rec_related_iv.iv_id_to);
163 
164         end if;
165       end loop;
166       hr_utility.trace('Successefully  created run_result_value');
167     end if;
168   end if;
169 END;
170 
171 --------------------------------------------------------------------------------
172 -- p_mode is ADD_NEW_IV then insert pay_link_input_value and pay_element_entry_values_f
173 --
174   PROCEDURE add_new_input_value(
175     p_mode        IN  VARCHAR2,
176     p_parameter_name  IN  VARCHAR2,
177     p_parameter_value IN  NUMBER)
178 --------------------------------------------------------------------------------
179 IS
180   l_input_value_id_to     NUMBER;
181   l_costed_flag       VARCHAR2(30);
182   l_total_upd_actions     NUMBER := 0;
183 
184   CURSOR  csr_run_result_id  IS
185     select  /*+ ORDERED
186                     INDEX(PIV PAY_INPUT_VALUES_F_PK)
187                     INDEX(PET PAY_ELEMENT_TYPES_F_PK)
188                     INDEX(PRR PAY_RUN_RESULTS_N1) */
189             prr.run_result_id   run_result_id
190     from  pay_input_values_f    piv,
191                 pay_element_types_f   pet,
192         pay_run_results     prr
193     where piv.input_value_id = p_parameter_value
194     and   pet.element_type_id = piv.element_type_id
195     and   piv.effective_start_date
196         between pet.effective_start_date and pet.effective_end_date
197     and   prr.element_type_id = pet.element_type_id
198     and not exists(
199         select  /*+ INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) */
200                         NULL
201         from  pay_run_result_values prrv
202         where prrv.run_result_id=prr.run_result_id
203         and   prrv.input_value_id=l_input_value_id_to);
204 
205   CURSOR  csr_element_link_id IS
206   select  /*+ ORDERED
207                 INDEX(PIV PAY_INPUT_VALUES_F_PK)
208                 INDEX(PET PAY_ELEMENT_TYPES_F_PK)
209                 INDEX(PEL PAY_ELEMENT_LINKS_F_N7) */
210         pel.rowid       row_id,
211       pel.element_link_id   element_link_id,
212       pel.costable_type   costable_type,
213       piv.name        input_value_name,
214       piv.effective_start_date      effective_start_date,
215       piv.effective_end_date        effective_end_date,
216       piv.default_value   default_value,
217       piv.max_value     max_value,
218       piv.min_value     min_value,
219       piv.warning_or_error  warning_or_error
220   from  pay_input_values_f  piv,
221             pay_element_types_f pet,
222       pay_element_links_f pel
223   where piv.input_value_id = p_parameter_value
224   and   pet.element_type_id = piv.element_type_id
225   and   piv.effective_start_date
226       between pet.effective_start_date and pet.effective_end_date
227   and   pel.element_type_id = pet.element_type_id
228   and   pel.effective_start_date    <= piv.effective_end_date
229   and   pel.effective_end_date      >= piv.effective_start_date;
230 
231   CURSOR csr_element_entry_id(
232     p_element_link_id IN NUMBER,
233     p_input_value_id  IN NUMBER)
234    IS
235     select  /*+ INDEX(PEE PAY_ELEMENT_ENTRIES_F_N4) */
236         pee.rowid   row_id,
237         pee.element_entry_id  element_entry_id
238     from  pay_element_entries_f pee
239     where pee.element_link_id = p_element_link_id
240     and not exists(select /*+ INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
241                         NULL
242         from  pay_element_entry_values_f peev
243         where peev.element_entry_id = pee.element_entry_id
244         and   peev.input_value_id = p_input_value_id
245         and   peev.effective_start_date = pee.effective_start_date
246         and   peev.effective_end_date = pee.effective_end_date);
247 
248 --
249 BEGIN
250 
251   hr_utility.set_location('Start procedure in the hr_jp_data_migration_pkg' || p_mode || p_parameter_name, 5);
252   if p_mode = 'ADD_NEW_IV' then
253     l_input_value_id_to := p_parameter_value;
254 
255     -- find element_link_id of parent element
256     for rec_element_link in csr_element_link_id
257     loop
258       if rec_element_link.costable_type in ('F', 'C', 'D')
259           and rec_element_link.input_value_name = hr_general.pay_value then
260         l_costed_flag := 'Y';
261       else
262         l_costed_flag := 'N';
263       end if;
264       insert  into  pay_link_input_values_f
265         (LINK_INPUT_VALUE_ID,
266          EFFECTIVE_START_DATE,
267          EFFECTIVE_END_DATE,
268          ELEMENT_LINK_ID,
269          INPUT_VALUE_ID,
270          COSTED_FLAG,
271          DEFAULT_VALUE,
272          MAX_VALUE,
273          MIN_VALUE,
274          WARNING_OR_ERROR,
275          LAST_UPDATE_DATE,
276          LAST_UPDATED_BY,
277          LAST_UPDATE_LOGIN,
278          CREATED_BY,
279          CREATION_DATE)
280        select PAY_LINK_INPUT_VALUES_S.nextval,
281         greatest(pel.effective_start_date,rec_element_link.effective_start_date),
282         least(pel.effective_end_date,rec_element_link.effective_end_date),
283         rec_element_link.element_link_id,
284         l_input_value_id_to,
285         l_costed_flag,
286         rec_element_link.default_value,
287         rec_element_link.max_value,
288         rec_element_link.min_value,
289         rec_element_link.warning_or_error,
290         pel.last_update_date,
291         pel.last_updated_by,
292         pel.last_update_login,
293         NULL,
294         pel.creation_date
295       from  pay_element_links_f pel
296       where pel.rowid=rec_element_link.row_id
297       and not exists(
298           select  null
299           from  pay_link_input_values_f
300           where element_link_id = rec_element_link.element_link_id
301           and   input_value_id = l_input_value_id_to);
302       hr_utility.trace('Successefully  created link_input_value '|| to_char(rec_element_link.element_link_id));
303 
304       for rec_element_entry in csr_element_entry_id(rec_element_link.element_link_id,l_input_value_id_to) loop
305         insert into pay_element_entry_values_f(
306           ELEMENT_ENTRY_VALUE_ID,
307           EFFECTIVE_START_DATE,
308           EFFECTIVE_END_DATE,
309           INPUT_VALUE_ID,
310           ELEMENT_ENTRY_ID,
311           SCREEN_ENTRY_VALUE)
312         select  pay_element_entry_values_s.nextval,
313           pee.effective_start_date,
314           pee.effective_end_date,
315           l_input_value_id_to,
316           pee.element_entry_id,
317           NULL
318         from  pay_element_entries_f pee
319         where pee.rowid=rec_element_entry.row_id;
320 
321         l_total_upd_actions := l_total_upd_actions + 1;
322         if l_total_upd_actions > 1000 then
323           commit;
324           l_total_upd_actions := 0;
325         end if;
326       end loop;
327       hr_utility.trace('Successefully  created element_entry_value');
328       if l_total_upd_actions > 0 then
329         l_total_upd_actions := 0;
330         commit;
331       end if;
332     end loop;
333 
334     -- find run_result_id of parent element
335     for rec_run_result_id in csr_run_result_id
336     loop
337       insert into pay_run_result_values (
338           INPUT_VALUE_ID,
339           RUN_RESULT_ID,
340           RESULT_VALUE)
341       values(l_input_value_id_to,
342           rec_run_result_id.run_result_id,
343           NULL);
344 
345       l_total_upd_actions := l_total_upd_actions + 1;
346       if l_total_upd_actions > 1000 then
347         commit;
348         l_total_upd_actions := 0;
349       end if;
350     end loop;
351     hr_utility.trace('Successefully  created run_result_value '|| to_char(l_input_value_id_to));
352 
353     if l_total_upd_actions > 0 then
354       commit;
355     end if;
356   end if;
357 END;
358 
359 --------------------------------------------------------------------------------
360 -- This procedure will update or delete element entry for obsolete elements in R11i.
361 --    -If the element entry exist on sysdate then effective_end_date set to sysdate.
362 --    -If the element entry exist later than sysdate then these records will be purged.
363 --
364   PROCEDURE end_element_entry(
365       p_mode        IN VARCHAR2,
366       p_parameter_name  IN VARCHAR2,
367       p_parameter_value IN NUMBER,
368       p_session_date    IN DATE)
369 --------------------------------------------------------------------------------
370 IS
371   l_element_type_id   NUMBER;
372   l_delete_mode     VARCHAR2(10);
373   l_target_date     DATE;
374   l_total_upd_actions   NUMBER := 0;
375   v_entry_id        NUMBER;
376 
377   CURSOR  csr_element_link_id IS
378     select  element_link_id
379     from  pay_element_links_f
380     where element_type_id=l_element_type_id;
381 
382   CURSOR csr_element_entry_id(
383     p_element_link_id IN NUMBER,
384     p_session_date    IN DATE)
385   IS
386     select  pee.element_entry_id,
387         pee.effective_start_date
388     from  pay_element_entries_f pee
389     where pee.element_link_id=p_element_link_id
390     and   p_session_date<pee.effective_end_date;
391 
392   CURSOR csr_chk_entry(
393     p_element_entry_id  IN NUMBER,
394     p_session_date    IN DATE)
395   IS
396     select  pee.element_entry_id
397     from  pay_element_entries_f pee
398     where pee.element_entry_id=p_element_entry_id
399     and p_session_date
400       between pee.effective_start_date and pee.effective_end_date;
401 
402 BEGIN
403   hr_utility.set_location('Start procedure in the hr_jp_data_migration_pkg' || p_mode || p_parameter_name, 5);
404   if p_mode = 'ELE_END_ENTRY' then
405     l_element_type_id := p_parameter_value;
406     for rec_element_link in csr_element_link_id
407     loop
408       for rec_element_entry in csr_element_entry_id(rec_element_link.element_link_id,p_session_date) loop
409         open csr_chk_entry(rec_element_entry.element_entry_id,rec_element_entry.effective_start_date);
410         fetch csr_chk_entry into v_entry_id;
411         if csr_chk_entry%found then
412           close csr_chk_entry;
413           if rec_element_entry.effective_start_date > p_session_date then
414             l_delete_mode := 'ZAP';
415             l_target_date := rec_element_entry.effective_start_date;
416           else
417             l_delete_mode := 'DELETE';
418             l_target_date := p_session_date;
419           end if;
420           hr_entry_api.delete_element_entry(l_delete_mode,l_target_date,rec_element_entry.element_entry_id);
421           l_total_upd_actions := l_total_upd_actions + 1;
422         else
423           close csr_chk_entry;
424         end if;
425         if l_total_upd_actions > 1000 then
426           commit;
427           l_total_upd_actions := 0;
428         end if;
429       end loop;
430       if l_total_upd_actions > 0 then
431         commit;
432       end if;
433     end loop;
434   end if;
435 END;
436 --
437 --
438 -- -------------------------------------------------------------------------
439 -- get_ass_info
440 -- -------------------------------------------------------------------------
441 function get_ass_info(
442   p_assignment_id  in number,
443   p_effective_date in date)
444 return t_ass_hi_smr_rec
445 is
446 --
447   l_proc varchar2(80) := c_package||'get_ass_info';
448 --
449   cursor csr_ass
450   is
451   select /*+ ORDERED
452              INDEX(PA PER_ASSIGNMENTS_F_PK) */
453          pbg.business_group_id bg_id,
454          pbg.name bg_name,
455          pa.assignment_number ass_num
456   from   per_all_assignments_f pa,
457          per_business_groups_perf pbg
458   where  pa.assignment_id = p_assignment_id
459   and    p_effective_date
460          between pa.effective_start_date and pa.effective_end_date
461   and    pbg.business_group_id = pa.business_group_id;
462 --
463   l_csr_ass csr_ass%rowtype;
464 --
465 begin
466 --
467   if g_debug then
468     hr_utility.set_location(l_proc,0);
469     hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
470   end if;
471 --
472   if g_ass_info.ass_id <> p_assignment_id
473   or g_ass_info.ass_id is null then
474   --
475     open csr_ass;
476     fetch csr_ass into l_csr_ass;
477     close csr_ass;
478   --
479     g_ass_info.ass_id  := p_assignment_id;
480     g_ass_info.ass_num := l_csr_ass.ass_num;
481     g_ass_info.bg_id   := l_csr_ass.bg_id;
482     g_ass_info.bg_name := l_csr_ass.bg_name;
483   --
484   end if;
485 --
486   if g_debug then
487     hr_utility.set_location(l_proc,1000);
488   end if;
489 --
490 return g_ass_info;
491 --
492 end get_ass_info;
493 --
494 -- -------------------------------------------------------------------------
495 -- set_hi_smr_id
496 -- -------------------------------------------------------------------------
497 procedure set_hi_smr_id
498 is
499 --
500   l_proc varchar2(80) := c_package||'set_hi_smr_id';
501 --
502 begin
503 --
504   if g_debug then
505     hr_utility.set_location(l_proc,0);
506   end if;
507 --
508   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);
509   c_am_iv_id    := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_am_iv,c_skip_warning);
510   c_mr_iv_id    := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_mr_iv,c_skip_warning);
511   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);
512   c_smr_iv_id   := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_smr_iv,c_skip_warning);
513   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);
514   c_at_iv_id    := hr_jp_id_pkg.input_value_id(c_com_hi_smr_elm_id,c_at_iv,c_skip_warning);
515 --
516   if g_debug then
517     hr_utility.trace('c_com_hi_smr_elm_id : '||to_char(c_com_hi_smr_elm_id));
518     hr_utility.trace('c_am_iv_id          : '||to_char(c_am_iv_id));
519     hr_utility.trace('c_mr_iv_id          : '||to_char(c_mr_iv_id));
520     hr_utility.trace('c_mr_o_iv_id        : '||to_char(c_mr_o_iv_id));
521     hr_utility.trace('c_smr_iv_id         : '||to_char(c_smr_iv_id));
522     hr_utility.trace('c_smr_o_iv_id       : '||to_char(c_smr_o_iv_id));
523     hr_utility.trace('c_at_iv_id          : '||to_char(c_at_iv_id));
524     hr_utility.set_location(l_proc,1000);
525   end if;
526 --
527 end set_hi_smr_id;
528 --
529 -- -------------------------------------------------------------------------
530 -- set_hi_smr_mesg
531 -- -------------------------------------------------------------------------
532 procedure set_hi_smr_mesg
533 is
534 --
535   l_proc varchar2(80) := c_package||'set_hi_smr_mesg';
536 --
537 begin
538 --
539   if g_debug then
540     hr_utility.set_location(l_proc,0);
541   end if;
542 --
543     fnd_message.set_name('PAY','PAY_JP_MIG_SMR_FUT_EXIST');
544     c_fut_exist_mesg := fnd_message.get;
545   --
546     fnd_message.set_name('PAY','PAY_JP_MIG_SMR_ALREADY_UPD');
547     c_already_upd_mesg := fnd_message.get;
548   --
549     fnd_message.set_name('PAY','PAY_JP_MIG_SMR_FUT_AM');
550     c_fut_am_mesg := fnd_message.get;
551   --
552     fnd_message.set_name('PAY','PAY_JP_MIG_SMR_AM_NULL');
553     c_am_null_mesg := fnd_message.get;
554   --
555     fnd_message.set_name('PAY','PAY_JP_MIG_SMR_MR_NULL');
556     c_mr_null_mesg := fnd_message.get;
557 --
558   if g_debug then
559     hr_utility.trace('c_fut_exist_mesg   : '||c_fut_exist_mesg);
560     hr_utility.trace('c_already_upd_mesg : '||c_already_upd_mesg);
561     hr_utility.trace('c_fut_am_mesg      : '||c_fut_am_mesg);
562     hr_utility.trace('c_am_null_mesg     : '||c_am_null_mesg);
563     hr_utility.trace('c_mr_null_mesg     : '||c_mr_null_mesg);
564     hr_utility.set_location(l_proc,1000);
565   end if;
566 --
567 end set_hi_smr_mesg;
568 --
569 -- -------------------------------------------------------------------------
570 -- get_sqlerrm
571 -- -------------------------------------------------------------------------
572 function get_sqlerrm
573 return varchar2
574 is
575 begin
576 --
577   if sqlcode = -20001 then
578   --
579     declare
580       l_sqlerrm varchar2(2000) := fnd_message.get;
581     begin
582       if l_sqlerrm is not null then
583         return l_sqlerrm;
584       else
585         return sqlerrm;
586       end if;
587     end;
588   --
589   else
590     return sqlerrm;
591   end if;
592 --
593 end get_sqlerrm;
594 --
595 -- -------------------------------------------------------------------------
596 -- get_mig_date
597 -- -------------------------------------------------------------------------
598 function get_mig_date
599 return date
600 is
601 --
602   l_proc varchar2(80) := c_package||'get_mig_date';
603 --
604 begin
605 --
606   if g_debug then
607     hr_utility.set_location(l_proc,0);
608     hr_utility.trace('g_mig_date : '||to_char(g_mig_date,'YYYY/MM/DD'));
609   end if;
610 --
611   if g_debug then
612     hr_utility.set_location(l_proc,1000);
613   end if;
614 --
615 return g_mig_date;
616 --
617 end get_mig_date;
618 --
619 -- -------------------------------------------------------------------------
620 -- insert_session
621 -- -------------------------------------------------------------------------
622 procedure insert_session(
623             p_effective_date in date)
624 is
625 --
626   l_rowid rowid;
627 --
628   cursor csr_session
629   is
630   select rowid
631   from   fnd_sessions
632   where  session_id = userenv('sessionid')
633   for update nowait;
634 --
635 begin
636 --
637   open csr_session;
638   fetch csr_session into l_rowid;
639   --
640     if csr_session%notfound then
641     --
642       insert into fnd_sessions(
643         session_id,
644         effective_date)
645       values(
646         userenv('sessionid'),
647         p_effective_date);
648     --
649     else
650     --
651       update fnd_sessions
652       set    effective_date = p_effective_date
653       where rowid = l_rowid;
654     --
655     end if;
656   --
657   close csr_session;
658 --
659 end insert_session;
660 --
661 -- -------------------------------------------------------------------------
662 -- delete_session
663 -- -------------------------------------------------------------------------
664 procedure delete_session
665 is
666 begin
667 --
668   delete
669   from  fnd_sessions
670   where session_id = userenv('sessionid');
671 --
672 end delete_session;
673 --
674 -- -------------------------------------------------------------------------
675 -- qualify_hi_smr_hd
676 -- -------------------------------------------------------------------------
677 -- run by qualify_hi_smr_data
678 procedure qualify_hi_smr_hd(
679   p_assignment_id in number)
680 is
681 --
682   l_proc varchar2(80) := c_package||'qualify_hi_smr_hd';
683 --
684 begin
685 --
686   if g_debug then
687     hr_utility.set_location(l_proc,0);
688     hr_utility.trace('g_qualify_ini_ass_id : '||to_char(g_qualify_ini_ass_id));
689   end if;
690 --
691   if g_qualify_ini_ass_id is null
692   or g_qualify_ini_ass_id = p_assignment_id then
693   --
694     fnd_file.put_line(fnd_file.log, 'Business Group Name            Assignment Number              Message');
695     fnd_file.put_line(fnd_file.log, '------------------------------ ------------------------------ --------------------------------------------------------------------------------');
696   --
697     g_qualify_ini_ass_id := p_assignment_id;
698   --
699   end if;
700 --
701   if g_debug then
702     hr_utility.trace('g_qualify_ini_ass_id : '||to_char(g_qualify_ini_ass_id));
703     hr_utility.set_location(l_proc,1000);
704   end if;
705 --
706 end qualify_hi_smr_hd;
707 --
708 -- -------------------------------------------------------------------------
709 -- migrate_hi_smr_hd
710 -- -------------------------------------------------------------------------
711 -- run by migrate_hi_smr_data
712 procedure migrate_hi_smr_hd(
713   p_assignment_id in number)
714 is
715 --
716   l_proc varchar2(80) := c_package||'migrate_hi_smr_hd';
717 --
718 begin
719 --
720   if g_debug then
721     hr_utility.set_location(l_proc,0);
722     hr_utility.trace('g_migrate_ini_ass_id : '||to_char(g_migrate_ini_ass_id));
723   end if;
724 --
725   if g_migrate_ini_ass_id is null
726   or g_migrate_ini_ass_id = p_assignment_id then
727   --
728     fnd_file.put_line(fnd_file.output, 'Business Group Name            Assignment Number                        MR        SMR    Exp SMR');
729     fnd_file.put_line(fnd_file.output, '------------------------------ ------------------------------ ------------ ---------- ----------');
730   --
731     g_migrate_ini_ass_id := p_assignment_id;
732   --
733   end if;
734 --
735   if g_debug then
736     hr_utility.trace('g_migrate_ini_ass_id : '||to_char(g_migrate_ini_ass_id));
737     hr_utility.set_location(l_proc,1000);
738   end if;
739 --
740 end migrate_hi_smr_hd;
741 --
742 -- -------------------------------------------------------------------------
743 -- init_def_hi_smr_data
744 -- -------------------------------------------------------------------------
745 -- run by qualify_hi_smr_data
746 procedure init_def_hi_smr_data
747 is
748 --
749   l_proc varchar2(80) := c_package||'init_def_hi_smr_data';
750 --
751 begin
752 --
753   if g_debug then
754     hr_utility.set_location(l_proc,0);
755     hr_utility.trace('g_legislation_code : '||g_legislation_code);
756   end if;
757 --
758   -- temporary solution because of no initialize.
759   -- run once for each thread.
760   if g_legislation_code is null
761   or g_legislation_code <> c_legislation_code then
762   --
763     -- no support of legsilative parameter at this moment.
764     g_skip_qualify := 'N';
765     g_upd_mode     := 'UPDATE';
766     g_mig_date     := c_mig_smr_sd;
767   --
768     set_hi_smr_id;
769     set_hi_smr_mesg;
770   --
771     g_legislation_code := c_legislation_code;
772   --
773   end if;
774 --
775   if g_debug then
776     hr_utility.trace('g_legislation_code : '||g_legislation_code);
777     hr_utility.set_location(l_proc,1000);
778   end if;
779 --
780 end init_def_hi_smr_data;
781 --
782 -- -------------------------------------------------------------------------
783 -- val_mig_smr_assact
784 -- -------------------------------------------------------------------------
785 procedure val_mig_smr_assact(
786   p_business_group_id   in number,
787   p_business_group_name in varchar2,
788   p_assignment_id       in number,
789   p_assignment_number   in varchar2,
790   p_session_date        in date,
791   p_valid_delete        in out nocopy varchar2)
792 is
793 --
794   l_proc varchar2(80) := c_package||'val_mig_smr_assact';
795 --
796   l_am_eev pay_element_entry_values_f.screen_entry_value%type;
797   l_mr_eev pay_element_entry_values_f.screen_entry_value%type;
798   l_ee_esd date;
799   l_ee_upd_id number;
800   l_ft_ee_esd date;
801   l_am_date date;
802 --
803   l_valid_delete varchar2(1) := 'N';
804 --
805   cursor csr_ee_esd
806   is
807   select /*+ ORDERED
808              USE_NL(PLIV, PEE)
809              INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
810              INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
811          pee.effective_start_date,
812          pee.updating_action_id
813   from   pay_link_input_values_f pliv,
814          pay_element_entries_f pee
815   where  pliv.input_value_id = c_smr_iv_id
816   and    p_session_date
817          between pliv.effective_start_date and pliv.effective_end_date
818   and    pee.element_link_id = pliv.element_link_id
819   and    pee.assignment_id = p_assignment_id
820   and    p_session_date
821          between pee.effective_start_date and pee.effective_end_date;
822 --
823   cursor csr_ft_ee
824   is
825   select /*+ ORDERED
826              USE_NL(PLIV, PEE)
827              INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
828              INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
829          pee.effective_start_date
830   from   pay_link_input_values_f pliv,
831          pay_element_entries_f pee
832   where  pliv.input_value_id = c_smr_iv_id
833   and    p_session_date
834          between pliv.effective_start_date and pliv.effective_end_date
835   and    pee.element_link_id = pliv.element_link_id
836   and    pee.assignment_id = p_assignment_id
837   and    pee.effective_start_date > p_session_date;
838 --
839 begin
840 --
841   if g_detail_debug then
842     hr_utility.set_location(l_proc,0);
843     hr_utility.trace(p_business_group_id ||' : '||p_assignment_id||'('||p_assignment_number||')');
844   end if;
845 --
846   if p_valid_delete = 'N' then
847   --
848   -- skip ee not exist.
849   -- skip already updated (manual update)
850   --
851     open csr_ee_esd;
852     fetch csr_ee_esd into l_ee_esd, l_ee_upd_id;
853     close csr_ee_esd;
854   --
855     if l_ee_esd is not null then
856     --
857       l_valid_delete := 'Y';
858     --
859     end if;
860   --
861     if g_skip_manual_upd = 'Y' then
862     --
863       if l_ee_esd = p_session_date
864       and l_ee_esd is not null
865       and l_ee_upd_id is null then
866       --
867         l_valid_delete := 'N';
868       --
869         if g_sql_run = 'Y' then
870         --
871           if g_log = 'Y' then
872           --
873             hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_already_upd_mesg);
874           --
875           end if;
876         --
877         else
878         --
879           fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_already_upd_mesg);
880         --
881         end if;
882       --
883       end if;
884     --
885     end if;
886   --
887     if g_detail_debug then
888       hr_utility.set_location(l_proc,10);
889       hr_utility.trace('skip manual upd : l_valid_delete : '||l_valid_delete);
890     end if;
891   --
892   -- skip future entry exists
893   --
894     if l_valid_delete = 'Y' then
895     --
896       open csr_ft_ee;
897       fetch csr_ft_ee into l_ft_ee_esd;
898       close csr_ft_ee;
899     --
900       if l_ft_ee_esd is not null then
901       --
902         l_valid_delete := 'N';
903       --
904         if g_sql_run = 'Y' then
905         --
906           if g_log = 'Y' then
907           --
908             hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_fut_exist_mesg);
909           --
910           end if;
911         --
912         else
913         --
914           fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_fut_exist_mesg);
915         --
916         end if;
917       --
918       end if;
919     --
920     end if;
921   --
922     if g_detail_debug then
923       hr_utility.set_location(l_proc,20);
924       hr_utility.trace('skip future entry : l_valid_delete : '||l_valid_delete);
925     end if;
926   --
927   -- skip in update mode applied month is future (>= p_session_date)
928   --
929     if l_valid_delete = 'Y'
930     and g_upd_mode <> 'OVERRIDE' then
931     --
932       l_am_eev := pay_jp_balance_pkg.get_entry_value_char(
933                      p_input_value_id => c_am_iv_id,
934                      p_assignment_id  => p_assignment_id,
935                      p_effective_date => p_session_date);
936     --
937       if l_am_eev is not null then
938       --
939         l_am_date := to_date(l_am_eev||'01','YYYYMMDD');
940       --
941         if trunc(l_am_date,'DD') >= trunc(p_session_date,'DD') then
942         --
943           l_valid_delete := 'N';
944         --
945           if g_sql_run = 'Y' then
946           --
947             if g_log = 'Y' then
948             --
949               hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_fut_am_mesg);
950             --
951             end if;
952           --
953           else
954           --
955             fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_fut_am_mesg);
956           --
957           end if;
958         --
959         end if;
960       --
961       else
962       --
963         l_valid_delete := 'N';
964       --
965         if g_sql_run = 'Y' then
966         --
967           if g_log = 'Y' then
968           --
969             hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_am_null_mesg);
970           --
971           end if;
972         --
973         else
974         --
975           fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_am_null_mesg);
976         --
977         end if;
978       --
979       end if;
980     --
981     end if;
982   --
983     if g_detail_debug then
984       hr_utility.set_location(l_proc,30);
985       hr_utility.trace('skip applied month in future : l_valid_delete : '||l_valid_delete);
986     end if;
987   --
988   -- skip mr is null.
989   -- target only assignment, who belongs to new mr range.
990   --
991     if l_valid_delete = 'Y' then
992     --
993       l_mr_eev := pay_jp_balance_pkg.get_entry_value_char(
994                      p_input_value_id => c_mr_iv_id,
995                      p_assignment_id  => p_assignment_id,
996                      p_effective_date => p_session_date);
997     --
998       if l_mr_eev is null then
999       --
1000         l_valid_delete := 'N';
1001       --
1002         if g_sql_run = 'Y' then
1003         --
1004           if g_log = 'Y' then
1005           --
1006             hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_mr_null_mesg);
1007           --
1008           end if;
1009         --
1010         else
1011         --
1012           fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||c_mr_null_mesg);
1013         --
1014         end if;
1015       --
1016       else
1017       --
1018         if g_skip_out_range_upd = 'Y' then
1019         --
1020           if (to_number(l_mr_eev) >= c_new_smr_min_high
1021               and to_number(l_mr_eev) < c_new_smr_max_low) then
1022           --
1023             l_valid_delete := 'N';
1024           --
1025           end if;
1026         --
1027         end if;
1028       --
1029       end if;
1030     --
1031     end if;
1032   --
1033     if g_detail_debug then
1034       hr_utility.set_location(l_proc,40);
1035       hr_utility.trace('skip mr is null or out range : l_valid_delete : '||l_valid_delete);
1036     end if;
1037   --
1038     if l_valid_delete = 'Y' then
1039     --
1040       p_valid_delete := 'Y';
1041     --
1042     end if;
1043   --
1044   end if;
1045 --
1046   if g_detail_debug then
1047     hr_utility.set_location(l_proc,1000);
1048   end if;
1049 --
1050 exception
1051 when others then
1052 --
1053   if g_debug then
1054     hr_utility.set_location(l_proc,-1000);
1055     hr_utility.trace(to_char(p_assignment_id)||':'||get_sqlerrm);
1056   end if;
1057 --
1058 end val_mig_smr_assact;
1059 --
1060 -- -------------------------------------------------------------------------
1061 -- mig_smr_assact
1062 -- -------------------------------------------------------------------------
1063 procedure mig_smr_assact(
1064   p_business_group_id   in number,
1065   p_business_group_name in varchar2,
1066   p_assignment_id       in number,
1067   p_assignment_number   in varchar2,
1068   p_session_date        in date,
1069   p_hi_mr               in varchar2)
1070 is
1071 --
1072   l_proc varchar2(80) := c_package||'mig_smr_assact';
1073 --
1074   l_mr_eev pay_element_entry_values_f.screen_entry_value%type;
1075   l_smr_eev pay_element_entry_values_f.screen_entry_value%type;
1076   l_mr_o_eev pay_element_entry_values_f.screen_entry_value%type;
1077   l_smr_o_eev pay_element_entry_values_f.screen_entry_value%type;
1078   l_exp_mr_eev pay_element_entry_values_f.screen_entry_value%type;
1079   l_exp_smr_eev pay_element_entry_values_f.screen_entry_value%type;
1080   l_exp_mr_o_eev pay_element_entry_values_f.screen_entry_value%type;
1081   l_exp_smr_o_eev pay_element_entry_values_f.screen_entry_value%type;
1082 --
1083   l_ovn number;
1084   l_esd date;
1085   l_eed date;
1086   l_warning boolean;
1087   l_upd varchar2(1) := 'Y';
1088 --
1089   cursor csr_entry
1090   is
1091   select /*+ ORDERED
1092              USE_NL(PEL, PEE, PEEV)
1093              INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
1094              INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51)
1095              INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
1096          pee.element_entry_id,
1097          pee.effective_start_date,
1098          pee.effective_end_date,
1099          pee.object_version_number,
1100          peev.input_value_id,
1101          peev.screen_entry_value
1102   from   pay_element_links_f        pel,
1103          pay_element_entries_f      pee,
1104          pay_element_entry_values_f peev
1105   where  pel.element_type_id = c_com_hi_smr_elm_id
1106   and    pel.business_group_id + 0 = p_business_group_id
1107   and    p_session_date
1108          between pel.effective_start_date and pel.effective_end_date
1109   and    pee.assignment_id = p_assignment_id
1110   and    pee.element_link_id = pel.element_link_id
1111   and    p_session_date
1112          between pee.effective_start_date and pee.effective_end_date
1113   and    pee.entry_type = 'E'
1114   and    peev.element_entry_id = pee.element_entry_id
1115   and    peev.effective_start_date = pee.effective_start_date
1116   and    peev.effective_end_date = pee.effective_end_date
1117   for update of peev.element_entry_value_id nowait;
1118 --
1119   l_csr_entry csr_entry%rowtype;
1120 --
1121 begin
1122 --
1123   if g_detail_debug then
1124     hr_utility.set_location(l_proc,0);
1125     hr_utility.trace(p_business_group_id ||' : '||p_assignment_id||'('||p_assignment_number||')');
1126   end if;
1127 --
1128   open csr_entry;
1129   loop
1130   --
1131     fetch csr_entry into l_csr_entry;
1132     exit when csr_entry%notfound;
1133   --
1134     if l_csr_entry.input_value_id = c_smr_iv_id then
1135     --
1136       l_smr_eev := l_csr_entry.screen_entry_value;
1137     --
1138     elsif l_csr_entry.input_value_id = c_smr_o_iv_id then
1139     --
1140       l_smr_o_eev := l_csr_entry.screen_entry_value;
1141     --
1142     elsif l_csr_entry.input_value_id = c_mr_iv_id then
1143     --
1144       l_mr_eev := l_csr_entry.screen_entry_value;
1145     --
1146     elsif l_csr_entry.input_value_id = c_mr_o_iv_id then
1147     --
1148       l_mr_o_eev := l_csr_entry.screen_entry_value;
1149     --
1150     end if;
1151   --
1152   end loop;
1153   close csr_entry;
1154 --
1155   if g_detail_debug then
1156     hr_utility.set_location(l_proc,10);
1157     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);
1158   end if;
1159 --
1160   if p_hi_mr is not null then
1161   --
1162     l_exp_mr_eev := p_hi_mr;
1163   --
1164   else
1165   --
1166     l_exp_mr_eev := l_mr_eev;
1167   --
1168   end if;
1169 --
1170   l_exp_smr_eev := substrb(ltrim(rtrim(hruserdt.get_table_value(
1171                      p_bus_group_id   => p_business_group_id,
1172                      p_table_name     => c_com_smr_tbl,
1173                      p_col_name       => c_hi_smr_col,
1174                      p_row_value      => l_exp_mr_eev,
1175                      p_effective_date => p_session_date))),0,60);
1176 --
1177   if g_detail_debug then
1178     hr_utility.set_location(l_proc,20);
1179     hr_utility.trace('l_exp_mr_eev : '||l_exp_mr_eev||', l_exp_smr_eev : '||l_exp_smr_eev);
1180   end if;
1181 --
1182   if g_exc_match_exp_smr = 'Y' then
1183   --
1184     if l_smr_eev = l_exp_smr_eev then
1185     --
1186       l_upd := 'N';
1187     --
1188     end if;
1189   --
1190   end if;
1191 --
1192   if g_upd_mode = 'OVERRIDE' then
1193   --
1194     l_exp_smr_o_eev := l_smr_o_eev;
1195     l_exp_mr_o_eev  := l_mr_o_eev;
1196   --
1197   else
1198   --
1199     l_exp_smr_o_eev := l_smr_eev;
1200     l_exp_mr_o_eev  := l_mr_eev;
1201   --
1202   end if;
1203 --
1204   if g_detail_debug then
1205     hr_utility.set_location(l_proc,30);
1206     hr_utility.trace('l_exp_smr_o_eev : '||l_exp_smr_o_eev||', l_exp_mr_o_eev : '||l_exp_mr_o_eev);
1207   end if;
1208 --
1209   l_ovn := l_csr_entry.object_version_number;
1210 --
1211   if l_upd = 'Y' then
1212   --
1213     if g_valid = 'N' then
1214     --
1215       pay_element_entry_api.update_element_entry(
1216         p_validate              => false,
1217         p_effective_date        => p_session_date,
1218         p_business_group_id     => null, -- not used
1219         p_datetrack_update_mode => 'UPDATE',
1220         p_element_entry_id      => l_csr_entry.element_entry_id,
1221         p_object_version_number => l_ovn,
1222         p_input_value_id1       => c_am_iv_id,
1223         p_input_value_id2       => c_smr_iv_id,
1224         p_input_value_id3       => c_smr_o_iv_id,
1225         p_input_value_id4       => c_at_iv_id,
1226         p_input_value_id5       => c_mr_iv_id,
1227         p_input_value_id6       => c_mr_o_iv_id,
1228         p_entry_value1          => to_char(p_session_date,'YYYYMM'),
1229         p_entry_value2          => l_exp_smr_eev,
1230         p_entry_value3          => l_exp_smr_o_eev,
1231         p_entry_value4          => 'O',
1232         p_entry_value5          => l_exp_mr_eev,
1233         p_entry_value6          => l_exp_mr_o_eev,
1234         p_effective_start_date  => l_esd,
1235         p_effective_end_date    => l_eed,
1236         p_update_warning        => l_warning);
1237     --
1238       if g_detail_debug then
1239         hr_utility.set_location(l_proc,40);
1240       end if;
1241     --
1242     end if;
1243   --
1244     if g_sql_run = 'Y' then
1245     --
1246       if g_log = 'Y' then
1247       --
1248         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));
1249       --
1250       end if;
1251     --
1252     else
1253     --
1254       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));
1255     --
1256     end if;
1257   --
1258   else
1259   --
1260     if g_sql_run = 'Y' then
1261     --
1262       if g_log = 'Y' then
1263       --
1264         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));
1265       --
1266       end if;
1267     --
1268     else
1269     --
1270       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));
1271     --
1272     end if;
1273   --
1274     if g_detail_debug then
1275       hr_utility.set_location(l_proc,45);
1276     end if;
1277   --
1278   end if;
1279 --
1280   if g_detail_debug then
1281     hr_utility.set_location(l_proc,1000);
1282   end if;
1283 --
1284 exception
1285 when hr_api.object_locked then
1286 --
1287   if g_debug then
1288     hr_utility.set_location(l_proc,-1000);
1289     hr_utility.trace(to_char(p_assignment_id)||':'||fnd_message.get_string('FND','FND_LOCK_RECORD_ERROR'));
1290   end if;
1291 --
1292   if g_sql_run = 'Y' then
1293   --
1294     if g_log = 'Y' then
1295     --
1296       hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||fnd_message.get_string('FND','FND_LOCK_RECORD_ERROR'));
1297     --
1298     end if;
1299   --
1300   else
1301   --
1302     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'));
1303   --
1304   end if;
1305 --
1306 when others then
1307 --
1308   if g_debug then
1309     hr_utility.set_location(l_proc,-1000);
1310     hr_utility.trace(to_char(p_assignment_id)||':'||get_sqlerrm);
1311   end if;
1312 --
1313   if g_sql_run = 'Y' then
1314   --
1315     if g_log = 'Y' then
1316     --
1317       hr_utility.trace(rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||get_sqlerrm);
1318     --
1319     end if;
1320   --
1321   else
1322   --
1323     fnd_file.put_line(fnd_file.log, rpad(p_business_group_name,30)||' '||rpad(p_assignment_number,30)||' '||get_sqlerrm);
1324   --
1325   end if;
1326 --
1327 end mig_smr_assact;
1328 --
1329 -- -------------------------------------------------------------------------
1330 -- run_mig_smr
1331 -- -------------------------------------------------------------------------
1332 -- this is for manual run by script, recommend to use generic upgrade instead of this.
1333 procedure run_mig_smr
1334 is
1335 --
1336   l_proc varchar2(80) := c_package||'run_mig_smr';
1337 --
1338   l_range_ass_tbl_cnt number := 0;
1339   l_qualify_ass_tbl_cnt number := 0;
1340   l_qualify_valid_update varchar2(1) := 'N';
1341 --
1342   l_mig_cnt number := 0;
1343   l_mig_commit_cnt number := 1;
1344 --
1345   cursor csr_range_ass
1346   is
1347   select /*+ ORDERED
1348              INDEX(PA PER_ASSIGNMENTS_F_FK1) */
1349          pbg.business_group_id,
1350          pbg.name bg_name,
1351          pa.assignment_id,
1352          pa.assignment_number
1353   from   per_business_groups_perf pbg,
1354          per_all_assignments_f pa
1355   where  pbg.legislation_code = g_legislation_code
1356 --and pbg.business_group_id = 3101
1357 --and pa.assignment_number
1358 --    between 1000 and 1200
1359   and    pa.business_group_id = pbg.business_group_id
1360   and    pa.effective_start_date = (
1361            select /*+ INDEX(PA2 PER_ASSIGNMENTS_F_PK) */
1362                   max(pa2.effective_start_date)
1363            from   per_all_assignments_f pa2
1364            where  pa2.assignment_id = pa.assignment_id);
1365 --
1366   l_csr_range_ass csr_range_ass%rowtype;
1367 --
1368 begin
1369 --
1370 -- --
1371 -- init
1372 -- --
1373 --
1374   g_sql_run := 'Y';
1375 --
1376   if g_sql_run = 'Y'
1377   and g_log = 'Y' then
1378     g_debug := false;
1379   end if;
1380 --
1381   if g_debug then
1382     hr_utility.set_location(l_proc,0);
1383   end if;
1384 --
1385   if g_skip_qualify is null then
1386     g_skip_qualify := 'N';
1387   end if;
1388 --
1389   if g_upd_mode is null then
1390     g_upd_mode := 'UPDATE';
1391   end if;
1392 --
1393   if g_mig_date is null then
1394     g_mig_date:= c_mig_smr_sd;
1395   end if;
1396 --
1397   -- can set g_range_ass_hi_smr_tbl by out of this procedure
1398   -- in case g_legislation_code is set.
1399   if g_legislation_code is null
1400   or g_legislation_code <> c_legislation_code then
1401   --
1402     g_range_ass_hi_smr_tbl.delete;
1403     g_legislation_code := c_legislation_code;
1404   --
1405   end if;
1406 --
1407   g_dml_num := null;
1408   g_qualify_hi_smr_ass_tbl.delete;
1409   l_range_ass_tbl_cnt := g_range_ass_hi_smr_tbl.count;
1410 --
1411   set_hi_smr_id;
1412   set_hi_smr_mesg;
1413 --
1414   -- for api use
1415   insert_session(g_mig_date);
1416 --
1417   if g_debug then
1418     hr_utility.set_location(l_proc,10);
1419   end if;
1420 --
1421 -- --
1422 -- range
1423 -- --
1424 --
1425   if l_range_ass_tbl_cnt = 0 then
1426   --
1427     open csr_range_ass;
1428     loop
1429     --
1430       fetch csr_range_ass into l_csr_range_ass;
1431       exit when csr_range_ass%notfound;
1432     --
1433       g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).bg_id    := l_csr_range_ass.business_group_id;
1434       g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).bg_name  := l_csr_range_ass.bg_name;
1435       g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).ass_id   := l_csr_range_ass.assignment_id;
1436       g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).ass_num  := l_csr_range_ass.assignment_number;
1437       g_range_ass_hi_smr_tbl(l_range_ass_tbl_cnt).del_done := 'N';
1438     --
1439       l_range_ass_tbl_cnt := l_range_ass_tbl_cnt + 1;
1440     --
1441     end loop;
1442     close csr_range_ass;
1443   --
1444   end if;
1445 --
1446   if g_debug then
1447   --
1448     hr_utility.set_location(l_proc,15);
1449     hr_utility.trace('g_range_ass_hi_smr_tbl.count : '||to_char(g_range_ass_hi_smr_tbl.count));
1450   --
1451     if g_range_ass_hi_smr_tbl.count > 0 then
1452     --
1453       for i in 0..g_range_ass_hi_smr_tbl.count - 1 loop
1454       --
1455         if g_detail_debug then
1456         --
1457           hr_utility.trace(to_char(g_range_ass_hi_smr_tbl(i).bg_id)
1458             ||':'||g_range_ass_hi_smr_tbl(i).ass_num
1459             ||'('||to_char(g_range_ass_hi_smr_tbl(i).ass_id)
1460             ||').'||g_range_ass_hi_smr_tbl(i).del_done);
1461         --
1462         end if;
1463       --
1464       end loop;
1465     --
1466     end if;
1467   --
1468   end if;
1469 --
1470   if g_debug then
1471     hr_utility.set_location(l_proc,20);
1472   end if;
1473 --
1474 -- --
1475 -- qualify
1476 -- --
1477 --
1478   if g_log = 'Y' then
1479   --
1480     hr_utility.trace('Business Group Name            Assignment Number              Message');
1481     hr_utility.trace('------------------------------ ------------------------------ --------------------------------------------------------------------------------');
1482   --
1483   end if;
1484 --
1485   if g_skip_qualify = 'N' then
1486   --
1487     if g_range_ass_hi_smr_tbl.count > 0 then
1488     --
1489       for j in 0..g_range_ass_hi_smr_tbl.count - 1 loop
1490       --
1491         val_mig_smr_assact(
1492           p_business_group_id   => g_range_ass_hi_smr_tbl(j).bg_id,
1493           p_business_group_name => g_range_ass_hi_smr_tbl(j).bg_name,
1494           p_assignment_id       => g_range_ass_hi_smr_tbl(j).ass_id,
1495           p_assignment_number   => g_range_ass_hi_smr_tbl(j).ass_num,
1496           p_session_date        => g_mig_date,
1497           p_valid_delete        => g_range_ass_hi_smr_tbl(j).del_done);
1498       --
1499         if l_qualify_valid_update = 'N'
1500         and g_range_ass_hi_smr_tbl(j).del_done = 'Y' then
1501           l_qualify_valid_update := 'Y';
1502         end if;
1503       --
1504       end loop;
1505     --
1506     end if;
1507   --
1508   else
1509   --
1510     l_qualify_valid_update := 'Y';
1511   --
1512   end if;
1513 --
1514   if l_qualify_valid_update = 'Y' then
1515   --
1516     if g_range_ass_hi_smr_tbl.count > 0 then
1517     --
1518       for k in 0..g_range_ass_hi_smr_tbl.count - 1 loop
1519       --
1520         if g_range_ass_hi_smr_tbl(k).del_done = 'Y'
1521         or g_skip_qualify = 'Y' then
1522         --
1523           g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).bg_id    := g_range_ass_hi_smr_tbl(k).bg_id;
1524           g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).bg_name  := g_range_ass_hi_smr_tbl(k).bg_name;
1525           g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).ass_id   := g_range_ass_hi_smr_tbl(k).ass_id;
1526           g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).ass_num  := g_range_ass_hi_smr_tbl(k).ass_num;
1527           g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).del_done := 'N';
1528           g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).hi_mr    := g_range_ass_hi_smr_tbl(k).hi_mr;
1529         --
1530           l_qualify_ass_tbl_cnt := l_qualify_ass_tbl_cnt + 1;
1531         --
1532         end if;
1533       --
1534       end loop;
1535     --
1536     end if;
1537   --
1538     if g_debug then
1539     --
1540       if g_qualify_hi_smr_ass_tbl.count > 0 then
1541       --
1542         for l in 0..g_qualify_hi_smr_ass_tbl.count - 1 loop
1543         --
1544           if g_detail_debug then
1545           --
1546             hr_utility.trace(to_char(g_qualify_hi_smr_ass_tbl(l).bg_id)
1547               ||':'||g_qualify_hi_smr_ass_tbl(l).ass_num
1548               ||'('||to_char(g_qualify_hi_smr_ass_tbl(l).ass_id)
1549               ||').'||g_qualify_hi_smr_ass_tbl(l).del_done);
1550           --
1551           end if;
1552         --
1553         end loop;
1554       --
1555       end if;
1556     --
1557     end if;
1558   --
1559   end if;
1560 --
1561   if g_debug then
1562     hr_utility.set_location(l_proc,30);
1563   end if;
1564 --
1565 -- --
1566 -- upgrade
1567 -- --
1568 --
1569   if g_log = 'Y' then
1570   --
1571     hr_utility.trace('----------------------------------------------------------------------------------------------------------------------------------------------');
1572     hr_utility.trace('Business Group Name            Assignment Number              E         MR        SMR    Exp SMR');
1573     hr_utility.trace('------------------------------ ------------------------------ - ---------- ---------- ----------');
1574   --
1575   end if;
1576 --
1577   if l_qualify_valid_update = 'Y' then
1578   --
1579     if g_qualify_hi_smr_ass_tbl.count > 0 then
1580     --
1581       for m in 0..g_qualify_hi_smr_ass_tbl.count - 1 loop
1582       --
1583         mig_smr_assact(
1584           p_business_group_id   => g_qualify_hi_smr_ass_tbl(m).bg_id,
1585           p_business_group_name => g_qualify_hi_smr_ass_tbl(m).bg_name,
1586           p_assignment_id       => g_qualify_hi_smr_ass_tbl(m).ass_id,
1587           p_assignment_number   => g_qualify_hi_smr_ass_tbl(m).ass_num,
1588           p_session_date        => g_mig_date,
1589           p_hi_mr               => g_qualify_hi_smr_ass_tbl(m).hi_mr);
1590       --
1591         l_mig_cnt := l_mig_cnt + 1;
1592       --
1593         if g_sql_run = 'Y' then
1594         --
1595           g_dml_num := g_dml_num + (l_mig_cnt - c_commit_num * (l_mig_commit_cnt - 1));
1596         --
1597           if g_dml_num > c_commit_num then
1598             commit;
1599             g_dml_num := 0;
1600             l_mig_commit_cnt := l_mig_commit_cnt + 1;
1601           end if;
1602         --
1603         end if;
1604       --
1605       end loop;
1606     --
1607     end if;
1608   --
1609   end if;
1610 --
1611   if g_debug then
1612     hr_utility.set_location(l_proc,40);
1613   end if;
1614 --
1615 -- --
1616 -- deinitialize
1617 -- --
1618 --
1619   if g_log = 'Y' then
1620   --
1621     hr_utility.trace('----------------------------------------------------------------------------------------------------------------------------------------------');
1622   --
1623   end if;
1624 --
1625   delete_session;
1626 --
1627   if l_qualify_valid_update = 'Y' then
1628   --
1629     commit;
1630   --
1631   end if;
1632 --
1633   if g_debug then
1634     hr_utility.set_location(l_proc,1000);
1635   end if;
1636 --
1637 end run_mig_smr;
1638 --
1639 END HR_JP_DATA_MIGRATION_PKG;
1640 --