[Home] [Help]
PACKAGE BODY: APPS.PAY_AE_SOE
Source
1 PACKAGE BODY pay_ae_soe AS
2 /* $Header: pyaesoer.pkb 120.1.12000000.4 2007/06/26 09:27:37 spendhar noship $ */
3
4 /*Function to pick up employee details*/
5
6 FUNCTION employees(p_assignment_action_id NUMBER) RETURN LONG IS
7 l_sql LONG;
8 BEGIN
9 --
10 l_sql :=
11 'Select org.name COL01
12 ,job.name COL02
13 ,loc.location_code COL03
14 ,grd.name COL04
15 ,pay.payroll_name COL05
16 ,pos.name COL06
17 ,hr_general.decode_organization(:tax_unit_id) COL07
18 ,pg.group_name COL08
19 ,peo.national_identifier COL09
20 ,hl.meaning COL10
21 ,asg.assignment_number COL11
22 ,hl1.meaning ||'' ''|| peo.full_name COL12
23 , decode(peo.per_information3,peo.per_information3,peo.per_information3||'' '',null) || decode(peo.per_information4,peo.per_information4,peo.per_information4||'' '',null)
24 || decode(peo.per_information5,peo.per_information5,peo.per_information5||'' '',null) || decode(peo.per_information6,peo.per_information6,peo.per_information6||'' '',null) COL14
25 from per_all_people_f peo
26 ,per_all_assignments_f asg
27 ,hr_all_organization_units_vl org
28 ,per_jobs_vl job
29 ,per_all_positions pos
30 ,hr_locations loc
31 ,per_grades_vl grd
32 ,pay_payrolls_f pay
33 ,pay_people_groups pg
34 ,hr_lookups hl
35 ,hr_lookups hl1
36 where asg.assignment_id = :assignment_id
37 and :effective_date
38 between asg.effective_start_date and asg.effective_end_date
39 and asg.person_id = peo.person_id
40 and :effective_date
41 between peo.effective_start_date and peo.effective_end_date
42 and asg.position_id = pos.position_id(+)
43 and asg.job_id = job.job_id(+)
44 and asg.location_id = loc.location_id(+)
45 and asg.grade_id = grd.grade_id(+)
46 and asg.people_group_id = pg.people_group_id(+)
47 and asg.payroll_id = pay.payroll_id(+)
48 and :effective_date
49 between pay.effective_start_date(+) and pay.effective_end_date(+)
50 and asg.organization_id = org.organization_id
51 and :effective_date
52 between org.date_from and nvl(org.date_to, :effective_date)
53 and hl.application_id (+) = 800
54 and hl.lookup_type (+) =''AE_NATIONALITY''
55 and hl.lookup_code (+) =peo.per_information18
56 and hl1.application_id (+) = 800
57 and hl1.lookup_type (+)=''TITLE''
58 and hl1.lookup_code (+)=peo.title';
59 return l_sql;
60 end employees;
61 --
62
63 function getBalances(p_assignment_action_id number
64 ,p_balance_attribute varchar2) return long is
65 --
66 TYPE balance_type_lst_rec is RECORD (balance_name varchar2(80)
67 ,reporting_name varchar2(80)
68 ,dimension_name varchar2(80)
69 ,defined_balance_name varchar2(80)
70 ,defined_balance_id number
71 , meaning_uom varchar2(100));
72 TYPE balance_type_lst_tab is TABLE of balance_type_lst_rec
73 INDEX BY BINARY_INTEGER;
74 l_balance_type_lst balance_type_lst_tab;
75 --
76 l_effective_date date;
77 l_earliest_ctx_date date;
78 l_temp_date date;
79 l_action_sequence number;
80 l_payroll_id number;
81 l_assignment_id number;
82 l_business_group_id number;
83 l_legislation_code varchar2(30);
84 l_save_asg_run_bal varchar2(30);
85 l_inp_val_name pay_input_values_f.name%type;
86 l_si_needed_chr varchar2(10);
87 l_st_needed_chr varchar2(10);
88 l_sn_needed_chr varchar2(10);
89 l_st2_needed_chr varchar2(10);
90 l_found boolean;
91 l_balance_uom varchar2(40);
92 l_meaning_uom varchar2(100);
93 l_currency_code varchar2(100);
94 balCount number;
95 --
96 l_defined_balance_lst pay_balance_pkg.t_balance_value_tab;
97 l_context_lst pay_balance_pkg.t_context_tab;
98 l_output_table pay_balance_pkg.t_detailed_bal_out_tab;
99 --
100 i number;
101
102 l_nat varchar2(80);
103 l_local_nat varchar2(80);
104 --
105 l_rr_processed varchar2(1);
106 l_si_ele_id number;
107 --
108 cursor getAction is
109 select pa.payroll_id
110 , aa.action_sequence
111 , pa.effective_date
112 , aa.assignment_id
113 , pa.business_group_id
114 , bg.legislation_code
115 , lrl.rule_mode
116 from pay_payroll_actions pa
117 , pay_assignment_actions aa
118 , per_business_groups bg
119 , pay_legislation_rules lrl
120 where aa.assignment_action_id = p_assignment_action_id
121 and aa.payroll_action_id = pa.payroll_action_id
122 and pa.business_group_id = bg.business_group_id
123 and lrl.legislation_code(+) = bg.legislation_code
124 and lrl.rule_type(+) = 'SAVE_ASG_RUN_BAL';
125 --
126 --
127 cursor getParameters(c_assignment_action_id in number) is
128 select '''' || bg.currency_code || '''' currency_code
129 from pay_payroll_actions pa
130 , pay_assignment_actions aa
131 , per_business_groups bg
132 where aa.assignment_action_id = p_assignment_action_id
133 and aa.payroll_action_id = pa.payroll_action_id
134 and pa.business_group_id = bg.business_group_id
135 and rownum = 1;
136 --
137 cursor getDBal is
138 select ba.defined_balance_id
139 , bd.dimension_name
140 , bd.period_type
141 , bt.balance_name
142 , bt.reporting_name
143 , nvl(oi.org_information7,nvl(bt.reporting_name,bt.balance_name)) defined_balance_name
144 , pbt.balance_uom
145 , hl.meaning
146 from pay_balance_attributes ba
147 , pay_bal_attribute_definitions bad
148 , pay_defined_balances db
149 , pay_balance_dimensions bd
150 , pay_balance_types_tl bt
151 , hr_organization_information oi
152 , pay_balance_types pbt
153 , hr_lookups hl
154 where bad.attribute_name = p_balance_attribute
155 and ( bad.BUSINESS_GROUP_ID IS NULL
156 OR bad.BUSINESS_GROUP_ID = l_business_group_id)
157 AND ( bad.LEGISLATION_CODE IS NULL
158 OR bad.LEGISLATION_CODE = l_legislation_code)
159 and bad.attribute_id = ba.attribute_id
160 and ba.defined_balance_id = db.defined_balance_id
161 and db.balance_dimension_id = bd.balance_dimension_id
162 and db.balance_type_id = bt.balance_type_id
163 and db.balance_type_id = pbt.balance_type_id
164 and pbt.balance_type_id = bt.balance_type_id
165 and bt.language = userenv('LANG')
166 and oi.org_information1 = 'BALANCE'
167 and oi.org_information4 = to_char(bt.balance_type_id)
168 and oi.org_information5 = to_char(db.balance_dimension_id)
169 and oi.org_information_context = 'Business Group:SOE Detail'
170 and oi.organization_id = l_business_group_id
171 and hl.lookup_type='UNITS'
172 and hl.lookup_code = pbt.balance_uom;
173 --
174 cursor getRBContexts is
175 select rb.TAX_UNIT_ID
176 , rb.JURISDICTION_CODE
177 , rb.SOURCE_ID
178 , rb.SOURCE_TEXT
179 , rb.SOURCE_NUMBER
180 , rb.SOURCE_TEXT2
181 from pay_run_balances rb
182 , pay_assignment_actions aa
183 , pay_payroll_actions pa
184 where rb.ASSIGNMENT_ID = l_assignment_id
185 and l_action_sequence >= aa.action_sequence
186 and rb.assignment_action_id = aa.assignment_action_id
187 and aa.payroll_action_id = pa.payroll_action_id
188 and pa.effective_date >= l_earliest_ctx_date;
189 --
190 cursor getRRContexts is
191 select distinct
192 aa.tax_unit_id tax_unit_id
193 , rr.jurisdiction_code jurisdiction_code
194 , decode(l_si_needed_chr,
195 'Y', pay_balance_pkg.find_context('SOURCE_ID'
196 ,rr.run_result_id)
197 ,null) source_id
198 , decode(l_st_needed_chr,
199 'Y', pay_balance_pkg.find_context('SOURCE_TEXT'
200 ,rr.run_result_id)
201 ,null) source_text
202 , decode(l_sn_needed_chr,
203 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER'
204 ,rr.run_result_id)
205 ,null) source_number
206 , decode(l_st2_needed_chr,
207 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2'
208 ,rr.run_result_id)
209 ,null) source_text2
210 from pay_assignment_actions aa,
211 pay_payroll_actions pa,
212 pay_run_results rr
213 where aa.ASSIGNMENT_ID = l_assignment_id
214 and aa.assignment_action_id = rr.assignment_action_id
215 and l_action_sequence >= aa.action_sequence
216 and aa.payroll_action_id = pa.payroll_action_id
217 and pa.effective_date >= l_earliest_ctx_date;
218 --
219 cursor getNationality IS
220 SELECT person.per_information18
221 FROM per_all_people_f person, per_all_assignments_f asg
222 WHERE person.person_id = asg.person_id
223 AND asg.assignment_id = l_assignment_id
224 AND trunc(l_effective_date,'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
225 AND trunc(l_effective_date,'MM') between trunc(person.effective_start_date,'MM') and person.effective_end_date;
226 --
227 cursor getLocalNat IS
228 SELECT org_information1
229 FROM HR_ORGANIZATION_INFORMATION
230 WHERE ORG_INFORMATION_CONTEXT = 'AE_BG_DETAILS'
231 AND ORGANIZATION_ID = l_business_group_id;
232 --
233
234 --
235 cursor getSIele IS
236 SELECT element_type_id
237 FROM pay_element_types_f
238 WHERE element_name = 'Social Insurance'
239 and legislation_code = 'AE';
240 --
241 cursor getRRstatus(l_ele_id number) IS
242 SELECT status
243 FROM pay_run_results rr
244 WHERE rr.assignment_action_id = p_assignment_action_id
245 AND rr.element_type_id = l_ele_id;
246 --
247 begin
248
249
250 l_nat := null;
251 l_local_nat := null;
252 l_rr_processed := null;
253
254 open getAction;
255 fetch getAction into l_payroll_id,
256 l_action_sequence,
257 l_effective_date,
258 l_assignment_id,
259 l_business_group_id,
260 l_legislation_code,
261 l_save_asg_run_bal;
262 close getAction;
263 --
264 l_earliest_ctx_date := l_effective_date;
265 --
266 open getParameters(p_assignment_action_id);
267 fetch getParameters into l_currency_code;
268 close getParameters;
269 --
270 i := 0;
271 for db in getDBal loop
272 i := i + 1;
273 --
274 l_defined_balance_lst(i).defined_balance_id := db.defined_balance_id;
275 --
276 l_balance_type_lst(db.defined_balance_id).balance_name :=
277 db.balance_name;
278 l_balance_type_lst(db.defined_balance_id).reporting_name :=
279 db.reporting_name;
280 l_balance_type_lst(db.defined_balance_id).defined_balance_name:=
281 db.defined_balance_name;
282 l_balance_type_lst(db.defined_balance_id).dimension_name :=
283 db.dimension_name;
284 l_balance_type_lst(db.defined_balance_id).defined_balance_id :=
285 db.defined_balance_id;
286 l_balance_type_lst(db.defined_balance_id).meaning_uom:=
287 db.meaning;
288 --
289 pay_balance_pkg.get_period_type_start
290 (p_period_type => db.period_type
291 ,p_effective_date => l_effective_date
292 ,p_payroll_id => l_payroll_id
293 ,p_start_date => l_temp_date);
294 --
295 if l_temp_date < l_earliest_ctx_date then
296 l_earliest_ctx_date := l_temp_date;
297 end if;
298 end loop;
299 --
300 i := 0;
301 if l_save_asg_run_bal = 'Y' then
302
303
304 OPEN getLocalNat;
305 FETCH getLocalNat INTO l_local_nat;
306 CLOSE getLocalNat;
307
308 OPEN getNationality;
309 FETCH getNationality INTO l_nat;
310 CLOSE getNationality;
311
312 OPEN getSIele;
313 FETCH getSIele into l_si_ele_id;
314 CLOSE getSIele;
315
316 OPEN getRRstatus(l_si_ele_id);
317 FETCH getRRstatus into l_rr_processed;
318 CLOSE getRRstatus;
319
320 /* Following OR condition added for SI element check to display the balances correctly */
321
322 If l_local_nat <> l_nat OR nvl(l_rr_processed,'*') <> 'P' then
323 /*Logic for non-UAE national */
324 l_si_needed_chr := 'N';
325 l_st_needed_chr := 'N';
326 l_sn_needed_chr := 'N';
327 l_st2_needed_chr := 'N';
328
329 for ctx in getRRContexts loop
330 i := i + 1;
331 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
332 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
333 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
334 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
335 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
336 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
337 end loop;
338 Else
339
340 for ctx in getRBContexts loop
341 i := i + 1;
342 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
343 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
344 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
345 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
346 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
347 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
348 end loop;
349
350 End If;
351
352 else
353 -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
354 l_si_needed_chr := 'N';
355 l_st_needed_chr := 'N';
356 l_sn_needed_chr := 'N';
357 l_st2_needed_chr := 'N';
358 --
359 pay_core_utils.get_leg_context_iv_name('SOURCE_ID',
360 l_legislation_code,
361 l_inp_val_name,
362 l_found);
363 if (l_found = TRUE) then
364 l_si_needed_chr := 'Y';
365 end if;
366 --
367 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT',
368 l_legislation_code,
369 l_inp_val_name,
370 l_found);
371 if (l_found = TRUE) then
372 l_st_needed_chr := 'Y';
373 end if;
374 --
375 pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER',
376 l_legislation_code,
377 l_inp_val_name,
378 l_found);
379 if (l_found = TRUE) then
380 l_sn_needed_chr := 'Y';
381 end if;
382 --
383 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT2',
384 l_legislation_code,
385 l_inp_val_name,
386 l_found);
387 if (l_found = TRUE) then
388 l_st2_needed_chr := 'Y';
389 end if;
390 --
391 --
392 for ctx in getRRContexts loop
393 i := i + 1;
394 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
398 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
395 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
396 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
397 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
399 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
400 end loop;
401 end if;
402 --
403 pay_balance_pkg.get_value (p_assignment_action_id => p_assignment_action_id
404 ,p_defined_balance_lst => l_defined_balance_lst
405 ,p_context_lst => l_context_lst
406 ,p_output_table => l_output_table);
407 --
408 pay_soe_util.clear;
409 --
410 balCount := 0;
411 if l_output_table.count > 0 then
412 for i in l_output_table.first..l_output_table.last loop
413 if l_output_table(i).balance_value <> 0 then
414 balCount := balCount + 1;
415 --
416 pay_soe_util.setValue('01'
417 ,l_balance_type_lst(l_output_table(i).defined_balance_id).balance_name
418 ,TRUE,FALSE);
419 pay_soe_util.setValue('02'
420 ,l_balance_type_lst(l_output_table(i).defined_balance_id).reporting_name
421 ,FALSE,FALSE);
422 pay_soe_util.setValue('03'
423 ,l_balance_type_lst(l_output_table(i).defined_balance_id).dimension_name
424 ,FALSE,FALSE);
425 pay_soe_util.setValue('04'
426 ,l_balance_type_lst(l_output_table(i).defined_balance_id).defined_balance_name
427 ,FALSE,FALSE);
428 pay_soe_util.setValue('05',
429 hr_general.decode_organization(to_char(l_output_table(i).tax_unit_id))
430 ,FALSE,FALSE);
431 pay_soe_util.setValue('06',to_char(l_output_table(i).tax_unit_id),FALSE,FALSE);
432 pay_soe_util.setValue('07',l_output_table(i).jurisdiction_code,FALSE,FALSE);
433 pay_soe_util.setValue('08',l_output_table(i).source_id,FALSE,FALSE);
434 pay_soe_util.setValue('09',l_output_table(i).source_text,FALSE,FALSE);
435 pay_soe_util.setValue('10',l_output_table(i).source_number,FALSE,FALSE);
436 pay_soe_util.setValue('11',l_output_table(i).source_text2,FALSE,FALSE);
437 pay_soe_util.setValue('15',l_balance_type_lst(l_output_table(i).defined_balance_id).meaning_uom,FALSE,FALSE);
438 pay_soe_util.setValue(16,to_char(l_output_table(i).balance_value,
439 fnd_currency.get_format_mask(substr(l_currency_code,2,3),40)),FALSE,FALSE);
440 pay_soe_util.setValue(17,to_char(l_output_table(i).defined_balance_id),FALSE,TRUE);
441 end if;
442 end loop;
443 end if;
444 --
445 if balCount > 0 then
446 return pay_soe_util.genCursor;
447 else
448 return ('select null COL01 from dual where 1=0');
449 --return null;
450 end if;
451 end getBalances;
452 --
453 function Balances(p_assignment_action_id number) return long is
454 begin
455 return getBalances(p_assignment_action_id
456 ,pay_soe_util.getConfig('BALANCES1'));
457 end Balances;
458 ---------------------------------------------------------------------
459 FUNCTION period (p_assignment_action_id NUMBER) RETURN LONG IS
460 --
461 l_sql Long;
462 --
463 l_action_type varchar2(2);
464 cursor periodDates is
465 select action_type from
466 pay_payroll_actions pa
467 , per_time_periods tp
468 , pay_assignment_actions aa
469 where pa.payroll_action_id = aa.payroll_action_id
470 and pa.effective_date = tp.regular_payment_date
471 and pa.payroll_id = tp.payroll_id
472 and aa.assignment_action_id = p_assignment_action_id;
473 begin
474 open periodDates;
475 fetch periodDates into l_action_type;
476 close periodDates;
477 if l_action_type is not null then
478 if l_action_type in ( 'P','U' ) then
479 l_sql :=
480 'select tp1.period_name || '' - '' || tp2.period_name COL01
481 ,fnd_date.date_to_displaydate(tp1.end_date) COL04
482 ,fnd_date.date_to_displaydate(pa2.effective_date) COL03
483 ,fnd_date.date_to_displaydate(aa1.start_date) COL05
484 ,fnd_date.date_to_displaydate(aa2.end_date) COL06
485 ,fnd_date.date_to_displaydate(tp1.start_date) COL02
486 ,tp1.period_type COL07
487 , ppf.payroll_name COL10
488 from pay_payroll_actions pa1
489 ,pay_payroll_actions pa2
490 ,per_time_periods tp1
491 ,per_time_periods tp2
492 ,pay_assignment_actions aa1
493 ,pay_assignment_actions aa2
494 ,pay_all_payrolls_f ppf
495 where pa1.payroll_action_id = aa1.payroll_action_id
496 and pa1.effective_date = tp1.regular_payment_date
497 and pa1.payroll_id = tp1.payroll_id
498 and pa1.payroll_id = ppf.payroll_id
499 and pa1.effective_date between ppf.effective_start_date and ppf.effective_end_date
500 and aa1.assignment_action_id = :PREPAY_MAX_ACTION
501 and pa2.payroll_action_id = aa2.payroll_action_id
502 and pa2.effective_date = tp2.regular_payment_date
503 and pa2.payroll_id = tp2.payroll_id
504 and aa2.assignment_action_id = :PREPAY_MIN_ACTION';
505 else
506 l_sql :=
507 'select tp.period_name COL01
508 ,fnd_date.date_to_displaydate(tp.end_date) COL04
509 ,fnd_date.date_to_displaydate(pa.effective_date) COL03
510 ,fnd_date.date_to_displaydate(aa.start_date) COL05
511 ,fnd_date.date_to_displaydate(aa.end_date) COL06
512 ,fnd_date.date_to_displaydate(tp.start_date) COL02
513 ,tp.period_type COL07
514 ,ppf.payroll_name COL10
515 from pay_payroll_actions pa
516 ,per_time_periods tp
517 ,pay_assignment_actions aa
518 , pay_all_payrolls_f ppf
519 where pa.payroll_action_id = aa.payroll_action_id
520 and pa.effective_date = tp.regular_payment_date
521 and pa.payroll_id = tp.payroll_id
522 and pa.payroll_id = ppf.payroll_id
523 and pa.effective_date between ppf.effective_start_date and ppf.effective_end_date
524 and aa.assignment_action_id = :assignment_action_id';
525 end if;
526 else
527 l_sql :=
528 'select tp.period_name COL01
529 ,fnd_date.date_to_displaydate(tp.end_date) COL04
530 ,fnd_date.date_to_displaydate(pa.effective_date) COL03
531 ,fnd_date.date_to_displaydate(aa.start_date) COL05
532 ,fnd_date.date_to_displaydate(aa.end_date) COL06
533 ,fnd_date.date_to_displaydate(tp.start_date) COL02
534 ,tp.period_type COL07
535 ,ppf.payroll_name COL10
536 from pay_payroll_actions pa
537 ,per_time_periods tp
538 ,pay_assignment_actions aa
539 ,pay_all_payrolls_f ppf
540 where pa.payroll_action_id = aa.payroll_action_id
541 and pa.payroll_id = ppf.payroll_id
542 and pa.effective_date between ppf.effective_start_date and ppf.effective_end_date
543 and pa.time_period_id = tp.time_period_id
544 and aa.assignment_action_id = :assignment_action_id';
545 end if;
546 --
547 return l_sql;
548 end Period;
549 --------------------------------------------------------------------
550 FUNCTION ae_loan_type (p_assignment_action_id NUMBER, p_run_result_id NUMBER , p_effective_date date)
551 RETURN VARCHAR2 IS
552 CURSOR csr_loan_type IS
553 SELECT hr_general.decode_lookup('AE_LOAN_TYPE',result_value)
554 FROM pay_run_results rr
555 ,pay_run_result_values rrv
556 ,pay_input_values_f iv
557 ,pay_input_values_f_tl ivt
558 ,pay_element_types_f et
559 ,pay_element_types_f_tl ettl
560 WHERE rr.element_type_id = et.element_type_id
561 AND iv.input_value_id = rrv.input_value_id
562 AND iv.name = 'Loan Type'
563 AND iv.legislation_code = 'AE'
564 AND p_effective_date between
565 iv.effective_start_date and iv.effective_end_date
566 AND iv.input_value_id = ivt.input_value_id
567 AND iv.element_type_id = et.element_type_id
568 AND p_effective_date between
569 et.effective_start_date and et.effective_end_date
570 AND et.element_type_id = ettl.element_type_id
571 AND et.element_name = 'Loan Recovery'
572 AND et.legislation_code = 'AE'
573 AND ivt.language = userenv('LANG')
574 AND ettl.language = userenv('LANG')
575 AND rr.assignment_action_id = p_assignment_action_id
576 AND rr.status in ('P','PA')
577 AND rr.run_result_id = rrv.run_result_id
581 l_loan_type := null;
578 AND rr.run_result_id = p_run_result_id;
579 l_loan_type VARCHAR2(100);
580 BEGIN
582 OPEN csr_loan_type;
583 FETCH csr_loan_type INTO l_loan_type;
584 CLOSE csr_loan_type;
585 IF l_loan_type IS NOT NULL THEN
586 l_loan_type := ' '||l_loan_type;
587 END IF;
588 RETURN l_loan_type;
589 END ae_loan_type;
590 --------------------------------------------------------------------
591 function getElements(p_assignment_action_id number
592 ,p_element_set_name varchar2) return long is
593 l_sql LONG;
594 begin
595 --
596 l_sql :=
597 'select nvl(ettl.reporting_name,et.element_type_id) COL01
598 , nvl(ettl.reporting_name,ettl.element_name) || pay_ae_soe.ae_loan_type(rr.assignment_action_id ,rr.run_result_id,:effective_date ) COL02
599 , to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
600 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator
601 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
602 from pay_assignment_actions aa
603 , pay_run_results rr
604 , pay_run_result_values rrv
605 , pay_input_values_f iv
606 , pay_input_values_f_tl ivtl
607 , pay_element_types_f et
608 , pay_element_types_f_tl ettl
609 , pay_element_set_members esm
610 , pay_element_sets es
611 where aa.assignment_action_id :action_clause
612 and aa.assignment_action_id = rr.assignment_action_id
613 and rr.status in (''P'',''PA'')
614 and rr.run_result_id = rrv.run_result_id
615 and rr.element_type_id = et.element_type_id
616 and :effective_date between
617 et.effective_start_date and et.effective_end_date
618 and et.element_type_id = ettl.element_type_id
619 and rrv.input_value_id = iv.input_value_id
620 and iv.name = ''Pay Value''
621 and :effective_date between
622 iv.effective_start_date and iv.effective_end_date
623 and iv.input_value_id = ivtl.input_value_id
624 and rrv.result_value is not null
625 and ettl.language = userenv(''LANG'')
626 and ivtl.language = userenv(''LANG'')
627 and et.element_type_id = esm.element_type_id
628 and esm.element_set_id = es.element_set_id
629 and ( es.BUSINESS_GROUP_ID IS NULL
630 OR es.BUSINESS_GROUP_ID = :business_group_id )
631 AND ( es.LEGISLATION_CODE IS NULL
632 OR es.LEGISLATION_CODE = '':legislation_code'' )
633 and es.element_set_name = '''|| p_element_set_name ||'''
634 group by nvl(ettl.reporting_name,ettl.element_name) || pay_ae_soe.ae_loan_type(rr.assignment_action_id ,rr.run_result_id,:effective_date )
640 --
635 , ettl.reporting_name
636 ,nvl(ettl.reporting_name,et.element_type_id)
637 order by nvl(ettl.reporting_name,ettl.element_name) || pay_ae_soe.ae_loan_type(rr.assignment_action_id ,rr.run_result_id,:effective_date )';
638 --
639 return l_sql;
641 end getElements;
642 --
643 ------------------------------------------------------------------------
644 function Elements2(p_assignment_action_id number) return long is
645 begin
646 return getElements(p_assignment_action_id
647 ,pay_soe_util.getConfig('ELEMENTS2'));
648 end Elements2;
649 ------------------------------------------------------------------------
650 END pay_ae_soe;