[Home] [Help]
PACKAGE BODY: APPS.PER_JP_CTR_UTILITY_PKG
Source
1 package body per_jp_ctr_utility_pkg as
2 /* $Header: pejpctru.pkb 120.5 2011/11/02 20:29:39 keyazawa ship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'per_jp_ctr_utility_pkg.';
7 c_default_itax_dpnt_ref_type constant hr_lookups.lookup_code%type := 'CTR_EE';
8 c_kou_information_type constant per_contact_extra_info_f.information_type%TYPE := 'JP_ITAX_DEPENDENT';
9 c_otsu_information_type constant per_contact_extra_info_f.information_type%TYPE := 'JP_ITAX_DEPENDENT_ON_OTHER_PAY';
10 c_husband_kanji constant hr_lookups.meaning%type := fnd_message.get_string('PAY', 'PAY_JP_HUSBAND');
11 c_husband_kana constant hr_lookups.meaning%type := hr_jp_standard_pkg.upper_kana(fnd_message.get_string('PAY', 'PAY_JP_HUSBAND_KANA'));
12 c_wife_kanji constant hr_lookups.meaning%type := fnd_message.get_string('PAY', 'PAY_JP_WIFE');
13 c_wife_kana constant hr_lookups.meaning%type := hr_jp_standard_pkg.upper_kana(fnd_message.get_string('PAY', 'PAY_JP_WIFE_KANA'));
14 --
15 -- Global Variables
16 -- Cache Information
17 --
18 g_itax_dpnt_rec t_itax_dpnt_rec;
19 --
20 type t_bg_itax_dpnt_rec is record(
21 business_group_id number,
22 ref_type hr_lookups.lookup_code%type);
23 g_bg_itax_dpnt_rec t_bg_itax_dpnt_rec;
24 -- ----------------------------------------------------------------------------
25 -- |------------------------< bg_itax_dpnt_ref_type >-------------------------|
26 -- ----------------------------------------------------------------------------
27 function bg_itax_dpnt_ref_type(p_business_group_id in number) return varchar2
28 is
29 cursor csr_bg is
30 select org_information2
31 from hr_organization_information
32 where organization_id = p_business_group_id
33 and org_information_context = 'JP_BUSINESS_GROUP_INFO';
34 begin
35 --
36 -- Use cache if available
37 --
38 if (g_bg_itax_dpnt_rec.business_group_id is null)
39 or (g_bg_itax_dpnt_rec.business_group_id <> p_business_group_id) then
40 g_bg_itax_dpnt_rec.business_group_id := p_business_group_id;
41 --
42 open csr_bg;
43 fetch csr_bg into g_bg_itax_dpnt_rec.ref_type;
44 if (csr_bg%notfound) or (g_bg_itax_dpnt_rec.ref_type is null) then
45 g_bg_itax_dpnt_rec.ref_type := c_default_itax_dpnt_ref_type;
46 end if;
47 close csr_bg;
48 end if;
49 --
50 return g_bg_itax_dpnt_rec.ref_type;
51 end bg_itax_dpnt_ref_type;
52 -- ----------------------------------------------------------------------------
53 -- |--------------------------< get_itax_dpnt_info >--------------------------|
54 -- ----------------------------------------------------------------------------
55 --
56 -- Procedure to derive dependent information from
57 -- PER_CONTACT_RELATIONSHIPS
58 -- PER_CONTACT_EXTRA_INFO_F
59 --
60 -- ----------------------------------------------------------------------------
61 procedure get_itax_dpnt_info(
62 p_assignment_id in number,
63 p_itax_type in varchar2,
64 p_effective_date in date,
65 p_itax_dpnt_rec out nocopy t_itax_dpnt_rec,
66 p_use_cache in boolean default TRUE)
67 is
68 --
69 l_proc varchar2(61) := c_package || 'get_itax_dpnt_info';
70 l_effective_date date := p_effective_date;
71 l_soy date;
72 l_eoy date;
73 l_information_type per_contact_extra_info_f.information_type%TYPE;
74 l_index number := 0;
75 --
76 -- Note that the age used below is January 1st in the next calendar year.
77 -- Normally, effective date used is date paid, but if contact person is deceased,
78 -- date_of_death is used as effective date instead.
79 --
80 -- cursor csr_dpnt(p_information_type varchar2) is
81 -- select /*+ ORDERED */
82 ---- v.effective_date,
83 ---- v.date_of_birth,
84 ---- v.date_of_death,
85 -- v.contact_type,
86 -- cei.contact_extra_info_id,
87 -- /* Note to add 1 day to effective_date(This is JP legal age calculation rule). */
88 -- trunc(months_between(
89 -- decode(v.effective_date, v.date_of_death, v.date_of_death, l_eoy) + 1,
90 -- v.date_of_birth) / 12) AGE,
91 -- --
92 -- -- The following 2 flags are available only for "KOU",
93 -- -- not available for "OTSU".
94 -- --
95 -- decode(cei.information_type, c_kou_information_type, cei.cei_information1, null) AGED_DPNT_PARENTS_LT_TYPE,
96 -- decode(cei.information_type, c_kou_information_type, cei.cei_information6, null) DSBL_TYPE,
97 -- decode(v.contact_type, 'S', decode(v.sex, 'F', c_wife_kanji, c_husband_kanji), hrl1.meaning) D_CONTACT_TYPE_KANJI,
98 -- decode(v.contact_type, 'S', decode(v.sex, 'F', c_wife_kana, c_husband_kana), hrl2.meaning) D_CONTACT_TYPE_KANA,
99 -- v.last_name_kanji,
100 -- v.first_name_kanji,
101 -- v.last_name_kana,
102 -- v.first_name_kana
103 -- from (
104 -- select ctr.contact_relationship_id,
105 -- ctr.contact_type,
106 -- ctr.date_start,
107 -- ctr.date_end,
108 -- per.sex,
109 -- per.per_information18 LAST_NAME_KANJI,
110 -- per.per_information19 FIRST_NAME_KANJI,
111 -- per.last_name LAST_NAME_KANA,
112 -- per.first_name FIRST_NAME_KANA,
113 -- per.date_of_birth,
114 -- per.date_of_death,
115 -- nvl(least(per.date_of_death, l_effective_date), l_effective_date) EFFECTIVE_DATE,
116 -- to_number(ctr.cont_information2) SEQUENCE
117 -- from per_all_assignments_f asg,
118 -- per_contact_relationships ctr,
119 -- per_all_people_f per
120 -- where asg.assignment_id = p_assignment_id
121 -- and l_effective_date
122 -- between asg.effective_start_date and asg.effective_end_date
123 -- and ctr.person_id = asg.person_id
124 -- /* Only primary contact relationship */
125 -- and ctr.cont_information1 = 'Y'
126 -- /* We need to take deceased case into consideration. When deceased,
127 -- we need to get information as of date_of_death, not effective_date.
128 -- Here only narrows date range within calendar year. */
129 -- and nvl(ctr.date_end, l_soy) >= l_soy
130 -- and nvl(ctr.date_start, l_effective_date) <= l_effective_date
131 -- and per.person_id = ctr.contact_person_id
132 -- and (
133 -- (
134 -- l_effective_date
135 -- between per.effective_start_date and per.effective_end_date
136 -- )
137 -- or (
138 -- per.effective_start_date = per.start_date
139 -- and not exists(
140 -- select null
141 -- from per_all_people_f per2
142 -- where per2.person_id = per.person_id
143 -- and l_effective_date
144 -- between per2.effective_start_date and per2.effective_end_date)
145 -- )
146 -- )
147 -- /* Deceased person is available until the end of year in which the effective_date falls. */
148 -- and nvl(trunc(per.date_of_death, 'YYYY'), l_soy) >= l_soy
149 -- ) v,
150 -- per_contact_extra_info_f cei,
151 -- hr_lookups hrl1,
152 -- hr_lookups hrl2
153 -- /* Here again narrows CTR date range either date_of_death or effective_date. */
154 -- where v.effective_date
155 -- between nvl(v.date_start, v.effective_date) and nvl(v.date_end, v.effective_date)
156 -- and cei.contact_relationship_id(+) = v.contact_relationship_id
157 -- and cei.information_type(+) = p_information_type
158 -- and v.effective_date
159 -- between cei.effective_start_date(+) and cei.effective_end_date(+)
160 -- /* We need to get spouse CTR information even the CTR does not have CEI information. */
161 -- and ((v.contact_type = 'S') or (cei.information_type is not null))
165 -- and hrl2.lookup_code(+) = hrl1.lookup_code
162 -- and hrl1.lookup_type = 'CONTACT'
163 -- and hrl1.lookup_code = v.contact_type
164 -- and hrl2.lookup_type(+) = 'JP_CONTACT_KANA'
166 -- order by
167 -- decode(v.contact_type, 'S', 1, 2),
168 -- v.sequence,
169 -- v.date_of_birth,
170 -- decode(v.sex, 'F', 2, 1),
171 -- v.last_name_kana,
172 -- v.first_name_kana;
173 --
174 cursor csr_dpnt(p_information_type varchar2)
175 is
176 select /*+ ORDERED PUSH_SUBQ */
177 ctr.contact_type,
178 cei.contact_extra_info_id,
179 /* Note to add 1 day to effective_date(This is JP legal age calculation rule). */
180 trunc(months_between(
181 to_date(decode(to_char(nvl(least(per.date_of_death, l_effective_date), l_effective_date),'YYYY/MM/DD'), to_char(per.date_of_death,'YYYY/MM/DD'), to_char(per.date_of_death,'YYYY/MM/DD'), to_char(l_eoy,'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
182 per.date_of_birth) / 12) AGE,
183 --
184 -- The following 2 flags are available only for "KOU",
185 -- not available for "OTSU".
186 --
187 decode(cei.information_type, c_kou_information_type, cei.cei_information1, null) AGED_DPNT_PARENTS_LT_TYPE,
188 decode(cei.information_type, c_kou_information_type, cei.cei_information6, null) DSBL_TYPE,
189 decode(ctr.contact_type, 'S', decode(per.sex, 'F', c_wife_kanji, c_husband_kanji), hrl1.meaning) D_CONTACT_TYPE_KANJI,
190 decode(ctr.contact_type, 'S', decode(per.sex, 'F', c_wife_kana, c_husband_kana), hrl2.meaning) D_CONTACT_TYPE_KANA,
191 per.per_information18 LAST_NAME_KANJI,
192 per.per_information19 FIRST_NAME_KANJI,
193 per.last_name LAST_NAME_KANA,
194 per.first_name FIRST_NAME_KANA
195 from per_all_assignments_f asg,
196 per_contact_relationships ctr,
197 per_contact_extra_info_f cei,
198 per_all_people_f per,
199 hr_lookups hrl1,
200 hr_lookups hrl2
201 where asg.assignment_id = p_assignment_id
202 and l_effective_date
203 between asg.effective_start_date and asg.effective_end_date
204 and ctr.person_id = asg.person_id
205 and cei.contact_relationship_id(+) = ctr.contact_relationship_id
206 and cei.information_type(+) = p_information_type
207 and l_effective_date
208 between cei.effective_start_date(+) and cei.effective_end_date(+)
209 /* We need to get "Spouse" CTR information even
210 1) CTR does not have CEI information.
211 2) CTR does not exist as of effective_date. */
212 and (cei.information_type is not null
213 or (ctr.contact_type = 'S'
214 and l_effective_date
215 between nvl(ctr.date_start, l_effective_date) and nvl(ctr.date_end, l_effective_date)))
216 and per.person_id = ctr.contact_person_id
217 and ((l_effective_date
218 between per.effective_start_date and per.effective_end_date)
219 or (per.effective_start_date = per.start_date
220 and not exists(
221 select null
222 from per_all_people_f per2
223 where per2.person_id = per.person_id
224 and l_effective_date
225 between per2.effective_start_date and per2.effective_end_date)))
226 and hrl1.lookup_type = 'CONTACT'
227 and hrl1.lookup_code = ctr.contact_type
228 and hrl2.lookup_type(+) = 'JP_CONTACT_KANA'
229 and hrl2.lookup_code(+) = hrl1.lookup_code
230 order by
231 decode(ctr.contact_type, 'S', 1, 2),
232 to_number(ctr.cont_information2),
233 per.date_of_birth,
234 decode(per.sex, 'F', 2, 1),
235 per.last_name,
236 per.first_name;
237 --
238 begin
239 --
240 hr_utility.set_location('Entering : ' || l_proc, 10);
241 --
242 -- If p_effective_date is null, use session_date as effective_date.
243 -- Never cache effective_date into package global variables
244 -- which will cause inconsistency when retropay is run.
245 --
246 if l_effective_date is null then
247 select effective_date
248 into l_effective_date
249 from fnd_sessions
250 where session_id = userenv('sessionid');
251 end if;
252 --
253 l_soy := trunc(l_effective_date, 'YYYY');
254 l_eoy := add_months(l_soy, 12) - 1;
255 --
256 --hr_utility.trace('L_EFFECTIVE_DATE : ' || fnd_date.date_to_chardate(l_effective_date));
257 --hr_utility.trace('L_SOY : ' || fnd_date.date_to_chardate(l_soy));
258 --hr_utility.trace('L_EOY : ' || fnd_date.date_to_chardate(l_eoy));
259 --
260 -- If cache is available, use cache.
261 --
262 if p_use_cache
263 and g_itax_dpnt_rec.assignment_id = p_assignment_id
264 and g_itax_dpnt_rec.itax_type = p_itax_type
265 and g_itax_dpnt_rec.effective_date = l_effective_date then
266 --
267 hr_utility.trace('Cache is available.');
268 --
269 p_itax_dpnt_rec := g_itax_dpnt_rec;
270 --
271 -- Cache is not available, derive directly from CTR and CEI.
272 --
273 else
274 --
275 hr_utility.trace('Cache is NOT available. Derive from CTR and CEI.');
276 --
277 -- Initialization for "p_itax_dpnt_rec" is automatically done by "OUT" parameter.
278 --
279 p_itax_dpnt_rec.assignment_id := p_assignment_id;
283 -- Income Tax Type "KOU"
280 p_itax_dpnt_rec.itax_type := p_itax_type;
281 p_itax_dpnt_rec.effective_date := l_effective_date;
282 --
284 --
285 if p_itax_type in ('M_KOU', 'D_KOU', 'M_OTSU', 'D_OTSU') then
286 --
287 hr_utility.trace('Taxation Type : ' || p_itax_type);
288 --
289 if p_itax_type in ('M_KOU', 'D_KOU') then
290 l_information_type := c_kou_information_type;
291 else
292 l_information_type := c_otsu_information_type;
293 end if;
294 --
295 for l_dpnt in csr_dpnt(l_information_type) loop
296 --
297 hr_utility.trace('**********');
298 --hr_utility.trace('Effective Date : ' || fnd_date.date_to_chardate(l_dpnt.effective_date));
299 --hr_utility.trace('Date of Birth : ' || fnd_date.date_to_chardate(l_dpnt.date_of_birth));
300 --hr_utility.trace('Date of Death : ' || fnd_date.date_to_chardate(l_dpnt.date_of_death));
301 hr_utility.trace('Contact Type : ' || l_dpnt.contact_type);
302 hr_utility.trace('Last Name : ' || l_dpnt.first_name_kana);
303 hr_utility.trace('Age : ' || l_dpnt.age);
304 --
305 --
306 -- If the contact_type is "Spouse"
307 --
308 if l_dpnt.contact_type = 'S' then
309 --
310 -- When there are multiple spouses, return warning status.
311 --
312 if p_itax_dpnt_rec.spouse_type <> '0' then
313 p_itax_dpnt_rec.multiple_spouses_warning := true;
314 else
315 --
316 -- If the contact person is dependent
317 --
318 if l_dpnt.contact_extra_info_id is not null then
319 --
320 l_index := l_index + 1;
321 --
322 p_itax_dpnt_rec.contact_type_tbl(l_index) := l_dpnt.contact_type;
323 p_itax_dpnt_rec.d_contact_type_kanji_tbl(l_index) := l_dpnt.d_contact_type_kanji;
324 p_itax_dpnt_rec.d_contact_type_kana_tbl(l_index) := l_dpnt.d_contact_type_kana;
325 p_itax_dpnt_rec.last_name_kanji_tbl(l_index) := l_dpnt.last_name_kanji;
326 p_itax_dpnt_rec.first_name_kanji_tbl(l_index) := l_dpnt.first_name_kanji;
327 p_itax_dpnt_rec.last_name_kana_tbl(l_index) := l_dpnt.last_name_kana;
328 p_itax_dpnt_rec.first_name_kana_tbl(l_index) := l_dpnt.first_name_kana;
329 p_itax_dpnt_rec.junior_tbl(l_index) := null;
330 --
331 -- Spouse Age Check
332 --
333 if l_dpnt.age >= 70 then
334 p_itax_dpnt_rec.spouse_type := '3';
335 else
336 p_itax_dpnt_rec.spouse_type := '2';
337 end if;
338 --
339 -- Disabled Type Check
340 --
341 if l_dpnt.dsbl_type = '10' then
342 p_itax_dpnt_rec.dpnt_spouse_dsbl_type := '1';
343 elsif l_dpnt.dsbl_type = '20' then
344 p_itax_dpnt_rec.dpnt_spouse_dsbl_type := '2';
345 elsif l_dpnt.dsbl_type = '30' then
346 p_itax_dpnt_rec.dpnt_spouse_dsbl_type := '3';
347 end if;
348 --
349 -- In case just Spouse exists.
350 -- Note this spouse is just spouse, not deductible spouse.
351 --
352 else
353 p_itax_dpnt_rec.spouse_type := '1';
354 p_itax_dpnt_rec.dpnt_spouse_dsbl_type := '0';
355 end if;
356 --
357 end if;
358 --
359 -- If the contact_type is not "Spouse"
360 --
361 else
362 --
363 l_index := l_index + 1;
364 --
365 p_itax_dpnt_rec.contact_type_tbl(l_index) := l_dpnt.contact_type;
366 p_itax_dpnt_rec.d_contact_type_kanji_tbl(l_index) := l_dpnt.d_contact_type_kanji;
367 p_itax_dpnt_rec.d_contact_type_kana_tbl(l_index) := l_dpnt.d_contact_type_kana;
368 p_itax_dpnt_rec.last_name_kanji_tbl(l_index) := l_dpnt.last_name_kanji;
369 p_itax_dpnt_rec.first_name_kanji_tbl(l_index) := l_dpnt.first_name_kanji;
370 p_itax_dpnt_rec.last_name_kana_tbl(l_index) := l_dpnt.last_name_kana;
371 p_itax_dpnt_rec.first_name_kana_tbl(l_index) := l_dpnt.first_name_kana;
372 p_itax_dpnt_rec.junior_tbl(l_index) := null;
373 --
374 --
375 -- Dependent Age Check
376 --
377 if to_number(to_char(l_effective_date,'YYYY')) >= 2011 then
378 --
379 -- include date_of_birth is null
380 if l_dpnt.age >= 16
381 or l_dpnt.age is null then
382 --
383 p_itax_dpnt_rec.dpnts := p_itax_dpnt_rec.dpnts + 1;
384 --
385 end if;
386 --
387 -- abolish minor dpnts, change range of young dpnts from 16 to 19
388 if l_dpnt.age between 19 and 22 then
389 --
390 p_itax_dpnt_rec.young_dpnts := p_itax_dpnt_rec.young_dpnts + 1;
391 --
392 end if;
393 --
394 else
395 --
396 p_itax_dpnt_rec.dpnts := p_itax_dpnt_rec.dpnts + 1;
397 --
398 if l_dpnt.age between 16 and 22 then
399 --
400 p_itax_dpnt_rec.young_dpnts := p_itax_dpnt_rec.young_dpnts + 1;
401 --
402 end if;
403 --
404 end if;
405 --
406 if l_dpnt.age >= 70 then
407 --
408 -- Aged Dependent Parents and Aged Dependents are mutually exclusive.
409 --
410 if l_dpnt.aged_dpnt_parents_lt_type = '10' then
411 p_itax_dpnt_rec.aged_dpnt_parents_lt := p_itax_dpnt_rec.aged_dpnt_parents_lt + 1;
412 else
413 p_itax_dpnt_rec.aged_dpnts := p_itax_dpnt_rec.aged_dpnts + 1;
414 end if;
415 --
416 -- include disabled dependent
417 -- exclude otsu tax type
418 elsif l_dpnt.age <= 15
419 and p_itax_type in ('M_KOU', 'D_KOU') then
420 --
421 p_itax_dpnt_rec.minor_dpnts := p_itax_dpnt_rec.minor_dpnts + 1;
422 p_itax_dpnt_rec.junior_tbl(l_index) := 'Y';
423 --
424 end if;
425 --
426 --
427 -- Disabled Type Check (no impact after 2011)
428 --
429 if l_dpnt.dsbl_type = '10' then
433 elsif l_dpnt.dsbl_type = '30' then
430 p_itax_dpnt_rec.dsbl_dpnts := p_itax_dpnt_rec.dsbl_dpnts + 1;
431 elsif l_dpnt.dsbl_type = '20' then
432 p_itax_dpnt_rec.svr_dsbl_dpnts := p_itax_dpnt_rec.svr_dsbl_dpnts + 1;
434 p_itax_dpnt_rec.svr_dsbl_dpnts_lt := p_itax_dpnt_rec.svr_dsbl_dpnts_lt + 1;
435 end if;
436 --
437 end if;
438 --
439 end loop;
440 --
441 end if;
442 --
443 -- Save into cache to decrease DB access when input parameters are the same.
444 -- These cache will be used if this function is called with the same values
445 -- for input parameters.
446 --
447 g_itax_dpnt_rec := p_itax_dpnt_rec;
448 --
449 hr_utility.trace('**********');
450 hr_utility.trace('spouse_type : ' || p_itax_dpnt_rec.spouse_type);
451 hr_utility.trace('dpnt_spouse_dsbl_type : ' || p_itax_dpnt_rec.dpnt_spouse_dsbl_type);
452 hr_utility.trace('dpnts : ' || to_char(p_itax_dpnt_rec.dpnts));
453 hr_utility.trace('aged_dpnts : ' || to_char(p_itax_dpnt_rec.aged_dpnts));
454 hr_utility.trace('aged_dpnt_parents_lt : ' || to_char(p_itax_dpnt_rec.aged_dpnt_parents_lt));
455 hr_utility.trace('young_dpnts : ' || to_char(p_itax_dpnt_rec.young_dpnts));
456 hr_utility.trace('minor_dpnts : ' || to_char(p_itax_dpnt_rec.minor_dpnts));
457 hr_utility.trace('dsbl_dpnts : ' || to_char(p_itax_dpnt_rec.dsbl_dpnts));
458 hr_utility.trace('svr_dsbl_dpnts : ' || to_char(p_itax_dpnt_rec.svr_dsbl_dpnts));
459 hr_utility.trace('svr_dsbl_dpnts_lt : ' || to_char(p_itax_dpnt_rec.svr_dsbl_dpnts_lt));
460 --
461 if p_itax_dpnt_rec.multiple_spouses_warning then
462 hr_utility.trace('multiple_spouses_warning : TRUE');
463 else
464 hr_utility.trace('multiple_spouses_warning : FALSE');
465 end if;
466 --
467 end if;
468 --
469 hr_utility.set_location('Leaving : ' || l_proc, 20);
470 --
471 end get_itax_dpnt_info;
472 -- ----------------------------------------------------------------------------
473 -- |--------------< Interface Functions for get_itax_dpnt_info >--------------|
474 -- ----------------------------------------------------------------------------
475 procedure get_itax_dpnt_info(
476 p_assignment_id in number,
477 p_itax_type in varchar2,
478 p_effective_date in date,
479 p_spouse_type out nocopy varchar2,
480 p_dpnt_spouse_dsbl_type out nocopy varchar2,
481 p_dpnts out nocopy number,
482 p_aged_dpnts out nocopy number,
483 p_aged_dpnt_parents_lt out nocopy number,
484 p_young_dpnts out nocopy number,
485 p_minor_dpnts out nocopy number,
486 p_dsbl_dpnts out nocopy number,
487 p_svr_dsbl_dpnts out nocopy number,
488 p_svr_dsbl_dpnts_lt out nocopy number,
489 p_multiple_spouses_warning out nocopy boolean,
490 p_use_cache in boolean default TRUE)
491 is
492 l_itax_dpnt_rec t_itax_dpnt_rec;
493 begin
494 get_itax_dpnt_info(
495 p_assignment_id => p_assignment_id,
496 p_effective_date => p_effective_date,
497 p_itax_type => p_itax_type,
498 p_itax_dpnt_rec => l_itax_dpnt_rec,
499 p_use_cache => p_use_cache);
500 --
501 p_spouse_type := l_itax_dpnt_rec.spouse_type;
502 p_dpnt_spouse_dsbl_type := l_itax_dpnt_rec.dpnt_spouse_dsbl_type;
503 p_dpnts := l_itax_dpnt_rec.dpnts;
504 p_aged_dpnts := l_itax_dpnt_rec.aged_dpnts;
505 p_aged_dpnt_parents_lt := l_itax_dpnt_rec.aged_dpnt_parents_lt;
506 p_young_dpnts := l_itax_dpnt_rec.young_dpnts;
507 p_minor_dpnts := l_itax_dpnt_rec.minor_dpnts;
508 p_dsbl_dpnts := l_itax_dpnt_rec.dsbl_dpnts;
509 p_svr_dsbl_dpnts := l_itax_dpnt_rec.svr_dsbl_dpnts;
510 p_svr_dsbl_dpnts_lt := l_itax_dpnt_rec.svr_dsbl_dpnts_lt;
511 p_multiple_spouses_warning := l_itax_dpnt_rec.multiple_spouses_warning;
512 end get_itax_dpnt_info;
513 --
514 procedure get_itax_dpnt_info(
515 p_assignment_id in number,
516 p_itax_type in varchar2,
517 p_effective_date in date,
518 p_dpnt_spouse_type out nocopy varchar2,
519 p_dpnt_spouse_dsbl_type out nocopy varchar2,
520 p_dpnts out nocopy number,
521 p_aged_dpnts out nocopy number,
522 p_cohab_aged_asc_dpnts out nocopy number,
523 p_major_dpnts out nocopy number,
524 p_minor_dpnts out nocopy number,
525 p_dsbl_dpnts out nocopy number,
526 p_svr_dsbl_dpnts out nocopy number,
527 p_cohab_svr_dsbl_dpnts out nocopy number,
528 p_multiple_spouses_warning out nocopy boolean,
529 p_use_cache in boolean default TRUE)
530 is
531 l_itax_dpnt_rec t_itax_dpnt_rec;
532 begin
533 get_itax_dpnt_info(
534 p_assignment_id => p_assignment_id,
535 p_effective_date => p_effective_date,
536 p_itax_type => p_itax_type,
537 p_itax_dpnt_rec => l_itax_dpnt_rec,
538 p_use_cache => p_use_cache);
539 --
540 p_dpnt_spouse_type := l_itax_dpnt_rec.spouse_type;
541 p_dpnt_spouse_dsbl_type := l_itax_dpnt_rec.dpnt_spouse_dsbl_type;
542 p_dpnts := l_itax_dpnt_rec.dpnts;
543 p_aged_dpnts := l_itax_dpnt_rec.aged_dpnts;
544 p_cohab_aged_asc_dpnts := l_itax_dpnt_rec.aged_dpnt_parents_lt;
545 p_major_dpnts := l_itax_dpnt_rec.young_dpnts;
546 p_minor_dpnts := l_itax_dpnt_rec.minor_dpnts;
547 p_dsbl_dpnts := l_itax_dpnt_rec.dsbl_dpnts;
548 p_svr_dsbl_dpnts := l_itax_dpnt_rec.svr_dsbl_dpnts;
549 p_cohab_svr_dsbl_dpnts := l_itax_dpnt_rec.svr_dsbl_dpnts_lt;
550 p_multiple_spouses_warning := l_itax_dpnt_rec.multiple_spouses_warning;
551 end get_itax_dpnt_info;
552 --
553 function get_itax_spouse_type(
554 p_assignment_id in number,
555 p_itax_type in varchar2,
556 p_effective_date in date default null) return varchar2
557 is
558 l_itax_dpnt_rec t_itax_dpnt_rec;
559 begin
560 get_itax_dpnt_info(
561 p_assignment_id => p_assignment_id,
562 p_effective_date => p_effective_date,
563 p_itax_type => p_itax_type,
564 p_itax_dpnt_rec => l_itax_dpnt_rec);
565 --
566 return l_itax_dpnt_rec.spouse_type;
567 end get_itax_spouse_type;
568 --
569 function get_itax_dpnt_spouse_type(
570 p_assignment_id in number,
571 p_itax_type in varchar2,
572 p_effective_date in date default null) return varchar2
573 is
574 l_itax_dpnt_rec t_itax_dpnt_rec;
575 begin
576 get_itax_dpnt_info(
577 p_assignment_id => p_assignment_id,
578 p_effective_date => p_effective_date,
579 p_itax_type => p_itax_type,
580 p_itax_dpnt_rec => l_itax_dpnt_rec);
581 --
582 return l_itax_dpnt_rec.spouse_type;
583 end get_itax_dpnt_spouse_type;
584 --
585 function get_itax_dpnt_spouse_dsbl_type(
586 p_assignment_id in number,
587 p_itax_type in varchar2,
588 p_effective_date in date default null) return varchar2
589 is
590 l_itax_dpnt_rec t_itax_dpnt_rec;
591 begin
592 get_itax_dpnt_info(
593 p_assignment_id => p_assignment_id,
594 p_effective_date => p_effective_date,
595 p_itax_type => p_itax_type,
596 p_itax_dpnt_rec => l_itax_dpnt_rec);
597 --
598 return l_itax_dpnt_rec.dpnt_spouse_dsbl_type;
599 end get_itax_dpnt_spouse_dsbl_type;
600 --
601 function get_itax_dpnts(
602 p_assignment_id in number,
603 p_itax_type in varchar2,
604 p_effective_date in date default null) return varchar2
605 is
606 l_itax_dpnt_rec t_itax_dpnt_rec;
607 begin
608 get_itax_dpnt_info(
609 p_assignment_id => p_assignment_id,
610 p_effective_date => p_effective_date,
611 p_itax_type => p_itax_type,
612 p_itax_dpnt_rec => l_itax_dpnt_rec);
613 --
614 return l_itax_dpnt_rec.dpnts;
615 end get_itax_dpnts;
616 --
617 function get_itax_aged_dpnts(
618 p_assignment_id in number,
619 p_itax_type in varchar2,
620 p_effective_date in date default null) return varchar2
621 is
622 l_itax_dpnt_rec t_itax_dpnt_rec;
623 begin
624 get_itax_dpnt_info(
625 p_assignment_id => p_assignment_id,
626 p_effective_date => p_effective_date,
627 p_itax_type => p_itax_type,
628 p_itax_dpnt_rec => l_itax_dpnt_rec);
629 --
630 return l_itax_dpnt_rec.aged_dpnts;
631 end get_itax_aged_dpnts;
632 --
633 function get_itax_cohab_aged_asc_dpnts(
634 p_assignment_id in number,
635 p_itax_type in varchar2,
636 p_effective_date in date default null) return varchar2
637 is
638 l_itax_dpnt_rec t_itax_dpnt_rec;
639 begin
640 get_itax_dpnt_info(
641 p_assignment_id => p_assignment_id,
642 p_effective_date => p_effective_date,
643 p_itax_type => p_itax_type,
644 p_itax_dpnt_rec => l_itax_dpnt_rec);
645 --
646 return l_itax_dpnt_rec.aged_dpnt_parents_lt;
647 end get_itax_cohab_aged_asc_dpnts;
648 --
649 function get_itax_aged_dpnt_parents_lt(
650 p_assignment_id in number,
651 p_itax_type in varchar2,
652 p_effective_date in date default null) return varchar2
653 is
654 l_itax_dpnt_rec t_itax_dpnt_rec;
655 begin
656 get_itax_dpnt_info(
657 p_assignment_id => p_assignment_id,
658 p_effective_date => p_effective_date,
659 p_itax_type => p_itax_type,
660 p_itax_dpnt_rec => l_itax_dpnt_rec);
661 --
662 return l_itax_dpnt_rec.aged_dpnt_parents_lt;
663 end get_itax_aged_dpnt_parents_lt;
664 --
665 function get_itax_young_dpnts(
666 p_assignment_id in number,
667 p_itax_type in varchar2,
668 p_effective_date in date default null) return varchar2
669 is
670 l_itax_dpnt_rec t_itax_dpnt_rec;
671 begin
672 get_itax_dpnt_info(
673 p_assignment_id => p_assignment_id,
674 p_effective_date => p_effective_date,
675 p_itax_type => p_itax_type,
676 p_itax_dpnt_rec => l_itax_dpnt_rec);
677 --
678 return l_itax_dpnt_rec.young_dpnts;
679 end get_itax_young_dpnts;
680 --
681 function get_itax_major_dpnts(
682 p_assignment_id in number,
683 p_itax_type in varchar2,
684 p_effective_date in date default null) return varchar2
685 is
686 l_itax_dpnt_rec t_itax_dpnt_rec;
687 begin
688 get_itax_dpnt_info(
689 p_assignment_id => p_assignment_id,
690 p_effective_date => p_effective_date,
691 p_itax_type => p_itax_type,
692 p_itax_dpnt_rec => l_itax_dpnt_rec);
693 --
694 return l_itax_dpnt_rec.young_dpnts;
695 end get_itax_major_dpnts;
696 --
697 function get_itax_minor_dpnts(
698 p_assignment_id in number,
699 p_itax_type in varchar2,
700 p_effective_date in date default null) return varchar2
701 is
702 l_itax_dpnt_rec t_itax_dpnt_rec;
703 begin
704 get_itax_dpnt_info(
705 p_assignment_id => p_assignment_id,
706 p_effective_date => p_effective_date,
707 p_itax_type => p_itax_type,
708 p_itax_dpnt_rec => l_itax_dpnt_rec);
709 --
710 return l_itax_dpnt_rec.minor_dpnts;
711 end get_itax_minor_dpnts;
712 --
713 function get_itax_dsbl_dpnts(
714 p_assignment_id in number,
715 p_itax_type in varchar2,
716 p_effective_date in date default null) return varchar2
717 is
718 l_itax_dpnt_rec t_itax_dpnt_rec;
719 begin
720 get_itax_dpnt_info(
721 p_assignment_id => p_assignment_id,
722 p_effective_date => p_effective_date,
723 p_itax_type => p_itax_type,
724 p_itax_dpnt_rec => l_itax_dpnt_rec);
725 --
726 return l_itax_dpnt_rec.dsbl_dpnts;
727 end get_itax_dsbl_dpnts;
728 --
729 function get_itax_svr_dsbl_dpnts(
730 p_assignment_id in number,
731 p_itax_type in varchar2,
732 p_effective_date in date default null) return varchar2
733 is
734 l_itax_dpnt_rec t_itax_dpnt_rec;
735 begin
736 get_itax_dpnt_info(
737 p_assignment_id => p_assignment_id,
738 p_effective_date => p_effective_date,
739 p_itax_type => p_itax_type,
740 p_itax_dpnt_rec => l_itax_dpnt_rec);
741 --
742 return l_itax_dpnt_rec.svr_dsbl_dpnts;
743 end get_itax_svr_dsbl_dpnts;
744 --
745 function get_itax_svr_dsbl_dpnts_lt(
746 p_assignment_id in number,
747 p_itax_type in varchar2,
748 p_effective_date in date default null) return varchar2
749 is
750 l_itax_dpnt_rec t_itax_dpnt_rec;
751 begin
752 get_itax_dpnt_info(
753 p_assignment_id => p_assignment_id,
754 p_effective_date => p_effective_date,
755 p_itax_type => p_itax_type,
756 p_itax_dpnt_rec => l_itax_dpnt_rec);
757 --
758 return l_itax_dpnt_rec.svr_dsbl_dpnts_lt;
759 end get_itax_svr_dsbl_dpnts_lt;
760 --
761 function get_itax_cohab_svr_dsbl_dpnts(
762 p_assignment_id in number,
763 p_itax_type in varchar2,
764 p_effective_date in date default null) return varchar2
765 is
766 l_itax_dpnt_rec t_itax_dpnt_rec;
767 begin
768 get_itax_dpnt_info(
769 p_assignment_id => p_assignment_id,
770 p_effective_date => p_effective_date,
771 p_itax_type => p_itax_type,
772 p_itax_dpnt_rec => l_itax_dpnt_rec);
773 --
774 return l_itax_dpnt_rec.svr_dsbl_dpnts_lt;
775 end get_itax_cohab_svr_dsbl_dpnts;
776 --
777 end per_jp_ctr_utility_pkg;