[Home] [Help]
PACKAGE BODY: APPS.PAY_AE_SOE
Source
1 PACKAGE BODY pay_ae_soe AS
5
2 /* $Header: pyaesoer.pkb 120.8 2012/01/19 08:38:17 rpahune ship $ */
3
4 /*Function to pick up employee details*/
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
143 , nvl(oi.org_information7,nvl(bt.reporting_name,bt.balance_name)) defined_balance_name
140 , bd.period_type
141 , bt.balance_name
142 , bt.reporting_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
168 and oi.org_information5 = to_char(db.balance_dimension_id)
165 and bt.language = userenv('LANG')
166 and oi.org_information1 = 'BALANCE'
167 and oi.org_information4 = to_char(bt.balance_type_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
248
245 AND rr.element_type_id = l_ele_id;
246 --
247 begin
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
332 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
329 for ctx in getRRContexts loop
330 i := i + 1;
331 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
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;
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;
398 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
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;
446 return pay_soe_util.genCursor;
443 end if;
444 --
445 if balCount > 0 then
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.date_earned between tp.start_date and tp.end_date /* 9178309 */
472 and pa.payroll_id = tp.payroll_id
473 and aa.assignment_action_id = p_assignment_action_id;
474 begin
475 open periodDates;
476 fetch periodDates into l_action_type;
477 close periodDates;
478 if l_action_type is not null then
479 if l_action_type in ( 'P','U' ) then
480 l_sql :=
481 'select tp1.period_name || '' - '' || tp2.period_name COL01
482 ,fnd_date.date_to_displaydate(tp1.end_date) COL04
483 ,fnd_date.date_to_displaydate(pa2.effective_date) COL03
484 ,fnd_date.date_to_displaydate(aa1.start_date) COL05
485 ,fnd_date.date_to_displaydate(aa2.end_date) COL06
486 ,fnd_date.date_to_displaydate(tp1.start_date) COL02
487 ,tp1.period_type COL07
488 , ppf.payroll_name COL10
489 from pay_payroll_actions pa1
490 ,pay_payroll_actions pa2
491 ,per_time_periods tp1
492 ,per_time_periods tp2
493 ,pay_assignment_actions aa1
494 ,pay_assignment_actions aa2
495 ,pay_all_payrolls_f ppf
496 where pa1.payroll_action_id = aa1.payroll_action_id
497 --and pa1.effective_date = tp1.regular_payment_date
498 and pa1.payroll_id = tp1.payroll_id
499 and pa1.date_earned between tp1.start_date and tp1.end_date /* 9178309 */
500 and pa1.payroll_id = ppf.payroll_id
501 and pa1.effective_date between ppf.effective_start_date and ppf.effective_end_date
502 and aa1.assignment_action_id = :PREPAY_MAX_ACTION
503 and pa2.payroll_action_id = aa2.payroll_action_id
504 --and pa2.effective_date = tp2.regular_payment_date
505 and pa2.payroll_id = tp2.payroll_id
506 and pa2.date_earned between tp2.start_date and tp2.end_date /* 9178309 */
507 and aa2.assignment_action_id = :PREPAY_MIN_ACTION';
508 else
509 l_sql :=
510 'select tp.period_name COL01
511 ,fnd_date.date_to_displaydate(tp.end_date) COL04
512 ,fnd_date.date_to_displaydate(pa.effective_date) COL03
513 ,fnd_date.date_to_displaydate(aa.start_date) COL05
514 ,fnd_date.date_to_displaydate(aa.end_date) COL06
515 ,fnd_date.date_to_displaydate(tp.start_date) COL02
516 ,tp.period_type COL07
517 ,ppf.payroll_name COL10
518 from pay_payroll_actions pa
519 ,per_time_periods tp
520 ,pay_assignment_actions aa
521 , pay_all_payrolls_f ppf
522 where pa.payroll_action_id = aa.payroll_action_id
523 --and pa.effective_date = tp.regular_payment_date
524 and pa.payroll_id = tp.payroll_id
525 and pa.date_earned between tp.start_date and tp.end_date /* 9178309 */
526 and pa.payroll_id = ppf.payroll_id
527 and pa.effective_date between ppf.effective_start_date and ppf.effective_end_date
528 and aa.assignment_action_id = :assignment_action_id';
529 end if;
530 else
531 l_sql :=
532 'select tp.period_name COL01
533 ,fnd_date.date_to_displaydate(tp.end_date) COL04
534 ,fnd_date.date_to_displaydate(pa.effective_date) COL03
535 ,fnd_date.date_to_displaydate(aa.start_date) COL05
536 ,fnd_date.date_to_displaydate(aa.end_date) COL06
537 ,fnd_date.date_to_displaydate(tp.start_date) COL02
538 ,tp.period_type COL07
539 ,ppf.payroll_name COL10
540 from pay_payroll_actions pa
541 ,per_time_periods tp
542 ,pay_assignment_actions aa
543 ,pay_all_payrolls_f ppf
544 where pa.payroll_action_id = aa.payroll_action_id
545 and pa.payroll_id = ppf.payroll_id
546 and pa.effective_date between ppf.effective_start_date and ppf.effective_end_date
547 --and pa.time_period_id = tp.time_period_id
548 and pa.payroll_id = tp.payroll_id /* 9178309 */
549 and pa.date_earned between tp.start_date and tp.end_date /* 9178309 */
550 and aa.assignment_action_id = :assignment_action_id';
551 end if;
552 --
553 return l_sql;
554 end Period;
555 --------------------------------------------------------------------
556 FUNCTION ae_loan_type (p_assignment_action_id NUMBER, p_run_result_id NUMBER , p_effective_date date)
557 RETURN VARCHAR2 IS
558 CURSOR csr_loan_type IS
559 SELECT hr_general.decode_lookup('AE_LOAN_TYPE',result_value)
560 FROM pay_run_results rr
561 ,pay_run_result_values rrv
562 ,pay_input_values_f iv
563 ,pay_input_values_f_tl ivt
564 ,pay_element_types_f et
565 ,pay_element_types_f_tl ettl
566 WHERE rr.element_type_id = et.element_type_id
567 AND iv.input_value_id = rrv.input_value_id
568 AND iv.name = 'Loan Type'
569 AND iv.legislation_code = 'AE'
570 AND p_effective_date between
574 AND p_effective_date between
571 iv.effective_start_date and iv.effective_end_date
572 AND iv.input_value_id = ivt.input_value_id
573 AND iv.element_type_id = et.element_type_id
575 et.effective_start_date and et.effective_end_date
576 AND et.element_type_id = ettl.element_type_id
577 AND et.element_name = 'Loan Recovery'
578 AND et.legislation_code = 'AE'
579 AND ivt.language = userenv('LANG')
580 AND ettl.language = userenv('LANG')
581 AND rr.assignment_action_id = p_assignment_action_id
582 AND rr.status in ('P','PA')
583 AND rr.run_result_id = rrv.run_result_id
584 AND rr.run_result_id = p_run_result_id;
585 l_loan_type VARCHAR2(100);
586 BEGIN
587 l_loan_type := null;
588 OPEN csr_loan_type;
589 FETCH csr_loan_type INTO l_loan_type;
590 CLOSE csr_loan_type;
591 IF l_loan_type IS NOT NULL THEN
592 l_loan_type := ' '||l_loan_type;
593 END IF;
594 RETURN l_loan_type;
595 END ae_loan_type;
596 --------------------------------------------------------------------
597 function getElements(p_assignment_action_id number
598 ,p_element_set_name varchar2) return long is
599 l_sql LONG;
600 begin
601 --
602 l_sql :=
603 'select nvl(ettl.reporting_name,et.element_type_id) COL01
604 , nvl(ettl.reporting_name,ettl.element_name) || pay_ae_soe.ae_loan_type(rr.assignment_action_id ,rr.run_result_id,:effective_date ) COL02
605 , to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
606 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator
607 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
608 from pay_assignment_actions aa
609 , pay_run_results rr
610 , pay_run_result_values rrv
611 , pay_input_values_f iv
612 , pay_input_values_f_tl ivtl
613 , pay_element_types_f et
614 , pay_element_types_f_tl ettl
615 --, pay_element_set_members esm
616 --, pay_element_sets es
617 where aa.assignment_action_id :action_clause
618 and aa.assignment_action_id = rr.assignment_action_id
619 and rr.status in (''P'',''PA'')
620 and rr.run_result_id = rrv.run_result_id
621 and rr.element_type_id = et.element_type_id
622 and :effective_date between
623 et.effective_start_date and et.effective_end_date
624 and et.element_type_id = ettl.element_type_id
625 and rrv.input_value_id = iv.input_value_id
626 and iv.name = ''Pay Value''
627 and :effective_date between
628 iv.effective_start_date and iv.effective_end_date
629 and iv.input_value_id = ivtl.input_value_id
630 and rrv.result_value is not null
631 and ettl.language = userenv(''LANG'')
632 and ivtl.language = userenv(''LANG'')
633 and et.element_type_id in (select esm.element_type_id from pay_element_sets es, pay_element_set_members esm
634 where es.element_set_name = '''|| p_element_set_name ||''' and es.element_set_id = esm.element_set_id
635 and ( es.BUSINESS_GROUP_ID IS NULL OR es.BUSINESS_GROUP_ID = :business_group_id )
636 and ( es.LEGISLATION_CODE IS NULL OR es.LEGISLATION_CODE = '':legislation_code'' ))
637 --and et.element_type_id = esm.element_type_id
638 --and esm.element_set_id = es.element_set_id
639 --and ( es.BUSINESS_GROUP_ID IS NULL
640 -- OR es.BUSINESS_GROUP_ID = :business_group_id )
641 --AND ( es.LEGISLATION_CODE IS NULL
642 -- OR es.LEGISLATION_CODE = '':legislation_code'' )
643 --and es.element_set_name = '''|| p_element_set_name ||'''
644 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 )
645 , ettl.reporting_name
646 ,nvl(ettl.reporting_name,et.element_type_id)
647 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 )';
648 --
649 return l_sql;
650 --
651 end getElements;
652 --
653 ------------------------------------------------------------------------
654 function Elements2(p_assignment_action_id number) return long is
655 begin
656 return getElements(p_assignment_action_id
657 ,pay_soe_util.getConfig('ELEMENTS2'));
658 end Elements2;
659 ------------------------------------------------------------------------
660 END pay_ae_soe;