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