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