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