[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_GENERIC_UPGRADE_PKG
Source
1 package body pay_jp_generic_upgrade_pkg as
2 /* $Header: pyjpgupg.pkb 120.18.12020000.6 2013/02/18 18:40:46 dduvvuri ship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pay_jp_generic_upgrade_pkg.';
7 type t_numbers is table of number index by binary_integer;
8 --
9 -- Global Variables
10 --
11 g_num_errors number := 0;
12 g_element_type_ids t_numbers;
13 --
14 g_debug boolean := hr_utility.debug_enabled;
15 --
16 -- |-------------------------------------------------------------------|
17 -- |----------------------< get_upgrade_status >-----------------------|
18 -- |-------------------------------------------------------------------|
19 function get_upgrade_status(
20 p_upgrade_short_name in varchar2,
21 p_legislation_code in varchar2) return varchar2
22 is
23 l_upgrade_status pay_upgrade_status.status%type;
24 --
25 cursor csr_upgrade_status is
26 select status
27 from pay_upgrade_status s,
28 pay_upgrade_definitions d
29 where d.short_name = p_upgrade_short_name
30 and s.upgrade_definition_id = d.upgrade_definition_id
31 -- and s.business_group_id is null
32 and s.legislation_code = p_legislation_code;
33 begin
34 open csr_upgrade_status;
35 fetch csr_upgrade_status into l_upgrade_status;
36 if csr_upgrade_status%notfound then
37 l_upgrade_status := null;
38 end if;
39 close csr_upgrade_status;
40 --
41 return l_upgrade_status;
42 end get_upgrade_status;
43 -- |-------------------------------------------------------------------|
44 -- |---------------------< get_business_group_id >---------------------|
45 -- |-------------------------------------------------------------------|
46 function get_business_group_id(p_legislation_code in varchar2) return varchar2
47 is
48 l_business_group_id number;
49 --
50 -- Do not use per_business_groups_perf which does not return
51 -- "Disabled" business groups.
52 --
53 cursor csr_bg is
54 select organization_id
55 from hr_organization_information
56 where org_information_context = 'Business Group Information'
57 and org_information9 = p_legislation_code
58 and rownum = 1;
59 begin
60 open csr_bg;
61 fetch csr_bg into l_business_group_id;
62 if csr_bg%notfound then
63 l_business_group_id := null;
64 end if;
65 close csr_bg;
66 --
67 return l_business_group_id;
68 end get_business_group_id;
69 /*
70 -- |-------------------------------------------------------------------|
71 -- |---------------------< set_upgrade_completed >---------------------|
72 -- |-------------------------------------------------------------------|
73 procedure set_upgrade_completed(
74 p_upgrade_short_name in varchar2,
75 p_legislation_code in varchar2)
76 is
77 begin
78 --
79 -- Do not set status to "Completed" directly which will fail.
80 -- At first "Processing" then "Completed".
81 --
82 hr_update_utility.setUpdateProcessing(
83 p_update_name => p_upgrade_short_name,
84 p_business_group_id => null,
85 p_legislation_code => p_legislation_code);
86 hr_update_utility.setUpdateComplete(
87 p_update_name => p_upgrade_short_name,
88 p_business_group_id => null,
89 p_legislation_code => p_legislation_code);
90 end set_upgrade_completed;
91 */
92 -- |-------------------------------------------------------------------|
93 -- |--------------------< validate_pay_jp_pre_tax >--------------------|
94 -- |-------------------------------------------------------------------|
95 procedure validate_pay_jp_pre_tax(p_valid_upgrade out nocopy varchar2)
96 is
97 c_proc constant varchar2(61) := c_package || 'validate_pay_jp_pre_tax';
98 --
99 cursor csr_exists is
100 /* Obsoleted for performance reasons
101 select 'TRUE'
102 from dual
103 where exists(
104 select null
105 from pay_jp_pre_tax ppt,
106 pay_assignment_actions paa
107 where paa.action_status = 'C'
108 and ppt.assignment_action_id = paa.assignment_action_id
109 and ppt.action_status = 'C'); */
110 /*
111 --Bug 4256063---
112 select 'TRUE'
113 from pay_jp_pre_tax ppt,
114 pay_assignment_actions paa
115 where paa.action_status = 'C'
116 and ppt.assignment_action_id = paa.assignment_action_id
117 and ppt.action_status = 'C'
118 and rownum =1 ;
119 */
120 select 'TRUE'
121 from pay_action_information pai,
122 pay_assignment_actions paa
123 where paa.action_status = 'C'
124 and pai.action_information_category = 'JP_PRE_TAX_1'
125 and pai.action_context_type = 'AAP'
126 and pai.action_information1 = paa.assignment_action_id
127 and rownum =1 ;
128
129 begin
130 hr_utility.set_location('Entering: ' || c_proc, 10);
131 --
132 open csr_exists;
133 fetch csr_exists into p_valid_upgrade;
134 if csr_exists%notfound then
135 p_valid_upgrade := 'FALSE';
136 end if;
137 close csr_exists;
138 --
139 hr_utility.trace(p_valid_upgrade);
140 hr_utility.set_location('Leaving: ' || c_proc, 100);
141 end validate_pay_jp_pre_tax;
142 -- |-------------------------------------------------------------------|
143 -- |--------------------< qualify_pay_jp_pre_tax >---------------------|
144 -- |-------------------------------------------------------------------|
145 procedure qualify_pay_jp_pre_tax(
146 p_assignment_id in number,
147 p_qualifier out nocopy varchar2)
148 is
149 c_proc constant varchar2(61) := c_package || 'qualify_pay_jp_pre_tax';
150 --
151 cursor csr_ppt is
152 /*
153 select 'Y'
154 from dual
155 where exists(
156 select null
157 from pay_jp_pre_tax ppt,
158 pay_assignment_actions paa
159 where paa.assignment_id = p_assignment_id
160 and paa.action_status = 'C'
161 and ppt.assignment_action_id = paa.assignment_action_id
162 and ppt.action_status = 'C');
163 */
164 select 'Y'
165 from pay_action_information pai,
166 pay_assignment_actions paa
167 where paa.assignment_id = p_assignment_id
168 and paa.action_status = 'C'
169 and pai.action_information_category = 'JP_PRE_TAX_1'
170 and pai.action_context_type = 'AAP'
171 and pai.action_information1 = paa.assignment_action_id
172 and rownum = 1;
173
174 begin
175 hr_utility.set_location('Entering: ' || c_proc, 10);
176 --
177 open csr_ppt;
178 fetch csr_ppt into p_qualifier;
179 if csr_ppt%notfound then
180 p_qualifier := 'N';
181 end if;
182 close csr_ppt;
183 --
184 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
185 hr_utility.set_location('Leaving: ' || c_proc, 100);
186 end qualify_pay_jp_pre_tax;
187 -- |-------------------------------------------------------------------|
188 -- |----------------< upgrade_disaster_tax_reduction >-----------------|
189 -- |-------------------------------------------------------------------|
190 procedure upgrade_disaster_tax_reduction(p_assignment_id in number)
191 is
192 c_proc constant varchar2(61) := c_package || 'upgrade_disaster_tax_reduction';
193 --
194 l_defined_balance_id number;
195 l_balance number;
196 --
197 cursor csr_assact is
198 select pai.action_information29 disaster_tax_reduction,
199 pai.action_information_id,
200 paa.assignment_action_id
201 from pay_action_information pai,
202 pay_assignment_actions paa
203 where paa.assignment_id = p_assignment_id
204 and paa.action_status = 'C'
205 and pai.action_information_category = 'JP_PRE_TAX_1'
206 and pai.action_context_type = 'AAP'
207 and pai.action_information1 = paa.assignment_action_id
208 for update of pai.action_information_id nowait;
209 /*
210 select ppt.rowid,
211 paa.assignment_action_id,
212 ppt.disaster_tax_reduction
213 from pay_jp_pre_tax ppt,
214 pay_assignment_actions paa
215 where paa.assignment_id = p_assignment_id
216 and paa.action_status = 'C'
217 and ppt.assignment_action_id = paa.assignment_action_id
218 and ppt.action_status = 'C'
219 for update of ppt.pre_tax_id nowait;
220 */
221 begin
222 hr_utility.set_location('Entering: ' || c_proc, 10);
223 hr_utility.trace('assignment_id: ' || p_assignment_id);
224 --
225 -- When the following sql fails, it means JP post install has not
226 -- completed successfully because qualify procedure guarantees that
227 -- JP post install had been completed before.
228 -- To fix no_data_found error here, need to complete JP post install
229 -- before applying this.
230 --
231 select creator_id
232 into l_defined_balance_id
233 from ff_user_entities
234 where user_entity_name = 'B_YEA_GRACE_ITX_ASG_RUN'
235 and legislation_code = 'JP'
236 and creator_type = 'B';
237 --
238 for l_assact in csr_assact loop
239 l_balance := pay_balance_pkg.get_value(l_defined_balance_id, l_assact.assignment_action_id);
240 --
241 if l_balance <> l_assact.disaster_tax_reduction then
242 update pay_action_information
243 set action_information29 = l_balance --Disaster Tax Reduction
244 where action_information_id = l_assact.action_information_id;
245 /*
246 update pay_jp_pre_tax
247 set disaster_tax_reduction = l_balance
248 where rowid = l_assact.rowid;
249 */
250 --
251 hr_utility.trace(l_assact.disaster_tax_reduction || ' --> ' || l_balance);
252 end if;
253 end loop;
254 --
255 hr_utility.set_location('Leaving: ' || c_proc, 100);
256 end upgrade_disaster_tax_reduction;
257 -- |-------------------------------------------------------------------|
258 -- |-----------------------< validate_prev_job >-----------------------|
259 -- |-------------------------------------------------------------------|
260 procedure validate_prev_job(p_valid_upgrade out nocopy varchar2)
261 is
262 c_proc constant varchar2(61) := c_package || 'validate_prev_job';
263 --
264 cursor csr_exists is
265 select 'TRUE'
266 from dual
267 where exists(
268 select null
269 from pay_element_types_f pet,
270 pay_element_links_f pel,
271 pay_element_entries_f pee
272 where pet.element_name = 'YEA_PREV_EMP_INFO'
273 and pet.legislation_code = 'JP'
274 and pel.element_type_id = pet.element_type_id
275 and pee.element_link_id = pel.element_link_id);
276 begin
277 hr_utility.set_location('Entering: ' || c_proc, 10);
278 --
279 open csr_exists;
280 fetch csr_exists into p_valid_upgrade;
281 if csr_exists%notfound then
282 p_valid_upgrade := 'FALSE';
283 end if;
284 close csr_exists;
285 --
286 hr_utility.trace(p_valid_upgrade);
287 hr_utility.set_location('Leaving: ' || c_proc, 100);
288 end validate_prev_job;
289 -- |-------------------------------------------------------------------|
290 -- |-----------------------< qualify_prev_job >------------------------|
291 -- |-------------------------------------------------------------------|
292 procedure qualify_prev_job(
293 p_assignment_id in number,
294 p_qualifier out nocopy varchar2)
295 is
296 c_proc constant varchar2(61) := c_package || 'qualify_prev_job';
297 --
298 cursor csr_ee is
299 select 'Y'
300 from dual
301 where exists(
302 select null
303 from pay_element_types_f pet,
304 pay_element_links_f pel,
305 pay_element_entries_f pee
306 where pet.element_name = 'YEA_PREV_EMP_INFO'
307 and pet.legislation_code = 'JP'
308 and pel.element_type_id = pet.element_type_id
309 and pee.assignment_id = p_assignment_id
310 and pee.element_link_id = pel.element_link_id);
311 begin
312 hr_utility.set_location('Entering: ' || c_proc, 10);
313 --
314 -- No need to check whether the assignment is "Payroll" assignment or not
315 -- not to raise error when creating previous employers by API because
316 -- non-recurring entries cannot be created for non-Payroll assignments.
317 --
318 open csr_ee;
319 fetch csr_ee into p_qualifier;
320 if csr_ee%notfound then
321 p_qualifier := 'N';
322 end if;
323 close csr_ee;
324 --
325 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
326 hr_utility.set_location('Leaving: ' || c_proc, 100);
327 end qualify_prev_job;
328 -- |-------------------------------------------------------------------|
329 -- |-----------------------< upgrade_prev_job >------------------------|
330 -- |-------------------------------------------------------------------|
331 procedure upgrade_prev_job(p_assignment_id in number)
332 is
333 c_proc constant varchar2(61) := c_package || 'upgrade_prev_job';
334 --
335 l_business_group_id number;
336 l_person_id number;
337 l_effective_date date;
338 --
339 l_party_id number;
340 -- l_bg_name per_business_groups_perf.name%type;
341 l_full_name per_all_people_f.full_name%type;
342 --
343 cursor csr_ee is
344 select employment_income,
345 si_prems,
346 mutual_aid_prem,
347 withholding_tax,
348 termination_date,
349 foreign_flag,
350 employer_address_kana,
351 employer_address,
352 employer_name_kana,
353 employer_name
354 from (
355 select /*+ ORDERED USE_NL(PEL PEE PEEV PIV) */
356 nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 1, peev.screen_entry_value))), 0) employment_income,
357 nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 2, peev.screen_entry_value))), 0) si_prems,
358 nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 3, peev.screen_entry_value))), 0) mutual_aid_prem,
359 nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 4, peev.screen_entry_value))), 0) withholding_tax,
360 fnd_date.canonical_to_date(min(decode(piv.display_sequence, 5, peev.screen_entry_value))) termination_date,
361 nvl(min(decode(piv.display_sequence, 6, peev.screen_entry_value)), 'N') foreign_flag,
362 hr_jp_standard_pkg.to_hankaku(min(decode(piv.display_sequence, 7, peev.screen_entry_value))) employer_address_kana,
363 min(decode(piv.display_sequence, 8, peev.screen_entry_value)) employer_address,
364 hr_jp_standard_pkg.to_hankaku(min(decode(piv.display_sequence, 9, peev.screen_entry_value))) employer_name_kana,
365 min(decode(piv.display_sequence, 10, peev.screen_entry_value)) employer_name
366 from pay_element_types_f pet,
367 pay_element_links_f pel,
368 pay_element_entries_f pee,
369 pay_element_entry_values_f peev,
370 pay_input_values_f piv
371 where pet.element_name = 'YEA_PREV_EMP_INFO'
372 and pet.legislation_code = 'JP'
373 and pel.element_type_id = pet.element_type_id
374 and pel.business_group_id + 0 = l_business_group_id
375 and pel.effective_start_date
376 between pet.effective_start_date and pet.effective_end_date
377 and pee.assignment_id = p_assignment_id
378 and pee.element_link_id = pel.element_link_id
379 and pee.effective_start_date
380 between pel.effective_start_date and pel.effective_end_date
381 and peev.element_entry_id = pee.element_entry_id
382 and peev.effective_start_date = pee.effective_start_date
383 and peev.effective_end_date = pee.effective_end_date
384 and piv.input_value_id = peev.input_value_id
385 and peev.effective_start_date
386 between piv.effective_start_date and pee.effective_end_date
387 group by
388 pee.element_entry_id,
389 pee.effective_start_date,
390 pee.effective_end_date
391 )
392 group by
393 employment_income,
394 si_prems,
395 mutual_aid_prem,
396 withholding_tax,
397 termination_date,
398 foreign_flag,
399 employer_address_kana,
400 employer_address,
401 employer_name_kana,
402 employer_name;
403 cursor csr_prev_employer(
404 p_person_id number,
405 p_employer_name varchar2,
406 p_foreign_flag varchar2,
407 p_termination_date date) is
408 select previous_employer_id,
409 object_version_number,
410 employer_country,
411 employer_address,
412 end_date
413 from per_previous_employers
414 where person_id = p_person_id
415 and (
416 replace(upper(hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(employer_name))), ' ') =
417 replace(upper(hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(p_employer_name))), ' ')
418 )
419 and (
420 end_date = p_termination_date
421 or (end_date is null and p_termination_date is not null)
422 or (end_date is not null and p_termination_date is null)
423 )
424 and (
425 (p_foreign_flag = 'N' and nvl(employer_country, 'JP') = 'JP')
426 or
427 (p_foreign_flag = 'Y' and employer_country <> 'JP')
428 )
429 and rownum <= 1
430 for update of previous_employer_id;
431 l_prev_employer csr_prev_employer%rowtype;
432 begin
433 begin
434 hr_utility.set_location('Entering: ' || c_proc, 10);
435 hr_utility.trace('assignment_id: ' || p_assignment_id);
436 --
437 select business_group_id,
438 person_id,
439 effective_start_date
440 into l_business_group_id,
441 l_person_id,
442 l_effective_date
443 from per_all_assignments_f asg
444 where assignment_id = p_assignment_id
445 and rownum <= 1;
446 --
447 select per.party_id,
448 -- bg.name,
449 per.full_name
450 into l_party_id,
451 -- l_bg_name,
452 l_full_name
453 from per_all_people_f per,
454 per_business_groups_perf bg
455 where per.person_id = l_person_id
456 and l_effective_date
457 between per.effective_start_date and per.effective_end_date
458 and bg.business_group_id = per.business_group_id;
459 --
460 hr_utility.set_location(c_proc, 20);
461 --
462 -- When the following sql fails, it means JP post install has not
463 -- completed successfully because qualify procedure guarantees that
464 -- JP post install had been completed before.
465 -- To fix no_data_found error here, need to complete JP post install
466 -- before applying this.
467 --
468 for l_prev_job in csr_ee loop
469 hr_utility.set_location(c_proc, 30);
470 --
471 hr_utility.trace('employment_income : ' || l_prev_job.employment_income);
472 hr_utility.trace('si_prems : ' || l_prev_job.si_prems);
473 hr_utility.trace('mutual_aid_prem : ' || l_prev_job.mutual_aid_prem);
474 hr_utility.trace('withholding_tax : ' || l_prev_job.withholding_tax);
475 hr_utility.trace('termination_date : ' || l_prev_job.termination_date);
476 hr_utility.trace('foreign_flag : ' || l_prev_job.foreign_flag);
477 hr_utility.trace('employer_address_kana: ' || l_prev_job.employer_address_kana);
478 hr_utility.trace('employer_address : ' || l_prev_job.employer_address);
479 hr_utility.trace('employer_name_kana : ' || l_prev_job.employer_name_kana);
480 hr_utility.trace('employer_name : ' || l_prev_job.employer_name);
481 --
482 open csr_prev_employer( l_person_id,
483 l_prev_job.employer_name,
484 l_prev_job.foreign_flag,
485 l_prev_job.termination_date);
486 fetch csr_prev_employer into l_prev_employer;
487 --
488 -- Update is only for the following cases (note address is not checked).
489 -- 1) Employer Name for both EE and PEM are not null and not different.
490 -- 2) Termination Date is not different.
491 -- 3) Country is not different.
492 --
493 if csr_prev_employer%found then
494 hr_utility.trace('Updating PER_PREVIOUS_EMPLOYERS...');
495 --
496 if l_prev_employer.end_date is null then
497 l_prev_employer.end_date := l_prev_job.termination_date;
498 end if;
499 --
500 if l_prev_employer.employer_address is null then
501 l_prev_employer.employer_address := l_prev_job.employer_address;
502 end if;
503 --
504 if l_prev_employer.employer_country is null then
505 if l_prev_job.foreign_flag = 'Y' then
506 l_prev_employer.employer_country := 'US';
507 else
508 l_prev_employer.employer_country := 'JP';
509 end if;
510 end if;
511 --
512 hr_previous_employment_api.update_previous_employer(
513 P_EFFECTIVE_DATE => l_effective_date,
514 P_PREVIOUS_EMPLOYER_ID => l_prev_employer.previous_employer_id,
515 P_END_DATE => l_prev_employer.end_date,
516 P_EMPLOYER_ADDRESS => l_prev_employer.employer_address,
517 P_EMPLOYER_COUNTRY => l_prev_employer.employer_country,
518 P_PEM_INFORMATION_CATEGORY => 'JP',
519 P_PEM_INFORMATION1 => l_prev_job.employer_name_kana,
520 P_PEM_INFORMATION2 => l_prev_job.employer_address_kana,
521 P_PEM_INFORMATION3 => fnd_number.number_to_canonical(l_prev_job.employment_income),
522 P_PEM_INFORMATION4 => fnd_number.number_to_canonical(l_prev_job.si_prems),
523 P_PEM_INFORMATION5 => fnd_number.number_to_canonical(l_prev_job.mutual_aid_prem),
524 P_PEM_INFORMATION6 => fnd_number.number_to_canonical(l_prev_job.withholding_tax),
525 P_OBJECT_VERSION_NUMBER => l_prev_employer.object_version_number);
526 else
527 hr_utility.trace('Inserting PER_PREVIOUS_EMPLOYERS...');
528 --
529 l_prev_employer := null;
530 --
531 if l_prev_job.foreign_flag = 'Y' then
532 l_prev_employer.employer_country := 'US';
533 else
534 l_prev_employer.employer_country := 'JP';
535 end if;
536 --
537 hr_previous_employment_api.create_previous_employer(
538 P_EFFECTIVE_DATE => l_effective_date,
539 P_BUSINESS_GROUP_ID => l_business_group_id,
540 P_PERSON_ID => l_person_id,
541 P_PARTY_ID => l_party_id,
542 P_END_DATE => l_prev_job.termination_date,
543 P_EMPLOYER_NAME => l_prev_job.employer_name,
544 P_EMPLOYER_COUNTRY => l_prev_employer.employer_country,
545 P_EMPLOYER_ADDRESS => l_prev_job.employer_address,
546 P_PEM_INFORMATION_CATEGORY => 'JP',
547 P_PEM_INFORMATION1 => l_prev_job.employer_name_kana,
548 P_PEM_INFORMATION2 => l_prev_job.employer_address_kana,
549 P_PEM_INFORMATION3 => fnd_number.number_to_canonical(l_prev_job.employment_income),
550 P_PEM_INFORMATION4 => fnd_number.number_to_canonical(l_prev_job.si_prems),
551 P_PEM_INFORMATION5 => fnd_number.number_to_canonical(l_prev_job.mutual_aid_prem),
552 P_PEM_INFORMATION6 => fnd_number.number_to_canonical(l_prev_job.withholding_tax),
553 P_PREVIOUS_EMPLOYER_ID => l_prev_employer.previous_employer_id,
554 P_OBJECT_VERSION_NUMBER => l_prev_employer.object_version_number);
555 end if;
556 close csr_prev_employer;
557 end loop;
558 --
559 hr_utility.set_location('Leaving: ' || c_proc, 100);
560 exception
561 when app_exception.application_exception then
562 app_exception.raise_exception;
563 end;
564 exception
565 when others then
566 if g_num_errors = 0 then
567 fnd_file.put_line(fnd_file.log, fnd_message.get_string('PAY', 'PAY_JP_RETRY_JP_PREV_JOB_UPG'));
568 fnd_file.put_line(fnd_file.log,
569 rpad(fnd_message.get_string('PER', 'OH_FULL_NAME'), 30) || ' ' ||
570 fnd_message.get_string('FND', 'FND_MESSAGE_TYPE_ERROR'));
571 fnd_file.put_line(fnd_file.log, rpad('-', 30, '-') || ' ' || rpad('-', 100, '-'));
572 end if;
573 g_num_errors := g_num_errors + 1;
574 --
575 fnd_file.put_line(fnd_file.log, rpad(l_full_name, 30) || ' ' || sqlerrm);
576 raise;
577 end upgrade_prev_job;
578 -- |-------------------------------------------------------------------|
579 -- |-------------------< entries_or_results_exist >--------------------|
580 -- |-------------------------------------------------------------------|
581 function entries_or_results_exist(p_legislation_code in varchar2) return boolean
582 is
583 c_proc constant varchar2(61) := c_package || 'entries_or_results_exist';
584 l_exists varchar2(1);
585 --
586 -- Check whether either any element entries or run results exist.
587 --
588 cursor csr_exists is
589 select 'Y'
590 from dual
591 where exists(
592 select /*+ ORDERED
593 NO_MERGE(BG)
594 USE_NL(PEL PEE)
595 INDEX(PEL PAY_ELEMENT_LINKS_F_FK11)
596 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N4) */
597 null
598 from per_business_groups_perf bg,
599 pay_element_links_f pel,
600 pay_element_entries_f pee
601 where bg.legislation_code = p_legislation_code
602 and pel.business_group_id = bg.business_group_id
603 and pee.element_link_id = pel.element_link_id)
604 or exists(
605 select /*+ ORDERED
606 NO_MERGE(BG)
607 USE_NL(PPA PAA PRR)
608 INDEX(PPA PAY_PAYROLL_ACTIONS_FK1)
609 INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50)
610 INDEX(PRR PAY_RUN_RESULTS_N50) */
611 null
612 from per_business_groups_perf bg,
613 pay_payroll_actions ppa,
614 pay_assignment_actions paa,
615 pay_run_results prr
616 where bg.legislation_code = p_legislation_code
617 and ppa.business_group_id = bg.business_group_id
618 and paa.payroll_action_id = ppa.payroll_action_id
619 and prr.assignment_action_id = paa.assignment_action_id);
620 begin
621 hr_utility.set_location('Entering: ' || c_proc, 10);
622 --
623 open csr_exists;
624 fetch csr_exists into l_exists;
625 if csr_exists%notfound then
626 l_exists := 'N';
627 end if;
628 close csr_exists;
629 --
630 hr_utility.trace('Exists? : ' || l_exists);
631 hr_utility.set_location('Leaving: ' || c_proc, 100);
632 --
633 return (l_exists = 'Y');
634 end entries_or_results_exist;
635 -- |-------------------------------------------------------------------|
636 -- |-------------------< entries_or_results_exist >--------------------|
637 -- |-------------------------------------------------------------------|
638 function entries_or_results_exist(p_element_type_id in number) return boolean
639 is
640 c_proc constant varchar2(61) := c_package || 'entries_or_results_exist';
641 l_exists varchar2(1);
642 --
643 -- Check whether either any element entries or run results exist.
644 --
645 cursor csr_exists is
646 select 'Y'
647 from dual
648 where exists(
649 select /*+ ORDERED
650 USE_NL(PEE)
651 INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
652 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N4) */
653 null
654 from pay_element_links_f pel,
655 pay_element_entries_f pee
656 where pel.element_type_id = p_element_type_id
657 and pee.element_link_id = pel.element_link_id)
658 or exists(
659 select null
660 from pay_run_results prr
661 where prr.element_type_id = p_element_type_id);
662 begin
663 hr_utility.set_location('Entering: ' || c_proc, 10);
664 --
665 open csr_exists;
666 fetch csr_exists into l_exists;
667 if csr_exists%notfound then
668 l_exists := 'N';
669 end if;
670 close csr_exists;
671 --
672 hr_utility.trace('Exists? : ' || l_exists);
673 hr_utility.set_location('Leaving: ' || c_proc, 100);
674 --
675 return (l_exists = 'Y');
676 end entries_or_results_exist;
677 -- |-------------------------------------------------------------------|
678 -- |-------------------< entries_or_results_exist >--------------------|
679 -- |-------------------------------------------------------------------|
680 function entries_or_results_exist(p_assignment_id in number) return boolean
681 is
682 c_proc constant varchar2(61) := c_package || 'entries_or_results_exist';
683 l_exists varchar2(1);
684 --
685 -- Check whether either any element entries or run results exist.
686 --
687 cursor csr_exists is
688 select 'Y'
689 from dual
690 where exists(
691 select null -- Removed the Hint Bug# 4674234
692 from pay_element_entries_f pee
693 where pee.assignment_id = p_assignment_id)
694 or exists(
695 select null
696 from pay_assignment_actions paa,
697 pay_run_results prr
698 where paa.assignment_id = p_assignment_id
699 and prr.assignment_action_id = paa.assignment_action_id);
700 begin
701 hr_utility.set_location('Entering: ' || c_proc, 10);
702 --
703 open csr_exists;
704 fetch csr_exists into l_exists;
705 if csr_exists%notfound then
706 l_exists := 'N';
707 end if;
708 close csr_exists;
709 --
710 hr_utility.trace(p_assignment_id || ' : ' || l_exists);
711 hr_utility.set_location('Leaving: ' || c_proc, 100);
712 --
713 return (l_exists = 'Y');
714 end entries_or_results_exist;
715 -- |-------------------------------------------------------------------|
716 -- |-------------------< entries_or_results_exist >--------------------|
717 -- |-------------------------------------------------------------------|
718 function entries_or_results_exist(
719 p_assignment_id in number,
720 p_element_type_id in number) return boolean
721 is
722 c_proc constant varchar2(61) := c_package || 'entries_or_results_exist';
723 l_exists varchar2(1);
724 --
725 -- Check whether either any element entries or run results exist.
726 --
727 cursor csr_exists is
728 select 'Y'
729 from dual
730 where exists(
731 select /*+ ORDERED
732 USE_NL(PEE)
733 INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
734 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
735 null
736 from pay_element_links_f pel,
737 pay_element_entries_f pee
738 where pel.element_type_id = p_element_type_id
739 and pee.assignment_id = p_assignment_id
740 and pee.element_link_id = pel.element_link_id)
741 or exists(
742 -- Removed Hint on PAA as per Bug# 4674234.
743 select /*+ ORDERED
744 USE_NL(PRR)
745 INDEX(PRR PAY_RUN_RESULTS_N50) */
746 null
747 from pay_assignment_actions paa,
748 pay_run_results prr
749 where paa.assignment_id = p_assignment_id
750 and prr.assignment_action_id = paa.assignment_action_id
751 and prr.element_type_id = p_element_type_id);
752 begin
753 hr_utility.set_location('Entering: ' || c_proc, 10);
754 --
755 open csr_exists;
756 fetch csr_exists into l_exists;
757 if csr_exists%notfound then
758 l_exists := 'N';
759 end if;
760 close csr_exists;
761 --
762 hr_utility.trace(p_assignment_id || ' : ' || l_exists);
763 hr_utility.set_location('Leaving: ' || c_proc, 100);
764 --
765 return (l_exists = 'Y');
766 end entries_or_results_exist;
767 -- |-------------------------------------------------------------------|
768 -- |--------------------< sync_link_input_values >---------------------|
769 -- |-------------------------------------------------------------------|
770 procedure sync_link_input_values(p_element_type_id in number)
771 is
772 c_proc constant varchar2(61) := c_package || 'sync_link_input_values';
773 --
774 l_link_input_value_id number;
775 l_effective_start_date date;
776 l_effective_end_date date;
777 l_object_version_number number;
778 l_pay_basis_warning boolean;
779 --
780 cursor csr_link is
781 select pel.element_link_id,
782 piv.input_value_id,
783 min(pel.effective_start_date) effective_start_date,
784 max(pel.effective_end_date) effective_end_date
785 from pay_element_links_f pel,
786 pay_input_values_f piv
787 where pel.element_type_id = p_element_type_id
788 and piv.element_type_id = pel.element_type_id
789 and pel.effective_start_date
790 between piv.effective_start_date and piv.effective_end_date
791 and not exists(
792 select null
793 from pay_link_input_values_f pliv
794 where pliv.element_link_id = pel.element_link_id
795 and pliv.input_value_id = piv.input_value_id)
796 group by pel.element_link_id, piv.input_value_id;
797 begin
798 hr_utility.set_location('Entering: ' || c_proc, 10);
799 --
800 if p_element_type_id is not null then
801 for l_rec in csr_link loop
802 hr_utility.trace('******************************');
803 hr_utility.trace('element_link_id : ' || l_rec.element_link_id);
804 hr_utility.trace('input_value_id : ' || l_rec.input_value_id);
805 hr_utility.trace('effective_start_date : ' || l_rec.effective_start_date);
806 hr_utility.trace('effective_end_date : ' || l_rec.effective_end_date);
807 --
808 pay_link_input_values_api.create_liv_internal(
809 P_EFFECTIVE_DATE => l_rec.effective_start_date,
810 P_ELEMENT_LINK_ID => l_rec.element_link_id,
811 P_INPUT_VALUE_ID => l_rec.input_value_id,
812 P_COSTED_FLAG => 'N',
813 P_DEFAULT_VALUE => null, -- Not used
814 P_MAX_VALUE => null, -- Not used
815 P_MIN_VALUE => null, -- Not used
816 P_WARNING_OR_ERROR => null, -- Not used
817 P_LINK_INPUT_VALUE_ID => l_link_input_value_id,
818 P_EFFECTIVE_START_DATE => l_effective_start_date,
819 P_EFFECTIVE_END_DATE => l_effective_end_date,
820 P_OBJECT_VERSION_NUMBER => l_object_version_number,
821 P_PAY_BASIS_WARNING => l_pay_basis_warning);
822 --
823 hr_utility.trace('------------------------------');
824 hr_utility.trace('link_input_value_id : ' || l_link_input_value_id);
825 hr_utility.trace('effective_start_date : ' || l_effective_start_date);
826 hr_utility.trace('effective_end_date : ' || l_effective_end_date);
827 end loop;
828 end if;
829 --
830 hr_utility.set_location('Leaving: ' || c_proc, 100);
831 end sync_link_input_values;
832 -- |-------------------------------------------------------------------|
833 -- |-------------------< sync_entries_and_results >--------------------|
834 -- |-------------------------------------------------------------------|
835 procedure sync_entries_and_results(
836 p_assignment_id in number,
837 p_element_type_id in number)
838 is
839 c_proc constant varchar2(61) := c_package || 'sync_entries_and_results';
840 --
841 l_rule_mode pay_legislation_rules.rule_mode%type;
842 l_found boolean;
843 l_business_group_id number;
844 begin
845 hr_utility.set_location('Entering: ' || c_proc, 10);
846 --
847 if p_element_type_id is not null then
848 --
849 -- Before running this SQL, PAY_LINK_INPUT_VALUES needs to be populated.
850 --
851 insert into pay_element_entry_values_f(
852 ELEMENT_ENTRY_VALUE_ID,
853 EFFECTIVE_START_DATE,
854 EFFECTIVE_END_DATE,
855 INPUT_VALUE_ID,
856 ELEMENT_ENTRY_ID,
857 SCREEN_ENTRY_VALUE)
858 select pay_element_entry_values_s.nextval,
859 pee.effective_start_date,
860 pee.effective_end_date,
861 pliv.input_value_id,
862 pee.element_entry_id,
863 decode(piv.hot_default_flag, 'Y', null, nvl(pliv.default_value, piv.default_value))
864 from pay_element_entries_f pee,
865 pay_element_links_f pel,
866 pay_link_input_values_f pliv,
867 pay_input_values_f piv
868 where pee.assignment_id = p_assignment_id
869 and pel.element_link_id = pee.element_link_id
870 and pee.effective_start_date
871 between pel.effective_start_date and pel.effective_end_date
872 and pel.element_type_id = p_element_type_id
873 and pliv.element_link_id = pel.element_link_id
874 and pee.effective_start_date
875 between pliv.effective_start_date and pliv.effective_end_date
876 and piv.input_value_id = pliv.input_value_id
877 and pee.effective_start_date
878 between piv.effective_start_date and piv.effective_end_date
879 and not exists(
880 /* If required, add hint in the following sql. */
881 select null
882 from pay_element_entry_values_f peev
883 where peev.element_entry_id = pee.element_entry_id
884 and peev.effective_start_date = pee.effective_start_date
885 and peev.effective_end_date = pee.effective_end_date
886 and peev.input_value_id = piv.input_value_id);
887 --
888 hr_utility.trace('assignment_id : ' || p_assignment_id);
889 hr_utility.trace('element_type_id : ' || p_element_type_id);
890 hr_utility.trace(sql%rowcount || ' rows inserted into pay_element_entry_values_f');
891 --
892 -- Check RR_SPARSE legislation rule
893 --
894 pay_core_utils.get_legislation_rule(
895 'RR_SPARSE',
896 'JP',
897 l_rule_mode,
898 l_found);
899 --
900 -- Check ENABLE_RR_SPARSE generic upgrade
901 --
902 if l_rule_mode = 'Y' then
903 select distinct
904 business_group_id
905 into l_business_group_id
906 from per_all_assignments_f
907 where assignment_id = p_assignment_id;
908 --
909 l_rule_mode := pay_core_utils.get_upgrade_status(l_business_group_id, 'ENABLE_RR_SPARSE');
910 end if;
911 -- When RR_SPARSE, create "null" result values only
912 -- for 'E'(Entry) or 'R'(Reversal) run results.
913 -- No need to create for 'I'(Indirect) and 'V'(Reversed Indirect).
914 --
915 if l_rule_mode = 'Y' then
916 null;
917 --
918 -- Bug.4360429
919 -- No "null" result values need to be created when sparse matrix is enabled.
920 --
921 -- insert into pay_run_result_values(
922 -- INPUT_VALUE_ID,
923 -- RUN_RESULT_ID,
924 -- RESULT_VALUE)
925 -- select /*+ ORDERED
926 -- USE_NL(PRR PPA PIV)
927 -- INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N1)
928 -- INDEX(PRR PAY_RUN_RESULTS_N50)
929 -- INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
930 -- INDEX(PIV PAY_INPUT_VALUES_F_N50) */
931 -- piv.input_value_id,
932 -- prr.run_result_id,
933 -- null
934 -- from pay_assignment_actions paa,
935 -- pay_run_results prr,
936 -- pay_payroll_actions ppa,
937 -- pay_input_values_f piv
938 -- where paa.assignment_id = p_assignment_id
939 -- and prr.assignment_action_id = paa.assignment_action_id
940 -- and prr.element_type_id = p_element_type_id
941 -- and prr.source_type in ('E', 'R')
942 -- and ppa.payroll_action_id = paa.payroll_action_id
943 -- and piv.element_type_id = prr.element_type_id
944 -- and ppa.effective_date
945 -- between piv.effective_start_date and piv.effective_end_date
946 -- and not exists(
947 -- /* If required, add hint in the following sql. */
948 -- select null
949 -- from pay_run_result_values prrv
950 -- where prrv.run_result_id = prr.run_result_id
951 -- and prrv.input_value_id = piv.input_value_id);
952 -- --
953 -- hr_utility.trace(sql%rowcount || ' rows inserted into pay_run_result_values (RR_SPARSE)');
954 --
955 -- When not RR_SPARSE, create "null" result values for all appropriate run results.
956 --
957 else
958 /* Removed ORDERED and USE_NL hints to
959 fix Bug 5232799 */
960 insert into pay_run_result_values(
961 INPUT_VALUE_ID,
962 RUN_RESULT_ID,
963 RESULT_VALUE)
964 select /*+ INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
965 INDEX(PRR PAY_RUN_RESULTS_N50)
966 INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
967 INDEX(PIV PAY_INPUT_VALUES_F_N50) */
968 piv.input_value_id,
969 prr.run_result_id,
970 null
971 from pay_assignment_actions paa,
972 pay_run_results prr,
973 pay_payroll_actions ppa,
974 pay_input_values_f piv
975 where paa.assignment_id = p_assignment_id
976 and prr.assignment_action_id = paa.assignment_action_id
977 and prr.element_type_id = p_element_type_id
978 and ppa.payroll_action_id = paa.payroll_action_id
979 and piv.element_type_id = prr.element_type_id
980 and ppa.effective_date
981 between piv.effective_start_date and piv.effective_end_date
982 and not exists(
983 /* If required, add hint in the following sql. */
984 select null
985 from pay_run_result_values prrv
986 where prrv.run_result_id = prr.run_result_id
987 and prrv.input_value_id = piv.input_value_id);
988 --
989 hr_utility.trace(sql%rowcount || ' rows inserted into pay_run_result_values');
990 end if;
991 end if;
992 --
993 hr_utility.set_location('Leaving: ' || c_proc, 100);
994 end sync_entries_and_results;
995 -- |-------------------------------------------------------------------|
996 -- |----------------------< get_element_type_id >----------------------|
997 -- |-------------------------------------------------------------------|
998 procedure get_element_type_id(
999 p_element_name in varchar2,
1000 p_element_type_ids in out nocopy t_numbers)
1001 is
1002 l_element_type_id number;
1003 --
1004 cursor csr_id is
1005 select distinct
1006 element_type_id
1007 from pay_element_types_f
1008 where element_name = p_element_name
1009 and business_group_id is null
1010 and legislation_code = 'JP';
1011 begin
1012 open csr_id;
1013 fetch csr_id into l_element_type_id;
1014 if csr_id%found then
1015 hr_utility.trace(p_element_name || ': ' || l_element_type_id);
1016 p_element_type_ids(p_element_type_ids.count + 1) := l_element_type_id;
1017 else
1018 hr_utility.trace(p_element_name || ' NOT found!');
1019 end if;
1020 close csr_id;
1021 end get_element_type_id;
1022 -- |-------------------------------------------------------------------|
1023 -- |--------------------------< init_pay_a >---------------------------|
1024 -- |-------------------------------------------------------------------|
1025 procedure init_pay_a
1026 is
1027 c_proc constant varchar2(61) := c_package || 'init_pay_a';
1028 begin
1029 hr_utility.set_location('Entering: ' || c_proc, 10);
1030 --
1031 if g_element_type_ids.count = 0 then
1032 hr_utility.trace('Not cached.');
1033 --
1034 get_element_type_id('INI_SAL2', g_element_type_ids);
1035 get_element_type_id('INI_BON2', g_element_type_ids);
1036 get_element_type_id('INI_SPB1', g_element_type_ids);
1037 get_element_type_id('INI_YEA2', g_element_type_ids);
1038 get_element_type_id('COM_ITX_INFO', g_element_type_ids);
1039 get_element_type_id('SAL_HI_PREM_PROC', g_element_type_ids);
1040 get_element_type_id('SAL_WP_PREM_PROC', g_element_type_ids);
1041 get_element_type_id('SAL_ITX', g_element_type_ids);
1042 get_element_type_id('BON_HI_PREM_PROC', g_element_type_ids);
1043 get_element_type_id('BON_WP_PREM_PROC', g_element_type_ids);
1044 get_element_type_id('BON_ITX', g_element_type_ids);
1045 get_element_type_id('SPB_ITX', g_element_type_ids);
1046 get_element_type_id('YEA_PREV_EMP_INFO', g_element_type_ids);
1047 get_element_type_id('YEA_ADJ_INFO', g_element_type_ids);
1048 get_element_type_id('YEA_AMT_AFTER_EMP_INCOME_DCT_RSLT', g_element_type_ids);
1049 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
1050 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_RSLT', g_element_type_ids);
1051 get_element_type_id('YEA_HOUSING_LOAN_TAX_CREDIT', g_element_type_ids);
1052 get_element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', g_element_type_ids);
1053 get_element_type_id('GEP_ADJ_INFO', g_element_type_ids);
1054 get_element_type_id('SAN_ADJ_INFO', g_element_type_ids);
1055 get_element_type_id('SAN_REPORT_RMKS_RSLT', g_element_type_ids);
1056 end if;
1057 --
1058 -- for i in 1..g_element_type_ids.count loop
1059 -- hr_utility.trace(i || ': ' || g_element_type_ids(i));
1060 -- end loop;
1061 --
1062 hr_utility.set_location('Leaving: ' || c_proc, 100);
1063 end init_pay_a;
1064 -- |-------------------------------------------------------------------|
1065 -- |------------------------< validate_pay_a >-------------------------|
1066 -- |-------------------------------------------------------------------|
1067 procedure validate_pay_a(p_valid_upgrade out nocopy varchar2)
1068 is
1069 c_proc constant varchar2(61) := c_package || 'validate_pay_a';
1070 --
1071 l_element_type_id1 number;
1072 l_element_type_id2 number;
1073 l_element_type_id3 number;
1074 begin
1075 hr_utility.set_location('Entering: ' || c_proc, 10);
1076 --
1077 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
1078 --
1079 init_pay_a;
1080 --
1081 for i in 1..g_element_type_ids.count loop
1082 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
1083 end loop;
1084 --
1085 if entries_or_results_exist(p_legislation_code => 'JP') then
1086 p_valid_upgrade := 'TRUE';
1087 else
1088 p_valid_upgrade := 'FALSE';
1089 end if;
1090 --
1091 hr_utility.trace(p_valid_upgrade);
1092 hr_utility.set_location('Leaving: ' || c_proc, 100);
1093 end validate_pay_a;
1094 -- |-------------------------------------------------------------------|
1095 -- |-------------------------< qualify_pay_a >-------------------------|
1096 -- |-------------------------------------------------------------------|
1097 procedure qualify_pay_a(
1098 p_assignment_id in number,
1099 p_qualifier out nocopy varchar2)
1100 is
1101 c_proc constant varchar2(61) := c_package || 'qualify_pay_a';
1102 begin
1103 hr_utility.set_location('Entering: ' || c_proc, 10);
1104 --
1105 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
1106 p_qualifier := 'Y';
1107 else
1108 p_qualifier := 'N';
1109 end if;
1110 --
1111 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1112 hr_utility.set_location('Leaving: ' || c_proc, 100);
1113 end qualify_pay_a;
1114 -- |-------------------------------------------------------------------|
1115 -- |----------------------< upgrade_pay_a >----------------------------|
1116 -- |-------------------------------------------------------------------|
1117 procedure upgrade_pay_a(p_assignment_id in number)
1118 is
1119 c_proc constant varchar2(61) := c_package || 'upgrade_pay_a';
1120 begin
1121 hr_utility.set_location('Entering: ' || c_proc, 10);
1122 --
1123 init_pay_a;
1124 --
1125 for i in 1..g_element_type_ids.count loop
1126 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
1127 end loop;
1128 --
1129 hr_utility.set_location('Leaving: ' || c_proc, 100);
1130 end upgrade_pay_a;
1131 -- |-------------------------------------------------------------------|
1132 -- |-------------------< validate_itax_description >-------------------|
1133 -- |-------------------------------------------------------------------|
1134 procedure validate_itax_description(
1135 p_valid_upgrade out nocopy varchar2
1136 )
1137 is
1138 --
1139 c_proc constant varchar2(61) := c_package || 'validate_itax_description';
1140 --
1141 begin
1142 --
1143 hr_utility.set_location('Entering: ' || c_proc, 10);
1144 --
1145 --
1146 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
1147 --
1148 if g_element_type_ids.count = 0 then
1149 --
1150 get_element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', g_element_type_ids);
1151 --
1152 end if;
1153 --
1154 for i in 1..g_element_type_ids.count loop
1155 --
1156 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
1157 --
1158 end loop;
1159 --
1160 if entries_or_results_exist(p_legislation_code => 'JP') then
1161 p_valid_upgrade := 'TRUE';
1162 else
1163 p_valid_upgrade := 'FALSE';
1164 end if;
1165 --
1166 hr_utility.trace(p_valid_upgrade);
1167 hr_utility.set_location('Leaving: ' || c_proc, 100);
1168 --
1169 end validate_itax_description;
1170 -- |-------------------------------------------------------------------|
1171 -- |-------------------< qualify_itax_description >--------------------|
1172 -- |-------------------------------------------------------------------|
1173 procedure qualify_itax_description(
1174 p_assignment_id in number,
1175 p_qualifier out nocopy varchar2
1176 )
1177 is
1178 c_proc constant varchar2(61) := c_package || 'qualify_itax_description';
1179 begin
1180 hr_utility.set_location('Entering: ' || c_proc, 10);
1181 --
1182 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
1183 p_qualifier := 'Y';
1184 else
1185 p_qualifier := 'N';
1186 end if;
1187 --
1188 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1189 hr_utility.set_location('Leaving: ' || c_proc, 100);
1190 end qualify_itax_description;
1191 -- |-------------------------------------------------------------------|
1192 -- |-------------------< upgrade_itax_description >--------------------|
1193 -- |-------------------------------------------------------------------|
1194 procedure upgrade_itax_description(
1195 p_assignment_id in number
1196 )
1197 is
1198 c_proc constant varchar2(61) := c_package || 'upgrade_itax_description';
1199 begin
1200 --
1201 hr_utility.set_location('Entering: ' || c_proc, 10);
1202 --
1203 if g_element_type_ids.count = 0 then
1204 --
1205 get_element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', g_element_type_ids);
1206 --
1207 end if;
1208 --
1209 for i in 1..g_element_type_ids.count loop
1210 --
1211 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
1212 --
1213 end loop;
1214 --
1215 hr_utility.set_location('Leaving: ' || c_proc, 100);
1216 --
1217 end upgrade_itax_description;
1218 -- |-------------------------------------------------------------------|
1219 -- |--------------------< validate_code_jp_pre_tax >-------------------|
1220 -- |-------------------------------------------------------------------|
1221 procedure validate_code_jp_pre_tax(p_valid_upgrade out nocopy varchar2)
1222 is
1223 c_proc constant varchar2(61) := c_package || 'validate_code_jp_pre_tax';
1224 --
1225 cursor csr_exists is
1226 select 'TRUE'
1227 from pay_jp_pre_tax_old ppt,
1228 pay_assignment_actions paa
1229 where paa.action_status = 'C'
1230 and ppt.assignment_action_id = paa.assignment_action_id
1231 and ppt.assignment_action_id NOT IN (
1232 select action_information1
1233 from pay_action_information
1234 where action_information_category = 'JP_PRE_TAX_1'
1235 and action_context_type = 'AAP')
1236 and rownum =1;
1237
1238 begin
1239 hr_utility.set_location('Entering: ' || c_proc, 10);
1240 --
1241 open csr_exists;
1242 fetch csr_exists into p_valid_upgrade;
1243 if csr_exists%notfound then
1244 p_valid_upgrade := 'FALSE';
1245 end if;
1246 close csr_exists;
1247 --
1248 hr_utility.trace(p_valid_upgrade);
1249 hr_utility.set_location('Leaving: ' || c_proc, 100);
1250 end validate_code_jp_pre_tax;
1251 -- |-------------------------------------------------------------------|
1252 -- |--------------------< qualifying_jp_pre_tax >----------------------|
1253 -- |-------------------------------------------------------------------|
1254 procedure qualifying_jp_pre_tax(
1255 p_assignment_id in number,
1256 p_qualifier out nocopy varchar2)
1257 is
1258 c_proc constant varchar2(61) := c_package || 'qualifying_jp_pre_tax';
1259 --
1260 cursor csr_ppt is
1261 select 'Y'
1262 from pay_jp_pre_tax_old ppt,
1263 pay_assignment_actions paa
1264 where paa.assignment_id = p_assignment_id
1265 and paa.action_status = 'C'
1266 and ppt.assignment_action_id = paa.assignment_action_id
1267 and ppt.assignment_action_id NOT IN (
1268 select action_information1
1269 from pay_action_information
1270 where action_information_category = 'JP_PRE_TAX_1'
1271 and action_context_type = 'AAP')
1272 and rownum = 1;
1273
1274 begin
1275 hr_utility.set_location('Entering: ' || c_proc, 10);
1276 --
1277 open csr_ppt;
1278 fetch csr_ppt into p_qualifier;
1279 if csr_ppt%notfound then
1280 p_qualifier := 'N';
1281 end if;
1282 close csr_ppt;
1283 --
1284 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1285 hr_utility.set_location('Leaving: ' || c_proc, 100);
1286 end qualifying_jp_pre_tax;
1287 -- |-------------------------------------------------------------------|
1288 -- |----------------------< upgrade_jp_pre_tax >-----------------------|
1289 -- |-------------------------------------------------------------------|
1290 procedure upgrade_jp_pre_tax(p_assignment_id in number)
1291 is
1292 c_proc constant varchar2(61) := c_package || 'upgrade_jp_pre_tax';
1293 --
1294 l_person_id per_all_assignments_f.person_id%TYPE;
1295 l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
1296 l_date_start per_periods_of_service.date_start%TYPE;
1297 l_leaving_reason per_periods_of_service.leaving_reason%TYPE;
1298 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
1299 l_employment_category per_all_assignments.employment_category%TYPE;
1300 l_effective_date pay_payroll_actions.effective_date%TYPE;
1301 --
1302 l_action_info_id1 pay_action_information.action_information_id%TYPE;
1303 l_action_info_id2 pay_action_information.action_information_id%TYPE;
1304 l_ovn pay_action_information.object_version_number%TYPE;
1305 --
1306 cursor csr_pre_tax is
1307 select ppt.*, ppa.effective_date
1308 from pay_jp_pre_tax_old ppt,
1309 pay_assignment_actions paa,
1310 pay_payroll_actions ppa
1311 where paa.assignment_id = p_assignment_id
1312 and paa.assignment_action_id = ppt.assignment_action_id
1313 and paa.payroll_action_id = ppa.payroll_action_id
1314 and not exists ( select 1
1315 from pay_action_information pai
1316 where ppt.assignment_action_id = pai.action_information1
1317 and paa.assignment_id = pai.assignment_id
1318 and pai.action_information_category = 'JP_PRE_TAX_1'
1319 and pai.action_context_type = 'AAP'
1320 )
1321 for update of ppt.pre_tax_id;
1322
1323 begin
1324 begin
1325 hr_utility.set_location('Entering: ' || c_proc, 10);
1326 hr_utility.trace('assignment_id: ' || p_assignment_id);
1327 --
1328 for l_pre_tax in csr_pre_tax
1329 loop
1330 hr_utility.set_location(c_proc, 20);
1331 --
1332 l_effective_date := l_pre_tax.effective_date;
1333 --
1334 select asg.person_id,
1335 asg.period_of_service_id,
1336 pds.date_start,
1337 pds.leaving_reason,
1338 pds.actual_termination_date,
1339 asg.employment_category
1340 into l_person_id,
1341 l_period_of_service_id,
1342 l_date_start,
1343 l_leaving_reason,
1344 l_actual_termination_date,
1345 l_employment_category
1346 from per_all_assignments_f asg,
1347 per_periods_of_service pds
1348 where asg.assignment_id = p_assignment_id
1349 and l_effective_date between asg.effective_start_date and asg.effective_end_date
1350 and pds.period_of_service_id = asg.period_of_service_id;
1351 --
1352 hr_utility.trace('salary_category : ' || l_pre_tax.salary_category);
1353 hr_utility.trace('taxable_sal_amt : ' || l_pre_tax.taxable_sal_amt);
1354 hr_utility.trace('taxable_mat_amt : ' || l_pre_tax.taxable_mat_amt);
1355 hr_utility.trace('previous_taxable_amt : ' || l_pre_tax.previous_taxable_amt);
1356 hr_utility.trace('hi_organization_id : ' || l_pre_tax.hi_organization_id);
1357 hr_utility.trace('hi_prem_ee : ' || l_pre_tax.hi_prem_ee);
1358 hr_utility.trace('hi_prem_er : ' || l_pre_tax.hi_prem_er);
1359 hr_utility.trace('wp_organization_id : ' || l_pre_tax.wp_organization_id);
1360 hr_utility.trace('wp_prem_ee : ' || l_pre_tax.wp_prem_ee);
1361 hr_utility.trace('wp_prem_er : ' || l_pre_tax.wp_prem_er);
1362 hr_utility.trace('wpf_organization_id : ' || l_pre_tax.wpf_organization_id);
1363 hr_utility.trace('wpf_prem_ee : ' || l_pre_tax.wpf_prem_ee);
1364 hr_utility.trace('wpf_prem_er : ' || l_pre_tax.wpf_prem_er);
1365 hr_utility.trace('ui_organization_id : ' || l_pre_tax.ui_organization_id);
1366 hr_utility.trace('ui_category : ' || l_pre_tax.ui_category);
1367 hr_utility.trace('ui_prem_ee : ' || l_pre_tax.ui_prem_ee);
1368 hr_utility.trace('ui_sal_amt : ' || l_pre_tax.ui_sal_amt);
1369 hr_utility.trace('wai_organization_id : ' || l_pre_tax.wai_organization_id);
1370 hr_utility.trace('wai_category : ' || l_pre_tax.wai_category);
1371 hr_utility.trace('wai_sal_amt : ' || l_pre_tax.wai_sal_amt);
1372 hr_utility.trace('itax_organization_id : ' || l_pre_tax.itax_organization_id);
1373 hr_utility.trace('itax_category : ' || l_pre_tax.itax_category);
1374 hr_utility.trace('itax_yea_category : ' || l_pre_tax.itax_yea_category);
1375 hr_utility.trace('itax : ' || l_pre_tax.itax);
1376 hr_utility.trace('itax_adjustment : ' || l_pre_tax.itax_adjustment);
1377 hr_utility.trace('previous_itax : ' || l_pre_tax.previous_itax);
1378 hr_utility.trace('ltax_organization_id : ' || l_pre_tax.ltax_organization_id);
1379 hr_utility.trace('ltax_district_code : ' || l_pre_tax.ltax_district_code);
1380 hr_utility.trace('ltax_swot_no : ' || l_pre_tax.ltax_swot_no);
1381 hr_utility.trace('ltax : ' || l_pre_tax.ltax);
1382 hr_utility.trace('ltax_lumpsum : ' || l_pre_tax.ltax_lumpsum);
1383 hr_utility.trace('sp_ltax : ' || l_pre_tax.sp_ltax);
1384 hr_utility.trace('sp_ltax_income : ' || l_pre_tax.sp_ltax_income);
1385 hr_utility.trace('sp_ltax_shi : ' || l_pre_tax.sp_ltax_shi);
1386 hr_utility.trace('sp_ltax_to : ' || l_pre_tax.sp_ltax_to);
1387 hr_utility.trace('ci_prem_ee : ' || l_pre_tax.ci_prem_ee);
1388 hr_utility.trace('ci_prem_er : ' || l_pre_tax.ci_prem_er);
1389 hr_utility.trace('mutual_aid : ' || l_pre_tax.mutual_aid);
1390 hr_utility.trace('disaster_tax_reduction : ' || l_pre_tax.disaster_tax_reduction);
1391 hr_utility.trace('sp_ltax_district_code : ' || l_pre_tax.sp_ltax_district_code);
1392 --
1393 pay_action_information_api.create_action_information
1394 (
1395 p_action_information_id => l_action_info_id1
1396 ,p_action_context_id => l_pre_tax.assignment_action_id
1397 ,p_action_context_type => 'AAP'
1398 ,p_object_version_number => l_ovn
1399 ,p_effective_date => l_effective_date
1400 ,p_assignment_id => p_assignment_id
1401 ,p_action_information_category => 'JP_PRE_TAX_1'
1402 ,p_action_information1 => fnd_number.number_to_canonical(l_pre_tax.assignment_action_id)
1403 ,p_action_information2 => fnd_number.number_to_canonical(l_pre_tax.taxable_sal_amt)
1404 ,p_action_information3 => fnd_number.number_to_canonical(l_pre_tax.taxable_mat_amt)
1405 ,p_action_information4 => fnd_number.number_to_canonical(l_person_id)
1406 ,p_action_information5 => l_pre_tax.hi_organization_id
1407 ,p_action_information6 => fnd_number.number_to_canonical(l_pre_tax.hi_prem_ee)
1408 ,p_action_information7 => fnd_number.number_to_canonical(l_pre_tax.hi_prem_er)
1409 ,p_action_information8 => l_pre_tax.wp_organization_id
1410 ,p_action_information9 => fnd_number.number_to_canonical(l_pre_tax.wp_prem_ee)
1411 ,p_action_information10 => fnd_number.number_to_canonical(l_pre_tax.wp_prem_er)
1412 ,p_action_information11 => l_pre_tax.wpf_organization_id
1413 ,p_action_information12 => fnd_number.number_to_canonical(l_pre_tax.wpf_prem_ee)
1414 ,p_action_information13 => l_pre_tax.salary_category
1415 ,p_action_information14 => fnd_number.number_to_canonical(l_pre_tax.mutual_aid)
1416 ,p_action_information15 => fnd_number.number_to_canonical(l_period_of_service_id)
1417 ,p_action_information16 => to_char(l_date_start,'YYYY/MM/DD')
1418 ,p_action_information17 => l_leaving_reason
1419 ,p_action_information18 => to_char(l_actual_termination_date,'YYYY/MM/DD')
1420 ,p_action_information19 => l_pre_tax.ui_organization_id
1421 ,p_action_information20 => fnd_number.number_to_canonical(l_pre_tax.ui_prem_ee)
1422 ,p_action_information21 => l_pre_tax.itax_organization_id
1423 ,p_action_information22 => l_pre_tax.itax_category
1424 ,p_action_information23 => l_pre_tax.itax_yea_category
1425 ,p_action_information24 => fnd_number.number_to_canonical(l_pre_tax.itax)
1426 ,p_action_information25 => fnd_number.number_to_canonical(l_pre_tax.itax_adjustment)
1427 ,p_action_information26 => fnd_number.number_to_canonical(l_pre_tax.pre_tax_id)
1428 ,p_action_information29 => fnd_number.number_to_canonical(l_pre_tax.disaster_tax_reduction)
1429 ,p_action_information30 => l_employment_category
1430 );
1431
1432 pay_action_information_api.create_action_information
1433 (
1434 p_action_information_id => l_action_info_id2
1435 ,p_action_context_id => l_pre_tax.assignment_action_id
1436 ,p_action_context_type => 'AAP'
1437 ,p_object_version_number => l_ovn
1438 ,p_effective_date => l_effective_date
1439 ,p_assignment_id => p_assignment_id
1440 ,p_action_information_category => 'JP_PRE_TAX_2'
1441 ,p_action_information1 => l_pre_tax.assignment_action_id
1442 ,p_action_information3 => l_pre_tax.ltax_district_code
1443 ,p_action_information5 => fnd_number.number_to_canonical(l_pre_tax.ltax)
1444 ,p_action_information6 => fnd_number.number_to_canonical(l_pre_tax.ltax_lumpsum)
1445 ,p_action_information7 => fnd_number.number_to_canonical(l_pre_tax.sp_ltax)
1446 ,p_action_information8 => fnd_number.number_to_canonical(l_pre_tax.sp_ltax_income)
1447 ,p_action_information9 => fnd_number.number_to_canonical(l_pre_tax.sp_ltax_shi)
1448 ,p_action_information10 => fnd_number.number_to_canonical(l_pre_tax.sp_ltax_to)
1449 ,p_action_information11 => fnd_number.number_to_canonical(l_pre_tax.ci_prem_ee)
1450 ,p_action_information12 => fnd_number.number_to_canonical(l_pre_tax.ci_prem_er)
1451 ,p_action_information13 => fnd_number.number_to_canonical(0)
1452 ,p_action_information14 => l_pre_tax.ui_category
1453 ,p_action_information15 => l_pre_tax.sp_ltax_district_code
1454 ,p_action_information16 => fnd_number.number_to_canonical(l_pre_tax.ui_sal_amt)
1455 ,p_action_information17 => l_pre_tax.wai_organization_id
1456 ,p_action_information18 => l_pre_tax.wai_category
1457 ,p_action_information19 => fnd_number.number_to_canonical(l_pre_tax.wai_sal_amt)
1458 ,p_action_information20 => fnd_number.number_to_canonical(l_pre_tax.wpf_prem_er)
1459 ,p_action_information21 => fnd_number.number_to_canonical(0)
1460 );
1461
1462 end loop;
1463 --
1464 hr_utility.set_location('Leaving: ' || c_proc, 100);
1465 --
1466 exception
1467 when app_exception.application_exception then
1468 app_exception.raise_exception;
1469 end;
1470 --
1471 exception
1472 when others then
1473 if g_num_errors = 0 then
1474 fnd_file.put_line(fnd_file.log, fnd_message.get_string('PAY', 'PAY_JP_RETRY_JP_PRE_TAX_UPG'));
1475 fnd_file.put_line(fnd_file.log,
1476 rpad(fnd_message.get_string('PAY', 'PAY_JP_ASSIGNMENT_ID'), 30) || ' ' || fnd_message.get_string('FND', 'FND_MESSAGE_TYPE_ERROR'));
1477 fnd_file.put_line(fnd_file.log, rpad('-', 30, '-') || ' ' || rpad('-', 100, '-'));
1478 end if;
1479 g_num_errors := g_num_errors + 1;
1480 --
1481 fnd_file.put_line(fnd_file.log, rpad(p_assignment_id, 30) || ' ' || sqlerrm);
1482 raise;
1483 end upgrade_jp_pre_tax;
1484 -- |-------------------------------------------------------------------|
1485 -- |-------------------< validate_yea_national_pens >------------------|
1486 -- |-------------------------------------------------------------------|
1487 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
1488 procedure validate_yea_national_pens(
1489 p_valid_upgrade out nocopy varchar2)
1490 is
1491 --
1492 c_proc constant varchar2(61) := c_package || 'validate_yea_national_pens';
1493 --
1494 begin
1495 --
1496 hr_utility.set_location('Entering: ' || c_proc, 10);
1497 --
1498 if g_element_type_ids.count = 0 then
1499 --
1500 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
1501 --
1502 end if;
1503 --
1504 for i in 1..g_element_type_ids.count loop
1505 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
1506 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
1507 --
1508 end loop;
1509 --
1510 if entries_or_results_exist('JP') then
1511 p_valid_upgrade := 'TRUE';
1512 else
1513 p_valid_upgrade := 'FALSE';
1514 end if;
1515 --
1516 hr_utility.trace(p_valid_upgrade);
1517 --
1518 hr_utility.set_location('Leaving: ' || c_proc, 100);
1519 --
1520 end validate_yea_national_pens;
1521 -- |-------------------------------------------------------------------|
1522 -- |------------------< qualify_yea_national_pens >--------------------|
1523 -- |-------------------------------------------------------------------|
1524 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
1525 procedure qualify_yea_national_pens(
1526 p_assignment_id in number,
1527 p_qualifier out nocopy varchar2)
1528 is
1529 c_proc constant varchar2(61) := c_package || 'qualify_yea_national_pens';
1530 begin
1531 --
1532 hr_utility.set_location('Entering: ' || c_proc, 10);
1533 --
1534 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
1535 p_qualifier := 'Y';
1536 else
1537 p_qualifier := 'N';
1538 end if;
1539 --
1540 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1541 hr_utility.set_location('Leaving: ' || c_proc, 100);
1542 --
1543 end qualify_yea_national_pens;
1544 -- |-------------------------------------------------------------------|
1545 -- |-------------------< upgrade_yea_national_pens >-------------------|
1546 -- |-------------------------------------------------------------------|
1547 procedure upgrade_yea_national_pens(
1548 p_assignment_id in number)
1549 is
1550 c_proc constant varchar2(61) := c_package || 'upgrade_yea_national_pens';
1551 begin
1552 --
1553 hr_utility.set_location('Entering: ' || c_proc, 10);
1554 --
1555 if g_element_type_ids.count = 0 then
1556 --
1557 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
1558 --
1559 end if;
1560 --
1561 for i in 1..g_element_type_ids.count loop
1562 --
1563 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
1564 --
1565 end loop;
1566 --
1567 hr_utility.set_location('Leaving: ' || c_proc, 100);
1568 --
1569 end upgrade_yea_national_pens;
1570 -- |-------------------------------------------------------------------|
1571 -- |--------------------< init_yea_earthquake_ins >--------------------|
1572 -- |-------------------------------------------------------------------|
1573 procedure init_yea_earthquake_ins
1574 is
1575 c_proc constant varchar2(61) := c_package || 'init_yea_earthquake_ins';
1576 begin
1577 hr_utility.set_location('Entering: ' || c_proc, 10);
1578 --
1579 if g_element_type_ids.count = 0 then
1580 hr_utility.trace('Not cached.');
1581 --
1582 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
1583 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_RSLT', g_element_type_ids);
1584 get_element_type_id('INI_YEA2', g_element_type_ids);
1585 get_element_type_id('YEA_INS_PREM_EXM_DECLARE_INFO', g_element_type_ids);
1586 end if;
1587 --
1588 for i in 1..g_element_type_ids.count loop
1589 hr_utility.trace(i || ': ' || g_element_type_ids(i));
1590 end loop;
1591 --
1592 hr_utility.set_location('Leaving: ' || c_proc, 100);
1593 end init_yea_earthquake_ins;
1594 -- |-------------------------------------------------------------------|
1595 -- |------------------< validate_yea_earthquake_ins >------------------|
1596 -- |-------------------------------------------------------------------|
1597 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
1598 procedure validate_yea_earthquake_ins(p_valid_upgrade out nocopy varchar2)
1599 is
1600 c_proc constant varchar2(61) := c_package || 'validate_yea_earthquake_ins';
1601 begin
1602 hr_utility.set_location('Entering: ' || c_proc, 10);
1603 --
1604 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
1605 --
1606 init_yea_earthquake_ins;
1607 --
1608 for i in 1..g_element_type_ids.count loop
1609 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
1610 end loop;
1611 --
1612 if entries_or_results_exist(p_legislation_code => 'JP') then
1613 p_valid_upgrade := 'TRUE';
1614 else
1615 p_valid_upgrade := 'FALSE';
1616 end if;
1617 --
1618 hr_utility.trace(p_valid_upgrade);
1619 hr_utility.set_location('Leaving: ' || c_proc, 100);
1620 end validate_yea_earthquake_ins;
1621 -- |-------------------------------------------------------------------|
1622 -- |------------------< qualify_yea_earthquake_ins >-------------------|
1623 -- |-------------------------------------------------------------------|
1624 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
1625 procedure qualify_yea_earthquake_ins(
1626 p_assignment_id in number,
1627 p_qualifier out nocopy varchar2)
1628 is
1629 c_proc constant varchar2(61) := c_package || 'qualify_yea_earthquake_ins';
1630 begin
1631 hr_utility.set_location('Entering: ' || c_proc, 10);
1632 --
1633 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
1634 p_qualifier := 'Y';
1635 else
1636 p_qualifier := 'N';
1637 end if;
1638 --
1639 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1640 hr_utility.set_location('Leaving: ' || c_proc, 100);
1641 end qualify_yea_earthquake_ins;
1642 -- |-------------------------------------------------------------------|
1643 -- |------------------< upgrade_yea_earthquake_ins >-------------------|
1644 -- |-------------------------------------------------------------------|
1645 procedure upgrade_yea_earthquake_ins(p_assignment_id in number)
1646 is
1647 c_proc constant varchar2(61) := c_package || 'upgrade_yea_earthquake_ins';
1648 begin
1649 hr_utility.set_location('Entering: ' || c_proc, 10);
1650 --
1651 init_yea_earthquake_ins;
1652 --
1653 for i in 1..g_element_type_ids.count loop
1654 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
1655 end loop;
1656 --
1657 hr_utility.set_location('Leaving: ' || c_proc, 100);
1658 end upgrade_yea_earthquake_ins;
1659 -- |-------------------------------------------------------------------|
1660 -- |---------------------< validate_itw_archive >----------------------|
1661 -- |-------------------------------------------------------------------|
1662 procedure validate_itw_archive(p_valid_upgrade out nocopy varchar2)
1663 is
1664 c_proc constant varchar2(61) := c_package || 'validate_itw_archive';
1665 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
1666 l_payroll_id number;
1667 l_itax_organization_id number;
1668 l_include_terminated_flag varchar2(1);
1669 l_termination_date_from date;
1670 l_termination_date_to date;
1671 l_rearchive_flag varchar2(1);
1672 l_inherit_archive_flag varchar2(1);
1673 l_publication_period_status varchar2(1);
1674 l_publication_start_date date;
1675 l_publication_end_date date;
1676 --
1677 l_id number;
1678 l_ovn number;
1679 --
1680 cursor csr_pacts is
1681 select payroll_action_id,
1682 effective_date,
1683 legislative_parameters
1684 from pay_payroll_actions ppa
1685 where ppa.action_type = 'X'
1686 and ppa.report_type = 'JPTW'
1687 and ppa.report_qualifier = 'JP'
1688 and ppa.report_category = 'ARCHIVE'
1689 -- and ppa.action_status <> 'P'
1690 and pay_core_utils.get_parameter('INCLUDE_TERMINATED_FLAG', legislative_parameters) is null;
1691 /*
1692 and not exists(
1693 select null
1694 from pay_action_information pai
1695 where pai.action_context_id = ppa.payroll_action_id
1696 and pai.action_context_type = 'PA'
1697 and pai.action_information_category = 'JP_ITAX_PACT');
1698 */
1699 --
1700 procedure concat_parameter(
1701 p_token_name in varchar2,
1702 p_token_value in varchar2)
1703 is
1704 l_separator varchar2(1);
1705 begin
1706 if p_token_name is not null and p_token_value is not null then
1707 if l_legislative_parameters is not null then
1708 l_legislative_parameters := l_legislative_parameters || ' ';
1709 end if;
1710 --
1711 if instr(p_token_value, ' ') > 0 then
1712 l_separator := '|';
1713 end if;
1714 --
1715 l_legislative_parameters := l_legislative_parameters
1716 || p_token_value || ' '
1717 || p_token_name || '='
1718 || l_separator || p_token_value || l_separator;
1719 end if;
1720 end concat_parameter;
1721 begin
1722 hr_utility.set_location('Entering: ' || c_proc, 10);
1723 --
1724 p_valid_upgrade := 'FALSE';
1725 --
1726 for l_pact in csr_pacts loop
1727 p_valid_upgrade := 'TRUE';
1728 --
1729 l_payroll_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL', l_pact.legislative_parameters));
1730 l_itax_organization_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('SWOT', l_pact.legislative_parameters));
1731 l_include_terminated_flag := 'Y';
1732 l_termination_date_from := null;
1733 l_termination_date_to := null;
1734 l_rearchive_flag := 'Y';
1735 l_inherit_archive_flag := 'Y';
1736 l_publication_period_status := 'O';
1737 l_publication_start_date := fnd_date.canonical_to_date(pay_core_utils.get_parameter('OPEN_DATE', l_pact.legislative_parameters));
1738 l_publication_end_date := fnd_date.canonical_to_date(pay_core_utils.get_parameter('CLOSE_DATE', l_pact.legislative_parameters));
1739 --
1740 l_legislative_parameters := null;
1741 concat_parameter('PAYROLL_ID', fnd_number.number_to_canonical(l_payroll_id));
1742 concat_parameter('ITAX_ORGANIZATION_ID', fnd_number.number_to_canonical(l_itax_organization_id));
1743 concat_parameter('INCLUDE_TERMINATED_FLAG', l_include_terminated_flag);
1744 concat_parameter('TERMINATION_DATE_FROM', fnd_date.date_to_canonical(l_termination_date_from));
1745 concat_parameter('TERMINATION_DATE_TO', fnd_date.date_to_canonical(l_termination_date_to));
1746 concat_parameter('REARCHIVE_FLAG', l_rearchive_flag);
1747 concat_parameter('INHERIT_ARCHIVE_FLAG', l_inherit_archive_flag);
1748 concat_parameter('PUBLICATION_PERIOD_STATUS', l_publication_period_status);
1749 concat_parameter('PUBLICATION_START_DATE', fnd_date.date_to_canonical(l_publication_start_date));
1750 concat_parameter('PUBLICATION_END_DATE', fnd_date.date_to_canonical(l_publication_end_date));
1751 concat_parameter('UPGRADE_FLAG', 'Y');
1752 --
1753 hr_utility.trace('payroll_action_id: ' || l_pact.payroll_action_id);
1754 hr_utility.trace('legislative_parameters: ' || l_legislative_parameters);
1755 --
1756 update pay_payroll_actions
1757 set legislative_parameters = l_legislative_parameters
1758 where payroll_action_id = l_pact.payroll_action_id;
1759 --
1760 pay_action_information_api.create_action_information(
1761 p_validate => false,
1762 p_action_context_id => l_pact.payroll_action_id,
1763 p_action_context_type => 'PA',
1764 p_action_information_category => 'JP_ITAX_PACT',
1765 p_effective_date => l_pact.effective_date,
1766 p_action_information1 => fnd_number.number_to_canonical(l_payroll_id),
1767 p_action_information2 => fnd_number.number_to_canonical(l_itax_organization_id),
1768 p_action_information3 => l_include_terminated_flag,
1769 p_action_information4 => fnd_date.date_to_canonical(l_termination_date_from),
1770 p_action_information5 => fnd_date.date_to_canonical(l_termination_date_to),
1771 p_action_information6 => l_publication_period_status,
1772 p_action_information7 => fnd_date.date_to_canonical(l_publication_start_date),
1773 p_action_information8 => fnd_date.date_to_canonical(l_publication_end_date),
1774 p_action_information_id => l_id,
1775 p_object_version_number => l_ovn);
1776 end loop;
1777 --
1778 hr_utility.trace(p_valid_upgrade);
1779 hr_utility.set_location('Leaving: ' || c_proc, 100);
1780 end validate_itw_archive;
1781 -- |-------------------------------------------------------------------|
1782 -- |----------------------< qualify_itw_archive >----------------------|
1783 -- |-------------------------------------------------------------------|
1784 procedure qualify_itw_archive(
1785 p_assignment_id in number,
1786 p_qualifier out nocopy varchar2)
1787 is
1788 c_proc constant varchar2(61) := c_package || 'qualify_itw_archive';
1789 --
1790 -- Old archiver does not have PACT level archive data.
1791 -- This checks whether the archive needs to be upgraded or not
1792 -- using PACT level archive data.
1793 --
1794 cursor csr_upgrade_required is
1795 select 'Y'
1796 from dual
1797 where exists(
1798 select null
1799 from pay_assignment_actions paa,
1800 pay_payroll_actions ppa
1801 where paa.assignment_id = p_assignment_id
1802 and paa.action_status = 'C'
1803 and ppa.payroll_action_id = paa.payroll_action_id
1804 and ppa.action_type = 'X'
1805 and ppa.report_type = 'JPTW'
1806 and ppa.report_qualifier = 'JP'
1807 and ppa.report_category = 'ARCHIVE'
1808 and pay_core_utils.get_parameter('UPGRADE_FLAG', ppa.legislative_parameters) = 'Y');
1809 begin
1810 hr_utility.set_location('Entering: ' || c_proc, 10);
1811 --
1812 open csr_upgrade_required;
1813 fetch csr_upgrade_required into p_qualifier;
1814 if csr_upgrade_required%notfound then
1815 p_qualifier := 'N';
1816 end if;
1817 close csr_upgrade_required;
1818 --
1819 hr_utility.trace(p_assignment_id || ': ' || p_qualifier);
1820 hr_utility.set_location('Leaving: ' || c_proc, 100);
1821 end qualify_itw_archive;
1822 -- |-------------------------------------------------------------------|
1823 -- |-----------------------< to_canonical_date >-----------------------|
1824 -- |-------------------------------------------------------------------|
1825 function to_canonical_date(p_str in varchar2) return varchar2
1826 is
1827 l_str varchar2(30) := p_str;
1828 begin
1829 if l_str is not null then
1830 if length(l_str) = 7 then
1831 l_str := fnd_date.date_to_canonical(hr_jp_standard_pkg.to_jp_date(l_str, 'EYYMMDD'));
1832 elsif length(l_str) = 10 then
1833 l_str := fnd_date.date_to_canonical(to_date(l_str, 'YYYY/MM/DD'));
1834 end if;
1835 end if;
1836 --
1837 return l_str;
1838 end to_canonical_date;
1839 -- |-------------------------------------------------------------------|
1840 -- |----------------------< upgrade_itw_archive >----------------------|
1841 -- |-------------------------------------------------------------------|
1842 procedure upgrade_itw_archive(p_assignment_id in number)
1843 is
1844 c_proc constant varchar2(61) := c_package || 'validate_itw_archive';
1845 --
1846 l_varchar2_tbl hr_jp_standard_pkg.t_varchar2_tbl;
1847 l_itw_user_desc_kanji1 varchar2(240);
1848 l_itw_user_desc_kanji2 varchar2(240);
1849 l_dummy varchar2(32767);
1850 l_wtm_user_desc_kanji1 varchar2(240);
1851 l_wtm_user_desc_kanji2 varchar2(240);
1852 l_wtm_user_desc_kana1 varchar2(240);
1853 l_wtm_user_desc_kana2 varchar2(240);
1854 --
1855 cursor csr is
1856 select person.rowid person_rowid,
1857 arch.rowid arch_rowid,
1858 arch.action_information16 ||
1859 arch.action_information17 ||
1860 arch.action_information18 ||
1861 arch.action_information19 ||
1862 arch.action_information20 ITW_USER_DESC_KANJI,
1863 arch.action_information21 ||
1864 arch.action_information22 ||
1865 arch.action_information23 ||
1866 arch.action_information24 ||
1867 arch.action_information25 WTM_USER_DESC,
1868 other2.rowid other2_rowid,
1869 other2.action_information13 ITW_OVERRIDE_FLAG,
1870 other2.action_information14 WTM_OVERRIDE_FLAG,
1871 other2.ACTION_INFORMATION23 ITW_SYSTEM_DESC1_KANJI,
1872 other2.ACTION_INFORMATION15 ITW_SYSTEM_DESC2_KANJI_1,
1873 other2.ACTION_INFORMATION16 ITW_SYSTEM_DESC2_KANJI_2,
1874 other2.ACTION_INFORMATION19 WTM_SYSTEM_DESC_KANJI_1,
1875 other2.ACTION_INFORMATION20 WTM_SYSTEM_DESC_KANJI_2,
1876 other2.ACTION_INFORMATION21 WTM_SYSTEM_DESC_KANA_1,
1877 other2.ACTION_INFORMATION22 WTM_SYSTEM_DESC_KANA_2
1878 from pay_assignment_actions paa,
1879 pay_payroll_actions ppa,
1880 pay_action_information person,
1881 pay_action_information arch,
1882 pay_action_information other2
1883 where paa.assignment_id = p_assignment_id
1884 and paa.action_status = 'C'
1885 and ppa.payroll_action_id = paa.payroll_action_id
1886 and ppa.action_type = 'X'
1887 and ppa.report_type = 'JPTW'
1888 and ppa.report_qualifier = 'JP'
1889 and ppa.report_category = 'ARCHIVE'
1890 and pay_core_utils.get_parameter('UPGRADE_FLAG', ppa.legislative_parameters) = 'Y'
1891 and person.action_context_id = paa.assignment_action_id
1892 and person.action_context_type = 'AAP'
1893 and person.action_information_category = 'JP_ITAX_PERSON'
1894 and arch.action_context_id = person.action_context_id
1895 and arch.action_context_type = 'AAP'
1896 and arch.action_information_category = 'JP_ITAX_ARCH'
1897 and arch.effective_date = person.effective_date
1898 and other2.action_context_id = person.action_context_id
1899 and other2.action_context_type = 'AAP'
1900 and other2.action_information_category = 'JP_ITAX_OTHER2'
1901 and other2.effective_date = person.effective_date
1902 for update of
1903 person.action_information_id,
1904 other2.action_information_id nowait;
1905 begin
1906 hr_utility.set_location('Entering: ' || c_proc, 10);
1907 --
1908 for l_rec in csr loop
1909 --
1910 -- JP_ITAX_PERSON
1911 --
1912 update pay_action_information
1913 set action_information11 = to_canonical_date(action_information11),
1914 action_information13 = to_canonical_date(action_information13),
1915 action_information25 = to_canonical_date(action_information25),
1916 -- action_information27 = 'O'
1917 action_information27 = null
1918 where rowid = l_rec.person_rowid;
1919 --
1920 -- JP_ITAX_ARCH
1921 --
1922 update pay_action_information
1923 set action_information12 = to_canonical_date(action_information12)
1924 where rowid = l_rec.arch_rowid;
1925 --
1926 -- JP_ITAX_OTHER2
1927 --
1928 -- ITW User Description
1929 --
1930 l_dummy := rtrim(substrb(l_rec.itw_user_desc_kanji, 1, 300));
1931 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1932 l_itw_user_desc_kanji1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1933 l_itw_user_desc_kanji2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1934 --
1935 -- ITW System Description
1936 --
1937 if l_rec.itw_override_flag = 'Y' then
1938 l_rec.ITW_SYSTEM_DESC1_KANJI := null;
1939 l_rec.ITW_SYSTEM_DESC2_KANJI_1 := null;
1940 l_rec.ITW_SYSTEM_DESC2_KANJI_2 := null;
1941 else
1942 l_rec.ITW_SYSTEM_DESC1_KANJI := rtrim(substrb(l_rec.ITW_SYSTEM_DESC1_KANJI, 1, 240));
1943 --
1944 l_dummy := rtrim(substrb(l_rec.ITW_SYSTEM_DESC2_KANJI_1 || l_rec.ITW_SYSTEM_DESC2_KANJI_2, 1, 300));
1945 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1946 l_rec.ITW_SYSTEM_DESC2_KANJI_1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1947 l_rec.ITW_SYSTEM_DESC2_KANJI_2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1948 end if;
1949 --
1950 -- WTM User Description
1951 --
1952 l_dummy := hr_jp_standard_pkg.to_zenkaku(l_rec.wtm_user_desc);
1953 l_dummy := substr(l_dummy, 1, 100);
1954 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1955 l_wtm_user_desc_kanji1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1956 l_wtm_user_desc_kanji2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1957 --
1958 l_dummy := hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(l_rec.wtm_user_desc, '?'));
1959 l_dummy := substr(l_dummy, 1, 100);
1960 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1961 l_wtm_user_desc_kana1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1962 l_wtm_user_desc_kana2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1963 --
1964 -- WTM System Description
1965 --
1966 if l_rec.wtm_override_flag = 'Y' then
1967 l_rec.WTM_SYSTEM_DESC_KANJI_1 := null;
1968 l_rec.WTM_SYSTEM_DESC_KANJI_2 := null;
1969 l_rec.WTM_SYSTEM_DESC_KANA_1 := null;
1970 l_rec.WTM_SYSTEM_DESC_KANA_2 := null;
1971 else
1972 l_dummy := hr_jp_standard_pkg.to_zenkaku(l_rec.WTM_SYSTEM_DESC_KANJI_1 || l_rec.WTM_SYSTEM_DESC_KANJI_2);
1973 l_dummy := substr(l_dummy, 1, 100);
1974 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1975 l_rec.WTM_SYSTEM_DESC_KANJI_1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1976 l_rec.WTM_SYSTEM_DESC_KANJI_2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1977 --
1978 l_dummy := hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(l_rec.WTM_SYSTEM_DESC_KANA_1 || l_rec.WTM_SYSTEM_DESC_KANA_2, '?'));
1979 l_dummy := substr(l_dummy, 1, 100);
1980 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1981 l_rec.WTM_SYSTEM_DESC_KANA_1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1982 l_rec.WTM_SYSTEM_DESC_KANA_2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1983 end if;
1984 --
1985 update pay_action_information
1986 set ACTION_INFORMATION23 = l_rec.ITW_SYSTEM_DESC1_KANJI,
1987 ACTION_INFORMATION15 = l_rec.ITW_SYSTEM_DESC2_KANJI_1,
1988 ACTION_INFORMATION16 = l_rec.ITW_SYSTEM_DESC2_KANJI_2,
1989 ACTION_INFORMATION19 = l_rec.WTM_SYSTEM_DESC_KANJI_1,
1990 ACTION_INFORMATION20 = l_rec.WTM_SYSTEM_DESC_KANJI_2,
1991 ACTION_INFORMATION21 = l_rec.WTM_SYSTEM_DESC_KANA_1,
1992 ACTION_INFORMATION22 = l_rec.WTM_SYSTEM_DESC_KANA_2,
1993 action_information25 = l_itw_user_desc_kanji1,
1994 action_information26 = l_itw_user_desc_kanji2,
1995 action_information27 = l_wtm_user_desc_kanji1,
1996 action_information28 = l_wtm_user_desc_kanji2,
1997 action_information29 = l_wtm_user_desc_kana1,
1998 action_information30 = l_wtm_user_desc_kana2
1999 where rowid = l_rec.other2_rowid;
2000 end loop;
2001 --
2002 hr_utility.set_location('Leaving: ' || c_proc, 100);
2003 end upgrade_itw_archive;
2004 --
2005 -- -------------------------------------------------------------------------
2006 -- qualify_hi_smr_data
2007 -- -------------------------------------------------------------------------
2008 -- run by pay_generic_upgrade.action_creation
2009 -- do_qualification run before calling hr_nonrun_asact.insact in create_object_action
2010 procedure qualify_hi_smr_data(
2011 p_assignment_id in number,
2012 p_qualifier out nocopy varchar2)
2013 is
2014 --
2015 l_proc varchar2(80) := c_package||'qualify_hi_smr_data';
2016 --
2017 l_valid_delete varchar2(1) := 'N';
2018 --
2019 l_ass_info hr_jp_data_migration_pkg.t_ass_hi_smr_rec;
2020 --
2021 begin
2022 --
2023 if g_debug then
2024 hr_utility.set_location(l_proc,0);
2025 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
2026 end if;
2027 --
2028 hr_jp_data_migration_pkg.init_def_hi_smr_data;
2029 --
2030 if g_debug then
2031 hr_utility.set_location(l_proc,10);
2032 hr_utility.trace('hr_jp_data_migration_pkg.g_skip_qualify : '||hr_jp_data_migration_pkg.g_skip_qualify);
2033 hr_utility.trace('hr_jp_data_migration_pkg.g_upd_mode : '||hr_jp_data_migration_pkg.g_upd_mode);
2034 hr_utility.trace('hr_jp_data_migration_pkg.g_mig_date : '||to_char(hr_jp_data_migration_pkg.g_mig_date,'YYYY/MM/DD'));
2035 end if;
2036 --
2037 if hr_jp_data_migration_pkg.g_skip_qualify = 'N' then
2038 --
2039 if g_debug then
2040 hr_utility.set_location(l_proc,20);
2041 end if;
2042 --
2043 -- print header for each threads but it is ok because just log.
2044 hr_jp_data_migration_pkg.qualify_hi_smr_hd(
2045 p_assignment_id => p_assignment_id);
2046 --
2047 if g_debug then
2048 hr_utility.set_location(l_proc,30);
2049 end if;
2050 --
2051 l_ass_info := hr_jp_data_migration_pkg.get_ass_info(
2052 p_assignment_id => p_assignment_id,
2053 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2054 --
2055 if g_debug then
2056 hr_utility.set_location(l_proc,40);
2057 hr_utility.trace('l_ass_info.ass_id : '||to_char(l_ass_info.ass_id));
2058 end if;
2059 --
2060 hr_jp_data_migration_pkg.val_mig_smr_assact(
2061 p_business_group_id => l_ass_info.bg_id,
2062 p_business_group_name => l_ass_info.bg_name,
2063 p_assignment_id => p_assignment_id,
2064 p_assignment_number => l_ass_info.ass_num,
2065 p_session_date => hr_jp_data_migration_pkg.g_mig_date,
2066 p_valid_delete => l_valid_delete);
2067 --
2068 if g_debug then
2069 hr_utility.set_location(l_proc,50);
2070 hr_utility.trace('l_valid_delete : '||l_valid_delete);
2071 end if;
2072 --
2073 p_qualifier := l_valid_delete;
2074 --
2075 -- never come here at this moment.
2076 -- notice: include following case in target because skip qualify.
2077 -- 1. ee null
2078 -- 2. ee already updated (manual update)
2079 -- 3. future entry exists
2080 -- 4. in update mode applied month is future (>= p_session_date)
2081 -- 5. mr is null
2082 else
2083 --
2084 p_qualifier := 'Y';
2085 --
2086 if g_debug then
2087 hr_utility.set_location(l_proc,60);
2088 end if;
2089 --
2090 end if;
2091 --
2092 if g_debug then
2093 hr_utility.trace('p_qualifier : '||p_qualifier);
2094 hr_utility.set_location(l_proc,1000);
2095 end if;
2096 --
2097 end qualify_hi_smr_data;
2098 --
2099 -- -------------------------------------------------------------------------
2100 -- migrate_hi_smr_data
2101 -- -------------------------------------------------------------------------
2102 -- run by pay_generic_upgrade.upgrade_data
2103 procedure migrate_hi_smr_data(
2104 p_assignment_id in number)
2105 is
2106 --
2107 l_proc varchar2(80) := c_package||'migrate_hi_smr_data';
2108 --
2109 l_ass_info hr_jp_data_migration_pkg.t_ass_hi_smr_rec;
2110 --
2111 begin
2112 --
2113 if g_debug then
2114 hr_utility.set_location(l_proc,0);
2115 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
2116 end if;
2117 --
2118 -- need to reset for retry, qualify_obs_data is not called in retry process.
2119 hr_jp_data_migration_pkg.init_def_hi_smr_data;
2120 --
2121 if g_debug then
2122 hr_utility.set_location(l_proc,10);
2123 hr_utility.trace('hr_jp_data_migration_pkg.g_skip_qualify : '||hr_jp_data_migration_pkg.g_skip_qualify);
2124 hr_utility.trace('hr_jp_data_migration_pkg.g_upd_mode : '||hr_jp_data_migration_pkg.g_upd_mode);
2125 hr_utility.trace('hr_jp_data_migration_pkg.g_mig_date : '||to_char(hr_jp_data_migration_pkg.g_mig_date,'YYYY/MM/DD'));
2126 end if;
2127 --
2128 -- print header for each threads but it is ok because just log.
2129 hr_jp_data_migration_pkg.migrate_hi_smr_hd(
2130 p_assignment_id => p_assignment_id);
2131 --
2132 if g_debug then
2133 hr_utility.set_location(l_proc,20);
2134 end if;
2135 --
2136 l_ass_info := hr_jp_data_migration_pkg.get_ass_info(
2137 p_assignment_id => p_assignment_id,
2138 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2139 --
2140 if g_debug then
2141 hr_utility.set_location(l_proc,30);
2142 hr_utility.trace('l_ass_info.ass_id : '||to_char(l_ass_info.ass_id));
2143 end if;
2144 --
2145 -- for api use
2146 hr_jp_data_migration_pkg.insert_session(
2147 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2148 --
2149 hr_jp_data_migration_pkg.mig_smr_assact(
2150 p_business_group_id => l_ass_info.bg_id,
2151 p_business_group_name => l_ass_info.bg_name,
2152 p_assignment_id => p_assignment_id,
2153 p_assignment_number => l_ass_info.ass_num,
2154 p_session_date => hr_jp_data_migration_pkg.g_mig_date,
2155 p_hi_mr => null);
2156 --
2157 -- delete session is invalid, cause no data found.
2158 --hr_jp_data_migration_pkg.delete_session;
2159 --
2160 -- commit is invalid in archive loop for ORA-01002
2161 -- automatically commit will be executed for each assignment in archive process.
2162 -- commit;
2163 --
2164 if g_debug then
2165 hr_utility.set_location(l_proc,1000);
2166 end if;
2167 --
2168 end migrate_hi_smr_data;
2169 --
2170 -- -------------------------------------------------------------------------
2171 -- init_adj_ann_std_bon
2172 -- -------------------------------------------------------------------------
2173 procedure init_adj_ann_std_bon
2174 is
2175 --
2176 c_proc constant varchar2(61) := c_package||'init_adj_ann_std_bon';
2177 --
2178 begin
2179 --
2180 if g_debug then
2181 hr_utility.set_location('Entering: ' || c_proc, 10);
2182 end if;
2183 --
2184 if g_element_type_ids.count = 0 then
2185 --
2186 if g_debug then
2187 hr_utility.trace('Not cached.');
2188 end if;
2189 --
2190 get_element_type_id('BON_HI_ADJ_INFO', g_element_type_ids);
2191 get_element_type_id('BON_HI_STD_BON', g_element_type_ids);
2192 --
2193 end if;
2194 --
2195 for i in 1..g_element_type_ids.count loop
2196 --
2197 if g_debug then
2198 hr_utility.trace(i || ': ' || g_element_type_ids(i));
2199 end if;
2200 --
2201 end loop;
2202 --
2203 if g_debug then
2204 hr_utility.set_location('Leaving: ' || c_proc, 100);
2205 end if;
2206 --
2207 end init_adj_ann_std_bon;
2208 --
2209 -- -------------------------------------------------------------------------
2210 -- validate_adj_ann_std_bon
2211 -- -------------------------------------------------------------------------
2212 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
2213 procedure validate_adj_ann_std_bon(
2214 p_valid_upgrade out nocopy varchar2)
2215 is
2216 --
2217 c_proc constant varchar2(61) := c_package || 'validate_adj_ann_std_bon';
2218 --
2219 begin
2220 --
2221 if g_debug then
2222 hr_utility.set_location('Entering: ' || c_proc, 10);
2223 end if;
2224 --
2225 init_adj_ann_std_bon;
2226 --
2227 for i in 1..g_element_type_ids.count loop
2228 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
2229 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
2230 --
2231 end loop;
2232 --
2233 if entries_or_results_exist('JP') then
2234 p_valid_upgrade := 'TRUE';
2235 else
2236 p_valid_upgrade := 'FALSE';
2237 end if;
2238 --
2239 if g_debug then
2240 hr_utility.trace(p_valid_upgrade);
2241 hr_utility.set_location('Leaving: ' || c_proc, 100);
2242 end if;
2243 --
2244 end validate_adj_ann_std_bon;
2245 --
2246 -- -------------------------------------------------------------------------
2247 -- qualify_adj_ann_std_bon
2248 -- -------------------------------------------------------------------------
2249 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
2250 procedure qualify_adj_ann_std_bon(
2251 p_assignment_id in number,
2252 p_qualifier out nocopy varchar2)
2253 is
2254 c_proc constant varchar2(61) := c_package || 'qualify_adj_ann_std_bon';
2255 begin
2256 --
2257 if g_debug then
2258 hr_utility.set_location('Entering: ' || c_proc, 10);
2259 end if;
2260 --
2261 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
2262 p_qualifier := 'Y';
2263 else
2264 p_qualifier := 'N';
2265 end if;
2266 --
2267 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
2268 hr_utility.set_location('Leaving: ' || c_proc, 100);
2269 --
2270 end qualify_adj_ann_std_bon;
2271 --
2272 -- -------------------------------------------------------------------------
2273 -- upgrade_adj_ann_std_bon
2274 -- -------------------------------------------------------------------------
2275 procedure upgrade_adj_ann_std_bon(
2276 p_assignment_id in number)
2277 is
2278 c_proc constant varchar2(61) := c_package || 'upgrade_adj_ann_std_bon';
2279 begin
2280 --
2281 if g_debug then
2282 hr_utility.set_location('Entering: ' || c_proc, 10);
2283 end if;
2284 --
2285 init_adj_ann_std_bon;
2286 --
2287 for i in 1..g_element_type_ids.count loop
2288 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
2289 end loop;
2290 --
2291 if g_debug then
2292 hr_utility.set_location('Leaving: ' || c_proc, 100);
2293 end if;
2294 --
2295 end upgrade_adj_ann_std_bon;
2296 --
2297 -- -------------------------------------------------------------------------
2298 -- qualify_dep_data
2299 -- -------------------------------------------------------------------------
2300 -- run by pay_generic_upgrade.action_creation
2301 -- do_qualification run before calling hr_nonrun_asact.insact in create_object_action
2302 procedure qualify_dep_data(
2303 p_assignment_id in number,
2304 p_qualifier out nocopy varchar2)
2305 is
2306 --
2307 l_proc varchar2(80) := c_package||'qualify_dep_data';
2308 --
2309 l_valid_update varchar2(1) := 'N';
2310 --
2311 l_ass_info hr_jp_data_migration_pkg.t_ass_hi_smr_rec;
2312 --
2313 begin
2314 --
2315 if g_debug then
2316 hr_utility.set_location(l_proc,0);
2317 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
2318 end if;
2319 --
2320 hr_jp_data_migration_pkg.init_def_dep_data;
2321 --
2322 if g_debug then
2323 hr_utility.set_location(l_proc,10);
2324 hr_utility.trace('hr_jp_data_migration_pkg.g_skip_qualify : '||hr_jp_data_migration_pkg.g_skip_qualify);
2325 hr_utility.trace('hr_jp_data_migration_pkg.g_upd_mode : '||hr_jp_data_migration_pkg.g_upd_mode);
2326 hr_utility.trace('hr_jp_data_migration_pkg.g_mig_date : '||to_char(hr_jp_data_migration_pkg.g_mig_date,'YYYY/MM/DD'));
2327 end if;
2328 --
2329 if hr_jp_data_migration_pkg.g_skip_qualify = 'N' then
2330 --
2331 if g_debug then
2332 hr_utility.set_location(l_proc,20);
2333 end if;
2334 --
2335 -- print header for each threads but it is ok because just log.
2336 hr_jp_data_migration_pkg.qualify_dep_hd(
2337 p_assignment_id => p_assignment_id);
2338 --
2339 if g_debug then
2340 hr_utility.set_location(l_proc,30);
2341 end if;
2342 --
2343 l_ass_info := hr_jp_data_migration_pkg.get_ass_info(
2344 p_assignment_id => p_assignment_id,
2345 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2346 --
2347 if g_debug then
2348 hr_utility.set_location(l_proc,40);
2349 hr_utility.trace('l_ass_info.ass_id : '||to_char(l_ass_info.ass_id));
2350 end if;
2351 --
2352 hr_jp_data_migration_pkg.val_mig_dep_assact(
2353 p_business_group_id => l_ass_info.bg_id,
2354 p_business_group_name => l_ass_info.bg_name,
2355 p_assignment_id => p_assignment_id,
2356 p_assignment_number => l_ass_info.ass_num,
2357 p_session_date => hr_jp_data_migration_pkg.g_mig_date,
2358 p_valid_update => l_valid_update);
2359 --
2360 if g_debug then
2361 hr_utility.set_location(l_proc,50);
2362 hr_utility.trace('l_valid_update : '||l_valid_update);
2363 end if;
2364 --
2365 p_qualifier := l_valid_update;
2366 --
2367 -- never come here at this moment.
2368 -- notice: include following case in target because skip qualify.
2369 -- 1. future cei dep exists
2370 -- 2. future cei dep oe exists
2371 -- 3. entry already updated (manual update)
2372 -- 4. future entry exists
2373 else
2374 --
2375 p_qualifier := 'Y';
2376 --
2377 if g_debug then
2378 hr_utility.set_location(l_proc,60);
2379 end if;
2380 --
2381 end if;
2382 --
2383 if g_debug then
2384 hr_utility.trace('p_qualifier : '||p_qualifier);
2385 hr_utility.set_location(l_proc,1000);
2386 end if;
2387 --
2388 end qualify_dep_data;
2389 --
2390 -- -------------------------------------------------------------------------
2391 -- migrate_dep_data
2392 -- -------------------------------------------------------------------------
2393 -- run by pay_generic_upgrade.upgrade_data
2394 procedure migrate_dep_data(
2395 p_assignment_id in number)
2396 is
2397 --
2398 l_proc varchar2(80) := c_package||'migrate_dep_data';
2399 --
2400 l_ass_info hr_jp_data_migration_pkg.t_ass_hi_smr_rec;
2401 --
2402 begin
2403 --
2404 if g_debug then
2405 hr_utility.set_location(l_proc,0);
2406 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
2407 end if;
2408 --
2409 -- need to reset for retry, qualify_dep_data is not called in retry process.
2410 hr_jp_data_migration_pkg.init_def_dep_data;
2411 --
2412 if g_debug then
2413 hr_utility.set_location(l_proc,10);
2414 hr_utility.trace('hr_jp_data_migration_pkg.g_skip_qualify : '||hr_jp_data_migration_pkg.g_skip_qualify);
2415 hr_utility.trace('hr_jp_data_migration_pkg.g_upd_mode : '||hr_jp_data_migration_pkg.g_upd_mode);
2416 hr_utility.trace('hr_jp_data_migration_pkg.g_mig_date : '||to_char(hr_jp_data_migration_pkg.g_mig_date,'YYYY/MM/DD'));
2417 end if;
2418 --
2419 -- print header for each threads but it is ok because just log.
2420 hr_jp_data_migration_pkg.migrate_dep_hd(
2421 p_assignment_id => p_assignment_id);
2422 --
2423 if g_debug then
2424 hr_utility.set_location(l_proc,20);
2425 end if;
2426 --
2427 l_ass_info := hr_jp_data_migration_pkg.get_ass_info(
2428 p_assignment_id => p_assignment_id,
2429 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2430 --
2431 if g_debug then
2432 hr_utility.set_location(l_proc,30);
2433 hr_utility.trace('l_ass_info.ass_id : '||to_char(l_ass_info.ass_id));
2434 end if;
2435 --
2436 -- for api use
2437 hr_jp_data_migration_pkg.insert_session(
2438 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2439 --
2440 hr_jp_data_migration_pkg.mig_dep_assact(
2441 p_business_group_id => l_ass_info.bg_id,
2442 p_business_group_name => l_ass_info.bg_name,
2443 p_assignment_id => p_assignment_id,
2444 p_assignment_number => l_ass_info.ass_num,
2445 p_session_date => hr_jp_data_migration_pkg.g_mig_date);
2446 --
2447 -- delete session is invalid, cause no data found.
2448 --hr_jp_data_migration_pkg.delete_session;
2449 --
2450 -- commit is invalid in archive loop for ORA-01002
2451 -- automatically commit will be executed for each assignment in archive process.
2452 -- commit;
2453 --
2454 if g_debug then
2455 hr_utility.set_location(l_proc,1000);
2456 end if;
2457 --
2458 end migrate_dep_data;
2459 --
2460 -- -------------------------------------------------------------------------
2461 -- init_yea_ins_dct
2462 -- -------------------------------------------------------------------------
2463 procedure init_yea_ins_dct
2464 is
2465 c_proc constant varchar2(61) := c_package || 'init_yea_ins_dct';
2466 begin
2467 --
2468 hr_utility.set_location('Entering: ' || c_proc, 10);
2469 --
2470 if g_element_type_ids.count = 0 then
2471 --
2472 hr_utility.trace('Not cached.');
2473 --
2474 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
2475 get_element_type_id('INI_YEA2', g_element_type_ids);
2476 --
2477 end if;
2478 --
2479 for i in 1..g_element_type_ids.count loop
2480 hr_utility.trace(i || ': ' || g_element_type_ids(i));
2481 end loop;
2482 --
2483 hr_utility.set_location('Leaving: ' || c_proc, 100);
2484 --
2485 end init_yea_ins_dct;
2486 --
2487 -- -------------------------------------------------------------------------
2488 -- validate_yea_ins_dct
2489 -- -------------------------------------------------------------------------
2490 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
2491 procedure validate_yea_ins_dct(p_valid_upgrade out nocopy varchar2)
2492 is
2493 --
2494 c_proc constant varchar2(61) := c_package || 'validate_yea_ins_dct';
2495 --
2496 begin
2497 --
2498 hr_utility.set_location('Entering: ' || c_proc, 10);
2499 --
2500 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
2501 --
2502 init_yea_ins_dct;
2503 --
2504 for i in 1..g_element_type_ids.count loop
2505 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
2506 end loop;
2507 --
2508 if entries_or_results_exist(p_legislation_code => 'JP') then
2509 p_valid_upgrade := 'TRUE';
2510 else
2511 p_valid_upgrade := 'FALSE';
2512 end if;
2513 --
2514 hr_utility.trace(p_valid_upgrade);
2515 hr_utility.set_location('Leaving: ' || c_proc, 100);
2516 --
2517 end validate_yea_ins_dct;
2518 --
2519 -- -------------------------------------------------------------------------
2520 -- qualify_yea_ins_dct
2521 -- -------------------------------------------------------------------------
2522 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
2523 procedure qualify_yea_ins_dct(
2524 p_assignment_id in number,
2525 p_qualifier out nocopy varchar2)
2526 is
2527 --
2528 c_proc constant varchar2(61) := c_package || 'qualify_yea_ins_dct';
2529 --
2530 begin
2531 --
2532 hr_utility.set_location('Entering: ' || c_proc, 10);
2533 --
2534 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
2535 p_qualifier := 'Y';
2536 else
2537 p_qualifier := 'N';
2538 end if;
2539 --
2540 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
2541 hr_utility.set_location('Leaving: ' || c_proc, 100);
2542 --
2543 end qualify_yea_ins_dct;
2544 --
2545 -- -------------------------------------------------------------------------
2546 -- upgrade_yea_ins_dct
2547 -- -------------------------------------------------------------------------
2548 procedure upgrade_yea_ins_dct(p_assignment_id in number)
2549 is
2550 --
2551 c_proc constant varchar2(61) := c_package || 'upgrade_yea_ins_dct';
2552 --
2553 begin
2554 --
2555 hr_utility.set_location('Entering: ' || c_proc, 10);
2556 --
2557 init_yea_ins_dct;
2558 --
2559 for i in 1..g_element_type_ids.count loop
2560 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
2561 end loop;
2562 --
2563 hr_utility.set_location('Leaving: ' || c_proc, 100);
2564 --
2565 end upgrade_yea_ins_dct;
2566 --
2567 -- |-------------------------------------------------------------------|
2568 -- |--------------------< init_yea_ins_prem_2012 >--------------------|
2569 -- |-------------------------------------------------------------------|
2570 procedure init_yea_ins_prem_2012
2571 is
2572 c_proc constant varchar2(61) := c_package || 'init_yea_ins_prem_2012';
2573 begin
2574 hr_utility.set_location('Entering: ' || c_proc, 10);
2575 --
2576 if g_element_type_ids.count = 0 then
2577 hr_utility.trace('Not cached.');
2578 --
2579 get_element_type_id('YEA_INS_PREM_EXM_DECLARE_INFO', g_element_type_ids);
2580 end if;
2581 --
2582 for i in 1..g_element_type_ids.count loop
2583 hr_utility.trace(i || ': ' || g_element_type_ids(i));
2584 end loop;
2585 --
2586 hr_utility.set_location('Leaving: ' || c_proc, 100);
2587 end init_yea_ins_prem_2012;
2588 -- |-------------------------------------------------------------------|
2589 -- |------------------< validate_yea_ins_prem_2012 >------------------|
2590 -- |-------------------------------------------------------------------|
2591 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
2592 procedure validate_yea_ins_prem_2012(p_valid_upgrade out nocopy varchar2)
2593 is
2594 c_proc constant varchar2(61) := c_package || 'validate_yea_ins_prem_2012';
2595 begin
2596 hr_utility.set_location('Entering: ' || c_proc, 10);
2597 --
2598 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
2599 --
2600 init_yea_ins_prem_2012;
2601 --
2602 for i in 1..g_element_type_ids.count loop
2603 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
2604 end loop;
2605 --
2606 if entries_or_results_exist(p_legislation_code => 'JP') then
2607 p_valid_upgrade := 'TRUE';
2608 else
2609 p_valid_upgrade := 'FALSE';
2610 end if;
2611 --
2612 hr_utility.trace(p_valid_upgrade);
2613 hr_utility.set_location('Leaving: ' || c_proc, 100);
2614 end validate_yea_ins_prem_2012;
2615 -- |-------------------------------------------------------------------|
2616 -- |------------------< qualify_yea_ins_prem_2012 >-------------------|
2617 -- |-------------------------------------------------------------------|
2618 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
2619 procedure qualify_yea_ins_prem_2012(
2620 p_assignment_id in number,
2621 p_qualifier out nocopy varchar2)
2622 is
2623 c_proc constant varchar2(61) := c_package || 'qualify_yea_ins_prem_2012';
2624 begin
2625 hr_utility.set_location('Entering: ' || c_proc, 10);
2626 --
2627 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
2628 p_qualifier := 'Y';
2629 else
2630 p_qualifier := 'N';
2631 end if;
2632 --
2633 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
2634 hr_utility.set_location('Leaving: ' || c_proc, 100);
2635 end qualify_yea_ins_prem_2012;
2636 -- |-------------------------------------------------------------------|
2637 -- |------------------< upgrade_yea_ins_prem_2012 >-------------------|
2638 -- |-------------------------------------------------------------------|
2639 procedure upgrade_yea_ins_prem_2012(p_assignment_id in number)
2640 is
2641 c_proc constant varchar2(61) := c_package || 'upgrade_yea_ins_prem_2012';
2642 begin
2643 hr_utility.set_location('Entering: ' || c_proc, 10);
2644 --
2645 init_yea_ins_prem_2012;
2646 --
2647 for i in 1..g_element_type_ids.count loop
2648 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
2649 end loop;
2650 --
2651 hr_utility.set_location('Leaving: ' || c_proc, 100);
2652 end upgrade_yea_ins_prem_2012;
2653
2654 -- |-------------------------------------------------------------------|
2655 -- |--------------------< init_net_annual_tax_2013 >--------------------|
2656 -- |-------------------------------------------------------------------|
2657 procedure init_net_annual_tax_2013
2658 is
2659 c_proc constant varchar2(61) := c_package || 'init_net_annual_tax_2013';
2660 begin
2661 hr_utility.set_location('Entering: ' || c_proc, 10);
2662 --
2663 if g_element_type_ids.count = 0 then
2664 hr_utility.trace('Not cached.');
2665 --
2666 get_element_type_id('YEA_NET_ANNUAL_TAX', g_element_type_ids);
2667 end if;
2668 --
2669 for i in 1..g_element_type_ids.count loop
2670 hr_utility.trace(i || ': ' || g_element_type_ids(i));
2671 end loop;
2672 --
2673 hr_utility.set_location('Leaving: ' || c_proc, 100);
2674 end init_net_annual_tax_2013;
2675 -- |-------------------------------------------------------------------|
2676 -- |------------------< validate_net_annual_tax_2013 >------------------|
2677 -- |-------------------------------------------------------------------|
2678 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
2679 procedure validate_net_annual_tax_2013(p_valid_upgrade out nocopy varchar2)
2680 is
2681 c_proc constant varchar2(61) := c_package || 'validate_net_annual_tax_2013';
2682 begin
2683 hr_utility.set_location('Entering: ' || c_proc, 10);
2684 --
2685 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
2686 --
2687 init_net_annual_tax_2013;
2688 --
2689 for i in 1..g_element_type_ids.count loop
2690 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
2691 end loop;
2692 --
2693 if entries_or_results_exist(p_legislation_code => 'JP') then
2694 p_valid_upgrade := 'TRUE';
2695 else
2696 p_valid_upgrade := 'FALSE';
2697 end if;
2698 --
2699 hr_utility.trace(p_valid_upgrade);
2700 hr_utility.set_location('Leaving: ' || c_proc, 100);
2701 end validate_net_annual_tax_2013;
2702 -- |-------------------------------------------------------------------|
2703 -- |------------------< qualify_net_annual_tax_2013 >-------------------|
2704 -- |-------------------------------------------------------------------|
2705 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
2706 procedure qualify_net_annual_tax_2013(
2707 p_assignment_id in number,
2708 p_qualifier out nocopy varchar2)
2709 is
2710 c_proc constant varchar2(61) := c_package || 'qualify_net_annual_tax_2013';
2711 begin
2712 hr_utility.set_location('Entering: ' || c_proc, 10);
2713 --
2714 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
2715 p_qualifier := 'Y';
2716 else
2717 p_qualifier := 'N';
2718 end if;
2719 --
2720 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
2721 hr_utility.set_location('Leaving: ' || c_proc, 100);
2722 end qualify_net_annual_tax_2013;
2723 -- |-------------------------------------------------------------------|
2724 -- |------------------< upgrade_net_annual_tax_2013 >-------------------|
2725 -- |-------------------------------------------------------------------|
2726 procedure upgrade_net_annual_tax_2013(p_assignment_id in number)
2727 is
2728 c_proc constant varchar2(61) := c_package || 'upgrade_net_annual_tax_2013';
2729 begin
2730 hr_utility.set_location('Entering: ' || c_proc, 10);
2731 --
2732 init_net_annual_tax_2013;
2733 --
2734 for i in 1..g_element_type_ids.count loop
2735 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
2736 end loop;
2737 --
2738 hr_utility.set_location('Leaving: ' || c_proc, 100);
2739 end upgrade_net_annual_tax_2013;
2740
2741 ----
2742 -- |-------------------------------------------------------------------|
2743 -- |--------------------< init_spc_exec_2013 >--------------------|
2744 -- |-------------------------------------------------------------------|
2745 procedure init_spc_exec_2013
2746 is
2747 c_proc constant varchar2(61) := c_package || 'init_spc_exec_2013';
2748 begin
2749 hr_utility.set_location('Entering: ' || c_proc, 10);
2750 --
2751 if g_element_type_ids.count = 0 then
2752 hr_utility.trace('Not cached.');
2753 --
2754 get_element_type_id('TRM_INCOME_DCT', g_element_type_ids);
2755 end if;
2756 --
2757 for i in 1..g_element_type_ids.count loop
2758 hr_utility.trace(i || ': ' || g_element_type_ids(i));
2759 end loop;
2760 --
2761 hr_utility.set_location('Leaving: ' || c_proc, 100);
2762 end init_spc_exec_2013;
2763 -- |-------------------------------------------------------------------|
2764 -- |------------------< validate_spc_exec_2013 >------------------|
2765 -- |-------------------------------------------------------------------|
2766 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
2767 procedure validate_spc_exec_2013(p_valid_upgrade out nocopy varchar2)
2768 is
2769 c_proc constant varchar2(61) := c_package || 'validate_spc_exec_2013';
2770 begin
2771 hr_utility.set_location('Entering: ' || c_proc, 10);
2772 --
2773 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
2774 --
2775 init_spc_exec_2013;
2776 --
2777 for i in 1..g_element_type_ids.count loop
2778 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
2779 end loop;
2780 --
2781 if entries_or_results_exist(p_legislation_code => 'JP') then
2782 p_valid_upgrade := 'TRUE';
2783 else
2784 p_valid_upgrade := 'FALSE';
2785 end if;
2786 --
2787 hr_utility.trace(p_valid_upgrade);
2788 hr_utility.set_location('Leaving: ' || c_proc, 100);
2789 end validate_spc_exec_2013;
2790 -- |-------------------------------------------------------------------|
2791 -- |------------------< qualify_spc_exec_2013 >-------------------|
2792 -- |-------------------------------------------------------------------|
2793 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
2794 procedure qualify_spc_exec_2013(
2795 p_assignment_id in number,
2796 p_qualifier out nocopy varchar2)
2797 is
2798 c_proc constant varchar2(61) := c_package || 'qualify_spc_exec_2013';
2799 begin
2800 hr_utility.set_location('Entering: ' || c_proc, 10);
2801 --
2802 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
2803 p_qualifier := 'Y';
2804 else
2805 p_qualifier := 'N';
2806 end if;
2807 --
2808 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
2809 hr_utility.set_location('Leaving: ' || c_proc, 100);
2810 end qualify_spc_exec_2013;
2811 -- |-------------------------------------------------------------------|
2812 -- |------------------< upgrade_spc_exec_2013 >-------------------|
2813 -- |-------------------------------------------------------------------|
2814 procedure upgrade_spc_exec_2013(p_assignment_id in number)
2815 is
2816 c_proc constant varchar2(61) := c_package || 'upgrade_spc_exec_2013';
2817 begin
2818 hr_utility.set_location('Entering: ' || c_proc, 10);
2819 --
2820 init_spc_exec_2013;
2821 --
2822 for i in 1..g_element_type_ids.count loop
2823 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
2824 end loop;
2825 --
2826 hr_utility.set_location('Leaving: ' || c_proc, 100);
2827 end upgrade_spc_exec_2013;
2828
2829 /*
2830 -- |-------------------------------------------------------------------|
2831 -- |------------------------< submit_request >-------------------------|
2832 -- |-------------------------------------------------------------------|
2833 function submit_request(
2834 p_legislation_code in varchar2,
2835 p_upgrade_short_name in varchar2,
2836 p_validate_procedure in varchar2,
2837 p_application_short_name in varchar2,
2838 p_concurrent_program_name in varchar2) return number
2839 is
2840 l_dummy varchar2(30);
2841 l_business_group_id number;
2842 l_valid_request boolean;
2843 l_request_id number;
2844 l_phase varchar2(255);
2845 l_status varchar2(255);
2846 l_dev_phase varchar2(255);
2847 l_dev_status varchar2(255);
2848 l_message varchar2(255);
2849 l_valid_upgrade varchar2(10) := 'TRUE';
2850 --
2851 cursor csr_upgrade_def is
2852 select upgrade_definition_id,
2853 upgrade_method,
2854 upgrade_level,
2855 legislation_code,
2856 legislatively_enabled
2857 from pay_upgrade_definitions
2858 where short_name = p_upgrade_short_name;
2859 l_upgrade_def csr_upgrade_def%rowtype;
2860 --
2861 cursor csr_upgrade_leg(p_upgrade_definition_id number) is
2862 select 'Y'
2863 from pay_upgrade_legislations
2864 where upgrade_definition_id = p_upgrade_definition_id
2865 and legislation_code = p_legislation_code;
2866 --
2867 cursor csr_upgrade_status(p_upgrade_definition_id number) is
2868 select status
2869 from pay_upgrade_status
2870 where upgrade_definition_id = p_upgrade_definition_id
2871 and business_group_id is null
2872 and legislation_code = p_legislation_code;
2873 --
2874 -- Do not use per_business_groups_perf which does not return
2875 -- "Disabled" business groups.
2876 --
2877 cursor csr_bg is
2878 select organization_id
2879 from hr_organization_information
2880 where org_information_context = 'Business Group Information'
2881 and org_information9 = p_legislation_code
2882 and rownum <= 1;
2883 --
2884 cursor csr_user_resp is
2885 select g.user_id,
2886 g.responsibility_id,
2887 g.responsibility_application_id
2888 from fnd_responsibility r,
2889 fnd_user_resp_groups g,
2890 fnd_user u
2891 where u.user_name = 'SYSADMIN'
2892 and g.user_id = u.user_id
2893 and g.security_group_id = 0
2894 and r.application_id = g.responsibility_application_id
2895 and r.responsibility_id = g.responsibility_id
2896 and r.responsibility_key = 'SYSTEM_ADMINISTRATOR';
2897 --
2898 procedure raise_error(
2899 p_message in varchar2,
2900 p_token in varchar2 default null)
2901 is
2902 begin
2903 if p_token is null then
2904 raise_application_error(-20001, p_message || ': ' || p_upgrade_short_name);
2905 else
2906 raise_application_error(-20001, p_message || ': ' || p_upgrade_short_name || ', ' || p_token);
2907 end if;
2908 end raise_error;
2909 --
2910 procedure set_upgrade_completed
2911 is
2912 begin
2913 pay_generic_upgrade.set_upgrade_status(
2914 p_upg_def_id => l_upgrade_def.upgrade_definition_id,
2915 p_upg_lvl => 'L',
2916 p_bus_grp => null,
2917 p_leg_code => p_legislation_code,
2918 p_status => 'P');
2919 pay_generic_upgrade.set_upgrade_status(
2920 p_upg_def_id => l_upgrade_def.upgrade_definition_id,
2921 p_upg_lvl => 'L',
2922 p_bus_grp => null,
2923 p_leg_code => p_legislation_code,
2924 p_status => 'C');
2925 end set_upgrade_completed;
2926 begin
2927 --
2928 -- Validate Input Parameters
2929 --
2930 open csr_upgrade_def;
2931 fetch csr_upgrade_def into l_upgrade_def;
2932 if csr_upgrade_def%notfound then
2933 close csr_upgrade_def;
2934 raise_error('Specified Upgrade Definition not found');
2935 end if;
2936 close csr_upgrade_def;
2937 --
2938 if l_upgrade_def.upgrade_method <> 'PYUGEN' then
2939 raise_error('Only Upgrade Method "PYUGEN" is supported', l_upgrade_def.upgrade_method);
2940 end if;
2941 --
2942 if l_upgrade_def.upgrade_level <> 'L' then
2943 raise_error('Only Upgrade Level "L" is supported', l_upgrade_def.upgrade_level);
2944 end if;
2945 --
2946 if l_upgrade_def.legislation_code is not null then
2947 if l_upgrade_def.legislation_code <> p_legislation_code then
2948 raise_error('Inconsistent legislation', l_upgrade_def.legislation_code);
2949 end if;
2950 else
2951 if l_upgrade_def.legislatively_enabled = 'Y' then
2952 open csr_upgrade_leg(l_upgrade_def.upgrade_definition_id);
2953 fetch csr_upgrade_leg into l_dummy;
2954 if csr_upgrade_leg%notfound then
2955 close csr_upgrade_leg;
2956 raise_error('Upgrade Definition is not legislatively available', p_legislation_code);
2957 end if;
2958 close csr_upgrade_leg;
2959 end if;
2960 end if;
2961 --
2962 -- Make sure the data update has not been performed already.
2963 -- pay_core_utils.get_upgrade_status cannot be used for
2964 -- legislative upgrade which requires business_group_id as mandatory parameter.
2965 -- There's possibility that business groups within "p_legislation_code" are not created yet at this point.
2966 --
2967 open csr_upgrade_status(l_upgrade_def.upgrade_definition_id);
2968 fetch csr_upgrade_status into l_dummy;
2969 if csr_upgrade_status%notfound then
2970 --
2971 -- To run concurrent program "Generic Upgrade Mechanism" at legislation level,
2972 -- it is required to derive business_group_id of one of business groups within "p_legislation_code".
2973 --
2974 open csr_bg;
2975 fetch csr_bg into l_business_group_id;
2976 --
2977 -- When no business group for specified legislation is available,
2978 -- stamp the legislation upgrade as "Completed".
2979 -- Note to stamp "Processing", then "Completed" to suppress error.
2980 --
2981 if csr_bg%notfound then
2982 set_upgrade_completed;
2983 else
2984 --
2985 -- Check pending or running concurrent program exists in FND_CONCURRENT_REQUESTS.
2986 --
2987 l_valid_request := fnd_concurrent.get_request_status(
2988 request_id => l_request_id,
2989 appl_shortname => p_application_short_name,
2990 program => p_concurrent_program_name,
2991 phase => l_phase,
2992 status => l_status,
2993 dev_phase => l_dev_phase,
2994 dev_status => l_dev_status,
2995 message => l_message);
2996 --
2997 -- When no request or no pending/running request found,
2998 -- submit request.
2999 --
3000 l_request_id := null;
3001 if (not l_valid_request) or (l_valid_request and l_dev_phase not in ('PENDING', 'RUNNING')) then
3002 --
3003 -- Execute validate procedure only when set.
3004 --
3005 if p_validate_procedure is not null then
3006 execute immediate 'begin ' || p_validate_procedure || '(:a); end;' using out l_valid_upgrade;
3007 end if;
3008 --
3009 if l_valid_upgrade = 'TRUE' then
3010 for l_rec in csr_user_resp loop
3011 fnd_global.apps_initialize(
3012 user_id => l_rec.user_id,
3013 resp_id => l_rec.responsibility_id,
3014 resp_appl_id => l_rec.responsibility_application_id);
3015 end loop;
3016 --
3017 l_request_id := fnd_request.submit_request (
3018 application => p_application_short_name,
3019 program => p_concurrent_program_name,
3020 argument1 => 'ARCHIVE', -- Process Name
3021 argument2 => 'GENERIC_UPGRADE', -- Report Type
3022 argument3 => 'DEFAULT', -- Rpt Qual
3023 argument4 => null, -- Start Date
3024 argument5 => null, -- End Date
3025 argument6 => 'PROCESS', -- Rpt Category
3026 argument7 => fnd_number.number_to_canonical(l_business_group_id), -- Business Grp
3027 argument8 => null, -- Mag File Nme
3028 argument9 => null, -- Rep File Nme
3029 argument10 => fnd_number.number_to_canonical(l_upgrade_def.upgrade_definition_id), -- ID
3030 argument11 => p_upgrade_short_name, -- Short Name
3031 argument12 => 'UPG_DEF_NAME=' || p_upgrade_short_name); -- Upgrade Name
3032 if l_request_id = 0 then
3033 hr_utility.raise_error;
3034 end if;
3035 else
3036 set_upgrade_completed;
3037 end if;
3038 end if;
3039 end if;
3040 close csr_bg;
3041 end if;
3042 close csr_upgrade_status;
3043 --
3044 return l_request_id;
3045 end submit_request;
3046 */
3047 --
3048 end pay_jp_generic_upgrade_pkg;