[Home] [Help]
PACKAGE BODY: APPS.HR_JPRTS
Source
1 PACKAGE BODY hr_jprts AS
2 /* $Header: pyjprts.pkb 120.4.12010000.2 2010/03/02 02:49:05 keyazawa ship $ */
3 /* ------------------------------------------------------------------------------------ */
4 -- Global variable for DIMENSION_RESETDATE,DIMENSION_RESET_LAST_DATE,
5 -- DIMENSION_RESET_DATE_USERDEF and DIM_RESET_LAST_DATE_USERDEF
6 g_reset_identifier CONSTANT VARCHAR2(7):=' RESET ';
7 --
8 -- Fiscal Year Start Date
9 -- If not entered, January 1st is used instead.
10 -- Bug.2597843
11 --
12 cursor csr_fiscal_year_start_date(p_business_group_id number) is
13 SELECT fnd_date.canonical_to_date(org_information11)
14 FROM hr_organization_information
15 WHERE org_information_context = 'Business Group Information'
16 AND organization_id = p_business_group_id;
17 g_business_group_id number;
18 g_fiscal_year_start_date date;
19
20 /* ------------------------------------------------------------------------------------
21 -- SPAN_START
22 -- return the general dimension's start of the span (year/quarter/week)
23 -- ------------------------------------------------------------------------------------ */
24 FUNCTION span_start(
25 p_input_date DATE,
26 p_frequency NUMBER,
27 p_start_dd_mm VARCHAR2)
28 RETURN DATE
29 IS
30 l_year NUMBER(4);
31 l_start DATE;
32 l_start_base_high date;
33 l_start_base_low date;
34 l_start_dd_mm varchar2(6);
35 l_correct_format BOOLEAN;
36 l_p_frequency number;
37 l_p_start_dd_mm varchar2(10);
38 BEGIN
39 -- To solve gscc error
40 l_p_frequency := nvl(p_frequency,1);
41 l_p_start_dd_mm := nvl(p_start_dd_mm,'01-01-');
42 --
43 l_year := TO_NUMBER(TO_CHAR(p_input_date,'YYYY'));
44 --
45 /* -- Check that the passed in start of year
46 -- is in the correct format. Add a hyphen if one is missing
47 -- from the end, and ensure DD-MM- only has 6 characters.
48 -- If none of these 2 criteria are met, return null. */
49 --
50 if length(l_p_start_dd_mm) = 5 and instr(l_p_start_dd_mm,'-',-1) = 3 then
51 l_start_dd_mm := l_p_start_dd_mm||'-';
52 l_correct_format := TRUE;
53 elsif length(l_p_start_dd_mm) = 6 and instr(l_p_start_dd_mm,'-',-1) = 6 then
54 l_start_dd_mm := l_p_start_dd_mm;
55 l_correct_format := TRUE;
56 else
57 l_correct_format := FALSE;
58 end if;
59 --
60 if l_correct_format then
61 IF p_input_date >= TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY') THEN
62 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY');
63 ELSE
64 -- l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year -1),'DD-MM-YYYY');
65 l_start := add_months(TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY'),-12);
66 END IF;
67 /* -- cater for weekly based frequency based on 52 per annum */
68 IF l_p_frequency IN (52,26,13) THEN
69 -- l_start := p_input_date - MOD(p_input_date - l_start,7 * 52/l_p_frequency);
70 l_start := p_input_date - MOD(to_number(p_input_date - l_start),7 * 52/l_p_frequency);
71 ELSE
72 /* -- cater for monthly based frequency based on 12 per annum */
73 if l_p_frequency <= 12 then
74 l_start := ADD_MONTHS(l_start, (12/l_p_frequency) * TRUNC(MONTHS_BETWEEN(p_input_date,l_start)/(12/l_p_frequency)));
75 /* -- cater for frequency under 12 per annum. */
76 else
77 l_start_base_low := ADD_MONTHS(l_start, trunc((12/l_p_frequency) * TRUNC(MONTHS_BETWEEN(p_input_date,l_start)/(12/l_p_frequency))));
78 l_start_base_high :=l_start_base_low + trunc((add_months(trunc(p_input_date,'MM'),1)-trunc(p_input_date,'MM')) * (12/l_p_frequency));
79 if l_start_base_high > p_input_date then
80 l_start := l_start_base_low;
81 else
82 l_start := l_start_base_high;
83 end if;
84 end if;
85 END IF;
86 end if;
87 --
88 RETURN l_start;
89 END span_start;
90 --
91 /* ------------------------------------------------------------------------------------
92 -- SPAN_END
93 -- return the end of the span (year/quarter/week)
94 -- (Originally it is used as returning the end of the person level (statutory) period.)
95 -- ------------------------------------------------------------------------------------ */
96 FUNCTION span_end(
97 p_input_date DATE,
98 p_frequency NUMBER,
99 p_end_dd_mm VARCHAR2)
100 RETURN DATE
101 IS
102 l_year NUMBER(4);
103 l_end DATE;
104 l_end_base_high date;
105 l_end_base_low date;
106 l_end_dd_mm varchar2(6);
107 l_correct_format BOOLEAN;
108 l_p_frequency number;
109 l_p_end_dd_mm varchar2(10);
110 BEGIN
111 -- To solve gscc error
112 l_p_frequency := nvl(p_frequency,1);
113 l_p_end_dd_mm := nvl(p_end_dd_mm,'01-01-');
114 --
115 l_year := TO_NUMBER(TO_CHAR(p_input_date,'YYYY'));
116 --
117 /* -- Check that the passed in start of year
118 -- is in the correct format. Add a hyphen if one is missing
119 -- from the end, and ensure DD-MM- only has 6 characters.
120 -- If none of these 2 criteria are met, return null. */
121 --
122 if length(l_p_end_dd_mm) = 5 and instr(l_p_end_dd_mm,'-',-1) = 3 then
123 l_end_dd_mm := l_p_end_dd_mm||'-';
124 l_correct_format := TRUE;
125 elsif length(l_p_end_dd_mm) = 6 and instr(l_p_end_dd_mm,'-',-1) = 6 then
126 l_end_dd_mm := l_p_end_dd_mm;
127 l_correct_format := TRUE;
128 else
129 l_correct_format := FALSE;
130 end if;
131 --
132 if l_correct_format then
133 IF p_input_date >= TO_DATE(l_end_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY') THEN
134 l_end := TO_DATE(l_end_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY');
135 ELSE
136 -- l_end := TO_DATE(l_end_dd_mm||TO_CHAR(l_year -1),'DD-MM-YYYY');
137 l_end := add_months(TO_DATE(l_end_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY'),-12);
138 END IF;
139 /* -- cater for weekly based frequency based on 52 per annum */
140 IF l_p_frequency IN (52,26,13) THEN
141 -- l_end := p_input_date - MOD(p_input_date - l_end, 7 * (52/l_p_frequency)) + ((7 * 52/l_p_frequency)-1);
142 l_end := (p_input_date - MOD(TO_NUMBER(p_input_date - l_end), 7 * (52/l_p_frequency)) + (7 * 52/l_p_frequency)-1);
143 ELSE
144 /* -- cater for monthly based frequency based on 12 per annum */
145 if l_p_frequency <= 12 then
146 l_end := (add_months (ADD_MONTHS(l_end, (12/l_p_frequency)
147 * TRUNC(MONTHS_BETWEEN(p_input_date,l_end)/(12/l_p_frequency))),(12/l_p_frequency)) -1);
148 /* -- cater for frequency under 12 per annum. */
149 else
150 l_end_base_low := ADD_MONTHS(l_end, trunc((12/l_p_frequency) * TRUNC(MONTHS_BETWEEN(p_input_date,l_end)/(12/l_p_frequency))));
151 l_end_base_high :=l_end_base_low + trunc((add_months(trunc(p_input_date,'MM'),1)-trunc(p_input_date,'MM')) * (12/l_p_frequency));
152 if l_end_base_high > p_input_date then
153 l_end := l_end_base_low;
154 else
155 l_end := l_end_base_high;
156 end if;
157 -- l_end := (l_end + trunc((add_months(trunc(p_input_date,'MM'),1)-trunc(p_input_date,'MM')) * (12/l_p_frequency)) -1);
158 l_end := (l_end + trunc((add_months(trunc(l_end,'MM'),1)-trunc(l_end,'MM')) * (12/l_p_frequency)) -1);
159 end if;
160 END IF;
161 end if;
162 --
163 RETURN l_end;
164 END span_end;
165 --
166 /* ------------------------------------------------------------------------------------
167 -- SPAN_START_FISICAL_YEAR
168 -- return the start of fisical year dimension's span (year/quarter/week)
169 -- ------------------------------------------------------------------------------------ */
170 FUNCTION span_start_fisical_year(
171 p_input_date DATE,
172 p_business_group_id NUMBER)
173 RETURN DATE
174 IS
175 l_start_date date;
176 /*
177 l_year NUMBER(4);
178 l_start DATE;
179 l_start_date VARCHAR2(11);
180 l_start_date_year number(4);
181 --
182 cursor csr_start_date
183 is
184 SELECT to_char(nvl(FND_DATE.CANONICAL_TO_DATE(org_information11),to_date(to_char(p_input_date,'YYYY')||'/04/01','YYYY/MM/DD')),'DD-MM-YYYY')
185 FROM hr_organization_information
186 WHERE org_information_context='Business Group Information'
187 AND organization_id = p_business_group_id;
188 */
189 BEGIN
190 --
191 /* -- select from Org. Develper PDF */
192 /*
193 open csr_start_date;
194 fetch csr_start_date into l_start_date;
195 close csr_start_date;
196 --
197 l_year := TO_NUMBER(TO_CHAR(p_input_date,'YYYY'));
198 l_start_date_year := to_number(substrb(l_start_date,7,4));
199 --
200 IF substrb(l_start_date,1,6) = '29-02-' then
201 -- if l_start_date_year > l_year then
202 if mod(l_start_date_year - l_year,4) <> 0 then
203 l_start_date := '28-02-'||to_char(l_start_date_year);
204 end if;
205 -- elsif l_start_date_year < l_year then
206 -- if mod(l_year - l_start_date_year,4) <> 0 then
207 -- l_start_date := '28-02-'||to_char(l_start_date_year);
208 -- end if;
209 -- end if;
210 END IF;
211 IF p_input_date >= to_date(substrb(l_start_date,1,6) ||TO_CHAR(l_year),'DD-MM-YYYY') THEN
212 l_start := to_date(substrb(l_start_date,1,6) ||TO_CHAR(l_year),'DD-MM-YYYY');
213 ELSE
214 -- l_start := to_date(substrb(l_start_date,1,6) ||TO_CHAR(l_year -1),'DD-MM-YYYY');
215 l_start := add_months(to_date(substrb(l_start_date,1,6) ||TO_CHAR(l_year),'DD-MM-YYYY'),-12);
216 END IF;
217 --
218 RETURN l_start;
219 */
220 --
221 -- Bug.2597843
222 --
223 if p_business_group_id = g_business_group_id then
224 l_start_date := g_fiscal_year_start_date;
225 else
226 open csr_fiscal_year_start_date(p_business_group_id);
227 fetch csr_fiscal_year_start_date into l_start_date;
228 close csr_fiscal_year_start_date;
229 if l_start_date is null then
230 l_start_date := trunc(p_input_date, 'YYYY');
231 end if;
232 --
233 -- Cache the fiscal year start date of current business group
234 --
235 g_business_group_id := p_business_group_id;
236 g_fiscal_year_start_date := l_start_date;
237 end if;
238 --
239 l_start_date := add_months(l_start_date, floor(months_between(p_input_date, l_start_date) / 12) * 12);
240 --
241 return l_start_date;
242 END span_start_fisical_year;
243 --
244 /* ------------------------------------------------------------------------------------
245 -- SPAN_END_FISICAL_YEAR
246 -- return the end of fisical year dimension's span (year/quarter/week)
247 -- ------------------------------------------------------------------------------------ */
248 FUNCTION span_end_fisical_year(
249 p_input_date DATE,
250 p_business_group_id NUMBER)
251 RETURN DATE
252 IS
253 l_start_date date;
254 l_end_date date;
255 /*
256 l_year NUMBER(4);
257 l_start DATE;
258 l_end DATE;
259 l_start_date VARCHAR2(11);
260 l_start_date_year number(4);
261 --
262 cursor csr_start_date
263 is
264 SELECT to_char(nvl(FND_DATE.CANONICAL_TO_DATE(org_information11),to_date(to_char(p_input_date,'YYYY')||'/04/01','YYYY/MM/DD')),'DD-MM-YYYY')
265 FROM hr_organization_information
266 WHERE org_information_context='Business Group Information'
267 AND organization_id = p_business_group_id;
268 */
269 BEGIN
270 --
271 /* -- select from Org. Develper PDF */
272 /*
273 open csr_start_date;
274 fetch csr_start_date into l_start_date;
275 close csr_start_date;
276 --
277 l_year := TO_NUMBER(TO_CHAR(p_input_date,'YYYY'));
278 l_start_date_year := to_number(substrb(l_start_date,7,4));
279 --
280 IF substrb(l_start_date,1,6) = '29-02-' then
281 -- if l_start_date_year > l_year then
282 if mod(l_start_date_year - l_year,4) <> 0 then
283 l_start_date := '28-02-'||to_char(l_start_date_year);
284 end if;
285 -- elsif l_start_date_year < l_year then
286 -- if mod(l_year - l_start_date_year,4) <> 0 then
287 -- l_start_date := '28-02-'||to_char(l_start_date_year);
288 -- end if;
289 -- end if;
290 END IF;
291 IF p_input_date >= to_date(substrb(l_start_date,1,6) ||TO_CHAR(l_year),'DD-MM-YYYY') THEN
292 l_start := to_date(substrb(l_start_date,1,6) ||TO_CHAR(l_year),'DD-MM-YYYY');
293 ELSE
294 -- l_start := to_date(substrb(l_start_date,1,6) ||TO_CHAR(l_year -1),'DD-MM-YYYY');
295 l_start := add_months(to_date(substrb(l_start_date,1,6) ||TO_CHAR(l_year),'DD-MM-YYYY'),-12);
296 END IF;
297 --
298 l_end := add_months(l_start,12) - 1;
299 --
300 RETURN l_end;
301 */
302 --
303 -- Bug.2597843
304 --
305 l_end_date := add_months(span_start_fisical_year(p_input_date, p_business_group_id), 12) - 1;
306 --
307 return l_end_date;
308 END span_end_fisical_year;
309 --
310 /* ------------------------------------------------------------------------------------
311 -- DIMENSION_RESET_DATE
312 -- what is the latest reset date for a particular dimension
313 -- ------------------------------------------------------------------------------------ */
314 FUNCTION dimension_reset_date(
315 p_dimension_name VARCHAR2,
316 p_user_date DATE)
317 RETURN DATE
318 IS
319 l_start_dd_mm VARCHAR2(6);
320 l_period_from_date DATE;
321 l_frequency NUMBER;
322 l_start_reset NUMBER;
323 l_reset_pos NUMBER;
324 BEGIN
325 -- l_frequency := TO_NUMBER(SUBSTRB(p_dimension_name, 58, 2));
326 -- l_start_dd_mm := SUBSTRB(p_dimension_name, 46, 5) || '-';
327 l_reset_pos := INSTRB(p_dimension_name,g_reset_identifier);
328 --
329 IF l_reset_pos > 0 THEN
330 l_frequency := TO_NUMBER(SUBSTRB(p_dimension_name,l_reset_pos + 7, 2));
331 l_start_dd_mm := SUBSTRB(p_dimension_name, l_reset_pos - 5, 5) || '-';
332 ELSE
333 l_frequency := NULL;
334 l_start_dd_mm := NULL;
335 END IF;
336 l_period_from_date := span_start(p_user_date, l_frequency, l_start_dd_mm);
337 --
338 RETURN l_period_from_date;
339 END dimension_reset_date;
340 --
341 /* ------------------------------------------------------------------------------------
342 -- DIMENSION_RESET_LAST_DATE
343 -- what is the latest reset last date for a particular dimension
344 -- ------------------------------------------------------------------------------------ */
345 FUNCTION dimension_reset_last_date(
346 p_dimension_name VARCHAR2,
347 p_user_date DATE)
348 RETURN DATE
349 IS
350 l_end_dd_mm VARCHAR2(6);
351 l_period_last_date DATE;
352 l_frequency NUMBER;
353 l_end_reset NUMBER;
354 l_reset_pos NUMBER;
355 BEGIN
356 -- l_frequency := TO_NUMBER(SUBSTRB(p_dimension_name, 58, 2));
357 -- l_end_dd_mm := SUBSTRB(p_dimension_name, 46, 5) || '-';
358 l_reset_pos := INSTRB(p_dimension_name,g_reset_identifier);
359 --
360 IF l_reset_pos > 0 THEN
361 l_frequency := TO_NUMBER(SUBSTRB(p_dimension_name,l_reset_pos + 7, 2));
362 l_end_dd_mm := SUBSTRB(p_dimension_name, l_reset_pos - 5, 5) || '-';
363 ELSE
364 l_frequency := NULL;
365 l_end_dd_mm := NULL;
366 END IF;
367 l_period_last_date := span_end(p_user_date, l_frequency, l_end_dd_mm);
368 --
369 RETURN l_period_last_date;
370 END dimension_reset_last_date;
371 --
372 /* ------------------------------------------------------------------------------------
373 -- DIMENSION_RESET_DATE_USERDEF
374 -- what is the latest reset date for a particular dimension REPORT use ONLY.
375 -- ------------------------------------------------------------------------------------ */
376 FUNCTION dimension_reset_date_userdef(
377 p_dimension_name VARCHAR2,
378 p_user_date DATE,
379 p_storage_type VARCHAR2,
380 p_storage_name VARCHAR2,
381 p_business_group_id NUMBER)
382 RETURN DATE
383 IS
384 l_start_dd_mm VARCHAR2(7);
385 l_period_from_date DATE;
386 l_frequency NUMBER;
387 l_start NUMBER;
388 l_reset_pos NUMBER;
389 BEGIN
390 -- l_frequency:=TO_NUMBER(SUBSTRB(p_dimension_name, 58, 2));
391 --
392 /*
393 l_reset_pos := INSTRB(p_dimension_name,g_reset_identifier);
394 IF l_reset_pos > 0 THEN
395 l_frequency := TO_NUMBER(SUBSTRB(p_dimension_name,l_reset_pos + 7, 2));
396 ELSE
397 l_frequency := NULL;
398 END IF;
399 */
400 --
401 IF p_storage_type = 'FLEX' THEN --(Org. Developer Flex)
402 l_period_from_date := span_start_fisical_year(
403 p_user_date,
404 p_business_group_id);
405 END IF;
406 --
407 RETURN l_period_from_date;
408 END dimension_reset_date_userdef;
409 --
410 /* ------------------------------------------------------------------------------------
411 -- DIM_RESET_LAST_DATE_USERDEF
412 -- what is the latest reset date for a particular dimension REPORT use ONLY.
413 -- ------------------------------------------------------------------------------------ */
414 FUNCTION dim_reset_last_date_userdef(
415 p_dimension_name VARCHAR2,
416 p_user_date DATE,
417 p_storage_type VARCHAR2,
418 p_storage_name VARCHAR2,
419 p_business_group_id NUMBER)
420 RETURN DATE
421 IS
422 l_end_dd_mm VARCHAR2(7);
423 l_period_last_date DATE;
424 l_frequency NUMBER;
425 l_end NUMBER;
426 l_reset_pos NUMBER;
427 BEGIN
428 -- l_frequency:=TO_NUMBER(SUBSTRB(p_dimension_name, 58, 2));
429 --
430 /*
431 l_reset_pos := INSTRB(p_dimension_name,g_reset_identifier);
432 IF l_reset_pos > 0 THEN
433 l_frequency := TO_NUMBER(SUBSTRB(p_dimension_name,l_reset_pos + 7, 2));
434 ELSE
435 l_frequency := NULL;
436 END IF;
437 */
438 --
439 IF p_storage_type = 'FLEX' THEN --(Org. Developer Flex)
440 l_period_last_date := span_end_fisical_year(
441 p_user_date,
442 p_business_group_id);
443 END IF;
444 --
445 RETURN l_period_last_date;
446 END dim_reset_last_date_userdef;
447 --
448 /* ------------------------------------------------------------------------------------
449 -- CALC_BALANCE_FOR_DATE_EARNED
450 -- General function for accumulating a balance between two dates
451 -- The object of date is DATE_EARNED.
452 -- ------------------------------------------------------------------------------------ */
453 FUNCTION calc_bal_date_earned(
454 p_assignment_id IN NUMBER,
455 p_balance_type_id IN NUMBER, -- balance
456 p_from_date IN DATE, -- since effective date of
457 p_to_date IN DATE, -- sum up to this date
458 p_action_sequence IN NUMBER) -- sum up to this sequence
459 RETURN NUMBER
460 IS
461 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
462 l_balance NUMBER;
463 --
464 -- cursor csr_assignment_action_id
465 -- is
466 -- SELECT ASSACT.assignment_action_id
467 -- FROM pay_payroll_actions PACT,
468 -- pay_assignment_actions ASSACT
469 -- WHERE ASSACT.assignment_id = P_ASSIGNMENT_ID
470 -- AND PACT.payroll_action_id = ASSACT.payroll_action_id
471 -- AND PACT.date_earned >= P_FROM_DATE
472 -- AND ( ( PACT.date_earned < P_TO_DATE)
473 -- or ( PACT.date_earned = P_TO_DATE
474 -- and ASSACT.action_sequence <= P_ACTION_SEQUENCE));
475 --
476 cursor csr_balance
477 is
478 -- SELECT /*+ RULE */
479 -- SELECT /* Hint removed : see bug :4256073 */
480 select /*+ ORDERED
481 USE_NL(FEED)
482 INDEX(FEED PAY_BALANCE_FEEDS_F_N50) */
483 NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
484 FROM pay_assignment_actions ASSACT,
485 pay_payroll_actions PACT,
486 pay_run_results RR,
487 pay_run_result_values TARGET,
488 pay_balance_feeds_f FEED
489 WHERE ASSACT.assignment_id = P_ASSIGNMENT_ID
490 -- AND ASSACT.action_sequence <= NVL(P_ACTION_SEQUENCE,ASSACT.action_sequence)
491 AND PACT.payroll_action_id = ASSACT.payroll_action_id
492 AND PACT.date_earned >= P_FROM_DATE
493 AND ( ( PACT.date_earned < P_TO_DATE)
494 or ( PACT.date_earned = P_TO_DATE
495 and ASSACT.action_sequence <= P_ACTION_SEQUENCE))
496 AND RR.assignment_action_id = ASSACT.assignment_action_id
497 AND RR.status IN ('P','PA')
498 AND TARGET.run_result_id = RR.run_result_id
499 AND nvl(TARGET.result_value, '0') <> '0'
500 AND FEED.input_value_id = TARGET.input_value_id
501 AND FEED.balance_type_id = P_BALANCE_TYPE_ID
502 -- AND PACT.effective_date
503 AND PACT.date_earned
504 BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
505 BEGIN
506 --l_balance := 0;
507 --l_defined_balance_id := PAY_JP_BALANCE_PKG.GET_DEFINED_BALANCE_ID(p_balance_type_id,p_assignment_id,p_to_date);
508
509 --IF l_defined_balance_id is not null THEN
510 -- FOR c_assignment_action_id IN csr_assignment_action_id LOOP
511 -- l_balance := l_balance + pay_balance_pkg.get_value(l_defined_balance_id, c_assignment_action_id.assignment_action_id);
512 -- END LOOP;
513 --ELSE
514 open csr_balance;
515 fetch csr_balance into l_balance;
516 close csr_balance;
517 --END IF;
518
519 --
520 RETURN l_balance;
521 END calc_bal_date_earned;
522 --
523 /* ------------------------------------------------------------------------------------
524 -- CALC_BALANCE_FOR_EFF_DATE
525 -- General function for accumulating a balance between two dates
526 -- The object of date is EFFECTIVE_DATE.
527 -- ------------------------------------------------------------------------------------ */
528 FUNCTION calc_bal_eff_date(
529 p_assignment_id IN NUMBER,
530 p_balance_type_id IN NUMBER, -- balance
531 p_from_date IN DATE, -- since effective date of
532 p_to_date IN DATE, -- sum up to this date
533 p_action_sequence IN NUMBER) -- sum up to this sequence
534 RETURN NUMBER
535 IS
536 l_assignment_action_id PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE;
537 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
538 l_balance NUMBER;
539 --
540 -- cursor csr_assignment_action_id
541 -- is
542 -- SELECT ASSACT.assignment_action_id
543 -- from pay_payroll_actions PACT,
544 -- pay_assignment_actions ASSACT
545 -- WHERE ASSACT.assignment_id = p_assignment_id
546 -- AND ASSACT.action_sequence <= NVL(p_action_sequence, ASSACT.action_sequence)
547 -- AND PACT.payroll_action_id = ASSACT.payroll_action_id
548 -- AND PACT.action_type <> 'V'
549 -- AND PACT.effective_date >= p_from_date
550 -- AND PACT.effective_date <= p_to_date
551 -- AND NOT EXISTS(
552 -- SELECT NULL
553 -- FROM pay_payroll_actions RPACT,
554 -- pay_assignment_actions RASSACT,
555 -- pay_action_interlocks RINTL
556 -- WHERE RINTL.locked_action_id = ASSACT.assignment_action_id
557 -- AND RASSACT.assignment_action_id = RINTL.locking_action_id
558 -- AND RPACT.payroll_action_id = RASSACT.payroll_action_id
559 -- AND RPACT.action_type = 'V');
560 --
561 --
562 /* -- This cursor have to check target ASSACT isn't locked by Reversal ASSACT. */
563 cursor csr_balance
564 is
565 -- SELECT /*+ RULE */
566 SELECT /*+ ORDERED */
567 NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
568 FROM ( select /*+ ORDERED */
569 ASSACT.assignment_action_id,
570 PACT.effective_date
571 from pay_assignment_actions ASSACT,
572 pay_payroll_actions PACT
573 WHERE ASSACT.assignment_id = p_assignment_id
574 AND ASSACT.action_sequence <= NVL(p_action_sequence, ASSACT.action_sequence)
575 AND PACT.payroll_action_id = ASSACT.payroll_action_id
576 AND PACT.action_type <> 'V'
577 AND PACT.effective_date >= p_from_date
578 AND PACT.effective_date <= p_to_date
579 AND NOT EXISTS(
580 SELECT NULL
581 FROM pay_payroll_actions RPACT,
582 pay_assignment_actions RASSACT,
583 pay_action_interlocks RINTL
584 WHERE RINTL.locked_action_id = ASSACT.assignment_action_id
585 AND RASSACT.assignment_action_id = RINTL.locking_action_id
586 AND RPACT.payroll_action_id = RASSACT.payroll_action_id
587 AND RPACT.action_type = 'V')) V1,
588 pay_run_results RR,
589 pay_run_result_values TARGET,
590 pay_balance_feeds_f FEED
591 WHERE RR.assignment_action_id = V1.assignment_action_id
592 AND RR.status IN ('P','PA')
593 AND TARGET.run_result_id = RR.run_result_id
594 AND nvl(TARGET.result_value, '0') <> '0'
595 AND FEED.input_value_id = TARGET.input_value_id
596 AND FEED.balance_type_id = p_balance_type_id
597 AND V1.effective_date
598 BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
599 BEGIN
600 --l_balance := 0;
601 --l_defined_balance_id := PAY_JP_BALANCE_PKG.GET_DEFINED_BALANCE_ID(p_balance_type_id,p_assignment_id,p_to_date);
602 --
603 --IF l_defined_balance_id is not null THEN
604 -- FOR c_assignment_action_id IN csr_assignment_action_id LOOP
605 -- l_balance := l_balance + pay_balance_pkg.get_value(l_defined_balance_id, c_assignment_action_id.assignment_action_id);
606 -- END LOOP;
607 --ELSE
608 open csr_balance;
609 fetch csr_balance into l_balance;
610 close csr_balance;
611 --END IF;
612 --
613 RETURN l_balance;
614 END calc_bal_eff_date;
615 --
616 /* ------------------------------------------------------------------------------------
617 -- ASG_FYTD_JP
618 -- JP Specific function for accumulating a balance using assignment_action_id.
619 -- This function is to accumulate values for the dimension of Financial Year to Date.
620 -- ------------------------------------------------------------------------------------ */
621 FUNCTION asg_fytd_jp(
622 p_assignment_action_id IN NUMBER,
623 p_balance_type_id IN NUMBER) -- balance
624 RETURN NUMBER
625 IS
626 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
627 l_balance NUMBER;
628 --
629 -- cursor csr_assignment_action_id(assact_id IN NUMBER,
630 -- baltype IN NUMBER)
631 -- is
632 -- SELECT /*+ ORDERED */
633 -- ASSACT.assignment_action_id
634 -- FROM ( select /*+ ORDERED */
635 -- BAL_ASSACT.assignment_id assignment_id,
636 -- BAL_ASSACT.action_sequence action_sequence,
637 -- BACT.date_earned date_earned,
638 -- add_months(
639 -- nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')),
640 -- floor(
641 -- months_between( BACT.date_earned,
642 -- nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')))
643 -- / 12) * 12) start_date
644 -- from pay_assignment_actions BAL_ASSACT,
645 -- pay_payroll_actions BACT,
646 -- hr_organization_information HROG
647 -- where BAL_ASSACT.assignment_action_id = assact_id
648 -- and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
649 -- and HROG.organization_id = BACT.business_group_id
650 -- and HROG.org_information_context = 'Business Group Information') V1,
651 -- pay_assignment_actions ASSACT,
652 -- pay_payroll_actions PACT
653 -- WHERE ASSACT.assignment_id = V1.assignment_id
654 -- AND PACT.payroll_action_id = ASSACT.payroll_action_id
655 -- AND PACT.date_earned >= V1.start_date
656 -- AND ( ( PACT.date_earned < V1.date_earned)
657 -- or ( PACT.date_earned = V1.date_earned
658 -- and ASSACT.action_sequence <= V1.action_sequence));
659 --
660 /* -- This cursor have to check target ASSACT isn't locked by Reversal ASSACT. */
661 cursor csr_balance(assact_id IN NUMBER,
662 baltype IN NUMBER)
663 is
664 -- SELECT /*+ RULE */
665 SELECT /*+ ORDERED
666 INDEX(FEED PAY_BALANCE_FEEDS_F_N50) */
667 NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
668 FROM
669 ( select BAL_ASSACT.assignment_id assignment_id,
670 BAL_ASSACT.action_sequence action_sequence,
671 -- BACT.action_type action_type,
672 BACT.date_earned date_earned,
673 --
674 -- When null, apply 1st January
675 --
676 add_months(
677 nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')),
678 floor(
679 months_between( BACT.date_earned,
680 nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')))
681 / 12) * 12) start_date
682 from hr_organization_information HROG,
683 pay_payroll_actions BACT,
684 pay_assignment_actions BAL_ASSACT
685 where BAL_ASSACT.assignment_action_id = assact_id
686 and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
687 and HROG.organization_id = BACT.business_group_id
688 and HROG.org_information_context = 'Business Group Information') V1,
689 pay_assignment_actions ASSACT,
690 pay_payroll_actions PACT,
691 pay_run_results RR,
692 pay_run_result_values TARGET,
693 pay_balance_feeds_f FEED
694 WHERE ASSACT.assignment_id = V1.assignment_id
695 AND PACT.payroll_action_id = ASSACT.payroll_action_id
696 -- AND ASSACT.action_sequence <= decode(V1.action_type,'N',ASSACT.action_sequence,V1.action_sequence)
697 /* -- AND ( (V1.action_type = 'N')
698 -- or ( V1.action_type <> 'N'
699 -- AND ASSACT.action_sequence <= NVL(V1.action_sequence,ASSACT.action_sequence))) */
700 --
701 -- The system guarantees assignment actions ordered by effective_date as sequential action,
702 -- but does not guarantee actions ordered by date_earned as sequential action.
703 --
704 AND PACT.date_earned >= V1.start_date
705 AND ( ( PACT.date_earned < V1.date_earned)
706 or ( PACT.date_earned = V1.date_earned
707 and ASSACT.action_sequence <= V1.action_sequence))
708 AND RR.assignment_action_id = ASSACT.assignment_action_id
709 AND RR.status IN ('P','PA')
710 AND TARGET.run_result_id = RR.run_result_id
711 AND nvl(TARGET.result_value, '0') <> '0'
712 AND FEED.input_value_id = TARGET.input_value_id
713 AND FEED.balance_type_id = baltype
714 AND PACT.date_earned
715 BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
716 BEGIN
717 --l_balance := 0;
718 --l_defined_balance_id := PAY_JP_BALANCE_PKG.GET_DEFINED_BALANCE_ID(p_balance_type_id,p_assignment_action_id);
719 --
720 --IF l_defined_balance_id is not null THEN
721 -- FOR c_assignment_action_id IN csr_assignment_action_id(p_assignment_action_id, p_balance_type_id) LOOP
722 -- l_balance := l_balance + pay_balance_pkg.get_value(l_defined_balance_id, c_assignment_action_id.assignment_action_id);
723 -- END LOOP;
724 --ELSE
725 open csr_balance(p_assignment_action_id, p_balance_type_id);
726 fetch csr_balance into l_balance;
727 close csr_balance;
728 --END IF;
729 --
730 RETURN l_balance;
731 END asg_fytd_jp;
732 --
733 /* ------------------------------------------------------------------------------------
734 -- RETRO_JP
735 -- The calculation of this dimension should be included in hr_routes.
736 -- General function for accumulating a balance using assignment_action_id
737 -- Actually, this function will not be used
738 -- because user does not need to know the calculated value.
739 -- ------------------------------------------------------------------------------------ */
740 FUNCTION retro_jp(
741 p_assignment_action_id IN NUMBER,
742 p_balance_type_id IN NUMBER) -- balance
743 RETURN NUMBER
744 IS
745 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
746 l_balance NUMBER;
747 --
748 BEGIN
749 --
750 l_defined_balance_id := PAY_JP_BALANCE_PKG.GET_DEFINED_BALANCE_ID(p_balance_type_id,p_assignment_action_id);
751
752 IF l_defined_balance_id is not null THEN
753 l_balance := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
754 -- Removed csr_balance cursor as _ASG_RETRO_RUN balance dimension
755 -- would not be available in R12, so the code related to it has been
756 -- obsoleted. Commented the below code as per Bug# 5033803.
757 /*
758 ELSE
759 open csr_balance(p_assignment_action_id, p_balance_type_id);
760 fetch csr_balance into l_balance;
761 close csr_balance;
762 */
763 END IF;
764 --
765 RETURN l_balance;
766 END retro_jp;
767 --
768 END hr_jprts;