[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_CBS_FILE
Source
1 package body PAY_NL_CBS_FILE as
2 /* $Header: pynlcbsf.pkb 120.0.12000000.1 2007/01/17 22:54:38 appldev noship $ */
3
4 g_package varchar2(33) := 'PAY_NL_CBS_FILE';
5 g_error_flag varchar2(60);
6 g_warning_flag varchar2(30);
7 g_error_count NUMBER := 0;
8 g_payroll_action_id NUMBER;
9 g_assignment_number VARCHAR2(30);
10 g_full_name VARCHAR2(150);
11 g_debug boolean ;
12 g_reporting_date DATE;
13 g_message_name VARCHAR2(255) := ' ';
14
15 g_working_hours_formula_exists BOOLEAN := TRUE;
16 g_working_hours_formula_cached BOOLEAN := FALSE;
17 g_working_hours_formula_id ff_formulas_f.formula_id%TYPE;
18 g_working_hours_formula_name ff_formulas_f.formula_name%TYPE;
19
20
21 /*******************************************************************************
22 |Name : GET_ALL_PARAMETERS |
23 |Type : Procedure |
24 |Description: Procedure which returns all the parameters of the archive process|
25 ********************************************************************************/
26
27 PROCEDURE get_all_parameters (
28 p_payroll_action_id IN NUMBER
29 ,p_business_group_id OUT NOCOPY NUMBER
30 ,p_reporting_date OUT NOCOPY DATE
31 ,p_effective_date OUT NOCOPY DATE
32 ,p_employer OUT NOCOPY NUMBER
33 ,p_si_provider OUT NOCOPY NUMBER
34 ,p_org_struct_id OUT NOCOPY NUMBER
35 ,p_medium_code OUT NOCOPY NUMBER
36 ,p_density OUT NOCOPY NUMBER
37 ) IS
38 --
39 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
40 SELECT fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTING_DATE',legislative_parameters))
41 ,pay_core_utils.get_parameter('EMPLOYER_ID',legislative_parameters)
42 ,pay_core_utils.get_parameter('ORG_STRUCT_ID',legislative_parameters)
43 ,pay_core_utils.get_parameter('SI_PROVIDER_ID',legislative_parameters)
44 ,pay_core_utils.get_parameter('MEDIUM_CODE',legislative_parameters)
45 ,pay_core_utils.get_parameter('DENSITY',legislative_parameters)
46 ,effective_date
47 ,business_group_id
48 FROM pay_payroll_actions
49 WHERE payroll_action_id = p_payroll_action_id;
50 --
51 -- l_effective_date date;
52 -- l_proc VARCHAR2(80):= g_package||' get_all_parameters ';
53 --
54 BEGIN
55 --
56 -- hr_utility.set_location('Entered get all parameters',425);
57
58 OPEN csr_parameter_info (p_payroll_action_id);
59 FETCH csr_parameter_info INTO
60 p_reporting_date,p_employer, p_org_struct_id,p_si_provider,p_medium_code,p_density
61 ,p_effective_date,p_business_group_id;
62 CLOSE csr_parameter_info;
63 g_reporting_date:= p_reporting_date;
64 if g_debug then
65 hr_utility.set_location('Executed the cursor in get all parameters ',425);
66 hr_utility.set_location('p_reporting_date'||p_reporting_date,425);
67 hr_utility.set_location('p_employer'||p_employer,425);
68 hr_utility.set_location('p_org_struct_id'||p_org_struct_id,425);
69 hr_utility.set_location('p_si_provider'||p_si_provider,425);
70 hr_utility.set_location('p_medium_code'||p_medium_code,425);
71 hr_utility.set_location('p_density'||p_density,425);
72 hr_utility.set_location('p_effective_date'||p_effective_date,425);
73 hr_utility.set_location('p_business_group_id'||p_business_group_id,425);
74 end if;
75 -- hr_utility.set_location('Leaving get all parameters',425);
76
77 --
78 END get_all_parameters;
79 --
80
81 /********************************************************************************
82 |Name : Mandatory_Check |
83 |Type : Procedure |
84 |Description : Procedure to check if the specified Mandatory Field is NULL |
85 | if so flag a Error message to the Log File |
86 *********************************************************************************/
87
88 Procedure Mandatory_Check(p_message_name varchar2
89 ,p_field varchar2
90 ,p_value varchar2) is
91 v_message_text fnd_new_messages.message_text%TYPE;
92 v_employee_dat VARCHAR2(255);
93 v_log_header VARCHAR2(255);
94 v_label_desc hr_lookups.meaning%TYPE;
95
96 Begin
97 -- hr_utility.set_location('Entered Mandatory_check',425);
98
99 if g_debug then
100 hr_utility.set_location('Started Checking Field in Mandatory_Check '||p_field,425);
101 end if;
102
103 If p_value is null then
104 v_label_desc := hr_general.decode_lookup('HR_NL_REPORT_LABELS', p_field);
105 v_employee_dat :=RPAD(SUBSTR(g_assignment_number,1,20),20)
106 ||' '||RPAD(SUBSTR(g_full_name,1,35),35)
107 ||' '||RPAD(SUBSTR(v_label_desc,1,35),35);
108 -- ||' '||RPAD(SUBSTR(g_error_flag,1,15),15);
109 hr_utility.set_message(801,p_message_name);
110 -- v_message_text :=SUBSTR(fnd_message.get,1,65);
111 g_error_count := NVL(g_error_count,0) +1;
112 if p_message_name <> g_message_name then
113 if g_message_name = 'PAY_NL_EE_REQUIRED_FIELD' then
114 v_log_header := RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','NL_ASSIGNMENT_NUMBER'),1,20),20)
115 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FULL_NAME'),1,35),35)
116 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FIELD_NAME'),1,35),35)
117 --||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR_TYPE'),1,15),15)
118 --||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','MESSAGE'),1,70),70
119 ;
120 Fnd_file.put_line(FND_FILE.LOG,v_log_header);
121 end if;
122 hr_utility.set_message(801,p_message_name);
123 v_message_text := rpad(fnd_message.get,255,' ');
124 FND_FILE.PUT_LINE(FND_FILE.LOG,v_message_text);
125 g_message_name := p_message_name;
126 end if;
127 FND_FILE.PUT_LINE(FND_FILE.LOG, v_employee_dat);
128 end if;
129 -- hr_utility.set_location('Leaving get all parameters',425);
130 End Mandatory_Check;
131
132 /********************************************************************************
133 |Name : get_loc_extra_info |
134 |Type : Function |
135 |Description : This Function returns extra information like Contact and |
136 | Telephone No for Employer details |
137 *********************************************************************************/
138
139 Function get_loc_extra_info(p_org_id number
140 ,p_contact out nocopy varchar2
141 ,p_telephone out nocopy varchar2
142 ) return number IS
143 cursor csr_loc_extra_info(p_org_id number) IS
144 select lei_information10,lei_information11
145 from hr_location_extra_info
146 where information_type = 'NL_POSTAL_ADDRESS'
147 and location_id = (select location_id from hr_organization_units where organization_id = p_org_id);
148 begin
149 -- hr_utility.set_location('Entering Get_loc_extra_info',600);
150 open csr_loc_extra_info(p_org_id);
151 fetch csr_loc_extra_info into p_contact,p_telephone;
152 close csr_loc_extra_info;
153 -- hr_utility.set_location('Exiting Get_loc_extra_info',600);
154 return 1;
155 end get_loc_extra_info;
156
157 /********************************************************************************
158 |Name : get_er_sequence |
159 |Type : Function |
160 |Description : This Function returns the next sequence number for |
161 | employer |
162 *********************************************************************************/
163
164 Function Get_er_sequence(p_employer_id number
165 ,p_si_provider_id number
166 ,p_reporting_date date
167 ,p_sequence out nocopy number ) return number IS
168
169 cursor csr_get_max_sequence IS
170 select max(pai.action_information4)
171 from pay_action_information pai
172 where pai.action_information1 = fnd_number.number_to_canonical(p_employer_id)
173 and pai.action_information2 = fnd_number.number_to_canonical(p_si_provider_id)
174 and to_char(pai.effective_date,'YYYY') = to_char(p_reporting_date,'YYYY');
175
176 l_max_sequence varchar2(30);
177 l_sequence number;
178 BEGIN
179 -- hr_utility.set_location('Entering Get_er_Sequence',600);
180 open csr_get_max_sequence;
181 fetch csr_get_max_sequence into l_max_Sequence;
182 close csr_get_max_Sequence;
183 l_sequence := fnd_number.canonical_to_number(l_max_Sequence);
184 p_sequence := nvl(to_number(l_sequence),0) + 1;
185 -- hr_utility.set_location('Exiting Get_er_Sequence',600);
186 return 1;
187 END Get_er_sequence;
188
189 /*******************************************************************************
190 |Name : Get_Balances1 |
191 |Type : Function |
192 |Description: Function which returns all the balances required for CBS File |
193 *******************************************************************************/
194
195 function get_balances1( p_frequency varchar2
196 ,p_assgt_act_id number
197 ,l_holiday_hours out nocopy number
198 ,l_adv_hours out nocopy number
199 ,l_si_wage out nocopy number
200 ,l_unique_payments out nocopy number
201 ,l_pre_tax_deductions out nocopy number
202 ,l_saving_scheme out nocopy number
203 ,l_sickness_days out nocopy number
204 ,l_unpaid_hours out nocopy number
205 ,l_sickness_pay out nocopy number
206 ,l_overtime_hours out nocopy number
207 )
208 return number is
209
210 l_pre_tax_only_ded NUMBER;
211 l_pre_si_pre_tax_ded NUMBER;
212 l_si_std_tax NUMBER;
213 l_si_spl_tax NUMBER;
214 l_ret_si_std_tax NUMBER;
215 l_ret_si_spl_tax NUMBER;
216 l_dimension VARCHAR2(20);
217 l_defined_balance VARCHAR2(150);
218 l_defined_balance_id NUMBER;
219
220
221 BEGIN
222 -- hr_utility.set_location('Entered get_balances1',425);
223
224 if p_frequency = 'K' then
225 l_dimension := '_ASG_QTD';
226 else
227 if p_frequency = 'M' then
228 l_dimension := '_ASG_MONTH';
229 else
230 l_dimension := '_ASG_LMONTH';
231 end if;
232 end if;
233
234 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_HOLIDAY_HOURS',500);
235 l_defined_balance := 'CBS_HOLIDAY_HOURS'||l_dimension;
236 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
237 l_holiday_hours:= nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
238 -- hr_utility.set_location('l_holiday_hours'||l_holiday_hours,425);
239
240 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_ADV_HOURS',500);
241 l_defined_balance := 'CBS_ADV_HOURS'||l_dimension;
242 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
243 l_adv_hours:=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
244 -- hr_utility.set_location('l_adv_hours'||l_adv_hours,425);
245
246 -- hr_utility.set_location('Fetching Defined Balance ID for SI_INCOME_STANDARD_TAX',500);
247 l_defined_balance := 'SI_INCOME_STANDARD_TAX'||l_dimension;
248 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
249 l_si_std_tax:=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
250 -- hr_utility.set_location('l_si_std_tax'||l_si_std_tax,425);
251
252 -- hr_utility.set_location('Fetching Defined Balance ID for SI_INCOME_SPECIAL_TAX',500);
253 l_defined_balance := 'SI_INCOME_SPECIAL_TAX'||l_dimension;
254 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
255 l_si_spl_tax:=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
256 -- hr_utility.set_location('l_si_spl_tax'||l_si_spl_tax,425);
257
258 -- hr_utility.set_location('Fetching Defined Balance ID for RETRO SI_INCOME_STANDARD_TAX',500);
259 l_defined_balance := 'RETRO_SI_INCOME_STANDARD_TAX'||l_dimension;
260 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
261 l_ret_si_std_tax :=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
262 -- hr_utility.set_location('l_ret_si_std_tax'||l_ret_si_std_tax,425);
263
264 -- hr_utility.set_location('Fetching Defined Balance ID for RETRO_SI_INCOME_SPECIAL_TAX',500);
265 l_defined_balance := 'RETRO_SI_INCOME_SPECIAL_TAX'||l_dimension;
266 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
267 l_ret_si_spl_tax:=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
268 -- hr_utility.set_location('l_ret_si_spl_tax'||l_ret_si_spl_tax,425);
269
270 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_UNIQUE_PAYMENTS',500);
271 l_defined_balance := 'CBS_UNIQUE_PAYMENTS'||l_dimension;
272 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
273 l_unique_payments :=round(nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0));
274 -- hr_utility.set_location('l_unique_payments'||l_unique_payments,425);
275
276 -- hr_utility.set_location('Fetching Defined Balance ID for PRE_TAX_ONLY_DEDUCTIONS',500);
277 l_defined_balance := 'PRE_TAX_ONLY_DEDUCTIONS'||l_dimension;
278 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
279 l_pre_tax_only_ded:=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
280 -- hr_utility.set_location('l_pre_tax_only_ded'||l_pre_tax_only_ded,425);
281
282 -- hr_utility.set_location('Fetching Defined Balance ID for PRE_TAX_ONLY_DEDUCTIONS',500);
283 l_defined_balance := 'PRE_TAX_DEDUCTIONS'||l_dimension;
284 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
285 l_pre_si_pre_tax_ded :=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
286 -- hr_utility.set_location('l_pre_si_pre_tax_ded'||l_pre_si_pre_tax_ded,425);
287
288 -- hr_utility.set_location('Fetching Defined Balance ID for EMPLOYEE_SAVINGS_CONTRIBUTION',500);
289 l_defined_balance := 'EMPLOYEE_SAVINGS_CONTRIBUTION'||l_dimension;
290 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
291 l_saving_scheme:=round(nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0));
292 -- hr_utility.set_location('l_saving_scheme'||l_saving_scheme,425);
293
294 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_SICKNESS_DAYS',500);
295 l_defined_balance := 'CBS_SICKNESS_DAYS'||l_dimension;
296 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
297 l_sickness_days :=round(nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0));
298 -- hr_utility.set_location('l_sickness_days'||l_sickness_days,425);
299
300 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_SICKNESS_PAY',500);
301 l_defined_balance := 'CBS_SICKNESS_PAY'||l_dimension;
302 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
303 l_sickness_pay:=round(nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0));
304 -- hr_utility.set_location('l_sickness_pay'||l_sickness_pay,425);
305
306 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_UNPAID_HOURS',500);
307 l_defined_balance := 'CBS_UNPAID_HOURS'||l_dimension;
308 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
309 l_unpaid_hours :=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
310 -- hr_utility.set_location('l_unpaid_hours'||l_unpaid_hours,425);
311
312 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_OVERTIME_HOURS',500);
313 l_defined_balance := 'CBS_OVERTIME_HOURS'||l_dimension;
314 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
315 l_overtime_hours:=nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0);
316 -- hr_utility.set_location('l_overtime_hours'||l_overtime_hours,425);
317
318 l_si_wage :=round(NVL(l_si_std_tax,0)+NVL(l_si_spl_tax,0)+NVL(l_ret_si_std_tax,0)+ NVL(l_ret_si_spl_tax,0));
319 l_pre_tax_deductions := round(NVL(l_pre_si_pre_tax_ded,0)- NVL(l_pre_tax_only_ded,0));
320
321 return 1;
322 -- hr_utility.set_location('Exited get_balances1',425);
323
324 end get_balances1;
325
326 /*******************************************************************************
327 |Name : Get_Balances2 |
328 |Type : Function |
329 |Description: Function which returns all the balances required for CBS File |
330 *******************************************************************************/
331 function get_balances2 ( p_frequency varchar2
332 ,p_assgt_act_id number
333 ,l_wage_agreed_by_contract out nocopy number
334 ,l_number_of_days out nocopy number
335 ,l_si_days_quarter out nocopy number
336 ,l_paid_gross_wage out nocopy number
337 ,l_wage_for_overtime out nocopy number
338 ) return number is
339
340 cursor csr_get_context_id (p_ass_act_id Number) IS
341 select ff.context_id context_id
342 , pact.context_value Context_value
343 , decode(context_value,'ZFW',0,'ZW',1,
344 'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6) seq
345 from ff_contexts ff, pay_action_contexts pact
346 where ff.context_name = 'SOURCE_TEXT' and
347 ff.context_id = pact.context_id and
348 pact.assignment_action_id = p_ass_act_id
349 ORDER BY decode(context_value,'ZFW',0,'ZW',1,
350 'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6) ;
351
352
353 l_context_id NUMBER;
354 l_context_value Varchar2(20);
355 l_context_seq NUMBER;
356 l_dimension VARCHAR2(20);
357 l_defined_balance VARCHAR2(150);
358 l_defined_balance_id NUMBER;
359
360
361 begin
362 -- hr_utility.set_location('Exited get_balances2',425);
363
364 if p_frequency = 'K' then
365 l_dimension := '_ASG_PTD';
366 else
367 if p_frequency = 'M' then
368 l_dimension := '_ASG_MONTH';
369 else
370 l_dimension := '_ASG_LMONTH';
371 end if;
372 end if;
373
374 open csr_get_context_id(p_assgt_act_id);
375 fetch csr_get_context_id into l_context_id , l_context_value , l_context_seq;
376 close csr_get_context_id;
377
378 l_defined_balance := 'CBS_CONTRACT_WAGE'||l_dimension;
379 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
380 l_wage_agreed_by_contract :=round(nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0));
381 -- hr_utility.set_location('l_wage_agreed_by_contract'||l_wage_agreed_by_contract,425);
382
383 if ( l_context_id is not null and l_context_value is not null ) then
384 if (p_frequency = 'M') then
385 -- hr_utility.set_location('Fetching Defined Balance ID for REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_MONTH',500);
386 l_defined_balance := 'REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_MONTH';
387 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
388 l_number_of_days := nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,l_context_value,null,null),0);
389 -- hr_utility.set_location('l_number_of_days'||l_number_of_days,425);
390 else
391 if (p_frequency = 'K') then
392 -- hr_utility.set_location('Fetching Defined Balance ID for REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_QTD',500);
393 l_defined_balance := 'REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_QTD';
394 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
395 l_number_of_days := nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,l_context_value,null,null),0);
396 else
397 -- hr_utility.set_location('Fetching Defined Balance ID for REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_LMONTH',500);
398 l_defined_balance := 'REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_LMONTH';
399 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
400 l_number_of_days := nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,l_context_value,null,null),0);
401 end if;
402 end if;
403
404 -- hr_utility.set_location('Fetching Defined Balance ID for REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_QTD',500);
405 l_defined_balance := 'REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_QTD';
406 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
407 l_si_days_quarter := nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id,null,null,l_context_id,l_context_value,null,null),0);
408 -- hr_utility.set_location('l_si_days_quarter'||l_si_days_quarter,425);
409 else
410 l_number_of_days := 0;
411 l_si_days_quarter := 0;
412 end if;
413
414 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_PAID_GROSS_WAGE',500);
415 l_defined_balance := 'CBS_PAID_GROSS_WAGE'||l_dimension;
416 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
417 l_paid_gross_wage :=round(nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0));
418 -- hr_utility.set_location('l_paid_gross_wage'||l_paid_gross_wage,425);
419
420 -- hr_utility.set_location('Fetching Defined Balance ID for CBS_OVERTIME_WAGE',500);
421 l_defined_balance := 'CBS_OVERTIME_WAGE'||l_dimension;
422 l_defined_balance_id:=pay_nl_general.get_defined_balance_id(l_defined_balance);
423 l_wage_for_overtime :=round(nvl(pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id),0));
424 -- hr_utility.set_location('l_wage_for_overtime'||l_wage_for_overtime,425);
425
426 return 1;
427 -- hr_utility.set_location('Exited get_balances2',425);
428 end get_balances2;
429
430 /*******************************************************************************
431 |Name : Get_health_insurance |
432 |Type : Function |
433 |Description: Function used to pick the si status of the assignment for ZFW |
434 | insurance. If health insurance is from a Private Insurance |
435 | provider,the provider type of the Private Health Insurance |
436 | Provider is returned. |
437 *******************************************************************************/
438
439 function get_health_insurance(p_assignment_id in number,
440 p_date in date) RETURN VARCHAR2 IS
441
442 cursor csr_get_si_status(p_assignment_id number,
443 p_date date) is
444 select pay_nl_si_pkg.get_si_status(p_assignment_id,p_date,'ZFW')
445 from dual;
446
447 cursor csr_get_phi_information(p_assignment_id number,
448 p_date date) is
449 SELECT ORG_INFORMATION1 FROM HR_ORGANIZATION_INFORMATION,PER_ASSIGNMENT_EXTRA_INFO PAEI
450 WHERE PAEI.ASSIGNMENT_ID = p_assignment_id
451 AND PAEI.AEI_INFORMATION_CATEGORY = 'NL_PHI'
452 AND ORGANIZATION_ID = PAEI.AEI_INFORMATION3
453 AND ORG_INFORMATION_CONTEXT = 'NL_PHI_ORG_INFO'
454 AND p_date between fnd_date.canonical_to_date(PAEI.AEI_INFORMATION1) and nvl(fnd_date.canonical_to_date(PAEI.AEI_INFORMATION2),hr_general.end_of_time);
455
456 -- For Fetching IZA Information
457 cursor csr_get_iza (p_assignment_id number,
458 p_date date) IS
459 SELECT ORG_INFORMATION1
460 FROM HR_ORGANIZATION_INFORMATION HOI,PER_ASSIGNMENT_EXTRA_INFO PAEI
461 WHERE PAEI.ASSIGNMENT_ID = p_assignment_id
462 AND PAEI.AEI_INFORMATION_CATEGORY = 'NL_IZA_INFO'
463 AND HOI.ORGANIZATION_ID = PAEI.AEI_INFORMATION3
464 AND HOI.ORG_INFORMATION_CONTEXT = 'NL_PHI_ORG_INFO'
465 AND p_date between fnd_date.canonical_to_date(PAEI.AEI_INFORMATION1) and nvl(fnd_date.canonical_to_date(PAEI.AEI_INFORMATION2),hr_general.end_of_time);
466
467 l_si_status NUMBER;
468 l_si_provider VARCHAR2(150);
469
470 BEGIN
471 -- hr_utility.set_location('Entering Get_health_insurance ',425);
472
473 open csr_get_si_status(p_assignment_id,p_date);
474 -- if csr_get_si_status % found then
475 fetch csr_get_si_status into l_si_status;
476 -- end if;
477 close csr_get_si_status;
478
479 -- hr_utility.set_location('fETCHED l_si_status '|| l_si_status,425);
480
481 if l_si_status is not null THEN
482 if l_si_status =4 then
483 open csr_get_iza(p_assignment_id,p_date);
484 -- hr_utility.set_location('opened the cursor '|| l_si_status,425);
485 fetch csr_get_iza into l_si_provider;
486 close csr_get_iza;
487 if l_si_provider is null then
488 -- hr_utility.set_location('entered into if loop '|| l_si_status,425);
489 open csr_get_phi_information(p_assignment_id,p_date);
490 -- hr_utility.set_location('opened the cursor '|| l_si_status,425);
491 fetch csr_get_phi_information into l_si_provider;
492 -- hr_utility.set_location('fETCHED l_si_provider from org info '|| l_si_status,425);
493 close csr_get_phi_information;
494 end if;
495 else
496 l_si_provider :='1';
497 end if;
498 -- hr_utility.set_location('Leaving Get_health_insurance ',425);
499 return l_si_provider;
500 else
501 return '0';
502 end if;
503 EXCEPTION
504 when others then
505 hr_utility.set_location('Exception :' ||'health insurance'||SQLERRM(SQLCODE),999);
506 -- hr_utility.set_location('Leaving Get_health_insurance ',425);
507 end;
508
509 /*******************************************************************************
510 |Name : Get_working_schedule |
511 |Type : Function |
512 |Description: Function to get working schedule for the assignment |
513 *******************************************************************************/
514 PROCEDURE Get_working_schedule (p_assignment_id IN NUMBER,
515 p_working_schedule OUT NOCOPY NUMBER,
516 p_reporting_start_date IN DATE,
517 p_reporting_end_date IN DATE )IS
518
519 cursor csr_get_working_schedule is
520 select sck.segment6,
521 SUM(decode(sign(p_reporting_end_date - paa.effective_end_date),-1,p_reporting_end_date,paa.effective_end_date)-decode(sign(paa.effective_start_date - p_reporting_start_date),-1,p_reporting_start_date,paa.effective_start_date)+1) Days
522 from per_all_assignments_f paa,hr_soft_coding_keyflex sck
523 where paa.assignment_id = p_assignment_id
524 and (paa.effective_start_date >= p_reporting_start_date or p_reporting_start_date between paa.effective_start_date and paa.effective_end_date)
525 and (paa.effective_end_date <= p_reporting_end_date or paa.effective_start_date <= p_reporting_end_date)
526 and sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
527 group by sck.segment6
528 order by Days desc;
529
530 l_temp_working_schedule VARCHAR2(10);
531 l_days NUMBER;
532
533 BEGIN
534 -- hr_utility.set_location('Entering Get_working_schedule ',425);
535
536 OPEN csr_get_working_schedule;
537 FETCH csr_get_working_schedule into l_temp_working_schedule,l_days;
538 CLOSE csr_get_working_schedule;
539
540 If l_temp_working_schedule = 'R' then
541 p_working_schedule := 1;
542 Else
543 If l_temp_working_schedule = 'I' then
544 p_working_schedule := 2;
545 Else
546 p_working_schedule := 3;
547 End if;
548 End if;
549 -- hr_utility.set_location('Exiting Get_working_schedule ',425);
550
551 END get_working_schedule;
552
553 /********************************************************************************
554 |Name : Get_dev_work_hours |
555 |Type : Function |
556 |Description: Function to get the Deviating Working Hours for the longest period|
557 *********************************************************************************/
558 PROCEDURE Get_dev_work_hours (p_assignment_id IN NUMBER,
559 p_dev_work_hours OUT NOCOPY NUMBER,
560 p_reporting_start_date IN DATE,
561 p_reporting_end_date IN DATE )IS
562
563 cursor csr_get_dev_work_hrs is
564 select sck.segment13,
565 SUM(decode(sign(p_reporting_end_date - paa.effective_end_date),-1,p_reporting_end_date,paa.effective_end_date)-decode(sign(paa.effective_start_date - p_reporting_start_date),-1,p_reporting_start_date,paa.effective_start_date)+1) Days
566 from per_all_assignments_f paa,hr_soft_coding_keyflex sck
567 where paa.assignment_id = p_assignment_id
568 and (paa.effective_start_date >= p_reporting_start_date or p_reporting_start_date between paa.effective_start_date and paa.effective_end_date)
569 and (paa.effective_end_date <= p_reporting_end_date or paa.effective_start_date <= p_reporting_end_date)
570 and sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
571 group by sck.segment13
572 order by Days desc;
573
574 l_temp_dev_work_hrs VARCHAR2(100);
575 l_days NUMBER;
576
577 BEGIN
578 -- hr_utility.set_location('Entering Get_dev_work_hours ',425);
579
580 OPEN csr_get_dev_work_hrs;
581 FETCH csr_get_dev_work_hrs into l_temp_dev_work_hrs,l_days;
582 CLOSE csr_get_dev_work_hrs;
583
584 If l_temp_dev_work_hrs is not null then
585 p_dev_work_hours := fnd_number.canonical_to_number(l_temp_dev_work_hrs);
586 else
587 p_dev_work_hours :=0;
588 End if;
589 -- hr_utility.set_location('Leaving Get_dev_work_hours ',425);
590
591 END Get_dev_work_hours;
592
593
594 /********************************************************************************
595 |Name : Get_Employment_Code |
596 |Type : Function |
597 |Description: Function to get the Employment Code |
598 *********************************************************************************/
599 Function Get_Employment_Code (p_assignment_id IN NUMBER,
600 p_employment_code IN OUT NOCOPY NUMBER,
601 p_reporting_start_date IN DATE,
602 p_reporting_end_date IN DATE ) return NUMBER IS
603
604 cursor csr_get_employmetnt_code is
605 select sck.segment6,paa.employment_category,
606 SUM(decode(sign(p_reporting_end_date - paa.effective_end_date),-1,p_reporting_end_date,paa.effective_end_date)-decode(sign(paa.effective_start_date - p_reporting_start_date),-1,p_reporting_start_date,paa.effective_start_date)+1) Days
607 from per_all_assignments_f paa,hr_soft_coding_keyflex sck
608 where paa.assignment_id = p_assignment_id
609 and (paa.effective_start_date >= p_reporting_start_date or p_reporting_start_date between paa.effective_start_date and paa.effective_end_date)
610 and (paa.effective_end_date <= p_reporting_end_date or paa.effective_start_date <= p_reporting_end_date)
611 and sck.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
612 group by sck.segment6,paa.employment_category
613 order by Days desc;
614
615 l_temp_working_schedule VARCHAR2(20);
616 l_emp_category VARCHAR2(20);
617 l_days NUMBER;
618
619 BEGIN
620 -- hr_utility.set_location('Entering Get_Employment_Code ',425);
621
622 OPEN csr_get_employmetnt_code;
623 FETCH csr_get_employmetnt_code into l_temp_working_schedule,l_emp_category,l_days;
624 CLOSE csr_get_employmetnt_code;
625
626 If (l_emp_category = 'PR' or l_emp_category = 'PT' or l_emp_category = 'NL_TRAINEE')
627 AND (l_temp_working_schedule = 'I' or l_temp_working_schedule = 'S') then
628 p_employment_code := 3;
629 return 1;
630 Else
631 If (l_emp_category = 'PR' or l_emp_category = 'PT' or l_emp_category = 'NL_TRAINEE_PT')
632 AND (l_temp_working_schedule = 'R') then
633 p_employment_code := 2;
634 return 1;
635 Else
636 If (l_emp_category = 'FR' or l_emp_category = 'FT' or l_emp_category = 'NL_TRAINEE') then
637 p_employment_code := 1;
638 return 1;
639 End if;
640 End if;
641 p_employment_code :=0;
642 return 1;
643 End if;
644
645 -- hr_utility.set_location('leaving Get_Employment_Code ',425);
646
647 END Get_Employment_Code;
648
649
650
651 /********************************************************************************
652 |Name : Get_cbs_Working_Hours |
653 |Type : Function |
654 |Description: Function to get the Working Hours |
655 *********************************************************************************/
656 FUNCTION Get_cbs_Working_Hours(p_business_group_id IN NUMBER,
657 p_assignment_id IN NUMBER ,
658 p_reporting_date IN DATE
659 ) RETURN NUMBER is
660
661 cursor csr_get_freq_ind_hours(l_assignment_id number ,l_reporting_date date) IS
662 select sck.segment28, paa.frequency
663 from PER_ALL_ASSIGNMENTS_F paa,HR_SOFT_CODING_KEYFLEX sck
664 where paa.assignment_id = l_assignment_id
665 and paa.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
666 and l_reporting_date between paa.effective_start_date and paa.effective_end_date;
667
668 l_inputs ff_exec.inputs_t;
669 l_outputs ff_exec.outputs_t;
670 l_formula_exists BOOLEAN := TRUE;
671 l_formula_cached BOOLEAN := FALSE;
672 l_formula_id ff_formulas_f.formula_id%TYPE;
673 l_formula_name ff_formulas_f.formula_name%TYPE;
674 l_weekly_working_hours NUMBER;
675 l_ind_work_hours NUMBER;
676 l_ind_work_hours2 varchar2(10);
677 l_frequency varchar2(10);
678 weeks_per_month VARCHAR2(20);
679
680
681 BEGIN
682 --
683 -- hr_utility.set_location('--In Get Working Hours ',10);
684 g_working_hours_formula_name := 'NL_WEEKLY_WORKING_HOURS';
685 IF g_working_hours_formula_exists = TRUE THEN
686 IF g_working_hours_formula_cached = FALSE THEN
687 PAY_NL_GENERAL.cache_formula('NL_WEEKLY_WORKING_HOURS',p_business_group_id,p_reporting_date,l_formula_id,l_formula_exists,l_formula_cached);
688 g_working_hours_formula_exists:=l_formula_exists;
689 g_working_hours_formula_cached:=l_formula_cached;
690 g_working_hours_formula_id:=l_formula_id;
691 END IF;
692 END IF;
693
694 IF g_working_hours_formula_exists = TRUE THEN
695
696 l_inputs(1).name := 'BUSINESS_GROUP_ID';
697 l_inputs(1).value := p_business_group_id;
698 l_inputs(2).name := 'ASSIGNMENT_ID';
699 l_inputs(2).value := p_assignment_id;
700
701 l_outputs(1).name := 'WEEKLY_WORKING_HOURS';
702
703 PAY_NL_GENERAL.run_formula(p_formula_id => g_working_hours_formula_id,
704 p_effective_date => g_reporting_date,
705 p_formula_name => g_working_hours_formula_name,
706 p_inputs => l_inputs,
707 p_outputs => l_outputs);
708
709 l_weekly_working_hours := fnd_number.canonical_to_number(l_outputs(1).value);
710
711 if l_weekly_working_hours is null then
712 l_weekly_working_hours := 0;
713 end if;
714 return l_weekly_working_hours;
715 ELSE
716 -- hr_utility.set_location('--In the ELSE Part of get working hours '|| p_assignment_id || p_reporting_date ,10);
717 OPEN csr_get_freq_ind_hours(p_assignment_id,p_reporting_date);
718 fetch csr_get_freq_ind_hours into l_ind_work_hours2,l_frequency;
719 close csr_get_freq_ind_hours;
720 l_ind_work_hours := fnd_number.canonical_to_number(l_ind_work_hours2);
721 weeks_per_month := pay_nl_general.get_global_value(p_reporting_date,'NL_TAX_WEEKS_PER_MONTH');
722 if l_frequency = 'M' then
723 l_weekly_working_hours := l_ind_work_hours/fnd_number.canonical_to_number(weeks_per_month);
724 else
725 if l_frequency = 'D' then
726 l_weekly_working_hours := l_ind_work_hours * 5;
727 else l_weekly_working_hours := l_ind_work_hours;
728 end if;
729
730 if l_weekly_working_hours is null then
731 l_weekly_working_hours := 0;
732 end if;
733 end if;
734 -- hr_utility.set_location('--In the ELSE Part of get working hours '|| l_weekly_working_hours,10);
735 return l_weekly_working_hours;
736 END IF;
737 -- hr_utility.set_location('Leaving Get_CBS_Working_Hours ',425);
738
739 EXCEPTION
740 when others then
741 hr_utility.set_location('Exception :' ||'cbs working hours'||SQLERRM(SQLCODE),999);
742 End Get_CBS_Working_Hours;
743
744 /*******************************************************************************
745 |Name : GET_CAO_CODE |
746 |Type : Procedure |
747 |Description: Function to get the collective agreement for the given assignmnent|
748 *******************************************************************************/
749 PROCEDURE GET_CAO_CODE (p_assignment_id in number,
750 p_cao_code in out nocopy number,
751 p_rep_date in DATE) IS
752
753 CURSOR csr_get_cao_code(l_assignment_id NUMBER) IS
754 select AEI_INFORMATION5 from per_assignment_extra_info aei
755 WHERE aei.information_type like 'NL_CADANS_INFO' and aei.assignment_id = l_assignment_id
756 and p_rep_date between fnd_date.canonical_to_date(aei.AEI_INFORMATION1) and nvl(fnd_date.canonical_to_date(aei.AEI_INFORMATION2),hr_general.end_of_time);
757
758 l_cao_code VARCHAR2(50);
759
760 begin
761
762 -- hr_utility.set_location('Entering GET_CAO_CODE ',425);
763
764 OPEN csr_get_cao_code(p_assignment_id);
765 FETCH csr_get_cao_code INTO l_cao_code;
766 CLOSE csr_get_cao_code;
767 p_cao_code := fnd_number.canonical_to_number(l_cao_code);
768
769 -- hr_utility.set_location('Leaving GET_CAO_CODE ',425);
770
771 END GET_CAO_CODE;
772
773 /********************************************************************************
774 |Name : get_grade_salary_number |
775 |Type : Function |
776 |Description: Function to get the grade and salary numbers |
777 ********************************************************************************/
778
779 Function get_grade_salary_number(p_assignment_id in number,
780 P_business_group_id in number,
781 p_org_id in number,
782 P_grade_id in number,
783 P_reporting_date in date,
784 P_public_sector in varchar2,
785 P_grade_number out nocopy varchar2,
786 P_salary_number out nocopy varchar2) return number is
787
788 -- Cursor to get the grade number
789 Cursor csr_get_grade_number(p_grade_id number,p_business_group_id number) is
790 Select sequence from per_grades pg
791 Where pg.grade_id = p_grade_id
792 And pg.business_group_id = p_business_group_id;
793
794 -- Cursor to get the ceiling
795 Cursor csr_get_ceiling(p_grade_id number,p_business_group_id number,p_reporting_date date) is
796 Select max(sequence) from per_spinal_point_steps_f psps
797 Where psps.grade_spine_id = (select grade_spine_id from per_grade_spines_f pgs
798 Where grade_id = p_grade_id
799 And p_reporting_date between pgs.effective_start_date and pgs.effective_end_date
800 and pgs.business_Group_id = p_businesS_group_id)
801 And p_reporting_date between psps.effective_start_date and psps.effective_end_date
802 And psps.business_group_id = p_business_group_id;
803
804 -- Cursor to get the salary sequence
805 Cursor csr_get_salary_sequence (p_grade_id number, p_business_group_id number,
806 p_assignment_id number,p_reporting_date date) is
807 Select sequence
808 from per_spinal_point_steps_f psps
809 Where psps.grade_spine_id = (select grade_spine_id from per_grade_spines_f pgs
810 Where grade_id = p_grade_id
811 And p_reporting_date between pgs.effective_start_date and pgs.effective_end_date
812 and pgs.business_Group_id = p_business_group_id)
813 And p_reporting_date between psps.effective_start_date and psps.effective_end_date
814 And psps.business_group_id = p_business_group_id
815 And psps.step_id = (select step_id
816 from per_spinal_point_placements_f psp
817 where assignment_id = p_assignment_id
818 and p_reporting_date between psp.effective_start_date and psp.effective_end_date
819 and psp.business_group_id = p_business_group_id);
820
821
822 l_public_sector_org varchar2(10);
823 l_ceiling number;
824 l_salary_sequence number;
825 l_salary_number number;
826 l_grade_number number;
827
828 Begin
829
830 -- l_public_sector_org := hr_nl_org_info.Get_Public_Sector_Org(p_org_id);
831 -- hr_utility.set_location('--Entering get_grade_salary_number ',10);
832
833 if P_public_sector = 'Y' then
834 Open csr_get_grade_number(p_grade_id,p_business_group_id);
835 Fetch csr_get_grade_number into l_grade_number;
836 Close csr_get_grade_number;
837 if l_grade_number is null then
838 l_grade_number := 999;
839 end if;
840 else
841 l_grade_number := 000;
842 end if;
843
844 Open csr_get_ceiling(p_grade_id,p_business_group_id,p_reporting_date);
845 Fetch csr_get_ceiling into l_ceiling;
846 Close csr_get_ceiling;
847
848 Open csr_get_salary_sequence(p_grade_id,p_business_group_id,p_assignment_id,p_reporting_date);
849 Fetch csr_get_salary_sequence into l_salary_sequence;
850 Close csr_get_salary_sequence;
851 if ( l_salary_sequence is null or l_ceiling is null) then
852 if P_public_sector = 'Y' then
853 l_salary_number := 999;
854 else
855 l_salary_number := 000;
856 end if;
857 else
858 l_salary_number := to_char(NVL(l_salary_sequence,0) - (NVL(l_ceiling,0) - NVL(l_salary_sequence,0)));
859 end if;
860 p_grade_number := lpad(to_char(l_grade_number),3,'0');
861 p_salary_number := lpad(to_char(l_salary_number),3,'0');
862
863 Return 1;
864 -- hr_utility.set_location('Leaving get_grade_salary_number ',425);
865
866 End get_grade_salary_number;
867
868
869 /*******************************************************************************
870 |Name : Get_tax_details |
871 |Type : Function |
872 |Description: Function to get the tax details |
873 *******************************************************************************/
874 Function Get_tax_details(p_max_assgt_act_id number
875 ,p_wage_tax_discount OUT NOCOPY varchar2
876 ,p_tax_code OUT NOCOPY VARCHAR2
877 ,p_labour_relation_code OUT NOCOPY VARCHAR2)
878 RETURN NUMBER IS
879
880 cursor csr_get_date_earned(p_asg_act_id number) is
881 select date_earned
882 from pay_payroll_actions ppa,pay_assignment_Actions paa
883 where
884 ppa.payroll_action_id = paa.payroll_action_id
885 and paa.assignment_action_id = p_max_assgt_act_id;
886
887 cursor csr_run_result_id(p_assignment_action_id number, p_element_type_id number) is
888 select prr.run_result_id
889 from pay_run_results prr
890 where
891 prr.element_type_id=p_element_type_id and
892 prr.assignment_action_id=p_assignment_action_id;
893
894 l_date_earned date;
895 l_run_result_id NUMBER;
896 l_element_type_id NUMBER;
897 l_tax_discount_input_id NUMBER;
898 l_tax_code_input_id NUMBER;
899 l_labour_relation_code_id NUMBER;
900
901 BEGIN
902 -- hr_utility.set_location('-- Entering Get_tax_details ',10);
903
904 open csr_get_date_earned(p_max_assgt_act_id);
905 fetch csr_get_date_earned into l_date_earned;
906 close csr_get_date_Earned;
907
908 l_element_type_id:=pay_nl_general.get_element_type_id('Standard Tax Deduction',l_date_earned);
909 l_tax_discount_input_id:=pay_nl_general.get_input_value_id(l_element_type_id,'Tax Reduction Flag',l_date_earned);
910 l_tax_code_input_id := pay_nl_general.get_input_value_id(l_element_type_id,'Tax Code',l_date_earned);
911 l_labour_relation_code_id:=pay_nl_general.get_input_value_id(l_element_type_id,'Labour Tax Reduction Flag',l_date_earned);
912
913 open csr_run_result_id ( p_max_assgt_act_id ,l_element_type_id) ;
914 fetch csr_run_result_id into l_run_result_id;
915 close csr_run_Result_id;
916
917 if pay_nl_general.get_run_result_value(p_max_assgt_act_id,l_element_type_id,l_tax_discount_input_id,l_run_result_id,'C') = 'NL_NONE' then
918 p_wage_tax_discount := '00';
919 else
920 p_wage_tax_discount := '01';
921 end if;
922
923 p_tax_code := pay_nl_general.get_run_result_value(p_max_assgt_act_id,l_element_type_id,l_tax_code_input_id,l_run_result_id,'C');
924
925 if pay_nl_general.get_run_result_value(p_max_assgt_act_id,l_element_type_id,l_labour_relation_code_id,l_run_result_id,'C') = 'N' then
926 p_labour_relation_code := '00';
927 else
928 p_labour_relation_code := '01';
929 end if;
930
931 return 1;
932 -- hr_utility.set_location('-- Leaving Get_tax_details ',10);
933
934 end get_tax_details;
935
936 /********************************************************************************
937 |Name : RANGE_CODE |
938 |Type : Procedure |
939 |Description : This procedure returns a sql string to select a range of |
940 | assignments eligible for archival |
941 *********************************************************************************/
942
943 Procedure RANGE_CODE (pactid IN NUMBER
944 ,sqlstr OUT NOCOPY VARCHAR2) IS
945 --
946 -- Variables for GET ALL PARAMETERS
947 --
948 l_business_group_id NUMBER;
949 l_employer_id NUMBER;
950 l_si_provider_id NUMBER;
951 l_payroll_id NUMBER;
952 l_report_type pay_payroll_actions.report_type%TYPE;
953 l_effective_date DATE;
954 l_employer NUMBER;
955 l_si_provider NUMBER;
956 l_org_struct_id NUMBER;
957 l_medium_code NUMBER;
958 l_density NUMBER;
959 l_reporting_date DATE;
960
961 --
962 -- Variables for Sender and Org Details
963 --
964 l_Sender_Rep_Name_bg VARCHAR2(255);
965 l_Sender_Reg_Number_bg VARCHAR2(255);
966 l_Wage_Tax_Rep_Name_org VARCHAR2(255);
967 l_Wage_Tax_Reg_Number_org VARCHAR2(255);
968 --
969 -- Variables for Sender and Org Details
970 --
971 l_Sender_Rep_Name_sip VARCHAR2(255);
972 l_Sender_Reg_Number_sip VARCHAR2(255);
973 l_Employer_Rep_Name_sip VARCHAR2(255);
974 l_Employer_Reg_Number_sip VARCHAR2(255);
975 --
976 -- Variables for Mandatory Data
977 --
978 l_employer_contact VARCHAR2(255);
979 l_employer_telephone VARCHAR2(255);
980 --
981 -- Variables for Sender Address
982 --
983 l_sen_house_number VARCHAR2(255);
984 l_sen_house_no_add VARCHAR2(255);
985 l_sen_street_name VARCHAR2(255);
986 l_sen_line1 VARCHAR2(255);
987 l_sen_line2 VARCHAR2(255);
988 l_sen_line3 VARCHAR2(255);
989 l_sen_city VARCHAR2(255);
990 l_sen_country VARCHAR2(255);
991 l_sen_postal_code VARCHAR2(255);
992 --
993 -- Variables for Address
994 --
995 l_house_number VARCHAR2(255);
996 l_house_no_add VARCHAR2(255);
997 l_street_name VARCHAR2(255);
998 l_line1 VARCHAR2(255);
999 l_line2 VARCHAR2(255);
1000 l_line3 VARCHAR2(255);
1001 l_city VARCHAR2(255);
1002 l_country VARCHAR2(255);
1003 l_postal_code VARCHAR2(255);
1004 --
1005 -- Variables for Mandatory Data
1006 --
1007 l_sender_address_field VARCHAR2(255);
1008 l_sen_city_field VARCHAR2(255);
1009 l_tax_address_field VARCHAR2(255);
1010 l_tax_city_field VARCHAR2(255);
1011 l_org_struct_version_id VARCHAR2(255);
1012 l_frequency VARCHAR2(255);
1013 l_customer_number VARCHAR2(255);
1014
1015 --
1016 -- Variables for Archival Code
1017 --
1018 l_return_value NUMBER;
1019 l_action_info_id NUMBER;
1020 l_ovn NUMBER;
1021 l_sender_address NUMBER;
1022 l_org_address NUMBER;
1023 l_sequence NUMBER;
1024 v_log_header VARCHAR2(1000);
1025
1026
1027
1028 BEGIN
1029
1030 g_debug := FALSE ;
1031 if g_debug then
1032 hr_utility.trace_on(NULL,'CBS');
1033 hr_utility.set_location('Entering GET ALL PARAMETERS of Range Code ',600);
1034 end if;
1035
1036 g_error_flag := hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR');
1037
1038 PAY_NL_CBS_FILE.get_all_parameters(pactid,
1039 P_business_group_id => l_business_group_id,
1040 P_reporting_date => l_reporting_date,
1041 P_effective_date => l_effective_date,
1042 P_employer => l_employer,
1043 P_si_provider => l_si_provider,
1044 P_org_struct_id => l_org_struct_id,
1045 P_medium_code => l_medium_code,
1046 P_density => l_density);
1047
1048 hr_utility.set_location('exited GET ALL PARAMETERS',600);
1049
1050 l_return_value := PAY_NL_TAXOFFICE_FILE.GET_TOS_SENDER_DETAILS
1051 (l_Business_Group_Id,
1052 l_Employer,
1053 l_Sender_Rep_Name_bg,
1054 l_Sender_Reg_Number_bg,
1055 l_Wage_Tax_Rep_Name_org,
1056 l_Wage_Tax_Reg_Number_org);
1057
1058 hr_utility.set_location('Executing Get_SIP_Details',600);
1059
1060 l_return_value := PAY_NL_ANNUAL_SI_FILE.Get_SIP_Details
1061 ( l_employer
1062 ,l_si_provider
1063 ,l_reporting_date
1064 ,l_Sender_Rep_Name_sip
1065 ,l_Sender_Reg_Number_sip
1066 ,l_Employer_Rep_Name_sip
1067 ,l_Employer_Reg_Number_sip
1068 );
1069
1070 l_return_value := get_loc_extra_info
1071 (l_employer,
1072 p_contact => l_employer_contact,
1073 p_telephone => l_employer_telephone);
1074
1075 -- hr_utility.set_location('Executing GET BG ADDRESS',600);
1076
1077 l_sender_address:=PAY_NL_GENERAL.GET_ORGANIZATION_ADDRESS
1078 (l_business_group_id,
1079 l_business_group_id,
1080 l_sen_house_number,
1081 l_sen_house_no_add,
1082 l_sen_street_name,
1083 l_sen_line1,
1084 l_sen_line2,
1085 l_sen_line3,
1086 l_sen_city,
1087 l_sen_country,
1088 l_sen_postal_code);
1089
1090 -- hr_utility.set_location('Executing GET ORG ADDRESS',600);
1091
1092 l_org_address:=PAY_NL_GENERAL.GET_ORGANIZATION_ADDRESS
1093 (l_employer
1094 ,l_business_group_id
1095 ,l_house_number
1096 ,l_house_no_add
1097 ,l_street_name
1098 ,l_line1
1099 ,l_line2
1100 ,l_line3
1101 ,l_city
1102 ,l_country
1103 ,l_postal_code);
1104
1105 -- hr_utility.set_location('Going to retrieve all the address fields',600);
1106
1107 l_sender_address_field := l_sen_street_name || l_sen_house_number || l_sen_house_no_add;
1108 l_sen_city_field := l_sen_postal_code || l_sen_city;
1109 l_tax_address_field := l_street_name || l_house_number || l_house_no_add;
1110 l_tax_city_field := l_postal_code || l_city;
1111 -- hr_utility.set_location('Going to retrieve the Data of org struct id,frequency and cust no'||l_reporting_date,600);
1112 l_org_struct_version_id := pay_nl_taxoffice_archive.get_org_hierarchy(l_org_struct_id,l_reporting_date);
1113 l_frequency := hr_nl_org_info.get_reporting_frequency(l_employer);
1114 l_customer_number := hr_nl_org_info.get_customer_number(l_employer);
1115
1116 -- hr_utility.set_location('Going to perform mandatory check the Data in Range Code',600);
1117
1118 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_SENDER_NAME', l_Sender_Rep_Name_bg);
1119 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_SENDER_ADDR', l_sender_address_field);
1120 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_SENDER_CITY', l_sen_city_field);
1121 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_SENDER_REG_NUM', l_Sender_Reg_Number_bg);
1122 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_REPORTING_FREQUENCY', l_frequency);
1123 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_CUSTOMER_NUMBER', l_customer_number);
1124 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_TAX_REPORTING_NAME',l_Wage_Tax_Rep_Name_org);
1125 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_ER_ADDR', l_tax_address_field);
1126 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_CITY', l_tax_city_field);
1127 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_TAX_REGISTRATION_NUMBER',l_Wage_Tax_Reg_Number_org);
1128 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_ER_SI_REG_NUMBER',l_Employer_Reg_Number_sip);
1129 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_ER_CONTACT',l_employer_contact);
1130 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_ER_TELEPHONE',l_employer_telephone);
1131
1132
1133 IF g_error_count=0 THEN
1134 -- l_return_value := Pay_nl_cbs_file.Get_er_sequence(l_employer,l_si_provider,l_reporting_date,l_sequence);
1135 -- hr_utility.set_location('Fetched the sequence, employer = '||l_employer||'si provider='||l_si_provider,600);
1136 l_sequence :=1;
1137 Pay_Action_information_api.create_action_information(
1138 P_action_information_id => l_action_info_id
1139 ,p_action_context_id => pactid
1140 ,p_action_context_type => 'PA'
1141 ,p_object_version_number => l_ovn
1142 ,p_effective_date => l_reporting_date
1143 ,p_source_id => NULL
1144 ,p_source_text => NULL
1145 ,p_action_information_category => 'NL CBS EMPLOYER DETAILS'
1146 ,p_action_information1 => l_employer
1147 ,p_action_information2 => l_si_provider
1148 ,p_action_information4 => l_sequence);
1149
1150 -- hr_utility.set_location('Completed the Employer Archive',600);
1151
1152 -- hr_utility.set_location('Generating the query for assignment actiond ids',600);
1153
1154 sqlstr := 'SELECT DISTINCT person_id
1155 FROM per_people_f ppf
1156 ,pay_payroll_actions ppa
1157 WHERE ppa.payroll_action_id = :payroll_action_id
1158 AND ppa.business_group_id = ppf.business_group_id
1159 ORDER BY ppf.person_id';
1160
1161 --and ppf.person_id = 13916';
1162
1163 /* v_log_header := RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','NL_ASSIGNMENT_NUMBER'),1,20),20)
1164 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FULL_NAME'),1,35),35)
1165 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FIELD_NAME'),1,35),35)
1166 --||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR_TYPE'),1,15),15)
1167 --||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','MESSAGE'),1,70),70
1168 );
1169
1170 Fnd_file.put_line(FND_FILE.LOG,v_log_header);*/
1171
1172 if g_debug then
1173 hr_utility.set_location('Leaving Range Code',350);
1174 end if;
1175
1176 ELSE
1177 /*If mandatory data is missing do not return any person ids*/
1178 sqlstr := 'SELECT DISTINCT person_id
1179 FROM per_people_f ppf
1180 ,pay_payroll_actions ppa
1181 WHERE ppa.payroll_action_id = :payroll_action_id
1182 AND 1 = 2
1183 AND ppa.business_group_id = ppf.business_group_id
1184 ORDER BY ppf.person_id';
1185 END IF;
1186 -- hr_utility.set_location('Exiting Range Code',600);
1187
1188 END RANGE_CODE;
1189
1190 /*******************************************************************************|
1191 |Name : check_Asg_si_provider |
1192 |Type : FUNCTION |
1193 |Description : This FUNCTION checks whether the given assignment corresponds |
1194 | : to the si provider or not |
1195 *********************************************************************************/
1196 FUNCTION check_Asg_si_provider(p_organization_id IN NUMBER
1197 ,p_si_provider_id IN NUMBER
1198 ,p_assignment_id IN NUMBER )
1199 RETURN NUMBER IS
1200
1201 l_si_provider_id NUMBER;
1202 BEGIN
1203
1204 -- hr_utility.set_location('Entering check_Asg_si_provider funciton ',600);
1205
1206 l_si_provider_id := HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(p_organization_id,'ZFW',p_assignment_id);
1207 IF ( l_si_provider_id = p_si_provider_id) THEN
1208 RETURN 1;
1209 END IF;
1210 l_si_provider_id := HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(p_organization_id,'ZW',p_assignment_id);
1211 IF ( l_si_provider_id = p_si_provider_id) THEN
1212 RETURN 1;
1213 END IF;
1214 l_si_provider_id := HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(p_organization_id,'WEWE',p_assignment_id);
1215 IF ( l_si_provider_id = p_si_provider_id) THEN
1216 RETURN 1;
1217 END IF;
1218 l_si_provider_id := HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(p_organization_id,'WEWA',p_assignment_id);
1219 IF ( l_si_provider_id = p_si_provider_id) THEN
1220 RETURN 1;
1221 END IF;
1222 l_si_provider_id := HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(p_organization_id,'WAOB',p_assignment_id);
1223 IF ( l_si_provider_id = p_si_provider_id) THEN
1224 RETURN 1;
1225 END IF;
1226 l_si_provider_id := HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(p_organization_id,'WAOD',p_assignment_id);
1227 IF ( l_si_provider_id = p_si_provider_id) THEN
1228 RETURN 1;
1229 else
1230 return 0;
1231 end if;
1232 -- hr_utility.set_location('Exiting check_Asg_si_provider funciton ',600);
1233
1234 end check_Asg_si_provider;
1235
1236
1237 /*******************************************************************************|
1238 |Name : chekck_asg_terminate |
1239 |Type : FUNCTION |
1240 |Description : This FUNCTION checks whether the given assignment terminated |
1241 | : on the given date |
1242 *********************************************************************************/
1243
1244 Function check_asg_terminate ( p_assignment_id NUMBER,
1245 p_rep_date DATE) return NUMBER IS
1246
1247 cursor csr_asg_term is
1248 select max(asg.effective_end_date) asg_end_date
1249 from per_all_assignments_f asg,
1250 per_assignment_status_types past
1251 where asg.assignment_id = p_assignment_id
1252 and past.per_system_status = 'ACTIVE_ASSIGN'
1253 and asg.assignment_status_type_id = past.assignment_status_type_id
1254 and asg.effective_start_date <= p_rep_date
1255 and asg.effective_end_date = p_rep_date;
1256 l_eff_date Date;
1257
1258 BEGIN
1259
1260 -- hr_utility.set_location('Entering chekck_asg_terminate funciton ',600);
1261
1262 OPEN csr_asg_term ;
1263 fetch csr_asg_term into l_eff_date;
1264 if csr_asg_term % found and l_eff_date = p_rep_date then
1265 return 1;
1266 else return 0;
1267 end if;
1268 close csr_asg_term;
1269 -- hr_utility.set_location('Exiting chekck_asg_terminate funciton ',600);
1270 end check_asg_terminate;
1271
1272 /*******************************************************************************|
1273 |Name : ASSIGNMENT_ACTION_CODE |
1274 |Type : Procedure |
1275 |Description : This procedure further restricts the assignment id's returned |
1276 | by the range code. |
1277 *********************************************************************************/
1278
1279 Procedure ASSIGNMENT_ACTION_CODE (p_payroll_action_id in number
1280 ,p_start_person_id in number
1281 ,p_end_person_id in number
1282 ,p_chunk in number) IS
1283
1284 Cursor csr_process_assignments
1285 (p_business_group_id number,
1286 p_employer_id number,
1287 p_reporting_start_date date,
1288 p_reporting_end_date date,
1289 p_org_struct_version_id number,
1290 p_si_provider_id number,
1291 p_frequency varchar2
1292 ) IS
1293 Select
1294 paa.organization_id,
1295 paa.soft_coding_keyflex_id,
1296 pap.person_id,
1297 paa.assignment_id,
1298 pap.last_name,
1299 paa.assignment_number,
1300 pap.full_name,
1301 pap.Date_of_Birth,
1302 pap.national_identifier,
1303 paa.grade_id
1304 from
1305 per_all_people_f pap
1306 ,per_all_assignments_f paa
1307 ,hr_soft_coding_keyflex scl_flx
1308 where
1309 pap.business_group_id =p_business_group_id
1310 and pap.person_id = paa.person_id
1311 and paa.person_id BETWEEN p_start_person_id AND p_end_person_id
1312 and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
1313 and p_reporting_end_Date between pap.effective_start_date and pap.effective_end_date
1314 and paa.effective_start_date =
1315 (
1316 SELECT MIN(asg.effective_start_date)
1317 FROM per_assignment_status_types past, per_all_assignments_f asg
1318 WHERE asg.assignment_id = paa.assignment_id
1319 and past.per_system_status = 'ACTIVE_ASSIGN'
1320 and asg.assignment_status_type_id = past.assignment_status_type_id
1321 and asg.effective_start_date <= p_reporting_End_Date
1322 and nvl(asg.effective_end_date, p_reporting_End_Date) >= p_reporting_Start_Date
1323 )
1324 and p_employer_id in
1325 (select hr_nl_org_info.get_tax_org_id(p_org_struct_version_id,paa.organization_id) from dual)
1326 and not exists
1327 (select 1
1328 from
1329 pay_action_information ee_ats
1330 WHERE ee_ats.action_context_type='AAP'
1331 AND ee_ats.action_information_category = 'NL CBS EMPLOYEE DETAILS'
1332 AND ee_ats.action_information1 =p_employer_id
1333 AND ee_ats.action_information4 =pap.person_id
1334 AND ee_ats.action_information2 =p_si_provider_id
1335 AND ee_ats.action_information3 =paa.assignment_id
1336 AND ee_ats.effective_date =p_reporting_end_date);
1337
1338
1339 l_asg_act_id number;
1340 l_dummy number;
1341 l_assignment_id number;
1342
1343 -- Variables for Get All Parameters.
1344 l_business_group_id NUMBER;
1345 l_employer_id NUMBER;
1346 l_si_provider_id NUMBER;
1347 l_payroll_id NUMBER;
1348 l_report_type pay_payroll_actions.report_type%TYPE;
1349 l_effective_date DATE;
1350 l_employer NUMBER;
1351 l_si_provider NUMBER;
1352 l_org_struct_id NUMBER;
1353 l_medium_code NUMBER;
1354 l_density NUMBER;
1355 l_reporting_date DATE;
1356 --
1357 --
1358 l_org_struct_version_id NUMBER;
1359 l_asg_employer_id NUMBER;
1360 l_frequency VARCHAR2(100);
1361 l_reporting_start_date DATE;
1362 l_reporting_end_date DATE;
1363 l_soft_coding_keyflex_id NUMBER;
1364
1365 -- Variables for Balances.
1366 l_holiday_hours NUMBER;
1367 l_adv_hours NUMBER;
1368 l_si_wage NUMBER;
1369 l_unique_payments NUMBER;
1370 l_pre_tax_deductions NUMBER;
1371 l_saving_scheme NUMBER;
1372 l_sickness_days NUMBER;
1373 l_unpaid_hours NUMBER;
1374 l_sickness_pay NUMBER;
1375 l_overtime_hours NUMBER;
1376 l_wage_agreed_by_contract NUMBER;
1377 l_number_of_days NUMBER;
1378 l_si_days_quarter NUMBER;
1379 l_paid_gross_wage NUMBER;
1380 l_wage_for_overtime NUMBER;
1381 l_employment NUMBER :=0;
1382 l_ovn NUMBER;
1383 l_number NUMBER;
1384 l_unpaid_hours1 VARCHAR2(20);
1385 l_overtime_hours1 VARCHAR2(20);
1386 -- Variables for other values.
1387 l_income_code VARCHAR2(80);
1388 l_health_insurance VARCHAR2(200);
1389
1390 -- Variables for Archiving the data
1391 l_grade_salary_info VARCHAR2(12);
1392 l_person_id NUMBER;
1393 l_max_assgt_act_id NUMBER;
1394 l_action_info_id NUMBER;
1395 l_employment_code NUMBER :=0;
1396 l_wage_tax_discount VARCHAR2(20);
1397 l_tax_code VARCHAR2(20);
1398 l_labour_relation_code VARCHAR2(20);
1399 l_working_schedule VARCHAR2(20);
1400 l_tax_info VARCHAR2(40);
1401 l_work_times VARCHAR2(40);
1402 l_cao_code NUMBER;
1403 l_si_provider_check NUMBER;
1404 l_grade_number VARCHAR2(10);
1405 l_salary_number VARCHAR2(10);
1406 l_deviating_working_hours number;
1407 l_grade_id NUMBER;
1408 l_public_sector VARCHAR2(10);
1409 l_asg_term NUMBER;
1410
1411
1412 BEGIN
1413 --
1414
1415 g_debug := FALSE;
1416 if g_debug then
1417 -- hr_utility.trace_on(NULL,'CBS');
1418 hr_utility.set_location('Entering Assignment Action Code',600);
1419 hr_utility.set_location('p_payroll_action_id'||p_payroll_action_id,400);
1420 end if;
1421
1422 g_error_flag := hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR');
1423
1424 /*Get concurrent program parameters*/
1425 PAY_NL_CBS_FILE.get_all_parameters(p_payroll_action_id,
1426 P_business_group_id => l_business_group_id,
1427 P_reporting_date => l_reporting_date,
1428 P_effective_date => l_effective_date,
1429 P_employer => l_employer_id,
1430 P_si_provider => l_si_provider_id,
1431 P_org_struct_id => l_org_struct_id,
1432 P_medium_code => l_medium_code,
1433 P_density => l_density);
1434
1435 if g_debug then
1436 hr_utility.set_location('Executed the cursor in get all parameters ',425);
1437 hr_utility.set_location('p_reporting_date'||l_reporting_date,425);
1438 hr_utility.set_location('p_employer'||l_employer_id,425);
1439 hr_utility.set_location('p_org_struct_id'||l_org_struct_id,425);
1440 hr_utility.set_location('p_si_provider'||l_si_provider_id,425);
1441 hr_utility.set_location('p_medium_code'||l_medium_code,425);
1442 hr_utility.set_location('p_density'||l_density,425);
1443 hr_utility.set_location('p_effective_date'||l_effective_date,425);
1444 hr_utility.set_location('p_business_group_id'||l_business_group_id,425);
1445 end if;
1446
1447 l_org_struct_version_id:=pay_nl_taxoffice_archive.get_org_hierarchy(l_org_struct_id,l_reporting_date);
1448 l_frequency := hr_nl_org_info.get_reporting_frequency(l_employer_id);
1449
1450 -- hr_utility.set_location('l_frequency'||l_frequency,425);
1451 if l_frequency = 'K' then
1452 l_reporting_start_date := ADD_MONTHS(l_reporting_date,-3);
1453 else
1454 if l_frequency = 'M' then
1455 l_reporting_start_date := ADD_MONTHS(l_reporting_date,-1);
1456 else
1457 if l_frequency = 'P' then
1458 l_reporting_start_date := l_reporting_date - 28;
1459 END IF;
1460 end if;
1461 end if;
1462
1463 -- hr_utility.set_location('l_reporting_start_date'||l_reporting_start_date,425);
1464 l_reporting_end_date := l_reporting_date;
1465 -- hr_utility.set_location('l_reporting_end_date'||l_reporting_end_date,425);
1466
1467
1468 FOR csr_process_rec in csr_process_assignments
1469 (l_business_group_id,
1470 l_employer_id,
1471 l_reporting_start_date,
1472 l_reporting_end_date,
1473 l_org_struct_version_id,
1474 l_si_provider_id,
1475 l_frequency) LOOP
1476
1477 l_assignment_id := csr_process_rec.assignment_id;
1478 -- hr_utility.set_location('l_assignment_id'||l_assignment_id,425);
1479 l_person_id := csr_process_rec.person_id;
1480 -- hr_utility.set_location('l_person_id'||l_person_id,425);
1481 l_asg_employer_id := csr_process_rec.organization_id;
1482 -- hr_utility.set_location('l_asg_employer_id'||l_asg_employer_id,425);
1483 g_assignment_number:=csr_process_rec.assignment_number;
1484 g_full_name:=csr_process_rec.full_name;
1485 l_soft_coding_keyflex_id := csr_process_rec.soft_coding_keyflex_id;
1486 l_grade_id := csr_process_rec.grade_id;
1487
1488 g_error_count := 0;
1489 l_si_provider_check := check_Asg_si_provider(l_asg_employer_id,l_si_provider_id,l_assignment_id);
1490 -- hr_utility.set_location('l_si_provider_check'||l_si_provider_check,500);
1491 -- Fetch maximum assignment action id in the reporting period
1492 l_max_assgt_act_id := pay_nl_taxoffice_archive.get_max_assgt_act_id(l_assignment_id,l_reporting_start_date,l_reporting_end_date);
1493 -- hr_utility.set_location('max assignmentid'||l_max_assgt_act_id,600);
1494
1495 IF (l_si_provider_check = 1 AND l_max_assgt_act_id is not null) THEN
1496 l_number := get_tax_details(l_max_assgt_act_id,l_wage_tax_discount,l_tax_code,l_labour_relation_code);
1497 l_income_code := pay_nl_taxoffice_archive.get_income_code(l_assignment_id,l_reporting_start_date,l_reporting_end_date);
1498 l_health_insurance := get_health_insurance(l_assignment_id,l_reporting_date);
1499 --l_work_pattern := get_work_pattern(l_soft_coding_keyflex_id);
1500 -- hr_utility.set_location('FETCHED HEALTH INSURANCE'||l_health_insurance,400);
1501 get_working_schedule(l_assignment_id,l_working_schedule,l_reporting_start_date,l_reporting_end_date);
1502 -- hr_utility.set_location('FETCHED WORKING SCHEDULE',600);
1503 l_asg_term := check_asg_terminate ( l_assignment_id , l_reporting_start_date );
1504 -- hr_utility.set_location('CHECKED ASG TERM'||l_asg_term,600);
1505
1506 if l_asg_term = 1 then
1507
1508 l_holiday_hours := 0;
1509 l_adv_hours := 0;
1510 l_si_wage := 0;
1511 l_unique_payments := 0;
1512 l_pre_tax_deductions := 0;
1513 l_saving_scheme := 0;
1514 l_sickness_days := 0;
1515 l_unpaid_hours := 0;
1516 l_sickness_pay := 0;
1517 l_overtime_hours := 0;
1518 l_wage_agreed_by_contract := 0;
1519 l_number_of_days := 0;
1520 l_si_days_quarter := 0;
1521 l_paid_gross_wage := 0;
1522 l_wage_for_overtime := 0;
1523
1524 -- Check for the Full sickness wage paid at the org level
1525 if (hr_nl_org_info.Get_Full_Sickness_Wage_Paid (l_employer_id) <> 'Y')
1526 then l_sickness_days := 99;
1527 l_sickness_pay := 9999999;
1528 end if;
1529 -- hr_utility.set_location('fetched full sickness wage paid'||l_asg_term,600);
1530
1531 else
1532 l_number := get_balances1(l_frequency
1533 ,l_max_assgt_act_id
1534 ,l_holiday_hours
1535 ,l_adv_hours
1536 ,l_si_wage
1537 ,l_unique_payments
1538 ,l_pre_tax_deductions
1539 ,l_saving_scheme
1540 ,l_sickness_days
1541 ,l_unpaid_hours
1542 ,l_sickness_pay
1543 ,l_overtime_hours );
1544
1545
1546 -- hr_utility.set_location('l_holiday_hours'||l_holiday_hours,600);
1547
1548 l_number := get_balances2(l_frequency
1549 ,l_max_assgt_act_id
1550 ,l_wage_agreed_by_contract
1551 ,l_number_of_days
1552 ,l_si_days_quarter
1553 ,l_paid_gross_wage
1554 ,l_wage_for_overtime);
1555
1556 if (hr_nl_org_info.Get_Full_Sickness_Wage_Paid (l_employer_id) <> 'Y')
1557 then l_sickness_days := 99;
1558 l_sickness_pay := 9999999;
1559 end if;
1560
1561 end if;
1562
1563
1564 -- hr_utility.set_location('l_wage_agreed_by_contract'||l_wage_agreed_by_contract,600);
1565 get_cao_code(l_assignment_id,l_cao_code,l_reporting_date);
1566 get_dev_work_hours(l_assignment_id,l_deviating_working_hours,l_reporting_start_date,l_reporting_end_date);
1567
1568 l_number:= get_employment_code(l_assignment_id,l_employment_code,l_reporting_start_date,l_reporting_end_date);
1569 l_public_sector := hr_nl_org_info.Get_Public_Sector_Org(l_employer_id);
1570 l_number := get_grade_salary_number(l_assignment_id,l_business_group_id,l_employer_id,l_grade_id,l_reporting_date
1571 ,l_public_sector,l_grade_number,l_salary_number);
1572 l_unpaid_hours1 := fnd_number.number_to_canonical(l_unpaid_hours);
1573 l_overtime_hours1 := fnd_number.number_to_canonical(l_overtime_hours);
1574
1575 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_EMPLOY_TYPE',l_employment_code);
1576 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_GRADE_NUMBER',l_grade_number);
1577 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_SALARY_NUMBER',l_salary_number);
1578 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_WAGE_TAX_DISC',l_wage_tax_discount);
1579 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_TAX_TABLE',l_tax_code);
1580 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','INCOME_CODE',l_income_code);
1581 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_LABOUR_RELATION',l_labour_relation_code);
1582 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_HEALTH_INSURANCE',l_health_insurance);
1583 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_WORK_SCHEDULE',l_working_schedule);
1584 Mandatory_Check('PAY_NL_EE_REQUIRED_FIELD','NL_DEV_WORK_HOURS',l_deviating_working_hours);
1585
1586 l_grade_salary_info := l_grade_number || l_salary_number;
1587 l_tax_info := lpad(l_wage_tax_discount,2,'0') || l_tax_code ||l_income_code||lpad(l_labour_relation_code,2,'0');
1588 l_work_times := l_working_schedule || l_deviating_working_hours || lpad(to_char(l_holiday_hours),3,'0') || lpad(to_char(l_adv_hours),3,'0') ;
1589
1590 IF g_error_count = 0 then
1591 IF l_max_assgt_act_id IS NOT NULL THEN
1592 /*Create the Assignment Action for the Assignments */
1593
1594 -- hr_utility.set_location('creating asg_act_id'||l_asg_act_id,600);
1595
1596 SELECT pay_assignment_actions_s.NEXTVAL
1597 INTO l_asg_act_id
1598 FROM dual;
1599
1600 -- hr_utility.set_location('new l_asg_act_id'||l_asg_act_id,600);
1601 --
1602 -- Create the archive assignment action
1603 --
1604 -- hr_utility.set_location('Before Inserting new assignment action id',600);
1605
1606 hr_nonrun_asact.insact(l_asg_act_id,l_Assignment_ID, p_payroll_action_id,p_chunk,NULL);
1607 -- hr_utility.set_location('Inserted new assignment action id',600);
1608 end if;
1609
1610 -- hr_utility.set_location('Going to archive the ee data',600);
1611 pay_action_information_api.create_action_information
1612 ( p_action_information_id => l_action_info_id
1613 ,p_action_context_id => l_asg_act_id
1614 ,p_action_context_type => 'AAP'
1615 ,p_object_version_number => l_ovn
1616 ,p_effective_date => l_reporting_end_date
1617 ,p_source_id => NULL
1618 ,p_source_text => NULL
1619 ,p_action_information_category => 'NL CBS EMPLOYEE DETAILS'
1620 ,p_action_information1 => l_employer_id
1621 ,p_action_information2 => l_si_provider_id
1622 ,p_action_information3 => l_assignment_id
1623 ,p_action_information4 => l_person_id
1624 ,p_action_information5 => l_grade_salary_info
1625 ,p_action_information6 => l_tax_info -- (Wage tax discount,tax table code,Income code,Labour relation code)
1626 ,p_action_information7 => l_health_insurance
1627 ,p_action_information8 => l_employment_code
1628 ,p_action_information9 => l_work_times --(Working schedule,deviating working hours,holiday hours, adv hours)
1629 ,p_action_information10 => l_cao_code
1630 ,p_action_information11 => l_number_of_days
1631 ,p_action_information12 => l_unpaid_hours1
1632 ,p_action_information13 => l_overtime_hours1
1633 ,p_action_information14 => l_wage_agreed_by_contract
1634 ,p_action_information15 => l_paid_gross_wage
1635 ,p_action_information16 => l_wage_for_overtime
1636 ,p_action_information17 => l_si_wage
1637 ,p_action_information18 => l_unique_payments
1638 ,p_action_information19 => l_pre_tax_deductions
1639 ,p_action_information20 => l_saving_scheme
1640 ,p_action_information21 => l_sickness_pay
1641 ,p_action_information22 => l_sickness_days
1642 ,p_action_information23 => l_si_days_quarter
1643 );
1644 -- hr_utility.set_location('Finished the archival process',600);
1645 END IF;
1646 END IF;
1647 END LOOP;
1648 END ASSIGNMENT_ACTION_CODE;
1649
1650 /********************************************************************************
1651 |Name : ARCHIVE_INIT |
1652 |Type : Procedure |
1653 |Description : Procedure sets the global tables g_statutory_balance_table, |
1654 | g_stat_element_table,g_user_balance_table,g_element_table. |
1655 *********************************************************************************/
1656
1657 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER) IS
1658 l_dummy number;
1659 BEGIN
1660 if g_debug then
1661 hr_utility.trace_on(NULL,'CBS');
1662 hr_utility.set_location('Entering ARCHIVE_INIT ',600);
1663 end if;
1664
1665 END ARCHIVE_INIT;
1666
1667 /********************************************************************************
1668 |Name : ARCHIVE_CODE |
1669 |Type : Procedure |
1670 |Description : This is the main procedure which calls the several procedures |
1671 | to archive the data. |
1672 *********************************************************************************/
1673
1674 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
1675 ,p_effective_date IN DATE) IS
1676 l_dummy number;
1677 BEGIN
1678
1679 if g_debug then
1680 hr_utility.trace_on(NULL,'CBS');
1681 hr_utility.set_location('Entering ARCHIVE_INIT ',600);
1682 end if;
1683
1684 END ARCHIVE_CODE;
1685 end PAY_NL_CBS_FILE;
1686