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