[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_NSI_PROCESS
Source
1 package body PAY_NL_NSI_PROCESS as
2 /* $Header: pynlnsia.pkb 120.1 2005/09/26 05:09:24 summohan noship $ */
3 g_package varchar2(33) := ' PAY_NL_NSI_PROCESS.';
4
5
6 g_error_flag varchar2(30);
7 g_warning_flag varchar2(30);
8 g_error_count NUMBER := 0;
9 g_payroll_action_id NUMBER;
10 g_assignment_number VARCHAR2(30);
11 g_full_name VARCHAR2(150);
12
13 /*------------------------------------------------------------------------------
14 |Name : GET_PARAMETER |
15 |Type : Function |
16 |Description : Funtion to get the parameters of the archive process |
17 -------------------------------------------------------------------------------*/
18
19 function get_parameter(
20 p_parameter_string in varchar2
21 ,p_token in varchar2
22 ,p_segment_number in number default null ) RETURN varchar2
23 IS
24
25 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
26 l_start_pos NUMBER;
27 l_delimiter varchar2(1):=' ';
28 l_proc VARCHAR2(40):= g_package||' get parameter ';
29
30 BEGIN
31 --
32 hr_utility.set_location('Entering get_parameter',52);
33 --
34 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
35 --
36 IF l_start_pos = 0 THEN
37 l_delimiter := '|';
38 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
39 end if;
40
41 IF l_start_pos <> 0 THEN
42 l_start_pos := l_start_pos + length(p_token||'=');
43 l_parameter := substr(p_parameter_string,
44 l_start_pos,
45 instr(p_parameter_string||' ',
46 l_delimiter,l_start_pos)
47 - l_start_pos);
48 IF p_segment_number IS NOT NULL THEN
49 l_parameter := ':'||l_parameter||':';
50 l_parameter := substr(l_parameter,
51 instr(l_parameter,':',1,p_segment_number)+1,
52 instr(l_parameter,':',1,p_segment_number+1) -1
53 - instr(l_parameter,':',1,p_segment_number));
54 END IF;
55 END IF;
56 --
57 hr_utility.set_location('Leaving get_parameter',53);
58 RETURN l_parameter;
59 END get_parameter;
60
61 /*-----------------------------------------------------------------------------
62 |Name : GET_ALL_PARAMETERS |
63 |Type : Procedure |
64 |Description: Procedure which returns all the parameters of the archive process|
65 -------------------------------------------------------------------------------*/
66
67
68 PROCEDURE get_all_parameters(
69 p_payroll_action_id IN NUMBER
70 ,p_business_group_id OUT NOCOPY NUMBER
71 ,p_employer_id OUT NOCOPY VARCHAR2
72 ,p_si_provider_id OUT NOCOPY VARCHAR2
73 ,p_nsi_month OUT NOCOPY VARCHAR2
74 ,p_output_media_type OUT NOCOPY VARCHAR2
75 ,p_payroll_id OUT NOCOPY VARCHAR2
76 ,p_withdraw_asg_set_id OUT NOCOPY VARCHAR2
77 ,p_report_type OUT NOCOPY VARCHAR2) IS
78 --
79 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
80
81 SELECT
82 PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'EMPLOYER_ID')
83 ,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'SI_PROVIDER_ID')
84 ,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'NSI_MONTH')
85 ,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'OUTPUT_MEDIA_TYPE')
86 ,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'PAYROLL_ID')
87 ,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'WITHDRAW_ASG_SET_ID')
88 ,business_group_id
89 ,report_type
90 FROM pay_payroll_actions
91 WHERE payroll_action_id = p_payroll_action_id;
92
93 --
94
95 l_proc VARCHAR2(240):= g_package||' get_all_parameters ';
96 --
97 BEGIN
98 --
99 hr_utility.set_location('Entering get_all_parameters',51);
100
101 OPEN csr_parameter_info (p_payroll_action_id);
102 FETCH csr_parameter_info INTO
103 p_employer_id,p_si_provider_id,p_nsi_month
104 ,p_output_media_type,p_payroll_id,p_withdraw_asg_set_id
105 ,p_business_group_id,p_report_type;
106 CLOSE csr_parameter_info;
107 --
108 hr_utility.set_location('Leaving get_all_parameters',54);
109
110 END get_all_parameters;
111
112 /*-------------------------------------------------------------------------------
113 |Name : get_country_name |
114 |Type : Function |
115 |Description : Function to get the country name from FND_TERRITORIES_VL |
116 -------------------------------------------------------------------------------*/
117
118 FUNCTION get_country_name(p_territory_code VARCHAR2) RETURN VARCHAR2 IS
119 CURSOR csr_get_territory_name(p_territory_code VARCHAR2) Is
120 SELECT TERRITORY_SHORT_NAME
121 FROM FND_TERRITORIES_VL
122 WHERE TERRITORY_CODE = p_territory_code;
123
124 l_country FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE;
125 BEGIN
126 OPEN csr_get_territory_name(p_territory_code);
127 FETCH csr_get_territory_name into l_country;
128 CLOSE csr_get_territory_name;
129 RETURN l_country;
130 END;/*-------------------------------------------------------------------------------
131 |Name : Mandatory_Check |
132 |Type : Procedure |
133 |Description : Procedure to check if the specified Mandatory Field is NULL |
134 | if so flag a Error message to the Log File |
135 -------------------------------------------------------------------------------*/
136
137 Procedure Mandatory_Check(p_message_name varchar2,p_field varchar2,p_value varchar2) is
138 v_message_text fnd_new_messages.message_text%TYPE;
139 v_employee_dat VARCHAR2(255);
140 v_label_desc hr_lookups.meaning%TYPE;
141 Begin
142 hr_utility.set_location('Checking Field '||p_field,425);
143 If p_value is null then
144 v_label_desc := hr_general.decode_lookup('HR_NL_REPORT_LABELS', p_field);
145 v_employee_dat :=RPAD(SUBSTR(g_assignment_number,1,20),20)
146 ||' '||RPAD(SUBSTR(g_full_name,1,25),25)
147 ||' '||RPAD(SUBSTR(v_label_desc,1,25),25)
148 ||' '||RPAD(SUBSTR(g_error_flag,1,15),15);
149 hr_utility.set_message(801,p_message_name);
150 v_message_text :=SUBSTR(fnd_message.get,1,70);
151 g_error_count := NVL(g_error_count,0) +1;
152 FND_FILE.PUT_LINE(FND_FILE.LOG, v_employee_dat||' '||v_message_text);
153 end if;
154
155 end;
156 /*-------------------------------------------------------------------------------
157 |Name : RANGE_CODE |
158 |Type : Procedure |
159 |Description : This procedure returns a sql string to select a range of |
160 | assignments eligible for archival |
161 -------------------------------------------------------------------------------*/
162
163 Procedure RANGE_CODE (pactid IN NUMBER
164 ,sqlstr OUT NOCOPY VARCHAR2) is
165
166 --Fetch Org Address - Sender (BG) /Employer
167 CURSOR csr_get_address(p_organization_id NUMBER) IS
168 SELECT hr_org.NAME name
169 ,hr_loc.style style
170 ,hr_loc.loc_information14 House_Num
171 ,hr_loc.loc_information15 House_Num_Add
172 ,hr_loc.region_1 street_name
173 ,pay_nl_general.get_postal_code(hr_loc.postal_code) postal_code
174 ,hr_general.decode_lookup('HR_NL_CITY', hr_loc.town_or_city) city
175 FROM hr_organization_units hr_org
176 ,hr_locations hr_loc
177 WHERE hr_org.organization_id = p_organization_id
178 AND hr_org.location_id = hr_loc.location_id (+);
179 v_csr_get_address csr_get_address%ROWTYPE;
180 v_csr_get_address1 csr_get_address%ROWTYPE;
181
182 v_House_Number VARCHAR2(15);
183
184 --Fetch Rep Name /Reg Name from HR Org -> Dutch SI Provider
185 CURSOR csr_get_sender_det(p_organization_id NUMBER
186 ,p_si_provider_id NUMBER
187 ,p_nsi_process_date date) IS
188 SELECT
189 DECODE(hoi.org_information3,'ZFW',1,'ZW',2,'WW',3,'WAO',4,'AMI',5,6) sort_order
190 ,hoi.org_information8 Sender_Rep_Name
191 ,hoi.org_information9 Sender_Reg_Number
192 ,hoi.org_information10 Employer_Rep_Name
193 ,hoi.org_information11 Employer_Reg_Number
194 FROM hr_organization_information hoi
195 WHERE hoi.org_information_context='NL_SIP'
196 AND hoi.organization_id = p_organization_id
197 AND hoi.org_information4 = p_si_provider_id
198 AND hoi.org_information3 IN('ZFW','ZW','WW','WAO','AMI')
199 AND p_nsi_process_date between
200 FND_DATE.CANONICAL_TO_DATE(hoi.org_information1) and
201 nvl(FND_DATE.CANONICAL_TO_DATE(hoi.org_information2),hr_general.end_of_time)
202 ORDER BY ORG_INFORMATION7,DECODE(hoi.org_information3,'ZFW',1,'ZW',2,'WW',3,'WAO',4,'AMI',5,6) ;
203 v_csr_get_sender_det csr_get_sender_det%ROWTYPE;
204 --
205 --
206 l_business_group_id NUMBER;
207 l_employer_id NUMBER;
208 l_si_provider_id NUMBER;
209 l_nsi_month VARCHAR2(10);
210 l_output_media_type VARCHAR2(10);
211 l_payroll_id NUMBER;
212 l_withdraw_asg_set_id NUMBER;
213 l_report_type pay_payroll_actions.report_type%TYPE;
214 --
215
216 --
217 l_Sender_Reg_Number VARCHAR2(15);
218 l_NSI_Process_Date DATE;
219 l_Sender_Report_Name VARCHAR2(22);
220 l_Sender_Address VARCHAR2(150);
221 l_Employer_Name VARCHAR2(22);
222 l_Employer_Address VARCHAR2(150);
223
224 l_Employer_Reg_Number VARCHAR2(15);
225
226 l_action_info_id NUMBER;
227 l_ovn NUMBER;
228 l_effective_date DATE;
229
230 v_log_header VARCHAR2(255);
231
232 Begin
233
234 --
235 --hr_utility.trace_on(NULL,'NL_NSI');
236 hr_utility.set_location('Entering Range Code',50);
237 --
238 --
239
240 g_error_count := 0;
241 g_payroll_action_id:=pactid;
242
243 PAY_NL_NSI_PROCESS.get_all_parameters
244 (p_payroll_action_id => pactid
245 ,p_business_group_id => l_business_group_id
246 ,p_employer_id => l_employer_id
247 ,p_si_provider_id => l_si_provider_id
248 ,p_nsi_month => l_nsi_month
249 ,p_output_media_type => l_output_media_type
250 ,p_payroll_id => l_payroll_id
251 ,p_withdraw_asg_set_id => l_withdraw_asg_set_id
252 ,p_report_type => l_report_type
253 );
254
255 hr_utility.set_location('g_payroll_action_id = ' || g_payroll_action_id,55);
256 hr_utility.set_location('NSI Archive p_payroll_action_id '||pactid,425);
257 hr_utility.set_location('NSI Archive l_business_group_id '||l_business_group_id,425);
258 hr_utility.set_location('NSI Archive l_employer_id '||l_employer_id,425);
259 hr_utility.set_location('NSI Archive l_si_provider_id '||l_si_provider_id,425);
260 hr_utility.set_location('NSI Archive l_nsi_month '||l_nsi_month,425);
261 hr_utility.set_location('NSI Archive l_payroll_id '||l_payroll_id,425);
262 hr_utility.set_location('NSI Archive l_withdraw_asg_set_id '||l_withdraw_asg_set_id,425);
263 hr_utility.set_location('NSI Archive l_NSI_Process_Date '||l_NSI_Process_Date,425);
264 hr_utility.set_location('NSI Archive l_report_type '||l_report_type,425);
265 --
266
267 --Determine the Process Dates
268 l_NSI_Process_Date := LAST_DAY(TO_DATE('01'||l_nsi_month,'DDMMYYYY'));
269
270
271 --Determine Sender Reporting Name/Reg Num/ER Rep Name/ER Reg Num
272 OPEN csr_get_sender_det( l_employer_id,l_si_provider_id,l_NSI_Process_Date);
273 FETCH csr_get_sender_det INTO v_csr_get_sender_det;
274 IF csr_get_sender_det%FOUND THEN
275 l_Sender_Reg_Number := LPAD(v_csr_get_sender_det.Sender_Reg_Number,15,'0') ;
276 l_Sender_Report_Name:= RPAD(v_csr_get_sender_det.Sender_Rep_Name,22) ;
277 l_Employer_Name:= RPAD(v_csr_get_sender_det.Employer_Rep_Name,22) ;
278 l_Employer_Reg_Number := LPAD(v_csr_get_sender_det.Employer_Reg_Number,15,'0');
279 END IF;
280 CLOSE csr_get_sender_det;
281 l_Sender_Report_Name := UPPER(RPAD(NVL(l_Sender_Report_Name,' '),22));
282 l_Employer_Name := UPPER(RPAD(NVL(l_Employer_Name,' '),22));
283 --hr_utility.set_location('l_Sender_Reg_Number :'||l_Sender_Reg_Number,425);
284 --hr_utility.set_location('l_Sender_Report_Name :'||l_Sender_Report_Name,425);
285 --hr_utility.set_location('l_Employer_Name :'||l_Employer_Name,425);
286 --hr_utility.set_location('l_Employer_Reg_Number :'||l_Employer_Reg_Number,425);
287
288
289 --Determine Sender Address (Business Group - Location)
290 OPEN csr_get_address(l_business_group_id);
291 FETCH csr_get_address INTO v_csr_get_address;
292 CLOSE csr_get_address;
293
294 v_House_Number := v_csr_get_address.House_Num;
295
296 IF v_csr_get_address.House_Num_Add IS NOT NULL
297 AND v_House_Number IS NOT NULL THEN
298 v_House_Number := v_House_Number||' ';
299 END IF;
300 v_House_Number := v_House_Number||v_csr_get_address.House_Num_Add;
301
302 IF v_House_Number IS NOT NULL THEN
303 l_Sender_Address := SUBSTR(v_csr_get_address.street_name,1,19-NVL(LENGTH(v_House_Number),0));
304 l_Sender_Address := l_Sender_Address ||' ';
305 ELSE
306 l_Sender_Address := SUBSTR(v_csr_get_address.street_name,1,20);
307 END IF;
308 l_Sender_Address := l_Sender_Address ||v_House_Number;
309
310 IF l_Sender_Address IS NOT NULL THEN
311 l_Sender_Address := RPAD(SUBSTR(l_Sender_Address,1,20),20);
312 ELSE
313 l_Sender_Address := RPAD(' ',20);
314 END IF;
315
316 IF v_csr_get_address.Postal_Code||v_csr_get_address.City IS NOT NULL THEN
317 l_Sender_Address := l_Sender_Address||
318 RPAD(SUBSTR(NVL(v_csr_get_address.Postal_Code,' '),1,6),6)||
319 RPAD(SUBSTR(NVL(v_csr_get_address.City,' '),1,14),14);
320 ELSE
321 l_Sender_Address := l_Sender_Address||RPAD(' ',20);
322 END IF;
323 l_Sender_Address :=UPPER(l_Sender_Address);
324 --hr_utility.set_location('l_Sender_Address :'||l_Sender_Address,425);
325
326 v_House_Number := NULL;
327 --v_csr_get_address:= NULL;
328
329 --Determine Employer Address
330 OPEN csr_get_address(l_employer_id);
331 FETCH csr_get_address INTO v_csr_get_address1;
332 CLOSE csr_get_address;
333 v_House_Number := v_csr_get_address1.House_Num;
334
335 IF v_csr_get_address1.House_Num_Add IS NOT NULL
336 AND v_House_Number IS NOT NULL THEN
337 v_House_Number := v_House_Number||' ';
338 END IF;
339 v_House_Number := v_House_Number||v_csr_get_address1.House_Num_Add;
340
341 IF v_House_Number IS NOT NULL THEN
342 l_Employer_Address := SUBSTR(v_csr_get_address1.street_name,1,19-NVL(LENGTH(v_House_Number),0));
343 l_Employer_Address := l_Employer_Address ||' ';
344 ELSE
345 l_Employer_Address := SUBSTR(v_csr_get_address1.street_name,1,20);
346 END IF;
347 l_Employer_Address := l_Employer_Address ||v_House_Number;
348
349 IF l_Employer_Address IS NOT NULL THEN
350 l_Employer_Address := RPAD(SUBSTR(l_Employer_Address,1,20),20);
351 ELSE
352 l_Employer_Address := RPAD(' ',20);
353 END IF;
354
355 IF v_csr_get_address.Postal_Code||v_csr_get_address1.City IS NOT NULL THEN
356 l_Employer_Address := l_Employer_Address||
357 RPAD(SUBSTR(NVL(v_csr_get_address1.Postal_Code,' '),1,6),6)||
358 RPAD(SUBSTR(NVL(v_csr_get_address1.City,' '),1,14),14);
359 ELSE
360 l_Employer_Address := l_Employer_Address||RPAD(' ',20);
361 END IF;
362 l_Employer_Address :=UPPER(l_Employer_Address);
363 --hr_utility.set_location('l_Employer_Address :'||l_Employer_Address,425);
364
365
366
367 -- Validate Checks for Employer NSI Data
368 -- Check For Mandatory Fields
369 -- Check for Sender Registration Number,If NULL Raise Error
370 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_SENDER_REG_NUM',l_Sender_Reg_Number);
371
372 -- Cadans /Cadans Zorg Specific Validation
373 -- Sender Address Mandatory for Cadans Files
374 -- Employer Postal Code/City Mandatory
375 IF (l_report_type='NL_CAD_NSI_ARCHIVE' OR l_report_type='NL_CADZ_NSI_ARCHIVE') THEN
376 --Sender Address details
377 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_SENDER_ADDR',SUBSTR(v_csr_get_address.street_name||v_csr_get_address.House_Num||v_csr_get_address.House_Num_Add,1,20));
378 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_POSTAL_CODE',SUBSTR(v_csr_get_address.Postal_Code,1,6));
379 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_CITY',v_csr_get_address.City);
380
381 --Employer Address details
382 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_POSTAL_CODE',SUBSTR(v_csr_get_address1.Postal_Code,1,6));
383 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_CITY',v_csr_get_address1.City);
384 END IF;
385
386 -- Check for Employer Rep Name or Employer Reg Number,If NULL Raise Error
387 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_EMPLOYER_NAME',l_Employer_Name);
388 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_ER_REG_NUMBER',l_Employer_Reg_Number);
389 Mandatory_Check('PAY_NL_ER_NSI_REQUIRED_FIELD','NL_ER_ADDR',SUBSTR(v_csr_get_address1.street_name||v_csr_get_address1.House_Num||v_csr_get_address1.House_Num_Add,1,20));
390
391 IF g_error_count=0 THEN
392 pay_action_information_api.create_action_information (
393 p_action_information_id => l_action_info_id
394 ,p_action_context_id => pactid
395 ,p_action_context_type => 'PA'
396 ,p_object_version_number => l_ovn
397 ,p_effective_date => l_NSI_Process_Date
398 ,p_source_id => NULL
399 ,p_source_text => NULL
400 ,p_action_information_category => 'NL NSI EMPLOYER DETAILS'
401 ,p_action_information1 => l_si_provider_id
402 ,p_action_information2 => l_employer_id
403 ,p_action_information3 => l_Sender_Reg_Number
404 ,p_action_information4 => l_Sender_Report_Name
405 ,p_action_information5 => l_Sender_Address
406 ,p_action_information6 => l_Employer_Name
407 ,p_action_information7 => l_Employer_Address
408 ,p_action_information8 => l_Employer_Reg_Number
409 );
410 sqlstr := 'SELECT DISTINCT person_id
411 FROM per_people_f ppf
412 ,pay_payroll_actions ppa
413 WHERE ppa.payroll_action_id = :payroll_action_id
414 AND ppa.business_group_id = ppf.business_group_id
415 ORDER BY ppf.person_id';
416 ELSE
417 sqlstr := 'SELECT DISTINCT person_id
418 FROM per_people_f ppf
419 ,pay_payroll_actions ppa
420 WHERE ppa.payroll_action_id = :payroll_action_id
421 AND 1 = 2
422 AND ppa.business_group_id = ppf.business_group_id
423 ORDER BY ppf.person_id';
424 END IF;
425 --
426 --
427 --Write to Log File
428 v_log_header := RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','NL_ASSIGNMENT_NUMBER'),1,20),20)
429 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FULL_NAME'),1,25),25)
430 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','FIELD_NAME'),1,25),25)
431 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR_TYPE'),1,15),15)
432 ||' '||RPAD(SUBSTR(hr_general.decode_lookup('HR_NL_REPORT_LABELS','MESSAGE'),1,70),70);
433 Fnd_file.put_line(FND_FILE.LOG,v_log_header);
434
435 hr_utility.set_location('Leaving Range Code',350);
436
437 EXCEPTION
438
439 WHEN OTHERS THEN
440 hr_utility.set_location('SQLERRM '||SQLERRM,350);
441
442 -- Return cursor that selects no rows
443 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
444 END RANGE_CODE;
445
446 /*-------------------------------------------------------------------------------
447 |Name : ARCHIVE_NL_NSI_EE_DETAILS |
448 |Type : Procedure |
449 |Description : Procedure archives the NL NSI EE DETAILS Context , |
450 -------------------------------------------------------------------------------*/
451 Procedure ARCHIVE_NL_NSI_EE_DETAILS(p_business_group_id IN NUMBER
452 ,p_report_type IN VARCHAR2
453 ,p_payroll_action_id IN NUMBER
454 ,p_assignment_action_id IN NUMBER
455 ,p_chunk in number
456 ,p_Starter_Flag IN VARCHAR2
457 ,p_person_id IN NUMBER
458 ,p_assignment_id IN NUMBER
459 ,p_employer_id IN NUMBER
460 ,p_si_provider_id IN NUMBER
461 ,p_cur_nsi_process_date IN DATE
462 ,p_lst_nsi_process_date IN DATE) IS
463
464
465 --
466 -- Cursor to retrieve All Date Track Changes to Employee Assignment Records.
467 -- Between the Last NSI Process Date and the Current NSI Run Process Date.
468 -- Also has a Union for Select that selects the Date Track Changes
469 -- in the Cadans Extra Info Change- Occupation Code
470 -- If the Run is for Cadans- NL_CAD_NSI_ARCHIVE OR NL_CADZ_NSI_ARCHIVE
471 CURSOR csr_ee_asg_si_info(lp_report_type varchar2
472 ,lp_person_id number,lp_assignment_id number
473 ,lp_si_provider number
474 ,lp_last_nsi_process_date date,lp_nsi_process_date date) IS
475 SELECT
476 paa.person_id ,paa.assignment_id
477 ,paa.organization_id,paa.effective_start_date ,paa.effective_end_date
478 ,FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1) si_eff_start_date
479 ,NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time) si_eff_end_date
480 ,hr_general.start_of_time cad_eff_start_date
481 ,hr_general.end_of_time cad_eff_end_date
482 ,scl_flx.SEGMENT2 Employment_Type
483 ,scl_flx.SEGMENT3 Employment_SubType
484 ,scl_flx.SEGMENT6 Work_Pattern
485 ,paa.assignment_status_type_id
486 ,asg_stat.per_system_status
487 ,null occupation_code
488 ,null other_occupation_name
489 ,null collective_agreement_code
490 ,null insurance_abp
491 ,null risk_fund
492 FROM
493 per_all_assignments_f paa
494 ,hr_soft_coding_keyflex scl_flx
495 ,per_assignment_status_types asg_stat
496 ,per_assignment_extra_info ee_si
497 WHERE paa.person_id = lp_person_id
498 and paa.assignment_id = lp_assignment_id
499 and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
500 and paa.assignment_status_type_id = asg_stat.assignment_status_type_id
501 AND paa.assignment_id = ee_si.assignment_id
502 AND ee_si.aei_information_category='NL_SII'
503 AND ee_si.aei_information3 IN('ZFW','ZW','WW','WAO','AMI')
504 AND paa.effective_start_date
505 BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1)
506 AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time)
507 and paa.effective_start_date BETWEEN lp_last_nsi_process_date AND lp_nsi_process_date
508 AND (lp_report_type = 'NL_GAK_NSI_ARCHIVE' ) /* Gak Asg Date Track Changes*/
509 UNION
510 SELECT
511 paa.person_id,paa.assignment_id
512 ,paa.organization_id,paa.effective_start_date,paa.effective_end_date
513 ,FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1) si_eff_start_date
514 ,NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time) si_eff_end_date
515 ,hr_general.start_of_time cad_eff_start_date
516 ,hr_general.end_of_time cad_eff_end_date
517 ,scl_flx.SEGMENT2 Employment_Type
518 ,scl_flx.SEGMENT3 Employment_SubType
519 ,scl_flx.SEGMENT6 Work_Pattern
520 ,paa.assignment_status_type_id
521 ,asg_stat.per_system_status
522 ,null occupation_code
523 ,null other_occupation_name
524 ,null collective_agreement_code
525 ,null insurance_abp
526 ,null risk_fund
527 FROM
528 per_all_assignments_f paa
529 ,hr_soft_coding_keyflex scl_flx
530 ,per_assignment_status_types asg_stat
531 ,per_assignment_extra_info ee_si
532 WHERE paa.person_id = lp_person_id
533 AND paa.assignment_id = lp_assignment_id
534 AND scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
535 AND paa.assignment_status_type_id = asg_stat.assignment_status_type_id
536 AND paa.assignment_id = ee_si.assignment_id
537 AND ee_si.aei_information_category='NL_SII'
538 AND ee_si.aei_information3 IN('ZFW','ZW','WW','WAO','AMI')
539 AND lp_nsi_process_date BETWEEN paa.effective_start_date AND paa.effective_end_date
540 AND FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1)
541 BETWEEN lp_last_nsi_process_date AND lp_nsi_process_date /* NL_SII-Code Insurance EIT Date Track Changes*/
542 UNION
543 SELECT
544 paa.person_id,paa.assignment_id
545 ,paa.organization_id,paa.effective_start_date,paa.effective_end_date
546 ,FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1) si_eff_start_date
547 ,NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time) si_eff_end_date
548 ,FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1) cad_eff_start_date
549 ,NVL(FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information2),hr_general.end_of_time) cad_eff_end_date
550 ,scl_flx.SEGMENT2 Employment_Type
551 ,scl_flx.SEGMENT3 Employment_SubType
552 ,scl_flx.SEGMENT6 Work_Pattern
553 ,paa.assignment_status_type_id
554 ,asg_stat.per_system_status
555 ,ee_cadans.aei_information3 occupation_code
556 ,ee_cadans.aei_information4 other_occupation_name
557 ,ee_cadans.aei_information5 collective_agreement_code
558 ,ee_cadans.aei_information6 insurance_abp
559 ,ee_cadans.aei_information7 risk_fund
560 FROM
561 per_all_assignments_f paa
562 ,hr_soft_coding_keyflex scl_flx
563 ,per_assignment_status_types asg_stat
564 ,per_assignment_extra_info ee_cadans
565 ,per_assignment_extra_info ee_si
566 WHERE paa.person_id = lp_person_id
567 and paa.assignment_id = lp_assignment_id
568 and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
569 and paa.assignment_status_type_id = asg_stat.assignment_status_type_id
570 AND paa.assignment_id = ee_si.assignment_id
571 AND ee_si.aei_information_category='NL_SII'
572 AND ee_si.aei_information3 IN('ZFW','ZW','WW','WAO','AMI')
573 AND paa.effective_start_date
574 BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1)
575 AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time)
576 AND paa.assignment_id = ee_cadans.assignment_id(+)
577 AND ee_cadans.aei_information_category='NL_CADANS_INFO'
578 and paa.effective_start_date BETWEEN lp_last_nsi_process_date AND lp_nsi_process_date
579 AND paa.effective_start_date
580 BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1) AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information2),hr_general.end_of_time)
581 AND (lp_report_type = 'NL_CAD_NSI_ARCHIVE' OR lp_report_type='NL_CADZ_NSI_ARCHIVE' ) /* Cadans Asg Date Track Changes*/
582 UNION
583 SELECT
584 paa.person_id,paa.assignment_id
585 ,paa.organization_id,paa.effective_start_date,paa.effective_end_date
586 ,FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1) si_eff_start_date
587 ,NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time) si_eff_end_date
588 ,FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1) cad_eff_start_date
589 ,NVL(FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information2),hr_general.end_of_time) cad_eff_end_date
590 ,scl_flx.SEGMENT2 Employment_Type
591 ,scl_flx.SEGMENT3 Employment_SubType
592 ,scl_flx.SEGMENT6 Work_Pattern
593 ,paa.assignment_status_type_id
594 ,asg_stat.per_system_status
595 ,ee_cadans.aei_information3 occupation_code
596 ,ee_cadans.aei_information4 other_occupation_name
597 ,ee_cadans.aei_information5 collective_agreement_code
598 ,ee_cadans.aei_information6 insurance_abp
599 ,ee_cadans.aei_information7 risk_fund
600 FROM
601 per_all_assignments_f paa
602 ,hr_soft_coding_keyflex scl_flx
603 ,per_assignment_status_types asg_stat
604 ,per_assignment_extra_info ee_cadans
605 ,per_assignment_extra_info ee_si
606 WHERE paa.person_id = lp_person_id
607 AND paa.assignment_id = lp_assignment_id
608 AND scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
609 AND (lp_report_type = 'NL_CAD_NSI_ARCHIVE' OR lp_report_type='NL_CADZ_NSI_ARCHIVE' )
610 AND paa.assignment_status_type_id = asg_stat.assignment_status_type_id
611 AND paa.assignment_id = ee_si.assignment_id
612 AND ee_si.aei_information_category='NL_SII'
613 AND ee_si.aei_information3 IN('ZFW','ZW','WW','WAO','AMI')
614 AND paa.effective_start_date
615 BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1)
616 AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time)
617 AND FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1)
618 BETWEEN paa.effective_start_date AND paa.effective_end_date
619 AND paa.assignment_id = ee_cadans.assignment_id
620 AND ee_cadans.aei_information_category='NL_CADANS_INFO'
621 AND FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1)
622 BETWEEN lp_last_nsi_process_date AND lp_nsi_process_date /* Cadans EIT Date Track Changes*/
623 order by 1,2,4,5;
624 csr_ee_asg_nsi csr_ee_asg_si_info%ROWTYPE;
625
626 l_notify_nsi_date DATE;
627
628 --Select the Most recently sent NSI Record for comparison
629 --to determine if a NSI Record needs to be generated or not.
630 CURSOR csr_ee_nsi_record (lp_person_id number
631 ,lp_assignment_id number
632 ,lp_employer_id number
633 ,lp_si_provider number
634 ,lp_nsi_notify_date date
635 ,lp_lst_nsi_process_date date) IS
636 SELECT
637 nl_ee_nsi.action_information1 Employer_ID
638 ,nl_ee_nsi.action_information2 Person_ID
639 ,nl_ee_nsi.action_information3 Assignment_ID
640 ,nl_ee_nsi.action_information4 SI_Provider_ID
641 ,nl_ee_nsi.action_information5 Hire_Date
642 ,nl_ee_nsi.action_information6 Actual_Termination_Date
643 ,nl_ee_nsi.action_information7 Assignment_Start_Date
644 ,nl_ee_nsi.action_information8 Assignment_End_Date
645 ,nl_ee_nsi.action_information9 Notification_a
646 ,nl_ee_nsi.action_information10 Notification_a_date
647 ,nl_ee_nsi.action_information11 Notification_b
648 ,nl_ee_nsi.action_information12 Notification_b_date
649 ,nl_ee_nsi.action_information13 Code_Insurance
650 ,nl_ee_nsi.action_information14 Code_Insurance_Basis
651 ,nl_ee_nsi.action_information15 Code_Occupation
652 ,nl_ee_nsi.action_information16 Work_Pattern
653 ,nl_ee_nsi.action_information17 St_Date_Lab_Rel
654 FROM PAY_ACTION_INFORMATION nl_ee_nsi
655 where nl_ee_nsi.action_context_type='AAP'
656 and nl_ee_nsi.action_information_category = 'NL NSI EMPLOYEE DETAILS'
657 and nl_ee_nsi.action_information1 = lp_employer_id
658 and nl_ee_nsi.action_information2 = lp_person_id
659 and nl_ee_nsi.action_information3 = lp_assignment_id
660 and nl_ee_nsi.action_information4 = lp_si_provider
661 and (nl_ee_nsi.action_information10 = TO_CHAR(lp_nsi_notify_date,'DDMMYYYY')
662 OR nl_ee_nsi.action_information12 = TO_CHAR(lp_nsi_notify_date,'DDMMYYYY'))
663 and nl_ee_nsi.effective_date <= lp_lst_nsi_process_date
664 and nl_ee_nsi.action_information9 <>'4'
665 ORDER BY nl_ee_nsi.effective_date DESC;
666 v_csr_ee_nsi_record csr_ee_nsi_record%ROWTYPE;
667
668 --Select the Last NSI Record Notify Dates for comparison
669 --to determine if a NSI Record needs to be generated or not
670 --in the Current Run.
671
672 CURSOR csr_ee_lat_nsi (lp_employer_id NUMBER
673 ,lp_si_provider_id NUMBER
674 ,lp_person_id NUMBER
675 ,lp_assignment_id NUMBER
676 ,lp_nsi_process_date DATE) IS
677 SELECT
678 min(TO_DATE(nl_ee_nsi1.action_information10,'DDMMYYYY')) notify_a_date,
679 min(TO_DATE(nl_ee_nsi1.action_information12,'DDMMYYYY')) notify_b_date
680 FROM PAY_ACTION_INFORMATION nl_ee_nsi1
681 where nl_ee_nsi1.action_context_type='AAP'
682 and nl_ee_nsi1.action_information_category = 'NL NSI EMPLOYEE DETAILS'
683 and nl_ee_nsi1.action_information1 = lp_employer_id
684 and nl_ee_nsi1.action_information2 = lp_person_id
685 and nl_ee_nsi1.action_information3 = lp_assignment_id
686 and nl_ee_nsi1.effective_date = lp_nsi_process_date
687 and nl_ee_nsi1.action_information9 <>'4';
688 v_csr_ee_lat_nsi csr_ee_lat_nsi%ROWTYPE;
689
690 --Select EE Assignment Effective Dates
691 CURSOR csr_ee_asg_dates (lp_assignment_id NUMBER) IS
692 SELECT TO_CHAR(min(asg.effective_start_date),'DDMMYYYY') asg_start_date
693 ,TO_CHAR(max(asg.effective_end_date),'DDMMYYYY') asg_end_date
694 from per_all_assignments_f asg,
695 per_assignment_status_types past
696 where asg.assignment_id = lp_assignment_id
697 and past.per_system_status = 'ACTIVE_ASSIGN'
698 and asg.assignment_status_type_id = past.assignment_status_type_id;
699
700 --Select EE Termination Details
701 CURSOR csr_ee_term (lp_person_id NUMBER) IS
702 SELECT leaving_reason,Actual_Termination_Date
703 from per_periods_of_service pos
704 where pos.person_id = lp_person_id;
705 v_csr_ee_term csr_ee_term%ROWTYPE;
706
707 --Select EE Assignment Status
708 CURSOR csr_ee_asg_status (lp_assignment_id NUMBER,l_effective_date date) IS
709 SELECT asg.effective_start_date,asg.effective_end_date,past.per_system_status
710 from per_all_assignments_f asg,
711 per_assignment_status_types past
712 where asg.assignment_id = lp_assignment_id
713 and asg.assignment_status_type_id = past.assignment_status_type_id
714 and l_effective_date BETWEEN asg.effective_start_date and asg.effective_end_date;
715 v_csr_ee_asg_status csr_ee_asg_status%ROWTYPE;
716
717 --Select EE SI Info - NL_SII
718 CURSOR csr_ee_siinfo (lp_assignment_id NUMBER
719 ,lp_organization_id NUMBER
720 ,lp_effective_date DATE) IS
721 SELECT
722 PAY_NL_SI_PKG.Get_Si_Status(lp_assignment_id,lp_effective_date,'ZW') zw_si_status
723 ,PAY_NL_SI_PKG.Get_Si_Status(lp_assignment_id,lp_effective_date,'WW') ww_si_status
724 ,PAY_NL_SI_PKG.Get_Si_Status(lp_assignment_id,lp_effective_date,'WAO') wao_si_status
725 ,PAY_NL_SI_PKG.Get_Si_Status(lp_assignment_id,lp_effective_date,'ZFW') zfw_si_status
726 FROM dual ;
727 v_csr_ee_siinfo csr_ee_siinfo%ROWTYPE;
728
729 -- Cursor to Determine the SI Provider for the Various SI Types
730 -- as on Effective Date
731 CURSOR csr_ee_si_prov_info(lp_organization_id number
732 ,lp_assignment_id number
733 ,lp_effective_date date
734 ,lp_zw_si_status Varchar2
735 ,lp_ww_si_status Varchar2
736 ,lp_wao_si_status Varchar2
737 ,lp_zfw_si_status Varchar2) IS
738 SELECT
739 DECODE(lp_zw_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'ZW',lp_assignment_id)) zw_provider
740 ,DECODE(lp_ww_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'WEWE',lp_assignment_id)) ww_provider
741 ,DECODE(lp_wao_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'WAOD',lp_assignment_id)) wao_provider
742 ,DECODE(lp_zfw_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'ZFW',lp_assignment_id)) zfw_provider
743 ,DECODE(lp_zw_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'ZW',lp_assignment_id)) zw_er_org_id
744 ,DECODE(lp_ww_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'WEWE',lp_assignment_id)) ww_er_org_id
745 ,DECODE(lp_wao_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'WAOD',lp_assignment_id)) wao_er_org_id
746 ,DECODE(lp_zfw_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'ZFW',lp_assignment_id)) zfw_er_org_id
747 FROM DUAL;
748 v_csr_ee_si_prov_info csr_ee_si_prov_info%ROWTYPE;
749
750
751 --Select EE Info : Name/SOFI Number
752 CURSOR csr_ee_info (lp_person_id NUMBER
753 ,lp_assignment_id NUMBER
754 ,lp_effective_date DATE) IS
755 SELECT
756 ee_info.Full_Name
757 ,replace(replace(ee_info.Last_Name,'.',''),',','') Last_Name
758 ,replace(replace(ee_info.Previous_Last_Name,'.',''),',','') Previous_Last_Name
759 ,replace(replace(ee_info.First_Name,'.',''),',','') First_Name
760 ,replace(replace(replace(ee_info.per_information1,'.',''),',',''),' ','') Initials
761 ,replace(replace(replace(ee_info.pre_name_adjunct,'.',''),',',''),' ','') Prefix
762 ,ee_info.National_Identifier SOFI_Number
763 ,ee_info.sex
764 ,ee_info.Marital_Status
765 ,ee_info.Date_Of_Birth
766 ,ee_info.Employee_Number
767 ,paa.Assignment_Number
768 FROM per_all_people_f ee_info
769 ,per_all_assignments_f paa
770 WHERE ee_info.person_id = lp_person_id
771 AND ee_info.person_id = paa.person_id
772 AND paa.assignment_id = lp_assignment_id
773 AND lp_effective_date BETWEEN ee_info.Effective_Start_Date and ee_info.Effective_End_Date
774 AND lp_effective_date BETWEEN paa.Effective_Start_Date and paa.Effective_End_Date;
775 v_csr_ee_info csr_ee_info%ROWTYPE;
776
777 --Select EE Spouse Info : Name
778 CURSOR csr_ee_sp_info (lp_person_id NUMBER
779 ,lp_effective_date DATE) IS
780 SELECT
781 ee_info.Last_Name
782 ,ee_info.Previous_Last_Name
783 ,replace(replace(replace(ee_info.pre_name_adjunct,'.',''),',',''),' ','') Prefix
784 FROM per_all_people_f ee_info,
785 per_contact_relationships con
786 WHERE ee_info.person_id = con.contact_person_id
787 AND con.person_id= lp_person_id
788 AND con.contact_type='S'
789 AND lp_effective_date BETWEEN ee_info.Effective_Start_Date and ee_info.Effective_End_Date;
790 v_csr_ee_sp_info csr_ee_sp_info%ROWTYPE;
791
792 --Select EE Address
793 CURSOR csr_ee_addr (lp_person_id NUMBER
794 ,lp_address_type VARCHAR2
795 ,lp_effective_date DATE) IS
796 SELECT ee_addr.style style
797 ,ee_addr.add_information13 House_Num
798 ,ee_addr.add_information14 House_Num_Add
799 ,ee_addr.region_1 street_name
800 ,pay_nl_general.get_postal_code(ee_addr.postal_code) postal_code
801 ,hr_general.decode_lookup('HR_NL_CITY', ee_addr.town_or_city) city
802 ,ee_addr.country country
803 FROM per_addresses ee_addr
804 WHERE ee_addr.person_id = lp_person_id
805 AND lp_effective_date between date_from and NVL(date_to,hr_general.end_of_time)
806 AND ((ee_addr.primary_flag ='Y' AND lp_address_type IS NULL)
807 OR (lp_address_type IS NOT NULL AND ee_addr.address_type = lp_address_type));
808 v_csr_ee_addr csr_ee_addr%ROWTYPE;
809 v_csr_ee_addr1 csr_ee_addr%ROWTYPE;
810
811
812 --Select EE Gak Info
813 CURSOR csr_ee_gak (lp_person_id NUMBER
814 ,lp_assignment_id NUMBER
815 ,lp_process_date DATE) IS
816 SELECT
817 hr_general.decode_lookup('NL_GAK_OCCUPATION_DESCRIPTION',ee_gak.aei_information3) Occupation_Desc
818 ,ee_gak.aei_information4 Weekly_4_Exp_SI_Days
819 FROM per_assignment_extra_info ee_gak
820 WHERE ee_gak.assignment_id = lp_assignment_id
821 AND ee_gak.aei_information_category='NL_GAK_INFO'
822 AND lp_process_date BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_gak.aei_information1)
823 AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_gak.aei_information2),hr_general.END_OF_TIME) ;
824 v_csr_ee_gak csr_ee_gak%ROWTYPE;
825
826 --Select EE Cadans Info
827 CURSOR csr_ee_cadans (lp_person_id NUMBER
828 ,lp_assignment_id NUMBER
829 ,lp_process_date DATE) IS
830 SELECT
831 ee_cadans.aei_information3 occupation_code
832 ,ee_cadans.aei_information4 other_occupation_name
833 ,ee_cadans.aei_information5 collective_agreement_code
834 ,ee_cadans.aei_information6 insurance_abp
835 ,ee_cadans.aei_information7 risk_fund
836 FROM per_assignment_extra_info ee_cadans
837 WHERE ee_cadans.assignment_id = lp_assignment_id
838 AND ee_cadans.aei_information_category='NL_CADANS_INFO'
839 AND lp_process_date BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1)
840 AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information2),hr_general.END_OF_TIME) ;
841 v_csr_ee_cadans csr_ee_cadans%ROWTYPE;
842
843 l_last_nsi_min_notify_date DATE;
844
845
846 l_action_info_id pay_action_information.action_information_id%TYPE;
847 l_ovn pay_action_information.object_version_number%TYPE;
848
849 l_effective_date DATE;
850 l_Employer_ID hr_organization_units.organization_id%TYPE;
851 l_Person_ID per_all_assignments_f.person_id%TYPE;
852 l_Assignment_ID per_all_assignments_f.assignment_id%TYPE;
853 l_Prev_Person_ID per_all_assignments_f.person_id%TYPE;
854 l_Prev_Assignment_ID per_all_assignments_f.assignment_id%TYPE;
855
856 l_NSI_Process_Date varchar2(8);
857 l_Hire_Date DATE;
858 l_Asg_Effective_Start_Date DATE;
859 l_Actual_Termination_Date DATE;
860 l_Assignment_Start_Date VARCHAR2(8);
861 l_Assignment_End_Date VARCHAR2(8);
862 l_Notification_a VARCHAR2(1);
863 l_Notification_a_date VARCHAR2(8);
864 l_Notification_b VARCHAR2(1);
865 l_Notification_b_date VARCHAR2(8);
866 l_Code_Insurance VARCHAR2(4);
867 l_Code_Ins_Basis VARCHAR2(15);
868 l_Code_Occupation VARCHAR2(3);
869 l_Work_Pattern VARCHAR2(3);
870 l_St_Date_Lab_Rel VARCHAR2(8);
871 l_SOFI_Number VARCHAR2(9);
872 l_Employee_Name VARCHAR2(150);
873 l_Employee_Primary_Add VARCHAR2(150);
874 l_Country_Name FND_TERRITORIES_VL.TERRITORY_SHORT_NAME%TYPE;
875 l_Employee_Pop_Reg_Add VARCHAR2(150);
876 l_Gak_Rep_Info VARCHAR2(150);
877 l_Cadans_Rep_Info VARCHAR2(150);
878 l_Employee_Details VARCHAR2(150);
879
880 l_Prev_Notification_a VARCHAR2(1);
881 l_Prev_Notification_a_date VARCHAR2(8);
882 l_Prev_Notification_b VARCHAR2(1);
883 l_Prev_Notification_b_date VARCHAR2(8);
884 l_Prev_Code_Insurance VARCHAR2(4);
885 l_Prev_Code_Ins_Basis VARCHAR2(15);
886 l_Prev_Code_Occupation VARCHAR2(3);
887 l_Prev_Work_Pattern VARCHAR2(3);
888 l_Prev_St_Date_Lab_Rel VARCHAR2(8);
889
890 l_Create_NSI BOOLEAN;--Flag to Control Creation of NSI Record
891 l_Multiple_NSI BOOLEAN;--Flag to Control Creation of Multiple NSI Record
892 l_Asg_NSI_Rec_Count NUMBER;
893 l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
894 l_utab_row_value VARCHAR2(100);
895 l_utab_cib_value VARCHAR2(100);
896 l_Starter_Flag VARCHAR2(50);
897
898 BEGIN
899 hr_utility.set_location('Entering Archive NL NSI EE Details',350);
900 hr_utility.set_location('Payroll Action Id '||p_payroll_action_id,350);
901
902 --Intialize Variables
903 l_Asg_NSI_Rec_Count := 0;
904 l_asg_act_id := p_assignment_action_id;
905 l_Employer_ID :=p_employer_id;
906 l_Starter_Flag := P_Starter_Flag;
907 IF l_asg_act_id IS NULL THEN
908 l_Prev_Person_ID :=NULL;
909 l_Prev_Assignment_ID :=NULL;
910 ELSE
911 l_Prev_Person_ID :=p_person_id;
912 l_Prev_Assignment_ID :=p_assignment_id;
913 END IF;
914 g_error_count := 0;
915
916
917 hr_utility.set_location('p_person_id '||p_person_id||' p_assignment_id '||p_assignment_id,350);
918 hr_utility.set_location(' p_si_provider_id '||p_si_provider_id
919 ||' p_report_type'||p_report_type,350);
920 hr_utility.set_location(' p_cur_nsi_process_date '||p_cur_nsi_process_date
921 ||' p_lst_nsi_process_date '||p_lst_nsi_process_date
922 ||' l_Starter_Flag '||l_Starter_Flag,350);
923 IF l_Starter_Flag='EXISTING' THEN
924 OPEN csr_ee_lat_nsi(p_employer_id,p_si_provider_id
925 ,p_person_id,p_assignment_id,p_lst_nsi_process_date );
926 FETCH csr_ee_lat_nsi INTO v_csr_ee_lat_nsi;
927 CLOSE csr_ee_lat_nsi;
928 hr_utility.set_location('v_csr_ee_lat_nsi.notify_a_date '||v_csr_ee_lat_nsi.notify_a_date,350);
929 hr_utility.set_location('v_csr_ee_lat_nsi.notify_b_date '||v_csr_ee_lat_nsi.notify_b_date,350);
930
931 l_last_nsi_min_notify_date := p_lst_nsi_process_date;
932
933 SELECT LEAST(NVL(v_csr_ee_lat_nsi.notify_a_date,v_csr_ee_lat_nsi.notify_b_date),
934 NVL(v_csr_ee_lat_nsi.notify_b_date,v_csr_ee_lat_nsi.notify_a_date))
935 INTO l_last_nsi_min_notify_date FROM DUAL;
936 END IF;
937 hr_utility.set_location(' l_last_nsi_min_notify_date '||l_last_nsi_min_notify_date,350);
938
939 --Determine EE Termination Details
940 OPEN csr_ee_term(p_person_id);
941 FETCH csr_ee_term INTO v_csr_ee_term;
942 CLOSE csr_ee_term;
943
944 OPEN csr_ee_asg_si_info(p_report_type
945 ,p_person_id,p_assignment_id
946 ,p_si_provider_id
947 ,NVL(l_last_nsi_min_notify_date,hr_general.START_OF_TIME),p_cur_nsi_process_date );
948 LOOP
949 FETCH csr_ee_asg_si_info INTO csr_ee_asg_nsi;
950 EXIT WHEN (csr_ee_asg_si_info%NOTFOUND OR g_error_count>0);
951 hr_utility.set_location(' Date Track ee_nsi.Eff_St_Date '||csr_ee_asg_nsi.Effective_Start_Date,350);
952
953 l_Asg_Effective_Start_Date := GREATEST(csr_ee_asg_nsi.Effective_Start_Date
954 ,csr_ee_asg_nsi.si_eff_start_date
955 ,csr_ee_asg_nsi.cad_eff_start_date);
956 hr_utility.set_location('l_Asg_Effective_Start_Date :'||l_Asg_Effective_Start_Date,450);
957
958
959
960 --Intialize all NSI Record Variables
961 l_Create_NSI := FALSE;
962 l_Multiple_NSI := FALSE;
963 l_NSI_Process_Date :=TO_CHAR(p_cur_nsi_process_date,'DDMMYYYY');
964 l_Notification_a := '0';
965 l_Notification_a_date := NULL;
966 l_Notification_b := '0';
967 l_Notification_b_date := NULL;
968 l_Code_Insurance := NULL;
969 l_Code_Ins_Basis := NULL;
970 l_Code_Occupation := NULL;
971 l_Work_Pattern := NULL;
972 l_St_Date_Lab_Rel := NULL;
973
974 l_utab_cib_value := NULL;
975 l_SOFI_Number := NULL;
976 l_Employee_Name := NULL;
977 l_Employee_Primary_Add := NULL;
978 l_Country_Name := NULL;
979 l_Employee_Pop_Reg_Add := NULL;
980 l_Gak_Rep_Info := NULL;
981 l_Cadans_Rep_Info := NULL;
982 l_Employee_Details := NULL;
983
984 l_Person_ID :=csr_ee_asg_nsi.person_id;
985 l_Assignment_ID :=csr_ee_asg_nsi.assignment_id;
986 l_Assignment_Start_Date := NULL;
987 l_Assignment_End_Date := NULL;
988 v_csr_ee_siinfo := NULL;
989 v_csr_ee_si_prov_info := NULL;
990 v_csr_ee_asg_status := NULL;
991 v_csr_ee_gak := NULL;
992 v_csr_ee_cadans := NULL;
993 v_csr_ee_nsi_record := NULL;
994
995 OPEN csr_ee_asg_dates(l_assignment_id);
996 FETCH csr_ee_asg_dates INTO l_Assignment_Start_Date,l_Assignment_End_Date;
997 CLOSE csr_ee_asg_dates;
998
999
1000 --Determine the l_Code_Insurance from EE NL_SII Data - Social Insurance Eligibilities
1001 --Fetch Employee SI Info
1002 OPEN csr_ee_siinfo(l_assignment_id,csr_ee_asg_nsi.organization_id,l_Asg_Effective_Start_Date);
1003 FETCH csr_ee_siinfo INTO v_csr_ee_siinfo;
1004 IF csr_ee_siinfo%FOUND THEN
1005
1006 --Fetch EE SI Provider Info
1007 OPEN csr_ee_si_prov_info(csr_ee_asg_nsi.organization_id,l_assignment_id,l_Asg_Effective_Start_Date
1008 ,v_csr_ee_siinfo.zw_si_status,v_csr_ee_siinfo.ww_si_status
1009 ,v_csr_ee_siinfo.wao_si_status,v_csr_ee_siinfo.zfw_si_status);
1010 FETCH csr_ee_si_prov_info INTO v_csr_ee_si_prov_info;
1011 CLOSE csr_ee_si_prov_info;
1012
1013 hr_utility.set_location(' ZW -'||v_csr_ee_si_prov_info.zw_provider||' WW -'||v_csr_ee_si_prov_info.ww_provider
1014 ||' WAO -'||v_csr_ee_si_prov_info.wao_provider||' ZFW - '||v_csr_ee_si_prov_info.zfw_provider,450);
1015
1016
1017 --Determine Code Insurance
1018 IF v_csr_ee_si_prov_info.zw_provider=p_si_provider_id
1019 AND v_csr_ee_si_prov_info.zw_er_org_id=l_employer_id
1020 AND v_csr_ee_siinfo.zw_si_status IS NOT NULL THEN
1021 l_Code_Insurance := '1';
1022 ELSE
1023 l_Code_Insurance := '2';
1024 END IF;
1025
1026 IF v_csr_ee_si_prov_info.ww_provider=p_si_provider_id
1027 AND v_csr_ee_si_prov_info.ww_er_org_id=l_employer_id
1028 AND v_csr_ee_siinfo.ww_si_status IS NOT NULL THEN
1029 l_Code_Insurance := l_Code_Insurance||'1';
1030 ELSE
1031 l_Code_Insurance := l_Code_Insurance||'2';
1032 END IF;
1033
1034 IF v_csr_ee_si_prov_info.wao_provider=p_si_provider_id
1035 AND v_csr_ee_si_prov_info.wao_er_org_id=l_employer_id
1036 AND v_csr_ee_siinfo.wao_si_status IS NOT NULL THEN
1037 l_Code_Insurance := l_Code_Insurance||'1';
1038 ELSE
1039 l_Code_Insurance := l_Code_Insurance||'2';
1040 END IF;
1041
1042 IF v_csr_ee_si_prov_info.zfw_provider=p_si_provider_id
1043 AND v_csr_ee_si_prov_info.zfw_er_org_id=l_employer_id
1044 AND v_csr_ee_siinfo.zfw_si_status IS NOT NULL
1045 AND v_csr_ee_siinfo.zfw_si_status <>'4' THEN
1046 l_Code_Insurance := l_Code_Insurance||'1';
1047 ELSE
1048 l_Code_Insurance := l_Code_Insurance||'2';
1049 END IF;
1050 END IF;
1051 CLOSE csr_ee_siinfo;
1052
1053 -- Determine the l_Code_Ins_Basis from EE Type and EE Sub Type Information
1054 --
1055 l_utab_row_value := csr_ee_asg_nsi.Employment_Type||csr_ee_asg_nsi.Employment_SubType;
1056 BEGIN
1057 hr_utility.set_location('Tab Value l_utab_row_value :'||l_utab_row_value,450);
1058 l_utab_cib_value:= hruserdt.get_table_value(p_business_group_id,'NL_EMP_SUB_TYPE_CIB_KOA','GAK_CADANS_CIB',l_utab_row_value,p_cur_nsi_process_date);
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061 hr_utility.set_location('Tab Value Error '||SQLCODE||' : '||SQLERRM(SQLCODE),450);
1062 l_utab_cib_value:=null;
1063 END;
1064
1065 hr_utility.set_location('CIB : l_utab_cib_value '||l_utab_cib_value,450);
1066 FOR i IN 18..31
1067 LOOP
1068 IF i IN(21,24,30) THEN
1069 l_Code_Ins_Basis:=l_Code_Ins_Basis||'0';
1070 ELSIF i=l_utab_cib_value THEN
1071 l_Code_Ins_Basis:=l_Code_Ins_Basis||'1';
1072 ELSE
1073 l_Code_Ins_Basis:=l_Code_Ins_Basis||'0';
1074 END IF;
1075 END LOOP;
1076
1077 IF p_report_type='NL_GAK_NSI_ARCHIVE' THEN
1078 IF csr_ee_asg_nsi.per_system_status ='SUSP_ASSIGN' THEN
1079 l_Code_Ins_Basis := l_Code_Ins_Basis||'1';
1080 ELSE
1081 l_Code_Ins_Basis := l_Code_Ins_Basis||'0';
1082 END IF;
1083 END IF;
1084
1085 -- Determine the Work Pattern
1086 l_Work_Pattern := SUBSTR(NVL(csr_ee_asg_nsi.Work_Pattern,'R'),1,1);
1087
1088 IF P_Report_Type='NL_GAK_NSI_ARCHIVE' THEN
1089 IF l_Work_Pattern='R' THEN
1090 l_Work_Pattern:='0';
1091 ELSE
1092 l_Work_Pattern:='1';
1093 END IF;
1094 --Fetch Employee GAK Rep Info
1095 OPEN csr_ee_gak(l_Person_ID,l_assignment_id,l_Asg_Effective_Start_Date);
1096 FETCH csr_ee_gak INTO v_csr_ee_gak;
1097 CLOSE csr_ee_gak;
1098 l_Gak_Rep_Info := RPAD(SUBSTR(v_csr_ee_gak.Occupation_Desc||' ',1,12),12);
1099 l_Gak_Rep_Info := l_Gak_Rep_Info||LPAD(SUBSTR(v_csr_ee_gak.Weekly_4_Exp_SI_Days,1,5),3,'0');
1100 END IF;
1101 l_Gak_Rep_Info := UPPER(l_Gak_Rep_Info); /* Convert to Upper Case*/
1102 hr_utility.set_location('l_Gak_Rep_Info : '||l_Gak_Rep_Info,450);
1103
1104 IF (P_Report_Type='NL_CAD_NSI_ARCHIVE' OR P_Report_Type='NL_CADZ_NSI_ARCHIVE' ) THEN
1105
1106 IF l_Work_Pattern='R' THEN
1107 l_Work_Pattern:='1';
1108 ELSE
1109 l_Work_Pattern:='0';
1110 END IF;
1111 -- Determine the Code Occupation
1112 --Fetch Employee Cadans Rep Info
1113 OPEN csr_ee_cadans(l_Person_ID,l_assignment_id,l_Asg_Effective_Start_Date);
1114 FETCH csr_ee_cadans INTO v_csr_ee_cadans;
1115 CLOSE csr_ee_cadans;
1116 l_Code_Occupation := SUBSTR(v_csr_ee_cadans.occupation_code,1,3);
1117 IF l_Code_Occupation = '129' THEN
1118 l_Cadans_Rep_Info := RPAD(SUBSTR(NVL(v_csr_ee_cadans.other_occupation_name,' '),1,9),9,' ');
1119 ELSE
1120 l_Cadans_Rep_Info := RPAD('0',9,'0');
1121 END IF;
1122 l_Cadans_Rep_Info := l_Cadans_Rep_Info||LPAD(SUBSTR(v_csr_ee_cadans.collective_agreement_code||' ',1,4),4,'0');
1123 l_Cadans_Rep_Info := l_Cadans_Rep_Info||LPAD(SUBSTR(v_csr_ee_cadans.insurance_abp||' ',1,1),1,'0');
1124 l_Cadans_Rep_Info := l_Cadans_Rep_Info||LPAD(SUBSTR(v_csr_ee_cadans.risk_fund||' ',1,1),1,'2');
1125 END IF;
1126 l_Cadans_Rep_Info := UPPER(l_Cadans_Rep_Info); /* Convert to Upper Case*/
1127 hr_utility.set_location('l_Cadans_Rep_Info : '||l_Cadans_Rep_Info,450);
1128
1129 l_St_Date_Lab_Rel := l_Assignment_Start_Date;
1130
1131 hr_utility.set_location('l_Code_Insurance '||l_Code_Insurance,450);
1132 hr_utility.set_location('l_Code_Ins_Basis '||l_Code_Ins_Basis,450);
1133 hr_utility.set_location('l_Code_Occupation '||l_Code_Occupation,450);
1134 hr_utility.set_location('l_Work_Pattern '||l_Work_Pattern,450);
1135 hr_utility.set_location('l_St_Date_Lab_Rel '||l_St_Date_Lab_Rel,450);
1136
1137
1138 IF l_Code_Insurance <>'2222' AND length(l_Code_Insurance)=4
1139 AND csr_ee_asg_nsi.PER_SYSTEM_STATUS <>'TERM_ASSIGN' THEN
1140
1141 --For a Starter the l_Create_NSI is intially set to TRUE
1142 --and Subsequent Date Track Changes ,we would need to chk if a NSI Record is needed or not
1143
1144 --Check the Status of the Next Date Track Assignment Record
1145
1146 OPEN csr_ee_asg_status(l_assignment_id,(csr_ee_asg_nsi.effective_end_date+1));
1147 FETCH csr_ee_asg_status INTO v_csr_ee_asg_status;
1148 CLOSE csr_ee_asg_status;
1149
1150 --Check if the Assignment was terminated
1151 --If so Set Notification B Type (Notif_b) to '1'
1152 IF v_csr_ee_asg_status.PER_SYSTEM_STATUS='TERM_ASSIGN' THEN
1153 l_Notification_b :='1';
1154 l_Notification_b_date :=TO_CHAR(csr_ee_asg_nsi.effective_end_date,'DDMMYYYY');
1155 ELSE
1156 hr_utility.set_location('Actual_Termination_Date '||v_csr_ee_term.Actual_Termination_Date,450);
1157 hr_utility.set_location('effective_end_date '||v_csr_ee_asg_status.effective_end_date,450);
1158 IF v_csr_ee_term.Actual_Termination_Date IS NOT NULL
1159 AND v_csr_ee_term.Actual_Termination_Date <= p_cur_nsi_process_date
1160 AND v_csr_ee_term.Actual_Termination_Date >= csr_ee_asg_nsi.effective_start_date
1161 AND v_csr_ee_term.Actual_Termination_Date <= csr_ee_asg_nsi.effective_end_date THEN
1162 IF v_csr_ee_term.Leaving_Reason ='D' THEN
1163 l_Notification_b :='2';
1164 l_Notification_b_date :=TO_CHAR(csr_ee_asg_nsi.effective_end_date,'DDMMYYYY');
1165 ELSIF v_csr_ee_term.Leaving_Reason IS NOT NULL THEN
1166 l_Notification_b :='1';
1167 l_Notification_b_date :=TO_CHAR(csr_ee_asg_nsi.effective_end_date,'DDMMYYYY');
1168 ELSE
1169 l_Notification_b :='0';
1170 l_Notification_b_date :=TO_CHAR(csr_ee_asg_nsi.effective_end_date,'DDMMYYYY');
1171 END IF;
1172 END IF;
1173 END IF;
1174
1175 IF l_Starter_Flag='EXISTING' THEN
1176 hr_utility.set_location('Fetching NSI Record as on l_Asg_Effective_Start_Date'||l_Asg_Effective_Start_Date,450);
1177
1178 OPEN csr_ee_nsi_record(p_person_id,p_assignment_id
1179 ,p_employer_id,p_si_provider_id,l_Asg_Effective_Start_Date,p_lst_nsi_process_date);
1180 FETCH csr_ee_nsi_record INTO v_csr_ee_nsi_record;
1181 IF csr_ee_nsi_record%FOUND THEN
1182 hr_utility.set_location('v_csr_ee_nsi_record.Notification_a '||v_csr_ee_nsi_record.Notification_a,450);
1183 hr_utility.set_location('v_csr_ee_nsi_record.Notification_a_date '||v_csr_ee_nsi_record.Notification_a_date,450);
1184 hr_utility.set_location('v_csr_ee_nsi_record.Notification_b '||v_csr_ee_nsi_record.Notification_b,450);
1185 hr_utility.set_location('v_csr_ee_nsi_record.Notification_b_date '||v_csr_ee_nsi_record.Notification_b_date,450);
1186 hr_utility.set_location('v_csr_ee_nsi_record.Code_Insurance '||v_csr_ee_nsi_record.Code_Insurance,450);
1187 hr_utility.set_location('v_csr_ee_nsi_record.Code_Insurance_Basis '||v_csr_ee_nsi_record.Code_Insurance_Basis,450);
1188 hr_utility.set_location('v_csr_ee_nsi_record.Code_Occupation '||v_csr_ee_nsi_record.Code_Occupation,450);
1189 hr_utility.set_location('v_csr_ee_nsi_record.Work_Pattern '||v_csr_ee_nsi_record.Work_Pattern,450);
1190 hr_utility.set_location('v_csr_ee_nsi_record.St_Date_Lab_Rel '||v_csr_ee_nsi_record.St_Date_Lab_Rel,450);
1191 l_Prev_Notification_a := v_csr_ee_nsi_record.Notification_a;
1192 l_Prev_Notification_a_date := v_csr_ee_nsi_record.Notification_a_date;
1193 l_Prev_Notification_b := v_csr_ee_nsi_record.Notification_b ;
1194 l_Prev_Notification_b_date := v_csr_ee_nsi_record.Notification_b_date;
1195 l_Prev_Code_Insurance := v_csr_ee_nsi_record.Code_Insurance ;
1196 l_Prev_Code_Ins_Basis := v_csr_ee_nsi_record.Code_Insurance_Basis ;
1197 l_Prev_Code_Occupation := v_csr_ee_nsi_record.Code_Occupation ;
1198 l_Prev_Work_Pattern := v_csr_ee_nsi_record.Work_Pattern ;
1199 l_Prev_St_Date_Lab_Rel := v_csr_ee_nsi_record.St_Date_Lab_Rel ;
1200
1201
1202 IF (v_csr_ee_nsi_record.Code_Insurance <> l_Code_Insurance)
1203 OR (SUBSTR(v_csr_ee_nsi_record.Code_Insurance_Basis,1,12) <> SUBSTR(l_Code_Ins_Basis,1,12))
1204 OR (v_csr_ee_nsi_record.Work_Pattern <> l_Work_Pattern)
1205 OR (v_csr_ee_nsi_record.Notification_b <> l_Notification_b)
1206 OR (v_csr_ee_nsi_record.Notification_b_date <> l_Notification_b_date) THEN
1207 l_Create_NSI := TRUE;
1208 END IF;
1209
1210 IF (P_Report_Type='NL_CAD_NSI_ARCHIVE' OR P_Report_Type='NL_CADZ_NSI_ARCHIVE' ) THEN
1211 IF v_csr_ee_nsi_record.Code_Occupation <> l_Code_Occupation THEN
1212 l_Create_NSI := TRUE;
1213 END IF;
1214 END IF;
1215 IF (P_Report_Type='NL_CADZ_NSI_ARCHIVE' ) THEN
1216 IF v_csr_ee_nsi_record.St_Date_Lab_Rel <> l_St_Date_Lab_Rel THEN
1217 l_Create_NSI := TRUE;
1218 END IF;
1219 END IF;
1220 --Set Notification Type (Notif_a) to '3' since it is a Correction to a previous
1221 --sent NSI Record
1222 IF l_Create_NSI=TRUE THEN
1223 l_Notification_a :='3';
1224 l_Notification_a_date :=TO_CHAR(l_Asg_Effective_Start_Date,'DDMMYYYY');
1225 END IF;
1226
1227 IF (l_Notification_b IS NOT NULL AND v_csr_ee_nsi_record.Notification_b <> l_Notification_b)
1228 OR (l_Notification_b_date IS NOT NULL AND v_csr_ee_nsi_record.Notification_b_date <> l_Notification_b_date) THEN
1229 l_Create_NSI := TRUE;
1230 END IF;
1231
1232
1233 END IF;
1234 CLOSE csr_ee_nsi_record;
1235 END IF;
1236
1237 hr_utility.set_location('l_Prev_Code_Insurance '||l_Prev_Code_Insurance,450);
1238 hr_utility.set_location('l_Prev_Code_Ins_Basis '||l_Prev_Code_Ins_Basis,450);
1239 hr_utility.set_location('l_Prev_Code_Occupation '||l_Prev_Code_Occupation,450);
1240 hr_utility.set_location('l_Prev_Work_Pattern '||l_Prev_Work_Pattern,450);
1241 hr_utility.set_location('l_Prev_St_Date_Lab_Rel '||l_Prev_St_Date_Lab_Rel,450);
1242
1243 /* Check if the previous Archived NSI Record is same as the
1244 NSI Data as on the Effective Date*/
1245 IF l_Create_NSI=FALSE AND l_Starter_Flag='EXISTING' THEN
1246 IF (l_Prev_Code_Insurance IS NOT NULL AND l_Prev_Code_Insurance <> l_Code_Insurance)
1247 OR (l_Prev_Code_Ins_Basis IS NOT NULL AND SUBSTR(l_Prev_Code_Ins_Basis,1,12) <> SUBSTR(l_Code_Ins_Basis,1,12))
1248 OR (l_Prev_Work_Pattern IS NOT NULL AND l_Prev_Work_Pattern <> l_Work_Pattern) THEN
1249 l_Create_NSI := TRUE;
1250 END IF;
1251
1252 IF (P_Report_Type='NL_CAD_NSI_ARCHIVE' OR P_Report_Type='NL_CADZ_NSI_ARCHIVE' ) THEN
1253 IF l_Prev_Code_Occupation IS NOT NULL
1254 AND l_Prev_Code_Occupation <> l_Code_Occupation THEN
1255 l_Create_NSI := TRUE;
1256 END IF;
1257 END IF;
1258 IF (P_Report_Type='NL_CADZ_NSI_ARCHIVE' ) THEN
1259 IF l_Prev_St_Date_Lab_Rel IS NOT NULL
1260 AND l_Prev_St_Date_Lab_Rel <> l_St_Date_Lab_Rel THEN
1261 l_Create_NSI := TRUE;
1262 END IF;
1263 END IF;
1264
1265 IF l_Create_NSI=TRUE THEN
1266 --Set Notification Type (Notif_a) to '2' since it is a Change Record
1267 --Any Date Track Changes to the NSI Data
1268 --Notification Type a would be 2
1269
1270 l_Notification_a :='2';
1271 l_Notification_a_date :=TO_CHAR(l_Asg_Effective_Start_Date,'DDMMYYYY');
1272 END IF;
1273 END IF;
1274 IF l_Create_NSI= FALSE AND l_Starter_Flag='STARTER' THEN
1275 --Determine the Notification type
1276 IF l_Asg_NSI_Rec_Count=0 THEN
1277 l_Create_NSI := TRUE;
1278
1279 --Set Notification Type (Notif_a) to '1' since it is a New Record - Starter
1280 l_Notification_a :='1';
1281 l_Notification_a_date :=TO_CHAR(csr_ee_asg_nsi.effective_start_date,'DDMMYYYY');
1282
1283 ELSIF l_Asg_NSI_Rec_Count>0 THEN
1284 --Set Notification Type (Notif_a) to '2' since it is a Change Record
1285 --Any Date Track Changes to the NSI Data for a Starter
1286 --Notification Type a would be 2
1287 l_Notification_a :='2';
1288 l_Notification_a_date :=TO_CHAR(l_Asg_Effective_Start_Date,'DDMMYYYY');
1289
1290 END IF;
1291
1292 IF l_Prev_Code_Insurance <> l_Code_Insurance
1293 OR SUBSTR(l_Prev_Code_Ins_Basis,1,12) <> SUBSTR(l_Code_Ins_Basis,1,12)
1294 OR l_Prev_Work_Pattern <> l_Work_Pattern THEN
1295 l_Create_NSI := TRUE;
1296 END IF;
1297
1298 IF (P_Report_Type='NL_CAD_NSI_ARCHIVE' OR P_Report_Type='NL_CADZ_NSI_ARCHIVE' ) THEN
1299 IF l_Prev_Code_Occupation <> l_Code_Occupation THEN
1300 l_Create_NSI := TRUE;
1301 END IF;
1302 END IF;
1303 IF (P_Report_Type='NL_CADZ_NSI_ARCHIVE' ) THEN
1304 IF l_Prev_St_Date_Lab_Rel <> l_St_Date_Lab_Rel THEN
1305 l_Create_NSI := TRUE;
1306 END IF;
1307 END IF;
1308
1309 END IF;
1310 hr_utility.set_location('l_Notification_a '||l_Notification_a,450);
1311 hr_utility.set_location('l_Notification_a_date '||l_Notification_a_date,450);
1312 hr_utility.set_location('l_Notification_b '||l_Notification_b,450);
1313 hr_utility.set_location('l_Notification_b_date '||l_Notification_b_date,450);
1314
1315 IF l_Create_NSI= TRUE THEN
1316 hr_utility.set_location('Creating NSI EE Record '||p_assignment_action_id,450);
1317
1318 v_csr_ee_info := NULL;
1319 v_csr_ee_sp_info := NULL;
1320 v_csr_ee_addr := NULL;
1321 v_csr_ee_addr1 := NULL;
1322
1323 --Fetch Employee Details
1324 OPEN csr_ee_info(l_Person_ID,l_Assignment_ID,csr_ee_asg_nsi.Effective_Start_Date );
1325 FETCH csr_ee_info INTO v_csr_ee_info;
1326 CLOSE csr_ee_info;
1327
1328 g_assignment_number := v_csr_ee_info.Assignment_Number;
1329 g_full_name := v_csr_ee_info.Full_Name;
1330
1331 --hr_utility.set_location('Previous_Last_Name : '||v_csr_ee_info.Previous_Last_Name,450);
1332 --hr_utility.set_location('Last_Name : '||v_csr_ee_info.Last_Name,450);
1333 --hr_utility.set_location('Prefix : '||v_csr_ee_info.Prefix,450);
1334 --hr_utility.set_location('First_Name : '||v_csr_ee_info.First_Name,450);
1335
1336 l_Employee_Name := RPAD(SUBSTR(NVL(v_csr_ee_info.Previous_Last_Name,v_csr_ee_info.Last_Name),1,49),49);
1337 l_Employee_Name := l_Employee_Name||RPAD(SUBSTR(v_csr_ee_info.Initials||' ',1,5),5);
1338 l_Employee_Name := l_Employee_Name||RPAD(SUBSTR(v_csr_ee_info.Prefix||' ',1,8),8);
1339 l_Employee_Name := l_Employee_Name||RPAD(SUBSTR(v_csr_ee_info.First_Name||' ',1,15),15);
1340 l_Employee_Name := upper(l_Employee_Name); /*Convert to Upper Case */
1341
1342 --Fetch Spouse Employee Details
1343 OPEN csr_ee_sp_info(l_Person_ID,csr_ee_asg_nsi.Effective_Start_Date);
1344 FETCH csr_ee_sp_info INTO v_csr_ee_sp_info;
1345 CLOSE csr_ee_sp_info;
1346
1347 l_Cadans_Rep_Info := RPAD(SUBSTR(NVL(v_csr_ee_sp_info.Previous_Last_Name,v_csr_ee_sp_info.Last_Name)||' ',1,30),30)
1348 ||RPAD(SUBSTR(v_csr_ee_sp_info.Prefix||' ',1,8),8)
1349 ||l_Cadans_Rep_Info;
1350
1351 l_Cadans_Rep_Info := UPPER(l_Cadans_Rep_Info); /*Convert to Upper Case */
1352 --hr_utility.set_location('Spouse Name and Prefix Added l_Cadans_Rep_Info : '||l_Cadans_Rep_Info,450);
1353
1354 --hr_utility.set_location('l_Employee_Name : '||l_Employee_Name,450);
1355
1356 l_SOFI_Number := LPAD(SUBSTR(v_csr_ee_info.SOFI_Number,1,9),9,'0');
1357 --hr_utility.set_location('l_SOFI_Number : '||l_SOFI_Number,450);
1358
1359 IF v_csr_ee_info.Sex='M' THEN
1360 l_Employee_Details:= '1';
1361 ELSIF v_csr_ee_info.Sex='F' THEN
1362 l_Employee_Details:= '2';
1363 END IF;
1364 IF (v_csr_ee_info.Marital_Status='M' OR
1365 v_csr_ee_info.Marital_Status='REG_PART' OR
1366 v_csr_ee_info.Marital_Status='LA') THEN
1367 l_Employee_Details:= l_Employee_Details||'2';
1368 ELSE
1369 l_Employee_Details:= l_Employee_Details||'1';
1370 END IF;
1371 l_Employee_Details:= l_Employee_Details||TO_CHAR(v_csr_ee_info.Date_Of_Birth,'DDMMYYYY');
1372 l_Employee_Details:= l_Employee_Details||RPAD(v_csr_ee_info.Employee_Number,30);/*Employee Num*/
1373 l_Employee_Details:= l_Employee_Details||RPAD(v_csr_ee_info.Assignment_Number,30);/*Asg Num*/
1374
1375 l_Employee_Details := upper(l_Employee_Details); /*Convert to Upper Case */
1376
1377
1378 --hr_utility.set_location('l_Employee_Details-Sex+MaritalStatus+EENum+AsgNum : '||l_Employee_Details,450);
1379
1380 --hr_utility.set_location('l_Person_ID : '||l_Person_ID,450);
1381
1382 --Fetch Employee Address
1383 OPEN csr_ee_addr(l_Person_ID,NULL,csr_ee_asg_nsi.Effective_Start_Date);
1384 FETCH csr_ee_addr INTO v_csr_ee_addr;
1385 CLOSE csr_ee_addr;
1386
1387 l_Employee_Primary_Add := RPAD(SUBSTR(NVL(v_csr_ee_addr.street_name,' '),1,25),25);
1388 IF P_Report_Type='NL_GAK_NSI_ARCHIVE' THEN
1389 l_Employee_Primary_Add := l_Employee_Primary_Add||LPAD(SUBSTR(NVL(v_csr_ee_addr.House_Num,'0'),1,5),5,'0');
1390 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD(SUBSTR(NVL(v_csr_ee_addr.House_Num_Add,' '),1,5),5);
1391 ELSE
1392 IF LENGTH(v_csr_ee_addr.House_Num||v_csr_ee_addr.House_Num_Add)<10 THEN
1393 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD(SUBSTR(v_csr_ee_addr.House_Num||' '||v_csr_ee_addr.House_Num_Add,1,10),10,' ');
1394 ELSE
1395 l_Employee_Primary_Add := l_Employee_Primary_Add||LPAD(SUBSTR(NVL(v_csr_ee_addr.House_Num,'0'),1,5),5,'0');
1396 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD(SUBSTR(NVL(v_csr_ee_addr.House_Num_Add,' '),1,5),5);
1397 END IF;
1398 END IF;
1399 --hr_utility.set_location('l_EE_Prim_Add : '||l_Employee_Primary_Add,450);
1400
1401 IF v_csr_ee_addr.Country ='NL' THEN
1402 l_Employee_Primary_Add := l_Employee_Primary_Add||LPAD(SUBSTR(NVL(v_csr_ee_addr.Postal_Code,'0'),1,4),4,'0');
1403 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD(SUBSTR(NVL(v_csr_ee_addr.Postal_Code,' '),5,2),2);
1404 ELSE
1405 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD('0',4,'0');
1406 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD(' ',2,' ');
1407 END IF;
1408 --hr_utility.set_location('l_EE_Prim_Add : '||l_Employee_Primary_Add,450);
1409
1410 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD(SUBSTR(nvl(v_csr_ee_addr.City,' '),1,20),20);
1411 --hr_utility.set_location('l_EE_Prim_Add +City : '||l_Employee_Primary_Add,450);
1412
1413 l_Country_Name:=get_country_name(v_csr_ee_addr.country);
1414 --hr_utility.set_location('l_Country_Name : '||l_Country_Name,450);
1415 IF v_csr_ee_addr.country='NL' THEN
1416 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD(' ',15);
1417 ELSE
1418 l_Employee_Primary_Add := l_Employee_Primary_Add||RPAD(SUBSTR(l_Country_Name,1,15),15);
1419 END IF;
1420 l_Employee_Primary_Add := upper(l_Employee_Primary_Add);/*Convert to Upper Case */
1421
1422 --hr_utility.set_location('l_Employee_Primary_Add : '||l_Employee_Primary_Add,450);
1423
1424 --Fetch Employee Population Register Address
1425 OPEN csr_ee_addr(l_Person_ID,'PRA',csr_ee_asg_nsi.Effective_Start_Date);
1426 FETCH csr_ee_addr INTO v_csr_ee_addr1;
1427 CLOSE csr_ee_addr;
1428 l_Employee_Pop_Reg_Add := RPAD(SUBSTR(v_csr_ee_addr1.street_name,1,25)||' ',25);
1429 IF P_Report_Type='NL_GAK_NSI_ARCHIVE' THEN
1430 l_Employee_Pop_Reg_Add := l_Employee_Pop_Reg_Add||LPAD(SUBSTR(NVL(v_csr_ee_addr1.House_Num,'0'),1,5),5,'0');
1431 l_Employee_Pop_Reg_Add := l_Employee_Pop_Reg_Add||RPAD(SUBSTR(NVL(v_csr_ee_addr1.House_Num_Add,' '),1,5),5);
1432 l_Employee_Pop_Reg_Add := l_Employee_Pop_Reg_Add||LPAD(SUBSTR(NVL(v_csr_ee_addr1.Postal_Code,'0'),1,4),4,'0');
1433 l_Employee_Pop_Reg_Add := l_Employee_Pop_Reg_Add||RPAD(SUBSTR(NVL(v_csr_ee_addr1.Postal_Code,' '),5,2),2);
1434 ELSE
1435 IF LENGTH(v_csr_ee_addr1.House_Num||v_csr_ee_addr1.House_Num_Add)<10 THEN
1436 l_Employee_Pop_Reg_Add := l_Employee_Pop_Reg_Add||RPAD(SUBSTR(v_csr_ee_addr1.House_Num||' '||v_csr_ee_addr1.House_Num_Add,1,10),10,' ');
1437 ELSE
1438 l_Employee_Pop_Reg_Add := l_Employee_Pop_Reg_Add||RPAD(SUBSTR(v_csr_ee_addr1.House_Num||v_csr_ee_addr1.House_Num_Add,1,10),10,' ');
1439 END IF;
1440 l_Employee_Pop_Reg_Add := l_Employee_Pop_Reg_Add||RPAD(SUBSTR(NVL(v_csr_ee_addr1.Postal_Code,' '),1,6),6,' ');
1441 END IF;
1442 l_Employee_Pop_Reg_Add := l_Employee_Pop_Reg_Add||RPAD(SUBSTR(NVL(v_csr_ee_addr1.City,' '),1,20),20);
1443
1444 l_Employee_Pop_Reg_Add := upper(l_Employee_Pop_Reg_Add);/*Convert to Upper Case */
1445 --hr_utility.set_location('l_Employee_Pop_Reg_Add : '||l_Employee_Pop_Reg_Add,450);
1446
1447 ---------------------------------------------------------------------------------------
1448 -- Validation Checks for Employee NSI Data
1449 ---------------------------------------------------------------------------------------
1450 -- Check For Mandatory Fields
1451
1452 -- Check for Date of Birth
1453 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_DATE_OF_BIRTH',v_csr_ee_info.Date_Of_Birth);
1454
1455 -- Check for Employment Type
1456 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_EMPLOY_TYPE',csr_ee_asg_nsi.Employment_Type);
1457
1458 -- Check for Employment Sub Type
1459 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_EMPLOY_STYPE',csr_ee_asg_nsi.Employment_SubType);
1460
1461 -- Check for Code Insurance Basis
1462 IF csr_ee_asg_nsi.Employment_Type IS NOT NULL
1463 AND csr_ee_asg_nsi.Employment_SubType IS NOT NULL THEN
1464 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_CODE_INSURANCE_BASIS',l_utab_cib_value);
1465 END IF;
1466
1467 -- Check for EE Primary Address,If NULL Raise Error
1468 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_STREET_PRIMARY',v_csr_ee_addr.street_name);
1469 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_HOUSE_NUMBER',v_csr_ee_addr.House_Num);
1470 --Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_ADD_TO_HOUSE_NUMBER',v_csr_ee_addr.House_Num_Add);
1471 --Postal Code is Mandatory only if Country is NL
1472 IF v_csr_ee_addr.country='NL' THEN
1473 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_POSTAL_CODE',SUBSTR(v_csr_ee_addr.Postal_Code,1,4));
1474 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_POSTAL_CODE',SUBSTR(v_csr_ee_addr.Postal_Code,5,2));
1475 END IF;
1476 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_CITY',v_csr_ee_addr.City);
1477
1478 -- Check for EE Population Register Address,If NULL Raise Error
1479 -- Conditional Mandatory
1480 -- If Street Name is entered, Then
1481 -- House Num, House Num Add, Postal Code (Num),Postal Code (Char) is set to mandatory.
1482 IF v_csr_ee_addr1.Street_Name IS NOT NULL THEN
1483 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_HOUSE_NUMBER',v_csr_ee_addr1.House_Num);
1484 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_POSTAL_CODE',SUBSTR(v_csr_ee_addr1.Postal_Code,1,4));
1485 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_POSTAL_CODE',SUBSTR(v_csr_ee_addr1.Postal_Code,5,2));
1486 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_CITY',v_csr_ee_addr1.City);
1487 END IF;
1488
1489 -- Check for SOFI Number,If NULL Raise Error
1490 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_SOFI_NUMBER',v_csr_ee_info.SOFI_Number);
1491
1492 -- Gak Specific Validation
1493 IF (P_Report_Type='NL_GAK_NSI_ARCHIVE') THEN
1494 -- Check Occupation Description
1495 IF l_Code_Ins_Basis='000000000000000' THEN
1496 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_OCC_DESC',v_csr_ee_gak.Occupation_Desc);
1497 END IF;
1498
1499 -- Check 4 Weekly Exp SI Days is Not Null if Work Pattern is set to 1
1500 IF l_Work_Pattern='1' THEN
1501 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_EXPECTED_SI_DAYS',v_csr_ee_gak.Weekly_4_Exp_SI_Days);
1502 END IF;
1503 END IF;
1504
1505
1506
1507 -- Cadans Specific Validation
1508 IF (P_Report_Type='NL_CAD_NSI_ARCHIVE') THEN
1509 --Name Spouse is entered,Check if Prefix Name spouse is entered or not
1510 IF NVL(v_csr_ee_sp_info.Previous_Last_Name,v_csr_ee_sp_info.Last_Name) IS NOT NULL THEN
1511 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_PREFIX_SPOUSE',v_csr_ee_sp_info.Prefix);
1512 END IF;
1513 -- Check for Marital Status
1514 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_MARITAL_STATUS',v_csr_ee_info.Marital_Status);
1515
1516 -- Check Code Occupation
1517 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_OCCUPATION_CODE',v_csr_ee_cadans.occupation_code);
1518 -- Check Occupation Description is Not Null if Occupation Code is set to 129-Others
1519 IF v_csr_ee_cadans.occupation_code='129' THEN
1520 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_OTH_OCC_NAME',v_csr_ee_cadans.other_occupation_name);
1521 END IF;
1522 END IF;
1523
1524 -- Cadans Zorg Specific Validation
1525 IF (P_Report_Type='NL_CADZ_NSI_ARCHIVE' ) THEN
1526 --Name Spouse is entered,Check if Prefix Name spouse is entered or not
1527 IF NVL(v_csr_ee_sp_info.Previous_Last_Name,v_csr_ee_sp_info.Last_Name) IS NOT NULL THEN
1528 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_PREFIX_SPOUSE',v_csr_ee_sp_info.Prefix);
1529 END IF;
1530 -- Check for Marital Status
1531 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_MARITAL_STATUS',v_csr_ee_info.Marital_Status);
1532
1533 -- Check Code Occupation,Collective Agreement Code,Code Insurance ABP, Code Risicofunds is NOT NULL
1534 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_OCCUPATION_CODE',v_csr_ee_cadans.occupation_code);
1535 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_COLLECTIVE_AGREEMENT_CODE',v_csr_ee_cadans.collective_agreement_code);
1536 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_INSURANCE_ABP',v_csr_ee_cadans.insurance_abp);
1537 Mandatory_Check('PAY_NL_ASG_NSI_REQUIRED_FIELD','NL_RISK_FUND',v_csr_ee_cadans.risk_fund);
1538 END IF;
1539
1540 IF g_error_count=0 THEN
1541 -- Create Archive Assignment Action for Assignment
1542 IF l_Prev_Assignment_ID IS NULL
1543 OR l_Prev_Assignment_ID <> l_Assignment_ID
1544 OR l_asg_act_id IS NULL THEN
1545 SELECT pay_assignment_actions_s.NEXTVAL
1546 INTO l_asg_act_id
1547 FROM dual;
1548 --
1549 -- Create the archive assignment action
1550 --
1551 hr_nonrun_asact.insact(l_asg_act_id,l_Assignment_ID, p_payroll_action_id,p_chunk,NULL);
1552
1553 --
1554 END IF;
1555 hr_utility.set_location('NSI Archive Assignment Action Id '||l_asg_act_id,450);
1556
1557 l_Prev_Notification_a := l_Notification_a;
1558 l_Prev_Notification_a_date := l_Notification_a_date;
1559 l_Prev_Notification_b := l_Notification_b ;
1560 l_Prev_Notification_b_date := l_Notification_b_date;
1561 l_Prev_Code_Insurance := l_Code_Insurance ;
1562 l_Prev_Code_Ins_Basis := l_Code_Ins_Basis ;
1563 l_Prev_Code_Occupation := l_Code_Occupation ;
1564 l_Prev_Work_Pattern := l_Work_Pattern ;
1565 l_Prev_St_Date_Lab_Rel := l_St_Date_Lab_Rel ;
1566
1567 -- Notif_a = 2 must not be combined with Notif_b=1
1568 -- Generate Two NSI- All Data Same but the Notif attributes set differently
1569 -- NSI 1 : Notif a = 2, Notif b= NULL
1570 -- NSI 2 : Notif a = NULL Notif b= 1/2
1571 l_Multiple_NSI := FALSE;
1572 IF l_Notification_a='2'
1573 AND ((l_Notification_b='1' OR l_Notification_b='2')
1574 OR (l_Notification_b='0' AND l_Notification_b_date IS NOT NULL)) THEN
1575 l_Notification_b := '0';
1576 l_Notification_b_date := NULL;
1577 l_Multiple_NSI := TRUE;
1578 END IF;
1579
1580 pay_action_information_api.create_action_information (
1581 p_action_information_id => l_action_info_id
1582 ,p_action_context_id => l_asg_act_id
1583 ,p_action_context_type => 'AAP'
1584 ,p_object_version_number => l_ovn
1585 ,p_effective_date => TO_DATE(l_NSI_Process_Date,'DDMMYYYY')
1586 ,p_source_id => NULL
1587 ,p_source_text => NULL
1588 ,p_action_information_category => 'NL NSI EMPLOYEE DETAILS'
1589 ,p_action_information1 => l_Employer_ID
1590 ,p_action_information2 => l_Person_ID
1591 ,p_action_information3 => l_Assignment_ID
1592 ,p_action_information4 => p_si_provider_id
1593 ,p_action_information5 => l_Hire_Date
1594 ,p_action_information6 => l_Actual_Termination_Date
1595 ,p_action_information7 => l_Assignment_Start_Date
1596 ,p_action_information8 => l_Assignment_End_Date
1597 ,p_action_information9 => l_Notification_a
1598 ,p_action_information10 => l_Notification_a_date
1599 ,p_action_information11 => l_Notification_b
1600 ,p_action_information12 => l_Notification_b_date
1601 ,p_action_information13 => l_Code_Insurance
1602 ,p_action_information14 => l_Code_Ins_Basis
1603 ,p_action_information15 => l_Code_Occupation
1604 ,p_action_information16 => l_Work_Pattern
1605 ,p_action_information17 => l_St_Date_Lab_Rel
1606 ,p_action_information18 => l_SOFI_Number
1607 ,p_action_information19 => l_Employee_Name
1608 ,p_action_information20 => l_Employee_Primary_Add
1609 ,p_action_information21 => l_Employee_Pop_Reg_Add
1610 ,p_action_information22 => l_Gak_Rep_Info
1611 ,p_action_information23 => l_Cadans_Rep_Info
1612 ,p_action_information24 => l_Employee_Details );
1613 hr_utility.set_location('NSI Archive l_action_info_id'||l_action_info_id,450);
1614
1615 --Create Multiple NSI records with Same Data expect for NSI Notify attributes.
1616 IF l_Multiple_NSI THEN
1617 l_Notification_a := '0';
1618 l_Notification_a_date := NULL;
1619 l_Notification_b := l_Prev_Notification_b;
1620 l_Notification_b_date := l_Prev_Notification_b_Date;
1621 l_Multiple_NSI := FALSE;
1622 pay_action_information_api.create_action_information (
1623 p_action_information_id => l_action_info_id
1624 ,p_action_context_id => l_asg_act_id
1625 ,p_action_context_type => 'AAP'
1626 ,p_object_version_number => l_ovn
1627 ,p_effective_date => TO_DATE(l_NSI_Process_Date,'DDMMYYYY')
1628 ,p_source_id => NULL
1629 ,p_source_text => NULL
1630 ,p_action_information_category => 'NL NSI EMPLOYEE DETAILS'
1631 ,p_action_information1 => l_Employer_ID
1632 ,p_action_information2 => l_Person_ID
1633 ,p_action_information3 => l_Assignment_ID
1634 ,p_action_information4 => p_si_provider_id
1635 ,p_action_information5 => l_Hire_Date
1636 ,p_action_information6 => l_Actual_Termination_Date
1637 ,p_action_information7 => l_Assignment_Start_Date
1638 ,p_action_information8 => l_Assignment_End_Date
1639 ,p_action_information9 => l_Notification_a
1640 ,p_action_information10 => l_Notification_a_date
1641 ,p_action_information11 => l_Notification_b
1642 ,p_action_information12 => l_Notification_b_date
1643 ,p_action_information13 => l_Code_Insurance
1644 ,p_action_information14 => l_Code_Ins_Basis
1645 ,p_action_information15 => l_Code_Occupation
1646 ,p_action_information16 => l_Work_Pattern
1647 ,p_action_information17 => l_St_Date_Lab_Rel
1648 ,p_action_information18 => l_SOFI_Number
1649 ,p_action_information19 => l_Employee_Name
1650 ,p_action_information20 => l_Employee_Primary_Add
1651 ,p_action_information21 => l_Employee_Pop_Reg_Add
1652 ,p_action_information22 => l_Gak_Rep_Info
1653 ,p_action_information23 => l_Cadans_Rep_Info
1654 ,p_action_information24 => l_Employee_Details );
1655 hr_utility.set_location('NSI Archive Multiple l_action_info_id'||l_action_info_id,450);
1656 END IF;
1657 END IF;/* End of g_error_count Check*/
1658 END IF; /* End of l_Create_NSI= TRUE Check*/
1659 END IF;/* End of l_Code_Insurance <>'2222' Check*/
1660
1661 l_Asg_NSI_Rec_Count := l_Asg_NSI_Rec_Count +1;
1662 l_Prev_Person_ID :=l_Person_ID;
1663 l_Prev_Assignment_ID :=l_Assignment_ID;
1664
1665 END LOOP;
1666 hr_utility.set_location('Leaving Archive NL NSI EE Details',350);
1667 End ARCHIVE_NL_NSI_EE_DETAILS;
1668
1669 /*-----------------------------------------------------------------------------
1670 |Name : WITHDRAW_NL_NSI_EE_NOTIF |
1671 |Type : Procedure |
1672 |Description : Procedure Withdraws the Last NL NSI EE DETAILS Context |
1673 -------------------------------------------------------------------------------*/
1674 Procedure WITHDRAW_NL_NSI_EE_NOTIF(p_payroll_action_id IN NUMBER
1675 ,p_chunk IN NUMBER
1676 ,p_person_id IN NUMBER
1677 ,p_assignment_id IN NUMBER
1678 ,p_employer_id IN NUMBER
1679 ,p_si_provider_id IN NUMBER
1680 ,p_cur_nsi_process_date IN DATE
1681 ,p_lst_nsi_process_date IN OUT NOCOPY DATE
1682 ,p_assignment_action_id OUT NOCOPY NUMBER
1683 ,p_Withdraw_New_Hire IN OUT NOCOPY BOOLEAN) IS
1684 --Select the Most recently sent NSI Record for comparison
1685 --to determine if a NSI Record needs to be generated or not.
1686 CURSOR csr_ee_nsi_record (lp_person_id number
1687 ,lp_assignment_id number
1688 ,lp_employer_id number
1689 ,lp_si_provider number
1690 ,lp_nsi_notify_date date) IS
1691 SELECT
1692 nl_ee_nsi.action_information1 Employer_ID
1693 ,nl_ee_nsi.action_information2 Person_ID
1694 ,nl_ee_nsi.action_information3 Assignment_ID
1695 ,nl_ee_nsi.action_information4 SI_Provider_ID
1696 ,nl_ee_nsi.action_information5 Hire_Date
1697 ,nl_ee_nsi.action_information6 Actual_Termination_Date
1698 ,nl_ee_nsi.action_information7 Assignment_Start_Date
1699 ,nl_ee_nsi.action_information8 Assignment_End_Date
1700 ,nl_ee_nsi.action_information9 Notification_a
1701 ,nl_ee_nsi.action_information10 Notification_a_date
1702 ,nl_ee_nsi.action_information11 Notification_b
1703 ,nl_ee_nsi.action_information12 Notification_b_date
1704 ,nl_ee_nsi.action_information13 Code_Insurance
1705 ,nl_ee_nsi.action_information14 Code_Insurance_Basis
1706 ,nl_ee_nsi.action_information15 Code_Occupation
1707 ,nl_ee_nsi.action_information16 Work_Pattern
1708 ,nl_ee_nsi.action_information17 St_Date_Lab_Rel
1709 ,nl_ee_nsi.action_information18 Sofi_Number
1710 ,nl_ee_nsi.action_information19 Employee_Name
1711 ,nl_ee_nsi.action_information20 Employee_Primary_Address
1712 ,nl_ee_nsi.action_information21 Employee_Pop_Reg_Add
1713 ,nl_ee_nsi.action_information22 Gak_Rep_Info
1714 ,nl_ee_nsi.action_information23 Cadans_Rep_Info
1715 ,nl_ee_nsi.action_information24 Employee_Details
1716 FROM PAY_ACTION_INFORMATION nl_ee_nsi
1717 where nl_ee_nsi.action_context_type='AAP'
1718 and nl_ee_nsi.action_information_category = 'NL NSI EMPLOYEE DETAILS'
1719 and nl_ee_nsi.action_information1 = lp_employer_id
1720 and nl_ee_nsi.action_information2 = lp_person_id
1721 and nl_ee_nsi.action_information3 = lp_assignment_id
1722 and nl_ee_nsi.action_information4 = lp_si_provider
1723 and nl_ee_nsi.effective_date = lp_nsi_notify_date
1724 and nl_ee_nsi.action_information9 <>'4'
1725 ORDER BY nl_ee_nsi.effective_date DESC;
1726 v_csr_ee_nsi_record csr_ee_nsi_record%ROWTYPE;
1727
1728 --Once the Previous NSI Record Process Date for assignment
1729 --is withdrawn,Fetch the Previous NSI Effective Date
1730 CURSOR csr_ee_prev_nsi (lp_employer_id NUMBER
1731 ,lp_si_provider_id NUMBER
1732 ,lp_person_id number
1733 ,lp_assignment_id number
1734 ,lp_nsi_process_date date) IS
1735 SELECT
1736 max(effective_date) prev_nsi_process_date
1737 FROM PAY_ACTION_INFORMATION nl_ee_nsi1
1738 where nl_ee_nsi1.action_context_type='AAP'
1739 and nl_ee_nsi1.action_information_category = 'NL NSI EMPLOYEE DETAILS'
1740 and nl_ee_nsi1.action_information1 = lp_employer_id
1741 and nl_ee_nsi1.action_information2 = lp_person_id
1742 and nl_ee_nsi1.action_information3 = lp_assignment_id
1743 and nl_ee_nsi1.action_information4 = lp_si_provider_id
1744 and nl_ee_nsi1.action_information9 <>'4'
1745 and nl_ee_nsi1.effective_date < lp_nsi_process_date;
1746 v_csr_ee_prev_nsi csr_ee_prev_nsi%ROWTYPE;
1747
1748
1749 l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
1750 l_action_info_id pay_action_information.action_information_id%TYPE;
1751 l_ovn pay_action_information.object_version_number%TYPE;
1752 l_withdrawn_action_count NUMBER :=0;
1753 BEGIN
1754 hr_utility.set_location('Entering Withdraw NSI Notification',360);
1755
1756 hr_utility.set_location(' p_person_id '||p_person_id||' p_assignment_id '||p_assignment_id,360);
1757 hr_utility.set_location(' p_employer_id '|| p_employer_id ||' p_si_provider_id '||p_si_provider_id,360);
1758 hr_utility.set_location(' p_lst_nsi_process_date '|| p_lst_nsi_process_date,360);
1759 hr_utility.set_location(' p_cur_nsi_process_date '|| p_cur_nsi_process_date,360);
1760 FOR v_csr_ee_nsi_record IN csr_ee_nsi_record(p_person_id,p_assignment_id
1761 ,p_employer_id,p_si_provider_id,p_lst_nsi_process_date )
1762 LOOP
1763 --Increment the Withdrawn Action count.
1764 l_withdrawn_action_count := l_withdrawn_action_count+1;
1765 --If a Previous NSI is Notif-a=1,then retrun TRUE
1766 IF v_csr_ee_nsi_record.Notification_a='1' THEN
1767 p_Withdraw_New_Hire:= TRUE;
1768 END IF;
1769 -- Create Archive Assignment Action for Assignment
1770 IF l_asg_act_id IS NULL THEN
1771 SELECT pay_assignment_actions_s.NEXTVAL
1772 INTO l_asg_act_id
1773 FROM dual;
1774 --
1775 -- Create the archive assignment action
1776 --
1777 hr_nonrun_asact.insact(l_asg_act_id,p_assignment_id, p_payroll_action_id,p_chunk,NULL);
1778
1779 --
1780 END IF;
1781 hr_utility.set_location('NSI Archive Assignment Action Id '||l_asg_act_id,450);
1782
1783
1784 pay_action_information_api.create_action_information (
1785 p_action_information_id => l_action_info_id
1786 ,p_action_context_id => l_asg_act_id
1787 ,p_action_context_type => 'AAP'
1788 ,p_object_version_number => l_ovn
1789 ,p_effective_date => p_cur_nsi_process_date
1790 ,p_source_id => NULL
1791 ,p_source_text => NULL
1792 ,p_action_information_category => 'NL NSI EMPLOYEE DETAILS'
1793 ,p_action_information1 => p_employer_id
1794 ,p_action_information2 => p_Person_ID
1795 ,p_action_information3 => p_Assignment_ID
1796 ,p_action_information4 => p_si_provider_id
1797 ,p_action_information5 => v_csr_ee_nsi_record.Hire_Date
1798 ,p_action_information6 => v_csr_ee_nsi_record.Actual_Termination_Date
1799 ,p_action_information7 => v_csr_ee_nsi_record.Assignment_Start_Date
1800 ,p_action_information8 => v_csr_ee_nsi_record.Assignment_End_Date
1801 ,p_action_information9 => '4'
1802 ,p_action_information10 => v_csr_ee_nsi_record.Notification_a_date
1803 ,p_action_information11 => '0'
1804 ,p_action_information12 => v_csr_ee_nsi_record.Notification_b_date
1805 ,p_action_information13 => v_csr_ee_nsi_record.Code_Insurance
1806 ,p_action_information14 => v_csr_ee_nsi_record.Code_Insurance_Basis
1807 ,p_action_information15 => v_csr_ee_nsi_record.Code_Occupation
1808 ,p_action_information16 => v_csr_ee_nsi_record.Work_Pattern
1809 ,p_action_information17 => v_csr_ee_nsi_record.St_Date_Lab_Rel
1810 ,p_action_information18 => v_csr_ee_nsi_record.SOFI_Number
1811 ,p_action_information19 => v_csr_ee_nsi_record.Employee_Name
1812 ,p_action_information20 => v_csr_ee_nsi_record.Employee_Primary_Address
1813 ,p_action_information21 => v_csr_ee_nsi_record.Employee_Pop_Reg_Add
1814 ,p_action_information22 => v_csr_ee_nsi_record.Gak_Rep_Info
1815 ,p_action_information23 => v_csr_ee_nsi_record.Cadans_Rep_Info
1816 ,p_action_information24 => v_csr_ee_nsi_record.Employee_Details );
1817 hr_utility.set_location('NSI Archive l_action_info_id'||l_action_info_id,450);
1818
1819 END LOOP;
1820
1821 IF l_withdrawn_action_count >0 then
1822 OPEN csr_ee_prev_nsi(p_employer_id,p_si_provider_id
1823 ,p_Person_ID,p_Assignment_ID,p_lst_nsi_process_date);
1824 FETCH csr_ee_prev_nsi INTO v_csr_ee_prev_nsi;
1825 CLOSE csr_ee_prev_nsi;
1826 END IF;
1827 p_lst_nsi_process_date :=v_csr_ee_prev_nsi.prev_nsi_process_date;
1828 p_assignment_action_id := l_asg_act_id;
1829 hr_utility.set_location('Leaving Withdraw NSI Notification',370);
1830 END WITHDRAW_NL_NSI_EE_NOTIF;
1831 /*-------------------------------------------------------------------------------
1832 |NAME : ASSIGNMENT_ACTION_CODE |
1833 |TYPE : PROCEDURE |
1834 |DESCRIPTION : THIS PROCEDURE FURTHER RESTRICTS THE ASSIGNMENT ID'S RETURNED |
1835 | BY THE RANGE CODE. |
1836 -------------------------------------------------------------------------------*/
1837
1838 Procedure ASSIGNMENT_ACTION_CODE (
1839 p_payroll_action_id in number
1840 ,p_start_person_id in number
1841 ,p_end_person_id in number
1842 ,p_chunk in number) IS
1843
1844 -- Cursor to fetch the Org Hierarachy Version ID
1845 -- as on Process Date
1846 CURSOR csr_org_hierarchy(lp_business_group_id number,lp_process_date date) IS
1847 select
1848 posv.org_structure_version_id
1849 from
1850 per_organization_structures pos,
1851 per_org_structure_versions posv
1852 where pos.organization_structure_id = posv.organization_structure_id
1853 and to_char(pos.organization_structure_id) IN (select org_information1
1854 from hr_organization_information hoi where hoi.org_information_context='NL_BG_INFO'
1855 and hoi.organization_id=lp_business_group_id)
1856 and lp_process_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);
1857 v_csr_org_hierarchy csr_org_hierarchy%ROWTYPE;
1858 -- Cursor to retrieve All Employee SI Records.
1859 -- as on Process Date
1860 CURSOR csr_employee_si_info(lp_business_group_id number
1861 ,lp_employer_id number
1862 ,lp_structure_version_id number
1863 ,lp_start_person_id number
1864 ,lp_end_person_id number
1865 ,lp_payroll_id number
1866 ,lp_si_provider number
1867 ,lp_nsi_process_date date) IS
1868 SELECT
1869 paa.person_id,paa.assignment_id
1870 ,paa.organization_id,paa.effective_start_date,paa.effective_end_date
1871 ,PAY_NL_SI_PKG.Get_Si_Status(paa.assignment_id,lp_nsi_process_date,'ZW') zw_si_status
1872 ,PAY_NL_SI_PKG.Get_Si_Status(paa.assignment_id,lp_nsi_process_date,'WW') ww_si_status
1873 ,PAY_NL_SI_PKG.Get_Si_Status(paa.assignment_id,lp_nsi_process_date,'WAO') wao_si_status
1874 ,PAY_NL_SI_PKG.Get_Si_Status(paa.assignment_id,lp_nsi_process_date,'ZFW') zfw_si_status
1875 ,scl_flx.SEGMENT2 Employment_Type
1876 ,scl_flx.SEGMENT3 Employment_SubType
1877 ,scl_flx.SEGMENT6 Work_Pattern
1878 FROM
1879 per_all_assignments_f paa
1880 ,per_all_people_f pap
1881 ,hr_soft_coding_keyflex scl_flx
1882 WHERE paa.business_group_id = lp_business_group_id
1883 and paa.person_id
1884 BETWEEN lp_start_person_id AND lp_end_person_id
1885 and (paa.payroll_id = lp_payroll_id OR lp_payroll_id IS NULL)
1886 and paa.person_id =pap.person_id
1887 and paa.organization_id IN (
1888 SELECT pose.organization_id_child FROM
1889 per_org_structure_elements pose
1890 CONNECT BY pose.organization_id_parent = prior pose.organization_id_child
1891 AND pose.org_structure_version_id =lp_structure_version_id
1892 START WITH pose.organization_id_parent=lp_employer_id
1893 AND pose.org_structure_version_id =lp_structure_version_id
1894 UNION
1895 SELECT lp_employer_id FROM DUAL
1896 )
1897 and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
1898 and ((lp_nsi_process_date >= paa.effective_start_date AND lp_nsi_process_date <= paa.effective_end_date)
1899 or (lp_nsi_process_date >= pap.effective_start_date AND lp_nsi_process_date <= pap.effective_end_date))
1900 order by paa.person_id,paa.assignment_id,paa.effective_start_date,paa.effective_end_date;
1901
1902
1903 -- Cursor to Determine the SI Provider Info for the Various SI Types
1904 -- as on Process Date
1905 CURSOR csr_ee_si_prov_info(lp_organization_id number
1906 ,lp_assignment_id number
1907 ,lp_nsi_process_date date
1908 ,lp_zw_si_status Varchar2
1909 ,lp_ww_si_status Varchar2
1910 ,lp_wao_si_status Varchar2
1911 ,lp_zfw_si_status Varchar2) IS
1912 SELECT
1913 DECODE(lp_zw_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'ZW',lp_assignment_id)) zw_provider
1914 ,DECODE(lp_ww_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'WEWE',lp_assignment_id)) ww_provider
1915 ,DECODE(lp_wao_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'WAOD',lp_assignment_id)) wao_provider
1916 ,DECODE(lp_zfw_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'ZFW',lp_assignment_id)) zfw_provider
1917 ,DECODE(lp_zw_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'ZW',lp_assignment_id)) zw_er_org_id
1918 ,DECODE(lp_ww_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'WEWE',lp_assignment_id)) ww_er_org_id
1919 ,DECODE(lp_wao_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'WAOD',lp_assignment_id)) wao_er_org_id
1920 ,DECODE(lp_zfw_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'ZFW',lp_assignment_id)) zfw_er_org_id
1921 FROM DUAL;
1922 v_csr_ee_si_prov_info csr_ee_si_prov_info%ROWTYPE;
1923
1924 --Select the Last NSI Record Proces Date for comparison
1925 --to determine if a NSI Record needs to be generated or not
1926 --in the Current Run.
1927 CURSOR csr_ee_lat_nsi (lp_employer_id NUMBER
1928 ,lp_si_provider_id NUMBER
1929 ,lp_person_id number
1930 ,lp_assignment_id number
1931 ,lp_nsi_process_date date) IS
1932 SELECT
1933 max(effective_date) nsi_process_date
1934 FROM PAY_ACTION_INFORMATION nl_ee_nsi1
1935 where nl_ee_nsi1.action_context_type='AAP'
1936 and nl_ee_nsi1.action_information_category = 'NL NSI EMPLOYEE DETAILS'
1937 and nl_ee_nsi1.action_information1 = lp_employer_id
1938 and nl_ee_nsi1.action_information2 = lp_person_id
1939 and nl_ee_nsi1.action_information3 = lp_assignment_id
1940 and nl_ee_nsi1.action_information4 = lp_si_provider_id
1941 and nl_ee_nsi1.action_information9 <>'4'
1942 and nl_ee_nsi1.effective_date <= lp_nsi_process_date;
1943 v_csr_ee_lat_nsi csr_ee_lat_nsi%ROWTYPE;
1944
1945 --Check if the Assignment is in the Withdrwal Assignment Set Id
1946 CURSOR csr_withdraw_asg (
1947 lp_withdraw_asg_set_id number
1948 ,lp_assignment_id number) IS
1949 SELECT
1950 asg_set.assignment_id
1951 FROM hr_assignment_set_amendments asg_set
1952 WHERE asg_set.assignment_set_id=lp_withdraw_asg_set_id
1953 AND asg_set.assignment_id=lp_assignment_id;
1954
1955 v_csr_withdraw_asg csr_withdraw_asg%ROWTYPE;
1956
1957 --
1958 l_business_group_id NUMBER;
1959 l_employer_id NUMBER;
1960 l_si_provider_id NUMBER;
1961 l_nsi_month VARCHAR2(10);
1962 l_output_media_type VARCHAR2(10);
1963 l_payroll_id NUMBER;
1964 l_withdraw_asg_set_id NUMBER;
1965 l_report_type pay_payroll_actions.report_type%TYPE;
1966
1967 --
1968 --
1969 l_prepay_action_id NUMBER;
1970
1971
1972 l_action_info_id pay_action_information.action_information_id%TYPE;
1973 l_ovn pay_action_information.object_version_number%TYPE;
1974 l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
1975
1976 l_Create_NSI BOOLEAN;--Flag to Control Creation of NSI Record
1977 v_Skip_Asg BOOLEAN;--Flag to control if processed asg can be skipped
1978 l_Withdraw_New_Hire BOOLEAN;--Flag returned by Withdraw Asg Proc if a Notif a=1 is withdrawn
1979 l_Prev_Person_ID per_all_assignments_f.person_id%TYPE;
1980 l_Prev_Assignment_ID per_all_assignments_f.person_id%TYPE;
1981 l_Prev_SIProvider_ID per_all_assignments_f.person_id%TYPE;
1982
1983 l_effective_date DATE;
1984 l_Registration_Number hr_organization_information.ORG_INFORMATION6%TYPE;
1985 l_Person_ID per_all_assignments_f.person_id%TYPE;
1986 l_Assignment_ID per_all_assignments_f.person_id%TYPE;
1987 l_NSI_Process_Date DATE;
1988 l_Hire_Date DATE;
1989 l_Actual_Termination_Date DATE;
1990 l_Assignment_Start_Date DATE;
1991 l_Assignment_End_Date DATE;
1992 l_Notification_a VARCHAR2(1);
1993 l_Notification_a_date VARCHAR2(8);
1994 l_Notification_b VARCHAR2(1);
1995 l_Notification_b_date VARCHAR2(8);
1996 l_Code_Insurance VARCHAR2(4);
1997 l_Code_Ins_Basis VARCHAR2(15);
1998 l_Code_Occupation VARCHAR2(3);
1999 l_Work_Pattern VARCHAR2(3);
2000 l_St_Date_Lab_Rel VARCHAR2(1);
2001
2002 BEGIN
2003 --
2004 --hr_utility.trace_on(NULL,'NL_NSI');
2005
2006 hr_utility.set_location('Entering Assignment Action Code',400);
2007 --
2008 g_error_flag := hr_general.decode_lookup('HR_NL_REPORT_LABELS','ERROR');
2009 g_warning_flag := hr_general.decode_lookup('HR_NL_REPORT_LABELS','WARNING');
2010
2011 PAY_NL_NSI_PROCESS.get_all_parameters
2012 (p_payroll_action_id => p_payroll_action_id
2013 ,p_business_group_id => l_business_group_id
2014 ,p_employer_id => l_employer_id
2015 ,p_si_provider_id => l_si_provider_id
2016 ,p_nsi_month => l_nsi_month
2017 ,p_output_media_type => l_output_media_type
2018 ,p_payroll_id => l_payroll_id
2019 ,p_withdraw_asg_set_id => l_withdraw_asg_set_id
2020 ,p_report_type => l_report_type );
2021
2022 --Determine the Process Dates
2023 l_NSI_Process_Date := LAST_DAY(TO_DATE('01'||l_nsi_month,'DDMMYYYY'));
2024
2025 --
2026 OPEN csr_org_hierarchy(l_business_group_id,l_NSI_Process_Date);
2027 FETCH csr_org_hierarchy INTO v_csr_org_hierarchy;
2028 CLOSE csr_org_hierarchy;
2029
2030 l_prepay_action_id := 0;
2031 --
2032
2033 --hr_utility.set_location('NSI Archive p_payroll_action_id '||p_payroll_action_id,425);
2034 --hr_utility.set_location('NSI Archive l_business_group_id '||l_business_group_id,425);
2035 --hr_utility.set_location('NSI Archive l_employer_id '||l_employer_id,425);
2036 --hr_utility.set_location('NSI Archive l_si_provider_id '||l_si_provider_id,425);
2037 --hr_utility.set_location('NSI Archive l_nsi_month '||l_nsi_month,425);
2038 --hr_utility.set_location('NSI Archive l_payroll_id '||l_payroll_id,425);
2039 --hr_utility.set_location('NSI Archive l_withdraw_asg_set_id '||l_withdraw_asg_set_id,425);
2040 --hr_utility.set_location('NSI Archive l_NSI_Process_Date '||l_NSI_Process_Date,425);
2041 --hr_utility.set_location('NSI Archive l_report_type '||l_report_type,425);
2042
2043 l_Prev_Person_ID := NULL;
2044 l_Prev_Assignment_ID := NULL;
2045
2046
2047 FOR csr_ee_rec IN csr_employee_si_info(
2048 l_business_group_id,l_employer_id,v_csr_org_hierarchy.org_structure_version_id
2049 ,p_start_person_id,p_end_person_id
2050 ,l_payroll_id,l_si_provider_id
2051 ,l_NSI_Process_Date)
2052 LOOP
2053 --
2054 --Intialize all NSI Record Variables
2055 l_Code_Insurance := NULL;
2056 l_Person_ID:= csr_ee_rec.Person_ID;
2057 l_Assignment_ID:= csr_ee_rec.Assignment_ID;
2058 hr_utility.set_location('l_Person_ID '||l_Person_ID||' l_Assignment_ID '||l_Assignment_ID,450);
2059
2060 IF l_Prev_Person_ID = l_Person_ID
2061 AND l_Prev_Assignment_ID = l_Assignment_ID THEN
2062 v_Skip_Asg := TRUE;
2063 ELSE
2064 v_Skip_Asg := FALSE;
2065 END IF;
2066 --Proceed only if a New Person/Assignment Record is being processed
2067 IF v_Skip_Asg=FALSE THEN
2068
2069 v_csr_ee_si_prov_info := NULL;
2070 v_csr_ee_lat_nsi := NULL;
2071 v_csr_withdraw_asg := NULL;
2072 l_Withdraw_New_Hire := FALSE;
2073
2074 OPEN csr_ee_lat_nsi(csr_ee_rec.organization_id,l_si_provider_id
2075 ,csr_ee_rec.person_id,csr_ee_rec.assignment_id,l_NSI_Process_Date);
2076 FETCH csr_ee_lat_nsi INTO v_csr_ee_lat_nsi;
2077 CLOSE csr_ee_lat_nsi;
2078 hr_utility.set_location('v_csr_ee_lat_nsi.nsi_process_date: '||v_csr_ee_lat_nsi.nsi_process_date,450);
2079
2080
2081 --Withdraw the Assignment,if included in the specified Withdraw Asg Set
2082 IF l_withdraw_asg_set_id IS NOT NULL THEN
2083 OPEN csr_withdraw_asg(l_withdraw_asg_set_id ,l_Assignment_ID);
2084 FETCH csr_withdraw_asg INTO v_csr_withdraw_asg;
2085 IF csr_withdraw_asg%FOUND THEN
2086 hr_utility.set_location('Asg in Withdrawal Set '||l_Assignment_ID,450);
2087 IF v_csr_ee_lat_nsi.nsi_process_date IS NOT NULL THEN
2088 WITHDRAW_NL_NSI_EE_NOTIF(p_payroll_action_id,p_chunk
2089 ,csr_ee_rec.person_id,csr_ee_rec.assignment_id
2090 ,csr_ee_rec.organization_id,l_si_provider_id
2091 ,l_NSI_Process_Date,v_csr_ee_lat_nsi.nsi_process_date,l_asg_act_id,l_Withdraw_New_Hire);
2092 hr_utility.set_location('l_asg_act_id: '||l_asg_act_id,450);
2093 END IF;
2094
2095 END IF;
2096 CLOSE csr_withdraw_asg;
2097 END IF;
2098 hr_utility.set_location('After Withdraw v_csr_ee_lat_nsi.nsi_process_date: '||v_csr_ee_lat_nsi.nsi_process_date,450);
2099
2100 IF l_Withdraw_New_Hire THEN
2101 --If Previous NSI Record- Notif-a is withdrawn
2102 --Fresh NSI Notif -a with 1 needs to be generated.
2103 v_csr_ee_lat_nsi.nsi_process_date:=NULL;
2104 END IF;
2105
2106 --Fetch EE SI Provider Info
2107 OPEN csr_ee_si_prov_info(csr_ee_rec.organization_id,csr_ee_rec.assignment_id,l_NSI_Process_Date
2108 ,csr_ee_rec.zw_si_status,csr_ee_rec.ww_si_status
2109 ,csr_ee_rec.wao_si_status,csr_ee_rec.zfw_si_status);
2110 FETCH csr_ee_si_prov_info INTO v_csr_ee_si_prov_info;
2111 CLOSE csr_ee_si_prov_info;
2112
2113 hr_utility.set_location(' ZW -'||v_csr_ee_si_prov_info.zw_provider||' WW -'||v_csr_ee_si_prov_info.ww_provider
2114 ||' WAO -'||v_csr_ee_si_prov_info.wao_provider||' ZFW - '||v_csr_ee_si_prov_info.zfw_provider,450);
2115
2116 --Determine Code Insurance
2117 IF v_csr_ee_si_prov_info.zw_provider=l_si_provider_id
2118 AND v_csr_ee_si_prov_info.zw_er_org_id=l_employer_id
2119 AND csr_ee_rec.zw_si_status IS NOT NULL THEN
2120 l_Code_Insurance := '1';
2121 ELSE
2122 l_Code_Insurance := '2';
2123 END IF;
2124
2125 IF v_csr_ee_si_prov_info.ww_provider=l_si_provider_id
2126 AND v_csr_ee_si_prov_info.ww_er_org_id=l_employer_id
2127 AND csr_ee_rec.ww_si_status IS NOT NULL THEN
2128 l_Code_Insurance := l_Code_Insurance||'1';
2129 ELSE
2130 l_Code_Insurance := l_Code_Insurance||'2';
2131 END IF;
2132
2133 IF v_csr_ee_si_prov_info.wao_provider=l_si_provider_id
2134 AND v_csr_ee_si_prov_info.wao_er_org_id=l_employer_id
2135 AND csr_ee_rec.wao_si_status IS NOT NULL THEN
2136 l_Code_Insurance := l_Code_Insurance||'1';
2137 ELSE
2138 l_Code_Insurance := l_Code_Insurance||'2';
2139 END IF;
2140
2141 IF v_csr_ee_si_prov_info.zfw_provider=l_si_provider_id
2142 AND v_csr_ee_si_prov_info.zfw_er_org_id=l_employer_id
2143 AND csr_ee_rec.zfw_si_status IS NOT NULL
2144 AND csr_ee_rec.zfw_si_status <>'4' THEN
2145 l_Code_Insurance := l_Code_Insurance||'1';
2146 ELSE
2147 l_Code_Insurance := l_Code_Insurance||'2';
2148 END IF;
2149
2150 hr_utility.set_location('l_Code_Insurance '||l_Code_Insurance,450);
2151 --
2152
2153 IF l_Code_Insurance <>'2222' AND length(l_Code_Insurance)=4 THEN
2154 hr_utility.set_location('l_Prev_Person_ID '||l_Prev_Person_ID||' l_Person_ID '||l_Person_ID,450);
2155 hr_utility.set_location('l_Prev_Assignment_ID '||l_Prev_Assignment_ID||' l_Assignment_ID '||l_Assignment_ID,450);
2156 hr_utility.set_location('l_Prev_SIProvider_ID '||l_Prev_SIProvider_ID||' l_si_provider_id '||l_si_provider_id,450);
2157
2158 l_Create_NSI := TRUE;--Reset the NSI Record Creation Flag
2159 IF l_Prev_Person_ID = l_Person_ID
2160 AND l_Prev_Assignment_ID <> l_Assignment_ID
2161 AND l_Prev_SIProvider_ID = l_si_provider_id THEN
2162 l_Create_NSI := FALSE;
2163 END IF;
2164
2165 IF l_Create_NSI THEN
2166 hr_utility.set_location('l_Create_NSI TRUE '||l_withdraw_asg_set_id||' Asg Id: '||l_Assignment_ID,450);
2167 IF v_csr_ee_lat_nsi.nsi_process_date IS NULL THEN
2168 l_Create_NSI := TRUE;--Archive NSI Record Not Found.Hence Creating NSI
2169
2170 hr_utility.set_location('Calling ARCHIVE_NL_NSI_EE_DETAILS in STARTER Rec Mode ',450);
2171 ARCHIVE_NL_NSI_EE_DETAILS
2172 (l_business_group_id,l_report_type,p_payroll_action_id,l_asg_act_id,p_chunk,'STARTER'
2173 ,csr_ee_rec.person_id,csr_ee_rec.assignment_id
2174 ,csr_ee_rec.organization_id,l_si_provider_id
2175 ,l_NSI_Process_Date,v_csr_ee_lat_nsi.nsi_process_date);
2176 ELSE
2177 hr_utility.set_location('Calling ARCHIVE_NL_NSI_EE_DETAILS in EXISTING Rec Mode ',450);
2178 ARCHIVE_NL_NSI_EE_DETAILS
2179 (l_business_group_id,l_report_type,p_payroll_action_id,l_asg_act_id,p_chunk,'EXISTING'
2180 ,csr_ee_rec.person_id,csr_ee_rec.assignment_id
2181 ,csr_ee_rec.organization_id,l_si_provider_id
2182 ,l_NSI_Process_Date,v_csr_ee_lat_nsi.nsi_process_date);
2183 END IF;
2184 l_Prev_Person_ID := l_Person_ID;
2185 l_Prev_Assignment_ID := l_Assignment_ID;
2186 l_Prev_SIProvider_ID := l_si_provider_id;
2187 END IF; --End of if for Create_NSI Check
2188 END IF;--End of if for NSI Record Creation
2189
2190 END IF;--End of IF for Skip Asg Check
2191 END LOOP;
2192 --
2193 hr_utility.set_location('Leaving Assignment Action Code',500);
2194 --
2195 END ASSIGNMENT_ACTION_CODE;
2196
2197
2198
2199 /*-------------------------------------------------------------------------------
2200 |Name : ARCHIVE_INIT |
2201 |Type : Procedure |
2202 |Description : Initialization Code for Archiver |
2203 -------------------------------------------------------------------------------*/
2204 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER) IS
2205
2206 BEGIN
2207 --
2208 hr_utility.set_location('Entering Archive Init',600);
2209 hr_utility.set_location('Leaving Archive Init',700);
2210 --
2211 END ARCHIVE_INIT;
2212
2213
2214
2215 /*-------------------------------------------------------------------------------
2216 |Name : ARCHIVE_CODE |
2217 |Type : Procedure |
2218 |Description : This is the main procedure which calls the several procedures |
2219 | to archive the data. |
2220 -------------------------------------------------------------------------------*/
2221
2222
2223 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
2224 ,p_effective_date IN DATE) IS
2225
2226
2227 BEGIN
2228 --
2229 hr_utility.set_location('Entering Archive Code',800);
2230 hr_utility.set_location('Leaving Archive Code',800);
2231 --
2232 END ARCHIVE_CODE;
2233
2234 END PAY_NL_NSI_PROCESS;