[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_WIC_PKG
Source
1 package body pay_jp_wic_pkg as
2 /* $Header: pyjpwic.pkb 120.14.12000000.9 2007/07/17 03:07:17 ttagawa noship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pay_jp_wic_pkg.';
7 FIXED_RATE_TAX_REDUCTION constant varchar2(30) := 'FIXED_RATE_TAX_REDUCTION';
8 NP_PREM constant varchar2(30) := 'NP_PREM';
9 DEPENDENT constant varchar2(30) := 'DEPENDENT';
10 PREV_JOB constant varchar2(30) := 'PREV_JOB';
11 HOUSING_TAX_REDUCTION constant varchar2(30) := 'HOUSING_TAX_REDUCTION';
12 DISASTER_TAX_REDUCTION constant varchar2(30) := 'DISASTER_TAX_REDUCTION';
13 ITW constant varchar2(30) := 'ITW';
14 WTM constant varchar2(30) := 'WTM';
15 --
16 -- Global Variables
17 --
18 type t_prompt is record(
19 yen varchar2(80),
20 fixed_rate_tax_reduction varchar2(80),
21 national_pens_prem varchar2(80),
22 prev_job varchar2(80),
23 taxable_income varchar2(80),
24 si_prem varchar2(80),
25 mutual_aid_prem varchar2(80),
26 itax varchar2(80),
27 terminated varchar2(80),
28 housing_tax_reduction varchar2(80),
29 residence_date varchar2(80),
30 unclaimed_housing_tax_reduct varchar2(80),
31 -- 2007/06/07
32 housing_tax_reduction_long varchar2(80),
33 total_housing_tax_reduction varchar2(80),
34 --
35 disaster_tax_reduction varchar2(80),
36 husband varchar2(80),
37 wife varchar2(80),
38 date_format varchar2(80),
39 other varchar2(80),
40 count varchar2(80));
41 g_prompt_kanji t_prompt;
42 g_prompt_kana t_prompt;
43 --
44 type t_taxable_income_exempt_elm is record(
45 element_type_id number,
46 net_taxable_income_iv number,
47 taxable_income_iv number,
48 prev_taxable_income_iv number,
49 prev_si_prem_iv number,
50 -- prev_itax_iv number,
51 taxable_income_adj_iv number,
52 si_prem_adj_iv number,
53 mutual_aid_prem_adj_iv number,
54 itax_adj_iv number);
55 g_taxable_income_exempt_elm t_taxable_income_exempt_elm;
56 --
57 type t_net_annual_itax_elm is record(
58 element_type_id number,
59 net_annual_itax_iv number,
60 housing_tax_reduction_iv number,
61 net_housing_tax_reduction_iv number,
62 fixed_rate_tax_reduction_iv number);
63 g_net_annual_itax_elm t_net_annual_itax_elm;
64 --
65 g_total_income_exempt_asg_run number;
66 g_itax_adjustment_asg_ytd number;
67 --g_itax_asg_ytd number;
68 --
69 type t_ins_prem_exempt_elm is record(
70 element_type_id number,
71 si_prem_iv number,
72 mutual_aid_prem_iv number,
73 li_prem_exempt_iv number,
74 pp_prem_iv number,
75 ai_prem_exempt_iv number,
76 long_ai_prem_iv number,
77 spouse_sp_exempt_iv number,
78 spouse_net_taxable_income_iv number);
79 g_ins_prem_exempt_elm t_ins_prem_exempt_elm;
80 --
81 type t_dep_exempt_elm is record(
82 element_type_id number,
83 disabled_iv number,
84 aged_iv number,
85 widow_iv number,
86 working_student_iv number,
87 spouse_type_iv number,
88 spouse_disabled_iv number,
89 num_deps_iv number,
90 num_ageds_iv number,
91 num_aged_parents_lt_iv number,
92 num_specifieds_iv number,
93 num_disableds_iv number,
94 num_svr_disableds_iv number,
95 num_svr_disableds_lt_iv number);
96 g_dep_exempt_elm t_dep_exempt_elm;
97 g_dep_exempt_result_elm t_dep_exempt_elm;
98 --
99 /*
100 type t_description_elm is record(
101 element_type_id number,
102 override_flag_iv number,
103 description_iv number,
104 mag_override_flag_iv number,
105 mag_description_iv number,
106 description_2_iv number,
107 description_3_iv number,
108 description_4_iv number,
109 description_5_iv number,
110 mag_description_2_iv number,
111 mag_description_3_iv number,
112 mag_description_4_iv number,
113 mag_description_5_iv number);
114 g_description_elm t_description_elm;
115 */
116 g_desc_element_type_id number;
117 --
118 type t_itax_info_elm is record(
119 element_type_id number,
120 foreigner_flag_iv number);
121 g_itax_info_elm t_itax_info_elm;
122 --
123 g_prev_job_elm number;
124 --
125 type t_housing_tax_reduction_elm is record(
126 element_type_id number,
127 residence_date_iv number);
128 g_housing_tax_reduction_elm t_housing_tax_reduction_elm;
129 --
130 type t_yea_ins_prem_sp_exm_info_elm is record(
131 element_type_id number,
132 national_pens_prem_iv number);
133 g_yea_ins_prem_sp_exm_info_elm t_yea_ins_prem_sp_exm_info_elm;
134 --
135 -- |-------------------------------------------------------------------|
136 -- |--------------------------< to_jp_char >---------------------------|
137 -- |-------------------------------------------------------------------|
138 function to_jp_char(
139 p_date in date,
140 p_date_format in varchar2) return varchar2
141 is
142 l_dummy varchar2(255);
143 begin
144 --
145 -- PL/SQL "to_char" has bug which does not work with "NLS" parameters.
146 -- We here use ORACLE "to_char" as a workaround.
147 --
148 if p_date is not null then
149 select to_char(p_date, p_date_format, 'NLS_CALENDAR=''Japanese Imperial''')
150 into l_dummy
151 from dual;
152 end if;
153 --
154 return l_dummy;
155 end to_jp_char;
156 -- |-------------------------------------------------------------------|
157 -- |-------------------------< decode_value >--------------------------|
158 -- |-------------------------------------------------------------------|
159 function decode_value(
160 p_condition in boolean,
161 p_value1 in number,
162 p_value2 in number default null) return number
163 is
164 begin
165 if p_condition then
166 return p_value1;
167 else
168 return p_value2;
169 end if;
170 end decode_value;
171 -- |-------------------------------------------------------------------|
172 -- |-------------------< get_withholding_tax_info >--------------------|
173 -- |-------------------------------------------------------------------|
174 -- This procedure grosses up from table PAY_JP_PRE_TAX used for
175 -- 1) derive current employer's payment for YEAed assignments
176 -- 2) derive non-YEAed amount including previous SWOTs' amount
177 procedure get_withholding_tax_info(
178 p_assignment_action_id in number,
179 p_assignment_id in number,
180 p_action_sequence in number,
181 p_effective_date in date,
182 p_itax_organization_id in number,
183 p_itax_category in varchar2,
184 p_itax_yea_category in varchar2,
185 p_withholding_tax_info out nocopy t_tax_info)
186 is
187 c_proc constant varchar2(61) := c_package || 'get_withholding_tax_info';
188 begin
189 hr_utility.set_location('Entering: ' || c_proc, 10);
190 --
191 if p_itax_yea_category = '0' then
192 hr_utility.set_location(c_proc, 11);
193 --
194 -- Grossup "Taxable Earnings", "Income Tax" etc.
195 -- with the same "SWOT".
196 --
197 select nvl(sum(taxable_amt), 0),
198 nvl(sum(mutual_aid), 0),
199 nvl(sum(si_prem), 0),
200 nvl(sum(itax), 0),
201 nvl(sum(itax_adjustment), 0),
202 nvl(sum(disaster_tax_reduction), 0)
203 into p_withholding_tax_info.taxable_income,
204 p_withholding_tax_info.mutual_aid_prem,
205 p_withholding_tax_info.si_prem,
206 p_withholding_tax_info.itax,
207 p_withholding_tax_info.itax_adjustment,
208 p_withholding_tax_info.disaster_tax_reduction
209 from pay_jp_wic_assacts_v2
210 where assignment_id = p_assignment_id
211 and action_sequence <= p_action_sequence
212 and effective_date >= trunc(p_effective_date, 'YYYY')
213 and itax_organization_id = p_itax_organization_id;
214 else
215 hr_utility.set_location(c_proc, 12);
216 --
217 -- Grossup "Taxable Earnings", "Income Tax" etc.
218 -- with the same "Tax Category" and "SWOT".
219 --
220 select nvl(sum(taxable_amt), 0),
221 nvl(sum(mutual_aid), 0),
222 nvl(sum(si_prem), 0),
223 nvl(sum(itax), 0),
224 nvl(sum(itax_adjustment), 0),
225 nvl(sum(disaster_tax_reduction), 0)
226 into p_withholding_tax_info.taxable_income,
227 p_withholding_tax_info.mutual_aid_prem,
228 p_withholding_tax_info.si_prem,
229 p_withholding_tax_info.itax,
230 p_withholding_tax_info.itax_adjustment,
231 p_withholding_tax_info.disaster_tax_reduction
232 from pay_jp_wic_assacts_v2
233 where assignment_id = p_assignment_id
234 and action_sequence <= p_action_sequence
235 and effective_date >= trunc(p_effective_date, 'YYYY')
236 and (
237 (p_itax_category in ('M_KOU', 'D_KOU') and itax_category in ('M_KOU', 'D_KOU'))
238 or (p_itax_category in ('M_OTSU', 'D_OTSU') and itax_category in ('M_OTSU', 'D_OTSU'))
239 or (p_itax_category = 'D_HEI' and itax_category = 'D_HEI')
240 )
241 and itax_organization_id = p_itax_organization_id;
242 end if;
243 --
244 hr_utility.set_location(c_proc, 20);
245 --
246 -- When current assignment_action_id is "YEA",
247 -- add the adjustment amount processed in YEA process.
248 -- Note if future assacts exist after YEA, it is not regarded as YEA,
249 -- it is just normal payment. In this case, no adjustment amount in YEA
250 -- is included to the total amount(Skip when null or '-1').
251 --
252 if p_itax_yea_category <> '-1' then
253 hr_utility.set_location(c_proc, 21);
254 --
255 p_withholding_tax_info.taxable_income := p_withholding_tax_info.taxable_income
256 + nvl(pay_jp_balance_pkg.get_result_value_number(g_taxable_income_exempt_elm.element_type_id, g_taxable_income_exempt_elm.taxable_income_adj_iv, p_assignment_action_id), 0);
257 p_withholding_tax_info.si_prem := p_withholding_tax_info.si_prem
258 + nvl(pay_jp_balance_pkg.get_result_value_number(g_taxable_income_exempt_elm.element_type_id, g_taxable_income_exempt_elm.si_prem_adj_iv, p_assignment_action_id), 0);
259 p_withholding_tax_info.mutual_aid_prem := p_withholding_tax_info.mutual_aid_prem
260 + nvl(pay_jp_balance_pkg.get_result_value_number(g_taxable_income_exempt_elm.element_type_id, g_taxable_income_exempt_elm.mutual_aid_prem_adj_iv, p_assignment_action_id), 0);
261 p_withholding_tax_info.itax := p_withholding_tax_info.itax
262 + nvl(pay_jp_balance_pkg.get_result_value_number(g_taxable_income_exempt_elm.element_type_id, g_taxable_income_exempt_elm.itax_adj_iv, p_assignment_action_id), 0);
263 end if;
264 --
265 p_withholding_tax_info.mutual_aid_prem := decode_value(p_withholding_tax_info.mutual_aid_prem <> 0, p_withholding_tax_info.mutual_aid_prem);
266 p_withholding_tax_info.disaster_tax_reduction := decode_value(p_withholding_tax_info.disaster_tax_reduction <> 0, p_withholding_tax_info.disaster_tax_reduction);
267 p_withholding_tax_info.withholding_itax := p_withholding_tax_info.itax + p_withholding_tax_info.itax_adjustment;
268 --
269 hr_utility.set_location('Leaving: ' || c_proc, 100);
270 end get_withholding_tax_info;
271 -- |-------------------------------------------------------------------|
272 -- |-------------------------< get_prev_jobs >-------------------------|
273 -- |-------------------------------------------------------------------|
274 -- Private Procedure
275 procedure get_prev_jobs(
276 p_assignment_id in number,
277 p_action_sequence in number,
278 p_business_group_id in number,
279 p_effective_date in date,
280 p_date_earned in date,
281 p_itax_organization_id in number,
282 p_person_id in number,
283 p_prev_jobs out nocopy t_prev_jobs)
284 is
285 c_proc constant varchar2(61) := c_package || 'get_prev_jobs';
286 l_withholding_tax_info t_tax_info;
287 l_prev_job_info t_prev_job_info;
288 l_found boolean;
289 l_upgrade_status varchar2(1);
290 --
291 cursor csr_swot is
292 select wic.assignment_action_id,
293 wic.assignment_id,
294 wic.action_sequence,
295 wic.effective_date,
296 wic.itax_organization_id,
297 wic.itax_category,
298 wic.itax_yea_category,
299 hoi.org_information1 SALARY_PAYER_NAME_KANJI,
300 hoi.org_information2 SALARY_PAYER_NAME_KANA,
301 hoi.org_information6 || hoi.org_information7 || hoi.org_information8 SALARY_PAYER_ADDRESS_KANJI,
302 hoi.org_information9 || hoi.org_information10 || hoi.org_information11 SALARY_PAYER_ADDRESS_KANA
303 from hr_organization_information hoi,
304 pay_jp_wic_assacts_v wic
305 where wic.assignment_id = p_assignment_id
306 and wic.action_sequence < p_action_sequence
307 and wic.effective_date >= trunc(p_effective_date, 'YYYY')
308 and wic.itax_organization_id <> p_itax_organization_id
309 -- The following condition removed because dimension (in YEA) can not handle this case.
310 -- and wic.itax_category in ('M_KOU', 'D_KOU', 'D_HEI')
311 and hoi.organization_id(+) = wic.itax_organization_id
312 and hoi.org_information_context(+) = 'JP_TAX_SWOT_INFO'
313 order by wic.action_sequence desc;
314 --
315 cursor csr_job_pem is
316 -- Added nvl(*, 0) except for mutual_aid_prem
317 select nvl(fnd_number.canonical_to_number(pem_information3), 0) taxable_income,
318 nvl(fnd_number.canonical_to_number(pem_information4), 0) si_prem,
319 fnd_number.canonical_to_number(pem_information5) mutual_aid_prem,
320 nvl(fnd_number.canonical_to_number(pem_information6), 0) itax,
321 end_date termination_date,
322 decode(employer_country, null, 'N', 'JP', 'N', 'Y') foreign_address_flag,
323 employer_name salary_payer_name_kanji,
324 employer_address salary_payer_address_kanji,
325 pem_information1 salary_payer_name_kana,
326 pem_information2 salary_payer_address_kana
327 from per_previous_employers
328 where person_id = p_person_id
329 --
330 -- Bug.4159708. Added new segment "Tax Year"(PEM_INFORMATION7)
331 --
332 and pem_information_category = 'JP'
333 and p_effective_date >= nvl(end_date, p_effective_date)
334 and (
335 (
336 pem_information7 is not null
337 and fnd_number.canonical_to_number(pem_information7) = to_number(to_char(p_effective_date, 'YYYY'))
338 )
339 or ( pem_information7 is null
340 and end_date >= trunc(p_effective_date, 'YYYY')
341 )
342 )
343 order by end_date desc;
344 --
345 cursor csr_job_pee is
346 -- Removed nvl(*, 0) from mutual_aid_prem
347 select nvl(sum(decode(piv.display_sequence, 1, to_number(peev.screen_entry_value))), 0) taxable_income,
348 nvl(sum(decode(piv.display_sequence, 2, to_number(peev.screen_entry_value))), 0) si_prem,
349 sum(decode(piv.display_sequence, 3, to_number(peev.screen_entry_value))) mutual_aid_prem,
350 nvl(sum(decode(piv.display_sequence, 4, to_number(peev.screen_entry_value))), 0) itax,
351 min(decode(piv.display_sequence, 5, fnd_date.canonical_to_date(peev.screen_entry_value))) termination_date,
352 nvl(min(decode(piv.display_sequence, 6, peev.screen_entry_value)), 'N') foreign_address_flag,
353 min(decode(piv.display_sequence, 7, peev.screen_entry_value)) salary_payer_address_kana,
354 min(decode(piv.display_sequence, 8, peev.screen_entry_value)) salary_payer_address_kanji,
355 min(decode(piv.display_sequence, 9, peev.screen_entry_value)) salary_payer_name_kana,
356 min(decode(piv.display_sequence, 10, peev.screen_entry_value)) salary_payer_name_kanji
357 from pay_input_values_f piv,
358 pay_element_entry_values_f peev,
359 pay_element_entries_f pee,
360 pay_element_links_f pel
361 where pel.element_type_id = g_prev_job_elm
362 and pel.business_group_id + 0 = p_business_group_id
363 and p_date_earned
364 between pel.effective_start_date and pel.effective_end_date
365 and pee.assignment_id = p_assignment_id
366 and pee.element_link_id = pel.element_link_id
367 and p_date_earned
368 between pee.effective_start_date and pee.effective_end_date
369 and peev.element_entry_id = pee.element_entry_id
370 and peev.effective_start_date = pee.effective_start_date
371 and peev.effective_end_date = pee.effective_end_date
372 and piv.input_value_id = peev.input_value_id
373 and p_date_earned
374 between piv.effective_start_date and piv.effective_end_date
375 group by pee.element_entry_id
376 order by termination_date desc;
377 begin
378 hr_utility.set_location('Entering: ' || c_proc, 10);
379 --
380 for l_rec in csr_swot loop
381 --
382 -- If the same itax_organization_id exist, existing information is replaced
383 -- by subsequent information, and the total amount is grossed up.
384 -- No need to call get_certificate_info because itax_yea_category of previous
385 -- employers' is "null" or "-1".
386 --
387 get_withholding_tax_info(
388 p_assignment_action_id => l_rec.assignment_action_id,
389 p_assignment_id => l_rec.assignment_id,
390 p_action_sequence => l_rec.action_sequence,
391 p_effective_date => l_rec.effective_date,
392 p_itax_organization_id => l_rec.itax_organization_id,
393 p_itax_category => l_rec.itax_category,
394 p_itax_yea_category => l_rec.itax_yea_category,
395 p_withholding_tax_info => l_withholding_tax_info);
396 --
397 l_found := false;
398 for i in 1..p_prev_jobs.count loop
399 l_prev_job_info := p_prev_jobs(i);
400 --
401 if l_prev_job_info.itax_organization_id = l_rec.itax_organization_id then
402 l_prev_job_info.taxable_income := l_prev_job_info.taxable_income + l_withholding_tax_info.taxable_income;
403 -- mutual_aid_prem can be <null>, so remember to use nvl before adding.
404 if l_withholding_tax_info.mutual_aid_prem <> 0 then
405 l_prev_job_info.mutual_aid_prem := nvl(l_prev_job_info.mutual_aid_prem, 0) + l_withholding_tax_info.mutual_aid_prem;
406 end if;
407 l_prev_job_info.si_prem := l_prev_job_info.si_prem + l_withholding_tax_info.si_prem;
408 l_prev_job_info.itax := l_prev_job_info.itax + l_withholding_tax_info.withholding_itax;
409 --
410 p_prev_jobs(i) := l_prev_job_info;
411 l_found := true;
412 exit;
413 end if;
414 end loop;
415 --
416 if not l_found then
417 l_prev_job_info := null;
418 --
419 l_prev_job_info.itax_organization_id := l_rec.itax_organization_id;
420 l_prev_job_info.taxable_income := l_withholding_tax_info.taxable_income;
421 l_prev_job_info.mutual_aid_prem := l_withholding_tax_info.mutual_aid_prem;
422 l_prev_job_info.mutual_aid_prem := decode_value(l_prev_job_info.mutual_aid_prem <> 0, l_prev_job_info.mutual_aid_prem);
423 l_prev_job_info.si_prem := l_withholding_tax_info.si_prem;
424 l_prev_job_info.itax := l_withholding_tax_info.withholding_itax;
425 l_prev_job_info.foreign_address_flag := 'N';
426 l_prev_job_info.salary_payer_address_kanji := l_rec.salary_payer_address_kanji;
427 l_prev_job_info.salary_payer_address_kana := l_rec.salary_payer_address_kana;
428 l_prev_job_info.salary_payer_name_kanji := l_rec.salary_payer_name_kanji;
429 l_prev_job_info.salary_payer_name_kana := l_rec.salary_payer_name_kana;
430 --
431 -- Bug.5116358
432 -- Changed not to set termination_date for previous SWOTs
433 -- in the same company.
434 --
435 -- l_prev_job_info.termination_date := l_rec.effective_date;
436 --
437 p_prev_jobs(p_prev_jobs.count + 1) := l_prev_job_info;
438 end if;
439 end loop;
440 --
441 hr_utility.set_location(c_proc, 20);
442 --
443 pay_core_utils.get_upgrade_status(
444 P_BUS_GRP_ID => p_business_group_id,
445 P_SHORT_NAME => 'JP_PREV_JOB',
446 P_STATUS => l_upgrade_status,
447 P_RAISE_ERROR => true);
448 --
449 if l_upgrade_status = 'Y' then
450 hr_utility.set_location(c_proc, 21);
451 --
452 for l_rec in csr_job_pem loop
453 l_prev_job_info := null;
454 --
455 -- itax_organization_id is null in element entry case.
456 --
457 l_prev_job_info.taxable_income := l_rec.taxable_income;
458 l_prev_job_info.mutual_aid_prem := l_rec.mutual_aid_prem;
459 l_prev_job_info.si_prem := l_rec.si_prem;
460 l_prev_job_info.itax := l_rec.itax;
461 l_prev_job_info.foreign_address_flag := l_rec.foreign_address_flag;
462 l_prev_job_info.salary_payer_address_kanji := l_rec.salary_payer_address_kanji;
463 l_prev_job_info.salary_payer_address_kana := l_rec.salary_payer_address_kana;
464 l_prev_job_info.salary_payer_name_kanji := l_rec.salary_payer_name_kanji;
465 l_prev_job_info.salary_payer_name_kana := l_rec.salary_payer_name_kana;
466 l_prev_job_info.termination_date := l_rec.termination_date;
467 --
468 p_prev_jobs(p_prev_jobs.count + 1) := l_prev_job_info;
469 end loop;
470 else
471 hr_utility.set_location(c_proc, 22);
472 --
473 for l_rec in csr_job_pee loop
474 l_prev_job_info := null;
475 --
476 -- itax_organization_id is null in element entry case.
477 --
478 l_prev_job_info.taxable_income := l_rec.taxable_income;
479 l_prev_job_info.mutual_aid_prem := l_rec.mutual_aid_prem;
480 l_prev_job_info.si_prem := l_rec.si_prem;
481 l_prev_job_info.itax := l_rec.itax;
482 l_prev_job_info.foreign_address_flag := l_rec.foreign_address_flag;
483 l_prev_job_info.salary_payer_address_kanji := l_rec.salary_payer_address_kanji;
484 l_prev_job_info.salary_payer_address_kana := l_rec.salary_payer_address_kana;
485 l_prev_job_info.salary_payer_name_kanji := l_rec.salary_payer_name_kanji;
486 l_prev_job_info.salary_payer_name_kana := l_rec.salary_payer_name_kana;
487 l_prev_job_info.termination_date := l_rec.termination_date;
488 --
489 p_prev_jobs(p_prev_jobs.count + 1) := l_prev_job_info;
490 end loop;
491 end if;
492 --
493 hr_utility.set_location(c_proc, 30);
494 --
495 for i in 1..p_prev_jobs.count loop
496 p_prev_jobs(i).mutual_aid_prem := decode_value(p_prev_jobs(i).mutual_aid_prem <> 0, p_prev_jobs(i).mutual_aid_prem);
497 end loop;
498 --
499 hr_utility.set_location('Leaving: ' || c_proc, 100);
500 end get_prev_jobs;
501 --
502 function convert_prev_jobs(p_prev_jobs t_prev_jobs) return t_prev_job_info
503 is
504 l_prev_jobs_count number;
505 l_prev_job_info t_prev_job_info;
506 begin
507 l_prev_jobs_count := p_prev_jobs.count;
508 --
509 -- Null to clear default values when no prev jobs found.
510 --
511 if l_prev_jobs_count = 0 then
512 l_prev_job_info := null;
513 else
514 for i in 1..l_prev_jobs_count loop
515 if i = 1 then
516 l_prev_job_info := p_prev_jobs(i);
517 --
518 l_prev_job_info.salary_payer_address_kanji := hr_jp_standard_pkg.to_zenkaku(l_prev_job_info.salary_payer_address_kanji);
519 l_prev_job_info.salary_payer_address_kana := hr_jp_standard_pkg.upper_kana(
520 hr_jp_standard_pkg.to_hankaku(l_prev_job_info.salary_payer_address_kana, '?'));
521 --
522 if l_prev_jobs_count > 1 then
523 l_prev_job_info.salary_payer_name_kanji := ltrim( l_prev_job_info.salary_payer_name_kanji
524 || ' '
525 || g_prompt_kanji.other
526 || to_char(l_prev_jobs_count - 1)
527 || g_prompt_kanji.count);
528 --
529 l_prev_job_info.salary_payer_name_kana := ltrim( l_prev_job_info.salary_payer_name_kana
530 || ' '
531 || g_prompt_kana.other
532 || to_char(l_prev_jobs_count - 1)
533 || g_prompt_kana.count);
534 end if;
535 --
536 l_prev_job_info.salary_payer_name_kanji := hr_jp_standard_pkg.to_zenkaku(l_prev_job_info.salary_payer_name_kanji);
537 l_prev_job_info.salary_payer_name_kana := hr_jp_standard_pkg.upper_kana(
538 hr_jp_standard_pkg.to_hankaku(l_prev_job_info.salary_payer_name_kana, '?'));
539 else
540 l_prev_job_info.taxable_income := l_prev_job_info.taxable_income + p_prev_jobs(i).taxable_income;
541 -- mutual_aid_prem can be <null>, so remember to use nvl before adding.
542 if p_prev_jobs(i).mutual_aid_prem <> 0 then
543 l_prev_job_info.mutual_aid_prem := nvl(l_prev_job_info.mutual_aid_prem, 0) + p_prev_jobs(i).mutual_aid_prem;
544 end if;
545 l_prev_job_info.si_prem := l_prev_job_info.si_prem + p_prev_jobs(i).si_prem;
546 l_prev_job_info.itax := l_prev_job_info.itax + p_prev_jobs(i).itax;
547 end if;
548 end loop;
549 end if;
550 --
551 return l_prev_job_info;
552 end convert_prev_jobs;
553 -- |-------------------------------------------------------------------|
554 -- |------------------< get_basic_certificate_info >-------------------|
555 -- |-------------------------------------------------------------------|
556 -- Source function for ITT
557 procedure get_basic_certificate_info(
558 p_assignment_action_id in number,
559 p_assignment_id in number,
560 p_action_sequence in number,
561 p_effective_date in date,
562 p_itax_organization_id in number,
563 p_itax_category in varchar2,
564 p_itax_yea_category in varchar2,
565 p_employment_category in varchar2,
566 p_certificate_info out nocopy t_tax_info,
567 p_submission_required_flag out nocopy varchar2)
568 is
569 c_proc constant varchar2(61) := c_package || 'get_basic_certificate_info (1)';
570 begin
571 hr_utility.set_location('Entering: ' || c_proc, 10);
572 --
573 if p_itax_yea_category = '0' then
574 hr_utility.set_location(c_proc, 21);
575 --
576 p_certificate_info.taxable_income := nvl(pay_jp_balance_pkg.get_result_value_number(g_taxable_income_exempt_elm.element_type_id, g_taxable_income_exempt_elm.taxable_income_iv, p_assignment_action_id), 0);
577 p_certificate_info.mutual_aid_prem := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id, g_ins_prem_exempt_elm.mutual_aid_prem_iv, p_assignment_action_id), 0);
578 p_certificate_info.mutual_aid_prem := decode_value(p_certificate_info.mutual_aid_prem <> 0, p_certificate_info.mutual_aid_prem);
579 p_certificate_info.si_prem := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id, g_ins_prem_exempt_elm.si_prem_iv, p_assignment_action_id), 0);
580 /*
581 p_certificate_info.itax := pay_jp_balance_pkg.get_balance_value(g_itax_asg_ytd, p_assignment_action_id)
582 + nvl(pay_jp_balance_pkg.get_result_value_number(g_taxable_income_exempt_elm.element_type_id, g_taxable_income_exempt_elm.prev_itax_iv, p_assignment_action_id), 0)
583 + nvl(pay_jp_balance_pkg.get_result_value_number(g_taxable_income_exempt_elm.element_type_id, g_taxable_income_exempt_elm.itax_adj_iv, p_assignment_action_id), 0);
584 */
585 p_certificate_info.itax_adjustment := pay_jp_balance_pkg.get_balance_value(g_itax_adjustment_asg_ytd, p_assignment_action_id);
586 p_certificate_info.withholding_itax := nvl(pay_jp_balance_pkg.get_result_value_number(g_net_annual_itax_elm.element_type_id, g_net_annual_itax_elm.net_annual_itax_iv, p_assignment_action_id), 0);
587 p_certificate_info.itax := p_certificate_info.withholding_itax - p_certificate_info.itax_adjustment;
588 --
589 -- No need to calculate Disaster Tax Reduction in case of YEA because it should be ZERO.
590 --
591 else
592 hr_utility.set_location(c_proc, 22);
593 --
594 -- Derive tax information withheld by current employer.
595 --
596 get_withholding_tax_info(
597 p_assignment_action_id => p_assignment_action_id,
598 p_assignment_id => p_assignment_id,
599 p_action_sequence => p_action_sequence,
600 p_effective_date => p_effective_date,
601 p_itax_organization_id => p_itax_organization_id,
602 p_itax_category => p_itax_category,
603 p_itax_yea_category => p_itax_yea_category,
604 p_withholding_tax_info => p_certificate_info);
605 end if;
606 --
607 hr_utility.set_location(c_proc, 30);
608 --
609 -- Check whether this Tax Receipt is required to submit to Tax Office or not.
610 --
611 p_submission_required_flag := 'N';
612 if to_number(to_char(p_effective_date, 'YYYY')) between 0 and 4712 then
613 if p_itax_yea_category = '0' then
614 if p_employment_category = 'JP_EX' then
615 if p_certificate_info.taxable_income > 1500000 then
616 p_submission_required_flag := 'Y';
617 end if;
618 else
619 if p_certificate_info.taxable_income > 5000000 then
620 p_submission_required_flag := 'Y';
621 end if;
622 end if;
623 else
624 if p_itax_category in ('M_KOU', 'D_KOU') then
625 if p_employment_category = 'JP_EX' then
626 if p_certificate_info.taxable_income > 500000 then
627 p_submission_required_flag := 'Y';
628 end if;
629 else
630 if p_certificate_info.taxable_income > 2500000 then
631 p_submission_required_flag := 'Y';
632 end if;
633 end if;
634 else
635 if p_certificate_info.taxable_income > 500000 then
636 p_submission_required_flag := 'Y';
637 end if;
638 end if;
639 end if;
640 end if;
641 --
642 hr_utility.set_location('Leaving: ' || c_proc, 100);
643 end get_basic_certificate_info;
644 --
645 -- Following is deprecated.
646 --
647 procedure get_certificate_info(
648 p_assignment_action_id in number,
649 p_assignment_id in number,
650 p_action_sequence in number,
651 p_effective_date in date,
652 p_itax_organization_id in number,
653 p_itax_category in varchar2,
654 p_itax_yea_category in varchar2,
655 p_employment_category in varchar2,
656 p_person_id in number,
657 p_business_group_id in number,
658 p_date_earned in date,
659 p_certificate_info out nocopy t_tax_info,
660 p_submission_required_flag out nocopy varchar2,
661 p_withholding_tax_info out nocopy t_tax_info,
662 p_prev_jobs out nocopy t_prev_jobs)
663 is
664 begin
665 get_certificate_info(
666 p_assignment_action_id => p_assignment_action_id,
667 p_assignment_id => p_assignment_id,
668 p_action_sequence => p_action_sequence,
669 p_effective_date => p_effective_date,
670 p_itax_organization_id => p_itax_organization_id,
671 p_itax_category => p_itax_category,
672 p_itax_yea_category => p_itax_yea_category,
673 p_employment_category => p_employment_category,
674 p_person_id => p_person_id,
675 p_business_group_id => p_business_group_id,
676 p_date_earned => p_date_earned,
677 p_certificate_info => p_certificate_info,
678 p_submission_required_flag => p_submission_required_flag,
679 p_withholding_tax_info => p_withholding_tax_info);
680 --
681 get_prev_jobs(
682 p_assignment_id => p_assignment_id,
683 p_action_sequence => p_action_sequence,
684 p_business_group_id => p_business_group_id,
685 p_effective_date => p_effective_date,
686 p_date_earned => p_date_earned,
687 p_itax_organization_id => p_itax_organization_id,
688 p_person_id => p_person_id,
689 p_prev_jobs => p_prev_jobs);
690 end get_certificate_info;
691 -- |-------------------------------------------------------------------|
692 -- |---------------------------< get_dpnts >---------------------------|
693 -- |-------------------------------------------------------------------|
694 -- Private Procedure
695 procedure get_dpnts(
696 p_dpnt_ref_type in varchar2,
697 p_assignment_id in number,
698 p_itax_category in varchar2,
699 p_dpnt_effective_date in date,
700 p_person_id in number,
701 p_effective_date in date,
702 p_sex in varchar2,
703 p_dpnts out nocopy t_dpnts)
704 is
705 c_proc constant varchar2(61) := c_package || 'get_dpnts';
706 l_index number := 0;
707 l_dpnt_rec per_jp_ctr_utility_pkg.t_itax_dpnt_rec;
708 --
709 cursor csr_dep is
710 select /*+ ORDERED USE_NL(PER) */
711 per.last_name LAST_NAME_KANA,
712 per.per_information18 LAST_NAME_KANJI,
713 per.first_name FIRST_NAME_KANA,
714 per.per_information19 FIRST_NAME_KANJI,
715 per.date_of_birth,
716 decode(ctr.contact_type, 'S', decode(p_sex, 'F', g_prompt_kanji.husband, g_prompt_kanji.wife),
717 hr_general.decode_lookup('CONTACT', ctr.contact_type)) D_CONTACT_TYPE_KANJI,
718 decode(ctr.contact_type, 'S', decode(p_sex, 'F', g_prompt_kana.husband, g_prompt_kana.wife),
719 hr_jp_standard_pkg.to_hankaku(hr_general.decode_lookup('JP_CONTACT_KANA', ctr.contact_type))) D_CONTACT_TYPE_KANA
720 from per_contact_relationships ctr,
721 per_all_people_f per
722 where ctr.person_id = p_person_id
723 and ctr.dependent_flag = 'Y'
724 and p_effective_date
725 between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date)
726 and per.person_id = ctr.contact_person_id
727 and ( p_effective_date
728 between per.effective_start_date and per.effective_end_date
729 or (
730 per.effective_start_date = per.start_date
731 and not exists(
732 select null
733 from per_all_people_f per2
734 where per2.person_id = per.person_id
735 and p_effective_date
736 between per2.effective_start_date and per2.effective_end_date)
737 )
738 )
739 order by
740 decode(ctr.contact_type, 'S', 1, 2),
741 per.date_of_birth,
742 decode(per.sex, 'M', 1, 'F', 2, 3),
743 per.last_name,
744 per.first_name;
745 begin
746 hr_utility.set_location('Entering: ' || c_proc, 10);
747 --
748 if p_itax_category in ('M_KOU', 'M_OTSU', 'D_KOU', 'D_OTSU') then
749 hr_utility.set_location(c_proc, 20);
750 --
751 if p_dpnt_ref_type like 'CEI%' then
752 hr_utility.set_location(c_proc, 21);
753 --
754 -- No need to care for performance issue for multiple calls of the following PL/SQL function.
755 -- The following function caches the output variable result for the same input parameter values.
756 --
757 per_jp_ctr_utility_pkg.get_itax_dpnt_info(
758 p_assignment_id => p_assignment_id,
759 p_itax_type => p_itax_category,
760 p_effective_date => p_dpnt_effective_date,
761 p_itax_dpnt_rec => l_dpnt_rec);
762 --
763 for i in 1..l_dpnt_rec.contact_type_tbl.count loop
764 l_index := l_index + 1;
765 --
766 p_dpnts(l_index).contact_type_kanji := l_dpnt_rec.d_contact_type_kanji_tbl(i);
767 p_dpnts(l_index).contact_type_kana := l_dpnt_rec.d_contact_type_kana_tbl(i);
768 p_dpnts(l_index).last_name_kanji := l_dpnt_rec.last_name_kanji_tbl(i);
769 p_dpnts(l_index).last_name_kana := l_dpnt_rec.last_name_kana_tbl(i);
770 p_dpnts(l_index).first_name_kanji := l_dpnt_rec.first_name_kanji_tbl(i);
771 p_dpnts(l_index).first_name_kana := l_dpnt_rec.first_name_kana_tbl(i);
772 end loop;
773 else
774 hr_utility.set_location(c_proc, 22);
775 --
776 for l_rec in csr_dep loop
777 l_index := l_index + 1;
778 --
779 p_dpnts(l_index).contact_type_kanji := l_rec.d_contact_type_kanji;
780 p_dpnts(l_index).contact_type_kana := l_rec.d_contact_type_kana;
781 p_dpnts(l_index).last_name_kanji := l_rec.last_name_kanji;
782 p_dpnts(l_index).last_name_kana := l_rec.last_name_kana;
783 p_dpnts(l_index).first_name_kanji := l_rec.first_name_kanji;
784 p_dpnts(l_index).first_name_kana := l_rec.first_name_kana;
785 end loop;
786 end if;
787 end if;
788 --
789 hr_utility.set_location('Leaving: ' || c_proc, 100);
790 end get_dpnts;
791 -- |-------------------------------------------------------------------|
792 -- |----------------------< get_ee_description >-----------------------|
793 -- |-------------------------------------------------------------------|
794 procedure get_ee_description(
795 p_assignment_id in number,
796 p_business_group_id in number,
797 p_effective_date in date,
798 p_itw_override_flag out nocopy varchar2,
799 p_itw_description1 out nocopy varchar2,
800 p_itw_description2 out nocopy varchar2,
801 p_itw_description3 out nocopy varchar2,
802 p_itw_description4 out nocopy varchar2,
803 p_itw_description5 out nocopy varchar2,
804 p_wtm_override_flag out nocopy varchar2,
805 p_wtm_description1 out nocopy varchar2,
806 p_wtm_description2 out nocopy varchar2,
807 p_wtm_description3 out nocopy varchar2,
808 p_wtm_description4 out nocopy varchar2,
809 p_wtm_description5 out nocopy varchar2)
810 is
811 c_proc constant varchar2(61) := c_package || 'get_ee_description';
812 --
813 cursor csr_eev is
814 select /*+ ORDERD USE_NL(PEE PEEV PIV) */
815 nvl(min(decode(piv.display_sequence, 1, peev.screen_entry_value)), 'N') itw_override_flag,
816 min(decode(piv.display_sequence, 2, peev.screen_entry_value)) itw_description1,
817 min(decode(piv.display_sequence, 3, peev.screen_entry_value)) itw_description2,
818 min(decode(piv.display_sequence, 4, peev.screen_entry_value)) itw_description3,
819 min(decode(piv.display_sequence, 5, peev.screen_entry_value)) itw_description4,
820 min(decode(piv.display_sequence, 6, peev.screen_entry_value)) itw_description5,
821 nvl(min(decode(piv.display_sequence, 7, peev.screen_entry_value)), 'N') wtm_override_flag,
822 min(decode(piv.display_sequence, 8, peev.screen_entry_value)) wtm_description1,
823 min(decode(piv.display_sequence, 9, peev.screen_entry_value)) wtm_description2,
824 min(decode(piv.display_sequence, 10, peev.screen_entry_value)) wtm_description3,
825 min(decode(piv.display_sequence, 11, peev.screen_entry_value)) wtm_description4,
826 min(decode(piv.display_sequence, 12, peev.screen_entry_value)) wtm_description5
827 from pay_element_links_f pel,
828 pay_element_entries_f pee,
829 pay_element_entry_values_f peev,
830 pay_input_values_f piv
831 where pel.element_type_id = g_desc_element_type_id
832 and pel.business_group_id = p_business_group_id
833 and p_effective_date
834 between pel.effective_start_date and pel.effective_end_date
835 and pee.assignment_id = p_assignment_id
836 and pee.element_link_id = pel.element_link_id
837 and pee.entry_type = 'E'
838 and p_effective_date
839 between pee.effective_start_date and pee.effective_end_date
840 and peev.element_entry_id = pee.element_entry_id
841 and peev.effective_start_date = pee.effective_start_date
842 and peev.effective_end_date = pee.effective_end_date
843 and piv.input_value_id = peev.input_value_id
844 and p_effective_date
845 between piv.effective_start_date and piv.effective_end_date
846 group by pee.element_entry_id;
847 begin
848 hr_utility.set_location('Entering: ' || c_proc, 10);
849 --
850 open csr_eev;
851 fetch csr_eev into
852 p_itw_override_flag,
853 p_itw_description1,
854 p_itw_description2,
855 p_itw_description3,
856 p_itw_description4,
857 p_itw_description5,
858 p_wtm_override_flag,
859 p_wtm_description1,
860 p_wtm_description2,
861 p_wtm_description3,
862 p_wtm_description4,
863 p_wtm_description5;
864 if csr_eev%notfound then
865 hr_utility.trace('EE not found.');
866 --
867 p_itw_override_flag := 'N';
868 p_itw_description1 := null;
869 p_itw_description2 := null;
870 p_itw_description3 := null;
871 p_itw_description4 := null;
872 p_itw_description5 := null;
873 p_wtm_override_flag := 'N';
874 p_wtm_description1 := null;
875 p_wtm_description2 := null;
876 p_wtm_description3 := null;
877 p_wtm_description4 := null;
878 p_wtm_description5 := null;
879 end if;
880 close csr_eev;
881 --
882 hr_utility.set_location('Leaving: ' || c_proc, 100);
883 end get_ee_description;
884 --
885 procedure get_ee_description(
886 p_assignment_id in number,
887 p_business_group_id in number,
888 p_effective_date in date,
889 p_itw_override_flag out nocopy varchar2,
890 p_itw_description out nocopy varchar2,
891 p_wtm_override_flag out nocopy varchar2,
892 p_wtm_description out nocopy varchar2)
893 is
894 l_itw_description1 pay_element_entry_values.screen_entry_value%type;
895 l_itw_description2 pay_element_entry_values.screen_entry_value%type;
896 l_itw_description3 pay_element_entry_values.screen_entry_value%type;
897 l_itw_description4 pay_element_entry_values.screen_entry_value%type;
898 l_itw_description5 pay_element_entry_values.screen_entry_value%type;
899 l_wtm_description1 pay_element_entry_values.screen_entry_value%type;
900 l_wtm_description2 pay_element_entry_values.screen_entry_value%type;
901 l_wtm_description3 pay_element_entry_values.screen_entry_value%type;
902 l_wtm_description4 pay_element_entry_values.screen_entry_value%type;
903 l_wtm_description5 pay_element_entry_values.screen_entry_value%type;
904 begin
905 get_ee_description(
906 p_assignment_id => p_assignment_id,
907 p_business_group_id => p_business_group_id,
908 p_effective_date => p_effective_date,
909 p_itw_override_flag => p_itw_override_flag,
910 p_itw_description1 => l_itw_description1,
911 p_itw_description2 => l_itw_description2,
912 p_itw_description3 => l_itw_description3,
913 p_itw_description4 => l_itw_description4,
914 p_itw_description5 => l_itw_description5,
915 p_wtm_override_flag => p_wtm_override_flag,
916 p_wtm_description1 => l_wtm_description1,
917 p_wtm_description2 => l_wtm_description2,
918 p_wtm_description3 => l_wtm_description3,
919 p_wtm_description4 => l_wtm_description4,
920 p_wtm_description5 => l_wtm_description5);
921 --
922 p_itw_description := l_itw_description1
923 || l_itw_description2
924 || l_itw_description3
925 || l_itw_description4
926 || l_itw_description5;
927 p_wtm_description := l_wtm_description1
928 || l_wtm_description2
929 || l_wtm_description3
930 || l_wtm_description4
931 || l_wtm_description5;
932 end get_ee_description;
933 -- |-------------------------------------------------------------------|
934 -- |------------------------< add_description >------------------------|
935 -- |-------------------------------------------------------------------|
936 procedure add_description(
937 p_description_type in varchar2,
938 p_description_kanji in varchar2,
939 p_description_kana in varchar2 default null,
940 p_descriptions in out nocopy t_descriptions)
941 is
942 l_index number;
943 begin
944 l_index := p_descriptions.count + 1;
945 --
946 -- PAYJPITW outputs mixed Kanji/Kana characters
947 -- into description field, so do not use
948 -- hr_jp_standard_pkg.to_zenkaku.
949 -- Use hr_jp_standard_pkg.to_zenkaku just before
950 -- outputting those in reports, e.g. PAYJPWTM.
951 --
952 p_descriptions(l_index).description_type := p_description_type;
953 p_descriptions(l_index).description_kanji := p_description_kanji;
954 -- p_descriptions(l_index).description_kana := hr_jp_standard_pkg.upper_kana(
955 -- hr_jp_standard_pkg.to_hankaku(p_description_kana, '?'));
956 p_descriptions(l_index).description_kana := hr_jp_standard_pkg.to_hankaku(p_description_kana, '?');
957 end add_description;
958 -- |-------------------------------------------------------------------|
959 -- |-----------------------< get_descriptions >------------------------|
960 -- |-------------------------------------------------------------------|
961 procedure get_descriptions(
962 p_assignment_id in number,
963 p_person_id in number,
964 p_effective_date in date,
965 p_itax_yea_category in varchar2,
966 p_certificate_info in t_certificate_info,
967 p_last_name_kanji in varchar2,
968 p_last_name_kana in varchar2,
969 p_dpnts in t_dpnts,
970 p_prev_jobs in t_prev_jobs,
971 p_report_type in varchar2, --> ITW/WTM
972 p_descriptions out nocopy t_descriptions)
973 is
974 c_proc constant varchar2(61) := c_package || 'get_descriptions';
975 l_description_kanji varchar2(32767);
976 l_description_kana varchar2(32767);
977 l_separator varchar2(1);
978 --
979 procedure add_desc(
980 p_description_type in varchar2,
981 p_prefix_kanji in varchar2,
982 p_prefix_kana in varchar2,
983 p_amount in number,
984 p_min_length_kanji in number,
985 p_min_length_kana in number)
986 is
987 l_description_kanji varchar2(32767);
988 l_description_kana varchar2(32767);
989 l_length number;
990 begin
991 l_description_kanji := to_char(p_amount);
992 l_description_kana := l_description_kanji;
993 --
994 if p_min_length_kanji is not null or p_min_length_kana is not null then
995 l_length := nvl(length(l_description_kanji), 0);
996 --
997 if l_length < p_min_length_kanji then
998 l_description_kanji := lpad(
999 nvl(l_description_kanji, ' '),
1000 p_min_length_kanji,
1001 ' ');
1002 end if;
1003 --
1004 if l_length < p_min_length_kana then
1005 l_description_kana := lpad(
1006 nvl(l_description_kana, ' '),
1007 p_min_length_kana,
1008 ' ');
1009 end if;
1010 end if;
1011 --
1012 l_description_kanji := p_prefix_kanji
1013 || l_description_kanji
1014 || g_prompt_kanji.yen;
1015 l_description_kana := p_prefix_kana
1016 || l_description_kana
1017 || g_prompt_kana.yen;
1018 --
1019 add_description(
1020 p_description_type,
1021 l_description_kanji,
1022 l_description_kana,
1023 p_descriptions);
1024 end add_desc;
1025 --
1026 procedure add_fixed_rate_description(
1027 p_fixed_rate_tax_reduction in number,
1028 p_length_kanji in number default null,
1029 p_length_kana in number default null)
1030 is
1031 begin
1032 add_desc(
1033 FIXED_RATE_TAX_REDUCTION,
1034 g_prompt_kanji.fixed_rate_tax_reduction,
1035 g_prompt_kana.fixed_rate_tax_reduction,
1036 p_fixed_rate_tax_reduction,
1037 p_length_kanji,
1038 p_length_kana);
1039 end add_fixed_rate_description;
1040 --
1041 procedure add_np_prem_description(
1042 p_np_prem in number,
1043 p_length_kanji in number default null,
1044 p_length_kana in number default null)
1045 is
1046 begin
1047 add_desc(
1048 NP_PREM,
1049 g_prompt_kanji.national_pens_prem,
1050 g_prompt_kana.national_pens_prem,
1051 p_np_prem,
1052 p_length_kanji,
1053 p_length_kana);
1054 end add_np_prem_description;
1055 begin
1056 hr_utility.set_location('Entering: ' || c_proc, 10);
1057 --
1058 -- ************************
1059 -- FIXED_RATE_TAX_REDUCTION
1060 -- NP_PREM
1061 -- ************************
1062 if p_itax_yea_category = '0' then
1063 if p_report_type = ITW then
1064 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
1065 add_fixed_rate_description(p_certificate_info.fixed_rate_tax_reduction, 6, 6);
1066 end if;
1067 --
1068 if to_number(to_char(p_effective_date, 'YYYY')) >= 2005 then
1069 add_np_prem_description(p_certificate_info.national_pens_prem, 4, 3);
1070 end if;
1071 else
1072 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
1073 add_fixed_rate_description(p_certificate_info.fixed_rate_tax_reduction);
1074 end if;
1075 --
1076 if to_number(to_char(p_effective_date, 'YYYY')) >= 2005 then
1077 add_np_prem_description(p_certificate_info.national_pens_prem);
1078 end if;
1079 end if;
1080 else
1081 if p_report_type = ITW then
1082 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
1083 add_fixed_rate_description(null, 6, 6);
1084 end if;
1085 --
1086 if to_number(to_char(p_effective_date, 'YYYY')) >= 2005 then
1087 add_np_prem_description(null, 4, 3);
1088 end if;
1089 end if;
1090 end if;
1091 --
1092 hr_utility.set_location(c_proc, 20);
1093 --
1094 -- ************************
1095 -- DEPENDENT
1096 -- ************************
1097 -- Omit last_name when different as follows.
1098 -- 1) null/null -> omit last_name
1099 -- 2) null/B -> omit last_name
1100 -- 3) A/null -> omit last_name
1101 -- 4) A/A -> omit last_name
1102 -- 5) A/B -> Never omit last_name
1103 --
1104 for i in 1..p_dpnts.count loop
1105 if (p_dpnts(i).last_name_kana <> p_last_name_kana)
1106 or (p_dpnts(i).last_name_kanji <> p_last_name_kanji) then
1107 l_description_kanji := p_dpnts(i).contact_type_kanji ||
1108 ' ' ||
1109 p_dpnts(i).last_name_kanji ||
1110 p_dpnts(i).first_name_kanji;
1111 l_description_kana := p_dpnts(i).contact_type_kana ||
1112 ' ' ||
1113 p_dpnts(i).last_name_kana ||
1114 p_dpnts(i).first_name_kana;
1115 else
1116 l_description_kanji := p_dpnts(i).contact_type_kanji ||
1117 ' ' ||
1118 p_dpnts(i).first_name_kanji;
1119 l_description_kana := p_dpnts(i).contact_type_kana ||
1120 ' ' ||
1121 p_dpnts(i).first_name_kana;
1122 end if;
1123 --
1124 add_description(
1125 DEPENDENT,
1126 l_description_kanji,
1127 l_description_kana,
1128 p_descriptions);
1129 end loop;
1130 --
1131 hr_utility.set_location(c_proc, 30);
1132 --
1133 -- The following information is available only for YEAed assact.
1134 --
1135 if p_itax_yea_category = '0' then
1136 hr_utility.set_location(c_proc, 31);
1137 --
1138 -- ************************
1139 -- PREV_JOB
1140 -- ************************
1141 for i in 1..p_prev_jobs.count loop
1142 --
1143 -- <Salary>
1144 --
1145 l_description_kanji := g_prompt_kanji.prev_job ||
1146 g_prompt_kanji.taxable_income ||
1147 to_char(p_prev_jobs(i).taxable_income) ||
1148 g_prompt_kanji.yen;
1149 l_description_kana := g_prompt_kana.prev_job ||
1150 g_prompt_kana.taxable_income ||
1151 to_char(p_prev_jobs(i).taxable_income) ||
1152 g_prompt_kana.yen;
1153 --
1154 -- <Social Insurance Premium>
1155 --
1156 l_description_kanji := l_description_kanji || ' ' ||
1157 g_prompt_kanji.si_prem ||
1158 to_char(p_prev_jobs(i).si_prem) ||
1159 g_prompt_kanji.yen;
1160 l_description_kana := l_description_kana || ' ' ||
1161 g_prompt_kana.si_prem ||
1162 to_char(p_prev_jobs(i).si_prem) ||
1163 g_prompt_kana.yen;
1164 --
1165 -- <Small-enterprise Mutual Aid Premium>
1166 --
1167 if p_prev_jobs(i).mutual_aid_prem <> 0 then
1168 l_description_kanji := l_description_kanji || '(' ||
1169 g_prompt_kanji.mutual_aid_prem ||
1170 to_char(p_prev_jobs(i).mutual_aid_prem) ||
1171 g_prompt_kanji.yen || ')';
1172 l_description_kana := l_description_kana || '(' ||
1173 g_prompt_kana.mutual_aid_prem ||
1174 to_char(p_prev_jobs(i).mutual_aid_prem) ||
1175 g_prompt_kana.yen || ')';
1176 end if;
1177 --
1178 -- <Income Tax>
1179 --
1180 l_description_kanji := l_description_kanji || ' ' ||
1181 g_prompt_kanji.itax ||
1182 to_char(p_prev_jobs(i).itax) ||
1183 g_prompt_kanji.yen;
1184 l_description_kana := l_description_kana || ' ' ||
1185 g_prompt_kana.itax ||
1186 to_char(p_prev_jobs(i).itax) ||
1187 g_prompt_kana.yen;
1188 --
1189 -- <Salary Payer Address>
1190 --
1191 if p_prev_jobs(i).salary_payer_address_kanji is not null then
1192 l_description_kanji := l_description_kanji || ' ' ||
1193 p_prev_jobs(i).salary_payer_address_kanji;
1194 end if;
1195 if p_prev_jobs(i).salary_payer_address_kana is not null then
1196 l_description_kana := l_description_kana || ' ' ||
1197 p_prev_jobs(i).salary_payer_address_kana;
1198 end if;
1199 --
1200 -- <Salary Payer Name>
1201 --
1202 if p_prev_jobs(i).salary_payer_name_kanji is not null then
1203 l_description_kanji := l_description_kanji || ' ' ||
1204 p_prev_jobs(i).salary_payer_name_kanji;
1205 end if;
1206 if p_prev_jobs(i).salary_payer_name_kana is not null then
1207 l_description_kana := l_description_kana || ' ' ||
1208 p_prev_jobs(i).salary_payer_name_kana;
1209 end if;
1210 --
1211 -- <Termination Date>
1212 -- Now to_char(''Japanese Imperial'') does not work in PL/SQL.
1213 -- Here uses to_char of ORACLE as a workaround.
1214 --
1215 if p_prev_jobs(i).termination_date is not null then
1216 l_description_kanji := l_description_kanji || ' ' ||
1217 hr_jp_standard_pkg.to_jp_char(p_prev_jobs(i).termination_date, g_prompt_kanji.date_format) ||
1218 g_prompt_kanji.terminated;
1219 l_description_kana := l_description_kana || ' ' ||
1220 hr_jp_standard_pkg.to_jp_char(p_prev_jobs(i).termination_date, g_prompt_kana.date_format) ||
1221 g_prompt_kana.terminated;
1222 end if;
1223 --
1224 add_description(
1225 PREV_JOB,
1226 l_description_kanji,
1227 l_description_kana,
1228 p_descriptions);
1229 end loop;
1230 --
1231 hr_utility.set_location(c_proc, 32);
1232 --
1233 -- ************************
1234 -- HOUSING_TAX_REDUCTION
1235 -- ************************
1236 -- Date: 2007/06/07
1237 -- If statement condition changed to print when "Total" housing tax reduction <> "0"
1238 -- even "Net" housing tax reduction = "0".
1239 --
1240 if p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct <> 0
1241 and (p_certificate_info.housing_residence_date is not null or p_certificate_info.unclaimed_housing_tax_reduct <> 0) then
1242 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
1243 l_description_kanji := g_prompt_kanji.housing_tax_reduction || '(';
1244 l_description_kana := g_prompt_kana.housing_tax_reduction || '(';
1245 else
1246 l_description_kanji := g_prompt_kanji.housing_tax_reduction_long || '(';
1247 l_description_kana := g_prompt_kana.housing_tax_reduction_long || '(';
1248 end if;
1249 --
1250 if p_certificate_info.housing_residence_date is not null then
1251 l_description_kanji := l_description_kanji ||
1252 g_prompt_kanji.residence_date ||
1253 hr_jp_standard_pkg.to_jp_char(p_certificate_info.housing_residence_date, g_prompt_kanji.date_format);
1254 l_description_kana := l_description_kana ||
1255 g_prompt_kana.residence_date ||
1256 hr_jp_standard_pkg.to_jp_char(p_certificate_info.housing_residence_date, g_prompt_kana.date_format);
1257 l_separator := ' ';
1258 end if;
1259 --
1260 if p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
1261 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
1262 l_description_kanji := l_description_kanji || l_separator ||
1263 g_prompt_kanji.unclaimed_housing_tax_reduct ||
1264 to_char(p_certificate_info.unclaimed_housing_tax_reduct) ||
1265 g_prompt_kanji.yen;
1266 l_description_kana := l_description_kana || l_separator ||
1267 g_prompt_kana.unclaimed_housing_tax_reduct ||
1268 to_char(p_certificate_info.unclaimed_housing_tax_reduct) ||
1269 g_prompt_kana.yen;
1270 else
1271 l_description_kanji := l_description_kanji || l_separator ||
1272 g_prompt_kanji.total_housing_tax_reduction ||
1273 to_char(p_certificate_info.housing_tax_reduction
1274 + p_certificate_info.unclaimed_housing_tax_reduct) ||
1275 g_prompt_kanji.yen;
1276 l_description_kana := l_description_kana || l_separator ||
1277 g_prompt_kana.total_housing_tax_reduction ||
1278 to_char(p_certificate_info.housing_tax_reduction
1279 + p_certificate_info.unclaimed_housing_tax_reduct) ||
1280 g_prompt_kana.yen;
1281 end if;
1282 end if;
1283 --
1284 l_description_kanji := l_description_kanji || ')';
1285 l_description_kana := l_description_kana || ')';
1286 --
1287 add_description(
1288 HOUSING_TAX_REDUCTION,
1289 l_description_kanji,
1290 l_description_kana,
1291 p_descriptions);
1292 end if;
1293 else
1294 hr_utility.set_location(c_proc, 33);
1295 --
1296 -- ************************
1297 -- DISASTER_TAX_REDUCTION
1298 -- ************************
1299 if p_certificate_info.tax_info.disaster_tax_reduction <> 0 then
1300 l_description_kanji := g_prompt_kanji.disaster_tax_reduction ||
1301 to_char(p_certificate_info.tax_info.disaster_tax_reduction) ||
1302 g_prompt_kanji.yen;
1303 l_description_kana := g_prompt_kana.disaster_tax_reduction ||
1304 to_char(p_certificate_info.tax_info.disaster_tax_reduction) ||
1305 g_prompt_kana.yen;
1306 --
1307 add_description(
1308 DISASTER_TAX_REDUCTION,
1309 l_description_kanji,
1310 l_description_kana,
1311 p_descriptions);
1312 end if;
1313 end if;
1314 --
1315 hr_utility.set_location('Leaving: ' || c_proc, 100);
1316 end get_descriptions;
1317 -- |-------------------------------------------------------------------|
1318 -- |---------------------< get_certificate_info >----------------------|
1319 -- |-------------------------------------------------------------------|
1320 -- Wrapper function for ITT
1321 procedure get_certificate_info(
1322 p_assignment_action_id in number,
1323 p_assignment_id in number,
1324 p_action_sequence in number,
1325 p_effective_date in date,
1326 p_itax_organization_id in number,
1327 p_itax_category in varchar2,
1328 p_itax_yea_category in varchar2,
1329 p_employment_category in varchar2,
1330 p_person_id in number,
1331 p_business_group_id in number,
1332 p_date_earned in date,
1333 p_certificate_info out nocopy t_tax_info,
1334 p_submission_required_flag out nocopy varchar2,
1335 p_withholding_tax_info out nocopy t_tax_info)
1336 is
1337 c_proc constant varchar2(61) := c_package || 'get_certificate_info (ITT)';
1338 begin
1339 hr_utility.set_location('Entering: ' || c_proc, 10);
1340 --
1341 get_basic_certificate_info(
1342 p_assignment_action_id => p_assignment_action_id,
1343 p_assignment_id => p_assignment_id,
1344 p_action_sequence => p_action_sequence,
1345 p_effective_date => p_effective_date,
1346 p_itax_organization_id => p_itax_organization_id,
1347 p_itax_category => p_itax_category,
1348 p_itax_yea_category => p_itax_yea_category,
1349 p_employment_category => p_employment_category,
1350 p_certificate_info => p_certificate_info,
1351 p_submission_required_flag => p_submission_required_flag);
1352 --
1353 hr_utility.set_location(c_proc, 20);
1354 --
1355 if p_itax_yea_category = '0' then
1356 hr_utility.set_location(c_proc, 21);
1357 --
1358 --
1359 -- Derive withholding tax information by current employer.
1360 --
1361 get_withholding_tax_info(
1362 p_assignment_action_id => p_assignment_action_id,
1363 p_assignment_id => p_assignment_id,
1364 p_action_sequence => p_action_sequence,
1365 p_effective_date => p_effective_date,
1366 p_itax_organization_id => p_itax_organization_id,
1367 p_itax_category => p_itax_category,
1368 p_itax_yea_category => p_itax_yea_category,
1369 p_withholding_tax_info => p_withholding_tax_info);
1370 else
1371 hr_utility.set_location(c_proc, 22);
1372 --
1373 --
1374 -- For non-YEAed employees, current employer's payment with same tax category
1375 -- are shown in certificates. So just copy p_certificate_info to p_withholding_tax_info.
1376 --
1377 p_withholding_tax_info := p_certificate_info;
1378 end if;
1379 --
1380 hr_utility.set_location('Leaving: ' || c_proc, 100);
1381 end get_certificate_info;
1382 -- |-------------------------------------------------------------------|
1383 -- |---------------------< get_certificate_info >----------------------|
1384 -- |-------------------------------------------------------------------|
1385 -- Source function for ITW, WTM, Archive
1386 procedure get_basic_certificate_info(
1387 p_assignment_action_id in number,
1388 p_assignment_id in number,
1389 p_action_sequence in number,
1390 p_business_group_id in number,
1391 p_effective_date in date,
1392 p_date_earned in date,
1393 p_itax_organization_id in number,
1394 p_itax_category in varchar2,
1395 p_itax_yea_category in varchar2,
1396 p_dpnt_ref_type in varchar2,
1397 p_dpnt_effective_date in date,
1398 p_person_id in number,
1399 p_sex in varchar2,
1400 p_date_of_birth in date,
1401 p_leaving_reason in varchar2,
1402 p_employment_category in varchar2,
1403 p_certificate_info out nocopy t_certificate_info,
1404 p_submission_required_flag out nocopy varchar2,
1405 p_prev_jobs out nocopy t_prev_jobs)
1406 is
1407 c_proc constant varchar2(61) := c_package || 'get_basic_certificate_info (2)';
1408 --
1409 l_dpnt_rec per_jp_ctr_utility_pkg.t_itax_dpnt_rec;
1410 l_disabled_type varchar2(30);
1411 l_aged_type varchar2(30);
1412 l_widow_type varchar2(30);
1413 l_working_student_type varchar2(30);
1414 l_spouse_type varchar2(30);
1415 l_spouse_disabled_type varchar2(30);
1416 l_num_deps number;
1417 l_num_ageds number;
1418 l_num_aged_parents_lt number;
1419 l_num_specifieds number;
1420 l_num_disableds number;
1421 l_num_svr_disableds number;
1422 l_num_svr_disableds_lt number;
1423 l_dummy varchar2(1);
1424 l_dummy_number number;
1425 --
1426 cursor csr_spouse is
1427 select 'Y'
1428 from per_contact_relationships ctr
1429 where ctr.person_id = p_person_id
1430 and ctr.contact_type = 'S'
1431 and p_effective_date
1432 between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date);
1433 begin
1434 hr_utility.set_location('Entering: ' || c_proc, 10);
1435 --
1436 get_basic_certificate_info(
1437 p_assignment_action_id => p_assignment_action_id,
1438 p_assignment_id => p_assignment_id,
1439 p_action_sequence => p_action_sequence,
1440 p_effective_date => p_effective_date,
1441 p_itax_organization_id => p_itax_organization_id,
1442 p_itax_category => p_itax_category,
1443 p_itax_yea_category => p_itax_yea_category,
1444 p_employment_category => p_employment_category,
1445 p_certificate_info => p_certificate_info.tax_info,
1446 p_submission_required_flag => p_submission_required_flag);
1447 --
1448 hr_utility.set_location(c_proc, 20);
1449 --
1450 -- The following information is specific only for YEAed assignments.
1451 --
1452 if p_itax_yea_category = '0' then
1453 hr_utility.set_location(c_proc, 21);
1454 --
1455 p_certificate_info.net_taxable_income := nvl(pay_jp_balance_pkg.get_result_value_number(g_taxable_income_exempt_elm.element_type_id, g_taxable_income_exempt_elm.net_taxable_income_iv, p_assignment_action_id), 0);
1456 --
1457 p_certificate_info.spouse_sp_exempt := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id, g_ins_prem_exempt_elm.spouse_sp_exempt_iv, p_assignment_action_id), 0);
1458 if p_certificate_info.spouse_sp_exempt <> 0 then
1459 p_certificate_info.spouse_net_taxable_income := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id, g_ins_prem_exempt_elm.spouse_net_taxable_income_iv, p_assignment_action_id), 0);
1460 end if;
1461 --
1462 -- Date: 2007/06/07
1463 -- "Personal Pension Premium" should be printed only when "Personal Pension Premium" <> 0.
1464 --
1465 p_certificate_info.li_prem_exempt := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id, g_ins_prem_exempt_elm.li_prem_exempt_iv, p_assignment_action_id), 0);
1466 if p_certificate_info.li_prem_exempt <> 0 then
1467 l_dummy_number := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id, g_ins_prem_exempt_elm.pp_prem_iv, p_assignment_action_id), 0);
1468 if l_dummy_number <> 0 then
1469 p_certificate_info.pp_prem := l_dummy_number;
1470 end if;
1471 end if;
1472 --
1473 -- Date: 2007/06/07
1474 -- "Long-term Accident Insurance Premium" should be printed only when "Long-term Accident Insurance Premium" <> 0.
1475 --
1476 p_certificate_info.ai_prem_exempt := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id, g_ins_prem_exempt_elm.ai_prem_exempt_iv, p_assignment_action_id), 0);
1477 if p_certificate_info.ai_prem_exempt <> 0 then
1478 l_dummy_number := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id, g_ins_prem_exempt_elm.long_ai_prem_iv, p_assignment_action_id), 0);
1479 if l_dummy_number <> 0 then
1480 p_certificate_info.long_ai_prem := l_dummy_number;
1481 end if;
1482 end if;
1483 --
1484 p_certificate_info.total_income_exempt := pay_jp_balance_pkg.get_balance_value(g_total_income_exempt_asg_run, p_assignment_action_id);
1485 --
1486 -- Date: 2007/06/07
1487 -- There's possibility when "Housing Tax Reduction" > 0, but "Net Housing Tax Reduction" = 0.
1488 -- This occurs when annual tax is zero. In this case, "Housing Tax Reduction Information" should
1489 -- be printed on "Description" field.
1490 --
1491 p_certificate_info.housing_tax_reduction := nvl(pay_jp_balance_pkg.get_result_value_number(g_net_annual_itax_elm.element_type_id, g_net_annual_itax_elm.net_housing_tax_reduction_iv, p_assignment_action_id), 0);
1492 l_dummy_number := nvl(pay_jp_balance_pkg.get_result_value_number(g_net_annual_itax_elm.element_type_id, g_net_annual_itax_elm.housing_tax_reduction_iv, p_assignment_action_id), 0);
1493 if l_dummy_number <> 0 then
1494 p_certificate_info.housing_residence_date := pay_jp_balance_pkg.get_entry_value_date(g_housing_tax_reduction_elm.residence_date_iv, p_assignment_id, p_date_earned);
1495 p_certificate_info.unclaimed_housing_tax_reduct := l_dummy_number - p_certificate_info.housing_tax_reduction;
1496 end if;
1497 --
1498 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
1499 p_certificate_info.fixed_rate_tax_reduction := nvl(pay_jp_balance_pkg.get_result_value_number(g_net_annual_itax_elm.element_type_id, g_net_annual_itax_elm.fixed_rate_tax_reduction_iv, p_assignment_action_id), 0);
1500 else
1501 p_certificate_info.fixed_rate_tax_reduction := 0;
1502 end if;
1503 --
1504 if to_number(to_char(p_effective_date, 'YYYY')) >= 2005 then
1505 p_certificate_info.national_pens_prem := nvl(pay_jp_balance_pkg.get_entry_value_number(g_yea_ins_prem_sp_exm_info_elm.national_pens_prem_iv,p_assignment_id,p_date_earned), 0);
1506 else
1507 p_certificate_info.national_pens_prem := 0;
1508 end if;
1509 --
1510 hr_utility.set_location(c_proc, 22);
1511 --
1512 get_prev_jobs(
1513 p_assignment_id => p_assignment_id,
1514 p_action_sequence => p_action_sequence,
1515 p_business_group_id => p_business_group_id,
1516 p_effective_date => p_effective_date,
1517 p_date_earned => p_date_earned,
1518 p_itax_organization_id => p_itax_organization_id,
1519 p_person_id => p_person_id,
1520 p_prev_jobs => p_prev_jobs);
1521 end if;
1522 --
1523 hr_utility.set_location(c_proc, 30);
1524 --
1525 -- Dependents Information
1526 --
1527 if p_itax_category in ('M_KOU', 'M_OTSU', 'D_KOU', 'D_OTSU') then
1528 if p_itax_yea_category = '0' then
1529 hr_utility.set_location(c_proc, 31);
1530 --
1531 l_disabled_type := nvl(pay_jp_balance_pkg.get_result_value_char(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.disabled_iv, p_assignment_action_id), '0');
1532 if to_number(to_char(p_effective_date,'YYYY')) < 2005 then
1533 l_aged_type := nvl(pay_jp_balance_pkg.get_result_value_char(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.aged_iv, p_assignment_action_id), '0');
1534 end if;
1535 l_widow_type := nvl(pay_jp_balance_pkg.get_result_value_char(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.widow_iv, p_assignment_action_id), '0');
1536 l_working_student_type := nvl(pay_jp_balance_pkg.get_result_value_char(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.working_student_iv, p_assignment_action_id), '0');
1537 --
1538 l_spouse_type := nvl(pay_jp_balance_pkg.get_result_value_char(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.spouse_type_iv, p_assignment_action_id), '0');
1539 l_spouse_disabled_type := nvl(pay_jp_balance_pkg.get_result_value_char(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.spouse_disabled_iv, p_assignment_action_id), '0');
1540 --
1541 l_num_deps := nvl(pay_jp_balance_pkg.get_result_value_number(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.num_deps_iv, p_assignment_action_id), 0);
1542 l_num_ageds := nvl(pay_jp_balance_pkg.get_result_value_number(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.num_ageds_iv, p_assignment_action_id), 0);
1543 l_num_aged_parents_lt := nvl(pay_jp_balance_pkg.get_result_value_number(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.num_aged_parents_lt_iv, p_assignment_action_id), 0);
1544 l_num_specifieds := nvl(pay_jp_balance_pkg.get_result_value_number(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.num_specifieds_iv, p_assignment_action_id), 0);
1545 l_num_disableds := nvl(pay_jp_balance_pkg.get_result_value_number(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.num_disableds_iv, p_assignment_action_id), 0);
1546 l_num_svr_disableds := nvl(pay_jp_balance_pkg.get_result_value_number(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.num_svr_disableds_iv, p_assignment_action_id), 0);
1547 l_num_svr_disableds_lt := nvl(pay_jp_balance_pkg.get_result_value_number(g_dep_exempt_result_elm.element_type_id, g_dep_exempt_result_elm.num_svr_disableds_lt_iv, p_assignment_action_id), 0);
1548 else
1549 hr_utility.set_location(c_proc, 32);
1550 --
1551 l_disabled_type := nvl(pay_jp_balance_pkg.get_entry_value_char(g_dep_exempt_elm.disabled_iv, p_assignment_id, p_date_earned), '0');
1552 if to_number(to_char(p_effective_date,'YYYY')) < 2005 then
1553 l_aged_type := nvl(pay_jp_balance_pkg.get_entry_value_char(g_dep_exempt_elm.aged_iv, p_assignment_id, p_date_earned), '0');
1554 end if;
1555 l_widow_type := nvl(pay_jp_balance_pkg.get_entry_value_char(g_dep_exempt_elm.widow_iv, p_assignment_id, p_date_earned), '0');
1556 l_working_student_type := nvl(pay_jp_balance_pkg.get_entry_value_char(g_dep_exempt_elm.working_student_iv, p_assignment_id, p_date_earned), '0');
1557 --
1558 l_spouse_type := pay_jp_balance_pkg.get_entry_value_char(g_dep_exempt_elm.spouse_type_iv, p_assignment_id, p_date_earned);
1559 l_spouse_disabled_type := pay_jp_balance_pkg.get_entry_value_char(g_dep_exempt_elm.spouse_disabled_iv, p_assignment_id, p_date_earned);
1560 --
1561 l_num_deps := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_deps_iv, p_assignment_id, p_date_earned);
1562 l_num_ageds := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_ageds_iv, p_assignment_id, p_date_earned);
1563 l_num_aged_parents_lt := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_aged_parents_lt_iv, p_assignment_id, p_date_earned);
1564 l_num_specifieds := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_specifieds_iv, p_assignment_id, p_date_earned);
1565 l_num_disableds := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_disableds_iv, p_assignment_id, p_date_earned);
1566 l_num_svr_disableds := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_svr_disableds_iv, p_assignment_id, p_date_earned);
1567 l_num_svr_disableds_lt := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_svr_disableds_lt_iv, p_assignment_id, p_date_earned);
1568 --
1569 -- Derive Dependent Information based on Income Tax Dependent Control Method.
1570 -- This flag is set at "Payroll Developer DF" or "Org Developer DF"(Business Group level).
1571 -- The value set in "Payroll Developer DF" has higher priority than "Org Developer DF".
1572 -- Here does not do the cross validation for each dependent deduction like FastFormula.
1573 -- This dynamic deriving routine is available when YEA is not processed.
1574 --
1575 if p_dpnt_ref_type = 'CEI' then
1576 hr_utility.set_location(c_proc, 33);
1577 --
1578 if l_spouse_type is null
1579 or l_spouse_disabled_type is null
1580 or l_num_deps is null
1581 or l_num_ageds is null
1582 or l_num_aged_parents_lt is null
1583 or l_num_specifieds is null
1584 or l_num_disableds is null
1585 or l_num_svr_disableds is null
1586 or l_num_svr_disableds_lt is null then
1587 per_jp_ctr_utility_pkg.get_itax_dpnt_info(
1588 p_assignment_id => p_assignment_id,
1589 p_itax_type => p_itax_category,
1590 p_effective_date => p_dpnt_effective_date,
1591 p_itax_dpnt_rec => l_dpnt_rec);
1592 --
1593 l_spouse_type := nvl(l_spouse_type, l_dpnt_rec.spouse_type);
1594 l_spouse_disabled_type := nvl(l_spouse_disabled_type, l_dpnt_rec.dpnt_spouse_dsbl_type);
1595 l_num_deps := nvl(l_num_deps, l_dpnt_rec.dpnts);
1596 l_num_ageds := nvl(l_num_ageds, l_dpnt_rec.aged_dpnts);
1597 l_num_aged_parents_lt := nvl(l_num_aged_parents_lt, l_dpnt_rec.aged_dpnt_parents_lt);
1598 l_num_specifieds := nvl(l_num_specifieds, l_dpnt_rec.young_dpnts);
1599 l_num_disableds := nvl(l_num_disableds, l_dpnt_rec.dsbl_dpnts);
1600 l_num_svr_disableds := nvl(l_num_svr_disableds, l_dpnt_rec.svr_dsbl_dpnts);
1601 l_num_svr_disableds_lt := nvl(l_num_svr_disableds_lt, l_dpnt_rec.svr_dsbl_dpnts_lt);
1602 end if;
1603 else
1604 hr_utility.set_location(c_proc, 34);
1605 --
1606 l_spouse_type := nvl(l_spouse_type, '0');
1607 l_spouse_disabled_type := nvl(l_spouse_disabled_type, '0');
1608 l_num_deps := nvl(l_num_deps, 0);
1609 l_num_ageds := nvl(l_num_ageds, 0);
1610 l_num_aged_parents_lt := nvl(l_num_aged_parents_lt, 0);
1611 l_num_specifieds := nvl(l_num_specifieds, 0);
1612 l_num_disableds := nvl(l_num_disableds, 0);
1613 l_num_svr_disableds := nvl(l_num_svr_disableds, 0);
1614 l_num_svr_disableds_lt := nvl(l_num_svr_disableds_lt, 0);
1615 end if;
1616 end if;
1617 --
1618 hr_utility.set_location(c_proc, 34);
1619 --
1620 if p_itax_category in ('M_KOU', 'D_KOU') then
1621 hr_utility.set_location(c_proc, 35);
1622 --
1623 if l_spouse_type = '2' then
1624 p_certificate_info.dep_spouse_exists_kou := 'Y';
1625 elsif l_spouse_type = '3' then
1626 p_certificate_info.dep_spouse_exists_kou := 'Y';
1627 p_certificate_info.aged_spouse_exists := 'Y';
1628 else
1629 p_certificate_info.dep_spouse_not_exist_kou := 'Y';
1630 end if;
1631 --
1632 p_certificate_info.num_specifieds_kou := l_num_specifieds;
1633 p_certificate_info.num_aged_parents_lt := l_num_aged_parents_lt;
1634 p_certificate_info.num_ageds_kou := l_num_aged_parents_lt + l_num_ageds;
1635 p_certificate_info.num_deps_kou := greatest(l_num_deps - (l_num_specifieds + l_num_aged_parents_lt + l_num_ageds), 0);
1636 --
1637 p_certificate_info.num_svr_disableds_lt := l_num_svr_disableds_lt;
1638 p_certificate_info.num_svr_disableds := l_num_svr_disableds;
1639 p_certificate_info.num_disableds := l_num_disableds;
1640 --
1641 if l_spouse_disabled_type = '1' then
1642 p_certificate_info.num_disableds := p_certificate_info.num_disableds + 1;
1643 elsif l_spouse_disabled_type = '2' then
1644 p_certificate_info.num_svr_disableds := p_certificate_info.num_svr_disableds + 1;
1645 elsif l_spouse_disabled_type = '3' then
1646 p_certificate_info.num_svr_disableds_lt := p_certificate_info.num_svr_disableds_lt + 1;
1647 end if;
1648 --
1649 p_certificate_info.num_svr_disableds := p_certificate_info.num_svr_disableds + p_certificate_info.num_svr_disableds_lt;
1650 --
1651 p_certificate_info.num_specifieds_kou := decode_value(p_certificate_info.num_specifieds_kou > 0, p_certificate_info.num_specifieds_kou);
1652 p_certificate_info.num_aged_parents_lt := decode_value(p_certificate_info.num_aged_parents_lt > 0, p_certificate_info.num_aged_parents_lt);
1653 p_certificate_info.num_ageds_kou := decode_value(p_certificate_info.num_ageds_kou > 0, p_certificate_info.num_ageds_kou);
1654 p_certificate_info.num_deps_kou := decode_value(p_certificate_info.num_deps_kou > 0, p_certificate_info.num_deps_kou );
1655 p_certificate_info.num_svr_disableds_lt := decode_value(p_certificate_info.num_svr_disableds_lt > 0, p_certificate_info.num_svr_disableds_lt);
1656 p_certificate_info.num_svr_disableds := decode_value(p_certificate_info.num_svr_disableds > 0, p_certificate_info.num_svr_disableds);
1657 p_certificate_info.num_disableds := decode_value(p_certificate_info.num_disableds > 0, p_certificate_info.num_disableds);
1658 --
1659 if l_disabled_type = '2' then
1660 p_certificate_info.svr_disabled_flag := 'Y';
1661 elsif l_disabled_type = '1' then
1662 p_certificate_info.disabled_flag := 'Y';
1663 end if;
1664 --
1665 if to_number(to_char(p_effective_date,'YYYY')) < 2005 then
1666 if l_aged_type = '1' then
1667 p_certificate_info.aged_flag := 'Y';
1668 end if;
1669 end if;
1670 --
1671 if l_widow_type = '2' then
1672 if p_sex = 'F' then
1673 p_certificate_info.sp_widow_flag := 'Y';
1674 else
1675 p_certificate_info.widower_flag := 'Y';
1676 end if;
1677 elsif l_widow_type = '1' then
1678 if p_sex = 'F' then
1679 p_certificate_info.widow_flag := 'Y';
1680 else
1681 p_certificate_info.widower_flag := 'Y';
1682 end if;
1683 end if;
1684 --
1685 if l_working_student_type = '1' then
1686 p_certificate_info.working_student_flag := 'Y';
1687 end if;
1688 elsif p_itax_category in ('M_OTSU', 'D_OTSU') then
1689 hr_utility.set_location(c_proc, 36);
1690 --
1691 if l_spouse_type = '2' then
1692 p_certificate_info.dep_spouse_exists_otsu := 'Y';
1693 elsif l_spouse_type = '3' then
1694 p_certificate_info.dep_spouse_exists_otsu := 'Y';
1695 p_certificate_info.aged_spouse_exists := 'Y';
1696 else
1697 p_certificate_info.dep_spouse_not_exist_otsu := 'Y';
1698 end if;
1699 --
1700 p_certificate_info.num_specifieds_otsu := l_num_specifieds;
1701 p_certificate_info.num_ageds_otsu := l_num_aged_parents_lt + l_num_ageds;
1702 p_certificate_info.num_deps_otsu := greatest(l_num_deps - (l_num_specifieds + l_num_aged_parents_lt + l_num_ageds), 0);
1703 --
1704 p_certificate_info.num_specifieds_otsu := decode_value(p_certificate_info.num_specifieds_otsu > 0, p_certificate_info.num_specifieds_otsu);
1705 p_certificate_info.num_ageds_otsu := decode_value(p_certificate_info.num_ageds_otsu > 0, p_certificate_info.num_ageds_otsu);
1706 p_certificate_info.num_deps_otsu := decode_value(p_certificate_info.num_deps_otsu > 0, p_certificate_info.num_deps_otsu);
1707 end if;
1708 end if;
1709 --
1710 hr_utility.set_location(c_proc, 40);
1711 --
1712 -- Husband Exists
1713 --
1714 if to_number(to_char(p_effective_date,'YYYY')) < 2005 then
1715 if p_sex = 'F' then
1716 open csr_spouse;
1717 fetch csr_spouse into l_dummy;
1718 if csr_spouse%found then
1719 p_certificate_info.husband_exists := 'Y';
1720 end if;
1721 close csr_spouse;
1722 end if;
1723 end if;
1724 --
1725 -- Minor is free from Local Tax.
1726 -- The age used here needs to be calculated as of Jan 1st in the next calendar year.
1727 -- If an employee is under 20 years old as of Jan 1st, he/she is "Minor".
1728 -- The way how to calculate age in Japanese law is slightly different from normal way,
1729 -- an employee's age is incremented on the previous day of the date of birth.
1730 -- As a result, we need to calculate age as of "Jan 1st + 1" = "Jan 2nd".
1731 -- Note "Aged" is calculated as of "End of Calendar Year" which is different from this case.
1732 --
1733 if floor(months_between(add_months(trunc(p_effective_date, 'YYYY'), 12) + 1, p_date_of_birth) / 12) < 20 then
1734 p_certificate_info.minor_flag := 'Y';
1735 end if;
1736 --
1737 -- Otsu
1738 --
1739 if p_itax_category in ('M_OTSU', 'D_OTSU') then
1740 p_certificate_info.otsu_flag := 'Y';
1741 end if;
1742 --
1743 -- Deceased Termination
1744 --
1745 if p_leaving_reason like 'D%' then
1746 p_certificate_info.deceased_termination_flag := 'Y';
1747 end if;
1748 --
1749 -- Disastered
1750 --
1751 if p_certificate_info.tax_info.disaster_tax_reduction <> 0 then
1752 p_certificate_info.disastered_flag := 'Y';
1753 end if;
1754 --
1755 -- Foreigner
1756 --
1757 if pay_jp_balance_pkg.get_entry_value_char(g_itax_info_elm.foreigner_flag_iv, p_assignment_id, p_date_earned) = 'Y' then
1758 p_certificate_info.foreigner_flag := 'Y';
1759 end if;
1760 --
1761 hr_utility.set_location('Leaving: ' || c_proc, 100);
1762 end get_basic_certificate_info;
1763 -- |-------------------------------------------------------------------|
1764 -- |---------------------< get_certificate_info >----------------------|
1765 -- |-------------------------------------------------------------------|
1766 -- Wrapper function for WTM
1767 procedure get_certificate_info(
1768 p_assignment_action_id in number,
1769 p_assignment_id in number,
1770 p_action_sequence in number,
1771 p_business_group_id in number,
1772 p_effective_date in date,
1773 p_date_earned in date,
1774 p_itax_organization_id in number,
1775 p_itax_category in varchar2,
1776 p_itax_yea_category in varchar2,
1777 p_dpnt_ref_type in varchar2,
1778 p_dpnt_effective_date in date,
1779 p_person_id in number,
1780 p_sex in varchar2,
1781 p_date_of_birth in date,
1782 p_leaving_reason in varchar2,
1783 p_last_name_kanji in varchar2,
1784 p_last_name_kana in varchar2,
1785 p_employment_category in varchar2,
1786 p_magnetic_media_flag in varchar2 default 'N',
1787 p_certificate_info out nocopy t_certificate_info,
1788 p_submission_required_flag out nocopy varchar2,
1789 p_prev_jobs out nocopy t_prev_jobs)
1790 is
1791 c_proc constant varchar2(61) := c_package || 'get_certificate_info (WTM)';
1792 l_itw_override_flag varchar2(1);
1793 l_itw_description varchar2(300);
1794 l_wtm_override_flag varchar2(1);
1795 l_wtm_description varchar2(300);
1796 l_override_flag varchar2(1);
1797 l_description varchar2(300);
1798 l_report_type varchar2(3);
1799 l_dpnts t_dpnts;
1800 l_descriptions t_descriptions;
1801 --
1802 procedure concat_description(
1803 p_description in varchar2,
1804 p_concat_description in out nocopy varchar2,
1805 p_separator in varchar2)
1806 is
1807 begin
1808 if p_description is not null then
1809 if p_concat_description is not null then
1810 p_concat_description := p_concat_description || p_separator || p_description;
1811 else
1812 p_concat_description := p_description;
1813 end if;
1814 end if;
1815 end concat_description;
1816 begin
1817 hr_utility.set_location('Entering: ' || c_proc, 10);
1818 --
1819 get_basic_certificate_info(
1820 p_assignment_action_id => p_assignment_action_id,
1821 p_assignment_id => p_assignment_id,
1822 p_action_sequence => p_action_sequence,
1823 p_business_group_id => p_business_group_id,
1824 p_effective_date => p_effective_date,
1825 p_date_earned => p_date_earned,
1826 p_itax_organization_id => p_itax_organization_id,
1827 p_itax_category => p_itax_category,
1828 p_itax_yea_category => p_itax_yea_category,
1829 p_dpnt_ref_type => p_dpnt_ref_type,
1830 p_dpnt_effective_date => p_dpnt_effective_date,
1831 p_person_id => p_person_id,
1832 p_sex => p_sex,
1833 p_date_of_birth => p_date_of_birth,
1834 p_leaving_reason => p_leaving_reason,
1835 p_employment_category => p_employment_category,
1836 p_certificate_info => p_certificate_info,
1837 p_submission_required_flag => p_submission_required_flag,
1838 p_prev_jobs => p_prev_jobs);
1839 --
1840 hr_utility.set_location(c_proc, 20);
1841 --
1842 -- Get Description information
1843 -- When description on element entry is marked as "Override",
1844 -- subsequent description will be replaced by this.
1845 --
1846 get_ee_description(
1847 p_assignment_id => p_assignment_id,
1848 p_business_group_id => p_business_group_id,
1849 p_effective_date => p_date_earned,
1850 p_itw_override_flag => l_itw_override_flag,
1851 p_itw_description => l_itw_description,
1852 p_wtm_override_flag => l_wtm_override_flag,
1853 p_wtm_description => l_wtm_description);
1854 --
1855 hr_utility.set_location(c_proc, 30);
1856 --
1857 if p_magnetic_media_flag = 'Y' then
1858 hr_utility.set_location(c_proc, 31);
1859 --
1860 l_override_flag := l_wtm_override_flag;
1861 l_description := l_wtm_description;
1862 l_report_type := WTM;
1863 else
1864 hr_utility.set_location(c_proc, 32);
1865 --
1866 l_override_flag := l_itw_override_flag;
1867 l_description := l_itw_description;
1868 l_report_type := ITW;
1869 end if;
1870 --
1871 hr_utility.set_location(c_proc, 40);
1872 --
1873 if l_override_flag = 'Y' then
1874 hr_utility.set_location(c_proc, 41);
1875 --
1876 p_certificate_info.description_kanji := l_description;
1877 p_certificate_info.description_kana := hr_jp_standard_pkg.to_hankaku(l_description);
1878 else
1879 hr_utility.set_location(c_proc, 42);
1880 --
1881 get_dpnts(
1882 p_dpnt_ref_type => p_dpnt_ref_type,
1883 p_assignment_id => p_assignment_id,
1884 p_itax_category => p_itax_category,
1885 p_dpnt_effective_date => p_dpnt_effective_date,
1886 p_person_id => p_person_id,
1887 p_effective_date => p_effective_date,
1888 p_sex => p_sex,
1889 p_dpnts => l_dpnts);
1890 --
1891 hr_utility.set_location(c_proc, 43);
1892 --
1893 get_descriptions(
1894 p_assignment_id => p_assignment_id,
1895 p_person_id => p_person_id,
1896 p_effective_date => p_effective_date,
1897 p_itax_yea_category => p_itax_yea_category,
1898 p_certificate_info => p_certificate_info,
1899 p_last_name_kanji => p_last_name_kanji,
1900 p_last_name_kana => p_last_name_kana,
1901 p_dpnts => l_dpnts,
1902 p_prev_jobs => p_prev_jobs,
1903 p_report_type => l_report_type,
1904 p_descriptions => l_descriptions);
1905 --
1906 hr_utility.set_location(c_proc, 44);
1907 --
1908 -- Construct Description field.
1909 --
1910 for i in 1..l_descriptions.count loop
1911 hr_utility.set_location(c_proc, 45);
1912 --
1913 if p_magnetic_media_flag = 'Y' then
1914 concat_description(l_descriptions(i).description_kanji, p_certificate_info.description_kanji, ',');
1915 concat_description(l_descriptions(i).description_kana, p_certificate_info.description_kana, ',');
1916 else
1917 if l_descriptions(i).description_type in (FIXED_RATE_TAX_REDUCTION, NP_PREM) then
1918 concat_description(l_descriptions(i).description_kanji, p_certificate_info.desc_line1_kanji, ' ');
1919 concat_description(l_descriptions(i).description_kana, p_certificate_info.desc_line1_kana, ' ');
1920 else
1921 concat_description(l_descriptions(i).description_kanji, p_certificate_info.description_kanji, ',');
1922 concat_description(l_descriptions(i).description_kana, p_certificate_info.description_kana, ',');
1923 end if;
1924 end if;
1925 end loop;
1926 --
1927 concat_description(l_description, p_certificate_info.description_kanji, ',');
1928 concat_description(hr_jp_standard_pkg.to_hankaku(l_description), p_certificate_info.description_kana, ',');
1929 end if;
1930 --
1931 hr_utility.set_location('Leaving: ' || c_proc, 100);
1932 end get_certificate_info;
1933 --
1934 procedure get_certificate_info(
1935 p_assignment_action_id in number,
1936 p_assignment_id in number,
1937 p_action_sequence in number,
1938 p_business_group_id in number,
1939 p_effective_date in date,
1940 p_date_earned in date,
1941 p_itax_organization_id in number,
1942 p_itax_category in varchar2,
1943 p_itax_yea_category in varchar2,
1944 p_dpnt_ref_type in varchar2,
1945 p_dpnt_effective_date in date,
1946 p_person_id in number,
1947 p_sex in varchar2,
1948 p_date_of_birth in date,
1949 p_leaving_reason in varchar2,
1950 p_last_name_kanji in varchar2,
1951 p_last_name_kana in varchar2,
1952 p_employment_category in varchar2,
1953 p_magnetic_media_flag in varchar2 default 'N',
1954 p_certificate_info out nocopy t_certificate_info,
1955 p_submission_required_flag out nocopy varchar2,
1956 p_prev_job_info out nocopy t_prev_job_info)
1957 is
1958 l_prev_jobs pay_jp_wic_pkg.t_prev_jobs;
1959 begin
1960 get_certificate_info(
1961 p_assignment_action_id => p_assignment_action_id,
1962 p_assignment_id => p_assignment_id,
1963 p_action_sequence => p_action_sequence,
1964 p_business_group_id => p_business_group_id,
1965 p_effective_date => p_effective_date,
1966 p_date_earned => p_date_earned,
1967 p_itax_organization_id => p_itax_organization_id,
1968 p_itax_category => p_itax_category,
1969 p_itax_yea_category => p_itax_yea_category,
1970 p_dpnt_ref_type => p_dpnt_ref_type,
1971 p_dpnt_effective_date => p_dpnt_effective_date,
1972 p_person_id => p_person_id,
1973 p_sex => p_sex,
1974 p_date_of_birth => p_date_of_birth,
1975 p_leaving_reason => p_leaving_reason,
1976 p_last_name_kanji => p_last_name_kanji,
1977 p_last_name_kana => p_last_name_kana,
1978 p_employment_category => p_employment_category,
1979 p_magnetic_media_flag => p_magnetic_media_flag,
1980 p_certificate_info => p_certificate_info,
1981 p_submission_required_flag => p_submission_required_flag,
1982 p_prev_jobs => l_prev_jobs);
1983 --
1984 p_prev_job_info := convert_prev_jobs(l_prev_jobs);
1985 end get_certificate_info;
1986 -- |-------------------------------------------------------------------|
1987 -- |---------------------< get_certificate_info >----------------------|
1988 -- |-------------------------------------------------------------------|
1989 -- Wrapper function for ITW
1990 --
1991 procedure get_certificate_info(
1992 p_assignment_action_id in number,
1993 p_assignment_id in number,
1994 p_action_sequence in number,
1995 p_business_group_id in number,
1996 p_effective_date in date,
1997 p_date_earned in date,
1998 p_itax_organization_id in number,
1999 p_itax_category in varchar2,
2000 p_itax_yea_category in varchar2,
2001 p_dpnt_ref_type in varchar2,
2002 p_dpnt_effective_date in date,
2003 p_person_id in number,
2004 p_sex in varchar2,
2005 p_date_of_birth in date,
2006 p_leaving_reason in varchar2,
2007 p_last_name_kanji in varchar2,
2008 p_last_name_kana in varchar2,
2009 p_employment_category in varchar2,
2010 p_magnetic_media_flag in varchar2 default 'N',
2011 p_certificate_info out nocopy t_certificate_info,
2012 p_submission_required_flag out nocopy varchar2,
2013 p_prev_jobs out nocopy t_prev_jobs,
2014 p_withholding_tax_info out nocopy t_tax_info)
2015 is
2016 c_proc constant varchar2(61) := c_package || 'get_certificate_info (ITW)';
2017 l_itw_override_flag varchar2(1);
2018 l_itw_description varchar2(300);
2019 l_wtm_override_flag varchar2(1);
2020 l_wtm_description varchar2(300);
2021 l_dpnts t_dpnts;
2022 l_descriptions t_descriptions;
2023 begin
2024 hr_utility.set_location('Entering: ' || c_proc, 10);
2025 --
2026 get_certificate_info(
2027 p_assignment_action_id => p_assignment_action_id,
2028 p_assignment_id => p_assignment_id,
2029 p_action_sequence => p_action_sequence,
2030 p_business_group_id => p_business_group_id,
2031 p_effective_date => p_effective_date,
2032 p_date_earned => p_date_earned,
2033 p_itax_organization_id => p_itax_organization_id,
2034 p_itax_category => p_itax_category,
2035 p_itax_yea_category => p_itax_yea_category,
2036 p_dpnt_ref_type => p_dpnt_ref_type,
2037 p_dpnt_effective_date => p_dpnt_effective_date,
2038 p_person_id => p_person_id,
2039 p_sex => p_sex,
2040 p_date_of_birth => p_date_of_birth,
2041 p_leaving_reason => p_leaving_reason,
2042 p_last_name_kanji => p_last_name_kanji,
2043 p_last_name_kana => p_last_name_kana,
2044 p_employment_category => p_employment_category,
2045 p_magnetic_media_flag => p_magnetic_media_flag,
2046 p_certificate_info => p_certificate_info,
2047 p_submission_required_flag => p_submission_required_flag,
2048 p_prev_jobs => p_prev_jobs);
2049 --
2050 hr_utility.set_location(c_proc, 20);
2051 --
2052 if p_itax_yea_category = '0' then
2053 hr_utility.set_location(c_proc, 21);
2054 --
2055 -- Derive withholding tax information by current employer.
2056 --
2057 get_withholding_tax_info(
2058 p_assignment_action_id => p_assignment_action_id,
2059 p_assignment_id => p_assignment_id,
2060 p_action_sequence => p_action_sequence,
2061 p_effective_date => p_effective_date,
2062 p_itax_organization_id => p_itax_organization_id,
2063 p_itax_category => p_itax_category,
2064 p_itax_yea_category => p_itax_yea_category,
2065 p_withholding_tax_info => p_withholding_tax_info);
2066 else
2067 hr_utility.set_location(c_proc, 22);
2068 --
2069 -- For non-YEAed employees, current employer's payment with same tax category
2070 -- are shown in certificates. So just copy p_certificate_info to p_withholding_tax_info.
2071 --
2072 p_withholding_tax_info := p_certificate_info.tax_info;
2073 end if;
2074 --
2075 hr_utility.set_location('Leaving: ' || c_proc, 100);
2076 end get_certificate_info;
2077 -- |-------------------------------------------------------------------|
2078 -- |---------------------< get_certificate_info >----------------------|
2079 -- |-------------------------------------------------------------------|
2080 -- Wrapper function for Archive
2081 procedure get_certificate_info(
2082 p_assignment_action_id in number,
2083 p_assignment_id in number,
2084 p_action_sequence in number,
2085 p_business_group_id in number,
2086 p_effective_date in date,
2087 p_date_earned in date,
2088 p_itax_organization_id in number,
2089 p_itax_category in varchar2,
2090 p_itax_yea_category in varchar2,
2091 p_dpnt_ref_type in varchar2,
2092 p_dpnt_effective_date in date,
2093 p_person_id in number,
2094 p_sex in varchar2,
2095 p_date_of_birth in date,
2096 p_leaving_reason in varchar2,
2097 p_last_name_kanji in varchar2,
2098 p_last_name_kana in varchar2,
2099 p_employment_category in varchar2,
2100 p_certificate_info out nocopy t_certificate_info,
2101 p_submission_required_flag out nocopy varchar2,
2102 p_prev_job_info out nocopy t_prev_job_info,
2103 p_withholding_tax_info out nocopy t_tax_info,
2104 p_itw_description out nocopy varchar2,
2105 p_itw_descriptions out nocopy t_descriptions,
2106 p_wtm_description out nocopy varchar2,
2107 p_wtm_descriptions out nocopy t_descriptions)
2108 is
2109 c_proc constant varchar2(61) := c_package || 'get_certificate_info (ARC)';
2110 l_dpnts t_dpnts;
2111 l_prev_jobs t_prev_jobs;
2112 l_itw_override_flag varchar2(1);
2113 l_wtm_override_flag varchar2(1);
2114 begin
2115 hr_utility.set_location('Entering: ' || c_proc, 10);
2116 --
2117 get_basic_certificate_info(
2118 p_assignment_action_id => p_assignment_action_id,
2119 p_assignment_id => p_assignment_id,
2120 p_action_sequence => p_action_sequence,
2121 p_business_group_id => p_business_group_id,
2122 p_effective_date => p_effective_date,
2123 p_date_earned => p_date_earned,
2124 p_itax_organization_id => p_itax_organization_id,
2125 p_itax_category => p_itax_category,
2126 p_itax_yea_category => p_itax_yea_category,
2127 p_dpnt_ref_type => p_dpnt_ref_type,
2128 p_dpnt_effective_date => p_dpnt_effective_date,
2129 p_person_id => p_person_id,
2130 p_sex => p_sex,
2131 p_date_of_birth => p_date_of_birth,
2132 p_leaving_reason => p_leaving_reason,
2133 p_employment_category => p_employment_category,
2134 p_certificate_info => p_certificate_info,
2135 p_submission_required_flag => p_submission_required_flag,
2136 p_prev_jobs => l_prev_jobs);
2137 --
2138 hr_utility.set_location(c_proc, 20);
2139 --
2140 p_prev_job_info := convert_prev_jobs(l_prev_jobs);
2141 --
2142 hr_utility.set_location(c_proc, 30);
2143 --
2144 if p_itax_yea_category = '0' then
2145 --
2146 -- Derive withholding tax information by current employer.
2147 --
2148 get_withholding_tax_info(
2149 p_assignment_action_id => p_assignment_action_id,
2150 p_assignment_id => p_assignment_id,
2151 p_action_sequence => p_action_sequence,
2152 p_effective_date => p_effective_date,
2153 p_itax_organization_id => p_itax_organization_id,
2154 p_itax_category => p_itax_category,
2155 p_itax_yea_category => p_itax_yea_category,
2156 p_withholding_tax_info => p_withholding_tax_info);
2157 else
2158 --
2159 -- For non-YEAed employees, current employer's payment with same tax category
2160 -- are shown in certificates. So just copy p_certificate_info to p_withholding_tax_info.
2161 --
2162 p_withholding_tax_info := p_certificate_info.tax_info;
2163 end if;
2164 --
2165 hr_utility.set_location(c_proc, 40);
2166 --
2167 -- Get Description information
2168 -- When description on element entry is marked as "Override",
2169 -- subsequent description will be replaced by this.
2170 --
2171 get_ee_description(
2172 p_assignment_id => p_assignment_id,
2173 p_business_group_id => p_business_group_id,
2174 p_effective_date => p_date_earned,
2175 p_itw_override_flag => l_itw_override_flag,
2176 p_itw_description => p_itw_description,
2177 p_wtm_override_flag => l_wtm_override_flag,
2178 p_wtm_description => p_wtm_description);
2179 --
2180 hr_utility.set_location(c_proc, 50);
2181 --
2182 if l_itw_override_flag = 'N' or l_wtm_override_flag = 'N' then
2183 hr_utility.set_location(c_proc, 51);
2184 --
2185 get_dpnts(
2186 p_dpnt_ref_type => p_dpnt_ref_type,
2187 p_assignment_id => p_assignment_id,
2188 p_itax_category => p_itax_category,
2189 p_dpnt_effective_date => p_dpnt_effective_date,
2190 p_person_id => p_person_id,
2191 p_effective_date => p_effective_date,
2192 p_sex => p_sex,
2193 p_dpnts => l_dpnts);
2194 --
2195 hr_utility.set_location(c_proc, 52);
2196 --
2197 if l_itw_override_flag = 'N' then
2198 hr_utility.set_location(c_proc, 53);
2199 --
2200 get_descriptions(
2201 p_assignment_id => p_assignment_id,
2202 p_person_id => p_person_id,
2203 p_effective_date => p_effective_date,
2204 p_itax_yea_category => p_itax_yea_category,
2205 p_certificate_info => p_certificate_info,
2206 p_last_name_kanji => p_last_name_kanji,
2207 p_last_name_kana => p_last_name_kana,
2208 p_dpnts => l_dpnts,
2209 p_prev_jobs => l_prev_jobs,
2210 p_report_type => ITW,
2211 p_descriptions => p_itw_descriptions);
2212 end if;
2213 --
2214 if l_wtm_override_flag = 'N' then
2215 hr_utility.set_location(c_proc, 54);
2216 --
2217 get_descriptions(
2218 p_assignment_id => p_assignment_id,
2219 p_person_id => p_person_id,
2220 p_effective_date => p_effective_date,
2221 p_itax_yea_category => p_itax_yea_category,
2222 p_certificate_info => p_certificate_info,
2223 p_last_name_kanji => p_last_name_kanji,
2224 p_last_name_kana => p_last_name_kana,
2225 p_dpnts => l_dpnts,
2226 p_prev_jobs => l_prev_jobs,
2227 p_report_type => WTM,
2228 p_descriptions => p_wtm_descriptions);
2229 end if;
2230 --
2231 hr_utility.set_location(c_proc, 55);
2232 end if;
2233 --
2234 hr_utility.set_location('Leaving: ' || c_proc, 100);
2235 end get_certificate_info;
2236 --
2237 -- |-------------------------------------------------------------------|
2238 -- |---------------------< set_valid_term_taxable_amt >----------------|
2239 -- |-------------------------------------------------------------------|
2240 procedure set_valid_term_taxable_amt(
2241 p_valid_term_taxable_amt in number)
2242 is
2243 begin
2244 --
2245 pay_jp_wic_pkg.g_valid_term_taxable_amt := p_valid_term_taxable_amt;
2246 --
2247 end set_valid_term_taxable_amt;
2248 --
2249 -- |-------------------------------------------------------------------|
2250 -- |----------------------< spr_term_valid >---------------------------|
2251 -- |-------------------------------------------------------------------|
2252 -- Use in Summary Payment Report and ITW with Term Validation
2253 function spr_term_valid(
2254 p_assignment_action_id in number,
2255 p_assignment_id in number,
2256 p_action_sequence in number,
2257 p_effective_date in date,
2258 p_itax_organization_id in number,
2259 p_itax_category in varchar2,
2260 p_itax_yea_category in varchar2,
2261 p_employment_category in varchar2,
2262 p_termination_date in date,
2263 p_certificate_info in t_certificate_info default null)
2264 return number
2265 is
2266 --
2267 l_result number := 0; /* 0 (false) or 1 (true) */
2268 --
2269 l_certificate_info t_certificate_info;
2270 l_submission_required_flag varchar2(1);
2271 --
2272 begin
2273 --
2274 if p_termination_date is not null then
2275 --
2276 if pay_jp_wic_pkg.g_valid_term_taxable_amt is null then
2277 hr_utility.set_message(800,'HR_7914_CHECK_FMT_NUMBER');
2278 hr_utility.set_message_token('ARG_NAME','pay_jp_wic_pkg.g_valid_term_taxable_amt');
2279 hr_utility.set_message_token('ARG_VALUE','pay_jp_wic_pkg.g_valid_term_taxable_amt');
2280 hr_utility.raise_error;
2281 end if;
2282 --
2283 if p_certificate_info.tax_info.taxable_income is null then
2284 --
2285 get_basic_certificate_info(
2286 p_assignment_action_id => p_assignment_action_id,
2287 p_assignment_id => p_assignment_id,
2288 p_action_sequence => p_action_sequence,
2289 p_effective_date => p_effective_date,
2290 p_itax_organization_id => p_itax_organization_id,
2291 p_itax_category => p_itax_category,
2292 p_itax_yea_category => p_itax_yea_category,
2293 p_employment_category => p_employment_category,
2294 p_certificate_info => l_certificate_info.tax_info,
2295 p_submission_required_flag => l_submission_required_flag);
2296 --
2297 else
2298 --
2299 l_certificate_info := p_certificate_info;
2300 --
2301 end if;
2302 --
2303 if l_certificate_info.tax_info.taxable_income <= pay_jp_wic_pkg.g_valid_term_taxable_amt then
2304 --
2305 l_result := 1;
2306 --
2307 end if;
2308 --
2309 end if;
2310 --
2311 return l_result;
2312 --
2313 end spr_term_valid;
2314 --
2315 -- |-------------------------------------------------------------------|
2316 -- |---------------------< get_certificate_info >----------------------|
2317 -- |-------------------------------------------------------------------|
2318 -- Wrapper for ITW with Term Validation
2319 procedure get_certificate_info(
2320 p_assignment_action_id in number,
2321 p_assignment_id in number,
2322 p_action_sequence in number,
2323 p_business_group_id in number,
2324 p_effective_date in date,
2325 p_date_earned in date,
2326 p_itax_organization_id in number,
2327 p_itax_category in varchar2,
2328 p_itax_yea_category in varchar2,
2329 p_dpnt_ref_type in varchar2,
2330 p_dpnt_effective_date in date,
2331 p_person_id in number,
2332 p_sex in varchar2,
2333 p_date_of_birth in date,
2334 p_leaving_reason in varchar2,
2335 p_last_name_kanji in varchar2,
2336 p_last_name_kana in varchar2,
2337 p_employment_category in varchar2,
2338 p_magnetic_media_flag in varchar2 default 'N',
2339 p_termination_date in date,
2340 p_certificate_info out nocopy t_certificate_info,
2341 p_submission_required_flag out nocopy varchar2,
2342 p_prev_jobs out nocopy t_prev_jobs,
2343 p_withholding_tax_info out nocopy t_tax_info,
2344 p_spr_term_valid out nocopy number)
2345 is
2346 begin
2347 --
2348 get_certificate_info(
2349 p_assignment_action_id => p_assignment_action_id,
2350 p_assignment_id => p_assignment_id,
2351 p_action_sequence => p_action_sequence,
2352 p_business_group_id => p_business_group_id,
2353 p_effective_date => p_effective_date,
2354 p_date_earned => p_date_earned,
2355 p_itax_organization_id => p_itax_organization_id,
2356 p_itax_category => p_itax_category,
2357 p_itax_yea_category => p_itax_yea_category,
2358 p_dpnt_ref_type => p_dpnt_ref_type,
2359 p_dpnt_effective_date => p_dpnt_effective_date,
2360 p_person_id => p_person_id,
2361 p_sex => p_sex,
2362 p_date_of_birth => p_date_of_birth,
2363 p_leaving_reason => p_leaving_reason,
2364 p_last_name_kanji => p_last_name_kanji,
2365 p_last_name_kana => p_last_name_kana,
2366 p_employment_category => p_employment_category,
2367 p_magnetic_media_flag => p_magnetic_media_flag,
2368 p_certificate_info => p_certificate_info,
2369 p_submission_required_flag => p_submission_required_flag,
2370 p_prev_jobs => p_prev_jobs,
2371 p_withholding_tax_info => p_withholding_tax_info);
2372 --
2373 p_spr_term_valid := spr_term_valid(
2374 p_assignment_action_id => p_assignment_action_id,
2375 p_assignment_id => p_assignment_id,
2376 p_action_sequence => p_action_sequence,
2377 p_effective_date => p_effective_date,
2378 p_itax_organization_id => p_itax_organization_id,
2379 p_itax_category => p_itax_category,
2380 p_itax_yea_category => p_itax_yea_category,
2381 p_employment_category => p_employment_category,
2382 p_termination_date => p_termination_date,
2383 p_certificate_info => p_certificate_info);
2384 --
2385 end get_certificate_info;
2386 --
2387 -- |-------------------------------------------------------------------|
2388 -- |---------------------< get_certificate_info >----------------------|
2389 -- |-------------------------------------------------------------------|
2390 -- For debugging purpose. NEVER USE THIS in your product code.
2391 procedure get_certificate_info(
2392 p_assignment_action_id in number,
2393 p_magnetic_media_flag in varchar2 default 'N',
2394 p_certificate_info out nocopy t_certificate_info,
2395 p_submission_required_flag out nocopy varchar2,
2396 p_prev_jobs out nocopy t_prev_jobs,
2397 p_withholding_tax_info out nocopy t_tax_info)
2398 is
2399 cursor csr_assact is
2400 select wic.assignment_id,
2401 wic.action_sequence,
2402 wic.business_group_id,
2403 wic.effective_date,
2404 wic.date_earned,
2405 wic.itax_organization_id,
2406 wic.itax_category,
2407 wic.itax_yea_category,
2408 nvl(nvl(pay.prl_information1, hoi.org_information2), 'CTR_EE') DPNT_REF_TYPE,
2409 nvl(fnd_date.canonical_to_date(pay_core_utils.get_parameter('ITAX_DPNT_EFFECTIVE_DATE', wic.legislative_parameters)),
2410 wic.effective_date) DPNT_EFFECTIVE_DATE,
2411 per.person_id,
2412 per.sex,
2413 per.date_of_birth,
2414 wic.leaving_reason,
2415 per.per_information18 LAST_NAME_KANJI,
2416 per.last_name LAST_NAME_KANA,
2417 wic.employment_category
2418 from per_all_people_f per,
2419 hr_organization_information hoi,
2420 pay_all_payrolls_f pay,
2421 /* Use V2 instead of V for debugging. */
2422 pay_jp_wic_assacts_v2 wic
2423 where wic.assignment_action_id = p_assignment_action_id
2424 and pay.payroll_id = wic.payroll_id
2425 and wic.date_earned
2426 between pay.effective_start_date and pay.effective_end_date
2427 and hoi.organization_id(+) = wic.business_group_id
2428 and hoi.org_information_context(+) = 'JP_BUSINESS_GROUP_INFO'
2429 and per.person_id = wic.person_id
2430 and wic.effective_date
2431 between per.effective_start_date and per.effective_end_date;
2432 begin
2433 for l_rec in csr_assact loop
2434 get_certificate_info(
2435 p_assignment_action_id => p_assignment_action_id,
2436 p_assignment_id => l_rec.assignment_id,
2437 p_action_sequence => l_rec.action_sequence,
2438 p_business_group_id => l_rec.business_group_id,
2439 p_effective_date => l_rec.effective_date,
2440 p_date_earned => l_rec.date_earned,
2441 p_itax_organization_id => l_rec.itax_organization_id,
2442 p_itax_category => l_rec.itax_category,
2443 p_itax_yea_category => l_rec.itax_yea_category,
2444 p_dpnt_ref_type => l_rec.dpnt_ref_type,
2445 p_dpnt_effective_date => l_rec.dpnt_effective_date,
2446 p_person_id => l_rec.person_id,
2447 p_sex => l_rec.sex,
2448 p_date_of_birth => l_rec.date_of_birth,
2449 p_leaving_reason => l_rec.leaving_reason,
2450 p_last_name_kanji => l_rec.last_name_kanji,
2451 p_last_name_kana => l_rec.last_name_kana,
2452 p_employment_category => l_rec.employment_category,
2453 p_magnetic_media_flag => p_magnetic_media_flag,
2454 p_certificate_info => p_certificate_info,
2455 p_submission_required_flag => p_submission_required_flag,
2456 p_prev_jobs => p_prev_jobs,
2457 p_withholding_tax_info => p_withholding_tax_info);
2458 end loop;
2459 end get_certificate_info;
2460 -- |-------------------------------------------------------------------|
2461 -- |----------------------< ass_set_validation >-----------------------|
2462 -- |-------------------------------------------------------------------|
2463 FUNCTION ass_set_validation(
2464 p_assignment_set_id in NUMBER,
2465 p_assignment_id in NUMBER,
2466 p_effective_date in DATE) RETURN NUMBER
2467 IS
2468 BEGIN
2469 --
2470 -- Bug.4863756
2471 -- Now this is just wrapper of hr_jp_ast_utility_pkg.assignment_set_validate.
2472 --
2473 if hr_jp_ast_utility_pkg.assignment_set_validate(
2474 p_assignment_set_id,
2475 p_assignment_id,
2476 p_effective_date,
2477 'N') = 'Y' then
2478 return 1;
2479 else
2480 return 0;
2481 end if;
2482 END ass_set_validation;
2483 --
2484 -- Package Initialization
2485 --
2486 begin
2487 g_prompt_kanji.yen := fnd_message.get_string('PAY', 'PAY_JP_JBA_MONEY_SUFFIX');
2488 g_prompt_kanji.fixed_rate_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_YEA_FIXED_RATE');
2489 g_prompt_kanji.national_pens_prem := fnd_message.get_string('PAY', 'PAY_JP_NATIONAL_PENSION');
2490 g_prompt_kanji.prev_job := fnd_message.get_string('PAY', 'PAY_JP_PREVIOUS_EMPLOYMENT');
2491 g_prompt_kanji.taxable_income := fnd_message.get_string('PAY', 'PAY_JP_SALARY');
2492 g_prompt_kanji.si_prem := fnd_message.get_string('PAY', 'PAY_JP_TRANS_SI');
2493 g_prompt_kanji.mutual_aid_prem := fnd_message.get_string('PAY', 'PAY_JP_WITHIN');
2494 g_prompt_kanji.itax := fnd_message.get_string('PAY', 'PAY_JP_TAX');
2495 g_prompt_kanji.terminated := fnd_message.get_string('PAY', 'PAY_JP_TERM');
2496 g_prompt_kanji.housing_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_HOUSING_DEDUCTION');
2497 g_prompt_kanji.residence_date := fnd_message.get_string('PAY', 'PAY_JP_RES_START_DATE');
2498 g_prompt_kanji.unclaimed_housing_tax_reduct := fnd_message.get_string('PAY', 'PAY_JP_DEDUCTIONS_NOT_TAKEN');
2499 -- 2007/06/07
2500 g_prompt_kanji.housing_tax_reduction_long := fnd_message.get_string('PAY', 'PAY_JP_HOUSING_DEDUCTION_LONG');
2501 g_prompt_kanji.total_housing_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_AVAILABLE_AMOUNT');
2502 --
2503 g_prompt_kanji.disaster_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_GRACE_TAX_AMOUNT');
2504 g_prompt_kanji.husband := fnd_message.get_string('PAY', 'PAY_JP_HUSBAND');
2505 g_prompt_kanji.wife := fnd_message.get_string('PAY', 'PAY_JP_WIFE');
2506 g_prompt_kanji.date_format := 'FMYY"' || fnd_message.get_string('PER', 'HR_JP_YY') ||
2507 '"MM"' || fnd_message.get_string('PER', 'HR_JP_MM') ||
2508 '"DD"' || fnd_message.get_string('PER', 'HR_JP_DD') || '"';
2509 g_prompt_kanji.other := fnd_message.get_string('PAY', 'PAY_JP_TRANS_OTHER');
2510 g_prompt_kanji.count := fnd_message.get_string('PAY', 'PAY_JP_TRANS_COUNT');
2511 --
2512 g_prompt_kana.yen := fnd_message.get_string('PAY', 'PAY_JP_JBA_MONEY_SUFFIX_KANA');
2513 g_prompt_kana.fixed_rate_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_YEA_FIXED_RATE_KANA');
2514 g_prompt_kana.national_pens_prem := fnd_message.get_string('PAY', 'PAY_JP_NATIONAL_PENSION_KANA');
2515 g_prompt_kana.prev_job := fnd_message.get_string('PAY', 'PAY_JP_PREV_EMPLOYMENT_KANA');
2516 g_prompt_kana.taxable_income := fnd_message.get_string('PAY', 'PAY_JP_TRANS_SALARY_KANA');
2517 g_prompt_kana.si_prem := fnd_message.get_string('PAY', 'PAY_JP_TRANS_SI_KANA');
2518 g_prompt_kana.mutual_aid_prem := fnd_message.get_string('PAY', 'PAY_JP_WITHIN_KANA');
2519 g_prompt_kana.itax := fnd_message.get_string('PAY', 'PAY_JP_TAX_KANA');
2520 g_prompt_kana.terminated := fnd_message.get_string('PAY', 'PAY_JP_TRANS_TERM_KANA');
2521 g_prompt_kana.housing_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_HOUSING_DEDUCTION_KANA');
2522 g_prompt_kana.residence_date := fnd_message.get_string('PAY', 'PAY_JP_RES_START_DATE_KANA');
2523 g_prompt_kana.unclaimed_housing_tax_reduct := fnd_message.get_string('PAY', 'PAY_JP_DCT_NOT_TAKEN_KANA');
2524 -- 2007/06/07
2525 g_prompt_kana.housing_tax_reduction_long := fnd_message.get_string('PAY', 'PAY_JP_HOUSING_DCT_LONG_KANA');
2526 g_prompt_kana.total_housing_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_AVAILABLE_AMOUNT_KANA');
2527 --
2528 g_prompt_kana.disaster_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_GRACE_TAX_AMOUNT_KANA');
2529 g_prompt_kana.husband := fnd_message.get_string('PAY', 'PAY_JP_HUSBAND_KANA');
2530 g_prompt_kana.wife := fnd_message.get_string('PAY', 'PAY_JP_WIFE_KANA');
2531 g_prompt_kana.date_format := 'FMYY"' || fnd_message.get_string('PAY', 'PAY_JP_TRANS_YY_KANA') ||
2532 '"MM"' || fnd_message.get_string('PAY', 'PAY_JP_TRANS_MM_KANA') ||
2533 '"DD"' || fnd_message.get_string('PAY', 'PAY_JP_TRANS_DD_KANA') || '"';
2534 g_prompt_kana.other := fnd_message.get_string('PAY', 'PAY_JP_TRANS_OTHER_KANA');
2535 g_prompt_kana.count := fnd_message.get_string('PAY', 'PAY_JP_TRANS_COUNT_KANA');
2536 --
2537 g_taxable_income_exempt_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_AMT_AFTER_EMP_INCOME_DCT_RSLT', null, 'JP');
2538 g_taxable_income_exempt_elm.net_taxable_income_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'Pay Value');
2539 g_taxable_income_exempt_elm.taxable_income_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'PAY_AMT');
2540 g_taxable_income_exempt_elm.prev_taxable_income_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'PREV_EMP_INCOME');
2541 g_taxable_income_exempt_elm.prev_si_prem_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'PREV_EMP_SI_PREM');
2542 -- g_taxable_income_exempt_elm.prev_itax_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'PREV_EMP_ITX');
2543 g_taxable_income_exempt_elm.taxable_income_adj_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'ADJ_EMP_INCOME');
2544 g_taxable_income_exempt_elm.si_prem_adj_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'ADJ_SI_PREM');
2545 g_taxable_income_exempt_elm.mutual_aid_prem_adj_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'ADJ_SMALL_COMPANY_MUTUAL_AID_PREM');
2546 g_taxable_income_exempt_elm.itax_adj_iv := hr_jp_id_pkg.input_value_id(g_taxable_income_exempt_elm.element_type_id, 'ADJ_ITX');
2547 --
2548 g_net_annual_itax_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_NET_ANNUAL_TAX', null, 'JP');
2549 g_net_annual_itax_elm.net_annual_itax_iv := hr_jp_id_pkg.input_value_id(g_net_annual_itax_elm.element_type_id, 'Pay Value');
2550 g_net_annual_itax_elm.housing_tax_reduction_iv := hr_jp_id_pkg.input_value_id(g_net_annual_itax_elm.element_type_id, 'HOUSING_LOAN_TAX_CREDIT');
2551 g_net_annual_itax_elm.net_housing_tax_reduction_iv := hr_jp_id_pkg.input_value_id(g_net_annual_itax_elm.element_type_id, 'ACTUAL_HOUSING_LOAN_TAX_CREDIT');
2552 g_net_annual_itax_elm.fixed_rate_tax_reduction_iv := hr_jp_id_pkg.input_value_id(g_net_annual_itax_elm.element_type_id, 'YEA_PROPORTIONAL_DCT');
2553 --
2554 g_total_income_exempt_asg_run := hr_jp_id_pkg.defined_balance_id('B_YEA_INCOME_EXM',
2555 '_ASG_RUN', null, 'JP');
2556 g_itax_adjustment_asg_ytd := hr_jp_id_pkg.defined_balance_id('B_YEA_TAX_PAY',
2557 '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
2558 -- g_itax_asg_ytd := hr_jp_id_pkg.defined_balance_id('B_YEA_WITHHOLD_ITX',
2559 -- '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
2560 --
2561 g_ins_prem_exempt_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_RSLT', null, 'JP');
2562 g_ins_prem_exempt_elm.si_prem_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'SI_PREM_DCT');
2563 g_ins_prem_exempt_elm.mutual_aid_prem_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'SMALL_COMPANY_MUTUAL_AID_PREM');
2564 g_ins_prem_exempt_elm.li_prem_exempt_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'LIFE_INS_PREM_EXM');
2565 g_ins_prem_exempt_elm.pp_prem_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'INDIVIDUAL_PENSION_PREM');
2566 g_ins_prem_exempt_elm.ai_prem_exempt_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'NONLIFE_INS_PREM_EXM');
2567 g_ins_prem_exempt_elm.long_ai_prem_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'LONG_TERM_NONLIFE_INS_PREM');
2568 g_ins_prem_exempt_elm.spouse_sp_exempt_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'SPOUSE_SP_EXM');
2569 g_ins_prem_exempt_elm.spouse_net_taxable_income_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'SPOUSE_INCOME');
2570 --
2571 g_dep_exempt_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_PROC', null, 'JP');
2572 g_dep_exempt_elm.disabled_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'DISABLE_TYPE');
2573 g_dep_exempt_elm.aged_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'ELDER_TYPE');
2574 g_dep_exempt_elm.widow_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'WIDOW_TYPE');
2575 g_dep_exempt_elm.working_student_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'WORKING_STUDENT_TYPE');
2576 g_dep_exempt_elm.spouse_type_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'SPOUSE_TYPE');
2577 g_dep_exempt_elm.spouse_disabled_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'SPOUSE_DISABLE_TYPE');
2578 g_dep_exempt_elm.num_deps_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_DEP');
2579 g_dep_exempt_elm.num_ageds_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_ELDER_DEP');
2580 g_dep_exempt_elm.num_aged_parents_lt_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_ELDER_PARENT_LT');
2581 g_dep_exempt_elm.num_specifieds_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_SPECIFIC_DEP');
2582 g_dep_exempt_elm.num_disableds_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_GEN_DISABLED');
2583 g_dep_exempt_elm.num_svr_disableds_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_SEV_DISABLED');
2584 g_dep_exempt_elm.num_svr_disableds_lt_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_SEV_DISABLED_LT');
2585 --
2586 g_dep_exempt_result_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_TYPE_RSLT', null, 'JP');
2587 g_dep_exempt_result_elm.disabled_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'DISABLE_TYPE');
2588 g_dep_exempt_result_elm.aged_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'ELDER_TYPE');
2589 g_dep_exempt_result_elm.widow_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'WIDOW_TYPE');
2590 g_dep_exempt_result_elm.working_student_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'WORKING_STUDENT_TYPE');
2591 g_dep_exempt_result_elm.spouse_type_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'SPOUSE_TYPE');
2592 g_dep_exempt_result_elm.spouse_disabled_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'SPOUSE_DISABLE_TYPE');
2593 g_dep_exempt_result_elm.num_deps_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'NUM_OF_DEP');
2594 g_dep_exempt_result_elm.num_ageds_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'NUM_OF_ELDER_DEP');
2595 g_dep_exempt_result_elm.num_aged_parents_lt_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'NUM_OF_ELDER_PARENT_LT');
2596 g_dep_exempt_result_elm.num_specifieds_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'NUM_OF_SPECIFIC_DEP');
2597 g_dep_exempt_result_elm.num_disableds_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'NUM_OF_GEN_DISABLED');
2598 g_dep_exempt_result_elm.num_svr_disableds_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'NUM_OF_SEV_DISABLED');
2599 g_dep_exempt_result_elm.num_svr_disableds_lt_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'NUM_OF_SEV_DISABLED_LT');
2600 --
2601 /*
2602 g_description_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', null, 'JP');
2603 g_description_elm.override_flag_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'OVERRIDE_FLAG');
2604 g_description_elm.description_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD');
2605 g_description_elm.mag_override_flag_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'OVERRIDE_FOR_FILE_FLAG');
2606 g_description_elm.mag_description_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD_FOR_FILE');
2607 --Added for ITAX REPORT Enhancement
2608 g_description_elm.description_2_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD2');
2609 g_description_elm.description_3_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD3');
2610 g_description_elm.description_4_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD4');
2611 g_description_elm.description_5_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD5');
2612 g_description_elm.mag_description_2_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD2_FOR_FILE');
2613 g_description_elm.mag_description_3_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD3_FOR_FILE');
2614 g_description_elm.mag_description_4_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD4_FOR_FILE');
2615 g_description_elm.mag_description_5_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD5_FOR_FILE');
2616 */
2617 g_desc_element_type_id := hr_jp_id_pkg.element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', null, 'JP');
2618 --
2619 g_itax_info_elm.element_type_id := hr_jp_id_pkg.element_type_id('COM_ITX_INFO', null, 'JP');
2620 g_itax_info_elm.foreigner_flag_iv := hr_jp_id_pkg.input_value_id(g_itax_info_elm.element_type_id, 'FOREIGNER_FLAG');
2621 --
2622 g_prev_job_elm := hr_jp_id_pkg.element_type_id('YEA_PREV_EMP_INFO', null, 'JP');
2623 --
2624 g_housing_tax_reduction_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_HOUSING_LOAN_TAX_CREDIT', null, 'JP');
2625 g_housing_tax_reduction_elm.residence_date_iv := hr_jp_id_pkg.input_value_id(g_housing_tax_reduction_elm.element_type_id, 'RES_START_DATE');
2626 --
2627 g_yea_ins_prem_sp_exm_info_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_INFO',null,'JP');
2628 g_yea_ins_prem_sp_exm_info_elm.national_pens_prem_iv := hr_jp_id_pkg.input_value_id(g_yea_ins_prem_sp_exm_info_elm.element_type_id,'NATIONAL_PENSION_PREM');
2629 --
2630 -- hr_utility.trace_on('F', 'TTAGAWA');
2631 end pay_jp_wic_pkg;