[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_TAXOFFICE_ARCHIVE
Source
1 PACKAGE BODY PAY_NL_TAXOFFICE_ARCHIVE as
2 /* $Header: pynltosa.pkb 120.13.12020000.2 2012/07/05 01:48:30 amnaraya ship $ */
3 g_package varchar2(33) := ' PAY_NL_TAXOFFICE_ARCHIVE.';
4
5
6 g_error_flag varchar2(30);
7 g_warning_flag varchar2(30);
8 g_error_count NUMBER;
9 g_payroll_action_id NUMBER;
10 g_assignment_number VARCHAR2(30);
11 g_full_name VARCHAR2(150);
12 g_debug boolean;
13
14 /*------------------------------------------------------------------------------
15 |Name : GET_PARAMETER |
16 |Type : Function |
17 |Description : Funtion to get the parameters of the archive process |
18 -------------------------------------------------------------------------------*/
19
20
21 function get_parameter(
22 p_parameter_string in varchar2
23 ,p_token in varchar2
24 ,p_segment_number in number default null ) RETURN varchar2
25 IS
26
27 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
28 l_start_pos NUMBER;
29 l_delimiter varchar2(1):=' ';
30 l_proc VARCHAR2(400):= g_package||' get parameter ';
31
32 BEGIN
33 if g_debug then
34 hr_utility.set_location('Entering get_parameter',50);
35 end if;
36
37 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
38 --
39 IF l_start_pos = 0 THEN
40 l_delimiter := '|';
41 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
42 end if;
43
44 IF l_start_pos <> 0 THEN
45 l_start_pos := l_start_pos + length(p_token||'=');
46 l_parameter := substr(p_parameter_string,
47 l_start_pos,
48 instr(p_parameter_string||' ',
49 l_delimiter,l_start_pos)
50 - l_start_pos);
51 IF p_segment_number IS NOT NULL THEN
52 l_parameter := ':'||l_parameter||':';
53 l_parameter := substr(l_parameter,
54 instr(l_parameter,':',1,p_segment_number)+1,
55 instr(l_parameter,':',1,p_segment_number+1) -1
56 - instr(l_parameter,':',1,p_segment_number));
57 END IF;
58 END IF;
59 --
60 hr_utility.set_location('Leaving get_parameter',53);
61 RETURN l_parameter;
62
63 hr_utility.set_location('Exiting get_parameters',50);
64 END get_parameter;
65
66
67
68
69 /*-----------------------------------------------------------------------------
70 |Name : GET_ALL_PARAMETERS |
71 |Type : Procedure |
72 |Description: Procedure which returns all the parameters of the archive process|
73 -------------------------------------------------------------------------------*/
74
75
76 -----------------------------------------------------------------------------
77 -- GET_ALL_PARAMETERS gets all parameters for the payroll action
78 -----------------------------------------------------------------------------
79 PROCEDURE get_all_parameters (
80 p_payroll_action_id IN NUMBER
81 ,p_business_group_id OUT NOCOPY NUMBER
82 ,p_effective_date OUT NOCOPY DATE
83 ,p_tax_year OUT NOCOPY DATE
84 ,p_employer OUT NOCOPY number ) IS
85 -- ,p_org_struct_id OUT NOCOPY number ) IS
86 --
87 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
88 SELECT fnd_date.canonical_to_date(pay_nl_taxoffice_archive.get_parameter(legislative_parameters,'REPORT_YEAR'))
89 ,pay_nl_taxoffice_archive.get_parameter(legislative_parameters,'EMPLOYER_ID')
90 -- ,pay_nl_taxoffice_archive.get_parameter(legislative_parameters,'ORG_HIERARCHY')
91 ,effective_date
92 ,business_group_id
93 FROM pay_payroll_actions
94 WHERE payroll_action_id = p_payroll_action_id;
95 --
96 l_effective_date date;
97 l_proc VARCHAR2(400):= g_package||' get_all_parameters ';
98 --
99 BEGIN
100 --
101
102 if g_debug then
103 hr_utility.set_location('Entering get_all_parameters',51);
104 end if;
105
106 OPEN csr_parameter_info (p_payroll_action_id);
107 FETCH csr_parameter_info INTO
108 p_tax_year, p_employer--, p_org_struct_id
109 ,p_effective_date,p_business_group_id;
110 CLOSE csr_parameter_info;
111
112 if g_debug then
113 hr_utility.set_location('Leaving get_all_parameters',54);
114 end if;
115
116 END;
117 --
118
119
120
121 /*-------------------------------------------------------------------------------
122 |Name : Mandatory_Check |
123 |Type : Procedure |
124 |Description : Procedure to check if the specified Mandatory Field is NULL |
125 | if so flag a Error message to the Log File |
126 -------------------------------------------------------------------------------*/
127
128 Procedure Mandatory_Check(p_message_name varchar2
129 ,p_field varchar2
130 ,p_value varchar2) is
131 v_message_text fnd_new_messages.message_text%TYPE;
132 v_employee_dat VARCHAR2(255);
133 v_label_desc hr_lookups.meaning%TYPE;
134 Begin
135 if g_debug then
136 hr_utility.set_location('Checking Field '||p_field,425);
137 end if;
138
139 If p_value is null then
140 v_label_desc := hr_general.decode_lookup('HR_NL_REPORT_LABELS', p_field);
141 v_employee_dat :=RPAD(SUBSTR(g_assignment_number,1,20),20)
142 ||' '||RPAD(SUBSTR(g_full_name,1,25),25)
143 ||' '||RPAD(SUBSTR(v_label_desc,1,25),25)
144 ||' '||RPAD(SUBSTR(g_error_flag,1,15),15);
145 hr_utility.set_message(801,p_message_name);
146 v_message_text :=SUBSTR(fnd_message.get,1,70);
147 g_error_count := NVL(g_error_count,0) +1;
148 FND_FILE.PUT_LINE(FND_FILE.LOG, v_employee_dat||' '||v_message_text);
149 end if;
150
151 end;
152
153 /*--------------------------------------------------------------------
154 |Name : RANGE_CODE |
155 |Type : Procedure |
156 |Description: This procedure returns a sql string to select a range of|
157 | assignments eligible for archival |
158 ----------------------------------------------------------------------*/
159
160 Procedure RANGE_CODE (pactid IN NUMBER
161 ,sqlstr OUT NOCOPY VARCHAR2) is
162
163 v_log_header VARCHAR2(255);
164
165 BEGIN
166 -- g_debug:=TRUE;
167 if g_debug then
168 hr_utility.trace_on(NULL,'TOA');
169 hr_utility.set_location('Entering Range Code',50);
170 end if;
171
172 /*Return the SELECT Statement to select a range of assignments
173 eligible for archival */
174
175 sqlstr := 'SELECT DISTINCT person_id
176 FROM per_people_f ppf
177 ,pay_payroll_actions ppa
178 WHERE ppa.payroll_action_id = :payroll_action_id
179 AND ppa.business_group_id = ppf.business_group_id
180 ORDER BY ppf.person_id';
181
182
183 --Write to Log File
184 v_log_header := RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','NL_ASSIGNMENT_NUMBER'),1,20),20)
185 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FULL_NAME'),1,25),25)
186 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FIELD_NAME'),1,25),25)
187 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR_TYPE'),1,15),15)
188 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','MESSAGE'),1,70),70);
189 Fnd_file.put_line(FND_FILE.LOG,v_log_header);
190
191 if g_debug then
192 hr_utility.set_location('Leaving Range Code',350);
193 end if;
194
195 END RANGE_CODE;
196
197
198 /*--------------------------------------------------------------------
199 |Name : ASSIGNMENT_ACTION_CODE |
200 |Type : Procedure |
201 |Description: This procedure Fetches,validates and archives |
202 | information in the newly created context |
203 | NL ATS EMPLOYEE DETAILS |
204 ----------------------------------------------------------------------*/
205 Procedure ASSIGNMENT_ACTION_CODE (
206 p_payroll_action_id in number
207 ,p_start_person_id in number
208 ,p_end_person_id in number
209 ,p_chunk in number) IS
210
211
212 /*Cursor Fetches All the Employee Assignment Records
213 whose Employer matches the one selected in the SRS Request
214 and for which a Record has not already been archived.
215 */
216
217 CURSOR Cur_EE_ATS_Archive(lp_business_group_id number,lp_employer_id number,
218 lp_Tax_Year_End_Date Date,
219 lp_Tax_Year_Start_Date Date,
220 -- lp_org_struct_version_id number,
221 lp_start_person_id number,
222 lp_end_person_id number
223 ) IS
224 SELECT
225 paa.organization_id,
226 pap.person_id , paa.assignment_id, paa.assignment_number,
227 pap.last_name, pap.Date_of_Birth, pap.full_name
228 FROM
229 per_people_f pap
230 ,per_assignments_f paa
231 ,pay_all_payrolls_f ppf
232 -- per_all_people_f pap Performance fix 5042871
233 -- ,per_all_assignments_f paa
234 WHERE pap.business_group_id = lp_business_group_id
235 and pap.person_id = paa.person_id
236 and paa.person_id BETWEEN lp_start_person_id AND lp_end_person_id
237 and lp_Tax_Year_End_Date between pap.effective_start_date and pap.effective_end_date
238 and paa.effective_start_date =
239 (
240 SELECT MIN(asg.effective_start_date)
241 FROM per_all_assignments_f asg
242 WHERE asg.assignment_id = paa.assignment_id
243 and asg.payroll_id is not NULL
244 and asg.effective_start_date <= lp_Tax_Year_End_Date
245 and nvl(asg.effective_end_date, lp_Tax_Year_End_Date) >= lp_Tax_Year_Start_Date
246
247 )
248 and paa.payroll_id = ppf.payroll_id
249 and ppf.business_group_id = lp_business_group_id
250 and ppf.effective_start_date <= lp_Tax_Year_End_Date
251 and ppf.effective_end_date >= lp_Tax_Year_Start_Date
252 and ppf.prl_information_category = 'NL'
253 and lp_employer_id = ppf.prl_information1;
254 /*and lp_employer_id = hr_nl_org_info.get_tax_org_id(lp_org_struct_version_id,paa.organization_id)
255 and not exists
256 (select 1 from pay_action_information ee_ats
257 WHERE ee_ats.action_context_type='AAP'
258 AND ee_ats.action_information_category = 'NL ATS EMPLOYEE DETAILS'
259 AND ee_ats.action_information1 = lp_employer_id
260 AND ee_ats.action_information2 =pap.person_id
261 AND ee_ats.action_information3 =paa.assignment_id
262 AND ee_ats.effective_date =lp_Tax_Year_End_Date)
263 order by pap.person_id,paa.assignment_id;*/
264
265
266
267 l_tax_year_start_date date;
268 l_tax_year_end_date date;
269 l_tax_year_date number;
270 l_person_id per_all_people_f.person_id%TYPE;
271 l_asg_start_date varchar2(255);
272 l_asg_end_date varchar2(255);
273 l_assgt_start_date date;
274 l_assgt_end_date date;
275 l_asg_dates_flag number;
276 l_assignment_id number;
277 l_sum_of_balances number;
278 l_assgt_act_id number;
279 l_business_group_id number;
280 l_tax_year date;
281 l_effective_date date;
282 -- l_org_struct_id number;
283 -- l_org_struct_version_id number;
284
285
286 l_action_info_id number;
287 l_asg_act_id number;
288 l_ovn number;
289 l_ATS_Process_Date date;
290 l_employer_id number;
291 l_wage number;
292 l_taxable_income number;
293 l_deduct_wage_tax_si_cont number;
294 l_Labour_Discount number;
295 l_Wage_Tax_Discount varchar2(255);
296 l_Wage_Tax_Table_Code varchar2(255);
297 l_Income_Code varchar2(15);
298 l_Special_Indicator varchar2(255);
299 l_Amount_Special_Indicator varchar2(255);
300 l_SI_Insured_Flag varchar2(10);
301 l_ZVW_Contribution number;
302 l_ZVW_Basis number;
303 l_Net_Expense_Allowance number;
304 l_Private_Use_Car number;
305 l_Value_Private_Use_Car number := 0;
306 l_LSS_Saved_Amount number := 0;
307 l_Employer_Part_Child_Care number := 0;
308 l_Allowance_On_Disability number := 0;
309 l_Applied_LCLD number := 0;
310 l_User_Bal_String varchar2(255);
311 l_active_asg_flag number := 0;
312
313
314 BEGIN
315 -- g_debug:=true;
316 if g_debug then
317
318 hr_utility.trace_on(NULL,'TOA');
319
320 hr_utility.set_location('Entering ASSIGNMENT_ACTION_CODE',300);
321 hr_utility.set_location('Entering Assignment Action Code',400);
322 hr_utility.set_location('p_payroll_action_id'||p_payroll_action_id,400);
323 end if;
324
325 get_all_parameters (
326 p_payroll_action_id,l_business_group_id
327 ,l_effective_date,l_tax_year,l_employer_id);--, l_org_struct_id);
328
329 if g_debug then
330
331 hr_utility.set_location('Archive p_payroll_action_id'||p_payroll_action_id,425);
332 hr_utility.set_location('Archive p_start_person_id'||p_start_person_id,425);
333 hr_utility.set_location('Archive p_end_person_id'||p_end_person_id,425);
334 hr_utility.set_location('Archive l_effective_date'||l_effective_date,425);
335 hr_utility.set_location('Archive l_business_group_id'||l_business_group_id,425);
336 hr_utility.set_location('Archive l_tax_year'||l_tax_year,425);
337 hr_utility.set_location('Archive l_employer_id'||l_employer_id,425);
338 -- hr_utility.set_location('Archive l_org_struct_id'||l_org_struct_id,425);
339 end if;
340 l_tax_year_date:=to_char(l_tax_year,'YYYY');
341 l_tax_year_end_date:= to_date('31/12/'||l_tax_year_date,'DD/MM/YYYY');
342 l_tax_year_start_date := to_date('01/01/'||l_tax_year_date,'DD/MM/YYYY');
343
344 populate_UserBal(l_business_group_id,l_tax_year_end_date);
345
346 g_error_flag := hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR');
347
348 if g_debug then
349 hr_utility.set_location('l_tax_year_date'||l_tax_year_date,425);
350 hr_utility.set_location('l_tax_year_end_date'||l_tax_year_end_date,425);
351 hr_utility.set_location('l_tax_year_start_date'||l_tax_year_start_date,425);
352 hr_utility.set_location('l_ATS_Process_Date'||l_ATS_Process_Date,425);
353 end if;
354
355 /*Determining the Org Hierarchy Version Id as on the Tax Year End Date
356 i.e 31st December for the Year process is being run*/
357
358 -- l_org_struct_version_id:=get_org_hierarchy(l_org_struct_id,l_tax_year_end_date);
359
360 -- hr_utility.set_location('l_org_struct_version_id'||l_org_struct_version_id,425);
361
362 --hr_utility.trace_on(NULL,'TOSA');
363
364 FOR Cur_EE_ATS_rec in Cur_EE_ATS_Archive(l_business_group_id,l_employer_id,l_tax_year_end_date,l_tax_year_start_date/*,l_org_struct_version_id*/,p_start_person_id,p_end_person_id)
365
366 LOOP
367
368 l_person_id:=Cur_EE_ATS_rec.person_id;
369 l_assignment_id :=Cur_EE_ATS_rec.assignment_id;
370 g_error_count:=0;
371 g_assignment_number:=Cur_EE_ATS_rec.assignment_number;
372 g_full_name:=Cur_EE_ATS_rec.full_name;
373 l_wage := 0;
374 l_taxable_income := 0;
375 l_deduct_wage_tax_si_cont := 0;
376 l_Labour_Discount := 0;
377 l_ZVW_Basis := 0;
378 l_ZVW_Contribution := 0;
379 l_Net_Expense_Allowance := 0;
380 l_Private_Use_Car := 0;
381 l_Value_Private_Use_Car := 0;
382 l_LSS_Saved_Amount := 0;
383 l_Employer_Part_Child_Care := 0;
384 l_Allowance_On_Disability := 0;
385 l_Applied_LCLD := 0;
386 l_active_asg_flag := 0;
387
388
389 hr_utility.set_location('Inside for loop, person id-'||l_person_id,350);
390 hr_utility.set_location('Inside for loop, assg no-'||g_assignment_number,350);
391 hr_utility.set_location('Inside for loop, name-'||g_full_name,350);
392
393 if g_debug then
394
395 hr_utility.set_location('l_person_id'||l_person_id,350);
396 hr_utility.set_location('l_assignment_id'||l_assignment_id,350);
397
398 end if;
399
400
401 /* fetching the assignment start date and assignment end date for archiving it to
402 columns pay_action_information6 and pay_action_information7 respectively */
403
404
405 /* fetching the max assignment action id for an assignment id in the tax year
406 for calculating various ASG_YTD balances */
407
408 l_assgt_act_id := get_max_assgt_act_id(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date);
409
410 if g_debug then
411 hr_utility.set_location('l_assgt_act_id'||l_assgt_act_id,425);
412 end if;
413
414 if l_assgt_act_id is not null then
415
416 l_ATS_Process_Date:=l_tax_year_end_date;
417
418 l_asg_dates_flag := pay_nl_general.get_period_asg_dates(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date,l_assgt_start_date,l_assgt_end_date);
419
420 if l_assgt_start_date < l_tax_year_start_date then
421 l_asg_start_date:=to_char(l_tax_year_start_date,'DDMMYYYY');
422 else
423 l_asg_start_date:=to_char(l_assgt_start_date,'DDMMYYYY');
424 end if;
425
426 if l_assgt_end_date > l_tax_year_end_date then
427 l_asg_end_date:=to_char(l_tax_year_end_date,'DDMMYYYY');
428 else
429 l_asg_end_date:=to_char(l_assgt_end_date,'DDMMYYYY');
430 end if;
431
432
433
434 if g_debug then
435 hr_utility.set_location('l_asg_start_date'||l_asg_start_date,400);
436 hr_utility.set_location('l_asg_end_date'||l_asg_end_date,400);
437 end if;
438
439 hr_utility.set_location('Fetching balances start',410);
440 -- l_wage:=floor(PAY_NL_TAXOFFICE_ARCHIVE.get_wage(l_assgt_act_id) + PAY_NL_TAXOFFICE_ARCHIVE.get_IZA_contributions(l_assgt_act_id)); --11886537
441 -- l_taxable_income:=floor(PAY_NL_TAXOFFICE_ARCHIVE.get_taxable_income(l_assgt_act_id)); --11886537
442 l_wage:=PAY_NL_TAXOFFICE_ARCHIVE.get_wage(l_assgt_act_id) + PAY_NL_TAXOFFICE_ARCHIVE.get_IZA_contributions(l_assgt_act_id); --11886537
443 l_taxable_income:=PAY_NL_TAXOFFICE_ARCHIVE.get_taxable_income(l_assgt_act_id); --11886537
444 l_deduct_wage_tax_si_cont:=PAY_NL_TAXOFFICE_ARCHIVE.get_deduct_wage_tax_si_cont(l_assgt_act_id);
445 l_Labour_Discount:=PAY_NL_TAXOFFICE_ARCHIVE.get_labour_discount(l_assgt_act_id);
446 l_ZVW_Contribution:=PAY_NL_TAXOFFICE_ARCHIVE.get_ZVW_contributions(l_assgt_act_id);
447 l_ZVW_Basis:=PAY_NL_TAXOFFICE_ARCHIVE.get_ZVW_basis(l_assgt_act_id);
448 l_Value_Private_Use_Car:=PAY_NL_TAXOFFICE_ARCHIVE.get_VALUE_PRIVATE_USE_CAR(l_assgt_act_id);
449 l_LSS_Saved_Amount:=PAY_NL_TAXOFFICE_ARCHIVE.get_LSS_Saved_Amount(l_assgt_act_id);
450 l_Employer_Part_Child_Care:=PAY_NL_TAXOFFICE_ARCHIVE.get_Employer_Part_Child_Care(l_assgt_act_id);
451 l_Allowance_On_Disability:=PAY_NL_TAXOFFICE_ARCHIVE.get_Allowance_On_Disability(l_assgt_act_id);
452 l_Applied_LCLD:=PAY_NL_TAXOFFICE_ARCHIVE.get_Applied_LCLD(l_assgt_act_id);
453
454 hr_utility.set_location('Fetching balances end',420);
455
456 PAY_NL_TAXOFFICE_ARCHIVE.get_special_indicators(l_assgt_act_id,l_assignment_id,l_tax_year_start_date,l_tax_year_end_date,l_Special_Indicator,l_Amount_Special_Indicator);
457 PAY_NL_TAXOFFICE_ARCHIVE.get_User_Balances(l_assgt_act_id,l_business_group_id,l_User_Bal_String);
458
459
460
461 if g_debug then
462 hr_utility.set_location('l_wage'||l_wage,425);
463 hr_utility.set_location('l_deduct_wage_tax_si_cont'||l_deduct_wage_tax_si_cont,425);
464 hr_utility.set_location('l_Labour_Discount'||l_Labour_Discount,425);
465 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
466 hr_utility.set_location('l_ZVW_Basis'||l_ZVW_Basis,425);
467 hr_utility.set_location('l_Value_Private_Use_Car'||l_Value_Private_Use_Car,425);
468 hr_utility.set_location('l_LSS_Saved_Amount'||l_LSS_Saved_Amount,425);
469 hr_utility.set_location('l_Employer_Part_Child_Care'||l_Employer_Part_Child_Care,425);
470 hr_utility.set_location('l_Allowance_On_Disability'||l_Allowance_On_Disability,425);
471 hr_utility.set_location('l_Applied_LCLD'||l_Applied_LCLD,425);
472 hr_utility.set_location('l_Special_Indicator'||l_Special_Indicator,425);
473 hr_utility.set_location('l_Amount_Special_Indicator'||l_Amount_Special_Indicator,425);
474 end if;
475
476 hr_utility.set_location('l_wage'||l_wage,425);
477 hr_utility.set_location('l_deduct_wage_tax_si_cont'||l_deduct_wage_tax_si_cont,425);
478 hr_utility.set_location('l_Labour_Discount'||l_Labour_Discount,425);
479 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
480 hr_utility.set_location('l_ZVW_Basis'||l_ZVW_Basis,425);
481 hr_utility.set_location('l_Value_Private_Use_Car'||l_Value_Private_Use_Car,425);
482 hr_utility.set_location('l_LSS_Saved_Amount'||l_LSS_Saved_Amount,425);
483 hr_utility.set_location('l_Employer_Part_Child_Care'||l_Employer_Part_Child_Care,425);
484 hr_utility.set_location('l_Allowance_On_Disability'||l_Allowance_On_Disability,425);
485 hr_utility.set_location('l_Applied_LCLD'||l_Applied_LCLD,425);
486 hr_utility.set_location('l_Special_Indicator'||l_Special_Indicator,425);
487 hr_utility.set_location('l_Amount_Special_Indicator'||l_Amount_Special_Indicator,425);
488
489
490 /* fetching the Wage Tax Discount for the given assignment in the tax year
491 it basically returns 0 or 1 depending on the Tax Reduction Flag set to None
492 or any other value, concatenated with the period start date, first changed value
493 of Tax Reduction Flag, first change start date, second changed value of
494 Tax Reduction Flag, second change start date.
495 If the changes are more than three, then the latest 3 changed values and
496 respective dates are picked up.*/
497
498
499 l_Wage_Tax_Discount:=PAY_NL_TAXOFFICE_ARCHIVE.get_wage_tax_discount(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date);
500
501
502 /* fetching the Wage Tax Table Code for the given assignment in the tax year
503 This is basically the tax code.It is obtained from the run results for the
504 input value Tax Code on the element Standard Tax Deduction.
505 If there is a change during the year, then the code that has been set
506 for the longest time during the year is shown.*/
507
508 l_Wage_Tax_Table_Code:=PAY_NL_TAXOFFICE_ARCHIVE.get_wage_tax_table_code(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date);
509
510 if g_debug then
511 hr_utility.set_location('l_Wage_Tax_Discount'||l_Wage_Tax_Discount,450);
512 hr_utility.set_location('l_Wage_Tax_Table_Code'||l_Wage_Tax_Table_Code,450);
513 end if;
514
515
516 /* fetching the income code */
517
518 l_income_code:=get_income_code(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date);
519
520 if g_debug then
521 hr_utility.set_location('l_income_code'||l_income_code,450);
522 end if;
523
524
525 /* fetching the SI Insured Flag */
526
527 l_SI_Insured_Flag:=get_si_insured_flag(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date);
528
529 if g_debug then
530 hr_utility.set_location('l_SI_Insured_Flag'||l_SI_Insured_Flag,500);
531 end if;
532
533 /*fetching the NET_EXPENSE_ALLOWANCE */
534 l_NET_EXPENSE_ALLOWANCE := get_NET_EXPENSE_ALLOWANCE(l_assgt_act_id);
535
536
537 /* fetching the Private Car Use Flag */
538
539 l_Private_Use_Car:=get_PRIVATE_USE_CAR(l_assgt_act_id);
540
541
542 if g_debug then
543 hr_utility.set_location('l_Private_Use_Car'||l_Private_Use_Car,500);
544 end if;
545
546 /* Checking the mandatory fields */
547 Mandatory_Check('PAY_NL_ASG_REQUIRED_FIELD','INCOME_CODE',l_Income_Code);
548
549 if g_error_count=0 then
550
551 /* Creating the archive assignment action */
552
553 SELECT pay_assignment_actions_s.NEXTVAL
554 INTO l_asg_act_id
555 FROM dual;
556 --
557 -- Create the archive assignment action
558 --
559 if g_debug then
560 hr_utility.set_location('Archive Assignment Action Id'||l_asg_act_id,450);
561 hr_utility.set_location('Archive Assignment Id'||l_Assignment_ID,450);
562 hr_utility.set_location('Archive Payroll Action Id'||p_payroll_action_id,450);
563 hr_utility.set_location('creating the archive asst. action',450);
564 hr_utility.set_location('Archive Assignment Action Id'||l_asg_act_id,475);
565 end if;
566
567
568
569
570 /*Creating the Assignment Action for the Assignment
571 and Locking the Latest Payroll Run Assignment Action for the Assignment
572 */
573
574 hr_nonrun_asact.insact(l_asg_act_id,l_Assignment_ID, p_payroll_action_id,p_chunk,NULL);
575 hr_nonrun_asact.insint(l_asg_act_id,l_assgt_act_id);
576
577 if g_debug then
578 hr_utility.set_location('Archive Assignment Action Id'||l_asg_act_id,475);
579 hr_utility.set_location('Action Information row about to create',450);
580 hr_utility.set_location('Coming out of loop',450);
581 hr_utility.set_location('Archive Assignment Action Id'||l_asg_act_id,450);
582 hr_utility.set_location('Archive Assignment Id'||l_Assignment_ID,450);
583 hr_utility.set_location('Archive Payroll Action Id'||p_payroll_action_id,450);
584 hr_utility.set_location('l_ovn'||l_ovn,450);
585 hr_utility.set_location('l_ATS_Process_Date'||l_ATS_Process_Date,450);
586 hr_utility.set_location('l_employer_id'||l_employer_id,450);
587 hr_utility.set_location('l_wage'||l_wage,450);
588 hr_utility.set_location('l_deduct_wage_tax_si_cont'||l_deduct_wage_tax_si_cont,450);
589 hr_utility.set_location('l_asg_start_date'||l_asg_start_date,450);
590 hr_utility.set_location('l_asg_end_date'||l_asg_end_date,450);
591 hr_utility.set_location('l_Labour_Discount'||l_Labour_Discount,450);
592 hr_utility.set_location('l_Wage_Tax_Discount'||l_Wage_Tax_Discount,450);
593 hr_utility.set_location('l_Wage_Tax_Table_Code'||l_Wage_Tax_Table_Code,450);
594 hr_utility.set_location('l_Income_Code'||l_Income_Code,450);
595 hr_utility.set_location('l_Special_Indicator'||l_Special_Indicator,450);
596 hr_utility.set_location('l_Amount_Special_Indicator'||l_Amount_Special_Indicator,450);
597 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,450);
598 hr_utility.set_location('l_SI_Insured_Flag'||l_SI_Insured_Flag,450);
599 hr_utility.set_location('l_Net_Expense_Allowance'||l_Net_Expense_Allowance,450);
600 hr_utility.set_location('l_Private_Use_Car'||l_Private_Use_Car,450);
601 hr_utility.set_location('l_taxable_income'||l_taxable_income,450);
602 end if;
603
604 BEGIN
605
606 SELECT 1 INTO l_active_asg_flag
607 FROM per_all_assignments_f asg, per_assignment_status_types past
608 WHERE asg.assignment_id = l_assignment_id
609 and past.assignment_status_type_id = asg.assignment_status_type_id
610 and past.per_system_status = 'ACTIVE_ASSIGN'
611 and asg.effective_start_date <= l_Tax_Year_End_Date
612 and nvl(asg.effective_end_date, l_Tax_Year_End_Date) >= l_Tax_Year_Start_Date;
613
614
615 EXCEPTION
616
617 WHEN TOO_MANY_ROWS
618 THEN l_active_asg_flag := 1;
619
620 WHEN NO_DATA_FOUND
621 THEN l_active_asg_flag := 0;
622
623 WHEN OTHERS
624 THEN null;
625
626 END;
627
628 IF l_active_asg_flag = 1 OR
629 (l_active_asg_flag = 0 AND
630 (l_wage <> 0 OR l_taxable_income <> 0 OR l_deduct_wage_tax_si_cont <> 0
631 OR l_Labour_Discount <> 0 OR l_ZVW_Basis <> 0 OR l_ZVW_Contribution <> 0
632 OR l_Net_Expense_Allowance <> 0 OR l_Private_Use_Car <> 0
633 OR l_Value_Private_Use_Car <> 0 OR l_LSS_Saved_Amount <> 0 OR l_Employer_Part_Child_Care <> 0
634 OR l_Allowance_On_Disability <> 0 OR l_Applied_LCLD <> 0)) THEN
635
636 pay_action_information_api.create_action_information (
637 p_action_information_id => l_action_info_id
638 ,p_action_context_id => l_asg_act_id
639 ,p_action_context_type => 'AAP'
640 ,p_object_version_number => l_ovn
641 ,p_effective_date => l_ATS_Process_Date
642 ,p_source_id => NULL
643 ,p_source_text => NULL
644 ,p_action_information_category => 'NL ATS EMPLOYEE DETAILS'
645 ,p_action_information1 => fnd_number.number_to_canonical(l_employer_id)
646 ,p_action_information2 => fnd_number.number_to_canonical(l_person_id)
647 ,p_action_information3 => fnd_number.number_to_canonical(l_assignment_id)
648 --,p_action_information4 => l_wage --11886537
649 ,p_action_information4 => fnd_number.number_to_canonical(l_wage) --11886537
650 --,p_action_information5 => l_deduct_wage_tax_si_cont --11886537
651 ,p_action_information5 => fnd_number.number_to_canonical(l_deduct_wage_tax_si_cont) --11886537
652 ,p_action_information6 => l_asg_start_date
653 ,p_action_information7 => l_asg_end_date
654 --,p_action_information8 => l_Labour_Discount --11886537
655 ,p_action_information8 => fnd_number.number_to_canonical(l_Labour_Discount) --11886537
656 ,p_action_information9 => l_Wage_Tax_Discount
657 ,p_action_information10 => l_Wage_Tax_Table_Code
658 ,p_action_information11 => l_Income_Code
659 ,p_action_information12 => l_Special_Indicator
660 ,p_action_information13 => l_Amount_Special_Indicator
661 ,p_action_information14 => l_SI_Insured_Flag
662 ,p_action_information15 => fnd_number.number_to_canonical(l_ZVW_Contribution)
663 ,p_action_information16 => fnd_number.number_to_canonical(l_Net_Expense_Allowance)
664 ,p_action_information17 => fnd_number.number_to_canonical(l_Private_Use_Car)
665 --,p_action_information18 => l_taxable_income --11886537
666 ,p_action_information18 => fnd_number.number_to_canonical(l_taxable_income) --11886537
667 ,p_action_information19 => fnd_number.number_to_canonical(l_ZVW_Basis)
668 ,p_action_information20 => fnd_number.number_to_canonical(l_Value_Private_Use_Car)
669 ,p_action_information21 => fnd_number.number_to_canonical(l_LSS_Saved_Amount)
670 ,p_action_information22 => fnd_number.number_to_canonical(l_Employer_Part_Child_Care)
671 ,p_action_information23 => fnd_number.number_to_canonical(l_Allowance_On_Disability)
672 ,p_action_information24 => fnd_number.number_to_canonical(l_Applied_LCLD)
673 ,p_action_information25 => l_User_Bal_String);
674
675 END IF;
676 end if;
677
678 end if;
679
680 hr_utility.set_location('l_action_info_id'||l_action_info_id,450);
681 hr_utility.set_location('l_ovn'||l_ovn,450);
682
683 if g_debug then
684 hr_utility.set_location('l_action_info_id'||l_action_info_id,450);
685 hr_utility.set_location('l_ovn'||l_ovn,450);
686 end if;
687
688 END LOOP;
689
690 hr_utility.set_location('Exiting ASSIGNMENT_ACTION_CODE',650);
691
692 END ASSIGNMENT_ACTION_CODE;
693
694
695
696 /*-------------------------------------------------------------------------------
697 |Name : ARCHIVE_INIT |
698 |Type : Procedure |
699 |Description : Initialization Code for Archiver |
700 -------------------------------------------------------------------------------*/
701 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER) IS
702
703 BEGIN
704 if g_debug then
705 hr_utility.set_location('Entering Archive Init',600);
706 hr_utility.set_location('Leaving Archive Init',700);
707 end if;
708
709 END ARCHIVE_INIT;
710
711
712
713 /*-------------------------------------------------------------------------------
714 |Name : ARCHIVE_CODE |
715 |Type : Procedure |
716 |Description : Archival code for archiver |
717 -------------------------------------------------------------------------------*/
718
719
720 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
721 ,p_effective_date IN DATE) IS
722
723
724 BEGIN
725 if g_debug then
726
727 hr_utility.set_location('Entering Archive Code',700);
728 hr_utility.set_location('Leaving Archive Code',700);
729 end if;
730
731 END ARCHIVE_CODE;
732
733 /*-----------------------------------------------------------------------------
734 |Name : get_max_assgt_act_id |
735 |Type : Function |
736 |Description: Function which returns the max. assignment_action_id for a given |
737 | assignment_id between a given start and end date |
738 -------------------------------------------------------------------------------*/
739
740 function get_max_assgt_act_id(p_assignment_id number
741 ,p_date_from date
742 ,p_date_to date)RETURN number IS
743
744 CURSOR csr_max_assgt_act_id IS
745 SELECT
746 --MAX(assignment_action_id) --11868538
747 fnd_number.canonical_to_number(substr(max(lpad(NVL(paa.action_sequence,0),15,'0')||paa.assignment_action_id),16)) Max_Asg_Act
748 from pay_assignment_actions paa
749 ,pay_payroll_actions ppa
750 ,pay_payrolls_f ppf
751 where paa.payroll_action_id =ppa.payroll_action_id
752 and paa.assignment_id = p_assignment_id
753 --11854614
754 --and ppa.date_earned between p_date_from and p_date_to
755 and ppa.payroll_id = ppf.payroll_id
756 and (ppa.date_earned+ppf.pay_date_offset) between p_date_from and p_date_to
757 --11854614
758 and ppa.action_type in ('R','B','Q','I','V')
759 and ppa.action_status = 'C'; --11868538
760
761 l_max_assgt_act_id number;
762
763 BEGIN
764
765 if g_debug then
766 hr_utility.set_location('Entering get_max_assgt_act_id',700);
767 end if;
768
769 OPEN csr_max_assgt_act_id;
770 FETCH csr_max_assgt_act_id into l_max_assgt_act_id;
771 CLOSE csr_max_assgt_act_id;
772
773 if g_debug then
774 hr_utility.set_location('l_max_assgt_act_id'||l_max_assgt_act_id,450);
775 hr_utility.set_location('Exiting get_max_assgt_act_id',700);
776 end if;
777
778 return l_max_assgt_act_id;
779
780 END get_max_assgt_act_id;
781
782
783 /*-----------------------------------------------------------------------------
784 |Name : get_context_id |
785 |Type : Function |
786 |Description: Function which returns the context id for a given context neme |
787 -------------------------------------------------------------------------------*/
788
789 function get_context_id(p_context_name VARCHAR2)return number IS
790
791 CURSOR csr_get_context_id IS
792 SELECT context_id
793 FROM ff_contexts ff
794 WHERE ff.context_name = p_context_name;
795
796 l_context_id number;
797
798 BEGIN
799
800 if g_debug then
801 hr_utility.set_location('Entering get_context_id',700);
802 end if;
803
804 OPEN csr_get_context_id;
805 FETCH csr_get_context_id into l_context_id;
806 CLOSE csr_get_context_id;
807
808 if g_debug then
809 hr_utility.set_location('l_context_id'||l_context_id,700);
810 hr_utility.set_location('Exiting get_context_id',700);
811 end if;
812
813 return l_context_id;
814
815
816 END get_context_id;
817
818
819
820 /*-----------------------------------------------------------------------------
821 |Name : get_wage |
822 |Type : Function |
823 |Description: Function which returns the wage for a given assignment action |
824 -------------------------------------------------------------------------------*/
825
826 function get_wage(p_assgt_act_id number)RETURN number IS
827 l_context_id number;
828 l_sum_of_balances number;
829 l_balance_value number;
830 l_defined_balance_id number;
831 l_pre_tax_ded number;
832 l_retro_pre_tax_ded number;
833 l_wage number;
834
835 BEGIN
836 if g_debug then
837 hr_utility.set_location('Entering get_wage',800);
838 end if;
839
840 l_sum_of_balances:=0;
841 l_pre_tax_ded:=0;
842 l_retro_pre_tax_ded:=0;
843 l_context_id:=get_context_id('SOURCE_TEXT');
844
845
846 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SI_INCOME_STANDARD_TAX_ASG_YTD');
847 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
848 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
849
850 if g_debug then
851 hr_utility.set_location('SI_INCOME_STANDARD_TAX_ASG_YTD',425);
852 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
853 hr_utility.set_location('l_balance_value'||l_balance_value,425);
854 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
855 end if;
856
857 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SI_INCOME_SPECIAL_TAX_ASG_YTD');
858 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
859 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
860
861 if g_debug then
862 hr_utility.set_location('SI_INCOME_SPECIAL_TAX_ASG_YTD',425);
863 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
864 hr_utility.set_location('l_balance_value'||l_balance_value,425);
865 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
866 end if;
867
868 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('WAGE_IN_MONEY_STANDARD_TAX_ONLY_ASG_YTD');
869 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
870 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
871
872 if g_debug then
873 hr_utility.set_location('WAGE_IN_MONEY_STANDARD_TAX_ONLY_ASG_YTD',425);
874 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
875 hr_utility.set_location('l_balance_value'||l_balance_value,425);
876 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
877 end if;
878
879 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('WAGE_IN_MONEY_SPECIAL_TAX_ONLY_ASG_YTD');
880 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
881 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
882
883 if g_debug then
884 hr_utility.set_location('WAGE_IN_MONEY_SPECIAL_TAX_ONLY_ASG_YTD',425);
885 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
886 hr_utility.set_location('l_balance_value'||l_balance_value,425);
887 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
888 end if;
889
890 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('WAGE_IN_KIND_STANDARD_TAX_ONLY_ASG_YTD');
891 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
892 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
893
894 if g_debug then
895 hr_utility.set_location('WAGE_IN_KIND_STANDARD_TAX_ONLY_ASG_YTD',425);
896 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
897 hr_utility.set_location('l_balance_value'||l_balance_value,425);
898 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
899 end if;
900
901 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('WAGE_IN_KIND_SPECIAL_TAX_ONLY_ASG_YTD');
902 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
903 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
904
905
906 if g_debug then
907 hr_utility.set_location('WAGE_IN_KIND_SPECIAL_TAX_ONLY_ASG_YTD',425);
908 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
909 hr_utility.set_location('l_balance_value'||l_balance_value,425);
910 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
911 end if;
912
913 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SI_INCOME_STANDARD_TAX_ASG_YTD');
914 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
915 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
916
917
918 if g_debug then
919 hr_utility.set_location('RETRO_SI_INCOME_STANDARD_TAX_ASG_YTD',425);
920 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
921 hr_utility.set_location('l_balance_value'||l_balance_value,425);
922 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
923 end if;
924
925
926 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SI_INCOME_SPECIAL_TAX_ASG_YTD');
927 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
928 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
929
930
931 if g_debug then
932 hr_utility.set_location('RETRO_SI_INCOME_SPECIAL_TAX_ASG_YTD',425);
933 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
934 hr_utility.set_location('l_balance_value'||l_balance_value,425);
935 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
936 end if;
937
938
939 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_WAGE_IN_MONEY_STANDARD_TAX_ONLY_ASG_YTD');
940 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
941 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
942
943 if g_debug then
944 hr_utility.set_location('RETRO_WAGE_IN_MONEY_STANDARD_TAX_ONLY_ASG_YTD',425);
945 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
946 hr_utility.set_location('l_balance_value'||l_balance_value,425);
947 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
948 end if;
949
950
951 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_WAGE_IN_MONEY_SPECIAL_TAX_ONLY_ASG_YTD');
952 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
953 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
954
955
956 if g_debug then
957 hr_utility.set_location('RETRO_WAGE_IN_MONEY_SPECIAL_TAX_ONLY_ASG_YTD',425);
958 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
959 hr_utility.set_location('l_balance_value'||l_balance_value,425);
960 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
961 end if;
962
963
964 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_WAGE_IN_KIND_STANDARD_TAX_ONLY_ASG_YTD');
965 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
966 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
967
968
969 if g_debug then
970 hr_utility.set_location('RETRO_WAGE_IN_KIND_STANDARD_TAX_ONLY_ASG_YTD',425);
971 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
972 hr_utility.set_location('l_balance_value'||l_balance_value,425);
973 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
974 end if;
975
976 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_WAGE_IN_KIND_SPECIAL_TAX_ONLY_ASG_YTD');
977 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
978 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
979
980 if g_debug then
981 hr_utility.set_location('RETRO_WAGE_IN_KIND_SPECIAL_TAX_ONLY_ASG_YTD',425);
982 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
983 hr_utility.set_location('l_balance_value'||l_balance_value,425);
984 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
985 end if;
986
987
988 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_ZVW_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
989 IF l_context_id IS NULL then
990 l_balance_value:=0;
991 ELSE
992 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
993 END IF;
994 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
995
996 if g_debug then
997 hr_utility.set_location('EMPLOYER_ZVW_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
998 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
999 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1000 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1001 end if;
1002
1003
1004
1005 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_ZVW_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
1006 IF l_context_id IS NULL then
1007 l_balance_value:=0;
1008 ELSE
1009 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
1010 END IF;
1011 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1012
1013 if g_debug then
1014 hr_utility.set_location('EMPLOYER_ZVW_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
1015 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1016 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1017 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1018 end if;
1019
1020
1021 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_ZVW_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
1022 IF l_context_id IS NULL then
1023 l_balance_value:=0;
1024 ELSE
1025 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
1026 END IF;
1027 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1028
1029 if g_debug then
1030 hr_utility.set_location('RETRO_EMPLOYER_ZVW_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
1031 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1032 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1033 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1034 end if;
1035
1036
1037 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_ZVW_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
1038 IF l_context_id IS NULL then
1039 l_balance_value:=0;
1040 ELSE
1041 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
1042 END IF;
1043 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1044
1045 if g_debug then
1046 hr_utility.set_location('RETRO_EMPLOYER_ZVW_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
1047 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1048 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1049 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1050 end if;
1051
1052
1053 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1054 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1055 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1056
1057 if g_debug then
1058 hr_utility.set_location('EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1059 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1060 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1061 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1062 end if;
1063
1064 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1065 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1066 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1067
1068 if g_debug then
1069 hr_utility.set_location('RETRO_EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1070 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1071 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1072 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1073 end if;
1074
1075
1076 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1077 IF l_context_id IS NULL then
1078 l_balance_value:=0;
1079 ELSE
1080 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWE',null,null);
1081 END IF;
1082 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1083
1084 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1085 IF l_context_id IS NULL then
1086 l_balance_value:=0;
1087 ELSE
1088 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWA',null,null);
1089 END IF;
1090 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1091
1092
1093 if g_debug then
1094 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1095 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1096 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1097 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1098 end if;
1099
1100
1101 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1102 IF l_context_id IS NULL then
1103 l_balance_value:=0;
1104 ELSE
1105 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWE',null,null);
1106 END IF;
1107 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1108
1109
1110 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1111 IF l_context_id IS NULL then
1112 l_balance_value:=0;
1113 ELSE
1114 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWA',null,null);
1115 END IF;
1116 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1117
1118
1119 if g_debug then
1120 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1121 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1122 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1123 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1124 end if;
1125
1126
1127 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1128 IF l_context_id IS NULL then
1129 l_balance_value:=0;
1130 ELSE
1131 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWE',null,null);
1132 END IF;
1133 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1134
1135 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1136 IF l_context_id IS NULL then
1137 l_balance_value:=0;
1138 ELSE
1139 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWA',null,null);
1140 END IF;
1141 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1142
1143
1144
1145 if g_debug then
1146 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1147 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1148 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1149 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1150 end if;
1151
1152
1153 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1154 IF l_context_id IS NULL then
1155 l_balance_value:=0;
1156 ELSE
1157 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWE',null,null);
1158 END IF;
1159 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1160
1161
1162 if g_debug then
1163 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1164 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1165 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1166 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1167 end if;
1168
1169
1170 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1171 IF l_context_id IS NULL then
1172 l_balance_value:=0;
1173 ELSE
1174 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWA',null,null);
1175 END IF;
1176 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1177
1178
1179 if g_debug then
1180 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1181 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1182 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1183 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1184 end if;
1185
1186
1187 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('PRE_TAX_ONLY_DEDUCTIONS_ASG_YTD');
1188 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1189 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1190
1191 if g_debug then
1192 hr_utility.set_location('PRE_TAX_ONLY_DEDUCTIONS_ASG_YTD',425);
1193 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1194 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1195 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1196 end if;
1197
1198 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_PRE_TAX_ONLY_DEDUCTIONS_ASG_YTD');
1199 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1200 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1201
1202 if g_debug then
1203 hr_utility.set_location('RETRO_PRE_TAX_ONLY_DEDUCTIONS_ASG_YTD',425);
1204 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1205 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1206 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1207 end if;
1208
1209 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('STANDARD_TAX_REDUCTION_ASG_YTD');
1210 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1211 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1212
1213 if g_debug then
1214 hr_utility.set_location('STANDARD_TAX_REDUCTION_ASG_YTD',425);
1215 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1216 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1217 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1218 end if;
1219
1220 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SPECIAL_TAX_REDUCTION_ASG_YTD');
1221 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1222 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1223
1224 if g_debug then
1225 hr_utility.set_location('SPECIAL_TAX_REDUCTION_ASG_YTD',425);
1226 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1227 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1228 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1229 end if;
1230
1231 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAX_REDUCTION_ASG_YTD');
1232 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1233 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1234
1235 if g_debug then
1236 hr_utility.set_location('RETRO_STANDARD_TAX_REDUCTION_ASG_YTD',425);
1237 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1238 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1239 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1240 end if;
1241
1242 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SPECIAL_TAX_REDUCTION_ASG_YTD');
1243 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1244 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1245
1246 if g_debug then
1247 hr_utility.set_location('RETRO_SPECIAL_TAX_REDUCTION_ASG_YTD',425);
1248 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1249 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1250 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1251
1252 hr_utility.set_location('Exiting get_wage',950);
1253 end if;
1254
1255
1256 l_wage:=l_sum_of_balances;
1257
1258
1259 return l_wage;
1260
1261 END get_wage;
1262
1263
1264 /*-----------------------------------------------------------------------------
1265 |Name : get_taxable_income |
1266 |Type : Function |
1267 |Description: Function which returns the taxable income for a given assignment |
1268 | action |
1269 -------------------------------------------------------------------------------*/
1270
1271 function get_taxable_income(p_assgt_act_id number)RETURN number
1272
1273 IS
1274 l_sum_of_balances number;
1275 l_balance_value number;
1276 l_defined_balance_id number;
1277 l_taxable_income number;
1278
1279 BEGIN
1280 if g_debug then
1281 hr_utility.set_location('Entering get_taxable_income',800);
1282 end if;
1283
1284 l_sum_of_balances:=0;
1285
1286
1287 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('STANDARD_TAXABLE_INCOME_ASG_YTD');
1288 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1289 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1290
1291 if g_debug then
1292 hr_utility.set_location('STANDARD_TAXABLE_INCOME_ASG_YTD',425);
1293 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1294 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1295 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1296 end if;
1297
1298
1299 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SPECIAL_TAXABLE_INCOME_ASG_YTD');
1300 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1301 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1302
1303 if g_debug then
1304 hr_utility.set_location('SPECIAL_TAXABLE_INCOME_ASG_YTD',425);
1305 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1306 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1307 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1308 end if;
1309
1310
1311 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAXABLE_INCOME_CURRENT_QUARTER_ASG_YTD');
1312 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1313 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1314
1315 if g_debug then
1316 hr_utility.set_location('RETRO_STANDARD_TAXABLE_INCOME_CURRENT_QUARTER_ASG_YTD',425);
1317 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1318 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1319 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1320 end if;
1321
1322
1323 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAXABLE_INCOME_ASG_YTD');
1324 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1325 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1326
1327 if g_debug then
1328 hr_utility.set_location('RETRO_STANDARD_TAXABLE_INCOME_ASG_YTD',425);
1329 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1330 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1331 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1332 end if;
1333
1334
1335 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SPECIAL_TAXABLE_INCOME_ASG_YTD');
1336 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1337 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1338
1339 if g_debug then
1340 hr_utility.set_location('RETRO_SPECIAL_TAXABLE_INCOME_ASG_YTD',425);
1341 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1342 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1343 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1344 end if;
1345
1346
1347 l_taxable_income:=l_sum_of_balances;
1348
1349
1350 return l_taxable_income;
1351
1352 END get_taxable_income;
1353
1354
1355 /*-----------------------------------------------------------------------------
1356 |Name : get_deduct_wage_tax_si_cont |
1357 |Type : Function |
1358 |Description: Function which returns the deduct_wage_tax value |
1359 | for a given assignment action |
1360 -------------------------------------------------------------------------------*/
1361
1362 function get_deduct_wage_tax_si_cont(p_assgt_act_id number) return number IS
1363
1364 l_wt_and_ni_cont number;
1365 l_balance_value number;
1366 l_defined_balance_id number;
1367 l_deduct_wage_tax_si_cont number;
1368
1369 BEGIN
1370
1371 if g_debug then
1372 hr_utility.set_location('Entering get_deduct_wage_tax_si_cont',1000);
1373 end if;
1374
1375 l_wt_and_ni_cont:=0;
1376
1377 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('STANDARD_TAX_DEDUCTION_ASG_YTD');
1378 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1379 l_wt_and_ni_cont:= l_wt_and_ni_cont + l_balance_value;
1380
1381
1382 if g_debug then
1383 hr_utility.set_location('STANDARD_TAX_DEDUCTION_ASG_YTD',425);
1384 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1385 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1386 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1387 end if;
1388
1389
1390 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SPECIAL_TAX_DEDUCTION_ASG_YTD');
1391 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1392 l_wt_and_ni_cont:= l_wt_and_ni_cont + l_balance_value;
1393
1394
1395 if g_debug then
1396 hr_utility.set_location('SPECIAL_TAX_DEDUCTION_ASG_YTD',425);
1397 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1398 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1399 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1400 end if;
1401
1402
1403 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAX_DEDUCTION_ASG_YTD');
1404 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1405 l_wt_and_ni_cont:= l_wt_and_ni_cont + l_balance_value;
1406
1407
1408 if g_debug then
1409 hr_utility.set_location('RETRO_STANDARD_TAX_DEDUCTION_ASG_YTD',425);
1410 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1411 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1412 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1413 end if;
1414
1415
1416 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SPECIAL_TAX_DEDUCTION_ASG_YTD');
1417 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1418 l_wt_and_ni_cont:= l_wt_and_ni_cont + l_balance_value;
1419
1420
1421 if g_debug then
1422 hr_utility.set_location('RETRO_SPECIAL_TAX_DEDUCTION_ASG_YTD',425);
1423 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1424 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1425 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1426 END IF;
1427
1428
1429 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAX_DEDUCTION_CURRENT_QUARTER_ASG_YTD');
1430 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1431 l_wt_and_ni_cont:= l_wt_and_ni_cont + l_balance_value;
1432
1433
1434 if g_debug then
1435 hr_utility.set_location('RETRO_STANDARD_TAX_DEDUCTION_CURRENT_QUARTER_ASG_YTD',425);
1436 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1437 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1438 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1439 END IF;
1440
1441
1442 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('STANDARD_TAX_CORRECTION_ASG_YTD');
1443 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1444 l_wt_and_ni_cont:= l_wt_and_ni_cont + l_balance_value;
1445
1446
1447 if g_debug then
1448 hr_utility.set_location('STANDARD_TAX_CORRECTION_ASG_YTD',425);
1449 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1450 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1451 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1452 end if;
1453
1454
1455 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SPECIAL_TAX_CORRECTION_ASG_YTD');
1456 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1457 l_wt_and_ni_cont:= l_wt_and_ni_cont + l_balance_value;
1458
1459
1460 if g_debug then
1461 hr_utility.set_location('SPECIAL_TAX_CORRECTION_ASG_YTD',425);
1462 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1463 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1464 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1465 end if;
1466
1467
1468 --l_deduct_wage_tax_si_cont:=ceil(l_wt_and_ni_cont); --11886537
1469 l_deduct_wage_tax_si_cont:=l_wt_and_ni_cont; --11886537
1470
1471 if g_debug then
1472 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1473 hr_utility.set_location('Exiting get_deduct_wage_tax_si_cont',1050);
1474 end if;
1475 return l_deduct_wage_tax_si_cont;
1476
1477 end get_deduct_wage_tax_si_cont;
1478
1479
1480 /*-----------------------------------------------------------------------------
1481 |Name : get_labour_discount |
1482 |Type : Function |
1483 |Description: Function which returns the labour discount value |
1484 | for a given assignment action |
1485 -------------------------------------------------------------------------------*/
1486
1487 function get_labour_discount(p_assgt_act_id number) return number IS
1488
1489 l_labour_discount number;
1490 l_balance_value number;
1491 l_defined_balance_id number;
1492
1493 BEGIN
1494 if g_debug then
1495 hr_utility.set_location('Entering get_labour_discount',1050);
1496 end if;
1497
1498 l_labour_discount:=0;
1499
1500 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('LABOUR_TAX_REDUCTION_ASG_YTD');
1501 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1502 l_labour_discount:= l_labour_discount + l_balance_value;
1503
1504
1505 if g_debug then
1506 hr_utility.set_location('LABOUR_TAX_REDUCTION_ASG_YTD',425);
1507 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1508 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1509 hr_utility.set_location('l_labour_discount'||l_labour_discount,425);
1510 end if;
1511
1512 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_LABOUR_TAX_REDUCTION_ASG_YTD');
1513 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1514 l_labour_discount:= l_labour_discount + l_balance_value;
1515
1516 if g_debug then
1517 hr_utility.set_location('RETRO_LABOUR_TAX_REDUCTION_ASG_YTD',425);
1518 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1519 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1520 hr_utility.set_location('l_labour_discount'||l_labour_discount,425);
1521 end if;
1522
1523 --l_labour_discount:= ceil(l_labour_discount); --11886537
1524
1525
1526 if g_debug then
1527 hr_utility.set_location('l_labour_discount'||l_labour_discount,425);
1528 hr_utility.set_location('Exiting get_labour_discount',1050);
1529 end if;
1530
1531 return l_labour_discount;
1532
1533 end get_labour_discount;
1534
1535
1536 /*-----------------------------------------------------------------------------
1537 |Name : get_ZFW_PHI_contributions |
1538 |Type : Function |
1539 |Description: Function which returns the ZFW PHI contributions |
1540 -------------------------------------------------------------------------------*/
1541
1542 function get_ZFW_PHI_contributions(p_assgt_act_id number) return number IS
1543
1544 l_ZFW_PHI_contribution number;
1545 l_context_id number;
1546 l_balance_value number;
1547 l_defined_balance_id number;
1548
1549 BEGIN
1550
1551 hr_utility.set_location('Entering get_ZFW_PHI_Contribution',1050);
1552
1553 l_ZFW_PHI_contribution:=0;
1554
1555 l_context_id:=get_context_id('SOURCE_TEXT');
1556
1557
1558
1559 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1560 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1561 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1562
1563
1564 if g_debug then
1565 hr_utility.set_location('EMPLOYEE_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1566 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1567 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1568 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1569 end if;
1570
1571
1572
1573 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1574 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1575 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1576
1577
1578 if g_debug then
1579 hr_utility.set_location('EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1580 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1581 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1582 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1583 end if;
1584
1585
1586
1587 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1588 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1589 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1590
1591
1592 if g_debug then
1593 hr_utility.set_location('RETRO_EMPLOYEE_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1594 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1595 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1596 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1597 end if;
1598
1599
1600 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1601 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1602 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1603
1604
1605 if g_debug then
1606 hr_utility.set_location('RETRO_EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1607 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1608 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1609 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1610 end if;
1611
1612
1613
1614 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_IZA_CONTRIBUTION_ASG_YTD');
1615 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1616 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1617
1618
1619 if g_debug then
1620 hr_utility.set_location('EMPLOYEE_IZA_CONTRIBUTION_ASG_YTD',425);
1621 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1622 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1623 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1624 end if;
1625
1626
1627 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
1628 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1629 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1630
1631
1632 if g_debug then
1633 hr_utility.set_location('EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
1634 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1635 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1636 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1637 end if;
1638
1639
1640 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
1641 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1642 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1643
1644
1645 if g_debug then
1646 hr_utility.set_location('EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
1647 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1648 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1649 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1650 end if;
1651
1652
1653 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_IZA_CONTRIBUTION_NON_TAXABLE_ASG_YTD');
1654 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1655 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1656
1657
1658 if g_debug then
1659 hr_utility.set_location('EMPLOYER_IZA_CONTRIBUTION_NON_TAXABLE_ASG_YTD',425);
1660 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1661 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1662 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1663 end if;
1664
1665
1666 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_IZA_CONTRIBUTION_ASG_YTD');
1667 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1668 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1669
1670
1671 if g_debug then
1672 hr_utility.set_location('RETRO_EMPLOYEE_IZA_CONTRIBUTION_ASG_YTD',425);
1673 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1674 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1675 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1676 end if;
1677
1678
1679 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
1680 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1681 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1682
1683
1684 if g_debug then
1685 hr_utility.set_location('RETRO_EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
1686 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1687 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1688 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1689 end if;
1690
1691
1692 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
1693 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1694 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1695
1696
1697 if g_debug then
1698 hr_utility.set_location('RETRO_EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
1699 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1700 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1701 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1702 end if;
1703
1704 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_IZA_CONTRIBUTION_NON_TAXABLE_ASG_YTD');
1705 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1706 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1707
1708
1709 if g_debug then
1710 hr_utility.set_location('RETRO_EMPLOYER_IZA_CONTRIBUTION_NON_TAXABLE_ASG_YTD',425);
1711 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1712 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1713 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1714 end if;
1715
1716
1717
1718 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD');
1719 IF l_context_id IS NULL then
1720 l_balance_value:=0;
1721 ELSE
1722 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1723 END IF;
1724 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1725
1726
1727 if g_debug then
1728 hr_utility.set_location('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD',425);
1729 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1730 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1731 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1732 end if;
1733
1734
1735
1736 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1737 IF l_context_id IS NULL then
1738 l_balance_value:=0;
1739 ELSE
1740 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1741 END IF;
1742 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1743
1744 if g_debug then
1745 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1746 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1747 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1748 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1749 end if;
1750
1751
1752
1753 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1754 IF l_context_id IS NULL then
1755 l_balance_value:=0;
1756 ELSE
1757 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1758 END IF;
1759 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1760
1761
1762 if g_debug then
1763 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1764 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1765 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1766 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1767 end if;
1768
1769
1770
1771 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD');
1772 IF l_context_id IS NULL then
1773 l_balance_value:=0;
1774 ELSE
1775 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1776 END IF;
1777 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1778
1779
1780 if g_debug then
1781 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD',425);
1782 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1783 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1784 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1785 end if;
1786
1787
1788
1789 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_SI_CONTRIBUTION_ASG_SIT_YTD');
1790 IF l_context_id IS NULL then
1791 l_balance_value:=0;
1792 ELSE
1793 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1794 END IF;
1795 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1796
1797
1798 if g_debug then
1799 hr_utility.set_location('EMPLOYER_SI_CONTRIBUTION_ASG_SIT_YTD',425);
1800 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1801 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1802 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1803 end if;
1804
1805
1806
1807 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1808 IF l_context_id IS NULL then
1809 l_balance_value:=0;
1810 ELSE
1811 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1812 END IF;
1813 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1814
1815
1816 if g_debug then
1817 hr_utility.set_location('EMPLOYER_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1818 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1819 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1820 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1821 end if;
1822
1823
1824
1825 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1826 IF l_context_id IS NULL then
1827 l_balance_value:=0;
1828 ELSE
1829 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1830 END IF;
1831 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1832
1833
1834 if g_debug then
1835 hr_utility.set_location('EMPLOYER_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1836 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1837 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1838 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1839 end if;
1840
1841
1842
1843 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD');
1844 IF l_context_id IS NULL then
1845 l_balance_value:=0;
1846 ELSE
1847 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1848 END IF;
1849 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1850
1851 if g_debug then
1852 hr_utility.set_location('EMPLOYER_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD',425);
1853 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1854 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1855 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1856 end if;
1857
1858
1859 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD');
1860 IF l_context_id IS NULL then
1861 l_balance_value:=0;
1862 ELSE
1863 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1864 END IF;
1865 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1866
1867
1868 if g_debug then
1869 hr_utility.set_location('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD',425);
1870 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1871 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1872 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1873 end if;
1874
1875
1876
1877
1878 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1879 IF l_context_id IS NULL then
1880 l_balance_value:=0;
1881 ELSE
1882 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1883 END IF;
1884 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1885
1886
1887 if g_debug then
1888 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1889 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1890 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1891 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1892 end if;
1893
1894
1895 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1896 IF l_context_id IS NULL then
1897 l_balance_value:=0;
1898 ELSE
1899 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1900 END IF;
1901 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1902
1903
1904 if g_debug then
1905 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1906 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1907 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1908 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1909 end if;
1910
1911
1912
1913 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD');
1914 IF l_context_id IS NULL then
1915 l_balance_value:=0;
1916 ELSE
1917 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1918 END IF;
1919 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1920
1921
1922 if g_debug then
1923 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD',425);
1924 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1925 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1926 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1927 end if;
1928
1929
1930 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_SI_CONTRIBUTION_ASG_SIT_YTD');
1931 IF l_context_id IS NULL then
1932 l_balance_value:=0;
1933 ELSE
1934 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1935 END IF;
1936 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1937
1938
1939 if g_debug then
1940 hr_utility.set_location('RETRO_EMPLOYER_SI_CONTRIBUTION_ASG_SIT_YTD',425);
1941 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1942 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1943 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1944 end if;
1945
1946
1947
1948 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1949 IF l_context_id IS NULL then
1950 l_balance_value:=0;
1951 ELSE
1952 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1953 END IF;
1954 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1955
1956
1957 if g_debug then
1958 hr_utility.set_location('RETRO_EMPLOYER_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1959 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1960 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1961 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1962 end if;
1963
1964
1965
1966 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1967 IF l_context_id IS NULL then
1968 l_balance_value:=0;
1969 ELSE
1970 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1971 END IF;
1972 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1973
1974
1975 if g_debug then
1976 hr_utility.set_location('RETRO_EMPLOYER_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1977 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1978 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1979 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1980 end if;
1981
1982
1983
1984 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD');
1985 IF l_context_id IS NULL then
1986 l_balance_value:=0;
1987 ELSE
1988 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1989 END IF;
1990 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1991
1992
1993 if g_debug then
1994 hr_utility.set_location('RETRO_EMPLOYER_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD',425);
1995 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1996 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1997 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1998 end if;
1999
2000
2001 l_ZFW_PHI_Contribution:=round(l_ZFW_PHI_Contribution,2);
2002
2003 return l_ZFW_PHI_Contribution;
2004
2005
2006 END get_ZFW_PHI_contributions;
2007
2008
2009 /*-----------------------------------------------------------------------------
2010 |Name : get_special_indicators |
2011 |Type : Procedure |
2012 |Description: Procedure which returns the special indicators string |
2013 -------------------------------------------------------------------------------*/
2014
2015 Procedure get_special_indicators(p_assgt_act_id in number
2016 ,p_assignment_id in number
2017 ,p_tax_year_start_date in date
2018 ,p_tax_year_end_date in date
2019 ,p_special_indicator out nocopy varchar2
2020 ,p_Amount_Special_indicator out nocopy varchar2) IS
2021
2022 cursor csr_tax_year_assgn_act_id is
2023 select paa.assignment_action_id,ppa.date_earned
2024 from pay_assignment_actions paa
2025 ,pay_payroll_actions ppa
2026 ,pay_payrolls_f ppf
2027 where
2028 paa.assignment_id = p_assignment_id and
2029 ppa.payroll_action_id = paa.payroll_action_id and
2030 --11854614
2031 --ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date
2032 ppa.payroll_id = ppf.payroll_id and
2033 (ppa.date_earned+ppf.pay_date_offset) between p_tax_year_start_date and p_tax_year_end_date
2034 --11854614
2035 and ppa.action_type in ('R','Q')
2036 and ppa.action_status = 'C';
2037
2038 cursor csr_run_result_id(lp_assignment_action_id number, lp_element_type_id number) is
2039 select prr.run_result_id
2040 from pay_run_results prr
2041 where
2042 prr.element_type_id=lp_element_type_id and
2043 prr.assignment_action_id=lp_assignment_action_id;
2044
2045 l_balance_value number;
2046 l_defined_balance_id number;
2047 l_spl_indicator1 varchar2(2);
2048 l_spl_indicator1_amount varchar2(9);
2049 l_spl_indicator2 varchar2(2);
2050 l_spl_indicator2_amount varchar2(9);
2051 l_spl_indicator3 varchar2(2);
2052 l_spl_indicator3_amount varchar2(9);
2053 l_spl_indicator4 varchar2(2);
2054 l_spl_indicator4_amount varchar2(9);
2055 l_spl_indicator5 varchar2(2);
2056 l_spl_indicator5_amount varchar2(9);
2057 l_spl_indicator6 varchar2(2);
2058 l_spl_indicator6_amount varchar2(9);
2059 l_comp_car varchar2(1);
2060 l_element_type_id number;
2061 l_input_value_id number;
2062 l_run_result_id number;
2063 l_run_result_value varchar2(255);
2064 l_eff_date date;
2065
2066 BEGIN
2067
2068 --if g_debug then
2069 hr_utility.set_location('Entering get_special_indicators',1200);
2070 --end if;
2071
2072 /*for csr_tax_year_assgn_act_id_rec in csr_tax_year_assgn_act_id
2073 loop
2074 l_element_type_id:=pay_nl_general.get_element_type_id('Standard Tax Deduction',csr_tax_year_assgn_act_id_rec.date_earned);
2075 l_input_value_id:=pay_nl_general.get_input_value_id(l_element_type_id,'Special Indicators',csr_tax_year_assgn_act_id_rec.date_earned);
2076
2077 OPEN csr_run_result_id(csr_tax_year_assgn_act_id_rec.assignment_action_id,l_element_type_id);
2078 FETCH csr_run_result_id into l_run_result_id;
2079 CLOSE csr_run_result_id;
2080
2081 l_run_result_value:=pay_nl_general.get_run_result_value(csr_tax_year_assgn_act_id_rec.assignment_action_id,l_element_type_id,l_input_value_id,l_run_result_id,'C');
2082
2083 IF substr (l_run_result_value, 1, 2) = '01' OR
2084 substr (l_run_result_value, 3, 2) = '01' OR
2085 substr (l_run_result_value, 5, 2) = '01' OR
2086 substr (l_run_result_value, 7, 2) = '01' OR
2087 substr (l_run_result_value, 9, 2) = '01' OR
2088 substr (l_run_result_value, 11, 2) = '01' OR
2089 substr (l_run_result_value, 13, 2) = '01' OR
2090 substr (l_run_result_value, 15, 2) = '01' OR
2091 substr (l_run_result_value, 17, 2) = '01' OR
2092 substr (l_run_result_value, 19, 2) = '01' OR
2093 substr (l_run_result_value, 21, 2) = '01' OR
2094 substr (l_run_result_value, 23, 2) = '01' OR
2095 substr (l_run_result_value, 25, 2) = '01' THEN
2096
2097 l_comp_car_spl_indicator := 'Y';
2098 exit;
2099 ELSE
2100 l_comp_car_spl_indicator := 'N';
2101 END IF;
2102 end loop;*/
2103
2104 BEGIN
2105
2106 select ppa.date_earned
2107 into l_eff_date
2108 from pay_payroll_actions ppa,
2109 pay_assignment_actions paa
2110 where paa.assignment_action_id = p_assgt_act_id
2111 and ppa.payroll_action_id = paa.payroll_action_id;
2112
2113 EXCEPTION
2114
2115 WHEN NO_DATA_FOUND
2116 THEN null;
2117
2118 WHEN OTHERS
2119 THEN null;
2120
2121 END;
2122 --if g_debug then
2123 hr_utility.set_location('l_eff_date - '||l_eff_date,427);
2124 --end if;
2125
2126 BEGIN
2127
2128 SELECT peev.screen_entry_value
2129 INTO l_comp_car
2130 FROM pay_element_types_f pet
2131 ,pay_input_values_f piv
2132 ,pay_element_entries_f peef
2133 ,pay_element_entry_values_f peev
2134 WHERE pet.element_name = 'Company Car Private Usage'
2135 AND pet.element_type_id = piv.element_type_id
2136 AND piv.name = 'Code Usage'
2137 AND pet.legislation_code = 'NL'
2138 AND piv.legislation_code = 'NL'
2139 AND peef.assignment_id = p_assignment_id
2140 AND peef.element_entry_id = peev.element_entry_id
2141 AND peef.element_type_id = pet.element_type_id
2142 AND peev.input_value_id = piv.input_value_id
2143 AND l_eff_date BETWEEN piv.effective_start_date
2144 AND piv.effective_end_date
2145 AND l_eff_date BETWEEN pet.effective_start_date
2146 AND pet.effective_end_date
2147 AND l_eff_date BETWEEN peev.effective_start_date
2148 AND peev.effective_end_date
2149 AND l_eff_date BETWEEN peef.effective_start_date
2150 AND peef.effective_end_date;
2151
2152 EXCEPTION
2153
2154 WHEN NO_DATA_FOUND
2155 THEN l_comp_car:=' ';
2156
2157 WHEN OTHERS
2158 THEN l_comp_car:=' ';
2159
2160 END;
2161 --if g_debug then
2162 hr_utility.set_location('l_comp_car - '||l_comp_car,427);
2163 --end if;
2164
2165 g_debug:=true;
2166
2167 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_TRAVEL_ALLOWANCE_ASG_YTD');
2168 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2169 if l_balance_value <> 0 then
2170 l_spl_indicator1 := '04';
2171 l_spl_indicator1_amount := l_balance_value;
2172 else
2173 l_spl_indicator1 := '00';
2174 l_spl_indicator1_amount := '000000';
2175 end if;
2176
2177 if g_debug then
2178 hr_utility.set_location('TAX_TRAVEL_ALLOWANCE_ASG_YTD',427);
2179 hr_utility.set_location('l_spl_indicator1'||l_spl_indicator1,427);
2180 hr_utility.set_location('l_spl_indicator1_amount'||l_spl_indicator1_amount,427);
2181 end if;
2182
2183 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_SEA_DAYS_DISCOUNT_ASG_YTD');
2184 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2185 if l_balance_value <> 0 then
2186 l_spl_indicator2 := '17';
2187 l_spl_indicator2_amount := l_balance_value;
2188 else
2189 l_spl_indicator2 := '00';
2190 l_spl_indicator2_amount := '000000';
2191 end if;
2192
2193 if g_debug then
2194 hr_utility.set_location('l_spl_indicator2'||l_spl_indicator2,427);
2195 hr_utility.set_location('l_spl_indicator2_amount'||l_spl_indicator2_amount,427);
2196 hr_utility.set_location('TAX_SEA_DAYS_DISCOUNT_ASG_YTD',427);
2197 end if;
2198
2199
2200 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_ABW_ALLOWANCE_ASG_YTD');
2201 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2202 if l_balance_value <> 0 then
2203 l_spl_indicator3 := '25';
2204 l_spl_indicator3_amount := l_balance_value;
2205 else
2206 l_spl_indicator3 := '00';
2207 l_spl_indicator3_amount := '000000';
2208 end if;
2209
2210
2211 if g_debug then
2212 hr_utility.set_location('TAX_ABW_ALLOWANCE_ASG_YTD',427);
2213 hr_utility.set_location('l_spl_indicator3'||l_spl_indicator3,427);
2214 hr_utility.set_location('l_spl_indicator3_amount'||l_spl_indicator3_amount,427);
2215 end if;
2216
2217
2218 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_ABW_ALLOWANCE_STOPPAGE_ASG_YTD');
2219 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2220 if l_balance_value <> 0 then
2221 l_spl_indicator4 := '26';
2222 l_spl_indicator4_amount := l_balance_value;
2223 else
2224 l_spl_indicator4 := '00';
2225 l_spl_indicator4_amount := '000000';
2226 end if;
2227
2228
2229 if g_debug then
2230 hr_utility.set_location('TAX_ABW_ALLOWANCE_STOPPAGE_ASG_YTD',427);
2231 hr_utility.set_location('l_spl_indicator4'||l_spl_indicator4,427);
2232 hr_utility.set_location('l_spl_indicator4_amount'||l_spl_indicator4_amount,427);
2233 end if;
2234
2235
2236 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_WAO_ALLOWANCE_ASG_YTD');
2237 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2238 if l_balance_value <> 0 then
2239 l_spl_indicator5 := '61';
2240 l_spl_indicator5_amount := l_balance_value;
2241 else
2242 l_spl_indicator5 := '00';
2243 l_spl_indicator5_amount := '000000';
2244 end if;
2245
2246 if g_debug then
2247 hr_utility.set_location('TAX_WAO_ALLOWANCE_ASG_YTD',427);
2248 hr_utility.set_location('l_spl_indicator5'||l_spl_indicator5,427);
2249 hr_utility.set_location('l_spl_indicator5_amount'||l_spl_indicator5_amount,427);
2250 end if;
2251
2252 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_TOTAL_ZFW_CONTRIBUTION_ASG_YTD');
2253 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2254 if l_balance_value <> 0 then
2255 l_spl_indicator6 := '64';
2256 l_spl_indicator6_amount := l_balance_value;
2257 else
2258 l_spl_indicator6 := '00';
2259 l_spl_indicator6_amount := '000000';
2260 end if;
2261
2262 if g_debug then
2263 hr_utility.set_location('TAX_TOTAL_ZFW_CONTRIBUTION_ASG_YTD',427);
2264 hr_utility.set_location('l_spl_indicator6'||l_spl_indicator6,427);
2265 hr_utility.set_location('l_spl_indicator6_amount'||l_spl_indicator6_amount,427);
2266 end if;
2267
2268
2269 p_special_indicator:=l_spl_indicator1||l_spl_indicator2||l_spl_indicator3||l_spl_indicator4||l_spl_indicator5||l_spl_indicator6 || l_comp_car;
2270
2271 if g_debug then
2272 hr_utility.set_location('p_special_indicator'||p_special_indicator,427);
2273 end if;
2274
2275 l_spl_indicator1_amount:=FLOOR(l_spl_indicator1_amount);
2276 if l_spl_indicator1_amount < 0 then
2277 l_spl_indicator1_amount:='-'||lpad(l_spl_indicator1_amount*(-1),5,0);
2278 else
2279 l_spl_indicator1_amount:=lpad(l_spl_indicator1_amount,6,0);
2280 end if;
2281
2282 if g_debug then
2283 hr_utility.set_location('l_spl_indicator1_amount'||l_spl_indicator1_amount,427);
2284 end if;
2285
2286 l_spl_indicator2_amount:=FLOOR(l_spl_indicator2_amount);
2287 if l_spl_indicator2_amount < 0 then
2288 l_spl_indicator2_amount:='-'||lpad(l_spl_indicator2_amount*(-1),5,0);
2289 else
2290 l_spl_indicator2_amount:=lpad(l_spl_indicator2_amount,6,0);
2291 end if;
2292
2293 if g_debug then
2294 hr_utility.set_location('l_spl_indicator2_amount'||l_spl_indicator2_amount,427);
2295 end if;
2296
2297 l_spl_indicator3_amount:=FLOOR(l_spl_indicator3_amount);
2298 if l_spl_indicator3_amount < 0 then
2299 l_spl_indicator3_amount:='-'||lpad(l_spl_indicator3_amount*(-1),5,0);
2300 else
2301 l_spl_indicator3_amount:=lpad(l_spl_indicator3_amount,6,0);
2302 end if;
2303
2304 if g_debug then
2305 hr_utility.set_location('l_spl_indicator3_amount'||l_spl_indicator3_amount,427);
2306 end if;
2307
2308 l_spl_indicator4_amount:=FLOOR(l_spl_indicator4_amount);
2309 if l_spl_indicator4_amount < 0 then
2310 l_spl_indicator4_amount:='-'||lpad(l_spl_indicator4_amount*(-1),5,0);
2311 else
2312 l_spl_indicator4_amount:=lpad(l_spl_indicator4_amount,6,0);
2313 end if;
2314
2315 if g_debug then
2316 hr_utility.set_location('l_spl_indicator4_amount'||l_spl_indicator4_amount,427);
2317 end if;
2318
2319 l_spl_indicator5_amount:=FLOOR(l_spl_indicator5_amount);
2320 if l_spl_indicator5_amount < 0 then
2321 l_spl_indicator5_amount:='-'||lpad(l_spl_indicator5_amount*(-1),5,0);
2322 else
2323 l_spl_indicator5_amount:=lpad(l_spl_indicator5_amount,6,0);
2324 end if;
2325
2326 if g_debug then
2327 hr_utility.set_location('l_spl_indicator5_amount'||l_spl_indicator5_amount,427);
2328 end if;
2329
2330 l_spl_indicator6_amount:=FLOOR(l_spl_indicator6_amount);
2331 if l_spl_indicator6_amount < 0 then
2332 l_spl_indicator6_amount:='-'||lpad(l_spl_indicator6_amount*(-1),5,0);
2333 else
2334 l_spl_indicator6_amount:=lpad(l_spl_indicator6_amount,6,0);
2335 end if;
2336
2337 if g_debug then
2338 hr_utility.set_location('l_spl_indicator6_amount'||l_spl_indicator6_amount,427);
2339 end if;
2340
2341 p_Amount_Special_indicator := l_spl_indicator1_amount || l_spl_indicator2_amount || l_spl_indicator3_amount || l_spl_indicator4_amount || l_spl_indicator5_amount || l_spl_indicator6_amount;
2342
2343 if g_debug then
2344 hr_utility.set_location('p_Amount_Special_indicator'||p_Amount_Special_indicator,427);
2345 hr_utility.set_location('Exiting get_special_indicators',1300);
2346 end if;
2347 g_debug:=false;
2348
2349 END get_special_indicators;
2350
2351 /*-----------------------------------------------------------------------------
2352 |Name : get_PRIVATE_USE_CAR |
2353 |Type : Function |
2354 |Description: Function which returns the private use car balance value |
2355 -------------------------------------------------------------------------------*/
2356
2357 function get_PRIVATE_USE_CAR(p_assgt_act_id number) return number IS
2358 l_balance_value number;
2359 l_defined_balance_id number;
2360 l_Private_Use_Car number;
2361
2362 BEGIN
2363 if g_debug then
2364 hr_utility.set_location('Entering get_PRIVATE_USE_CAR',1300);
2365 end if;
2366
2367 l_Private_Use_Car:=0;
2368
2369 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('PRIVATE_USE_CAR_ASG_YTD');
2370 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2371 l_Private_Use_Car:=l_balance_value;
2372 hr_utility.set_location('l_Private_Use_Car'||l_Private_Use_Car,425);
2373
2374 if g_debug then
2375 hr_utility.set_location('Exiting get_PRIVATE_USE_CAR',1300);
2376 end if;
2377 return l_Private_Use_Car;
2378
2379
2380 END get_PRIVATE_USE_CAR;
2381
2382
2383 /*-----------------------------------------------------------------------------
2384 |Name : get_NET_EXPENSE_ALLOWANCE |
2385 |Type : Function |
2386 |Description: Function which returns the NET EXPENSE ALLOWANCE balance value |
2387 -------------------------------------------------------------------------------*/
2388
2389 function get_NET_EXPENSE_ALLOWANCE(p_assgt_act_id number) return number IS
2390 l_balance_value number;
2391 l_defined_balance_id number;
2392 l_NET_EXPENSE_ALLOWANCE number;
2393
2394 BEGIN
2395
2396 if g_debug then
2397 hr_utility.set_location('Entering get_NET_EXPENSE_ALLOWANCE',1350);
2398 end if;
2399
2400 l_NET_EXPENSE_ALLOWANCE:=0;
2401
2402 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('NET_EXPENSE_ALLOWANCE_ASG_YTD');
2403 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2404 l_Net_Expense_Allowance:=l_balance_value;
2405
2406 if g_debug then
2407 hr_utility.set_location('l_Net_Expense_Allowance'||l_Net_Expense_Allowance,425);
2408 end if;
2409
2410
2411 if g_debug then
2412 hr_utility.set_location('Exiting get_NET_EXPENSE_ALLOWANCE',1350);
2413 end if;
2414
2415 return l_NET_EXPENSE_ALLOWANCE;
2416
2417
2418 END get_NET_EXPENSE_ALLOWANCE;
2419
2420 /*-----------------------------------------------------------------------------
2421 |Name : get_wage_tax_discount |
2422 |Type : Function |
2423 |Description: Function which returns the wage tax discount value |
2424 -------------------------------------------------------------------------------*/
2425
2426 function get_wage_tax_discount(p_assignment_id number
2427 ,p_tax_year_start_date date
2428 ,p_tax_year_end_date date) return varchar2 IS
2429
2430 TYPE wtd_rec IS RECORD (
2431 date_earned DATE,
2432 code VARCHAR2(20) ,
2433 period_start_date DATE );
2434
2435 TYPE wtd_filter_rec IS RECORD (
2436 date_earned DATE,
2437 code VARCHAR2(20),
2438 period_start_date DATE);
2439
2440 TYPE wtd_table IS TABLE OF wtd_rec INDEX BY BINARY_INTEGER;
2441 TYPE wtd_filter_table IS TABLE OF wtd_filter_rec INDEX BY BINARY_INTEGER;
2442
2443 l_wtd_table wtd_table;
2444 l_wtd_filter_table wtd_filter_table;
2445
2446 CURSOR csr_wtd_code is
2447 /* select decode(prrv.result_value,'NL_NONE','0','1') code,ppa.date_earned,paa.assignment_action_id,ptp.start_date --,prrv.result_value
2448 from
2449 pay_payroll_actions ppa,
2450 pay_assignment_actions paa,
2451 pay_element_types_f pet,
2452 pay_input_values_f piv,
2453 pay_run_results prr,
2454 pay_run_result_values prrv,
2455 per_time_periods ptp
2456 where
2457 pet.element_name='Standard Tax Deduction' and
2458 pet.element_type_id=piv.element_type_id and
2459 piv.name='Tax Reduction Flag' and
2460 ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
2461 ppa.payroll_action_id=paa.payroll_action_id and
2462 paa.assignment_id = p_assignment_id and
2463 prrv.input_value_id=piv.input_value_id and
2464 ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
2465 ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
2466 paa.assignment_action_id=prr.assignment_action_id and
2467 prrv.run_result_id=prr.run_result_id and
2468 ptp.time_period_id=ppa.time_period_id
2469 order by date_earned,paa.assignment_action_id; */
2470
2471 /* 14934380*/
2472
2473 select /*+ORDERED INDEX(ptp PER_TIME_PERIODS_PK) */ decode(prrv.result_value,'NL_NONE','0','1') code,ppa.date_earned,paa.assignment_action_id,ptp.start_date --,prrv.result_value
2474 from
2475 pay_payrolls_f ppf,
2476 pay_assignment_actions paa,
2477 pay_payroll_actions ppa,
2478 pay_element_types_f pet,
2479 pay_input_values_f piv,
2480 pay_run_results prr,
2481 pay_run_result_values prrv,
2482 per_time_periods ptp
2483 where
2484 pet.element_name='Standard Tax Deduction' and
2485 pet.element_type_id=piv.element_type_id and
2486 piv.name='Tax Reduction Flag' and
2487 --11854614
2488 --ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
2489 ppa.payroll_id = ppf.payroll_id and
2490 (ppa.date_earned+ppf.pay_date_offset) between p_tax_year_start_date and p_tax_year_end_date and
2491 --11854614
2492 ppa.payroll_action_id=paa.payroll_action_id and
2493 paa.assignment_id = p_assignment_id and
2494 -- ppa.business_group_id = p_bg_id and
2495 prrv.input_value_id=piv.input_value_id and
2496 ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
2497 ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
2498 paa.assignment_action_id=prr.assignment_action_id and
2499 prrv.run_result_id=prr.run_result_id and
2500 ptp.time_period_id=ppa.time_period_id and
2501 ptp.payroll_id = ppa.payroll_id
2502 order by date_earned,paa.assignment_action_id;
2503
2504
2505
2506 l_index number;
2507 l_index1 number;
2508 l_loop_count number;
2509 l_wts_code1 varchar2(1);
2510 l_wts_date1 varchar2(4);
2511 l_wts_code2 varchar2(1);
2512 l_wts_date2 varchar2(4);
2513 l_wts_code3 varchar2(1);
2514 l_wts_date3 varchar2(4);
2515 l_code varchar2(1);
2516 l_date_earned date;
2517 l_period_start_date date;
2518 l_filter_table_count number;
2519 l_wage_tax_discount varchar2(255);
2520
2521 BEGIN
2522
2523 if g_debug then
2524 hr_utility.set_location('Entering get_wage_tax_discount',1400);
2525 end if;
2526
2527 l_index:=0;
2528 l_index1:=0;
2529 l_loop_count:=0;
2530 l_wts_code1:='0';
2531 l_wts_date1:='0000';
2532 l_wts_code2:='0';
2533 l_wts_date2:='0000';
2534 l_wts_code3:='0';
2535 l_wts_date3:='0000';
2536
2537 for csr_wtd_code_rec in csr_wtd_code
2538
2539 loop
2540 l_loop_count:=l_loop_count+1;
2541 l_code:=csr_wtd_code_rec.code;
2542 l_date_earned:=csr_wtd_code_rec.date_earned;
2543 l_period_start_date:=csr_wtd_code_rec.start_date;
2544
2545 if l_loop_count=1 then
2546 l_wtd_table(l_index).code:=csr_wtd_code_rec.code;
2547
2548
2549 l_wtd_table(l_index).period_start_date:=csr_wtd_code_rec.start_date;
2550
2551
2552
2553 l_wtd_table(l_index).date_earned:=csr_wtd_code_rec.date_earned;
2554
2555
2556 end if;
2557 if l_loop_count>1 then
2558
2559
2560 if l_date_earned=l_wtd_table(l_index).date_earned then
2561 l_wtd_table(l_index).code:=l_code;
2562 l_wtd_table(l_index).date_earned:=l_date_earned;
2563 l_wtd_table(l_index).period_start_date:=l_period_start_date;
2564 else
2565 l_index:=l_index+1;
2566
2567 l_wtd_table(l_index).code:=l_code;
2568 l_wtd_table(l_index).date_earned:=l_date_earned;
2569 l_wtd_table(l_index).period_start_date:=l_period_start_date;
2570
2571 end if;
2572 end if;
2573 end loop;
2574
2575 FOR l_count IN 1 .. l_wtd_table.count
2576
2577
2578 LOOP
2579
2580
2581 if l_count=1 then
2582 l_wtd_filter_table(l_index1).date_earned:=l_wtd_table(l_index1).date_earned;
2583 l_wtd_filter_table(l_index1).code:=l_wtd_table(l_index1).code;
2584 l_wtd_filter_table(l_index1).period_start_date:=l_wtd_table(l_index1).period_start_date;
2585 end if;
2586 if l_count>1 then
2587
2588 if l_wtd_table(l_count-1).code<>l_wtd_filter_table(l_index1).code then
2589 l_index1:=l_index1+1;
2590 l_wtd_filter_table(l_index1).code:=l_wtd_table(l_count-1).code;
2591 l_wtd_filter_table(l_index1).date_earned:=l_wtd_table(l_count-1).date_earned;
2592 l_wtd_filter_table(l_index1).period_start_date:=l_wtd_table(l_count-1).period_start_date;
2593 end if;
2594 end if;
2595
2596
2597 END LOOP;
2598
2599 l_filter_table_count:=l_wtd_filter_table.count;
2600 IF l_filter_table_count>3 then
2601 l_wts_code1:=l_wtd_filter_table(l_filter_table_count-3).code;
2602 l_wts_date1:=to_char(l_wtd_filter_table(l_filter_table_count-3).period_start_date,'DDMM');
2603 l_wts_code2:=l_wtd_filter_table(l_filter_table_count-2).code;
2604 l_wts_date2:=to_char(l_wtd_filter_table(l_filter_table_count-2).period_start_date,'DDMM');
2605 l_wts_code3:=l_wtd_filter_table(l_filter_table_count-1).code;
2606 l_wts_date3:=to_char(l_wtd_filter_table(l_filter_table_count-1).period_start_date,'DDMM');
2607 ELSE
2608 if l_filter_table_count=3 then
2609 l_wts_code1:=l_wtd_filter_table(0).code;
2610 l_wts_date1:=to_char(l_wtd_filter_table(0).period_start_date,'DDMM');
2611 l_wts_code2:=l_wtd_filter_table(1).code;
2612 l_wts_date2:=to_char(l_wtd_filter_table(1).period_start_date,'DDMM');
2613 l_wts_code3:=l_wtd_filter_table(2).code;
2614 l_wts_date3:=to_char(l_wtd_filter_table(2).period_start_date,'DDMM');
2615 elsif l_filter_table_count=2 then
2616 l_wts_code1:=l_wtd_filter_table(0).code;
2617 l_wts_date1:=to_char(l_wtd_filter_table(0).period_start_date,'DDMM');
2618 l_wts_code2:=l_wtd_filter_table(1).code;
2619 l_wts_date2:=to_char(l_wtd_filter_table(1).period_start_date,'DDMM');
2620 elsif l_filter_table_count=1 then
2621 l_wts_code1:=l_wtd_filter_table(0).code;
2622 l_wts_date1:=to_char(l_wtd_filter_table(0).period_start_date,'DDMM');
2623 end if;
2624 END IF;
2625
2626 if g_debug then
2627 hr_utility.set_location('l_wts_code1'||l_wts_code1,425);
2628 hr_utility.set_location('l_wts_code2'||l_wts_code2,425);
2629 hr_utility.set_location('l_wts_date2'||l_wts_date2,425);
2630 hr_utility.set_location('l_wts_code3'||l_wts_code3,425);
2631 hr_utility.set_location('l_wts_date3'||l_wts_date3,425);
2632 end if;
2633
2634 l_wage_tax_discount:=l_wts_code1 || l_wts_date1 || l_wts_code2 || l_wts_date2 || l_wts_code3 || l_wts_date3;
2635
2636 if g_debug then
2637 hr_utility.set_location('l_wage_tax_discount'||l_wage_tax_discount,1500);
2638 hr_utility.set_location('Exiting get_wage_tax_discount',1500);
2639 end if;
2640
2641 return l_wage_tax_discount;
2642
2643
2644
2645 END get_wage_tax_discount;
2646
2647 /*-----------------------------------------------------------------------------
2648 |Name : get_wage_tax_table_code |
2649 |Type : Function |
2650 |Description: Function which returns the wage tax table code |
2651 -------------------------------------------------------------------------------*/
2652
2653 function get_wage_tax_table_code(p_assignment_id number
2654 ,p_tax_year_start_date date
2655 ,p_tax_year_end_date date)return varchar2 IS
2656
2657 CURSOR csr_wage_tax_table_code is
2658 /* select count(prrv.result_value) counter,prrv.result_value
2659 from
2660 pay_payroll_actions ppa,
2661 pay_assignment_actions paa,
2662 pay_element_types_f pet,
2663 pay_input_values_f piv,
2664 pay_run_results prr,
2665 pay_run_result_values prrv
2666 where
2667 pet.element_name='Standard Tax Deduction' and
2668 pet.element_type_id=piv.element_type_id and
2669 piv.name='Tax Code' and
2670 ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
2671 ppa.payroll_action_id=paa.payroll_action_id and
2672 paa.assignment_id = p_assignment_id and
2673 prrv.input_value_id=piv.input_value_id
2674 and
2675 paa.assignment_action_id=prr.assignment_action_id and
2676 ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
2677 ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
2678 prrv.run_result_id=prr.run_result_id
2679 group by prrv.result_value
2680 order by counter desc; */
2681
2682 /* 14934399*/
2683 select /*+ ORDERED */ count(prrv.result_value) counter,prrv.result_value
2684 from
2685 pay_payrolls_f ppf,
2686 pay_assignment_actions paa,
2687 pay_payroll_actions ppa,
2688 pay_element_types_f pet,
2689 pay_input_values_f piv,
2690 pay_run_results prr,
2691 pay_run_result_values prrv
2692 where
2693 pet.element_name='Standard Tax Deduction' and
2694 pet.element_type_id=piv.element_type_id and
2695 piv.name='Tax Code' and
2696 --ppa.business_group_id = p_bg_id and
2697 --11854614
2698 --ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
2699 ppa.payroll_id = ppf.payroll_id and
2700 (ppa.date_earned+ppf.pay_date_offset) between p_tax_year_start_date and p_tax_year_end_date and
2701 --11854614
2702 ppa.payroll_action_id=paa.payroll_action_id and
2703 paa.assignment_id = p_assignment_id and
2704 prrv.input_value_id=piv.input_value_id
2705 and
2706 paa.assignment_action_id=prr.assignment_action_id and
2707 ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
2708 ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
2709 prrv.run_result_id=prr.run_result_id
2710 group by prrv.result_value
2711 order by counter desc;
2712
2713
2714
2715 l_wage_tax_table_code varchar2(5);
2716 l_count number;
2717
2718 BEGIN
2719
2720 if g_debug then
2721 hr_utility.set_location('Entering get_wage_tax_table_code',1500);
2722 end if;
2723
2724 OPEN csr_wage_tax_table_code;
2725 FETCH csr_wage_tax_table_code into l_count,l_wage_tax_table_code;
2726 CLOSE csr_wage_tax_table_code;
2727
2728 if g_debug then
2729 hr_utility.set_location('l_count'||l_count,1500);
2730 hr_utility.set_location('l_wage_tax_table_code'||l_wage_tax_table_code,1500);
2731 end if;
2732
2733 return l_wage_tax_table_code;
2734
2735 if g_debug then
2736 hr_utility.set_location('Exiting get_wage_tax_table_code',1500);
2737 end if;
2738 END get_wage_tax_table_code;
2739
2740
2741 /*-----------------------------------------------------------------------------
2742 |Name : get_si_insured_flag |
2743 |Type : Function |
2744 |Description: Function which returns the si insured flag string |
2745 -------------------------------------------------------------------------------*/
2746
2747 function get_si_insured_flag(p_assignment_id number
2748 ,p_tax_year_start_date date
2749 ,p_tax_year_end_date date) return varchar2 is
2750
2751 CURSOR csr_si_insured_flag(lp_element_name varchar2) is
2752 /* 14934414 */
2753 select 1 from dual
2754 where exists
2755 (select /*+ USE_NL(paa, ppa, pet,prr) */ prr.run_result_id,ppa.date_earned from
2756 pay_payrolls_f ppf
2757 ,pay_payroll_actions ppa
2758 ,pay_assignment_actions paa
2759 ,pay_run_results prr
2760 ,pay_element_types_f pet
2761 where ppa.payroll_action_id = paa.payroll_action_id
2762 and paa.assignment_id = p_assignment_id
2763 and paa.assignment_action_id = paa.assignment_action_id
2764 --11854614
2765 --and ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date
2766 and ppa.payroll_id = ppf.payroll_id
2767 and (ppa.date_earned+ppf.pay_date_offset) between p_tax_year_start_date and p_tax_year_end_date
2768 --11854614
2769 and ppa.action_type in ('R','Q','B','I','V')
2770 and paa.assignment_action_id=prr.assignment_action_id
2771 and pet.element_type_id=prr.element_type_id
2772 and pet.element_name=lp_element_name
2773 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date);
2774
2775 l_si_insured_flag varchar2(2);
2776 l_SI_Insured_Flag1 varchar2(1);
2777 l_SI_Insured_Flag2 varchar2(1);
2778 l_flag varchar2(20);
2779
2780
2781 BEGIN
2782
2783 if g_debug then
2784 hr_utility.set_location('Entering l_SI_Insured_Flag',425);
2785 end if;
2786
2787
2788 OPEN csr_si_insured_flag('WAO Basis Social Insurance');
2789 FETCH csr_si_insured_flag into l_flag;
2790 if l_flag is not null then
2791 l_SI_Insured_Flag1:='1';
2792 else
2793 l_SI_Insured_Flag1:='0';
2794 end if;
2795 CLOSE csr_si_insured_flag;
2796
2797 l_flag:=NULL;
2798
2799
2800 /*OPEN csr_si_insured_flag('ZFW Social Insurance');
2801 FETCH csr_si_insured_flag into l_flag;
2802 if l_flag is not null then
2803 l_SI_Insured_Flag2:='1';
2804 else
2805 l_SI_Insured_Flag2:='0';
2806 end if;
2807 CLOSE csr_si_insured_flag;
2808
2809
2810 l_SI_Insured_Flag:= l_SI_Insured_Flag1||l_SI_Insured_Flag2;
2811
2812 if g_debug then
2813 hr_utility.set_location('l_SI_Insured_Flag'||l_SI_Insured_Flag,425);
2814 hr_utility.set_location('l_SI_Insured_Flag1'||l_SI_Insured_Flag1,425);
2815 hr_utility.set_location('l_SI_Insured_Flag2'||l_SI_Insured_Flag2,425);
2816 hr_utility.set_location('Exiting l_SI_Insured_Flag',425);
2817 end if;*/
2818
2819 return l_SI_Insured_Flag1;
2820
2821 END get_si_insured_flag;
2822
2823
2824 /*-----------------------------------------------------------------------------
2825 |Name : get_income_code |
2826 |Type : Function |
2827 |Description: Function which returns the income code |
2828 -------------------------------------------------------------------------------*/
2829
2830 function get_income_code(p_assignment_id number
2831 ,p_tax_year_start_date date
2832 ,p_tax_year_end_date date)return varchar2 is
2833
2834
2835 cursor csr_get_income_code is
2836 select sck.segment8,SUM(decode(sign(p_tax_year_end_date - paa.effective_end_date),-1,p_tax_year_end_date,paa.effective_end_date)-decode(sign(paa.effective_start_date - p_tax_year_start_date),-1,p_tax_year_start_date,paa.effective_start_date)+1) Days
2837 from per_all_assignments_f paa,hr_soft_coding_keyflex sck
2838 where paa.assignment_id = p_assignment_id
2839 and (paa.effective_start_date >= p_tax_year_start_date or p_tax_year_start_date between paa.effective_start_date and paa.effective_end_date)
2840 and (paa.effective_end_date <= p_tax_year_end_date or paa.effective_start_date <= p_tax_year_end_date)
2841 and sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
2842 group by sck.segment8
2843 order by Days desc;
2844
2845
2846 l_income_code varchar2(10);
2847 l_days number;
2848
2849 BEGIN
2850
2851
2852 OPEN csr_get_income_code;
2853 FETCH csr_get_income_code into l_income_code,l_days;
2854 CLOSE csr_get_income_code;
2855
2856 return l_income_code;
2857
2858 END get_income_code;
2859
2860
2861 /*-----------------------------------------------------------------------------
2862 |Name : get_org_hierarchy |
2863 |Type : Function |
2864 |Description: Function which returns organization structure version id |
2865 -------------------------------------------------------------------------------*/
2866
2867 function get_org_hierarchy(p_org_struct_id varchar2
2868 ,p_tax_year_end_date date) return number IS
2869
2870 cursor csr_org_hierarchy IS
2871 select
2872 posv.org_structure_version_id
2873 from
2874 per_organization_structures pos,
2875 per_org_structure_versions posv
2876 where pos.organization_structure_id = posv.organization_structure_id
2877 and to_char(pos.organization_structure_id) = p_org_struct_id
2878 and p_tax_year_end_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);
2879
2880 l_org_structure_version_id number;
2881
2882 BEGIN
2883 OPEN csr_org_hierarchy;
2884 FETCH csr_org_hierarchy INTO l_org_structure_version_id;
2885 CLOSE csr_org_hierarchy;
2886
2887 hr_utility.set_location('l_org_structure_version_id'||l_org_structure_version_id,425);
2888
2889 return l_org_structure_version_id;
2890
2891 END get_org_hierarchy;
2892
2893
2894
2895 function get_IZA_contributions(p_assgt_act_id number) return number IS
2896
2897 l_IZA_contributions number;
2898 l_balance_value number;
2899 l_defined_balance_id number;
2900
2901 BEGIN
2902
2903 hr_utility.set_location('Entering get_IZA_contributions',1050);
2904
2905 l_IZA_contributions:=0;
2906
2907 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
2908 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2909 l_IZA_contributions:=l_IZA_contributions + l_balance_value;
2910
2911 if g_debug then
2912 hr_utility.set_location('EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
2913 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2914 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2915 hr_utility.set_location('l_IZA_contributions'||l_IZA_contributions,425);
2916 end if;
2917
2918
2919 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
2920 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2921 l_IZA_contributions:=l_IZA_contributions + l_balance_value;
2922
2923 if g_debug then
2924 hr_utility.set_location('EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
2925 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2926 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2927 hr_utility.set_location('l_IZA_contributions'||l_IZA_contributions,425);
2928 end if;
2929
2930
2931 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
2932 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2933 l_IZA_contributions:=l_IZA_contributions + l_balance_value;
2934
2935 if g_debug then
2936 hr_utility.set_location('RETRO_EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
2937 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2938 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2939 hr_utility.set_location('l_IZA_contributions'||l_IZA_contributions,425);
2940 end if;
2941
2942
2943 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
2944 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2945 l_IZA_contributions:=l_IZA_contributions + l_balance_value;
2946
2947 if g_debug then
2948 hr_utility.set_location('RETRO_EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
2949 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2950 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2951 hr_utility.set_location('l_IZA_contributions'||l_IZA_contributions,425);
2952
2953 hr_utility.set_location('Exiting get_IZA_contributions',950);
2954 end if;
2955
2956
2957 return l_IZA_contributions;
2958
2959 END get_IZA_contributions;
2960
2961
2962 function get_ZVW_basis(p_assgt_act_id number) return number IS
2963
2964 l_ZVW_basis number := 0;
2965 l_balance_value number := 0;
2966 l_defined_balance_id number;
2967
2968 BEGIN
2969
2970 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('ZVW_INCOME_STANDARD_TAX_ASG_YTD');
2971 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2972 l_ZVW_basis:=l_ZVW_basis + l_balance_value;
2973
2974 if g_debug then
2975 hr_utility.set_location('ZVW_INCOME_STANDARD_TAX_ASG_YTD',425);
2976 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2977 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2978 hr_utility.set_location('l_ZVW_basis'||l_ZVW_basis,425);
2979 end if;
2980
2981 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('ZVW_INCOME_SPECIAL_TAX_ASG_YTD');
2982 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2983 l_ZVW_basis:=l_ZVW_basis + l_balance_value;
2984
2985 if g_debug then
2986 hr_utility.set_location('ZVW_INCOME_SPECIAL_TAX_ASG_YTD',425);
2987 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2988 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2989 hr_utility.set_location('l_ZVW_basis'||l_ZVW_basis,425);
2990 end if;
2991
2992 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_ZVW_INCOME_STANDARD_TAX_ASG_YTD');
2993 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2994 l_ZVW_basis:=l_ZVW_basis + l_balance_value;
2995
2996 if g_debug then
2997 hr_utility.set_location('RETRO_ZVW_INCOME_STANDARD_TAX_ASG_YTD',425);
2998 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2999 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3000 hr_utility.set_location('l_ZVW_basis'||l_ZVW_basis,425);
3001 end if;
3002
3003 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_ZVW_INCOME_SPECIAL_TAX_ASG_YTD');
3004 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3005 l_ZVW_basis:=l_ZVW_basis + l_balance_value;
3006
3007 if g_debug then
3008 hr_utility.set_location('RETRO_ZVW_INCOME_SPECIAL_TAX_ASG_YTD',425);
3009 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3010 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3011 hr_utility.set_location('l_ZVW_basis'||l_ZVW_basis,425);
3012 end if;
3013
3014 --l_ZVW_basis := floor(l_ZVW_basis); --11886537
3015 return l_ZVW_basis;
3016
3017 END get_ZVW_basis;
3018
3019
3020 function get_ZVW_contributions(p_assgt_act_id number) return number IS
3021
3022 l_ZVW_contribution number;
3023 l_context_id number;
3024 l_balance_value number;
3025 l_defined_balance_id number;
3026
3027 BEGIN
3028
3029 hr_utility.set_location('Entering get_ZVW_Contribution',1050);
3030
3031 l_ZVW_Contribution:=0;
3032
3033 l_context_id:=get_context_id('SOURCE_TEXT');
3034
3035
3036 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD');
3037 IF l_context_id IS NULL then
3038 l_balance_value:=0;
3039 ELSE
3040 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3041 END IF;
3042 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3043
3044
3045 if g_debug then
3046 hr_utility.set_location('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD',425);
3047 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3048 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3049 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3050 end if;
3051
3052
3053
3054 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
3055 IF l_context_id IS NULL then
3056 l_balance_value:=0;
3057 ELSE
3058 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3059 END IF;
3060 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3061
3062 if g_debug then
3063 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
3064 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3065 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3066 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3067 end if;
3068
3069
3070
3071 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
3072 IF l_context_id IS NULL then
3073 l_balance_value:=0;
3074 ELSE
3075 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3076 END IF;
3077 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3078
3079
3080 if g_debug then
3081 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
3082 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3083 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3084 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3085 end if;
3086
3087
3088 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD');
3089 IF l_context_id IS NULL then
3090 l_balance_value:=0;
3091 ELSE
3092 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3093 END IF;
3094 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3095
3096
3097 if g_debug then
3098 hr_utility.set_location('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD',425);
3099 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3100 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3101 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3102 end if;
3103
3104
3105 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
3106 IF l_context_id IS NULL then
3107 l_balance_value:=0;
3108 ELSE
3109 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3110 END IF;
3111 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3112
3113
3114 if g_debug then
3115 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
3116 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3117 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3118 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3119 end if;
3120
3121
3122 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
3123 IF l_context_id IS NULL then
3124 l_balance_value:=0;
3125 ELSE
3126 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3127 END IF;
3128 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3129
3130
3131 if g_debug then
3132 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
3133 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3134 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3135 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3136 end if;
3137
3138 --l_ZVW_Contribution:=ceil(l_ZVW_Contribution); --11886537
3139
3140 return l_ZVW_Contribution;
3141
3142
3143 END get_ZVW_Contributions;
3144
3145
3146 /*-----------------------------------------------------------------------------
3147 |Name : get_VALUE_PRIVATE_USE_CAR |
3148 |Type : Function |
3149 |Description: Function which returns the value private use car balance value |
3150 -------------------------------------------------------------------------------*/
3151
3152 function get_VALUE_PRIVATE_USE_CAR(p_assgt_act_id number) return number IS
3153 l_balance_value number;
3154 l_defined_balance_id number;
3155 l_Value_Private_Use_Car number;
3156
3157 BEGIN
3158 if g_debug then
3159 hr_utility.set_location('Entering get_VALUE_PRIVATE_USE_CAR',1300);
3160 end if;
3161
3162 l_Value_Private_Use_Car:=0;
3163
3164 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('VALUE_PRIVATE_USAGE_COMPANY_CAR_ASG_YTD');
3165 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3166 l_Value_Private_Use_Car:=l_balance_value;
3167 hr_utility.set_location('l_Value_Private_Use_Car'||l_Value_Private_Use_Car,425);
3168
3169 if g_debug then
3170 hr_utility.set_location('Exiting get_VALUE_PRIVATE_USE_CAR',1300);
3171 end if;
3172 return l_Value_Private_Use_Car;
3173
3174
3175 END get_VALUE_PRIVATE_USE_CAR;
3176
3177
3178 /*-----------------------------------------------------------------------------
3179 |Name : get_LSS_Saved_Amount |
3180 |Type : Function |
3181 |Description: Function which returns the saved amount for life saving scheme |
3182 -------------------------------------------------------------------------------*/
3183
3184 function get_LSS_Saved_Amount(p_assgt_act_id number) return number IS
3185 l_balance_value number;
3186 l_defined_balance_id number;
3187 l_LSS_Saved_Amount number;
3188
3189 BEGIN
3190 if g_debug then
3191 hr_utility.set_location('Entering get_LSS_Saved_Amount',1300);
3192 end if;
3193
3194 l_LSS_Saved_Amount:=0;
3195
3196 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_LIFE_SAVINGS_CONTRIBUTION_ASG_YTD');
3197 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3198 l_LSS_Saved_Amount:=l_LSS_Saved_Amount + l_balance_value;
3199
3200 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_LIFE_SAVINGS_CONTRIBUTION_ASG_YTD');
3201 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3202 l_LSS_Saved_Amount:=l_LSS_Saved_Amount + l_balance_value;
3203
3204 hr_utility.set_location('l_LSS_Saved_Amount'||l_LSS_Saved_Amount,425);
3205
3206 if g_debug then
3207 hr_utility.set_location('Exiting get_LSS_Saved_Amount',1300);
3208 end if;
3209 return l_LSS_Saved_Amount;
3210
3211
3212 END get_LSS_Saved_Amount;
3213
3214
3215 /*-----------------------------------------------------------------------------
3216 |Name : get_Employer_Part_Child_Care |
3217 |Type : Function |
3218 |Description: Function which returns the Employer part Child Care balance value|
3219 -------------------------------------------------------------------------------*/
3220
3221 function get_Employer_Part_Child_Care(p_assgt_act_id number) return number IS
3222 l_balance_value number;
3223 l_defined_balance_id number;
3224 l_Employer_Part_Child_Care number;
3225
3226 BEGIN
3227 if g_debug then
3228 hr_utility.set_location('Entering get_Employer_Part_Child_Care',1300);
3229 end if;
3230
3231 l_Employer_Part_Child_Care:=0;
3232
3233 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('CHILD_CARE_EMPLOYER_CONTRIBUTION_ASG_YTD');
3234 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3235 l_Employer_Part_Child_Care:=l_balance_value;
3236 hr_utility.set_location('l_Employer_Part_Child_Care'||l_Employer_Part_Child_Care,425);
3237
3238 if g_debug then
3239 hr_utility.set_location('Exiting get_Employer_Part_Child_Care',1300);
3240 end if;
3241 return l_Employer_Part_Child_Care;
3242
3243
3244 END get_Employer_Part_Child_Care;
3245
3246
3247 /*-----------------------------------------------------------------------------
3248 |Name : get_Allowance_On_Disability |
3249 |Type : Function |
3250 |Description: Function which returns the paid allowance on Disability Allowance|
3251 -------------------------------------------------------------------------------*/
3252
3253 function get_Allowance_On_Disability(p_assgt_act_id number) return number IS
3254 l_balance_value number;
3255 l_defined_balance_id number;
3256 l_Allowance_On_Disability number;
3257
3258 BEGIN
3259 if g_debug then
3260 hr_utility.set_location('Entering get_Allowance_On_Disability',1300);
3261 end if;
3262
3263 l_Allowance_On_Disability:=0;
3264
3265 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('PAID_DISABILITY_ALLOWANCE_ASG_YTD');
3266 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3267 l_Allowance_On_Disability:=l_balance_value;
3268 hr_utility.set_location('l_Allowance_On_Disability'||l_Allowance_On_Disability,425);
3269
3270 if g_debug then
3271 hr_utility.set_location('Exiting get_Allowance_On_Disability',1300);
3272 end if;
3273 return l_Allowance_On_Disability;
3274
3275
3276 END get_Allowance_On_Disability;
3277
3278
3279 /*-----------------------------------------------------------------------------
3280 |Name : get_Applied_LCLD |
3281 |Type : Function |
3282 |Description: Function which returns the Applied Life Cycle Leave Discount |
3283 -------------------------------------------------------------------------------*/
3284
3285 function get_Applied_LCLD(p_assgt_act_id number) return number IS
3286 l_balance_value number;
3287 l_defined_balance_id number;
3288 l_Applied_LCLD number;
3289
3290 BEGIN
3291 if g_debug then
3292 hr_utility.set_location('Entering get_Applied_LCLD',1300);
3293 end if;
3294
3295 l_Applied_LCLD:=0;
3296
3297 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('LIFE_CYCLE_LEAVE_DISCOUNT_ASG_YTD');
3298 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3299 l_Applied_LCLD:=l_Applied_LCLD + l_balance_value;
3300
3301 /* l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_LIFE_CYCLE_LEAVE_DISCOUNT_ASG_YTD');
3302 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3303 l_Applied_LCLD:=l_Applied_LCLD + l_balance_value;*/
3304
3305 hr_utility.set_location('l_Applied_LCLD'||l_Applied_LCLD,425);
3306
3307 if g_debug then
3308 hr_utility.set_location('Exiting get_Applied_LCLD',1300);
3309 end if;
3310 return l_Applied_LCLD;
3311
3312
3313 END get_Applied_LCLD;
3314
3315
3316 /*-----------------------------------------------------------------------------
3317 |Name : populate_UserBal |
3318 |Type : Procedure |
3319 |Description: Procedure which populates pl/sql table with user defined balance |
3320 | names and tag names |
3321 -------------------------------------------------------------------------------*/
3322
3323 PROCEDURE populate_UserBal(p_bg_id number, p_effective_date DATE) IS
3324
3325 CURSOR csr_get_rows IS
3326 select pur.user_row_id
3327 from pay_user_rows_f pur,
3328 pay_user_tables put
3329 where put.user_table_name='NL_ATS_USER_BALANCES'
3330 and put.legislation_code='NL'
3331 and pur.user_table_id=put.user_table_id
3332 and p_effective_date between pur.effective_start_date and pur.effective_end_date;
3333
3334 v_csr_get_rows csr_get_rows%ROWTYPE;
3335 vCtr NUMBER;
3336 vBalColId NUMBER;
3337 vTagColId NUMBER;
3338 vBalName VARCHAR2(1000);
3339 vTagName VARCHAR2(1000);
3340
3341 BEGIN
3342
3343 vUserBalTable.DELETE;
3344 vCtr := 0;
3345 vBalColId := null;
3346 vTagColId := null;
3347
3348 BEGIN
3349
3350 SELECT puc.user_column_id
3351 INTO vBalColId
3352 FROM pay_user_columns puc,
3353 pay_user_tables put
3354 WHERE put.user_table_name='NL_ATS_USER_BALANCES'
3355 and put.legislation_code='NL'
3356 and put.user_table_id=puc.user_table_id
3357 and puc.user_column_name='BAL_NAME';
3358
3359 EXCEPTION
3360
3361 WHEN NO_DATA_FOUND
3362 THEN vBalColId := null;
3363
3364 WHEN OTHERS
3365 THEN vBalColId := null;
3366
3367 END;
3368
3369 BEGIN
3370
3371 SELECT puc.user_column_id
3372 INTO vTagColId
3373 FROM pay_user_columns puc,
3374 pay_user_tables put
3375 WHERE put.user_table_name='NL_ATS_USER_BALANCES'
3376 and put.legislation_code='NL'
3377 and put.user_table_id=puc.user_table_id
3378 and puc.user_column_name='TAG_NAME';
3379
3380 EXCEPTION
3381
3382 WHEN NO_DATA_FOUND
3383 THEN vTagColId := null;
3384
3385 WHEN OTHERS
3386 THEN vTagColId := null;
3387
3388 END;
3389
3390 IF vBalColId is NOT NULL and vTagColId is NOT NULL THEN
3391
3392 FOR v_csr_get_rows IN csr_get_rows
3393 LOOP
3394
3395 vBalName := null;
3396 vTagName := null;
3397
3398 BEGIN
3399
3400 SELECT puci.value
3401 INTO vBalName
3402 FROM pay_user_column_instances_f puci
3403 WHERE puci.user_row_id=v_csr_get_rows.user_row_id
3404 AND puci.user_column_id=vBalColId
3405 AND p_effective_date between puci.effective_start_date and puci.effective_end_date;
3406
3407 EXCEPTION
3408
3409 WHEN NO_DATA_FOUND
3410 THEN vBalName := null;
3411
3412 WHEN OTHERS
3413 THEN vBalName := null;
3414
3415 END;
3416
3417 BEGIN
3418
3419 SELECT puci.value
3420 INTO vTagName
3421 FROM pay_user_column_instances_f puci
3422 WHERE puci.user_row_id=v_csr_get_rows.user_row_id
3423 AND puci.user_column_id=vTagColId
3424 AND p_effective_date between puci.effective_start_date and puci.effective_end_date;
3425
3426 EXCEPTION
3427
3428 WHEN NO_DATA_FOUND
3429 THEN vTagName := null;
3430
3431 WHEN OTHERS
3432 THEN vTagName := null;
3433
3434 END;
3435
3436 IF vBalName is not NULL and vTagName is not NULL THEN
3437
3438 vUserBalTable(vCtr).BalName := replace(upper(vBalName),' ','_')||'_ASG_YTD';
3439 vUserBalTable(vCtr).TagName := replace(vTagName,' ','');
3440 vCtr := vCtr + 1;
3441
3442 END IF;
3443
3444 END LOOP;
3445
3446 END IF;
3447
3448 END populate_UserBal;
3449
3450 /*-----------------------------------------------------------------------------
3451 |Name : get_User_Balances |
3452 |Type : Procedure |
3453 |Description: Procedure which returns the User Defined Balances |
3454 -------------------------------------------------------------------------------*/
3455
3456 PROCEDURE get_User_Balances (p_assgt_act_id in number
3457 ,p_bg_id in number
3458 ,p_User_Bal_String out nocopy varchar2) IS
3459
3460 l_defined_balance_id number;
3461 l_balance_value number;
3462 l_balance_string varchar2(255) := null;
3463 l_ctr_table number;
3464
3465 BEGIN
3466
3467 IF vUserBalTable.count > 0 THEN
3468
3469 FOR l_ctr_table IN vUserBalTable.FIRST .. vUserBalTable.LAST LOOP
3470
3471 l_defined_balance_id := null;
3472 l_balance_value := 0;
3473
3474 l_defined_balance_id:=get_User_Defined_Balance_Id(vUserBalTable(l_ctr_table).BalName,p_bg_id);
3475
3476 IF l_defined_balance_id is NULL THEN
3477 l_balance_value := 0;
3478 ELSE
3479 l_balance_value := pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3480 END IF;
3481
3482 l_balance_string := l_balance_string||fnd_number.number_to_canonical(ceil(l_balance_value))||'|';
3483
3484 END LOOP;
3485
3486 p_User_Bal_String := l_balance_string;
3487
3488 END IF;
3489
3490 END get_User_Balances;
3491
3492
3493 /*-----------------------------------------------------------------------------
3494 |Name : get_User_Defined_Balance_Id |
3495 |Type : Procedure |
3496 |Description: Procedure which returns the User Defined Balance Id |
3497 -------------------------------------------------------------------------------*/
3498
3499 FUNCTION get_User_Defined_Balance_Id (p_user_name IN VARCHAR2, p_bg_id IN NUMBER) RETURN NUMBER IS
3500 /* Cursor to retrieve User Defined Balance Id */
3501 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
3502 SELECT u.creator_id
3503 FROM ff_user_entities u,
3504 ff_database_items d
3505 WHERE d.user_name = p_user_name
3506 AND u.user_entity_id = d.user_entity_id
3507 AND (u.legislation_code is NULL )
3508 AND (u.business_group_id = p_bg_id )
3509 AND u.creator_type = 'B';
3510
3511 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
3512
3513 BEGIN
3514 OPEN csr_def_bal_id(p_user_name);
3515 FETCH csr_def_bal_id INTO l_defined_balance_id;
3516 CLOSE csr_def_bal_id;
3517 RETURN l_defined_balance_id;
3518
3519 END get_User_Defined_Balance_Id;
3520
3521 END PAY_NL_TAXOFFICE_ARCHIVE;