[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.9.12000000.6 2007/08/08 04:27:47 keyazawa noship $ */
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 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 hr_jp_standard_pkg.to_hankaku(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 /*+ ORDERED
859 USE_NL(PEL PLIV PIV)
860 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N50)
861 INDEX(PEL PAY_ELEMENT_LINKS_F_PK)
862 INDEX(PLIV PAY_LINK_INPUT_VALUES_F_U50)
863 INDEX(PIV PAY_INPUT_VALUES_F_PK) */
864 pay_element_entry_values_s.nextval,
865 pee.effective_start_date,
866 pee.effective_end_date,
867 pliv.input_value_id,
868 pee.element_entry_id,
869 decode(piv.hot_default_flag, 'Y', null, nvl(pliv.default_value, piv.default_value))
870 from pay_element_entries_f pee,
871 pay_element_links_f pel,
872 pay_link_input_values_f pliv,
873 pay_input_values_f piv
874 where pee.assignment_id = p_assignment_id
875 and pel.element_link_id = pee.element_link_id
876 and pee.effective_start_date
877 between pel.effective_start_date and pel.effective_end_date
878 and pel.element_type_id = p_element_type_id
879 and pliv.element_link_id = pel.element_link_id
880 and pee.effective_start_date
881 between pliv.effective_start_date and pliv.effective_end_date
882 and piv.input_value_id = pliv.input_value_id
883 and pee.effective_start_date
884 between piv.effective_start_date and piv.effective_end_date
885 and not exists(
886 /* If required, add hint in the following sql. */
887 select null
888 from pay_element_entry_values_f peev
889 where peev.element_entry_id = pee.element_entry_id
890 and peev.effective_start_date = pee.effective_start_date
891 and peev.effective_end_date = pee.effective_end_date
892 and peev.input_value_id = piv.input_value_id);
893 --
894 hr_utility.trace('assignment_id : ' || p_assignment_id);
895 hr_utility.trace('element_type_id : ' || p_element_type_id);
896 hr_utility.trace(sql%rowcount || ' rows inserted into pay_element_entry_values_f');
897 --
898 -- Check RR_SPARSE legislation rule
899 --
900 pay_core_utils.get_legislation_rule(
901 'RR_SPARSE',
902 'JP',
903 l_rule_mode,
904 l_found);
905 --
906 -- Check ENABLE_RR_SPARSE generic upgrade
907 --
908 if l_rule_mode = 'Y' then
909 select distinct
910 business_group_id
911 into l_business_group_id
912 from per_all_assignments_f
913 where assignment_id = p_assignment_id;
914 --
915 l_rule_mode := pay_core_utils.get_upgrade_status(l_business_group_id, 'ENABLE_RR_SPARSE');
916 end if;
917 -- When RR_SPARSE, create "null" result values only
918 -- for 'E'(Entry) or 'R'(Reversal) run results.
919 -- No need to create for 'I'(Indirect) and 'V'(Reversed Indirect).
920 --
921 if l_rule_mode = 'Y' then
922 null;
923 --
924 -- Bug.4360429
925 -- No "null" result values need to be created when sparse matrix is enabled.
926 --
927 -- insert into pay_run_result_values(
928 -- INPUT_VALUE_ID,
929 -- RUN_RESULT_ID,
930 -- RESULT_VALUE)
931 -- select /*+ ORDERED
932 -- USE_NL(PRR PPA PIV)
933 -- INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N1)
934 -- INDEX(PRR PAY_RUN_RESULTS_N50)
935 -- INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
936 -- INDEX(PIV PAY_INPUT_VALUES_F_N50) */
937 -- piv.input_value_id,
938 -- prr.run_result_id,
939 -- null
940 -- from pay_assignment_actions paa,
941 -- pay_run_results prr,
942 -- pay_payroll_actions ppa,
943 -- pay_input_values_f piv
944 -- where paa.assignment_id = p_assignment_id
945 -- and prr.assignment_action_id = paa.assignment_action_id
946 -- and prr.element_type_id = p_element_type_id
947 -- and prr.source_type in ('E', 'R')
948 -- and ppa.payroll_action_id = paa.payroll_action_id
949 -- and piv.element_type_id = prr.element_type_id
950 -- and ppa.effective_date
951 -- between piv.effective_start_date and piv.effective_end_date
952 -- and not exists(
953 -- /* If required, add hint in the following sql. */
954 -- select null
955 -- from pay_run_result_values prrv
956 -- where prrv.run_result_id = prr.run_result_id
957 -- and prrv.input_value_id = piv.input_value_id);
958 -- --
959 -- hr_utility.trace(sql%rowcount || ' rows inserted into pay_run_result_values (RR_SPARSE)');
960 --
961 -- When not RR_SPARSE, create "null" result values for all appropriate run results.
962 --
963 else
964 /* Removed ORDERED and USE_NL hints to
965 fix Bug 5232799 */
966 insert into pay_run_result_values(
967 INPUT_VALUE_ID,
968 RUN_RESULT_ID,
969 RESULT_VALUE)
970 select /*+ INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
971 INDEX(PRR PAY_RUN_RESULTS_N50)
972 INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
973 INDEX(PIV PAY_INPUT_VALUES_F_N50) */
974 piv.input_value_id,
975 prr.run_result_id,
976 null
977 from pay_assignment_actions paa,
978 pay_run_results prr,
979 pay_payroll_actions ppa,
980 pay_input_values_f piv
981 where paa.assignment_id = p_assignment_id
982 and prr.assignment_action_id = paa.assignment_action_id
983 and prr.element_type_id = p_element_type_id
984 and ppa.payroll_action_id = paa.payroll_action_id
985 and piv.element_type_id = prr.element_type_id
986 and ppa.effective_date
987 between piv.effective_start_date and piv.effective_end_date
988 and not exists(
989 /* If required, add hint in the following sql. */
990 select null
991 from pay_run_result_values prrv
992 where prrv.run_result_id = prr.run_result_id
993 and prrv.input_value_id = piv.input_value_id);
994 --
995 hr_utility.trace(sql%rowcount || ' rows inserted into pay_run_result_values');
996 end if;
997 end if;
998 --
999 hr_utility.set_location('Leaving: ' || c_proc, 100);
1000 end sync_entries_and_results;
1001 -- |-------------------------------------------------------------------|
1002 -- |----------------------< get_element_type_id >----------------------|
1003 -- |-------------------------------------------------------------------|
1004 procedure get_element_type_id(
1005 p_element_name in varchar2,
1006 p_element_type_ids in out nocopy t_numbers)
1007 is
1008 l_element_type_id number;
1009 --
1010 cursor csr_id is
1011 select distinct
1012 element_type_id
1013 from pay_element_types_f
1014 where element_name = p_element_name
1015 and business_group_id is null
1016 and legislation_code = 'JP';
1017 begin
1018 open csr_id;
1019 fetch csr_id into l_element_type_id;
1020 if csr_id%found then
1021 hr_utility.trace(p_element_name || ': ' || l_element_type_id);
1022 p_element_type_ids(p_element_type_ids.count + 1) := l_element_type_id;
1023 else
1024 hr_utility.trace(p_element_name || ' NOT found!');
1025 end if;
1026 close csr_id;
1027 end get_element_type_id;
1028 -- |-------------------------------------------------------------------|
1029 -- |--------------------------< init_pay_a >---------------------------|
1030 -- |-------------------------------------------------------------------|
1031 procedure init_pay_a
1032 is
1033 c_proc constant varchar2(61) := c_package || 'init_pay_a';
1034 begin
1035 hr_utility.set_location('Entering: ' || c_proc, 10);
1036 --
1037 if g_element_type_ids.count = 0 then
1038 hr_utility.trace('Not cached.');
1039 --
1040 get_element_type_id('INI_SAL2', g_element_type_ids);
1041 get_element_type_id('INI_BON2', g_element_type_ids);
1042 get_element_type_id('INI_SPB1', g_element_type_ids);
1043 get_element_type_id('INI_YEA2', g_element_type_ids);
1044 get_element_type_id('COM_ITX_INFO', g_element_type_ids);
1045 get_element_type_id('SAL_HI_PREM_PROC', g_element_type_ids);
1046 get_element_type_id('SAL_WP_PREM_PROC', g_element_type_ids);
1047 get_element_type_id('SAL_ITX', g_element_type_ids);
1048 get_element_type_id('BON_HI_PREM_PROC', g_element_type_ids);
1049 get_element_type_id('BON_WP_PREM_PROC', g_element_type_ids);
1050 get_element_type_id('BON_ITX', g_element_type_ids);
1051 get_element_type_id('SPB_ITX', g_element_type_ids);
1052 get_element_type_id('YEA_PREV_EMP_INFO', g_element_type_ids);
1053 get_element_type_id('YEA_ADJ_INFO', g_element_type_ids);
1054 get_element_type_id('YEA_AMT_AFTER_EMP_INCOME_DCT_RSLT', g_element_type_ids);
1055 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
1056 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_RSLT', g_element_type_ids);
1057 get_element_type_id('YEA_HOUSING_LOAN_TAX_CREDIT', g_element_type_ids);
1058 get_element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', g_element_type_ids);
1059 get_element_type_id('GEP_ADJ_INFO', g_element_type_ids);
1060 get_element_type_id('SAN_ADJ_INFO', g_element_type_ids);
1061 get_element_type_id('SAN_REPORT_RMKS_RSLT', g_element_type_ids);
1062 end if;
1063 --
1064 -- for i in 1..g_element_type_ids.count loop
1065 -- hr_utility.trace(i || ': ' || g_element_type_ids(i));
1066 -- end loop;
1067 --
1068 hr_utility.set_location('Leaving: ' || c_proc, 100);
1069 end init_pay_a;
1070 -- |-------------------------------------------------------------------|
1071 -- |------------------------< validate_pay_a >-------------------------|
1072 -- |-------------------------------------------------------------------|
1073 procedure validate_pay_a(p_valid_upgrade out nocopy varchar2)
1074 is
1075 c_proc constant varchar2(61) := c_package || 'validate_pay_a';
1076 --
1077 l_element_type_id1 number;
1078 l_element_type_id2 number;
1079 l_element_type_id3 number;
1080 begin
1081 hr_utility.set_location('Entering: ' || c_proc, 10);
1082 --
1083 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
1084 --
1085 init_pay_a;
1086 --
1087 for i in 1..g_element_type_ids.count loop
1088 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
1089 end loop;
1090 --
1091 if entries_or_results_exist(p_legislation_code => 'JP') then
1092 p_valid_upgrade := 'TRUE';
1093 else
1094 p_valid_upgrade := 'FALSE';
1095 end if;
1096 --
1097 hr_utility.trace(p_valid_upgrade);
1098 hr_utility.set_location('Leaving: ' || c_proc, 100);
1099 end validate_pay_a;
1100 -- |-------------------------------------------------------------------|
1101 -- |-------------------------< qualify_pay_a >-------------------------|
1102 -- |-------------------------------------------------------------------|
1103 procedure qualify_pay_a(
1104 p_assignment_id in number,
1105 p_qualifier out nocopy varchar2)
1106 is
1107 c_proc constant varchar2(61) := c_package || 'qualify_pay_a';
1108 begin
1109 hr_utility.set_location('Entering: ' || c_proc, 10);
1110 --
1111 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
1112 p_qualifier := 'Y';
1113 else
1114 p_qualifier := 'N';
1115 end if;
1116 --
1117 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1118 hr_utility.set_location('Leaving: ' || c_proc, 100);
1119 end qualify_pay_a;
1120 -- |-------------------------------------------------------------------|
1121 -- |----------------------< upgrade_pay_a >----------------------------|
1122 -- |-------------------------------------------------------------------|
1123 procedure upgrade_pay_a(p_assignment_id in number)
1124 is
1125 c_proc constant varchar2(61) := c_package || 'upgrade_pay_a';
1126 begin
1127 hr_utility.set_location('Entering: ' || c_proc, 10);
1128 --
1129 init_pay_a;
1130 --
1131 for i in 1..g_element_type_ids.count loop
1132 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
1133 end loop;
1134 --
1135 hr_utility.set_location('Leaving: ' || c_proc, 100);
1136 end upgrade_pay_a;
1137 -- |-------------------------------------------------------------------|
1138 -- |-------------------< validate_itax_description >-------------------|
1139 -- |-------------------------------------------------------------------|
1140 procedure validate_itax_description(
1141 p_valid_upgrade out nocopy varchar2
1142 )
1143 is
1144 --
1145 c_proc constant varchar2(61) := c_package || 'validate_itax_description';
1146 --
1147 begin
1148 --
1149 hr_utility.set_location('Entering: ' || c_proc, 10);
1150 --
1151 --
1152 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
1153 --
1154 if g_element_type_ids.count = 0 then
1155 --
1156 get_element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', g_element_type_ids);
1157 --
1158 end if;
1159 --
1160 for i in 1..g_element_type_ids.count loop
1161 --
1162 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
1163 --
1164 end loop;
1165 --
1166 if entries_or_results_exist(p_legislation_code => 'JP') then
1167 p_valid_upgrade := 'TRUE';
1168 else
1169 p_valid_upgrade := 'FALSE';
1170 end if;
1171 --
1172 hr_utility.trace(p_valid_upgrade);
1173 hr_utility.set_location('Leaving: ' || c_proc, 100);
1174 --
1175 end validate_itax_description;
1176 -- |-------------------------------------------------------------------|
1177 -- |-------------------< qualify_itax_description >--------------------|
1178 -- |-------------------------------------------------------------------|
1179 procedure qualify_itax_description(
1180 p_assignment_id in number,
1181 p_qualifier out nocopy varchar2
1182 )
1183 is
1184 c_proc constant varchar2(61) := c_package || 'qualify_itax_description';
1185 begin
1186 hr_utility.set_location('Entering: ' || c_proc, 10);
1187 --
1188 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
1189 p_qualifier := 'Y';
1190 else
1191 p_qualifier := 'N';
1192 end if;
1193 --
1194 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1195 hr_utility.set_location('Leaving: ' || c_proc, 100);
1196 end qualify_itax_description;
1197 -- |-------------------------------------------------------------------|
1198 -- |-------------------< upgrade_itax_description >--------------------|
1199 -- |-------------------------------------------------------------------|
1200 procedure upgrade_itax_description(
1201 p_assignment_id in number
1202 )
1203 is
1204 c_proc constant varchar2(61) := c_package || 'upgrade_itax_description';
1205 begin
1206 --
1207 hr_utility.set_location('Entering: ' || c_proc, 10);
1208 --
1209 if g_element_type_ids.count = 0 then
1210 --
1211 get_element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', g_element_type_ids);
1212 --
1213 end if;
1214 --
1215 for i in 1..g_element_type_ids.count loop
1216 --
1217 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
1218 --
1219 end loop;
1220 --
1221 hr_utility.set_location('Leaving: ' || c_proc, 100);
1222 --
1223 end upgrade_itax_description;
1224 -- |-------------------------------------------------------------------|
1225 -- |--------------------< validate_code_jp_pre_tax >-------------------|
1226 -- |-------------------------------------------------------------------|
1227 procedure validate_code_jp_pre_tax(p_valid_upgrade out nocopy varchar2)
1228 is
1229 c_proc constant varchar2(61) := c_package || 'validate_code_jp_pre_tax';
1230 --
1231 cursor csr_exists is
1232 select 'TRUE'
1233 from pay_jp_pre_tax_old ppt,
1234 pay_assignment_actions paa
1235 where paa.action_status = 'C'
1236 and ppt.assignment_action_id = paa.assignment_action_id
1237 and ppt.assignment_action_id NOT IN (
1238 select action_information1
1239 from pay_action_information
1240 where action_information_category = 'JP_PRE_TAX_1'
1241 and action_context_type = 'AAP')
1242 and rownum =1;
1243
1244 begin
1245 hr_utility.set_location('Entering: ' || c_proc, 10);
1246 --
1247 open csr_exists;
1248 fetch csr_exists into p_valid_upgrade;
1249 if csr_exists%notfound then
1250 p_valid_upgrade := 'FALSE';
1251 end if;
1252 close csr_exists;
1253 --
1254 hr_utility.trace(p_valid_upgrade);
1255 hr_utility.set_location('Leaving: ' || c_proc, 100);
1256 end validate_code_jp_pre_tax;
1257 -- |-------------------------------------------------------------------|
1258 -- |--------------------< qualifying_jp_pre_tax >----------------------|
1259 -- |-------------------------------------------------------------------|
1260 procedure qualifying_jp_pre_tax(
1261 p_assignment_id in number,
1262 p_qualifier out nocopy varchar2)
1263 is
1264 c_proc constant varchar2(61) := c_package || 'qualifying_jp_pre_tax';
1265 --
1266 cursor csr_ppt is
1267 select 'Y'
1268 from pay_jp_pre_tax_old ppt,
1269 pay_assignment_actions paa
1270 where paa.assignment_id = p_assignment_id
1271 and paa.action_status = 'C'
1272 and ppt.assignment_action_id = paa.assignment_action_id
1273 and ppt.assignment_action_id NOT IN (
1274 select action_information1
1275 from pay_action_information
1276 where action_information_category = 'JP_PRE_TAX_1'
1277 and action_context_type = 'AAP')
1278 and rownum = 1;
1279
1280 begin
1281 hr_utility.set_location('Entering: ' || c_proc, 10);
1282 --
1283 open csr_ppt;
1284 fetch csr_ppt into p_qualifier;
1285 if csr_ppt%notfound then
1286 p_qualifier := 'N';
1287 end if;
1288 close csr_ppt;
1289 --
1290 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1291 hr_utility.set_location('Leaving: ' || c_proc, 100);
1292 end qualifying_jp_pre_tax;
1293 -- |-------------------------------------------------------------------|
1294 -- |----------------------< upgrade_jp_pre_tax >-----------------------|
1295 -- |-------------------------------------------------------------------|
1296 procedure upgrade_jp_pre_tax(p_assignment_id in number)
1297 is
1298 c_proc constant varchar2(61) := c_package || 'upgrade_jp_pre_tax';
1299 --
1300 l_person_id per_all_assignments_f.person_id%TYPE;
1301 l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
1302 l_date_start per_periods_of_service.date_start%TYPE;
1303 l_leaving_reason per_periods_of_service.leaving_reason%TYPE;
1304 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
1305 l_employment_category per_all_assignments.employment_category%TYPE;
1306 l_effective_date pay_payroll_actions.effective_date%TYPE;
1307 --
1308 l_action_info_id1 pay_action_information.action_information_id%TYPE;
1309 l_action_info_id2 pay_action_information.action_information_id%TYPE;
1310 l_ovn pay_action_information.object_version_number%TYPE;
1311 --
1312 cursor csr_pre_tax is
1313 select ppt.*, ppa.effective_date
1314 from pay_jp_pre_tax_old ppt,
1315 pay_assignment_actions paa,
1316 pay_payroll_actions ppa
1317 where paa.assignment_id = p_assignment_id
1318 and paa.assignment_action_id = ppt.assignment_action_id
1319 and paa.payroll_action_id = ppa.payroll_action_id
1320 and not exists ( select 1
1321 from pay_action_information pai
1322 where ppt.assignment_action_id = pai.action_information1
1323 and paa.assignment_id = pai.assignment_id
1324 and pai.action_information_category = 'JP_PRE_TAX_1'
1325 and pai.action_context_type = 'AAP'
1326 )
1327 for update of ppt.pre_tax_id;
1328
1329 begin
1330 begin
1331 hr_utility.set_location('Entering: ' || c_proc, 10);
1332 hr_utility.trace('assignment_id: ' || p_assignment_id);
1333 --
1334 for l_pre_tax in csr_pre_tax
1335 loop
1336 hr_utility.set_location(c_proc, 20);
1337 --
1338 l_effective_date := l_pre_tax.effective_date;
1339 --
1340 select asg.person_id,
1341 asg.period_of_service_id,
1342 pds.date_start,
1343 pds.leaving_reason,
1344 pds.actual_termination_date,
1345 asg.employment_category
1346 into l_person_id,
1347 l_period_of_service_id,
1348 l_date_start,
1349 l_leaving_reason,
1350 l_actual_termination_date,
1351 l_employment_category
1352 from per_all_assignments_f asg,
1353 per_periods_of_service pds
1354 where asg.assignment_id = p_assignment_id
1355 and l_effective_date between asg.effective_start_date and asg.effective_end_date
1356 and pds.period_of_service_id = asg.period_of_service_id;
1357 --
1358 hr_utility.trace('salary_category : ' || l_pre_tax.salary_category);
1359 hr_utility.trace('taxable_sal_amt : ' || l_pre_tax.taxable_sal_amt);
1360 hr_utility.trace('taxable_mat_amt : ' || l_pre_tax.taxable_mat_amt);
1361 hr_utility.trace('previous_taxable_amt : ' || l_pre_tax.previous_taxable_amt);
1362 hr_utility.trace('hi_organization_id : ' || l_pre_tax.hi_organization_id);
1363 hr_utility.trace('hi_prem_ee : ' || l_pre_tax.hi_prem_ee);
1364 hr_utility.trace('hi_prem_er : ' || l_pre_tax.hi_prem_er);
1365 hr_utility.trace('wp_organization_id : ' || l_pre_tax.wp_organization_id);
1366 hr_utility.trace('wp_prem_ee : ' || l_pre_tax.wp_prem_ee);
1367 hr_utility.trace('wp_prem_er : ' || l_pre_tax.wp_prem_er);
1368 hr_utility.trace('wpf_organization_id : ' || l_pre_tax.wpf_organization_id);
1369 hr_utility.trace('wpf_prem_ee : ' || l_pre_tax.wpf_prem_ee);
1370 hr_utility.trace('wpf_prem_er : ' || l_pre_tax.wpf_prem_er);
1371 hr_utility.trace('ui_organization_id : ' || l_pre_tax.ui_organization_id);
1372 hr_utility.trace('ui_category : ' || l_pre_tax.ui_category);
1373 hr_utility.trace('ui_prem_ee : ' || l_pre_tax.ui_prem_ee);
1374 hr_utility.trace('ui_sal_amt : ' || l_pre_tax.ui_sal_amt);
1375 hr_utility.trace('wai_organization_id : ' || l_pre_tax.wai_organization_id);
1376 hr_utility.trace('wai_category : ' || l_pre_tax.wai_category);
1377 hr_utility.trace('wai_sal_amt : ' || l_pre_tax.wai_sal_amt);
1378 hr_utility.trace('itax_organization_id : ' || l_pre_tax.itax_organization_id);
1379 hr_utility.trace('itax_category : ' || l_pre_tax.itax_category);
1380 hr_utility.trace('itax_yea_category : ' || l_pre_tax.itax_yea_category);
1381 hr_utility.trace('itax : ' || l_pre_tax.itax);
1382 hr_utility.trace('itax_adjustment : ' || l_pre_tax.itax_adjustment);
1383 hr_utility.trace('previous_itax : ' || l_pre_tax.previous_itax);
1384 hr_utility.trace('ltax_organization_id : ' || l_pre_tax.ltax_organization_id);
1385 hr_utility.trace('ltax_district_code : ' || l_pre_tax.ltax_district_code);
1386 hr_utility.trace('ltax_swot_no : ' || l_pre_tax.ltax_swot_no);
1387 hr_utility.trace('ltax : ' || l_pre_tax.ltax);
1388 hr_utility.trace('ltax_lumpsum : ' || l_pre_tax.ltax_lumpsum);
1389 hr_utility.trace('sp_ltax : ' || l_pre_tax.sp_ltax);
1390 hr_utility.trace('sp_ltax_income : ' || l_pre_tax.sp_ltax_income);
1391 hr_utility.trace('sp_ltax_shi : ' || l_pre_tax.sp_ltax_shi);
1392 hr_utility.trace('sp_ltax_to : ' || l_pre_tax.sp_ltax_to);
1393 hr_utility.trace('ci_prem_ee : ' || l_pre_tax.ci_prem_ee);
1394 hr_utility.trace('ci_prem_er : ' || l_pre_tax.ci_prem_er);
1395 hr_utility.trace('mutual_aid : ' || l_pre_tax.mutual_aid);
1396 hr_utility.trace('disaster_tax_reduction : ' || l_pre_tax.disaster_tax_reduction);
1397 hr_utility.trace('sp_ltax_district_code : ' || l_pre_tax.sp_ltax_district_code);
1398 --
1399 pay_action_information_api.create_action_information
1400 (
1401 p_action_information_id => l_action_info_id1
1402 ,p_action_context_id => l_pre_tax.assignment_action_id
1403 ,p_action_context_type => 'AAP'
1404 ,p_object_version_number => l_ovn
1405 ,p_effective_date => l_effective_date
1406 ,p_assignment_id => p_assignment_id
1407 ,p_action_information_category => 'JP_PRE_TAX_1'
1408 ,p_action_information1 => fnd_number.number_to_canonical(l_pre_tax.assignment_action_id)
1409 ,p_action_information2 => fnd_number.number_to_canonical(l_pre_tax.taxable_sal_amt)
1410 ,p_action_information3 => fnd_number.number_to_canonical(l_pre_tax.taxable_mat_amt)
1411 ,p_action_information4 => fnd_number.number_to_canonical(l_person_id)
1412 ,p_action_information5 => l_pre_tax.hi_organization_id
1413 ,p_action_information6 => fnd_number.number_to_canonical(l_pre_tax.hi_prem_ee)
1414 ,p_action_information7 => fnd_number.number_to_canonical(l_pre_tax.hi_prem_er)
1415 ,p_action_information8 => l_pre_tax.wp_organization_id
1416 ,p_action_information9 => fnd_number.number_to_canonical(l_pre_tax.wp_prem_ee)
1417 ,p_action_information10 => fnd_number.number_to_canonical(l_pre_tax.wp_prem_er)
1418 ,p_action_information11 => l_pre_tax.wpf_organization_id
1419 ,p_action_information12 => fnd_number.number_to_canonical(l_pre_tax.wpf_prem_ee)
1420 ,p_action_information13 => l_pre_tax.salary_category
1421 ,p_action_information14 => fnd_number.number_to_canonical(l_pre_tax.mutual_aid)
1422 ,p_action_information15 => fnd_number.number_to_canonical(l_period_of_service_id)
1423 ,p_action_information16 => to_char(l_date_start,'YYYY/MM/DD')
1424 ,p_action_information17 => l_leaving_reason
1425 ,p_action_information18 => to_char(l_actual_termination_date,'YYYY/MM/DD')
1426 ,p_action_information19 => l_pre_tax.ui_organization_id
1427 ,p_action_information20 => fnd_number.number_to_canonical(l_pre_tax.ui_prem_ee)
1428 ,p_action_information21 => l_pre_tax.itax_organization_id
1429 ,p_action_information22 => l_pre_tax.itax_category
1430 ,p_action_information23 => l_pre_tax.itax_yea_category
1431 ,p_action_information24 => fnd_number.number_to_canonical(l_pre_tax.itax)
1432 ,p_action_information25 => fnd_number.number_to_canonical(l_pre_tax.itax_adjustment)
1433 ,p_action_information26 => fnd_number.number_to_canonical(l_pre_tax.pre_tax_id)
1434 ,p_action_information29 => fnd_number.number_to_canonical(l_pre_tax.disaster_tax_reduction)
1435 ,p_action_information30 => l_employment_category
1436 );
1437
1438 pay_action_information_api.create_action_information
1439 (
1440 p_action_information_id => l_action_info_id2
1441 ,p_action_context_id => l_pre_tax.assignment_action_id
1442 ,p_action_context_type => 'AAP'
1443 ,p_object_version_number => l_ovn
1444 ,p_effective_date => l_effective_date
1445 ,p_assignment_id => p_assignment_id
1446 ,p_action_information_category => 'JP_PRE_TAX_2'
1447 ,p_action_information1 => l_pre_tax.assignment_action_id
1448 ,p_action_information3 => l_pre_tax.ltax_district_code
1449 ,p_action_information5 => fnd_number.number_to_canonical(l_pre_tax.ltax)
1450 ,p_action_information6 => fnd_number.number_to_canonical(l_pre_tax.ltax_lumpsum)
1451 ,p_action_information7 => fnd_number.number_to_canonical(l_pre_tax.sp_ltax)
1452 ,p_action_information8 => fnd_number.number_to_canonical(l_pre_tax.sp_ltax_income)
1453 ,p_action_information9 => fnd_number.number_to_canonical(l_pre_tax.sp_ltax_shi)
1454 ,p_action_information10 => fnd_number.number_to_canonical(l_pre_tax.sp_ltax_to)
1455 ,p_action_information11 => fnd_number.number_to_canonical(l_pre_tax.ci_prem_ee)
1456 ,p_action_information12 => fnd_number.number_to_canonical(l_pre_tax.ci_prem_er)
1457 ,p_action_information13 => fnd_number.number_to_canonical(0)
1458 ,p_action_information14 => l_pre_tax.ui_category
1459 ,p_action_information15 => l_pre_tax.sp_ltax_district_code
1460 ,p_action_information16 => fnd_number.number_to_canonical(l_pre_tax.ui_sal_amt)
1461 ,p_action_information17 => l_pre_tax.wai_organization_id
1462 ,p_action_information18 => l_pre_tax.wai_category
1463 ,p_action_information19 => fnd_number.number_to_canonical(l_pre_tax.wai_sal_amt)
1464 ,p_action_information20 => fnd_number.number_to_canonical(l_pre_tax.wpf_prem_er)
1465 ,p_action_information21 => fnd_number.number_to_canonical(0)
1466 );
1467
1468 end loop;
1469 --
1470 hr_utility.set_location('Leaving: ' || c_proc, 100);
1471 --
1472 exception
1473 when app_exception.application_exception then
1474 app_exception.raise_exception;
1475 end;
1476 --
1477 exception
1478 when others then
1479 if g_num_errors = 0 then
1480 fnd_file.put_line(fnd_file.log, fnd_message.get_string('PAY', 'PAY_JP_RETRY_JP_PRE_TAX_UPG'));
1481 fnd_file.put_line(fnd_file.log,
1482 rpad(fnd_message.get_string('PAY', 'PAY_JP_ASSIGNMENT_ID'), 30) || ' ' || fnd_message.get_string('FND', 'FND_MESSAGE_TYPE_ERROR'));
1483 fnd_file.put_line(fnd_file.log, rpad('-', 30, '-') || ' ' || rpad('-', 100, '-'));
1484 end if;
1485 g_num_errors := g_num_errors + 1;
1486 --
1487 fnd_file.put_line(fnd_file.log, rpad(p_assignment_id, 30) || ' ' || sqlerrm);
1488 raise;
1489 end upgrade_jp_pre_tax;
1490 -- |-------------------------------------------------------------------|
1491 -- |-------------------< validate_yea_national_pens >------------------|
1492 -- |-------------------------------------------------------------------|
1493 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
1494 procedure validate_yea_national_pens(
1495 p_valid_upgrade out nocopy varchar2)
1496 is
1497 --
1498 c_proc constant varchar2(61) := c_package || 'validate_yea_national_pens';
1499 --
1500 begin
1501 --
1502 hr_utility.set_location('Entering: ' || c_proc, 10);
1503 --
1504 if g_element_type_ids.count = 0 then
1505 --
1506 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
1507 --
1508 end if;
1509 --
1510 for i in 1..g_element_type_ids.count loop
1511 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
1512 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
1513 --
1514 end loop;
1515 --
1516 if entries_or_results_exist('JP') then
1517 p_valid_upgrade := 'TRUE';
1518 else
1519 p_valid_upgrade := 'FALSE';
1520 end if;
1521 --
1522 hr_utility.trace(p_valid_upgrade);
1523 --
1524 hr_utility.set_location('Leaving: ' || c_proc, 100);
1525 --
1526 end validate_yea_national_pens;
1527 -- |-------------------------------------------------------------------|
1528 -- |------------------< qualify_yea_national_pens >--------------------|
1529 -- |-------------------------------------------------------------------|
1530 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
1531 procedure qualify_yea_national_pens(
1532 p_assignment_id in number,
1533 p_qualifier out nocopy varchar2)
1534 is
1535 c_proc constant varchar2(61) := c_package || 'qualify_yea_national_pens';
1536 begin
1537 --
1538 hr_utility.set_location('Entering: ' || c_proc, 10);
1539 --
1540 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
1541 p_qualifier := 'Y';
1542 else
1543 p_qualifier := 'N';
1544 end if;
1545 --
1546 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1547 hr_utility.set_location('Leaving: ' || c_proc, 100);
1548 --
1549 end qualify_yea_national_pens;
1550 -- |-------------------------------------------------------------------|
1551 -- |-------------------< upgrade_yea_national_pens >-------------------|
1552 -- |-------------------------------------------------------------------|
1553 procedure upgrade_yea_national_pens(
1554 p_assignment_id in number)
1555 is
1556 c_proc constant varchar2(61) := c_package || 'upgrade_yea_national_pens';
1557 begin
1558 --
1559 hr_utility.set_location('Entering: ' || c_proc, 10);
1560 --
1561 if g_element_type_ids.count = 0 then
1562 --
1563 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
1564 --
1565 end if;
1566 --
1567 for i in 1..g_element_type_ids.count loop
1568 --
1569 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
1570 --
1571 end loop;
1572 --
1573 hr_utility.set_location('Leaving: ' || c_proc, 100);
1574 --
1575 end upgrade_yea_national_pens;
1576 -- |-------------------------------------------------------------------|
1577 -- |--------------------< init_yea_earthquake_ins >--------------------|
1578 -- |-------------------------------------------------------------------|
1579 procedure init_yea_earthquake_ins
1580 is
1581 c_proc constant varchar2(61) := c_package || 'init_yea_earthquake_ins';
1582 begin
1583 hr_utility.set_location('Entering: ' || c_proc, 10);
1584 --
1585 if g_element_type_ids.count = 0 then
1586 hr_utility.trace('Not cached.');
1587 --
1588 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO', g_element_type_ids);
1589 get_element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_RSLT', g_element_type_ids);
1590 get_element_type_id('INI_YEA2', g_element_type_ids);
1591 get_element_type_id('YEA_INS_PREM_EXM_DECLARE_INFO', g_element_type_ids);
1592 end if;
1593 --
1594 for i in 1..g_element_type_ids.count loop
1595 hr_utility.trace(i || ': ' || g_element_type_ids(i));
1596 end loop;
1597 --
1598 hr_utility.set_location('Leaving: ' || c_proc, 100);
1599 end init_yea_earthquake_ins;
1600 -- |-------------------------------------------------------------------|
1601 -- |------------------< validate_yea_earthquake_ins >------------------|
1602 -- |-------------------------------------------------------------------|
1603 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
1604 procedure validate_yea_earthquake_ins(p_valid_upgrade out nocopy varchar2)
1605 is
1606 c_proc constant varchar2(61) := c_package || 'validate_yea_earthquake_ins';
1607 begin
1608 hr_utility.set_location('Entering: ' || c_proc, 10);
1609 --
1610 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
1611 --
1612 init_yea_earthquake_ins;
1613 --
1614 for i in 1..g_element_type_ids.count loop
1615 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
1616 end loop;
1617 --
1618 if entries_or_results_exist(p_legislation_code => 'JP') then
1619 p_valid_upgrade := 'TRUE';
1620 else
1621 p_valid_upgrade := 'FALSE';
1622 end if;
1623 --
1624 hr_utility.trace(p_valid_upgrade);
1625 hr_utility.set_location('Leaving: ' || c_proc, 100);
1626 end validate_yea_earthquake_ins;
1627 -- |-------------------------------------------------------------------|
1628 -- |------------------< qualify_yea_earthquake_ins >-------------------|
1629 -- |-------------------------------------------------------------------|
1630 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
1631 procedure qualify_yea_earthquake_ins(
1632 p_assignment_id in number,
1633 p_qualifier out nocopy varchar2)
1634 is
1635 c_proc constant varchar2(61) := c_package || 'qualify_yea_earthquake_ins';
1636 begin
1637 hr_utility.set_location('Entering: ' || c_proc, 10);
1638 --
1639 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
1640 p_qualifier := 'Y';
1641 else
1642 p_qualifier := 'N';
1643 end if;
1644 --
1645 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
1646 hr_utility.set_location('Leaving: ' || c_proc, 100);
1647 end qualify_yea_earthquake_ins;
1648 -- |-------------------------------------------------------------------|
1649 -- |------------------< upgrade_yea_earthquake_ins >-------------------|
1650 -- |-------------------------------------------------------------------|
1651 procedure upgrade_yea_earthquake_ins(p_assignment_id in number)
1652 is
1653 c_proc constant varchar2(61) := c_package || 'upgrade_yea_earthquake_ins';
1654 begin
1655 hr_utility.set_location('Entering: ' || c_proc, 10);
1656 --
1657 init_yea_earthquake_ins;
1658 --
1659 for i in 1..g_element_type_ids.count loop
1660 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
1661 end loop;
1662 --
1663 hr_utility.set_location('Leaving: ' || c_proc, 100);
1664 end upgrade_yea_earthquake_ins;
1665 -- |-------------------------------------------------------------------|
1666 -- |---------------------< validate_itw_archive >----------------------|
1667 -- |-------------------------------------------------------------------|
1668 procedure validate_itw_archive(p_valid_upgrade out nocopy varchar2)
1669 is
1670 c_proc constant varchar2(61) := c_package || 'validate_itw_archive';
1671 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
1672 l_payroll_id number;
1673 l_itax_organization_id number;
1674 l_include_terminated_flag varchar2(1);
1675 l_termination_date_from date;
1676 l_termination_date_to date;
1677 l_rearchive_flag varchar2(1);
1678 l_inherit_archive_flag varchar2(1);
1679 l_publication_period_status varchar2(1);
1680 l_publication_start_date date;
1681 l_publication_end_date date;
1682 --
1683 l_id number;
1684 l_ovn number;
1685 --
1686 cursor csr_pacts is
1687 select payroll_action_id,
1688 effective_date,
1689 legislative_parameters
1690 from pay_payroll_actions ppa
1691 where ppa.action_type = 'X'
1692 and ppa.report_type = 'JPTW'
1693 and ppa.report_qualifier = 'JP'
1694 and ppa.report_category = 'ARCHIVE'
1695 -- and ppa.action_status <> 'P'
1696 and pay_core_utils.get_parameter('INCLUDE_TERMINATED_FLAG', legislative_parameters) is null;
1697 /*
1698 and not exists(
1699 select null
1700 from pay_action_information pai
1701 where pai.action_context_id = ppa.payroll_action_id
1702 and pai.action_context_type = 'PA'
1703 and pai.action_information_category = 'JP_ITAX_PACT');
1704 */
1705 --
1706 procedure concat_parameter(
1707 p_token_name in varchar2,
1708 p_token_value in varchar2)
1709 is
1710 l_separator varchar2(1);
1711 begin
1712 if p_token_name is not null and p_token_value is not null then
1713 if l_legislative_parameters is not null then
1714 l_legislative_parameters := l_legislative_parameters || ' ';
1715 end if;
1716 --
1717 if instr(p_token_value, ' ') > 0 then
1718 l_separator := '|';
1719 end if;
1720 --
1721 l_legislative_parameters := l_legislative_parameters
1722 || p_token_value || ' '
1723 || p_token_name || '='
1724 || l_separator || p_token_value || l_separator;
1725 end if;
1726 end concat_parameter;
1727 begin
1728 hr_utility.set_location('Entering: ' || c_proc, 10);
1729 --
1730 p_valid_upgrade := 'FALSE';
1731 --
1732 for l_pact in csr_pacts loop
1733 p_valid_upgrade := 'TRUE';
1734 --
1735 l_payroll_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL', l_pact.legislative_parameters));
1736 l_itax_organization_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('SWOT', l_pact.legislative_parameters));
1737 l_include_terminated_flag := 'Y';
1738 l_termination_date_from := null;
1739 l_termination_date_to := null;
1740 l_rearchive_flag := 'Y';
1741 l_inherit_archive_flag := 'Y';
1742 l_publication_period_status := 'O';
1743 l_publication_start_date := fnd_date.canonical_to_date(pay_core_utils.get_parameter('OPEN_DATE', l_pact.legislative_parameters));
1744 l_publication_end_date := fnd_date.canonical_to_date(pay_core_utils.get_parameter('CLOSE_DATE', l_pact.legislative_parameters));
1745 --
1746 l_legislative_parameters := null;
1747 concat_parameter('PAYROLL_ID', fnd_number.number_to_canonical(l_payroll_id));
1748 concat_parameter('ITAX_ORGANIZATION_ID', fnd_number.number_to_canonical(l_itax_organization_id));
1749 concat_parameter('INCLUDE_TERMINATED_FLAG', l_include_terminated_flag);
1750 concat_parameter('TERMINATION_DATE_FROM', fnd_date.date_to_canonical(l_termination_date_from));
1751 concat_parameter('TERMINATION_DATE_TO', fnd_date.date_to_canonical(l_termination_date_to));
1752 concat_parameter('REARCHIVE_FLAG', l_rearchive_flag);
1753 concat_parameter('INHERIT_ARCHIVE_FLAG', l_inherit_archive_flag);
1754 concat_parameter('PUBLICATION_PERIOD_STATUS', l_publication_period_status);
1755 concat_parameter('PUBLICATION_START_DATE', fnd_date.date_to_canonical(l_publication_start_date));
1756 concat_parameter('PUBLICATION_END_DATE', fnd_date.date_to_canonical(l_publication_end_date));
1757 concat_parameter('UPGRADE_FLAG', 'Y');
1758 --
1759 hr_utility.trace('payroll_action_id: ' || l_pact.payroll_action_id);
1760 hr_utility.trace('legislative_parameters: ' || l_legislative_parameters);
1761 --
1762 update pay_payroll_actions
1763 set legislative_parameters = l_legislative_parameters
1764 where payroll_action_id = l_pact.payroll_action_id;
1765 --
1766 pay_action_information_api.create_action_information(
1767 p_validate => false,
1768 p_action_context_id => l_pact.payroll_action_id,
1769 p_action_context_type => 'PA',
1770 p_action_information_category => 'JP_ITAX_PACT',
1771 p_effective_date => l_pact.effective_date,
1772 p_action_information1 => fnd_number.number_to_canonical(l_payroll_id),
1773 p_action_information2 => fnd_number.number_to_canonical(l_itax_organization_id),
1774 p_action_information3 => l_include_terminated_flag,
1775 p_action_information4 => fnd_date.date_to_canonical(l_termination_date_from),
1776 p_action_information5 => fnd_date.date_to_canonical(l_termination_date_to),
1777 p_action_information6 => l_publication_period_status,
1778 p_action_information7 => fnd_date.date_to_canonical(l_publication_start_date),
1779 p_action_information8 => fnd_date.date_to_canonical(l_publication_end_date),
1780 p_action_information_id => l_id,
1781 p_object_version_number => l_ovn);
1782 end loop;
1783 --
1784 hr_utility.trace(p_valid_upgrade);
1785 hr_utility.set_location('Leaving: ' || c_proc, 100);
1786 end validate_itw_archive;
1787 -- |-------------------------------------------------------------------|
1788 -- |----------------------< qualify_itw_archive >----------------------|
1789 -- |-------------------------------------------------------------------|
1790 procedure qualify_itw_archive(
1791 p_assignment_id in number,
1792 p_qualifier out nocopy varchar2)
1793 is
1794 c_proc constant varchar2(61) := c_package || 'qualify_itw_archive';
1795 --
1796 -- Old archiver does not have PACT level archive data.
1797 -- This checks whether the archive needs to be upgraded or not
1798 -- using PACT level archive data.
1799 --
1800 cursor csr_upgrade_required is
1801 select 'Y'
1802 from dual
1803 where exists(
1804 select null
1805 from pay_assignment_actions paa,
1806 pay_payroll_actions ppa
1807 where paa.assignment_id = p_assignment_id
1808 and paa.action_status = 'C'
1809 and ppa.payroll_action_id = paa.payroll_action_id
1810 and ppa.action_type = 'X'
1811 and ppa.report_type = 'JPTW'
1812 and ppa.report_qualifier = 'JP'
1813 and ppa.report_category = 'ARCHIVE'
1814 and pay_core_utils.get_parameter('UPGRADE_FLAG', ppa.legislative_parameters) = 'Y');
1815 begin
1816 hr_utility.set_location('Entering: ' || c_proc, 10);
1817 --
1818 open csr_upgrade_required;
1819 fetch csr_upgrade_required into p_qualifier;
1820 if csr_upgrade_required%notfound then
1821 p_qualifier := 'N';
1822 end if;
1823 close csr_upgrade_required;
1824 --
1825 hr_utility.trace(p_assignment_id || ': ' || p_qualifier);
1826 hr_utility.set_location('Leaving: ' || c_proc, 100);
1827 end qualify_itw_archive;
1828 -- |-------------------------------------------------------------------|
1829 -- |-----------------------< to_canonical_date >-----------------------|
1830 -- |-------------------------------------------------------------------|
1831 function to_canonical_date(p_str in varchar2) return varchar2
1832 is
1833 l_str varchar2(30) := p_str;
1834 begin
1835 if l_str is not null then
1836 if length(l_str) = 7 then
1837 l_str := fnd_date.date_to_canonical(hr_jp_standard_pkg.to_jp_date(l_str, 'EYYMMDD'));
1838 elsif length(l_str) = 10 then
1839 l_str := fnd_date.date_to_canonical(to_date(l_str, 'YYYY/MM/DD'));
1840 end if;
1841 end if;
1842 --
1843 return l_str;
1844 end to_canonical_date;
1845 -- |-------------------------------------------------------------------|
1846 -- |----------------------< upgrade_itw_archive >----------------------|
1847 -- |-------------------------------------------------------------------|
1848 procedure upgrade_itw_archive(p_assignment_id in number)
1849 is
1850 c_proc constant varchar2(61) := c_package || 'validate_itw_archive';
1851 --
1852 l_varchar2_tbl hr_jp_standard_pkg.t_varchar2_tbl;
1853 l_itw_user_desc_kanji1 varchar2(240);
1854 l_itw_user_desc_kanji2 varchar2(240);
1855 l_dummy varchar2(32767);
1856 l_wtm_user_desc_kanji1 varchar2(240);
1857 l_wtm_user_desc_kanji2 varchar2(240);
1858 l_wtm_user_desc_kana1 varchar2(240);
1859 l_wtm_user_desc_kana2 varchar2(240);
1860 --
1861 cursor csr is
1862 select person.rowid person_rowid,
1863 arch.rowid arch_rowid,
1864 arch.action_information16 ||
1865 arch.action_information17 ||
1866 arch.action_information18 ||
1867 arch.action_information19 ||
1868 arch.action_information20 ITW_USER_DESC_KANJI,
1869 arch.action_information21 ||
1870 arch.action_information22 ||
1871 arch.action_information23 ||
1872 arch.action_information24 ||
1873 arch.action_information25 WTM_USER_DESC,
1874 other2.rowid other2_rowid,
1875 other2.action_information13 ITW_OVERRIDE_FLAG,
1876 other2.action_information14 WTM_OVERRIDE_FLAG,
1877 other2.ACTION_INFORMATION23 ITW_SYSTEM_DESC1_KANJI,
1878 other2.ACTION_INFORMATION15 ITW_SYSTEM_DESC2_KANJI_1,
1879 other2.ACTION_INFORMATION16 ITW_SYSTEM_DESC2_KANJI_2,
1880 other2.ACTION_INFORMATION19 WTM_SYSTEM_DESC_KANJI_1,
1881 other2.ACTION_INFORMATION20 WTM_SYSTEM_DESC_KANJI_2,
1882 other2.ACTION_INFORMATION21 WTM_SYSTEM_DESC_KANA_1,
1883 other2.ACTION_INFORMATION22 WTM_SYSTEM_DESC_KANA_2
1884 from pay_assignment_actions paa,
1885 pay_payroll_actions ppa,
1886 pay_action_information person,
1887 pay_action_information arch,
1888 pay_action_information other2
1889 where paa.assignment_id = p_assignment_id
1890 and paa.action_status = 'C'
1891 and ppa.payroll_action_id = paa.payroll_action_id
1892 and ppa.action_type = 'X'
1893 and ppa.report_type = 'JPTW'
1894 and ppa.report_qualifier = 'JP'
1895 and ppa.report_category = 'ARCHIVE'
1896 and pay_core_utils.get_parameter('UPGRADE_FLAG', ppa.legislative_parameters) = 'Y'
1897 and person.action_context_id = paa.assignment_action_id
1898 and person.action_context_type = 'AAP'
1899 and person.action_information_category = 'JP_ITAX_PERSON'
1900 and arch.action_context_id = person.action_context_id
1901 and arch.action_context_type = 'AAP'
1902 and arch.action_information_category = 'JP_ITAX_ARCH'
1903 and arch.effective_date = person.effective_date
1904 and other2.action_context_id = person.action_context_id
1905 and other2.action_context_type = 'AAP'
1906 and other2.action_information_category = 'JP_ITAX_OTHER2'
1907 and other2.effective_date = person.effective_date
1908 for update of
1909 person.action_information_id,
1910 other2.action_information_id nowait;
1911 begin
1912 hr_utility.set_location('Entering: ' || c_proc, 10);
1913 --
1914 for l_rec in csr loop
1915 --
1916 -- JP_ITAX_PERSON
1917 --
1918 update pay_action_information
1919 set action_information11 = to_canonical_date(action_information11),
1920 action_information13 = to_canonical_date(action_information13),
1921 action_information25 = to_canonical_date(action_information25),
1922 -- action_information27 = 'O'
1923 action_information27 = null
1924 where rowid = l_rec.person_rowid;
1925 --
1926 -- JP_ITAX_ARCH
1927 --
1928 update pay_action_information
1929 set action_information12 = to_canonical_date(action_information12)
1930 where rowid = l_rec.arch_rowid;
1931 --
1932 -- JP_ITAX_OTHER2
1933 --
1934 -- ITW User Description
1935 --
1936 l_dummy := rtrim(substrb(l_rec.itw_user_desc_kanji, 1, 300));
1937 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1938 l_itw_user_desc_kanji1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1939 l_itw_user_desc_kanji2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1940 --
1941 -- ITW System Description
1942 --
1943 if l_rec.itw_override_flag = 'Y' then
1944 l_rec.ITW_SYSTEM_DESC1_KANJI := null;
1945 l_rec.ITW_SYSTEM_DESC2_KANJI_1 := null;
1946 l_rec.ITW_SYSTEM_DESC2_KANJI_2 := null;
1947 else
1948 l_rec.ITW_SYSTEM_DESC1_KANJI := rtrim(substrb(l_rec.ITW_SYSTEM_DESC1_KANJI, 1, 240));
1949 --
1950 l_dummy := rtrim(substrb(l_rec.ITW_SYSTEM_DESC2_KANJI_1 || l_rec.ITW_SYSTEM_DESC2_KANJI_2, 1, 300));
1951 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1952 l_rec.ITW_SYSTEM_DESC2_KANJI_1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1953 l_rec.ITW_SYSTEM_DESC2_KANJI_2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1954 end if;
1955 --
1956 -- WTM User Description
1957 --
1958 l_dummy := hr_jp_standard_pkg.to_zenkaku(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_kanji1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1962 l_wtm_user_desc_kanji2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1963 --
1964 l_dummy := hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(l_rec.wtm_user_desc, '?'));
1965 l_dummy := substr(l_dummy, 1, 100);
1966 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1967 l_wtm_user_desc_kana1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1968 l_wtm_user_desc_kana2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1969 --
1970 -- WTM System Description
1971 --
1972 if l_rec.wtm_override_flag = 'Y' then
1973 l_rec.WTM_SYSTEM_DESC_KANJI_1 := null;
1974 l_rec.WTM_SYSTEM_DESC_KANJI_2 := null;
1975 l_rec.WTM_SYSTEM_DESC_KANA_1 := null;
1976 l_rec.WTM_SYSTEM_DESC_KANA_2 := null;
1977 else
1978 l_dummy := hr_jp_standard_pkg.to_zenkaku(l_rec.WTM_SYSTEM_DESC_KANJI_1 || l_rec.WTM_SYSTEM_DESC_KANJI_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_KANJI_1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1982 l_rec.WTM_SYSTEM_DESC_KANJI_2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1983 --
1984 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, '?'));
1985 l_dummy := substr(l_dummy, 1, 100);
1986 hr_jp_standard_pkg.to_table(l_dummy, 240, l_varchar2_tbl);
1987 l_rec.WTM_SYSTEM_DESC_KANA_1 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 1);
1988 l_rec.WTM_SYSTEM_DESC_KANA_2 := hr_jp_standard_pkg.get_index_at(l_varchar2_tbl, 2);
1989 end if;
1990 --
1991 update pay_action_information
1992 set ACTION_INFORMATION23 = l_rec.ITW_SYSTEM_DESC1_KANJI,
1993 ACTION_INFORMATION15 = l_rec.ITW_SYSTEM_DESC2_KANJI_1,
1994 ACTION_INFORMATION16 = l_rec.ITW_SYSTEM_DESC2_KANJI_2,
1995 ACTION_INFORMATION19 = l_rec.WTM_SYSTEM_DESC_KANJI_1,
1996 ACTION_INFORMATION20 = l_rec.WTM_SYSTEM_DESC_KANJI_2,
1997 ACTION_INFORMATION21 = l_rec.WTM_SYSTEM_DESC_KANA_1,
1998 ACTION_INFORMATION22 = l_rec.WTM_SYSTEM_DESC_KANA_2,
1999 action_information25 = l_itw_user_desc_kanji1,
2000 action_information26 = l_itw_user_desc_kanji2,
2001 action_information27 = l_wtm_user_desc_kanji1,
2002 action_information28 = l_wtm_user_desc_kanji2,
2003 action_information29 = l_wtm_user_desc_kana1,
2004 action_information30 = l_wtm_user_desc_kana2
2005 where rowid = l_rec.other2_rowid;
2006 end loop;
2007 --
2008 hr_utility.set_location('Leaving: ' || c_proc, 100);
2009 end upgrade_itw_archive;
2010 --
2011 -- -------------------------------------------------------------------------
2012 -- qualify_hi_smr_data
2013 -- -------------------------------------------------------------------------
2014 -- run by pay_generic_upgrade.action_creation
2015 -- do_qualification run before calling hr_nonrun_asact.insact in create_object_action
2016 procedure qualify_hi_smr_data(
2017 p_assignment_id in number,
2018 p_qualifier out nocopy varchar2)
2019 is
2020 --
2021 l_proc varchar2(80) := c_package||'qualify_hi_smr_data';
2022 --
2023 l_valid_delete varchar2(1) := 'N';
2024 --
2025 l_ass_info hr_jp_data_migration_pkg.t_ass_hi_smr_rec;
2026 --
2027 begin
2028 --
2029 if g_debug then
2030 hr_utility.set_location(l_proc,0);
2031 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
2032 end if;
2033 --
2034 hr_jp_data_migration_pkg.init_def_hi_smr_data;
2035 --
2036 if g_debug then
2037 hr_utility.set_location(l_proc,10);
2038 hr_utility.trace('hr_jp_data_migration_pkg.g_skip_qualify : '||hr_jp_data_migration_pkg.g_skip_qualify);
2039 hr_utility.trace('hr_jp_data_migration_pkg.g_upd_mode : '||hr_jp_data_migration_pkg.g_upd_mode);
2040 hr_utility.trace('hr_jp_data_migration_pkg.g_mig_date : '||to_char(hr_jp_data_migration_pkg.g_mig_date,'YYYY/MM/DD'));
2041 end if;
2042 --
2043 if hr_jp_data_migration_pkg.g_skip_qualify = 'N' then
2044 --
2045 if g_debug then
2046 hr_utility.set_location(l_proc,20);
2047 end if;
2048 --
2049 -- print header for each threads but it is ok because just log.
2050 hr_jp_data_migration_pkg.qualify_hi_smr_hd(
2051 p_assignment_id => p_assignment_id);
2052 --
2053 if g_debug then
2054 hr_utility.set_location(l_proc,30);
2055 end if;
2056 --
2057 l_ass_info := hr_jp_data_migration_pkg.get_ass_info(
2058 p_assignment_id => p_assignment_id,
2059 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2060 --
2061 if g_debug then
2062 hr_utility.set_location(l_proc,40);
2063 hr_utility.trace('l_ass_info.ass_id : '||to_char(l_ass_info.ass_id));
2064 end if;
2065 --
2066 hr_jp_data_migration_pkg.val_mig_smr_assact(
2067 p_business_group_id => l_ass_info.bg_id,
2068 p_business_group_name => l_ass_info.bg_name,
2069 p_assignment_id => p_assignment_id,
2070 p_assignment_number => l_ass_info.ass_num,
2071 p_session_date => hr_jp_data_migration_pkg.g_mig_date,
2072 p_valid_delete => l_valid_delete);
2073 --
2074 if g_debug then
2075 hr_utility.set_location(l_proc,50);
2076 hr_utility.trace('l_valid_delete : '||l_valid_delete);
2077 end if;
2078 --
2079 p_qualifier := l_valid_delete;
2080 --
2081 -- never come here at this moment.
2082 -- notice: include following case in target because skip qualify.
2083 -- 1. ee null
2084 -- 2. ee already updated (manual update)
2085 -- 3. future entry exists
2086 -- 4. in update mode applied month is future (>= p_session_date)
2087 -- 5. mr is null
2088 else
2089 --
2090 p_qualifier := 'Y';
2091 --
2092 if g_debug then
2093 hr_utility.set_location(l_proc,60);
2094 end if;
2095 --
2096 end if;
2097 --
2098 if g_debug then
2099 hr_utility.trace('p_qualifier : '||p_qualifier);
2100 hr_utility.set_location(l_proc,1000);
2101 end if;
2102 --
2103 end qualify_hi_smr_data;
2104 --
2105 -- -------------------------------------------------------------------------
2106 -- migrate_hi_smr_data
2107 -- -------------------------------------------------------------------------
2108 -- run by pay_generic_upgrade.upgrade_data
2109 procedure migrate_hi_smr_data(
2110 p_assignment_id in number)
2111 is
2112 --
2113 l_proc varchar2(80) := c_package||'migrate_hi_smr_data';
2114 --
2115 l_ass_info hr_jp_data_migration_pkg.t_ass_hi_smr_rec;
2116 --
2117 begin
2118 --
2119 if g_debug then
2120 hr_utility.set_location(l_proc,0);
2121 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
2122 end if;
2123 --
2124 -- need to reset for retry, qualify_obs_data is not called in retry process.
2125 hr_jp_data_migration_pkg.init_def_hi_smr_data;
2126 --
2127 if g_debug then
2128 hr_utility.set_location(l_proc,10);
2129 hr_utility.trace('hr_jp_data_migration_pkg.g_skip_qualify : '||hr_jp_data_migration_pkg.g_skip_qualify);
2130 hr_utility.trace('hr_jp_data_migration_pkg.g_upd_mode : '||hr_jp_data_migration_pkg.g_upd_mode);
2131 hr_utility.trace('hr_jp_data_migration_pkg.g_mig_date : '||to_char(hr_jp_data_migration_pkg.g_mig_date,'YYYY/MM/DD'));
2132 end if;
2133 --
2134 -- print header for each threads but it is ok because just log.
2135 hr_jp_data_migration_pkg.migrate_hi_smr_hd(
2136 p_assignment_id => p_assignment_id);
2137 --
2138 if g_debug then
2139 hr_utility.set_location(l_proc,20);
2140 end if;
2141 --
2142 l_ass_info := hr_jp_data_migration_pkg.get_ass_info(
2143 p_assignment_id => p_assignment_id,
2144 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2145 --
2146 if g_debug then
2147 hr_utility.set_location(l_proc,30);
2148 hr_utility.trace('l_ass_info.ass_id : '||to_char(l_ass_info.ass_id));
2149 end if;
2150 --
2151 -- for api use
2152 hr_jp_data_migration_pkg.insert_session(
2153 p_effective_date => hr_jp_data_migration_pkg.g_mig_date);
2154 --
2155 hr_jp_data_migration_pkg.mig_smr_assact(
2156 p_business_group_id => l_ass_info.bg_id,
2157 p_business_group_name => l_ass_info.bg_name,
2158 p_assignment_id => p_assignment_id,
2159 p_assignment_number => l_ass_info.ass_num,
2160 p_session_date => hr_jp_data_migration_pkg.g_mig_date,
2161 p_hi_mr => null);
2162 --
2163 -- delete session is invalid, cause no data found.
2164 --hr_jp_data_migration_pkg.delete_session;
2165 --
2166 -- commit is invalid in archive loop for ORA-01002
2167 -- automatically commit will be executed for each assignment in archive process.
2168 -- commit;
2169 --
2170 if g_debug then
2171 hr_utility.set_location(l_proc,1000);
2172 end if;
2173 --
2174 end migrate_hi_smr_data;
2175 --
2176 -- -------------------------------------------------------------------------
2177 -- init_adj_ann_std_bon
2178 -- -------------------------------------------------------------------------
2179 procedure init_adj_ann_std_bon
2180 is
2181 --
2182 c_proc constant varchar2(61) := c_package||'init_adj_ann_std_bon';
2183 --
2184 begin
2185 --
2186 if g_debug then
2187 hr_utility.set_location('Entering: ' || c_proc, 10);
2188 end if;
2189 --
2190 if g_element_type_ids.count = 0 then
2191 --
2192 if g_debug then
2193 hr_utility.trace('Not cached.');
2194 end if;
2195 --
2196 get_element_type_id('BON_HI_ADJ_INFO', g_element_type_ids);
2197 get_element_type_id('BON_HI_STD_BON', g_element_type_ids);
2198 --
2199 end if;
2200 --
2201 for i in 1..g_element_type_ids.count loop
2202 --
2203 if g_debug then
2204 hr_utility.trace(i || ': ' || g_element_type_ids(i));
2205 end if;
2206 --
2207 end loop;
2208 --
2209 if g_debug then
2210 hr_utility.set_location('Leaving: ' || c_proc, 100);
2211 end if;
2212 --
2213 end init_adj_ann_std_bon;
2214 --
2215 -- -------------------------------------------------------------------------
2216 -- validate_adj_ann_std_bon
2217 -- -------------------------------------------------------------------------
2218 -- pay_upgrade_definitions.validate_procedure is revoked from hr_update_utility (hrglobal)
2219 procedure validate_adj_ann_std_bon(
2220 p_valid_upgrade out nocopy varchar2)
2221 is
2222 --
2223 c_proc constant varchar2(61) := c_package || 'validate_adj_ann_std_bon';
2224 --
2225 begin
2226 --
2227 if g_debug then
2228 hr_utility.set_location('Entering: ' || c_proc, 10);
2229 end if;
2230 --
2231 init_adj_ann_std_bon;
2232 --
2233 for i in 1..g_element_type_ids.count loop
2234 -- At first, upgrade PAY_LINK_INPUT_VALUES_F.
2235 pay_jp_generic_upgrade_pkg.sync_link_input_values(g_element_type_ids(i));
2236 --
2237 end loop;
2238 --
2239 if entries_or_results_exist('JP') then
2240 p_valid_upgrade := 'TRUE';
2241 else
2242 p_valid_upgrade := 'FALSE';
2243 end if;
2244 --
2245 if g_debug then
2246 hr_utility.trace(p_valid_upgrade);
2247 hr_utility.set_location('Leaving: ' || c_proc, 100);
2248 end if;
2249 --
2250 end validate_adj_ann_std_bon;
2251 --
2252 -- -------------------------------------------------------------------------
2253 -- qualify_adj_ann_std_bon
2254 -- -------------------------------------------------------------------------
2255 -- pay_upgrade_definitions.qualifying_procedure is revoked from pay_generic_upgrade
2256 procedure qualify_adj_ann_std_bon(
2257 p_assignment_id in number,
2258 p_qualifier out nocopy varchar2)
2259 is
2260 c_proc constant varchar2(61) := c_package || 'qualify_adj_ann_std_bon';
2261 begin
2262 --
2263 if g_debug then
2264 hr_utility.set_location('Entering: ' || c_proc, 10);
2265 end if;
2266 --
2267 if entries_or_results_exist(p_assignment_id => p_assignment_id) then
2268 p_qualifier := 'Y';
2269 else
2270 p_qualifier := 'N';
2271 end if;
2272 --
2273 hr_utility.trace(p_assignment_id || ' : ' || p_qualifier);
2274 hr_utility.set_location('Leaving: ' || c_proc, 100);
2275 --
2276 end qualify_adj_ann_std_bon;
2277 --
2278 -- -------------------------------------------------------------------------
2279 -- upgrade_adj_ann_std_bon
2280 -- -------------------------------------------------------------------------
2281 procedure upgrade_adj_ann_std_bon(
2282 p_assignment_id in number)
2283 is
2284 c_proc constant varchar2(61) := c_package || 'upgrade_adj_ann_std_bon';
2285 begin
2286 --
2287 if g_debug then
2288 hr_utility.set_location('Entering: ' || c_proc, 10);
2289 end if;
2290 --
2291 init_adj_ann_std_bon;
2292 --
2293 for i in 1..g_element_type_ids.count loop
2294 sync_entries_and_results(p_assignment_id, g_element_type_ids(i));
2295 end loop;
2296 --
2297 if g_debug then
2298 hr_utility.set_location('Leaving: ' || c_proc, 100);
2299 end if;
2300 --
2301 end upgrade_adj_ann_std_bon;
2302 --
2303 /*
2304 -- |-------------------------------------------------------------------|
2305 -- |------------------------< submit_request >-------------------------|
2306 -- |-------------------------------------------------------------------|
2307 function submit_request(
2308 p_legislation_code in varchar2,
2309 p_upgrade_short_name in varchar2,
2310 p_validate_procedure in varchar2,
2311 p_application_short_name in varchar2,
2312 p_concurrent_program_name in varchar2) return number
2313 is
2314 l_dummy varchar2(30);
2315 l_business_group_id number;
2316 l_valid_request boolean;
2317 l_request_id number;
2318 l_phase varchar2(255);
2319 l_status varchar2(255);
2320 l_dev_phase varchar2(255);
2321 l_dev_status varchar2(255);
2322 l_message varchar2(255);
2323 l_valid_upgrade varchar2(10) := 'TRUE';
2324 --
2325 cursor csr_upgrade_def is
2326 select upgrade_definition_id,
2327 upgrade_method,
2328 upgrade_level,
2329 legislation_code,
2330 legislatively_enabled
2331 from pay_upgrade_definitions
2332 where short_name = p_upgrade_short_name;
2333 l_upgrade_def csr_upgrade_def%rowtype;
2334 --
2335 cursor csr_upgrade_leg(p_upgrade_definition_id number) is
2336 select 'Y'
2337 from pay_upgrade_legislations
2338 where upgrade_definition_id = p_upgrade_definition_id
2339 and legislation_code = p_legislation_code;
2340 --
2341 cursor csr_upgrade_status(p_upgrade_definition_id number) is
2342 select status
2343 from pay_upgrade_status
2344 where upgrade_definition_id = p_upgrade_definition_id
2345 and business_group_id is null
2346 and legislation_code = p_legislation_code;
2347 --
2348 -- Do not use per_business_groups_perf which does not return
2349 -- "Disabled" business groups.
2350 --
2351 cursor csr_bg is
2352 select organization_id
2353 from hr_organization_information
2354 where org_information_context = 'Business Group Information'
2355 and org_information9 = p_legislation_code
2356 and rownum <= 1;
2357 --
2358 cursor csr_user_resp is
2359 select g.user_id,
2360 g.responsibility_id,
2361 g.responsibility_application_id
2362 from fnd_responsibility r,
2363 fnd_user_resp_groups g,
2364 fnd_user u
2365 where u.user_name = 'SYSADMIN'
2366 and g.user_id = u.user_id
2367 and g.security_group_id = 0
2368 and r.application_id = g.responsibility_application_id
2369 and r.responsibility_id = g.responsibility_id
2370 and r.responsibility_key = 'SYSTEM_ADMINISTRATOR';
2371 --
2372 procedure raise_error(
2373 p_message in varchar2,
2374 p_token in varchar2 default null)
2375 is
2376 begin
2377 if p_token is null then
2378 raise_application_error(-20001, p_message || ': ' || p_upgrade_short_name);
2379 else
2380 raise_application_error(-20001, p_message || ': ' || p_upgrade_short_name || ', ' || p_token);
2381 end if;
2382 end raise_error;
2383 --
2384 procedure set_upgrade_completed
2385 is
2386 begin
2387 pay_generic_upgrade.set_upgrade_status(
2388 p_upg_def_id => l_upgrade_def.upgrade_definition_id,
2389 p_upg_lvl => 'L',
2390 p_bus_grp => null,
2391 p_leg_code => p_legislation_code,
2392 p_status => 'P');
2393 pay_generic_upgrade.set_upgrade_status(
2394 p_upg_def_id => l_upgrade_def.upgrade_definition_id,
2395 p_upg_lvl => 'L',
2396 p_bus_grp => null,
2397 p_leg_code => p_legislation_code,
2398 p_status => 'C');
2399 end set_upgrade_completed;
2400 begin
2401 --
2402 -- Validate Input Parameters
2403 --
2404 open csr_upgrade_def;
2405 fetch csr_upgrade_def into l_upgrade_def;
2406 if csr_upgrade_def%notfound then
2407 close csr_upgrade_def;
2408 raise_error('Specified Upgrade Definition not found');
2409 end if;
2410 close csr_upgrade_def;
2411 --
2412 if l_upgrade_def.upgrade_method <> 'PYUGEN' then
2413 raise_error('Only Upgrade Method "PYUGEN" is supported', l_upgrade_def.upgrade_method);
2414 end if;
2415 --
2416 if l_upgrade_def.upgrade_level <> 'L' then
2417 raise_error('Only Upgrade Level "L" is supported', l_upgrade_def.upgrade_level);
2418 end if;
2419 --
2420 if l_upgrade_def.legislation_code is not null then
2421 if l_upgrade_def.legislation_code <> p_legislation_code then
2422 raise_error('Inconsistent legislation', l_upgrade_def.legislation_code);
2423 end if;
2424 else
2425 if l_upgrade_def.legislatively_enabled = 'Y' then
2426 open csr_upgrade_leg(l_upgrade_def.upgrade_definition_id);
2427 fetch csr_upgrade_leg into l_dummy;
2428 if csr_upgrade_leg%notfound then
2429 close csr_upgrade_leg;
2430 raise_error('Upgrade Definition is not legislatively available', p_legislation_code);
2431 end if;
2432 close csr_upgrade_leg;
2433 end if;
2434 end if;
2435 --
2436 -- Make sure the data update has not been performed already.
2437 -- pay_core_utils.get_upgrade_status cannot be used for
2438 -- legislative upgrade which requires business_group_id as mandatory parameter.
2439 -- There's possibility that business groups within "p_legislation_code" are not created yet at this point.
2440 --
2441 open csr_upgrade_status(l_upgrade_def.upgrade_definition_id);
2442 fetch csr_upgrade_status into l_dummy;
2443 if csr_upgrade_status%notfound then
2444 --
2445 -- To run concurrent program "Generic Upgrade Mechanism" at legislation level,
2446 -- it is required to derive business_group_id of one of business groups within "p_legislation_code".
2447 --
2448 open csr_bg;
2449 fetch csr_bg into l_business_group_id;
2450 --
2451 -- When no business group for specified legislation is available,
2452 -- stamp the legislation upgrade as "Completed".
2453 -- Note to stamp "Processing", then "Completed" to suppress error.
2454 --
2455 if csr_bg%notfound then
2456 set_upgrade_completed;
2457 else
2458 --
2459 -- Check pending or running concurrent program exists in FND_CONCURRENT_REQUESTS.
2460 --
2461 l_valid_request := fnd_concurrent.get_request_status(
2462 request_id => l_request_id,
2463 appl_shortname => p_application_short_name,
2464 program => p_concurrent_program_name,
2465 phase => l_phase,
2466 status => l_status,
2467 dev_phase => l_dev_phase,
2468 dev_status => l_dev_status,
2469 message => l_message);
2470 --
2471 -- When no request or no pending/running request found,
2472 -- submit request.
2473 --
2474 l_request_id := null;
2475 if (not l_valid_request) or (l_valid_request and l_dev_phase not in ('PENDING', 'RUNNING')) then
2476 --
2477 -- Execute validate procedure only when set.
2478 --
2479 if p_validate_procedure is not null then
2480 execute immediate 'begin ' || p_validate_procedure || '(:a); end;' using out l_valid_upgrade;
2481 end if;
2482 --
2483 if l_valid_upgrade = 'TRUE' then
2484 for l_rec in csr_user_resp loop
2485 fnd_global.apps_initialize(
2486 user_id => l_rec.user_id,
2487 resp_id => l_rec.responsibility_id,
2488 resp_appl_id => l_rec.responsibility_application_id);
2489 end loop;
2490 --
2491 l_request_id := fnd_request.submit_request (
2492 application => p_application_short_name,
2493 program => p_concurrent_program_name,
2494 argument1 => 'ARCHIVE', -- Process Name
2495 argument2 => 'GENERIC_UPGRADE', -- Report Type
2496 argument3 => 'DEFAULT', -- Rpt Qual
2497 argument4 => null, -- Start Date
2498 argument5 => null, -- End Date
2499 argument6 => 'PROCESS', -- Rpt Category
2500 argument7 => fnd_number.number_to_canonical(l_business_group_id), -- Business Grp
2501 argument8 => null, -- Mag File Nme
2502 argument9 => null, -- Rep File Nme
2503 argument10 => fnd_number.number_to_canonical(l_upgrade_def.upgrade_definition_id), -- ID
2504 argument11 => p_upgrade_short_name, -- Short Name
2505 argument12 => 'UPG_DEF_NAME=' || p_upgrade_short_name); -- Upgrade Name
2506 if l_request_id = 0 then
2507 hr_utility.raise_error;
2508 end if;
2509 else
2510 set_upgrade_completed;
2511 end if;
2512 end if;
2513 end if;
2514 close csr_bg;
2515 end if;
2516 close csr_upgrade_status;
2517 --
2518 return l_request_id;
2519 end submit_request;
2520 */
2521 --
2522 --begin
2523 -- hr_utility.trace_on('F', 'TTAGAWA');
2524 end pay_jp_generic_upgrade_pkg;