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