[Home] [Help]
PACKAGE BODY: APPS.PYJPEXC
Source
1 package body pyjpexc as
2 /* $Header: pyjpexc.pkb 120.2.12000000.2 2007/03/05 08:53:51 keyazawa noship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pyjpexc.';
7 --
8 -- Global Variables
9 --
10 -- Fiscal Year Start Date Cache
11 --
12 type t_fy_cache is record(
13 business_group_id number,
14 start_date date);
15 g_fy_cache t_fy_cache;
16 type t_pact_cache is record(
17 payroll_action_id number,
18 business_group_id number);
19 g_pact_cache t_pact_cache;
20 --
21 function asg_run return varchar2 is begin return c_asg_run; end asg_run;
22 function asg_ptd return varchar2 is begin return c_asg_ptd; end asg_ptd;
23 function asg_mtd return varchar2 is begin return c_asg_mtd; end asg_mtd;
24 function asg_ytd return varchar2 is begin return c_asg_ytd; end asg_ytd;
25 function asg_aprtd return varchar2 is begin return c_asg_aprtd; end asg_aprtd;
26 function asg_jultd return varchar2 is begin return c_asg_jultd; end asg_jultd;
27 function asg_augtd return varchar2 is begin return c_asg_augtd; end asg_augtd;
28 function asg_fytd return varchar2 is begin return c_asg_fytd; end asg_fytd;
29 function asg_fytd_de return varchar2 is begin return c_asg_fytd_de; end asg_fytd_de;
30 function asg_ltd return varchar2 is begin return c_asg_ltd; end asg_ltd;
31 function element_ptd return varchar2 is begin return c_element_ptd; end element_ptd;
32 function element_ltd return varchar2 is begin return c_element_ltd; end element_ltd;
33 function asg_retro return varchar2 is begin return c_asg_retro; end asg_retro;
34 function payments return varchar2 is begin return c_payments; end payments;
35 -- ----------------------------------------------------------------------------
36 -- |--------------------------------< ptd_ec >--------------------------------|
37 -- ----------------------------------------------------------------------------
38 procedure ptd_ec(
39 p_owner_payroll_action_id in number,
40 p_user_payroll_action_id in number,
41 p_owner_assignment_action_id in number,
42 p_user_assignment_action_id in number,
43 p_owner_effective_date in date,
44 p_user_effective_date in date,
45 p_dimension_name in varchar2,
46 p_expiry_information out nocopy number)
47 is
48 c_proc constant varchar2(61) := c_package || 'ptd_ec';
49 --
50 l_owner_time_period_id number;
51 l_user_time_period_id number;
52 begin
53 hr_utility.set_location('Entering : ' || c_proc, 10);
54 hr_utility.trace(p_dimension_name);
55 --
56 select ppa_owner.time_period_id,
57 ppa_user.time_period_id
58 into l_owner_time_period_id,
59 l_user_time_period_id
60 from pay_payroll_actions ppa_user,
61 pay_payroll_actions ppa_owner
62 where ppa_owner.payroll_action_id = p_owner_payroll_action_id
63 and ppa_user.payroll_action_id = p_user_payroll_action_id;
64 --
65 -- Note this expiry checking mechanism has bug.2646992
66 --
67 if l_user_time_period_id = l_owner_time_period_id then
68 hr_utility.trace('NOT expired');
69 p_expiry_information := 0;
70 else
71 hr_utility.trace('expired');
72 p_expiry_information := 1;
73 end if;
74 --
75 hr_utility.set_location('Leaving : ' || c_proc, 20);
76 end ptd_ec;
77 -- ----------------------------------------------------------------------------
78 -- |--------------------------< ptd_ec (date mode)>---------------------------|
79 -- ----------------------------------------------------------------------------
80 procedure ptd_ec(
81 p_owner_payroll_action_id in number,
82 p_user_payroll_action_id in number,
83 p_owner_assignment_action_id in number,
84 p_user_assignment_action_id in number,
85 p_owner_effective_date in date,
86 p_user_effective_date in date,
87 p_dimension_name in varchar2,
88 p_expiry_information out nocopy date)
89 is
90 c_proc constant varchar2(61) := c_package || 'ptd_ec (date mode)';
91 --
92 -- l_owner_time_period_id number;
93 -- l_user_time_period_id number;
94 begin
95 hr_utility.set_location('Entering : ' || c_proc, 10);
96 --
97 select ptp.end_date
98 into p_expiry_information
99 from pay_payroll_actions ppa,
100 per_time_periods ptp
101 where ppa.payroll_action_id = p_owner_payroll_action_id
102 and ptp.time_period_id = ppa.time_period_id;
103 /*
104 --
105 -- Note this expiry checking mechanism has bug.2646992
106 --
107 -- We do not support the payroll change and back to the same payroll
108 -- within the same payroll period. For example,
109 -- Payroll A --> Payroll B --> Payroll A in January for an assignment.
110 -- If you do this, the latest balance will get corrupted.
111 --
112 -- Date mode expiry checking is called with the same payroll_action_id,
113 -- assignment_action_id and effective_date, which means
114 -- p_owner_payroll_action_id = p_user_payroll_action_id
115 -- p_owner_assignment_action_id = p_user_payroll_action_id
116 -- p_owner_effective_date = p_user_effective_date
117 -- This expiry checking is called for both owner and user payroll action,
118 -- then the expiry_date is compared.
119 --
120 hr_utility.trace(p_dimension_name);
121 hr_utility.trace('owner_payroll_action_id : ' || to_char(p_owner_payroll_action_id));
122 hr_utility.trace('user_payroll_action_id : ' || to_char(p_user_payroll_action_id));
123 hr_utility.trace('owner_assignment_action_id : ' || to_char(p_owner_assignment_action_id));
124 hr_utility.trace('user_assignment_action_id : ' || to_char(p_user_assignment_action_id));
125 hr_utility.trace('owner_effective_date : ' || to_char(p_owner_effective_date));
126 hr_utility.trace('user_effective_date : ' || to_char(p_user_effective_date));
127 --
128 -- Following SQL will return period end date in general.
129 -- But the payroll is changed in the middle of the payroll period,
130 -- this returns the previous date of the date changed.
131 --
132 select least(max(asg.effective_end_date), ptp_owner.end_date)
133 into p_expiry_information
134 from per_all_assignments_f asg,
135 per_time_periods ptp_owner,
136 pay_payroll_actions ppa_owner,
137 pay_assignment_actions paa_owner
138 where paa_owner.assignment_action_id = p_owner_assignment_action_id
139 and ppa_owner.payroll_action_id = p_owner_payroll_action_id
140 and ptp_owner.time_period_id = ppa_owner.time_period_id
141 and asg.assignment_id = paa_owner.assignment_id
142 and asg.effective_end_date >= p_owner_effective_date
143 and asg.effective_start_date <= ptp_owner.end_date
144 and asg.payroll_id + 0 = ppa_owner.payroll_id
145 group by ptp_owner.end_date;
146 */
147 /*
148 select ppa_owner.time_period_id,
149 ppa_user.time_period_id
150 into l_owner_time_period_id,
151 l_user_time_period_id
152 from per_time_periods ptp_user,
153 pay_payroll_actions ppa_user,
154 per_time_periods ptp_owner,
155 pay_payroll_actions ppa_owner
156 where ppa_owner.payroll_action_id = p_owner_payroll_action_id
157 and ptp_owner.time_period_id = ppa_owner.time_period_id
158 and ppa_user.payroll_action_id = p_user_payroll_action_id
159 and ptp_user.time_period_id = ppa_user.time_period_id;
160 --
161 -- We do not need to return the accurate expiry date here.
162 -- This expiry_date is used as follows.
163 -- 1) expiry_date is greater equal owner_effective_date, or less than owner_effective_date
164 -- 2) expiry_date is greater equal user_effective_date, or less than user_effective_date
165 -- Note always p_user_effective_date >= p_owner_effective_date.
166 --
167 if l_owner_time_period_id <> l_user_time_period_id then
168 p_expiry_information := p_owner_effective_date;
169 else
170 p_expiry_information := p_user_effective_date;
171 end if;
172 */
173 --
174 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
175 hr_utility.set_location('Leaving : ' || c_proc, 20);
176 end ptd_ec;
177 -- ----------------------------------------------------------------------------
178 -- |--------------------------------< mtd_ec >--------------------------------|
179 -- ----------------------------------------------------------------------------
180 procedure mtd_ec(
181 p_owner_payroll_action_id in number,
182 p_user_payroll_action_id in number,
183 p_owner_assignment_action_id in number,
184 p_user_assignment_action_id in number,
185 p_owner_effective_date in date,
186 p_user_effective_date in date,
187 p_dimension_name in varchar2,
188 p_expiry_information out nocopy number)
189 is
190 c_proc constant varchar2(61) := c_package || 'mtd_ec';
191 begin
192 hr_utility.set_location('Entering : ' || c_proc, 10);
193 hr_utility.trace(p_dimension_name);
194 --
195 if trunc(p_user_effective_date, 'MM') > p_owner_effective_date then
196 hr_utility.trace('expired');
197 p_expiry_information := 1;
198 else
199 hr_utility.trace('NOT expired');
200 p_expiry_information := 0;
201 end if;
202 --
203 hr_utility.set_location('Leaving : ' || c_proc, 20);
204 end mtd_ec;
205 -- ----------------------------------------------------------------------------
206 -- |--------------------------< mtd_ec (date mode)>---------------------------|
207 -- ----------------------------------------------------------------------------
208 procedure mtd_ec(
209 p_owner_payroll_action_id in number,
210 p_user_payroll_action_id in number,
211 p_owner_assignment_action_id in number,
212 p_user_assignment_action_id in number,
213 p_owner_effective_date in date,
214 p_user_effective_date in date,
215 p_dimension_name in varchar2,
216 p_expiry_information out nocopy date)
217 is
218 c_proc constant varchar2(61) := c_package || 'mtd_ec (date mode)';
219 begin
220 hr_utility.set_location('Entering : ' || c_proc, 10);
221 hr_utility.trace(p_dimension_name);
222 --
223 p_expiry_information := last_day(p_owner_effective_date);
224 hr_utility.trace('owner_date : ' || to_char(p_owner_effective_date));
225 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
226 --
227 hr_utility.set_location('Leaving : ' || c_proc, 20);
228 end mtd_ec;
229 -- ----------------------------------------------------------------------------
230 -- |--------------------------------< qtd_ec >--------------------------------|
231 -- ----------------------------------------------------------------------------
232 procedure qtd_ec(
233 p_owner_payroll_action_id in number,
234 p_user_payroll_action_id in number,
235 p_owner_assignment_action_id in number,
236 p_user_assignment_action_id in number,
237 p_owner_effective_date in date,
238 p_user_effective_date in date,
239 p_dimension_name in varchar2,
240 p_expiry_information out nocopy number)
241 is
242 c_proc constant varchar2(61) := c_package || 'qtd_ec';
243 begin
244 hr_utility.set_location('Entering : ' || c_proc, 10);
245 hr_utility.trace(p_dimension_name);
246 --
247 if trunc(p_user_effective_date, 'Q') > p_owner_effective_date then
248 hr_utility.trace('expired');
249 p_expiry_information := 1;
250 else
251 hr_utility.trace('NOT expired');
252 p_expiry_information := 0;
253 end if;
254 --
255 hr_utility.set_location('Leaving : ' || c_proc, 20);
256 end qtd_ec;
257 -- ----------------------------------------------------------------------------
258 -- |--------------------------< qtd_ec (date mode)>---------------------------|
259 -- ----------------------------------------------------------------------------
260 procedure qtd_ec(
261 p_owner_payroll_action_id in number,
262 p_user_payroll_action_id in number,
263 p_owner_assignment_action_id in number,
264 p_user_assignment_action_id in number,
265 p_owner_effective_date in date,
266 p_user_effective_date in date,
267 p_dimension_name in varchar2,
268 p_expiry_information out nocopy date)
269 is
270 c_proc constant varchar2(61) := c_package || 'qtd_ec (date mode)';
271 begin
272 hr_utility.set_location('Entering : ' || c_proc, 10);
273 hr_utility.trace(p_dimension_name);
274 --
275 p_expiry_information := add_months(trunc(p_owner_effective_date, 'Q'), 3) - 1;
276 hr_utility.trace('owner_date : ' || to_char(p_owner_effective_date));
277 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
278 --
279 hr_utility.set_location('Leaving : ' || c_proc, 20);
280 end qtd_ec;
281 -- ----------------------------------------------------------------------------
282 -- |--------------------------------< ytd_ec >--------------------------------|
283 -- ----------------------------------------------------------------------------
284 procedure ytd_ec(
285 p_owner_payroll_action_id in number,
286 p_user_payroll_action_id in number,
287 p_owner_assignment_action_id in number,
288 p_user_assignment_action_id in number,
289 p_owner_effective_date in date,
290 p_user_effective_date in date,
291 p_dimension_name in varchar2,
292 p_expiry_information out nocopy number)
293 is
294 c_proc constant varchar2(61) := c_package || 'ytd_ec';
295 begin
296 hr_utility.set_location('Entering : ' || c_proc, 10);
297 hr_utility.trace(p_dimension_name);
298 --
299 if trunc(p_user_effective_date, 'YYYY') > p_owner_effective_date then
300 hr_utility.trace('expired');
301 p_expiry_information := 1;
302 else
303 hr_utility.trace('NOT expired');
304 p_expiry_information := 0;
305 end if;
306 --
307 hr_utility.set_location('Leaving : ' || c_proc, 20);
308 end ytd_ec;
309 -- ----------------------------------------------------------------------------
310 -- |--------------------------< ytd_ec (date mode)>---------------------------|
311 -- ----------------------------------------------------------------------------
312 procedure ytd_ec(
313 p_owner_payroll_action_id in number,
314 p_user_payroll_action_id in number,
315 p_owner_assignment_action_id in number,
316 p_user_assignment_action_id in number,
317 p_owner_effective_date in date,
318 p_user_effective_date in date,
319 p_dimension_name in varchar2,
320 p_expiry_information out nocopy date)
321 is
322 c_proc constant varchar2(61) := c_package || 'ytd_ec (date mode)';
323 begin
324 hr_utility.set_location('Entering : ' || c_proc, 10);
325 hr_utility.trace(p_dimension_name);
326 --
327 p_expiry_information := add_months(trunc(p_owner_effective_date, 'YYYY'), 12) - 1;
328 hr_utility.trace('owner_date : ' || to_char(p_owner_effective_date));
329 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
330 --
331 hr_utility.set_location('Leaving : ' || c_proc, 20);
332 end ytd_ec;
333 -- ----------------------------------------------------------------------------
334 -- |-------------------------------< aprtd_ec >-------------------------------|
335 -- ----------------------------------------------------------------------------
336 procedure aprtd_ec(
337 p_owner_payroll_action_id in number,
338 p_user_payroll_action_id in number,
339 p_owner_assignment_action_id in number,
340 p_user_assignment_action_id in number,
341 p_owner_effective_date in date,
342 p_user_effective_date in date,
343 p_dimension_name in varchar2,
344 p_expiry_information out nocopy number)
345 is
346 c_proc constant varchar2(61) := c_package || 'aprtd_ec';
347 begin
348 hr_utility.set_location('Entering : ' || c_proc, 10);
349 hr_utility.trace(p_dimension_name);
350 --
351 if add_months(trunc(add_months(p_user_effective_date, 9), 'YYYY'), -9) > p_owner_effective_date then
352 hr_utility.trace('expired');
353 p_expiry_information := 1;
354 else
355 hr_utility.trace('NOT expired');
356 p_expiry_information := 0;
357 end if;
358 --
359 hr_utility.set_location('Leaving : ' || c_proc, 20);
360 end aprtd_ec;
361 -- ----------------------------------------------------------------------------
362 -- |-------------------------< aprtd_ec (date mode)>--------------------------|
363 -- ----------------------------------------------------------------------------
364 procedure aprtd_ec(
365 p_owner_payroll_action_id in number,
366 p_user_payroll_action_id in number,
367 p_owner_assignment_action_id in number,
368 p_user_assignment_action_id in number,
369 p_owner_effective_date in date,
370 p_user_effective_date in date,
371 p_dimension_name in varchar2,
372 p_expiry_information out nocopy date)
373 is
374 c_proc constant varchar2(61) := c_package || 'aprtd_ec (date mode)';
375 begin
376 hr_utility.set_location('Entering : ' || c_proc, 10);
377 hr_utility.trace(p_dimension_name);
378 --
379 p_expiry_information := add_months(trunc(add_months(p_owner_effective_date, 9), 'YYYY'), 3) - 1;
380 hr_utility.trace('owner_date : ' || to_char(p_owner_effective_date));
381 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
382 --
383 hr_utility.set_location('Leaving : ' || c_proc, 20);
384 end aprtd_ec;
385 -- ----------------------------------------------------------------------------
386 -- |-------------------------------< aprtd_sd >-------------------------------|
387 -- ----------------------------------------------------------------------------
388 procedure aprtd_sd(
389 p_effective_date in date,
390 p_start_date out nocopy date,
391 p_payroll_id in number,
392 p_bus_grp in number,
393 p_asg_action in number)
394 is
395 c_proc constant varchar2(61) := c_package || 'aprtd_sd';
396 begin
397 hr_utility.set_location('Entering : ' || c_proc, 10);
398 --
399 p_start_date := add_months(trunc(add_months(p_effective_date, 9), 'YYYY'), -9);
400 hr_utility.trace('start_date : ' || to_char(p_start_date));
401 --
402 hr_utility.set_location('Leaving : ' || c_proc, 20);
403 end aprtd_sd;
404 -- ----------------------------------------------------------------------------
405 -- |-------------------------------< jultd_ec >-------------------------------|
406 -- ----------------------------------------------------------------------------
407 procedure jultd_ec(
408 p_owner_payroll_action_id in number,
409 p_user_payroll_action_id in number,
410 p_owner_assignment_action_id in number,
411 p_user_assignment_action_id in number,
415 p_expiry_information out nocopy number)
412 p_owner_effective_date in date,
413 p_user_effective_date in date,
414 p_dimension_name in varchar2,
416 is
417 c_proc constant varchar2(61) := c_package || 'jultd_ec';
418 begin
419 hr_utility.set_location('Entering : ' || c_proc, 10);
420 hr_utility.trace(p_dimension_name);
421 --
422 if add_months(trunc(add_months(p_user_effective_date, 6), 'YYYY'), -6) > p_owner_effective_date then
423 hr_utility.trace('expired');
424 p_expiry_information := 1;
425 else
426 hr_utility.trace('NOT expired');
427 p_expiry_information := 0;
428 end if;
429 --
430 hr_utility.set_location('Leaving : ' || c_proc, 20);
431 end jultd_ec;
432 -- ----------------------------------------------------------------------------
433 -- |-------------------------< jultd_ec (date mode)>--------------------------|
434 -- ----------------------------------------------------------------------------
435 procedure jultd_ec(
436 p_owner_payroll_action_id in number,
437 p_user_payroll_action_id in number,
438 p_owner_assignment_action_id in number,
439 p_user_assignment_action_id in number,
440 p_owner_effective_date in date,
441 p_user_effective_date in date,
442 p_dimension_name in varchar2,
443 p_expiry_information out nocopy date)
444 is
445 c_proc constant varchar2(61) := c_package || 'jultd_ec (date mode)';
446 begin
447 hr_utility.set_location('Entering : ' || c_proc, 10);
448 hr_utility.trace(p_dimension_name);
449 --
450 p_expiry_information := add_months(trunc(add_months(p_owner_effective_date, 6), 'YYYY'), 6) - 1;
451 hr_utility.trace('owner_date : ' || to_char(p_owner_effective_date));
452 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
453 --
454 hr_utility.set_location('Leaving : ' || c_proc, 20);
455 end jultd_ec;
456 -- ----------------------------------------------------------------------------
457 -- |-------------------------------< jultd_sd >-------------------------------|
458 -- ----------------------------------------------------------------------------
459 procedure jultd_sd(
460 p_effective_date in date,
461 p_start_date out nocopy date,
462 p_payroll_id in number,
463 p_bus_grp in number,
464 p_asg_action in number)
465 is
466 c_proc constant varchar2(61) := c_package || 'jultd_sd';
467 begin
468 hr_utility.set_location('Entering : ' || c_proc, 10);
469 --
470 p_start_date := add_months(trunc(add_months(p_effective_date, 6), 'YYYY'), -6);
471 hr_utility.trace('start_date : ' || to_char(p_start_date));
472 --
473 hr_utility.set_location('Leaving : ' || c_proc, 20);
474 end jultd_sd;
475 -- ----------------------------------------------------------------------------
476 -- |-------------------------------< augtd_ec >-------------------------------|
477 -- ----------------------------------------------------------------------------
478 procedure augtd_ec(
479 p_owner_payroll_action_id in number,
480 p_user_payroll_action_id in number,
481 p_owner_assignment_action_id in number,
482 p_user_assignment_action_id in number,
483 p_owner_effective_date in date,
484 p_user_effective_date in date,
485 p_dimension_name in varchar2,
486 p_expiry_information out nocopy number)
487 is
488 c_proc constant varchar2(61) := c_package || 'augtd_ec';
489 begin
490 hr_utility.set_location('Entering : ' || c_proc, 10);
491 hr_utility.trace(p_dimension_name);
492 --
493 if add_months(trunc(add_months(p_user_effective_date, 5), 'YYYY'), -5) > p_owner_effective_date then
494 hr_utility.trace('expired');
495 p_expiry_information := 1;
496 else
497 hr_utility.trace('NOT expired');
498 p_expiry_information := 0;
499 end if;
500 --
501 hr_utility.set_location('Leaving : ' || c_proc, 20);
502 end augtd_ec;
503 -- ----------------------------------------------------------------------------
504 -- |-------------------------< augtd_ec (date mode)>--------------------------|
505 -- ----------------------------------------------------------------------------
506 procedure augtd_ec(
507 p_owner_payroll_action_id in number,
508 p_user_payroll_action_id in number,
509 p_owner_assignment_action_id in number,
510 p_user_assignment_action_id in number,
511 p_owner_effective_date in date,
512 p_user_effective_date in date,
513 p_dimension_name in varchar2,
514 p_expiry_information out nocopy date)
515 is
516 c_proc constant varchar2(61) := c_package || 'augtd_ec (date mode)';
517 begin
518 hr_utility.set_location('Entering : ' || c_proc, 10);
519 hr_utility.trace(p_dimension_name);
520 --
521 p_expiry_information := add_months(trunc(add_months(p_owner_effective_date, 5), 'YYYY'), 7) - 1;
522 hr_utility.trace('owner_date : ' || to_char(p_owner_effective_date));
523 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
524 --
525 hr_utility.set_location('Leaving : ' || c_proc, 20);
526 end augtd_ec;
527 -- ----------------------------------------------------------------------------
528 -- |-------------------------------< augtd_sd >-------------------------------|
529 -- ----------------------------------------------------------------------------
530 procedure augtd_sd(
531 p_effective_date in date,
532 p_start_date out nocopy date,
533 p_payroll_id in number,
534 p_bus_grp in number,
535 p_asg_action in number)
539 hr_utility.set_location('Entering : ' || c_proc, 10);
536 is
537 c_proc constant varchar2(61) := c_package || 'augtd_sd';
538 begin
540 --
541 p_start_date := add_months(trunc(add_months(p_effective_date, 5), 'YYYY'), -5);
542 hr_utility.trace('start_date : ' || to_char(p_start_date));
543 --
544 hr_utility.set_location('Leaving : ' || c_proc, 20);
545 end augtd_sd;
546 -- ----------------------------------------------------------------------------
547 -- |----------------------------< fy_start_date >-----------------------------|
548 -- ----------------------------------------------------------------------------
549 function fy_start_date(p_business_group_id in number) return date
550 is
551 c_proc constant varchar2(61) := c_package || 'fy_start_date';
552 --
553 l_fy_start_date date;
554 cursor csr_fy_start_date(p_business_group_id number) is
555 select fnd_date.canonical_to_date(org_information11)
556 from hr_organization_information
557 where organization_id = p_business_group_id
558 and org_information_context = 'Business Group Information';
559 begin
560 hr_utility.set_location('Entering : ' || c_proc, 10);
561 --
562 -- Once the fiscal year is derived from BG, the value is stored
563 -- in global variables to reduce the overhead.
564 --
565 if p_business_group_id = g_fy_cache.business_group_id then
566 hr_utility.trace('cache available');
567 l_fy_start_date := g_fy_cache.start_date;
568 else
569 hr_utility.trace('cache NOT available');
570 --
571 open csr_fy_start_date(p_business_group_id);
572 fetch csr_fy_start_date into l_fy_start_date;
573 close csr_fy_start_date;
574 --
575 -- Cache the fiscal year start date of current business group
576 --
577 if l_fy_start_date is not null then
578 g_fy_cache.business_group_id := p_business_group_id;
579 g_fy_cache.start_date := l_fy_start_date;
580 end if;
581 end if;
582 --
583 hr_utility.trace('fy_start_date : ' || to_char(l_fy_start_date));
584 hr_utility.set_location('Leaving : ' || c_proc, 20);
585 --
586 return l_fy_start_date;
587 end fy_start_date;
588 -- ----------------------------------------------------------------------------
589 -- |--------------------------< business_group_id >---------------------------|
590 -- ----------------------------------------------------------------------------
591 function business_group_id(p_payroll_action_id in number) return number
592 is
593 c_proc constant varchar2(61) := c_package || 'business_group_id';
594 --
595 l_business_group_id number;
596 begin
597 hr_utility.set_location('Entering : ' || c_proc, 10);
598 --
599 if p_payroll_action_id = g_pact_cache.payroll_action_id then
600 hr_utility.trace('cache available');
601 l_business_group_id := g_pact_cache.business_group_id;
602 else
603 hr_utility.trace('cache NOT available');
604 --
605 select business_group_id
606 into l_business_group_id
607 from pay_payroll_actions
608 where payroll_action_id = p_payroll_action_id;
609 --
610 -- Cache the business_group_id of current payroll action
611 --
612 g_pact_cache.payroll_action_id := p_payroll_action_id;
613 g_pact_cache.business_group_id := l_business_group_id;
614 end if;
615 --
616 hr_utility.set_location('Leaving : ' || c_proc, 20);
617 --
618 return l_business_group_id;
619 end business_group_id;
620 -- ----------------------------------------------------------------------------
621 -- |-------------------------------< fqtd_ec >--------------------------------|
622 -- ----------------------------------------------------------------------------
623 procedure fqtd_ec(
624 p_owner_payroll_action_id in number,
625 p_user_payroll_action_id in number,
626 p_owner_assignment_action_id in number,
627 p_user_assignment_action_id in number,
628 p_owner_effective_date in date,
629 p_user_effective_date in date,
630 p_dimension_name in varchar2,
631 p_expiry_information out nocopy number)
632 is
633 c_proc constant varchar2(61) := c_package || 'fqtd_ec';
634 --
635 l_fy_start_date date;
636 l_start_date date;
637 begin
638 hr_utility.set_location('Entering : ' || c_proc, 10);
639 hr_utility.trace(p_dimension_name);
640 --
641 -- Bind p_user_payroll_action_id not p_owner_payroll_action_id
642 -- to use cache as much as possible.
643 --
644 l_fy_start_date := fy_start_date(business_group_id(p_user_payroll_action_id));
645 l_start_date := add_months(l_fy_start_date, floor(months_between(p_user_effective_date, l_fy_start_date) / 3) * 3);
646 --
647 if l_start_date > p_owner_effective_date then
648 hr_utility.trace('expired');
649 p_expiry_information := 1;
650 else
651 hr_utility.trace('NOT expired');
652 p_expiry_information := 0;
653 end if;
654 --
655 hr_utility.set_location('Leaving : ' || c_proc, 20);
656 end fqtd_ec;
657 -- ----------------------------------------------------------------------------
658 -- |--------------------------< fqtd_ec (date mode)>--------------------------|
659 -- ----------------------------------------------------------------------------
660 procedure fqtd_ec(
661 p_owner_payroll_action_id in number,
662 p_user_payroll_action_id in number,
663 p_owner_assignment_action_id in number,
664 p_user_assignment_action_id in number,
668 p_expiry_information out nocopy date)
665 p_owner_effective_date in date,
666 p_user_effective_date in date,
667 p_dimension_name in varchar2,
669 is
670 c_proc constant varchar2(61) := c_package || 'fqtd_ec (date mode)';
671 --
672 l_fy_start_date date;
673 begin
674 hr_utility.set_location('Entering : ' || c_proc, 10);
675 hr_utility.trace(p_dimension_name);
676 --
677 l_fy_start_date := fy_start_date(business_group_id(p_owner_payroll_action_id));
678 p_expiry_information := add_months(l_fy_start_date, (floor(months_between(p_owner_effective_date, l_fy_start_date) / 3) + 1) * 3) - 1;
679 --
680 hr_utility.trace('owner_date : ' || to_char(p_owner_effective_date));
681 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
682 --
683 hr_utility.set_location('Leaving : ' || c_proc, 20);
684 end fqtd_ec;
685 -- ----------------------------------------------------------------------------
686 -- |-------------------------------< fytd_ec >--------------------------------|
687 -- ----------------------------------------------------------------------------
688 procedure fytd_ec(
689 p_owner_payroll_action_id in number,
690 p_user_payroll_action_id in number,
691 p_owner_assignment_action_id in number,
692 p_user_assignment_action_id in number,
693 p_owner_effective_date in date,
694 p_user_effective_date in date,
695 p_dimension_name in varchar2,
696 p_expiry_information out nocopy number)
697 is
698 c_proc constant varchar2(61) := c_package || 'fytd_ec';
699 --
700 l_fy_start_date date;
701 l_start_date date;
702 begin
703 hr_utility.set_location('Entering : ' || c_proc, 10);
704 hr_utility.trace(p_dimension_name);
705 --
706 l_fy_start_date := fy_start_date(business_group_id(p_user_payroll_action_id));
707 l_start_date := add_months(l_fy_start_date, floor(months_between(p_user_effective_date, l_fy_start_date) / 12) * 12);
708 --
709 if l_start_date > p_owner_effective_date then
710 hr_utility.trace('expired');
711 p_expiry_information := 1;
712 else
713 hr_utility.trace('NOT expired');
714 p_expiry_information := 0;
715 end if;
716 --
717 hr_utility.set_location('Leaving : ' || c_proc, 20);
718 end fytd_ec;
719 -- ----------------------------------------------------------------------------
720 -- |--------------------------< fytd_ec (date mode)>--------------------------|
721 -- ----------------------------------------------------------------------------
722 procedure fytd_ec(
723 p_owner_payroll_action_id in number,
724 p_user_payroll_action_id in number,
725 p_owner_assignment_action_id in number,
726 p_user_assignment_action_id in number,
727 p_owner_effective_date in date,
728 p_user_effective_date in date,
729 p_dimension_name in varchar2,
730 p_expiry_information out nocopy date)
731 is
732 c_proc constant varchar2(61) := c_package || 'fytd_ec (date mode)';
733 --
734 l_fy_start_date date;
735 begin
736 hr_utility.set_location('Entering : ' || c_proc, 10);
737 hr_utility.trace(p_dimension_name);
738 --
739 l_fy_start_date := fy_start_date(business_group_id(p_owner_payroll_action_id));
740 p_expiry_information := add_months(l_fy_start_date, (floor(months_between(p_owner_effective_date, l_fy_start_date) / 12) + 1) * 12) - 1;
741 --
742 hr_utility.trace('owner_date : ' || to_char(p_owner_effective_date));
743 hr_utility.trace('expiry_date : ' || to_char(p_expiry_information));
744 --
745 hr_utility.set_location('Leaving : ' || c_proc, 20);
746 end fytd_ec;
747 -- ----------------------------------------------------------------------------
748 -- |---------------------------< show_dim_periods >---------------------------|
749 -- ----------------------------------------------------------------------------
750 -- This procedure checks the period in which the date from p_start_date to
751 -- p_end_date falls is continuous and date is really within the period.
752 -- This is for debugging purpose.
753 -- Currently, this procedure support the following dimensions.
754 -- 1) FYTD
755 -- 2) User Defined Dimensions
756 --
757 /*
758 procedure show_dim_periods(
759 p_business_group_id in number,
760 p_dimension_name in varchar2,
761 p_start_date in date,
762 p_end_date in date)
763 is
764 l_date date;
765 l_start_date date;
766 l_end_date date;
767 l_text varchar2(255);
768 l_first_period boolean := true;
769 l_prev_start_date date;
770 l_prev_end_date date;
771 begin
772 fnd_file.put_line(fnd_file.output, 'Dimension :' || p_dimension_name);
773 fnd_file.put_line(fnd_file.output, 'Start Date:' || to_char(p_start_date));
774 fnd_file.put_line(fnd_file.output, 'End Date :' || to_char(p_end_date));
775 fnd_file.new_line(fnd_file.output);
776 fnd_file.put_line(fnd_file.output, 'Year Start Date End Date');
777 fnd_file.put_line(fnd_file.output, '---- ----------- -----------');
778 --
779 l_date := p_start_date;
780 while l_date <= p_end_date loop
781 if p_dimension_name = c_fytd then
782 --
783 -- Here does not use cache which is easier to debug in the same session.
784 --
785 fiscal_year_period(l_date, fiscal_year_start_date(p_business_group_id, false), l_start_date, l_end_date);
786 else
787 user_reg_period(p_dimension_name, l_date, l_start_date, l_end_date);
788 end if;
789 --
790 -- Validate whether the effective_date is really within the period
791 -- and the period is continuous or not.
792 --
793 if (l_start_date is null) or (l_end_date is null) then
794 fnd_message.set_name('PAY', 'PAY_JP_DIM_PERIOD_NULL');
795 fnd_message.set_token('DIMENSION_NAME', p_dimension_name);
796 fnd_message.set_token('EFFECTIVE_DATE', fnd_date.date_to_chardate(l_date));
797 fnd_message.set_token('START_DATE', fnd_date.date_to_chardate(l_start_date));
798 fnd_message.set_token('END_DATE', fnd_date.date_to_chardate(l_end_date));
799 fnd_message.raise_error;
800 elsif l_date not between l_start_date and l_end_date then
801 fnd_message.set_name('PAY', 'PAY_JP_DIM_DATE_OUT_OF_PERIOD');
802 fnd_message.set_token('DIMENSION_NAME', p_dimension_name);
803 fnd_message.set_token('EFFECTIVE_DATE', fnd_date.date_to_chardate(l_date));
804 fnd_message.set_token('START_DATE', fnd_date.date_to_chardate(l_start_date));
805 fnd_message.set_token('END_DATE', fnd_date.date_to_chardate(l_end_date));
806 fnd_message.raise_error;
807 elsif ((l_prev_start_date <> l_start_date) or (l_prev_end_date <> l_end_date))
808 and ((l_start_date <> l_prev_end_date + 1) or (l_start_date <> l_date)) then
809 fnd_message.set_name('PAY', 'PAY_JP_DIM_PERIOD_NOT_CONT');
810 fnd_message.set_token('DIMENSION_NAME', p_dimension_name);
811 fnd_message.set_token('EFFECTIVE_DATE', fnd_date.date_to_chardate(l_date));
812 fnd_message.set_token('START_DATE', fnd_date.date_to_chardate(l_start_date));
813 fnd_message.set_token('END_DATE', fnd_date.date_to_chardate(l_end_date));
814 fnd_message.set_token('PREV_START_DATE', fnd_date.date_to_chardate(l_prev_start_date));
815 fnd_message.set_token('PREV_END_DATE', fnd_date.date_to_chardate(l_prev_end_date));
816 fnd_message.raise_error;
817 end if;
818 --
819 -- Output period date range if the current date is in the end of the period
820 --
821 if (l_date = l_end_date)
822 or (l_date = p_end_date) then
823 --
824 -- Write down "Year" if the period is the first period in the year.
825 --
826 if (l_first_period)
827 or (trunc(l_start_date, 'YYYY') = l_start_date)
828 or (to_char(l_start_date, 'YYYY') <> to_char(l_end_date, 'YYYY')) then
829 l_text := to_char(l_end_date, 'YYYY');
830 else
831 l_text := ' ';
832 end if;
833 l_first_period := false;
834 --
835 l_text := l_text || ' ' || rpad(to_char(l_start_date), 11) || ' ' || to_char(l_end_date);
836 --
837 fnd_file.put_line(fnd_file.output, l_text);
838 end if;
839 --
840 l_prev_start_date := l_start_date;
841 l_prev_end_date := l_end_date;
842 l_date := l_date + 1;
843 end loop;
844 end show_dim_periods;
845 */
846 --
847 --begin
848 -- hr_utility.trace_on('F', 'TTAGAWA');
849 end pyjpexc;