[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_TAXOFFICE_ARCHIVE
Source
1 PACKAGE BODY PAY_NL_TAXOFFICE_ARCHIVE as
2 /* $Header: pynltosa.pkb 120.2.12000000.4 2007/03/13 12:00:10 summohan noship $ */
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
441 l_wage:=floor(PAY_NL_TAXOFFICE_ARCHIVE.get_wage(l_assgt_act_id) + PAY_NL_TAXOFFICE_ARCHIVE.get_IZA_contributions(l_assgt_act_id));
442 l_taxable_income:=floor(PAY_NL_TAXOFFICE_ARCHIVE.get_taxable_income(l_assgt_act_id));
443 l_deduct_wage_tax_si_cont:=PAY_NL_TAXOFFICE_ARCHIVE.get_deduct_wage_tax_si_cont(l_assgt_act_id);
444 l_Labour_Discount:=PAY_NL_TAXOFFICE_ARCHIVE.get_labour_discount(l_assgt_act_id);
445 l_ZVW_Contribution:=PAY_NL_TAXOFFICE_ARCHIVE.get_ZVW_contributions(l_assgt_act_id);
446 l_ZVW_Basis:=PAY_NL_TAXOFFICE_ARCHIVE.get_ZVW_basis(l_assgt_act_id);
447 l_Value_Private_Use_Car:=PAY_NL_TAXOFFICE_ARCHIVE.get_VALUE_PRIVATE_USE_CAR(l_assgt_act_id);
448 l_LSS_Saved_Amount:=PAY_NL_TAXOFFICE_ARCHIVE.get_LSS_Saved_Amount(l_assgt_act_id);
449 l_Employer_Part_Child_Care:=PAY_NL_TAXOFFICE_ARCHIVE.get_Employer_Part_Child_Care(l_assgt_act_id);
450 l_Allowance_On_Disability:=PAY_NL_TAXOFFICE_ARCHIVE.get_Allowance_On_Disability(l_assgt_act_id);
451 l_Applied_LCLD:=PAY_NL_TAXOFFICE_ARCHIVE.get_Applied_LCLD(l_assgt_act_id);
452
453 hr_utility.set_location('Fetching balances end',420);
454
455 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);
456 PAY_NL_TAXOFFICE_ARCHIVE.get_User_Balances(l_assgt_act_id,l_business_group_id,l_User_Bal_String);
457
458
459
460 if g_debug then
461 hr_utility.set_location('l_wage'||l_wage,425);
462 hr_utility.set_location('l_deduct_wage_tax_si_cont'||l_deduct_wage_tax_si_cont,425);
463 hr_utility.set_location('l_Labour_Discount'||l_Labour_Discount,425);
464 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
465 hr_utility.set_location('l_ZVW_Basis'||l_ZVW_Basis,425);
466 hr_utility.set_location('l_Value_Private_Use_Car'||l_Value_Private_Use_Car,425);
467 hr_utility.set_location('l_LSS_Saved_Amount'||l_LSS_Saved_Amount,425);
468 hr_utility.set_location('l_Employer_Part_Child_Care'||l_Employer_Part_Child_Care,425);
469 hr_utility.set_location('l_Allowance_On_Disability'||l_Allowance_On_Disability,425);
470 hr_utility.set_location('l_Applied_LCLD'||l_Applied_LCLD,425);
471 hr_utility.set_location('l_Special_Indicator'||l_Special_Indicator,425);
472 hr_utility.set_location('l_Amount_Special_Indicator'||l_Amount_Special_Indicator,425);
473 end if;
474
475 hr_utility.set_location('l_wage'||l_wage,425);
476 hr_utility.set_location('l_deduct_wage_tax_si_cont'||l_deduct_wage_tax_si_cont,425);
477 hr_utility.set_location('l_Labour_Discount'||l_Labour_Discount,425);
478 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
479 hr_utility.set_location('l_ZVW_Basis'||l_ZVW_Basis,425);
480 hr_utility.set_location('l_Value_Private_Use_Car'||l_Value_Private_Use_Car,425);
481 hr_utility.set_location('l_LSS_Saved_Amount'||l_LSS_Saved_Amount,425);
482 hr_utility.set_location('l_Employer_Part_Child_Care'||l_Employer_Part_Child_Care,425);
483 hr_utility.set_location('l_Allowance_On_Disability'||l_Allowance_On_Disability,425);
484 hr_utility.set_location('l_Applied_LCLD'||l_Applied_LCLD,425);
485 hr_utility.set_location('l_Special_Indicator'||l_Special_Indicator,425);
486 hr_utility.set_location('l_Amount_Special_Indicator'||l_Amount_Special_Indicator,425);
487
488
489 /* fetching the Wage Tax Discount for the given assignment in the tax year
490 it basically returns 0 or 1 depending on the Tax Reduction Flag set to None
491 or any other value, concatenated with the period start date, first changed value
492 of Tax Reduction Flag, first change start date, second changed value of
493 Tax Reduction Flag, second change start date.
494 If the changes are more than three, then the latest 3 changed values and
495 respective dates are picked up.*/
496
497
498 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);
499
500
501 /* fetching the Wage Tax Table Code for the given assignment in the tax year
502 This is basically the tax code.It is obtained from the run results for the
503 input value Tax Code on the element Standard Tax Deduction.
504 If there is a change during the year, then the code that has been set
505 for the longest time during the year is shown.*/
506
507 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);
508
509 if g_debug then
510 hr_utility.set_location('l_Wage_Tax_Discount'||l_Wage_Tax_Discount,450);
511 hr_utility.set_location('l_Wage_Tax_Table_Code'||l_Wage_Tax_Table_Code,450);
512 end if;
513
514
515 /* fetching the income code */
516
517 l_income_code:=get_income_code(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date);
518
519 if g_debug then
520 hr_utility.set_location('l_income_code'||l_income_code,450);
521 end if;
522
523
524 /* fetching the SI Insured Flag */
525
526 l_SI_Insured_Flag:=get_si_insured_flag(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date);
527
528 if g_debug then
529 hr_utility.set_location('l_SI_Insured_Flag'||l_SI_Insured_Flag,500);
530 end if;
531
532 /*fetching the NET_EXPENSE_ALLOWANCE */
533 l_NET_EXPENSE_ALLOWANCE := get_NET_EXPENSE_ALLOWANCE(l_assgt_act_id);
534
535
536 /* fetching the Private Car Use Flag */
537
538 l_Private_Use_Car:=get_PRIVATE_USE_CAR(l_assgt_act_id);
539
540
541 if g_debug then
542 hr_utility.set_location('l_Private_Use_Car'||l_Private_Use_Car,500);
543 end if;
544
545 /* Checking the mandatory fields */
546 Mandatory_Check('PAY_NL_ASG_REQUIRED_FIELD','INCOME_CODE',l_Income_Code);
547
548 if g_error_count=0 then
549
550 /* Creating the archive assignment action */
551
552 SELECT pay_assignment_actions_s.NEXTVAL
553 INTO l_asg_act_id
554 FROM dual;
555 --
556 -- Create the archive assignment action
557 --
558 if g_debug then
559 hr_utility.set_location('Archive Assignment Action Id'||l_asg_act_id,450);
560 hr_utility.set_location('Archive Assignment Id'||l_Assignment_ID,450);
561 hr_utility.set_location('Archive Payroll Action Id'||p_payroll_action_id,450);
562 hr_utility.set_location('creating the archive asst. action',450);
563 hr_utility.set_location('Archive Assignment Action Id'||l_asg_act_id,475);
564 end if;
565
566
567
568
569 /*Creating the Assignment Action for the Assignment
570 and Locking the Latest Payroll Run Assignment Action for the Assignment
571 */
572
573 hr_nonrun_asact.insact(l_asg_act_id,l_Assignment_ID, p_payroll_action_id,p_chunk,NULL);
574 hr_nonrun_asact.insint(l_asg_act_id,l_assgt_act_id);
575
576 if g_debug then
577 hr_utility.set_location('Archive Assignment Action Id'||l_asg_act_id,475);
578 hr_utility.set_location('Action Information row about to create',450);
579 hr_utility.set_location('Coming out of loop',450);
580 hr_utility.set_location('Archive Assignment Action Id'||l_asg_act_id,450);
581 hr_utility.set_location('Archive Assignment Id'||l_Assignment_ID,450);
582 hr_utility.set_location('Archive Payroll Action Id'||p_payroll_action_id,450);
583 hr_utility.set_location('l_ovn'||l_ovn,450);
584 hr_utility.set_location('l_ATS_Process_Date'||l_ATS_Process_Date,450);
585 hr_utility.set_location('l_employer_id'||l_employer_id,450);
586 hr_utility.set_location('l_wage'||l_wage,450);
587 hr_utility.set_location('l_deduct_wage_tax_si_cont'||l_deduct_wage_tax_si_cont,450);
588 hr_utility.set_location('l_asg_start_date'||l_asg_start_date,450);
589 hr_utility.set_location('l_asg_end_date'||l_asg_end_date,450);
590 hr_utility.set_location('l_Labour_Discount'||l_Labour_Discount,450);
591 hr_utility.set_location('l_Wage_Tax_Discount'||l_Wage_Tax_Discount,450);
592 hr_utility.set_location('l_Wage_Tax_Table_Code'||l_Wage_Tax_Table_Code,450);
593 hr_utility.set_location('l_Income_Code'||l_Income_Code,450);
594 hr_utility.set_location('l_Special_Indicator'||l_Special_Indicator,450);
595 hr_utility.set_location('l_Amount_Special_Indicator'||l_Amount_Special_Indicator,450);
596 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,450);
597 hr_utility.set_location('l_SI_Insured_Flag'||l_SI_Insured_Flag,450);
598 hr_utility.set_location('l_Net_Expense_Allowance'||l_Net_Expense_Allowance,450);
599 hr_utility.set_location('l_Private_Use_Car'||l_Private_Use_Car,450);
600 hr_utility.set_location('l_taxable_income'||l_taxable_income,450);
601 end if;
602
603 BEGIN
604
605 SELECT 1 INTO l_active_asg_flag
606 FROM per_all_assignments_f asg, per_assignment_status_types past
607 WHERE asg.assignment_id = l_assignment_id
608 and past.assignment_status_type_id = asg.assignment_status_type_id
609 and past.per_system_status = 'ACTIVE_ASSIGN'
610 and asg.effective_start_date <= l_Tax_Year_End_Date
611 and nvl(asg.effective_end_date, l_Tax_Year_End_Date) >= l_Tax_Year_Start_Date;
612
613
614 EXCEPTION
615
616 WHEN TOO_MANY_ROWS
617 THEN l_active_asg_flag := 1;
618
619 WHEN NO_DATA_FOUND
620 THEN l_active_asg_flag := 0;
621
622 WHEN OTHERS
623 THEN null;
624
625 END;
626
627 IF l_active_asg_flag = 1 OR
628 (l_active_asg_flag = 0 AND
629 (l_wage <> 0 OR l_taxable_income <> 0 OR l_deduct_wage_tax_si_cont <> 0
630 OR l_Labour_Discount <> 0 OR l_ZVW_Basis <> 0 OR l_ZVW_Contribution <> 0
631 OR l_Net_Expense_Allowance <> 0 OR l_Private_Use_Car <> 0
632 OR l_Value_Private_Use_Car <> 0 OR l_LSS_Saved_Amount <> 0 OR l_Employer_Part_Child_Care <> 0
633 OR l_Allowance_On_Disability <> 0 OR l_Applied_LCLD <> 0)) THEN
634
635 pay_action_information_api.create_action_information (
636 p_action_information_id => l_action_info_id
637 ,p_action_context_id => l_asg_act_id
638 ,p_action_context_type => 'AAP'
639 ,p_object_version_number => l_ovn
640 ,p_effective_date => l_ATS_Process_Date
641 ,p_source_id => NULL
642 ,p_source_text => NULL
643 ,p_action_information_category => 'NL ATS EMPLOYEE DETAILS'
644 ,p_action_information1 => fnd_number.number_to_canonical(l_employer_id)
645 ,p_action_information2 => fnd_number.number_to_canonical(l_person_id)
646 ,p_action_information3 => fnd_number.number_to_canonical(l_assignment_id)
647 ,p_action_information4 => l_wage
648 ,p_action_information5 => l_deduct_wage_tax_si_cont
649 ,p_action_information6 => l_asg_start_date
650 ,p_action_information7 => l_asg_end_date
651 ,p_action_information8 => l_Labour_Discount
652 ,p_action_information9 => l_Wage_Tax_Discount
653 ,p_action_information10 => l_Wage_Tax_Table_Code
654 ,p_action_information11 => l_Income_Code
655 ,p_action_information12 => l_Special_Indicator
656 ,p_action_information13 => l_Amount_Special_Indicator
657 ,p_action_information14 => l_SI_Insured_Flag
658 ,p_action_information15 => fnd_number.number_to_canonical(l_ZVW_Contribution)
659 ,p_action_information16 => fnd_number.number_to_canonical(l_Net_Expense_Allowance)
660 ,p_action_information17 => fnd_number.number_to_canonical(l_Private_Use_Car)
661 ,p_action_information18 => l_taxable_income
662 ,p_action_information19 => fnd_number.number_to_canonical(l_ZVW_Basis)
663 ,p_action_information20 => fnd_number.number_to_canonical(l_Value_Private_Use_Car)
664 ,p_action_information21 => fnd_number.number_to_canonical(l_LSS_Saved_Amount)
665 ,p_action_information22 => fnd_number.number_to_canonical(l_Employer_Part_Child_Care)
666 ,p_action_information23 => fnd_number.number_to_canonical(l_Allowance_On_Disability)
667 ,p_action_information24 => fnd_number.number_to_canonical(l_Applied_LCLD)
668 ,p_action_information25 => l_User_Bal_String);
669
670 END IF;
671 end if;
672
673 end if;
674
675 hr_utility.set_location('l_action_info_id'||l_action_info_id,450);
676 hr_utility.set_location('l_ovn'||l_ovn,450);
677
678 if g_debug then
679 hr_utility.set_location('l_action_info_id'||l_action_info_id,450);
680 hr_utility.set_location('l_ovn'||l_ovn,450);
681 end if;
682
683 END LOOP;
684
685 hr_utility.set_location('Exiting ASSIGNMENT_ACTION_CODE',650);
686
687 END ASSIGNMENT_ACTION_CODE;
688
689
690
691 /*-------------------------------------------------------------------------------
692 |Name : ARCHIVE_INIT |
693 |Type : Procedure |
694 |Description : Initialization Code for Archiver |
695 -------------------------------------------------------------------------------*/
696 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER) IS
697
698 BEGIN
699 if g_debug then
700 hr_utility.set_location('Entering Archive Init',600);
701 hr_utility.set_location('Leaving Archive Init',700);
702 end if;
703
704 END ARCHIVE_INIT;
705
706
707
708 /*-------------------------------------------------------------------------------
709 |Name : ARCHIVE_CODE |
710 |Type : Procedure |
711 |Description : Archival code for archiver |
712 -------------------------------------------------------------------------------*/
713
714
715 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
716 ,p_effective_date IN DATE) IS
717
718
719 BEGIN
720 if g_debug then
721
722 hr_utility.set_location('Entering Archive Code',700);
723 hr_utility.set_location('Leaving Archive Code',700);
724 end if;
725
726 END ARCHIVE_CODE;
727
728 /*-----------------------------------------------------------------------------
729 |Name : get_max_assgt_act_id |
730 |Type : Function |
731 |Description: Function which returns the max. assignment_action_id for a given |
732 | assignment_id between a given start and end date |
733 -------------------------------------------------------------------------------*/
734
735 function get_max_assgt_act_id(p_assignment_id number
736 ,p_date_from date
737 ,p_date_to date)RETURN number IS
738
739 CURSOR csr_max_assgt_act_id IS
740 SELECT MAX(assignment_action_id)
741 from pay_assignment_actions paa
742 ,pay_payroll_actions ppa
743 where paa.payroll_action_id =ppa.payroll_action_id
744 and paa.assignment_id = p_assignment_id
745 and ppa.date_earned between p_date_from and p_date_to
746 and ppa.action_type in ('R','B','Q','I','V');
747
748 l_max_assgt_act_id number;
749
750 BEGIN
751
752 if g_debug then
753 hr_utility.set_location('Entering get_max_assgt_act_id',700);
754 end if;
755
756 OPEN csr_max_assgt_act_id;
757 FETCH csr_max_assgt_act_id into l_max_assgt_act_id;
758 CLOSE csr_max_assgt_act_id;
759
760 if g_debug then
761 hr_utility.set_location('l_max_assgt_act_id'||l_max_assgt_act_id,450);
762 hr_utility.set_location('Exiting get_max_assgt_act_id',700);
763 end if;
764
765 return l_max_assgt_act_id;
766
767 END get_max_assgt_act_id;
768
769
770 /*-----------------------------------------------------------------------------
771 |Name : get_context_id |
772 |Type : Function |
773 |Description: Function which returns the context id for a given context neme |
774 -------------------------------------------------------------------------------*/
775
776 function get_context_id(p_context_name VARCHAR2)return number IS
777
778 CURSOR csr_get_context_id IS
779 SELECT context_id
780 FROM ff_contexts ff
781 WHERE ff.context_name = p_context_name;
782
783 l_context_id number;
784
785 BEGIN
786
787 if g_debug then
788 hr_utility.set_location('Entering get_context_id',700);
789 end if;
790
791 OPEN csr_get_context_id;
792 FETCH csr_get_context_id into l_context_id;
793 CLOSE csr_get_context_id;
794
795 if g_debug then
796 hr_utility.set_location('l_context_id'||l_context_id,700);
797 hr_utility.set_location('Exiting get_context_id',700);
798 end if;
799
800 return l_context_id;
801
802
803 END get_context_id;
804
805
806
807 /*-----------------------------------------------------------------------------
808 |Name : get_wage |
809 |Type : Function |
810 |Description: Function which returns the wage for a given assignment action |
811 -------------------------------------------------------------------------------*/
812
813 function get_wage(p_assgt_act_id number)RETURN number IS
814 l_context_id number;
815 l_sum_of_balances number;
816 l_balance_value number;
817 l_defined_balance_id number;
818 l_pre_tax_ded number;
819 l_retro_pre_tax_ded number;
820 l_wage number;
821
822 BEGIN
823 if g_debug then
824 hr_utility.set_location('Entering get_wage',800);
825 end if;
826
827 l_sum_of_balances:=0;
828 l_pre_tax_ded:=0;
829 l_retro_pre_tax_ded:=0;
830 l_context_id:=get_context_id('SOURCE_TEXT');
831
832
833 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SI_INCOME_STANDARD_TAX_ASG_YTD');
834 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
835 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
836
837 if g_debug then
838 hr_utility.set_location('SI_INCOME_STANDARD_TAX_ASG_YTD',425);
839 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
840 hr_utility.set_location('l_balance_value'||l_balance_value,425);
841 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
842 end if;
843
844 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SI_INCOME_SPECIAL_TAX_ASG_YTD');
845 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
846 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
847
848 if g_debug then
849 hr_utility.set_location('SI_INCOME_SPECIAL_TAX_ASG_YTD',425);
850 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
851 hr_utility.set_location('l_balance_value'||l_balance_value,425);
852 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
853 end if;
854
855 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('WAGE_IN_MONEY_STANDARD_TAX_ONLY_ASG_YTD');
856 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
857 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
858
859 if g_debug then
860 hr_utility.set_location('WAGE_IN_MONEY_STANDARD_TAX_ONLY_ASG_YTD',425);
861 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
862 hr_utility.set_location('l_balance_value'||l_balance_value,425);
863 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
864 end if;
865
866 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('WAGE_IN_MONEY_SPECIAL_TAX_ONLY_ASG_YTD');
867 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
868 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
869
870 if g_debug then
871 hr_utility.set_location('WAGE_IN_MONEY_SPECIAL_TAX_ONLY_ASG_YTD',425);
872 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
873 hr_utility.set_location('l_balance_value'||l_balance_value,425);
874 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
875 end if;
876
877 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('WAGE_IN_KIND_STANDARD_TAX_ONLY_ASG_YTD');
878 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
879 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
880
881 if g_debug then
882 hr_utility.set_location('WAGE_IN_KIND_STANDARD_TAX_ONLY_ASG_YTD',425);
883 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
884 hr_utility.set_location('l_balance_value'||l_balance_value,425);
885 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
886 end if;
887
888 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('WAGE_IN_KIND_SPECIAL_TAX_ONLY_ASG_YTD');
889 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
890 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
891
892
893 if g_debug then
894 hr_utility.set_location('WAGE_IN_KIND_SPECIAL_TAX_ONLY_ASG_YTD',425);
895 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
896 hr_utility.set_location('l_balance_value'||l_balance_value,425);
897 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
898 end if;
899
900 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SI_INCOME_STANDARD_TAX_ASG_YTD');
901 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
902 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
903
904
905 if g_debug then
906 hr_utility.set_location('RETRO_SI_INCOME_STANDARD_TAX_ASG_YTD',425);
907 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
908 hr_utility.set_location('l_balance_value'||l_balance_value,425);
909 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
910 end if;
911
912
913 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SI_INCOME_SPECIAL_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_SPECIAL_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_WAGE_IN_MONEY_STANDARD_TAX_ONLY_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 if g_debug then
931 hr_utility.set_location('RETRO_WAGE_IN_MONEY_STANDARD_TAX_ONLY_ASG_YTD',425);
932 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
933 hr_utility.set_location('l_balance_value'||l_balance_value,425);
934 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
935 end if;
936
937
938 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_WAGE_IN_MONEY_SPECIAL_TAX_ONLY_ASG_YTD');
939 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
940 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
941
942
943 if g_debug then
944 hr_utility.set_location('RETRO_WAGE_IN_MONEY_SPECIAL_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_KIND_STANDARD_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_KIND_STANDARD_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 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_WAGE_IN_KIND_SPECIAL_TAX_ONLY_ASG_YTD');
964 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
965 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
966
967 if g_debug then
968 hr_utility.set_location('RETRO_WAGE_IN_KIND_SPECIAL_TAX_ONLY_ASG_YTD',425);
969 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
970 hr_utility.set_location('l_balance_value'||l_balance_value,425);
971 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
972 end if;
973
974
975 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_ZVW_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
976 IF l_context_id IS NULL then
977 l_balance_value:=0;
978 ELSE
979 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
980 END IF;
981 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
982
983 if g_debug then
984 hr_utility.set_location('EMPLOYER_ZVW_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
985 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
986 hr_utility.set_location('l_balance_value'||l_balance_value,425);
987 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
988 end if;
989
990
991
992 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_ZVW_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
993 IF l_context_id IS NULL then
994 l_balance_value:=0;
995 ELSE
996 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
997 END IF;
998 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
999
1000 if g_debug then
1001 hr_utility.set_location('EMPLOYER_ZVW_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
1002 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1003 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1004 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1005 end if;
1006
1007
1008 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_ZVW_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
1009 IF l_context_id IS NULL then
1010 l_balance_value:=0;
1011 ELSE
1012 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
1013 END IF;
1014 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1015
1016 if g_debug then
1017 hr_utility.set_location('RETRO_EMPLOYER_ZVW_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
1018 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1019 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1020 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1021 end if;
1022
1023
1024 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_ZVW_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
1025 IF l_context_id IS NULL then
1026 l_balance_value:=0;
1027 ELSE
1028 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
1029 END IF;
1030 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1031
1032 if g_debug then
1033 hr_utility.set_location('RETRO_EMPLOYER_ZVW_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
1034 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1035 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1036 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1037 end if;
1038
1039
1040 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1041 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1042 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1043
1044 if g_debug then
1045 hr_utility.set_location('EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1046 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1047 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1048 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1049 end if;
1050
1051 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1052 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1053 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1054
1055 if g_debug then
1056 hr_utility.set_location('RETRO_EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1057 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1058 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1059 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1060 end if;
1061
1062
1063 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1064 IF l_context_id IS NULL then
1065 l_balance_value:=0;
1066 ELSE
1067 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWE',null,null);
1068 END IF;
1069 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1070
1071 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1072 IF l_context_id IS NULL then
1073 l_balance_value:=0;
1074 ELSE
1075 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWA',null,null);
1076 END IF;
1077 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1078
1079
1080 if g_debug then
1081 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1082 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1083 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1084 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1085 end if;
1086
1087
1088 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1089 IF l_context_id IS NULL then
1090 l_balance_value:=0;
1091 ELSE
1092 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWE',null,null);
1093 END IF;
1094 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1095
1096
1097 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1098 IF l_context_id IS NULL then
1099 l_balance_value:=0;
1100 ELSE
1101 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWA',null,null);
1102 END IF;
1103 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1104
1105
1106 if g_debug then
1107 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1108 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1109 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1110 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1111 end if;
1112
1113
1114 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1115 IF l_context_id IS NULL then
1116 l_balance_value:=0;
1117 ELSE
1118 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWE',null,null);
1119 END IF;
1120 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1121
1122 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1123 IF l_context_id IS NULL then
1124 l_balance_value:=0;
1125 ELSE
1126 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWA',null,null);
1127 END IF;
1128 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1129
1130
1131
1132 if g_debug then
1133 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1134 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1135 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1136 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1137 end if;
1138
1139
1140 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1141 IF l_context_id IS NULL then
1142 l_balance_value:=0;
1143 ELSE
1144 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWE',null,null);
1145 END IF;
1146 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1147
1148
1149 if g_debug then
1150 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1151 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1152 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1153 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1154 end if;
1155
1156
1157 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1158 IF l_context_id IS NULL then
1159 l_balance_value:=0;
1160 ELSE
1161 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'WEWA',null,null);
1162 END IF;
1163 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1164
1165
1166 if g_debug then
1167 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1168 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1169 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1170 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1171 end if;
1172
1173
1174 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('PRE_TAX_ONLY_DEDUCTIONS_ASG_YTD');
1175 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1176 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1177
1178 if g_debug then
1179 hr_utility.set_location('PRE_TAX_ONLY_DEDUCTIONS_ASG_YTD',425);
1180 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1181 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1182 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1183 end if;
1184
1185 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_PRE_TAX_ONLY_DEDUCTIONS_ASG_YTD');
1186 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1187 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1188
1189 if g_debug then
1190 hr_utility.set_location('RETRO_PRE_TAX_ONLY_DEDUCTIONS_ASG_YTD',425);
1191 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1192 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1193 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1194 end if;
1195
1196 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('STANDARD_TAX_REDUCTION_ASG_YTD');
1197 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1198 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1199
1200 if g_debug then
1201 hr_utility.set_location('STANDARD_TAX_REDUCTION_ASG_YTD',425);
1202 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1203 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1204 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1205 end if;
1206
1207 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SPECIAL_TAX_REDUCTION_ASG_YTD');
1208 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1209 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1210
1211 if g_debug then
1212 hr_utility.set_location('SPECIAL_TAX_REDUCTION_ASG_YTD',425);
1213 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1214 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1215 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1216 end if;
1217
1218 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAX_REDUCTION_ASG_YTD');
1219 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1220 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1221
1222 if g_debug then
1223 hr_utility.set_location('RETRO_STANDARD_TAX_REDUCTION_ASG_YTD',425);
1224 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1225 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1226 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1227 end if;
1228
1229 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SPECIAL_TAX_REDUCTION_ASG_YTD');
1230 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1231 l_sum_of_balances:=l_sum_of_balances - l_balance_value;
1232
1233 if g_debug then
1234 hr_utility.set_location('RETRO_SPECIAL_TAX_REDUCTION_ASG_YTD',425);
1235 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1236 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1237 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1238
1239 hr_utility.set_location('Exiting get_wage',950);
1240 end if;
1241
1242
1243 l_wage:=l_sum_of_balances;
1244
1245
1246 return l_wage;
1247
1248 END get_wage;
1249
1250
1251 /*-----------------------------------------------------------------------------
1252 |Name : get_taxable_income |
1253 |Type : Function |
1254 |Description: Function which returns the taxable income for a given assignment |
1255 | action |
1256 -------------------------------------------------------------------------------*/
1257
1258 function get_taxable_income(p_assgt_act_id number)RETURN number
1259
1260 IS
1261 l_sum_of_balances number;
1262 l_balance_value number;
1263 l_defined_balance_id number;
1264 l_taxable_income number;
1265
1266 BEGIN
1267 if g_debug then
1268 hr_utility.set_location('Entering get_taxable_income',800);
1269 end if;
1270
1271 l_sum_of_balances:=0;
1272
1273
1274 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('STANDARD_TAXABLE_INCOME_ASG_YTD');
1275 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1276 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1277
1278 if g_debug then
1279 hr_utility.set_location('STANDARD_TAXABLE_INCOME_ASG_YTD',425);
1280 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1281 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1282 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1283 end if;
1284
1285
1286 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SPECIAL_TAXABLE_INCOME_ASG_YTD');
1287 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1288 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1289
1290 if g_debug then
1291 hr_utility.set_location('SPECIAL_TAXABLE_INCOME_ASG_YTD',425);
1292 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1293 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1294 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1295 end if;
1296
1297
1298 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAXABLE_INCOME_CURRENT_QUARTER_ASG_YTD');
1299 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1300 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1301
1302 if g_debug then
1303 hr_utility.set_location('RETRO_STANDARD_TAXABLE_INCOME_CURRENT_QUARTER_ASG_YTD',425);
1304 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1305 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1306 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1307 end if;
1308
1309
1310 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAXABLE_INCOME_ASG_YTD');
1311 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1312 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1313
1314 if g_debug then
1315 hr_utility.set_location('RETRO_STANDARD_TAXABLE_INCOME_ASG_YTD',425);
1316 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1317 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1318 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1319 end if;
1320
1321
1322 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SPECIAL_TAXABLE_INCOME_ASG_YTD');
1323 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1324 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
1325
1326 if g_debug then
1327 hr_utility.set_location('RETRO_SPECIAL_TAXABLE_INCOME_ASG_YTD',425);
1328 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1329 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1330 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
1331 end if;
1332
1333
1334 l_taxable_income:=l_sum_of_balances;
1335
1336
1337 return l_taxable_income;
1338
1339 END get_taxable_income;
1340
1341
1342 /*-----------------------------------------------------------------------------
1343 |Name : get_deduct_wage_tax_si_cont |
1344 |Type : Function |
1345 |Description: Function which returns the deduct_wage_tax value |
1346 | for a given assignment action |
1347 -------------------------------------------------------------------------------*/
1348
1349 function get_deduct_wage_tax_si_cont(p_assgt_act_id number) return number IS
1350
1351 l_wt_and_ni_cont number;
1352 l_balance_value number;
1353 l_defined_balance_id number;
1354 l_deduct_wage_tax_si_cont number;
1355
1356 BEGIN
1357
1358 if g_debug then
1359 hr_utility.set_location('Entering get_deduct_wage_tax_si_cont',1000);
1360 end if;
1361
1362 l_wt_and_ni_cont:=0;
1363
1364 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('STANDARD_TAX_DEDUCTION_ASG_YTD');
1365 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1366 l_wt_and_ni_cont:= l_wt_and_ni_cont + l_balance_value;
1367
1368
1369 if g_debug then
1370 hr_utility.set_location('STANDARD_TAX_DEDUCTION_ASG_YTD',425);
1371 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1372 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1373 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1374 end if;
1375
1376
1377 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SPECIAL_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('SPECIAL_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('RETRO_STANDARD_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('RETRO_STANDARD_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_SPECIAL_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_SPECIAL_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_STANDARD_TAX_DEDUCTION_CURRENT_QUARTER_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_STANDARD_TAX_DEDUCTION_CURRENT_QUARTER_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('STANDARD_TAX_CORRECTION_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('STANDARD_TAX_CORRECTION_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('SPECIAL_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('SPECIAL_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_deduct_wage_tax_si_cont:=ceil(l_wt_and_ni_cont);
1456
1457
1458 if g_debug then
1459 hr_utility.set_location('l_wt_and_ni_cont'||l_wt_and_ni_cont,425);
1460 hr_utility.set_location('Exiting get_deduct_wage_tax_si_cont',1050);
1461 end if;
1462 return l_deduct_wage_tax_si_cont;
1463
1464 end get_deduct_wage_tax_si_cont;
1465
1466
1467 /*-----------------------------------------------------------------------------
1468 |Name : get_labour_discount |
1469 |Type : Function |
1470 |Description: Function which returns the labour discount value |
1471 | for a given assignment action |
1472 -------------------------------------------------------------------------------*/
1473
1474 function get_labour_discount(p_assgt_act_id number) return number IS
1475
1476 l_labour_discount number;
1477 l_balance_value number;
1478 l_defined_balance_id number;
1479
1480 BEGIN
1481 if g_debug then
1482 hr_utility.set_location('Entering get_labour_discount',1050);
1483 end if;
1484
1485 l_labour_discount:=0;
1486
1487 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('LABOUR_TAX_REDUCTION_ASG_YTD');
1488 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1489 l_labour_discount:= l_labour_discount + l_balance_value;
1490
1491
1492 if g_debug then
1493 hr_utility.set_location('LABOUR_TAX_REDUCTION_ASG_YTD',425);
1494 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1495 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1496 hr_utility.set_location('l_labour_discount'||l_labour_discount,425);
1497 end if;
1498
1499 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_LABOUR_TAX_REDUCTION_ASG_YTD');
1500 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1501 l_labour_discount:= l_labour_discount + l_balance_value;
1502
1503 if g_debug then
1504 hr_utility.set_location('RETRO_LABOUR_TAX_REDUCTION_ASG_YTD',425);
1505 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1506 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1507 hr_utility.set_location('l_labour_discount'||l_labour_discount,425);
1508 end if;
1509
1510 l_labour_discount:= ceil(l_labour_discount);
1511
1512
1513 if g_debug then
1514 hr_utility.set_location('l_labour_discount'||l_labour_discount,425);
1515 hr_utility.set_location('Exiting get_labour_discount',1050);
1516 end if;
1517
1518 return l_labour_discount;
1519
1520 end get_labour_discount;
1521
1522
1523 /*-----------------------------------------------------------------------------
1524 |Name : get_ZFW_PHI_contributions |
1525 |Type : Function |
1526 |Description: Function which returns the ZFW PHI contributions |
1527 -------------------------------------------------------------------------------*/
1528
1529 function get_ZFW_PHI_contributions(p_assgt_act_id number) return number IS
1530
1531 l_ZFW_PHI_contribution number;
1532 l_context_id number;
1533 l_balance_value number;
1534 l_defined_balance_id number;
1535
1536 BEGIN
1537
1538 hr_utility.set_location('Entering get_ZFW_PHI_Contribution',1050);
1539
1540 l_ZFW_PHI_contribution:=0;
1541
1542 l_context_id:=get_context_id('SOURCE_TEXT');
1543
1544
1545
1546 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1547 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1548 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1549
1550
1551 if g_debug then
1552 hr_utility.set_location('EMPLOYEE_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1553 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1554 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1555 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1556 end if;
1557
1558
1559
1560 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1561 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1562 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1563
1564
1565 if g_debug then
1566 hr_utility.set_location('EMPLOYER_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1567 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1568 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1569 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1570 end if;
1571
1572
1573
1574 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD');
1575 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1576 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1577
1578
1579 if g_debug then
1580 hr_utility.set_location('RETRO_EMPLOYEE_PRIVATE_HEALTH_CONTRIBUTION_ASG_YTD',425);
1581 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1582 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1583 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1584 end if;
1585
1586
1587 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_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_EMPLOYER_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
1601 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_IZA_CONTRIBUTION_ASG_YTD');
1602 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1603 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1604
1605
1606 if g_debug then
1607 hr_utility.set_location('EMPLOYEE_IZA_CONTRIBUTION_ASG_YTD',425);
1608 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1609 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1610 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1611 end if;
1612
1613
1614 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_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('EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_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_SPECIAL_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_SPECIAL_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_NON_TAXABLE_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_NON_TAXABLE_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('RETRO_EMPLOYEE_IZA_CONTRIBUTION_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('RETRO_EMPLOYEE_IZA_CONTRIBUTION_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_EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_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_EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_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_SPECIAL_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_SPECIAL_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 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_IZA_CONTRIBUTION_NON_TAXABLE_ASG_YTD');
1692 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
1693 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1694
1695
1696 if g_debug then
1697 hr_utility.set_location('RETRO_EMPLOYER_IZA_CONTRIBUTION_NON_TAXABLE_ASG_YTD',425);
1698 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1699 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1700 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1701 end if;
1702
1703
1704
1705 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD');
1706 IF l_context_id IS NULL then
1707 l_balance_value:=0;
1708 ELSE
1709 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1710 END IF;
1711 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1712
1713
1714 if g_debug then
1715 hr_utility.set_location('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD',425);
1716 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1717 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1718 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1719 end if;
1720
1721
1722
1723 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1724 IF l_context_id IS NULL then
1725 l_balance_value:=0;
1726 ELSE
1727 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1728 END IF;
1729 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1730
1731 if g_debug then
1732 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1733 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1734 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1735 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1736 end if;
1737
1738
1739
1740 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1741 IF l_context_id IS NULL then
1742 l_balance_value:=0;
1743 ELSE
1744 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1745 END IF;
1746 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1747
1748
1749 if g_debug then
1750 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1751 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1752 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1753 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1754 end if;
1755
1756
1757
1758 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD');
1759 IF l_context_id IS NULL then
1760 l_balance_value:=0;
1761 ELSE
1762 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1763 END IF;
1764 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1765
1766
1767 if g_debug then
1768 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD',425);
1769 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1770 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1771 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1772 end if;
1773
1774
1775
1776 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_SI_CONTRIBUTION_ASG_SIT_YTD');
1777 IF l_context_id IS NULL then
1778 l_balance_value:=0;
1779 ELSE
1780 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1781 END IF;
1782 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1783
1784
1785 if g_debug then
1786 hr_utility.set_location('EMPLOYER_SI_CONTRIBUTION_ASG_SIT_YTD',425);
1787 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1788 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1789 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1790 end if;
1791
1792
1793
1794 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1795 IF l_context_id IS NULL then
1796 l_balance_value:=0;
1797 ELSE
1798 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1799 END IF;
1800 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1801
1802
1803 if g_debug then
1804 hr_utility.set_location('EMPLOYER_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1805 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1806 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1807 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1808 end if;
1809
1810
1811
1812 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1813 IF l_context_id IS NULL then
1814 l_balance_value:=0;
1815 ELSE
1816 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1817 END IF;
1818 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1819
1820
1821 if g_debug then
1822 hr_utility.set_location('EMPLOYER_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1823 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1824 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1825 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1826 end if;
1827
1828
1829
1830 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD');
1831 IF l_context_id IS NULL then
1832 l_balance_value:=0;
1833 ELSE
1834 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1835 END IF;
1836 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1837
1838 if g_debug then
1839 hr_utility.set_location('EMPLOYER_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD',425);
1840 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1841 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1842 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1843 end if;
1844
1845
1846 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD');
1847 IF l_context_id IS NULL then
1848 l_balance_value:=0;
1849 ELSE
1850 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1851 END IF;
1852 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1853
1854
1855 if g_debug then
1856 hr_utility.set_location('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD',425);
1857 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1858 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1859 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1860 end if;
1861
1862
1863
1864
1865 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1866 IF l_context_id IS NULL then
1867 l_balance_value:=0;
1868 ELSE
1869 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1870 END IF;
1871 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1872
1873
1874 if g_debug then
1875 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1876 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1877 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1878 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1879 end if;
1880
1881
1882 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1883 IF l_context_id IS NULL then
1884 l_balance_value:=0;
1885 ELSE
1886 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1887 END IF;
1888 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1889
1890
1891 if g_debug then
1892 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1893 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1894 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1895 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1896 end if;
1897
1898
1899
1900 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD');
1901 IF l_context_id IS NULL then
1902 l_balance_value:=0;
1903 ELSE
1904 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1905 END IF;
1906 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1907
1908
1909 if g_debug then
1910 hr_utility.set_location('RETRO_EMPLOYEE_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD',425);
1911 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1912 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1913 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1914 end if;
1915
1916
1917 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_SI_CONTRIBUTION_ASG_SIT_YTD');
1918 IF l_context_id IS NULL then
1919 l_balance_value:=0;
1920 ELSE
1921 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1922 END IF;
1923 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1924
1925
1926 if g_debug then
1927 hr_utility.set_location('RETRO_EMPLOYER_SI_CONTRIBUTION_ASG_SIT_YTD',425);
1928 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1929 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1930 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1931 end if;
1932
1933
1934
1935 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
1936 IF l_context_id IS NULL then
1937 l_balance_value:=0;
1938 ELSE
1939 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1940 END IF;
1941 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1942
1943
1944 if g_debug then
1945 hr_utility.set_location('RETRO_EMPLOYER_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
1946 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1947 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1948 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1949 end if;
1950
1951
1952
1953 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
1954 IF l_context_id IS NULL then
1955 l_balance_value:=0;
1956 ELSE
1957 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1958 END IF;
1959 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1960
1961
1962 if g_debug then
1963 hr_utility.set_location('RETRO_EMPLOYER_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
1964 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1965 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1966 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1967 end if;
1968
1969
1970
1971 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD');
1972 IF l_context_id IS NULL then
1973 l_balance_value:=0;
1974 ELSE
1975 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZFW',null,null);
1976 END IF;
1977 l_ZFW_PHI_Contribution:=l_ZFW_PHI_Contribution + l_balance_value;
1978
1979
1980 if g_debug then
1981 hr_utility.set_location('RETRO_EMPLOYER_SI_CONTRIBUTION_NON_TAXABLE_ASG_SIT_YTD',425);
1982 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
1983 hr_utility.set_location('l_balance_value'||l_balance_value,425);
1984 hr_utility.set_location('l_ZFW_PHI_Contribution'||l_ZFW_PHI_Contribution,425);
1985 end if;
1986
1987
1988 l_ZFW_PHI_Contribution:=round(l_ZFW_PHI_Contribution,2);
1989
1990 return l_ZFW_PHI_Contribution;
1991
1992
1993 END get_ZFW_PHI_contributions;
1994
1995
1996 /*-----------------------------------------------------------------------------
1997 |Name : get_special_indicators |
1998 |Type : Procedure |
1999 |Description: Procedure which returns the special indicators string |
2000 -------------------------------------------------------------------------------*/
2001
2002 Procedure get_special_indicators(p_assgt_act_id in number
2003 ,p_assignment_id in number
2004 ,p_tax_year_start_date in date
2005 ,p_tax_year_end_date in date
2006 ,p_special_indicator out nocopy varchar2
2007 ,p_Amount_Special_indicator out nocopy varchar2) IS
2008
2009 cursor csr_tax_year_assgn_act_id is
2010 select paa.assignment_action_id,ppa.date_earned
2011 from pay_assignment_actions paa
2012 ,pay_payroll_actions ppa
2013 where
2014 paa.assignment_id = p_assignment_id and
2015 ppa.payroll_action_id = paa.payroll_action_id and
2016 ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date
2017 and ppa.action_type in ('R','Q')
2018 and ppa.action_status = 'C';
2019
2020 cursor csr_run_result_id(lp_assignment_action_id number, lp_element_type_id number) is
2021 select prr.run_result_id
2022 from pay_run_results prr
2023 where
2024 prr.element_type_id=lp_element_type_id and
2025 prr.assignment_action_id=lp_assignment_action_id;
2026
2027 l_balance_value number;
2028 l_defined_balance_id number;
2029 l_spl_indicator1 varchar2(2);
2030 l_spl_indicator1_amount varchar2(9);
2031 l_spl_indicator2 varchar2(2);
2032 l_spl_indicator2_amount varchar2(9);
2033 l_spl_indicator3 varchar2(2);
2034 l_spl_indicator3_amount varchar2(9);
2035 l_spl_indicator4 varchar2(2);
2036 l_spl_indicator4_amount varchar2(9);
2037 l_spl_indicator5 varchar2(2);
2038 l_spl_indicator5_amount varchar2(9);
2039 l_spl_indicator6 varchar2(2);
2040 l_spl_indicator6_amount varchar2(9);
2041 l_comp_car varchar2(1);
2042 l_element_type_id number;
2043 l_input_value_id number;
2044 l_run_result_id number;
2045 l_run_result_value varchar2(255);
2046 l_eff_date date;
2047
2048 BEGIN
2049
2050 --if g_debug then
2051 hr_utility.set_location('Entering get_special_indicators',1200);
2052 --end if;
2053
2054 /*for csr_tax_year_assgn_act_id_rec in csr_tax_year_assgn_act_id
2055 loop
2056 l_element_type_id:=pay_nl_general.get_element_type_id('Standard Tax Deduction',csr_tax_year_assgn_act_id_rec.date_earned);
2057 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);
2058
2059 OPEN csr_run_result_id(csr_tax_year_assgn_act_id_rec.assignment_action_id,l_element_type_id);
2060 FETCH csr_run_result_id into l_run_result_id;
2061 CLOSE csr_run_result_id;
2062
2063 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');
2064
2065 IF substr (l_run_result_value, 1, 2) = '01' OR
2066 substr (l_run_result_value, 3, 2) = '01' OR
2067 substr (l_run_result_value, 5, 2) = '01' OR
2068 substr (l_run_result_value, 7, 2) = '01' OR
2069 substr (l_run_result_value, 9, 2) = '01' OR
2070 substr (l_run_result_value, 11, 2) = '01' OR
2071 substr (l_run_result_value, 13, 2) = '01' OR
2072 substr (l_run_result_value, 15, 2) = '01' OR
2073 substr (l_run_result_value, 17, 2) = '01' OR
2074 substr (l_run_result_value, 19, 2) = '01' OR
2075 substr (l_run_result_value, 21, 2) = '01' OR
2076 substr (l_run_result_value, 23, 2) = '01' OR
2077 substr (l_run_result_value, 25, 2) = '01' THEN
2078
2079 l_comp_car_spl_indicator := 'Y';
2080 exit;
2081 ELSE
2082 l_comp_car_spl_indicator := 'N';
2083 END IF;
2084 end loop;*/
2085
2086 BEGIN
2087
2088 select ppa.date_earned
2089 into l_eff_date
2090 from pay_payroll_actions ppa,
2091 pay_assignment_actions paa
2092 where paa.assignment_action_id = p_assgt_act_id
2093 and ppa.payroll_action_id = paa.payroll_action_id;
2094
2095 EXCEPTION
2096
2097 WHEN NO_DATA_FOUND
2098 THEN null;
2099
2100 WHEN OTHERS
2101 THEN null;
2102
2103 END;
2104 --if g_debug then
2105 hr_utility.set_location('l_eff_date - '||l_eff_date,427);
2106 --end if;
2107
2108 BEGIN
2109
2110 SELECT peev.screen_entry_value
2111 INTO l_comp_car
2112 FROM pay_element_types_f pet
2113 ,pay_input_values_f piv
2114 ,pay_element_entries_f peef
2115 ,pay_element_entry_values_f peev
2116 WHERE pet.element_name = 'Company Car Private Usage'
2117 AND pet.element_type_id = piv.element_type_id
2118 AND piv.name = 'Code Usage'
2119 AND pet.legislation_code = 'NL'
2120 AND piv.legislation_code = 'NL'
2121 AND peef.assignment_id = p_assignment_id
2122 AND peef.element_entry_id = peev.element_entry_id
2123 AND peef.element_type_id = pet.element_type_id
2124 AND peev.input_value_id = piv.input_value_id
2125 AND l_eff_date BETWEEN piv.effective_start_date
2126 AND piv.effective_end_date
2127 AND l_eff_date BETWEEN pet.effective_start_date
2128 AND pet.effective_end_date
2129 AND l_eff_date BETWEEN peev.effective_start_date
2130 AND peev.effective_end_date
2131 AND l_eff_date BETWEEN peef.effective_start_date
2132 AND peef.effective_end_date;
2133
2134 EXCEPTION
2135
2136 WHEN NO_DATA_FOUND
2137 THEN l_comp_car:=' ';
2138
2139 WHEN OTHERS
2140 THEN l_comp_car:=' ';
2141
2142 END;
2143 --if g_debug then
2144 hr_utility.set_location('l_comp_car - '||l_comp_car,427);
2145 --end if;
2146
2147 g_debug:=true;
2148
2149 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_TRAVEL_ALLOWANCE_ASG_YTD');
2150 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2151 if l_balance_value <> 0 then
2152 l_spl_indicator1 := '04';
2153 l_spl_indicator1_amount := l_balance_value;
2154 else
2155 l_spl_indicator1 := '00';
2156 l_spl_indicator1_amount := '000000';
2157 end if;
2158
2159 if g_debug then
2160 hr_utility.set_location('TAX_TRAVEL_ALLOWANCE_ASG_YTD',427);
2161 hr_utility.set_location('l_spl_indicator1'||l_spl_indicator1,427);
2162 hr_utility.set_location('l_spl_indicator1_amount'||l_spl_indicator1_amount,427);
2163 end if;
2164
2165 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_SEA_DAYS_DISCOUNT_ASG_YTD');
2166 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2167 if l_balance_value <> 0 then
2168 l_spl_indicator2 := '17';
2169 l_spl_indicator2_amount := l_balance_value;
2170 else
2171 l_spl_indicator2 := '00';
2172 l_spl_indicator2_amount := '000000';
2173 end if;
2174
2175 if g_debug then
2176 hr_utility.set_location('l_spl_indicator2'||l_spl_indicator2,427);
2177 hr_utility.set_location('l_spl_indicator2_amount'||l_spl_indicator2_amount,427);
2178 hr_utility.set_location('TAX_SEA_DAYS_DISCOUNT_ASG_YTD',427);
2179 end if;
2180
2181
2182 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_ABW_ALLOWANCE_ASG_YTD');
2183 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2184 if l_balance_value <> 0 then
2185 l_spl_indicator3 := '25';
2186 l_spl_indicator3_amount := l_balance_value;
2187 else
2188 l_spl_indicator3 := '00';
2189 l_spl_indicator3_amount := '000000';
2190 end if;
2191
2192
2193 if g_debug then
2194 hr_utility.set_location('TAX_ABW_ALLOWANCE_ASG_YTD',427);
2195 hr_utility.set_location('l_spl_indicator3'||l_spl_indicator3,427);
2196 hr_utility.set_location('l_spl_indicator3_amount'||l_spl_indicator3_amount,427);
2197 end if;
2198
2199
2200 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_ABW_ALLOWANCE_STOPPAGE_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_indicator4 := '26';
2204 l_spl_indicator4_amount := l_balance_value;
2205 else
2206 l_spl_indicator4 := '00';
2207 l_spl_indicator4_amount := '000000';
2208 end if;
2209
2210
2211 if g_debug then
2212 hr_utility.set_location('TAX_ABW_ALLOWANCE_STOPPAGE_ASG_YTD',427);
2213 hr_utility.set_location('l_spl_indicator4'||l_spl_indicator4,427);
2214 hr_utility.set_location('l_spl_indicator4_amount'||l_spl_indicator4_amount,427);
2215 end if;
2216
2217
2218 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_WAO_ALLOWANCE_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_indicator5 := '61';
2222 l_spl_indicator5_amount := l_balance_value;
2223 else
2224 l_spl_indicator5 := '00';
2225 l_spl_indicator5_amount := '000000';
2226 end if;
2227
2228 if g_debug then
2229 hr_utility.set_location('TAX_WAO_ALLOWANCE_ASG_YTD',427);
2230 hr_utility.set_location('l_spl_indicator5'||l_spl_indicator5,427);
2231 hr_utility.set_location('l_spl_indicator5_amount'||l_spl_indicator5_amount,427);
2232 end if;
2233
2234 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('TAX_TOTAL_ZFW_CONTRIBUTION_ASG_YTD');
2235 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2236 if l_balance_value <> 0 then
2237 l_spl_indicator6 := '64';
2238 l_spl_indicator6_amount := l_balance_value;
2239 else
2240 l_spl_indicator6 := '00';
2241 l_spl_indicator6_amount := '000000';
2242 end if;
2243
2244 if g_debug then
2245 hr_utility.set_location('TAX_TOTAL_ZFW_CONTRIBUTION_ASG_YTD',427);
2246 hr_utility.set_location('l_spl_indicator6'||l_spl_indicator6,427);
2247 hr_utility.set_location('l_spl_indicator6_amount'||l_spl_indicator6_amount,427);
2248 end if;
2249
2250
2251 p_special_indicator:=l_spl_indicator1||l_spl_indicator2||l_spl_indicator3||l_spl_indicator4||l_spl_indicator5||l_spl_indicator6 || l_comp_car;
2252
2253 if g_debug then
2254 hr_utility.set_location('p_special_indicator'||p_special_indicator,427);
2255 end if;
2256
2257 l_spl_indicator1_amount:=FLOOR(l_spl_indicator1_amount);
2258 if l_spl_indicator1_amount < 0 then
2259 l_spl_indicator1_amount:='-'||lpad(l_spl_indicator1_amount*(-1),5,0);
2260 else
2261 l_spl_indicator1_amount:=lpad(l_spl_indicator1_amount,6,0);
2262 end if;
2263
2264 if g_debug then
2265 hr_utility.set_location('l_spl_indicator1_amount'||l_spl_indicator1_amount,427);
2266 end if;
2267
2268 l_spl_indicator2_amount:=FLOOR(l_spl_indicator2_amount);
2269 if l_spl_indicator2_amount < 0 then
2270 l_spl_indicator2_amount:='-'||lpad(l_spl_indicator2_amount*(-1),5,0);
2271 else
2272 l_spl_indicator2_amount:=lpad(l_spl_indicator2_amount,6,0);
2273 end if;
2274
2275 if g_debug then
2276 hr_utility.set_location('l_spl_indicator2_amount'||l_spl_indicator2_amount,427);
2277 end if;
2278
2279 l_spl_indicator3_amount:=FLOOR(l_spl_indicator3_amount);
2280 if l_spl_indicator3_amount < 0 then
2281 l_spl_indicator3_amount:='-'||lpad(l_spl_indicator3_amount*(-1),5,0);
2282 else
2283 l_spl_indicator3_amount:=lpad(l_spl_indicator3_amount,6,0);
2284 end if;
2285
2286 if g_debug then
2287 hr_utility.set_location('l_spl_indicator3_amount'||l_spl_indicator3_amount,427);
2288 end if;
2289
2290 l_spl_indicator4_amount:=FLOOR(l_spl_indicator4_amount);
2291 if l_spl_indicator4_amount < 0 then
2292 l_spl_indicator4_amount:='-'||lpad(l_spl_indicator4_amount*(-1),5,0);
2293 else
2294 l_spl_indicator4_amount:=lpad(l_spl_indicator4_amount,6,0);
2295 end if;
2296
2297 if g_debug then
2298 hr_utility.set_location('l_spl_indicator4_amount'||l_spl_indicator4_amount,427);
2299 end if;
2300
2301 l_spl_indicator5_amount:=FLOOR(l_spl_indicator5_amount);
2302 if l_spl_indicator5_amount < 0 then
2303 l_spl_indicator5_amount:='-'||lpad(l_spl_indicator5_amount*(-1),5,0);
2304 else
2305 l_spl_indicator5_amount:=lpad(l_spl_indicator5_amount,6,0);
2306 end if;
2307
2308 if g_debug then
2309 hr_utility.set_location('l_spl_indicator5_amount'||l_spl_indicator5_amount,427);
2310 end if;
2311
2312 l_spl_indicator6_amount:=FLOOR(l_spl_indicator6_amount);
2313 if l_spl_indicator6_amount < 0 then
2314 l_spl_indicator6_amount:='-'||lpad(l_spl_indicator6_amount*(-1),5,0);
2315 else
2316 l_spl_indicator6_amount:=lpad(l_spl_indicator6_amount,6,0);
2317 end if;
2318
2319 if g_debug then
2320 hr_utility.set_location('l_spl_indicator6_amount'||l_spl_indicator6_amount,427);
2321 end if;
2322
2323 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;
2324
2325 if g_debug then
2326 hr_utility.set_location('p_Amount_Special_indicator'||p_Amount_Special_indicator,427);
2327 hr_utility.set_location('Exiting get_special_indicators',1300);
2328 end if;
2329 g_debug:=false;
2330
2331 END get_special_indicators;
2332
2333 /*-----------------------------------------------------------------------------
2334 |Name : get_PRIVATE_USE_CAR |
2335 |Type : Function |
2336 |Description: Function which returns the private use car balance value |
2337 -------------------------------------------------------------------------------*/
2338
2339 function get_PRIVATE_USE_CAR(p_assgt_act_id number) return number IS
2340 l_balance_value number;
2341 l_defined_balance_id number;
2342 l_Private_Use_Car number;
2343
2344 BEGIN
2345 if g_debug then
2346 hr_utility.set_location('Entering get_PRIVATE_USE_CAR',1300);
2347 end if;
2348
2349 l_Private_Use_Car:=0;
2350
2351 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('PRIVATE_USE_CAR_ASG_YTD');
2352 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2353 l_Private_Use_Car:=l_balance_value;
2354 hr_utility.set_location('l_Private_Use_Car'||l_Private_Use_Car,425);
2355
2356 if g_debug then
2357 hr_utility.set_location('Exiting get_PRIVATE_USE_CAR',1300);
2358 end if;
2359 return l_Private_Use_Car;
2360
2361
2362 END get_PRIVATE_USE_CAR;
2363
2364
2365 /*-----------------------------------------------------------------------------
2366 |Name : get_NET_EXPENSE_ALLOWANCE |
2367 |Type : Function |
2368 |Description: Function which returns the NET EXPENSE ALLOWANCE balance value |
2369 -------------------------------------------------------------------------------*/
2370
2371 function get_NET_EXPENSE_ALLOWANCE(p_assgt_act_id number) return number IS
2372 l_balance_value number;
2373 l_defined_balance_id number;
2374 l_NET_EXPENSE_ALLOWANCE number;
2375
2376 BEGIN
2377
2378 if g_debug then
2379 hr_utility.set_location('Entering get_NET_EXPENSE_ALLOWANCE',1350);
2380 end if;
2381
2382 l_NET_EXPENSE_ALLOWANCE:=0;
2383
2384 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('NET_EXPENSE_ALLOWANCE_ASG_YTD');
2385 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2386 l_Net_Expense_Allowance:=l_balance_value;
2387
2388 if g_debug then
2389 hr_utility.set_location('l_Net_Expense_Allowance'||l_Net_Expense_Allowance,425);
2390 end if;
2391
2392
2393 if g_debug then
2394 hr_utility.set_location('Exiting get_NET_EXPENSE_ALLOWANCE',1350);
2395 end if;
2396
2397 return l_NET_EXPENSE_ALLOWANCE;
2398
2399
2400 END get_NET_EXPENSE_ALLOWANCE;
2401
2402 /*-----------------------------------------------------------------------------
2403 |Name : get_wage_tax_discount |
2404 |Type : Function |
2405 |Description: Function which returns the wage tax discount value |
2406 -------------------------------------------------------------------------------*/
2407
2408 function get_wage_tax_discount(p_assignment_id number
2409 ,p_tax_year_start_date date
2410 ,p_tax_year_end_date date) return varchar2 IS
2411
2412 TYPE wtd_rec IS RECORD (
2413 date_earned DATE,
2414 code VARCHAR2(20) ,
2415 period_start_date DATE );
2416
2417 TYPE wtd_filter_rec IS RECORD (
2418 date_earned DATE,
2419 code VARCHAR2(20),
2420 period_start_date DATE);
2421
2422 TYPE wtd_table IS TABLE OF wtd_rec INDEX BY BINARY_INTEGER;
2423 TYPE wtd_filter_table IS TABLE OF wtd_filter_rec INDEX BY BINARY_INTEGER;
2424
2425 l_wtd_table wtd_table;
2426 l_wtd_filter_table wtd_filter_table;
2427
2428 CURSOR csr_wtd_code is
2429 /* select decode(prrv.result_value,'NL_NONE','0','1') code,ppa.date_earned,paa.assignment_action_id,ptp.start_date --,prrv.result_value
2430 from
2431 pay_payroll_actions ppa,
2432 pay_assignment_actions paa,
2433 pay_element_types_f pet,
2434 pay_input_values_f piv,
2435 pay_run_results prr,
2436 pay_run_result_values prrv,
2437 per_time_periods ptp
2438 where
2439 pet.element_name='Standard Tax Deduction' and
2440 pet.element_type_id=piv.element_type_id and
2441 piv.name='Tax Reduction Flag' and
2442 ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
2443 ppa.payroll_action_id=paa.payroll_action_id and
2444 paa.assignment_id = p_assignment_id and
2445 prrv.input_value_id=piv.input_value_id and
2446 ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
2447 ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
2448 paa.assignment_action_id=prr.assignment_action_id and
2449 prrv.run_result_id=prr.run_result_id and
2450 ptp.time_period_id=ppa.time_period_id
2451 order by date_earned,paa.assignment_action_id; */
2452
2453 /* 14934380*/
2454
2455 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
2456 from
2457 pay_assignment_actions paa,
2458 pay_payroll_actions ppa,
2459 pay_element_types_f pet,
2460 pay_input_values_f piv,
2461 pay_run_results prr,
2462 pay_run_result_values prrv,
2463 per_time_periods ptp
2464 where
2465 pet.element_name='Standard Tax Deduction' and
2466 pet.element_type_id=piv.element_type_id and
2467 piv.name='Tax Reduction Flag' and
2468 ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
2469 ppa.payroll_action_id=paa.payroll_action_id and
2470 paa.assignment_id = p_assignment_id and
2471 -- ppa.business_group_id = p_bg_id and
2472 prrv.input_value_id=piv.input_value_id and
2473 ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
2474 ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
2475 paa.assignment_action_id=prr.assignment_action_id and
2476 prrv.run_result_id=prr.run_result_id and
2477 ptp.time_period_id=ppa.time_period_id and
2478 ptp.payroll_id = ppa.payroll_id
2479 order by date_earned,paa.assignment_action_id;
2480
2481
2482
2483 l_index number;
2484 l_index1 number;
2485 l_loop_count number;
2486 l_wts_code1 varchar2(1);
2487 l_wts_date1 varchar2(4);
2488 l_wts_code2 varchar2(1);
2489 l_wts_date2 varchar2(4);
2490 l_wts_code3 varchar2(1);
2491 l_wts_date3 varchar2(4);
2492 l_code varchar2(1);
2493 l_date_earned date;
2494 l_period_start_date date;
2495 l_filter_table_count number;
2496 l_wage_tax_discount varchar2(255);
2497
2498 BEGIN
2499
2500 if g_debug then
2501 hr_utility.set_location('Entering get_wage_tax_discount',1400);
2502 end if;
2503
2504 l_index:=0;
2505 l_index1:=0;
2506 l_loop_count:=0;
2507 l_wts_code1:='0';
2508 l_wts_date1:='0000';
2509 l_wts_code2:='0';
2510 l_wts_date2:='0000';
2511 l_wts_code3:='0';
2512 l_wts_date3:='0000';
2513
2514 for csr_wtd_code_rec in csr_wtd_code
2515
2516 loop
2517 l_loop_count:=l_loop_count+1;
2518 l_code:=csr_wtd_code_rec.code;
2519 l_date_earned:=csr_wtd_code_rec.date_earned;
2520 l_period_start_date:=csr_wtd_code_rec.start_date;
2521
2522 if l_loop_count=1 then
2523 l_wtd_table(l_index).code:=csr_wtd_code_rec.code;
2524
2525
2526 l_wtd_table(l_index).period_start_date:=csr_wtd_code_rec.start_date;
2527
2528
2529
2530 l_wtd_table(l_index).date_earned:=csr_wtd_code_rec.date_earned;
2531
2532
2533 end if;
2534 if l_loop_count>1 then
2535
2536
2537 if l_date_earned=l_wtd_table(l_index).date_earned then
2538 l_wtd_table(l_index).code:=l_code;
2539 l_wtd_table(l_index).date_earned:=l_date_earned;
2540 l_wtd_table(l_index).period_start_date:=l_period_start_date;
2541 else
2542 l_index:=l_index+1;
2543
2544 l_wtd_table(l_index).code:=l_code;
2545 l_wtd_table(l_index).date_earned:=l_date_earned;
2546 l_wtd_table(l_index).period_start_date:=l_period_start_date;
2547
2548 end if;
2549 end if;
2550 end loop;
2551
2552 FOR l_count IN 1 .. l_wtd_table.count
2553
2554
2555 LOOP
2556
2557
2558 if l_count=1 then
2559 l_wtd_filter_table(l_index1).date_earned:=l_wtd_table(l_index1).date_earned;
2560 l_wtd_filter_table(l_index1).code:=l_wtd_table(l_index1).code;
2561 l_wtd_filter_table(l_index1).period_start_date:=l_wtd_table(l_index1).period_start_date;
2562 end if;
2563 if l_count>1 then
2564
2565 if l_wtd_table(l_count-1).code<>l_wtd_filter_table(l_index1).code then
2566 l_index1:=l_index1+1;
2567 l_wtd_filter_table(l_index1).code:=l_wtd_table(l_count-1).code;
2568 l_wtd_filter_table(l_index1).date_earned:=l_wtd_table(l_count-1).date_earned;
2569 l_wtd_filter_table(l_index1).period_start_date:=l_wtd_table(l_count-1).period_start_date;
2570 end if;
2571 end if;
2572
2573
2574 END LOOP;
2575
2576 l_filter_table_count:=l_wtd_filter_table.count;
2577 IF l_filter_table_count>3 then
2578 l_wts_code1:=l_wtd_filter_table(l_filter_table_count-3).code;
2579 l_wts_date1:=to_char(l_wtd_filter_table(l_filter_table_count-3).period_start_date,'DDMM');
2580 l_wts_code2:=l_wtd_filter_table(l_filter_table_count-2).code;
2581 l_wts_date2:=to_char(l_wtd_filter_table(l_filter_table_count-2).period_start_date,'DDMM');
2582 l_wts_code3:=l_wtd_filter_table(l_filter_table_count-1).code;
2583 l_wts_date3:=to_char(l_wtd_filter_table(l_filter_table_count-1).period_start_date,'DDMM');
2584 ELSE
2585 if l_filter_table_count=3 then
2586 l_wts_code1:=l_wtd_filter_table(0).code;
2587 l_wts_date1:=to_char(l_wtd_filter_table(0).period_start_date,'DDMM');
2588 l_wts_code2:=l_wtd_filter_table(1).code;
2589 l_wts_date2:=to_char(l_wtd_filter_table(1).period_start_date,'DDMM');
2590 l_wts_code3:=l_wtd_filter_table(2).code;
2591 l_wts_date3:=to_char(l_wtd_filter_table(2).period_start_date,'DDMM');
2592 elsif l_filter_table_count=2 then
2593 l_wts_code1:=l_wtd_filter_table(0).code;
2594 l_wts_date1:=to_char(l_wtd_filter_table(0).period_start_date,'DDMM');
2595 l_wts_code2:=l_wtd_filter_table(1).code;
2596 l_wts_date2:=to_char(l_wtd_filter_table(1).period_start_date,'DDMM');
2597 elsif l_filter_table_count=1 then
2598 l_wts_code1:=l_wtd_filter_table(0).code;
2599 l_wts_date1:=to_char(l_wtd_filter_table(0).period_start_date,'DDMM');
2600 end if;
2601 END IF;
2602
2603 if g_debug then
2604 hr_utility.set_location('l_wts_code1'||l_wts_code1,425);
2605 hr_utility.set_location('l_wts_code2'||l_wts_code2,425);
2606 hr_utility.set_location('l_wts_date2'||l_wts_date2,425);
2607 hr_utility.set_location('l_wts_code3'||l_wts_code3,425);
2608 hr_utility.set_location('l_wts_date3'||l_wts_date3,425);
2609 end if;
2610
2611 l_wage_tax_discount:=l_wts_code1 || l_wts_date1 || l_wts_code2 || l_wts_date2 || l_wts_code3 || l_wts_date3;
2612
2613 if g_debug then
2614 hr_utility.set_location('l_wage_tax_discount'||l_wage_tax_discount,1500);
2615 hr_utility.set_location('Exiting get_wage_tax_discount',1500);
2616 end if;
2617
2618 return l_wage_tax_discount;
2619
2620
2621
2622 END get_wage_tax_discount;
2623
2624 /*-----------------------------------------------------------------------------
2625 |Name : get_wage_tax_table_code |
2626 |Type : Function |
2627 |Description: Function which returns the wage tax table code |
2628 -------------------------------------------------------------------------------*/
2629
2630 function get_wage_tax_table_code(p_assignment_id number
2631 ,p_tax_year_start_date date
2632 ,p_tax_year_end_date date)return varchar2 IS
2633
2634 CURSOR csr_wage_tax_table_code is
2635 /* select count(prrv.result_value) counter,prrv.result_value
2636 from
2637 pay_payroll_actions ppa,
2638 pay_assignment_actions paa,
2639 pay_element_types_f pet,
2640 pay_input_values_f piv,
2641 pay_run_results prr,
2642 pay_run_result_values prrv
2643 where
2644 pet.element_name='Standard Tax Deduction' and
2645 pet.element_type_id=piv.element_type_id and
2646 piv.name='Tax Code' and
2647 ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
2648 ppa.payroll_action_id=paa.payroll_action_id and
2649 paa.assignment_id = p_assignment_id and
2650 prrv.input_value_id=piv.input_value_id
2651 and
2652 paa.assignment_action_id=prr.assignment_action_id and
2653 ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
2654 ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
2655 prrv.run_result_id=prr.run_result_id
2656 group by prrv.result_value
2657 order by counter desc; */
2658
2659 /* 14934399*/
2660 select /*+ ORDERED */ count(prrv.result_value) counter,prrv.result_value
2661 from
2662 pay_assignment_actions paa,
2663 pay_payroll_actions ppa,
2664 pay_element_types_f pet,
2665 pay_input_values_f piv,
2666 pay_run_results prr,
2667 pay_run_result_values prrv
2668 where
2669 pet.element_name='Standard Tax Deduction' and
2670 pet.element_type_id=piv.element_type_id and
2671 piv.name='Tax Code' and
2672 --ppa.business_group_id = p_bg_id and
2673 ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date and
2674 ppa.payroll_action_id=paa.payroll_action_id and
2675 paa.assignment_id = p_assignment_id and
2676 prrv.input_value_id=piv.input_value_id
2677 and
2678 paa.assignment_action_id=prr.assignment_action_id and
2679 ppa.date_earned between pet.effective_start_date and pet.effective_end_date and
2680 ppa.date_earned between piv.effective_start_date and piv.effective_end_date and
2681 prrv.run_result_id=prr.run_result_id
2682 group by prrv.result_value
2683 order by counter desc;
2684
2685
2686
2687 l_wage_tax_table_code varchar2(5);
2688 l_count number;
2689
2690 BEGIN
2691
2692 if g_debug then
2693 hr_utility.set_location('Entering get_wage_tax_table_code',1500);
2694 end if;
2695
2696 OPEN csr_wage_tax_table_code;
2697 FETCH csr_wage_tax_table_code into l_count,l_wage_tax_table_code;
2698 CLOSE csr_wage_tax_table_code;
2699
2700 if g_debug then
2701 hr_utility.set_location('l_count'||l_count,1500);
2702 hr_utility.set_location('l_wage_tax_table_code'||l_wage_tax_table_code,1500);
2703 end if;
2704
2705 return l_wage_tax_table_code;
2706
2707 if g_debug then
2708 hr_utility.set_location('Exiting get_wage_tax_table_code',1500);
2709 end if;
2710 END get_wage_tax_table_code;
2711
2712
2713 /*-----------------------------------------------------------------------------
2714 |Name : get_si_insured_flag |
2715 |Type : Function |
2716 |Description: Function which returns the si insured flag string |
2717 -------------------------------------------------------------------------------*/
2718
2719 function get_si_insured_flag(p_assignment_id number
2720 ,p_tax_year_start_date date
2721 ,p_tax_year_end_date date) return varchar2 is
2722
2723 CURSOR csr_si_insured_flag(lp_element_name varchar2) is
2724 /* 14934414 */
2725 select 1 from dual
2726 where exists
2727 (select /*+ USE_NL(paa, ppa, pet,prr) */ prr.run_result_id,ppa.date_earned from pay_payroll_actions ppa
2728 ,pay_assignment_actions paa
2729 ,pay_run_results prr
2730 ,pay_element_types_f pet
2731 where ppa.payroll_action_id = paa.payroll_action_id
2732 and paa.assignment_id = p_assignment_id
2733 and paa.assignment_action_id = paa.assignment_action_id
2734 and ppa.date_earned between p_tax_year_start_date and p_tax_year_end_date
2735 and ppa.action_type in ('R','Q','B','I','V')
2736 and paa.assignment_action_id=prr.assignment_action_id
2737 and pet.element_type_id=prr.element_type_id
2738 and pet.element_name=lp_element_name
2739 and ppa.date_earned between pet.effective_start_date and pet.effective_end_date);
2740
2741 l_si_insured_flag varchar2(2);
2742 l_SI_Insured_Flag1 varchar2(1);
2743 l_SI_Insured_Flag2 varchar2(1);
2744 l_flag varchar2(20);
2745
2746
2747 BEGIN
2748
2749 if g_debug then
2750 hr_utility.set_location('Entering l_SI_Insured_Flag',425);
2751 end if;
2752
2753
2754 OPEN csr_si_insured_flag('WAO Basis Social Insurance');
2755 FETCH csr_si_insured_flag into l_flag;
2756 if l_flag is not null then
2757 l_SI_Insured_Flag1:='1';
2758 else
2759 l_SI_Insured_Flag1:='0';
2760 end if;
2761 CLOSE csr_si_insured_flag;
2762
2763 l_flag:=NULL;
2764
2765
2766 /*OPEN csr_si_insured_flag('ZFW Social Insurance');
2767 FETCH csr_si_insured_flag into l_flag;
2768 if l_flag is not null then
2769 l_SI_Insured_Flag2:='1';
2770 else
2771 l_SI_Insured_Flag2:='0';
2772 end if;
2773 CLOSE csr_si_insured_flag;
2774
2775
2776 l_SI_Insured_Flag:= l_SI_Insured_Flag1||l_SI_Insured_Flag2;
2777
2778 if g_debug then
2779 hr_utility.set_location('l_SI_Insured_Flag'||l_SI_Insured_Flag,425);
2780 hr_utility.set_location('l_SI_Insured_Flag1'||l_SI_Insured_Flag1,425);
2781 hr_utility.set_location('l_SI_Insured_Flag2'||l_SI_Insured_Flag2,425);
2782 hr_utility.set_location('Exiting l_SI_Insured_Flag',425);
2783 end if;*/
2784
2785 return l_SI_Insured_Flag1;
2786
2787 END get_si_insured_flag;
2788
2789
2790 /*-----------------------------------------------------------------------------
2791 |Name : get_income_code |
2792 |Type : Function |
2793 |Description: Function which returns the income code |
2794 -------------------------------------------------------------------------------*/
2795
2796 function get_income_code(p_assignment_id number
2797 ,p_tax_year_start_date date
2798 ,p_tax_year_end_date date)return varchar2 is
2799
2800
2801 cursor csr_get_income_code is
2802 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
2803 from per_all_assignments_f paa,hr_soft_coding_keyflex sck
2804 where paa.assignment_id = p_assignment_id
2805 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)
2806 and (paa.effective_end_date <= p_tax_year_end_date or paa.effective_start_date <= p_tax_year_end_date)
2807 and sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
2808 group by sck.segment8
2809 order by Days desc;
2810
2811
2812 l_income_code varchar2(10);
2813 l_days number;
2814
2815 BEGIN
2816
2817
2818 OPEN csr_get_income_code;
2819 FETCH csr_get_income_code into l_income_code,l_days;
2820 CLOSE csr_get_income_code;
2821
2822 return l_income_code;
2823
2824 END get_income_code;
2825
2826
2827 /*-----------------------------------------------------------------------------
2828 |Name : get_org_hierarchy |
2829 |Type : Function |
2830 |Description: Function which returns organization structure version id |
2831 -------------------------------------------------------------------------------*/
2832
2833 function get_org_hierarchy(p_org_struct_id varchar2
2834 ,p_tax_year_end_date date) return number IS
2835
2836 cursor csr_org_hierarchy IS
2837 select
2838 posv.org_structure_version_id
2839 from
2840 per_organization_structures pos,
2841 per_org_structure_versions posv
2842 where pos.organization_structure_id = posv.organization_structure_id
2843 and to_char(pos.organization_structure_id) = p_org_struct_id
2844 and p_tax_year_end_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);
2845
2846 l_org_structure_version_id number;
2847
2848 BEGIN
2849 OPEN csr_org_hierarchy;
2850 FETCH csr_org_hierarchy INTO l_org_structure_version_id;
2851 CLOSE csr_org_hierarchy;
2852
2853 hr_utility.set_location('l_org_structure_version_id'||l_org_structure_version_id,425);
2854
2855 return l_org_structure_version_id;
2856
2857 END get_org_hierarchy;
2858
2859
2860
2861 function get_IZA_contributions(p_assgt_act_id number) return number IS
2862
2863 l_IZA_contributions number;
2864 l_balance_value number;
2865 l_defined_balance_id number;
2866
2867 BEGIN
2868
2869 hr_utility.set_location('Entering get_IZA_contributions',1050);
2870
2871 l_IZA_contributions:=0;
2872
2873 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
2874 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2875 l_IZA_contributions:=l_IZA_contributions + l_balance_value;
2876
2877 if g_debug then
2878 hr_utility.set_location('EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
2879 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2880 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2881 hr_utility.set_location('l_IZA_contributions'||l_IZA_contributions,425);
2882 end if;
2883
2884
2885 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
2886 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2887 l_IZA_contributions:=l_IZA_contributions + l_balance_value;
2888
2889 if g_debug then
2890 hr_utility.set_location('EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
2891 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2892 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2893 hr_utility.set_location('l_IZA_contributions'||l_IZA_contributions,425);
2894 end if;
2895
2896
2897 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD');
2898 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2899 l_IZA_contributions:=l_IZA_contributions + l_balance_value;
2900
2901 if g_debug then
2902 hr_utility.set_location('RETRO_EMPLOYER_IZA_CONTRIBUTION_STANDARD_TAX_ASG_YTD',425);
2903 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2904 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2905 hr_utility.set_location('l_IZA_contributions'||l_IZA_contributions,425);
2906 end if;
2907
2908
2909 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD');
2910 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2911 l_IZA_contributions:=l_IZA_contributions + l_balance_value;
2912
2913 if g_debug then
2914 hr_utility.set_location('RETRO_EMPLOYER_IZA_CONTRIBUTION_SPECIAL_TAX_ASG_YTD',425);
2915 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2916 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2917 hr_utility.set_location('l_IZA_contributions'||l_IZA_contributions,425);
2918
2919 hr_utility.set_location('Exiting get_IZA_contributions',950);
2920 end if;
2921
2922
2923 return l_IZA_contributions;
2924
2925 END get_IZA_contributions;
2926
2927
2928 function get_ZVW_basis(p_assgt_act_id number) return number IS
2929
2930 l_ZVW_basis number := 0;
2931 l_balance_value number := 0;
2932 l_defined_balance_id number;
2933
2934 BEGIN
2935
2936 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('ZVW_INCOME_STANDARD_TAX_ASG_YTD');
2937 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2938 l_ZVW_basis:=l_ZVW_basis + l_balance_value;
2939
2940 if g_debug then
2941 hr_utility.set_location('ZVW_INCOME_STANDARD_TAX_ASG_YTD',425);
2942 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2943 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2944 hr_utility.set_location('l_ZVW_basis'||l_ZVW_basis,425);
2945 end if;
2946
2947 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('ZVW_INCOME_SPECIAL_TAX_ASG_YTD');
2948 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2949 l_ZVW_basis:=l_ZVW_basis + l_balance_value;
2950
2951 if g_debug then
2952 hr_utility.set_location('ZVW_INCOME_SPECIAL_TAX_ASG_YTD',425);
2953 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2954 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2955 hr_utility.set_location('l_ZVW_basis'||l_ZVW_basis,425);
2956 end if;
2957
2958 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_ZVW_INCOME_STANDARD_TAX_ASG_YTD');
2959 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2960 l_ZVW_basis:=l_ZVW_basis + l_balance_value;
2961
2962 if g_debug then
2963 hr_utility.set_location('RETRO_ZVW_INCOME_STANDARD_TAX_ASG_YTD',425);
2964 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2965 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2966 hr_utility.set_location('l_ZVW_basis'||l_ZVW_basis,425);
2967 end if;
2968
2969 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_ZVW_INCOME_SPECIAL_TAX_ASG_YTD');
2970 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
2971 l_ZVW_basis:=l_ZVW_basis + l_balance_value;
2972
2973 if g_debug then
2974 hr_utility.set_location('RETRO_ZVW_INCOME_SPECIAL_TAX_ASG_YTD',425);
2975 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
2976 hr_utility.set_location('l_balance_value'||l_balance_value,425);
2977 hr_utility.set_location('l_ZVW_basis'||l_ZVW_basis,425);
2978 end if;
2979
2980 l_ZVW_basis := floor(l_ZVW_basis);
2981 return l_ZVW_basis;
2982
2983 END get_ZVW_basis;
2984
2985
2986 function get_ZVW_contributions(p_assgt_act_id number) return number IS
2987
2988 l_ZVW_contribution number;
2989 l_context_id number;
2990 l_balance_value number;
2991 l_defined_balance_id number;
2992
2993 BEGIN
2994
2995 hr_utility.set_location('Entering get_ZVW_Contribution',1050);
2996
2997 l_ZVW_Contribution:=0;
2998
2999 l_context_id:=get_context_id('SOURCE_TEXT');
3000
3001
3002 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD');
3003 IF l_context_id IS NULL then
3004 l_balance_value:=0;
3005 ELSE
3006 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3007 END IF;
3008 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3009
3010
3011 if g_debug then
3012 hr_utility.set_location('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD',425);
3013 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3014 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3015 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3016 end if;
3017
3018
3019
3020 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD');
3021 IF l_context_id IS NULL then
3022 l_balance_value:=0;
3023 ELSE
3024 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3025 END IF;
3026 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3027
3028 if g_debug then
3029 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_YTD',425);
3030 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3031 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3032 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3033 end if;
3034
3035
3036
3037 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD');
3038 IF l_context_id IS NULL then
3039 l_balance_value:=0;
3040 ELSE
3041 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,'ZVW',null,null);
3042 END IF;
3043 l_ZVW_Contribution:=l_ZVW_Contribution + l_balance_value;
3044
3045
3046 if g_debug then
3047 hr_utility.set_location('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_YTD',425);
3048 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3049 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3050 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3051 end if;
3052
3053
3054 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_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
3063 if g_debug then
3064 hr_utility.set_location('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_YTD',425);
3065 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
3066 hr_utility.set_location('l_balance_value'||l_balance_value,425);
3067 hr_utility.set_location('l_ZVW_Contribution'||l_ZVW_Contribution,425);
3068 end if;
3069
3070
3071 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_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('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_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_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_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_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_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 l_ZVW_Contribution:=ceil(l_ZVW_Contribution);
3105
3106 return l_ZVW_Contribution;
3107
3108
3109 END get_ZVW_Contributions;
3110
3111
3112 /*-----------------------------------------------------------------------------
3113 |Name : get_VALUE_PRIVATE_USE_CAR |
3114 |Type : Function |
3115 |Description: Function which returns the value private use car balance value |
3116 -------------------------------------------------------------------------------*/
3117
3118 function get_VALUE_PRIVATE_USE_CAR(p_assgt_act_id number) return number IS
3119 l_balance_value number;
3120 l_defined_balance_id number;
3121 l_Value_Private_Use_Car number;
3122
3123 BEGIN
3124 if g_debug then
3125 hr_utility.set_location('Entering get_VALUE_PRIVATE_USE_CAR',1300);
3126 end if;
3127
3128 l_Value_Private_Use_Car:=0;
3129
3130 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('VALUE_PRIVATE_USAGE_COMPANY_CAR_ASG_YTD');
3131 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3132 l_Value_Private_Use_Car:=l_balance_value;
3133 hr_utility.set_location('l_Value_Private_Use_Car'||l_Value_Private_Use_Car,425);
3134
3135 if g_debug then
3136 hr_utility.set_location('Exiting get_VALUE_PRIVATE_USE_CAR',1300);
3137 end if;
3138 return l_Value_Private_Use_Car;
3139
3140
3141 END get_VALUE_PRIVATE_USE_CAR;
3142
3143
3144 /*-----------------------------------------------------------------------------
3145 |Name : get_LSS_Saved_Amount |
3146 |Type : Function |
3147 |Description: Function which returns the saved amount for life saving scheme |
3148 -------------------------------------------------------------------------------*/
3149
3150 function get_LSS_Saved_Amount(p_assgt_act_id number) return number IS
3151 l_balance_value number;
3152 l_defined_balance_id number;
3153 l_LSS_Saved_Amount number;
3154
3155 BEGIN
3156 if g_debug then
3157 hr_utility.set_location('Entering get_LSS_Saved_Amount',1300);
3158 end if;
3159
3160 l_LSS_Saved_Amount:=0;
3161
3162 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYEE_LIFE_SAVINGS_CONTRIBUTION_ASG_YTD');
3163 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3164 l_LSS_Saved_Amount:=l_LSS_Saved_Amount + l_balance_value;
3165
3166 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('EMPLOYER_LIFE_SAVINGS_CONTRIBUTION_ASG_YTD');
3167 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3168 l_LSS_Saved_Amount:=l_LSS_Saved_Amount + l_balance_value;
3169
3170 hr_utility.set_location('l_LSS_Saved_Amount'||l_LSS_Saved_Amount,425);
3171
3172 if g_debug then
3173 hr_utility.set_location('Exiting get_LSS_Saved_Amount',1300);
3174 end if;
3175 return l_LSS_Saved_Amount;
3176
3177
3178 END get_LSS_Saved_Amount;
3179
3180
3181 /*-----------------------------------------------------------------------------
3182 |Name : get_Employer_Part_Child_Care |
3183 |Type : Function |
3184 |Description: Function which returns the Employer part Child Care balance value|
3185 -------------------------------------------------------------------------------*/
3186
3187 function get_Employer_Part_Child_Care(p_assgt_act_id number) return number IS
3188 l_balance_value number;
3189 l_defined_balance_id number;
3190 l_Employer_Part_Child_Care number;
3191
3192 BEGIN
3193 if g_debug then
3194 hr_utility.set_location('Entering get_Employer_Part_Child_Care',1300);
3195 end if;
3196
3197 l_Employer_Part_Child_Care:=0;
3198
3199 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('CHILD_CARE_EMPLOYER_CONTRIBUTION_ASG_YTD');
3200 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3201 l_Employer_Part_Child_Care:=l_balance_value;
3202 hr_utility.set_location('l_Employer_Part_Child_Care'||l_Employer_Part_Child_Care,425);
3203
3204 if g_debug then
3205 hr_utility.set_location('Exiting get_Employer_Part_Child_Care',1300);
3206 end if;
3207 return l_Employer_Part_Child_Care;
3208
3209
3210 END get_Employer_Part_Child_Care;
3211
3212
3213 /*-----------------------------------------------------------------------------
3214 |Name : get_Allowance_On_Disability |
3215 |Type : Function |
3216 |Description: Function which returns the paid allowance on Disability Allowance|
3217 -------------------------------------------------------------------------------*/
3218
3219 function get_Allowance_On_Disability(p_assgt_act_id number) return number IS
3220 l_balance_value number;
3221 l_defined_balance_id number;
3222 l_Allowance_On_Disability number;
3223
3224 BEGIN
3225 if g_debug then
3226 hr_utility.set_location('Entering get_Allowance_On_Disability',1300);
3227 end if;
3228
3229 l_Allowance_On_Disability:=0;
3230
3231 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('PAID_DISABILITY_ALLOWANCE_ASG_YTD');
3232 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3233 l_Allowance_On_Disability:=l_balance_value;
3234 hr_utility.set_location('l_Allowance_On_Disability'||l_Allowance_On_Disability,425);
3235
3236 if g_debug then
3237 hr_utility.set_location('Exiting get_Allowance_On_Disability',1300);
3238 end if;
3239 return l_Allowance_On_Disability;
3240
3241
3242 END get_Allowance_On_Disability;
3243
3244
3245 /*-----------------------------------------------------------------------------
3246 |Name : get_Applied_LCLD |
3247 |Type : Function |
3248 |Description: Function which returns the Applied Life Cycle Leave Discount |
3249 -------------------------------------------------------------------------------*/
3250
3251 function get_Applied_LCLD(p_assgt_act_id number) return number IS
3252 l_balance_value number;
3253 l_defined_balance_id number;
3254 l_Applied_LCLD number;
3255
3256 BEGIN
3257 if g_debug then
3258 hr_utility.set_location('Entering get_Applied_LCLD',1300);
3259 end if;
3260
3261 l_Applied_LCLD:=0;
3262
3263 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('LIFE_CYCLE_LEAVE_DISCOUNT_ASG_YTD');
3264 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3265 l_Applied_LCLD:=l_Applied_LCLD + l_balance_value;
3266
3267 /* l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_LIFE_CYCLE_LEAVE_DISCOUNT_ASG_YTD');
3268 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3269 l_Applied_LCLD:=l_Applied_LCLD + l_balance_value;*/
3270
3271 hr_utility.set_location('l_Applied_LCLD'||l_Applied_LCLD,425);
3272
3273 if g_debug then
3274 hr_utility.set_location('Exiting get_Applied_LCLD',1300);
3275 end if;
3276 return l_Applied_LCLD;
3277
3278
3279 END get_Applied_LCLD;
3280
3281
3282 /*-----------------------------------------------------------------------------
3283 |Name : populate_UserBal |
3284 |Type : Procedure |
3285 |Description: Procedure which populates pl/sql table with user defined balance |
3286 | names and tag names |
3287 -------------------------------------------------------------------------------*/
3288
3289 PROCEDURE populate_UserBal(p_bg_id number, p_effective_date DATE) IS
3290
3291 CURSOR csr_get_rows IS
3292 select pur.user_row_id
3293 from pay_user_rows_f pur,
3294 pay_user_tables put
3295 where put.user_table_name='NL_ATS_USER_BALANCES'
3296 and put.legislation_code='NL'
3297 and pur.user_table_id=put.user_table_id
3298 and p_effective_date between pur.effective_start_date and pur.effective_end_date;
3299
3300 v_csr_get_rows csr_get_rows%ROWTYPE;
3301 vCtr NUMBER;
3302 vBalColId NUMBER;
3303 vTagColId NUMBER;
3304 vBalName VARCHAR2(1000);
3305 vTagName VARCHAR2(1000);
3306
3307 BEGIN
3308
3309 vUserBalTable.DELETE;
3310 vCtr := 0;
3311 vBalColId := null;
3312 vTagColId := null;
3313
3314 BEGIN
3315
3316 SELECT puc.user_column_id
3317 INTO vBalColId
3318 FROM pay_user_columns puc,
3319 pay_user_tables put
3320 WHERE put.user_table_name='NL_ATS_USER_BALANCES'
3321 and put.legislation_code='NL'
3322 and put.user_table_id=puc.user_table_id
3323 and puc.user_column_name='BAL_NAME';
3324
3325 EXCEPTION
3326
3327 WHEN NO_DATA_FOUND
3328 THEN vBalColId := null;
3329
3330 WHEN OTHERS
3331 THEN vBalColId := null;
3332
3333 END;
3334
3335 BEGIN
3336
3337 SELECT puc.user_column_id
3338 INTO vTagColId
3339 FROM pay_user_columns puc,
3340 pay_user_tables put
3341 WHERE put.user_table_name='NL_ATS_USER_BALANCES'
3342 and put.legislation_code='NL'
3343 and put.user_table_id=puc.user_table_id
3344 and puc.user_column_name='TAG_NAME';
3345
3346 EXCEPTION
3347
3348 WHEN NO_DATA_FOUND
3349 THEN vTagColId := null;
3350
3351 WHEN OTHERS
3352 THEN vTagColId := null;
3353
3354 END;
3355
3356 IF vBalColId is NOT NULL and vTagColId is NOT NULL THEN
3357
3358 FOR v_csr_get_rows IN csr_get_rows
3359 LOOP
3360
3361 vBalName := null;
3362 vTagName := null;
3363
3364 BEGIN
3365
3366 SELECT puci.value
3367 INTO vBalName
3368 FROM pay_user_column_instances_f puci
3369 WHERE puci.user_row_id=v_csr_get_rows.user_row_id
3370 AND puci.user_column_id=vBalColId
3371 AND p_effective_date between puci.effective_start_date and puci.effective_end_date;
3372
3373 EXCEPTION
3374
3375 WHEN NO_DATA_FOUND
3376 THEN vBalName := null;
3377
3378 WHEN OTHERS
3379 THEN vBalName := null;
3380
3381 END;
3382
3383 BEGIN
3384
3385 SELECT puci.value
3386 INTO vTagName
3387 FROM pay_user_column_instances_f puci
3388 WHERE puci.user_row_id=v_csr_get_rows.user_row_id
3389 AND puci.user_column_id=vTagColId
3390 AND p_effective_date between puci.effective_start_date and puci.effective_end_date;
3391
3392 EXCEPTION
3393
3394 WHEN NO_DATA_FOUND
3395 THEN vTagName := null;
3396
3397 WHEN OTHERS
3398 THEN vTagName := null;
3399
3400 END;
3401
3402 IF vBalName is not NULL and vTagName is not NULL THEN
3403
3404 vUserBalTable(vCtr).BalName := replace(upper(vBalName),' ','_')||'_ASG_YTD';
3405 vUserBalTable(vCtr).TagName := replace(vTagName,' ','');
3406 vCtr := vCtr + 1;
3407
3408 END IF;
3409
3410 END LOOP;
3411
3412 END IF;
3413
3414 END populate_UserBal;
3415
3416 /*-----------------------------------------------------------------------------
3417 |Name : get_User_Balances |
3418 |Type : Procedure |
3419 |Description: Procedure which returns the User Defined Balances |
3420 -------------------------------------------------------------------------------*/
3421
3422 PROCEDURE get_User_Balances (p_assgt_act_id in number
3423 ,p_bg_id in number
3424 ,p_User_Bal_String out nocopy varchar2) IS
3425
3426 l_defined_balance_id number;
3427 l_balance_value number;
3428 l_balance_string varchar2(255) := null;
3429 l_ctr_table number;
3430
3431 BEGIN
3432
3433 IF vUserBalTable.count > 0 THEN
3434
3435 FOR l_ctr_table IN vUserBalTable.FIRST .. vUserBalTable.LAST LOOP
3436
3437 l_defined_balance_id := null;
3438 l_balance_value := 0;
3439
3440 l_defined_balance_id:=get_User_Defined_Balance_Id(vUserBalTable(l_ctr_table).BalName,p_bg_id);
3441
3442 IF l_defined_balance_id is NULL THEN
3443 l_balance_value := 0;
3444 ELSE
3445 l_balance_value := pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
3446 END IF;
3447
3448 l_balance_string := l_balance_string||fnd_number.number_to_canonical(ceil(l_balance_value))||'|';
3449
3450 END LOOP;
3451
3452 p_User_Bal_String := l_balance_string;
3453
3454 END IF;
3455
3456 END get_User_Balances;
3457
3458
3459 /*-----------------------------------------------------------------------------
3460 |Name : get_User_Defined_Balance_Id |
3461 |Type : Procedure |
3462 |Description: Procedure which returns the User Defined Balance Id |
3463 -------------------------------------------------------------------------------*/
3464
3465 FUNCTION get_User_Defined_Balance_Id (p_user_name IN VARCHAR2, p_bg_id IN NUMBER) RETURN NUMBER IS
3466 /* Cursor to retrieve User Defined Balance Id */
3467 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
3468 SELECT u.creator_id
3469 FROM ff_user_entities u,
3470 ff_database_items d
3471 WHERE d.user_name = p_user_name
3472 AND u.user_entity_id = d.user_entity_id
3473 AND (u.legislation_code is NULL )
3474 AND (u.business_group_id = p_bg_id )
3475 AND u.creator_type = 'B';
3476
3477 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
3478
3479 BEGIN
3480 OPEN csr_def_bal_id(p_user_name);
3481 FETCH csr_def_bal_id INTO l_defined_balance_id;
3482 CLOSE csr_def_bal_id;
3483 RETURN l_defined_balance_id;
3484
3485 END get_User_Defined_Balance_Id;
3486
3487 END PAY_NL_TAXOFFICE_ARCHIVE;