[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_SOE
Source
1 PACKAGE BODY pay_dk_soe AS
2 /* $Header: pydksoe.pkb 120.0.12000000.2 2007/03/05 09:43:17 nprasath noship $ */
3 --
4 --
5 g_debug boolean := hr_utility.debug_enabled;
6 -----------------------------------------------------------------------------------------
7 -- function for fetching the Legal Entity CVR Number or Pension Provider
8
9 FUNCTION get_cvr_or_pension
10 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
11 p_effective_date IN Date,
12 p_org_information_context IN VARCHAR2 )
13 return VARCHAR2
14
15 IS
16 l_cvr_number NUMBER;
17 l_pension_provider VARCHAR2(240);
18 l_return_value VARCHAR2(240);
19
20 BEGIN
21 BEGIN
22
23 IF p_org_information_context = 'DK_LEGAL_ENTITY_DETAILS'
24 THEN
25 OPEN pay_dk_soe.csr_get_cvr_number (p_assignment_id,p_effective_date);
26 FETCH pay_dk_soe.csr_get_cvr_number INTO l_cvr_number;
27
28 IF pay_dk_soe.csr_get_cvr_number%NOTFOUND
29 THEN l_cvr_number := NULL;
30 END IF;
31
32 CLOSE pay_dk_soe.csr_get_cvr_number;
33 l_return_value := to_char(l_cvr_number);
34
35 ELSIF p_org_information_context = 'DK_PENSION_PROVIDER_DETAILS'
36 THEN
37 OPEN pay_dk_soe.csr_get_pension_provider (p_assignment_id,p_effective_date);
38 FETCH pay_dk_soe.csr_get_pension_provider INTO l_pension_provider;
39
40 IF pay_dk_soe.csr_get_pension_provider%NOTFOUND
41 THEN l_pension_provider := NULL;
42 END IF;
43
44 CLOSE pay_dk_soe.csr_get_pension_provider;
45 l_return_value := l_pension_provider;
46
47 END IF;
48
49 END;
50
51 RETURN l_return_value;
52
53 END get_cvr_or_pension;
54 -----------------------------------------------------------------------------------------
55
56 -- function for fetching the Union Membership
57
58 FUNCTION get_union_membership
59 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
60 p_effective_date IN Date )
61 return varchar2
62
63 IS
64 l_union_membership varchar2(240);
65 BEGIN
66 BEGIN
67
68 select lkp.meaning
69 into l_union_membership
70 from per_all_assignments_f asg
71 ,hr_soft_coding_keyflex scl
72 ,hr_lookups lkp
73 where lkp.lookup_type = 'DK_UNION_MEMBERSHIP'
74 and lkp.lookup_code = scl.segment5
75 and lkp.enabled_flag = 'Y'
76 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
77 and p_effective_date between asg.effective_start_date and effective_end_date
78 and asg.assignment_id = p_assignment_id;
79
80 EXCEPTION
81 WHEN NO_DATA_FOUND
82 THEN
83 l_union_membership := NULL;
84
85 END;
86
87 RETURN l_union_membership;
88
89 END get_union_membership;
90
91
92 -----------------------------------------------------------------------------------------
93
94 -- function for fetching the Bank Registration Number
95
96 FUNCTION get_bank_reg_number
97 (p_external_account_id IN NUMBER)
98 return varchar2
99
100 IS
101 l_bank_reg_number varchar2(240);
102 BEGIN
103 BEGIN
104
105 IF p_external_account_id = NULL
106
107 THEN
108 l_bank_reg_number := NULL;
109
110 ELSE
111 select pea.segment1
112 into l_bank_reg_number
113 from pay_external_accounts pea
114 where pea.external_account_id=p_external_account_id;
115
116 END IF;
117
118 EXCEPTION
119 WHEN NO_DATA_FOUND
120 THEN
121 l_bank_reg_number := NULL;
122
123 END;
124
125 RETURN l_bank_reg_number;
126
127 END get_bank_reg_number;
128
129 -----------------------------------------------------------------------------------------
130
131 /* Function : Employee
132
133 Returns SQL string for retrievening Employee information based on
134 assignment ID and effective date derived from the assignment action ID
135 passed onto the SOE module
136 ------------------------------------------------------------------------ */
137 function Employee(p_assignment_action_id in number) return long is
138 --
139 l_sql long;
140 begin
141
142 l_sql :=
143 'Select org.name COL01
144 ,job.name COL02
145 ,loc.location_code COL03
146 ,grd.name COL04
147 ,pay.payroll_name COL05
148 ,pos.name COL06
149 ,hr_general.decode_organization(:tax_unit_id) COL07
150 ,pg.group_name COL08
151 ,peo.national_identifier COL09
152 ,employee_number COL10
153 ,hl.meaning COL11
154 ,assignment_number COL12
155 ,nvl(ppb1.salary,''0'') COL13
156 ,peo.FIRST_NAME COL14
157 ,peo.MIDDLE_NAMES COL15
158 ,peo.LAST_NAME COL16
159 ,nvl(pay_dk_soe.get_cvr_or_pension(:assignment_id,:effective_date,''DK_LEGAL_ENTITY_DETAILS''), '''') COL17
160 ,nvl(pay_dk_soe.get_cvr_or_pension(:assignment_id,:effective_date,''DK_PENSION_PROVIDER_DETAILS''), '''') COL18
161 ,nvl(pay_dk_soe.get_union_membership(:assignment_id,:effective_date), '''') COL19
162 from per_all_people_f peo
163 ,per_all_assignments_f asg
164 ,hr_all_organization_units_vl org
165 ,per_jobs_vl job
166 ,per_all_positions pos
167 ,hr_locations loc
168 ,per_grades_vl grd
169 ,pay_payrolls_f pay
170 ,pay_people_groups pg
171 ,hr_lookups hl
172 ,(select ppb2.pay_basis_id
173 ,ppb2.business_group_id
174 ,ee.assignment_id
175 ,eev.screen_entry_value salary
176 from per_pay_bases ppb2
177 ,pay_element_entries_f ee
178 ,pay_element_entry_values_f eev
179 where ppb2.input_value_id = eev.input_value_id
180 and ee.element_entry_id = eev.element_entry_id
181 and :effective_date between ee.effective_start_date
182 and ee.effective_end_date
183 and :effective_date between eev.effective_start_date
184 and eev.effective_end_date
185 ) ppb1
186 where asg.assignment_id = :assignment_id
187 and :effective_date
188 between asg.effective_start_date and asg.effective_end_date
189 and asg.person_id = peo.person_id
190 and :effective_date
191 between peo.effective_start_date and peo.effective_end_date
192 and asg.position_id = pos.position_id(+)
193 and asg.job_id = job.job_id(+)
194 and asg.location_id = loc.location_id(+)
195 and asg.grade_id = grd.grade_id(+)
196 and asg.people_group_id = pg.people_group_id(+)
197 and asg.payroll_id = pay.payroll_id(+)
198 and :effective_date
199 between pay.effective_start_date(+) and pay.effective_end_date(+)
200 and asg.organization_id = org.organization_id
201 and :effective_date
202 between org.date_from and nvl(org.date_to, :effective_date)
203 and asg.pay_basis_id = ppb1.pay_basis_id(+)
204 and asg.assignment_id = ppb1.assignment_id(+)
205 and asg.business_group_id = ppb1.business_group_id(+)
206 and hl.application_id (+) = 800
207 and hl.lookup_type (+) =''NATIONALITY''
208 and hl.lookup_code (+) =peo.nationality';
209 --
210 return l_sql;
211 --
212 end Employee;
213 -----------------------------------------------------------------------------------------
214
215
216 /* Function : Period
217
218 Returns Payroll Period Information
219 ------------------------------------------------------------------------ */
220 function Period(p_assignment_action_id in number) return long is
221 --
222 l_sql long;
223 l_action_type varchar2(2);
224 cursor periodDates is
225 select action_type from
226 pay_payroll_actions pa
227 , per_time_periods tp
228 , pay_assignment_actions aa
229 where pa.payroll_action_id = aa.payroll_action_id
230 and pa.effective_date = tp.regular_payment_date
231 and pa.payroll_id = tp.payroll_id
232 and aa.assignment_action_id = p_assignment_action_id;
233
234 begin
235
236 open periodDates;
237 fetch periodDates into l_action_type;
238 close periodDates;
239
240 if l_action_type is not null then
241 if l_action_type in ( 'P','U' ) then
242 l_sql :=
243 'select tp1.period_name || '' - '' || tp2.period_name COL01
244 ,fnd_date.date_to_displaydate(tp1.end_date) COL04
245 ,fnd_date.date_to_displaydate(pa2.effective_date) COL03
246 ,fnd_date.date_to_displaydate(aa1.start_date) COL05
247 ,fnd_date.date_to_displaydate(aa2.end_date) COL06
248 ,fnd_date.date_to_displaydate(tp1.start_date) COL02
249 ,tp1.period_type COL07
250 ,fnd_date.date_to_displaydate(tp1.DEFAULT_DD_DATE) COL08
251 from pay_payroll_actions pa1
252 ,pay_payroll_actions pa2
253 ,per_time_periods tp1
254 ,per_time_periods tp2
255 ,pay_assignment_actions aa1
256 ,pay_assignment_actions aa2
257 where pa1.payroll_action_id = aa1.payroll_action_id
258 and pa1.effective_date = tp1.regular_payment_date
259 and pa1.payroll_id = tp1.payroll_id
260 and aa1.assignment_action_id = :PREPAY_MAX_ACTION
261 and pa2.payroll_action_id = aa2.payroll_action_id
262 and pa2.effective_date = tp2.regular_payment_date
263 and pa2.payroll_id = tp2.payroll_id
264 and aa2.assignment_action_id = :PREPAY_MIN_ACTION';
265 else
266 l_sql :=
267 'select tp.period_name COL01
268 ,fnd_date.date_to_displaydate(tp.end_date) COL04
269 ,fnd_date.date_to_displaydate(pa.effective_date) COL03
270 ,fnd_date.date_to_displaydate(aa.start_date) COL05
271 ,fnd_date.date_to_displaydate(aa.end_date) COL06
272 ,fnd_date.date_to_displaydate(tp.start_date) COL02
273 ,tp.period_type COL07
274 ,fnd_date.date_to_displaydate(tp.DEFAULT_DD_DATE) COL08
275 from pay_payroll_actions pa
276 ,per_time_periods tp
277 ,pay_assignment_actions aa
278 where pa.payroll_action_id = aa.payroll_action_id
279 and pa.effective_date = tp.regular_payment_date
280 and pa.payroll_id = tp.payroll_id
281 and aa.assignment_action_id = :assignment_action_id';
282 end if;
283 else
284 l_sql :=
285 'select tp.period_name COL01
286 ,fnd_date.date_to_displaydate(tp.end_date) COL04
287 ,fnd_date.date_to_displaydate(pa.effective_date) COL03
288 ,fnd_date.date_to_displaydate(aa.start_date) COL05
289 ,fnd_date.date_to_displaydate(aa.end_date) COL06
290 ,fnd_date.date_to_displaydate(tp.start_date) COL02
291 ,tp.period_type COL07
292 ,fnd_date.date_to_displaydate(tp.DEFAULT_DD_DATE) COL08
293 from pay_payroll_actions pa
294 ,per_time_periods tp
295 ,pay_assignment_actions aa
296 where pa.payroll_action_id = aa.payroll_action_id
297 and pa.time_period_id = tp.time_period_id
298 and aa.assignment_action_id = :assignment_action_id';
299 end if;
300 --
301
302 return l_sql;
303 end Period;
304 --
305
306 -----------------------------------------------------------------------------------------
307
308 /* Function : PrePayments
309
310 Returns Payment Information
311 ------------------------------------------------------------------------ */
312 function PrePayments(p_assignment_action_id number) return long is
313 --
314 l_sql long;
315 begin
316 l_sql :=
317 'select ORG_PAYMENT_METHOD_NAME COL01
318 ,pt.payment_type_name COL04
319 ,pay_soe_util.getBankDetails('':legislation_code''
320 ,ppm.external_account_id
321 ,''BANK_NAME''
322 ,null) COL02
323 ,pay_soe_util.getBankDetails('':legislation_code''
324 ,ppm.external_account_id
325 ,''BANK_ACCOUNT_NUMBER''
326 ,fnd_profile.value(''HR_MASK_CHARACTERS'')) COL03
327 ,to_char(pp.value,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
328 ,nvl(pay_dk_soe.get_bank_reg_number(ppm.external_account_id), '''') COL05
329 from pay_pre_payments pp
330 , pay_personal_payment_methods_f ppm
331 , pay_org_payment_methods_f opm
332 , pay_payment_types_tl pt
333 where pp.assignment_action_id in
334 (select ai.locking_action_id
335 from pay_action_interlocks ai
336 where ai.locked_action_id :action_clause)
337 and pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
338 and :effective_date
339 between ppm.effective_start_date(+) and ppm.effective_end_date(+)
340 and pp.org_payment_method_id = opm.org_payment_method_id
341 and :effective_date
342 between opm.effective_start_date and opm.effective_end_date
343 and opm.payment_type_id = pt.payment_type_id
344 and pt.language = userenv(''LANG'')';
345 --
346 return l_sql;
347 end PrePayments;
348 --
349
350 /* Added for Pension changes */
351
352 /* ---------------------------------------------------------------------
353 Function : get_pp_name
354
355 Returns Pension Provider Name to be appended in Pension elements' names
356 ------------------------------------------------------------------------ */
357
358
359 function get_pp_name(p_effective_date date
360 ,p_run_result_id number)
361 return varchar2 is
362
363 /* Changes made to to_number used in csr_get_pp_name */
364 cursor csr_get_pp_name(p_effective_date date ,p_run_result_id number) is
365 select hou.name
366 from
367 pay_run_result_values rrv
368 , pay_input_values_f iv
369 , hr_organization_units hou
370 where rrv.run_result_id = p_run_result_id
371 and rrv.input_value_id = iv.input_value_id
372 and iv.name = 'Third Party Payee'
373 and p_effective_date between
374 iv.effective_start_date and iv.effective_end_date
375 and hou.organization_id = FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)
376 and p_effective_date between hou.date_from and nvl(hou.date_to, p_effective_date);
377
378 rec_get_pp_name csr_get_pp_name%rowtype;
379
380 begin
381
382 open csr_get_pp_name(p_effective_date, p_run_result_id);
383 fetch csr_get_pp_name into rec_get_pp_name;
384 close csr_get_pp_name;
385
386 return rec_get_pp_name.name;
387
388 end get_pp_name;
389
390
391
392
393 /* ---------------------------------------------------------------------
394 Function : getElements
395
396 Returns Element Information
397 ------------------------------------------------------------------------ */
398 function getElements(p_assignment_action_id number
399 ,p_element_set_name varchar2)
400 return long is
401 l_sql long;
402
403 begin
404 --
405
406 l_sql :=
407 'select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
408 , nvl(ettl.reporting_name,ettl.element_name)||
409 decode( nvl(ettl.reporting_name,ettl.element_name)
410 ,''Pension'', '' ( ''||pay_dk_soe.get_pp_name(:effective_date,max(rr.run_result_id))||'' )''
411 ,''Employer Pension'', '' ( ''||pay_dk_soe.get_pp_name(:effective_date,max(rr.run_result_id))||'' )''
412 ,''Retro Pension'', '' ( ''||pay_dk_soe.get_pp_name(:effective_date,max(rr.run_result_id))||'' )''
413 ,''Retro Employer Pension'','' ( ''||pay_dk_soe.get_pp_name(:effective_date,max(rr.run_result_id))||'' )'') COL02
414 , to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
415 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator
416 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
417 from pay_assignment_actions aa
418 , pay_run_results rr
419 , pay_run_result_values rrv
420 , pay_input_values_f iv
421 , pay_input_values_f_tl ivtl
422 , pay_element_types_f et
423 , pay_element_types_f_tl ettl
424 , pay_element_set_members esm
425 , pay_element_sets es
426 where aa.assignment_action_id :action_clause
427 and aa.assignment_action_id = rr.assignment_action_id
428 and rr.status in (''P'',''PA'')
429 and rr.run_result_id = rrv.run_result_id
430 and rr.element_type_id = et.element_type_id
431 and :effective_date between
432 et.effective_start_date and et.effective_end_date
433 and et.element_type_id = ettl.element_type_id
434 and rrv.input_value_id = iv.input_value_id
435 and iv.name = ''Pay Value''
436 and :effective_date between
437 iv.effective_start_date and iv.effective_end_date
438 and iv.input_value_id = ivtl.input_value_id
439 and ettl.language = userenv(''LANG'')
440 and ivtl.language = userenv(''LANG'')
441 and et.element_type_id = esm.element_type_id
442 and esm.element_set_id = es.element_set_id
443 and ( es.BUSINESS_GROUP_ID IS NULL
444 OR es.BUSINESS_GROUP_ID = :business_group_id )
445 AND ( es.LEGISLATION_CODE IS NULL
446 OR es.LEGISLATION_CODE = '':legislation_code'' )
447 and es.element_set_name = '''|| p_element_set_name ||'''
448 group by nvl(ettl.reporting_name,ettl.element_name)
449 , ettl.reporting_name
450 ,nvl(ettl.reporting_name,et.element_type_id)
451 /*Changed for Pension provider break up */
452 , nvl(ettl.reporting_name,ettl.element_name)||
453 decode( nvl(ettl.reporting_name,ettl.element_name)
454 ,''Pension'', '' ( ''||pay_dk_soe.get_pp_name(:effective_date,rr.run_result_id)||'' )''
455 ,''Employer Pension'', '' ( ''||pay_dk_soe.get_pp_name(:effective_date,rr.run_result_id)||'' )''
456 ,''Retro Pension'', '' ( ''||pay_dk_soe.get_pp_name(:effective_date,rr.run_result_id)||'' )''
457 ,''Retro Employer Pension'','' ( ''||pay_dk_soe.get_pp_name(:effective_date,rr.run_result_id)||'' )'')
458 order by nvl(ettl.reporting_name,ettl.element_name)';
459
460
461
462 return l_sql;
463 --
464 end getElements;
465 --
466
467 /* ---------------------------------------------------------------------
468 Function : SetParameters
469 Function : Elements1
470
471 Text
472 ------------------------------------------------------------------------ */
473 function Elements1(p_assignment_action_id number) return long is
474 begin
475 return getElements(p_assignment_action_id
476 ,pay_soe_util.getConfig('ELEMENTS1'));
477 end Elements1;
478 --
479
480 /* ---------------------------------------------------------------------
481 Function : SetParameters
482 Function : Elements2
483
484 Text
485 ------------------------------------------------------------------------ */
486 function Elements2(p_assignment_action_id number) return long is
487 begin
488 return getElements(p_assignment_action_id
489 ,pay_soe_util.getConfig('ELEMENTS2'));
490 end Elements2;
491 --
492
493 /* ---------------------------------------------------------------------
494 Function : SetParameters
495 Function : Elements3
496
497 Text
498 ------------------------------------------------------------------------ */
499 function Elements3(p_assignment_action_id number) return long is
500 begin
501 return getElements(p_assignment_action_id
502 ,pay_soe_util.getConfig('ELEMENTS3'));
503 end Elements3;
504 --
505
506 /* Added for display of Pension Provider balances */
507
508 /* ---------------------------------------------------------------------
509 Function : getBalances
510
511 Text : Modified version of pay_dk_soe.getBalances with ORG_ID support
512 and customized display of values for Pension Provider balances in DK.
513 ------------------------------------------------------------------------ */
514 function getBalances(p_assignment_action_id number
515 ) return long is
516 --
517 TYPE balance_type_lst_rec is RECORD (balance_name varchar2(80)
518 ,reporting_name varchar2(80)
519 ,dimension_name varchar2(80)
520 ,defined_balance_name varchar2(80)
521 ,defined_balance_id number);
522 TYPE balance_type_lst_tab is TABLE of balance_type_lst_rec
523 INDEX BY BINARY_INTEGER;
524 l_balance_type_lst balance_type_lst_tab;
525 --
526 l_effective_date date;
527 l_earliest_ctx_date date;
528 l_temp_date date;
529 l_action_sequence number;
530 l_payroll_id number;
531 l_assignment_id number;
532 l_business_group_id number;
533 l_legislation_code varchar2(30);
534 l_save_asg_run_bal varchar2(30);
535 l_inp_val_name pay_input_values_f.name%type;
536 l_si_needed_chr varchar2(10);
537 l_st_needed_chr varchar2(10);
538 l_sn_needed_chr varchar2(10);
539 l_st2_needed_chr varchar2(10);
540 l_found boolean;
541 balCount number;
542
543 /* Added for display of Pension Provider balances */
544 l_org_needed_chr varchar2(10);
545 --
546 l_defined_balance_lst pay_balance_pkg.t_balance_value_tab;
547 l_context_lst pay_balance_pkg.t_context_tab;
548 l_output_table pay_balance_pkg.t_detailed_bal_out_tab;
549 --
550 i number;
551 /* Added for display of Pension Provider balances */
552 j number;
553 temp pay_balance_pkg.t_detailed_bal_out_tab;
554 --
555 --
556 cursor getAction is
557 select pa.payroll_id
558 , aa.action_sequence
559 , pa.effective_date
560 , aa.assignment_id
561 , pa.business_group_id
562 , bg.legislation_code
563 , lrl.rule_mode
564 from pay_payroll_actions pa
565 , pay_assignment_actions aa
566 , per_business_groups bg
567 , pay_legislation_rules lrl
568 where aa.assignment_action_id = p_assignment_action_id
569 and aa.payroll_action_id = pa.payroll_action_id
570 and pa.business_group_id = bg.business_group_id
571 and lrl.legislation_code(+) = bg.legislation_code
572 and lrl.rule_type(+) = 'SAVE_ASG_RUN_BAL';
573 --
574
575 /* Modified for display of Pension Provider balances */
576 cursor getDBal is
577 select db.defined_balance_id
578 , bd.dimension_name
579 , bd.period_type
580 , bt.balance_name
581 , bt.reporting_name
582 , nvl(bt.reporting_name,bt.balance_name) defined_balance_name
583 from
584 pay_defined_balances db
585 , pay_balance_dimensions bd
586 , pay_balance_types_tl bt
587 where db.balance_dimension_id = bd.balance_dimension_id
588 and db.balance_type_id = bt.balance_type_id
589 and bt.language = userenv('LANG')
590 and bd.legislation_code ='DK'
591 and bd.database_item_suffix IN ('_PP_ASG_PTD','_PP_ASG_YTD','_PP_PAYMENTS');
592 --
593 cursor getRBContexts is
594 select rb.TAX_UNIT_ID
595 , rb.JURISDICTION_CODE
596 , rb.SOURCE_ID
597 , rb.SOURCE_TEXT
598 , rb.SOURCE_NUMBER
599 , rb.SOURCE_TEXT2
600 from pay_run_balances rb
601 , pay_assignment_actions aa
602 , pay_payroll_actions pa
603 where rb.ASSIGNMENT_ID = l_assignment_id
604 and l_action_sequence >= aa.action_sequence
605 and rb.assignment_action_id = aa.assignment_action_id
606 and aa.payroll_action_id = pa.payroll_action_id
607 and pa.effective_date >= l_earliest_ctx_date;
608 --
609 cursor getRRContexts is
610 select distinct
611 aa.tax_unit_id tax_unit_id
612 , rr.jurisdiction_code jurisdiction_code
613 , decode(l_si_needed_chr,
614 'Y', pay_balance_pkg.find_context('SOURCE_ID'
615 ,rr.run_result_id)
616 ,null) source_id
617 , decode(l_st_needed_chr,
618 'Y', pay_balance_pkg.find_context('SOURCE_TEXT'
619 ,rr.run_result_id)
620 ,null) source_text
621 , decode(l_sn_needed_chr,
622 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER'
623 ,rr.run_result_id)
624 ,null) source_number
625 , decode(l_st2_needed_chr,
626 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2'
627 ,rr.run_result_id)
628 ,null) source_text2
629 /* Added for display of Pension Provider balances */
630 , decode(l_org_needed_chr,
631 'Y', pay_balance_pkg.find_context('ORGANIZATION_ID'
632 ,rr.run_result_id)
633 ,null) organization_id
634 from pay_assignment_actions aa,
635 pay_payroll_actions pa,
636 pay_run_results rr
637 where aa.ASSIGNMENT_ID = l_assignment_id
638 and aa.assignment_action_id = rr.assignment_action_id
639 and l_action_sequence >= aa.action_sequence
640 and aa.payroll_action_id = pa.payroll_action_id
641 and pa.effective_date >= l_earliest_ctx_date;
642
643 /* Added for display of Pension Provider balances */
644
645 CURSOR csr_get_org_name( p_org_id number) IS
646 SELECT name
647 FROM hr_organization_units
648 WHERE organization_id = p_org_id ;
649
650 l_org_name VARCHAR(80);
651
652 CURSOR csr_get_params( p_assignment_action_id NUMBER) IS
653 SELECT '''' || bg.currency_code || '''' currency_code
654 FROM pay_payroll_actions pa
655 , pay_assignment_actions aa
656 , per_business_groups bg
657 WHERE aa.assignment_action_id = p_assignment_action_id
658 AND aa.payroll_action_id = pa.payroll_action_id
659 AND pa.business_group_id = bg.business_group_id
660 AND rownum = 1;
661
662 rec_get_params csr_get_params%ROWTYPE;
663
664
665 --
666 begin
667 --
668
669 if g_debug then
670 hr_utility.set_location('Entering pay_dk_soe.getBalances', 10);
671 end if;
672 --
673 open getAction;
674 fetch getAction into l_payroll_id,
675 l_action_sequence,
676 l_effective_date,
677 l_assignment_id,
678 l_business_group_id,
679 l_legislation_code,
680 l_save_asg_run_bal;
681 close getAction;
682 --
683 l_earliest_ctx_date := l_effective_date;
684 --
685 i := 0;
686 --
687 if g_debug then
688 hr_utility.set_location('pay_dk_soe.getBalances', 20);
689 end if;
690 --
691 for db in getDBal loop
692 i := i + 1;
693 --
694 l_defined_balance_lst(i).defined_balance_id := db.defined_balance_id;
695 --
696 l_balance_type_lst(db.defined_balance_id).balance_name :=
697 db.balance_name;
698 l_balance_type_lst(db.defined_balance_id).reporting_name :=
699 db.reporting_name;
700 l_balance_type_lst(db.defined_balance_id).defined_balance_name:=
701 db.defined_balance_name;
702 l_balance_type_lst(db.defined_balance_id).dimension_name :=
703 db.dimension_name;
704 l_balance_type_lst(db.defined_balance_id).defined_balance_id :=
705 db.defined_balance_id;
706 --
707 pay_balance_pkg.get_period_type_start
708 (p_period_type => db.period_type
709 ,p_effective_date => l_effective_date
710 ,p_payroll_id => l_payroll_id
711 ,p_start_date => l_temp_date);
712 --
713 if l_temp_date < l_earliest_ctx_date then
714 l_earliest_ctx_date := l_temp_date;
715 end if;
716 end loop;
717 --
718 i := 0;
719 if l_save_asg_run_bal = 'Y' then
720 if g_debug then
721 hr_utility.set_location('pay_dk_soe.getBalances', 30);
722 end if;
723 for ctx in getRBContexts loop
724 i := i + 1;
725 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
726 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
727 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
728 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
729 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
730 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
731 end loop;
732 else
733 if g_debug then
734 hr_utility.set_location('pay_dk_soe.getBalances', 40);
735 end if;
736 -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
737 l_si_needed_chr := 'N';
738 l_st_needed_chr := 'N';
739 l_sn_needed_chr := 'N';
740 l_st2_needed_chr := 'N';
741 /* Added for display of Pension Provider balances */
742 l_org_needed_chr :='N';
743 --
744 pay_core_utils.get_leg_context_iv_name('SOURCE_ID',
745 l_legislation_code,
746 l_inp_val_name,
747 l_found);
748 if (l_found = TRUE) then
749 l_si_needed_chr := 'Y';
750 end if;
751 --
752 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT',
753 l_legislation_code,
754 l_inp_val_name,
755 l_found);
756 if (l_found = TRUE) then
757 l_st_needed_chr := 'Y';
758 end if;
759 --
760 pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER',
761 l_legislation_code,
762 l_inp_val_name,
763 l_found);
764 if (l_found = TRUE) then
765 l_sn_needed_chr := 'Y';
766 end if;
767 --
768 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT2',
769 l_legislation_code,
770 l_inp_val_name,
771 l_found);
772 if (l_found = TRUE) then
773 l_st2_needed_chr := 'Y';
774 end if;
775 --
776 /* Added for display of Pension Provider balances */
777 pay_core_utils.get_leg_context_iv_name('ORGANIZATION_ID',
778 l_legislation_code,
779 l_inp_val_name,
780 l_found);
781 if (l_found = TRUE) then
782 l_org_needed_chr := 'Y';
783 end if;
784 --
785 --
786 for ctx in getRRContexts loop
787 i := i + 1;
788 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
789 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
790 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
791 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
792 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
793 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
794 /* Added for display of Pension Provider balances */
795 l_context_lst(i).ORGANIZATION_ID := ctx.ORGANIZATION_ID;
796 end loop;
797 end if;
798 --
799 --
800 if g_debug then
801 hr_utility.set_location('pay_dk_soe.getBalances', 50);
802 end if;
803 --
804 pay_balance_pkg.get_value (p_assignment_action_id => p_assignment_action_id
805 ,p_defined_balance_lst => l_defined_balance_lst
806 ,p_context_lst => l_context_lst
807 ,p_output_table => l_output_table);
808 --
809 pay_soe_util.clear;
810 --
811 balCount := 0;
812 if l_output_table.count > 0 then
813 --
814 if g_debug then
815 hr_utility.set_location('pay_dk_soe.getBalances', 60);
816 end if;
817 --
818
819 /* Sort the data in PL/SQL table according to organization_id */
820 for i in l_output_table.first..l_output_table.last loop
821 for j in l_output_table.first..l_output_table.last-i loop
822 if(l_output_table(j).organization_id > l_output_table(j+1).organization_id) then
823 temp(i) := l_output_table(j);
824 l_output_table(j) := l_output_table(j+1);
825 l_output_table(j+1) := temp(i);
826 end if;
827 end loop;
828 end loop;
829
830 for i in l_output_table.first..l_output_table.last loop
831 if l_output_table(i).balance_value <> 0 then
832 balCount := balCount + 1;
833
834 /* Added for display of Pension Provider balances */
835 OPEN csr_get_org_name(l_output_table(i).organization_id);
836 FETCH csr_get_org_name INTO l_org_name;
837 CLOSE csr_get_org_name;
838
839 OPEN csr_get_params(p_assignment_action_id);
840 FETCH csr_get_params INTO rec_get_params;
841 CLOSE csr_get_params;
842
843 --
844 pay_soe_util.setValue('01'
845 ,l_balance_type_lst(l_output_table(i).defined_balance_id).balance_name
846 ,TRUE,FALSE);
847 pay_soe_util.setValue('02'
848 ,l_balance_type_lst(l_output_table(i).defined_balance_id).reporting_name
849 ,FALSE,FALSE);
850 pay_soe_util.setValue('03'
851 ,l_balance_type_lst(l_output_table(i).defined_balance_id).dimension_name
852 ,FALSE,FALSE);
853 pay_soe_util.setValue('04'
854 ,l_balance_type_lst(l_output_table(i).defined_balance_id).defined_balance_name
855 ,FALSE,FALSE);
856
857 pay_soe_util.setValue('05',
858 hr_general.decode_organization(to_char(l_output_table(i).tax_unit_id))
859 ,FALSE,FALSE);
860 pay_soe_util.setValue('06',to_char(l_output_table(i).tax_unit_id),FALSE,FALSE);
861
862
863 pay_soe_util.setValue('07',l_output_table(i).jurisdiction_code,FALSE,FALSE);
864 pay_soe_util.setValue('08',l_output_table(i).source_id,FALSE,FALSE);
865 pay_soe_util.setValue('09',l_output_table(i).source_text,FALSE,FALSE);
866 pay_soe_util.setValue('10',l_output_table(i).source_number,FALSE,FALSE);
867 pay_soe_util.setValue('11',l_output_table(i).source_text2,FALSE,FALSE);
868
869 /* Added for display of Pension Provider balances */
870 pay_soe_util.setValue('12',l_output_table(i).organization_id,FALSE,FALSE);
871 pay_soe_util.setValue('13',l_org_name,FALSE,FALSE);
872
873 pay_soe_util.setValue(16,to_char(l_output_table(i).balance_value,
874 fnd_currency.get_format_mask(substr(rec_get_params.currency_code,2,3),40)),FALSE,FALSE);
875 pay_soe_util.setValue(17,to_char(l_output_table(i).defined_balance_id),FALSE,TRUE);
876
877 end if;
878 end loop;
879 end if;
880 --
881 if balCount > 0 then
882 return pay_soe_util.genCursor;
883 else
884 return ('select null COL01 from dual where 1=0');
885 --return null;
886 end if;
887 end getBalances;
888 --
889
890
891 --
892 -- End of the Package
893
894 END pay_dk_soe;