[Home] [Help]
PACKAGE BODY: APPS.PAY_SOE_GLB
Source
1 package body PAY_SOE_GLB as
2 /* $Header: pysoeglb.pkb 120.15.12020000.2 2012/10/15 12:26:10 bklingam ship $ */
3 --
4 l_sql long;
5 g_debug boolean := hr_utility.debug_enabled;
6 g_max_action number;
7 g_min_action number;
8
9 --
10 --
11 /* ---------------------------------------------------------------------
12 Function : SetParameters
13
14 Text
15 ------------------------------------------------------------------------ */
16 function setParameters(p_assignment_action_id in number) return varchar2 is
17 --
18 cursor getParameters(c_assignment_action_id in number) is
19 select pa.payroll_id
20 --, to_number(to_char(pa.effective_date,'J')) effective_date
21 ,replace(substr(FND_DATE.DATE_TO_CANONICAL(pa.effective_date),1,10),'/','-') jsqldate --YYYY-MM-DD
22 ,'' || pa.effective_date || '' effective_date
23 , aa.assignment_id
24 , pa.business_group_id
25 , aa.tax_unit_id
26 ,'''' || bg.currency_code || '''' currency_code
27 ,action_type
28 ,fc.name currency_name
29 from pay_payroll_actions pa
30 , pay_assignment_actions aa
31 , per_business_groups bg
32 , fnd_currencies_vl fc
33 where aa.assignment_action_id = p_assignment_action_id
34 and aa.payroll_action_id = pa.payroll_action_id
35 and pa.business_group_id = bg.business_group_id
36 and fc.currency_code = bg.currency_code
37 and rownum = 1;
38
39 cursor getActions is
40 select assignment_action_id
41 from pay_assignment_actions
42 where level =
43 (select max(level)
44 from pay_assignment_actions
45 connect by source_action_id = prior assignment_action_id
46 start with assignment_action_id = p_assignment_action_id)
47 connect by source_action_id = prior assignment_action_id
48 start with assignment_action_id = p_assignment_action_id;
49
50 l_action_type pay_payroll_actions.action_type%type;
51
52 cursor lockedActions is
53 select locked_action_id,
54 action_sequence
55 from pay_action_interlocks,
56 pay_assignment_actions
57 where locking_action_id = p_assignment_action_id
58 and locked_action_id = assignment_action_id
59 order by action_sequence desc;
60
61 --
62 l_parameters varchar2(2000);
63 l_action_count number;
64 l_actions varchar2(2000);
65 l_max_action number;
66 l_min_action number;
67 l_assignment_action_id number;
68 --
69 begin
70 --
71 if g_debug then
72 hr_utility.set_location('Entering pay_soe_glb.setParameters', 10);
73 end if;
74 --
75 -- Prepay change
76 select action_type
77 into l_action_type
78 from pay_payroll_actions pa
79 ,pay_assignment_actions aa
80 where aa.assignment_action_id = p_assignment_action_id
81 and aa.payroll_action_id = pa.payroll_action_id;
82
83 /* exception
84 when no_data_found then
85 */
86
87 l_action_count := 0;
88 l_max_action := 0;
89 l_min_action := 0;
90
91 if l_action_type in ('P','U') then
92 for a in lockedActions loop
93 l_action_count := l_action_count + 1;
94 l_actions := l_actions || a.locked_action_id|| ',';
95 if l_max_action = 0 then
96 l_max_action := a.locked_action_id;
97 end if;
98 l_min_action := a.locked_action_id;
99 end loop;
100 else
101 for a in getActions loop
102 l_action_count := l_action_count + 1;
103 l_actions := l_actions || a.assignment_action_id|| ',';
104 end loop;
105 end if;
106
107 l_actions := substr(l_actions,1,length(l_actions)-1);
108 --
109 if l_action_type in ( 'P','U' ) then
110 l_assignment_action_id := l_max_action; -- for Prepays, effective date is date of
111 else -- latest run action.
112 l_assignment_action_id := p_assignment_action_id;
113 end if;
114
115 for p in getParameters(l_assignment_action_id) loop
116 l_parameters := 'PAYROLL_ID:' ||p.payroll_id ||':'||
117 'JSQLDATE:' ||p.jsqldate ||':'||
118 'EFFECTIVE_DATE:' ||p.effective_date ||':'||
119 'ASSIGNMENT_ID:' ||p.assignment_id ||':'||
120 'BUSINESS_GROUP_ID:' ||p.business_group_id ||':'||
121 'TAX_UNIT_ID:' ||p.tax_unit_id ||':'||
122 'G_CURRENCY_CODE:' ||p.currency_code ||':'||
123 'PREPAY_MAX_ACTION:' ||l_max_action ||':'||
124 'PREPAY_MIN_ACTION:' ||l_min_action ||':'||
125 'CURRENCY_NAME:' ||p.currency_name ||':'||
126 'ASSIGNMENT_ACTION_ID:'||p_assignment_action_id||':';
127 if g_debug then
128 hr_utility.trace('p_payroll_id = ' || p.payroll_id);
129 hr_utility.trace('jsqldate = ' || p.jsqldate);
130 hr_utility.trace('effective_date = ' || p.effective_date);
131 hr_utility.trace('assignment_id = ' || p.assignment_id);
132 hr_utility.trace('business_group_id = ' || p.business_group_id);
133 hr_utility.trace('tax_unit_id = ' || p.tax_unit_id);
134 hr_utility.trace('g_currency_code = ' || g_currency_code);
135 hr_utility.trace('action_clause = ' || l_actions);
136 end if;
137 g_currency_code := p.currency_code;
138 l_action_type := p.action_type;
139 end loop;
140 --
141 if l_action_count = 1 then
142 l_parameters := l_parameters || 'ACTION_CLAUSE:' ||
143 ' = '||l_actions ||':';
144 else
145 l_parameters := l_parameters || 'ACTION_CLAUSE:' ||
146 ' in ('||l_actions ||')' ||':';
147 end if;
148 --
149 if g_debug then
150 hr_utility.trace('l_parameters = ' || l_parameters);
151 hr_utility.set_location('Leaving pay_soe_glb.setParameters', 20);
152 end if;
153 --
154 return l_parameters;
155 end;
156 --
157 /* ---------------------------------------------------------------------
158 Function : SetParameters
159
160 Text
161 ------------------------------------------------------------------------ */
162 function setParameters(p_person_id in number, p_assignment_id in number, p_effective_date date) return varchar2 is
163 cursor csr_get_asg_id is
164 select assignment_id
165 from per_all_assignments_f
166 where person_id = p_person_id
167 and p_effective_date between effective_start_date and effective_end_date;
168
169 /* Bug#6887749
170 * Removed join with per_time_periods to fetch the latest assignment action
171 * id of the assignment irrespective of the session date.*/
172 cursor csr_get_action_id (asg_id number) is
173 select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
174 from pay_payroll_actions pa,
175 pay_assignment_actions aa
176 -- per_time_periods ptp
177 where aa.action_status = 'C'
178 and pa.payroll_action_id = aa.payroll_action_id
179 and aa.assignment_id = asg_id
180 --and ptp.payroll_id = pa.payroll_id
181 --and pa.effective_date <= ptp.regular_payment_date
182 --and p_effective_date between ptp.start_date and ptp.end_date
183 and pa.action_type in ('P','Q','R','U')
184 order by pa.effective_date desc ;
185
186 l_assignment_action_id number;
187 l_assignment_id number;
188 begin
189 --
190 if g_debug then
191 hr_utility.set_location('Entering pay_soe_glb.setParameters', 10);
192 end if;
193 --
194 l_assignment_id := p_assignment_id;
195 if l_assignment_id is null then
196 open csr_get_asg_id;
197 fetch csr_get_asg_id into l_assignment_id;
198 close csr_get_asg_id;
199 end if;
200
201 open csr_get_action_id(l_assignment_id);
202 fetch csr_get_action_id into l_assignment_action_id;
203 close csr_get_action_id;
204
205 /* Bug # 6887749
206 * If there is no assignment action for this employee then the cursor returns
207 * null, so we are passing -1 to java layer to raise error message.*/
208 if (l_assignment_action_id is null) then
209 return '-1';
210 else
211 return pay_soe_glb.setParameters(l_assignment_action_id);
212 end if;
213 --
214 end;
215 --
216 /* ---------------------------------------------------------------------
217 Function : Employee
218
219 Returns SQL string for retrievening Employee information based on
220 assignment ID and effective date derived from the assignment action ID
221 passed onto the SOE module
222 ------------------------------------------------------------------------ */
223 function Employee(p_assignment_action_id in number) return long is
224 --
225 begin
226 --
227 if g_debug then
228 hr_utility.set_location('Entering pay_soe_glb.Employee', 10);
229 end if;
230 --
231 l_sql :=
232 'Select org.name COL01
233 ,job.name COL02
234 ,loc.location_code COL03
235 ,grd.name COL04
236 ,pay.payroll_name COL05
237 ,pos.name COL06
238 ,hr_general.decode_organization(:tax_unit_id) COL07
239 ,pg.group_name COL08
240 ,peo.national_identifier COL09
241 ,employee_number COL10
242 ,hl.meaning COL11
243 ,assignment_number COL12
244 ,nvl(ppb1.salary,''0'') COL13
245 from per_all_people_f peo
246 ,per_all_assignments_f asg
247 ,hr_all_organization_units_vl org
248 ,per_jobs_vl job
249 ,per_all_positions pos
250 ,hr_locations loc
251 ,per_grades_vl grd
252 ,pay_payrolls_f pay
253 ,pay_people_groups pg
254 ,hr_lookups hl
255 ,(select ppb2.pay_basis_id
256 ,ppb2.business_group_id
257 ,ee.assignment_id
258 ,eev.screen_entry_value salary
259 from per_pay_bases ppb2
260 ,pay_element_entries_f ee
261 ,pay_element_entry_values_f eev
262 where ppb2.input_value_id = eev.input_value_id
263 and ee.element_entry_id = eev.element_entry_id
264 and :effective_date between ee.effective_start_date
265 and ee.effective_end_date
266 and :effective_date between eev.effective_start_date
267 and eev.effective_end_date
268 ) ppb1
269 where asg.assignment_id = :assignment_id
270 and :effective_date
271 between asg.effective_start_date and asg.effective_end_date
272 and asg.person_id = peo.person_id
273 and :effective_date
274 between peo.effective_start_date and peo.effective_end_date
275 and asg.position_id = pos.position_id(+)
276 and asg.job_id = job.job_id(+)
277 and asg.location_id = loc.location_id(+)
278 and asg.grade_id = grd.grade_id(+)
279 and asg.people_group_id = pg.people_group_id(+)
280 and asg.payroll_id = pay.payroll_id(+)
281 and :effective_date
282 between pay.effective_start_date(+) and pay.effective_end_date(+)
283 and asg.organization_id = org.organization_id
284 and :effective_date
285 between org.date_from and nvl(org.date_to, :effective_date)
286 and asg.pay_basis_id = ppb1.pay_basis_id(+)
287 and asg.assignment_id = ppb1.assignment_id(+)
288 and asg.business_group_id = ppb1.business_group_id(+)
289 and hl.application_id (+) = 800
290 and hl.lookup_type (+) =''NATIONALITY''
291 and hl.lookup_code (+) =peo.nationality';
292 --
293 --
294 if g_debug then
295 hr_utility.set_location('Leaving pay_soe_glb.Employee', 20);
296 end if;
297 --
298 return l_sql;
299 --
300 end Employee;
301 --
302 --
303 /* ---------------------------------------------------------------------
304 Function : Period
305
306 Text
307 ------------------------------------------------------------------------ */
308 function Period(p_assignment_action_id in number) return long is
309 --
310 l_action_type varchar2(2);
311 cursor periodDates is
312 select pa.action_type from
313 pay_payroll_actions pa
314 , pay_assignment_actions aa
315 where pa.payroll_action_id = aa.payroll_action_id
316 and aa.assignment_action_id = p_assignment_action_id;
317
318 begin
319 --
320 if g_debug then
321 hr_utility.set_location('Entering pay_soe_glb.Period', 10);
322 end if;
323 --
324
325 open periodDates;
326 fetch periodDates into l_action_type;
327 close periodDates;
328
329 if l_action_type is not null then
330 if l_action_type in ( 'P','U' ) then
331 l_sql :=
332 'select tp1.period_name || decode(tp2.period_name, tp1.period_name, null, '' - '' || tp2.period_name) COL01
333 ,fnd_date.date_to_displaydate(tp1.end_date,2) COL04 -- change as per bug 11830805
334 ,fnd_date.date_to_displaydate(pa2.effective_date,2) COL03 -- change as per bug 11830805
335 ,fnd_date.date_to_displaydate(aa1.start_date,2) COL05 -- change as per bug 11830805
336 ,fnd_date.date_to_displaydate(aa2.end_date,2) COL06 -- change as per bug 11830805
337 ,fnd_date.date_to_displaydate(tp1.start_date,2) COL02 -- change as per bug 11830805
338 ,tp1.period_type COL07
339 from pay_all_payrolls_f pp1
340 ,pay_all_payrolls_f pp2
341 ,pay_payroll_actions pa1
342 ,pay_payroll_actions pa2
343 ,per_time_periods tp1
344 ,per_time_periods tp2
345 ,pay_assignment_actions aa1
346 ,pay_assignment_actions aa2
347 where pa1.payroll_action_id = aa1.payroll_action_id
348 --We are considering effective_date(Date Paid) which some
349 --localizations may allows the user to change. Its the same
350 --case for the Date Earned as well. Its better to use
351 --time period id which is consistent.
352 /*and pa1.effective_date +nvl(pp1.pay_date_offset,0) =
353 tp1.regular_payment_date*/
354 and pa1.time_period_id = tp1.time_period_id
355 and pa1.payroll_id = tp1.payroll_id
356 and aa1.assignment_action_id = :PREPAY_MAX_ACTION
357 and pa2.payroll_action_id = aa2.payroll_action_id
358 --We are considering effective_date(Date Paid) which some
359 --localizations may allows the user to change. Its the same
360 --case for the Date Earned as well. Its better to use
361 --time period id which is consistent.
362 /*and pa2.effective_date +nvl(pp2.pay_date_offset,0) =
363 tp2.regular_payment_date*/
364 and pa2.time_period_id = tp2.time_period_id
365 and pa2.payroll_id = tp2.payroll_id
366 and aa2.assignment_action_id = :PREPAY_MIN_ACTION
367 and pa1.payroll_id = pp1.payroll_id
368 and pa1.effective_date between pp1.effective_start_date
369 and pp1.effective_end_date
370 and pa2.payroll_id = pp2.payroll_id
371 and pa2.effective_date between pp2.effective_start_date
372 and pp2.effective_end_date';
373 else
374 l_sql :=
375 'select tp.period_name COL01
376 ,fnd_date.date_to_displaydate(tp.end_date,2) COL04 -- change as per bug 11830805
377 ,fnd_date.date_to_displaydate(pa.effective_date,2) COL03 -- change as per bug 11830805
378 ,fnd_date.date_to_displaydate(aa.start_date,2) COL05 -- change as per bug 11830805
379 ,fnd_date.date_to_displaydate(aa.end_date,2) COL06 -- change as per bug 11830805
380 ,fnd_date.date_to_displaydate(tp.start_date,2) COL02 -- change as per bug 11830805
381 ,tp.period_type COL07
382 from pay_payroll_actions pa
383 ,per_time_periods tp
384 ,pay_assignment_actions aa
385 where pa.payroll_action_id = aa.payroll_action_id
386 -- Bug 9877851 : Included join on time period id rather than on effective date
387 /* and pa.effective_date = tp.regular_payment_date */
388 and pa.time_period_id = tp.time_period_id
389 and pa.payroll_id = tp.payroll_id
390 and aa.assignment_action_id = :assignment_action_id';
391 end if;
392 else
393 l_sql :=
394 'select tp.period_name COL01
395 ,fnd_date.date_to_displaydate(tp.end_date,2) COL04 -- change as per bug 11830805
396 ,fnd_date.date_to_displaydate(pa.effective_date,2) COL03 -- change as per bug 11830805
397 ,fnd_date.date_to_displaydate(aa.start_date,2) COL05 -- change as per bug 11830805
398 ,fnd_date.date_to_displaydate(aa.end_date,2) COL06 -- change as per bug 11830805
399 ,fnd_date.date_to_displaydate(tp.start_date,2) COL02 -- change as per bug 11830805
400 ,tp.period_type COL07
401 from pay_payroll_actions pa
402 ,per_time_periods tp
403 ,pay_assignment_actions aa
404 where pa.payroll_action_id = aa.payroll_action_id
405 and pa.time_period_id = tp.time_period_id
406 and aa.assignment_action_id = :assignment_action_id';
407 end if;
408 --
409 --
410 if g_debug then
411 hr_utility.set_location('Leaving pay_soe_glb.Period', 20);
412 end if;
413 --
414 return l_sql;
415 end Period;
416 --
417 /* ---------------------------------------------------------------------
418 Function : getElements
419
420 Text
421 ------------------------------------------------------------------------ */
422 function getElements(p_assignment_action_id number
423 ,p_element_set_name varchar2) return long is
424 begin
425 --
426 --
427 if g_debug then
428 hr_utility.set_location('Entering pay_soe_glb.getElements', 10);
429 end if;
430 --
431 -- Bugfix 5724212
432 -- Return null if p_element_set_name is NULL (since the SQL statement below
433 -- will not fetch any rows anyway).
434 --
435 if p_element_set_name is null then
436 l_sql := null;
437 else
438 --
439 l_sql := 'select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
440 , nvl(orginfo.org_information7,nvl(ettl.reporting_name, ettl.element_name)) COL02
441 , to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
442 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator,
443 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
444 from pay_assignment_actions aa
445 , pay_run_results rr
446 , pay_run_result_values rrv
447 , pay_input_values_f iv
448 , pay_input_values_f_tl ivtl
449 , pay_element_types_f et
450 , pay_element_types_f_tl ettl
451 , hr_organization_information orginfo
452 where aa.assignment_action_id :action_clause
453 and aa.assignment_action_id = rr.assignment_action_id
454 and rr.status in (''P'',''PA'')
455 and rr.run_result_id = rrv.run_result_id
456 and rr.element_type_id = et.element_type_id
457 and :effective_date between
458 et.effective_start_date and et.effective_end_date
459 and et.element_type_id = ettl.element_type_id
460 and rrv.input_value_id = iv.input_value_id
461 and iv.name = ''Pay Value''
462 and :effective_date between
463 iv.effective_start_date and iv.effective_end_date
464 and iv.input_value_id = ivtl.input_value_id
465 and ettl.language = userenv(''LANG'')
466 and ivtl.language = userenv(''LANG'')
467 and iv.element_type_id = et.element_type_id
468 and exists (select 1
469 from pay_element_set_members esm
470 ,pay_element_sets es
471 where et.element_type_id = esm.element_type_id
472 and iv.element_type_id = et.element_type_id
473 and esm.element_set_id = es.element_set_id
474 and ( es.BUSINESS_GROUP_ID IS NULL
475 OR es.BUSINESS_GROUP_ID = :business_group_id )
476 AND ( es.LEGISLATION_CODE IS NULL
477 OR es.LEGISLATION_CODE = '':legislation_code'')
478 and es.element_set_name = '''|| p_element_set_name ||''')
479 and orginfo.org_information1 = ''ELEMENT''
480 and orginfo.org_information_context = ''Business Group:SOE Detail''
481 and orginfo.organization_id = :business_group_id
482 and et.element_type_id (+)= to_number(orginfo.org_information2)
483 group by nvl(orginfo.org_information7,nvl(ettl.reporting_name, ettl.element_name))
484 , ettl.reporting_name
485 ,nvl(ettl.reporting_name,et.element_type_id)
486 UNION ALL
487 select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
488 , nvl(ettl.reporting_name,ettl.element_name) COL02
489 , to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
490 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator,
491 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
492 from pay_assignment_actions aa
493 , pay_run_results rr
494 , pay_run_result_values rrv
495 , pay_input_values_f iv
496 , pay_input_values_f_tl ivtl
497 , pay_element_types_f et
498 , pay_element_types_f_tl ettl
499 where aa.assignment_action_id :action_clause
500 and aa.assignment_action_id = rr.assignment_action_id
501 and rr.status in (''P'',''PA'')
502 and rr.run_result_id = rrv.run_result_id
503 and rr.element_type_id = et.element_type_id
504 and :effective_date between
505 et.effective_start_date and et.effective_end_date
506 and et.element_type_id = ettl.element_type_id
507 and rrv.input_value_id = iv.input_value_id
508 and iv.name = ''Pay Value''
509 and :effective_date between
510 iv.effective_start_date and iv.effective_end_date
511 and iv.input_value_id = ivtl.input_value_id
512 and ettl.language = userenv(''LANG'')
513 and ivtl.language = userenv(''LANG'')
514 and iv.element_type_id = et.element_type_id
515 and exists (select 1
516 from pay_element_set_members esm
517 ,pay_element_sets es
518 where et.element_type_id = esm.element_type_id
519 and iv.element_type_id = et.element_type_id
520 and esm.element_set_id = es.element_set_id
521 and ( es.BUSINESS_GROUP_ID IS NULL
522 OR es.BUSINESS_GROUP_ID = :business_group_id)
523 AND ( es.LEGISLATION_CODE IS NULL
524 OR es.LEGISLATION_CODE = '':legislation_code'')
525 and es.element_set_name = '''|| p_element_set_name ||''')
526 AND not exists (select 1
527 from hr_organization_information orginfo
528 WHERE orginfo.org_information1 = ''ELEMENT''
529 and orginfo.org_information_context = ''Business Group:SOE Detail''
530 and orginfo.organization_id = :business_group_id
531 and et.element_type_id = to_number(orginfo.org_information2))
532 group by nvl(ettl.reporting_name,ettl.element_name)
533 , ettl.reporting_name
534 ,nvl(ettl.reporting_name,et.element_type_id)
535 order by COL02';
536
537 --
538 end if;
539 --
540 --
541 if g_debug then
542 hr_utility.set_location('Leaving pay_soe_glb.getElements', 20);
543 end if;
544 --
545 return l_sql;
546 --
547 end getElements;
548 --
549 /* ---------------------------------------------------------------------
550 Function : getBalances
551
552 Text
553 ------------------------------------------------------------------------ */
554 function getBalances(p_assignment_action_id number
555 ,p_balance_attribute varchar2) return long is
556 --
557 TYPE balance_type_lst_rec is RECORD (balance_name varchar2(80)
558 ,reporting_name varchar2(80)
559 ,dimension_name varchar2(80)
560 ,defined_balance_name varchar2(80)
561 ,defined_balance_id number);
562 TYPE balance_type_lst_tab is TABLE of balance_type_lst_rec
563 INDEX BY BINARY_INTEGER;
564 l_balance_type_lst balance_type_lst_tab;
565 --
566 l_effective_date date;
567 l_earliest_ctx_date date;
568 l_temp_date date;
569 l_action_sequence number;
570 l_payroll_id number;
571 l_assignment_id number;
572 l_business_group_id number;
573 l_legislation_code varchar2(30);
574 l_save_asg_run_bal varchar2(30);
575 l_inp_val_name pay_input_values_f.name%type;
576 l_si_needed_chr varchar2(10);
577 l_st_needed_chr varchar2(10);
578 l_sn_needed_chr varchar2(10);
579 l_st2_needed_chr varchar2(10);
580 l_found boolean;
581 balCount number;
582 --
583 l_defined_balance_lst pay_balance_pkg.t_balance_value_tab;
584 l_context_lst pay_balance_pkg.t_context_tab;
585 l_output_table pay_balance_pkg.t_detailed_bal_out_tab;
586 --
587 i number;
588 --
589 --
590 cursor getAction is
591 select pa.payroll_id
592 , aa.action_sequence
593 , pa.effective_date
594 , aa.assignment_id
595 , pa.business_group_id
596 , bg.legislation_code
597 , lrl.rule_mode
598 from pay_payroll_actions pa
599 , pay_assignment_actions aa
600 , per_business_groups bg
601 , pay_legislation_rules lrl
602 where aa.assignment_action_id = p_assignment_action_id
603 and aa.payroll_action_id = pa.payroll_action_id
604 and pa.business_group_id = bg.business_group_id
605 and lrl.legislation_code(+) = bg.legislation_code
606 and lrl.rule_type(+) = 'SAVE_ASG_RUN_BAL';
607 --
608 cursor getDBal is
609 select ba.defined_balance_id
610 , bd.dimension_name
611 , bd.period_type
612 , bt.balance_name
613 , bt.reporting_name
614 , nvl(oi.org_information7,nvl(bt.reporting_name,bt.balance_name)) defined_balance_name
615 from pay_balance_attributes ba
616 , pay_bal_attribute_definitions bad
617 , pay_defined_balances db
618 , pay_balance_dimensions bd
619 , pay_balance_types_tl bt
620 , hr_organization_information oi
621 where bad.attribute_name = p_balance_attribute
622 and ( bad.BUSINESS_GROUP_ID IS NULL
623 OR bad.BUSINESS_GROUP_ID = l_business_group_id)
624 AND ( bad.LEGISLATION_CODE IS NULL
625 OR bad.LEGISLATION_CODE = l_legislation_code)
626 and bad.attribute_id = ba.attribute_id
627 and ba.defined_balance_id = db.defined_balance_id
628 and db.balance_dimension_id = bd.balance_dimension_id
629 and db.balance_type_id = bt.balance_type_id
630 and bt.language = userenv('LANG')
631 --
632 and oi.org_information1 = 'BALANCE'
633 --
634 and oi.org_information4 = to_char(bt.balance_type_id)
635 and oi.org_information5 = to_char(db.balance_dimension_id)
636 --
637 and oi.org_information_context = 'Business Group:SOE Detail'
638 and oi.organization_id = l_business_group_id;
639 --
640 cursor getRBContexts is
641 select rb.TAX_UNIT_ID
642 , rb.JURISDICTION_CODE
643 , rb.SOURCE_ID
644 , rb.SOURCE_TEXT
645 , rb.SOURCE_NUMBER
646 , rb.SOURCE_TEXT2
647 from pay_run_balances rb
648 , pay_assignment_actions aa
649 , pay_payroll_actions pa
650 where rb.ASSIGNMENT_ID = l_assignment_id
651 and l_action_sequence >= aa.action_sequence
652 and rb.assignment_action_id = aa.assignment_action_id
653 and aa.payroll_action_id = pa.payroll_action_id
654 and pa.effective_date >= l_earliest_ctx_date;
655 --
656 cursor getRRContexts is
657 select distinct
658 aa.tax_unit_id tax_unit_id
659 , rr.jurisdiction_code jurisdiction_code
660 , decode(l_si_needed_chr,
661 'Y', pay_balance_pkg.find_context('SOURCE_ID'
662 ,rr.run_result_id)
663 ,null) source_id
664 , decode(l_st_needed_chr,
665 'Y', pay_balance_pkg.find_context('SOURCE_TEXT'
666 ,rr.run_result_id)
667 ,null) source_text
668 , decode(l_sn_needed_chr,
669 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER'
670 ,rr.run_result_id)
671 ,null) source_number
672 , decode(l_st2_needed_chr,
673 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2'
674 ,rr.run_result_id)
675 ,null) source_text2
676 from pay_assignment_actions aa,
677 pay_payroll_actions pa,
678 pay_run_results rr
679 where aa.ASSIGNMENT_ID = l_assignment_id
680 and aa.assignment_action_id = rr.assignment_action_id
681 and l_action_sequence >= aa.action_sequence
682 and aa.payroll_action_id = pa.payroll_action_id
683 and pa.effective_date >= l_earliest_ctx_date;
684 --
685 begin
686 --
687 if g_debug then
688 hr_utility.set_location('Entering pay_soe_glb.getBalances', 10);
689 end if;
690 --
691 open getAction;
692 fetch getAction into l_payroll_id,
693 l_action_sequence,
694 l_effective_date,
695 l_assignment_id,
696 l_business_group_id,
697 l_legislation_code,
698 l_save_asg_run_bal;
699 close getAction;
700 /*Bug 10212578 */
701 if l_save_asg_run_bal is null and substr(fnd_release.release_name,1,2) = '12' and substr(fnd_release.release_name,4,1) >= '2' then
702 l_save_asg_run_bal:='Y';
703 end if;
704 --
705 l_earliest_ctx_date := l_effective_date;
706 --
707 i := 0;
708 --
709 if g_debug then
710 hr_utility.set_location('pay_soe_glb.getBalances', 20);
711 end if;
712 --
713 for db in getDBal loop
714 i := i + 1;
715 --
716 l_defined_balance_lst(i).defined_balance_id := db.defined_balance_id;
717 --
718 l_balance_type_lst(db.defined_balance_id).balance_name :=
719 db.balance_name;
720 l_balance_type_lst(db.defined_balance_id).reporting_name :=
721 db.reporting_name;
722 l_balance_type_lst(db.defined_balance_id).defined_balance_name:=
723 db.defined_balance_name;
724 l_balance_type_lst(db.defined_balance_id).dimension_name :=
725 db.dimension_name;
726 l_balance_type_lst(db.defined_balance_id).defined_balance_id :=
727 db.defined_balance_id;
728 --
729 pay_balance_pkg.get_period_type_start
730 (p_period_type => db.period_type
731 ,p_effective_date => l_effective_date
732 ,p_payroll_id => l_payroll_id
733 ,p_start_date => l_temp_date);
734 --
735 if l_temp_date < l_earliest_ctx_date then
736 l_earliest_ctx_date := l_temp_date;
737 end if;
738 end loop;
739 --
740 i := 0;
741 if l_save_asg_run_bal = 'Y' then
742 if g_debug then
743 hr_utility.set_location('pay_soe_glb.getBalances', 30);
744 end if;
745 for ctx in getRBContexts loop
746 i := i + 1;
747 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
748 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
749 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
750 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
751 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
752 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
753 end loop;
754 else
755 if g_debug then
756 hr_utility.set_location('pay_soe_glb.getBalances', 40);
757 end if;
758 -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
759 l_si_needed_chr := 'N';
760 l_st_needed_chr := 'N';
761 l_sn_needed_chr := 'N';
762 l_st2_needed_chr := 'N';
763 --
764 pay_core_utils.get_leg_context_iv_name('SOURCE_ID',
765 l_legislation_code,
766 l_inp_val_name,
767 l_found);
768 if (l_found = TRUE) then
769 l_si_needed_chr := 'Y';
770 end if;
771 --
772 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT',
773 l_legislation_code,
774 l_inp_val_name,
775 l_found);
776 if (l_found = TRUE) then
777 l_st_needed_chr := 'Y';
778 end if;
779 --
780 pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER',
781 l_legislation_code,
782 l_inp_val_name,
783 l_found);
784 if (l_found = TRUE) then
785 l_sn_needed_chr := 'Y';
786 end if;
787 --
788 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT2',
789 l_legislation_code,
790 l_inp_val_name,
791 l_found);
792 if (l_found = TRUE) then
793 l_st2_needed_chr := 'Y';
794 end if;
795 --
796 --
797 for ctx in getRRContexts loop
798 i := i + 1;
799 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
800 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
801 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
802 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
803 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
804 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
805 end loop;
806 end if;
807 --
808 --
809 if g_debug then
810 hr_utility.set_location('pay_soe_glb.getBalances', 50);
811 end if;
812 --
813 pay_balance_pkg.get_value (p_assignment_action_id => p_assignment_action_id
814 ,p_defined_balance_lst => l_defined_balance_lst
815 ,p_context_lst => l_context_lst
816 ,p_output_table => l_output_table);
817 --
818 pay_soe_util.clear;
819 --
820 balCount := 0;
821 if l_output_table.count > 0 then
822 --
823 if g_debug then
824 hr_utility.set_location('pay_soe_glb.getBalances', 60);
825 end if;
826 --
827 for i in l_output_table.first..l_output_table.last loop
828 if l_output_table(i).balance_value <> 0 then
829 balCount := balCount + 1;
830 --
831 pay_soe_util.setValue('01'
832 ,l_balance_type_lst(l_output_table(i).defined_balance_id).balance_name
833 ,TRUE,FALSE);
834 pay_soe_util.setValue('02'
835 ,l_balance_type_lst(l_output_table(i).defined_balance_id).reporting_name
836 ,FALSE,FALSE);
837 pay_soe_util.setValue('03'
838 ,l_balance_type_lst(l_output_table(i).defined_balance_id).dimension_name
839 ,FALSE,FALSE);
840 pay_soe_util.setValue('04'
841 ,l_balance_type_lst(l_output_table(i).defined_balance_id).defined_balance_name
842 ,FALSE,FALSE);
843 pay_soe_util.setValue('05',
844 hr_general.decode_organization(to_char(l_output_table(i).tax_unit_id))
845 ,FALSE,FALSE);
846 pay_soe_util.setValue('06',to_char(l_output_table(i).tax_unit_id),FALSE,FALSE);
847
848
849 pay_soe_util.setValue('07',l_output_table(i).jurisdiction_code,FALSE,FALSE);
850 pay_soe_util.setValue('08',l_output_table(i).source_id,FALSE,FALSE);
851 pay_soe_util.setValue('09',l_output_table(i).source_text,FALSE,FALSE);
852 pay_soe_util.setValue('10',l_output_table(i).source_number,FALSE,FALSE);
853 pay_soe_util.setValue('11',l_output_table(i).source_text2,FALSE,FALSE);
854
855 pay_soe_util.setValue(16,to_char(l_output_table(i).balance_value,
856 fnd_currency.get_format_mask(substr(g_currency_code,2,3),40)),FALSE,FALSE);
857 pay_soe_util.setValue(17,to_char(l_output_table(i).defined_balance_id),FALSE,TRUE);
858
859 end if;
860 end loop;
861 end if;
862 --
863 if balCount > 0 then
864 return pay_soe_util.genCursor;
865 else
866 --
867 -- Bugfix 5724212
868 -- Return null since we are not fetching any rows.
869 --
870 return null;
871 --
872 end if;
873 end getBalances;
874 --
875 /* ---------------------------------------------------------------------
876 Function : getInformation
877
878 Text
879 ------------------------------------------------------------------------ */
880 function getInformation(p_assignment_action_id number
881 ,p_element_set_name varchar2) return long is
882 begin
883 --
884 hr_utility.trace('in getInformation' || p_element_set_name || p_assignment_action_id);
885 --
886 -- Bugfix 7527825
887 -- Added nvl(oi.org_information7, to COL02
888 --
889 --
890 --
891 -- Bugfix 5724212
892 -- Return null if p_element_set_name is NULL (since the SQL statement below
893 -- will not fetch any rows anyway).
894 --
895 if p_element_set_name is null then
896 l_sql := null;
897 else
898 --
899 l_sql :=
900 'select distinct ettl.element_name COL01
901 , nvl(oi.org_information7,nvl(ettl.reporting_name, ettl.element_name)) COL02 -- for BUG 3880887,7527825
902 , ivtl.name COL03
903 , rrv.result_value COL04
904 , 1 COL05 -- to indicate that we should drilldown directly to run_result_values
905 , rr.run_result_id COL18
906 from pay_assignment_actions aa
907 , pay_run_results rr
908 , pay_run_result_values rrv
909 , pay_input_values_f iv
910 , pay_input_values_f_tl ivtl
911 , pay_element_types_f et
912 , pay_element_types_f_tl ettl
913 , hr_organization_information oi
914 where aa.assignment_action_id :action_clause
915 and aa.assignment_action_id = rr.assignment_action_id
916 and rr.status in (''P'',''PA'')
917 and rr.run_result_id = rrv.run_result_id
918 and rr.element_type_id = et.element_type_id
919 and rrv.input_value_id = iv.input_value_id
920 and to_char(iv.input_value_id) = oi.org_information3
921 and iv.input_value_id = ivtl.input_value_id
922 and ivtl.language = userenv(''LANG'')
923 and :effective_date between
924 iv.effective_start_date and iv.effective_end_date
925 and to_char(et.element_type_id) = oi.org_information2
926 and :effective_date between
927 et.effective_start_date and et.effective_end_date
928 and et.element_type_id = ettl.element_type_id
929 and ettl.language = userenv(''LANG'')
930 and iv.element_type_id = et.element_type_id
931 and exists (select 1
932 from pay_element_set_members esm
933 , pay_element_sets es
934 where et.element_type_id = esm.element_type_id
935 and iv.element_type_id = et.element_type_id
936 and ( esm.BUSINESS_GROUP_ID IS NULL
937 OR esm.BUSINESS_GROUP_ID = :business_group_id)
938 AND ( esm.LEGISLATION_CODE IS NULL
939 OR esm.LEGISLATION_CODE = '':legislation_code'')
940 and esm.element_set_id = es.element_set_id
941 and ( es.BUSINESS_GROUP_ID IS NULL
942 OR es.BUSINESS_GROUP_ID = :business_group_id)
943 AND ( es.LEGISLATION_CODE IS NULL
944 OR es.LEGISLATION_CODE = '':legislation_code'' )
945 and es.element_set_name = ''' || p_element_set_name || ''' )
946 --
947 and oi.org_information1 = ''ELEMENT''
948 --
949 and oi.org_information_context = ''Business Group:SOE Detail''
950 and oi.organization_id = :business_group_id';
951 --
952 end if;
953 --
954 return l_sql;
955 end getInformation;
956 --
957 /* ---------------------------------------------------------------------
958 Function : PrePayments
959
960 Text
961 ------------------------------------------------------------------------ */
962 function PrePayments(p_assignment_action_id number) return long is
963 begin
964 l_sql :=
965 'select ORG_PAYMENT_METHOD_NAME COL01
966 ,pt.payment_type_name COL04
967 ,pay_soe_util.getBankDetails('':legislation_code''
968 ,ppm.external_account_id
969 ,''BANK_NAME''
970 ,null) COL02
971 ,pay_soe_util.getBankDetails('':legislation_code''
972 ,ppm.external_account_id
973 ,''BANK_ACCOUNT_NUMBER''
974 ,fnd_profile.value(''HR_MASK_CHARACTERS'')) COL03
975 ,to_char(pp.value,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
976 from pay_pre_payments pp
977 , pay_personal_payment_methods_f ppm
978 , pay_org_payment_methods_f opm
979 , pay_payment_types_tl pt
980 where pp.assignment_action_id in
981 (select ai.locking_action_id
982 from pay_action_interlocks ai
983 where ai.locked_action_id :action_clause)
984 and pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
985 and :effective_date
986 between ppm.effective_start_date(+) and ppm.effective_end_date(+)
987 and pp.org_payment_method_id = opm.org_payment_method_id
988 and :effective_date
989 between opm.effective_start_date and opm.effective_end_date
990 and opm.payment_type_id = pt.payment_type_id
991 and pt.language = userenv(''LANG'')';
992 --
993 return l_sql;
994 end PrePayments;
995 --
996 /* ---------------------------------------------------------------------
997 Function : Message
998
999 Text
1000 ------------------------------------------------------------------------ */
1001 function Message(p_assignment_action_id number) return long is
1002 begin
1003 l_sql :=
1004 'select distinct line_text COL01
1005 from pay_message_lines
1006 where source_id :action_clause';
1007 --
1008 return l_sql;
1009 end Message;
1010 --
1011 /* ---------------------------------------------------------------------
1012 Function : Elements1
1013
1014 Text
1015 ------------------------------------------------------------------------ */
1016 function Elements1(p_assignment_action_id number) return long is
1017 begin
1018 hr_utility.trace('Entering elements1');
1019 return getElements(p_assignment_action_id
1020 ,pay_soe_util.getConfig('ELEMENTS1'));
1021 hr_utility.trace('Leaving Elements1');
1022 end Elements1;
1023 --
1024 /* ---------------------------------------------------------------------
1025 Function : SetParameters
1026 Function : Elements2
1027
1028 Text
1029 ------------------------------------------------------------------------ */
1030 function Elements2(p_assignment_action_id number) return long is
1031 begin
1032 return getElements(p_assignment_action_id
1033 ,pay_soe_util.getConfig('ELEMENTS2'));
1034 end Elements2;
1035 --
1036 /* ---------------------------------------------------------------------
1037 Function : SetParameters
1038 Function : Elements3
1039
1040 Text
1041 ------------------------------------------------------------------------ */
1042 function Elements3(p_assignment_action_id number) return long is
1043 begin
1044 return getElements(p_assignment_action_id
1045 ,pay_soe_util.getConfig('ELEMENTS3'));
1046 end Elements3;
1047 --
1048 /* ---------------------------------------------------------------------
1049 Function : SetParameters
1050 Function : Elements4
1051
1052 Text
1053 ------------------------------------------------------------------------ */
1054 function Elements4(p_assignment_action_id number) return long is
1055 begin
1056 return getElements(p_assignment_action_id
1057 ,pay_soe_util.getConfig('ELEMENTS4'));
1058 end Elements4;
1059 --
1060 /* ---------------------------------------------------------------------
1061 Function : SetParameters
1062 Function : Elements5
1063
1064 Text
1065 ------------------------------------------------------------------------ */
1066 function Elements5(p_assignment_action_id number) return long is
1067 begin
1068 return getElements(p_assignment_action_id
1069 ,pay_soe_util.getConfig('ELEMENTS5'));
1070 end Elements5;
1071 --
1072 /* ---------------------------------------------------------------------
1073 Function : SetParameters
1074 Function : Elements6
1075
1076 Text
1077 ------------------------------------------------------------------------ */
1078 function Elements6(p_assignment_action_id number) return long is
1079 begin
1080 return getElements(p_assignment_action_id
1081 ,pay_soe_util.getConfig('ELEMENTS6'));
1082 end Elements6;
1083 --
1084 /* ---------------------------------------------------------------------
1085 Function : SetParameters
1086 Function : Information1
1087
1088 Text
1089 ------------------------------------------------------------------------ */
1090 function Information1(p_assignment_action_id number) return long is
1091 begin
1092 hr_utility.trace('in Information1');
1093 return getInformation(p_assignment_action_id
1094 ,pay_soe_util.getConfig('INFORMATION1'));
1095 end Information1;
1096 --
1097 /* ---------------------------------------------------------------------
1098 Function : Balances1
1099
1100 Text
1101 ------------------------------------------------------------------------ */
1102 function Balances1(p_assignment_action_id number) return long is
1103 begin
1104 return getBalances(p_assignment_action_id
1105 ,pay_soe_util.getConfig('BALANCES1'));
1106 end Balances1;
1107 --
1108 /* ---------------------------------------------------------------------
1109 Function : Balances2
1110
1111 Text
1112 ------------------------------------------------------------------------ */
1113 function Balances2(p_assignment_action_id number) return long is
1114 begin
1115 return getBalances(p_assignment_action_id
1116 ,pay_soe_util.getConfig('BALANCES2'));
1117 end Balances2;
1118 --
1119 /* ---------------------------------------------------------------------
1120 Function : Balances3
1121
1122 Text
1123 ------------------------------------------------------------------------ */
1124 function Balances3(p_assignment_action_id number) return long is
1125 begin
1126 return getBalances(p_assignment_action_id
1127 ,pay_soe_util.getConfig('BALANCES3'));
1128 end Balances3;
1129 --
1130 ---------------------------------------------------------------------------
1131 -- Function : get_retro_period, taken from pynlgenr.pkb
1132 -- Function returns the retro period for the given element_entry_id and
1133 -- date_earned
1134 ---------------------------------------------------------------------------
1135
1136 function get_retro_period
1137 ( p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1138 p_date_earned in pay_payroll_actions.date_earned%TYPE,
1139 p_call_type in integer -- if 0 then return the period, 1 retro_start, 2 retro_end
1140 ) return varchar2 is
1141
1142 cursor c_get_creator_type(c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1143 c_date_earned in pay_payroll_actions.date_earned%TYPE
1144 ) is
1145 SELECT creator_type
1146 FROM pay_element_entries_f pee
1147 WHERE pee.element_entry_id=c_element_entry_id
1148 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1149
1150
1151 cursor get_retro_period_rr
1152 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1153 c_date_earned in pay_payroll_actions.date_earned%TYPE
1154 ) is
1155
1156 SELECT ptp.start_date
1157 ,ptp.end_date
1158 ,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
1159 FROM per_time_periods ptp,
1160 pay_payroll_actions ppa,
1161 pay_assignment_actions paa,
1162 pay_run_results prr,
1163 pay_element_entries_f pee
1164 WHERE pee.element_entry_id=c_element_entry_id
1165 and prr.run_result_id = pee.source_id
1166 and paa.assignment_action_id=prr.assignment_action_id
1167 and ppa.payroll_action_id=paa.payroll_action_id
1168 and ptp.payroll_id=ppa.payroll_id
1169 and pee.creator_type='RR'
1170 and ppa.date_earned between ptp.start_date and ptp.end_date
1171 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1172
1173
1174 cursor get_retro_period_nr
1175 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1176 c_date_earned in pay_payroll_actions.date_earned%TYPE
1177 ) is
1178 SELECT ptp.start_date
1179 ,ptp.end_date
1180 ,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
1181 FROM per_time_periods ptp,
1182 pay_payroll_actions ppa,
1183 pay_assignment_actions paa,
1184 pay_run_results prr,
1185 pay_element_entries_f pee
1186 WHERE pee.element_entry_id=c_element_entry_id
1187 and prr.run_result_id = pee.source_id
1188 and paa.assignment_action_id=prr.assignment_action_id
1189 and ppa.payroll_action_id=paa.payroll_action_id
1190 and ptp.payroll_id=ppa.payroll_id
1191 and pee.creator_type='NR'
1192 and ppa.date_earned between ptp.start_date and ptp.end_date
1193 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1194
1195
1196 cursor get_retro_period_pr
1197 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1198 c_date_earned in pay_payroll_actions.date_earned%TYPE
1199 ) is
1200 SELECT ptp.start_date
1201 ,ptp.end_date
1202 ,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
1203 FROM per_time_periods ptp,
1204 pay_payroll_actions ppa,
1205 pay_assignment_actions paa,
1206 pay_run_results prr,
1207 pay_element_entries_f pee
1208 WHERE pee.element_entry_id=c_element_entry_id
1209 and prr.run_result_id = pee.source_id
1210 and paa.assignment_action_id=prr.assignment_action_id
1211 and ppa.payroll_action_id=paa.payroll_action_id
1212 and ptp.payroll_id=ppa.payroll_id
1213 and pee.creator_type='PR'
1214 and ppa.date_earned between ptp.start_date and ptp.end_date
1215 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1216
1217
1218 cursor get_retro_period_ee
1219 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1220 c_date_earned in pay_payroll_actions.date_earned%TYPE
1221 ) is
1222 SELECT ptp.start_date
1223 ,ptp.end_date
1224 ,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
1225 FROM per_time_periods ptp,
1226 pay_payroll_actions ppa,
1227 pay_assignment_actions paa,
1228 pay_element_entries_f pee
1229 WHERE pee.element_entry_id=c_element_entry_id
1230 and paa.assignment_action_id=pee.source_asg_action_id
1231 and ppa.payroll_action_id=paa.payroll_action_id
1232 and ptp.payroll_id=ppa.payroll_id
1233 and pee.creator_type='EE'
1234 and ppa.date_earned between ptp.start_date and ptp.end_date
1235 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1236
1237 l_creator_type pay_element_entries_f.creator_type%TYPE;
1238 l_period_obtained_flag number;
1239 l_retro_start_date date;
1240 l_retro_end_date date;
1241 l_period_num_year varchar2(30) :=null;
1242
1243
1244 begin
1245 l_period_obtained_flag:=1;
1246 hr_utility.set_location('Entering: '||l_period_obtained_flag,1);
1247
1248 OPEN c_get_creator_type(p_element_entry_id,p_date_earned);
1249 FETCH c_get_creator_type INTO l_creator_type ;
1250 CLOSE c_get_creator_type;
1251
1252
1253 if l_creator_type = 'RR' then
1254 OPEN get_retro_period_rr(p_element_entry_id,p_date_earned);
1255 FETCH get_retro_period_rr into l_retro_start_date,
1256 l_retro_end_date,
1257 l_period_num_year;
1258 CLOSE get_retro_period_rr;
1259 l_period_obtained_flag:=1;
1260 end if;
1261
1262 if l_creator_type = 'NR' then
1263 OPEN get_retro_period_nr(p_element_entry_id,p_date_earned);
1264 FETCH get_retro_period_nr into l_retro_start_date,
1265 l_retro_end_date,
1266 l_period_num_year;
1267 CLOSE get_retro_period_nr;
1268 l_period_obtained_flag:=1;
1269 end if;
1270
1271 if l_creator_type = 'PR' then
1272 OPEN get_retro_period_pr(p_element_entry_id,p_date_earned);
1273 FETCH get_retro_period_pr into l_retro_start_date,
1274 l_retro_end_date,
1275 l_period_num_year;
1276 CLOSE get_retro_period_pr;
1277 l_period_obtained_flag:=1;
1278 end if;
1279
1280 if l_creator_type = 'EE' then
1281 OPEN get_retro_period_ee(p_element_entry_id,p_date_earned);
1282 FETCH get_retro_period_ee into l_retro_start_date,
1283 l_retro_end_date,
1284 l_period_num_year;
1285 CLOSE get_retro_period_ee;
1286 l_period_obtained_flag:=1;
1287 end if;
1288
1289 hr_utility.set_location('Entering element entry id: '||p_element_entry_id,4);
1290 hr_utility.set_location('Entering start date earned : '||p_date_earned,5);
1291 hr_utility.set_location('Entering period obtained flag: '||l_period_obtained_flag,6);
1292
1293 if p_call_type = 1 then
1294 return l_retro_start_date;
1295 elsif p_call_type = 2 then
1296 return l_retro_end_date;
1297 elsif p_call_type = 0 then
1298 return l_period_num_year;
1299 end if;
1300 end get_retro_period;
1301
1302 end pay_soe_glb;