[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_ANNUAL_SI_FILE
Source
1 PACKAGE BODY PAY_NL_ANNUAL_SI_FILE as
2 /* $Header: pynlasif.pkb 120.0.12000000.1 2007/01/17 22:54:30 appldev noship $ */
3
4 g_package varchar2(33) := ' PAY_NL_ANNUAL_SI_FILE';
5 g_debug BOOLEAN;
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_message_name VARCHAR2(150) := ' ';
13
14
15 /*-------------------------------------------------------------------------------
16 |Name : Mandatory_Check |
17 |Type : Procedure |
18 |Description : Procedure to check if the specified Mandatory Field is NULL |
19 | if so flag a Error message to the Log File |
20 -------------------------------------------------------------------------------*/
21
22 Procedure Mandatory_Check(p_message_name varchar2
23 ,p_field varchar2
24 ,p_value varchar2) is
25 v_message_text fnd_new_messages.message_text%TYPE;
26 v_employee_dat VARCHAR2(255);
27 v_label_desc hr_lookups.meaning%TYPE;
28 Begin
29 if g_debug then
30 hr_utility.set_location('Checking Field '||p_field,425);
31 end if;
32
33 If p_value is null then
34 v_label_desc := hr_general.decode_lookup('HR_NL_REPORT_LABELS', p_field);
35 v_employee_dat :=RPAD(SUBSTR(g_assignment_number,1,20),20)
36 ||' '||RPAD(SUBSTR(g_full_name,1,25),25)
37 ||' '||RPAD(SUBSTR(v_label_desc,1,30),30)
38 ||' '||RPAD(SUBSTR(g_error_flag,1,15),15);
39 g_error_count := NVL(g_error_count,0) +1;
40
41 if p_message_name <> g_message_name then
42 hr_utility.set_message(801,p_message_name);
43 v_message_text := rpad(fnd_message.get,255,' ');
44 FND_FILE.PUT_LINE(FND_FILE.LOG,v_message_text);
45 g_message_name := p_message_name;
46 end if;
47
48 FND_FILE.PUT_LINE(FND_FILE.LOG, v_employee_dat);
49 end if;
50
51 end;
52
53
54
55 /*-----------------------------------------------------------------------------
56 |Name : GET_SI_WAGE |
57 |Type : Function |
58 |Description: Function returns SI Wage - sum of SI_INCOME_STANDARD_TAX, |
59 | SI_INCOME_SPECIAL_TAX , RETRO_SI_INCOME_STANDARD_TAX , |
60 | RETRO_SI_INCOME_SPECIAL_TAX |
61 | SIP,ORG levels |
62 -------------------------------------------------------------------------------*/
63
64 function get_si_wage(p_assgt_act_id number)RETURN number IS
65 l_sum_of_balances number;
66 l_balance_value number;
67 l_defined_balance_id number;
68 l_si_wage number;
69
70 BEGIN
71 if g_debug then
72 hr_utility.set_location('Entering get_wage',800);
73 end if;
74
75 l_sum_of_balances:=0;
76
77
78 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SI_INCOME_STANDARD_TAX_ASG_YTD');
79 --hr_utility.trace('L_DEFINED_BALANCE_ID'||l_defined_balance_id);
80 --hr_utility.trace('ASSIGNMENT_ACTION_ID'||p_assgt_act_id);
81 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
82 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
83
84 if g_debug then
85 hr_utility.set_location('SI_INCOME_STANDARD_TAX_ASG_YTD',425);
86 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
87 hr_utility.set_location('l_balance_value'||l_balance_value,425);
88 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
89 end if;
90
91 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SI_INCOME_SPECIAL_TAX_ASG_YTD');
92 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
93 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
94
95 if g_debug then
96 hr_utility.set_location('SI_INCOME_SPECIAL_TAX_ASG_YTD',425);
97 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
98 hr_utility.set_location('l_balance_value'||l_balance_value,425);
99 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
100 end if;
101
102 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SI_INCOME_STANDARD_TAX_ASG_YTD');
103 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
104 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
105
106 if g_debug then
107 hr_utility.set_location('WAGE_IN_MONEY_STANDARD_TAX_ONLY_ASG_YTD',425);
108 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
109 hr_utility.set_location('l_balance_value'||l_balance_value,425);
110 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
111 end if;
112
113 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('RETRO_SI_INCOME_SPECIAL_TAX_ASG_YTD');
114 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
115 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
116
117 if g_debug then
118 hr_utility.set_location('WAGE_IN_MONEY_SPECIAL_TAX_ONLY_ASG_YTD',425);
119 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
120 hr_utility.set_location('l_balance_value'||l_balance_value,425);
121 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
122 end if;
123
124 l_si_wage:=floor(l_sum_of_balances);
125
126 return l_si_wage;
127
128 END get_si_wage;
129
130
131 /*-----------------------------------------------------------------------------
132 |Name : GET_SI_SUPPLEMENTARY_DAYS |
133 |Type : Function |
134 |Description: Function returns SI Supplementary Days - |
135 | balance SI_SUPPLEMENATRY_DAYS |
136 -------------------------------------------------------------------------------*/
137
138 function get_si_supplementary_days(p_assgt_act_id number)RETURN number IS
139 l_sum_of_balances number;
140 l_balance_value number;
141 l_defined_balance_id number;
142 l_si_supplementary_days number;
143
144 BEGIN
145 if g_debug then
146 hr_utility.set_location('Entering get_wage',800);
147 end if;
148
149 l_sum_of_balances:=0;
150
151
152 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SI_SUPPLEMENTARY_DAYS_ASG_YTD');
153 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
154 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
155
156 if g_debug then
157 hr_utility.set_location('SI_SUPPLEMENTARY_DAYS_ASG_YTD',425);
158 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
159 hr_utility.set_location('l_balance_value'||l_balance_value,425);
160 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
161 end if;
162
163 l_si_supplementary_days:=l_sum_of_balances;
164
165 return l_si_supplementary_days;
166
167 END get_si_supplementary_days;
168
169 /*-----------------------------------------------------------------------------
170 |Name : GET_SI_AMOUNT_ALLOWANCE |
171 |Type : Function |
172 |Description: Function returns SI Amount Allowance - |
173 | balance SI_AMOUNT_ALLOWANCE |
174 -------------------------------------------------------------------------------*/
175
176 function get_si_amount_allowance(p_assgt_act_id number)RETURN number IS
177 l_sum_of_balances number;
178 l_balance_value number;
179 l_defined_balance_id number;
180 l_si_amount_allowance number;
181
182 BEGIN
183 if g_debug then
184 hr_utility.set_location('Entering get_wage',800);
185 end if;
186
187 l_sum_of_balances:=0;
188
189
190 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('SI_AMOUNT_ALLOWANCE_ASG_YTD');
191 l_balance_value:=pay_balance_pkg.get_value(l_defined_balance_id,p_assgt_act_id);
192 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
193
194 if g_debug then
195 hr_utility.set_location('SI_AMOUNT_ALLOWANCE_ASG_YTD',425);
196 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
197 hr_utility.set_location('l_balance_value'||l_balance_value,425);
198 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
199 end if;
200
201 l_si_amount_allowance:=floor(l_sum_of_balances);
202
203 return l_si_amount_allowance;
204
205 END get_si_amount_allowance;
206
207 function get_org_name(p_org_id number
208 ,l_org_name OUT NOCOPY VARCHAR2)RETURN NUMBER IS
209 cursor csr_get_org_name(p_org_id NUMBER) IS
210 SELECT name from hr_organization_units
211 where organization_id = p_org_id;
212
213 l_number NUMBER;
214
215 BEGIN
216 OPEN csr_get_org_name(p_org_id);
217 FETCH csr_get_org_name INTO l_org_name;
218 CLOSE csr_get_org_name;
219
220 l_number := 1;
221 return l_number;
222
223 END get_org_name;
224
225
226
227 /*-----------------------------------------------------------------------------
228 |Name : GET_SI_SPECIAL_INDICATOR |
229 |Type : Procedure |
230 |Description: Function fetches the SI Special Indicator |
231 -------------------------------------------------------------------------------*/
232
233 PROCEDURE get_si_special_indicator(p_assignment_id IN NUMBER,
234 l_si_special_indicator OUT NOCOPY VARCHAR2 )IS
235 CURSOR get_scl_id(p_assignment_id NUMBER) IS
236 SELECT soft_coding_keyflex_id FROM per_all_assignments_f
237 WHERE assignment_id = p_assignment_id AND
238 effective_start_date =
239 (SELECT max(effective_start_date)
240 FROM per_all_assignments_f asg,per_assignment_status_types past
241 WHERE past.per_system_status = 'ACTIVE_ASSIGN'
242 AND asg.assignment_status_type_id = past.assignment_status_type_id and assignment_id = p_assignment_id);
243
244 CURSOR get_si_special_indicator(p_scl_id NUMBER) IS
245 SELECT segment30 from hr_soft_Coding_keyflex where soft_coding_keyflex_id = p_scl_id;
246
247 l_scl_id NUMBER;
248
249
250 BEGIN
251
252 OPEN get_scl_id(p_assignment_id);
253 FETCH get_scl_id into l_scl_id;
254 CLOSE get_scl_id;
255
256 OPEN get_si_special_indicator(l_scl_id);
257 FETCH get_si_special_indicator INTO l_si_special_indicator;
258 CLOSE get_si_special_indicator;
259
260 IF l_si_special_indicator IS NULL THEN
261 l_si_special_indicator :='00';
262 END IF;
263
264 END get_si_special_indicator;
265
266 /*-----------------------------------------------------------------------------
267 |Name : GET_NUMBER_OF_DAYS |
268 |Type : Function |
269 |Description: Function returns Number Of Days - |
270 | balance REAL_SOCIAL_INSURANCE_DAYS |
271 -------------------------------------------------------------------------------*/
272
273 function get_number_of_days(p_assgt_act_id number)RETURN number IS
274
275 CURSOR csr_get_context_id(p_context_name VARCHAR2
276 ,p_assignment_action_id NUMBER) IS
277 SELECT ff.context_id context_id
278 ,pact.context_value context_value
279 , decode(context_value,'ZFW',0,'ZW',1,
280 'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6) seq
281 FROM ff_contexts ff
282 ,pay_action_contexts pact
283 WHERE ff.context_name = p_context_name
284 AND pact.context_id = ff.context_id
285 AND pact.assignment_action_id=p_assignment_action_id
286 ORDER BY decode(context_value,'ZFW',0,'ZW',1,
287 'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6);
288
289 l_context_id NUMBER;
290 l_context_value pay_action_contexts.context_value%TYPE;
291 l_seq VARCHAR2(60);
292 l_sum_of_balances number;
293 l_balance_value number;
294 l_defined_balance_id number;
295 l_si_amount_allowance number;
296 l_number_of_days number;
297
298 BEGIN
299 if g_debug then
300 hr_utility.set_location('Entering get_wage',800);
301 end if;
302
303 l_sum_of_balances:=0;
304
305
306 l_defined_balance_id:=pay_nl_general.get_defined_balance_id('REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_YTD');
307 OPEN csr_get_context_id('SOURCE_TEXT',p_assgt_act_id);
308 FETCH csr_get_context_id INTO l_context_id,l_context_value,l_seq;
309 CLOSE csr_get_context_id;
310
311 IF l_context_id IS NOT NULL THEN
312 l_balance_value := pay_balance_pkg.get_value(l_defined_balance_id
313 ,p_assgt_act_id
314 ,NULL
315 ,NULL
316 ,l_context_id
317 ,l_context_value
318 ,NULL
319 ,NULL);
320 END IF;
321 l_sum_of_balances:=l_sum_of_balances + l_balance_value;
322
323 if g_debug then
324 hr_utility.set_location('SI_AMOUNT_ALLOWANCE_ASG_YTD',425);
325 hr_utility.set_location('l_defined_balance_id'||l_defined_balance_id,425);
326 hr_utility.set_location('l_balance_value'||l_balance_value,425);
327 hr_utility.set_location('l_sum_of_balances'||l_sum_of_balances,425);
328 end if;
329
330 l_number_of_days:=floor(l_sum_of_balances);
331
332 return l_number_of_days;
333
334 END get_number_of_days;
335
336 /*-----------------------------------------------------------------------------
337 |Name : Get_SIP_Details |
338 |Type : Function |
339 |Description: Procedure gets Reg Number , reporting name details at |
340 | SIP level |
341 -------------------------------------------------------------------------------*/
342 FUNCTION Get_SIP_Details
343 ( P_Employer_ID IN NUMBER
344 ,P_SI_PROVIDER_ID IN NUMBER
345 ,P_Process_Date IN DATE
346 ,p_Sender_Rep_Name_sip OUT NOCOPY VARCHAR2
347 ,p_Sender_Reg_Number_sip OUT NOCOPY VARCHAR2
348 ,p_Employer_Rep_Name_sip OUT NOCOPY VARCHAR2
349 ,p_Employer_Reg_Number_sip OUT NOCOPY VARCHAR2
350 ) RETURN NUMBER IS
351 --
352
353 CURSOR csr_sip_details(p_employer_id NUMBER , p_si_provider_id NUMBER) IS
354 SELECT org_information8 SRepName,org_information9 SRegNo,
355 org_information10 ERRepName,org_information11 ERRegNo,
356 DECODE(org_information3,'ZFW',1,'ZW',2,'WW',3,'WAO',4,'AMI',5,6) sort_order
357 FROM hr_organization_information
358 where organization_id = p_employer_id
359 and org_information_context = 'NL_SIP'
360 and org_information4 = p_si_provider_id
361 AND p_process_date between
362 FND_DATE.CANONICAL_TO_DATE(org_information1) and
363 nvl(FND_DATE.CANONICAL_TO_DATE(org_information2),hr_general.end_of_time)
364 ORDER BY ORG_INFORMATION7,DECODE(org_information3,'ZFW',1,'ZW',2,'WW',3,'WAO',4,'AMI',5,6) ;
365
366 l_number NUMBER;
367 l_si_type number;
368
369 BEGIN
370
371
372 OPEN csr_sip_details(p_employer_id,p_si_provider_id);
373 FETCH csr_sip_details INTO p_Sender_Rep_Name_sip,p_Sender_Reg_Number_sip,
374 p_Employer_Rep_Name_sip,p_Employer_Reg_Number_sip,l_si_type;
375 CLOSE csr_sip_details;
376 l_number := 1;
377 return l_number;
378
379 END;
380
381
382 /*-----------------------------------------------------------------------------
383 |Name : GET_ALL_PARAMETERS |
384 |Type : Procedure |
385 |Description: Procedure which returns all the parameters of the archive process|
386 -------------------------------------------------------------------------------*/
387
388
389 -----------------------------------------------------------------------------
390 -- GET_ALL_PARAMETERS gets all parameters for the payroll action
391 -----------------------------------------------------------------------------
392 PROCEDURE get_all_parameters (
393 p_payroll_action_id IN NUMBER
394 ,p_business_group_id OUT NOCOPY NUMBER
395 ,p_si_provider_id OUT NOCOPY NUMBER
396 ,p_effective_date OUT NOCOPY DATE
397 ,p_tax_year OUT NOCOPY DATE
398 ,p_employer OUT NOCOPY NUMBER
399 ,p_org_struct_id OUT NOCOPY NUMBER ) IS
400 --
401 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
402 SELECT fnd_date.canonical_to_date(pay_core_utils.get_parameter('TAX_YEAR',legislative_parameters))
403 ,pay_core_utils.get_parameter('EMPLOYER_ID',legislative_parameters)
404 ,pay_core_utils.get_parameter('ORG_HIERARCHY',legislative_parameters)
405 ,pay_core_utils.get_parameter('SI_PROVIDER_ID',legislative_parameters)
406 ,effective_date
407 ,business_group_id
408 FROM pay_payroll_actions
409 WHERE payroll_action_id = p_payroll_action_id;
410 --
411 l_effective_date date;
412 l_proc VARCHAR2(80):= g_package||' get_all_parameters ';
413 --
414 BEGIN
415 --
416 OPEN csr_parameter_info (p_payroll_action_id);
417 FETCH csr_parameter_info INTO
418 p_tax_year,p_employer, p_org_struct_id,p_si_provider_id
419 ,p_effective_date,p_business_group_id;
420 CLOSE csr_parameter_info;
421 --
422 --hr_utility.trace('YEAR'||p_tax_year);
423 --hr_utility.trace('EMPLOYER'||p_employer);
424 --hr_utility.trace('ORG STRUCT'||p_org_struct_id);
425 --hr_utility.trace('EFFECTIVE_DATE'||p_effective_date);
426 --hr_utility.trace('BUSINESS_GROUP'||p_business_group_id);
427 END get_all_parameters;
428 --
429
430 /*--------------------------------------------------------------------
431 |Name : RANGE_CODE |
432 |Type : Procedure |
433 |Description: This procedure returns a sql string to select a range of|
434 | assignments eligible for archival
435 ----------------------------------------------------------------------*/
436
437 Procedure RANGE_CODE (pactid IN NUMBER
438 ,sqlstr OUT NOCOPY VARCHAR2) is
439
440 v_log_header VARCHAR2(255);
441 l_number number;
442 l_return_value number;
443 l_business_group_id NUMBER;
444 l_si_provider_id NUMBER;
445 l_effective_date DATE;
446 l_tax_year_date DATE;
447 l_tax_year NUMBER;
448 l_tax_year_start_date date;
449 l_tax_year_end_date date;
450 l_employer_id NUMBER;
451 l_org_struct_id NUMBER;
452 l_max_assgt_act_id NUMBER;
453 l_asg_act_id NUMBER;
454 l_Sender_Rep_Name_sip VARCHAR2(100);
455 l_Sender_Reg_Number_sip VARCHAR2(100);
456 l_Employer_Rep_Name_sip VARCHAR2(100);
457 l_Employer_Reg_Number_sip VARCHAR2(100);
458 l_Wage_Tax_Reg_Number_org VARCHAR2(100);
459 l_Wage_Tax_Rep_Name_org VARCHAR2(100);
460 l_Sender_Rep_Name_bg VARCHAR2(100);
461 l_Sender_Reg_Number_bg VARCHAR2(100);
462 l_org_struct_version_id NUMBER;
463 l_assignment_id NUMBER;
464 l_ovn NUMBER;
465 l_ASI_Process_Date DATE;
466 l_action_info_id NUMBER;
467 l_si_wage NUMBER;
468 l_si_supplementary_days NUMBER;
469 l_si_amount_allowance NUMBER;
470 l_si_special_indicator VARCHAR2(10);
471 l_number_of_days NUMBER;
472 l_person_id NUMBER;
473
474 l_org_address number;
475 l_house_number varchar2(255);
476 l_house_no_add varchar2(255);
477 l_street_name varchar2(255);
478 l_line1 varchar2(255);
479 l_line2 varchar2(255);
480 l_line3 varchar2(255);
481 l_city varchar2(255);
482 l_country varchar2(255);
483 l_postal_code varchar2(255);
484
485 l_sender_address number;
486 l_sen_house_number varchar2(255);
487 l_sen_house_no_add varchar2(255);
488 l_sen_street_name varchar2(255);
489 l_sen_line1 varchar2(255);
490 l_sen_line2 varchar2(255);
491 l_sen_line3 varchar2(255);
492 l_sen_city varchar2(255);
493 l_sen_country varchar2(255);
494 l_sen_postal_code varchar2(255);
495 l_sender_address_field varchar2(255);
496 l_sen_city_field varchar2(255);
497 l_tax_address_field varchar2(255);
498 l_tax_city_field varchar2(255);
499
500 BEGIN
501
502 g_error_count := 0;
503 g_payroll_action_id:=pactid;
504
505
506 /*Return the SELECT Statement to select a range of assignments
507 eligible for archival */
508
509 get_all_parameters
510 (
511 pactid,
512 l_business_group_id,
513 l_si_provider_id,
514 l_effective_date,
515 l_tax_year_date,
516 l_employer_id,
517 l_org_struct_id
518 );
519
520 l_tax_year := to_char(l_tax_year_date,'YYYY');
521 l_tax_year_start_date := to_date('01/01/'||l_tax_year,'DD/MM/YYYY');
522 l_tax_year_end_date := to_date('31/12/'||l_tax_year,'DD/MM/YYYY');
523 l_org_struct_version_id:=pay_nl_taxoffice_archive.get_org_hierarchy(l_org_struct_id,l_tax_year_end_date);
524 l_ASI_Process_Date := l_tax_year_end_date;
525
526
527 --hr_utility.trace(l_tax_year_start_date);
528 --hr_utility.trace(l_tax_year_end_date);
529 --hr_utility.trace(l_ASI_Process_Date);
530
531 l_return_value := Get_SIP_Details
532 ( l_Employer_ID
533 ,l_SI_PROVIDER_ID
534 ,l_ASI_Process_Date
535 ,l_Sender_Rep_Name_sip
536 ,l_Sender_Reg_Number_sip
537 ,l_Employer_Rep_Name_sip
538 ,l_Employer_Reg_Number_sip
539 );
540
541 l_return_value := PAY_NL_TAXOFFICE_FILE.GET_TOS_SENDER_DETAILS
542 (l_Business_Group_Id,
543 l_Employer_ID,
544 l_Sender_Rep_Name_bg,
545 l_Sender_Reg_Number_bg,
546 l_Wage_Tax_Rep_Name_org,
547 l_Wage_Tax_Reg_Number_org);
548
549 l_org_address:=PAY_NL_GENERAL.GET_ORGANIZATION_ADDRESS(l_employer_id,l_business_group_id,l_house_number,l_house_no_add,l_street_name,l_line1,l_line2,l_line3,l_city,l_country,l_postal_code);
550 l_sender_address:=PAY_NL_GENERAL.GET_ORGANIZATION_ADDRESS(l_business_group_id,l_business_group_id,l_sen_house_number,l_sen_house_no_add,l_sen_street_name,l_sen_line1,l_sen_line2,l_sen_line3,l_sen_city,l_sen_country,l_sen_postal_code);
551
552 l_sender_address_field := l_sen_street_name || l_sen_house_number || l_sen_house_no_add;
553 l_sen_city_field := l_sen_postal_code || l_sen_city;
554 l_tax_address_field := l_street_name || l_house_number || l_house_no_add;
555 l_tax_city_field := l_postal_code || l_city;
556
557
558
559 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_SENDER_ADDR',l_sender_address_field);
560 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_SENDER_CITY',l_sen_city_field);
561 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_SENDER_REG_NUM',l_Sender_Reg_Number_bg);
562 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_ER_ADDR',l_tax_address_field);
563 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_CITY',l_tax_city_field);
564 Mandatory_Check('PAY_NL_ER_REQUIRED_FIELD','NL_TAX_REGISTRATION_NUMBER',l_Wage_Tax_Reg_Number_org);
565 Mandatory_Check('PAY_NL_SIP_REQUIRED_FIELD','NL_ER_REG_NUMBER',l_Employer_Reg_Number_sip);
566 Mandatory_Check('PAY_NL_SIP_REQUIRED_FIELD','NL_SEN_REP_NAME',l_Sender_Rep_Name_sip);
567 Mandatory_Check('PAY_NL_SIP_REQUIRED_FIELD','NL_SENDER_REG_NUM',l_Sender_Reg_Number_sip);
568 Mandatory_Check('PAY_NL_SIP_REQUIRED_FIELD','NL_ER_REP_NAME',l_Employer_Rep_Name_sip);
569
570 IF g_error_count=0 THEN
571 sqlstr := 'SELECT DISTINCT person_id
572 FROM per_people_f ppf
573 ,pay_payroll_actions ppa
574 WHERE ppa.payroll_action_id = :payroll_action_id
575 AND ppa.business_group_id = ppf.business_group_id
576 ORDER BY ppf.person_id';
577
578 --Write to Log File
579 v_log_header := RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','NL_ASSIGNMENT_NUMBER'),1,20),20)
580 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FULL_NAME'),1,25),25)
581 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FIELD_NAME'),1,25),25)
582 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR_TYPE'),1,15),15)
583 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','MESSAGE'),1,70),70);
584 Fnd_file.put_line(FND_FILE.LOG,v_log_header);
585
586 if g_debug then
587 hr_utility.set_location('Leaving Range Code',350);
588 end if;
589
590
591 ELSE
592 sqlstr := 'SELECT DISTINCT person_id
593 FROM per_people_f ppf
594 ,pay_payroll_actions ppa
595 WHERE ppa.payroll_action_id = :payroll_action_id
596 AND 1 = 2
597 AND ppa.business_group_id = ppf.business_group_id
598 ORDER BY ppf.person_id';
599 END IF;
600
601
602 END RANGE_CODE;
603
604
605 /*--------------------------------------------------------------------
606 |Name : ASSIGNMENT_ACTION_CODE |
607 |Type : Procedure |
608 |Description: This procedure Fetches,validates and archives |
609 | information in the newly created context |
610 | NL ATS EMPLOYEE DETAILS |
611 ----------------------------------------------------------------------*/
612
613 Procedure ASSIGNMENT_ACTION_CODE (p_payroll_action_id in number
614 ,p_start_person_id in number
615 ,p_end_person_id in number
616 ,p_chunk in number) IS
617
618
619 Cursor csr_process_assignments
620 (p_business_group_id number,
621 p_employer_id number,
622 p_tax_year_date date,
623 p_tax_year_start_date date,
624 p_tax_year_end_date date,
625 p_org_struct_version_id number,
626 p_si_provider_id number
627 )
628 is
629 Select
630 paa.organization_id,
631 paa.soft_coding_keyflex_id,
632 pap.person_id,
633 paa.assignment_id,
634 pap.last_name,
635 paa.assignment_number,
636 pap.full_name,
637 pap.Date_of_Birth,
638 pap.national_identifier
639 from
640 per_all_people_f pap
641 ,per_all_assignments_f paa
642 ,hr_soft_coding_keyflex scl_flx
643 where
644 pap.business_group_id =p_business_group_id
645 and pap.person_id = paa.person_id
646 and paa.person_id BETWEEN p_start_person_id AND p_end_person_id
647 and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
648 and p_Tax_Year_End_Date between pap.effective_start_date and pap.effective_end_date
649 and paa.effective_start_date =
650 (
651 SELECT MIN(asg.effective_start_date)
652 FROM per_assignment_status_types past, per_all_assignments_f asg
653 WHERE asg.assignment_id = paa.assignment_id
654 and past.per_system_status = 'ACTIVE_ASSIGN'
655 and asg.assignment_status_type_id = past.assignment_status_type_id
656 and asg.effective_start_date <= p_Tax_Year_End_Date
657 and nvl(asg.effective_end_date, p_Tax_Year_End_Date) >= p_Tax_Year_Start_Date
658 )
659 and p_employer_id in
660 (select hr_nl_org_info.get_tax_org_id(p_org_struct_version_id,paa.organization_id) from dual)
661 and not exists
662 (select 1
663 from
664 pay_action_information ee_ats
665 WHERE ee_ats.action_context_type='AAP'
666 AND ee_ats.action_information_category = 'NL ASI EMPLOYEE DETAILS'
667 AND ee_ats.action_information1 =p_employer_id
668 AND ee_ats.action_information4 =pap.person_id
669 AND ee_ats.action_information2 =paa.assignment_id
670 AND ee_ats.action_information3 =p_si_provider_id
671 AND ee_ats.effective_date =p_tax_year_end_date)
672 AND
673 (p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'ZFW',paa.assignment_id)
674 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'ZW',paa.assignment_id)
675 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'WEWE',paa.assignment_id)
676 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'WEWA',paa.assignment_id)
677 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'WAOB',paa.assignment_id)
678 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'WAOD',paa.assignment_id)
679 );
680
681
682 l_number number;
683 l_return_value number;
684 l_business_group_id NUMBER;
685 l_si_provider_id NUMBER;
686 l_effective_date DATE;
687 l_tax_year_date DATE;
688 l_tax_year NUMBER;
689 l_tax_year_start_date date;
690 l_tax_year_end_date date;
691 l_employer_id NUMBER;
692 l_org_struct_id NUMBER;
693 l_max_assgt_act_id NUMBER;
694 l_asg_act_id NUMBER;
695 l_org_struct_version_id NUMBER;
696 l_assignment_id NUMBER;
697 l_ovn NUMBER;
698 l_ASI_Process_Date DATE;
699 l_action_info_id NUMBER;
700 l_si_wage NUMBER;
701 l_si_supplementary_days NUMBER;
702 l_si_amount_allowance NUMBER;
703 l_si_special_indicator VARCHAR2(10);
704 l_number_of_days NUMBER;
705 l_person_id NUMBER;
706
707
708
709 BEGIN
710
711 g_error_flag := hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR');
712
713 get_all_parameters
714 (
715 p_payroll_action_id,
716 l_business_group_id,
717 l_si_provider_id,
718 l_effective_date,
719 l_tax_year_date,
720 l_employer_id,
721 l_org_struct_id
722 );
723
724 l_tax_year := to_char(l_tax_year_date,'YYYY');
725 l_tax_year_start_date := to_date('01/01/'||l_tax_year,'DD/MM/YYYY');
726 l_tax_year_end_date := to_date('31/12/'||l_tax_year,'DD/MM/YYYY');
727 l_org_struct_version_id:=pay_nl_taxoffice_archive.get_org_hierarchy(l_org_struct_id,l_tax_year_end_date);
728 l_ASI_Process_Date := l_tax_year_end_date;
729
730
731 --hr_utility.trace(l_tax_year_start_date);
732 --hr_utility.trace(l_tax_year_end_date);
733 --hr_utility.trace(l_ASI_Process_Date);
734
735
736
737 FOR process_rec in csr_process_assignments
738 (l_business_group_id ,
739 l_employer_id ,
740 l_tax_year_date ,
741 l_tax_year_start_date ,
742 l_tax_year_end_date ,
743 l_org_struct_version_id ,
744 l_si_provider_id ) LOOP
745
746 l_assignment_id := process_rec.assignment_id;
747 l_person_id := process_rec.person_id;
748 g_assignment_number:=process_rec.assignment_number;
749 g_full_name:=process_rec.full_name;
750 g_error_count := 0;
751 -- Fetch Action to be locked into l_max_assgt_act_id
752
753 l_max_assgt_act_id := pay_nl_taxoffice_archive.get_max_assgt_act_id(l_assignment_id,l_tax_year_start_date,l_tax_year_end_date);
754 IF l_max_assgt_act_id IS NOT NULL THEN
755 /*Create the Assignment Action for the Assignment
756 and Lock the Latest Payroll Run Assignment Action for the Assignment
757 */
758 SELECT pay_assignment_actions_s.NEXTVAL
759 INTO l_asg_act_id
760 FROM dual;
761 --
762 -- Create the archive assignment action
763 --
764 hr_nonrun_asact.insact(l_asg_act_id,l_Assignment_ID, p_payroll_action_id,p_chunk,NULL);
765
766
767 --hr_utility.trace('ASSIGNMENT_DETAILS'||TO_CHAR(l_assignment_id)||' '||to_char(l_max_assgt_act_id));
768
769 -- Now Create the locking actions
770 IF l_max_assgt_act_id IS NOT NULL THEN
771 hr_nonrun_asact.insint(l_asg_act_id,l_max_assgt_act_id);
772 END IF;
773
774
775 /*Archive the Employee Fiscal Record Details in the
776 NL ATS EMPLOYEE DETAILS Context of the Pay Action Information Table
777 */
778
779 /* Obtain the following
780
781 l_number_of_days := Sum of balances as specified in the earlier details table.
782 l_si_wage := Sum of balances as specified
783 l_sup_days:=Balance value of SI Supplementary Days
784 l_allowance_amount := Balance value of SI Amount Allowance
785 l_spl_indicator := New Code Special Indicator */
786
787 l_si_wage := NVL(PAY_NL_ANNUAL_SI_FILE.get_si_wage(l_max_assgt_act_id),0);
788 l_si_supplementary_days := NVL(PAY_NL_ANNUAL_SI_FILE.get_si_supplementary_days(l_max_assgt_act_id),0);
789 l_si_amount_allowance := NVL(PAY_NL_ANNUAL_SI_FILE.get_si_amount_Allowance(l_max_assgt_act_id),0);
790 PAY_NL_ANNUAL_SI_FILE.get_si_special_indicator(l_assignment_id,l_si_special_indicator);
791 l_number_of_days := NVL(PAY_NL_ANNUAL_SI_FILE.get_number_of_days(l_max_assgt_act_id),0);
792 --hr_utility.trace('NUMBER OF DAYS'||to_char(l_number_of_days));
793
794 pay_action_information_api.create_action_information
795 (
796 p_action_information_id => l_action_info_id
797 ,p_action_context_id => l_asg_act_id
798 ,p_action_context_type => 'AAP'
799 ,p_object_version_number => l_ovn
800 ,p_effective_date => l_ASI_Process_Date
801 ,p_source_id => NULL
802 ,p_source_text => NULL
803 ,p_action_information_category => 'NL ASI EMPLOYEE DETAILS'
804 ,p_action_information1 => l_employer_id
805 ,p_action_information2 => l_assignment_id
806 ,p_action_information3 => l_si_provider_id
807 ,p_action_information4 => l_person_id
808 ,p_action_information5 => fnd_number.number_to_canonical(l_number_of_days)
809 ,p_action_information6 => fnd_number.number_to_canonical(l_si_wage)
810 ,p_action_information7 => fnd_number.number_to_canonical(l_si_supplementary_days)
811 ,p_action_information8 => fnd_number.number_to_canonical(l_si_amount_allowance)
812 ,p_action_information9 => fnd_number.number_to_canonical(l_si_special_indicator)
813 );
814
815 END IF;
816 END LOOP;
817
818
819
820
821 END;
822
823 /*----------------------------------------------------------------------------------
824 |Name : ARCHIVE_INIT |
825 |Type : Procedure |
826 |Description : Initialization Code for Archiver |
827 -----------------------------------------------------------------------------------*/
828
829 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER)IS
830 l_number number;
831 BEGIN
832 if g_debug then
833 hr_utility.set_location('Entering Archive Init',600);
834 hr_utility.set_location('Leaving Archive Init',700);
835 end if;
836
837 END ARCHIVE_INIT;
838
839
840
841 /*-------------------------------------------------------------------------------
842 |Name : ARCHIVE_CODE |
843 |Type : Procedure |
844 |Description : Archival code for archiver |
845 -------------------------------------------------------------------------------*/
846
847 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
848 ,p_effective_date IN DATE) IS
849 l_number number;
850 BEGIN
851 if g_debug then
852
853 hr_utility.set_location('Entering Archive Code',700);
854 hr_utility.set_location('Leaving Archive Code',700);
855 end if;
856
857 END ARCHIVE_CODE;
858
859
860
861
862 END PAY_NL_ANNUAL_SI_FILE;