[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.30.12020000.2 2012/10/11 10:58:50 mdubasi ship $ */
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 --
346 c_proc CONSTANT VARCHAR2(61) := c_package || 'ARCHIVE_CODE';
347 --
348 l_assignment_id number;
349 l_archive boolean;
350 l_action_information_ids fnd_table_of_number := fnd_table_of_number();
351 --
352 l_dummy varchar2(255);
353 l_certificate_info pay_jp_wic_pkg.t_certificate_info;
354 l_withholding_tax_info pay_jp_wic_pkg.t_tax_info;
355 l_submission_required_flag varchar2(1);
356 l_prev_job_info pay_jp_wic_pkg.t_prev_job_info;
357 l_housing_info pay_jp_wic_pkg.t_housing_info;
358 l_adr_effective_date date;
359 --
360 l_itw_user_desc_kanji varchar2(32767);
361 l_itw_descriptions pay_jp_wic_pkg.t_descriptions;
362 l_wtm_user_desc varchar2(32767);
363 l_wtm_user_desc_kanji varchar2(32767);
364 l_wtm_user_desc_kana varchar2(32767);
365 l_wtm_descriptions pay_jp_wic_pkg.t_descriptions;
366 --
367 l_itw_system_desc1_kanji varchar2(32767);
368 l_itw_system_desc2_kanji varchar2(32767);
369 l_wtm_system_desc_kanji varchar2(32767);
370 l_wtm_system_desc_kana varchar2(32767);
371 l_varchar2_tbl hr_jp_standard_pkg.t_varchar2_tbl;
372 --
373 l_person_rec pay_action_information%rowtype;
374 l_arch_rec pay_action_information%rowtype;
375 l_tax_rec pay_action_information%rowtype;
376 l_other_rec pay_action_information%rowtype;
377 l_other2_rec pay_action_information%rowtype;
378 l_housing_rec pay_action_information%rowtype;
379 l_dep_rec pay_action_information%rowtype;
380 l_tax_rec1 pay_action_information%rowtype;
381 --
382 cursor csr_wic(cp_assignment_id number)
383 is
384 select wic.assignment_action_id,
385 wic.action_sequence,
386 wic.payroll_id,
387 nvl(pay.prl_information1, g_bg_dpnt_ref_type) dpnt_ref_type,
388 nvl(fnd_date.canonical_to_date(pay_core_utils.get_parameter('ITAX_DPNT_EFFECTIVE_DATE', wic.legislative_parameters)),wic.effective_date) dpnt_effective_date,
389 wic.effective_date,
390 wic.date_earned,
391 wic.itax_organization_id,
392 wic.itax_category,
393 wic.itax_yea_category,
394 wic.person_id,
395 wic.date_start,
396 wic.leaving_reason,
397 wic.actual_termination_date,
398 wic.employment_category
399 from pay_jp_wic_assacts_v wic,
400 pay_all_payrolls_f pay
401 where wic.assignment_id = cp_assignment_id
402 and wic.effective_date
403 between g_soy and g_effective_date
404 and ((g_itax_organization_id is null) or (wic.itax_organization_id = g_itax_organization_id))
405 and ((g_payroll_id is null) or (wic.payroll_id = g_payroll_id))
406 --
407 -- Do not check termination_date in ARCHIVE_CODE here
408 -- which has already been validated in ASSIGNMENT_ACTION_CODE.
409 --
410 /*
411 and ( ( g_include_terminated_flag = 'Y'
412 and ( (g_termination_date_from is null and g_termination_date_to is null)
413 or wic.actual_termination_date
414 between nvl(g_termination_date_from, wic.actual_termination_date)
415 and nvl(g_termination_date_to, wic.actual_termination_date)
416 )
417 )
418 or (g_include_terminated_flag = 'N' and wic.actual_termination_date is null)
419 )
420 */
421 and pay.payroll_id = wic.payroll_id
422 and wic.effective_date
423 between pay.effective_start_date and pay.effective_end_date
424 order by wic.effective_date;
425 --
426 -- Used when g_rearchive_flag = 'N'
427 -- to check whether the assact is already archived or not.
428 --
429 cursor csr_prev_archive(
430 cp_assignment_id number,
431 cp_effective_date date,
432 cp_assignment_action_id number)
433 is
434 select person.action_information_id
435 from pay_jp_itax_arch_v2 arch,
436 pay_jp_itax_person_v2 person
437 where arch.assignment_id = cp_assignment_id
438 -- effective_date validation is for performance reason.
439 and arch.effective_date = cp_effective_date
440 and arch.assignment_action_id = cp_assignment_action_id
441 and person.action_context_id = arch.action_context_id
442 and person.effective_date = arch.effective_date;
443 --
444 -- Used when g_inherit_archive_flag = 'Y'
445 -- to inherit description information under new archive assact.
446 -- The archive derived by this cursor will be removed.
447 --
448 cursor csr_replace_archive(
449 cp_assignment_id number,
450 cp_itax_organization_id number,
451 cp_itax_category varchar2)
452 is
453 select person.action_information_id,
454 person.action_context_id,
455 person.effective_date,
456 other2.ITW_USER_DESC_KANJI,
457 other2.WTM_USER_DESC_KANJI,
458 other2.WTM_USER_DESC_KANA
459 from pay_jp_itax_person_v2 person,
460 pay_jp_itax_arch_v2 arch,
461 pay_jp_itax_other2_v2 other2
462 where person.assignment_id = cp_assignment_id
463 and person.effective_date
464 between g_soy and g_eoy
465 and person.itax_organization_id = cp_itax_organization_id
466 and arch.action_context_id = person.action_context_id
467 and arch.effective_date = person.effective_date
468 and arch.itax_category = cp_itax_category
469 and other2.action_context_id = person.action_context_id
470 and other2.effective_date = person.effective_date
471 for update of person.action_information_id nowait
472 order by person.effective_date;
473 --
474 -- When g_rearchive_flag = 'Y' then all records derived by the
475 -- following cursor will be deleted.
476 -- When g_rearchive_flag = 'N' then all records except for records
477 -- derived by csr_prev_archive or created by current assact
478 -- stored in "cp_action_information_ids" will be deleted.
479 --
480 cursor csr_delete_archives(
481 cp_assignment_id number,
482 cp_action_information_ids fnd_table_of_number)
483 is
484 select person.action_information_id,
485 person.action_context_id,
486 person.effective_date
487 from pay_jp_itax_person_v2 person,
488 pay_jp_itax_arch_v2 arch
489 where person.assignment_id = cp_assignment_id
490 and person.effective_date
491 between g_soy and g_effective_date
492 -- bug.5657929
493 and person.action_information_category = 'JP_ITAX_PERSON'
494 and person.action_information_id not in (
495 select *
496 from table(cp_action_information_ids))
497 and ((g_itax_organization_id is null) or (person.itax_organization_id = g_itax_organization_id))
498 and arch.action_context_id = person.action_context_id
499 and arch.effective_date = person.effective_date
500 and ((g_payroll_id is null) or (arch.payroll_id = g_payroll_id))
501 for update of person.action_information_id nowait;
502 --
503 cursor csr_per(
504 cp_assignment_id number,
505 cp_effective_date date,
506 cp_adr_effective_date date)
507 is
508 select per.employee_number,
509 per.last_name last_name_kana,
510 per.first_name first_name_kana,
511 per.per_information18 last_name_kanji,
512 per.per_information19 first_name_kanji,
513 per.sex,
514 per.date_of_birth,
515 nvl(adrr.address_id, adrc.address_id) address_id,
516 rtrim(substrb(decode(adrr.address_id, null,
517 adrc.region_1 || adrc.region_2 || adrc.region_3,
518 adrr.region_1 || adrr.region_2 || adrr.region_3), 1, 240)) address_kana,
519 rtrim(substrb(decode(adrr.address_id, null,
520 adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
521 adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) address_kanji,
522 decode(adrr.address_id, null, adrc.country, adrr.country) country,
523 decode(adrr.address_id, null, adrc.town_or_city, adrr.town_or_city) district_code,
524 asg.organization_id
525 from per_all_assignments_f asg,
526 per_all_people_f per,
527 per_addresses adrr,
528 per_addresses adrc
529 where asg.assignment_id = cp_assignment_id
530 and cp_effective_date
531 between asg.effective_start_date and asg.effective_end_date
532 and per.person_id = asg.person_id
533 and cp_effective_date
534 between per.effective_start_date and per.effective_end_date
535 and adrr.person_id(+) = per.person_id
536 and adrr.address_type(+) = 'JP_R'
537 and cp_adr_effective_date
538 between adrr.date_from(+) and nvl(adrr.date_to(+), cp_adr_effective_date)
539 and adrc.person_id(+) = per.person_id
540 and adrc.address_type(+) = 'JP_C'
541 and cp_adr_effective_date
542 between adrc.date_from(+) and nvl(adrc.date_to(+), cp_adr_effective_date);
543 --
544 l_per_rec csr_per%rowtype;
545 --
546 cursor csr_swot(cp_itax_organization_id number)
547 is
548 select hoi2.org_information3 reference_number,
549 lpad(hoi2.org_information4, 10, '0') reference_number1,
550 hoi2.org_information5 reference_number2,
551 rtrim(substrb(hoi1.org_information6 || hoi1.org_information7 || hoi1.org_information8, 1, 240)) salary_payer_address_kanji,
552 hoi1.org_information1 salary_payer_name_kanji,
553 hoi1.org_information12 salary_payer_telephone_number,
554 hoi2.org_information2 tax_office_number
555 from hr_all_organization_units hou,
556 hr_organization_information hoi1,
557 hr_organization_information hoi2
558 where hou.organization_id = cp_itax_organization_id
559 and hoi1.organization_id(+) = hou.organization_id
560 and hoi1.org_information_context(+) = 'JP_TAX_SWOT_INFO'
561 and hoi2.organization_id(+) = hou.organization_id
562 and hoi2.org_information_context(+) = 'JP_ITAX_WITHHELD_INFO';
563 --
564 l_swot_rec csr_swot%rowtype;
565 --
566 procedure concat_description(
567 p_src in out nocopy varchar2,
568 p_description in varchar2,
569 p_separator in varchar2)
570 is
571 begin
572 if p_description is not null then
573 if p_src is not null then
574 p_src := p_src || p_separator;
575 end if;
576 --
577 p_src := p_src || p_description;
578 end if;
579 end concat_description;
580 --
581 function get_index_at(
582 p_varchar2_tbl in hr_jp_standard_pkg.t_varchar2_tbl,
583 p_index in number) return varchar2
584 is
585 begin
586 if p_varchar2_tbl.exists(p_index) then
587 return p_varchar2_tbl(p_index);
588 else
589 return null;
590 end if;
591 end get_index_at;
592 --
593 /*
594 procedure log_description(
595 p_system_description in varchar2,
596 p_user_description in varchar2,
597 p_is_zenkaku in boolean)
598 is
599 l_description varchar2(32767);
600 l_separator varchar2(10) := ',';
601 begin
602 if p_system_description is null then
603 l_description := p_user_description;
604 elsif p_user_description is null then
605 l_description := p_system_description;
606 else
607 if p_is_zenkaku then
608 l_separator := hr_jp_standard_pkg.to_zenkaku(l_separator);
609 end if;
610 --
611 l_description := p_system_description || l_separator || p_user_description;
612 end if;
613 --
614 if length(l_description) > 100 then
615 fnd_message.set_name('PAY', 'PAY_JP_ITW_ARCHIVE_DESC_WARN');
616 fnd_message.set_token('ACTION_INFORMATION_ID', null);
617 fnd_message.set_token('EMPLOYEE_NUMBER', null);
618 fnd_message.set_token('FULL_NAME', null);
619 fnd_message.set_token('SWOT_NAME', null);
620 fnd_message.set_token('TAX_TYPE', null);
621 fnd_message.set_token('DESCRIPTION', l_description);
622 fnd_file.put_line(fnd_file.log, fnd_message.get);
623 end if;
624 end log_description;
625 */
626 --
627 procedure delete_archive(
628 p_action_information_id in number,
629 p_action_context_id in number,
630 p_effective_date in date)
631 is
632 l_count number;
633 begin
634 --
635 -- Currently, the unique key is (action_context_id, effective_date),
636 -- which needs to be changed to (action_context_id) to fix potential bugs.
637 --
638 select count(*)
639 into l_count
640 from pay_jp_itax_person_v2
641 where action_context_id = p_action_context_id
642 and action_information_id <> p_action_information_id;
643 --
644 -- When multiple archives exist, delete the correponding action info only.
645 -- If not, rollback the assignment action itself.
646 --
647 if l_count > 0 then
648 delete
649 from pay_action_information
650 where action_context_type = 'AAP'
651 and action_context_id = p_action_context_id
652 and effective_date = p_effective_date;
653 else
654 py_rollback_pkg.rollback_ass_action(p_action_context_id);
655 end if;
656 --
657 fnd_message.set_name('PAY', 'PAY_JP_ITW_ARCHIVE_DELETED');
658 fnd_message.set_token('ACTION_INFORMATION_ID', p_action_information_id);
659 fnd_message.set_token('EMPLOYEE_NUMBER', null);
660 fnd_message.set_token('FULL_NAME', null);
661 fnd_message.set_token('SWOT_NAME', null);
662 fnd_message.set_token('TAX_TYPE', null);
663 fnd_file.put_line(fnd_file.log, fnd_message.get);
664 --
665 end delete_archive;
666 --
667 BEGIN
668 --
669 hr_utility.set_location('Entering: ' || c_proc, 10);
670 --
671 -- Lock first(not latest) record on PER_ALL_ASSIGNMENTS_F
672 -- to suppress other PAYJPITW processes archive the same information
673 -- at the same time.
674 --
675 select asg.assignment_id
676 into l_assignment_id
677 from pay_assignment_actions paa,
678 per_all_assignments_f asg
679 where paa.assignment_action_id = p_assignment_action_id
680 and asg.assignment_id = paa.assignment_id
681 and asg.effective_start_date <= g_effective_date
682 and asg.effective_end_date >= g_soy
683 and not exists(
684 select null
685 from per_all_assignments_f asg2
686 where asg2.assignment_id = asg.assignment_id
687 and asg2.effective_start_date < asg.effective_start_date
688 and asg2.effective_end_date >= g_soy)
689 for update of asg.assignment_id nowait;
690 --
691 -- Fetch same year's data
692 --
693 for l_wic_rec in csr_wic(l_assignment_id) loop
694 --
695 hr_utility.set_location(c_proc, 20);
696 --
697 l_archive := true;
698 --
699 -- Check whether the wic assact is already archived or not
700 -- when g_rearchive_flag = 'N'. If found, skip processing
701 -- this wic assact.
702 --
703 if g_rearchive_flag = 'N' then
704 --
705 for l_archive_rec in csr_prev_archive(l_assignment_id, l_wic_rec.effective_date, l_wic_rec.assignment_action_id) loop
706 l_archive := false;
707 l_action_information_ids.extend;
708 l_action_information_ids(l_action_information_ids.last) := l_archive_rec.action_information_id;
709 end loop;
710 --
711 end if;
712 --
713 if l_archive then
714 --
715 l_adr_effective_date := nvl(l_wic_rec.actual_termination_date, g_eoy + 1);
716 --
717 -- Personal Information
718 --
719 open csr_per(l_assignment_id, l_wic_rec.effective_date, l_adr_effective_date);
720 fetch csr_per into l_per_rec;
721 if csr_per%notfound then
722 close csr_per;
723 raise no_data_found;
724 end if;
725 close csr_per;
726 --
727 hr_utility.set_location(c_proc, 30);
728 --
729 -- Employer Information
730 --
731 open csr_swot(l_wic_rec.itax_organization_id);
732 fetch csr_swot into l_swot_rec;
733 if csr_swot%notfound then
734 close csr_swot;
735 raise no_data_found;
736 end if;
737 close csr_swot;
738 --
739 hr_utility.set_location(c_proc, 40);
740 --
741 -- Certificate Information
742 --
743 -- Never call "pay_jp_wic_pkg.get_certificate_info" multiple times
744 -- which will cause severe performance loss.
745 -- Also never call debugging "pay_jp_wic_pkg.get_certificate_info"
746 -- which is only for debugging purpose.
747 --
748 pay_jp_wic_pkg.get_certificate_info(
749 p_assignment_action_id => l_wic_rec.assignment_action_id,
750 p_assignment_id => l_assignment_id,
751 p_action_sequence => l_wic_rec.action_sequence,
752 p_business_group_id => g_business_group_id,
753 p_effective_date => l_wic_rec.effective_date,
754 p_date_earned => l_wic_rec.date_earned,
755 p_itax_organization_id => l_wic_rec.itax_organization_id,
756 p_itax_category => l_wic_rec.itax_category,
757 p_itax_yea_category => l_wic_rec.itax_yea_category,
758 p_dpnt_ref_type => l_wic_rec.dpnt_ref_type,
759 p_dpnt_effective_date => l_wic_rec.dpnt_effective_date,
760 p_person_id => l_wic_rec.person_id,
761 p_sex => l_per_rec.sex,
762 p_date_of_birth => l_per_rec.date_of_birth,
763 p_leaving_reason => l_wic_rec.leaving_reason,
764 p_last_name_kanji => l_per_rec.last_name_kanji,
765 p_last_name_kana => l_per_rec.last_name_kana,
766 p_employment_category => l_wic_rec.employment_category,
767 p_certificate_info => l_certificate_info,
768 p_submission_required_flag => l_submission_required_flag,
769 p_prev_job_info => l_prev_job_info,
770 p_housing_info => l_housing_info,
771 p_withholding_tax_info => l_withholding_tax_info,
772 p_itw_description => l_itw_user_desc_kanji,
773 p_itw_descriptions => l_itw_descriptions,
774 p_wtm_description => l_wtm_user_desc,
775 p_wtm_descriptions => l_wtm_descriptions);
776 --
777 l_wtm_user_desc_kanji := l_wtm_user_desc;
778 l_wtm_user_desc_kana := l_wtm_user_desc;
779 --
780 hr_utility.set_location(c_proc, 50);
781 --
782 -- Get old description and replace system derived description with those if available,
783 -- then delete all archive data.
784 -- This specification seems to be confusing for user, there's possibility to
785 -- comment out the following code.
786 -- Fixed to replace not only ITW but also WTM description.
787 -- Note old override flag is not checked.
788 --
789 for l_archive_rec in csr_replace_archive(
790 l_assignment_id,
791 l_wic_rec.itax_organization_id,
792 l_wic_rec.itax_category) loop
793 --
794 hr_utility.set_location(c_proc, 61);
795 --
796 if g_inherit_archive_flag = 'Y' then
797 --
798 l_itw_user_desc_kanji := l_archive_rec.itw_user_desc_kanji;
799 l_wtm_user_desc_kanji := l_archive_rec.wtm_user_desc_kanji;
800 l_wtm_user_desc_kana := l_archive_rec.wtm_user_desc_kana;
801 /*
802 --
803 fnd_message.set_name('PAY', 'PAY_JP_ITW_ARCHIVE_INHERITED');
804 fnd_message.set_token('ACTION_INFORMATION_ID', null);
805 fnd_message.set_token('EMPLOYEE_NUMBER', null);
806 fnd_message.set_token('FULL_NAME', null);
807 fnd_message.set_token('SWOT_NAME', null);
808 fnd_message.set_token('TAX_TYPE', null);
809 fnd_message.set_token('SRC_ACTION_INFORMATION_ID', null);
810 fnd_file.put_line(fnd_file.log, fnd_message.get);
811 --
812 */
813 --
814 end if;
815 --
816 delete_archive(
817 l_archive_rec.action_information_id,
818 l_archive_rec.action_context_id,
819 l_archive_rec.effective_date);
820 --
821 end loop;
822 --
823 hr_utility.set_location(c_proc, 70);
824 --
825 -- JP_ITAX_PERSON
826 --
827 l_person_rec.action_context_id := p_assignment_action_id;
828 l_person_rec.action_context_type := 'AAP';
829 l_person_rec.action_information_category := 'JP_ITAX_PERSON';
830 l_person_rec.effective_date := l_wic_rec.effective_date;
831 l_person_rec.assignment_id := l_assignment_id;
832 l_person_rec.action_information1 := l_per_rec.employee_number;
833 l_person_rec.action_information2 := l_per_rec.last_name_kana;
834 l_person_rec.action_information3 := l_per_rec.first_name_kana;
835 l_person_rec.action_information4 := l_per_rec.last_name_kanji;
836 l_person_rec.action_information5 := l_per_rec.first_name_kanji;
837 l_person_rec.action_information6 := l_per_rec.sex;
838 l_person_rec.action_information7 := fnd_number.number_to_canonical(l_per_rec.address_id);
839 l_person_rec.action_information8 := l_per_rec.address_kana;
840 l_person_rec.action_information9 := l_per_rec.address_kanji;
841 l_person_rec.action_information10 := l_per_rec.country;
842 l_person_rec.action_information11 := fnd_date.date_to_canonical(l_wic_rec.date_start);
843 l_person_rec.action_information12 := l_wic_rec.leaving_reason;
844 l_person_rec.action_information13 := fnd_date.date_to_canonical(l_per_rec.date_of_birth);
845 --
846 jp_date(
847 p_date => l_per_rec.date_of_birth,
848 p_meiji => l_person_rec.action_information14,
849 p_taishou => l_person_rec.action_information15,
850 p_shouwa => l_person_rec.action_information16,
851 p_heisei => l_person_rec.action_information17,
852 p_year => l_person_rec.action_information18,
853 p_month => l_person_rec.action_information19,
854 p_day => l_person_rec.action_information20);
855 --
856 /* Bug.6208573. 21/22/23 obsolete.
857 --
858 jp_date(
859 p_date => nvl(l_wic_rec.actual_termination_date, l_wic_rec.date_start),
860 p_meiji => l_dummy,
861 p_taishou => l_dummy,
862 p_shouwa => l_dummy,
863 p_heisei => l_dummy,
864 p_year => l_person_rec.action_information21,
865 p_month => l_person_rec.action_information22,
866 p_day => l_person_rec.action_information23);
867 --
868 */
869 --
870 l_person_rec.action_information24 := fnd_number.number_to_canonical(l_wic_rec.itax_organization_id);
871 l_person_rec.action_information25 := fnd_date.date_to_canonical(l_wic_rec.actual_termination_date);
872 l_person_rec.action_information26 := fnd_number.number_to_canonical(l_per_rec.organization_id);
873 l_person_rec.action_information28 := l_per_rec.district_code;
874 --
875 -- JP_ITAX_ARCH
876 --
877 l_arch_rec.action_context_id := p_assignment_action_id;
878 l_arch_rec.action_context_type := 'AAP';
879 l_arch_rec.action_information_category := 'JP_ITAX_ARCH';
880 l_arch_rec.effective_date := l_wic_rec.effective_date;
881 l_arch_rec.assignment_id := l_assignment_id;
882 l_arch_rec.action_information1 := fnd_number.number_to_canonical(l_wic_rec.person_id);
883 l_arch_rec.action_information2 := fnd_number.number_to_canonical(l_wic_rec.assignment_action_id);
884 l_arch_rec.action_information3 := fnd_number.number_to_canonical(l_wic_rec.payroll_id);
885 l_arch_rec.action_information4 := l_swot_rec.reference_number;
886 l_arch_rec.action_information5 := l_swot_rec.reference_number1;
887 l_arch_rec.action_information6 := l_swot_rec.reference_number2;
888 l_arch_rec.action_information7 := fnd_number.number_to_canonical(l_wic_rec.action_sequence);
889 l_arch_rec.action_information8 := l_swot_rec.salary_payer_address_kanji;
890 l_arch_rec.action_information9 := l_swot_rec.salary_payer_name_kanji;
891 l_arch_rec.action_information10 := l_swot_rec.salary_payer_telephone_number;
892 l_arch_rec.action_information11 := l_swot_rec.tax_office_number;
893 l_arch_rec.action_information12 := fnd_date.date_to_canonical(l_wic_rec.date_earned);
894 l_arch_rec.action_information13 := l_wic_rec.employment_category;
895 l_arch_rec.action_information14 := l_wic_rec.itax_category;
896 l_arch_rec.action_information15 := l_wic_rec.itax_yea_category;
897 l_arch_rec.action_information30 := l_submission_required_flag;
898 --
899 -- JP_ITAX_TAX
900 --
901 l_tax_rec.action_context_id := p_assignment_action_id;
902 l_tax_rec.action_context_type := 'AAP';
903 l_tax_rec.action_information_category := 'JP_ITAX_TAX';
904 l_tax_rec.effective_date := l_wic_rec.effective_date;
905 l_tax_rec.assignment_id := l_assignment_id;
906 l_tax_rec.action_information1 := fnd_number.number_to_canonical(l_certificate_info.tax_info.taxable_income);
907 l_tax_rec.action_information2 := fnd_number.number_to_canonical(l_certificate_info.net_taxable_income);
908 l_tax_rec.action_information3 := fnd_number.number_to_canonical(l_certificate_info.total_income_exempt);
909 l_tax_rec.action_information4 := fnd_number.number_to_canonical(l_certificate_info.tax_info.withholding_itax);
910 l_tax_rec.action_information5 := fnd_number.number_to_canonical(l_withholding_tax_info.withholding_itax);
911 l_tax_rec.action_information6 := fnd_number.number_to_canonical(l_certificate_info.spouse_sp_exempt);
912 l_tax_rec.action_information7 := fnd_number.number_to_canonical(l_certificate_info.tax_info.si_prem);
913 l_tax_rec.action_information8 := fnd_number.number_to_canonical(l_certificate_info.tax_info.mutual_aid_prem);
914 l_tax_rec.action_information9 := fnd_number.number_to_canonical(l_certificate_info.li_prem_exempt);
915 l_tax_rec.action_information10 := fnd_number.number_to_canonical(l_certificate_info.ai_prem_exempt);
916 l_tax_rec.action_information11 := fnd_number.number_to_canonical(l_certificate_info.housing_tax_reduction);
917 l_tax_rec.action_information12 := fnd_number.number_to_canonical(l_withholding_tax_info.itax_adjustment);
918 l_tax_rec.action_information13 := fnd_number.number_to_canonical(l_certificate_info.spouse_net_taxable_income);
919 l_tax_rec.action_information14 := fnd_number.number_to_canonical(l_certificate_info.pp_prem);
920 l_tax_rec.action_information15 := fnd_number.number_to_canonical(l_certificate_info.long_ai_prem);
921 l_tax_rec.action_information16 := fnd_number.number_to_canonical(l_certificate_info.tax_info.disaster_tax_reduction);
922 l_tax_rec.action_information17 := l_prev_job_info.salary_payer_address_kana;
923 l_tax_rec.action_information18 := l_prev_job_info.salary_payer_address_kanji;
924 l_tax_rec.action_information19 := l_prev_job_info.salary_payer_name_kana;
925 l_tax_rec.action_information20 := l_prev_job_info.salary_payer_name_kanji;
926 --
927 -- bug.6168642
928 -- Fixed to set null when no prev jobs exist.
929 --l_tax_rec.action_information21 := decode_value(l_prev_job_info.foreign_address_flag = 'Y', '1', '0');
930 --
931 if l_prev_job_info.foreign_address_flag = 'Y' then
932 l_tax_rec.action_information21 := '1';
933 elsif l_prev_job_info.foreign_address_flag = 'N' then
934 l_tax_rec.action_information21 := '0';
935 else
936 l_tax_rec.action_information21 := null;
937 end if;
938 --
939 l_tax_rec.action_information22 := fnd_number.number_to_canonical(l_prev_job_info.taxable_income);
940 l_tax_rec.action_information23 := fnd_number.number_to_canonical(l_prev_job_info.itax);
941 l_tax_rec.action_information24 := fnd_number.number_to_canonical(l_prev_job_info.si_prem);
942 --
943 jp_date(
944 p_date => l_prev_job_info.termination_date,
945 p_meiji => l_dummy,
946 p_taishou => l_dummy,
947 p_shouwa => l_dummy,
948 p_heisei => l_dummy,
949 p_year => l_tax_rec.action_information25,
950 p_month => l_tax_rec.action_information26,
951 p_day => l_tax_rec.action_information27);
952 --
953 -- housing_residence_date will be null since april 2009
954 --
955 jp_date(
956 p_date => l_certificate_info.housing_residence_date,
957 p_meiji => l_dummy,
958 p_taishou => l_dummy,
959 p_shouwa => l_dummy,
960 p_heisei => l_dummy,
961 p_year => l_tax_rec.action_information28,
962 p_month => l_tax_rec.action_information29,
963 p_day => l_tax_rec.action_information30);
964 --
965 -- 2012 Withholding Changes JP_ITAX_TAX1
966 l_tax_rec1.action_context_id := p_assignment_action_id;
967 l_tax_rec1.action_context_type := 'AAP';
968 l_tax_rec1.action_information_category := 'JP_ITAX_TAX1';
969 l_tax_rec1.effective_date := l_wic_rec.effective_date;
970 l_tax_rec1.assignment_id := l_assignment_id;
971 l_tax_rec1.action_information1 := fnd_number.number_to_canonical(l_certificate_info.old_life_ins_prem);
972 l_tax_rec1.action_information2 := fnd_number.number_to_canonical(l_certificate_info.new_life_ins_prem);
973 l_tax_rec1.action_information3 := fnd_number.number_to_canonical(l_certificate_info.care_med_ins_prem);
974 l_tax_rec1.action_information4 := fnd_number.number_to_canonical(l_certificate_info.new_pen_ins_prem);
975
976 -- JP_ITAX_OTHER
977 --
978 l_other_rec.action_context_id := p_assignment_action_id;
979 l_other_rec.action_context_type := 'AAP';
980 l_other_rec.action_information_category := 'JP_ITAX_OTHER';
981 l_other_rec.effective_date := l_wic_rec.effective_date;
982 l_other_rec.assignment_id := l_assignment_id;
983 -- Dependent Spouse
984 l_other_rec.action_information1 := check_when_true(l_certificate_info.dep_spouse_exists_kou = 'Y');
985 l_other_rec.action_information2 := check_when_true(l_certificate_info.dep_spouse_not_exist_kou = 'Y');
986 l_other_rec.action_information3 := check_when_true(l_certificate_info.dep_spouse_exists_otsu = 'Y');
987 l_other_rec.action_information4 := check_when_true(l_certificate_info.dep_spouse_not_exist_otsu = 'Y');
988 l_other_rec.action_information5 := check_when_true(l_certificate_info.aged_spouse_exists = 'Y');
989 -- Dependents
990 l_other_rec.action_information6 := fnd_number.number_to_canonical(l_certificate_info.num_specifieds_kou);
991 l_other_rec.action_information7 := fnd_number.number_to_canonical(l_certificate_info.num_specifieds_otsu);
992 l_other_rec.action_information8 := fnd_number.number_to_canonical(l_certificate_info.num_aged_parents_lt);
993 l_other_rec.action_information9 := fnd_number.number_to_canonical(l_certificate_info.num_ageds_kou);
994 l_other_rec.action_information10 := fnd_number.number_to_canonical(l_certificate_info.num_ageds_otsu);
995 l_other_rec.action_information11 := fnd_number.number_to_canonical(l_certificate_info.num_deps_kou);
996 l_other_rec.action_information12 := fnd_number.number_to_canonical(l_certificate_info.num_deps_otsu);
997 -- Disableds
998 l_other_rec.action_information13 := fnd_number.number_to_canonical(l_certificate_info.num_svr_disableds_lt);
999 l_other_rec.action_information14 := fnd_number.number_to_canonical(l_certificate_info.num_svr_disableds);
1000 l_other_rec.action_information15 := fnd_number.number_to_canonical(l_certificate_info.num_disableds);
1001 -- Other Additional Info
1002 l_other_rec.action_information16 := check_when_true(l_certificate_info.husband_exists = 'Y');
1003 l_other_rec.action_information17 := check_when_true(l_certificate_info.minor_flag = 'Y');
1004 l_other_rec.action_information18 := check_when_true(l_certificate_info.otsu_flag = 'Y');
1005 l_other_rec.action_information19 := check_when_true(l_certificate_info.svr_disabled_flag = 'Y');
1006 l_other_rec.action_information20 := check_when_true(l_certificate_info.disabled_flag = 'Y');
1007 l_other_rec.action_information21 := check_when_true(l_certificate_info.aged_flag = 'Y');
1008 l_other_rec.action_information22 := check_when_true(l_certificate_info.widow_flag = 'Y');
1009 l_other_rec.action_information23 := check_when_true(l_certificate_info.sp_widow_flag = 'Y');
1010 l_other_rec.action_information24 := check_when_true(l_certificate_info.widower_flag = 'Y');
1011 l_other_rec.action_information25 := check_when_true(l_certificate_info.working_student_flag = 'Y');
1012 l_other_rec.action_information26 := check_when_true(l_certificate_info.deceased_termination_flag = 'Y');
1013 l_other_rec.action_information27 := check_when_true(l_certificate_info.disastered_flag = 'Y');
1014 l_other_rec.action_information28 := check_when_true(l_certificate_info.foreigner_flag = 'Y');
1015 --
1016 l_other_rec.action_information29 := check_when_true(l_wic_rec.date_start is not null);
1017 l_other_rec.action_information30 := check_when_true(l_wic_rec.actual_termination_date is not null);
1018 --
1019 -- JP_ITAX_DEP (JP_ITAX_OTHER use full 30 action info)
1020 --
1021 l_dep_rec.action_context_id := p_assignment_action_id;
1022 l_dep_rec.action_context_type := 'AAP';
1023 l_dep_rec.assignment_id := l_assignment_id;
1024 l_dep_rec.effective_date := l_wic_rec.effective_date;
1025 l_dep_rec.action_information_category := 'JP_ITAX_DEP';
1026 l_dep_rec.action_information1 := fnd_number.number_to_canonical(l_certificate_info.num_junior_deps);
1027 --
1028 -- JP_ITAX_OTHER2
1029 --
1030 l_other2_rec.action_context_id := p_assignment_action_id;
1031 l_other2_rec.action_context_type := 'AAP';
1032 l_other2_rec.action_information_category := 'JP_ITAX_OTHER2';
1033 l_other2_rec.effective_date := l_wic_rec.effective_date;
1034 l_other2_rec.assignment_id := l_assignment_id;
1035 l_other2_rec.action_information1 := fnd_number.canonical_to_number(g_business_group_id);
1036 --
1037 hr_utility.set_location(c_proc, 80);
1038 --
1039 -- Construct ITW description
1040 --
1041 l_itw_system_desc1_kanji := null;
1042 l_itw_system_desc2_kanji := null;
1043 --
1044 for i in 1..l_itw_descriptions.count loop
1045 --
1046 hr_utility.trace('itw description: ' || l_itw_descriptions(i).description_type);
1047 --
1048 -- Following replace is only for PAYJPITW pdf files.
1049 --
1050 if l_itw_descriptions(i).description_type = 'FIXED_RATE_TAX_REDUCTION' then
1051 --
1052 l_itw_descriptions(i).description_kanji := replace(l_itw_descriptions(i).description_kanji,g_old_fixed_rate_kanji,g_new_fixed_rate_kanji);
1053 l_itw_descriptions(i).description_kana := replace(l_itw_descriptions(i).description_kana,g_old_fixed_rate_kana,g_new_fixed_rate_kana);
1054 --
1055 elsif l_itw_descriptions(i).description_type = 'NP_PREM' then
1056 --
1057 l_itw_descriptions(i).description_kanji := replace(l_itw_descriptions(i).description_kanji,g_old_national_pens_kanji,g_new_national_pens_kanji);
1058 l_itw_descriptions(i).description_kana := replace(l_itw_descriptions(i).description_kana,g_old_national_pens_kana,g_new_national_pens_kana);
1059 --
1060 end if;
1061 --
1062 -- following routine will not be used since 2009, description type is NP_PREM_CONC
1063 --
1064 if l_itw_descriptions(i).description_type in ('FIXED_RATE_TAX_REDUCTION', 'NP_PREM') then
1065 --
1066 concat_description(l_itw_system_desc1_kanji,l_itw_descriptions(i).description_kanji,' ');
1067 --
1068 else
1069 --
1070 concat_description(l_itw_system_desc2_kanji,l_itw_descriptions(i).description_kanji,',');
1071 --
1072 end if;
1073 --
1074 end loop;
1075 --
1076 -- Construct WTM description
1077 --
1078 l_wtm_system_desc_kanji := null;
1079 l_wtm_system_desc_kana := null;
1080 --
1081 for i in 1..l_wtm_descriptions.count loop
1082 --
1083 hr_utility.trace('wtm description: ' || l_wtm_descriptions(i).description_type);
1084 --
1085 concat_description(l_wtm_system_desc_kanji,l_wtm_descriptions(i).description_kanji,',');
1086 concat_description(l_wtm_system_desc_kana,l_wtm_descriptions(i).description_kana,',');
1087 --
1088 end loop;
1089 --
1090 l_wtm_system_desc_kanji := hr_jp_standard_pkg.to_zenkaku(l_wtm_system_desc_kanji);
1091 l_wtm_system_desc_kana := hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(l_wtm_system_desc_kana, '?'));
1092 l_wtm_user_desc_kanji := hr_jp_standard_pkg.to_zenkaku(l_wtm_user_desc_kanji);
1093 l_wtm_user_desc_kana := hr_jp_standard_pkg.upper_kana(hr_jp_standard_pkg.to_hankaku(l_wtm_user_desc_kana, '?'));
1094 --
1095 hr_utility.trace('***** original descriptions *****');
1096 hr_utility.trace('itw_system_desc1_kanji: ' || l_itw_system_desc1_kanji);
1097 hr_utility.trace('itw_system_desc2_kanji: ' || l_itw_system_desc2_kanji);
1098 hr_utility.trace('itw_user_desc_kanji : ' || l_itw_user_desc_kanji);
1099 hr_utility.trace('wtm_system_desc_kanji : ' || l_wtm_system_desc_kanji);
1100 hr_utility.trace('wtm_system_desc_kana : ' || l_wtm_system_desc_kana);
1101 hr_utility.trace('wtm_user_desc_kanji : ' || l_wtm_user_desc_kanji);
1102 hr_utility.trace('wtm_user_desc_kana : ' || l_wtm_user_desc_kana);
1103 --
1104 l_itw_system_desc1_kanji := rtrim(substrb(l_itw_system_desc1_kanji, 1, 240));
1105 l_itw_system_desc2_kanji := rtrim(substrb(l_itw_system_desc2_kanji, 1, 300));
1106 l_itw_user_desc_kanji := rtrim(substrb(l_itw_user_desc_kanji, 1, 300));
1107 l_wtm_system_desc_kanji := substr(l_wtm_system_desc_kanji, 1, 100);
1108 l_wtm_system_desc_kana := substr(l_wtm_system_desc_kana, 1, 100);
1109 l_wtm_user_desc_kanji := substr(l_wtm_user_desc_kanji, 1, 100);
1110 l_wtm_user_desc_kana := substr(l_wtm_user_desc_kana, 1, 100);
1111 --
1112 hr_utility.trace('***** truncated descriptions *****');
1113 hr_utility.trace('itw_system_desc1_kanji: ' || l_itw_system_desc1_kanji);
1114 hr_utility.trace('itw_system_desc2_kanji: ' || l_itw_system_desc2_kanji);
1115 hr_utility.trace('itw_user_desc_kanji : ' || l_itw_user_desc_kanji);
1116 hr_utility.trace('wtm_system_desc_kanji : ' || l_wtm_system_desc_kanji);
1117 hr_utility.trace('wtm_system_desc_kana : ' || l_wtm_system_desc_kana);
1118 hr_utility.trace('wtm_user_desc_kanji : ' || l_wtm_user_desc_kanji);
1119 hr_utility.trace('wtm_user_desc_kana : ' || l_wtm_user_desc_kana);
1120 --
1121 /*
1122 --
1123 -- If number of characters for WTM exceeds 100, output to log file
1124 -- to warn to user.
1125 --
1126 log_description(l_wtm_system_desc_kanji, l_wtm_user_desc_kanji, true);
1127 log_description(l_wtm_system_desc_kana, l_wtm_user_desc_kana, true);
1128 --
1129 */
1130 --
1131 -- Never use substrb(240) to split string over 240 bytes,
1132 -- which will truncate multibyte characters.
1133 --
1134 l_other2_rec.action_information23 := l_itw_system_desc1_kanji;
1135 --
1136 hr_jp_standard_pkg.to_table(l_itw_system_desc2_kanji, 240, l_varchar2_tbl);
1137 l_other2_rec.action_information15 := get_index_at(l_varchar2_tbl, 1);
1138 l_other2_rec.action_information16 := get_index_at(l_varchar2_tbl, 2);
1139 --
1140 hr_jp_standard_pkg.to_table(l_itw_user_desc_kanji, 240, l_varchar2_tbl);
1141 l_other2_rec.action_information25 := get_index_at(l_varchar2_tbl, 1);
1142 l_other2_rec.action_information26 := get_index_at(l_varchar2_tbl, 2);
1143 --
1144 hr_jp_standard_pkg.to_table(l_wtm_system_desc_kanji, 240, l_varchar2_tbl);
1145 l_other2_rec.action_information19 := get_index_at(l_varchar2_tbl, 1);
1146 l_other2_rec.action_information20 := get_index_at(l_varchar2_tbl, 2);
1147 --
1148 hr_jp_standard_pkg.to_table(l_wtm_system_desc_kana, 240, l_varchar2_tbl);
1149 l_other2_rec.action_information21 := get_index_at(l_varchar2_tbl, 1);
1150 l_other2_rec.action_information22 := get_index_at(l_varchar2_tbl, 2);
1151 --
1152 hr_jp_standard_pkg.to_table(l_wtm_user_desc_kanji, 240, l_varchar2_tbl);
1153 l_other2_rec.action_information27 := get_index_at(l_varchar2_tbl, 1);
1154 l_other2_rec.action_information28 := get_index_at(l_varchar2_tbl, 2);
1155 --
1156 hr_jp_standard_pkg.to_table(l_wtm_user_desc_kana, 240, l_varchar2_tbl);
1157 l_other2_rec.action_information29 := get_index_at(l_varchar2_tbl, 1);
1158 l_other2_rec.action_information30 := get_index_at(l_varchar2_tbl, 2);
1159 --
1160 hr_utility.set_location(c_proc, 90);
1161 --
1162 -- JP_ITAX_HOUSING
1163 --
1164 l_housing_rec.action_context_id := p_assignment_action_id;
1165 l_housing_rec.action_context_type := 'AAP';
1166 l_housing_rec.assignment_id := l_assignment_id;
1167 l_housing_rec.effective_date := l_wic_rec.effective_date;
1168 l_housing_rec.action_information_category := 'JP_ITAX_HOUSING';
1169 l_housing_rec.action_information1 := fnd_number.number_to_canonical(l_housing_info.payable_loan);
1170 l_housing_rec.action_information2 := fnd_number.number_to_canonical(l_housing_info.loan_count);
1171 l_housing_rec.action_information3 := fnd_date.date_to_canonical(l_housing_info.residence_date_1);
1172 l_housing_rec.action_information4 := l_housing_info.loan_type_1;
1173 l_housing_rec.action_information5 := fnd_number.number_to_canonical(l_housing_info.loan_balance_1);
1174 l_housing_rec.action_information6 := fnd_date.date_to_canonical(l_housing_info.residence_date_2);
1175 l_housing_rec.action_information7 := l_housing_info.loan_type_2;
1176 l_housing_rec.action_information8 := fnd_number.number_to_canonical(l_housing_info.loan_balance_2);
1177 --
1178 -- Create the data
1179 --
1180 create_action_information(l_person_rec);
1181 l_action_information_ids.extend;
1182 l_action_information_ids(l_action_information_ids.last) := l_person_rec.action_information_id;
1183 --
1184 create_action_information(l_arch_rec);
1185 create_action_information(l_tax_rec);
1186 create_action_information(l_other_rec);
1187 create_action_information(l_dep_rec);
1188 create_action_information(l_other2_rec);
1189 create_action_information(l_housing_rec);
1190 create_action_information(l_tax_rec1);
1191 --
1192 end if;
1193 --
1194 end loop;
1195 --
1196 -- Delete old archive data
1197 --
1198 for l_archive_rec in csr_delete_archives(l_assignment_id, l_action_information_ids) loop
1199 --
1200 delete_archive(
1201 l_archive_rec.action_information_id,
1202 l_archive_rec.action_context_id,
1203 l_archive_rec.effective_date);
1204 --
1205 end loop;
1206 --
1207 hr_utility.set_location('Leaving: ' || c_proc, 100);
1208 --
1209 END ARCHIVE_CODE;
1210 -- +------------------------------------------------------------------------------------------------+
1211 -- |< DEINITIALIZATION_CODE >-----------------------------------------------------------------------|
1212 -- +------------------------------------------------------------------------------------------------+
1213 PROCEDURE DEINITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
1214 IS
1215 c_proc CONSTANT VARCHAR2(61) := c_package || 'DEINITIALIZATION_CODE';
1216 l_dummy varchar2(1);
1217 l_pact_rec pay_action_information%rowtype;
1218 --
1219 cursor csr_pa_exists is
1220 select 'Y'
1221 from dual
1222 where exists(
1223 select null
1224 from pay_action_information
1225 where action_context_id = p_payroll_action_id
1226 and action_context_type = 'PA');
1227 --
1228 cursor csr_assacts is
1229 select paa.assignment_action_id
1230 from pay_assignment_actions paa
1231 where paa.payroll_action_id = p_payroll_action_id
1232 and paa.action_status = 'C'
1233 and not exists(
1234 select null
1235 from pay_action_information pai
1236 where pai.action_context_id = paa.assignment_action_id
1237 and pai.action_context_type = 'AAP');
1238 BEGIN
1239 hr_utility.set_location('Entering: ' || c_proc, 10);
1240 --
1241 open csr_pa_exists;
1242 fetch csr_pa_exists into l_dummy;
1243 if csr_pa_exists%notfound then
1244 hr_utility.set_location(c_proc, 20);
1245 --
1246 initialization_code(p_payroll_action_id);
1247 --
1248 -- JP_ITAX_PACT
1249 --
1250 l_pact_rec.action_context_id := p_payroll_action_id;
1251 l_pact_rec.action_context_type := 'PA';
1252 l_pact_rec.action_information_category := 'JP_ITAX_PACT';
1253 l_pact_rec.effective_date := g_effective_date;
1254 l_pact_rec.action_information1 := fnd_number.number_to_canonical(g_payroll_id);
1255 l_pact_rec.action_information2 := fnd_number.number_to_canonical(g_itax_organization_id);
1256 l_pact_rec.action_information3 := g_include_terminated_flag;
1257 l_pact_rec.action_information4 := fnd_date.date_to_canonical(g_termination_date_from);
1258 l_pact_rec.action_information5 := fnd_date.date_to_canonical(g_termination_date_to);
1259 l_pact_rec.action_information6 := g_publication_period_status;
1260 l_pact_rec.action_information7 := fnd_date.date_to_canonical(g_publication_start_date);
1261 l_pact_rec.action_information8 := fnd_date.date_to_canonical(g_publication_end_date);
1262 --
1263 create_action_information(l_pact_rec);
1264 end if;
1265 --
1266 -- Delete completed assignment actions without PAY_ACTION_INFORMATION.
1267 --
1268 for l_rec in csr_assacts loop
1269 py_rollback_pkg.rollback_ass_action(l_rec.assignment_action_id);
1270 end loop;
1271 --
1272 hr_utility.set_location('Leaving: ' || c_proc, 100);
1273 END DEINITIALIZATION_CODE;
1274 --
1275 END PAY_JP_ITAX_ARCHIVE_PKG;