[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_WIC_PKG
Source
1 package body pay_jp_wic_pkg as
2 /* $Header: pyjpwic.pkb 120.30.12020000.3 2012/10/19 09:09:31 mdubasi ship $ */
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 HOUSING_LOAN_INFO constant varchar2(30) := 'HOUSING_LOAN_INFO';
13 DISASTER_TAX_REDUCTION constant varchar2(30) := 'DISASTER_TAX_REDUCTION';
14 ITW constant varchar2(30) := 'ITW';
15 WTM constant varchar2(30) := 'WTM';
16 --
17 NP_PREM_CONC constant varchar2(30) := 'NP_PREM_CONC';
18 --
19 c_date_fmt_itw varchar2(30) := 'EYY.MM.DD';
20 c_space_separator varchar2(1) := ' ';
21 c_comma_separator varchar2(1) := ',';
22 c_bracket_left varchar2(1) := '(';
23 c_bracket_right varchar2(1) := ')';
24 --
25 -- Global Variables
26 --
27 type t_prompt is record(
28 yen varchar2(80),
29 fixed_rate_tax_reduction varchar2(80),
30 national_pens_prem varchar2(80),
31 prev_job varchar2(80),
32 taxable_income varchar2(80),
33 si_prem varchar2(80),
34 mutual_aid_prem varchar2(80),
35 itax varchar2(80),
36 terminated varchar2(80),
37 housing_tax_reduction varchar2(80),
38 residence_date varchar2(80),
39 unclaimed_housing_tax_reduct varchar2(80),
40 -- 2007/06/07
41 housing_tax_reduction_long varchar2(80),
42 total_housing_tax_reduction varchar2(80),
43 --
44 disaster_tax_reduction varchar2(80),
45 husband varchar2(80),
46 wife varchar2(80),
47 date_format varchar2(80),
48 other varchar2(80),
49 count varchar2(80),
50 --
51 housing_loan_itw varchar2(200),
52 housing_res_date_itw varchar2(200),
53 housing_loan_balance_itw varchar2(200),
54 housing_loan_type_wtm varchar2(200),
55 housing_count_wtm varchar2(200),
56 housing_res_date_wtm varchar2(200),
57 housing_loan_balance_wtm varchar2(200),
58 np_prem_itw varchar2(200),
59 housing_loan_type_gen_itw varchar2(200),
60 housing_loan_type_ext_itw varchar2(200),
61 housing_loan_type_etq_itw varchar2(200),
62 --
63 dpnt_junior varchar2(80)
64 --
65 );
66 g_prompt_kanji t_prompt;
67 g_prompt_kana t_prompt;
68 --
69 type t_taxable_income_exempt_elm is record(
70 element_type_id number,
71 net_taxable_income_iv number,
72 taxable_income_iv number,
73 prev_taxable_income_iv number,
74 prev_si_prem_iv number,
75 -- prev_itax_iv number,
76 taxable_income_adj_iv number,
77 si_prem_adj_iv number,
78 mutual_aid_prem_adj_iv number,
79 itax_adj_iv number);
80 g_taxable_income_exempt_elm t_taxable_income_exempt_elm;
81 --
82 type t_net_annual_itax_elm is record(
83 element_type_id number,
84 net_annual_itax_iv number,
85 housing_tax_reduction_iv number,
86 net_housing_tax_reduction_iv number,
87 fixed_rate_tax_reduction_iv number);
88 g_net_annual_itax_elm t_net_annual_itax_elm;
89 --
90 g_total_income_exempt_asg_run number;
91 g_itax_adjustment_asg_ytd number;
92 --g_itax_asg_ytd number;
93 --
94 type t_ins_prem_exempt_elm is record(
95 element_type_id number,
96 si_prem_iv number,
97 mutual_aid_prem_iv number,
98 li_prem_exempt_iv number,
99 pp_prem_iv number,
100 ai_prem_exempt_iv number,
101 long_ai_prem_iv number,
102 spouse_sp_exempt_iv number,
103 spouse_net_taxable_income_iv number,
104 old_life_ins_prem_iv number
105 );
106 g_ins_prem_exempt_elm t_ins_prem_exempt_elm;
107 --
108 --2012 Withholding Changes
109 type t_ins_prem_exempt_elm_new is record(
110 element_type_id number,
111 new_life_ins_prem_iv number,
112 care_med_ins_prem_iv number,
113 new_pen_ins_prem_iv number
114 );
115 g_ins_prem_exempt_elm_new t_ins_prem_exempt_elm_new;
116
117 --
118 type t_dep_exempt_elm is record(
119 element_type_id number,
120 disabled_iv number,
121 aged_iv number,
122 widow_iv number,
123 working_student_iv number,
124 spouse_type_iv number,
125 spouse_disabled_iv number,
126 num_deps_iv number,
127 num_ageds_iv number,
128 num_aged_parents_lt_iv number,
129 num_specifieds_iv number,
130 num_junior_deps_iv number,
131 num_disableds_iv number,
132 num_svr_disableds_iv number,
133 num_svr_disableds_lt_iv number);
134 g_dep_exempt_elm t_dep_exempt_elm;
135 g_dep_exempt_result_elm t_dep_exempt_elm;
136 --
137 /*
138 type t_description_elm is record(
139 element_type_id number,
140 override_flag_iv number,
141 description_iv number,
142 mag_override_flag_iv number,
143 mag_description_iv number,
144 description_2_iv number,
145 description_3_iv number,
146 description_4_iv number,
147 description_5_iv number,
148 mag_description_2_iv number,
149 mag_description_3_iv number,
150 mag_description_4_iv number,
151 mag_description_5_iv number);
152 g_description_elm t_description_elm;
153 */
154 g_desc_element_type_id number;
155 --
156 type t_housing_loan_info_elm is record(
157 element_type_id number,
158 res_date_iv number,
159 loan_type_iv number,
160 loan_balance_iv number);
161 g_housing_loan_info_elm t_housing_loan_info_elm;
162 --
163 type t_itax_info_elm is record(
164 element_type_id number,
165 foreigner_flag_iv number);
166 g_itax_info_elm t_itax_info_elm;
167 --
168 g_prev_job_elm number;
169 --
170 type t_housing_tax_reduction_elm is record(
171 element_type_id number,
172 residence_date_iv number);
173 g_housing_tax_reduction_elm t_housing_tax_reduction_elm;
174 --
175 type t_yea_ins_prem_sp_exm_info_elm is record(
176 element_type_id number,
177 national_pens_prem_iv number);
178 g_yea_ins_prem_sp_exm_info_elm t_yea_ins_prem_sp_exm_info_elm;
179 --
180 -- |-------------------------------------------------------------------|
181 -- |--------------------------< to_jp_char >---------------------------|
182 -- |-------------------------------------------------------------------|
183 function to_jp_char(
184 p_date in date,
185 p_date_format in varchar2) return varchar2
186 is
187 l_dummy varchar2(255);
188 begin
189 --
190 -- PL/SQL "to_char" has bug which does not work with "NLS" parameters.
191 -- We here use ORACLE "to_char" as a workaround.
192 --
193 if p_date is not null then
194 select to_char(p_date, p_date_format, 'NLS_CALENDAR=''Japanese Imperial''')
195 into l_dummy
196 from dual;
197 end if;
198 --
199 return l_dummy;
200 end to_jp_char;
201 -- |-------------------------------------------------------------------|
202 -- |-------------------------< decode_value >--------------------------|
203 -- |-------------------------------------------------------------------|
204 function decode_value(
205 p_condition in boolean,
206 p_value1 in number,
207 p_value2 in number default null) return number
208 is
209 begin
210 if p_condition then
211 return p_value1;
212 else
213 return p_value2;
214 end if;
215 end decode_value;
216 -- |-------------------------------------------------------------------|
217 -- |-------------------< get_withholding_tax_info >--------------------|
218 -- |-------------------------------------------------------------------|
219 -- This procedure grosses up from table PAY_JP_PRE_TAX used for
220 -- 1) derive current employer's payment for YEAed assignments
221 -- 2) derive non-YEAed amount including previous SWOTs' amount
222 procedure get_withholding_tax_info(
223 p_assignment_action_id in number,
224 p_assignment_id in number,
225 p_action_sequence in number,
226 p_effective_date in date,
227 p_itax_organization_id in number,
228 p_itax_category in varchar2,
229 p_itax_yea_category in varchar2,
230 p_withholding_tax_info out nocopy t_tax_info)
231 is
232 c_proc constant varchar2(61) := c_package || 'get_withholding_tax_info';
233 begin
234 hr_utility.set_location('Entering: ' || c_proc, 10);
235 --
236 if p_itax_yea_category = '0' then
237 hr_utility.set_location(c_proc, 11);
238 --
239 -- Grossup "Taxable Earnings", "Income Tax" etc.
240 -- with the same "SWOT".
241 --
242 select nvl(sum(taxable_amt), 0),
243 nvl(sum(mutual_aid), 0),
244 nvl(sum(si_prem), 0),
245 nvl(sum(itax), 0),
246 nvl(sum(itax_adjustment), 0),
247 nvl(sum(disaster_tax_reduction), 0)
248 into p_withholding_tax_info.taxable_income,
249 p_withholding_tax_info.mutual_aid_prem,
250 p_withholding_tax_info.si_prem,
251 p_withholding_tax_info.itax,
252 p_withholding_tax_info.itax_adjustment,
253 p_withholding_tax_info.disaster_tax_reduction
254 from pay_jp_wic_assacts_v2
255 where assignment_id = p_assignment_id
256 and action_sequence <= p_action_sequence
257 and effective_date >= trunc(p_effective_date, 'YYYY')
258 and itax_organization_id = p_itax_organization_id;
259 else
260 hr_utility.set_location(c_proc, 12);
261 --
262 -- Grossup "Taxable Earnings", "Income Tax" etc.
263 -- with the same "Tax Category" and "SWOT".
264 --
265 select nvl(sum(taxable_amt), 0),
266 nvl(sum(mutual_aid), 0),
267 nvl(sum(si_prem), 0),
268 nvl(sum(itax), 0),
269 nvl(sum(itax_adjustment), 0),
270 nvl(sum(disaster_tax_reduction), 0)
271 into p_withholding_tax_info.taxable_income,
272 p_withholding_tax_info.mutual_aid_prem,
273 p_withholding_tax_info.si_prem,
274 p_withholding_tax_info.itax,
275 p_withholding_tax_info.itax_adjustment,
276 p_withholding_tax_info.disaster_tax_reduction
277 from pay_jp_wic_assacts_v2
278 where assignment_id = p_assignment_id
279 and action_sequence <= p_action_sequence
280 and effective_date >= trunc(p_effective_date, 'YYYY')
281 and (
282 (p_itax_category in ('M_KOU', 'D_KOU') and itax_category in ('M_KOU', 'D_KOU'))
283 or (p_itax_category in ('M_OTSU', 'D_OTSU') and itax_category in ('M_OTSU', 'D_OTSU'))
284 or (p_itax_category = 'D_HEI' and itax_category = 'D_HEI')
285 )
286 and itax_organization_id = p_itax_organization_id;
287 end if;
288 --
289 hr_utility.set_location(c_proc, 20);
290 --
291 -- When current assignment_action_id is "YEA",
292 -- add the adjustment amount processed in YEA process.
293 -- Note if future assacts exist after YEA, it is not regarded as YEA,
294 -- it is just normal payment. In this case, no adjustment amount in YEA
295 -- is included to the total amount(Skip when null or '-1').
296 --
297 if p_itax_yea_category <> '-1' then
298 hr_utility.set_location(c_proc, 21);
299 --
300 p_withholding_tax_info.taxable_income := p_withholding_tax_info.taxable_income
301 + 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);
302 p_withholding_tax_info.si_prem := p_withholding_tax_info.si_prem
303 + 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);
304 p_withholding_tax_info.mutual_aid_prem := p_withholding_tax_info.mutual_aid_prem
305 + 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);
306 p_withholding_tax_info.itax := p_withholding_tax_info.itax
307 + 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);
308 end if;
309 --
310 p_withholding_tax_info.mutual_aid_prem := decode_value(p_withholding_tax_info.mutual_aid_prem <> 0, p_withholding_tax_info.mutual_aid_prem);
311 p_withholding_tax_info.disaster_tax_reduction := decode_value(p_withholding_tax_info.disaster_tax_reduction <> 0, p_withholding_tax_info.disaster_tax_reduction);
312 p_withholding_tax_info.withholding_itax := p_withholding_tax_info.itax + p_withholding_tax_info.itax_adjustment;
313 --
314 hr_utility.set_location('Leaving: ' || c_proc, 100);
315 end get_withholding_tax_info;
316 -- |-------------------------------------------------------------------|
317 -- |-------------------------< get_prev_jobs >-------------------------|
318 -- |-------------------------------------------------------------------|
319 -- Private Procedure
320 procedure get_prev_jobs(
321 p_assignment_id in number,
322 p_action_sequence in number,
323 p_business_group_id in number,
324 p_effective_date in date,
325 p_date_earned in date,
326 p_itax_organization_id in number,
327 p_person_id in number,
328 p_prev_jobs out nocopy t_prev_jobs)
329 is
330 c_proc constant varchar2(61) := c_package || 'get_prev_jobs';
331 l_withholding_tax_info t_tax_info;
332 l_prev_job_info t_prev_job_info;
333 l_found boolean;
334 l_upgrade_status varchar2(1);
335 --
336 cursor csr_swot is
337 select wic.assignment_action_id,
338 wic.assignment_id,
339 wic.action_sequence,
340 wic.effective_date,
341 wic.itax_organization_id,
342 wic.itax_category,
343 wic.itax_yea_category,
344 hoi.org_information1 SALARY_PAYER_NAME_KANJI,
345 hoi.org_information2 SALARY_PAYER_NAME_KANA,
346 hoi.org_information6 || hoi.org_information7 || hoi.org_information8 SALARY_PAYER_ADDRESS_KANJI,
347 hoi.org_information9 || hoi.org_information10 || hoi.org_information11 SALARY_PAYER_ADDRESS_KANA
348 from hr_organization_information hoi,
349 pay_jp_wic_assacts_v wic
350 where wic.assignment_id = p_assignment_id
351 and wic.action_sequence < p_action_sequence
352 and wic.effective_date >= trunc(p_effective_date, 'YYYY')
353 and wic.itax_organization_id <> p_itax_organization_id
354 -- The following condition removed because dimension (in YEA) can not handle this case.
355 -- and wic.itax_category in ('M_KOU', 'D_KOU', 'D_HEI')
356 and hoi.organization_id(+) = wic.itax_organization_id
357 and hoi.org_information_context(+) = 'JP_TAX_SWOT_INFO'
358 order by wic.action_sequence desc;
359 --
360 cursor csr_job_pem is
361 -- Added nvl(*, 0) except for mutual_aid_prem
362 select nvl(fnd_number.canonical_to_number(pem_information3), 0) taxable_income,
363 nvl(fnd_number.canonical_to_number(pem_information4), 0) si_prem,
364 fnd_number.canonical_to_number(pem_information5) mutual_aid_prem,
365 nvl(fnd_number.canonical_to_number(pem_information6), 0) itax,
366 end_date termination_date,
367 decode(employer_country, null, 'N', 'JP', 'N', 'Y') foreign_address_flag,
368 employer_name salary_payer_name_kanji,
369 employer_address salary_payer_address_kanji,
370 pem_information1 salary_payer_name_kana,
371 pem_information2 salary_payer_address_kana
372 from per_previous_employers
373 where person_id = p_person_id
374 --
375 -- Bug.4159708. Added new segment "Tax Year"(PEM_INFORMATION7)
376 --
377 and pem_information_category = 'JP'
378 and p_effective_date >= nvl(end_date, p_effective_date)
379 and (
380 (
381 pem_information7 is not null
382 and fnd_number.canonical_to_number(pem_information7) = to_number(to_char(p_effective_date, 'YYYY'))
383 )
384 or ( pem_information7 is null
385 and end_date >= trunc(p_effective_date, 'YYYY')
386 )
387 )
388 order by end_date desc;
389 --
390 cursor csr_job_pee is
391 -- Removed nvl(*, 0) from mutual_aid_prem
392 select nvl(sum(decode(piv.display_sequence, 1, to_number(peev.screen_entry_value))), 0) taxable_income,
393 nvl(sum(decode(piv.display_sequence, 2, to_number(peev.screen_entry_value))), 0) si_prem,
394 sum(decode(piv.display_sequence, 3, to_number(peev.screen_entry_value))) mutual_aid_prem,
395 nvl(sum(decode(piv.display_sequence, 4, to_number(peev.screen_entry_value))), 0) itax,
396 min(decode(piv.display_sequence, 5, fnd_date.canonical_to_date(peev.screen_entry_value))) termination_date,
397 nvl(min(decode(piv.display_sequence, 6, peev.screen_entry_value)), 'N') foreign_address_flag,
398 min(decode(piv.display_sequence, 7, peev.screen_entry_value)) salary_payer_address_kana,
399 min(decode(piv.display_sequence, 8, peev.screen_entry_value)) salary_payer_address_kanji,
400 min(decode(piv.display_sequence, 9, peev.screen_entry_value)) salary_payer_name_kana,
401 min(decode(piv.display_sequence, 10, peev.screen_entry_value)) salary_payer_name_kanji
402 from pay_input_values_f piv,
403 pay_element_entry_values_f peev,
404 pay_element_entries_f pee,
405 pay_element_links_f pel
406 where pel.element_type_id = g_prev_job_elm
407 and pel.business_group_id + 0 = p_business_group_id
408 and p_date_earned
409 between pel.effective_start_date and pel.effective_end_date
410 and pee.assignment_id = p_assignment_id
411 and pee.element_link_id = pel.element_link_id
412 and p_date_earned
413 between pee.effective_start_date and pee.effective_end_date
414 and peev.element_entry_id = pee.element_entry_id
415 and peev.effective_start_date = pee.effective_start_date
416 and peev.effective_end_date = pee.effective_end_date
417 and piv.input_value_id = peev.input_value_id
418 and p_date_earned
419 between piv.effective_start_date and piv.effective_end_date
420 group by pee.element_entry_id
421 order by termination_date desc;
422 begin
423 hr_utility.set_location('Entering: ' || c_proc, 10);
424 --
425 for l_rec in csr_swot loop
426 --
427 -- If the same itax_organization_id exist, existing information is replaced
428 -- by subsequent information, and the total amount is grossed up.
429 -- No need to call get_certificate_info because itax_yea_category of previous
430 -- employers' is "null" or "-1".
431 --
432 get_withholding_tax_info(
433 p_assignment_action_id => l_rec.assignment_action_id,
434 p_assignment_id => l_rec.assignment_id,
435 p_action_sequence => l_rec.action_sequence,
436 p_effective_date => l_rec.effective_date,
437 p_itax_organization_id => l_rec.itax_organization_id,
438 p_itax_category => l_rec.itax_category,
439 p_itax_yea_category => l_rec.itax_yea_category,
440 p_withholding_tax_info => l_withholding_tax_info);
441 --
442 l_found := false;
443 for i in 1..p_prev_jobs.count loop
444 l_prev_job_info := p_prev_jobs(i);
445 --
446 if l_prev_job_info.itax_organization_id = l_rec.itax_organization_id then
447 l_prev_job_info.taxable_income := l_prev_job_info.taxable_income + l_withholding_tax_info.taxable_income;
448 -- mutual_aid_prem can be <null>, so remember to use nvl before adding.
449 if l_withholding_tax_info.mutual_aid_prem <> 0 then
450 l_prev_job_info.mutual_aid_prem := nvl(l_prev_job_info.mutual_aid_prem, 0) + l_withholding_tax_info.mutual_aid_prem;
451 end if;
452 l_prev_job_info.si_prem := l_prev_job_info.si_prem + l_withholding_tax_info.si_prem;
453 l_prev_job_info.itax := l_prev_job_info.itax + l_withholding_tax_info.withholding_itax;
454 --
455 p_prev_jobs(i) := l_prev_job_info;
456 l_found := true;
457 exit;
458 end if;
459 end loop;
460 --
461 if not l_found then
462 l_prev_job_info := null;
463 --
464 l_prev_job_info.itax_organization_id := l_rec.itax_organization_id;
465 l_prev_job_info.taxable_income := l_withholding_tax_info.taxable_income;
466 l_prev_job_info.mutual_aid_prem := l_withholding_tax_info.mutual_aid_prem;
467 l_prev_job_info.mutual_aid_prem := decode_value(l_prev_job_info.mutual_aid_prem <> 0, l_prev_job_info.mutual_aid_prem);
468 l_prev_job_info.si_prem := l_withholding_tax_info.si_prem;
469 l_prev_job_info.itax := l_withholding_tax_info.withholding_itax;
470 l_prev_job_info.foreign_address_flag := 'N';
471 l_prev_job_info.salary_payer_address_kanji := l_rec.salary_payer_address_kanji;
472 l_prev_job_info.salary_payer_address_kana := l_rec.salary_payer_address_kana;
473 l_prev_job_info.salary_payer_name_kanji := l_rec.salary_payer_name_kanji;
474 l_prev_job_info.salary_payer_name_kana := l_rec.salary_payer_name_kana;
475 --
476 -- Bug.5116358
477 -- Changed not to set termination_date for previous SWOTs
478 -- in the same company.
479 --
480 -- l_prev_job_info.termination_date := l_rec.effective_date;
481 --
482 p_prev_jobs(p_prev_jobs.count + 1) := l_prev_job_info;
483 end if;
484 end loop;
485 --
486 hr_utility.set_location(c_proc, 20);
487 --
488 pay_core_utils.get_upgrade_status(
489 P_BUS_GRP_ID => p_business_group_id,
490 P_SHORT_NAME => 'JP_PREV_JOB',
491 P_STATUS => l_upgrade_status,
492 P_RAISE_ERROR => true);
493 --
494 if l_upgrade_status = 'Y' then
495 hr_utility.set_location(c_proc, 21);
496 --
497 for l_rec in csr_job_pem loop
498 l_prev_job_info := null;
499 --
500 -- itax_organization_id is null in element entry case.
501 --
502 l_prev_job_info.taxable_income := l_rec.taxable_income;
503 l_prev_job_info.mutual_aid_prem := l_rec.mutual_aid_prem;
504 l_prev_job_info.si_prem := l_rec.si_prem;
505 l_prev_job_info.itax := l_rec.itax;
506 l_prev_job_info.foreign_address_flag := l_rec.foreign_address_flag;
507 l_prev_job_info.salary_payer_address_kanji := l_rec.salary_payer_address_kanji;
508 l_prev_job_info.salary_payer_address_kana := l_rec.salary_payer_address_kana;
509 l_prev_job_info.salary_payer_name_kanji := l_rec.salary_payer_name_kanji;
510 l_prev_job_info.salary_payer_name_kana := l_rec.salary_payer_name_kana;
511 l_prev_job_info.termination_date := l_rec.termination_date;
512 --
513 p_prev_jobs(p_prev_jobs.count + 1) := l_prev_job_info;
514 end loop;
515 else
516 hr_utility.set_location(c_proc, 22);
517 --
518 for l_rec in csr_job_pee loop
519 l_prev_job_info := null;
520 --
521 -- itax_organization_id is null in element entry case.
522 --
523 l_prev_job_info.taxable_income := l_rec.taxable_income;
524 l_prev_job_info.mutual_aid_prem := l_rec.mutual_aid_prem;
525 l_prev_job_info.si_prem := l_rec.si_prem;
526 l_prev_job_info.itax := l_rec.itax;
527 l_prev_job_info.foreign_address_flag := l_rec.foreign_address_flag;
528 l_prev_job_info.salary_payer_address_kanji := l_rec.salary_payer_address_kanji;
529 l_prev_job_info.salary_payer_address_kana := l_rec.salary_payer_address_kana;
530 l_prev_job_info.salary_payer_name_kanji := l_rec.salary_payer_name_kanji;
531 l_prev_job_info.salary_payer_name_kana := l_rec.salary_payer_name_kana;
532 l_prev_job_info.termination_date := l_rec.termination_date;
533 --
534 p_prev_jobs(p_prev_jobs.count + 1) := l_prev_job_info;
535 end loop;
536 end if;
537 --
538 hr_utility.set_location(c_proc, 30);
539 --
540 for i in 1..p_prev_jobs.count loop
541 p_prev_jobs(i).mutual_aid_prem := decode_value(p_prev_jobs(i).mutual_aid_prem <> 0, p_prev_jobs(i).mutual_aid_prem);
542 end loop;
543 --
544 hr_utility.set_location('Leaving: ' || c_proc, 100);
545 end get_prev_jobs;
546 --
547 function convert_prev_jobs(p_prev_jobs t_prev_jobs) return t_prev_job_info
548 is
549 l_prev_jobs_count number;
550 l_prev_job_info t_prev_job_info;
551 begin
552 l_prev_jobs_count := p_prev_jobs.count;
553 --
554 -- Null to clear default values when no prev jobs found.
555 --
556 if l_prev_jobs_count = 0 then
557 l_prev_job_info := null;
558 else
559 for i in 1..l_prev_jobs_count loop
560 if i = 1 then
561 l_prev_job_info := p_prev_jobs(i);
562 --
563 l_prev_job_info.salary_payer_address_kanji := hr_jp_standard_pkg.to_zenkaku(l_prev_job_info.salary_payer_address_kanji);
564 l_prev_job_info.salary_payer_address_kana := hr_jp_standard_pkg.upper_kana(
565 hr_jp_standard_pkg.to_hankaku(l_prev_job_info.salary_payer_address_kana, '?'));
566 --
567 if l_prev_jobs_count > 1 then
568 l_prev_job_info.salary_payer_name_kanji := ltrim( l_prev_job_info.salary_payer_name_kanji
569 || ' '
570 || g_prompt_kanji.other
571 || to_char(l_prev_jobs_count - 1)
572 || g_prompt_kanji.count);
573 --
574 l_prev_job_info.salary_payer_name_kana := ltrim( l_prev_job_info.salary_payer_name_kana
575 || ' '
576 || g_prompt_kana.other
577 || to_char(l_prev_jobs_count - 1)
578 || g_prompt_kana.count);
579 end if;
580 --
581 l_prev_job_info.salary_payer_name_kanji := hr_jp_standard_pkg.to_zenkaku(l_prev_job_info.salary_payer_name_kanji);
582 l_prev_job_info.salary_payer_name_kana := hr_jp_standard_pkg.upper_kana(
583 hr_jp_standard_pkg.to_hankaku(l_prev_job_info.salary_payer_name_kana, '?'));
584 else
585 l_prev_job_info.taxable_income := l_prev_job_info.taxable_income + p_prev_jobs(i).taxable_income;
586 -- mutual_aid_prem can be <null>, so remember to use nvl before adding.
587 if p_prev_jobs(i).mutual_aid_prem <> 0 then
588 l_prev_job_info.mutual_aid_prem := nvl(l_prev_job_info.mutual_aid_prem, 0) + p_prev_jobs(i).mutual_aid_prem;
589 end if;
590 l_prev_job_info.si_prem := l_prev_job_info.si_prem + p_prev_jobs(i).si_prem;
591 l_prev_job_info.itax := l_prev_job_info.itax + p_prev_jobs(i).itax;
592 end if;
593 end loop;
594 end if;
595 --
596 return l_prev_job_info;
597 end convert_prev_jobs;
598 --
599 function convert_housing_tbl(
600 p_effective_date in date,
601 p_itax_yea_category in varchar2,
602 p_certificate_info in t_certificate_info,
603 p_housing_tbl in t_housing_tbl)
604 return t_housing_info
605 is
606 --
607 l_total_housing_loan number;
608 l_housing_info t_housing_info;
609 --
610 begin
611 --
612 l_total_housing_loan := p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct;
613 --
614 l_housing_info.residence_date_1 := null;
615 l_housing_info.loan_count := null;
616 l_housing_info.payable_loan := null;
617 l_housing_info.loan_type_1 := null;
618 l_housing_info.loan_balance_1 := null;
619 l_housing_info.residence_date_2 := null;
620 l_housing_info.loan_type_2 := null;
621 l_housing_info.loan_balance_2 := null;
622 --
623 -- l_total_housing_loan is null in case p_itax_yea_category = '0'
624 if l_total_housing_loan <> 0
625 and p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
626 --
627 l_housing_info.payable_loan := l_total_housing_loan;
628 --
629 end if;
630 --
631 if to_number(to_char(p_effective_date,'YYYY')) > 2009
632 or (to_number(to_char(p_effective_date,'YYYY')) = 2009
633 and to_number(to_char(p_effective_date,'MM')) >= 4) then
634 --
635 -- p_housing_tbl exists in case when p_itax_yea_category = '0' and l_total_housing_loan <> 0
636 -- should not related with p_certificate_info.unclaimed_housing_tax_reduct in efile
637 --
638 if p_housing_tbl.count > 0 then
639 --
640 l_housing_info.loan_count := p_housing_tbl.count;
641 --
642 <<loop_housing_tbl>>
643 for i in 1..p_housing_tbl.count loop
644 --
645 if i > 2 then
646 --
647 exit loop_housing_tbl;
648 --
649 end if;
650 --
651 if i = 1 then
652 --
653 l_housing_info.residence_date_1 := p_housing_tbl(i).residence_date;
654 l_housing_info.loan_type_1 := p_housing_tbl(i).loan_type;
655 --
656 if (p_housing_tbl.count > 1
657 or (p_housing_tbl.count = 1 and p_housing_tbl(1).loan_type = '03')) then
658 --
659 l_housing_info.loan_balance_1 := p_housing_tbl(i).loan_balance;
660 --
661 end if;
662 --
663 elsif i = 2 then
664 --
665 l_housing_info.residence_date_2 := p_housing_tbl(i).residence_date;
666 l_housing_info.loan_type_2 := p_housing_tbl(i).loan_type;
667 l_housing_info.loan_balance_2 := p_housing_tbl(i).loan_balance;
668 --
669 end if;
670 --
671 end loop loop_housing_tbl;
672 --
673 end if;
674 --
675 elsif (to_number(to_char(p_effective_date,'YYYY')) = 2009
676 and to_number(to_char(p_effective_date,'MM')) < 4) then
677 --
678 if p_itax_yea_category = '0'
679 and l_total_housing_loan <> 0 then
680 --
681 l_housing_info.loan_count := 1;
682 --
683 l_housing_info.residence_date_1 := p_certificate_info.housing_residence_date;
684 --
685 end if;
686 --
687 end if;
688 --
689 return l_housing_info;
690 --
691 end convert_housing_tbl;
692 --
693 -- |-------------------------------------------------------------------|
694 -- |------------------< get_basic_certificate_info >-------------------|
695 -- |-------------------------------------------------------------------|
696 -- Source function for ITT, get_basic_certificate_info (source function for ITW, WTP, wrapper ITW Archive) (called from gci_s : gci_s_b)
697 procedure get_basic_certificate_info(
698 p_assignment_action_id in number,
699 p_assignment_id in number,
700 p_action_sequence in number,
701 p_effective_date in date,
702 p_itax_organization_id in number,
703 p_itax_category in varchar2,
704 p_itax_yea_category in varchar2,
705 p_employment_category in varchar2,
706 p_certificate_info out nocopy t_tax_info,
707 p_submission_required_flag out nocopy varchar2)
708 is
709 c_proc constant varchar2(61) := c_package || 'get_basic_certificate_info (1)';
710 begin
711 hr_utility.set_location('Entering: ' || c_proc, 10);
712 --
713 if p_itax_yea_category = '0' then
714 hr_utility.set_location(c_proc, 21);
715 --
716 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);
717 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);
718 p_certificate_info.mutual_aid_prem := decode_value(p_certificate_info.mutual_aid_prem <> 0, p_certificate_info.mutual_aid_prem);
719 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);
720 /*
721 p_certificate_info.itax := pay_jp_balance_pkg.get_balance_value(g_itax_asg_ytd, p_assignment_action_id)
722 + 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)
723 + 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);
724 */
725 p_certificate_info.itax_adjustment := pay_jp_balance_pkg.get_balance_value(g_itax_adjustment_asg_ytd, p_assignment_action_id);
726 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);
727 p_certificate_info.itax := p_certificate_info.withholding_itax - p_certificate_info.itax_adjustment;
728 --
729 -- No need to calculate Disaster Tax Reduction in case of YEA because it should be ZERO.
730 --
731 else
732 hr_utility.set_location(c_proc, 22);
733 --
734 -- Derive tax information withheld by current employer.
735 --
736 get_withholding_tax_info(
737 p_assignment_action_id => p_assignment_action_id,
738 p_assignment_id => p_assignment_id,
739 p_action_sequence => p_action_sequence,
740 p_effective_date => p_effective_date,
741 p_itax_organization_id => p_itax_organization_id,
742 p_itax_category => p_itax_category,
743 p_itax_yea_category => p_itax_yea_category,
744 p_withholding_tax_info => p_certificate_info);
745 end if;
746 --
747 hr_utility.set_location(c_proc, 30);
748 --
749 -- Check whether this Tax Receipt is required to submit to Tax Office or not.
750 --
751 p_submission_required_flag := 'N';
752 if to_number(to_char(p_effective_date, 'YYYY')) between 0 and 4712 then
753 if p_itax_yea_category = '0' then
754 if p_employment_category = 'JP_EX' then
755 if p_certificate_info.taxable_income > 1500000 then
756 p_submission_required_flag := 'Y';
757 end if;
758 else
759 if p_certificate_info.taxable_income > 5000000 then
760 p_submission_required_flag := 'Y';
761 end if;
762 end if;
763 else
764 if p_itax_category in ('M_KOU', 'D_KOU') then
765 if p_employment_category = 'JP_EX' then
766 if p_certificate_info.taxable_income > 500000 then
767 p_submission_required_flag := 'Y';
768 end if;
769 else
770 if p_certificate_info.taxable_income > 2500000 then
771 p_submission_required_flag := 'Y';
772 end if;
773 end if;
774 else
775 if p_certificate_info.taxable_income > 500000 then
776 p_submission_required_flag := 'Y';
777 end if;
778 end if;
779 end if;
780 end if;
781 --
782 hr_utility.set_location('Leaving: ' || c_proc, 100);
783 end get_basic_certificate_info;
784 --
785 -- Following is deprecated.
786 --
787 procedure get_certificate_info(
788 p_assignment_action_id in number,
789 p_assignment_id in number,
790 p_action_sequence in number,
791 p_effective_date in date,
792 p_itax_organization_id in number,
793 p_itax_category in varchar2,
794 p_itax_yea_category in varchar2,
795 p_employment_category in varchar2,
796 p_person_id in number,
797 p_business_group_id in number,
798 p_date_earned in date,
799 p_certificate_info out nocopy t_tax_info,
800 p_submission_required_flag out nocopy varchar2,
801 p_withholding_tax_info out nocopy t_tax_info,
802 p_prev_jobs out nocopy t_prev_jobs)
803 is
804 begin
805 get_certificate_info(
806 p_assignment_action_id => p_assignment_action_id,
807 p_assignment_id => p_assignment_id,
808 p_action_sequence => p_action_sequence,
809 p_effective_date => p_effective_date,
810 p_itax_organization_id => p_itax_organization_id,
811 p_itax_category => p_itax_category,
812 p_itax_yea_category => p_itax_yea_category,
813 p_employment_category => p_employment_category,
814 p_person_id => p_person_id,
815 p_business_group_id => p_business_group_id,
816 p_date_earned => p_date_earned,
817 p_certificate_info => p_certificate_info,
818 p_submission_required_flag => p_submission_required_flag,
819 p_withholding_tax_info => p_withholding_tax_info);
820 --
821 get_prev_jobs(
822 p_assignment_id => p_assignment_id,
823 p_action_sequence => p_action_sequence,
824 p_business_group_id => p_business_group_id,
825 p_effective_date => p_effective_date,
826 p_date_earned => p_date_earned,
827 p_itax_organization_id => p_itax_organization_id,
828 p_person_id => p_person_id,
829 p_prev_jobs => p_prev_jobs);
830 end get_certificate_info;
831 -- |-------------------------------------------------------------------|
832 -- |---------------------------< get_dpnts >---------------------------|
833 -- |-------------------------------------------------------------------|
834 -- Private Procedure
835 procedure get_dpnts(
836 p_dpnt_ref_type in varchar2,
837 p_assignment_id in number,
838 p_itax_category in varchar2,
839 p_dpnt_effective_date in date,
840 p_person_id in number,
841 p_effective_date in date,
842 p_sex in varchar2,
843 p_dpnts out nocopy t_dpnts)
844 is
845 c_proc constant varchar2(61) := c_package || 'get_dpnts';
846 l_index number := 0;
847 l_dpnt_rec per_jp_ctr_utility_pkg.t_itax_dpnt_rec;
848 --
849 cursor csr_dep is
850 select /*+ ORDERED USE_NL(PER) */
851 per.last_name LAST_NAME_KANA,
852 per.per_information18 LAST_NAME_KANJI,
853 per.first_name FIRST_NAME_KANA,
854 per.per_information19 FIRST_NAME_KANJI,
855 per.date_of_birth,
856 decode(ctr.contact_type, 'S', decode(p_sex, 'F', g_prompt_kanji.husband, g_prompt_kanji.wife),
857 hr_general.decode_lookup('CONTACT', ctr.contact_type)) D_CONTACT_TYPE_KANJI,
858 decode(ctr.contact_type, 'S', decode(p_sex, 'F', g_prompt_kana.husband, g_prompt_kana.wife),
859 hr_jp_standard_pkg.to_hankaku(hr_general.decode_lookup('JP_CONTACT_KANA', ctr.contact_type))) D_CONTACT_TYPE_KANA,
860 trunc(months_between(
861 to_date(decode(to_char(nvl(least(per.date_of_death, p_effective_date), p_effective_date),'YYYY/MM/DD'),
862 to_char(per.date_of_death,'YYYY/MM/DD'), to_char(per.date_of_death,'YYYY/MM/DD'),
863 to_char(add_months(trunc(p_effective_date, 'YYYY'), 12) - 1,'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
864 per.date_of_birth) / 12) age
865 from per_contact_relationships ctr,
866 per_all_people_f per
867 where ctr.person_id = p_person_id
868 and ctr.dependent_flag = 'Y'
869 and p_effective_date
870 between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date)
871 and per.person_id = ctr.contact_person_id
872 and ( p_effective_date
873 between per.effective_start_date and per.effective_end_date
874 or (
875 per.effective_start_date = per.start_date
876 and not exists(
877 select null
878 from per_all_people_f per2
879 where per2.person_id = per.person_id
880 and p_effective_date
881 between per2.effective_start_date and per2.effective_end_date)
882 )
883 )
884 order by
885 decode(ctr.contact_type, 'S', 1, 2),
886 per.date_of_birth,
887 decode(per.sex, 'M', 1, 'F', 2, 3),
888 per.last_name,
889 per.first_name;
890 begin
891 hr_utility.set_location('Entering: ' || c_proc, 10);
892 --
893 if p_itax_category in ('M_KOU', 'M_OTSU', 'D_KOU', 'D_OTSU') then
894 hr_utility.set_location(c_proc, 20);
895 --
896 if p_dpnt_ref_type like 'CEI%' then
897 hr_utility.set_location(c_proc, 21);
898 --
899 -- No need to care for performance issue for multiple calls of the following PL/SQL function.
900 -- The following function caches the output variable result for the same input parameter values.
901 --
902 per_jp_ctr_utility_pkg.get_itax_dpnt_info(
903 p_assignment_id => p_assignment_id,
904 p_itax_type => p_itax_category,
905 p_effective_date => p_dpnt_effective_date,
906 p_itax_dpnt_rec => l_dpnt_rec);
907 --
908 for i in 1..l_dpnt_rec.contact_type_tbl.count loop
909 l_index := l_index + 1;
910 --
911 p_dpnts(l_index).contact_type_kanji := l_dpnt_rec.d_contact_type_kanji_tbl(i);
912 p_dpnts(l_index).contact_type_kana := l_dpnt_rec.d_contact_type_kana_tbl(i);
913 p_dpnts(l_index).last_name_kanji := l_dpnt_rec.last_name_kanji_tbl(i);
914 p_dpnts(l_index).last_name_kana := l_dpnt_rec.last_name_kana_tbl(i);
915 p_dpnts(l_index).first_name_kanji := l_dpnt_rec.first_name_kanji_tbl(i);
916 p_dpnts(l_index).first_name_kana := l_dpnt_rec.first_name_kana_tbl(i);
917 p_dpnts(l_index).junior := l_dpnt_rec.junior_tbl(i);
918 end loop;
919 else
920 hr_utility.set_location(c_proc, 22);
921 --
922 for l_rec in csr_dep loop
923 l_index := l_index + 1;
924 --
925 p_dpnts(l_index).contact_type_kanji := l_rec.d_contact_type_kanji;
926 p_dpnts(l_index).contact_type_kana := l_rec.d_contact_type_kana;
927 p_dpnts(l_index).last_name_kanji := l_rec.last_name_kanji;
928 p_dpnts(l_index).last_name_kana := l_rec.last_name_kana;
929 p_dpnts(l_index).first_name_kanji := l_rec.first_name_kanji;
930 p_dpnts(l_index).first_name_kana := l_rec.first_name_kana;
931 --
932 -- exclude l_rec.age null
933 if l_rec.age <= 15
934 and p_itax_category in ('M_KOU','D_KOU') then
935 --
936 p_dpnts(l_index).junior := 'Y';
937 --
938 end if;
939 end loop;
940 end if;
941 end if;
942 --
943 hr_utility.set_location('Leaving: ' || c_proc, 100);
944 end get_dpnts;
945 --
946 -- ----------------------------------------------------------------------------
947 -- get_housing_tbl
948 -- ----------------------------------------------------------------------------
949 --
950 procedure get_housing_tbl(
951 p_assignment_id in number,
952 p_date_earned in date,
953 p_housing_tbl out nocopy t_housing_tbl)
954 is
955 --
956 c_proc constant varchar2(61) := c_package||'get_housing';
957 --
958 l_cnt number := 0;
959 --
960 cursor csr_housing
961 is
962 select /*+ ORDERED
963 USE_NL(PIV_1, PIV_3, PLIV_1, PLIV_2, PLIV_3, PEE, PEEV_1, PEEV_2, PEEV_3)
964 INDEX(PIV_1 PAY_INPUT_VALUES_F_PK)
965 INDEX(PIV_3 PAY_INPUT_VALUES_F_PK)
966 INDEX(PLIV_1 PAY_LINK_INPUT_VALUES_F_N2)
967 INDEX(PLIV_2 PAY_LINK_INPUT_VALUES_F_N2)
968 INDEX(PLIV_3 PAY_LINK_INPUT_VALUES_F_N2)
969 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51)
970 INDEX(PEEV_1 PAY_ELEMENT_ENTRY_VALUES_F_N50)
971 INDEX(PEEV_2 PAY_ELEMENT_ENTRY_VALUES_F_N50)
972 INDEX(PEEV_3 PAY_ELEMENT_ENTRY_VALUES_F_N50) */
973 decode(piv_1.uom,'D',fnd_date.canonical_to_date(peev_1.screen_entry_value),null) residence_date,
974 peev_2.screen_entry_value loan_type,
975 decode(piv_3.uom,'M',fnd_number.canonical_to_number(peev_3.screen_entry_value),null) loan_balance
976 from pay_input_values_f piv_1,
977 pay_input_values_f piv_3,
978 pay_link_input_values_f pliv_1,
979 pay_link_input_values_f pliv_2,
980 pay_link_input_values_f pliv_3,
981 pay_element_entries_f pee,
982 pay_element_entry_values_f peev_1,
983 pay_element_entry_values_f peev_2,
984 pay_element_entry_values_f peev_3
985 where piv_1.input_value_id = g_housing_loan_info_elm.res_date_iv
986 and p_date_earned
987 between piv_1.effective_start_date and piv_1.effective_end_date
988 and piv_3.input_value_id = g_housing_loan_info_elm.loan_balance_iv
989 and p_date_earned
990 between piv_3.effective_start_date and piv_3.effective_end_date
991 and pliv_1.input_value_id = piv_1.input_value_id
992 and p_date_earned
993 between pliv_1.effective_start_date and pliv_1.effective_end_date
994 and pliv_2.input_value_id = g_housing_loan_info_elm.loan_type_iv
995 and p_date_earned
996 between pliv_2.effective_start_date and pliv_2.effective_end_date
997 and pliv_3.input_value_id = piv_3.input_value_id
998 and p_date_earned
999 between pliv_3.effective_start_date and pliv_3.effective_end_date
1000 and pee.element_link_id = pliv_1.element_link_id
1001 and pee.element_link_id = pliv_2.element_link_id
1002 and pee.element_link_id = pliv_3.element_link_id
1003 and pee.assignment_id = p_assignment_id
1004 and pee.entry_type = 'E'
1005 and p_date_earned
1006 between pee.effective_start_date and pee.effective_end_date
1007 and peev_1.element_entry_id = pee.element_entry_id
1008 and peev_1.input_value_id = pliv_1.input_value_id
1009 and peev_1.effective_start_date = pee.effective_start_date
1010 and peev_1.effective_end_date = pee.effective_end_date
1011 and peev_2.element_entry_id = pee.element_entry_id
1012 and peev_2.input_value_id = pliv_2.input_value_id
1013 and peev_2.effective_start_date = pee.effective_start_date
1014 and peev_2.effective_end_date = pee.effective_end_date
1015 and peev_3.element_entry_id = pee.element_entry_id
1016 and peev_3.input_value_id = pliv_3.input_value_id
1017 and peev_3.effective_start_date = pee.effective_start_date
1018 and peev_3.effective_end_date = pee.effective_end_date
1019 order by 1, 2;
1020 --
1021 l_csr_housing csr_housing%rowtype;
1022 --
1023 begin
1024 --
1025 hr_utility.set_location('Entering: '||c_proc,10);
1026 --
1027 open csr_housing;
1028 loop
1029 --
1030 fetch csr_housing into l_csr_housing;
1031 exit when csr_housing%notfound;
1032 --
1033 l_cnt := l_cnt + 1;
1034 --
1035 p_housing_tbl(l_cnt).residence_date := l_csr_housing.residence_date;
1036 p_housing_tbl(l_cnt).loan_type := l_csr_housing.loan_type;
1037 p_housing_tbl(l_cnt).loan_balance := l_csr_housing.loan_balance;
1038 --
1039 end loop;
1040 close csr_housing;
1041 --
1042 hr_utility.set_location('Leaving: '||c_proc,100);
1043 --
1044 end get_housing_tbl;
1045 --
1046 -- |-------------------------------------------------------------------|
1047 -- |----------------------< get_ee_description >-----------------------|
1048 -- |-------------------------------------------------------------------|
1049 procedure get_ee_description(
1050 p_assignment_id in number,
1051 p_business_group_id in number,
1052 p_effective_date in date,
1053 p_itw_override_flag out nocopy varchar2,
1054 p_itw_description1 out nocopy varchar2,
1055 p_itw_description2 out nocopy varchar2,
1056 p_itw_description3 out nocopy varchar2,
1057 p_itw_description4 out nocopy varchar2,
1058 p_itw_description5 out nocopy varchar2,
1059 p_wtm_override_flag out nocopy varchar2,
1060 p_wtm_description1 out nocopy varchar2,
1061 p_wtm_description2 out nocopy varchar2,
1062 p_wtm_description3 out nocopy varchar2,
1063 p_wtm_description4 out nocopy varchar2,
1064 p_wtm_description5 out nocopy varchar2)
1065 is
1066 c_proc constant varchar2(61) := c_package || 'get_ee_description';
1067 --
1068 cursor csr_eev is
1069 select /*+ ORDERD USE_NL(PEE PEEV PIV) */
1070 nvl(min(decode(piv.display_sequence, 1, peev.screen_entry_value)), 'N') itw_override_flag,
1071 min(decode(piv.display_sequence, 2, peev.screen_entry_value)) itw_description1,
1072 min(decode(piv.display_sequence, 3, peev.screen_entry_value)) itw_description2,
1073 min(decode(piv.display_sequence, 4, peev.screen_entry_value)) itw_description3,
1074 min(decode(piv.display_sequence, 5, peev.screen_entry_value)) itw_description4,
1075 min(decode(piv.display_sequence, 6, peev.screen_entry_value)) itw_description5,
1076 nvl(min(decode(piv.display_sequence, 7, peev.screen_entry_value)), 'N') wtm_override_flag,
1077 min(decode(piv.display_sequence, 8, peev.screen_entry_value)) wtm_description1,
1078 min(decode(piv.display_sequence, 9, peev.screen_entry_value)) wtm_description2,
1079 min(decode(piv.display_sequence, 10, peev.screen_entry_value)) wtm_description3,
1080 min(decode(piv.display_sequence, 11, peev.screen_entry_value)) wtm_description4,
1081 min(decode(piv.display_sequence, 12, peev.screen_entry_value)) wtm_description5
1082 from pay_element_links_f pel,
1083 pay_element_entries_f pee,
1084 pay_element_entry_values_f peev,
1085 pay_input_values_f piv
1086 where pel.element_type_id = g_desc_element_type_id
1087 and pel.business_group_id = p_business_group_id
1088 and p_effective_date
1089 between pel.effective_start_date and pel.effective_end_date
1090 and pee.assignment_id = p_assignment_id
1091 and pee.element_link_id = pel.element_link_id
1092 and pee.entry_type = 'E'
1093 and p_effective_date
1094 between pee.effective_start_date and pee.effective_end_date
1095 and peev.element_entry_id = pee.element_entry_id
1096 and peev.effective_start_date = pee.effective_start_date
1097 and peev.effective_end_date = pee.effective_end_date
1098 and piv.input_value_id = peev.input_value_id
1099 and p_effective_date
1100 between piv.effective_start_date and piv.effective_end_date
1101 group by pee.element_entry_id;
1102 begin
1103 hr_utility.set_location('Entering: ' || c_proc, 10);
1104 --
1105 open csr_eev;
1106 fetch csr_eev into
1107 p_itw_override_flag,
1108 p_itw_description1,
1109 p_itw_description2,
1110 p_itw_description3,
1111 p_itw_description4,
1112 p_itw_description5,
1113 p_wtm_override_flag,
1114 p_wtm_description1,
1115 p_wtm_description2,
1116 p_wtm_description3,
1117 p_wtm_description4,
1118 p_wtm_description5;
1119 if csr_eev%notfound then
1120 hr_utility.trace('EE not found.');
1121 --
1122 p_itw_override_flag := 'N';
1123 p_itw_description1 := null;
1124 p_itw_description2 := null;
1125 p_itw_description3 := null;
1126 p_itw_description4 := null;
1127 p_itw_description5 := null;
1128 p_wtm_override_flag := 'N';
1129 p_wtm_description1 := null;
1130 p_wtm_description2 := null;
1131 p_wtm_description3 := null;
1132 p_wtm_description4 := null;
1133 p_wtm_description5 := null;
1134 end if;
1135 close csr_eev;
1136 --
1137 hr_utility.set_location('Leaving: ' || c_proc, 100);
1138 end get_ee_description;
1139 --
1140 -- called from gci_b, gci_itw_arc
1141 --
1142 procedure get_ee_description(
1143 p_assignment_id in number,
1144 p_business_group_id in number,
1145 p_effective_date in date,
1146 p_itw_override_flag out nocopy varchar2,
1147 p_itw_description out nocopy varchar2,
1148 p_wtm_override_flag out nocopy varchar2,
1149 p_wtm_description out nocopy varchar2)
1150 is
1151 l_itw_description1 pay_element_entry_values.screen_entry_value%type;
1152 l_itw_description2 pay_element_entry_values.screen_entry_value%type;
1153 l_itw_description3 pay_element_entry_values.screen_entry_value%type;
1154 l_itw_description4 pay_element_entry_values.screen_entry_value%type;
1155 l_itw_description5 pay_element_entry_values.screen_entry_value%type;
1156 l_wtm_description1 pay_element_entry_values.screen_entry_value%type;
1157 l_wtm_description2 pay_element_entry_values.screen_entry_value%type;
1158 l_wtm_description3 pay_element_entry_values.screen_entry_value%type;
1159 l_wtm_description4 pay_element_entry_values.screen_entry_value%type;
1160 l_wtm_description5 pay_element_entry_values.screen_entry_value%type;
1161 begin
1162 get_ee_description(
1163 p_assignment_id => p_assignment_id,
1164 p_business_group_id => p_business_group_id,
1165 p_effective_date => p_effective_date,
1166 p_itw_override_flag => p_itw_override_flag,
1167 p_itw_description1 => l_itw_description1,
1168 p_itw_description2 => l_itw_description2,
1169 p_itw_description3 => l_itw_description3,
1170 p_itw_description4 => l_itw_description4,
1171 p_itw_description5 => l_itw_description5,
1172 p_wtm_override_flag => p_wtm_override_flag,
1173 p_wtm_description1 => l_wtm_description1,
1174 p_wtm_description2 => l_wtm_description2,
1175 p_wtm_description3 => l_wtm_description3,
1176 p_wtm_description4 => l_wtm_description4,
1177 p_wtm_description5 => l_wtm_description5);
1178 --
1179 p_itw_description := l_itw_description1
1180 || l_itw_description2
1181 || l_itw_description3
1182 || l_itw_description4
1183 || l_itw_description5;
1184 p_wtm_description := l_wtm_description1
1185 || l_wtm_description2
1186 || l_wtm_description3
1187 || l_wtm_description4
1188 || l_wtm_description5;
1189 end get_ee_description;
1190 -- |-------------------------------------------------------------------|
1191 -- |------------------------< add_description >------------------------|
1192 -- |-------------------------------------------------------------------|
1193 procedure add_description(
1194 p_description_type in varchar2,
1195 p_description_kanji in varchar2,
1196 p_description_kana in varchar2 default null,
1197 p_descriptions in out nocopy t_descriptions)
1198 is
1199 l_index number;
1200 begin
1201 l_index := p_descriptions.count + 1;
1202 --
1203 -- PAYJPITW outputs mixed Kanji/Kana characters
1204 -- into description field, so do not use
1205 -- hr_jp_standard_pkg.to_zenkaku.
1206 -- Use hr_jp_standard_pkg.to_zenkaku just before
1207 -- outputting those in reports, e.g. PAYJPWTM.
1208 --
1209 p_descriptions(l_index).description_type := p_description_type;
1210 p_descriptions(l_index).description_kanji := p_description_kanji;
1211 -- p_descriptions(l_index).description_kana := hr_jp_standard_pkg.upper_kana(
1212 -- hr_jp_standard_pkg.to_hankaku(p_description_kana, '?'));
1213 p_descriptions(l_index).description_kana := hr_jp_standard_pkg.to_hankaku(p_description_kana, '?');
1214 end add_description;
1215 -- |-------------------------------------------------------------------|
1216 -- |-----------------------< get_descriptions >------------------------|
1217 -- |-------------------------------------------------------------------|
1218 procedure get_descriptions(
1219 p_assignment_id in number,
1220 p_person_id in number,
1221 p_effective_date in date,
1222 p_itax_yea_category in varchar2,
1223 p_certificate_info in t_certificate_info,
1224 p_last_name_kanji in varchar2,
1225 p_last_name_kana in varchar2,
1226 p_dpnts in t_dpnts,
1227 p_prev_jobs in t_prev_jobs,
1228 p_housing_tbl in t_housing_tbl,
1229 p_report_type in varchar2, --> ITW/WTM
1230 p_descriptions out nocopy t_descriptions)
1231 is
1232 --
1233 c_proc constant varchar2(61) := c_package || 'get_descriptions';
1234 --
1235 l_description_kanji varchar2(32767);
1236 l_description_kana varchar2(32767);
1237 l_separator varchar2(1);
1238 --
1239 procedure add_desc(
1240 p_description_type in varchar2,
1241 p_prefix_kanji in varchar2,
1242 p_prefix_kana in varchar2,
1243 p_amount in number,
1244 p_min_length_kanji in number,
1245 p_min_length_kana in number)
1246 is
1247 --
1248 l_description_kanji varchar2(32767);
1249 l_description_kana varchar2(32767);
1250 l_length number;
1251 --
1252 begin
1253 --
1254 l_description_kanji := to_char(p_amount);
1255 l_description_kana := l_description_kanji;
1256 --
1257 if p_min_length_kanji is not null or p_min_length_kana is not null then
1258 --
1259 l_length := nvl(length(l_description_kanji), 0);
1260 --
1261 if l_length < p_min_length_kanji then
1262 --
1263 l_description_kanji := lpad(
1264 nvl(l_description_kanji, ' '),
1265 p_min_length_kanji,
1266 ' ');
1267 --
1268 end if;
1269 --
1270 if l_length < p_min_length_kana then
1271 --
1272 l_description_kana := lpad(
1273 nvl(l_description_kana, ' '),
1274 p_min_length_kana,
1275 ' ');
1276 --
1277 end if;
1278 --
1279 end if;
1280 --
1281 l_description_kanji := p_prefix_kanji
1282 || l_description_kanji
1283 || g_prompt_kanji.yen;
1284 --
1285 l_description_kana := p_prefix_kana
1286 || l_description_kana
1287 || g_prompt_kana.yen;
1288 --
1289 add_description(
1290 p_description_type,
1291 l_description_kanji,
1292 l_description_kana,
1293 p_descriptions);
1294 --
1295 end add_desc;
1296 --
1297 procedure add_fixed_rate_description(
1298 p_fixed_rate_tax_reduction in number,
1299 p_length_kanji in number default null,
1300 p_length_kana in number default null)
1301 is
1302 begin
1303 --
1304 add_desc(
1305 FIXED_RATE_TAX_REDUCTION,
1306 g_prompt_kanji.fixed_rate_tax_reduction,
1307 g_prompt_kana.fixed_rate_tax_reduction,
1308 p_fixed_rate_tax_reduction,
1309 p_length_kanji,
1310 p_length_kana);
1311 --
1312 end add_fixed_rate_description;
1313 --
1314 procedure add_np_prem_description(
1315 p_np_prem in number,
1316 p_length_kanji in number default null,
1317 p_length_kana in number default null)
1318 is
1319 begin
1320 --
1321 add_desc(
1322 NP_PREM,
1323 g_prompt_kanji.national_pens_prem,
1324 g_prompt_kana.national_pens_prem,
1325 p_np_prem,
1326 p_length_kanji,
1327 p_length_kana);
1328 --
1329 end add_np_prem_description;
1330 --
1331 -- following procedure are available since april in 2009
1332 procedure proc_housing_loan1
1333 is
1334 --
1335 l_loan_type_kanji varchar2(2000);
1336 l_loan_type_kana varchar2(2000);
1337 l_total_housing_loan number;
1338 --
1339 begin
1340 --
1341 if to_number(to_char(p_effective_date,'YYYY')) > 2009
1342 or (to_number(to_char(p_effective_date,'YYYY')) = 2009
1343 and to_number(to_char(p_effective_date,'MM')) >= 4) then
1344 --
1345 if p_report_type = 'ITW' then
1346 --
1347 l_total_housing_loan := p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct;
1348 --
1349 -- always prompt should be printed (in accordance with guidance of national tax agency)
1350 l_description_kanji := g_prompt_kanji.housing_loan_itw;
1351 l_description_kana := g_prompt_kana.housing_loan_itw;
1352 --
1353 if p_itax_yea_category = '0'
1354 and l_total_housing_loan <> 0
1355 and p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
1356 --
1357 l_description_kanji := l_description_kanji||to_char(l_total_housing_loan)||g_prompt_kanji.yen;
1358 l_description_kana := l_description_kana||to_char(l_total_housing_loan)||g_prompt_kana.yen;
1359 --
1360 else
1361 --
1362 l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.yen;
1363 l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.yen;
1364 --
1365 end if;
1366 --
1367 -- following routine has been moved to proc_housing_loan2 to reorder sequence of item
1368 --
1369 -- -- always prompt should be printed (in accordance with guidance of national tax agency)
1370 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.housing_res_date_itw;
1371 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.housing_res_date_itw;
1372 --
1373 -- if p_itax_yea_category = '0'
1374 -- and l_total_housing_loan <> 0
1375 -- and p_housing_tbl.count <> 0 then
1376 -- --
1377 -- if p_housing_tbl.count = 1 then
1378 -- --
1379 -- l_description_kanji := l_description_kanji||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(1).residence_date,c_date_fmt_itw);
1380 -- l_description_kana := l_description_kana||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(1).residence_date,c_date_fmt_itw);
1381 -- --
1382 -- end if;
1383 -- --
1384 -- if (p_housing_tbl.count > 1
1385 -- or (p_housing_tbl.count = 1 and p_housing_tbl(1).loan_type = '03')) then
1386 -- --
1387 -- for i in 1..p_housing_tbl.count loop
1388 -- --
1389 -- l_loan_type_kanji := null;
1390 -- l_loan_type_kana := null;
1391 -- --
1392 -- if i = 1 then
1393 -- --
1394 -- -- this skip in case loan_type = 03
1395 -- --
1396 -- if p_housing_tbl.count <> 1 then
1397 -- --
1398 -- l_description_kanji := l_description_kanji||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,c_date_fmt_itw);
1399 -- l_description_kana := l_description_kana||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,c_date_fmt_itw);
1400 -- --
1401 -- end if;
1402 -- --
1403 -- else
1404 -- --
1405 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.housing_res_date_itw||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,c_date_fmt_itw);
1406 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.housing_res_date_itw||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,c_date_fmt_itw);
1407 -- --
1408 -- end if;
1409 -- --
1410 -- -- return multiple resident date only in case unclaimed reduciton = 0, type, balance are not required. (ie. payable_loan = total_housing_loan <> 0)
1411 -- --
1412 -- if p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
1413 -- --
1414 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.housing_loan_balance_itw;
1415 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.housing_loan_balance_itw;
1416 -- --
1417 -- if p_housing_tbl(i).loan_type is not null then
1418 -- --
1419 -- if p_housing_tbl(i).loan_type in ('01','02') then
1420 -- --
1421 -- l_loan_type_kanji := g_prompt_kanji.housing_loan_type_gen_itw;
1422 -- l_loan_type_kana := g_prompt_kana.housing_loan_type_gen_itw;
1423 -- --
1424 -- elsif p_housing_tbl(i).loan_type = '03' then
1425 -- --
1426 -- l_loan_type_kanji := g_prompt_kanji.housing_loan_type_ext_itw;
1427 -- l_loan_type_kana := g_prompt_kana.housing_loan_type_ext_itw;
1428 -- --
1429 -- elsif p_housing_tbl(i).loan_type = '04' then
1430 -- --
1431 -- l_loan_type_kanji := g_prompt_kanji.housing_loan_type_etq_itw;
1432 -- l_loan_type_kana := g_prompt_kana.housing_loan_type_etq_itw;
1433 -- --
1434 -- end if;
1435 -- --
1436 -- l_description_kanji := l_description_kanji||c_space_separator||l_loan_type_kanji;
1437 -- l_description_kana := l_description_kana||c_space_separator||l_loan_type_kana;
1438 -- --
1439 -- end if;
1440 -- --
1441 -- if p_housing_tbl(i).loan_balance is not null then
1442 -- --
1443 -- l_description_kanji := l_description_kanji||c_space_separator||to_char(p_housing_tbl(i).loan_balance)||g_prompt_kanji.yen;
1444 -- l_description_kana := l_description_kana||c_space_separator||to_char(p_housing_tbl(i).loan_balance)||g_prompt_kana.yen;
1445 -- --
1446 -- else
1447 -- --
1448 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.yen;
1449 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.yen;
1450 -- --
1451 -- end if;
1452 -- --
1453 -- end if;
1454 -- --
1455 -- end loop;
1456 -- --
1457 -- end if;
1458 -- --
1459 -- end if;
1460 --
1461 add_description(
1462 HOUSING_TAX_REDUCTION,
1463 l_description_kanji,
1464 l_description_kana,
1465 p_descriptions);
1466 --
1467 elsif p_report_type = 'WTM' then
1468 --
1469 -- p_housing_tbl exists in case when p_itax_yea_category = '0' and total_housing_loan <> 0
1470 --
1471 if p_housing_tbl.count > 2 then
1472 --
1473 for i in 3..p_housing_tbl.count loop
1474 --
1475 l_description_kanji :=
1476 g_prompt_kanji.housing_loan_type_wtm||c_bracket_left||to_char(i)||g_prompt_kanji.housing_count_wtm||c_bracket_right||
1477 p_housing_tbl(i).loan_type||c_space_separator||
1478 g_prompt_kanji.housing_res_date_wtm||c_bracket_left||to_char(i)||g_prompt_kanji.housing_count_wtm||c_bracket_right||
1479 hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,g_prompt_kanji.date_format)||c_space_separator||
1480 g_prompt_kanji.housing_loan_balance_wtm||c_bracket_left||to_char(i)||g_prompt_kanji.housing_count_wtm||c_bracket_right||
1481 to_char(p_housing_tbl(i).loan_balance)||g_prompt_kanji.yen;
1482 l_description_kana :=
1483 g_prompt_kana.housing_loan_type_wtm||c_bracket_left||to_char(i)||g_prompt_kana.housing_count_wtm||c_bracket_right||
1484 p_housing_tbl(i).loan_type||c_space_separator||
1485 g_prompt_kana.housing_res_date_wtm||c_bracket_left||to_char(i)||g_prompt_kana.housing_count_wtm||c_bracket_right||
1486 hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,g_prompt_kana.date_format)||c_space_separator||
1487 g_prompt_kana.housing_loan_balance_wtm||c_bracket_left||to_char(i)||g_prompt_kana.housing_count_wtm||c_bracket_right||
1488 to_char(p_housing_tbl(i).loan_balance)||g_prompt_kana.yen;
1489 --
1490 add_description(
1491 HOUSING_TAX_REDUCTION,
1492 l_description_kanji,
1493 l_description_kana,
1494 p_descriptions);
1495 --
1496 end loop;
1497 --
1498 end if;
1499 --
1500 end if;
1501 --
1502 -- data source between jan and mar is different from source since april
1503 -- but it should be compliant with 2009 format
1504 --
1505 elsif (to_number(to_char(p_effective_date,'YYYY')) = 2009
1506 and to_number(to_char(p_effective_date,'MM')) < 4) then
1507 --
1508 -- no action for WTM because it should work for p_housing_tbl.count > 2
1509 --
1510 if p_report_type = 'ITW' then
1511 --
1512 l_total_housing_loan := p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct;
1513 --
1514 -- always prompt should be printed (in accordance with guidance of national tax agency)
1515 l_description_kanji := g_prompt_kanji.housing_loan_itw;
1516 l_description_kana := g_prompt_kana.housing_loan_itw;
1517 --
1518 if p_itax_yea_category = '0'
1519 and l_total_housing_loan <> 0
1520 and p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
1521 --
1522 l_description_kanji := l_description_kanji||to_char(l_total_housing_loan)||g_prompt_kanji.yen;
1523 l_description_kana := l_description_kana||to_char(l_total_housing_loan)||g_prompt_kana.yen;
1524 --
1525 else
1526 --
1527 l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.yen;
1528 l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.yen;
1529 --
1530 end if;
1531 --
1532 -- following routine has been moved to proc_housing_loan2 to reorder sequence of item
1533 --
1534 -- -- always prompt should be printed (in accordance with guidance of national tax agency)
1535 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.housing_res_date_itw;
1536 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.housing_res_date_itw;
1537 ----
1538 -- if p_itax_yea_category = '0'
1539 -- and l_total_housing_loan <> 0 then
1540 -- --
1541 -- l_description_kanji := l_description_kanji||hr_jp_standard_pkg.to_jp_char(p_certificate_info.housing_residence_date,c_date_fmt_itw);
1542 -- l_description_kana := l_description_kana||hr_jp_standard_pkg.to_jp_char(p_certificate_info.housing_residence_date,c_date_fmt_itw);
1543 -- --
1544 -- end if;
1545 --
1546 add_description(
1547 HOUSING_TAX_REDUCTION,
1548 l_description_kanji,
1549 l_description_kana,
1550 p_descriptions);
1551 --
1552 end if;
1553 --
1554 end if;
1555 --
1556 end proc_housing_loan1;
1557 --
1558 -- this is required to list as order payable loan, national pension, housing info by tax agency guide
1559 procedure proc_housing_loan2
1560 is
1561 --
1562 l_loan_type_kanji varchar2(2000);
1563 l_loan_type_kana varchar2(2000);
1564 l_total_housing_loan number;
1565 --
1566 begin
1567 --
1568 if to_number(to_char(p_effective_date,'YYYY')) > 2009
1569 or (to_number(to_char(p_effective_date,'YYYY')) = 2009
1570 and to_number(to_char(p_effective_date,'MM')) >= 4) then
1571 --
1572 if p_report_type = 'ITW' then
1573 --
1574 l_total_housing_loan := p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct;
1575 --
1576 -- following routine has been moved to proc_housing_loan1 to reorder sequence of item
1577 --
1578 -- -- always prompt should be printed (in accordance with guidance of national tax agency)
1579 -- l_description_kanji := g_prompt_kanji.housing_loan_itw;
1580 -- l_description_kana := g_prompt_kana.housing_loan_itw;
1581 ----
1582 -- if p_itax_yea_category = '0'
1583 -- and l_total_housing_loan <> 0
1584 -- and p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
1585 -- --
1586 -- l_description_kanji := l_description_kanji||to_char(l_total_housing_loan)||g_prompt_kanji.yen;
1587 -- l_description_kana := l_description_kana||to_char(l_total_housing_loan)||g_prompt_kana.yen;
1588 -- --
1589 -- else
1590 -- --
1591 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.yen;
1592 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.yen;
1593 -- --
1594 -- end if;
1595 --
1596 -- -- always prompt should be printed (in accordance with guidance of national tax agency)
1597 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.housing_res_date_itw;
1598 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.housing_res_date_itw;
1599 l_description_kanji := g_prompt_kanji.housing_res_date_itw;
1600 l_description_kana := g_prompt_kana.housing_res_date_itw;
1601 --
1602 if p_itax_yea_category = '0'
1603 and l_total_housing_loan <> 0
1604 and p_housing_tbl.count <> 0 then
1605 --
1606 if p_housing_tbl.count = 1 then
1607 --
1608 l_description_kanji := l_description_kanji||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(1).residence_date,c_date_fmt_itw);
1609 l_description_kana := l_description_kana||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(1).residence_date,c_date_fmt_itw);
1610 --
1611 end if;
1612 --
1613 if (p_housing_tbl.count > 1
1614 or (p_housing_tbl.count = 1 and p_housing_tbl(1).loan_type = '03')) then
1615 --
1616 for i in 1..p_housing_tbl.count loop
1617 --
1618 l_loan_type_kanji := null;
1619 l_loan_type_kana := null;
1620 --
1621 if i = 1 then
1622 --
1623 -- this skip in case loan_type = 03
1624 --
1625 if p_housing_tbl.count <> 1 then
1626 --
1627 l_description_kanji := l_description_kanji||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,c_date_fmt_itw);
1628 l_description_kana := l_description_kana||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,c_date_fmt_itw);
1629 --
1630 end if;
1631 --
1632 else
1633 --
1634 l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.housing_res_date_itw||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,c_date_fmt_itw);
1635 l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.housing_res_date_itw||hr_jp_standard_pkg.to_jp_char(p_housing_tbl(i).residence_date,c_date_fmt_itw);
1636 --
1637 end if;
1638 --
1639 -- return multiple resident date only in case unclaimed reduciton = 0, type, balance are not required. (ie. payable_loan = total_housing_loan <> 0)
1640 --
1641 if p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
1642 --
1643 l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.housing_loan_balance_itw;
1644 l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.housing_loan_balance_itw;
1645 --
1646 if p_housing_tbl(i).loan_type is not null then
1647 --
1648 if p_housing_tbl(i).loan_type in ('01','02') then
1649 --
1650 l_loan_type_kanji := g_prompt_kanji.housing_loan_type_gen_itw;
1651 l_loan_type_kana := g_prompt_kana.housing_loan_type_gen_itw;
1652 --
1653 elsif p_housing_tbl(i).loan_type = '03' then
1654 --
1655 l_loan_type_kanji := g_prompt_kanji.housing_loan_type_ext_itw;
1656 l_loan_type_kana := g_prompt_kana.housing_loan_type_ext_itw;
1657 --
1658 elsif p_housing_tbl(i).loan_type = '04' then
1659 --
1660 l_loan_type_kanji := g_prompt_kanji.housing_loan_type_etq_itw;
1661 l_loan_type_kana := g_prompt_kana.housing_loan_type_etq_itw;
1662 --
1663 end if;
1664 --
1665 l_description_kanji := l_description_kanji||c_space_separator||l_loan_type_kanji;
1666 l_description_kana := l_description_kana||c_space_separator||l_loan_type_kana;
1667 --
1668 end if;
1669 --
1670 if p_housing_tbl(i).loan_balance is not null then
1671 --
1672 l_description_kanji := l_description_kanji||c_space_separator||to_char(p_housing_tbl(i).loan_balance)||g_prompt_kanji.yen;
1673 l_description_kana := l_description_kana||c_space_separator||to_char(p_housing_tbl(i).loan_balance)||g_prompt_kana.yen;
1674 --
1675 else
1676 --
1677 l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.yen;
1678 l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.yen;
1679 --
1680 end if;
1681 --
1682 end if;
1683 --
1684 end loop;
1685 --
1686 end if;
1687 --
1688 end if;
1689 --
1690 add_description(
1691 HOUSING_LOAN_INFO,
1692 l_description_kanji,
1693 l_description_kana,
1694 p_descriptions);
1695 --
1696 end if;
1697 --
1698 -- data source between jan and mar is different from source since april
1699 -- but it should be compliant with 2009 format
1700 --
1701 elsif (to_number(to_char(p_effective_date,'YYYY')) = 2009
1702 and to_number(to_char(p_effective_date,'MM')) < 4) then
1703 --
1704 if p_report_type = 'ITW' then
1705 --
1706 l_total_housing_loan := p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct;
1707 --
1708 -- following routine has been moved to proc_housing_loan1 to reorder sequence of item
1709 --
1710 -- -- always prompt should be printed (in accordance with guidance of national tax agency)
1711 -- l_description_kanji := g_prompt_kanji.housing_loan_itw;
1712 -- l_description_kana := g_prompt_kana.housing_loan_itw;
1713 ----
1714 -- if p_itax_yea_category = '0'
1715 -- and l_total_housing_loan <> 0
1716 -- and p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
1717 -- --
1718 -- l_description_kanji := l_description_kanji||to_char(l_total_housing_loan)||g_prompt_kanji.yen;
1719 -- l_description_kana := l_description_kana||to_char(l_total_housing_loan)||g_prompt_kana.yen;
1720 -- --
1721 -- else
1722 -- --
1723 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.yen;
1724 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.yen;
1725 -- --
1726 -- end if;
1727 --
1728 -- -- always prompt should be printed (in accordance with guidance of national tax agency)
1729 -- l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.housing_res_date_itw;
1730 -- l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.housing_res_date_itw;
1731 l_description_kanji := g_prompt_kanji.housing_res_date_itw;
1732 l_description_kana := g_prompt_kana.housing_res_date_itw;
1733 --
1734 if p_itax_yea_category = '0'
1735 and l_total_housing_loan <> 0 then
1736 --
1737 l_description_kanji := l_description_kanji||hr_jp_standard_pkg.to_jp_char(p_certificate_info.housing_residence_date,c_date_fmt_itw);
1738 l_description_kana := l_description_kana||hr_jp_standard_pkg.to_jp_char(p_certificate_info.housing_residence_date,c_date_fmt_itw);
1739 --
1740 end if;
1741 --
1742 add_description(
1743 HOUSING_LOAN_INFO,
1744 l_description_kanji,
1745 l_description_kana,
1746 p_descriptions);
1747 --
1748 end if;
1749 --
1750 end if;
1751 --
1752 end proc_housing_loan2;
1753 --
1754 procedure proc_np_prem
1755 is
1756 begin
1757 --
1758 if p_report_type = 'ITW' then
1759 --
1760 -- always prompt should be printed (in accordance with guidance of national tax agency)
1761 l_description_kanji := g_prompt_kanji.np_prem_itw;
1762 l_description_kana := g_prompt_kana.np_prem_itw;
1763 --
1764 if p_itax_yea_category = '0'
1765 and p_certificate_info.national_pens_prem <> 0 then
1766 --
1767 l_description_kanji := l_description_kanji||to_char(p_certificate_info.national_pens_prem)||g_prompt_kanji.yen;
1768 l_description_kana := l_description_kana||to_char(p_certificate_info.national_pens_prem)||g_prompt_kana.yen;
1769 --
1770 else
1771 --
1772 l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.yen;
1773 l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.yen;
1774 --
1775 end if;
1776 --
1777 -- use NP_PREM_CONC to concatenate with other description, rather than using line1
1778 add_description(
1779 NP_PREM_CONC,
1780 l_description_kanji,
1781 l_description_kana,
1782 p_descriptions);
1783 --
1784 else
1785 --
1786 -- take same behavior with previous
1787 if p_itax_yea_category = '0' then
1788 --
1789 -- use NP_PREM_CONC to concatenate with other description, rather than using line1
1790 add_desc(
1791 NP_PREM_CONC,
1792 g_prompt_kanji.national_pens_prem,
1793 g_prompt_kana.national_pens_prem,
1794 p_certificate_info.national_pens_prem,
1795 null,
1796 null);
1797 --
1798 end if;
1799 --
1800 end if;
1801 --
1802 end proc_np_prem;
1803 --
1804 procedure proc_prev_job
1805 is
1806 begin
1807 --
1808 if p_itax_yea_category = '0' then
1809 --
1810 for i in 1..p_prev_jobs.count loop
1811 --
1812 -- salary
1813 --
1814 l_description_kanji := g_prompt_kanji.prev_job||g_prompt_kanji.taxable_income||to_char(p_prev_jobs(i).taxable_income)||g_prompt_kanji.yen;
1815 l_description_kana := g_prompt_kana.prev_job||g_prompt_kana.taxable_income||to_char(p_prev_jobs(i).taxable_income)||g_prompt_kana.yen;
1816 --
1817 -- social insurance premium
1818 --
1819 l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.si_prem||to_char(p_prev_jobs(i).si_prem)||g_prompt_kanji.yen;
1820 l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.si_prem||to_char(p_prev_jobs(i).si_prem)||g_prompt_kana.yen;
1821 --
1822 -- small-enterprise mutual aid premium
1823 --
1824 if p_prev_jobs(i).mutual_aid_prem <> 0 then
1825 --
1826 l_description_kanji := l_description_kanji||'('||g_prompt_kanji.mutual_aid_prem||to_char(p_prev_jobs(i).mutual_aid_prem)||g_prompt_kanji.yen||')';
1827 l_description_kana := l_description_kana||'('||g_prompt_kana.mutual_aid_prem||to_char(p_prev_jobs(i).mutual_aid_prem)||g_prompt_kana.yen||')';
1828 --
1829 end if;
1830 --
1831 -- income tax
1832 --
1833 l_description_kanji := l_description_kanji||c_space_separator||g_prompt_kanji.itax||to_char(p_prev_jobs(i).itax)||g_prompt_kanji.yen;
1834 l_description_kana := l_description_kana||c_space_separator||g_prompt_kana.itax||to_char(p_prev_jobs(i).itax)||g_prompt_kana.yen;
1835 --
1836 -- salary payer address
1837 --
1838 if p_prev_jobs(i).salary_payer_address_kanji is not null then
1839 --
1840 l_description_kanji := l_description_kanji||c_space_separator||p_prev_jobs(i).salary_payer_address_kanji;
1841 --
1842 end if;
1843 --
1844 if p_prev_jobs(i).salary_payer_address_kana is not null then
1845 --
1846 l_description_kana := l_description_kana||c_space_separator||p_prev_jobs(i).salary_payer_address_kana;
1847 --
1848 end if;
1849 --
1850 -- salary payer name
1851 --
1852 if p_prev_jobs(i).salary_payer_name_kanji is not null then
1853 --
1854 l_description_kanji := l_description_kanji||c_space_separator||p_prev_jobs(i).salary_payer_name_kanji;
1855 --
1856 end if;
1857 --
1858 if p_prev_jobs(i).salary_payer_name_kana is not null then
1859 --
1860 l_description_kana := l_description_kana||c_space_separator||p_prev_jobs(i).salary_payer_name_kana;
1861 --
1862 end if;
1863 --
1864 -- termination date
1865 -- Now to_char(''Japanese Imperial'') does not work in PL/SQL.
1866 -- Here uses to_char of ORACLE as a workaround.
1867 --
1868 if p_prev_jobs(i).termination_date is not null then
1869 --
1870 l_description_kanji := l_description_kanji||c_space_separator||hr_jp_standard_pkg.to_jp_char(p_prev_jobs(i).termination_date,g_prompt_kanji.date_format)||g_prompt_kanji.terminated;
1871 l_description_kana := l_description_kana||c_space_separator||hr_jp_standard_pkg.to_jp_char(p_prev_jobs(i).termination_date,g_prompt_kana.date_format)||g_prompt_kana.terminated;
1872 --
1873 end if;
1874 --
1875 add_description(
1876 PREV_JOB,
1877 l_description_kanji,
1878 l_description_kana,
1879 p_descriptions);
1880 --
1881 end loop;
1882 --
1883 end if;
1884 --
1885 end proc_prev_job;
1886 --
1887 procedure proc_dependent
1888 is
1889 begin
1890 --
1891 --
1892 -- dependent
1893 --
1894 -- Omit last_name when different as follows.
1895 -- 1) null/null -> omit last_name
1896 -- 2) null/B -> omit last_name
1897 -- 3) A/null -> omit last_name
1898 -- 4) A/A -> omit last_name
1899 -- 5) A/B -> Never omit last_name
1900 --
1901 for i in 1..p_dpnts.count loop
1902 --
1903 if (p_dpnts(i).last_name_kana <> p_last_name_kana)
1904 or (p_dpnts(i).last_name_kanji <> p_last_name_kanji) then
1905 --
1906 l_description_kanji := p_dpnts(i).contact_type_kanji||c_space_separator||p_dpnts(i).last_name_kanji||p_dpnts(i).first_name_kanji;
1907 l_description_kana := p_dpnts(i).contact_type_kana||c_space_separator||p_dpnts(i).last_name_kana||p_dpnts(i).first_name_kana;
1908 --
1909 else
1910 --
1911 l_description_kanji := p_dpnts(i).contact_type_kanji||c_space_separator||p_dpnts(i).first_name_kanji;
1912 l_description_kana := p_dpnts(i).contact_type_kana||c_space_separator||p_dpnts(i).first_name_kana;
1913 --
1914 end if;
1915 --
1916 if to_number(to_char(p_effective_date,'YYYY')) >= 2011
1917 and nvl(p_dpnts(i).junior,'N') = 'Y' then
1918 --
1919 l_description_kanji := l_description_kanji||c_bracket_left||g_prompt_kanji.dpnt_junior||c_bracket_right;
1920 l_description_kana := l_description_kana||c_bracket_left||g_prompt_kana.dpnt_junior||c_bracket_right;
1921 --
1922 end if;
1923 --
1924 add_description(
1925 DEPENDENT,
1926 l_description_kanji,
1927 l_description_kana,
1928 p_descriptions);
1929 --
1930 end loop;
1931 --
1932 end proc_dependent;
1933 --
1934 procedure proc_disaster
1935 is
1936 begin
1937 --
1938 if p_itax_yea_category <> '0' then
1939 --
1940 -- disaster tax reduction
1941 --
1942 if p_certificate_info.tax_info.disaster_tax_reduction <> 0 then
1943 --
1944 l_description_kanji := g_prompt_kanji.disaster_tax_reduction||to_char(p_certificate_info.tax_info.disaster_tax_reduction)||g_prompt_kanji.yen;
1945 l_description_kana := g_prompt_kana.disaster_tax_reduction||to_char(p_certificate_info.tax_info.disaster_tax_reduction)||g_prompt_kana.yen;
1946 --
1947 add_description(
1948 DISASTER_TAX_REDUCTION,
1949 l_description_kanji,
1950 l_description_kana,
1951 p_descriptions);
1952 --
1953 end if;
1954 --
1955 end if;
1956 --
1957 end proc_disaster;
1958 --
1959 begin
1960 --
1961 hr_utility.set_location('Entering: ' || c_proc, 10);
1962 --
1963 if to_number(to_char(p_effective_date,'YYYY')) >= 2009 then
1964 --
1965 hr_utility.set_location(c_proc, 1009);
1966 --
1967 proc_housing_loan1;
1968 --
1969 hr_utility.set_location(c_proc, 2009);
1970 --
1971 proc_np_prem;
1972 --
1973 hr_utility.set_location(c_proc, 3009);
1974 --
1975 proc_housing_loan2;
1976 --
1977 hr_utility.set_location(c_proc, 4009);
1978 --
1979 proc_prev_job;
1980 --
1981 hr_utility.set_location(c_proc, 5009);
1982 --
1983 proc_dependent;
1984 --
1985 hr_utility.set_location(c_proc, 6009);
1986 --
1987 proc_disaster;
1988 --
1989 -- basically, pass following routine only before 2009, so not maintain since 2010
1990 else
1991 --
1992 hr_utility.set_location(c_proc, 1000);
1993 --
1994 -- ************************
1995 -- FIXED_RATE_TAX_REDUCTION
1996 -- NP_PREM
1997 -- ************************
1998 if p_itax_yea_category = '0' then
1999 --
2000 if p_report_type = ITW then
2001 --
2002 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
2003 add_fixed_rate_description(p_certificate_info.fixed_rate_tax_reduction, 6, 6);
2004 end if;
2005 --
2006 if to_number(to_char(p_effective_date, 'YYYY')) >= 2005 then
2007 add_np_prem_description(p_certificate_info.national_pens_prem, 4, 3);
2008 end if;
2009 --
2010 else
2011 --
2012 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
2013 add_fixed_rate_description(p_certificate_info.fixed_rate_tax_reduction);
2014 end if;
2015 --
2016 if to_number(to_char(p_effective_date, 'YYYY')) >= 2005 then
2017 add_np_prem_description(p_certificate_info.national_pens_prem);
2018 end if;
2019 --
2020 end if;
2021 --
2022 else
2023 --
2024 if p_report_type = ITW then
2025 --
2026 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
2027 add_fixed_rate_description(null, 6, 6);
2028 end if;
2029 --
2030 if to_number(to_char(p_effective_date, 'YYYY')) >= 2005 then
2031 add_np_prem_description(null, 4, 3);
2032 end if;
2033 --
2034 end if;
2035 --
2036 end if;
2037 --
2038 hr_utility.set_location(c_proc, 2000);
2039 --
2040 -- ************************
2041 -- DEPENDENT
2042 -- ************************
2043 -- Omit last_name when different as follows.
2044 -- 1) null/null -> omit last_name
2045 -- 2) null/B -> omit last_name
2046 -- 3) A/null -> omit last_name
2047 -- 4) A/A -> omit last_name
2048 -- 5) A/B -> Never omit last_name
2049 --
2050 for i in 1..p_dpnts.count loop
2051 --
2052 if (p_dpnts(i).last_name_kana <> p_last_name_kana)
2053 or (p_dpnts(i).last_name_kanji <> p_last_name_kanji) then
2054 --
2055 l_description_kanji := p_dpnts(i).contact_type_kanji ||
2056 ' ' ||
2057 p_dpnts(i).last_name_kanji ||
2058 p_dpnts(i).first_name_kanji;
2059 --
2060 l_description_kana := p_dpnts(i).contact_type_kana ||
2061 ' ' ||
2062 p_dpnts(i).last_name_kana ||
2063 p_dpnts(i).first_name_kana;
2064 --
2065 else
2066 --
2067 l_description_kanji := p_dpnts(i).contact_type_kanji ||
2068 ' ' ||
2069 p_dpnts(i).first_name_kanji;
2070 --
2071 l_description_kana := p_dpnts(i).contact_type_kana ||
2072 ' ' ||
2073 p_dpnts(i).first_name_kana;
2074 --
2075 end if;
2076 --
2077 add_description(
2078 DEPENDENT,
2079 l_description_kanji,
2080 l_description_kana,
2081 p_descriptions);
2082 --
2083 end loop;
2084 --
2085 hr_utility.set_location(c_proc, 3000);
2086 --
2087 -- The following information is available only for YEAed assact.
2088 --
2089 if p_itax_yea_category = '0' then
2090 --
2091 hr_utility.set_location(c_proc, 3100);
2092 --
2093 -- ************************
2094 -- PREV_JOB
2095 -- ************************
2096 for i in 1..p_prev_jobs.count loop
2097 --
2098 -- <Salary>
2099 --
2100 l_description_kanji := g_prompt_kanji.prev_job ||
2101 g_prompt_kanji.taxable_income ||
2102 to_char(p_prev_jobs(i).taxable_income) ||
2103 g_prompt_kanji.yen;
2104 --
2105 l_description_kana := g_prompt_kana.prev_job ||
2106 g_prompt_kana.taxable_income ||
2107 to_char(p_prev_jobs(i).taxable_income) ||
2108 g_prompt_kana.yen;
2109 --
2110 -- <Social Insurance Premium>
2111 --
2112 l_description_kanji := l_description_kanji || ' ' ||
2113 g_prompt_kanji.si_prem ||
2114 to_char(p_prev_jobs(i).si_prem) ||
2115 g_prompt_kanji.yen;
2116 --
2117 l_description_kana := l_description_kana || ' ' ||
2118 g_prompt_kana.si_prem ||
2119 to_char(p_prev_jobs(i).si_prem) ||
2120 g_prompt_kana.yen;
2121 --
2122 -- <Small-enterprise Mutual Aid Premium>
2123 --
2124 if p_prev_jobs(i).mutual_aid_prem <> 0 then
2125 --
2126 l_description_kanji := l_description_kanji || '(' ||
2127 g_prompt_kanji.mutual_aid_prem ||
2128 to_char(p_prev_jobs(i).mutual_aid_prem) ||
2129 g_prompt_kanji.yen || ')';
2130 --
2131 l_description_kana := l_description_kana || '(' ||
2132 g_prompt_kana.mutual_aid_prem ||
2133 to_char(p_prev_jobs(i).mutual_aid_prem) ||
2134 g_prompt_kana.yen || ')';
2135 --
2136 end if;
2137 --
2138 -- <Income Tax>
2139 --
2140 l_description_kanji := l_description_kanji || ' ' ||
2141 g_prompt_kanji.itax ||
2142 to_char(p_prev_jobs(i).itax) ||
2143 g_prompt_kanji.yen;
2144 --
2145 l_description_kana := l_description_kana || ' ' ||
2146 g_prompt_kana.itax ||
2147 to_char(p_prev_jobs(i).itax) ||
2148 g_prompt_kana.yen;
2149 --
2150 -- <Salary Payer Address>
2151 --
2152 if p_prev_jobs(i).salary_payer_address_kanji is not null then
2153 --
2154 l_description_kanji := l_description_kanji || ' ' ||
2155 p_prev_jobs(i).salary_payer_address_kanji;
2156 --
2157 end if;
2158 --
2159 if p_prev_jobs(i).salary_payer_address_kana is not null then
2160 --
2161 l_description_kana := l_description_kana || ' ' ||
2162 p_prev_jobs(i).salary_payer_address_kana;
2163 --
2164 end if;
2165 --
2166 -- <Salary Payer Name>
2167 --
2168 if p_prev_jobs(i).salary_payer_name_kanji is not null then
2169 --
2170 l_description_kanji := l_description_kanji || ' ' ||
2171 p_prev_jobs(i).salary_payer_name_kanji;
2172 --
2173 end if;
2174 --
2175 if p_prev_jobs(i).salary_payer_name_kana is not null then
2176 --
2177 l_description_kana := l_description_kana || ' ' ||
2178 p_prev_jobs(i).salary_payer_name_kana;
2179 --
2180 end if;
2181 --
2182 -- <Termination Date>
2183 -- Now to_char(''Japanese Imperial'') does not work in PL/SQL.
2184 -- Here uses to_char of ORACLE as a workaround.
2185 --
2186 if p_prev_jobs(i).termination_date is not null then
2187 --
2188 l_description_kanji := l_description_kanji || ' ' ||
2189 hr_jp_standard_pkg.to_jp_char(p_prev_jobs(i).termination_date, g_prompt_kanji.date_format) ||
2190 g_prompt_kanji.terminated;
2191 --
2192 l_description_kana := l_description_kana || ' ' ||
2193 hr_jp_standard_pkg.to_jp_char(p_prev_jobs(i).termination_date, g_prompt_kana.date_format) ||
2194 g_prompt_kana.terminated;
2195 --
2196 end if;
2197 --
2198 add_description(
2199 PREV_JOB,
2200 l_description_kanji,
2201 l_description_kana,
2202 p_descriptions);
2203 --
2204 end loop;
2205 --
2206 hr_utility.set_location(c_proc, 3200);
2207 --
2208 -- ************************
2209 -- HOUSING_TAX_REDUCTION
2210 -- ************************
2211 -- Date: 2007/06/07
2212 -- If statement condition changed to print when "Total" housing tax reduction <> "0"
2213 -- even "Net" housing tax reduction = "0".
2214 --
2215 if p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct <> 0
2216 and (p_certificate_info.housing_residence_date is not null or p_certificate_info.unclaimed_housing_tax_reduct <> 0) then
2217 --
2218 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
2219 --
2220 l_description_kanji := g_prompt_kanji.housing_tax_reduction || '(';
2221 l_description_kana := g_prompt_kana.housing_tax_reduction || '(';
2222 --
2223 else
2224 --
2225 l_description_kanji := g_prompt_kanji.housing_tax_reduction_long || '(';
2226 l_description_kana := g_prompt_kana.housing_tax_reduction_long || '(';
2227 --
2228 end if;
2229 --
2230 if p_certificate_info.housing_residence_date is not null then
2231 --
2232 l_description_kanji := l_description_kanji ||
2233 g_prompt_kanji.residence_date ||
2234 hr_jp_standard_pkg.to_jp_char(p_certificate_info.housing_residence_date, g_prompt_kanji.date_format);
2235 --
2236 l_description_kana := l_description_kana ||
2237 g_prompt_kana.residence_date ||
2238 hr_jp_standard_pkg.to_jp_char(p_certificate_info.housing_residence_date, g_prompt_kana.date_format);
2239 --
2240 l_separator := ' ';
2241 --
2242 end if;
2243 --
2244 if p_certificate_info.unclaimed_housing_tax_reduct <> 0 then
2245 --
2246 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
2247 --
2248 l_description_kanji := l_description_kanji || l_separator ||
2249 g_prompt_kanji.unclaimed_housing_tax_reduct ||
2250 to_char(p_certificate_info.unclaimed_housing_tax_reduct) ||
2251 g_prompt_kanji.yen;
2252 l_description_kana := l_description_kana || l_separator ||
2253 g_prompt_kana.unclaimed_housing_tax_reduct ||
2254 to_char(p_certificate_info.unclaimed_housing_tax_reduct) ||
2255 g_prompt_kana.yen;
2256 --
2257 elsif to_number(to_char(p_effective_date,'YYYY')) = 2008 then
2258 --
2259 if not (p_certificate_info.housing_residence_date is not null
2260 and p_certificate_info.housing_residence_date >= to_date('2007/01/01','YYYY/MM/DD')) then
2261 --
2262 l_description_kanji :=
2263 l_description_kanji || l_separator ||
2264 g_prompt_kanji.total_housing_tax_reduction ||
2265 to_char(p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct) ||
2266 g_prompt_kanji.yen;
2267 l_description_kana :=
2268 l_description_kana || l_separator ||
2269 g_prompt_kana.total_housing_tax_reduction ||
2270 to_char(p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct) ||
2271 g_prompt_kana.yen;
2272 --
2273 end if;
2274 --
2275 else
2276 --
2277 l_description_kanji :=
2278 l_description_kanji || l_separator ||
2279 g_prompt_kanji.total_housing_tax_reduction ||
2280 to_char(p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct) ||
2281 g_prompt_kanji.yen;
2282 l_description_kana :=
2283 l_description_kana || l_separator ||
2284 g_prompt_kana.total_housing_tax_reduction ||
2285 to_char(p_certificate_info.housing_tax_reduction + p_certificate_info.unclaimed_housing_tax_reduct) ||
2286 g_prompt_kana.yen;
2287 --
2288 end if;
2289 --
2290 end if;
2291 --
2292 l_description_kanji := l_description_kanji || ')';
2293 l_description_kana := l_description_kana || ')';
2294 --
2295 add_description(
2296 HOUSING_TAX_REDUCTION,
2297 l_description_kanji,
2298 l_description_kana,
2299 p_descriptions);
2300 --
2301 end if;
2302 --
2303 else
2304 --
2305 hr_utility.set_location(c_proc, 3300);
2306 --
2307 -- ************************
2308 -- DISASTER_TAX_REDUCTION
2309 -- ************************
2310 --
2311 if p_certificate_info.tax_info.disaster_tax_reduction <> 0 then
2312 --
2313 l_description_kanji := g_prompt_kanji.disaster_tax_reduction ||
2314 to_char(p_certificate_info.tax_info.disaster_tax_reduction) ||
2315 g_prompt_kanji.yen;
2316 --
2317 l_description_kana := g_prompt_kana.disaster_tax_reduction ||
2318 to_char(p_certificate_info.tax_info.disaster_tax_reduction) ||
2319 g_prompt_kana.yen;
2320 --
2321 add_description(
2322 DISASTER_TAX_REDUCTION,
2323 l_description_kanji,
2324 l_description_kana,
2325 p_descriptions);
2326 --
2327 end if;
2328 --
2329 end if;
2330 --
2331 end if;
2332 --
2333 hr_utility.set_location('Leaving: ' || c_proc, 10000);
2334 --
2335 end get_descriptions;
2336 --
2337 -- Wrapper function for existing user program (for previous behavior)
2338 --
2339 procedure get_descriptions(
2340 p_assignment_id in number,
2341 p_person_id in number,
2342 p_effective_date in date,
2343 p_itax_yea_category in varchar2,
2344 p_certificate_info in t_certificate_info,
2345 p_last_name_kanji in varchar2,
2346 p_last_name_kana in varchar2,
2347 p_dpnts in t_dpnts,
2348 p_prev_jobs in t_prev_jobs,
2349 p_report_type in varchar2, --> ITW/WTM
2350 p_descriptions out nocopy t_descriptions)
2351 is
2352 --
2353 l_housing_tbl t_housing_tbl;
2354 --
2355 begin
2356 --
2357 get_descriptions(
2358 p_assignment_id => p_assignment_id,
2359 p_person_id => p_person_id,
2360 p_effective_date => p_effective_date,
2361 p_itax_yea_category => p_itax_yea_category,
2362 p_certificate_info => p_certificate_info,
2363 p_last_name_kanji => p_last_name_kanji,
2364 p_last_name_kana => p_last_name_kana,
2365 p_dpnts => p_dpnts,
2366 p_prev_jobs => p_prev_jobs,
2367 p_housing_tbl => l_housing_tbl,
2368 p_report_type => p_report_type,
2369 p_descriptions => p_descriptions);
2370 --
2371 end get_descriptions;
2372 --
2373 -- |-------------------------------------------------------------------|
2374 -- |---------------------< get_certificate_info >----------------------|
2375 -- |-------------------------------------------------------------------|
2376 -- Wrapper function for ITT
2377 procedure get_certificate_info(
2378 p_assignment_action_id in number,
2379 p_assignment_id in number,
2380 p_action_sequence in number,
2381 p_effective_date in date,
2382 p_itax_organization_id in number,
2383 p_itax_category in varchar2,
2384 p_itax_yea_category in varchar2,
2385 p_employment_category in varchar2,
2386 p_person_id in number,
2387 p_business_group_id in number,
2388 p_date_earned in date,
2389 p_certificate_info out nocopy t_tax_info,
2390 p_submission_required_flag out nocopy varchar2,
2391 p_withholding_tax_info out nocopy t_tax_info)
2392 is
2393 c_proc constant varchar2(61) := c_package || 'get_certificate_info (ITT)';
2394 begin
2395 hr_utility.set_location('Entering: ' || c_proc, 10);
2396 --
2397 get_basic_certificate_info(
2398 p_assignment_action_id => p_assignment_action_id,
2399 p_assignment_id => p_assignment_id,
2400 p_action_sequence => p_action_sequence,
2401 p_effective_date => p_effective_date,
2402 p_itax_organization_id => p_itax_organization_id,
2403 p_itax_category => p_itax_category,
2404 p_itax_yea_category => p_itax_yea_category,
2405 p_employment_category => p_employment_category,
2406 p_certificate_info => p_certificate_info,
2407 p_submission_required_flag => p_submission_required_flag);
2408 --
2409 hr_utility.set_location(c_proc, 20);
2410 --
2411 if p_itax_yea_category = '0' then
2412 hr_utility.set_location(c_proc, 21);
2413 --
2414 --
2415 -- Derive withholding tax information by current employer.
2416 --
2417 get_withholding_tax_info(
2418 p_assignment_action_id => p_assignment_action_id,
2419 p_assignment_id => p_assignment_id,
2420 p_action_sequence => p_action_sequence,
2421 p_effective_date => p_effective_date,
2422 p_itax_organization_id => p_itax_organization_id,
2423 p_itax_category => p_itax_category,
2424 p_itax_yea_category => p_itax_yea_category,
2425 p_withholding_tax_info => p_withholding_tax_info);
2426 else
2427 hr_utility.set_location(c_proc, 22);
2428 --
2429 --
2430 -- For non-YEAed employees, current employer's payment with same tax category
2431 -- are shown in certificates. So just copy p_certificate_info to p_withholding_tax_info.
2432 --
2433 p_withholding_tax_info := p_certificate_info;
2434 end if;
2435 --
2436 hr_utility.set_location('Leaving: ' || c_proc, 100);
2437 end get_certificate_info;
2438 -- |-------------------------------------------------------------------|
2439 -- |---------------------< get_basic_certificate_info >----------------|
2440 -- |-------------------------------------------------------------------|
2441 --
2442 -- Source function for ITW, WTM, the wrapper for Archive (called from gci_b, gci_itw_arc : gci_s)
2443 --
2444 procedure get_basic_certificate_info(
2445 p_assignment_action_id in number,
2446 p_assignment_id in number,
2447 p_action_sequence in number,
2448 p_business_group_id in number,
2449 p_effective_date in date,
2450 p_date_earned in date,
2451 p_itax_organization_id in number,
2452 p_itax_category in varchar2,
2453 p_itax_yea_category in varchar2,
2454 p_dpnt_ref_type in varchar2,
2455 p_dpnt_effective_date in date,
2456 p_person_id in number,
2457 p_sex in varchar2,
2458 p_date_of_birth in date,
2459 p_leaving_reason in varchar2,
2460 p_employment_category in varchar2,
2461 p_certificate_info out nocopy t_certificate_info,
2462 p_submission_required_flag out nocopy varchar2,
2463 p_prev_jobs out nocopy t_prev_jobs,
2464 p_housing_tbl out nocopy t_housing_tbl)
2465 is
2466 --
2467 c_proc constant varchar2(61) := c_package || 'get_basic_certificate_info (2)';
2468 --
2469 l_dpnt_rec per_jp_ctr_utility_pkg.t_itax_dpnt_rec;
2470 l_disabled_type varchar2(30);
2471 l_aged_type varchar2(30);
2472 l_widow_type varchar2(30);
2473 l_working_student_type varchar2(30);
2474 l_spouse_type varchar2(30);
2475 l_spouse_disabled_type varchar2(30);
2476 l_num_deps number;
2477 l_num_ageds number;
2478 l_num_aged_parents_lt number;
2479 l_num_specifieds number;
2480 l_num_junior_deps number;
2481 l_num_disableds number;
2482 l_num_svr_disableds number;
2483 l_num_svr_disableds_lt number;
2484 l_dummy varchar2(1);
2485 l_dummy_number number;
2486 l_dummy_new_life number;
2487 l_dummy_old_life number;
2488 l_dummy_new_pension number;
2489 l_dummy_care_med number;
2490 --
2491 cursor csr_spouse is
2492 select 'Y'
2493 from per_contact_relationships ctr
2494 where ctr.person_id = p_person_id
2495 and ctr.contact_type = 'S'
2496 and p_effective_date
2497 between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date);
2498 --
2499 begin
2500 --
2501 hr_utility.set_location('Entering: ' || c_proc, 10);
2502 --
2503 get_basic_certificate_info(
2504 p_assignment_action_id => p_assignment_action_id,
2505 p_assignment_id => p_assignment_id,
2506 p_action_sequence => p_action_sequence,
2507 p_effective_date => p_effective_date,
2508 p_itax_organization_id => p_itax_organization_id,
2509 p_itax_category => p_itax_category,
2510 p_itax_yea_category => p_itax_yea_category,
2511 p_employment_category => p_employment_category,
2512 p_certificate_info => p_certificate_info.tax_info,
2513 p_submission_required_flag => p_submission_required_flag);
2514 --
2515 hr_utility.set_location(c_proc, 20);
2516 --
2517 --
2518 -- The following information is specific only for YEAed assignments.
2519 --
2520 if p_itax_yea_category = '0' then
2521 --
2522 hr_utility.set_location(c_proc, 21);
2523 --
2524 p_certificate_info.net_taxable_income
2525 := 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);
2526 --
2527 p_certificate_info.spouse_sp_exempt
2528 := 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);
2529 --
2530 if p_certificate_info.spouse_sp_exempt <> 0 then
2531 --
2532 p_certificate_info.spouse_net_taxable_income
2533 := 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);
2534 --
2535 end if;
2536 --
2537 -- Date: 2007/06/07
2538 -- "Personal Pension Premium" should be printed only when "Personal Pension Premium" <> 0.
2539 --
2540 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);
2541 --
2542 if p_certificate_info.li_prem_exempt <> 0 then
2543 --
2544 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);
2545 --
2546 if l_dummy_number <> 0 then
2547 --
2548 p_certificate_info.pp_prem := l_dummy_number;
2549 --
2550 end if;
2551 -- Date: 2012/09/26 2012 Withholding Changes
2552 -- "Old Life Insurance Premium"
2553 l_dummy_old_life := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm.element_type_id,g_ins_prem_exempt_elm.old_life_ins_prem_iv,p_assignment_action_id),0);
2554 l_dummy_new_life := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm_new.element_type_id,g_ins_prem_exempt_elm_new.new_life_ins_prem_iv,p_assignment_action_id),0);
2555 l_dummy_new_pension := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm_new.element_type_id,g_ins_prem_exempt_elm_new.new_pen_ins_prem_iv,p_assignment_action_id),0);
2556 l_dummy_care_med := nvl(pay_jp_balance_pkg.get_result_value_number(g_ins_prem_exempt_elm_new.element_type_id,g_ins_prem_exempt_elm_new.care_med_ins_prem_iv,p_assignment_action_id),0);
2557
2558 if l_dummy_old_life <> 0 then
2559 p_certificate_info.old_life_ins_prem := l_dummy_old_life;
2560 end if;
2561 if l_dummy_new_life <> 0 then
2562 p_certificate_info.new_life_ins_prem := l_dummy_new_life;
2563 end if;
2564 if l_dummy_new_pension <> 0 then
2565 p_certificate_info.new_pen_ins_prem := l_dummy_new_pension;
2566 end if;
2567 if l_dummy_care_med <> 0 then
2568 p_certificate_info.care_med_ins_prem := l_dummy_care_med;
2569 end if;
2570 --
2571 end if;
2572 --
2573 -- Date: 2007/06/07
2574 -- "Long-term Accident Insurance Premium" should be printed only when "Long-term Accident Insurance Premium" <> 0.
2575 --
2576 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);
2577 --
2578 if p_certificate_info.ai_prem_exempt <> 0 then
2579 --
2580 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);
2581 --
2582 if l_dummy_number <> 0 then
2583 --
2584 p_certificate_info.long_ai_prem := l_dummy_number;
2585 --
2586 end if;
2587 --
2588 end if;
2589 --
2590 p_certificate_info.total_income_exempt := pay_jp_balance_pkg.get_balance_value(g_total_income_exempt_asg_run, p_assignment_action_id);
2591 --
2592 -- Date: 2007/06/07
2593 -- There's possibility when "Housing Tax Reduction" > 0, but "Net Housing Tax Reduction" = 0.
2594 -- This occurs when annual tax is zero. In this case, "Housing Tax Reduction Information" should
2595 -- be printed on "Description" field.
2596 --
2597 p_certificate_info.housing_tax_reduction
2598 := 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);
2599 --
2600 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);
2601 --
2602 if l_dummy_number <> 0 then
2603 --
2604 if to_number(to_char(p_effective_date,'YYYY')) < 2009
2605 or (to_number(to_char(p_effective_date,'YYYY')) = 2009
2606 and to_number(to_char(p_effective_date,'MM')) < 4) then
2607 --
2608 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);
2609 --
2610 end if;
2611 --
2612 p_certificate_info.unclaimed_housing_tax_reduct := l_dummy_number - p_certificate_info.housing_tax_reduction;
2613 --
2614 end if;
2615 --
2616 if to_number(to_char(p_effective_date, 'YYYY')) <= 2006 then
2617 --
2618 p_certificate_info.fixed_rate_tax_reduction
2619 := 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);
2620 --
2621 else
2622 --
2623 p_certificate_info.fixed_rate_tax_reduction := 0;
2624 --
2625 end if;
2626 --
2627 if to_number(to_char(p_effective_date, 'YYYY')) >= 2005 then
2628 --
2629 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);
2630 --
2631 else
2632 --
2633 p_certificate_info.national_pens_prem := 0;
2634 --
2635 end if;
2636 --
2637 if to_number(to_char(p_effective_date,'YYYY')) > 2009
2638 or (to_number(to_char(p_effective_date,'YYYY')) = 2009
2639 and to_number(to_char(p_effective_date,'MM')) >= 4) then
2640 --
2641 -- should get p_housing_tbl in only following case
2642 --
2643 -- from performance reason, not get for all to check if entry exists
2644 -- actually desired to accept in case total_housing_loan (dummy_number) is 0 if residence date is set.
2645 --
2646 if p_itax_yea_category = '0'
2647 and l_dummy_number <> 0 then
2648 --
2649 get_housing_tbl(
2650 p_assignment_id => p_assignment_id,
2651 p_date_earned => p_date_earned,
2652 p_housing_tbl => p_housing_tbl);
2653 --
2654 end if;
2655 --
2656 end if;
2657 --
2658 hr_utility.set_location(c_proc, 22);
2659 --
2660 get_prev_jobs(
2661 p_assignment_id => p_assignment_id,
2662 p_action_sequence => p_action_sequence,
2663 p_business_group_id => p_business_group_id,
2664 p_effective_date => p_effective_date,
2665 p_date_earned => p_date_earned,
2666 p_itax_organization_id => p_itax_organization_id,
2667 p_person_id => p_person_id,
2668 p_prev_jobs => p_prev_jobs);
2669 --
2670 end if;
2671 --
2672 hr_utility.set_location(c_proc, 30);
2673 --
2674 -- Dependents Information
2675 --
2676 if p_itax_category in ('M_KOU', 'M_OTSU', 'D_KOU', 'D_OTSU') then
2677 --
2678 if p_itax_yea_category = '0' then
2679 --
2680 hr_utility.set_location(c_proc, 31);
2681 --
2682 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');
2683 --
2684 if to_number(to_char(p_effective_date,'YYYY')) < 2005 then
2685 --
2686 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');
2687 --
2688 end if;
2689 --
2690 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');
2691 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');
2692 --
2693 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');
2694 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');
2695 --
2696 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);
2697 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);
2698 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);
2699 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);
2700 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);
2701 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);
2702 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);
2703 --
2704 else
2705 --
2706 hr_utility.set_location(c_proc, 32);
2707 --
2708 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');
2709 --
2710 if to_number(to_char(p_effective_date,'YYYY')) < 2005 then
2711 --
2712 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');
2713 --
2714 end if;
2715 --
2716 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');
2717 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');
2718 --
2719 l_spouse_type := pay_jp_balance_pkg.get_entry_value_char(g_dep_exempt_elm.spouse_type_iv,p_assignment_id,p_date_earned);
2720 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);
2721 --
2722 l_num_deps := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_deps_iv,p_assignment_id,p_date_earned);
2723 l_num_ageds := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_ageds_iv,p_assignment_id,p_date_earned);
2724 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);
2725 l_num_specifieds := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_specifieds_iv,p_assignment_id,p_date_earned);
2726 l_num_disableds := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_disableds_iv,p_assignment_id,p_date_earned);
2727 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);
2728 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);
2729 --
2730 -- Derive Dependent Information based on Income Tax Dependent Control Method.
2731 -- This flag is set at "Payroll Developer DF" or "Org Developer DF"(Business Group level).
2732 -- The value set in "Payroll Developer DF" has higher priority than "Org Developer DF".
2733 -- Here does not do the cross validation for each dependent deduction like FastFormula.
2734 -- This dynamic deriving routine is available when YEA is not processed.
2735 --
2736 if p_dpnt_ref_type = 'CEI' then
2737 --
2738 hr_utility.set_location(c_proc, 33);
2739 --
2740 if l_spouse_type is null
2741 or l_spouse_disabled_type is null
2742 or l_num_deps is null
2743 or l_num_ageds is null
2744 or l_num_aged_parents_lt is null
2745 or l_num_specifieds is null
2746 or l_num_disableds is null
2747 or l_num_svr_disableds is null
2748 or l_num_svr_disableds_lt is null then
2749 --
2750 per_jp_ctr_utility_pkg.get_itax_dpnt_info(
2751 p_assignment_id => p_assignment_id,
2752 p_itax_type => p_itax_category,
2753 p_effective_date => p_dpnt_effective_date,
2754 p_itax_dpnt_rec => l_dpnt_rec);
2755 --
2756 l_spouse_type := nvl(l_spouse_type,l_dpnt_rec.spouse_type);
2757 l_spouse_disabled_type := nvl(l_spouse_disabled_type,l_dpnt_rec.dpnt_spouse_dsbl_type);
2758 l_num_deps := nvl(l_num_deps,l_dpnt_rec.dpnts);
2759 l_num_ageds := nvl(l_num_ageds,l_dpnt_rec.aged_dpnts);
2760 l_num_aged_parents_lt := nvl(l_num_aged_parents_lt,l_dpnt_rec.aged_dpnt_parents_lt);
2761 l_num_specifieds := nvl(l_num_specifieds,l_dpnt_rec.young_dpnts);
2762 l_num_disableds := nvl(l_num_disableds,l_dpnt_rec.dsbl_dpnts);
2763 l_num_svr_disableds := nvl(l_num_svr_disableds,l_dpnt_rec.svr_dsbl_dpnts);
2764 l_num_svr_disableds_lt := nvl(l_num_svr_disableds_lt,l_dpnt_rec.svr_dsbl_dpnts_lt);
2765 --
2766 end if;
2767 --
2768 else
2769 --
2770 hr_utility.set_location(c_proc, 34);
2771 --
2772 l_spouse_type := nvl(l_spouse_type,'0');
2773 l_spouse_disabled_type := nvl(l_spouse_disabled_type,'0');
2774 l_num_deps := nvl(l_num_deps,0);
2775 l_num_ageds := nvl(l_num_ageds,0);
2776 l_num_aged_parents_lt := nvl(l_num_aged_parents_lt,0);
2777 l_num_specifieds := nvl(l_num_specifieds,0);
2778 l_num_disableds := nvl(l_num_disableds,0);
2779 l_num_svr_disableds := nvl(l_num_svr_disableds,0);
2780 l_num_svr_disableds_lt := nvl(l_num_svr_disableds_lt,0);
2781 --
2782 end if;
2783 --
2784 end if;
2785 --
2786 if to_number(to_char(p_effective_date,'YYYY')) >= 2011 then
2787 --
2788 l_num_junior_deps := pay_jp_balance_pkg.get_entry_value_number(g_dep_exempt_elm.num_junior_deps_iv,p_assignment_id,p_date_earned);
2789 --
2790 if p_dpnt_ref_type = 'CEI' then
2791 --
2792 if l_num_junior_deps is null then
2793 --
2794 if l_dpnt_rec.assignment_id is null
2795 or l_dpnt_rec.assignment_id <> p_assignment_id then
2796 --
2797 per_jp_ctr_utility_pkg.get_itax_dpnt_info(
2798 p_assignment_id => p_assignment_id,
2799 p_itax_type => p_itax_category,
2800 p_effective_date => p_dpnt_effective_date,
2801 p_itax_dpnt_rec => l_dpnt_rec);
2802 --
2803 end if;
2804 --
2805 l_num_junior_deps := nvl(l_num_junior_deps,l_dpnt_rec.minor_dpnts);
2806 --
2807 end if;
2808 --
2809 else
2810 --
2811 l_num_junior_deps := nvl(l_num_junior_deps,0);
2812 --
2813 end if;
2814 --
2815 end if;
2816 --
2817 hr_utility.set_location(c_proc, 34);
2818 --
2819 if p_itax_category in ('M_KOU','D_KOU') then
2820 --
2821 hr_utility.set_location(c_proc, 35);
2822 --
2823 if l_spouse_type = '2' then
2824 --
2825 p_certificate_info.dep_spouse_exists_kou := 'Y';
2826 --
2827 elsif l_spouse_type = '3' then
2828 --
2829 p_certificate_info.dep_spouse_exists_kou := 'Y';
2830 p_certificate_info.aged_spouse_exists := 'Y';
2831 --
2832 else
2833 --
2834 p_certificate_info.dep_spouse_not_exist_kou := 'Y';
2835 --
2836 end if;
2837 --
2838 p_certificate_info.num_specifieds_kou := l_num_specifieds;
2839 p_certificate_info.num_aged_parents_lt := l_num_aged_parents_lt;
2840 p_certificate_info.num_ageds_kou := l_num_aged_parents_lt + l_num_ageds;
2841 p_certificate_info.num_deps_kou := greatest(l_num_deps - (l_num_specifieds + l_num_aged_parents_lt + l_num_ageds), 0);
2842 --
2843 p_certificate_info.num_svr_disableds_lt := l_num_svr_disableds_lt;
2844 p_certificate_info.num_svr_disableds := l_num_svr_disableds;
2845 p_certificate_info.num_disableds := l_num_disableds;
2846 --
2847 if l_spouse_disabled_type = '1' then
2848 --
2849 p_certificate_info.num_disableds := p_certificate_info.num_disableds + 1;
2850 --
2851 elsif l_spouse_disabled_type = '2' then
2852 --
2853 p_certificate_info.num_svr_disableds := p_certificate_info.num_svr_disableds + 1;
2854 --
2855 elsif l_spouse_disabled_type = '3' then
2856 --
2857 p_certificate_info.num_svr_disableds_lt := p_certificate_info.num_svr_disableds_lt + 1;
2858 --
2859 end if;
2860 --
2861 p_certificate_info.num_svr_disableds := p_certificate_info.num_svr_disableds + p_certificate_info.num_svr_disableds_lt;
2862 --
2863 p_certificate_info.num_specifieds_kou := decode_value(p_certificate_info.num_specifieds_kou > 0, p_certificate_info.num_specifieds_kou);
2864 p_certificate_info.num_aged_parents_lt := decode_value(p_certificate_info.num_aged_parents_lt > 0, p_certificate_info.num_aged_parents_lt);
2865 p_certificate_info.num_ageds_kou := decode_value(p_certificate_info.num_ageds_kou > 0, p_certificate_info.num_ageds_kou);
2866 p_certificate_info.num_deps_kou := decode_value(p_certificate_info.num_deps_kou > 0, p_certificate_info.num_deps_kou );
2867 p_certificate_info.num_svr_disableds_lt := decode_value(p_certificate_info.num_svr_disableds_lt > 0, p_certificate_info.num_svr_disableds_lt);
2868 p_certificate_info.num_svr_disableds := decode_value(p_certificate_info.num_svr_disableds > 0, p_certificate_info.num_svr_disableds);
2869 p_certificate_info.num_disableds := decode_value(p_certificate_info.num_disableds > 0, p_certificate_info.num_disableds);
2870 --
2871 if l_disabled_type = '2' then
2872 --
2873 p_certificate_info.svr_disabled_flag := 'Y';
2874 --
2875 elsif l_disabled_type = '1' then
2876 --
2877 p_certificate_info.disabled_flag := 'Y';
2878 --
2879 end if;
2880 --
2881 if to_number(to_char(p_effective_date,'YYYY')) < 2005 then
2882 --
2883 if l_aged_type = '1' then
2884 --
2885 p_certificate_info.aged_flag := 'Y';
2886 --
2887 end if;
2888 --
2889 end if;
2890 --
2891 if l_widow_type = '2' then
2892 --
2893 if p_sex = 'F' then
2894 --
2895 p_certificate_info.sp_widow_flag := 'Y';
2896 --
2897 else
2898 --
2899 p_certificate_info.widower_flag := 'Y';
2900 --
2901 end if;
2902 --
2903 elsif l_widow_type = '1' then
2904 --
2905 if p_sex = 'F' then
2906 --
2907 p_certificate_info.widow_flag := 'Y';
2908 --
2909 else
2910 --
2911 p_certificate_info.widower_flag := 'Y';
2912 --
2913 end if;
2914 --
2915 end if;
2916 --
2917 if l_working_student_type = '1' then
2918 --
2919 p_certificate_info.working_student_flag := 'Y';
2920 --
2921 end if;
2922 --
2923 elsif p_itax_category in ('M_OTSU','D_OTSU') then
2924 --
2925 hr_utility.set_location(c_proc, 36);
2926 --
2927 if l_spouse_type = '2' then
2928 --
2929 p_certificate_info.dep_spouse_exists_otsu := 'Y';
2930 --
2931 elsif l_spouse_type = '3' then
2932 --
2933 p_certificate_info.dep_spouse_exists_otsu := 'Y';
2934 p_certificate_info.aged_spouse_exists := 'Y';
2935 --
2936 else
2937 p_certificate_info.dep_spouse_not_exist_otsu := 'Y';
2938 --
2939 end if;
2940 --
2941 p_certificate_info.num_specifieds_otsu := l_num_specifieds;
2942 p_certificate_info.num_ageds_otsu := l_num_aged_parents_lt + l_num_ageds;
2943 p_certificate_info.num_deps_otsu := greatest(l_num_deps - (l_num_specifieds + l_num_aged_parents_lt + l_num_ageds), 0);
2944 --
2945 p_certificate_info.num_specifieds_otsu := decode_value(p_certificate_info.num_specifieds_otsu > 0, p_certificate_info.num_specifieds_otsu);
2946 p_certificate_info.num_ageds_otsu := decode_value(p_certificate_info.num_ageds_otsu > 0, p_certificate_info.num_ageds_otsu);
2947 p_certificate_info.num_deps_otsu := decode_value(p_certificate_info.num_deps_otsu > 0, p_certificate_info.num_deps_otsu);
2948 --
2949 end if;
2950 --
2951 p_certificate_info.num_junior_deps := decode_value(nvl(l_num_junior_deps,0) > 0, l_num_junior_deps);
2952 --
2953 end if;
2954 --
2955 hr_utility.set_location(c_proc, 40);
2956 --
2957 --
2958 -- Husband Exists
2959 --
2960 --
2961 if to_number(to_char(p_effective_date,'YYYY')) < 2005 then
2962 --
2963 if p_sex = 'F' then
2964 --
2965 open csr_spouse;
2966 fetch csr_spouse into l_dummy;
2967 if csr_spouse%found then
2968 p_certificate_info.husband_exists := 'Y';
2969 end if;
2970 close csr_spouse;
2971 --
2972 end if;
2973 --
2974 end if;
2975 --
2976 -- Minor is free from Local Tax.
2977 -- The age used here needs to be calculated as of Jan 1st in the next calendar year.
2978 -- If an employee is under 20 years old as of Jan 1st, he/she is "Minor".
2979 -- The way how to calculate age in Japanese law is slightly different from normal way,
2980 -- an employee's age is incremented on the previous day of the date of birth.
2981 -- As a result, we need to calculate age as of "Jan 1st + 1" = "Jan 2nd".
2982 -- Note "Aged" is calculated as of "End of Calendar Year" which is different from this case.
2983 --
2984 if floor(months_between(add_months(trunc(p_effective_date, 'YYYY'), 12) + 1, p_date_of_birth) / 12) < 20 then
2985 --
2986 p_certificate_info.minor_flag := 'Y';
2987 --
2988 end if;
2989 --
2990 --
2991 -- Otsu
2992 --
2993 --
2994 if p_itax_category in ('M_OTSU', 'D_OTSU') then
2995 --
2996 p_certificate_info.otsu_flag := 'Y';
2997 --
2998 end if;
2999 --
3000 --
3001 -- Deceased Termination
3002 --
3003 --
3004 if p_leaving_reason like 'D%' then
3005 --
3006 p_certificate_info.deceased_termination_flag := 'Y';
3007 --
3008 end if;
3009 --
3010 --
3011 -- Disastered
3012 --
3013 --
3014 if p_certificate_info.tax_info.disaster_tax_reduction <> 0 then
3015 --
3016 p_certificate_info.disastered_flag := 'Y';
3017 --
3018 end if;
3019 --
3020 --
3021 -- Foreigner
3022 --
3023 --
3024 if pay_jp_balance_pkg.get_entry_value_char(g_itax_info_elm.foreigner_flag_iv, p_assignment_id, p_date_earned) = 'Y' then
3025 --
3026 p_certificate_info.foreigner_flag := 'Y';
3027 --
3028 end if;
3029 --
3030 hr_utility.set_location('Leaving: ' || c_proc, 100);
3031 --
3032 end get_basic_certificate_info;
3033 --
3034 -- |-------------------------------------------------------------------|
3035 -- |---------------------< get_certificate_info >----------------------|
3036 -- |-------------------------------------------------------------------|
3037 --
3038 -- Wrapper function for the wrapper function for WTM, ITW (called from gci_wtm, gci_itw_b : gci_b)
3039 --
3040 procedure get_certificate_info(
3041 p_assignment_action_id in number,
3042 p_assignment_id in number,
3043 p_action_sequence in number,
3044 p_business_group_id in number,
3045 p_effective_date in date,
3046 p_date_earned in date,
3047 p_itax_organization_id in number,
3048 p_itax_category in varchar2,
3049 p_itax_yea_category in varchar2,
3050 p_dpnt_ref_type in varchar2,
3051 p_dpnt_effective_date in date,
3052 p_person_id in number,
3053 p_sex in varchar2,
3054 p_date_of_birth in date,
3055 p_leaving_reason in varchar2,
3056 p_last_name_kanji in varchar2,
3057 p_last_name_kana in varchar2,
3058 p_employment_category in varchar2,
3059 p_magnetic_media_flag in varchar2 default 'N',
3060 p_certificate_info out nocopy t_certificate_info,
3061 p_submission_required_flag out nocopy varchar2,
3062 p_prev_jobs out nocopy t_prev_jobs,
3063 p_housing_tbl out nocopy t_housing_tbl)
3064 is
3065 --
3066 c_proc constant varchar2(61) := c_package || 'get_certificate_info (WTM)';
3067 --
3068 l_itw_override_flag varchar2(1);
3069 l_itw_description varchar2(300);
3070 l_wtm_override_flag varchar2(1);
3071 l_wtm_description varchar2(300);
3072 l_override_flag varchar2(1);
3073 l_description varchar2(300);
3074 l_report_type varchar2(3);
3075 --
3076 l_dpnts t_dpnts;
3077 l_descriptions t_descriptions;
3078 --
3079 procedure concat_description(
3080 p_description in varchar2,
3081 p_concat_description in out nocopy varchar2,
3082 p_separator in varchar2)
3083 is
3084 begin
3085 --
3086 if p_description is not null then
3087 --
3088 if p_concat_description is not null then
3089 --
3090 p_concat_description := p_concat_description || p_separator || p_description;
3091 --
3092 else
3093 --
3094 p_concat_description := p_description;
3095 --
3096 end if;
3097 --
3098 end if;
3099 --
3100 end concat_description;
3101 --
3102 begin
3103 --
3104 hr_utility.set_location('Entering: ' || c_proc, 10);
3105 --
3106 get_basic_certificate_info(
3107 p_assignment_action_id => p_assignment_action_id,
3108 p_assignment_id => p_assignment_id,
3109 p_action_sequence => p_action_sequence,
3110 p_business_group_id => p_business_group_id,
3111 p_effective_date => p_effective_date,
3112 p_date_earned => p_date_earned,
3113 p_itax_organization_id => p_itax_organization_id,
3114 p_itax_category => p_itax_category,
3115 p_itax_yea_category => p_itax_yea_category,
3116 p_dpnt_ref_type => p_dpnt_ref_type,
3117 p_dpnt_effective_date => p_dpnt_effective_date,
3118 p_person_id => p_person_id,
3119 p_sex => p_sex,
3120 p_date_of_birth => p_date_of_birth,
3121 p_leaving_reason => p_leaving_reason,
3122 p_employment_category => p_employment_category,
3123 p_certificate_info => p_certificate_info,
3124 p_submission_required_flag => p_submission_required_flag,
3125 p_prev_jobs => p_prev_jobs,
3126 p_housing_tbl => p_housing_tbl);
3127 --
3128 hr_utility.set_location(c_proc, 20);
3129 --
3130 -- Get Description information
3131 -- When description on element entry is marked as "Override",
3132 -- subsequent description will be replaced by this.
3133 --
3134 get_ee_description(
3135 p_assignment_id => p_assignment_id,
3136 p_business_group_id => p_business_group_id,
3137 p_effective_date => p_date_earned,
3138 p_itw_override_flag => l_itw_override_flag,
3139 p_itw_description => l_itw_description,
3140 p_wtm_override_flag => l_wtm_override_flag,
3141 p_wtm_description => l_wtm_description);
3142 --
3143 hr_utility.set_location(c_proc, 30);
3144 --
3145 if p_magnetic_media_flag = 'Y' then
3146 --
3147 hr_utility.set_location(c_proc, 31);
3148 --
3149 l_override_flag := l_wtm_override_flag;
3150 l_description := l_wtm_description;
3151 l_report_type := WTM;
3152 --
3153 else
3154 --
3155 hr_utility.set_location(c_proc, 32);
3156 --
3157 l_override_flag := l_itw_override_flag;
3158 l_description := l_itw_description;
3159 l_report_type := ITW;
3160 --
3161 end if;
3162 --
3163 hr_utility.set_location(c_proc, 40);
3164 --
3165 if l_override_flag = 'Y' then
3166 --
3167 hr_utility.set_location(c_proc, 41);
3168 --
3169 p_certificate_info.description_kanji := l_description;
3170 p_certificate_info.description_kana := hr_jp_standard_pkg.to_hankaku(l_description);
3171 --
3172 else
3173 --
3174 hr_utility.set_location(c_proc, 42);
3175 --
3176 get_dpnts(
3177 p_dpnt_ref_type => p_dpnt_ref_type,
3178 p_assignment_id => p_assignment_id,
3179 p_itax_category => p_itax_category,
3180 p_dpnt_effective_date => p_dpnt_effective_date,
3181 p_person_id => p_person_id,
3182 p_effective_date => p_effective_date,
3183 p_sex => p_sex,
3184 p_dpnts => l_dpnts);
3185 --
3186 hr_utility.set_location(c_proc, 43);
3187 --
3188 get_descriptions(
3189 p_assignment_id => p_assignment_id,
3190 p_person_id => p_person_id,
3191 p_effective_date => p_effective_date,
3192 p_itax_yea_category => p_itax_yea_category,
3193 p_certificate_info => p_certificate_info,
3194 p_last_name_kanji => p_last_name_kanji,
3195 p_last_name_kana => p_last_name_kana,
3196 p_dpnts => l_dpnts,
3197 p_prev_jobs => p_prev_jobs,
3198 p_housing_tbl => p_housing_tbl,
3199 p_report_type => l_report_type,
3200 p_descriptions => l_descriptions);
3201 --
3202 hr_utility.set_location(c_proc, 44);
3203 --
3204 -- Construct Description field.
3205 --
3206 for i in 1..l_descriptions.count loop
3207 --
3208 hr_utility.set_location(c_proc, 45);
3209 --
3210 if p_magnetic_media_flag = 'Y' then
3211 --
3212 concat_description(l_descriptions(i).description_kanji, p_certificate_info.description_kanji, ',');
3213 concat_description(l_descriptions(i).description_kana, p_certificate_info.description_kana, ',');
3214 --
3215 else
3216 --
3217 if l_descriptions(i).description_type in (FIXED_RATE_TAX_REDUCTION, NP_PREM) then
3218 --
3219 concat_description(l_descriptions(i).description_kanji, p_certificate_info.desc_line1_kanji, ' ');
3220 concat_description(l_descriptions(i).description_kana, p_certificate_info.desc_line1_kana, ' ');
3221 --
3222 else
3223 --
3224 concat_description(l_descriptions(i).description_kanji, p_certificate_info.description_kanji, ',');
3225 concat_description(l_descriptions(i).description_kana, p_certificate_info.description_kana, ',');
3226 --
3227 end if;
3228 --
3229 end if;
3230 --
3231 end loop;
3232 --
3233 concat_description(l_description, p_certificate_info.description_kanji, ',');
3234 concat_description(hr_jp_standard_pkg.to_hankaku(l_description), p_certificate_info.description_kana, ',');
3235 --
3236 end if;
3237 --
3238 hr_utility.set_location('Leaving: ' || c_proc, 100);
3239 --
3240 end get_certificate_info;
3241 --
3242 -- Wrapper function for WTM (used this in efile PAYJPWTM, PAYJPSPE : gci_wtm)
3243 --
3244 procedure get_certificate_info(
3245 p_assignment_action_id in number,
3246 p_assignment_id in number,
3247 p_action_sequence in number,
3248 p_business_group_id in number,
3249 p_effective_date in date,
3250 p_date_earned in date,
3251 p_itax_organization_id in number,
3252 p_itax_category in varchar2,
3253 p_itax_yea_category in varchar2,
3254 p_dpnt_ref_type in varchar2,
3255 p_dpnt_effective_date in date,
3256 p_person_id in number,
3257 p_sex in varchar2,
3258 p_date_of_birth in date,
3259 p_leaving_reason in varchar2,
3260 p_last_name_kanji in varchar2,
3261 p_last_name_kana in varchar2,
3262 p_employment_category in varchar2,
3263 p_magnetic_media_flag in varchar2 default 'N',
3264 p_certificate_info out nocopy t_certificate_info,
3265 p_submission_required_flag out nocopy varchar2,
3266 p_prev_job_info out nocopy t_prev_job_info,
3267 p_housing_info out nocopy t_housing_info)
3268 is
3269 --
3270 l_housing_tbl t_housing_tbl;
3271 l_prev_jobs pay_jp_wic_pkg.t_prev_jobs;
3272 --
3273 begin
3274 --
3275 get_certificate_info(
3276 p_assignment_action_id => p_assignment_action_id,
3277 p_assignment_id => p_assignment_id,
3278 p_action_sequence => p_action_sequence,
3279 p_business_group_id => p_business_group_id,
3280 p_effective_date => p_effective_date,
3281 p_date_earned => p_date_earned,
3282 p_itax_organization_id => p_itax_organization_id,
3283 p_itax_category => p_itax_category,
3284 p_itax_yea_category => p_itax_yea_category,
3285 p_dpnt_ref_type => p_dpnt_ref_type,
3286 p_dpnt_effective_date => p_dpnt_effective_date,
3287 p_person_id => p_person_id,
3288 p_sex => p_sex,
3289 p_date_of_birth => p_date_of_birth,
3290 p_leaving_reason => p_leaving_reason,
3291 p_last_name_kanji => p_last_name_kanji,
3292 p_last_name_kana => p_last_name_kana,
3293 p_employment_category => p_employment_category,
3294 p_magnetic_media_flag => p_magnetic_media_flag,
3295 p_certificate_info => p_certificate_info,
3296 p_submission_required_flag => p_submission_required_flag,
3297 p_prev_jobs => l_prev_jobs,
3298 p_housing_tbl => l_housing_tbl);
3299 --
3300 p_housing_info := convert_housing_tbl(
3301 p_effective_date,
3302 p_itax_yea_category,
3303 p_certificate_info,
3304 l_housing_tbl);
3305 --
3306 p_prev_job_info := convert_prev_jobs(l_prev_jobs);
3307 --
3308 end get_certificate_info;
3309 --
3310 -- Wrapper function for existing user program (call gci_wtm for previous behavior : gci_wtm_usr)
3311 --
3312 procedure get_certificate_info(
3313 p_assignment_action_id in number,
3314 p_assignment_id in number,
3315 p_action_sequence in number,
3316 p_business_group_id in number,
3317 p_effective_date in date,
3318 p_date_earned in date,
3319 p_itax_organization_id in number,
3320 p_itax_category in varchar2,
3321 p_itax_yea_category in varchar2,
3322 p_dpnt_ref_type in varchar2,
3323 p_dpnt_effective_date in date,
3324 p_person_id in number,
3325 p_sex in varchar2,
3326 p_date_of_birth in date,
3327 p_leaving_reason in varchar2,
3328 p_last_name_kanji in varchar2,
3329 p_last_name_kana in varchar2,
3330 p_employment_category in varchar2,
3331 p_magnetic_media_flag in varchar2 default 'N',
3332 p_certificate_info out nocopy t_certificate_info,
3333 p_submission_required_flag out nocopy varchar2,
3334 p_prev_job_info out nocopy t_prev_job_info)
3335 is
3336 --
3337 l_housing_info t_housing_info;
3338 --
3339 begin
3340 --
3341 get_certificate_info(
3342 p_assignment_action_id => p_assignment_action_id,
3343 p_assignment_id => p_assignment_id,
3344 p_action_sequence => p_action_sequence,
3345 p_business_group_id => p_business_group_id,
3346 p_effective_date => p_effective_date,
3347 p_date_earned => p_date_earned,
3348 p_itax_organization_id => p_itax_organization_id,
3349 p_itax_category => p_itax_category,
3350 p_itax_yea_category => p_itax_yea_category,
3351 p_dpnt_ref_type => p_dpnt_ref_type,
3352 p_dpnt_effective_date => p_dpnt_effective_date,
3353 p_person_id => p_person_id,
3354 p_sex => p_sex,
3355 p_date_of_birth => p_date_of_birth,
3356 p_leaving_reason => p_leaving_reason,
3357 p_last_name_kanji => p_last_name_kanji,
3358 p_last_name_kana => p_last_name_kana,
3359 p_employment_category => p_employment_category,
3360 p_magnetic_media_flag => p_magnetic_media_flag,
3361 p_certificate_info => p_certificate_info,
3362 p_submission_required_flag => p_submission_required_flag,
3363 p_prev_job_info => p_prev_job_info,
3364 p_housing_info => l_housing_info);
3365 --
3366 end get_certificate_info;
3367 --
3368 -- |-------------------------------------------------------------------|
3369 -- |---------------------< get_certificate_info >----------------------|
3370 -- |-------------------------------------------------------------------|
3371 --
3372 -- Wrapper function for ITW (called from gci_itw : gci_itw_b)
3373 --
3374 procedure get_certificate_info(
3375 p_assignment_action_id in number,
3376 p_assignment_id in number,
3377 p_action_sequence in number,
3378 p_business_group_id in number,
3379 p_effective_date in date,
3380 p_date_earned in date,
3381 p_itax_organization_id in number,
3382 p_itax_category in varchar2,
3383 p_itax_yea_category in varchar2,
3384 p_dpnt_ref_type in varchar2,
3385 p_dpnt_effective_date in date,
3386 p_person_id in number,
3387 p_sex in varchar2,
3388 p_date_of_birth in date,
3389 p_leaving_reason in varchar2,
3390 p_last_name_kanji in varchar2,
3391 p_last_name_kana in varchar2,
3392 p_employment_category in varchar2,
3393 p_magnetic_media_flag in varchar2 default 'N',
3394 p_certificate_info out nocopy t_certificate_info,
3395 p_submission_required_flag out nocopy varchar2,
3396 p_prev_jobs out nocopy t_prev_jobs,
3397 p_withholding_tax_info out nocopy t_tax_info)
3398 is
3399 --
3400 c_proc constant varchar2(61) := c_package || 'get_certificate_info (ITW)';
3401 --
3402 l_itw_override_flag varchar2(1);
3403 l_itw_description varchar2(300);
3404 l_wtm_override_flag varchar2(1);
3405 l_wtm_description varchar2(300);
3406 --
3407 l_dpnts t_dpnts;
3408 l_descriptions t_descriptions;
3409 l_housing_tbl t_housing_tbl;
3410 --
3411 begin
3412 --
3413 hr_utility.set_location('Entering: ' || c_proc, 10);
3414 --
3415 get_certificate_info(
3416 p_assignment_action_id => p_assignment_action_id,
3417 p_assignment_id => p_assignment_id,
3418 p_action_sequence => p_action_sequence,
3419 p_business_group_id => p_business_group_id,
3420 p_effective_date => p_effective_date,
3421 p_date_earned => p_date_earned,
3422 p_itax_organization_id => p_itax_organization_id,
3423 p_itax_category => p_itax_category,
3424 p_itax_yea_category => p_itax_yea_category,
3425 p_dpnt_ref_type => p_dpnt_ref_type,
3426 p_dpnt_effective_date => p_dpnt_effective_date,
3427 p_person_id => p_person_id,
3428 p_sex => p_sex,
3429 p_date_of_birth => p_date_of_birth,
3430 p_leaving_reason => p_leaving_reason,
3431 p_last_name_kanji => p_last_name_kanji,
3432 p_last_name_kana => p_last_name_kana,
3433 p_employment_category => p_employment_category,
3434 p_magnetic_media_flag => p_magnetic_media_flag,
3435 p_certificate_info => p_certificate_info,
3436 p_submission_required_flag => p_submission_required_flag,
3437 p_prev_jobs => p_prev_jobs,
3438 p_housing_tbl => l_housing_tbl);
3439 --
3440 hr_utility.set_location(c_proc, 20);
3441 --
3442 if p_itax_yea_category = '0' then
3443 --
3444 hr_utility.set_location(c_proc, 21);
3445 --
3446 -- Derive withholding tax information by current employer.
3447 --
3448 get_withholding_tax_info(
3449 p_assignment_action_id => p_assignment_action_id,
3450 p_assignment_id => p_assignment_id,
3451 p_action_sequence => p_action_sequence,
3452 p_effective_date => p_effective_date,
3453 p_itax_organization_id => p_itax_organization_id,
3454 p_itax_category => p_itax_category,
3455 p_itax_yea_category => p_itax_yea_category,
3456 p_withholding_tax_info => p_withholding_tax_info);
3457 --
3458 else
3459 --
3460 hr_utility.set_location(c_proc, 22);
3461 --
3462 -- For non-YEAed employees, current employer's payment with same tax category
3463 -- are shown in certificates. So just copy p_certificate_info to p_withholding_tax_info.
3464 --
3465 p_withholding_tax_info := p_certificate_info.tax_info;
3466 --
3467 end if;
3468 --
3469 hr_utility.set_location('Leaving: ' || c_proc, 100);
3470 --
3471 end get_certificate_info;
3472 --
3473 -- |-------------------------------------------------------------------|
3474 -- |---------------------< get_certificate_info >----------------------|
3475 -- |-------------------------------------------------------------------|
3476 --
3477 -- Wrapper function for Archive (used this in PAYJPITW_ARCHIVE : gci_itw_arc)
3478 --
3479 procedure get_certificate_info(
3480 p_assignment_action_id in number,
3481 p_assignment_id in number,
3482 p_action_sequence in number,
3483 p_business_group_id in number,
3484 p_effective_date in date,
3485 p_date_earned in date,
3486 p_itax_organization_id in number,
3487 p_itax_category in varchar2,
3488 p_itax_yea_category in varchar2,
3489 p_dpnt_ref_type in varchar2,
3490 p_dpnt_effective_date in date,
3491 p_person_id in number,
3492 p_sex in varchar2,
3493 p_date_of_birth in date,
3494 p_leaving_reason in varchar2,
3495 p_last_name_kanji in varchar2,
3496 p_last_name_kana in varchar2,
3497 p_employment_category in varchar2,
3498 p_certificate_info out nocopy t_certificate_info,
3499 p_submission_required_flag out nocopy varchar2,
3500 p_prev_job_info out nocopy t_prev_job_info,
3501 p_housing_info out nocopy t_housing_info,
3502 p_withholding_tax_info out nocopy t_tax_info,
3503 p_itw_description out nocopy varchar2,
3504 p_itw_descriptions out nocopy t_descriptions,
3505 p_wtm_description out nocopy varchar2,
3506 p_wtm_descriptions out nocopy t_descriptions)
3507 is
3508 --
3509 c_proc constant varchar2(61) := c_package || 'get_certificate_info (ARC)';
3510 --
3511 l_dpnts t_dpnts;
3512 l_housing_tbl t_housing_tbl;
3513 l_prev_jobs t_prev_jobs;
3514 --
3515 l_itw_override_flag varchar2(1);
3516 l_wtm_override_flag varchar2(1);
3517 --
3518 begin
3519 --
3520 hr_utility.set_location('Entering: ' || c_proc, 10);
3521 --
3522 get_basic_certificate_info(
3523 p_assignment_action_id => p_assignment_action_id,
3524 p_assignment_id => p_assignment_id,
3525 p_action_sequence => p_action_sequence,
3526 p_business_group_id => p_business_group_id,
3527 p_effective_date => p_effective_date,
3528 p_date_earned => p_date_earned,
3529 p_itax_organization_id => p_itax_organization_id,
3530 p_itax_category => p_itax_category,
3531 p_itax_yea_category => p_itax_yea_category,
3532 p_dpnt_ref_type => p_dpnt_ref_type,
3533 p_dpnt_effective_date => p_dpnt_effective_date,
3534 p_person_id => p_person_id,
3535 p_sex => p_sex,
3536 p_date_of_birth => p_date_of_birth,
3537 p_leaving_reason => p_leaving_reason,
3538 p_employment_category => p_employment_category,
3539 p_certificate_info => p_certificate_info,
3540 p_submission_required_flag => p_submission_required_flag,
3541 p_prev_jobs => l_prev_jobs,
3542 p_housing_tbl => l_housing_tbl);
3543 --
3544 hr_utility.set_location(c_proc, 20);
3545 --
3546 p_housing_info := convert_housing_tbl(
3547 p_effective_date,
3548 p_itax_yea_category,
3549 p_certificate_info,
3550 l_housing_tbl);
3551 --
3552 p_prev_job_info := convert_prev_jobs(l_prev_jobs);
3553 --
3554 hr_utility.set_location(c_proc, 30);
3555 --
3556 if p_itax_yea_category = '0' then
3557 --
3558 -- Derive withholding tax information by current employer.
3559 --
3560 get_withholding_tax_info(
3561 p_assignment_action_id => p_assignment_action_id,
3562 p_assignment_id => p_assignment_id,
3563 p_action_sequence => p_action_sequence,
3564 p_effective_date => p_effective_date,
3565 p_itax_organization_id => p_itax_organization_id,
3566 p_itax_category => p_itax_category,
3567 p_itax_yea_category => p_itax_yea_category,
3568 p_withholding_tax_info => p_withholding_tax_info);
3569 --
3570 else
3571 --
3572 -- For non-YEAed employees, current employer's payment with same tax category
3573 -- are shown in certificates. So just copy p_certificate_info to p_withholding_tax_info.
3574 --
3575 p_withholding_tax_info := p_certificate_info.tax_info;
3576 --
3577 end if;
3578 --
3579 hr_utility.set_location(c_proc, 40);
3580 --
3581 -- Get Description information
3582 -- When description on element entry is marked as "Override",
3583 -- subsequent description will be replaced by this.
3584 --
3585 get_ee_description(
3586 p_assignment_id => p_assignment_id,
3587 p_business_group_id => p_business_group_id,
3588 p_effective_date => p_date_earned,
3589 p_itw_override_flag => l_itw_override_flag,
3590 p_itw_description => p_itw_description,
3591 p_wtm_override_flag => l_wtm_override_flag,
3592 p_wtm_description => p_wtm_description);
3593 --
3594 hr_utility.set_location(c_proc, 50);
3595 --
3596 if l_itw_override_flag = 'N' or l_wtm_override_flag = 'N' then
3597 --
3598 hr_utility.set_location(c_proc, 51);
3599 --
3600 get_dpnts(
3601 p_dpnt_ref_type => p_dpnt_ref_type,
3602 p_assignment_id => p_assignment_id,
3603 p_itax_category => p_itax_category,
3604 p_dpnt_effective_date => p_dpnt_effective_date,
3605 p_person_id => p_person_id,
3606 p_effective_date => p_effective_date,
3607 p_sex => p_sex,
3608 p_dpnts => l_dpnts);
3609 --
3610 hr_utility.set_location(c_proc, 52);
3611 --
3612 if l_itw_override_flag = 'N' then
3613 --
3614 hr_utility.set_location(c_proc, 53);
3615 --
3616 get_descriptions(
3617 p_assignment_id => p_assignment_id,
3618 p_person_id => p_person_id,
3619 p_effective_date => p_effective_date,
3620 p_itax_yea_category => p_itax_yea_category,
3621 p_certificate_info => p_certificate_info,
3622 p_last_name_kanji => p_last_name_kanji,
3623 p_last_name_kana => p_last_name_kana,
3624 p_dpnts => l_dpnts,
3625 p_prev_jobs => l_prev_jobs,
3626 p_housing_tbl => l_housing_tbl,
3627 p_report_type => ITW,
3628 p_descriptions => p_itw_descriptions);
3629 --
3630 end if;
3631 --
3632 if l_wtm_override_flag = 'N' then
3633 --
3634 hr_utility.set_location(c_proc, 54);
3635 --
3636 get_descriptions(
3637 p_assignment_id => p_assignment_id,
3638 p_person_id => p_person_id,
3639 p_effective_date => p_effective_date,
3640 p_itax_yea_category => p_itax_yea_category,
3641 p_certificate_info => p_certificate_info,
3642 p_last_name_kanji => p_last_name_kanji,
3643 p_last_name_kana => p_last_name_kana,
3644 p_dpnts => l_dpnts,
3645 p_prev_jobs => l_prev_jobs,
3646 p_housing_tbl => l_housing_tbl,
3647 p_report_type => WTM,
3648 p_descriptions => p_wtm_descriptions);
3649 --
3650 end if;
3651 --
3652 hr_utility.set_location(c_proc, 55);
3653 --
3654 end if;
3655 --
3656 hr_utility.set_location('Leaving: ' || c_proc, 100);
3657 --
3658 end get_certificate_info;
3659 --
3660 -- Wrapper function for wage ledger (used this in PAYJPWL_ARCHIVE : gci_wl_arc)
3661 --
3662 procedure get_certificate_info(
3663 p_assignment_action_id in number,
3664 p_assignment_id in number,
3665 p_action_sequence in number,
3666 p_business_group_id in number,
3667 p_effective_date in date,
3668 p_date_earned in date,
3669 p_itax_organization_id in number,
3670 p_itax_category in varchar2,
3671 p_itax_yea_category in varchar2,
3672 p_dpnt_ref_type in varchar2,
3673 p_dpnt_effective_date in date,
3674 p_person_id in number,
3675 p_sex in varchar2,
3676 p_date_of_birth in date,
3677 p_leaving_reason in varchar2,
3678 p_last_name_kanji in varchar2,
3679 p_last_name_kana in varchar2,
3680 p_employment_category in varchar2,
3681 p_certificate_info out nocopy t_certificate_info,
3682 p_submission_required_flag out nocopy varchar2,
3683 p_prev_job_info out nocopy t_prev_job_info,
3684 p_withholding_tax_info out nocopy t_tax_info,
3685 p_itw_description out nocopy varchar2,
3686 p_itw_descriptions out nocopy t_descriptions,
3687 p_wtm_description out nocopy varchar2,
3688 p_wtm_descriptions out nocopy t_descriptions)
3689 is
3690 --
3691 l_housing_info t_housing_info;
3692 --
3693 begin
3694 --
3695 get_certificate_info(
3696 p_assignment_action_id => p_assignment_action_id,
3697 p_assignment_id => p_assignment_id,
3698 p_action_sequence => p_action_sequence,
3699 p_business_group_id => p_business_group_id,
3700 p_effective_date => p_effective_date,
3701 p_date_earned => p_date_earned,
3702 p_itax_organization_id => p_itax_organization_id,
3703 p_itax_category => p_itax_category,
3704 p_itax_yea_category => p_itax_yea_category,
3705 p_dpnt_ref_type => p_dpnt_ref_type,
3706 p_dpnt_effective_date => p_dpnt_effective_date,
3707 p_person_id => p_person_id,
3708 p_sex => p_sex,
3709 p_date_of_birth => p_date_of_birth,
3710 p_leaving_reason => p_leaving_reason,
3711 p_last_name_kanji => p_last_name_kanji,
3712 p_last_name_kana => p_last_name_kana,
3713 p_employment_category => p_employment_category,
3714 p_certificate_info => p_certificate_info,
3715 p_submission_required_flag => p_submission_required_flag,
3716 p_prev_job_info => p_prev_job_info,
3717 p_housing_info => l_housing_info,
3718 p_withholding_tax_info => p_withholding_tax_info,
3719 p_itw_description => p_itw_description,
3720 p_itw_descriptions => p_itw_descriptions,
3721 p_wtm_description => p_wtm_description,
3722 p_wtm_descriptions => p_wtm_descriptions);
3723 --
3724 end get_certificate_info;
3725 --
3726 -- |-------------------------------------------------------------------|
3727 -- |---------------------< set_valid_term_taxable_amt >----------------|
3728 -- |-------------------------------------------------------------------|
3729 procedure set_valid_term_taxable_amt(
3730 p_valid_term_taxable_amt in number)
3731 is
3732 begin
3733 --
3734 pay_jp_wic_pkg.g_valid_term_taxable_amt := p_valid_term_taxable_amt;
3735 --
3736 end set_valid_term_taxable_amt;
3737 --
3738 -- |-------------------------------------------------------------------|
3739 -- |----------------------< spr_term_valid >---------------------------|
3740 -- |-------------------------------------------------------------------|
3741 -- Use in Summary Payment Report and ITW with Term Validation
3742 function spr_term_valid(
3743 p_assignment_action_id in number,
3744 p_assignment_id in number,
3745 p_action_sequence in number,
3746 p_effective_date in date,
3747 p_itax_organization_id in number,
3748 p_itax_category in varchar2,
3749 p_itax_yea_category in varchar2,
3750 p_employment_category in varchar2,
3751 p_termination_date in date,
3752 p_certificate_info in t_certificate_info default null)
3753 return number
3754 is
3755 --
3756 l_result number := 0; /* 0 (false) or 1 (true) */
3757 --
3758 l_certificate_info t_certificate_info;
3759 l_submission_required_flag varchar2(1);
3760 --
3761 begin
3762 --
3763 if p_termination_date is not null then
3764 --
3765 if pay_jp_wic_pkg.g_valid_term_taxable_amt is null then
3766 hr_utility.set_message(800,'HR_7914_CHECK_FMT_NUMBER');
3767 hr_utility.set_message_token('ARG_NAME','pay_jp_wic_pkg.g_valid_term_taxable_amt');
3768 hr_utility.set_message_token('ARG_VALUE','pay_jp_wic_pkg.g_valid_term_taxable_amt');
3769 hr_utility.raise_error;
3770 end if;
3771 --
3772 if p_certificate_info.tax_info.taxable_income is null then
3773 --
3774 get_basic_certificate_info(
3775 p_assignment_action_id => p_assignment_action_id,
3776 p_assignment_id => p_assignment_id,
3777 p_action_sequence => p_action_sequence,
3778 p_effective_date => p_effective_date,
3779 p_itax_organization_id => p_itax_organization_id,
3780 p_itax_category => p_itax_category,
3781 p_itax_yea_category => p_itax_yea_category,
3782 p_employment_category => p_employment_category,
3783 p_certificate_info => l_certificate_info.tax_info,
3784 p_submission_required_flag => l_submission_required_flag);
3785 --
3786 else
3787 --
3788 l_certificate_info := p_certificate_info;
3789 --
3790 end if;
3791 --
3792 if l_certificate_info.tax_info.taxable_income <= pay_jp_wic_pkg.g_valid_term_taxable_amt then
3793 --
3794 l_result := 1;
3795 --
3796 end if;
3797 --
3798 end if;
3799 --
3800 return l_result;
3801 --
3802 end spr_term_valid;
3803 --
3804 -- |-------------------------------------------------------------------|
3805 -- |---------------------< get_certificate_info >----------------------|
3806 -- |-------------------------------------------------------------------|
3807 --
3808 -- Wrapper for ITW with Term Validation (used this in PAYJPITW : gci_itw)
3809 --
3810 procedure get_certificate_info(
3811 p_assignment_action_id in number,
3812 p_assignment_id in number,
3813 p_action_sequence in number,
3814 p_business_group_id in number,
3815 p_effective_date in date,
3816 p_date_earned in date,
3817 p_itax_organization_id in number,
3818 p_itax_category in varchar2,
3819 p_itax_yea_category in varchar2,
3820 p_dpnt_ref_type in varchar2,
3821 p_dpnt_effective_date in date,
3822 p_person_id in number,
3823 p_sex in varchar2,
3824 p_date_of_birth in date,
3825 p_leaving_reason in varchar2,
3826 p_last_name_kanji in varchar2,
3827 p_last_name_kana in varchar2,
3828 p_employment_category in varchar2,
3829 p_magnetic_media_flag in varchar2 default 'N',
3830 p_termination_date in date,
3831 p_certificate_info out nocopy t_certificate_info,
3832 p_submission_required_flag out nocopy varchar2,
3833 p_prev_jobs out nocopy t_prev_jobs,
3834 p_withholding_tax_info out nocopy t_tax_info,
3835 p_spr_term_valid out nocopy number)
3836 is
3837 begin
3838 --
3839 get_certificate_info(
3840 p_assignment_action_id => p_assignment_action_id,
3841 p_assignment_id => p_assignment_id,
3842 p_action_sequence => p_action_sequence,
3843 p_business_group_id => p_business_group_id,
3844 p_effective_date => p_effective_date,
3845 p_date_earned => p_date_earned,
3846 p_itax_organization_id => p_itax_organization_id,
3847 p_itax_category => p_itax_category,
3848 p_itax_yea_category => p_itax_yea_category,
3849 p_dpnt_ref_type => p_dpnt_ref_type,
3850 p_dpnt_effective_date => p_dpnt_effective_date,
3851 p_person_id => p_person_id,
3852 p_sex => p_sex,
3853 p_date_of_birth => p_date_of_birth,
3854 p_leaving_reason => p_leaving_reason,
3855 p_last_name_kanji => p_last_name_kanji,
3856 p_last_name_kana => p_last_name_kana,
3857 p_employment_category => p_employment_category,
3858 p_magnetic_media_flag => p_magnetic_media_flag,
3859 p_certificate_info => p_certificate_info,
3860 p_submission_required_flag => p_submission_required_flag,
3861 p_prev_jobs => p_prev_jobs,
3862 p_withholding_tax_info => p_withholding_tax_info);
3863 --
3864 p_spr_term_valid := spr_term_valid(
3865 p_assignment_action_id => p_assignment_action_id,
3866 p_assignment_id => p_assignment_id,
3867 p_action_sequence => p_action_sequence,
3868 p_effective_date => p_effective_date,
3869 p_itax_organization_id => p_itax_organization_id,
3870 p_itax_category => p_itax_category,
3871 p_itax_yea_category => p_itax_yea_category,
3872 p_employment_category => p_employment_category,
3873 p_termination_date => p_termination_date,
3874 p_certificate_info => p_certificate_info);
3875 --
3876 end get_certificate_info;
3877 --
3878 -- |-------------------------------------------------------------------|
3879 -- |---------------------< get_certificate_info >----------------------|
3880 -- |-------------------------------------------------------------------|
3881 -- For debugging purpose. NEVER USE THIS in your product code.
3882 procedure get_certificate_info(
3883 p_assignment_action_id in number,
3884 p_magnetic_media_flag in varchar2 default 'N',
3885 p_certificate_info out nocopy t_certificate_info,
3886 p_submission_required_flag out nocopy varchar2,
3887 p_prev_jobs out nocopy t_prev_jobs,
3888 p_withholding_tax_info out nocopy t_tax_info)
3889 is
3890 cursor csr_assact is
3891 select wic.assignment_id,
3892 wic.action_sequence,
3893 wic.business_group_id,
3894 wic.effective_date,
3895 wic.date_earned,
3896 wic.itax_organization_id,
3897 wic.itax_category,
3898 wic.itax_yea_category,
3899 nvl(nvl(pay.prl_information1, hoi.org_information2), 'CTR_EE') DPNT_REF_TYPE,
3900 nvl(fnd_date.canonical_to_date(pay_core_utils.get_parameter('ITAX_DPNT_EFFECTIVE_DATE', wic.legislative_parameters)),
3901 wic.effective_date) DPNT_EFFECTIVE_DATE,
3902 per.person_id,
3903 per.sex,
3904 per.date_of_birth,
3905 wic.leaving_reason,
3906 per.per_information18 LAST_NAME_KANJI,
3907 per.last_name LAST_NAME_KANA,
3908 wic.employment_category
3909 from per_all_people_f per,
3910 hr_organization_information hoi,
3911 pay_all_payrolls_f pay,
3912 /* Use V2 instead of V for debugging. */
3913 pay_jp_wic_assacts_v2 wic
3914 where wic.assignment_action_id = p_assignment_action_id
3915 and pay.payroll_id = wic.payroll_id
3916 and wic.date_earned
3917 between pay.effective_start_date and pay.effective_end_date
3918 and hoi.organization_id(+) = wic.business_group_id
3919 and hoi.org_information_context(+) = 'JP_BUSINESS_GROUP_INFO'
3920 and per.person_id = wic.person_id
3921 and wic.effective_date
3922 between per.effective_start_date and per.effective_end_date;
3923 begin
3924 for l_rec in csr_assact loop
3925 get_certificate_info(
3926 p_assignment_action_id => p_assignment_action_id,
3927 p_assignment_id => l_rec.assignment_id,
3928 p_action_sequence => l_rec.action_sequence,
3929 p_business_group_id => l_rec.business_group_id,
3930 p_effective_date => l_rec.effective_date,
3931 p_date_earned => l_rec.date_earned,
3932 p_itax_organization_id => l_rec.itax_organization_id,
3933 p_itax_category => l_rec.itax_category,
3934 p_itax_yea_category => l_rec.itax_yea_category,
3935 p_dpnt_ref_type => l_rec.dpnt_ref_type,
3936 p_dpnt_effective_date => l_rec.dpnt_effective_date,
3937 p_person_id => l_rec.person_id,
3938 p_sex => l_rec.sex,
3939 p_date_of_birth => l_rec.date_of_birth,
3940 p_leaving_reason => l_rec.leaving_reason,
3941 p_last_name_kanji => l_rec.last_name_kanji,
3942 p_last_name_kana => l_rec.last_name_kana,
3943 p_employment_category => l_rec.employment_category,
3944 p_magnetic_media_flag => p_magnetic_media_flag,
3945 p_certificate_info => p_certificate_info,
3946 p_submission_required_flag => p_submission_required_flag,
3947 p_prev_jobs => p_prev_jobs,
3948 p_withholding_tax_info => p_withholding_tax_info);
3949 end loop;
3950 end get_certificate_info;
3951 -- |-------------------------------------------------------------------|
3952 -- |----------------------< ass_set_validation >-----------------------|
3953 -- |-------------------------------------------------------------------|
3954 FUNCTION ass_set_validation(
3955 p_assignment_set_id in NUMBER,
3956 p_assignment_id in NUMBER,
3957 p_effective_date in DATE) RETURN NUMBER
3958 IS
3959 BEGIN
3960 --
3961 -- Bug.4863756
3962 -- Now this is just wrapper of hr_jp_ast_utility_pkg.assignment_set_validate.
3963 --
3964 if hr_jp_ast_utility_pkg.assignment_set_validate(
3965 p_assignment_set_id,
3966 p_assignment_id,
3967 p_effective_date,
3968 'N') = 'Y' then
3969 return 1;
3970 else
3971 return 0;
3972 end if;
3973 END ass_set_validation;
3974 --
3975 -- Package Initialization
3976 --
3977 begin
3978 g_prompt_kanji.yen := fnd_message.get_string('PAY', 'PAY_JP_JBA_MONEY_SUFFIX');
3979 g_prompt_kanji.fixed_rate_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_YEA_FIXED_RATE');
3980 g_prompt_kanji.national_pens_prem := fnd_message.get_string('PAY', 'PAY_JP_NATIONAL_PENSION');
3981 g_prompt_kanji.prev_job := fnd_message.get_string('PAY', 'PAY_JP_PREVIOUS_EMPLOYMENT');
3982 g_prompt_kanji.taxable_income := fnd_message.get_string('PAY', 'PAY_JP_SALARY');
3983 g_prompt_kanji.si_prem := fnd_message.get_string('PAY', 'PAY_JP_TRANS_SI');
3984 g_prompt_kanji.mutual_aid_prem := fnd_message.get_string('PAY', 'PAY_JP_WITHIN');
3985 g_prompt_kanji.itax := fnd_message.get_string('PAY', 'PAY_JP_TAX');
3986 g_prompt_kanji.terminated := fnd_message.get_string('PAY', 'PAY_JP_TERM');
3987 g_prompt_kanji.housing_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_HOUSING_DEDUCTION');
3988 g_prompt_kanji.residence_date := fnd_message.get_string('PAY', 'PAY_JP_RES_START_DATE');
3989 g_prompt_kanji.unclaimed_housing_tax_reduct := fnd_message.get_string('PAY', 'PAY_JP_DEDUCTIONS_NOT_TAKEN');
3990 -- 2007/06/07
3991 g_prompt_kanji.housing_tax_reduction_long := fnd_message.get_string('PAY', 'PAY_JP_HOUSING_DEDUCTION_LONG');
3992 g_prompt_kanji.total_housing_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_AVAILABLE_AMOUNT');
3993 --
3994 g_prompt_kanji.disaster_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_GRACE_TAX_AMOUNT');
3995 g_prompt_kanji.husband := fnd_message.get_string('PAY', 'PAY_JP_HUSBAND');
3996 g_prompt_kanji.wife := fnd_message.get_string('PAY', 'PAY_JP_WIFE');
3997 g_prompt_kanji.date_format := 'FMYY"' || fnd_message.get_string('PER', 'HR_JP_YY') ||
3998 '"MM"' || fnd_message.get_string('PER', 'HR_JP_MM') ||
3999 '"DD"' || fnd_message.get_string('PER', 'HR_JP_DD') || '"';
4000 g_prompt_kanji.other := fnd_message.get_string('PAY', 'PAY_JP_TRANS_OTHER');
4001 g_prompt_kanji.count := fnd_message.get_string('PAY', 'PAY_JP_TRANS_COUNT');
4002 --
4003 g_prompt_kana.yen := fnd_message.get_string('PAY', 'PAY_JP_JBA_MONEY_SUFFIX_KANA');
4004 g_prompt_kana.fixed_rate_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_YEA_FIXED_RATE_KANA');
4005 g_prompt_kana.national_pens_prem := fnd_message.get_string('PAY', 'PAY_JP_NATIONAL_PENSION_KANA');
4006 g_prompt_kana.prev_job := fnd_message.get_string('PAY', 'PAY_JP_PREV_EMPLOYMENT_KANA');
4007 g_prompt_kana.taxable_income := fnd_message.get_string('PAY', 'PAY_JP_TRANS_SALARY_KANA');
4008 g_prompt_kana.si_prem := fnd_message.get_string('PAY', 'PAY_JP_TRANS_SI_KANA');
4009 g_prompt_kana.mutual_aid_prem := fnd_message.get_string('PAY', 'PAY_JP_WITHIN_KANA');
4010 g_prompt_kana.itax := fnd_message.get_string('PAY', 'PAY_JP_TAX_KANA');
4011 g_prompt_kana.terminated := fnd_message.get_string('PAY', 'PAY_JP_TRANS_TERM_KANA');
4012 g_prompt_kana.housing_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_HOUSING_DEDUCTION_KANA');
4013 g_prompt_kana.residence_date := fnd_message.get_string('PAY', 'PAY_JP_RES_START_DATE_KANA');
4014 g_prompt_kana.unclaimed_housing_tax_reduct := fnd_message.get_string('PAY', 'PAY_JP_DCT_NOT_TAKEN_KANA');
4015 -- 2007/06/07
4016 g_prompt_kana.housing_tax_reduction_long := fnd_message.get_string('PAY', 'PAY_JP_HOUSING_DCT_LONG_KANA');
4017 g_prompt_kana.total_housing_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_AVAILABLE_AMOUNT_KANA');
4018 --
4019 g_prompt_kana.disaster_tax_reduction := fnd_message.get_string('PAY', 'PAY_JP_GRACE_TAX_AMOUNT_KANA');
4020 g_prompt_kana.husband := fnd_message.get_string('PAY', 'PAY_JP_HUSBAND_KANA');
4021 g_prompt_kana.wife := fnd_message.get_string('PAY', 'PAY_JP_WIFE_KANA');
4022 g_prompt_kana.date_format := 'FMYY"' || fnd_message.get_string('PAY', 'PAY_JP_TRANS_YY_KANA') ||
4023 '"MM"' || fnd_message.get_string('PAY', 'PAY_JP_TRANS_MM_KANA') ||
4024 '"DD"' || fnd_message.get_string('PAY', 'PAY_JP_TRANS_DD_KANA') || '"';
4025 g_prompt_kana.other := fnd_message.get_string('PAY', 'PAY_JP_TRANS_OTHER_KANA');
4026 g_prompt_kana.count := fnd_message.get_string('PAY', 'PAY_JP_TRANS_COUNT_KANA');
4027 --
4028 --
4029 -- since april in 2009
4030 g_prompt_kanji.housing_loan_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_ITW');
4031 g_prompt_kanji.housing_res_date_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_RD_ITW');
4032 g_prompt_kanji.housing_loan_balance_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_BAL_ITW');
4033 g_prompt_kanji.housing_loan_type_wtm := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_TYPE_WTM');
4034 g_prompt_kanji.housing_count_wtm := fnd_message.get_string('PAY','PAY_JP_WIC_P_CNT_WTM');
4035 g_prompt_kanji.housing_res_date_wtm := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_RD_WTM');
4036 g_prompt_kanji.housing_loan_balance_wtm := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_BAL_WTM');
4037 g_prompt_kanji.np_prem_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_NP_ITW');
4038 g_prompt_kanji.housing_loan_type_gen_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_GEN_ITW');
4039 g_prompt_kanji.housing_loan_type_ext_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_EXT_ITW');
4040 g_prompt_kanji.housing_loan_type_etq_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_ETQ_ITW');
4041 --
4042 g_prompt_kana.housing_loan_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_ITW_K');
4043 g_prompt_kana.housing_res_date_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_RD_ITW_K');
4044 g_prompt_kana.housing_loan_balance_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_BAL_ITW_K');
4045 g_prompt_kana.housing_loan_type_wtm := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_TYPE_WTM_K');
4046 g_prompt_kana.housing_count_wtm := fnd_message.get_string('PAY','PAY_JP_WIC_P_CNT_WTM_K');
4047 g_prompt_kana.housing_res_date_wtm := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_RD_WTM_K');
4048 g_prompt_kana.housing_loan_balance_wtm := fnd_message.get_string('PAY','PAY_JP_WIC_P_HLD_BAL_WTM_K');
4049 g_prompt_kana.np_prem_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_NP_ITW_K');
4050 g_prompt_kana.housing_loan_type_gen_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_GEN_ITW_K');
4051 g_prompt_kana.housing_loan_type_ext_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_EXT_ITW_K');
4052 g_prompt_kana.housing_loan_type_etq_itw := fnd_message.get_string('PAY','PAY_JP_WIC_P_ETQ_ITW_K');
4053 --
4054 -- since 2011
4055 g_prompt_kanji.dpnt_junior := fnd_message.get_string('PAY','PAY_JP_WIC_P_JUNIOR');
4056 --
4057 g_prompt_kana.dpnt_junior := fnd_message.get_string('PAY','PAY_JP_WIC_P_JUNIOR_K');
4058 --
4059 g_taxable_income_exempt_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_AMT_AFTER_EMP_INCOME_DCT_RSLT', null, 'JP');
4060 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');
4061 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');
4062 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');
4063 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');
4064 -- 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');
4065 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');
4066 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');
4067 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');
4068 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');
4069 --
4070 g_net_annual_itax_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_NET_ANNUAL_TAX', null, 'JP');
4071 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');
4072 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');
4073 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');
4074 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');
4075 --
4076 g_total_income_exempt_asg_run := hr_jp_id_pkg.defined_balance_id('B_YEA_INCOME_EXM',
4077 '_ASG_RUN', null, 'JP');
4078 g_itax_adjustment_asg_ytd := hr_jp_id_pkg.defined_balance_id('B_YEA_TAX_PAY',
4079 '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
4080 -- g_itax_asg_ytd := hr_jp_id_pkg.defined_balance_id('B_YEA_WITHHOLD_ITX',
4081 -- '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
4082 --
4083 g_ins_prem_exempt_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_RSLT', null, 'JP');
4084 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');
4085 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');
4086 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');
4087 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');
4088 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');
4089 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');
4090 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');
4091 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');
4092 -- 2012 Withholding changes
4093 g_ins_prem_exempt_elm.old_life_ins_prem_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm.element_type_id, 'GEN_LIFE_INS_PREM');
4094
4095 g_ins_prem_exempt_elm_new.element_type_id := hr_jp_id_pkg.element_type_id('YEA_INS_PREM_SPOUSE_SP_EXM_RSLT2', null, 'JP');
4096 g_ins_prem_exempt_elm_new.new_life_ins_prem_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm_new.element_type_id, 'GEN_LIFE_INS_PREM_2012');
4097 g_ins_prem_exempt_elm_new.care_med_ins_prem_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm_new.element_type_id, 'CARE_INS_PREM_2012');
4098 g_ins_prem_exempt_elm_new.new_pen_ins_prem_iv := hr_jp_id_pkg.input_value_id(g_ins_prem_exempt_elm_new.element_type_id, 'INDIVIDUAL_PENSION_PREM_2012');
4099
4100 --
4101 g_dep_exempt_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_PROC', null, 'JP');
4102 g_dep_exempt_elm.disabled_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'DISABLE_TYPE');
4103 g_dep_exempt_elm.aged_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'ELDER_TYPE');
4104 g_dep_exempt_elm.widow_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'WIDOW_TYPE');
4105 g_dep_exempt_elm.working_student_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'WORKING_STUDENT_TYPE');
4106 g_dep_exempt_elm.spouse_type_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'SPOUSE_TYPE');
4107 g_dep_exempt_elm.spouse_disabled_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'SPOUSE_DISABLE_TYPE');
4108 g_dep_exempt_elm.num_deps_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_DEP');
4109 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');
4110 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');
4111 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');
4112 g_dep_exempt_elm.num_junior_deps_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_elm.element_type_id, 'NUM_OF_JUNIOR_DEP');
4113 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');
4114 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');
4115 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');
4116 --
4117 g_dep_exempt_result_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_DEP_EXM_TYPE_RSLT', null, 'JP');
4118 g_dep_exempt_result_elm.disabled_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'DISABLE_TYPE');
4119 g_dep_exempt_result_elm.aged_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'ELDER_TYPE');
4120 g_dep_exempt_result_elm.widow_iv := hr_jp_id_pkg.input_value_id(g_dep_exempt_result_elm.element_type_id, 'WIDOW_TYPE');
4121 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');
4122 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');
4123 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');
4124 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');
4125 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');
4126 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');
4127 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');
4128 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');
4129 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');
4130 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');
4131 --
4132 /*
4133 g_description_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', null, 'JP');
4134 g_description_elm.override_flag_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'OVERRIDE_FLAG');
4135 g_description_elm.description_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD');
4136 g_description_elm.mag_override_flag_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'OVERRIDE_FOR_FILE_FLAG');
4137 g_description_elm.mag_description_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD_FOR_FILE');
4138 --Added for ITAX REPORT Enhancement
4139 g_description_elm.description_2_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD2');
4140 g_description_elm.description_3_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD3');
4141 g_description_elm.description_4_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD4');
4142 g_description_elm.description_5_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD5');
4143 g_description_elm.mag_description_2_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD2_FOR_FILE');
4144 g_description_elm.mag_description_3_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD3_FOR_FILE');
4145 g_description_elm.mag_description_4_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD4_FOR_FILE');
4146 g_description_elm.mag_description_5_iv := hr_jp_id_pkg.input_value_id(g_description_elm.element_type_id, 'DESC_FIELD5_FOR_FILE');
4147 */
4148 g_desc_element_type_id := hr_jp_id_pkg.element_type_id('YEA_WITHHOLD_TAX_REPORT_INFO', null, 'JP');
4149 --
4150 g_itax_info_elm.element_type_id := hr_jp_id_pkg.element_type_id('COM_ITX_INFO', null, 'JP');
4151 g_itax_info_elm.foreigner_flag_iv := hr_jp_id_pkg.input_value_id(g_itax_info_elm.element_type_id, 'FOREIGNER_FLAG');
4152 --
4153 g_prev_job_elm := hr_jp_id_pkg.element_type_id('YEA_PREV_EMP_INFO', null, 'JP');
4154 --
4155 g_housing_tax_reduction_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_HOUSING_LOAN_TAX_CREDIT', null, 'JP');
4156 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');
4157 --
4158 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');
4159 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');
4160 --
4161 --
4162 g_housing_loan_info_elm.element_type_id := hr_jp_id_pkg.element_type_id('YEA_HOUSING_LOAN_INFO',null,'JP');
4163 g_housing_loan_info_elm.res_date_iv := hr_jp_id_pkg.input_value_id(g_housing_loan_info_elm.element_type_id,'RES_DATE');
4164 g_housing_loan_info_elm.loan_type_iv := hr_jp_id_pkg.input_value_id(g_housing_loan_info_elm.element_type_id,'LOAN_TYPE');
4165 g_housing_loan_info_elm.loan_balance_iv := hr_jp_id_pkg.input_value_id(g_housing_loan_info_elm.element_type_id,'LOAN_BALANCE');
4166 --
4167 -- hr_utility.trace_on('F', 'TTAGAWA');
4168 end pay_jp_wic_pkg;
4169