[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_BALANCE_VIEW_PKG
Source
1 package body pay_jp_balance_view_pkg as
2 /* $Header: pyjpbalv.pkb 120.0 2006/04/24 00:03 ttagawa noship $ */
3 --
4 -- Global Variables
5 --
6 type t_assignment_rec is record(
7 assignment_id number,
8 effective_date date,
9 business_group_id number,
10 payroll_id number,
11 time_period_id number);
12 g_assignment_rec t_assignment_rec;
13 --
14 type t_defined_balance_rec is record(
15 defined_balance_id number,
16 balance_type_id number,
17 dimension_name pay_balance_dimensions.dimension_name%type,
18 business_group_id number,
19 dimension_level pay_balance_dimensions.dimension_level%type,
20 date_type varchar2(30),
21 period_type pay_balance_dimensions.period_type%type,
22 start_date_code pay_balance_dimensions.start_date_code%type);
23 g_defined_balance_rec t_defined_balance_rec;
24 -- ----------------------------------------------------------------------------
25 -- |-------------------------< get_assignment_info >--------------------------|
26 -- ----------------------------------------------------------------------------
27 procedure get_assignment_info(
28 p_assignment_id in number,
29 p_effective_date in date,
30 p_assignment_rec out nocopy t_assignment_rec)
31 is
32 l_assignment_rec t_assignment_rec;
33 begin
34 if g_assignment_rec.assignment_id = p_assignment_id
35 and g_assignment_rec.effective_date = p_effective_date then
36 null;
37 else
38 select asg.business_group_id,
39 asg.payroll_id,
40 ptp.time_period_id
41 into l_assignment_rec.business_group_id,
42 l_assignment_rec.payroll_id,
43 l_assignment_rec.time_period_id
44 from per_all_assignments_f asg,
45 per_time_periods ptp
46 where asg.assignment_id = p_assignment_id
47 and p_effective_date
48 between asg.effective_start_date and asg.effective_end_date
49 and ptp.payroll_id(+) = asg.payroll_id
50 and p_effective_date
51 between ptp.start_date(+) and ptp.end_date(+);
52 --
53 l_assignment_rec.assignment_id := p_assignment_id;
54 l_assignment_rec.effective_date := p_effective_date;
55 g_assignment_rec := l_assignment_rec;
56 end if;
57 --
58 p_assignment_rec := g_assignment_rec;
59 end get_assignment_info;
60 -- ----------------------------------------------------------------------------
61 -- |-----------------------< get_defined_balance_info >-----------------------|
62 -- ----------------------------------------------------------------------------
63 procedure get_defined_balance_info(
64 p_balance_type_id in number,
65 p_dimension_name in varchar2,
66 p_business_group_id in number,
67 p_defined_balance_rec out nocopy t_defined_balance_rec)
68 is
69 l_legislation_code pay_defined_balances.legislation_code%type;
70 l_defined_balance_rec t_defined_balance_rec;
71 begin
72 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
73 --
74 if g_defined_balance_rec.balance_type_id = p_balance_type_id
75 and g_defined_balance_rec.dimension_name = p_dimension_name
76 and g_defined_balance_rec.business_group_id = p_business_group_id then
77 null;
78 else
79 begin
80 select def.defined_balance_id,
81 dim.dimension_level,
82 pay_core_utils.get_parameter('DATE_TYPE', dim.description),
83 dim.period_type,
84 dim.start_date_code
85 into l_defined_balance_rec.defined_balance_id,
86 l_defined_balance_rec.dimension_level,
87 l_defined_balance_rec.date_type,
88 l_defined_balance_rec.period_type,
89 l_defined_balance_rec.start_date_code
90 from pay_defined_balances def,
91 pay_balance_dimensions dim
92 where def.balance_type_id = p_balance_type_id
93 and nvl(def.business_group_id, p_business_group_id) = p_business_group_id
94 and nvl(def.legislation_code, l_legislation_code) = l_legislation_code
95 and dim.balance_dimension_id = def.balance_dimension_id
96 and dim.dimension_name = p_dimension_name;
97 --
98 l_defined_balance_rec.balance_type_id := p_balance_type_id;
99 l_defined_balance_rec.dimension_name := p_dimension_name;
100 l_defined_balance_rec.business_group_id := p_business_group_id;
101 --
102 g_defined_balance_rec := l_defined_balance_rec;
103 exception
104 when no_data_found then
105 return;
106 end;
107 end if;
108 --
109 p_defined_balance_rec := g_defined_balance_rec;
110 end get_defined_balance_info;
111 -- ----------------------------------------------------------------------------
112 -- |-----------------------< get_defined_balance_info >-----------------------|
113 -- ----------------------------------------------------------------------------
114 procedure get_defined_balance_info(
115 p_defined_balance_id in number,
116 p_defined_balance_rec out nocopy t_defined_balance_rec)
117 is
118 l_defined_balance_rec t_defined_balance_rec;
119 begin
120 if g_defined_balance_rec.defined_balance_id = p_defined_balance_id then
121 null;
122 else
123 begin
124 select def.balance_type_id,
125 dim.dimension_name,
126 def.business_group_id,
127 dim.dimension_level,
128 pay_core_utils.get_parameter('DATE_TYPE', dim.description),
129 dim.period_type,
130 dim.start_date_code
131 into l_defined_balance_rec.balance_type_id,
132 l_defined_balance_rec.dimension_name,
133 l_defined_balance_rec.business_group_id,
134 l_defined_balance_rec.dimension_level,
135 l_defined_balance_rec.date_type,
136 l_defined_balance_rec.period_type,
137 l_defined_balance_rec.start_date_code
138 from pay_defined_balances def,
139 pay_balance_dimensions dim
140 where def.defined_balance_id = p_defined_balance_id
141 and dim.balance_dimension_id = def.balance_dimension_id;
142 --
143 l_defined_balance_rec.defined_balance_id := p_defined_balance_id;
144 --
145 g_defined_balance_rec := l_defined_balance_rec;
146 exception
147 when no_data_found then
148 null;
149 end;
150 end if;
151 --
152 p_defined_balance_rec := g_defined_balance_rec;
153 end get_defined_balance_info;
154 -- ----------------------------------------------------------------------------
155 -- |-------------------------< get_latest_action_id >-------------------------|
156 -- ----------------------------------------------------------------------------
157 function get_latest_action_id(
158 p_assignment_id in number,
159 p_effective_date in date,
160 p_start_date in date default null,
161 p_dimension_level in varchar2 default 'ASG',
162 p_date_type in varchar2 default 'DP') return number
163 is
164 l_assignment_action_id number;
165 begin
166 if p_dimension_level = 'ASG' then
167 if p_date_type = 'DE' then
168 select /*+ ORDERED USE_NL(PPA) */
169 to_number(substr(
170 max(
171 to_char(ppa.date_earned, 'YYYYMMDD') ||
172 to_char(paa.action_sequence, 'FM099999999999999') ||
173 to_char(paa.assignment_action_id)
174 )
175 , 24))
176 into l_assignment_action_id
177 from pay_assignment_actions paa,
178 pay_payroll_actions ppa
179 where paa.assignment_id = p_assignment_id
180 and paa.action_status = 'C'
181 and ppa.payroll_action_id = paa.payroll_action_id
182 and ppa.date_earned
183 between nvl(p_start_date, ppa.date_earned) and p_effective_date
184 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
185 else
186 select /*+ ORDERED USE_NL(PPA) */
187 to_number(substr(
188 max(
189 to_char(paa.action_sequence, 'FM099999999999999') ||
190 to_char(paa.assignment_action_id)
191 )
192 , 16))
193 into l_assignment_action_id
194 from pay_assignment_actions paa,
195 pay_payroll_actions ppa
196 where paa.assignment_id = p_assignment_id
197 and paa.action_status = 'C'
198 and ppa.payroll_action_id = paa.payroll_action_id
199 and ppa.effective_date
200 between nvl(p_start_date, ppa.effective_date) and p_effective_date
201 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
202 end if;
203 --
204 -- This PER level dimension works only when "Independent Time Period"(I)
205 -- legislation rule is set to "N".
206 --
207 elsif p_dimension_level = 'PER' then
208 if p_date_type = 'DE' then
209 select /*+ ORDERED USE_NL(PAA PPA) NO_EXPAND */
210 to_number(substr(
211 max(
212 to_char(ppa.date_earned, 'YYYYMMDD') ||
213 to_char(paa.action_sequence, 'FM099999999999999') ||
214 to_char(paa.assignment_action_id)
215 )
216 , 24))
217 into l_assignment_action_id
218 from (
219 select asg2.assignment_id
220 from per_all_assignments_f asg,
221 per_all_assignments_f asg2
222 where asg.assignment_id = p_assignment_id
223 and p_effective_date
224 between asg.effective_start_date and asg.effective_end_date
225 and asg2.person_id = asg.person_id
226 group by asg2.assignment_id
227 ) v,
228 pay_assignment_actions paa,
229 pay_payroll_actions ppa
230 where paa.assignment_id = v.assignment_id
231 and paa.action_status = 'C'
232 and ppa.payroll_action_id = paa.payroll_action_id
233 and ppa.date_earned
234 between nvl(p_start_date, ppa.date_earned) and p_effective_date
235 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
236 else
237 select /*+ ORDERED USE_NL(PAA PPA) NO_EXPAND */
238 to_number(substr(
239 max(
240 to_char(paa.action_sequence, 'FM099999999999999') ||
241 to_char(paa.assignment_action_id)
242 )
243 , 16))
244 into l_assignment_action_id
245 from (
246 select asg2.assignment_id
247 from per_all_assignments_f asg,
248 per_all_assignments_f asg2
249 where asg.assignment_id = p_assignment_id
250 and p_effective_date
251 between asg.effective_start_date and asg.effective_end_date
252 and asg2.person_id = asg.person_id
253 group by asg2.assignment_id
254 ) v,
255 pay_assignment_actions paa,
256 pay_payroll_actions ppa
257 where paa.assignment_id = v.assignment_id
258 and paa.action_status = 'C'
259 and ppa.payroll_action_id = paa.payroll_action_id
260 and ppa.effective_date
261 between nvl(p_start_date, ppa.effective_date) and p_effective_date
262 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
263 end if;
264 else
265 fnd_message.set_name('PAY', 'PAY_JP_INV_DIMENSION_LEVEL');
266 fnd_message.set_token('DIMENSION_LEVEL', p_dimension_level);
267 fnd_message.raise_error;
268 end if;
269 --
270 return l_assignment_action_id;
271 exception
272 when no_data_found then
273 return null;
274 end get_latest_action_id;
275 -- ----------------------------------------------------------------------------
276 -- |---------------------< get_period_latest_action_id >----------------------|
277 -- ----------------------------------------------------------------------------
278 function get_period_latest_action_id(
279 p_assignment_id in number,
280 p_effective_date in date,
281 p_dimension_level in varchar2 default 'ASG') return number
282 is
283 l_assignment_rec t_assignment_rec;
284 l_assignment_action_id number;
285 begin
286 get_assignment_info(
287 p_assignment_id => p_assignment_id,
288 p_effective_date => p_effective_date,
289 p_assignment_rec => l_assignment_rec);
290 --
291 if l_assignment_rec.time_period_id is not null then
292 begin
293 --
294 -- DATE_EARNED is not supported for PTD dimensions.
295 --
296 if p_dimension_level = 'ASG' then
297 select /*+ ORDERED USE_NL(PPA) */
298 to_number(substr(
299 max(
300 to_char(paa.action_sequence, 'FM099999999999999') ||
301 to_char(paa.assignment_action_id)
302 )
303 , 16))
304 into l_assignment_action_id
305 from pay_assignment_actions paa,
306 pay_payroll_actions ppa
307 where paa.assignment_id = p_assignment_id
308 and paa.action_status = 'C'
309 and ppa.payroll_action_id = paa.payroll_action_id
310 and ppa.time_period_id = l_assignment_rec.time_period_id
311 and ppa.effective_date <= p_effective_date
312 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
313 --
314 -- This PER level dimension works only when "Independent Time Period"(I)
315 -- legislation rule is set to "N".
316 --
317 elsif p_dimension_level = 'PER' then
318 select /*+ ORDERED USE_NL(PAA PPA) */
319 to_number(substr(
320 max(
321 to_char(paa.action_sequence, 'FM099999999999999') ||
322 to_char(paa.assignment_action_id)
323 )
324 , 16))
325 into l_assignment_action_id
326 from (
327 select asg2.assignment_id
328 from per_all_assignments_f asg,
329 per_all_assignments_f asg2
330 where asg.assignment_id = p_assignment_id
331 and p_effective_date
332 between asg.effective_start_date and asg.effective_end_date
333 and asg2.person_id = asg.person_id
334 group by asg2.assignment_id
335 ) v,
336 pay_assignment_actions paa,
337 pay_payroll_actions ppa
338 where paa.assignment_id = v.assignment_id
339 and paa.action_status = 'C'
340 and ppa.payroll_action_id = paa.payroll_action_id
341 and ppa.time_period_id = l_assignment_rec.time_period_id
342 and ppa.effective_date <= p_effective_date
343 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V');
344 else
345 fnd_message.set_name('PAY', 'PAY_JP_INV_DIMENSION_LEVEL');
346 fnd_message.set_token('DIMENSION_LEVEL', p_dimension_level);
347 fnd_message.raise_error;
348 end if;
349 exception
350 when no_data_found then
351 return null;
352 end;
353 end if;
354 --
355 return l_assignment_action_id;
356 end get_period_latest_action_id;
357 -- ----------------------------------------------------------------------------
358 -- |------------------------< get_value (date mode) >-------------------------|
359 -- ----------------------------------------------------------------------------
360 function get_value(
361 p_assignment_id in number,
362 p_effective_date in date,
363 p_defined_balance_id in number,
364 p_dimension_level in varchar2,
365 p_date_type in varchar2,
366 p_period_type in varchar2,
367 p_start_date_code in varchar2,
368 p_dimension_name in varchar2,
369 p_original_entry_id in number default null) return number
370 is
371 l_assignment_action_id number;
372 l_balance_value number;
373 --
374 l_assignment_rec t_assignment_rec;
375 l_start_date date;
376 l_date_type varchar2(30);
377 begin
378 if p_period_type is not null then
379 if p_period_type = 'PAYMENT' then
380 l_assignment_action_id := null;
381 --
382 -- In case of RUN, pick up latest assignment_action_id within the payroll period.
383 --
384 elsif p_period_type in ('RUN', 'PERIOD') then
385 l_assignment_action_id := get_period_latest_action_id(
386 p_assignment_id => p_assignment_id,
387 p_effective_date => p_effective_date,
388 p_dimension_level => p_dimension_level);
389 elsif p_period_type = 'LIFETIME' then
390 l_assignment_action_id := get_latest_action_id(
391 p_assignment_id => p_assignment_id,
392 p_effective_date => p_effective_date,
393 p_start_date => null,
394 p_dimension_level => p_dimension_level,
395 p_date_type => p_date_type);
396 else
397 get_assignment_info(
398 p_assignment_id => p_assignment_id,
399 p_effective_date => p_effective_date,
400 p_assignment_rec => l_assignment_rec);
401 --
402 pay_balance_pkg.get_period_type_start(
403 p_period_type => p_period_type,
404 p_effective_date => p_effective_date,
405 p_start_date => l_start_date,
406 p_start_date_code => p_start_date_code,
407 p_payroll_id => l_assignment_rec.payroll_id,
408 p_bus_grp => l_assignment_rec.business_group_id);
409 --
410 l_assignment_action_id := get_latest_action_id(
411 p_assignment_id => p_assignment_id,
412 p_effective_date => p_effective_date,
413 p_start_date => l_start_date,
414 p_dimension_level => p_dimension_level,
415 p_date_type => p_date_type);
416 end if;
417 else
418 --
419 -- Old User Defined Dimensions
420 --
421 if substr(rpad(p_dimension_name, 68), -8) = 'USER-REG' then
422 l_start_date := hr_jprts.dimension_reset_date(p_dimension_name, p_effective_date);
423 --
424 l_date_type := rtrim(substr(rpad(p_dimension_name, 44), -14));
425 if l_date_type = 'DATE_EARNED' then
426 l_date_type := 'DE';
427 else
428 l_date_type := 'DP';
429 end if;
430 --
431 l_assignment_action_id := get_latest_action_id(
432 p_assignment_id => p_assignment_id,
433 p_effective_date => p_effective_date,
434 p_start_date => l_start_date,
435 p_dimension_level => 'ASG',
436 p_date_type => l_date_type);
437 else
438 --
439 -- Unknown Balance Dimension
440 -- pay_balance_pkg.get_value(date mode) cannot be called
441 -- which will raise error if this function is called in SQL statement
442 -- because of "rollback to savepoint" call exists in get_value(date mode).
443 --
444 l_assignment_action_id := -1;
445 end if;
446 end if;
447 --
448 if l_assignment_action_id > 0 then
449 l_balance_value := pay_balance_pkg.get_value(
450 p_defined_balance_id => p_defined_balance_id,
451 p_assignment_action_id => l_assignment_action_id,
452 p_tax_unit_id => null,
453 p_jurisdiction_code => null,
454 p_source_id => null,
455 p_source_text => null,
456 p_tax_group => null,
457 p_original_entry_id => p_original_entry_id,
458 p_date_earned => null);
459 elsif l_assignment_action_id = -1 then
460 l_balance_value := null;
461 else
462 l_balance_value := 0;
463 end if;
464 --
465 return l_balance_value;
466 end get_value;
467 -- ----------------------------------------------------------------------------
468 -- |------------------------< get_value (date mode) >-------------------------|
469 -- ----------------------------------------------------------------------------
470 function get_value(
471 p_assignment_id in number,
472 p_effective_date in date,
473 p_defined_balance_id in number,
474 p_original_entry_id in number default null) return number
475 is
476 l_defined_balance_rec t_defined_balance_rec;
477 l_balance_value number;
478 begin
479 get_defined_balance_info(
480 p_defined_balance_id => p_defined_balance_id,
481 p_defined_balance_rec => l_defined_balance_rec);
482 --
483 l_balance_value := get_value(
484 p_assignment_id => p_assignment_id,
485 p_effective_date => p_effective_date,
486 p_defined_balance_id => l_defined_balance_rec.defined_balance_id,
487 p_dimension_level => l_defined_balance_rec.dimension_level,
488 p_date_type => l_defined_balance_rec.date_type,
489 p_period_type => l_defined_balance_rec.period_type,
490 p_start_date_code => l_defined_balance_rec.start_date_code,
491 p_dimension_name => l_defined_balance_rec.dimension_name,
492 p_original_entry_id => p_original_entry_id);
493 --
494 return l_balance_value;
495 end get_value;
496 -- ----------------------------------------------------------------------------
497 -- |------------------------< get_value (date mode) >-------------------------|
498 -- ----------------------------------------------------------------------------
499 function get_value(
500 p_assignment_id in number,
501 p_effective_date in date,
502 p_balance_type_id in number,
503 p_dimension_name in varchar2,
504 p_business_group_id in number,
505 p_original_entry_id in number default null) return number
506 is
507 l_defined_balance_rec t_defined_balance_rec;
508 l_balance_value number;
509 begin
510 get_defined_balance_info(
511 p_balance_type_id => p_balance_type_id,
512 p_dimension_name => p_dimension_name,
513 p_business_group_id => p_business_group_id,
514 p_defined_balance_rec => l_defined_balance_rec);
515 --
516 if l_defined_balance_rec.defined_balance_id is not null then
517 /*
518 --
519 -- This is to pick up latest assignment_action_id within the payroll period.
520 -- If RUN is passed, then balance value is always "0" in date mode.
521 --
522 if l_defined_balance_rec.period_type = 'RUN' then
523 l_defined_balance_rec.period_type := 'PERIOD';
524 end if;
525 */
526 --
527 l_balance_value := get_value(
528 p_assignment_id => p_assignment_id,
529 p_effective_date => p_effective_date,
530 p_defined_balance_id => l_defined_balance_rec.defined_balance_id,
531 p_dimension_level => l_defined_balance_rec.dimension_level,
532 p_date_type => l_defined_balance_rec.date_type,
533 p_period_type => l_defined_balance_rec.period_type,
534 p_start_date_code => l_defined_balance_rec.start_date_code,
535 p_dimension_name => l_defined_balance_rec.dimension_name,
536 p_original_entry_id => p_original_entry_id);
537 end if;
538 --
539 return l_balance_value;
540 end get_value;
541 -- ----------------------------------------------------------------------------
542 -- |-----------------------< get_value (action mode) >------------------------|
543 -- ----------------------------------------------------------------------------
544 function get_value(
545 p_assignment_action_id in number,
546 p_balance_type_id in number,
547 p_dimension_name in varchar2,
548 p_business_group_id in number,
549 p_original_entry_id in number default null) return number
550 is
551 l_defined_balance_rec t_defined_balance_rec;
552 l_balance_value number;
553 begin
554 get_defined_balance_info(
555 p_balance_type_id => p_balance_type_id,
556 p_dimension_name => p_dimension_name,
557 p_business_group_id => p_business_group_id,
558 p_defined_balance_rec => l_defined_balance_rec);
559 --
560 if l_defined_balance_rec.defined_balance_id is not null then
561 l_balance_value := pay_balance_pkg.get_value(
562 p_defined_balance_id => l_defined_balance_rec.defined_balance_id,
563 p_assignment_action_id => p_assignment_action_id,
564 p_tax_unit_id => null,
565 p_jurisdiction_code => null,
566 p_source_id => null,
567 p_source_text => null,
568 p_tax_group => null,
569 p_original_entry_id => p_original_entry_id,
570 p_date_earned => null);
571 end if;
572 --
573 return l_balance_value;
574 end get_value;
575 --
576 end pay_jp_balance_view_pkg;