[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_MAGTAPE_FUN_PKG
Source
1 package body pay_kr_yea_magtape_fun_pkg as
2 /* $Header: pykryean.pkb 120.9 2011/03/02 11:11:44 pnethaga ship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := ' pay_kr_yea_magtape_fun_pkg.';
7 --
8 ------------------------------------------------------------------------
9 procedure invalid_argument(
10 p_procedure in varchar2,
11 p_argument in varchar2,
12 p_value in varchar2)
13 ------------------------------------------------------------------------
14 is
15 begin
16 fnd_message.set_name('FND', 'FORM_INVALID_ARGUMENT');
17 fnd_message.set_token('PROCEDURE', p_procedure);
18 fnd_message.set_token('ARGUMENT', p_argument);
19 fnd_message.set_token('VALUE', p_value);
20 fnd_message.raise_error;
21 end invalid_argument;
22 ------------------------------------------------------------------------
23 procedure populate_b(p_bp_number in varchar2,p_tax_office_code in varchar2)
24 ------------------------------------------------------------------------
25 is
26 l_proc varchar2(61) := c_package || 'populate_b';
27 --
28 cursor csr_b1 is
29 select count(*)
30 from pay_assignment_actions paa,
31 pay_payroll_actions ppa,
32 hr_organization_units bp,
33 hr_organization_information hoi1,
34 hr_organization_information hoi2
35 where hoi1.org_information2 = p_bp_number --Bug# 2822459
36 and hoi2.org_information9 = p_tax_office_code
37 and hoi1.organization_id = hoi2.organization_id
38 and hoi1.organization_id = bp.organization_id
39 and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
40 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
41 and ppa.report_type = 'YEA'
42 and ppa.report_qualifier = 'KR'
43 -- Bug 3248513
44 and ( (ppa.report_category in (pay_kr_yea_magtape_pkg.g_normal_yea, pay_kr_yea_magtape_pkg.g_interim_yea, pay_kr_yea_magtape_pkg.g_re_yea)) or (ppa.payroll_action_id = pay_kr_yea_magtape_pkg.g_payroll_action_id) )
45 and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year
46 --
47 and ppa.action_type in ('B','X')
48 and paa.payroll_action_id = ppa.payroll_action_id
49 and ppa.payroll_action_id = ppa.payroll_action_id
50 -- Bug 3248513
51 and ((pay_kr_yea_magtape_pkg.g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(pay_kr_yea_magtape_pkg.g_assignment_set_id, paa.assignment_id) = 'Y'))
52 and ((pay_kr_yea_magtape_pkg.g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_magtape_pkg.g_payroll_action_id, pay_kr_yea_magtape_pkg.g_target_year) = 'Y'))
53 --
54 and paa.tax_unit_id = bp.organization_id
55 and paa.action_status = 'C';
56
57 cursor csr_b3 (p_user_entity_id in ff_user_entities.user_entity_id%type) is
58 select nvl(sum(greatest(to_number(i1.value), 0)), 0)
59 from ff_archive_items i1,
60 pay_assignment_actions paa,
61 pay_payroll_actions ppa,
62 hr_organization_units bp,
63 hr_organization_information hoi1,
64 hr_organization_information hoi2
65 where hoi1.org_information2 = p_bp_number --Bug# 2822459
66 and hoi2.org_information9 = p_tax_office_code
67 and hoi1.organization_id = hoi2.organization_id
68 and hoi1.organization_id = bp.organization_id
69 and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
70 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
71 and ppa.report_type = 'YEA'
72 and ppa.report_qualifier = 'KR'
73 -- Bug 3248513
74 and ( (ppa.report_category in (pay_kr_yea_magtape_pkg.g_normal_yea, pay_kr_yea_magtape_pkg.g_interim_yea, pay_kr_yea_magtape_pkg.g_re_yea)) or (ppa.payroll_action_id = pay_kr_yea_magtape_pkg.g_payroll_action_id) )
75 and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year
76 --
77 and ppa.action_type in ('B','X')
78 and paa.payroll_action_id = ppa.payroll_action_id
79 and ppa.payroll_action_id = ppa.payroll_action_id
80 -- Bug 3248513
81 and ((pay_kr_yea_magtape_pkg.g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(pay_kr_yea_magtape_pkg.g_assignment_set_id, paa.assignment_id) = 'Y'))
82 and ((pay_kr_yea_magtape_pkg.g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_magtape_pkg.g_payroll_action_id, pay_kr_yea_magtape_pkg.g_target_year) = 'Y'))
83 --
84 and paa.tax_unit_id = bp.organization_id
85 and paa.action_status = 'C'
86 and i1.context1(+) = paa.assignment_action_id
87 and i1.user_entity_id(+) = p_user_entity_id;
88
89 cursor csr_b2 is
90 select
91 count(*)
92 from per_assignment_extra_info aei,
93 pay_assignment_actions paa,
94 pay_payroll_actions ppa,
95 hr_organization_units bp,
96 hr_organization_information hoi1,
97 hr_organization_information hoi2
98 where hoi1.org_information2 = p_bp_number --Bug# 2822459
99 and hoi2.org_information9 = p_tax_office_code
100 and hoi1.organization_id = hoi2.organization_id
101 and hoi1.organization_id = bp.organization_id
102 and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
103 and hoi1.org_information_context like 'KR_BUSINESS_PLACE_REGISTRATION'
104 and ppa.report_type = 'YEA'
105 and ppa.report_qualifier = 'KR'
106 -- Bug 3248513
107 and ( (ppa.report_category in (pay_kr_yea_magtape_pkg.g_normal_yea, pay_kr_yea_magtape_pkg.g_interim_yea, pay_kr_yea_magtape_pkg.g_re_yea)) or (ppa.payroll_action_id = pay_kr_yea_magtape_pkg.g_payroll_action_id) )
108 and to_number(to_char(ppa.effective_date, 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year
109 --
110 and ppa.action_type in ('B','X')
111 and paa.payroll_action_id = ppa.payroll_action_id
112 and ppa.payroll_action_id = ppa.payroll_action_id
113 -- Bug 3248513
114 and ((pay_kr_yea_magtape_pkg.g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(pay_kr_yea_magtape_pkg.g_assignment_set_id, paa.assignment_id) = 'Y'))
115 and ((pay_kr_yea_magtape_pkg.g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, pay_kr_yea_magtape_pkg.g_payroll_action_id, pay_kr_yea_magtape_pkg.g_target_year) = 'Y'))
116 --
117 and paa.tax_unit_id = bp.organization_id
118 and paa.action_status = 'C'
119 and aei.assignment_id = paa.assignment_id
120 and aei.information_type = 'KR_YEA_PREV_ER_INFO'
121 and to_number(to_char(fnd_date.canonical_to_date(aei.aei_information1), 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year;
122 begin
123 -- if g_b_record.tax_unit_id is null or g_b_record.tax_unit_id <> p_tax_unit_id then
124 -- g_b_record.tax_unit_id := p_tax_unit_id;
125 --
126 open csr_b1;
127 fetch csr_b1 into g_b_record.c_records;
128 close csr_b1;
129
130 open csr_b3(pay_kr_yea_magtape_pkg.g_taxable_id);
131 fetch csr_b3 into g_b_record.taxable;
132 close csr_b3;
133
134 open csr_b3(pay_kr_yea_magtape_pkg.g_annual_itax_id);
135 fetch csr_b3 into g_b_record.annual_itax;
136 close csr_b3;
137
138 open csr_b3(pay_kr_yea_magtape_pkg.g_annual_rtax_id);
139 fetch csr_b3 into g_b_record.annual_rtax;
140 close csr_b3;
141
142 open csr_b3(pay_kr_yea_magtape_pkg.g_annual_stax_id);
143 fetch csr_b3 into g_b_record.annual_stax;
144 close csr_b3;
145
146 --
147 open csr_b2;
148 fetch csr_b2 into g_b_record.d_records;
149 close csr_b2;
150 -- end if;
151 end populate_b;
152 ------------------------------------------------------------------------
153 function b_data(
154 p_bp_number in varchar2,
155 p_tax_office_code in varchar2,
156 p_item_name in varchar2) return varchar2
157 ------------------------------------------------------------------------
158 is
159 l_proc varchar2(61) := c_package || 'b_data';
160 begin
161 populate_b(p_bp_number,p_tax_office_code);
162 --
163 if p_item_name = 'C_RECORDS' then
164 return to_char(g_b_record.c_records);
165 elsif p_item_name = 'TAXABLE' then
166 return to_char(g_b_record.taxable);
167 elsif p_item_name = 'ANNUAL_ITAX' then
168 return to_char(g_b_record.annual_itax);
169 elsif p_item_name = 'ANNUAL_RTAX' then
170 return to_char(g_b_record.annual_rtax);
171 elsif p_item_name = 'ANNUAL_STAX' then
172 return to_char(g_b_record.annual_stax);
173 elsif p_item_name = 'D_RECORDS' then
174 return to_char(g_b_record.d_records);
175 else
176 invalid_argument(l_proc, 'P_ITEM_NAME', p_item_name);
177 end if;
178 end b_data;
179 ------------------------------------------------------------------------
180 procedure populate_c(p_assignment_id in number)
181 ------------------------------------------------------------------------
182 is
183 l_proc varchar2(61) := c_package || 'populate_c';
184 --
185 cursor csr_c is
186 select
187 count(*)
188 from per_assignment_extra_info aei
189 where aei.assignment_id = p_assignment_id
190 and aei.information_type = 'KR_YEA_PREV_ER_INFO'
191 and to_number(to_char(fnd_date.canonical_to_date(aei.aei_information1), 'YYYY')) = pay_kr_yea_magtape_pkg.g_target_year;
192 begin
193 if g_c_record.assignment_id is null or g_c_record.assignment_id <> p_assignment_id then
194 g_c_record.assignment_id := p_assignment_id;
195 --
196 open csr_c;
197 fetch csr_c into g_c_record.d_records_per_c;
198 close csr_c;
199 end if;
200 end populate_c;
201 ------------------------------------------------------------------------
202 function c_data(
203 p_assignment_id in number,
204 p_item_name in varchar2) return varchar2
205 ------------------------------------------------------------------------
206 is
207 l_proc varchar2(61) := c_package || 'c_data';
208 begin
209 populate_c(p_assignment_id);
210 --
211 if p_item_name = 'D_RECORDS_PER_C' then
212 return to_char(g_c_record.d_records_per_c);
213 else
214 invalid_argument(l_proc, 'P_ITEM_NAME', p_item_name);
215 end if;
216 end c_data;
217
218 ------------------------------------------------------------------------
219 -- Bug 3248513 Function latest_yea_action created to get the latest
220 -- Re-YEA action if e-file is printed for re-yea
221 ------------------------------------------------------------------------
222 function latest_yea_action(
223 ------------------------------------------------------------------------
224 p_asg_action_id in pay_assignment_actions.assignment_action_id%type,
225 p_pact in number,
226 p_target_year in number
227 ) return varchar2
228 ------------------------------------------------------------------------
229 is
230
231 l_is_latest varchar2(1);
232
233 Cursor is_latest is
234 Select 'Y'
235 from pay_assignment_actions paa,
236 pay_payroll_actions ppa
237 where paa.assignment_action_id = p_asg_action_id
238 and ppa.payroll_action_id = paa.payroll_action_id
239 and not exists
240 ( Select assignment_action_id
241 from pay_assignment_actions paa1,
242 pay_payroll_actions ppa1
243 where paa1.assignment_id = paa.assignment_id
244 and ppa1.payroll_action_id = paa1.payroll_action_id
245 and ppa1.action_type in ('B', 'X')
246 and paa1.action_status = 'C'
247 and ppa1.report_type = 'YEA'
248 and ppa1.report_qualifier = 'KR'
249 and ppa1.report_category = 'R'
250 and to_number(to_char(ppa1.effective_date, 'YYYY')) = p_target_year -- Bug 4726974
251 and paa1.action_sequence > paa.action_sequence);
252 begin
253 if p_pact is not null then -- Bug 4726974
254 return 'Y';
255 else
256 open is_latest;
257 fetch is_latest into l_is_latest;
258 close is_latest;
259
260 return nvl(l_is_latest, 'N');
261 end if;
262 end;
263 ---------------------------------------------------------------------------
264 -- Bug : 4738717
265 -- This function returns the count of the dependents
266 -- who are elilgible for exemptions
267 function e_record_count( p_ass_id in varchar2,
268 p_eff_date in date ) return number
269 is
270 cursor csr_e_count( p_assignment_id varchar2, p_effective_date date ) is
271 -- Bug 5654127
272 -- Bug 5872042
273 -- Bug 7661820
274 select count(*)
275 from pay_kr_cont_details_v pkc,
276 per_contact_extra_info_f cei -- Bug 5879106
277 where pkc.assignment_id = p_assignment_id
278 and p_effective_date between pkc.emp_start_date and pkc.emp_end_date
279 and pay_kr_ff_functions_pkg.is_exempted_dependent(pkc.contact_type, pkc.cont_information11, pkc.national_identifier, pkc.cont_information2,
280 pkc.cont_information3,
281 pkc.cont_information4,
282 pkc.cont_information7,
283 pkc.cont_information8,
284 p_effective_date,
285 pkc.cont_information10,
286 pkc.cont_information12,
287 pkc.cont_information13,
288 pkc.cont_information14,
289 cei.contact_extra_info_id) = 'Y'
290 and to_char(cei.effective_start_date(+), 'yyyy') = to_char(p_effective_date,'yyyy')
291 and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
292 and cei.contact_relationship_id(+) = pkc.contact_relationship_id
293 and p_effective_date between nvl(pkc.date_start, p_effective_date)
294 and decode(pkc.cont_information9, 'D',trunc(add_months(nvl(pkc.date_end, p_effective_date),12),'YYYY')-1,
295 nvl(pkc.date_end, p_effective_date) )
296 and p_effective_date between nvl(ADDRESS_START_DATE, p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
297 and p_effective_date between pkc.CONT_START_DATE and pkc.CONT_END_DATE;
298 -- End of Bug 5872042
299 -- End of Bug 5654127
300 l_count number;
301
302 begin
303
304 open csr_e_count(p_ass_id,p_eff_date);
305 fetch csr_e_count into l_count;
306 close csr_e_count;
307
308 return l_count+1; -- Bug 5654127: Added 1 for the employee details record
309
310 end e_record_count;
311 ---------------------------------------------------------------------------
312 -- Bug 9213683: Created a new function to fetch the non-taxable earnings
313 -- values for the Previous employer.
314 ---------------------------------------------------------------------------
315 function prev_non_tax_values(
316 p_assignment_id in varchar2,
317 p_bp_number in varchar2,
318 p_code in varchar2,
319 p_effective_date in date) return number
320 is
321 --
322 l_dummy number := 0;
323
324 cursor csr_aei is
325 SELECT nvl(aei_information5, 0) VALUE
326 FROM per_assignment_extra_info
327 WHERE assignment_id = p_assignment_id
328 AND aei_information4 = p_bp_number
329 AND aei_information2 = p_code
330 AND information_type = 'KR_YEA_NON_TAXABLE_EARN_DETAIL'
331 AND TRUNC(fnd_date.canonical_to_date(aei_information1), 'YYYY') = TRUNC(p_effective_date, 'YYYY');
332 --
333 begin
334 --
335 open csr_aei;
336 fetch csr_aei into l_dummy;
337 if csr_aei%NOTFOUND then
338 l_dummy := 0;
339 end if;
340 close csr_aei;
341
342 return l_dummy;
343 --
344 end;
345 ---------------------------------------------------------------------------
346 -- Bug 10184055
347 ---------------------------------------------------------------------------
348 function f_record_count ( p_ass_id in varchar2,
349 p_eff_date in date,
350 p_assignment_action_id in varchar2) return number
351 is
352
353 l_count number := 0;
354
355 cursor csr_f_count (p_assignment_id varchar2, p_effective_date date, p_assignment_action_id in varchar2 ) is
356 select count(*)
357 from per_assignment_extra_info
358 where
359 information_type in ('KR_YEA_SEP_PEN_DETAILS','KR_YEA_PEN_SAVING_DETAILS','KR_YEA_HOU_SAVING_DETAILS','KR_YEA_LT_STOCK_SAVING_DETAILS')
360 and assignment_id = p_ass_id
361 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
362 and pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,
363 p_effective_date, information_type,aei_information6) > 0;
364
365
366 begin
367
368 open csr_f_count (p_ass_id,p_eff_date,p_assignment_action_id);
369 fetch csr_f_count into l_count;
370 if csr_f_count%NOTFOUND then
371 l_count := 0;
372 end if;
373 close csr_f_count;
374
375 return l_count;
376 end f_record_count;
377 ----------------------------------------------------------------------------
378 function get_sep_pen_exem_amount(
379 p_archive in varchar2,
380 p_row_count in number) return number
381 is
382
383 l_exem_amount number := 0;
384 l_dummy varchar2(300);
385
386 begin
387
388 if nvl(p_row_count,1) = 1 then
389 l_exem_amount := to_number(substr(p_archive,1,instr(p_archive,'X',1,1)-1));
390
391 else
392 l_dummy := instr(p_archive,'X',1,p_row_count-1)+1;
393 l_exem_amount := to_number (substr(p_archive,l_dummy,instr(p_archive,'X',1,p_row_count)-l_dummy));
394 end if;
395 return l_exem_amount;
396 end get_sep_pen_exem_amount;
397 ----------------------------------------------------------------------------
398 function get_sep_pen_eligible ( p_assignment_id in varchar2, p_assignment_action_id in varchar2,
399 p_effective_date in date, p_information_type in varchar2, p_aei_information6 in varchar2 ) return number is
400
401 g_archive_item_value number;
402 g_ex_archive_item_value varchar2(300);
403 p_row_count number;
404
405 cursor csr(p_information_type in varchar2, p_assignment_id in varchar2, p_effective_date in date) is
406 select aei_information6
407 from per_assignment_extra_info
408 where information_type = p_information_type
409 and assignment_id = p_assignment_id
410 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
411 order by to_number(aei_information6);
412
413 --------------------------------------------------------
414 function archive_item_value(p_user_entity_name in varchar2) return varchar2
415 --------------------------------------------------------
416 is
417 value varchar2(300);
418 begin
419 select ffai.value
420 into value
421 from ff_user_entities ffue, ff_archive_items ffai
422 where ffue.user_entity_name = p_user_entity_name
423 and ffue.legislation_code = 'KR'
424 and ffue.creator_type = 'X'
425 and ffai.user_entity_id = ffue.user_entity_id
426 and ffai.context1 = p_assignment_action_id;
427 --
428 return value;
429 end archive_item_value;
430
431 begin
432 p_row_count := 0;
433 g_archive_item_value := 0;
434
435 for rec in csr(p_information_type,p_assignment_id,p_effective_date) loop
436 p_row_count := p_row_count + 1;
437 if rec.aei_information6 = p_aei_information6 then
438 exit;
439 end if;
440 end loop;
441
442 if p_information_type = 'KR_YEA_SEP_PEN_DETAILS' then
443 if p_row_count = 1 then
444 g_archive_item_value := to_number(archive_item_value ('X_YEA_SEP_PEN_EXEM_AMT1'));
445 elsif p_row_count = 2 then
446 g_archive_item_value := to_number(archive_item_value ('X_YEA_SEP_PEN_EXEM_AMT2'));
447 elsif p_row_count > 2 then
448 g_ex_archive_item_value := archive_item_value ('X_YEA_SEP_PEN_EXEM_AMT_ARCHIVE');
449 g_archive_item_value := get_sep_pen_exem_amount(g_ex_archive_item_value,p_row_count-2);
450 end if;
451 elsif p_information_type = 'KR_YEA_PEN_SAVING_DETAILS' then
452 if p_row_count = 1 then
453 g_archive_item_value := to_number(archive_item_value ('X_YEA_PEN_SAV_EXEM_AMT1'));
454 elsif p_row_count = 2 then
455 g_archive_item_value := to_number(archive_item_value ('X_YEA_PEN_SAV_EXEM_AMT2'));
456 elsif p_row_count > 2 then
457 g_ex_archive_item_value := archive_item_value ('X_YEA_PEN_SAV_EXEM_AMT_ARCHIVE');
458 g_archive_item_value := get_sep_pen_exem_amount(g_ex_archive_item_value,p_row_count-2);
459 end if;
460 elsif p_information_type = 'KR_YEA_HOU_SAVING_DETAILS' then
461 if p_row_count = 1 then
462 g_archive_item_value := to_number(archive_item_value ('X_YEA_HOU_SAV_EXEM_AMT1'));
463 elsif p_row_count = 2 then
464 g_archive_item_value := to_number(archive_item_value ('X_YEA_HOU_SAV_EXEM_AMT2'));
465 elsif p_row_count = 3 then
466 g_archive_item_value := to_number(archive_item_value ('X_YEA_HOU_SAV_EXEM_AMT3'));
467 elsif p_row_count > 3 then
468 g_ex_archive_item_value := archive_item_value ('X_YEA_HOU_SAV_EXEM_AMT_ARCHIVE');
469 g_archive_item_value := get_sep_pen_exem_amount(g_ex_archive_item_value,p_row_count-3);
470 end if;
471 elsif p_information_type = 'KR_YEA_LT_STOCK_SAVING_DETAILS' then
472 if p_row_count = 1 then
473 g_archive_item_value := to_number(archive_item_value ('X_YEA_LT_STCK_SAV_EXEM_AMT1'));
474 elsif p_row_count = 2 then
475 g_archive_item_value := to_number(archive_item_value ('X_YEA_LT_STCK_SAV_EXEM_AMT2'));
476 elsif p_row_count = 3 then
477 g_archive_item_value := to_number(archive_item_value ('X_YEA_LT_STCK_SAV_EXEM_AMT3'));
478 elsif p_row_count > 3 then
479 g_ex_archive_item_value := archive_item_value ('X_YEA_LT_STCK_SAV_EXEM_AMT_ARCHIVE');
480 g_archive_item_value := get_sep_pen_exem_amount(g_ex_archive_item_value,p_row_count-3);
481 end if;
482 end if;
483
484 return g_archive_item_value;
485
486 end get_sep_pen_eligible;
487
488 end pay_kr_yea_magtape_fun_pkg;