[Home] [Help]
PACKAGE BODY: APPS.HR_JPRTS
Source
1 PACKAGE BODY hr_jprts AS
2 /* $Header: pyjprts.pkb 120.4 2006/04/03 23:27:37 sgottipa noship $ */
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 --
404 p_business_group_id);
401 IF p_storage_type = 'FLEX' THEN --(Org. Developer Flex)
402 l_period_from_date := span_start_fisical_year(
403 p_user_date,
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 NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
481 FROM pay_assignment_actions ASSACT,
482 pay_payroll_actions PACT,
483 pay_run_results RR,
484 pay_run_result_values TARGET,
485 pay_balance_feeds_f FEED
486 WHERE ASSACT.assignment_id = P_ASSIGNMENT_ID
487 -- AND ASSACT.action_sequence <= NVL(P_ACTION_SEQUENCE,ASSACT.action_sequence)
488 AND PACT.payroll_action_id = ASSACT.payroll_action_id
489 AND PACT.date_earned >= P_FROM_DATE
490 AND ( ( PACT.date_earned < P_TO_DATE)
491 or ( PACT.date_earned = P_TO_DATE
492 and ASSACT.action_sequence <= P_ACTION_SEQUENCE))
493 AND RR.assignment_action_id = ASSACT.assignment_action_id
494 AND RR.status IN ('P','PA')
495 AND TARGET.run_result_id = RR.run_result_id
496 AND nvl(TARGET.result_value, '0') <> '0'
497 AND FEED.input_value_id = TARGET.input_value_id
498 AND FEED.balance_type_id = P_BALANCE_TYPE_ID
499 -- AND PACT.effective_date
500 AND PACT.date_earned
501 BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
502 BEGIN
503 --l_balance := 0;
504 --l_defined_balance_id := PAY_JP_BALANCE_PKG.GET_DEFINED_BALANCE_ID(p_balance_type_id,p_assignment_id,p_to_date);
505
506 --IF l_defined_balance_id is not null THEN
507 -- FOR c_assignment_action_id IN csr_assignment_action_id LOOP
508 -- l_balance := l_balance + pay_balance_pkg.get_value(l_defined_balance_id, c_assignment_action_id.assignment_action_id);
509 -- END LOOP;
510 --ELSE
511 open csr_balance;
512 fetch csr_balance into l_balance;
513 close csr_balance;
514 --END IF;
515
516 --
517 RETURN l_balance;
518 END calc_bal_date_earned;
519 --
520 /* ------------------------------------------------------------------------------------
521 -- CALC_BALANCE_FOR_EFF_DATE
522 -- General function for accumulating a balance between two dates
523 -- The object of date is EFFECTIVE_DATE.
524 -- ------------------------------------------------------------------------------------ */
525 FUNCTION calc_bal_eff_date(
526 p_assignment_id IN NUMBER,
527 p_balance_type_id IN NUMBER, -- balance
528 p_from_date IN DATE, -- since effective date of
529 p_to_date IN DATE, -- sum up to this date
530 p_action_sequence IN NUMBER) -- sum up to this sequence
531 RETURN NUMBER
532 IS
533 l_assignment_action_id PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE;
534 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
535 l_balance NUMBER;
539 -- SELECT ASSACT.assignment_action_id
536 --
537 -- cursor csr_assignment_action_id
538 -- is
540 -- from pay_payroll_actions PACT,
541 -- pay_assignment_actions ASSACT
542 -- WHERE ASSACT.assignment_id = p_assignment_id
543 -- AND ASSACT.action_sequence <= NVL(p_action_sequence, ASSACT.action_sequence)
544 -- AND PACT.payroll_action_id = ASSACT.payroll_action_id
545 -- AND PACT.action_type <> 'V'
546 -- AND PACT.effective_date >= p_from_date
547 -- AND PACT.effective_date <= p_to_date
548 -- AND NOT EXISTS(
549 -- SELECT NULL
550 -- FROM pay_payroll_actions RPACT,
551 -- pay_assignment_actions RASSACT,
552 -- pay_action_interlocks RINTL
553 -- WHERE RINTL.locked_action_id = ASSACT.assignment_action_id
554 -- AND RASSACT.assignment_action_id = RINTL.locking_action_id
555 -- AND RPACT.payroll_action_id = RASSACT.payroll_action_id
556 -- AND RPACT.action_type = 'V');
557 --
558 --
559 /* -- This cursor have to check target ASSACT isn't locked by Reversal ASSACT. */
560 cursor csr_balance
561 is
562 -- SELECT /*+ RULE */
563 SELECT /*+ ORDERED */
564 NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
565 FROM ( select /*+ ORDERED */
566 ASSACT.assignment_action_id,
567 PACT.effective_date
568 from pay_assignment_actions ASSACT,
569 pay_payroll_actions PACT
570 WHERE ASSACT.assignment_id = p_assignment_id
571 AND ASSACT.action_sequence <= NVL(p_action_sequence, ASSACT.action_sequence)
572 AND PACT.payroll_action_id = ASSACT.payroll_action_id
573 AND PACT.action_type <> 'V'
574 AND PACT.effective_date >= p_from_date
575 AND PACT.effective_date <= p_to_date
576 AND NOT EXISTS(
577 SELECT NULL
578 FROM pay_payroll_actions RPACT,
579 pay_assignment_actions RASSACT,
580 pay_action_interlocks RINTL
581 WHERE RINTL.locked_action_id = ASSACT.assignment_action_id
582 AND RASSACT.assignment_action_id = RINTL.locking_action_id
583 AND RPACT.payroll_action_id = RASSACT.payroll_action_id
584 AND RPACT.action_type = 'V')) V1,
585 pay_run_results RR,
586 pay_run_result_values TARGET,
587 pay_balance_feeds_f FEED
588 WHERE RR.assignment_action_id = V1.assignment_action_id
589 AND RR.status IN ('P','PA')
590 AND TARGET.run_result_id = RR.run_result_id
591 AND nvl(TARGET.result_value, '0') <> '0'
592 AND FEED.input_value_id = TARGET.input_value_id
593 AND FEED.balance_type_id = p_balance_type_id
594 AND V1.effective_date
595 BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
596 BEGIN
597 --l_balance := 0;
598 --l_defined_balance_id := PAY_JP_BALANCE_PKG.GET_DEFINED_BALANCE_ID(p_balance_type_id,p_assignment_id,p_to_date);
599 --
600 --IF l_defined_balance_id is not null THEN
601 -- FOR c_assignment_action_id IN csr_assignment_action_id LOOP
602 -- l_balance := l_balance + pay_balance_pkg.get_value(l_defined_balance_id, c_assignment_action_id.assignment_action_id);
603 -- END LOOP;
604 --ELSE
605 open csr_balance;
606 fetch csr_balance into l_balance;
607 close csr_balance;
608 --END IF;
609 --
610 RETURN l_balance;
611 END calc_bal_eff_date;
612 --
613 /* ------------------------------------------------------------------------------------
614 -- ASG_FYTD_JP
615 -- JP Specific function for accumulating a balance using assignment_action_id.
616 -- This function is to accumulate values for the dimension of Financial Year to Date.
617 -- ------------------------------------------------------------------------------------ */
618 FUNCTION asg_fytd_jp(
619 p_assignment_action_id IN NUMBER,
620 p_balance_type_id IN NUMBER) -- balance
621 RETURN NUMBER
622 IS
623 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
624 l_balance NUMBER;
625 --
626 -- cursor csr_assignment_action_id(assact_id IN NUMBER,
627 -- baltype IN NUMBER)
628 -- is
629 -- SELECT /*+ ORDERED */
630 -- ASSACT.assignment_action_id
631 -- FROM ( select /*+ ORDERED */
632 -- BAL_ASSACT.assignment_id assignment_id,
633 -- BAL_ASSACT.action_sequence action_sequence,
634 -- BACT.date_earned date_earned,
635 -- add_months(
636 -- nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')),
637 -- floor(
638 -- months_between( BACT.date_earned,
639 -- nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')))
640 -- / 12) * 12) start_date
641 -- from pay_assignment_actions BAL_ASSACT,
642 -- pay_payroll_actions BACT,
643 -- hr_organization_information HROG
644 -- where BAL_ASSACT.assignment_action_id = assact_id
645 -- and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
646 -- and HROG.organization_id = BACT.business_group_id
647 -- and HROG.org_information_context = 'Business Group Information') V1,
648 -- pay_assignment_actions ASSACT,
649 -- pay_payroll_actions PACT
650 -- WHERE ASSACT.assignment_id = V1.assignment_id
651 -- AND PACT.payroll_action_id = ASSACT.payroll_action_id
652 -- AND PACT.date_earned >= V1.start_date
653 -- AND ( ( PACT.date_earned < V1.date_earned)
654 -- or ( PACT.date_earned = V1.date_earned
655 -- and ASSACT.action_sequence <= V1.action_sequence));
656 --
657 /* -- This cursor have to check target ASSACT isn't locked by Reversal ASSACT. */
658 cursor csr_balance(assact_id IN NUMBER,
659 baltype IN NUMBER)
660 is
661 -- SELECT /*+ RULE */
665 pay_run_result_values TARGET,
662 SELECT
663 NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
664 FROM pay_balance_feeds_f FEED,
666 pay_run_results RR,
667 pay_payroll_actions PACT,
668 pay_assignment_actions ASSACT,
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 WHERE ASSACT.assignment_id = V1.assignment_id
690 AND PACT.payroll_action_id = ASSACT.payroll_action_id
691 -- AND ASSACT.action_sequence <= decode(V1.action_type,'N',ASSACT.action_sequence,V1.action_sequence)
692 /* -- AND ( (V1.action_type = 'N')
693 -- or ( V1.action_type <> 'N'
694 -- AND ASSACT.action_sequence <= NVL(V1.action_sequence,ASSACT.action_sequence))) */
695 --
696 -- The system guarantees assignment actions ordered by effective_date as sequential action,
697 -- but does not guarantee actions ordered by date_earned as sequential action.
698 --
699 AND PACT.date_earned >= V1.start_date
700 AND ( ( PACT.date_earned < V1.date_earned)
701 or ( PACT.date_earned = V1.date_earned
702 and ASSACT.action_sequence <= V1.action_sequence))
703 AND RR.assignment_action_id = ASSACT.assignment_action_id
704 AND RR.status IN ('P','PA')
705 AND TARGET.run_result_id = RR.run_result_id
706 AND nvl(TARGET.result_value, '0') <> '0'
707 AND FEED.input_value_id = TARGET.input_value_id
708 AND FEED.balance_type_id = baltype
709 AND PACT.date_earned
710 BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
711 BEGIN
712 --l_balance := 0;
713 --l_defined_balance_id := PAY_JP_BALANCE_PKG.GET_DEFINED_BALANCE_ID(p_balance_type_id,p_assignment_action_id);
714 --
715 --IF l_defined_balance_id is not null THEN
716 -- FOR c_assignment_action_id IN csr_assignment_action_id(p_assignment_action_id, p_balance_type_id) LOOP
717 -- l_balance := l_balance + pay_balance_pkg.get_value(l_defined_balance_id, c_assignment_action_id.assignment_action_id);
718 -- END LOOP;
719 --ELSE
720 open csr_balance(p_assignment_action_id, p_balance_type_id);
721 fetch csr_balance into l_balance;
722 close csr_balance;
723 --END IF;
724 --
725 RETURN l_balance;
726 END asg_fytd_jp;
727 --
728 /* ------------------------------------------------------------------------------------
729 -- RETRO_JP
733 -- because user does not need to know the calculated value.
730 -- The calculation of this dimension should be included in hr_routes.
731 -- General function for accumulating a balance using assignment_action_id
732 -- Actually, this function will not be used
734 -- ------------------------------------------------------------------------------------ */
735 FUNCTION retro_jp(
736 p_assignment_action_id IN NUMBER,
737 p_balance_type_id IN NUMBER) -- balance
738 RETURN NUMBER
739 IS
740 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
741 l_balance NUMBER;
742 --
743 BEGIN
744 --
745 l_defined_balance_id := PAY_JP_BALANCE_PKG.GET_DEFINED_BALANCE_ID(p_balance_type_id,p_assignment_action_id);
746
747 IF l_defined_balance_id is not null THEN
748 l_balance := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
749 -- Removed csr_balance cursor as _ASG_RETRO_RUN balance dimension
750 -- would not be available in R12, so the code related to it has been
751 -- obsoleted. Commented the below code as per Bug# 5033803.
752 /*
753 ELSE
754 open csr_balance(p_assignment_action_id, p_balance_type_id);
755 fetch csr_balance into l_balance;
756 close csr_balance;
757 */
758 END IF;
759 --
760 RETURN l_balance;
761 END retro_jp;
762 --
763 END hr_jprts;