[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_SOE
Source
1 package body PAY_NL_SOE as
2 /* $Header: pynlsoer.pkb 120.3.12000000.2 2007/02/28 10:27:01 shmittal noship $ */
3 g_package varchar2(33) := ' PAY_NL_SOE.';
4 l_sql long;
5 g_debug boolean := hr_utility.debug_enabled;
6 g_max_action number;
7 g_min_action number;
8
9
10 /* ---------------------------------------------------------------------
11 Function : Get_Spl_Tax_Ind
12
13 Text : Returns the Concatenated Special Indicators Value
14 ------------------------------------------------------------------------ */
15 function Get_Spl_Tax_Ind(p_spl_ind VARCHAR2) return varchar2 IS
16 l_Special_Indicator VARCHAR2(32000);
17 l_SPL_IND1 varchar2(2);
18 l_SPL_IND2 varchar2(2);
19 l_SPL_IND3 varchar2(2);
20 l_SPL_IND4 varchar2(2);
21 l_SPL_IND5 varchar2(2);
22 l_SPL_IND6 varchar2(2);
23 l_SPL_IND7 varchar2(2);
24 l_SPL_IND8 varchar2(2);
25 l_SPL_IND9 varchar2(2);
26 l_SPL_IND10 varchar2(2);
27 l_SPL_IND11 varchar2(2);
28 l_SPL_IND12 varchar2(2);
29 l_SPL_IND13 varchar2(2);
30
31
32 BEGIN
33 --Fetch Split Special Indicators
34 pay_nl_tax_pkg.get_spl_inds (P_SPL_IND =>p_spl_ind
35 ,P_SPL_IND1=>l_SPL_IND1
36 ,P_SPL_IND2=>l_SPL_IND2
37 ,P_SPL_IND3=>l_SPL_IND3
38 ,P_SPL_IND4=>l_SPL_IND4
39 ,P_SPL_IND5=>l_SPL_IND5
40 ,P_SPL_IND6=>l_SPL_IND6
41 ,P_SPL_IND7=>l_SPL_IND7
42 ,P_SPL_IND8=>l_SPL_IND8
43 ,P_SPL_IND9=>l_SPL_IND9
44 ,P_SPL_IND10=>l_SPL_IND10
45 ,P_SPL_IND11=>l_SPL_IND11
46 ,P_SPL_IND12=>l_SPL_IND12
47 ,P_SPL_IND13=>l_SPL_IND13);
48 --Conctenate the Individual Special Indicators concatenate to l_Special_Indicator
49 IF l_SPL_IND1 IS NOT NULL THEN
50 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND1;
51 END IF;
52 IF l_SPL_IND2 IS NOT NULL THEN
53 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND2;
54 END IF;
55 IF l_SPL_IND3 IS NOT NULL THEN
56 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND3;
57 END IF;
58 IF l_SPL_IND4 IS NOT NULL THEN
59 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND4;
60 END IF;
61 IF l_SPL_IND5 IS NOT NULL THEN
62 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND5;
63 END IF;
64 IF l_SPL_IND6 IS NOT NULL THEN
65 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND6;
66 END IF;
67 IF l_SPL_IND7 IS NOT NULL THEN
68 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND7;
69 END IF;
70 IF l_SPL_IND8 IS NOT NULL THEN
71 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND8;
72 END IF;
73 IF l_SPL_IND9 IS NOT NULL THEN
74 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND9;
75 END IF;
76 IF l_SPL_IND10 IS NOT NULL THEN
77 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND10;
78 END IF;
79 IF l_SPL_IND11 IS NOT NULL THEN
80 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND11;
81 END IF;
82 IF l_SPL_IND12 IS NOT NULL THEN
83 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND12;
84 END IF;
85 IF l_SPL_IND13 IS NOT NULL THEN
86 l_Special_Indicator := l_Special_Indicator||' '||l_SPL_IND13;
87 END IF;
88 return l_Special_Indicator;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 return l_Special_Indicator;
93 END;
94
95
96 /* ---------------------------------------------------------------------
97 Function : Tax_Info
98
99 Text : Fetches Tax Information
100 ------------------------------------------------------------------------ */
101 FUNCTION Tax_Info(p_assignment_action_id NUMBER) RETURN LONG IS
102 l_sql LONG;
103 l_defbal_id NUMBER;
104 l_Travel_Allowance NUMBER;
105 l_Sea_Days_Discount NUMBER;
106 l_ABW_Allowance NUMBER;
107 l_ABW_Allowance_Stoppage NUMBER;
108 l_WAO_Allowance NUMBER;
109 l_ZVW_Contribution NUMBER;
110 l_sp_percentage_rate pay_run_result_values.result_value%TYPE;
111 l_prev_tax_income pay_run_result_values.result_value%TYPE;
112 l_Tax_Code pay_run_result_values.result_value%TYPE;
113 l_Tax_Table varchar2(5000);
114 l_Tax_Reduction varchar2(5000);
115 l_Labour_Tax_Reduction varchar2(5000);
116 l_Special_Indicators pay_run_result_values.result_value%TYPE;
117 l_Senior_Tax_Reduction varchar2(5000);
118 l_locked_assignment_action_id pay_action_interlocks.locked_action_id%TYPE;
119 l_context_id NUMBER;
120
121 CURSOR cur_spl_rate(lp_assignment_action_id NUMBER,
122 lp_element_name VARCHAR2,lp_input_value_name VARCHAR2) IS
123 select prrv.result_value
124 from pay_run_result_values prrv,
125 pay_run_results prr,
126 pay_element_types_f pet,
127 pay_input_values_f piv,
128 pay_assignment_actions paa,
129 pay_payroll_actions ppa
130 where prrv.run_result_id = prr.run_result_id
131 and paa.assignment_action_id=lp_assignment_action_id
132 and prr.assignment_action_id = paa.assignment_action_id
133 and paa.payroll_action_id= ppa.payroll_action_id
134 and pet.element_type_id = piv.element_type_id
135 and pet.element_name=lp_element_name
136 and piv.name =lp_input_value_name
137 and piv.input_value_id = prrv.input_value_id
138 and pet.legislation_code = 'NL'
139 and piv.legislation_code = 'NL'
140 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
141 and ppa.date_earned between piv.effective_start_date and piv.effective_end_date;
142
143 cursor getLockedActionid is
144 select max(locked_action_id) from pay_action_interlocks where locking_action_id = p_assignment_action_id;
145
146 cursor get_context_id is
147 select context_id from ff_contexts where context_name = 'SOURCE_TEXT';
148
149 BEGIN
150 --
151 --
152 -- Mapping....
153 --
154 -- COL16 : Special Tax Rate Percentage
155 -- COL17 : Previous Year Taxable Income
156 -- COL01 : Tax Code
157 -- COL02 : Tax Table Colour
158 -- COL03 : Tax Reduction
159 -- COL07 : Labour Tax Reduction
160 -- COL05 : Additional Senior Tax Reduction
161 -- COL06 : Special Tax Indicators
162 -- COL18 : Travel Allowance
163 -- COL19 : Sea Days Discount
164 -- COL20 : ABW Allowance
165 -- COL21 : ABW Allowance Tax Stoppage
166 -- COL22 : WAO Allowance
167 -- COL23 : ZVW Contribution
168
169 --Fetch locked assignment action id for prepayments
170 open getLockedActionid;
171 fetch getLockedActionid into l_locked_assignment_action_id;
172 close getLockedActionid;
173 if l_locked_assignment_action_id is null then
174 l_locked_assignment_action_id := p_assignment_action_id;
175 end if;
176 --Fetch Special Tax Rate Percentage
177 OPEN cur_spl_rate(l_locked_assignment_action_id,'Special Tax Deduction','Percentage Rate') ;
178 FETCH cur_spl_rate INTO l_sp_percentage_rate;
179 CLOSE cur_spl_rate;
180
181 --Fetch Previous Year Taxable Income
182 OPEN cur_spl_rate(l_locked_assignment_action_id,'Special Tax Deduction','Previous Year Taxable Income');
183 FETCH cur_spl_rate INTO l_prev_tax_income;
184 CLOSE cur_spl_rate;
185
186 --Fetch Tax code
187 OPEN cur_spl_rate(l_locked_assignment_action_id,'Standard Tax Deduction','Tax Code');
188 FETCH cur_spl_rate INTO l_Tax_Code;
189 CLOSE cur_spl_rate;
190
191 --Fetch Tax Table Colour
192 OPEN cur_spl_rate(l_locked_assignment_action_id,'Standard Tax Deduction','Tax Table');
193 FETCH cur_spl_rate INTO l_Tax_Table;
194 CLOSE cur_spl_rate;
195 l_Tax_Table := hr_general.decode_lookup('NL_TAX_TABLE',l_Tax_Table);
196 l_Tax_Table := replace(l_Tax_table,'''','||fnd_global.local_chr(39)');
197
198 --Fetch Tax Reduction Flag
199 OPEN cur_spl_rate(l_locked_assignment_action_id,'Standard Tax Deduction','Tax Reduction Flag');
200 FETCH cur_spl_rate INTO l_Tax_Reduction;
201 CLOSE cur_spl_rate;
202 l_Tax_Reduction := hr_general.decode_lookup('HR_NL_REPORT_LABELS',l_Tax_Reduction);
203 l_Tax_Reduction := replace(l_Tax_Reduction,'''','||fnd_global.local_chr(39)');
204
205 --Fetch Labour Tax Reduction Flag
206 OPEN cur_spl_rate(l_locked_assignment_action_id,'Standard Tax Deduction','Labour Tax Reduction Flag');
207 FETCH cur_spl_rate INTO l_Labour_Tax_Reduction;
208 CLOSE cur_spl_rate;
209 l_Labour_Tax_Reduction := hr_general.decode_lookup('HR_NL_YES_NO',l_Labour_Tax_Reduction);
210 l_Labour_Tax_Reduction := replace(l_Labour_Tax_Reduction,'''','||fnd_global.local_chr(39)');
211
212 --Fetch Additional Senior Tax Reduction Flag
213 OPEN cur_spl_rate(l_locked_assignment_action_id,'Standard Tax Deduction','Additional Senior Tax Flag');
214 FETCH cur_spl_rate INTO l_Senior_Tax_Reduction;
215 CLOSE cur_spl_rate;
216 l_Senior_Tax_Reduction := hr_general.decode_lookup('HR_NL_YES_NO',l_Senior_Tax_Reduction);
217 l_Senior_Tax_Reduction := replace(l_Senior_Tax_Reduction,'''','||fnd_global.local_chr(39)');
218
219 --Fetch Special Tax Indicators
220 OPEN cur_spl_rate(l_locked_assignment_action_id,'Standard Tax Deduction','Special Indicators');
221 FETCH cur_spl_rate INTO l_Special_Indicators;
222 CLOSE cur_spl_rate;
223
224 l_sp_percentage_rate:= NVL(l_sp_percentage_rate,0);
225 l_prev_tax_income:= NVL(l_prev_tax_income,0);
226 -- Fetch Balances
227 --
228 l_defbal_id := PAY_NL_GENERAL.GET_DEFINED_BALANCE_ID('TAX_TRAVEL_ALLOWANCE_ASG_PTD');
229 l_Travel_Allowance := NVL(pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id),0);
230
231 l_defbal_id := PAY_NL_GENERAL.GET_DEFINED_BALANCE_ID('TAX_SEA_DAYS_DISCOUNT_ASG_PTD');
232 l_Sea_Days_Discount := NVL(pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id),0);
233
234 l_defbal_id := PAY_NL_GENERAL.GET_DEFINED_BALANCE_ID('TAX_ABW_ALLOWANCE_ASG_PTD');
235 l_ABW_Allowance := NVL(pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id),0);
236
237 l_defbal_id := PAY_NL_GENERAL.GET_DEFINED_BALANCE_ID('TAX_ABW_ALLOWANCE_STOPPAGE_ASG_PTD');
238 l_ABW_Allowance_Stoppage := NVL(pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id),0);
239
240 l_defbal_id := PAY_NL_GENERAL.GET_DEFINED_BALANCE_ID('TAX_WAO_ALLOWANCE_ASG_PTD');
241 l_WAO_Allowance := NVL(pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id),0);
242
243 l_defbal_id := PAY_NL_GENERAL.GET_DEFINED_BALANCE_ID('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_PTD');
244
245 open get_context_id;
246 fetch get_context_id into l_context_id;
247 close get_context_id;
248 l_ZVW_Contribution := NVL(pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id, null, null, l_context_id, 'ZVW', null, null ),0);
249 l_defbal_id := PAY_NL_GENERAL.GET_DEFINED_BALANCE_ID('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_PTD');
250 l_ZVW_Contribution := l_ZVW_Contribution + NVL(pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id, null, null, l_context_id, 'ZVW', null, null ),0);
251 l_defbal_id := PAY_NL_GENERAL.GET_DEFINED_BALANCE_ID('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_PTD');
252 l_ZVW_Contribution := l_ZVW_Contribution + NVL(pay_balance_pkg.get_value(l_defbal_id, p_assignment_action_id, null, null, l_context_id, 'ZVW', null, null ),0);
253
254 l_sql :=
255 'SELECT TO_CHAR('||l_sp_percentage_rate|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16 '||
256 ',TO_CHAR('||l_prev_tax_income||',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL17 '||
257 ','''||l_Tax_Code||''' COL01 '||
258 ',REPLACE('''||l_Tax_Table||''',''||fnd_global.local_chr(39)'',fnd_global.local_chr(39)) COL02 '||
259 ',REPLACE('''||l_Tax_Reduction||''',''||fnd_global.local_chr(39)'',fnd_global.local_chr(39)) COL03 '||
260 ',REPLACE('''||l_Labour_Tax_Reduction||''',''||fnd_global.local_chr(39)'',fnd_global.local_chr(39)) COL07 '||
261 ',REPLACE('''||l_Senior_Tax_Reduction||''',''||fnd_global.local_chr(39)'',fnd_global.local_chr(39)) COL05 '||
262 ','''||l_Special_Indicators||''' COL06 ' ||
263 ',TO_CHAR('''||TO_CHAR(l_Travel_Allowance)||''',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL18 '
264 ||',TO_CHAR('''||TO_CHAR(l_Sea_Days_Discount)||''',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL19 '
265 ||',TO_CHAR('''||TO_CHAR(l_ABW_Allowance)||''',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL20 '
266 ||',TO_CHAR('''||TO_CHAR(l_ABW_Allowance_Stoppage)||''',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL21 '
267 ||',TO_CHAR('''||TO_CHAR(l_WAO_Allowance)||''',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL22 '
268 ||',TO_CHAR('''||TO_CHAR(l_ZVW_Contribution)||''',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL23 '||
269 'FROM dual';
270 --
271 RETURN l_sql;
272 --
273 END tax_info;
274
275
276 --
277 /* ---------------------------------------------------------------------
278 Function : getElements
279
280 Text
281 ------------------------------------------------------------------------ */
282 function getElements(p_assignment_action_id number
283 ,p_element_set_name varchar2) return long is
284 begin
285 --
286 --
287 if g_debug then
288 hr_utility.set_location('Entering pay_soe_glb.getElements', 10);
289 end if;
290 --
291 l_sql :=
292 'select nvl(ettl.reporting_name,et.element_type_id) COL01
293 , nvl(ettl.reporting_name,ettl.element_name) COL02
294 , pay_nl_general.get_iv_run_result(rr.run_result_id,et.element_type_id,''SI Type Name'') COL03
295 , decode(pay_nl_general.get_iv_run_result(rr.run_result_id,et.element_type_id,''SI Type Name''),null,'' '',
296 '' and pay_nl_general.get_iv_run_result('' || max(rr.run_result_id) || '','' || max(et.element_type_id)
297 || '','' || ''''''SI Type Name'''''' || '') ='' || ''pay_nl_general.get_iv_run_result(run_result_id,element_type_id,''
298 || ''''''SI Type Name'''''' || '')'') COL04
299 , to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
300 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator
301 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
302 from pay_assignment_actions aa
303 , pay_run_results rr
304 , pay_run_result_values rrv
305 , pay_input_values_f iv
306 , pay_input_values_f_tl ivtl
307 , pay_element_types_f et
308 , pay_element_types_f_tl ettl
309 , pay_element_set_members esm
310 , pay_element_sets es
311 where aa.assignment_action_id :action_clause
312 and aa.assignment_action_id = rr.assignment_action_id
313 and rr.status in (''P'',''PA'')
314 and rr.run_result_id = rrv.run_result_id
315 and rr.element_type_id = et.element_type_id
316 and :effective_date between
317 et.effective_start_date and et.effective_end_date
318 and et.element_type_id = ettl.element_type_id
319 and rrv.input_value_id = iv.input_value_id
320 and iv.name = ''Pay Value''
321 and :effective_date between
322 iv.effective_start_date and iv.effective_end_date
323 and iv.input_value_id = ivtl.input_value_id
324 and ivtl.language = userenv(''LANG'')
325 and ettl.language = userenv(''LANG'')
326 and et.element_type_id = esm.element_type_id
327 and esm.element_set_id = es.element_set_id
328 and ( es.BUSINESS_GROUP_ID IS NULL
329 OR es.BUSINESS_GROUP_ID = :business_group_id )
330 AND ( es.LEGISLATION_CODE IS NULL
331 OR es.LEGISLATION_CODE = '':legislation_code'' )
332 and es.element_set_name = '''|| p_element_set_name ||'''
333 group by nvl(ettl.reporting_name,ettl.element_name)
334 , ettl.reporting_name
335 ,pay_nl_general.get_iv_run_result(rr.run_result_id,et.element_type_id,''SI Type Name'')
336 ,nvl(ettl.reporting_name,et.element_type_id)
337 order by nvl(ettl.reporting_name,ettl.element_name),nvl(ettl.reporting_name,et.element_type_id)';
338 --
339 --
340 if g_debug then
341 hr_utility.set_location('Leaving pay_soe_glb.getElements', 20);
342 end if;
343 --
344 return l_sql;
345 --
346 end getElements;
347 --
348 /* ---------------------------------------------------------------------
349 Function : Elements2
350
351 Text :Returns the SQL for the Deductions Region of the SOE
352 ------------------------------------------------------------------------ */
353 function Elements2(p_assignment_action_id number) return long is
354 begin
355 return getElements(p_assignment_action_id
356 ,pay_soe_util.getConfig('ELEMENTS2'));
357 end Elements2;
358
359
360 /* ---------------------------------------------------------------------
361 Function : getBalances
362
363 Text : Constructs the SQL for the Balances and YTD Values Region of SOE
364 similar to Core Function - pay_soe_glb.getBalances
365 Only difference-Returns SI Type Name by calling
366 pay_nl_general.get_sit_type_name
367 ------------------------------------------------------------------------ */
368 function getBalances(p_assignment_action_id number
369 ,p_balance_attribute varchar2) return long is
370 --
371 TYPE balance_type_lst_rec is RECORD (balance_name pay_balance_types.balance_name%TYPE
372 ,balance_type_id pay_balance_types.balance_type_id%TYPE
373 ,reporting_name pay_balance_types.reporting_name%TYPE
374 ,dimension_name pay_balance_dimensions.dimension_name%TYPE
375 ,defined_balance_name pay_balance_types.reporting_name%TYPE
376 ,defined_balance_id pay_defined_balances.defined_balance_id%TYPE);
377 TYPE balance_type_lst_tab is TABLE of balance_type_lst_rec
378 INDEX BY BINARY_INTEGER;
379 l_balance_type_lst balance_type_lst_tab;
380 --
381 l_effective_date date;
382 l_earliest_ctx_date date;
383 l_temp_date date;
384 l_action_sequence number;
385 l_payroll_id number;
386 l_assignment_id number;
387 l_business_group_id number;
388 l_legislation_code varchar2(30);
389 l_save_asg_run_bal varchar2(30);
390 l_inp_val_name pay_input_values_f.name%type;
391 l_si_needed_chr varchar2(10);
392 l_st_needed_chr varchar2(10);
393 l_sn_needed_chr varchar2(10);
394 l_st2_needed_chr varchar2(10);
395 l_found boolean;
396 balCount number;
397 locked_assignment_action_id pay_action_interlocks.locked_action_id%TYPE;
398 --
399 l_defined_balance_lst pay_balance_pkg.t_balance_value_tab;
400 l_context_lst pay_balance_pkg.t_context_tab;
401 l_output_table pay_balance_pkg.t_detailed_bal_out_tab;
402 --
403 i number;
404 --
405 --
406 /* bug 4253982 */
407 cursor getAsgActions(l_assignment_action_id number,l_eff_date date) is
408 select paa.assignment_action_id from
409 pay_assignment_actions paa,
410 pay_payroll_actions ppa
411 where paa.assignment_id =
412 ( select assignment_id
413 from pay_assignment_actions
414 where assignment_action_id = l_assignment_action_id
415 )
416 and paa.action_status = 'C'
417 and paa.assignment_action_id < l_assignment_action_id
418 and paa.payroll_action_id = ppa.payroll_action_id
419 and ppa.date_earned >= trunc(l_eff_date,'Y')
420 order by paa.assignment_action_id desc;
421
422 cursor getAction is
423 select pa.payroll_id
424 , aa.action_sequence
425 , pa.effective_date
426 , aa.assignment_id
427 , pa.business_group_id
428 , bg.legislation_code
429 , lrl.rule_mode
430 from pay_payroll_actions pa
431 , pay_assignment_actions aa
432 , per_business_groups bg
433 , pay_legislation_rules lrl
434 where aa.assignment_action_id = p_assignment_action_id
435 and aa.payroll_action_id = pa.payroll_action_id
436 and pa.business_group_id = bg.business_group_id
437 and lrl.legislation_code(+) = bg.legislation_code
438 and lrl.rule_type(+) = 'SAVE_ASG_RUN_BAL';
439 --
440 cursor getDBal is
441 select ba.defined_balance_id
442 , bd.dimension_name
443 , bd.period_type
444 , bt.balance_name
445 , bt.reporting_name
446 , bt.balance_type_id
447 , NVL(NVL(oi.org_information7,bt.reporting_name),bt.balance_name) defined_balance_name
448 from pay_balance_attributes ba
449 , pay_bal_attribute_definitions bad
450 , pay_defined_balances db
451 , pay_balance_dimensions bd
452 , pay_balance_types_tl bt
453 , hr_organization_information oi
454 where bad.attribute_name = p_balance_attribute
455 and ( bad.BUSINESS_GROUP_ID IS NULL
456 OR bad.BUSINESS_GROUP_ID = l_business_group_id)
457 AND ( bad.LEGISLATION_CODE IS NULL
458 OR bad.LEGISLATION_CODE = l_legislation_code)
459 and bad.attribute_id = ba.attribute_id
460 and ba.defined_balance_id = db.defined_balance_id
461 and db.balance_dimension_id = bd.balance_dimension_id
462 and db.balance_type_id = bt.balance_type_id
463 and bt.language = userenv('LANG')
464 --
465 and oi.org_information1 = 'BALANCE'
466 --
467 and oi.org_information4 = to_char(bt.balance_type_id)
468 and oi.org_information5 = to_char(db.balance_dimension_id)
469 --
470 and oi.org_information_context = 'Business Group:SOE Detail'
471 and oi.organization_id = l_business_group_id
472 order by NVL(LPAD(oi.ORG_INFORMATION8,15,0),0),NVL(NVL(oi.org_information7,bt.reporting_name),bt.balance_name);
473 --
474 cursor getRBContexts is
475 select rb.TAX_UNIT_ID
476 , rb.JURISDICTION_CODE
477 , rb.SOURCE_ID
478 , rb.SOURCE_TEXT
479 , rb.SOURCE_NUMBER
480 , rb.SOURCE_TEXT2
481 from pay_run_balances rb
482 where rb.ASSIGNMENT_ID = l_assignment_id
483 and l_action_sequence >= rb.action_sequence
484 and rb.effective_date >= l_earliest_ctx_date;
485 --
486 cursor getRRContexts is
487 select distinct
488 aa.tax_unit_id tax_unit_id
489 , rr.jurisdiction_code jurisdiction_code
490 , decode(l_si_needed_chr,
491 'Y', pay_balance_pkg.find_context('SOURCE_ID'
492 ,rr.run_result_id)
493 ,null) source_id
494 , decode(l_st_needed_chr,
495 'Y', pay_balance_pkg.find_context('SOURCE_TEXT'
496 ,rr.run_result_id)
497 ,null) source_text
498 , decode(l_sn_needed_chr,
499 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER'
500 ,rr.run_result_id)
501 ,null) source_number
502 , decode(l_st2_needed_chr,
503 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2'
504 ,rr.run_result_id)
505 ,null) source_text2
506 from pay_assignment_actions aa,
507 pay_payroll_actions pa,
508 pay_run_results rr
509 where aa.ASSIGNMENT_ID = l_assignment_id
510 and aa.assignment_action_id = rr.assignment_action_id
511 and l_action_sequence >= aa.action_sequence
512 and aa.payroll_action_id = pa.payroll_action_id
513 and pa.effective_date >= l_earliest_ctx_date;
514 l_si_type_name pay_run_result_values.result_value%TYPE;
515 --
516 cursor getLockedActionid is
517 select max(locked_action_id) from pay_action_interlocks where locking_action_id = p_assignment_action_id;
518 --
519 begin
520 --
521 if g_debug then
522 hr_utility.set_location('Entering pay_soe_glb.getBalances', 10);
523 end if;
524 --
525 open getAction;
526 fetch getAction into l_payroll_id,
527 l_action_sequence,
528 l_effective_date,
529 l_assignment_id,
530 l_business_group_id,
531 l_legislation_code,
532 l_save_asg_run_bal;
533 close getAction;
534 --
535 l_earliest_ctx_date := l_effective_date;
536 --
537 i := 0;
538 --
539 if g_debug then
540 hr_utility.set_location('pay_soe_glb.getBalances', 20);
541 end if;
542 --
543 open getLockedActionid;
544 fetch getLockedActionid into locked_assignment_action_id;
545 close getLockedActionid;
546 if locked_assignment_action_id is null then
547 locked_assignment_action_id := p_assignment_action_id;
548 end if;
549 --
550 for db in getDBal loop
551 i := i + 1;
552 --
553 l_defined_balance_lst(i).defined_balance_id := db.defined_balance_id;
554 --
555 l_balance_type_lst(db.defined_balance_id).balance_name :=
556 db.balance_name;
557 l_balance_type_lst(db.defined_balance_id).reporting_name :=
558 db.reporting_name;
559 l_balance_type_lst(db.defined_balance_id).balance_type_id :=
560 db.balance_type_id;
561 l_balance_type_lst(db.defined_balance_id).defined_balance_name:=
562 db.defined_balance_name;
563 l_balance_type_lst(db.defined_balance_id).dimension_name :=
564 db.dimension_name;
565 l_balance_type_lst(db.defined_balance_id).defined_balance_id :=
566 db.defined_balance_id;
567 --
568 pay_balance_pkg.get_period_type_start
569 (p_period_type => db.period_type
570 ,p_effective_date => l_effective_date
571 ,p_payroll_id => l_payroll_id
572 ,p_start_date => l_temp_date);
573 --
574 if l_temp_date < l_earliest_ctx_date then
575 l_earliest_ctx_date := l_temp_date;
576 end if;
577 end loop;
578 --
579 i := 0;
580 if l_save_asg_run_bal = 'Y' then
581 if g_debug then
582 hr_utility.set_location('pay_soe_glb.getBalances', 30);
583 end if;
584 for ctx in getRBContexts loop
585 i := i + 1;
586 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
587 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
588 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
589 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
590 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
591 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
592 end loop;
593 else
594 if g_debug then
595 hr_utility.set_location('pay_soe_glb.getBalances', 40);
596 end if;
597 -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
598 l_si_needed_chr := 'N';
599 l_st_needed_chr := 'N';
600 l_sn_needed_chr := 'N';
601 l_st2_needed_chr := 'N';
602 --
603 pay_core_utils.get_leg_context_iv_name('SOURCE_ID',
604 l_legislation_code,
605 l_inp_val_name,
606 l_found);
607 if (l_found = TRUE) then
608 l_si_needed_chr := 'Y';
609 end if;
610 --
611 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT',
612 l_legislation_code,
613 l_inp_val_name,
614 l_found);
615 if (l_found = TRUE) then
616 l_st_needed_chr := 'Y';
617 end if;
618 --
619 pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER',
620 l_legislation_code,
621 l_inp_val_name,
622 l_found);
623 if (l_found = TRUE) then
624 l_sn_needed_chr := 'Y';
625 end if;
626 --
627 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT2',
628 l_legislation_code,
629 l_inp_val_name,
630 l_found);
631 if (l_found = TRUE) then
632 l_st2_needed_chr := 'Y';
633 end if;
634 --
635 --
636 for ctx in getRRContexts loop
637 i := i + 1;
638 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
639 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
640 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
641 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
642 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
643 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
644 end loop;
645 end if;
646 --
647 --
648 if g_debug then
649 hr_utility.set_location('pay_soe_glb.getBalances', 50);
650 end if;
651 --
652 pay_balance_pkg.get_value (p_assignment_action_id => p_assignment_action_id
653 ,p_defined_balance_lst => l_defined_balance_lst
654 ,p_context_lst => l_context_lst
655 ,p_output_table => l_output_table);
656 --
657 pay_soe_util.clear;
658 --
659 balCount := 0;
660 if l_output_table.count > 0 then
661 --
662 if g_debug then
663 hr_utility.set_location('pay_soe_glb.getBalances', 60);
664 end if;
665 --
666 for i in l_output_table.first..l_output_table.last loop
667 if l_output_table(i).balance_value <> 0 then
668 balCount := balCount + 1;
669 --
670 pay_soe_util.setValue('01'
671 ,l_balance_type_lst(l_output_table(i).defined_balance_id).balance_name
672 ,TRUE,FALSE);
673 pay_soe_util.setValue('02'
674 ,l_balance_type_lst(l_output_table(i).defined_balance_id).reporting_name
675 ,FALSE,FALSE);
676 pay_soe_util.setValue('03'
677 ,l_balance_type_lst(l_output_table(i).defined_balance_id).dimension_name
678 ,FALSE,FALSE);
679 pay_soe_util.setValue('04'
680 ,l_balance_type_lst(l_output_table(i).defined_balance_id).defined_balance_name
681 ,FALSE,FALSE);
682 pay_soe_util.setValue('05',
683 hr_general.decode_organization(to_char(l_output_table(i).tax_unit_id))
684 ,FALSE,FALSE);
685 pay_soe_util.setValue('06',to_char(l_output_table(i).tax_unit_id),FALSE,FALSE);
686
687
688 pay_soe_util.setValue('07',l_output_table(i).jurisdiction_code,FALSE,FALSE);
689 pay_soe_util.setValue('08',l_output_table(i).source_id,FALSE,FALSE);
690 pay_soe_util.setValue('09',l_output_table(i).source_text,FALSE,FALSE);
691 pay_soe_util.setValue('10',l_output_table(i).source_number,FALSE,FALSE);
692 pay_soe_util.setValue('11',l_output_table(i).source_text2,FALSE,FALSE);
693 l_si_type_name := NULL;
694 IF l_output_table(i).source_text IS NOT NULL THEN
695 l_si_type_name := pay_nl_general.get_sit_type_name(l_balance_type_lst(l_output_table(i).defined_balance_id).balance_type_id
696 ,locked_assignment_action_id,l_effective_date,l_output_table(i).source_text);
697
698 /* Bug 4253982 */
699 IF l_si_type_name IS NULL THEN
700
701 FOR l_asg_actions in getAsgActions(locked_assignment_action_id,l_effective_date) loop
702
703 l_si_type_name := pay_nl_general.get_sit_type_name(l_balance_type_lst(l_output_table(i).defined_balance_id).balance_type_id
704 ,l_asg_actions.assignment_action_id,l_effective_date,l_output_table(i).source_text);
705 IF l_si_type_name IS NOT NULL THEN
706 EXIT;
707 END IF;
708 END LOOP;
709 END IF;
710 /* end Bug 4253982 */
711 END IF;
712 pay_soe_util.setValue('12',l_si_type_name,FALSE,FALSE);
713 pay_soe_util.setValue(16,to_char(l_output_table(i).balance_value,
714 fnd_currency.get_format_mask(substr(PAY_SOE_GLB.g_currency_code,2,3),40)),FALSE,FALSE);
715 pay_soe_util.setValue(17,to_char(l_output_table(i).defined_balance_id),FALSE,TRUE);
716
717 end if;
718 end loop;
719 end if;
720 --
721 if balCount > 0 then
722 return pay_soe_util.genCursor;
723 else
724 return ('select null COL01 from dual where 1=0');
725 --return null;
726 end if;
727 end getBalances;
728 --
729
730 /* ---------------------------------------------------------------------
731 Function : Balances1
732
733 Text : Displays the Balances in the Balances Region
734 ------------------------------------------------------------------------ */
735 function Balances1(p_assignment_action_id number) return long is
736 begin
737 return getBalances(p_assignment_action_id
738 ,pay_soe_util.getConfig('BALANCES1'));
739 end Balances1;
740 --
741 /* ---------------------------------------------------------------------
742 Function : Balances2
743
744 Text : Displays the Balances in the YTD Values Region
745 ------------------------------------------------------------------------ */
746 function Balances2(p_assignment_action_id number) return long is
747 begin
748 return getBalances(p_assignment_action_id
749 ,pay_soe_util.getConfig('BALANCES2'));
750 end Balances2;
751 END PAY_NL_SOE;