[Home] [Help]
PACKAGE BODY: APPS.PER_JP_CMA_UTILITY_PKG
Source
1 package body per_jp_cma_utility_pkg as
2 /* $Header: pejpcmau.pkb 120.7 2011/10/03 04:43:42 keyazawa ship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'per_jp_cma_utility_pkg.';
7 --
8 c_car_element_name constant pay_element_types_f.element_name%type := 'SAL_CMA_PRIVATE_TRANSPORT_INFO';
9 c_train_element_name constant pay_element_types_f.element_name%type := 'SAL_CMA_PUBLIC_TRANSPORT_INFO';
10 --
11 c_non_taxable_udt_name constant varchar2(80) := 'T_SAL_CMA_PRIVATE_TRANSPORT_NTXBL_ERN_MAX';
12 c_non_taxable_udt_col_name constant varchar2(80) := 'MAX';
13 c_equivalent_cost_udt_col_name constant varchar2(80) := 'FARE_EQUIVALENT_AMT_PRIORITY_FLAG';
14 --
15 c_means_udt_name constant varchar2(80) := 'T_SAL_CMA_METHOD_INFO';
16 c_means_udt_col_name constant varchar2(80) := 'TXBL_FLAG';
17 c_parking_fees constant varchar2(30) := 'PARKING_FEE';
18 --
19 c_st_upd_date_2012 constant date := to_date('2012/01/01','YYYY/MM/DD');
20 --
21 -- Global Variables
22 --
23 type t_bg_cma_formula_rec is record(
24 business_group_id number,
25 ref_type varchar2(150));
26 g_bg_cma_formula_rec t_bg_cma_formula_rec;
27 -- ----------------------------------------------------------------------------
28 -- |-------------------------< bg_cma_formula_id >----------------------------|
29 -- ----------------------------------------------------------------------------
30 function bg_cma_formula_id(p_business_group_id in number) return varchar2
31 is
32 cursor csr_bg is
33 select org_information3
34 from hr_organization_information
35 where organization_id = p_business_group_id
36 and org_information_context = 'JP_BUSINESS_GROUP_INFO';
37 begin
38 --
39 -- Use cache if available
40 --
41 if (g_bg_cma_formula_rec.business_group_id is null)
42 or (g_bg_cma_formula_rec.business_group_id <> p_business_group_id) then
43 g_bg_cma_formula_rec.business_group_id := p_business_group_id;
44 --
45 open csr_bg;
46 fetch csr_bg into g_bg_cma_formula_rec.ref_type;
47 /*
48 if (csr_bg%notfound) or (g_bg_cma_formula_rec.ref_type is null) then
49 g_bg_cma_formula_rec.ref_type := c_default_itax_dpnt_ref_type;
50 end if;
51 */
52 close csr_bg;
53 end if;
54 --
55 return g_bg_cma_formula_rec.ref_type;
56 end bg_cma_formula_id;
57 -- ----------------------------------------------------------------------------
58 -- |---------------------------< calc_car_amount >----------------------------|
59 -- ----------------------------------------------------------------------------
60 --
61 -- This procedure is called from PAYJPCMA form.
62 --
63 -- ----------------------------------------------------------------------------
64 procedure calc_car_amount(
65 p_formula_id in number,
66 p_business_group_id in number,
67 p_assignment_id in number,
68 p_effective_date in date,
69 p_ev_rec_tbl in pay_jp_entries_pkg.ev_rec_tbl,
70 p_attribute_tbl in pay_jp_entries_pkg.attribute_tbl,
71 p_outputs out nocopy varchar2,
72 p_val_returned out nocopy boolean)
73 is
74 c_proc constant varchar2(61) := c_package || 'calc_car_amount';
75 --
76 l_inputs ff_exec.inputs_t;
77 l_outputs ff_exec.outputs_t;
78 begin
79 --
80 -- Initialize formula informations.
81 --
82 ff_exec.init_formula(
83 p_formula_id => p_formula_id,
84 p_effective_date => p_effective_date,
85 p_inputs => l_inputs,
86 p_outputs => l_outputs);
87 --
88 -- Setup input variables.
89 --
90 for i in 1..l_inputs.count loop
91 if l_inputs(i).name = 'BUSINESS_GROUP_ID' then
92 l_inputs(i).value := to_char(p_business_group_id);
93 elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
94 l_inputs(i).value := to_char(p_assignment_id);
95 elsif l_inputs(i).name = 'DATE_EARNED' then
96 l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
97 elsif l_inputs(i).name like 'ENTRY_VALUE%' then
98 for l_counter in 1..15 loop
99 if l_inputs(i).name = 'ENTRY_VALUE' || to_char(l_counter) then
100 l_inputs(i).value := p_ev_rec_tbl(l_counter).entry_value;
101 end if;
102 end loop;
103 elsif l_inputs(i).name like 'ATTRIBUTE%' then
104 for l_counter in 1..20 loop
105 if l_inputs(i).name = 'ATTRIBUTE' || to_char(l_counter) then
106 l_inputs(i).value := p_attribute_tbl.attribute(l_counter);
107 end if;
108 end loop;
109 end if;
110 end loop;
111 --
112 -- Execute formula. Formula unexpected error is raised by ffexec,
113 -- so not necessary to handle error.
114 --
115 ff_exec.run_formula(
116 p_inputs => l_inputs,
117 p_outputs => l_outputs,
118 p_use_dbi_cache => TRUE);
119 --
120 -- Setup output variables.
121 --
122 p_val_returned := False;
123 for i in 1..l_outputs.count loop
124 if l_outputs(i).name = 'RETURN_AMOUNT' then
125 p_outputs := l_outputs(i).value;
126 p_val_returned := True;
127 exit;
128 end if;
129 end loop;
130 exception
131 when OTHERS then
132 p_val_returned := False;
133 end calc_car_amount;
134 --
135 -- ----------------------------------------------------------------------------
136 -- |-----------------------------< get_cma_info >-----------------------------|
137 -- ----------------------------------------------------------------------------
138 -- The amount for transportation and traffic tool are got from commutation
139 -- information, and taxable amount, non-taxable amount etc. are calculated.
140 -- Strictly speaking, p_non_taxable_limit_1mth shouldn't be passed into
141 -- because this limit is p_effective_date dependent.
142 -- But this amount is currently 100,000 yen and has never been changed.
143 -- We leave this issue until this limit will be datetracked in the future.
144 -- ----------------------------------------------------------------------------
145 -- p_payment_date was added by bug.4029525, but this parameter is not required
146 -- from coding point of view, which can be derived using si_month and p_effective_date.
147 -- There are some potential issues when SI month is set to "Next Month".
148 -- For example, car is set to "Current Month" and train is set to "Next Month".
149 -- In this case, which date should be set to p_effective_date?
150 -- For car, p_effective_date is this month, and p_effective_date is next month for train.
151 -- In general, there will be no datetrack records in the future,
152 -- p_effective_date(=next month) for cars will be OK for most cases.
153 procedure get_cma_info(
154 p_business_group_id in number,
155 p_assignment_id in number,
156 p_payment_date in date, -- bug 4029525
157 p_effective_date in date,
158 p_non_taxable_limit_1mth in number,
159 p_cma_rec out nocopy cma_rec,
160 p_record_exist out nocopy boolean)
161 is
162 c_proc constant varchar2(61) := c_package || 'get_cma_info';
163 --
164 -- Type Definitions
165 --
166 type deemed_income_rec is record(
167 sal_taxable_amount number := 0,
168 sal_non_taxable_amount number := 0,
169 mtr_taxable_amount number := 0,
170 mtr_non_taxable_amount number := 0,
171 paid_sal_non_taxable_amount number := 0,
172 paid_mtr_non_taxable_amount number := 0);
173 type deemed_income_tbl is table of deemed_income_rec index by binary_integer;
174 l_deemed_income deemed_income_tbl;
175 ---- bug 4029525 ----
176 l_payment_date date;
177 ---------------------
178 l_enforced_taxable_flag number;
179 l_parking_taxable_flag number;
180 l_equivalent_cost_priority number;
181 l_mod number;
182 l_non_taxable_limit number;
183 l_lcm number;
184 l_amount number;
185 i number;
186 --
187 l_prev_payment_date date;
188 l_payment_date_offset number;
189 l_si_date date;
190 l_index number;
191 --
192 l_sal_taxable_amount number;
193 l_sal_non_taxable_amount number;
194 l_mtr_taxable_amount number;
195 l_mtr_non_taxable_amount number;
196 --
197 l_sal_taxable_flag boolean := false;
198 l_sal_non_taxable_flag boolean := false;
199 l_mtr_taxable_flag boolean := false;
200 l_mtr_non_taxable_flag boolean := false;
201 l_sal_income number := 0;
202 l_mtr_income number := 0;
203 --
204 l_sal_si_flag boolean := false;
205 l_mtr_si_flag boolean := false;
206 l_sal_si_wage number := 0;
207 l_mtr_si_wage number := 0;
208 --
209 l_car_sal_taxable_amount number;
210 l_car_sal_non_taxable_amount number;
211 --
212 -- si_month is not supported for cars.
213 -- The storage is just created for future enhancement.
214 -- There's no specification/usage available for car si_month at the moment.
215 --
216 cursor csr_car is
217 select car.element_entry_id,
218 car.tranpo_type,
219 car.period,
220 car.distance,
221 car.amount,
222 car.parking_fees,
223 car.equivalent_cost,
224 car.pay_start,
225 car.pay_end
226 from (
227 select v.element_entry_id,
228 v.tranpo_type,
229 v.period,
230 v.distance,
231 v.amount,
232 v.parking_fees,
233 v.equivalent_cost,
234 nvl(to_date(v.pay_start, 'YYYYMM'), trunc(v.effective_start_date, 'MM')) pay_start,
235 nvl(to_date(v.pay_end, 'YYYYMM'), trunc(v.effective_end_date, 'MM')) pay_end
236 from (
237 select /*+ ORDERED USE_NL(PEL PEE PEEV PIV) INDEX (PEE PAY_ELEMENT_ENTRIES_F_N51) */
238 pee.element_entry_id,
239 pee.effective_start_date,
240 pee.effective_end_date,
241 substrb(min(decode(piv.display_sequence, 1, peev.screen_entry_value, NULL)), 1, 30) tranpo_type,
242 nvl(fnd_number.canonical_to_number(substr(min(decode(piv.display_sequence, 3, peev.screen_entry_value, NULL)), 5)), 1) period, -- MTH_x(x=1,3,6)
243 -- Value needs to truncated to avoid no_data_found in get_table_value
244 trunc(nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 4, peev.screen_entry_value, NULL))), 0) * 1000) distance,
245 -- amount and parking_fees can be null values.
246 fnd_number.canonical_to_number(min(decode(piv.display_sequence, 6, peev.screen_entry_value, NULL))) amount,
247 fnd_number.canonical_to_number(min(decode(piv.display_sequence, 7, peev.screen_entry_value, NULL))) parking_fees,
248 nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 8, peev.screen_entry_value, NULL))), 0) equivalent_cost,
249 substrb(min(decode(piv.display_sequence, 9, peev.screen_entry_value, NULL)), 1, 6) pay_start,
250 substrb(min(decode(piv.display_sequence, 10, peev.screen_entry_value, NULL)), 1, 6) pay_end
251 from pay_element_types_f pet,
252 pay_element_links_f pel,
253 pay_element_entries_f pee,
254 pay_element_entry_values_f peev,
255 pay_input_values_f piv
256 where pet.element_name = c_car_element_name
257 and pet.legislation_code = 'JP'
258 and p_effective_date
259 between pet.effective_start_date and pet.effective_end_date
260 and pel.element_type_id = pet.element_type_id
261 and p_effective_date
262 between pel.effective_start_date and pel.effective_end_date
263 and pel.business_group_id = p_business_group_id
264 and pee.assignment_id = p_assignment_id
265 and pee.element_link_id = pel.element_link_id
266 and p_effective_date
267 between pee.effective_start_date and pee.effective_end_date
268 and pee.entry_type = 'E'
269 and peev.element_entry_id = pee.element_entry_id
270 and peev.effective_start_date = pee.effective_start_date
271 and peev.effective_end_date = pee.effective_end_date
272 and piv.input_value_id = peev.input_value_id
273 and p_effective_date
274 between piv.effective_start_date and piv.effective_end_date
275 group by
276 pee.element_entry_id,
277 pee.effective_start_date,
278 pee.effective_end_date
279 ) v
280 where v.tranpo_type is not null
281 ) car
282 where l_payment_date
283 between car.pay_start and car.pay_end;
284 --
285 type t_car_tbl is table of csr_car%rowtype;
286 l_cars t_car_tbl;
287 l_car csr_car%rowtype;
288 --
289 -- Only in case of trains entries which do not exist as of p_effective_date
290 -- can contribute to si_wages (we need to take si_month into consideration).
291 --
292 cursor csr_train is
293 select train.element_entry_id,
294 train.effective_start_date,
295 train.effective_end_date,
296 train.tranpo_type,
297 train.period,
298 train.amount_type,
299 train.amount,
300 train.pay_start,
301 train.pay_end,
302 train.si_start,
303 train.si_end,
304 train.si_month
305 from (
306 select v.element_entry_id,
307 v.effective_start_date,
308 v.effective_end_date,
309 v.tranpo_type,
310 v.period,
311 v.amount_type,
312 v.amount,
313 --
314 -- When pay_start is null, pay_start is
315 -- 1. 1 month before effective_start_date month when si_month = "Next Month"
316 -- 2. effective_start_date month when si_month = "This Month"
317 --
318 nvl(to_date(v.pay_start, 'YYYYMM'), trunc(add_months(v.effective_start_date, - v.si_month), 'MM')) pay_start,
319 nvl(to_date(v.pay_end, 'YYYYMM'), trunc(add_months(v.effective_end_date, - v.si_month), 'MM')) pay_end,
320 nvl(add_months(to_date(v.pay_start, 'YYYYMM'), v.si_month), trunc(v.effective_start_date, 'MM')) si_start,
321 nvl(add_months(to_date(v.pay_end, 'YYYYMM'), v.si_month), trunc(v.effective_end_date, 'MM')) si_end,
322 v.si_month
323 from (
324 select /*+ ORDERED USE_NL(PEL PEE PEEV PIV) INDEX (PEE PAY_ELEMENT_ENTRIES_F_N51) */
325 pee.element_entry_id,
326 pee.effective_start_date,
327 pee.effective_end_date,
328 substrb(min(decode(piv.display_sequence, 1, peev.screen_entry_value, NULL)), 1, 30) tranpo_type,
329 nvl(fnd_number.canonical_to_number(substr(min(decode(piv.display_sequence, 5, peev.screen_entry_value, NULL)), 5)), 1) period, -- MTH_x(x=1,3,6)
330 nvl(substrb(min(decode(piv.display_sequence, 6, peev.screen_entry_value, NULL)), 1, 30), 'SALARY') amount_type, -- SALARY/MATERIAL
331 nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence,7, peev.screen_entry_value, NULL))), 0) amount,
332 substrb(min(decode(piv.display_sequence, 8, peev.screen_entry_value, NULL)), 1, 6) pay_start,
333 substrb(min(decode(piv.display_sequence, 9, peev.screen_entry_value, NULL)), 1, 6) pay_end,
334 nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 10, peev.screen_entry_value, NULL))), 0) si_month
335 from pay_element_types_f pet,
336 pay_element_links_f pel,
337 pay_element_entries_f pee,
338 pay_element_entry_values_f peev,
339 pay_input_values_f piv
340 where pet.element_name = c_train_element_name
341 and pet.legislation_code = 'JP'
342 and p_effective_date
343 between pet.effective_start_date and pet.effective_end_date
344 and pel.element_type_id = pet.element_type_id
345 and p_effective_date
346 between pel.effective_start_date and pel.effective_end_date
347 and pel.business_group_id = p_business_group_id
348 and pee.assignment_id = p_assignment_id
349 and pee.element_link_id = pel.element_link_id
350 -- This date range check is loose, and will exact range check be done outside this inline SQL.
351 -- We take si_month into consideration only for train tranpo, not car tranpo.
352 and pee.effective_start_date <= p_effective_date
353 and pee.entry_type = 'E'
354 and peev.element_entry_id = pee.element_entry_id
355 and peev.effective_start_date = pee.effective_start_date
356 and peev.effective_end_date = pee.effective_end_date
357 and piv.input_value_id = peev.input_value_id
358 and p_effective_date
359 between piv.effective_start_date and piv.effective_end_date
360 group by
361 pee.element_entry_id,
362 pee.effective_start_date,
363 pee.effective_end_date
364 ) v
365 where v.tranpo_type is not null
366 ) train
367 where
368 --
369 -- Taxable Check
370 --
371 (
372 train.effective_end_date >= p_effective_date
373 and l_payment_date
374 between train.pay_start and train.pay_end
375 )
376 or
377 --
378 -- SI Wage Check (Already paid before l_payment_date)
379 --
380 (
381 train.si_month <> 0
382 and train.effective_end_date >= add_months(p_effective_date, - train.si_month)
383 and train.si_end >= l_payment_date
384 /*
385 and add_months(p_effective_date, - train.si_month)
386 between train.effective_start_date and train.effective_end_date
387 and l_payment_date
388 between train.si_start and train.si_end
389 */
390 );
391 --
392 type t_train_tbl is table of csr_train%rowtype;
393 l_trains t_train_tbl;
394 l_train csr_train%rowtype;
395 --
396 procedure chk_non_taxable_limit(
397 p_taxable_amount in out nocopy number,
398 p_non_taxable_amount in out nocopy number,
399 p_non_taxable_limit in number,
400 p_paid_non_taxable_amount in number default null)
401 is
402 l_non_taxable_limit number;
403 begin
404 if p_non_taxable_amount is not null and p_non_taxable_limit is not null then
405 l_non_taxable_limit := greatest(p_non_taxable_limit - nvl(p_paid_non_taxable_amount, 0), 0);
406 if p_non_taxable_amount > l_non_taxable_limit then
407 p_taxable_amount := nvl(p_taxable_amount, 0) + (p_non_taxable_amount - l_non_taxable_limit);
408 p_non_taxable_amount := l_non_taxable_limit;
409 end if;
410 end if;
411 end chk_non_taxable_limit;
412 --
413 procedure init_deemed_income(p_index in number)
414 is
415 begin
416 if not l_deemed_income.exists(p_index) then
417 l_deemed_income(p_index).sal_taxable_amount := 0;
418 end if;
419 end init_deemed_income;
420 begin
421 hr_utility.set_location('Entering: ' || c_proc, 10);
422 ---- bug 4029525 ----
423 l_payment_date := trunc(p_payment_date, 'MM');
424 ---------------------
425 hr_utility.trace('p_payment_date : ' || p_payment_date);
426 hr_utility.trace('p_effective_date: ' || p_effective_date);
427 hr_utility.trace('l_payment_date : ' || l_payment_date);
428 --
429 open csr_car;
430 fetch csr_car bulk collect into l_cars;
431 close csr_car;
432 --
433 -- Exit when multiple car transportation subject to taxation is found
434 -- for one of the months.
435 --
436 if l_cars.count > 1 then
437 p_record_exist := true;
438 p_cma_rec.multiple_entry_warning := true;
439 return;
440 end if;
441 --
442 open csr_train;
443 fetch csr_train bulk collect into l_trains;
444 close csr_train;
445 --
446 -- Derive "Least Common Multiple" of "period" of all transportations.
447 -- This is used to split "transportation fees * l_lcm" into subject month
448 -- to avoid each month amount to be decimal number.
449 --
450 l_lcm := 1;
451 for i in 1..l_cars.count loop
452 hr_utility.trace('car period: ' || l_cars(i).period);
453 p_record_exist := true;
454 l_lcm := hr_jp_standard_pkg.lcm(l_lcm, l_cars(i).period);
455 end loop;
456 --
457 for i in 1..l_trains.count loop
458 hr_utility.trace('train period: ' || l_trains(i).period);
459 p_record_exist := true;
460 l_lcm := hr_jp_standard_pkg.lcm(l_lcm, l_trains(i).period);
461 end loop;
462 --
463 hr_utility.trace('lcm: ' || l_lcm);
464 --
465 -- Split transportation fee for cars into each month.
466 --
467 for i in 1..l_cars.count loop
468 l_car := l_cars(i);
469 l_car_sal_taxable_amount := 0;
470 l_car_sal_non_taxable_amount := 0;
471 l_enforced_taxable_flag := null;
472 l_parking_taxable_flag := null;
473 --
474 hr_utility.trace('***** Car Info: ' || to_char(l_car.element_entry_id));
475 hr_utility.trace('amount : ' || l_car.amount);
476 hr_utility.trace('parking_fees: ' || l_car.parking_fees);
477 hr_utility.trace('period : ' || l_car.period);
478 hr_utility.trace('pay_start : ' || l_car.pay_start);
479 hr_utility.trace('pay_end : ' || l_car.pay_end);
480 --
481 if l_car.amount is not null then
482 --
483 -- Strictly speaking, following flag should be checked
484 -- as of the date of each month subject to SI.
485 -- Currently, we leave this issue.
486 --
487 l_enforced_taxable_flag := to_number(pay_jp_formula_function_pkg.get_table_value_with_default(
488 p_business_group_id,
489 c_means_udt_name,
490 c_means_udt_col_name,
491 hr_general.decode_lookup('JP_CMA_CAR_MEANS', l_car.tranpo_type),
492 ---- bug 4029525 ----
493 p_payment_date,
494 ---------------------
495 '0',
496 'N'));
497 if l_enforced_taxable_flag = 1 then
498 l_car_sal_taxable_amount := l_car_sal_taxable_amount + l_car.amount;
499 else
500 l_car_sal_non_taxable_amount := l_car_sal_non_taxable_amount + l_car.amount;
501 end if;
502 end if;
503 --
504 if l_car.parking_fees is not null then
505 --
506 -- Strictly speaking, following flag should be checked
507 -- as of the date of each month subject to SI.
508 -- Currently, we leave this issue.
509 --
510 l_parking_taxable_flag := to_number(pay_jp_formula_function_pkg.get_table_value_with_default(
511 p_business_group_id,
512 c_means_udt_name,
513 c_means_udt_col_name,
514 c_parking_fees,
515 ---- bug 4029525 ----
516 p_payment_date,
517 ---------------------
518 '0',
519 'N'));
520 if l_parking_taxable_flag = 1 then
521 l_car_sal_taxable_amount := l_car_sal_taxable_amount + l_car.parking_fees;
522 else
523 l_car_sal_non_taxable_amount := l_car_sal_non_taxable_amount + l_car.parking_fees;
524 end if;
525 end if;
526 --
527 if l_car_sal_non_taxable_amount > 0 then
528 --
529 l_non_taxable_limit := null;
530 --
531 if trunc(p_payment_date,'DD') < c_st_upd_date_2012 then
532 --
533 if l_car.equivalent_cost > 0 then
534 --
535 l_equivalent_cost_priority :=
536 to_number(hruserdt.get_table_value(
537 p_business_group_id,
538 c_non_taxable_udt_name,
539 c_equivalent_cost_udt_col_name,
540 -- Pass truncated value to avoid no_data_found
541 l_car.distance,
542 ---- bug 4077909 ----
543 p_payment_date
544 ---------------------
545 ));
546 --
547 if l_equivalent_cost_priority = 1 then
548 --
549 l_non_taxable_limit := l_car.equivalent_cost;
550 --
551 end if;
552 --
553 end if;
554 --
555 end if;
556 --
557 if l_non_taxable_limit is null then
558 --
559 l_non_taxable_limit :=
560 to_number(hruserdt.get_table_value(
561 p_business_group_id,
562 c_non_taxable_udt_name,
563 c_non_taxable_udt_col_name,
564 -- Pass truncated value to avoid no_data_found
565 l_car.distance,
566 ---- bug 4077909 ----
567 p_payment_date
568 ---------------------
569 ));
570 --
571 end if;
572 --
573 -- Non-taxable limit will be checked later,
574 -- so the following check is not required here.
575 --
576 --l_non_taxable_limit := least(l_non_taxable_limit, p_non_taxable_limit_1mth);
577 --
578 -- limitation at the payment date is effective for non taxable amount in all period
579 -- even if period is over c_st_upd_date_2012 (eg. 2011/12,2012/1 2mth from payment date)
580 chk_non_taxable_limit(l_car_sal_taxable_amount, l_car_sal_non_taxable_amount, l_non_taxable_limit * l_car.period);
581 --
582 end if;
583 --
584 l_mod := mod(months_between(l_payment_date, l_car.pay_start), l_car.period);
585 --
586 -- When actually paid.
587 --
588 if l_mod = 0 then
589 if l_enforced_taxable_flag = 1 or l_parking_taxable_flag = 1 then
590 l_sal_taxable_flag := true;
591 else
592 l_sal_non_taxable_flag := true;
593 end if;
594 --
595 l_sal_income := l_sal_income + nvl(l_car.amount, 0) + nvl(l_car.parking_fees, 0);
596 --
597 -- Store <periodic amount> * <Least Common Multiple> into pl/sql table.
598 -- This is to avoid losing errors of decimal points.
599 --
600 for j in 1..l_car.period loop
601 init_deemed_income(j);
602 l_deemed_income(j).sal_taxable_amount := l_deemed_income(j).sal_taxable_amount + l_car_sal_taxable_amount * l_lcm / l_car.period;
603 l_deemed_income(j).sal_non_taxable_amount := l_deemed_income(j).sal_non_taxable_amount + l_car_sal_non_taxable_amount * l_lcm / l_car.period;
604 end loop;
605 --
606 -- When no payment, but need to calculate deemed amount.
607 --
608 else
609 for j in 1..(l_car.period - l_mod) loop
610 --
611 -- Derive the already paid amount for from month of SRS effective_date,
612 -- so that make adjustments to non-taxable amount which is going to paid.
613 -- We do not have to care about Taxable amount which has no limit.
614 --
615 init_deemed_income(j);
616 l_deemed_income(j).paid_sal_non_taxable_amount := l_deemed_income(j).paid_sal_non_taxable_amount + l_car_sal_non_taxable_amount * l_lcm / l_car.period;
617 end loop;
618 end if;
619 --
620 l_sal_si_flag := true;
621 l_sal_si_wage := l_sal_si_wage + (l_car_sal_taxable_amount + l_car_sal_non_taxable_amount) * l_lcm / l_car.period;
622 end loop;
623 --
624 -- Split transportation fee for trains into each month.
625 --
626 for i in 1..l_trains.count loop
627 l_train := l_trains(i);
628 l_amount := l_train.amount * l_lcm / l_train.period;
629 --
630 hr_utility.trace('***** Train Info: ' || to_char(l_train.element_entry_id));
631 hr_utility.trace('amount : ' || l_train.amount);
632 hr_utility.trace('period : ' || l_train.period);
633 hr_utility.trace('pay_start : ' || l_train.pay_start);
634 hr_utility.trace('pay_end : ' || l_train.pay_end);
635 hr_utility.trace('si_start : ' || l_train.si_start);
636 hr_utility.trace('si_end : ' || l_train.si_end);
637 hr_utility.trace('si_month : ' || l_train.si_month);
638 hr_utility.trace('m_amount(lcm): ' || l_amount);
639 --
640 -- Taxable/Non-taxable
641 --
642 if p_effective_date between l_train.effective_start_date and l_train.effective_end_date
643 and l_payment_date between l_train.pay_start and l_train.pay_end then
644 l_mod := mod(months_between(l_payment_date, l_train.pay_start), l_train.period);
645 --
646 -- Only when actually paid.
647 --
648 if l_mod = 0 then
649 --
650 -- Strictly speaking, following flag should be checked
651 -- as of the date of each month subject to SI.
652 -- Currently, we leave this issue.
653 --
654 l_enforced_taxable_flag := to_number(pay_jp_formula_function_pkg.get_table_value_with_default(
655 p_business_group_id,
656 c_means_udt_name,
657 c_means_udt_col_name,
658 hr_general.decode_lookup('JP_CMA_TRAIN_MEANS', l_train.tranpo_type),
659 ---- bug 4029525 ----
660 p_payment_date,
661 ---------------------
662 '0',
663 'N'));
664 --
665 if l_train.amount_type = 'SALARY' then
666 if l_enforced_taxable_flag = 1 then
667 l_sal_taxable_flag := true;
668 else
669 l_sal_non_taxable_flag := true;
670 end if;
671 --
672 l_sal_income := l_sal_income + l_train.amount;
673 else
674 if l_enforced_taxable_flag = 1 then
675 l_mtr_taxable_flag := true;
676 else
677 l_mtr_non_taxable_flag := true;
678 end if;
679 --
680 l_mtr_income := l_mtr_income + l_train.amount;
681 end if;
682 --
683 for j in (1 + l_train.si_month)..(l_train.period + l_train.si_month) loop
684 --
685 -- Stores already paid amount in the month to which l_payment_date belongs
686 -- for SI.
687 --
688 if j = 1 then
689 if l_train.amount_type = 'SALARY' then
690 l_sal_si_flag := true;
691 l_sal_si_wage := l_sal_si_wage + l_amount;
692 else
693 l_mtr_si_flag := true;
694 l_mtr_si_wage := l_mtr_si_wage + l_amount;
695 end if;
696 end if;
697 --
698 init_deemed_income(j);
699 if l_train.amount_type = 'SALARY' then
700 if l_enforced_taxable_flag = 1 then
701 l_deemed_income(j).sal_taxable_amount := l_deemed_income(j).sal_taxable_amount + l_amount;
702 else
703 l_deemed_income(j).sal_non_taxable_amount := l_deemed_income(j).sal_non_taxable_amount + l_amount;
704 end if;
705 else
706 if l_enforced_taxable_flag = 1 then
707 l_deemed_income(j).mtr_taxable_amount := l_deemed_income(j).mtr_taxable_amount + l_amount;
708 else
709 l_deemed_income(j).mtr_non_taxable_amount := l_deemed_income(j).mtr_non_taxable_amount + l_amount;
710 end if;
711 end if;
712 end loop;
713 end if;
714 end if;
715 --
716 -- SI Wages
717 --
718 if l_train.effective_end_date >= add_months(p_effective_date, - l_train.si_month)
719 and l_train.si_end >= l_payment_date then
720 --
721 -- Derive already paid amount in the past before l_payment_date.
722 -- Derive first payment_date from (si_month + period - 1) months before
723 -- to add_months(l_payment_date, -1).
724 --
725 l_prev_payment_date := greatest(add_months(l_payment_date, - (l_train.period - 1 + l_train.si_month)), l_train.pay_start);
726 l_prev_payment_date := add_months(l_train.pay_start, ceil(months_between(l_prev_payment_date, l_train.pay_start) / l_train.period) * l_train.period);
727 l_payment_date_offset := months_between(l_prev_payment_date, l_payment_date);
728 --
729 while l_prev_payment_date < l_payment_date
730 and add_months(p_effective_date, l_payment_date_offset)
731 between l_train.effective_start_date and l_train.effective_end_date
732 and l_prev_payment_date <= l_train.pay_end loop
733 --
734 -- Strictly speaking, following flag should be checked
735 -- as of the date of each month subject to SI.
736 -- Currently, we leave this issue.
737 --
738 l_enforced_taxable_flag := to_number(pay_jp_formula_function_pkg.get_table_value_with_default(
739 p_business_group_id,
740 c_means_udt_name,
741 c_means_udt_col_name,
742 hr_general.decode_lookup('JP_CMA_TRAIN_MEANS', l_train.tranpo_type),
743 ---- bug 4029525 ----
744 add_months(p_payment_date, l_payment_date_offset),
745 ---------------------
746 '0',
747 'N'));
748 --
749 for j in 1..l_train.period loop
750 l_si_date := add_months(l_prev_payment_date, l_train.si_month + j - 1);
751 --
752 if l_si_date >= l_payment_date then
753 l_index := months_between(l_si_date, l_payment_date) + 1;
754 --
755 -- Stores already paid amount in the month to which l_payment_date belongs
756 -- for SI.
757 --
758 if l_index = 1 then
759 if l_train.amount_type = 'SALARY' then
760 l_sal_si_flag := true;
761 l_sal_si_wage := l_sal_si_wage + l_amount;
762 else
763 l_mtr_si_flag := true;
764 l_mtr_si_wage := l_mtr_si_wage + l_amount;
765 end if;
766 end if;
767 --
768 if l_enforced_taxable_flag <> 1 then
769 init_deemed_income(l_index);
770 if l_train.amount_type = 'SALARY' then
771 l_deemed_income(l_index).paid_sal_non_taxable_amount := l_deemed_income(l_index).paid_sal_non_taxable_amount + l_amount;
772 else
773 l_deemed_income(l_index).paid_mtr_non_taxable_amount := l_deemed_income(l_index).paid_mtr_non_taxable_amount + l_amount;
774 end if;
775 end if;
776 end if;
777 end loop;
778 --
779 l_prev_payment_date := add_months(l_prev_payment_date, l_train.period);
780 l_payment_date_offset := l_payment_date_offset + l_train.period;
781 end loop;
782 end if;
783 end loop;
784 --
785 -- Check non-taxable maximum for each month.
786 --
787 l_non_taxable_limit := p_non_taxable_limit_1mth * l_lcm;
788 --
789 l_sal_taxable_amount := 0;
790 l_sal_non_taxable_amount:= 0;
791 l_mtr_taxable_amount := 0;
792 l_mtr_non_taxable_amount:= 0;
793 --
794 i := l_deemed_income.first;
795 while i is not null loop
796 chk_non_taxable_limit( l_deemed_income(i).mtr_taxable_amount,
797 l_deemed_income(i).mtr_non_taxable_amount,
798 l_non_taxable_limit,
799 l_deemed_income(i).paid_sal_non_taxable_amount
800 + l_deemed_income(i).paid_mtr_non_taxable_amount);
801 chk_non_taxable_limit( l_deemed_income(i).sal_taxable_amount,
802 l_deemed_income(i).sal_non_taxable_amount,
803 l_non_taxable_limit,
804 l_deemed_income(i).paid_sal_non_taxable_amount
805 + l_deemed_income(i).paid_mtr_non_taxable_amount
806 + l_deemed_income(i).mtr_non_taxable_amount);
807 --
808 hr_utility.trace('***** i = ' || i || ' *****');
809 hr_utility.trace('d.sal_taxable_amount : ' || l_deemed_income(i).sal_taxable_amount);
810 hr_utility.trace('d.sal_non_taxable_amount : ' || l_deemed_income(i).sal_non_taxable_amount);
811 hr_utility.trace('d.mtr_taxable_amount : ' || l_deemed_income(i).mtr_taxable_amount);
812 hr_utility.trace('d.mtr_non_taxable_amount : ' || l_deemed_income(i).mtr_non_taxable_amount);
813 hr_utility.trace('d.paid_sal_non_taxable_amount: ' || l_deemed_income(i).paid_sal_non_taxable_amount);
814 hr_utility.trace('d.paid_mtr_non_taxable_amount: ' || l_deemed_income(i).paid_mtr_non_taxable_amount);
815 --
816 l_sal_taxable_amount := l_sal_taxable_amount + l_deemed_income(i).sal_taxable_amount;
817 l_sal_non_taxable_amount:= l_sal_non_taxable_amount + l_deemed_income(i).sal_non_taxable_amount;
818 l_mtr_taxable_amount := l_mtr_taxable_amount + l_deemed_income(i).mtr_taxable_amount;
819 l_mtr_non_taxable_amount:= l_mtr_non_taxable_amount + l_deemed_income(i).mtr_non_taxable_amount;
820 --
821 i := l_deemed_income.next(i);
822 end loop;
823 --
824 hr_utility.trace('***** before devide by ' || l_lcm || ' *****');
825 hr_utility.trace('l_sal_taxable_amount : ' || l_sal_taxable_amount);
826 hr_utility.trace('l_sal_non_taxable_amount: ' || l_sal_non_taxable_amount);
827 hr_utility.trace('l_mtr_taxable_amount : ' || l_mtr_taxable_amount);
828 hr_utility.trace('l_mtr_non_taxable_amount: ' || l_mtr_non_taxable_amount);
829 hr_utility.trace('l_sal_si_wage : ' || l_sal_si_wage);
830 hr_utility.trace('l_mtr_si_wage : ' || l_mtr_si_wage);
831 --
832 -- Adjust errors of non-taxable into taxable.
833 -- mtr_non_taxable_amount/mtr_taxable_amount must be integer values, no rounding required.
834 -- If there's decimal fraction, it is coding bug and should be fixed.
835 --
836 l_mod := mod(l_mtr_non_taxable_amount, l_lcm);
837 l_mtr_non_taxable_amount:= (l_mtr_non_taxable_amount - l_mod) / l_lcm;
838 l_mtr_taxable_amount := (l_mtr_taxable_amount + l_mod) / l_lcm;
839 --
840 -- Never carry over above fractional material non-taxable "l_mod" to salary non-taxable,
841 -- which could cause issue for "Enforce Taxable" udt values.
842 -- non_taxable_amount/taxable_amount must be integer values, no rounding required.
843 --
844 l_mod := mod(l_sal_non_taxable_amount, l_lcm);
845 l_sal_non_taxable_amount:= (l_sal_non_taxable_amount - l_mod) / l_lcm;
846 l_sal_taxable_amount := (l_sal_taxable_amount + l_mod) / l_lcm;
847 --
848 -- Truncated amount is returned for SI wages.
849 --
850 l_sal_si_wage := trunc(l_sal_si_wage / l_lcm);
851 l_mtr_si_wage := trunc(l_mtr_si_wage / l_lcm);
852 --
853 hr_utility.trace('***** after devide by ' || l_lcm || ' *****');
854 hr_utility.trace('l_sal_taxable_amount : ' || l_sal_taxable_amount);
855 hr_utility.trace('l_sal_non_taxable_amount: ' || l_sal_non_taxable_amount);
856 hr_utility.trace('l_mtr_taxable_amount : ' || l_mtr_taxable_amount);
857 hr_utility.trace('l_mtr_non_taxable_amount: ' || l_mtr_non_taxable_amount);
858 hr_utility.trace('l_sal_si_wage : ' || l_sal_si_wage);
859 hr_utility.trace('l_mtr_si_wage : ' || l_mtr_si_wage);
860 hr_utility.trace('l_sal_income : ' || l_sal_income);
861 hr_utility.trace('l_mtr_income : ' || l_mtr_income);
862 --
863 -- This is just to ensure that payment is split correctly.
864 -- Errors will never be raised.
865 --
866 if (l_sal_taxable_amount + l_sal_non_taxable_amount) <> l_sal_income
867 or (l_mtr_taxable_amount + l_mtr_non_taxable_amount) <> l_mtr_income then
868 fnd_message.set_name('PAY', 'PAY_JP_CMA_INV_TOTAL_PAY');
869 fnd_message.raise_error;
870 end if;
871 --
872 -- This is just to ensure each amount is integer amount.
873 -- Errors will never be raised.
874 --
875 if hr_jp_standard_pkg.is_integer(l_sal_taxable_amount) = 'N'
876 or hr_jp_standard_pkg.is_integer(l_sal_non_taxable_amount) = 'N'
877 or hr_jp_standard_pkg.is_integer(l_mtr_taxable_amount) = 'N'
878 or hr_jp_standard_pkg.is_integer(l_mtr_non_taxable_amount) = 'N' then
879 fnd_message.set_name('PAY', 'PAY_JP_CMA_INVALID_AMOUNT');
880 fnd_message.raise_error;
881 end if;
882 --
883 -- Initialization for cma_rec.
884 -- Unnecessary values are returned by null values.
885 --
886 if l_sal_taxable_flag or l_sal_non_taxable_flag then
887 if l_sal_taxable_amount = 0 and l_sal_non_taxable_amount = 0 then
888 if l_sal_taxable_flag then
889 p_cma_rec.taxable_amount := l_sal_taxable_amount;
890 else
891 p_cma_rec.non_taxable_amount := l_sal_non_taxable_amount;
892 end if;
893 else
894 if l_sal_taxable_amount <> 0 then
895 p_cma_rec.taxable_amount := l_sal_taxable_amount;
896 end if;
897 --
898 if l_sal_non_taxable_amount <> 0 then
899 p_cma_rec.non_taxable_amount := l_sal_non_taxable_amount;
900 end if;
901 end if;
902 end if;
903 --
904 if l_mtr_taxable_flag or l_mtr_non_taxable_flag then
905 if l_mtr_taxable_amount = 0 and l_mtr_non_taxable_amount = 0 then
906 if l_mtr_taxable_flag then
907 p_cma_rec.mtr_taxable_amount := l_mtr_taxable_amount;
908 else
909 p_cma_rec.mtr_non_taxable_amount := l_mtr_non_taxable_amount;
910 end if;
911 else
912 if l_mtr_taxable_amount <> 0 then
913 p_cma_rec.mtr_taxable_amount := l_mtr_taxable_amount;
914 end if;
915 --
916 if l_mtr_non_taxable_amount <> 0 then
917 p_cma_rec.mtr_non_taxable_amount := l_mtr_non_taxable_amount;
918 end if;
919 end if;
920 end if;
921 --
922 if l_sal_si_flag then
923 p_cma_rec.si_wage := l_sal_si_wage;
924 --
925 -- Return null when 0 for adjustment values.
926 --
927 if l_sal_si_wage <> 0 then
928 p_cma_rec.si_wage_adj := - l_sal_si_wage;
929 end if;
930 end if;
931 --
932 if l_mtr_si_flag then
933 p_cma_rec.mtr_si_wage := l_mtr_si_wage;
934 --
935 -- Return null when 0 for adjustment values.
936 --
937 if l_mtr_si_wage <> 0 then
938 p_cma_rec.mtr_si_wage_adj := - l_mtr_si_wage;
939 end if;
940 end if;
941 --
942 if l_sal_si_flag or l_mtr_si_flag then
943 p_cma_rec.si_fixed_wage := l_sal_si_wage + l_mtr_si_wage;
944 p_cma_rec.ui_wage_adj := l_sal_si_wage + l_mtr_si_wage
945 - l_sal_income - l_mtr_income;
946 --
947 -- Return null when 0 for adjustment values.
948 --
949 if p_cma_rec.ui_wage_adj = 0 then
950 p_cma_rec.ui_wage_adj := null;
951 end if;
952 end if;
953 --
954 hr_utility.trace('***** output *****');
955 hr_utility.trace('p.taxable_amount : ' || p_cma_rec.taxable_amount);
956 hr_utility.trace('p.non_taxable_amount : ' || p_cma_rec.non_taxable_amount);
957 hr_utility.trace('p.mtr_taxable_amount : ' || p_cma_rec.mtr_taxable_amount);
958 hr_utility.trace('p.mtr_non_taxable_amount : ' || p_cma_rec.mtr_non_taxable_amount);
959 hr_utility.trace('p.si_wage : ' || p_cma_rec.si_wage);
960 hr_utility.trace('p.si_wage_adj : ' || p_cma_rec.si_wage_adj);
961 hr_utility.trace('p.mtr_si_wage : ' || p_cma_rec.mtr_si_wage);
962 hr_utility.trace('p.mtr_si_wage_adj : ' || p_cma_rec.mtr_si_wage_adj);
963 hr_utility.trace('p.si_fixed_wage : ' || p_cma_rec.si_fixed_wage);
964 hr_utility.trace('p.ui_wage_adj : ' || p_cma_rec.ui_wage_adj);
965 if p_record_exist then
966 hr_utility.trace('p_record_exist : TRUE');
967 else
968 hr_utility.trace('p_record_exist : FALSE');
969 end if;
970 --
971 hr_utility.set_location('Leaving: ' || c_proc, 100);
972 end get_cma_info;
973 --
974 end per_jp_cma_utility_pkg;