[Home] [Help]
PACKAGE BODY: APPS.PAY_SA_SOE
Source
1 PACKAGE BODY pay_sa_soe AS
2 /* $Header: pysasoer.pkb 120.3.12020000.2 2012/07/05 02:36:19 amnaraya ship $ */
3 /*Function to pick up Reference Salary*/
4 FUNCTIOn get_reference_salary (p_effective_date DATE
5 ,p_assignment_action_id NUMBER) RETURN NUMBER IS
6 l_defbal_id NUMBER;
7 l_balvalue NUMBER;
8 l_lower_base VARCHAR2(10);
9 l_upper_base VARCHAR2(10);
10 /* Cursor to fetch lower limit of gosi base*/
11 CURSOR get_lower_base(l_effective_date DATE) IS
12 SELECT global_value
13 FROM ff_globals_f
14 WHERE global_name = 'SA_GOSI_BASE_LOWER_LIMIT'
15 AND legislation_code = 'SA'
16 AND business_group_id IS NULL
17 AND l_effective_date BETWEEN effective_start_date
18 AND effective_end_date;
19 /* Cursor to fetch upper limit of gosi base*/
20 CURSOR get_upper_base(l_effective_date DATE) IS
21 SELECT global_value
22 FROM ff_globals_f
23 WHERE global_name = 'SA_GOSI_BASE_UPPER_LIMIT'
24 AND legislation_code = 'SA'
25 AND business_group_id IS NULL
26 AND l_effective_date BETWEEN effective_start_date
27 AND effective_end_date;
28 BEGIN
29 --
30 l_defbal_id :=
31 PAY_SA_ARCHIVE.GET_DEFINED_BALANCE_ID('GOSI_REFERENCE_EARNINGS_ASG_YTD');
32 l_balvalue := pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id);
33 OPEN get_lower_base(p_effective_date);
34 FETCH get_lower_base INTO l_lower_base;
35 CLOSE get_lower_base;
36 OPEN get_upper_base(p_effective_date);
37 FETCH get_upper_base INTO l_upper_base;
38 CLOSE get_upper_base;
39 IF(l_balvalue > to_number(l_upper_base)) THEN
40 l_balvalue := to_number(l_upper_base);
41 ELSIF(l_balvalue < to_number(l_lower_base)) THEN
42 l_balvalue := to_number(l_lower_base);
43 END IF;
44 return l_balvalue;
45 END;
46 /*Function to pick up GOSI information */
47 FUNCTION gosi_info(p_assignment_action_id NUMBER) RETURN LONG IS
48 l_sql LONG;
49 BEGIN
50 --
51 -- Mapping....
52 --
53 -- COL02 : GOSI No
54 -- COL03 : Pay annuities
55 -- COL04 : Annuities branch joining date
56 -- COL05 : Pay hazards
57 -- COL06 : Hazards branch joining date
58 -- COL07 : GOSI Reference Earnings
59 --
60
61 /* bug # 10241638 - setting the parameter calendar_aware=>2 for international calendar support */
62
63 l_sql :=
64 'SELECT scl.segment2 COL02
65 ,hr_general.decode_lookup(''YES_NO'', scl.segment3) COL03
66 ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(scl.segment4), 2)
67 COL04
68 ,hr_general.decode_lookup(''YES_NO'', scl.segment5) COL05
69 ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(scl.segment6), 2)
70 COL06 '
71 || ' ,TO_CHAR(TO_CHAR(pay_sa_soe.get_reference_salary(:effective_date,
72 :assignment_action_id)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL07
73 ' ||
74 'FROM per_all_assignments_f asg
75 ,hr_soft_coding_keyflex scl
76 WHERE asg.assignment_id = :assignment_id
77 AND :effective_date BETWEEN asg.effective_start_date
78 AND asg.effective_end_date
79 AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id';
80 --
81 RETURN l_sql;
82 --
83 END gosi_info;
84 /*Function to pick up employee details*/
85 FUNCTION employees(p_assignment_action_id NUMBER) RETURN LONG IS
86 l_sql LONG;
87 BEGIN
88 --
89 l_sql :=
90 'Select org.name COL01
91 ,job.name COL02
92 ,loc.location_code COL03
93 ,grd.name COL04
94 ,pay.payroll_name COL05
95 ,pos.name COL06
96 ,hr_general.decode_organization(:tax_unit_id) COL07
97 ,pg.group_name COL08
98 ,peo.national_identifier COL09
99 ,hl.meaning COL10
100 ,asg.assignment_number COL11
101 ,hl1.meaning ||'' ''|| peo.full_name COL12
102 from per_all_people_f peo
103 ,per_all_assignments_f asg
104 ,hr_all_organization_units_vl org
105 ,per_jobs_vl job
106 ,per_all_positions pos
107 ,hr_locations loc
108 ,per_grades_vl grd
109 ,pay_payrolls_f pay
110 ,pay_people_groups pg
111 ,hr_lookups hl
112 ,hr_lookups hl1
113 where asg.assignment_id = :assignment_id
114 and :effective_date
115 between asg.effective_start_date and asg.effective_end_date
116 and asg.person_id = peo.person_id
117 and :effective_date
118 between peo.effective_start_date and peo.effective_end_date
119 and asg.position_id = pos.position_id(+)
120 and asg.job_id = job.job_id(+)
121 and asg.location_id = loc.location_id(+)
122 and asg.grade_id = grd.grade_id(+)
123 and asg.people_group_id = pg.people_group_id(+)
124 and asg.payroll_id = pay.payroll_id(+)
125 and :effective_date
126 between pay.effective_start_date(+) and pay.effective_end_date(+)
127 and asg.organization_id = org.organization_id
128 and :effective_date
129 between org.date_from and nvl(org.date_to, :effective_date)
130 and hl.application_id (+) = 800
131 and hl.lookup_type (+) =''NATIONALITY''
132 and hl.lookup_code (+) =peo.nationality
133 and hl1.application_id (+) = 800
134 and hl1.lookup_type (+)=''TITLE''
135 and hl1.lookup_code (+)=peo.title';
136 return l_sql;
137 end employees;
138 -----------------------------------------------------------------------------
139 function getBalances(p_assignment_action_id number
140 ,p_balance_attribute varchar2) return long is
141 --
142 TYPE balance_type_lst_rec is RECORD (balance_name varchar2(80)
143 ,reporting_name varchar2(80)
144 ,dimension_name varchar2(80)
145 ,defined_balance_name varchar2(80)
146 ,defined_balance_id number
147 , meaning_uom varchar2(100));
148 TYPE balance_type_lst_tab is TABLE of balance_type_lst_rec
149 INDEX BY BINARY_INTEGER;
150 l_balance_type_lst balance_type_lst_tab;
151 --
152 l_effective_date date;
153 l_earliest_ctx_date date;
154 l_temp_date date;
155 l_action_sequence number;
156 l_payroll_id number;
157 l_assignment_id number;
158 l_business_group_id number;
159 l_legislation_code varchar2(30);
160 l_save_asg_run_bal varchar2(30);
161 l_inp_val_name pay_input_values_f.name%type;
162 l_si_needed_chr varchar2(10);
163 l_st_needed_chr varchar2(10);
164 l_sn_needed_chr varchar2(10);
165 l_st2_needed_chr varchar2(10);
166 l_found boolean;
167 l_balance_uom varchar2(40);
168 l_meaning_uom varchar2(100);
169 l_currency_code varchar2(100);
170 balCount number;
171 --
172 l_defined_balance_lst pay_balance_pkg.t_balance_value_tab;
173 l_context_lst pay_balance_pkg.t_context_tab;
174 l_output_table pay_balance_pkg.t_detailed_bal_out_tab;
175 --
176 i number;
177 l_rr_processed varchar2(1);
178 l_GOSI_ele_id number;
179 --
180 --
181 cursor getAction is
182 select pa.payroll_id
183 , aa.action_sequence
184 , pa.effective_date
185 , aa.assignment_id
186 , pa.business_group_id
187 , bg.legislation_code
188 , lrl.rule_mode
189 from pay_payroll_actions pa
190 , pay_assignment_actions aa
191 , per_business_groups bg
192 , pay_legislation_rules lrl
193 where aa.assignment_action_id = p_assignment_action_id
194 and aa.payroll_action_id = pa.payroll_action_id
195 and pa.business_group_id = bg.business_group_id
196 and lrl.legislation_code(+) = bg.legislation_code
197 and lrl.rule_type(+) = 'SAVE_ASG_RUN_BAL';
198 --
199 --
200 cursor getParameters(c_assignment_action_id in number) is
201 select '''' || bg.currency_code || '''' currency_code
202 from pay_payroll_actions pa
203 , pay_assignment_actions aa
204 , per_business_groups bg
205 where aa.assignment_action_id = p_assignment_action_id
206 and aa.payroll_action_id = pa.payroll_action_id
207 and pa.business_group_id = bg.business_group_id
208 and rownum = 1;
209 --
210 cursor getDBal is
211 select ba.defined_balance_id
212 , bd.dimension_name
213 , bd.period_type
214 , bt.balance_name
215 , bt.reporting_name
216 , nvl(oi.org_information7,nvl(bt.reporting_name,bt.balance_name))
217 defined_balance_name
218 , pbt.balance_uom
219 , hl.meaning
220 from pay_balance_attributes ba
221 , pay_bal_attribute_definitions bad
222 , pay_defined_balances db
223 , pay_balance_dimensions bd
224 , pay_balance_types_tl bt
225 , hr_organization_information oi
226 , pay_balance_types pbt
227 , hr_lookups hl
228 where bad.attribute_name = p_balance_attribute
229 and ( bad.BUSINESS_GROUP_ID IS NULL
230 OR bad.BUSINESS_GROUP_ID = l_business_group_id)
231 AND ( bad.LEGISLATION_CODE IS NULL
232 OR bad.LEGISLATION_CODE = l_legislation_code)
233 and bad.attribute_id = ba.attribute_id
234 and ba.defined_balance_id = db.defined_balance_id
235 and db.balance_dimension_id = bd.balance_dimension_id
236 and db.balance_type_id = bt.balance_type_id
237 and db.balance_type_id = pbt.balance_type_id
238 and pbt.balance_type_id = bt.balance_type_id
239 and bt.language = userenv('LANG')
240 and oi.org_information1 = 'BALANCE'
241 and oi.org_information4 = to_char(bt.balance_type_id)
242 and oi.org_information5 = to_char(db.balance_dimension_id)
243 and oi.org_information_context = 'Business Group:SOE Detail'
244 and oi.organization_id = l_business_group_id
245 and hl.lookup_type='UNITS'
246 and hl.lookup_code = pbt.balance_uom;
247 --
248 cursor getRBContexts is
249 select rb.TAX_UNIT_ID
250 , rb.JURISDICTION_CODE
251 , rb.SOURCE_ID
252 , rb.SOURCE_TEXT
253 , rb.SOURCE_NUMBER
254 , rb.SOURCE_TEXT2
255 from pay_run_balances rb
256 , pay_assignment_actions aa
257 , pay_payroll_actions pa
258 where rb.ASSIGNMENT_ID = l_assignment_id
259 and l_action_sequence >= aa.action_sequence
260 and rb.assignment_action_id = aa.assignment_action_id
261 and aa.payroll_action_id = pa.payroll_action_id
262 and pa.effective_date >= l_earliest_ctx_date;
263 --
264 cursor getRRContexts is
265 select distinct
266 aa.tax_unit_id tax_unit_id
267 , rr.jurisdiction_code jurisdiction_code
268 , decode(l_si_needed_chr,
269 'Y', pay_balance_pkg.find_context('SOURCE_ID'
270 ,rr.run_result_id)
271 ,null) source_id
272 , decode(l_st_needed_chr,
273 'Y', pay_balance_pkg.find_context('SOURCE_TEXT'
274 ,rr.run_result_id)
275 ,null) source_text
276 , decode(l_sn_needed_chr,
277 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER'
278 ,rr.run_result_id)
279 ,null) source_number
280 , decode(l_st2_needed_chr,
281 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2'
282 ,rr.run_result_id)
283 ,null) source_text2
284 from pay_assignment_actions aa,
285 pay_payroll_actions pa,
286 pay_run_results rr
287 where aa.ASSIGNMENT_ID = l_assignment_id
288 and aa.assignment_action_id = rr.assignment_action_id
289 and l_action_sequence >= aa.action_sequence
290 and aa.payroll_action_id = pa.payroll_action_id
291 and pa.effective_date >= l_earliest_ctx_date;
292 --
293 cursor getGOSIele IS
294 SELECT element_type_id
295 FROM pay_element_types_f
296 WHERE element_name = 'GOSI'
297 and legislation_code = 'SA';
298 --
299 cursor getRRstatus(l_ele_id number) IS
300 SELECT status
301 FROM pay_run_results rr
302 WHERE rr.assignment_action_id = p_assignment_action_id
303 AND rr.element_type_id = l_ele_id;
304 begin
305 l_rr_processed := null;
306 open getAction;
307 fetch getAction into l_payroll_id,
308 l_action_sequence,
309 l_effective_date,
310 l_assignment_id,
311 l_business_group_id,
312 l_legislation_code,
313 l_save_asg_run_bal;
314 close getAction;
315 --
316 l_earliest_ctx_date := l_effective_date;
317 --
318 open getParameters(p_assignment_action_id);
319 fetch getParameters into l_currency_code;
320 close getParameters;
321 --
322 i := 0;
323 for db in getDBal loop
324 i := i + 1;
325 --
326 l_defined_balance_lst(i).defined_balance_id := db.defined_balance_id;
327 --
328 l_balance_type_lst(db.defined_balance_id).balance_name :=
329 db.balance_name;
330 l_balance_type_lst(db.defined_balance_id).reporting_name :=
331 db.reporting_name;
332 l_balance_type_lst(db.defined_balance_id).defined_balance_name:=
333 db.defined_balance_name;
334 l_balance_type_lst(db.defined_balance_id).dimension_name :=
335 db.dimension_name;
336 l_balance_type_lst(db.defined_balance_id).defined_balance_id :=
337 db.defined_balance_id;
338 l_balance_type_lst(db.defined_balance_id).meaning_uom:=
339 db.meaning;
340 --
341 pay_balance_pkg.get_period_type_start
342 (p_period_type => db.period_type
343 ,p_effective_date => l_effective_date
344 ,p_payroll_id => l_payroll_id
345 ,p_start_date => l_temp_date);
346 --
347 if l_temp_date < l_earliest_ctx_date then
348 l_earliest_ctx_date := l_temp_date;
349 end if;
350 end loop;
351 --
352 i := 0;
353 if l_save_asg_run_bal = 'Y' then
354 OPEN getGOSIele;
355 FETCH getGOSIele into l_GOSI_ele_id;
356 CLOSE getGOSIele;
357 OPEN getRRstatus(l_GOSI_ele_id);
358 FETCH getRRstatus into l_rr_processed;
359 CLOSE getRRstatus;
360 /* Following OR condition added for GOSI element check to display the
361 * balances correctly */
362 If nvl(l_rr_processed,'*') <> 'P' then
363 l_si_needed_chr := 'N';
364 l_st_needed_chr := 'N';
365 l_sn_needed_chr := 'N';
366 l_st2_needed_chr := 'N';
367 for ctx in getRRContexts loop
368 i := i + 1;
369 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
370 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
371 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
372 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
373 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
374 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
375 end loop;
376 Else
377 for ctx in getRBContexts loop
378 i := i + 1;
379 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
380 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
381 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
382 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
383 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
384 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
385 end loop;
386 End If;
387 else
388 -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
389 l_si_needed_chr := 'N';
390 l_st_needed_chr := 'N';
391 l_sn_needed_chr := 'N';
392 l_st2_needed_chr := 'N';
393 --
394 pay_core_utils.get_leg_context_iv_name('SOURCE_ID',
395 l_legislation_code,
396 l_inp_val_name,
397 l_found);
398 if (l_found = TRUE) then
399 l_si_needed_chr := 'Y';
400 end if;
401 --
402 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT',
403 l_legislation_code,
404 l_inp_val_name,
405 l_found);
406 if (l_found = TRUE) then
407 l_st_needed_chr := 'Y';
408 end if;
409 --
410 pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER',
411 l_legislation_code,
412 l_inp_val_name,
413 l_found);
414 if (l_found = TRUE) then
415 l_sn_needed_chr := 'Y';
416 end if;
417 --
418 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT2',
419 l_legislation_code,
420 l_inp_val_name,
421 l_found);
422 if (l_found = TRUE) then
423 l_st2_needed_chr := 'Y';
424 end if;
425 --
426 --
427 for ctx in getRRContexts loop
428 i := i + 1;
429 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
430 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
431 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
432 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
433 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
434 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
435 end loop;
436 end if;
437 --
438 pay_balance_pkg.get_value (p_assignment_action_id => p_assignment_action_id
439 ,p_defined_balance_lst => l_defined_balance_lst
440 ,p_context_lst => l_context_lst
441 ,p_output_table => l_output_table);
442 --
443 pay_soe_util.clear;
444 --
445 balCount := 0;
446 if l_output_table.count > 0 then
447 for i in l_output_table.first..l_output_table.last loop
448 if l_output_table(i).balance_value <> 0 then
449 balCount := balCount + 1;
450 --
451 pay_soe_util.setValue('01'
452 ,l_balance_type_lst(l_output_table(i).defined_balance_id).balance_name
453 ,TRUE,FALSE);
454 pay_soe_util.setValue('02'
455 ,l_balance_type_lst(l_output_table(i).defined_balance_id).reporting_name
456 ,FALSE,FALSE);
457 pay_soe_util.setValue('03'
458 ,l_balance_type_lst(l_output_table(i).defined_balance_id).dimension_name
459 ,FALSE,FALSE);
460 pay_soe_util.setValue('04'
461 ,l_balance_type_lst(l_output_table(i).defined_balance_id).defined_balance_name
462 ,FALSE,FALSE);
463 pay_soe_util.setValue('05',
464 hr_general.decode_organization(to_char(l_output_table(i).tax_unit_id))
465 ,FALSE,FALSE);
466 pay_soe_util.setValue('06',to_char(l_output_table(i).tax_unit_id),FALSE,FALSE);
467 pay_soe_util.setValue('07',l_output_table(i).jurisdiction_code,FALSE,FALSE);
468 pay_soe_util.setValue('08',l_output_table(i).source_id,FALSE,FALSE);
469 pay_soe_util.setValue('09',l_output_table(i).source_text,FALSE,FALSE);
470 pay_soe_util.setValue('10',l_output_table(i).source_number,FALSE,FALSE);
471 pay_soe_util.setValue('11',l_output_table(i).source_text2,FALSE,FALSE);
472 pay_soe_util.setValue('15',l_balance_type_lst(l_output_table(i).defined_balance_id).meaning_uom,FALSE,FALSE);
473 pay_soe_util.setValue(16,to_char(l_output_table(i).balance_value,
474 fnd_currency.get_format_mask(substr(l_currency_code,2,3),40)),FALSE,FALSE);
475 pay_soe_util.setValue(17,to_char(l_output_table(i).defined_balance_id),FALSE,TRUE);
476 end if;
477 end loop;
478 end if;
479 --
480 if balCount > 0 then
481 return pay_soe_util.genCursor;
482 else
483 return ('select null COL01 from dual where 1=0');
484 --return null;
485 end if;
486 end getBalances;
487 --
488 ---------------------------------------------------------------------
489 function Balances(p_assignment_action_id number) return long is
490 begin
491 return getBalances(p_assignment_action_id
492 ,pay_soe_util.getConfig('BALANCES1'));
493 end Balances;
494 ---------------------------------------------------------------------
495 END pay_sa_soe;
496