[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ITAX_ARCHIVE_PKG
Source
1 package BODY PAY_JP_ITAX_ARCHIVE_PKG as
2 /* $Header: pyjpiarc.pkb 120.21.12000000.6 2007/07/19 03:21:19 ttagawa noship $ */
3 --
4 -- Constants
5 --
6 c_package CONSTANT VARCHAR2(31) := 'pay_jp_itax_archive_pkg.';
7 c_checked CONSTANT VARCHAR2(2) := '*';
8 --
9 -- Global Variables
10 --
11 g_old_fixed_rate_kanji VARCHAR2(80) := fnd_message.get_string('PAY', 'PAY_JP_YEA_FIXED_RATE');
12 g_old_fixed_rate_kana VARCHAR2(80) := fnd_message.get_string('PAY', 'PAY_JP_YEA_FIXED_RATE_KANA');
13 g_old_national_pens_kanji VARCHAR2(80) := fnd_message.get_string('PAY', 'PAY_JP_NATIONAL_PENSION');
14 g_old_national_pens_kana VARCHAR2(80) := fnd_message.get_string('PAY', 'PAY_JP_NATIONAL_PENSION_KANA');
15 g_new_fixed_rate_kanji VARCHAR2(80) := fnd_message.get_string('PAY', 'PAY_JP_ITAX_FIXED_RATE');
16 g_new_fixed_rate_kana VARCHAR2(80) := fnd_message.get_string('PAY', 'PAY_JP_ITAX_FIXED_RATE_KANA');
17 g_new_national_pens_kanji VARCHAR2(80) := fnd_message.get_string('PAY', 'PAY_JP_ITAX_NATIONAL_PENSION');
18 g_new_national_pens_kana VARCHAR2(80) := fnd_message.get_string('PAY', 'PAY_JP_ITAX_NATIONAL_PENS_KANA');
19 --
20 g_payroll_action_id number;
21 g_business_group_id number;
22 g_bg_dpnt_ref_type varchar2(30);
23 g_effective_date date;
24 g_payroll_id number;
25 g_soy date;
26 g_eoy date;
27 g_itax_organization_id number;
28 g_include_terminated_flag varchar2(1);
29 g_termination_date_from date;
30 g_termination_date_to date;
31 g_inherit_archive_flag varchar2(1);
32 g_rearchive_flag varchar2(1);
33 g_publication_period_status varchar2(1);
34 g_publication_start_date date;
35 g_publication_end_date date;
36 --
37 FUNCTION check_when_true(p_condition in boolean) return varchar2
38 is
39 begin
40 if p_condition then
41 return c_checked;
42 else
43 return null;
44 end if;
45 end check_when_true;
46 --
47 /* Commented out. bug.6168642
48 FUNCTION decode_value(
49 p_condition in boolean,
50 p_value1 in varchar2,
51 p_value2 in varchar2) return varchar2
52 is
53 begin
54 if p_condition then
55 return p_value1;
56 else
57 return p_value2;
58 end if;
59 end decode_value;
60 */
61 --
62 PROCEDURE jp_date(
63 p_date in date,
64 p_meiji out nocopy varchar2,
65 p_taishou out nocopy varchar2,
66 p_shouwa out nocopy varchar2,
67 p_heisei out nocopy varchar2,
68 p_year out nocopy varchar2,
69 p_month out nocopy varchar2,
70 p_day out nocopy varchar2)
71 is
72 l_era_code varchar2(1);
73 l_char_date varchar2(7);
74 begin
75 hr_utility.trace('jp_date: ' || p_date);
76 --
77 if p_date is not null then
78 l_char_date := hr_jp_standard_pkg.to_jp_char(p_date, 'EYYMMDD');
79 l_era_code := substr(l_char_date, 1, 1);
80 --
81 p_meiji := check_when_true(l_era_code = 'M');
82 p_taishou := check_when_true(l_era_code = 'T');
83 p_shouwa := check_when_true(l_era_code = 'S');
84 p_heisei := check_when_true(l_era_code = 'H');
85 p_year := substr(l_char_date, 2, 2);
86 p_month := substr(l_char_date, 4, 2);
87 p_day := substr(l_char_date, 6, 2);
88 end if;
89 end jp_date;
90 --
91 -- +------------------------------------------------------------------------------------------------+
92 -- |< INITIALIZATION_CODE >-------------------------------------------------------------------------|
93 -- |This sets the global contexts(PAYROLL_ID) for formulas.-----------------------------------------|
94 -- +------------------------------------------------------------------------------------------------+
95 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
96 IS
97 c_proc CONSTANT VARCHAR2(61) := c_package || 'INITIALIZATION_CODE';
98 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
99 BEGIN
100 hr_utility.set_location('Entering: ' || c_proc, 10);
101 --
102 if g_payroll_action_id is null or g_payroll_action_id <> p_payroll_action_id then
103 hr_utility.trace('cache not available.');
104 --
105 select business_group_id,
106 effective_date,
107 legislative_parameters
108 into g_business_group_id,
109 g_effective_date,
110 l_legislative_parameters
111 from pay_payroll_actions
112 where payroll_action_id = p_payroll_action_id;
113 --
114 -- "Assignment Set" cannot be implemented because of localization strategy
115 -- at the moment.
116 --
117 g_soy := trunc(g_effective_date, 'YYYY');
118 g_eoy := add_months(g_soy, 12) - 1;
119 g_payroll_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ID', l_legislative_parameters));
120 g_itax_organization_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ITAX_ORGANIZATION_ID', l_legislative_parameters));
121 g_include_terminated_flag := nvl(pay_core_utils.get_parameter('INCLUDE_TERMINATED_FLAG', l_legislative_parameters), 'Y');
122 g_termination_date_from := fnd_date.canonical_to_date(pay_core_utils.get_parameter('TERMINATION_DATE_FROM', l_legislative_parameters));
123 g_termination_date_to := fnd_date.canonical_to_date(pay_core_utils.get_parameter('TERMINATION_DATE_TO', l_legislative_parameters));
124 g_inherit_archive_flag := nvl(pay_core_utils.get_parameter('INHERIT_ARCHIVE_FLAG', l_legislative_parameters), 'Y');
125 g_rearchive_flag := nvl(pay_core_utils.get_parameter('REARCHIVE_FLAG', l_legislative_parameters), 'N');
126 g_publication_period_status := nvl(pay_core_utils.get_parameter('PUBLICATION_PERIOD_STATUS', l_legislative_parameters), 'C');
127 g_publication_start_date := fnd_date.canonical_to_date(pay_core_utils.get_parameter('PUBLICATION_START_DATE', l_legislative_parameters));
128 g_publication_end_date := fnd_date.canonical_to_date(pay_core_utils.get_parameter('PUBLICATION_END_DATE', l_legislative_parameters));
129 g_bg_dpnt_ref_type := per_jp_ctr_utility_pkg.bg_itax_dpnt_ref_type(g_business_group_id);
130 --
131 g_payroll_action_id := p_payroll_action_id;
132 end if;
133 --
134 hr_utility.set_location('Leaving: ' || c_proc, 20);
135 END INITIALIZATION_CODE;
136 --
137 -- +------------------------------------------------------------------------------------------------+
138 -- |< RANGE_CODE >----------------------------------------------------------------------------------|
139 -- |The "p_sqlstr" fetches person_id with assignment action that should do the archive.-------------|
140 -- +------------------------------------------------------------------------------------------------+
141 PROCEDURE RANGE_CODE(
142 p_payroll_action_id IN NUMBER,
143 p_sqlstr OUT NOCOPY VARCHAR2)
144 IS
145 c_proc CONSTANT VARCHAR2(61) := c_package || 'RANGE_CODE';
146 BEGIN
147 hr_utility.set_location('Entering: ' || c_proc, 10);
148 --
149 initialization_code(p_payroll_action_id);
150 --
151 p_sqlstr :=
152 'SELECT DISTINCT per.person_id
153 FROM pay_payroll_actions ppa,
154 per_all_people_f per
155 WHERE ppa.payroll_action_id = :payroll_action_id
156 AND per.business_group_id + 0 = ppa.business_group_id
157 ORDER BY per.person_id';
158 --
159 hr_utility.set_location('Leaving: ' || c_proc, 20);
160 END RANGE_CODE;
161 --
162 -- +------------------------------------------------------------------------------------------------+
163 -- |< ASSIGNMENT_ACTION_CODE >----------------------------------------------------------------------|
164 -- |This package procedure further restricts and creates the assignment action.---------------------|
165 -- +------------------------------------------------------------------------------------------------+
166 PROCEDURE ASSIGNMENT_ACTION_CODE(
167 p_payroll_action_id IN NUMBER,
168 p_start_person_id IN NUMBER,
169 p_end_person_id IN NUMBER,
170 p_chunk IN NUMBER)
171 IS
172 c_proc CONSTANT VARCHAR2(61) := c_package || 'ASSIGNMENT_ACTION_CODE';
173 l_locking_action_id NUMBER;
174 --
175 -- Currently, 1 assignment action is created for each assignment,
176 -- then multiple certificate archive data is created under each assignment action.
177 -- This will be changed "1 assignment action for each certificate"
178 -- in next major release.
179 --
180 -- This archive process not only archives data but also validates existing archive data.
181 -- Performance against multiple records of PER_ALL_ASSIGNMENTS_F with the same ASSIGNMENT_ID
182 -- can be ignored because "exists" validation is performed only once for each ASSIGNMENT_ID.
183 --
184 -- Cursor is changed not to check archive data as follows.
185 -- Assignment action is created for each assignment returned by view
186 -- pay_jp_wic_assacts_v for usability.
187 -- It is recommended not to specify "Payroll" and "Withholding Agent"
188 -- parameters which will be confusing for user when archive is created
189 -- for inappropriate assact in the middle of the year.
190 -- "Assignment Set" will be the best solution.
191 --
192 cursor csr_asg is
193 select distinct
194 asg.assignment_id
195 from per_all_assignments_f asg
196 where asg.person_id
197 between p_start_person_id and p_end_person_id
198 and asg.business_group_id + 0 = g_business_group_id
199 and asg.assignment_type = 'E'
200 and asg.effective_start_date <= g_effective_date
201 and asg.effective_end_date >= g_soy
202 and (
203 /*
204 --
205 -- The reason why this SQL uses exists statement, not using UNION ALL,
206 -- is the latter "exists" statement performance is worse than former
207 -- "exists" statement. If former "exists" statement returns "true",
208 -- then latter "exists" is not checked.
209 --
210 exists(
211 select null
212 from pay_jp_itax_person_v2 person,
213 pay_jp_itax_arch_v2 arch
214 where person.assignment_id = asg.assignment_id
215 and person.effective_date
216 between g_soy and g_effective_date
217 and ((g_itax_organization_id is null) or (person.itax_organization_id = g_itax_organization_id))
218 and ( ( g_include_terminated_flag = 'Y'
219 and ( (g_termination_date_from is null and g_termination_date_to is null)
220 or person.actual_termination_date
221 between nvl(g_termination_date_from, person.actual_termination_date)
222 and nvl(g_termination_date_to, person.actual_termination_date)
223 )
224 )
225 or (g_include_terminated_flag = 'N' and person.actual_termination_date is null)
226 )
227 and arch.action_context_id = person.action_context_id
228 and arch.effective_date = person.effective_date
229 and ((g_payroll_id is null) or (arch.payroll_id = g_payroll_id))
230 )
231 or
232 */
233 exists(
234 select null
235 from pay_jp_wic_assacts_v wic
236 where wic.assignment_id = asg.assignment_id
237 and wic.effective_date
238 between g_soy and g_effective_date
239 and ((g_itax_organization_id is null) or (wic.itax_organization_id = g_itax_organization_id))
240 and ((g_payroll_id is null) or (wic.payroll_id = g_payroll_id))
241 and ( ( g_include_terminated_flag = 'Y'
242 and ( (g_termination_date_from is null and g_termination_date_to is null)
243 or wic.actual_termination_date
244 between nvl(g_termination_date_from, wic.actual_termination_date)
245 and nvl(g_termination_date_to, wic.actual_termination_date)
246 )
247 )
248 or (g_include_terminated_flag = 'N' and wic.actual_termination_date is null)
249 )
250 /* Following check is removed for inappropriate archive data deletion in ARCHIVE_CODE
251 and (
252 g_rearchive_flag = 'Y'
253 or not exists(
254 select null
255 from pay_jp_itax_arch_v2 v
256 where v.assignment_id = wic.assignment_id
257 -- effective_date validation is for performance reason.
258 and v.effective_date = wic.effective_date
259 and v.assignment_action_id = wic.assignment_action_id)
260 )
261 */
262 )
263 );
264 BEGIN
265 hr_utility.set_location('Entering: ' || c_proc, 10);
266 --
267 initialization_code(p_payroll_action_id);
268 --
269 -- RANGE_PERSON_ID, PAY_ACTION_PARAMETER_GROUPS(PAY_REPORT_FORMAT_PARAMETERS)
270 -- is not supported in this release.
271 --
272 FOR l_asg in csr_asg LOOP
273 hr_utility.set_location('Entering: ' || c_proc, 20);
274 --
275 SELECT pay_assignment_actions_s.nextval
276 INTO l_locking_action_id
277 FROM dual;
278 --
279 hr_nonrun_asact.insact(
280 lockingactid => l_locking_action_id,
281 assignid => l_asg.assignment_id,
282 pactid => p_payroll_action_id,
283 chunk => p_chunk);
284 END LOOP;
285 --
286 hr_utility.set_location('Leaving: ' || c_proc, 100);
287 END ASSIGNMENT_ACTION_CODE;
288 -- +------------------------------------------------------------------------------------------------+
289 -- |< create_action_information >-------------------------------------------------------------------|
290 -- |Private procedure.------------------------------------------------------------------------------|
291 -- +------------------------------------------------------------------------------------------------+
292 procedure create_action_information(p_action_information_rec in out nocopy pay_action_information%rowtype)
293 is
294 l_id number;
295 l_ovn number;
296 begin
297 pay_action_information_api.create_action_information(
298 P_VALIDATE => false,
299 P_ACTION_CONTEXT_ID => p_action_information_rec.ACTION_CONTEXT_ID,
300 P_ACTION_CONTEXT_TYPE => p_action_information_rec.ACTION_CONTEXT_TYPE,
301 P_ACTION_INFORMATION_CATEGORY => p_action_information_rec.ACTION_INFORMATION_CATEGORY,
302 P_EFFECTIVE_DATE => p_action_information_rec.EFFECTIVE_DATE,
303 P_ASSIGNMENT_ID => p_action_information_rec.ASSIGNMENT_ID,
304 P_ACTION_INFORMATION1 => p_action_information_rec.ACTION_INFORMATION1,
305 P_ACTION_INFORMATION2 => p_action_information_rec.ACTION_INFORMATION2,
306 P_ACTION_INFORMATION3 => p_action_information_rec.ACTION_INFORMATION3,
307 P_ACTION_INFORMATION4 => p_action_information_rec.ACTION_INFORMATION4,
308 P_ACTION_INFORMATION5 => p_action_information_rec.ACTION_INFORMATION5,
309 P_ACTION_INFORMATION6 => p_action_information_rec.ACTION_INFORMATION6,
310 P_ACTION_INFORMATION7 => p_action_information_rec.ACTION_INFORMATION7,
311 P_ACTION_INFORMATION8 => p_action_information_rec.ACTION_INFORMATION8,
312 P_ACTION_INFORMATION9 => p_action_information_rec.ACTION_INFORMATION9,
313 P_ACTION_INFORMATION10 => p_action_information_rec.ACTION_INFORMATION10,
314 P_ACTION_INFORMATION11 => p_action_information_rec.ACTION_INFORMATION11,
315 P_ACTION_INFORMATION12 => p_action_information_rec.ACTION_INFORMATION12,
316 P_ACTION_INFORMATION13 => p_action_information_rec.ACTION_INFORMATION13,
317 P_ACTION_INFORMATION14 => p_action_information_rec.ACTION_INFORMATION14,
318 P_ACTION_INFORMATION15 => p_action_information_rec.ACTION_INFORMATION15,
319 P_ACTION_INFORMATION16 => p_action_information_rec.ACTION_INFORMATION16,
320 P_ACTION_INFORMATION17 => p_action_information_rec.ACTION_INFORMATION17,
321 P_ACTION_INFORMATION18 => p_action_information_rec.ACTION_INFORMATION18,
322 P_ACTION_INFORMATION19 => p_action_information_rec.ACTION_INFORMATION19,
323 P_ACTION_INFORMATION20 => p_action_information_rec.ACTION_INFORMATION20,
324 P_ACTION_INFORMATION21 => p_action_information_rec.ACTION_INFORMATION21,
325 P_ACTION_INFORMATION22 => p_action_information_rec.ACTION_INFORMATION22,
326 P_ACTION_INFORMATION23 => p_action_information_rec.ACTION_INFORMATION23,
327 P_ACTION_INFORMATION24 => p_action_information_rec.ACTION_INFORMATION24,
328 P_ACTION_INFORMATION25 => p_action_information_rec.ACTION_INFORMATION25,
329 P_ACTION_INFORMATION26 => p_action_information_rec.ACTION_INFORMATION26,
330 P_ACTION_INFORMATION27 => p_action_information_rec.ACTION_INFORMATION27,
331 P_ACTION_INFORMATION28 => p_action_information_rec.ACTION_INFORMATION28,
332 P_ACTION_INFORMATION29 => p_action_information_rec.ACTION_INFORMATION29,
333 P_ACTION_INFORMATION30 => p_action_information_rec.ACTION_INFORMATION30,
334 P_ACTION_INFORMATION_ID => p_action_information_rec.action_information_id,
335 P_OBJECT_VERSION_NUMBER => p_action_information_rec.object_version_number);
336 end create_action_information;
337 -- +------------------------------------------------------------------------------------------------+
338 -- |< ARCHIVE_CODE >--------------------------------------------------------------------------------|
339 -- |This sets the context for the assignment level contexts(ASSIGNMENT_ID).-------------------------|
340 -- +------------------------------------------------------------------------------------------------+
341 PROCEDURE ARCHIVE_CODE(
342 p_assignment_action_id IN NUMBER,
343 p_effective_date IN DATE)
344 IS
345 c_proc CONSTANT VARCHAR2(61) := c_package || 'ARCHIVE_CODE';
346 --
347 l_assignment_id number;
348 l_archive boolean;
349 l_action_information_ids fnd_table_of_number := fnd_table_of_number();
350 --
351 l_dummy varchar2(255);
352 l_certificate_info pay_jp_wic_pkg.t_certificate_info;
353 l_withholding_tax_info pay_jp_wic_pkg.t_tax_info;
354 l_submission_required_flag varchar2(1);
355 l_prev_job_info pay_jp_wic_pkg.t_prev_job_info;
356 l_adr_effective_date date;
357 --
358 l_itw_user_desc_kanji varchar2(32767);
359 l_itw_descriptions pay_jp_wic_pkg.t_descriptions;
360 l_wtm_user_desc varchar2(32767);
361 l_wtm_user_desc_kanji varchar2(32767);
362 l_wtm_user_desc_kana varchar2(32767);
363 l_wtm_descriptions pay_jp_wic_pkg.t_descriptions;
364 --
365 l_itw_system_desc1_kanji varchar2(32767);
366 l_itw_system_desc2_kanji varchar2(32767);
367 l_wtm_system_desc_kanji varchar2(32767);
368 l_wtm_system_desc_kana varchar2(32767);
369 l_varchar2_tbl hr_jp_standard_pkg.t_varchar2_tbl;
370 --
371 l_person_rec pay_action_information%rowtype;
372 l_arch_rec pay_action_information%rowtype;
373 l_tax_rec pay_action_information%rowtype;
374 l_other_rec pay_action_information%rowtype;
375 l_other2_rec pay_action_information%rowtype;
376 --
377 cursor csr_wic(cp_assignment_id number) is
378 select wic.assignment_action_id,
379 wic.action_sequence,
380 wic.payroll_id,
381 nvl(pay.prl_information1, g_bg_dpnt_ref_type) dpnt_ref_type,
382 nvl(fnd_date.canonical_to_date(pay_core_utils.get_parameter('ITAX_DPNT_EFFECTIVE_DATE', wic.legislative_parameters)),
383 wic.effective_date) dpnt_effective_date,
384 wic.effective_date,
385 wic.date_earned,
386 wic.itax_organization_id,
387 wic.itax_category,
388 wic.itax_yea_category,
389 wic.person_id,
390 wic.date_start,
391 wic.leaving_reason,
392 wic.actual_termination_date,
393 wic.employment_category
394 from pay_jp_wic_assacts_v wic,
395 pay_all_payrolls_f pay
396 where wic.assignment_id = cp_assignment_id
397 and wic.effective_date
398 between g_soy and g_effective_date
399 and ((g_itax_organization_id is null) or (wic.itax_organization_id = g_itax_organization_id))
400 and ((g_payroll_id is null) or (wic.payroll_id = g_payroll_id))
401 --
402 -- Do not check termination_date in ARCHIVE_CODE here
403 -- which has already been validated in ASSIGNMENT_ACTION_CODE.
404 --
405 /*
406 and ( ( g_include_terminated_flag = 'Y'
407 and ( (g_termination_date_from is null and g_termination_date_to is null)
408 or wic.actual_termination_date
409 between nvl(g_termination_date_from, wic.actual_termination_date)
410 and nvl(g_termination_date_to, wic.actual_termination_date)
411 )
412 )
413 or (g_include_terminated_flag = 'N' and wic.actual_termination_date is null)
414 )
415 */
416 and pay.payroll_id = wic.payroll_id
417 and wic.effective_date
418 between pay.effective_start_date and pay.effective_end_date
419 order by wic.effective_date;
420 --
421 -- Used when g_rearchive_flag = 'N'
422 -- to check whether the assact is already archived or not.
423 --
424 cursor csr_prev_archive(
425 cp_assignment_id number,
426 cp_effective_date date,
427 cp_assignment_action_id number) is
428 select person.action_information_id
429 from pay_jp_itax_arch_v2 arch,
430 pay_jp_itax_person_v2 person
431 where arch.assignment_id = cp_assignment_id
432 -- effective_date validation is for performance reason.
433 and arch.effective_date = cp_effective_date
434 and arch.assignment_action_id = cp_assignment_action_id
435 and person.action_context_id = arch.action_context_id
436 and person.effective_date = arch.effective_date;
437 --
438 -- Used when g_inherit_archive_flag = 'Y'
439 -- to inherit description information under new archive assact.
440 -- The archive derived by this cursor will be removed.
441 --
442 cursor csr_replace_archive(
443 cp_assignment_id number,
444 cp_itax_organization_id number,
445 cp_itax_category varchar2) is
446 select person.action_information_id,
447 person.action_context_id,
448 person.effective_date,
449 other2.ITW_USER_DESC_KANJI,
450 other2.WTM_USER_DESC_KANJI,
451 other2.WTM_USER_DESC_KANA
452 from pay_jp_itax_person_v2 person,
453 pay_jp_itax_arch_v2 arch,
454 pay_jp_itax_other2_v2 other2
455 where person.assignment_id = cp_assignment_id
456 and person.effective_date
457 between g_soy and g_eoy
458 and person.itax_organization_id = cp_itax_organization_id
459 and arch.action_context_id = person.action_context_id
460 and arch.effective_date = person.effective_date
461 and arch.itax_category = cp_itax_category
462 and other2.action_context_id = person.action_context_id
463 and other2.effective_date = person.effective_date
464 for update of person.action_information_id nowait
465 order by person.effective_date;
466 --
467 -- When g_rearchive_flag = 'Y' then all records derived by the
468 -- following cursor will be deleted.
469 -- When g_rearchive_flag = 'N' then all records except for records
470 -- derived by csr_prev_archive or created by current assact
471 -- stored in "cp_action_information_ids" will be deleted.
472 --
473 cursor csr_delete_archives(
474 cp_assignment_id number,
475 cp_action_information_ids fnd_table_of_number) is
476 select person.action_information_id,
477 person.action_context_id,
478 person.effective_date
479 from pay_jp_itax_person_v2 person,
480 pay_jp_itax_arch_v2 arch
481 where person.assignment_id = cp_assignment_id
482 and person.effective_date
483 between g_soy and g_effective_date
484 -- bug.5657929
485 and person.action_information_category = 'JP_ITAX_PERSON'
486 and person.action_information_id not in (
487 select *
488 from table(cp_action_information_ids))
489 and ((g_itax_organization_id is null) or (person.itax_organization_id = g_itax_organization_id))
490 and arch.action_context_id = person.action_context_id
491 and arch.effective_date = person.effective_date
492 and ((g_payroll_id is null) or (arch.payroll_id = g_payroll_id))
493 for update of person.action_information_id nowait;
494 --
495 cursor csr_per(
496 cp_assignment_id number,
497 cp_effective_date date,
498 cp_adr_effective_date date) is
499 select per.employee_number,
500 per.last_name last_name_kana,
501 per.first_name first_name_kana,
502 per.per_information18 last_name_kanji,
503 per.per_information19 first_name_kanji,
504 per.sex,
505 per.date_of_birth,
506 nvl(adrr.address_id, adrc.address_id) address_id,
507 rtrim(substrb(decode(adrr.address_id, null,
508 adrc.region_1 || adrc.region_2 || adrc.region_3,
509 adrr.region_1 || adrr.region_2 || adrr.region_3), 1, 240)) address_kana,
510 rtrim(substrb(decode(adrr.address_id, null,
511 adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
512 adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) address_kanji,
513 decode(adrr.address_id, null, adrc.country, adrr.country) country,
514 decode(adrr.address_id, null, adrc.town_or_city, adrr.town_or_city) district_code,
515 asg.organization_id
516 from per_all_assignments_f asg,
517 per_all_people_f per,
518 per_addresses adrr,
519 per_addresses adrc
520 where asg.assignment_id = cp_assignment_id
521 and cp_effective_date
522 between asg.effective_start_date and asg.effective_end_date
523 and per.person_id = asg.person_id
524 and cp_effective_date
525 between per.effective_start_date and per.effective_end_date
526 and adrr.person_id(+) = per.person_id
527 and adrr.address_type(+) = 'JP_R'
528 and cp_adr_effective_date
529 between adrr.date_from(+) and nvl(adrr.date_to(+), cp_adr_effective_date)
530 and adrc.person_id(+) = per.person_id
531 and adrc.address_type(+) = 'JP_C'
532 and cp_adr_effective_date
533 between adrc.date_from(+) and nvl(adrc.date_to(+), cp_adr_effective_date);
534 l_per_rec csr_per%rowtype;
535 --
536 cursor csr_swot(cp_itax_organization_id number) is
537 select hoi2.org_information3 reference_number,
538 lpad(hoi2.org_information4, 10, '0') reference_number1,
539 lpad(hoi2.org_information5, 12, '0') reference_number2,
540 rtrim(substrb(hoi1.org_information6 || hoi1.org_information7 || hoi1.org_information8, 1, 240))
541 salary_payer_address_kanji,
542 hoi1.org_information1 salary_payer_name_kanji,
543 hoi1.org_information12 salary_payer_telephone_number,
544 hoi2.org_information2 tax_office_number
545 from hr_all_organization_units hou,
546 hr_organization_information hoi1,
547 hr_organization_information hoi2
548 where hou.organization_id = cp_itax_organization_id
549 and hoi1.organization_id(+) = hou.organization_id
550 and hoi1.org_information_context(+) = 'JP_TAX_SWOT_INFO'
551 and hoi2.organization_id(+) = hou.organization_id
552 and hoi2.org_information_context(+) = 'JP_ITAX_WITHHELD_INFO';
553 l_swot_rec csr_swot%rowtype;
554 --
555 procedure concat_description(
556 p_src in out nocopy varchar2,
557 p_description in varchar2,
558 p_separator in varchar2)
559 is
560 begin
561 if p_description is not null then
562 if p_src is not null then
563 p_src := p_src || p_separator;
564 end if;
565 --
566 p_src := p_src || p_description;
567 end if;
568 end concat_description;
569 --
570 function get_index_at(
571 p_varchar2_tbl in hr_jp_standard_pkg.t_varchar2_tbl,
572 p_index in number) return varchar2
573 is
574 begin
575 if p_varchar2_tbl.exists(p_index) then
576 return p_varchar2_tbl(p_index);
577 else
578 return null;
579 end if;
580 end get_index_at;
581 --
582 /*
583 procedure log_description(
584 p_system_description in varchar2,
585 p_user_description in varchar2,
586 p_is_zenkaku in boolean)
587 is
588 l_description varchar2(32767);
589 l_separator varchar2(10) := ',';
590 begin
591 if p_system_description is null then
592 l_description := p_user_description;
593 elsif p_user_description is null then
594 l_description := p_system_description;
595 else
596 if p_is_zenkaku then
597 l_separator := hr_jp_standard_pkg.to_zenkaku(l_separator);
598 end if;
599 --
600 l_description := p_system_description || l_separator || p_user_description;
601 end if;
602 --
603 if length(l_description) > 100 then
604 fnd_message.set_name('PAY', 'PAY_JP_ITW_ARCHIVE_DESC_WARN');
605 fnd_message.set_token('ACTION_INFORMATION_ID', null);
606 fnd_message.set_token('EMPLOYEE_NUMBER', null);
607 fnd_message.set_token('FULL_NAME', null);
608 fnd_message.set_token('SWOT_NAME', null);
609 fnd_message.set_token('TAX_TYPE', null);
610 fnd_message.set_token('DESCRIPTION', l_description);
611 fnd_file.put_line(fnd_file.log, fnd_message.get);
612 end if;
613 end log_description;
614 */
615 --
616 procedure delete_archive(
617 p_action_information_id in number,
618 p_action_context_id in number,
619 p_effective_date in date)
620 is
621 l_count number;
622 begin
623 --
624 -- Currently, the unique key is (action_context_id, effective_date),
625 -- which needs to be changed to (action_context_id) to fix potential bugs.
626 --
627 select count(*)
628 into l_count
629 from pay_jp_itax_person_v2
630 where action_context_id = p_action_context_id
631 and action_information_id <> p_action_information_id;
632 --
633 -- When multiple archives exist, delete the correponding action info only.
634 -- If not, rollback the assignment action itself.
635 --
636 if l_count > 0 then
637 delete
638 from pay_action_information
639 where action_context_type = 'AAP'
640 and action_context_id = p_action_context_id
641 and effective_date = p_effective_date;
642 else
643 py_rollback_pkg.rollback_ass_action(p_action_context_id);
644 end if;
645 --
646 fnd_message.set_name('PAY', 'PAY_JP_ITW_ARCHIVE_DELETED');
647 fnd_message.set_token('ACTION_INFORMATION_ID', p_action_information_id);
648 fnd_message.set_token('EMPLOYEE_NUMBER', null);
649 fnd_message.set_token('FULL_NAME', null);
650 fnd_message.set_token('SWOT_NAME', null);
651 fnd_message.set_token('TAX_TYPE', null);
652 fnd_file.put_line(fnd_file.log, fnd_message.get);
653 end delete_archive;
654 BEGIN
655 hr_utility.set_location('Entering: ' || c_proc, 10);
656 --
657 -- Lock first(not latest) record on PER_ALL_ASSIGNMENTS_F
658 -- to suppress other PAYJPITW processes archive the same information
659 -- at the same time.
660 --
661 select asg.assignment_id
662 into l_assignment_id
663 from pay_assignment_actions paa,
664 per_all_assignments_f asg
665 where paa.assignment_action_id = p_assignment_action_id
666 and asg.assignment_id = paa.assignment_id
667 and asg.effective_start_date <= g_effective_date
668 and asg.effective_end_date >= g_soy
669 and not exists(
670 select null
671 from per_all_assignments_f asg2
672 where asg2.assignment_id = asg.assignment_id
673 and asg2.effective_start_date < asg.effective_start_date
674 and asg2.effective_end_date >= g_soy)
675 for update of asg.assignment_id nowait;
676 --
677 -- Fetch same year's data
678 --
679 for l_wic_rec in csr_wic(l_assignment_id) loop
680 hr_utility.set_location(c_proc, 20);
681 --
682 l_archive := true;
683 --
684 -- Check whether the wic assact is already archived or not
685 -- when g_rearchive_flag = 'N'. If found, skip processing
686 -- this wic assact.
687 --
688 if g_rearchive_flag = 'N' then
689 for l_archive_rec in csr_prev_archive(l_assignment_id, l_wic_rec.effective_date, l_wic_rec.assignment_action_id) loop
690 l_archive := false;
691 l_action_information_ids.extend;
692 l_action_information_ids(l_action_information_ids.last) := l_archive_rec.action_information_id;
693 end loop;
694 end if;
695 --
696 if l_archive then
697 l_adr_effective_date := nvl(l_wic_rec.actual_termination_date, g_eoy + 1);
698 --
699 -- Personal Information
700 --
701 open csr_per(l_assignment_id, l_wic_rec.effective_date, l_adr_effective_date);
702 fetch csr_per into l_per_rec;
703 if csr_per%notfound then
704 close csr_per;
705 raise no_data_found;
706 end if;
707 close csr_per;
708 --
709 hr_utility.set_location(c_proc, 30);
710 --
711 -- Employer Information
712 --
713 open csr_swot(l_wic_rec.itax_organization_id);
714 fetch csr_swot into l_swot_rec;
715 if csr_swot%notfound then
716 close csr_swot;
717 raise no_data_found;
718 end if;
719 close csr_swot;
720 --
721 hr_utility.set_location(c_proc, 40);
722 --
723 -- Certificate Information
724 -- Never call "pay_jp_wic_pkg.get_certificate_info" multiple times
725 -- which will cause severe performance loss.
726 -- Also never call debugging "pay_jp_wic_pkg.get_certificate_info"
727 -- which is only for debugging purpose.
728 --
729 pay_jp_wic_pkg.get_certificate_info(
730 p_assignment_action_id => l_wic_rec.assignment_action_id,
731 p_assignment_id => l_assignment_id,
732 p_action_sequence => l_wic_rec.action_sequence,
733 p_business_group_id => g_business_group_id,
734 p_effective_date => l_wic_rec.effective_date,
735 p_date_earned => l_wic_rec.date_earned,
736 p_itax_organization_id => l_wic_rec.itax_organization_id,
737 p_itax_category => l_wic_rec.itax_category,
738 p_itax_yea_category => l_wic_rec.itax_yea_category,
739 p_dpnt_ref_type => l_wic_rec.dpnt_ref_type,
740 p_dpnt_effective_date => l_wic_rec.dpnt_effective_date,
741 p_person_id => l_wic_rec.person_id,
742 p_sex => l_per_rec.sex,
743 p_date_of_birth => l_per_rec.date_of_birth,
744 p_leaving_reason => l_wic_rec.leaving_reason,
745 p_last_name_kanji => l_per_rec.last_name_kanji,
746 p_last_name_kana => l_per_rec.last_name_kana,
747 p_employment_category => l_wic_rec.employment_category,
748 p_certificate_info => l_certificate_info,
749 p_submission_required_flag => l_submission_required_flag,
750 p_prev_job_info => l_prev_job_info,
751 p_withholding_tax_info => l_withholding_tax_info,
752 p_itw_description => l_itw_user_desc_kanji,
753 p_itw_descriptions => l_itw_descriptions,
754 p_wtm_description => l_wtm_user_desc,
755 p_wtm_descriptions => l_wtm_descriptions);
756 --
757 l_wtm_user_desc_kanji := l_wtm_user_desc;
758 l_wtm_user_desc_kana := l_wtm_user_desc;
759 --
760 hr_utility.set_location(c_proc, 50);
761 --
762 -- Get old description and replace system derived description with those if available,
763 -- then delete all archive data.
764 -- This specification seems to be confusing for user, there's possibility to
765 -- comment out the following code.
766 -- Fixed to replace not only ITW but also WTM description.
767 -- Note old override flag is not checked.
768 --
769 for l_archive_rec in csr_replace_archive(l_assignment_id,
770 l_wic_rec.itax_organization_id,
771 l_wic_rec.itax_category) loop
772 hr_utility.set_location(c_proc, 61);
773 --
774 if g_inherit_archive_flag = 'Y' then
775 l_itw_user_desc_kanji := l_archive_rec.itw_user_desc_kanji;
776 l_wtm_user_desc_kanji := l_archive_rec.wtm_user_desc_kanji;
777 l_wtm_user_desc_kana := l_archive_rec.wtm_user_desc_kana;
778 /*
779 --
780 fnd_message.set_name('PAY', 'PAY_JP_ITW_ARCHIVE_INHERITED');
781 fnd_message.set_token('ACTION_INFORMATION_ID', null);
782 fnd_message.set_token('EMPLOYEE_NUMBER', null);
783 fnd_message.set_token('FULL_NAME', null);
784 fnd_message.set_token('SWOT_NAME', null);
785 fnd_message.set_token('TAX_TYPE', null);
786 fnd_message.set_token('SRC_ACTION_INFORMATION_ID', null);
787 fnd_file.put_line(fnd_file.log, fnd_message.get);
788 */
789 end if;
790 --
791 delete_archive(
792 l_archive_rec.action_information_id,
793 l_archive_rec.action_context_id,
794 l_archive_rec.effective_date);
795 end loop;
796 --
797 hr_utility.set_location(c_proc, 70);
798 --
799 -- JP_ITAX_PERSON
800 --
801 l_person_rec.action_context_id := p_assignment_action_id;
802 l_person_rec.action_context_type := 'AAP';
803 l_person_rec.action_information_category := 'JP_ITAX_PERSON';
804 l_person_rec.effective_date := l_wic_rec.effective_date;
805 l_person_rec.assignment_id := l_assignment_id;
806 l_person_rec.action_information1 := l_per_rec.employee_number;
807 l_person_rec.action_information2 := l_per_rec.last_name_kana;
808 l_person_rec.action_information3 := l_per_rec.first_name_kana;
809 l_person_rec.action_information4 := l_per_rec.last_name_kanji;
810 l_person_rec.action_information5 := l_per_rec.first_name_kanji;
811 l_person_rec.action_information6 := l_per_rec.sex;
812 l_person_rec.action_information7 := fnd_number.number_to_canonical(l_per_rec.address_id);
813 l_person_rec.action_information8 := l_per_rec.address_kana;
814 l_person_rec.action_information9 := l_per_rec.address_kanji;
815 l_person_rec.action_information10 := l_per_rec.country;
816 l_person_rec.action_information11 := fnd_date.date_to_canonical(l_wic_rec.date_start);
817 l_person_rec.action_information12 := l_wic_rec.leaving_reason;
818 l_person_rec.action_information13 := fnd_date.date_to_canonical(l_per_rec.date_of_birth);
819 jp_date(
820 p_date => l_per_rec.date_of_birth,
821 p_meiji => l_person_rec.action_information14,
822 p_taishou => l_person_rec.action_information15,
823 p_shouwa => l_person_rec.action_information16,
824 p_heisei => l_person_rec.action_information17,
825 p_year => l_person_rec.action_information18,
826 p_month => l_person_rec.action_information19,
827 p_day => l_person_rec.action_information20);
828 /* Bug.6208573. 21/22/23 obsolete.
829 jp_date(
830 p_date => nvl(l_wic_rec.actual_termination_date, l_wic_rec.date_start),
831 p_meiji => l_dummy,
832 p_taishou => l_dummy,
833 p_shouwa => l_dummy,
834 p_heisei => l_dummy,
835 p_year => l_person_rec.action_information21,
836 p_month => l_person_rec.action_information22,
837 p_day => l_person_rec.action_information23);
838 */
839 l_person_rec.action_information24 := fnd_number.number_to_canonical(l_wic_rec.itax_organization_id);
840 l_person_rec.action_information25 := fnd_date.date_to_canonical(l_wic_rec.actual_termination_date);
841 l_person_rec.action_information26 := fnd_number.number_to_canonical(l_per_rec.organization_id);
842 l_person_rec.action_information28 := l_per_rec.district_code;
843 --
844 -- JP_ITAX_ARCH
845 --
846 l_arch_rec.action_context_id := p_assignment_action_id;
847 l_arch_rec.action_context_type := 'AAP';
848 l_arch_rec.action_information_category := 'JP_ITAX_ARCH';
849 l_arch_rec.effective_date := l_wic_rec.effective_date;
850 l_arch_rec.assignment_id := l_assignment_id;
851 l_arch_rec.action_information1 := fnd_number.number_to_canonical(l_wic_rec.person_id);
852 l_arch_rec.action_information2 := fnd_number.number_to_canonical(l_wic_rec.assignment_action_id);
853 l_arch_rec.action_information3 := fnd_number.number_to_canonical(l_wic_rec.payroll_id);
854 l_arch_rec.action_information4 := l_swot_rec.reference_number;
855 l_arch_rec.action_information5 := l_swot_rec.reference_number1;
856 l_arch_rec.action_information6 := l_swot_rec.reference_number2;
857 l_arch_rec.action_information7 := fnd_number.number_to_canonical(l_wic_rec.action_sequence);
858 l_arch_rec.action_information8 := l_swot_rec.salary_payer_address_kanji;
859 l_arch_rec.action_information9 := l_swot_rec.salary_payer_name_kanji;
860 l_arch_rec.action_information10 := l_swot_rec.salary_payer_telephone_number;
861 l_arch_rec.action_information11 := l_swot_rec.tax_office_number;
862 l_arch_rec.action_information12 := fnd_date.date_to_canonical(l_wic_rec.date_earned);
863 l_arch_rec.action_information13 := l_wic_rec.employment_category;
864 l_arch_rec.action_information14 := l_wic_rec.itax_category;
865 l_arch_rec.action_information15 := l_wic_rec.itax_yea_category;
866 l_arch_rec.action_information30 := l_submission_required_flag;
867 --
868 -- JP_ITAX_TAX
869 --
870 l_tax_rec.action_context_id := p_assignment_action_id;
871 l_tax_rec.action_context_type := 'AAP';
872 l_tax_rec.action_information_category := 'JP_ITAX_TAX';
873 l_tax_rec.effective_date := l_wic_rec.effective_date;
874 l_tax_rec.assignment_id := l_assignment_id;
875 l_tax_rec.action_information1 := fnd_number.number_to_canonical(l_certificate_info.tax_info.taxable_income);
876 l_tax_rec.action_information2 := fnd_number.number_to_canonical(l_certificate_info.net_taxable_income);
877 l_tax_rec.action_information3 := fnd_number.number_to_canonical(l_certificate_info.total_income_exempt);
878 l_tax_rec.action_information4 := fnd_number.number_to_canonical(l_certificate_info.tax_info.withholding_itax);
879 l_tax_rec.action_information5 := fnd_number.number_to_canonical(l_withholding_tax_info.withholding_itax);
880 l_tax_rec.action_information6 := fnd_number.number_to_canonical(l_certificate_info.spouse_sp_exempt);
881 l_tax_rec.action_information7 := fnd_number.number_to_canonical(l_certificate_info.tax_info.si_prem);
882 l_tax_rec.action_information8 := fnd_number.number_to_canonical(l_certificate_info.tax_info.mutual_aid_prem);
883 l_tax_rec.action_information9 := fnd_number.number_to_canonical(l_certificate_info.li_prem_exempt);
884 l_tax_rec.action_information10 := fnd_number.number_to_canonical(l_certificate_info.ai_prem_exempt);
885 l_tax_rec.action_information11 := fnd_number.number_to_canonical(l_certificate_info.housing_tax_reduction);
886 l_tax_rec.action_information12 := fnd_number.number_to_canonical(l_withholding_tax_info.itax_adjustment);
887 l_tax_rec.action_information13 := fnd_number.number_to_canonical(l_certificate_info.spouse_net_taxable_income);
888 l_tax_rec.action_information14 := fnd_number.number_to_canonical(l_certificate_info.pp_prem);
889 l_tax_rec.action_information15 := fnd_number.number_to_canonical(l_certificate_info.long_ai_prem);
890 l_tax_rec.action_information16 := fnd_number.number_to_canonical(l_certificate_info.tax_info.disaster_tax_reduction);
891 l_tax_rec.action_information17 := l_prev_job_info.salary_payer_address_kana;
892 l_tax_rec.action_information18 := l_prev_job_info.salary_payer_address_kanji;
893 l_tax_rec.action_information19 := l_prev_job_info.salary_payer_name_kana;
894 l_tax_rec.action_information20 := l_prev_job_info.salary_payer_name_kanji;
895 -- bug.6168642
896 -- Fixed to set null when no prev jobs exist.
897 -- l_tax_rec.action_information21 := decode_value(l_prev_job_info.foreign_address_flag = 'Y', '1', '0');
898 if l_prev_job_info.foreign_address_flag = 'Y' then
899 l_tax_rec.action_information21 := '1';
900 elsif l_prev_job_info.foreign_address_flag = 'N' then
901 l_tax_rec.action_information21 := '0';
902 else
903 l_tax_rec.action_information21 := null;
904 end if;
905 --
906 l_tax_rec.action_information22 := fnd_number.number_to_canonical(l_prev_job_info.taxable_income);
907 l_tax_rec.action_information23 := fnd_number.number_to_canonical(l_prev_job_info.itax);
908 l_tax_rec.action_information24 := fnd_number.number_to_canonical(l_prev_job_info.si_prem);
909 jp_date(
910 p_date => l_prev_job_info.termination_date,
911 p_meiji => l_dummy,
912 p_taishou => l_dummy,
913 p_shouwa => l_dummy,
914 p_heisei => l_dummy,
915 p_year => l_tax_rec.action_information25,
916 p_month => l_tax_rec.action_information26,
917 p_day => l_tax_rec.action_information27);
918 jp_date(
919 p_date => l_certificate_info.housing_residence_date,
920 p_meiji => l_dummy,
921 p_taishou => l_dummy,
922 p_shouwa => l_dummy,
923 p_heisei => l_dummy,
924 p_year => l_tax_rec.action_information28,
925 p_month => l_tax_rec.action_information29,
926 p_day => l_tax_rec.action_information30);
927 --
928 -- JP_ITAX_OTHER
929 --
930 l_other_rec.action_context_id := p_assignment_action_id;
931 l_other_rec.action_context_type := 'AAP';
932 l_other_rec.action_information_category := 'JP_ITAX_OTHER';
933 l_other_rec.effective_date := l_wic_rec.effective_date;
934 l_other_rec.assignment_id := l_assignment_id;
935 -- Dependent Spouse
936 l_other_rec.action_information1 := check_when_true(l_certificate_info.dep_spouse_exists_kou = 'Y');
937 l_other_rec.action_information2 := check_when_true(l_certificate_info.dep_spouse_not_exist_kou = 'Y');
938 l_other_rec.action_information3 := check_when_true(l_certificate_info.dep_spouse_exists_otsu = 'Y');
939 l_other_rec.action_information4 := check_when_true(l_certificate_info.dep_spouse_not_exist_otsu = 'Y');
940 l_other_rec.action_information5 := check_when_true(l_certificate_info.aged_spouse_exists = 'Y');
941 -- Dependents
942 l_other_rec.action_information6 := fnd_number.number_to_canonical(l_certificate_info.num_specifieds_kou);
943 l_other_rec.action_information7 := fnd_number.number_to_canonical(l_certificate_info.num_specifieds_otsu);
944 l_other_rec.action_information8 := fnd_number.number_to_canonical(l_certificate_info.num_aged_parents_lt);
945 l_other_rec.action_information9 := fnd_number.number_to_canonical(l_certificate_info.num_ageds_kou);
946 l_other_rec.action_information10 := fnd_number.number_to_canonical(l_certificate_info.num_ageds_otsu);
947 l_other_rec.action_information11 := fnd_number.number_to_canonical(l_certificate_info.num_deps_kou);
948 l_other_rec.action_information12 := fnd_number.number_to_canonical(l_certificate_info.num_deps_otsu);
949 -- Disableds
950 l_other_rec.action_information13 := fnd_number.number_to_canonical(l_certificate_info.num_svr_disableds_lt);
951 l_other_rec.action_information14 := fnd_number.number_to_canonical(l_certificate_info.num_svr_disableds);
952 l_other_rec.action_information15 := fnd_number.number_to_canonical(l_certificate_info.num_disableds);
953 -- Other Additional Info
954 l_other_rec.action_information16 := check_when_true(l_certificate_info.husband_exists = 'Y');
955 l_other_rec.action_information17 := check_when_true(l_certificate_info.minor_flag = 'Y');
956 l_other_rec.action_information18 := check_when_true(l_certificate_info.otsu_flag = 'Y');
957 l_other_rec.action_information19 := check_when_true(l_certificate_info.svr_disabled_flag = 'Y');
958 l_other_rec.action_information20 := check_when_true(l_certificate_info.disabled_flag = 'Y');
959 l_other_rec.action_information21 := check_when_true(l_certificate_info.aged_flag = 'Y');
960 l_other_rec.action_information22 := check_when_true(l_certificate_info.widow_flag = 'Y');
961 l_other_rec.action_information23 := check_when_true(l_certificate_info.sp_widow_flag = 'Y');
962 l_other_rec.action_information24 := check_when_true(l_certificate_info.widower_flag = 'Y');
963 l_other_rec.action_information25 := check_when_true(l_certificate_info.working_student_flag = 'Y');
964 l_other_rec.action_information26 := check_when_true(l_certificate_info.deceased_termination_flag = 'Y');
965 l_other_rec.action_information27 := check_when_true(l_certificate_info.disastered_flag = 'Y');
966 l_other_rec.action_information28 := check_when_true(l_certificate_info.foreigner_flag = 'Y');
967 --
968 l_other_rec.action_information29 := check_when_true(l_wic_rec.date_start is not null);
969 l_other_rec.action_information30 := check_when_true(l_wic_rec.actual_termination_date is not null);
970 --
971 -- JP_ITAX_OTHER2
972 --
973 l_other2_rec.action_context_id := p_assignment_action_id;
974 l_other2_rec.action_context_type := 'AAP';
975 l_other2_rec.action_information_category := 'JP_ITAX_OTHER2';
976 l_other2_rec.effective_date := l_wic_rec.effective_date;
977 l_other2_rec.assignment_id := l_assignment_id;
978 l_other2_rec.action_information1 := fnd_number.canonical_to_number(g_business_group_id);
979 --
980 hr_utility.set_location(c_proc, 80);
981 --
982 -- Construct ITW description
983 --
984 l_itw_system_desc1_kanji := null;
985 l_itw_system_desc2_kanji := null;
986 for i in 1..l_itw_descriptions.count loop
987 hr_utility.trace('itw description: ' || l_itw_descriptions(i).description_type);
988 --
989 -- Following replace is only for PAYJPITW pdf files.
990 --
991 if l_itw_descriptions(i).description_type = 'FIXED_RATE_TAX_REDUCTION' then
992 l_itw_descriptions(i).description_kanji := replace(l_itw_descriptions(i).description_kanji,
993 g_old_fixed_rate_kanji, g_new_fixed_rate_kanji);
994 l_itw_descriptions(i).description_kana := replace(l_itw_descriptions(i).description_kana,
995 g_old_fixed_rate_kana, g_new_fixed_rate_kana);
996 elsif l_itw_descriptions(i).description_type = 'NP_PREM' then
997 l_itw_descriptions(i).description_kanji := replace(l_itw_descriptions(i).description_kanji,
998 g_old_national_pens_kanji, g_new_national_pens_kanji);
999 l_itw_descriptions(i).description_kana := replace(l_itw_descriptions(i).description_kana,
1000 g_old_national_pens_kana, g_new_national_pens_kana);
1001 end if;
1002 --
1003 if l_itw_descriptions(i).description_type in ('FIXED_RATE_TAX_REDUCTION', 'NP_PREM') then
1004 concat_description(l_itw_system_desc1_kanji, l_itw_descriptions(i).description_kanji, ' ');
1005 else
1006 concat_description(l_itw_system_desc2_kanji, l_itw_descriptions(i).description_kanji, ',');
1007 end if;
1008 end loop;
1009 --
1010 -- Construct WTM description
1011 --
1012 l_wtm_system_desc_kanji := null;
1013 l_wtm_system_desc_kana := null;
1014 for i in 1..l_wtm_descriptions.count loop
1015 hr_utility.trace('wtm description: ' || l_wtm_descriptions(i).description_type);
1016 --
1017 concat_description(l_wtm_system_desc_kanji, l_wtm_descriptions(i).description_kanji, ',');
1018 concat_description(l_wtm_system_desc_kana, l_wtm_descriptions(i).description_kana, ',');
1019 end loop;
1020 --
1021 l_wtm_system_desc_kanji := hr_jp_standard_pkg.to_zenkaku(l_wtm_system_desc_kanji);
1022 l_wtm_system_desc_kana := hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(l_wtm_system_desc_kana, '?'));
1023 l_wtm_user_desc_kanji := hr_jp_standard_pkg.to_zenkaku(l_wtm_user_desc_kanji);
1024 l_wtm_user_desc_kana := hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(l_wtm_user_desc_kana, '?'));
1025 --
1026 hr_utility.trace('***** original descriptions *****');
1027 hr_utility.trace('itw_system_desc1_kanji: ' || l_itw_system_desc1_kanji);
1028 hr_utility.trace('itw_system_desc2_kanji: ' || l_itw_system_desc2_kanji);
1029 hr_utility.trace('itw_user_desc_kanji : ' || l_itw_user_desc_kanji);
1030 hr_utility.trace('wtm_system_desc_kanji : ' || l_wtm_system_desc_kanji);
1031 hr_utility.trace('wtm_system_desc_kana : ' || l_wtm_system_desc_kana);
1032 hr_utility.trace('wtm_user_desc_kanji : ' || l_wtm_user_desc_kanji);
1033 hr_utility.trace('wtm_user_desc_kana : ' || l_wtm_user_desc_kana);
1034 --
1035 l_itw_system_desc1_kanji := rtrim(substrb(l_itw_system_desc1_kanji, 1, 240));
1036 l_itw_system_desc2_kanji := rtrim(substrb(l_itw_system_desc2_kanji, 1, 300));
1037 l_itw_user_desc_kanji := rtrim(substrb(l_itw_user_desc_kanji, 1, 300));
1038 l_wtm_system_desc_kanji := substr(l_wtm_system_desc_kanji, 1, 100);
1039 l_wtm_system_desc_kana := substr(l_wtm_system_desc_kana, 1, 100);
1040 l_wtm_user_desc_kanji := substr(l_wtm_user_desc_kanji, 1, 100);
1041 l_wtm_user_desc_kana := substr(l_wtm_user_desc_kana, 1, 100);
1042 --
1043 hr_utility.trace('***** truncated descriptions *****');
1044 hr_utility.trace('itw_system_desc1_kanji: ' || l_itw_system_desc1_kanji);
1045 hr_utility.trace('itw_system_desc2_kanji: ' || l_itw_system_desc2_kanji);
1046 hr_utility.trace('itw_user_desc_kanji : ' || l_itw_user_desc_kanji);
1047 hr_utility.trace('wtm_system_desc_kanji : ' || l_wtm_system_desc_kanji);
1048 hr_utility.trace('wtm_system_desc_kana : ' || l_wtm_system_desc_kana);
1049 hr_utility.trace('wtm_user_desc_kanji : ' || l_wtm_user_desc_kanji);
1050 hr_utility.trace('wtm_user_desc_kana : ' || l_wtm_user_desc_kana);
1051 /*
1052 --
1053 -- If number of characters for WTM exceeds 100, output to log file
1054 -- to warn to user.
1055 --
1056 log_description(l_wtm_system_desc_kanji, l_wtm_user_desc_kanji, true);
1057 log_description(l_wtm_system_desc_kana, l_wtm_user_desc_kana, true);
1058 */
1059 --
1060 -- Never use substrb(240) to split string over 240 bytes,
1061 -- which will truncate multibyte characters.
1062 --
1063 l_other2_rec.action_information23 := l_itw_system_desc1_kanji;
1064 --
1065 hr_jp_standard_pkg.to_table(l_itw_system_desc2_kanji, 240, l_varchar2_tbl);
1066 l_other2_rec.action_information15 := get_index_at(l_varchar2_tbl, 1);
1067 l_other2_rec.action_information16 := get_index_at(l_varchar2_tbl, 2);
1068 --
1069 hr_jp_standard_pkg.to_table(l_itw_user_desc_kanji, 240, l_varchar2_tbl);
1070 l_other2_rec.action_information25 := get_index_at(l_varchar2_tbl, 1);
1071 l_other2_rec.action_information26 := get_index_at(l_varchar2_tbl, 2);
1072 --
1073 hr_jp_standard_pkg.to_table(l_wtm_system_desc_kanji, 240, l_varchar2_tbl);
1074 l_other2_rec.action_information19 := get_index_at(l_varchar2_tbl, 1);
1075 l_other2_rec.action_information20 := get_index_at(l_varchar2_tbl, 2);
1076 --
1077 hr_jp_standard_pkg.to_table(l_wtm_system_desc_kana, 240, l_varchar2_tbl);
1078 l_other2_rec.action_information21 := get_index_at(l_varchar2_tbl, 1);
1079 l_other2_rec.action_information22 := get_index_at(l_varchar2_tbl, 2);
1080 --
1081 hr_jp_standard_pkg.to_table(l_wtm_user_desc_kanji, 240, l_varchar2_tbl);
1082 l_other2_rec.action_information27 := get_index_at(l_varchar2_tbl, 1);
1083 l_other2_rec.action_information28 := get_index_at(l_varchar2_tbl, 2);
1084 --
1085 hr_jp_standard_pkg.to_table(l_wtm_user_desc_kana, 240, l_varchar2_tbl);
1086 l_other2_rec.action_information29 := get_index_at(l_varchar2_tbl, 1);
1087 l_other2_rec.action_information30 := get_index_at(l_varchar2_tbl, 2);
1088 --
1089 hr_utility.set_location(c_proc, 90);
1090 --
1091 -- Create the data
1092 --
1093 create_action_information(l_person_rec);
1094 l_action_information_ids.extend;
1095 l_action_information_ids(l_action_information_ids.last) := l_person_rec.action_information_id;
1096 --
1097 create_action_information(l_arch_rec);
1098 create_action_information(l_tax_rec);
1099 create_action_information(l_other_rec);
1100 create_action_information(l_other2_rec);
1101 end if;
1102 end loop;
1103 --
1104 -- Delete old archive data
1105 --
1106 for l_archive_rec in csr_delete_archives(l_assignment_id, l_action_information_ids) loop
1107 delete_archive(
1108 l_archive_rec.action_information_id,
1109 l_archive_rec.action_context_id,
1110 l_archive_rec.effective_date);
1111 end loop;
1112 --
1113 hr_utility.set_location('Leaving: ' || c_proc, 100);
1114 END ARCHIVE_CODE;
1115 -- +------------------------------------------------------------------------------------------------+
1116 -- |< DEINITIALIZATION_CODE >-----------------------------------------------------------------------|
1117 -- +------------------------------------------------------------------------------------------------+
1118 PROCEDURE DEINITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
1119 IS
1120 c_proc CONSTANT VARCHAR2(61) := c_package || 'DEINITIALIZATION_CODE';
1121 l_dummy varchar2(1);
1122 l_pact_rec pay_action_information%rowtype;
1123 --
1124 cursor csr_pa_exists is
1125 select 'Y'
1126 from dual
1127 where exists(
1128 select null
1129 from pay_action_information
1130 where action_context_id = p_payroll_action_id
1131 and action_context_type = 'PA');
1132 --
1133 cursor csr_assacts is
1134 select paa.assignment_action_id
1135 from pay_assignment_actions paa
1136 where paa.payroll_action_id = p_payroll_action_id
1137 and paa.action_status = 'C'
1138 and not exists(
1139 select null
1140 from pay_action_information pai
1141 where pai.action_context_id = paa.assignment_action_id
1142 and pai.action_context_type = 'AAP');
1143 BEGIN
1144 hr_utility.set_location('Entering: ' || c_proc, 10);
1145 --
1146 open csr_pa_exists;
1147 fetch csr_pa_exists into l_dummy;
1148 if csr_pa_exists%notfound then
1149 hr_utility.set_location(c_proc, 20);
1150 --
1151 initialization_code(p_payroll_action_id);
1152 --
1153 -- JP_ITAX_PACT
1154 --
1155 l_pact_rec.action_context_id := p_payroll_action_id;
1156 l_pact_rec.action_context_type := 'PA';
1157 l_pact_rec.action_information_category := 'JP_ITAX_PACT';
1158 l_pact_rec.effective_date := g_effective_date;
1159 l_pact_rec.action_information1 := fnd_number.number_to_canonical(g_payroll_id);
1160 l_pact_rec.action_information2 := fnd_number.number_to_canonical(g_itax_organization_id);
1161 l_pact_rec.action_information3 := g_include_terminated_flag;
1162 l_pact_rec.action_information4 := fnd_date.date_to_canonical(g_termination_date_from);
1163 l_pact_rec.action_information5 := fnd_date.date_to_canonical(g_termination_date_to);
1164 l_pact_rec.action_information6 := g_publication_period_status;
1165 l_pact_rec.action_information7 := fnd_date.date_to_canonical(g_publication_start_date);
1166 l_pact_rec.action_information8 := fnd_date.date_to_canonical(g_publication_end_date);
1167 --
1168 create_action_information(l_pact_rec);
1169 end if;
1170 --
1171 -- Delete completed assignment actions without PAY_ACTION_INFORMATION.
1172 --
1173 for l_rec in csr_assacts loop
1174 py_rollback_pkg.rollback_ass_action(l_rec.assignment_action_id);
1175 end loop;
1176 --
1177 hr_utility.set_location('Leaving: ' || c_proc, 100);
1178 END DEINITIALIZATION_CODE;
1179 --
1180 END PAY_JP_ITAX_ARCHIVE_PKG;