[Home] [Help]
PACKAGE BODY: APPS.PAY_KW_ANNUAL_REPORTS
Source
1 PACKAGE BODY pay_kw_annual_reports AS
2 /* $Header: pykwyear.pkb 120.36 2006/08/22 06:05:47 spendhar noship $ */
3
4 lg_format_mask varchar2(50);
5 PROCEDURE set_currency_mask
6 (p_business_group_id IN NUMBER) IS
7 /* Cursor to retrieve Currency */
8 CURSOR csr_currency IS
9 SELECT org_information10
10 FROM hr_organization_information
11 WHERE organization_id = p_business_group_id
12 AND org_information_context = 'Business Group Information';
13 l_currency VARCHAR2(40);
14 BEGIN
15 OPEN csr_currency;
16 FETCH csr_currency into l_currency;
17 CLOSE csr_currency;
18 lg_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
19 END set_currency_mask;
20
21 -------------------------------------------------------------------------
22 FUNCTION get_lookup_meaning
23 (p_lookup_type varchar2
24 ,p_lookup_code varchar2)
25 RETURN VARCHAR2 IS
26 CURSOR csr_lookup IS
27 select meaning
28 from hr_lookups
29 where lookup_type = p_lookup_type
30 and lookup_code = p_lookup_code;
31 l_meaning hr_lookups.meaning%type;
32 BEGIN
33 OPEN csr_lookup;
34 FETCH csr_lookup INTO l_Meaning;
35 CLOSE csr_lookup;
36 RETURN l_meaning;
37 END get_lookup_meaning;
38 ------------------------------------------------------------------------------------------
39
40 PROCEDURE report55
41 (p_request_id NUMBER
42 ,p_report VARCHAR2
43 ,p_business_group_id NUMBER
44 ,p_employer_id NUMBER
45 ,p_effective_month VARCHAR2
46 ,p_effective_year VARCHAR2
47 ,l_xfdf_blob OUT NOCOPY BLOB
48 )
49 AS
50
51
52 /*Cursor for fetching Local nationality for the BG */
53 CURSOR csr_get_loc_nat IS
54 SELECT org_information1
55 FROM hr_organization_information
56 WHERE organization_id = p_business_group_id
57 AND org_information_context = 'KW_BG_DETAILS';
58
59 /* Cursor for fetching Defined balance ids from Org EIT */
60 CURSOR csr_get_def_bal_ids (l_emp_id number) IS
61 SELECT ORG_INFORMATION1
62 FROM HR_ORGANIZATION_INFORMATION
63 WHERE Organization_id = l_emp_id
64 AND org_information_context = 'KW_SI_DETAILS';
65
66 /*Cursor for fetching Employer SSN*/
67 CURSOR csr_employer_ssn IS
68 SELECT LPAD(org_information4,9,'0')
69 FROM hr_organization_information
70 WHERE organization_id = p_employer_id
71 AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
72
73 /*Cursor for fetching Employer Name*/
74 CURSOR csr_employer_name IS
75 SELECT name
76 FROM hr_organization_units
77 WHERE organization_id = p_employer_id;
78
79 /*Cursor for fetching defined balance id*/
80 CURSOR csr_get_def_bal_id(p_user_name VARCHAR2) IS
81 SELECT u.creator_id
82 FROM ff_user_entities u,
83 ff_database_items d
84 WHERE d.user_name = p_user_name
85 AND u.user_entity_id = d.user_entity_id
86 AND u.legislation_code = 'KW'
87 AND u.business_group_id is null
88 AND u.creator_type = 'B';
89
90 /*Cursor for fetching list of employees*/
91 CURSOR csr_get_emp (l_employer_id number, l_date date , l_nat varchar2) IS
92 SELECT distinct asg.person_id
93 ,asg.assignment_id
94 ,paa.assignment_action_id
95 FROM per_assignments_f asg
96 ,pay_assignment_actions paa
97 ,pay_payroll_actions ppa
98 ,hr_soft_coding_keyflex hscl
99 ,per_people_f ppf
100 WHERE asg.assignment_id = paa.assignment_id
101 AND paa.payroll_action_id = ppa.payroll_action_id
102 AND ppa.action_type in ('R','Q')
103 AND ppa.action_status = 'C'
104 AND paa.action_status = 'C'
105 AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
106 AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
107 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
108 AND hscl.segment1 = to_char(l_employer_id)
109 AND ppf.person_id = asg.person_id
110 AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
111 AND ppf.nationality = l_nat;
112 rec_get_emp csr_get_emp%ROWTYPE;
113
114 /* Cursor to fetch first assignment_action_id and date earned for the employee */
115 CURSOR csr_get_first_assact (l_assignment_id number,l_date date) IS
116 select decode(trunc(ppa.date_earned,'YYYY'),trunc(l_date,'YYYY'),trunc(l_date,'YYYY'),ppa.date_earned) , paa.assignment_action_id
117 from pay_payroll_actions ppa, pay_assignment_actions paa
118 Where paa.assignment_id = l_assignment_id
119 and paa.payroll_action_id = ppa.payroll_action_id
120 and ppa.action_type in ('R','Q')
121 and ppa.action_status = 'C'
122 and paa.action_status = 'C'
123 order by ppa.date_earned asc;
124
125 /* Cursor to fetch phone and fax information of the employer */
126 CURSOR csr_get_det_employer (l_type varchar2) IS
127 select org_information3
128 from hr_organization_information
129 where org_information_context = 'ORG_CONTACT_DETAILS'
130 and organization_id = p_employer_id
131 and org_information1 = l_type;
132
133 /* Cursor for fetching person's phone details */
134 CURSOR csr_p_phone_data (l_person_id number,l_ph_type varchar2,l_effective_date date) IS
135 SELECT pp.phone_number
136 FROM per_phones pp,per_people_f ppf
137 WHERE pp.parent_id = ppf.person_id
138 AND pp.phone_type = l_ph_type
139 AND ppf.person_id = l_person_id
140 AND l_effective_date between pp.date_from and nvl(pp.date_to,to_date('31-12-4712','DD-MM-YYYY'));
141
142 /* Cursor for fetching Employer's location_id */
143 CURSOR csr_get_loc_id IS
144 select location_id
145 from hr_organization_units
146 where organization_id = p_employer_id
147 and business_group_id = p_business_group_id;
148
149 /* Cursor for fetching Employer's Address */
150 CURSOR csr_get_address (l_location_id number) IS
151 select address_line_1 || decode(address_line_2,null,null,',') || address_line_2 , postal_code
152 from hr_locations
153 where location_id = l_location_id;
154
155 /* Cursor for fetching person's full name */
156 CURSOR csr_get_full_name (l_person_id number, l_effective_date date) IS
157 SELECT ppf.full_name
158 FROM per_people_f ppf
159 WHERE ppf.person_id = l_person_id
160 AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
161
162 /* Cursor for fetching the person's assignment data */
163 CURSOR csr_p_asg_data (l_person_id number,l_effective_date date) IS
164 SELECT hsck.segment2,paf.job_id
165 FROM per_assignments_f paf,hr_soft_coding_keyflex hsck
166 WHERE paf.person_id = l_person_id
167 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
168 AND hsck.segment1 = p_employer_id
169 AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
170
171 /* Cursor for fetching the person's job */
172 CURSOR csr_p_job (l_person_id number,l_effective_date date) IS
173 SELECT pjb.name
174 FROM per_assignments_f paf,per_jobs pjb
175 WHERE paf.person_id = l_person_id
176 AND pjb.job_id = paf.job_id
177 AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
178
179 /* Cursor to fetch assignment_action_ids and date earned for the employee TO CAPTURE SOCIAL ALLOWANCE */
180 CURSOR csr_get_assact_first (l_assignment_id number,l_date date) IS
181 select ppa.date_earned, paa.assignment_action_id
182 from pay_payroll_actions ppa, pay_assignment_actions paa
183 Where paa.assignment_id = l_assignment_id
184 and paa.payroll_action_id = ppa.payroll_action_id
185 and ppa.action_type in ('R','Q')
186 and ppa.action_status = 'C'
187 and paa.action_status = 'C'
188 and ppa.date_earned <= l_date
189 order by ppa.date_earned ASC;
190
191 /* Cursor to fetch assignment_action_id corresponding to first_date_earned to calculate social allowance */
192 CURSOR csr_get_assact_one (l_assignment_id number,l_date date) IS
193 select paa.assignment_action_id
194 from pay_payroll_actions ppa, pay_assignment_actions paa
195 Where paa.assignment_id = l_assignment_id
196 and paa.payroll_action_id = ppa.payroll_action_id
197 and ppa.action_type in ('R','Q')
198 and ppa.action_status = 'C'
199 and paa.action_status = 'C'
200 and trunc(ppa.date_earned,'MM') = trunc(l_date,'MM') ;
201
202 TYPE assact_rec IS RECORD
203 (person_id NUMBER
204 ,assignment_id NUMBER
205 ,assignment_action_id NUMBER);
206 TYPE t_assact_table IS TABLE OF assact_rec INDEX BY BINARY_INTEGER;
207 t_store_assact t_assact_table;
208
209
210 l_postal_code varchar2(100);
211 l_employer_name hr_organization_units.name%TYPE;
212 l_employer_ssn NUMBER;
213 l_basic_social_id NUMBER;
214 l_supplementary_social_id NUMBER;
215 l_additional_social_id NUMBER;
216 l_first_assact_id number;
217 l_job_id number;
218 l_def_bal_id number;
219
220 l_first_date date;
221
222 l_monthly_sal number(15,3);
223 l_monthly_earning number(15,3);
224 l_first_social number(15,3);
225 l_total number(15,3);
226
227 l_fm_l_monthly_sal varchar2(100);
228 l_fm_l_first_social varchar2(100);
229 l_fm_l_total varchar2(100);
230
231 l_basic_si_base_val NUMBER;
232 l_supp_si_base_val NUMBER;
233 l_add_si_val NUMBER;
234
235 l_full_name varchar2(240);
236 l_insured_ssn varchar2(100);
237 l_job varchar2(100);
238
239 l_effective_date DATE;
240 l_input_date VARCHAR2(30);
241
242 l_total_amount NUMBER;
243
244 l_fm_total_amount VARCHAR2(50);
245 l_effective_month VARCHAR2(50);
246
247 l_loc_id number;
248 l_employer_address varchar2(400);
249 l_employer_phone varchar2(100);
250 l_employer_fax varchar2(100);
251 l number;
252 i number;
253 j number;
254 l_new_processed number;
255 l_all_processed number;
256 l_new_count number;
257
258 l_basic_arrears_id number;
259 l_supp_arrears_id number;
260 l_add_arrears_id number;
261 l_tot_earn_bal_id number;
262 l_social_id number;
263
264 l_first_date_earned date;
265 l_assact_one number;
266 l_first_assact number;
267 l_loc_nat varchar2(100);
268
269 l_user_format VARCHAR2(80);
270
271 BEGIN
272
273
274 set_currency_mask(p_business_group_id);
275
276 l_input_date := '01-'||p_effective_month||'-'||p_effective_year;
277 l_effective_date := last_day(to_date(l_input_date,'DD-MM-YYYY'));
278
279 INSERT INTO fnd_sessions (session_id, effective_date)
280 VALUES (userenv('sessionid'), l_effective_date);
281
282 -- To clear the PL/SQL Table values.
283 vXMLTable.DELETE;
284 vCtr := 1;
285 hr_utility.set_location('Entering report166 ',10);
286
287 l_effective_month := hr_general.decode_lookup('KW_GREGORIAN_MONTH', p_effective_month);
288
289 l_user_format := NVL(FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G');
290
291 /* vXMLTable(vCtr).TagName := 'month';
292 vXMLTable(vCtr).TagValue := l_effective_month;
293 vctr := vctr + 1;
294
295 vXMLTable(vCtr).TagName := 'year';
296 vXMLTable(vCtr).TagValue := p_effective_year;
297 vctr := vctr + 1;
298 */
299
300 /*Fetch Local Nationality */
301 OPEN csr_get_loc_nat;
302 FETCH csr_get_loc_nat into l_loc_nat;
303 CLOSE csr_get_loc_nat;
304
305 /*Fetch Employer SSN*/
306 OPEN csr_employer_ssn;
307 FETCH csr_employer_ssn INTO l_employer_ssn;
308 CLOSE csr_employer_ssn;
309
310 /* vXMLTable(vCtr).TagName := 'employer_ssn';
311 vXMLTable(vCtr).TagValue := l_employer_ssn;
312 vctr := vctr + 1;*/
313
314 /*Fetch Employer Name*/
315 OPEN csr_employer_name;
316 FETCH csr_employer_name INTO l_employer_name;
317 CLOSE csr_employer_name;
318
319 /* vXMLTable(vCtr).TagName := 'employer_name';
320 vXMLTable(vCtr).TagValue := l_employer_name;
321 vctr := vctr + 1;*/
322
323 /*Fetch Employer Address*/
324 OPEN csr_get_loc_id;
325 FETCH csr_get_loc_id INTO l_loc_id;
326 CLOSE csr_get_loc_id;
327
328 OPEN csr_get_address (l_loc_id);
329 FETCH csr_get_address INTO l_employer_address,l_postal_code;
330 CLOSE csr_get_address;
331
332 OPEN csr_get_def_bal_id('TOTAL_EARNINGS_ASG_RUN');
333 FETCH csr_get_def_bal_id into l_tot_earn_bal_id;
334 CLOSE csr_get_def_bal_id;
335
336 OPEN csr_get_def_bal_ids(p_employer_id);
337 FETCH csr_get_def_bal_ids into l_social_id;
338 CLOSE csr_get_def_bal_ids;
339 /*
340 vXMLTable(vCtr).TagName := 'work_address';
341 vXMLTable(vCtr).TagValue := l_employer_address;
342 vctr := vctr + 1;
343
344 vXMLTable(vCtr).TagName := 'postal_code';
345 vXMLTable(vCtr).TagValue := l_postal_code;
346 vctr := vctr + 1;
347 */
348
349 /*Fetch Employer Phone*/
350 OPEN csr_get_det_employer('PHONE');
351 FETCH csr_get_det_employer INTO l_employer_phone;
352 CLOSE csr_get_det_employer;
353
354 /*
355 vXMLTable(vCtr).TagName := 'work_phone';
356 vXMLTable(vCtr).TagValue := l_employer_phone;
357 vctr := vctr + 1;
358 */
359
360 /*Fetch Employer Fax*/
361 OPEN csr_get_det_employer('FAX');
362 FETCH csr_get_det_employer INTO l_employer_fax;
363 CLOSE csr_get_det_employer;
364
365 /*
366 vXMLTable(vCtr).TagName := 'work_fax';
367 vXMLTable(vCtr).TagValue := l_employer_fax;
368 vctr := vctr + 1;
369 */
370
371 i := 0;
372
373 OPEN csr_get_emp(p_employer_id , l_effective_date ,l_loc_nat);
374 LOOP
375 FETCH csr_get_emp INTO rec_get_emp;
376 EXIT WHEN csr_get_emp%NOTFOUND;
377 i := i + 1;
378 t_store_assact(i).person_id := rec_get_emp.person_id;
379 t_store_assact(i).assignment_id := rec_get_emp.assignment_id;
380 t_store_assact(i).assignment_action_id := rec_get_emp.assignment_action_id;
381 END LOOP;
382 CLOSE csr_get_emp;
383
384 IF i > 0 THEN
385 l_new_processed := 0;
386 ELSE
387 l_new_processed := 1;
388 END IF;
389
390 l_all_processed := 0;
391
392 l := 1;
393
394 j := 1;
395
396 WHILE l_all_processed <> 1 LOOP
397 --Writing data for new employees
398 l_new_count := 0;
399
400 vXMLTable(vCtr).TagName := 'employer_name';
401 vXMLTable(vCtr).TagValue := l_employer_ssn;
402 vctr := vctr + 1;
403
404 vXMLTable(vCtr).TagName := 'employer_ssn'; /*** Changed after demo ***/
405 vXMLTable(vCtr).TagValue := l_employer_name;
406 vctr := vctr + 1;
407
408 vXMLTable(vCtr).TagName := 'month';
409 vXMLTable(vCtr).TagValue := l_effective_month;
410 vctr := vctr + 1;
411
412 vXMLTable(vCtr).TagName := 'year';
413 vXMLTable(vCtr).TagValue := p_effective_year;
414 vctr := vctr + 1;
415
416 vXMLTable(vCtr).TagName := 'Month';
417 vXMLTable(vCtr).TagValue := l_effective_month;
418 vctr := vctr + 1;
419
420 vXMLTable(vCtr).TagName := 'Year';
421 vXMLTable(vCtr).TagValue := p_effective_year;
422 vctr := vctr + 1;
423
424
425 vXMLTable(vCtr).TagName := 'work_address';
426 vXMLTable(vCtr).TagValue := l_employer_address;
427 vctr := vctr + 1;
428
429 vXMLTable(vCtr).TagName := 'postal_code';
430 vXMLTable(vCtr).TagValue := l_postal_code;
431 vctr := vctr + 1;
432
433 vXMLTable(vCtr).TagName := 'work_phone';
434 vXMLTable(vCtr).TagValue := l_employer_phone;
435 vctr := vctr + 1;
436
437 vXMLTable(vCtr).TagName := 'work_fax';
438 vXMLTable(vCtr).TagValue := l_employer_fax;
439 vctr := vctr + 1;
440
441 WHILE j <= i LOOP
442
443 OPEN csr_get_full_name(t_store_assact(j).person_id,l_effective_date);
444 FETCH csr_get_full_name INTO l_full_name;
445 CLOSE csr_get_full_name;
446
447 l_full_name := null;
448
449 l_full_name := hr_person_name.get_person_name
450 (p_person_id => t_store_assact(j).person_id
451 ,p_effective_date => l_effective_date
452 ,p_format_name => 'DISPLAY_NAME'
453 ,p_user_format_choice => l_user_format);
454
455 /* Reset ssn */
456
457 l_insured_ssn := null;
458 l_job_id := null;
459
460 OPEN csr_p_asg_data(t_store_assact(j).person_id, l_effective_date);
461 FETCH csr_p_asg_data into l_insured_ssn,l_job_id;
462 CLOSE csr_p_asg_data;
463
464 /* Reset job */
465
466 l_job := null;
467
468 OPEN csr_p_job(t_store_assact(j).person_id, l_effective_date);
469 FETCH csr_p_job into l_job;
470 CLOSE csr_p_job;
471
472 l_new_count := l_new_count+1;
473
474 OPEN csr_get_first_assact (t_store_assact(j).assignment_id,l_effective_date);
475 FETCH csr_get_first_assact into l_first_date,l_first_assact_id;
476 CLOSE csr_get_first_assact;
477
478 l_monthly_earning := pay_balance_pkg.get_value(l_tot_earn_bal_id,t_store_assact(j).assignment_action_id);
479
480 OPEN csr_get_assact_first(t_store_assact(j).assignment_id,l_effective_date);
481 FETCH csr_get_assact_first INTO l_first_date_earned,l_first_assact;
482 CLOSE csr_get_assact_first;
483
484 If trunc(l_first_date_earned,'MM') <> trunc(l_effective_date,'YYYY') then
485 If trunc(l_first_date_earned,'YYYY') < trunc(l_effective_date,'YYYY') then
486 l_first_date_earned := trunc(l_effective_date,'YYYY');
487 End If;
488 End If;
489
490 l_assact_one := null;
491
492 /* Get the assact id corresponding to the first_assact_date calculated above */
493 OPEN csr_get_assact_one (t_store_assact(j).assignment_id,l_first_date_earned);
494 FETCH csr_get_assact_one into l_assact_one;
495 CLOSE csr_get_assact_one;
496
497 If l_social_id is not null THEN
498 If l_assact_one is not null then
499 l_first_social := pay_balance_pkg.get_value(l_social_id,l_assact_one);
500 Else
501 l_first_social := 0;
502 End If;
503 Else
504 l_first_social := 0;
505 End If;
506
507 /*fnd_file.put_line(fnd_file.log,' l_first_social '|| l_first_social);
508 fnd_file.put_line(fnd_file.log,' l_assact_one '|| l_assact_one);
509 fnd_file.put_line(fnd_file.log,' l_social_id '|| l_social_id);*/
510
511 l_monthly_sal := l_monthly_earning - l_first_social;
512
513 l_total := l_monthly_earning;
514
515 l_fm_l_monthly_sal := to_char(l_monthly_sal,lg_format_mask);
516 l_fm_l_first_social := to_char(l_first_social,lg_format_mask);
517 l_fm_l_total := to_char(l_total,lg_format_mask);
518
519 /** Populate the XML file **/
520
521 vXMLTable(vCtr).TagName := 's_no_'||l;
522 vXMLTable(vCtr).TagValue := l;
523 vctr := vctr + 1;
524
525 vXMLTable(vCtr).TagName := 'insured_ssn_'||l;
526 vXMLTable(vCtr).TagValue := l_insured_ssn;
527 vctr := vctr + 1;
528
529 vXMLTable(vCtr).TagName := 'insured_name_'||l;
530 vXMLTable(vCtr).TagValue := SUBSTR(l_full_name,1,60);
531 vctr := vctr + 1;
532
533 vXMLTable(vCtr).TagName := 'job_'||l;
534 vXMLTable(vCtr).TagValue := substr(l_job,1,30);
535 vctr := vctr + 1;
536
537 vXMLTable(vCtr).TagName := 'monthly_sal_dinars_'||l;
538 vXMLTable(vCtr).TagValue := substr(l_fm_l_monthly_sal,1,length(l_fm_l_monthly_sal)-4);
539 vctr := vctr + 1;
540
541 vXMLTable(vCtr).TagName := 'monthly_salary_fills_'||l;
542 vXMLTable(vCtr).TagValue := substr(l_fm_l_monthly_sal,length(l_fm_l_monthly_sal)-2);
543 vctr := vctr + 1;
544
545 vXMLTable(vCtr).TagName := 'first_sal_dinars_'||l;
546 vXMLTable(vCtr).TagValue := substr(l_fm_l_first_social,1,length(l_fm_l_first_social)-4);
547 vctr := vctr + 1;
548
549 vXMLTable(vCtr).TagName := 'first_salary_fills_'||l;
550 vXMLTable(vCtr).TagValue := substr(l_fm_l_first_social,length(l_fm_l_first_social)-2);
551 vctr := vctr + 1;
552
553 vXMLTable(vCtr).TagName := 'total_sal_dinars_'||l;
554 vXMLTable(vCtr).TagValue := substr(l_fm_l_total,1,length(l_fm_l_total)-4);
555 vctr := vctr + 1;
556
557 vXMLTable(vCtr).TagName := 'total_salary_fills_'||l;
558 vXMLTable(vCtr).TagValue := substr(l_fm_l_total,length(l_fm_l_total)-2);
559 vctr := vctr + 1;
560
561 j := j + 1;
562
563 l := l + 1;
564
565 IF j > i THEN
566 l_all_processed := 1;
567 END IF;
568
569 IF l_new_count = 15 THEN
570 l := 1;
571
572
573 vXMLTable(vCtr).TagName := 'PAGE-BK';
574 vXMLTable(vCtr).TagValue := ' ';
575 vctr := vctr + 1;
576
577 EXIT;
578 END IF;
579 END LOOP;
580
581 IF j > i THEN
582 l_new_processed := 1;
583 EXIT;
584 END IF;
585 END LOOP;
586
587 hr_utility.set_location('Finished creating xml data for Procedure report166 ',20);
588
589 WritetoCLOB ( l_xfdf_blob );
590
591 /*EXCEPTION
592 WHEN utl_file.invalid_path then
593 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
594 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
595 hr_utility.raise_error;
596 --
597 WHEN utl_file.invalid_mode then
598 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
599 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
600 hr_utility.raise_error;
601 --
602 WHEN utl_file.invalid_filehandle then
603 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
604 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
605 hr_utility.raise_error;
606 --
607 WHEN utl_file.invalid_operation then
608 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
609 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
610 hr_utility.raise_error;
611 --
612 WHEN utl_file.read_error then
613 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
614 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
615 hr_utility.raise_error;
616 --
617 WHEN others THEN
618 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
619 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
620 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
621 hr_utility.raise_error;*/
622
623 END report55;
624 ------------------------------------------------------------------------------------
625
626 PROCEDURE report56
627 (p_request_id NUMBER
628 ,p_report VARCHAR2
629 ,p_business_group_id NUMBER
630 ,p_employer_id NUMBER
631 ,p_effective_month VARCHAR2
632 ,p_effective_year VARCHAR2
633 ,p_assignment_id NUMBER DEFAULT NULL
634 ,l_xfdf_blob OUT NOCOPY BLOB
635 )
636
637 AS
638
639
640 l_effective_date DATE;
641
642 /*Cursor for fetching Local nationality for the BG */
643 CURSOR csr_get_loc_nat IS
644 SELECT org_information1
645 FROM hr_organization_information
646 WHERE organization_id = p_business_group_id
647 AND org_information_context = 'KW_BG_DETAILS';
648
649 /*Cursor for fetching Employer SSN*/
650 CURSOR csr_employer_ssn IS
651 SELECT LPAD(org_information4,9,'0')
652 FROM hr_organization_information
653 WHERE organization_id = p_employer_id
654 AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
655
656 /*Cursor for fetching Employer Name*/
657 CURSOR csr_employer_name IS
658 SELECT name
659 FROM hr_organization_units
660 WHERE organization_id = p_employer_id;
661
662 /*Cursor for fetching defined balance id*/
663 CURSOR csr_get_def_bal_id(p_user_name VARCHAR2) IS
664 SELECT u.creator_id
665 FROM ff_user_entities u,
666 ff_database_items d
667 WHERE d.user_name = p_user_name
668 AND u.user_entity_id = d.user_entity_id
669 AND u.legislation_code = 'KW'
670 AND u.business_group_id is null
671 AND u.creator_type = 'B';
672
673 /*Cursor for fetching list of employees*/
674 CURSOR csr_get_emp (l_employer_id number , l_date date , l_nat varchar2) IS
675 SELECT distinct asg.person_id
676 ,asg.assignment_id
677 ,paa.assignment_action_id
678 ,ppa.date_earned
679 FROM per_assignments_f asg
680 ,pay_assignment_actions paa
681 ,pay_payroll_actions ppa
682 ,hr_soft_coding_keyflex hscl
683 ,per_people_f ppf
684 WHERE asg.assignment_id = paa.assignment_id
685 AND paa.payroll_action_id = ppa.payroll_action_id
686 AND ppa.action_type in ('R','Q')
687 AND ppa.action_status = 'C'
688 AND paa.action_status = 'C'
689 AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
690 AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
691 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
692 AND hscl.segment1 = to_char(l_employer_id)
693 AND ppf.person_id = asg.person_id
694 AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
695 AND ppf.nationality = l_nat;
696 rec_get_emp csr_get_emp%ROWTYPE;
697
698 /*Cursor for fetching person_id if employee is the parameter */
699 CURSOR csr_get_emp_det (l_employer_id number, l_date date, l_nat varchar2) IS
700 SELECT distinct asg.person_id
701 ,asg.assignment_id
702 ,paa.assignment_action_id
703 ,ppa.date_earned
704 FROM per_assignments_f asg
705 ,pay_assignment_actions paa
706 ,pay_payroll_actions ppa
707 ,hr_soft_coding_keyflex hscl
708 ,per_people_f ppf
709 WHERE asg.assignment_id = p_assignment_id
710 AND asg.assignment_id = paa.assignment_id
711 AND paa.payroll_action_id = ppa.payroll_action_id
712 AND ppa.action_type in ('R','Q')
713 AND ppa.action_status = 'C'
714 AND paa.action_status = 'C'
715 AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
716 AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
717 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
718 AND hscl.segment1 = to_char(l_employer_id)
719 AND ppf.person_id = asg.person_id
720 AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
721 AND ppf.nationality = l_nat;
722
723 /*Cursor for fetching employee name*/
724 CURSOR csr_get_emp_name(p_person_id NUMBER,l_format VARCHAR2) IS
725 SELECT decode(l_format,'L',PER_INFORMATION3,SUBSTR(first_name,1,60)) first_name ,
726 decode(l_format,'L',PER_INFORMATION4,SUBSTR(PER_INFORMATION1,1,60)) father_name ,
727 decode(l_format,'L',PER_INFORMATION5,SUBSTR(PER_INFORMATION2,1,60)) grandfather_name,
728 decode(l_format,'L',PER_INFORMATION6,SUBSTR(last_name,1,60)) last_name
729 FROM per_people_f ppf
730 WHERE person_id = p_person_id
731 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
732 rec_get_emp_name csr_get_emp_name%ROWTYPE;
733
734 /* Cursor to fetch assignment_action_ids and date earned for the employee */
735 CURSOR csr_get_assact (l_assignment_id number,l_date date) IS
736 select ppa.date_earned, paa.assignment_action_id
737 from pay_payroll_actions ppa, pay_assignment_actions paa
738 Where paa.assignment_id = l_assignment_id
739 and paa.payroll_action_id = ppa.payroll_action_id
740 and ppa.action_type in ('R','Q')
741 and ppa.action_status = 'C'
742 and paa.action_status = 'C'
743 and ppa.date_earned <= l_date
744 order by ppa.date_earned DESC;
745
746 /* Cursor for fetching the person's assignment data */
747 CURSOR csr_p_asg_data (l_person_id number,l_effective_date date) IS
748 SELECT hsck.segment2
749 FROM per_assignments_f paf,hr_soft_coding_keyflex hsck
750 WHERE paf.person_id = l_person_id
751 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
752 AND hsck.segment1 = p_employer_id
753 AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
754
755 /* Cursor to fetch termination details */
756 CURSOR csr_get_term_details_56 (l_assignment_id number,l_date date) IS
757 SELECT pos.actual_termination_date,pos.leaving_reason
758 FROM per_periods_of_service pos , per_assignments_f paf
759 WHERE paf.assignment_id = l_assignment_id
760 AND paf.period_of_service_id = pos.period_of_service_id
761 AND trunc(pos.actual_termination_date,'MM') = trunc(l_date,'MM')
762 AND trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date;
763
764 /* Cursor to fetch leaving reason meaning */
765 CURSOR csr_fetch_leav_reas (l_code varchar2) IS
766 SELECT meaning
767 FROM hr_lookups
768 WHERE lookup_type = 'LEAV_REAS'
769 AND lookup_code = l_code
770 AND enabled_flag = 'Y';
771
772 /* Cursor to fetch hire date for an employee */
773 CURSOR csr_get_hire_date_56(l_assignment_id number, l_date date) IS
774 SELECT pos.date_start
775 from per_periods_of_service pos, per_assignments_f paf
776 WHERE paf.assignment_id = l_assignment_id
777 AND paf.period_of_service_id = pos.period_of_service_id
778 AND trunc(pos.date_start,'MM') = trunc(l_date,'MM')
779 AND trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date;
780
781 TYPE assact_rec IS RECORD
782 (person_id NUMBER
783 ,assignment_id NUMBER
784 ,assignment_action_id NUMBER
785 ,date_earned DATE);
786 TYPE t_assact_table IS TABLE OF assact_rec INDEX BY BINARY_INTEGER;
787 t_store_assact t_assact_table;
788
789 TYPE store_rec IS RECORD
790 (date_earned date
791 ,assignment_action_id NUMBER
792 ,supp_bal_value number(15,3)
793 ,tot_earn_value number(15,3));
794 TYPE t_store_table IS TABLE OF store_rec INDEX BY BINARY_INTEGER;
795 t_store_recs t_store_table;
796
797 x number;
798 y number;
799 i number;
800 j number;
801 z number;
802 v number;
803 e number;
804
805 l_employer_name varchar2(240);
806 l_employer_ssn varchar2(100);
807 l_full_name varchar2(240);
808 l_input_date varchar2(100);
809 l_supp_id number;
810
811 l_emp varchar2(10);
812 l_per_person_id number;
813 l_per_assact_id number;
814 l_per_date_earned date;
815 l_new_processed number;
816 l_all_processed number;
817 l_per_assignment_id number;
818 l_emp_term_date date;
819 l_commencement_date date;
820 l_termination_date date;
821 l_last_sal_date date;
822 l_new_count number;
823 l_insured_ssn varchar2(100);
824 l_current_supp_contri number(15,3);
825 l_fm_l_current_supp_contri varchar2(100);
826
827 l_temp_bal_value number(15,3);
828
829 l_supp_bal number(15,3);
830 l_termination varchar2(10);
831 l_rejoin varchar2(10);
832 l_new varchar2(10);
833 l_diff_exists varchar2(10);
834 l_first_name varchar2(120);
835 l_father_name varchar2(120);
836 l_grand_name varchar2(120);
837 l_last_name varchar2(120);
838 l_loc_nat varchar2(100);
839 l_tot_earn_id number;
840 l_tot_earn number(15,3);
841 l_act_term_date_56 date;
842 l_leav_reas varchar2(100);
843 l_leav_reas_cd varchar2(30);
844 l_hire_date_56 date;
845
846 l_df_flag varchar2(10);
847
848 l_user_format VARCHAR2(80);
849
850 BEGIN
851
852 set_currency_mask(p_business_group_id);
853
854 l_input_date := '01-'||p_effective_month||'-'||p_effective_year;
855 l_effective_date := last_day(to_date(l_input_date,'DD-MM-YYYY'));
856 /*l_eff_term_date := to_date('28-'||to_char(l_effective_date,'MM-YYYY'),'DD-MM-YYYY');*/
857 INSERT INTO fnd_sessions (session_id, effective_date)
858 VALUES (userenv('sessionid'), l_effective_date);
859
860 -- To clear the PL/SQL Table values.
861 vXMLTable.DELETE;
862 vCtr := 1;
863 hr_utility.set_location('Entering FORM1 ',10);
864
865 l_user_format := null;
866 l_user_format := FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT');
867
868 /*Fetch Local Nationality */
869 OPEN csr_get_loc_nat;
870 FETCH csr_get_loc_nat into l_loc_nat;
871 CLOSE csr_get_loc_nat;
872
873 /*Fetch Employer SSN*/
874 OPEN csr_employer_ssn;
875 FETCH csr_employer_ssn INTO l_employer_ssn;
876 CLOSE csr_employer_ssn;
877
878 /*Fetch Employer Name*/
879 OPEN csr_employer_name;
880 FETCH csr_employer_name INTO l_employer_name;
881 CLOSE csr_employer_name;
882
883
884 OPEN csr_get_def_bal_id('SUPPLEMENTARY_SOCIAL_INSURANCE_BASE_ASG_RUN');
885 FETCH csr_get_def_bal_id into l_supp_id;
886 CLOSE csr_get_def_bal_id;
887
888 OPEN csr_get_def_bal_id('TOTAL_EARNINGS_ASG_RUN');
889 FETCH csr_get_def_bal_id into l_tot_earn_id;
890 CLOSE csr_get_def_bal_id;
891
892
893 i := 0;
894 l_df_flag := 'U';
895
896
897 If p_assignment_id is null then
898
899 l_emp := 'N';
900
901 OPEN csr_get_emp(p_employer_id , l_effective_date ,l_loc_nat);
902 LOOP
903 FETCH csr_get_emp INTO rec_get_emp;
904 EXIT WHEN csr_get_emp%NOTFOUND;
905
906 i := i + 1;
907
908 t_store_assact(i).person_id := rec_get_emp.person_id;
909 t_store_assact(i).assignment_id := rec_get_emp.assignment_id;
910 t_store_assact(i).assignment_action_id := rec_get_emp.assignment_action_id;
911 t_store_assact(i).date_earned := rec_get_emp.date_earned;
912 END LOOP;
913 CLOSE csr_get_emp;
914 Else
915 l_emp := 'Y';
916
917 OPEN csr_get_emp_det(p_employer_id, l_effective_date,l_loc_nat);
918 FETCH csr_get_emp_det into l_per_person_id ,l_per_assignment_id, l_per_assact_id, l_per_date_earned;
919 CLOSE csr_get_emp_det;
920
921 If l_per_person_id is not null then
922 i := 1;
923 t_store_assact(i).person_id := l_per_person_id;
924 t_store_assact(i).assignment_id := p_assignment_id;
925 t_store_assact(i).assignment_action_id := l_per_assact_id;
926 t_store_assact(i).date_earned := l_per_date_earned;
927 End If;
928
929 End If;
930
931
932
933 IF i > 0 THEN
934 l_new_processed := 0;
935 ELSE
936 l_new_processed := 1;
937
938 END IF;
939
940 l_all_processed := 0;
941
942 j := 1;
943
944
945
946 WHILE j <= i LOOP
947
948 l_new := null;
949 l_termination := null;
950 l_rejoin := null;
951 l_termination_date := null;
952 l_last_sal_date := null;
953 l_father_name := null;
954 l_grand_name := null;
955 l_first_name := null;
956 l_insured_ssn := null;
957
958 l_act_term_date_56 := null;
959 l_leav_reas := null;
960 l_leav_reas_cd := null;
961 l_hire_date_56 := null;
962 l_commencement_date := null;
963
964 v := 1;
965
966 OPEN csr_get_assact (t_store_assact(j).assignment_id,l_effective_date);
967 LOOP
968 FETCH csr_get_assact into t_store_recs(v).date_earned,t_store_recs(v).assignment_action_id;
969 EXIT WHEN csr_get_assact%notfound;
970 l_supp_bal := pay_balance_pkg.get_value(l_supp_id,t_store_recs(v).assignment_action_id);
971 l_tot_earn := pay_balance_pkg.get_value(l_tot_earn_id,t_store_recs(v).assignment_action_id);
972 t_store_recs(v).supp_bal_value := l_supp_bal;
973 t_store_recs(v).tot_earn_value := l_tot_earn;
974 v:= v + 1;
975 END LOOP;
976
977 CLOSE csr_get_assact;
978
979 For x in t_store_recs.first..t_store_recs.last
980 LOOP
981
982 e := t_store_recs.last;
983
984
985 y := x + 1;
986
987 If y > e then
988 l_new := 'Y';
989
990 EXIT;
991 End If;
992
993 If t_store_recs(x).supp_bal_value = 0 then
994 y := x + 1;
995
996 If t_store_recs(y).supp_bal_value > 0 or l_emp_term_date < t_store_recs(x).date_earned then
997 l_termination := 'Y';
998
999 open csr_get_term_details_56(t_store_assact(j).assignment_id,l_effective_date);
1000 fetch csr_get_term_details_56 into l_act_term_date_56 , l_leav_reas_cd;
1001 close csr_get_term_details_56;
1002
1003 If l_leav_reas_cd is not null then
1004 OPEN csr_fetch_leav_reas(l_leav_reas_cd);
1005 FETCH csr_fetch_leav_reas into l_leav_reas;
1006 CLOSE csr_fetch_leav_reas;
1007 Else
1008 l_leav_reas := null;
1009 End If;
1010
1011
1012 If l_act_term_date_56 is not null then
1013 l_termination_date := l_act_term_date_56;
1014 Else
1015 l_termination_date := last_day(add_months(t_store_recs(x).date_earned,-1));
1016 End If;
1017
1018 EXIT;
1019 End if;
1020 Else
1021 y := x + 1;
1022
1023 If t_store_recs(y).supp_bal_value = 0 then
1024 y := y + 1;
1025 If y <= t_store_recs.last then
1026
1027 FOR z in y..t_store_recs.last
1028 LOOP
1029 l_temp_bal_value := t_store_recs(z).supp_bal_value;
1030 If l_temp_bal_value > 0 then
1031 l_rejoin := 'Y';
1032 EXIT;
1033 End If;
1034 END LOOP;
1035 Else
1036 l_new := 'Y';
1037 End If;
1038 Else
1039 l_new := 'N';
1040 l_rejoin := 'N';
1041 End If;
1042
1043
1044 If l_rejoin <>'Y' and l_new <> 'N' then
1045 l_new := 'Y';
1046 EXIT;
1047 Else
1048 EXIT;
1049 End If;
1050
1051 End If;
1052 END LOOP;
1053
1054 l_diff_exists := 'N';
1055
1056 For x in t_store_recs.first..t_store_recs.last
1057 LOOP
1058 If t_store_recs(x).supp_bal_value > 0 then
1059 l_commencement_date := t_store_recs(x).date_earned; /* COMMENCEMENT DATE*/
1060 End If;
1061
1062 y := x + 1;
1063
1064 If y > v-1 then
1065 If l_diff_exists <> 'Y' then
1066 l_last_sal_date := t_store_recs(x).date_earned; /* May require change */
1067 End If;
1068
1069 EXIT;
1070
1071 End If;
1072
1073 If (t_store_recs(x).tot_earn_value <> t_store_recs(y).tot_earn_value) and l_diff_exists <> 'Y' then
1074 l_diff_exists := 'Y';
1075 /*************************** change in last salary date logic , myay require change ***************************/
1076 l_last_sal_date := t_store_recs(x).date_earned; /* May require change */
1077 -- /*****/ l_last_sal_date := t_store_recs(y).date_earned; /* LAST SALARY DATE */
1078 End If;
1079
1080 END LOOP;
1081
1082 l_new_count := l_new_count+1;
1083
1084 OPEN csr_get_emp_name(t_store_assact(j).person_id,l_user_format);
1085 FETCH csr_get_emp_name INTO l_first_name,l_father_name,l_grand_name,l_last_name;
1086 CLOSE csr_get_emp_name;
1087
1088 OPEN csr_p_asg_data (t_store_assact(j).person_id,l_effective_date);
1089 FETCH csr_p_asg_data INTO l_insured_ssn;
1090 CLOSE csr_p_asg_data ;
1091
1092
1093 x := 1;
1094
1095 l_current_supp_contri := t_store_recs(x).supp_bal_value;
1096 l_fm_l_current_supp_contri := to_char(l_current_supp_contri,lg_format_mask);
1097
1098 /*fnd_file.put_line(fnd_file.log,' new '|| l_new || ' rejoin ' || l_rejoin || ' term ' || l_termination || ' ' ||t_store_assact(j).assignment_action_id); */
1099
1100 /** Populate the XML file **/
1101
1102 If l_termination ='Y' OR (l_rejoin = 'Y' and l_current_supp_contri <>0 ) OR (l_new = 'Y' and l_current_supp_contri <> 0) then
1103
1104 l_df_flag := 'Y';
1105
1106 vXMLTable(vCtr).TagName := 'employer_ssn';
1107 vXMLTable(vCtr).TagValue := l_employer_ssn;
1108 vctr := vctr + 1;
1109
1110 vXMLTable(vCtr).TagName := 'employer_name';
1111 vXMLTable(vCtr).TagValue := l_employer_name;
1112 vctr := vctr + 1;
1113
1114 vXMLTable(vCtr).TagName := 'insured_last';
1115 vXMLTable(vCtr).TagValue := l_last_name;
1116 vctr := vctr + 1;
1117
1118 vXMLTable(vCtr).TagName := 'insured_grand';
1119 vXMLTable(vCtr).TagValue := l_father_name;
1120 vctr := vctr + 1;
1121
1122 vXMLTable(vCtr).TagName := 'insured_father';
1123 vXMLTable(vCtr).TagValue := l_grand_name;
1124 vctr := vctr + 1;
1125
1126 vXMLTable(vCtr).TagName := 'insured_first';
1127 vXMLTable(vCtr).TagValue := l_first_name;
1128 vctr := vctr + 1;
1129
1130
1131
1132 vXMLTable(vCtr).TagName := 'insured_ssn';
1133 vXMLTable(vCtr).TagValue := l_insured_ssn;
1134 vctr := vctr + 1;
1135
1136 If l_termination = 'Y' then
1137 vXMLTable(vCtr).TagName := 'application_type';
1138 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TERM_56');
1139 vctr := vctr + 1;
1140 ElsIf l_rejoin = 'Y' then
1141 vXMLTable(vCtr).TagName := 'application_type';
1142 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','REJOIN_56');
1143 /* vXMLTable(vCtr).TagValue := 'Rejoining of Complementary Contribution'; */
1144 vctr := vctr + 1;
1145 ElsIf l_new = 'Y' then
1146 vXMLTable(vCtr).TagName := 'application_type';
1147 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','NEW_56');
1148 /* vXMLTable(vCtr).TagValue := 'Commencement of Complimentary Contribution'; */
1149 vctr := vctr + 1;
1150
1151 l_last_sal_date := t_store_assact(j).date_earned;
1152
1153 OPEN csr_get_hire_date_56 (t_store_assact(j).assignment_id , l_effective_date);
1154 FETCH csr_get_hire_date_56 into l_hire_date_56;
1155 CLOSE csr_get_hire_date_56;
1156
1157 If trunc(l_hire_date_56,'MM') = trunc(l_effective_date,'MM') then
1158 l_commencement_date := l_hire_date_56;
1159 End If;
1160
1161 End If;
1162
1163 vXMLTable(vCtr).TagName := 'commencement_day';
1164 vXMLTable(vCtr).TagValue := to_char(l_commencement_date,'DD');
1165 vctr := vctr + 1;
1166
1167 vXMLTable(vCtr).TagName := 'commencement_month';
1168 vXMLTable(vCtr).TagValue := to_char(l_commencement_date,'MM');
1169 vctr := vctr + 1;
1170
1171 vXMLTable(vCtr).TagName := 'commencement_year';
1172 vXMLTable(vCtr).TagValue := to_char(l_commencement_date,'YYYY');
1173 vctr := vctr + 1;
1174
1175 vXMLTable(vCtr).TagName := 'supplementary_dinars';
1176 vXMLTable(vCtr).TagValue := substr(l_fm_l_current_supp_contri,1,length(l_fm_l_current_supp_contri)-4);
1177 vctr := vctr + 1;
1178
1179 vXMLTable(vCtr).TagName := 'supplementary_fills';
1180 vXMLTable(vCtr).TagValue := substr(l_fm_l_current_supp_contri,length(l_fm_l_current_supp_contri)-2);
1181 vctr := vctr + 1;
1182
1183 vXMLTable(vCtr).TagName := 'total_dinars';
1184 vXMLTable(vCtr).TagValue := substr(l_fm_l_current_supp_contri,1,length(l_fm_l_current_supp_contri)-4);
1185 vctr := vctr + 1;
1186
1187 vXMLTable(vCtr).TagName := 'total_fills';
1188 vXMLTable(vCtr).TagValue := substr(l_fm_l_current_supp_contri,length(l_fm_l_current_supp_contri)-2);
1189 vctr := vctr + 1;
1190
1191 vXMLTable(vCtr).TagName := 'termination_day';
1192 vXMLTable(vCtr).TagValue := to_char(l_termination_date,'DD');
1193 vctr := vctr + 1;
1194
1195 vXMLTable(vCtr).TagName := 'termination_month';
1196 vXMLTable(vCtr).TagValue := to_char(l_termination_date,'MM');
1197 vctr := vctr + 1;
1198
1199 vXMLTable(vCtr).TagName := 'termination_year';
1200 vXMLTable(vCtr).TagValue := to_char(l_termination_date,'YYYY');
1201 vctr := vctr + 1;
1202
1203 vXMLTable(vCtr).TagName := 'termination_reason';
1204 vXMLTable(vCtr).TagValue := l_leav_reas;
1205 vctr := vctr + 1;
1206
1207 vXMLTable(vCtr).TagName := 'last_salary_day';
1208 vXMLTable(vCtr).TagValue := to_char(l_last_sal_date,'DD');
1209 vctr := vctr + 1;
1210
1211 vXMLTable(vCtr).TagName := 'last_salary_month';
1212 vXMLTable(vCtr).TagValue := to_char(l_last_sal_date,'MM');
1213 vctr := vctr + 1;
1214
1215 vXMLTable(vCtr).TagName := 'last_salary_year';
1216 vXMLTable(vCtr).TagValue := to_char(l_last_sal_date,'YYYY');
1217 vctr := vctr + 1;
1218
1219 vXMLTable(vCtr).TagName := 'PAGE-BK';
1220 vXMLTable(vCtr).TagValue := ' ';
1221 vctr := vctr + 1;
1222
1223 End If;
1224
1225 t_store_recs.DELETE;
1226
1227 j := j + 1;
1228
1229 IF j > i THEN
1230 l_new_processed := 1;
1231 EXIT;
1232 END IF;
1233 END LOOP;
1234
1235 hr_utility.set_location('Finished creating xml data for Procedure REPORT56 ',20);
1236
1237 If l_df_flag <> 'Y' then
1238 fnd_file.put_line(fnd_file.log,get_lookup_meaning('KW_FORM_LABELS','NDF'));
1239 End If;
1240
1241 WritetoCLOB ( l_xfdf_blob );
1242
1243
1244 /*EXCEPTION
1245 WHEN utl_file.invalid_path then
1246 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
1247 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1248 hr_utility.raise_error;
1249 --
1250 WHEN utl_file.invalid_mode then
1251 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
1252 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1253 hr_utility.raise_error;
1254 --
1255 WHEN utl_file.invalid_filehandle then
1256 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
1257 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1258 hr_utility.raise_error;
1259 --
1260 WHEN utl_file.invalid_operation then
1261 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
1262 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1263 hr_utility.raise_error;
1264 --
1265 WHEN utl_file.read_error then
1266 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
1267 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1268 hr_utility.raise_error;
1269 --
1270 WHEN others THEN
1271 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
1272 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
1273 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1274 hr_utility.raise_error;*/
1275
1276 END report56;
1277 -------------------------------------------------------------------------------------------
1278
1279
1280
1281 PROCEDURE report103
1282 (p_request_id NUMBER
1283 ,p_report VARCHAR2
1284 ,p_business_group_id NUMBER
1285 ,p_employer_id NUMBER
1286 ,p_effective_month VARCHAR2
1287 ,p_effective_year VARCHAR2
1288 ,p_employee_id NUMBER DEFAULT NULL
1289 ,l_xfdf_blob OUT NOCOPY BLOB
1290 )
1291 AS
1292
1293 l_effective_date DATE;
1294
1295 /*Cursor for fetching Local nationality for the BG */
1296 CURSOR csr_get_loc_nat IS
1297 SELECT org_information1
1298 FROM hr_organization_information
1299 WHERE organization_id = p_business_group_id
1300 AND org_information_context = 'KW_BG_DETAILS';
1301
1302 /* Cursor for fetching Defined balance ids from Org EIT */
1303 CURSOR csr_get_def_bal_ids (l_emp_id number) IS
1304 SELECT ORG_INFORMATION1
1305 FROM HR_ORGANIZATION_INFORMATION
1306 WHERE Organization_id = l_emp_id
1307 AND org_information_context = 'KW_SI_DETAILS';
1308
1309 /*Cursor for fetching Employer SSN*/
1310 CURSOR csr_employer_ssn IS
1311 SELECT LPAD(org_information4,9,'0')
1312 FROM hr_organization_information
1313 WHERE organization_id = p_employer_id
1314 AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
1315
1316 /*Cursor for fetching Employer Name*/
1317 CURSOR csr_employer_name IS
1318 SELECT name
1319 FROM hr_organization_units
1320 WHERE organization_id = p_employer_id;
1321
1322 /*Cursor for fetching defined balance id*/
1323 CURSOR csr_get_def_bal_id(p_user_name VARCHAR2) IS
1324 SELECT u.creator_id
1325 FROM ff_user_entities u,
1326 ff_database_items d
1327 WHERE d.user_name = p_user_name
1328 AND u.user_entity_id = d.user_entity_id
1329 AND u.legislation_code = 'KW'
1330 AND u.business_group_id is null
1331 AND u.creator_type = 'B';
1332
1333 /*Cursor for fetching list of new / terminated employees*/
1334 CURSOR csr_get_new_term_emp (l_date date, l_nat varchar2) IS
1335 SELECT distinct asg.person_id
1336 ,asg.assignment_id
1337 ,paa.assignment_action_id
1338 ,ppa.date_earned
1339 ,decode(trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM'),TRUNC(l_date, 'MM'),'Y','N') term_flag
1340 FROM per_assignments_f asg
1341 ,pay_assignment_actions paa
1342 ,pay_payroll_actions ppa
1343 ,hr_soft_coding_keyflex hscl
1344 ,per_periods_of_service pos
1345 ,per_people_f ppf
1346 WHERE asg.assignment_id = paa.assignment_id
1347 AND paa.payroll_action_id = ppa.payroll_action_id
1348 AND pos.period_of_service_id = asg.period_of_service_id
1349 AND ppa.action_type in ('R','Q')
1350 AND ppa.action_status = 'C'
1351 AND paa.action_status = 'C'
1352 AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
1353 AND ( trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') = TRUNC(l_date, 'MM')
1354 OR trunc(pos.date_start, 'MM') = trunc(l_date, 'MM') )
1355 AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
1356 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1357 AND hscl.segment1 = to_char(p_employer_id)
1358 AND ppf.person_id = asg.person_id
1359 AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
1360 AND ppf.nationality = l_nat;
1361 rec_get_emp csr_get_new_term_emp%ROWTYPE;
1362
1363 /*Cursor for fetching employee details */
1364 CURSOR csr_get_emp_details (p_person_id NUMBER,l_format VARCHAR2) IS
1365 SELECT decode(l_format,'L',PER_INFORMATION3,SUBSTR(first_name,1,60)),
1366 decode(l_format,'L',PER_INFORMATION6,SUBSTR(last_name,1,60)),
1367 decode(l_format,'L',PER_INFORMATION4,per_information1),
1368 decode(l_format,'L',PER_INFORMATION5,per_information2),
1369 sex,date_of_birth, pos.date_start , national_identifier , per_information9,per_information10,
1370 fnd_date.canonical_to_date(per_information11)
1371 FROM per_people_f ppf , per_periods_of_service pos
1372 WHERE ppf.person_id = p_person_id
1373 AND ppf.person_id = pos.person_id
1374 AND l_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1375 AND trunc(l_effective_date,'MM') between trunc(pos.date_start,'MM') and nvl(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'));
1376
1377 /* Cursor for fetching the person's assignment data */
1378 CURSOR csr_p_asg_data (l_person_id number,l_date date) IS
1379 SELECT hsck.segment2,job_id , fnd_date.canonical_to_date(hsck.segment3)
1380 FROM per_assignments_f paf,hr_soft_coding_keyflex hsck
1381 WHERE paf.person_id = l_person_id
1382 AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1383 AND hsck.segment1 = p_employer_id
1384 AND l_date between paf.effective_start_date and paf.effective_end_date;
1385
1386 CURSOR csr_p_gender (l_sex varchar2) IS
1387 SELECT hl.meaning
1388 FROM hr_lookups hl
1389 WHERE hl.lookup_type = 'SEX'
1390 AND hl.lookup_code = l_sex
1391 AND hl.enabled_flag = 'Y';
1392
1393 /* Cursor for fetching the job */
1394 CURSOR csr_p_job (l_job varchar2 ,l_date date) IS
1395 SELECT name
1396 FROM per_jobs
1397 WHERE job_id = l_job
1398 AND l_date between date_from and nvl(date_to, to_date('31-12-4712','dd-mm-yyyy'));
1399
1400 /* Cursor for fetching person's Address */
1401 CURSOR csr_p_address_data (l_person_id number,l_date date) IS
1402 SELECT substr(addr.ADDRESS_LINE1 || ' ' ||addr.address_line2,1,120)
1403 FROM per_addresses addr
1404 WHERE addr.person_id = l_person_id
1405 AND l_date between addr.date_from and nvl(addr.date_to,to_date('31-12-4712','dd-mm-yyyy'))
1406 AND addr.primary_flag = 'Y';
1407
1408 /* Cursor to fetch assignment_action_ids and date earned for the employee TO CAPTURE LAST SALARY DATE */
1409 CURSOR csr_get_assact_de (l_assignment_id number,l_date date) IS
1410 select ppa.date_earned, paa.assignment_action_id
1411 from pay_payroll_actions ppa, pay_assignment_actions paa
1412 Where paa.assignment_id = l_assignment_id
1413 and paa.payroll_action_id = ppa.payroll_action_id
1414 and ppa.action_type in ('R','Q')
1415 and ppa.action_status = 'C'
1416 and paa.action_status = 'C'
1417 and ppa.date_earned <= l_date
1418 order by ppa.date_earned DESC;
1419
1420 /* Cursor to fetch assignment_action_ids and date earned for the employee TO CAPTURE SOCIAL ALLOWANCE */
1421 CURSOR csr_get_assact_first (l_assignment_id number,l_date date) IS
1422 select ppa.date_earned, paa.assignment_action_id
1423 from pay_payroll_actions ppa, pay_assignment_actions paa
1424 Where paa.assignment_id = l_assignment_id
1425 and paa.payroll_action_id = ppa.payroll_action_id
1426 and ppa.action_type in ('R','Q')
1427 and ppa.action_status = 'C'
1428 and paa.action_status = 'C'
1429 and ppa.date_earned <= l_date
1430 order by ppa.date_earned ASC;
1431
1432 /* Cursor to fetch assignment_action_id corresponding to first_date_earned to calculate social allowance */
1433 CURSOR csr_get_assact_one (l_assignment_id number,l_date date) IS
1434 select paa.assignment_action_id
1435 from pay_payroll_actions ppa, pay_assignment_actions paa
1436 Where paa.assignment_id = l_assignment_id
1437 and paa.payroll_action_id = ppa.payroll_action_id
1438 and ppa.action_type in ('R','Q')
1439 and ppa.action_status = 'C'
1440 and paa.action_status = 'C'
1441 and trunc(ppa.date_earned,'MM') = trunc(l_date,'MM') ;
1442
1443 /* Cursor for fetching all deduction details UNION additional SI */
1444 CURSOR csr_get_ded_details (l_assignment_id number , l_assignment_action_id number, l_effective_date date) IS
1445 SELECT rrv.RESULT_VALUE val,pee.entry_information1 ref,pee.entry_information2 authority,pee.entry_information3 type,
1446 pee.entry_information4 debt,fnd_date.canonical_to_date(pee.entry_information5) start_d
1447 ,fnd_date.canonical_to_date(pee.entry_information6) end_d ,pet.element_type_id
1448 FROM pay_element_types_f pet,
1449 pay_element_entries_f pee,
1450 pay_run_results prr,
1451 pay_run_result_values rrv,
1452 pay_input_values_f piv
1453 WHERE rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
1454 AND prr.assignment_action_id = l_assignment_action_id
1455 AND prr.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
1456 AND piv.element_type_id = pet.element_type_id
1457 AND piv.name = 'Pay Value'
1458 AND rrv.input_value_id = piv.input_value_id
1459 AND pee.assignment_id = l_assignment_id
1460 AND TRUNC(l_effective_date,'MM') between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1461 AND TRUNC(l_effective_date,'MM') between trunc(piv.effective_start_date,'MM') and nvl(piv.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1462 AND pee.element_type_id = pet.element_type_id
1463 AND pee.entry_information3 is not null
1464 AND rrv.result_value is not null
1465 AND TRUNC(l_effective_date,'MM') between trunc(pet.effective_start_date,'MM') and nvl(pet.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'));
1466 /* Commented the code after Kuwait 167 report review
1467 UNION
1468 select distinct eev1.SCREEN_ENTRY_VALUE val, eev2.SCREEN_ENTRY_VALUE ref, eev3.SCREEN_ENTRY_VALUE authority, '72' type,
1469 eev4.SCREEN_ENTRY_VALUE debt,eev1.effective_start_date start_d,eev1.effective_end_date end_d, pet.element_type_id
1470 from pay_element_entry_values_f eev1,
1471 pay_element_types_f pet,
1472 pay_element_entries_f pee,
1473 pay_input_values_f piv1,
1474 pay_input_values_f piv2,
1475 pay_input_values_f piv3,
1476 pay_input_values_f piv4,
1477 pay_element_entry_values_f eev2,
1478 pay_element_entry_values_f eev3,
1479 pay_element_entry_values_f eev4
1480 where pet.element_name = 'Additional Social Insurance Information'
1481 AND pet.element_type_id = pee.element_type_id
1482 AND pee.assignment_id = l_assignment_id
1483 AND pee.element_entry_id = eev1.element_entry_id
1484 AND PAY_PAYWSMEE_PKG.PROCESSED(pee.element_entry_id,pee.original_entry_id , pet.processing_type , pee.entry_type, l_effective_date) = 'Y'
1485 AND piv1.element_type_id = pet.element_type_id
1486 AND piv1.name = 'Amount'
1487 AND eev1.input_value_id = piv1.input_value_id
1488 AND TRUNC(l_effective_date,'MM') between trunc(eev1.effective_start_date,'MM') and nvl(eev1.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1489 AND pee.element_entry_id = eev2.element_entry_id
1490 AND piv2.element_type_id = pet.element_type_id
1491 AND piv2.name = 'Reference Number'
1492 AND eev2.input_value_id = piv2.input_value_id
1493 AND TRUNC(l_effective_date,'MM') between trunc(eev2.effective_start_date,'MM') and nvl(eev2.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1494 AND pee.element_entry_id = eev3.element_entry_id
1495 AND piv3.element_type_id = pet.element_type_id
1496 AND piv3.name = 'Deduction Authority'
1497 AND eev3.input_value_id = piv3.input_value_id
1498 AND TRUNC(l_effective_date,'MM') between trunc(eev3.effective_start_date,'MM') and nvl(eev3.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1499 AND pee.element_entry_id = eev4.element_entry_id
1500 AND piv4.element_type_id = pet.element_type_id
1501 AND piv4.name = 'Total Deduction Amount'
1502 AND eev4.input_value_id = piv4.input_value_id
1503 AND TRUNC(l_effective_date,'MM') between trunc(eev4.effective_start_date,'MM') and nvl(eev4.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1504 AND TRUNC(l_effective_date,'MM') between trunc(pet.effective_start_date,'MM') and nvl(pet.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1505 AND TRUNC(l_effective_date,'MM') between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1506 AND TRUNC(l_effective_date,'MM') between trunc(piv1.effective_start_date,'MM') and nvl(piv1.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1507 AND TRUNC(l_effective_date,'MM') between trunc(piv2.effective_start_date,'MM') and nvl(piv2.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1508 AND TRUNC(l_effective_date,'MM') between trunc(piv3.effective_start_date,'MM') and nvl(piv3.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
1509 AND TRUNC(l_effective_date,'MM') between trunc(piv4.effective_start_date,'MM') and nvl(piv4.effective_end_date,to_date('31-12-4712','DD-MM-YYYY')); */
1510
1511 /* Cursor for fetching termination date and reason */
1512 CURSOR csr_get_term_det (l_person_id number, l_date date) IS
1513 SELECT pos.actual_termination_date , pos.leaving_reason
1514 FROM per_periods_of_service pos
1515 WHERE pos.person_id = l_person_id
1516 AND trunc(l_date,'MM') between trunc(pos.date_start,'MM') and nvl(actual_termination_date,to_date('31-12-4712','dd-mm-yyyy'));
1517
1518 /* Cursor to fetch termination reason meaning */
1519 CURSOR csr_get_term_meaning (l_code varchar2) IS
1520 SELECT hl.meaning
1521 FROM hr_lookups hl
1522 WHERE hl.lookup_type = 'LEAV_REAS'
1523 AND hl.lookup_code = l_code
1524 AND hl.enabled_flag = 'Y';
1525
1526 /* Cursor to fetch previous employer name */
1527 CURSOR csr_get_prev_emp_name (l_person_id number) IS
1528 SELECT employer_name
1529 FROM per_previous_employers
1530 WHERE person_id = l_person_id
1531 ORDER by previous_employer_id DESC;
1532
1533 /* Cursor to fetch deduction_type meaning */
1534 CURSOR csr_get_ded_meaning (L_TYPE VARCHAR2) IS
1535 SELECT hl.meaning
1536 FROM hr_lookups hl
1537 WHERE hl.lookup_type = 'KW_DEDUCTION_CODES'
1538 AND hl.lookup_code = l_type
1539 AND hl.enabled_flag = 'Y';
1540
1541 /* Cursor to fetch person id and termination flag when Employee is the parameter */
1542 CURSOR csr_get_per_term_data (l_asg_id number , l_date date , l_nat varchar2) IS
1543 SELECT ppf.person_id , paf.assignment_id , decode(trunc(pos.actual_termination_date,'MM'),trunc(l_date,'MM'),'Y','N') , decode(trunc(pos.date_start,'MM'),trunc(l_date,'MM'),'Y','N')
1544 FROM per_people_f ppf, per_periods_of_service pos , per_assignments_f paf
1545 WHERE paf.assignment_id = l_asg_id
1546 AND paf.person_id = ppf.person_id
1547 AND paf.period_of_service_id = pos.period_of_service_id
1548 AND paf.person_id = pos.person_id
1549 AND ppf.nationality = l_nat
1550 AND trunc(l_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
1551 AND trunc(l_date,'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
1552 AND trunc(l_date,'MM') between trunc(pos.date_start,'MM') and nvl(pos.actual_termination_date,to_date('31-12-4712','dd-mm-yyyy'));
1553
1554 /* Cursor to fetch assignment_action_id and date_earned if Employee is the parameter */
1555 CURSOR csr_get_emp_assact_data (l_assignment_id number,l_date date) IS
1556 select paa.assignment_action_id , ppa.date_earned
1557 from pay_payroll_actions ppa, pay_assignment_actions paa
1558 Where paa.assignment_id = l_assignment_id
1559 and paa.payroll_action_id = ppa.payroll_action_id
1560 and ppa.action_type in ('R','Q')
1561 and ppa.action_status = 'C'
1562 and paa.action_status = 'C'
1563 and trunc(ppa.date_earned,'MM') = trunc(l_date,'MM') ;
1564
1565 /* Cursor to fetch last sal date */
1566 CURSOR csr_get_last_sal_date(l_assignment_id number , l_date date) IS
1567 SELECT min(ppa.date_earned)
1568 FROM pay_payroll_actions ppa, pay_assignment_actions paa
1569 WHERE paa.assignment_id = l_assignment_id
1570 AND paa.payroll_action_id = ppa.payroll_action_id
1571 and ppa.action_type in ('R','Q')
1572 and ppa.action_status = 'C'
1573 and paa.action_status = 'C'
1574 and ppa.date_earned > l_date;
1575
1576 TYPE assact_rec IS RECORD
1577 (person_id NUMBER
1578 ,assignment_id NUMBER
1579 ,assignment_action_id NUMBER
1580 ,date_earned DATE
1581 ,term_flag VARCHAR2(1));
1582 TYPE t_assact_table IS TABLE OF assact_rec INDEX BY BINARY_INTEGER;
1583 t_store_assact t_assact_table;
1584
1585 l_input_date varchar2(30);
1586 l_employer_ssn varchar2(30);
1587 l_employer_name varchar2(240);
1588 l_si_id number;
1589 l_basic_social_id number;
1590 l_supplementary_social_id number;
1591 l_additional_social_id number;
1592 l_supp_base_id number;
1593 l_basic_arrears_id number;
1594 l_supp_arrears_id number;
1595 l_add_arrears_id number;
1596
1597 i number;
1598 l_new_processed number;
1599 l_all_processed number;
1600 j number;
1601
1602 l_first_name varchar2(240);
1603 l_gender varchar2(30);
1604 l_gender_meaning varchar2(30);
1605 l_per_address varchar2(2000);
1606 l_insured_ssn varchar2(30);
1607 l_job_cd varchar2(30);
1608 l_job_meaning varchar2(240);
1609 l_first_date_earned date;
1610 l_last_name varchar2(240);
1611 l_father_name varchar2(240);
1612 l_grandfather_name varchar2(240);
1613 l_subscription_date date;
1614 l_first_assact number;
1615 l_assact_one number;
1616 l_first_social number(15,3);
1617 l_curr_val number(15,3);
1618 l_temp_date date;
1619 l_temp_assact number;
1620 l_diff_exists varchar2(10);
1621
1622 l_dob date;
1623 l_subject_supp_val number(15,3);
1624 l_temp_val number(15,3);
1625 l_temp_per_date date;
1626 l_term_per_reason_cd varchar2(30);
1627 l_prev_emp_name varchar2(240);
1628 l_start_date date;
1629 l_diff_date date;
1630 l_term_per_date date;
1631 l_term_reason_cd varchar2(100);
1632 l_term_reason_meaning varchar2(100);
1633 l_application_type varchar2(100);
1634 l_new_count number;
1635 l_civil_id varchar2(100);
1636 l_nat_number varchar2(100);
1637 l_article_number varchar2(100);
1638 l_nat_date date;
1639 l_ded_count number;
1640 l_ded_val number(15,3);
1641 l_ded_val_v varchar2(30);
1642 l_ded_type varchar2(30);
1643 l_ded_meaning varchar2(240);
1644 l_ded_authority varchar2(240);
1645 l_ded_debt varchar2(100);
1646 l_ded_start date;
1647 l_ded_end date;
1648 l_ded_ele_id number;
1649
1650 l_fl_l_curr_val varchar2(100);
1651 l_fl_l_first_social varchar2(100);
1652 l_fl_l_subject_supp_val varchar2(100);
1653 l_emp_person_id number;
1654 l_emp_assact number;
1655 l_emp_date_earned date;
1656 l_emp_term_flag varchar2(10);
1657 l_emp_new_flag varchar2(10);
1658 l_social_id number;
1659 l_tot_earn_id number;
1660 l_ref_num varchar2(60);
1661 l_loc_nat varchar2(100);
1662 l_emp_asg_id number;
1663 l_tot_ded_count number := 0;
1664 l_csr_tot number := 0;
1665
1666 l_df_flag varchar2(10);
1667
1668 l_user_format VARCHAR2(80);
1669
1670 BEGIN
1671
1672 set_currency_mask(p_business_group_id);
1673
1674 l_input_date := '01-'||p_effective_month||'-'||p_effective_year;
1675 l_effective_date := last_day(to_date(l_input_date,'DD-MM-YYYY'));
1676 /*l_eff_term_date := to_date('28-'||to_char(l_effective_date,'MM-YYYY'),'DD-MM-YYYY');*/
1677 INSERT INTO fnd_sessions (session_id, effective_date)
1678 VALUES (userenv('sessionid'), l_effective_date);
1679
1680 -- To clear the PL/SQL Table values.
1681 vXMLTable.DELETE;
1682 vCtr := 1;
1683 hr_utility.set_location('Entering FORM1 ',10);
1684
1685 l_user_format := null;
1686 l_user_format := FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT');
1687
1688 /*Fetch Local Nationality */
1689 OPEN csr_get_loc_nat;
1690 FETCH csr_get_loc_nat into l_loc_nat;
1691 CLOSE csr_get_loc_nat;
1692
1693 OPEN csr_get_def_bal_ids(p_employer_id);
1694 FETCH csr_get_def_bal_ids into l_social_id;
1695 CLOSE csr_get_def_bal_ids;
1696
1697 OPEN csr_get_def_bal_id('TOTAL_EARNINGS_ASG_RUN');
1698 FETCH csr_get_def_bal_id into l_tot_earn_id;
1699 CLOSE csr_get_def_bal_id;
1700
1701 OPEN csr_get_def_bal_id('SUPPLEMENTARY_SOCIAL_INSURANCE_BASE_ASG_RUN');
1702 FETCH csr_get_def_bal_id into l_supp_base_id;
1703 CLOSE csr_get_def_bal_id;
1704
1705 /* OPEN csr_get_def_bal_id('SUBJECT_TO_SOCIAL_INSURANCE_ASG_RUN');
1706 FETCH csr_get_def_bal_id into l_si_id;
1707 CLOSE csr_get_def_bal_id;
1708
1709 OPEN csr_get_def_bal_id('EMPLOYEE_BASIC_SOCIAL_INSURANCE_ASG_RUN');
1710 FETCH csr_get_def_bal_id into l_basic_social_id ;
1711 CLOSE csr_get_def_bal_id;
1712
1713 OPEN csr_get_def_bal_id('EMPLOYEE_SUPPLEMENTARY_SOCIAL_INSURANCE_ASG_RUN');
1714 FETCH csr_get_def_bal_id into l_supplementary_social_id ;
1715 CLOSE csr_get_def_bal_id;
1716
1717 OPEN csr_get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_ASG_RUN');
1718 FETCH csr_get_def_bal_id into l_additional_social_id;
1719 CLOSE csr_get_def_bal_id;
1720
1721
1722 OPEN csr_get_def_bal_id('EMPLOYEE_BASIC_SOCIAL_INSURANCE_ARREARS_ASG_RUN');
1723 FETCH csr_get_def_bal_id into l_basic_arrears_id;
1724 CLOSE csr_get_def_bal_id;
1725
1726 OPEN csr_get_def_bal_id('EMPLOYEE_SUPPLEMENTARY_SOCIAL_INSURANCE_ARREARS_ASG_RUN');
1727 FETCH csr_get_def_bal_id into l_supp_arrears_id;
1728 CLOSE csr_get_def_bal_id;
1729
1730 OPEN csr_get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_ARREARS_ASG_RUN');
1731 FETCH csr_get_def_bal_id into l_add_arrears_id;
1732 CLOSE csr_get_def_bal_id;
1733 */
1734
1735 i := 0;
1736
1737 l_df_flag := 'U';
1738
1739 If p_employee_id is not null then
1740
1741
1742 OPEN csr_get_per_term_data (p_employee_id,l_effective_date, l_loc_nat);
1743 FETCH csr_get_per_term_data into l_emp_person_id,l_emp_asg_id, l_emp_term_flag, l_emp_new_flag;
1744 CLOSE csr_get_per_term_data;
1745
1746 OPEN csr_get_emp_assact_data (l_emp_asg_id , l_effective_date);
1747 FETCH csr_get_emp_assact_data into l_emp_assact, l_emp_date_earned;
1748 CLOSE csr_get_emp_assact_data;
1749
1750 If l_emp_person_id is not null then
1751 i := i + 1;
1752 t_store_assact(i).person_id := l_emp_person_id;
1753 t_store_assact(i).assignment_id := l_emp_asg_id;
1754 t_store_assact(i).assignment_action_id := l_emp_assact;
1755 t_store_assact(i).date_earned := l_emp_date_earned;
1756 t_store_assact(i).term_flag := l_emp_term_flag;
1757 End If;
1758
1759 Else
1760
1761 OPEN csr_get_new_term_emp(l_effective_date , l_loc_nat);
1762 LOOP
1763 FETCH csr_get_new_term_emp INTO rec_get_emp;
1764 EXIT WHEN csr_get_new_term_emp%NOTFOUND;
1765 i := i + 1;
1766 t_store_assact(i).person_id := rec_get_emp.person_id;
1767 t_store_assact(i).assignment_id := rec_get_emp.assignment_id;
1768 t_store_assact(i).assignment_action_id := rec_get_emp.assignment_action_id;
1769 t_store_assact(i).date_earned := rec_get_emp.date_earned;
1770 t_store_assact(i).term_flag := rec_get_emp.term_flag;
1771 END LOOP;
1772 CLOSE csr_get_new_term_emp;
1773 End If;
1774
1775
1776 IF i > 0 THEN
1777 l_new_processed := 0;
1778 ELSE
1779 l_new_processed := 1;
1780 END IF;
1781
1782 l_all_processed := 0;
1783
1784 j := 1;
1785
1786 WHILE j <= i LOOP
1787
1788
1789 /*Fetch Employer SSN*/
1790 OPEN csr_employer_ssn;
1791 FETCH csr_employer_ssn INTO l_employer_ssn;
1792 CLOSE csr_employer_ssn;
1793
1794 /*Fetch Employer Name*/
1795 OPEN csr_employer_name;
1796 FETCH csr_employer_name INTO l_employer_name;
1797 CLOSE csr_employer_name;
1798
1799 /* Reset grandfather name,father name,nat number etc */
1800
1801 l_father_name:=null;
1802 l_first_name := null;
1803 l_last_name := null;
1804 l_grandfather_name := null;
1805 l_civil_id := null;
1806 l_nat_number := null;
1807 l_per_address := null;
1808 l_article_number := null;
1809 l_nat_date := null;
1810
1811
1812 OPEN csr_get_emp_details(t_store_assact(j).person_id,l_user_format);
1813 FETCH csr_get_emp_details INTO l_first_name,l_last_name,l_father_name,l_grandfather_name,l_gender,l_dob,l_start_date,l_civil_id, l_nat_number,l_article_number,l_nat_date;
1814 CLOSE csr_get_emp_details;
1815
1816 OPEN csr_p_gender (l_gender);
1817 FETCH csr_p_gender INTO l_gender_meaning;
1818 CLOSE csr_p_gender;
1819
1820 OPEN csr_p_address_data (t_store_assact(j).person_id,l_effective_date);
1821 FETCH csr_p_address_data into l_per_address;
1822 CLOSE csr_p_address_data;
1823
1824 /* Reset job */
1825
1826 l_job_cd := null;
1827 l_subscription_date := null;
1828 l_insured_ssn := null;
1829 l_job_meaning := null;
1830
1831 OPEN csr_p_asg_data (t_store_assact(j).person_id,l_effective_date);
1832 FETCH csr_p_asg_data INTO l_insured_ssn,l_job_cd,l_subscription_date;
1833 CLOSE csr_p_asg_data ;
1834
1835 OPEN csr_p_job (l_job_cd , l_effective_date);
1836 FETCH csr_p_job INTO l_job_meaning;
1837 CLOSE csr_p_job;
1838
1839 OPEN csr_get_assact_first (t_store_assact(j).assignment_id,l_effective_date);
1840 FETCH csr_get_assact_first into l_first_date_earned, l_first_assact;
1841 CLOSE csr_get_assact_first;
1842
1843 If trunc(l_first_date_earned,'MM') <> trunc(l_effective_date,'YYYY') then
1844 If trunc(l_first_date_earned,'YYYY') < trunc(l_effective_date,'YYYY') then
1845 l_first_date_earned := trunc(l_effective_date,'YYYY');
1846 End If;
1847 End If;
1848
1849 l_assact_one := null;
1850
1851 /* Get the assact id corresponding to the first_assact_date calculated above */
1852 OPEN csr_get_assact_one (t_store_assact(j).assignment_id,l_first_date_earned);
1853 FETCH csr_get_assact_one into l_assact_one;
1854 CLOSE csr_get_assact_one;
1855
1856 /* Get Social allowance */
1857
1858 If l_social_id is not null then
1859 If l_assact_one is not null then
1860 l_first_social := pay_balance_pkg.get_value(l_social_id,l_assact_one);
1861 Else
1862 l_first_social := 0;
1863 End If;
1864 Else
1865 l_first_social := 0;
1866 End If;
1867
1868 /* Get current salary */
1869
1870 l_curr_val := pay_balance_pkg.get_value(l_tot_earn_id,t_store_assact(j).assignment_action_id);
1871
1872 /* Get amount subject to supplementary_social_insurance */
1873
1874 l_subject_supp_val := pay_balance_pkg.get_value(l_supp_base_id,t_store_assact(j).assignment_action_id);
1875
1876 /* Get Last salary date */
1877
1878 OPEN csr_get_assact_de (t_store_assact(j).assignment_id, l_effective_date);
1879 LOOP
1880
1881 FETCH csr_get_assact_de into l_temp_date , l_temp_assact;
1882 EXIT WHEN csr_get_assact_de%NOTFOUND;
1883 l_diff_exists := 'N';
1884
1885 l_temp_val := pay_balance_pkg.get_value(l_tot_earn_id,l_temp_assact);
1886
1887 If l_curr_val <> l_temp_val then
1888 l_diff_exists := 'Y';
1889 OPEN csr_get_last_sal_date(t_store_assact(j).assignment_id,l_temp_date);
1890 FETCH csr_get_last_sal_date into l_diff_date;
1891 CLOSE csr_get_last_sal_date;
1892
1893 /************************May need some change ****************************/
1894 -- l_diff_date := l_temp_date; /* LAST SALARY DATE EARNED */
1895 EXIT;
1896 End If;
1897 END LOOP;
1898
1899 CLOSE csr_get_assact_de;
1900
1901 If l_diff_exists <> 'Y' and l_diff_date is null then
1902 l_diff_date := l_temp_date;
1903 End If;
1904
1905 If t_store_assact(j).term_flag = 'Y' then
1906 OPEN csr_get_term_det (t_store_assact(j).person_id , l_effective_date);
1907 FETCH csr_get_term_det into l_term_per_date, l_term_per_reason_cd;
1908 CLOSE csr_get_term_det;
1909
1910 OPEN csr_get_term_meaning (l_term_per_reason_cd);
1911 FETCH csr_get_term_meaning INTO l_term_reason_meaning;
1912 CLOSE csr_get_term_meaning;
1913 Else
1914 l_term_per_date := null;
1915 l_term_reason_meaning := null;
1916 End If;
1917
1918 OPEN csr_get_prev_emp_name(t_store_assact(j).person_id);
1919 FETCH csr_get_prev_emp_name INTO l_prev_emp_name;
1920 CLOSE csr_get_prev_emp_name;
1921
1922
1923
1924 l_new_count := l_new_count+1;
1925
1926 l_fl_l_curr_val := to_char(l_curr_val,lg_format_mask);
1927 l_fl_l_first_social := to_char(l_first_social,lg_format_mask);
1928 l_fl_l_subject_supp_val := to_char(l_subject_supp_val,lg_format_mask);
1929
1930 /** Populate the XML file **/
1931
1932 If p_employee_id is not null and (l_emp_new_flag = 'N' and l_emp_term_flag = 'N') then
1933 EXIT;
1934 End If;
1935
1936
1937 OPEN csr_get_ded_details(t_store_assact(j).assignment_id,t_store_assact(j).assignment_action_id,l_effective_date);
1938 LOOP
1939 FETCH csr_get_ded_details into l_ded_val , l_ref_num ,l_ded_authority, l_ded_type , l_ded_debt , l_ded_start , l_ded_end , l_ded_ele_id;
1940 EXIT WHEN csr_get_ded_details%NOTFOUND;
1941 l_tot_ded_count := l_tot_ded_count + 1;
1942 END LOOP;
1943 CLOSE csr_get_ded_details;
1944
1945 l_ded_val := null;
1946 l_ref_num := null;
1947 l_ded_authority:= null;
1948 l_ded_type := null;
1949 l_ded_debt := null;
1950 l_ded_start := null;
1951 l_ded_end := null;
1952 l_ded_ele_id:= null;
1953 l_ded_val_v := null;
1954
1955 If t_store_assact(j).term_flag = 'Y' then
1956 /* l_application_type := 'Termination of service';*/ /******** To be taken from lookup ********/
1957 l_application_type := get_lookup_meaning('KW_FORM_LABELS','TERM_103');
1958 ElsIf t_store_assact(j).term_flag = 'N' AND l_insured_ssn is not null then
1959 /* l_application_type := 'Previously Subscripted';*/
1960 l_application_type := get_lookup_meaning('KW_FORM_LABELS','PREV_103');
1961 l_diff_date := t_store_assact(j).date_earned;
1962 Else
1963 /* l_application_type := 'Commencement of Subscription';*/
1964 l_application_type := get_lookup_meaning('KW_FORM_LABELS','NEW_103');
1965 l_diff_date := t_store_assact(j).date_earned;
1966 End If;
1967
1968
1969 l_df_flag := 'Y';
1970
1971 vXMLTable(vCtr).TagName := 'application_type';
1972 vXMLTable(vCtr).TagValue := l_application_type;
1973 vctr := vctr + 1;
1974
1975 vXMLTable(vCtr).TagName := 'insured_name_first';
1976 vXMLTable(vCtr).TagValue := l_first_name;
1977 vctr := vctr + 1;
1978
1979 vXMLTable(vCtr).TagName := 'insured_name_father';
1980 vXMLTable(vCtr).TagValue := l_father_name;
1981 vctr := vctr + 1;
1982
1983 vXMLTable(vCtr).TagName := 'insured_name_grandfather';
1984 vXMLTable(vCtr).TagValue := l_grandfather_name;
1985 vctr := vctr + 1;
1986
1987 vXMLTable(vCtr).TagName := 'insured_name_last';
1988 vXMLTable(vCtr).TagValue := l_last_name;
1989 vctr := vctr + 1;
1990
1991 vXMLTable(vCtr).TagName := 'address';
1992 vXMLTable(vCtr).TagValue := l_per_address;
1993 vctr := vctr + 1;
1994
1995 vXMLTable(vCtr).TagName := 'insured_ssn';
1996 vXMLTable(vCtr).TagValue := l_insured_ssn;
1997 vctr := vctr + 1;
1998
1999 vXMLTable(vCtr).TagName := 'dob_day';
2000 vXMLTable(vCtr).TagValue := to_char(l_dob,'DD');
2001 vctr := vctr + 1;
2002
2003 vXMLTable(vCtr).TagName := 'dob_month';
2004 vXMLTable(vCtr).TagValue := to_char(l_dob,'MM');
2005 vctr := vctr + 1;
2006
2007 vXMLTable(vCtr).TagName := 'dob_year';
2008 vXMLTable(vCtr).TagValue := to_char(l_dob,'YYYY');
2009 vctr := vctr + 1;
2010
2011 vXMLTable(vCtr).TagName := 'gender';
2012 vXMLTable(vCtr).TagValue := l_gender_meaning;
2013 vctr := vctr + 1;
2014
2015 vXMLTable(vCtr).TagName := 'nationality_number';
2016 vXMLTable(vCtr).TagValue := l_nat_number;
2017 vctr := vctr + 1;
2018
2019 vXMLTable(vCtr).TagName := 'article';
2020 vXMLTable(vCtr).TagValue := l_article_number;
2021 vctr := vctr + 1;
2022
2023 vXMLTable(vCtr).TagName := 'nat_date_day';
2024 vXMLTable(vCtr).TagValue := to_char(l_nat_date,'DD');
2025 vctr := vctr + 1;
2026
2027 vXMLTable(vCtr).TagName := 'nat_date_month';
2028 vXMLTable(vCtr).TagValue := to_char(l_nat_date,'MM');
2029 vctr := vctr + 1;
2030
2031 vXMLTable(vCtr).TagName := 'nat_date_year';
2032 vXMLTable(vCtr).TagValue := to_char(l_nat_date,'YYYY');
2033 vctr := vctr + 1;
2034
2035 vXMLTable(vCtr).TagName := 'civil_id';
2036 vXMLTable(vCtr).TagValue := l_civil_id;
2037 vctr := vctr + 1;
2038
2039 vXMLTable(vCtr).TagName := 'employer_name';
2040 vXMLTable(vCtr).TagValue := l_employer_name;
2041 vctr := vctr + 1;
2042
2043 vXMLTable(vCtr).TagName := 'employer_ssn';
2044 vXMLTable(vCtr).TagValue := l_employer_ssn;
2045 vctr := vctr + 1;
2046
2047 vXMLTable(vCtr).TagName := 'hire_date_day';
2048 vXMLTable(vCtr).TagValue := to_char(l_start_date,'DD');
2049 vctr := vctr + 1;
2050
2051 vXMLTable(vCtr).TagName := 'hire_date_month';
2052 vXMLTable(vCtr).TagValue := to_char(l_start_date,'MM');
2053 vctr := vctr + 1;
2054
2055 vXMLTable(vCtr).TagName := 'hire_date_year';
2056 vXMLTable(vCtr).TagValue := to_char(l_start_date,'YYYY');
2057 vctr := vctr + 1;
2058
2059 vXMLTable(vCtr).TagName := 'commencement_date_day';
2060 vXMLTable(vCtr).TagValue := to_char(l_subscription_date,'DD');
2061 vctr := vctr + 1;
2062
2063 vXMLTable(vCtr).TagName := 'commencement_date_month';
2064 vXMLTable(vCtr).TagValue := to_char(l_subscription_date,'MM');
2065 vctr := vctr + 1;
2066
2067 vXMLTable(vCtr).TagName := 'commencement_date_year';
2068 vXMLTable(vCtr).TagValue := to_char(l_subscription_date,'YYYY');
2069 vctr := vctr + 1;
2070
2071 vXMLTable(vCtr).TagName := 'job';
2072 vXMLTable(vCtr).TagValue := substr(l_job_meaning,1,30);
2073 vctr := vctr + 1;
2074
2075 vXMLTable(vCtr).TagName := 'total_salary_dinars';
2076 vXMLTable(vCtr).TagValue := substr(l_fl_l_curr_val,1,length(l_fl_l_curr_val)-4);
2077 vctr := vctr + 1;
2078
2079 vXMLTable(vCtr).TagName := 'total_salary_fills';
2080 vXMLTable(vCtr).TagValue := substr(l_fl_l_curr_val,length(l_fl_l_curr_val)-2);
2081 vctr := vctr + 1;
2082
2083 vXMLTable(vCtr).TagName := 'social_allowance_dinars';
2084 vXMLTable(vCtr).TagValue := substr(l_fl_l_first_social,1,length(l_fl_l_first_social)-4);
2085 vctr := vctr + 1;
2086
2087 vXMLTable(vCtr).TagName := 'social_allowance_fills';
2088 vXMLTable(vCtr).TagValue := substr(l_fl_l_first_social,length(l_fl_l_first_social)-2);
2089 vctr := vctr + 1;
2090
2091 vXMLTable(vCtr).TagName := 'subject_to_comp_dinars';
2092 /** commented vXMLTable(vCtr).TagValue := substr(l_fl_l_subject_supp_val,1,length(l_fl_l_subject_supp_val)-4);*/
2093 vXMLTable(vCtr).TagValue := ' ';
2094 vctr := vctr + 1;
2095
2096 vXMLTable(vCtr).TagName := 'subject_to_comp_fills';
2097 /**commented vXMLTable(vCtr).TagValue := substr(l_fl_l_subject_supp_val,length(l_fl_l_subject_supp_val)-2);*/
2098 vXMLTable(vCtr).TagValue := ' ';
2099 vctr := vctr + 1;
2100
2101 vXMLTable(vCtr).TagName := 'last_salary_date';
2102 vXMLTable(vCtr).TagValue := to_char(l_diff_date,'YYYY/MM/DD');
2103 vctr := vctr + 1;
2104
2105 vXMLTable(vCtr).TagName := 'term_date_day';
2106 vXMLTable(vCtr).TagValue := to_char(l_term_per_date,'DD');
2107 vctr := vctr + 1;
2108
2109 vXMLTable(vCtr).TagName := 'term_date_month';
2110 vXMLTable(vCtr).TagValue := to_char(l_term_per_date,'MM');
2111 vctr := vctr + 1;
2112
2113 vXMLTable(vCtr).TagName := 'term_date_year';
2114 vXMLTable(vCtr).TagValue := to_char(l_term_per_date,'YYYY');
2115 vctr := vctr + 1;
2116
2117 vXMLTable(vCtr).TagName := 'termination_reason';
2118 vXMLTable(vCtr).TagValue := l_term_reason_meaning;
2119 vctr := vctr + 1;
2120
2121 vXMLTable(vCtr).TagName := 'prev_employer';
2122 vXMLTable(vCtr).TagValue := l_prev_emp_name;
2123 vctr := vctr + 1;
2124
2125
2126 OPEN csr_get_ded_details(t_store_assact(j).assignment_id,t_store_assact(j).assignment_action_id,l_effective_date);
2127 LOOP
2128 l_ded_count := 1;
2129
2130 --OPEN csr_get_ded_details(t_store_assact(j).assignment_id,t_store_assact(j).assignment_action_id,l_effective_date);
2131 LOOP
2132 FETCH csr_get_ded_details into l_ded_val , l_ref_num ,l_ded_authority, l_ded_type , l_ded_debt , l_ded_start , l_ded_end , l_ded_ele_id;
2133 EXIT WHEN csr_get_ded_details%NOTFOUND;
2134
2135 l_csr_tot := l_csr_tot + 1;
2136
2137 OPEN csr_get_ded_meaning (l_ded_type);
2138 FETCH csr_get_ded_meaning into l_ded_meaning;
2139 CLOSE csr_get_ded_meaning;
2140
2141 vXMLTable(vCtr).TagName := 'ref_number_' || l_ded_count;
2142 vXMLTable(vCtr).TagValue := l_ref_num;
2143 vctr := vctr + 1;
2144
2145 vXMLTable(vCtr).TagName := 'deduction_authority_' || l_ded_count;
2146 vXMLTable(vCtr).TagValue := l_ded_authority;
2147 vctr := vctr + 1;
2148
2149 OPEN csr_get_ded_meaning(l_ded_type);
2150 FETCH csr_get_ded_meaning INTO l_ded_meaning;
2151 CLOSE csr_get_ded_meaning;
2152
2153 vXMLTable(vCtr).TagName := 'deduction_type_' || l_ded_count;
2154 vXMLTable(vCtr).TagValue := substr(l_ded_meaning,1,30);
2155 vctr := vctr + 1;
2156
2157 vXMLTable(vCtr).TagName := 'total_debt_' || l_ded_count;
2158 vXMLTable(vCtr).TagValue := l_ded_debt;
2159 vctr := vctr + 1;
2160
2161 vXMLTable(vCtr).TagName := 'monthly_installment_' || l_ded_count;
2162 vXMLTable(vCtr).TagValue := to_char(l_ded_val,lg_format_mask);
2163 vctr := vctr + 1;
2164
2165 vXMLTable(vCtr).TagName := 'deduction_start_date_' || l_ded_count;
2166 vXMLTable(vCtr).TagValue := l_ded_start;
2167 vctr := vctr + 1;
2168
2169 vXMLTable(vCtr).TagName := 'deduction_end_date_' || l_ded_count;
2170 vXMLTable(vCtr).TagValue := l_ded_end;
2171 vctr := vctr + 1;
2172
2173 l_ded_count := l_ded_count + 1;
2174
2175 If l_ded_count = 5 then
2176
2177 vXMLTable(vCtr).TagName := 'PAGE-BK';
2178 vXMLTable(vCtr).TagValue := ' ';
2179 vctr := vctr + 1;
2180 EXIT;
2181 End If;
2182
2183 END LOOP;
2184
2185
2186 If (l_ded_count = 5 and l_ded_count <= l_tot_ded_count) then
2187 If t_store_assact(j).term_flag = 'Y' then
2188 l_application_type := 'Termination of service'; /******** To be taken from lookup ********/
2189 ElsIf t_store_assact(j).term_flag = 'N' AND l_insured_ssn is not null then
2190 l_application_type := 'Previously Subscripted';
2191 l_diff_date := t_store_assact(j).date_earned;
2192 Else
2193 l_application_type := 'Commencement of Subscription';
2194 l_diff_date := t_store_assact(j).date_earned;
2195 End If;
2196
2197 vXMLTable(vCtr).TagName := 'application_type';
2198 vXMLTable(vCtr).TagValue := l_application_type;
2199 vctr := vctr + 1;
2200
2201 vXMLTable(vCtr).TagName := 'insured_name_first';
2202 vXMLTable(vCtr).TagValue := l_first_name;
2203 vctr := vctr + 1;
2204
2205 vXMLTable(vCtr).TagName := 'insured_name_father';
2206 vXMLTable(vCtr).TagValue := l_father_name;
2207 vctr := vctr + 1;
2208
2209 vXMLTable(vCtr).TagName := 'insured_name_grandfather';
2210 vXMLTable(vCtr).TagValue := l_grandfather_name;
2211 vctr := vctr + 1;
2212
2213 vXMLTable(vCtr).TagName := 'insured_name_last';
2214 vXMLTable(vCtr).TagValue := l_last_name;
2215 vctr := vctr + 1;
2216
2217 vXMLTable(vCtr).TagName := 'address';
2218 vXMLTable(vCtr).TagValue := l_per_address;
2219 vctr := vctr + 1;
2220
2221 vXMLTable(vCtr).TagName := 'insured_ssn';
2222 vXMLTable(vCtr).TagValue := l_insured_ssn;
2223 vctr := vctr + 1;
2224
2225 vXMLTable(vCtr).TagName := 'dob_day';
2226 vXMLTable(vCtr).TagValue := to_char(l_dob,'DD');
2227 vctr := vctr + 1;
2228
2229 vXMLTable(vCtr).TagName := 'dob_month';
2230 vXMLTable(vCtr).TagValue := to_char(l_dob,'MM');
2231 vctr := vctr + 1;
2232
2233 vXMLTable(vCtr).TagName := 'dob_year';
2234 vXMLTable(vCtr).TagValue := to_char(l_dob,'YYYY');
2235 vctr := vctr + 1;
2236
2237 vXMLTable(vCtr).TagName := 'gender';
2238 vXMLTable(vCtr).TagValue := l_gender_meaning;
2239 vctr := vctr + 1;
2240
2241 vXMLTable(vCtr).TagName := 'nationality_number';
2242 vXMLTable(vCtr).TagValue := l_nat_number;
2243 vctr := vctr + 1;
2244
2245 vXMLTable(vCtr).TagName := 'article';
2246 vXMLTable(vCtr).TagValue := l_article_number;
2247 vctr := vctr + 1;
2248
2249 vXMLTable(vCtr).TagName := 'nat_date_day';
2250 vXMLTable(vCtr).TagValue := to_char(l_nat_date,'DD');
2251 vctr := vctr + 1;
2252
2253 vXMLTable(vCtr).TagName := 'nat_date_month';
2254 vXMLTable(vCtr).TagValue := to_char(l_nat_date,'MM');
2255 vctr := vctr + 1;
2256
2257 vXMLTable(vCtr).TagName := 'nat_date_year';
2258 vXMLTable(vCtr).TagValue := to_char(l_nat_date,'YYYY');
2259 vctr := vctr + 1;
2260
2261 vXMLTable(vCtr).TagName := 'civil_id';
2262 vXMLTable(vCtr).TagValue := l_civil_id;
2263 vctr := vctr + 1;
2264
2265 vXMLTable(vCtr).TagName := 'employer_name';
2266 vXMLTable(vCtr).TagValue := l_employer_name;
2267 vctr := vctr + 1;
2268
2269 vXMLTable(vCtr).TagName := 'employer_ssn';
2270 vXMLTable(vCtr).TagValue := l_employer_ssn;
2271 vctr := vctr + 1;
2272
2273 vXMLTable(vCtr).TagName := 'hire_date_day';
2274 vXMLTable(vCtr).TagValue := to_char(l_start_date,'DD');
2275 vctr := vctr + 1;
2276
2277 vXMLTable(vCtr).TagName := 'hire_date_month';
2278 vXMLTable(vCtr).TagValue := to_char(l_start_date,'MM');
2279 vctr := vctr + 1;
2280
2281 vXMLTable(vCtr).TagName := 'hire_date_year';
2282 vXMLTable(vCtr).TagValue := to_char(l_start_date,'YYYY');
2283 vctr := vctr + 1;
2284
2285 vXMLTable(vCtr).TagName := 'commencement_date_day';
2286 vXMLTable(vCtr).TagValue := to_char(l_subscription_date,'DD');
2287 vctr := vctr + 1;
2288
2289 vXMLTable(vCtr).TagName := 'commencement_date_month';
2290 vXMLTable(vCtr).TagValue := to_char(l_subscription_date,'MM');
2291 vctr := vctr + 1;
2292
2293 vXMLTable(vCtr).TagName := 'commencement_date_year';
2294 vXMLTable(vCtr).TagValue := to_char(l_subscription_date,'YYYY');
2295 vctr := vctr + 1;
2296
2297 vXMLTable(vCtr).TagName := 'job';
2298 vXMLTable(vCtr).TagValue := substr(l_job_meaning,1,30);
2299 vctr := vctr + 1;
2300
2301 vXMLTable(vCtr).TagName := 'total_salary_dinars';
2302 vXMLTable(vCtr).TagValue := substr(l_fl_l_curr_val,1,length(l_fl_l_curr_val)-4);
2303 vctr := vctr + 1;
2304
2305 vXMLTable(vCtr).TagName := 'total_salary_fills';
2306 vXMLTable(vCtr).TagValue := substr(l_fl_l_curr_val,length(l_fl_l_curr_val)-2);
2307 vctr := vctr + 1;
2308
2309 vXMLTable(vCtr).TagName := 'social_allowance_dinars';
2310 vXMLTable(vCtr).TagValue := substr(l_fl_l_first_social,1,length(l_fl_l_first_social)-4);
2311 vctr := vctr + 1;
2312
2313 vXMLTable(vCtr).TagName := 'social_allowance_fills';
2314 vXMLTable(vCtr).TagValue := substr(l_fl_l_first_social,length(l_fl_l_first_social)-2);
2315 vctr := vctr + 1;
2316
2317 vXMLTable(vCtr).TagName := 'subject_to_comp_dinars';
2318 /** commented vXMLTable(vCtr).TagValue := substr(l_fl_l_subject_supp_val,1,length(l_fl_l_subject_supp_val)-4);*/
2319 vXMLTable(vCtr).TagValue := ' ';
2320 vctr := vctr + 1;
2321
2322 vXMLTable(vCtr).TagName := 'subject_to_comp_fills';
2323 /**commented vXMLTable(vCtr).TagValue := substr(l_fl_l_subject_supp_val,length(l_fl_l_subject_supp_val)-2);*/
2324 vXMLTable(vCtr).TagValue := ' ';
2325 vctr := vctr + 1;
2326
2327 vXMLTable(vCtr).TagName := 'last_salary_date';
2328 vXMLTable(vCtr).TagValue := to_char(l_diff_date,'YYYY/MM/DD');
2329 vctr := vctr + 1;
2330
2331 vXMLTable(vCtr).TagName := 'term_date_day';
2332 vXMLTable(vCtr).TagValue := to_char(l_term_per_date,'DD');
2333 vctr := vctr + 1;
2334
2335 vXMLTable(vCtr).TagName := 'term_date_month';
2336 vXMLTable(vCtr).TagValue := to_char(l_term_per_date,'MM');
2337 vctr := vctr + 1;
2338
2339 vXMLTable(vCtr).TagName := 'term_date_year';
2340 vXMLTable(vCtr).TagValue := to_char(l_term_per_date,'YYYY');
2341 vctr := vctr + 1;
2342
2343 vXMLTable(vCtr).TagName := 'termination_reason';
2344 vXMLTable(vCtr).TagValue := l_term_reason_meaning;
2345 vctr := vctr + 1;
2346
2347 vXMLTable(vCtr).TagName := 'prev_employer';
2348 vXMLTable(vCtr).TagValue := l_prev_emp_name;
2349 vctr := vctr + 1;
2350 End If;
2351
2352 /*
2353 If l_ded_count = 5 then
2354 If l_csr_tot <> l_tot_ded_count then
2355 vXMLTable(vCtr).TagName := 'PAGE-BK';
2356 vXMLTable(vCtr).TagValue := ' ';
2357 vctr := vctr + 1;
2358 End If;
2359 End If;
2360 */
2361
2362 If l_ded_count <> 5 then
2363 EXIT;
2364 End If;
2365
2366 If l_ded_count = 5 then
2367 If l_csr_tot = l_tot_ded_count then
2368 EXIT;
2369 End If;
2370 End If;
2371
2372 EXIT WHEN csr_get_ded_details%NOTFOUND;
2373
2374 END LOOP;
2375
2376 CLOSE csr_get_ded_details;
2377
2378 vXMLTable(vCtr).TagName := 'PAGE-BK';
2379 vXMLTable(vCtr).TagValue := ' ';
2380 vctr := vctr + 1;
2381
2382
2383
2384
2385 j := j + 1;
2386
2387 IF j > i THEN
2388 l_new_processed := 1;
2389 EXIT;
2390 END IF;
2391 END LOOP;
2392
2393 hr_utility.set_location('Finished creating xml data for Procedure REPORT103 ',20);
2394
2395 If l_df_flag <> 'Y' then
2396 fnd_file.put_line(fnd_file.log,get_lookup_meaning('KW_FORM_LABELS','NDF'));
2397 End If;
2398
2399 WritetoCLOB ( l_xfdf_blob );
2400
2401 /*EXCEPTION
2402 WHEN utl_file.invalid_path then
2403 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
2404 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2405 hr_utility.raise_error;
2406 --
2407 WHEN utl_file.invalid_mode then
2408 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
2409 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2410 hr_utility.raise_error;
2411 --
2412 WHEN utl_file.invalid_filehandle then
2413 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
2414 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2415 hr_utility.raise_error;
2416 --
2417 WHEN utl_file.invalid_operation then
2418 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
2419 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2420 hr_utility.raise_error;
2421 --
2422 WHEN utl_file.read_error then
2423 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
2424 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2425 hr_utility.raise_error;
2426 --
2427 WHEN others THEN
2428 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
2429 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
2430 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2431 hr_utility.raise_error;*/
2432
2433 END report103;
2434 -------------------------------------------------------------------------------------------
2435
2436 PROCEDURE WritetoCLOB
2437 (p_xfdf_blob out nocopy blob)
2438 IS
2439 l_xfdf_string clob;
2440 l_str1 varchar2(1000);
2441 l_str2 varchar2(20);
2442 l_str3 varchar2(20);
2443 l_str4 varchar2(20);
2444 l_str5 varchar2(20);
2445 l_str6 varchar2(30);
2446 l_str7 varchar2(1000);
2447 l_str8 varchar2(240);
2448 l_str9 varchar2(240);
2449 BEGIN
2450 hr_utility.set_location('Entered Procedure Write to clob ',100);
2451 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
2452 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
2453 <fields> ' ;
2454 l_str2 := '<field name="';
2455 l_str3 := '">';
2456 l_str4 := '<value>' ;
2457 l_str5 := '</value> </field>' ;
2458 l_str6 := '</fields> </xfdf>';
2459 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
2460 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
2461 <fields>
2462 </fields> </xfdf>';
2463 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
2464 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
2465 if vXMLTable.COUNT > 0 then
2466 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
2467 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
2468 l_str8 := vXMLTable(ctr_table).TagName;
2469 l_str9 := vXMLTable(ctr_table).TagValue;
2470 if (l_str9 is not null) then
2471 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
2472 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
2473 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
2474 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
2475 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
2476 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
2477 elsif (l_str9 is null and l_str8 is not null) then
2478 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
2479 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
2480 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
2481 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
2482 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
2483 else
2484 null;
2485 end if;
2486 END LOOP;
2487 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
2488 else
2489 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
2490 end if;
2491 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
2492 clob_to_blob(l_xfdf_string,p_xfdf_blob);
2493 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
2494 --return p_xfdf_blob;
2495 EXCEPTION
2496 WHEN OTHERS then
2497 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
2498 HR_UTILITY.RAISE_ERROR;
2499 END WritetoCLOB;
2500 ----------------------------------------------------------------
2501 Procedure clob_to_blob
2502 (p_clob clob,
2503 p_blob IN OUT NOCOPY Blob)
2504 is
2505 l_length_clob number;
2506 l_offset pls_integer;
2507 l_varchar_buffer varchar2(32767);
2508 l_raw_buffer raw(32767);
2509 l_buffer_len number;
2510 l_chunk_len number;
2511 l_blob blob;
2512 g_nls_db_char varchar2(60);
2513
2514 l_raw_buffer_len pls_integer;
2515 l_blob_offset pls_integer := 1;
2516
2517 begin
2518 l_buffer_len := 20000;
2519 hr_utility.set_location('Entered Procedure clob to blob',120);
2520 select userenv('LANGUAGE') into g_nls_db_char from dual;
2521 l_length_clob := dbms_lob.getlength(p_clob);
2522 l_offset := 1;
2523 while l_length_clob > 0 loop
2524 hr_utility.trace('l_length_clob '|| l_length_clob);
2525 if l_length_clob < l_buffer_len then
2526 l_chunk_len := l_length_clob;
2527 else
2528 l_chunk_len := l_buffer_len;
2529 end if;
2530 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
2531 --l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
2532 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char);
2533 /*fnd_file.put_line(fnd_file.log,l_varchar_buffer);*/
2534 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char));
2535
2536 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
2537 --dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
2538 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
2539 l_blob_offset := l_blob_offset + l_raw_buffer_len;
2540
2541 l_offset := l_offset + l_chunk_len;
2542 l_length_clob := l_length_clob - l_chunk_len;
2543 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
2544 end loop;
2545 hr_utility.set_location('Finished Procedure clob to blob ',130);
2546 end clob_to_blob;
2547
2548 ------------------------------------------------------------------
2549 Procedure fetch_pdf_blob
2550 (p_report in varchar2,
2551 p_pdf_blob OUT NOCOPY blob)
2552 IS
2553 BEGIN
2554 IF (p_report='REPORT55') THEN
2555 Select file_data
2556 Into p_pdf_blob
2557 From fnd_lobs
2558 Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_R55_ar_KW.pdf');
2559 ELSIF (p_report='REPORT56') THEN
2560 Select file_data
2561 Into p_pdf_blob
2562 From fnd_lobs
2563 Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_R56_ar_KW.pdf');
2564 ELSIF (p_report='REPORT103') THEN
2565 Select file_data
2566 Into p_pdf_blob
2567 From fnd_lobs
2568 Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_R103_ar_KW.pdf');
2569 END IF;
2570 EXCEPTION
2571 when no_data_found then
2572 null;
2573 END fetch_pdf_blob;
2574
2575 -------------------------------------------------------------------
2576
2577
2578 PROCEDURE WritetoXML (
2579 p_request_id in number,
2580 p_report in varchar2,
2581 p_output_fname out nocopy varchar2)
2582 IS
2583 p_l_fp UTL_FILE.FILE_TYPE;
2584 l_audit_log_dir varchar2(500);
2585 l_file_name varchar2(50);
2586 l_check_flag number;
2587 BEGIN
2588 l_audit_log_dir := '/sqlcom/outbound';
2589 /*Msg in the temorary table*/
2590 --insert into tstmsg values('Entered the procedure WritetoXML.');
2591 -----------------------------------------------------------------------------
2592 -- Writing into XML File
2593 -----------------------------------------------------------------------------
2594 -- Assigning the File name.
2595 l_file_name := to_char(p_request_id) || '.xml';
2596 -- Getting the Util file directory name.mostly it'll be /sqlcom/outbound )
2597 BEGIN
2598 SELECT value
2599 INTO l_audit_log_dir
2600 FROM v$parameter
2601 WHERE LOWER(name) = 'utl_file_dir';
2602 -- Check whether more than one util file directory is found
2603 IF INSTR(l_audit_log_dir,',') > 0 THEN
2604 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
2605 END IF;
2606 EXCEPTION
2607 when no_data_found then
2608 null;
2609 END;
2610 -- Find out whether the OS is MS or Unix based
2611 -- If it's greater than 0, it's unix based environment
2612 IF INSTR(l_audit_log_dir,'/') > 0 THEN
2613 p_output_fname := l_audit_log_dir || '/' || l_file_name;
2614 ELSE
2615 p_output_fname := l_audit_log_dir || '\' || l_file_name;
2616 END IF;
2617 -- getting Agency name
2618 p_l_fp := utl_file.fopen(l_audit_log_dir,l_file_name,'A');
2619 utl_file.put_line(p_l_fp,'<?xml version="1.0" encoding="UTF-8"?>');
2620 utl_file.put_line(p_l_fp,'<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">');
2621 -- Writing from and to dates
2622 utl_file.put_line(p_l_fp,'<fields>');
2623 -- Write the header fields to XML File.
2624 --WriteXMLvalues(p_l_fp,'P0_from_date',to_char(p_from_date,'dd') || ' ' || trim(to_char(p_from_date,'Month')) || ' ' || to_char(p_from_date,'yyyy') );
2625 --WriteXMLvalues(p_l_fp,'P0_to_date',to_char(p_to_date,'dd') || ' ' ||to_char(p_to_date,'Month') || ' ' || to_char(p_to_date,'yyyy') );
2626 -- Loop through PL/SQL Table and write the values into the XML File.
2627 -- Need to try FORALL instead of FOR
2628 IF vXMLTable.count >0 then
2629
2630 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
2631 WriteXMLvalues(p_l_fp,vXMLTable(ctr_table).TagName ,vXMLTable(ctr_table).TagValue);
2632 END LOOP;
2633 END IF;
2634 -- Write the end tag and close the XML File.
2635 utl_file.put_line(p_l_fp,'</fields>');
2636 utl_file.put_line(p_l_fp,'</xfdf>');
2637 utl_file.fclose(p_l_fp);
2638 /*Msg in the temorary table*/
2639 --insert into tstmsg values('Leaving the procedure WritetoXML.');
2640 END WritetoXML;
2641 ---------------------------------------------------------------------
2642 PROCEDURE WriteXMLvalues( p_l_fp utl_file.file_type,p_tagname IN VARCHAR2, p_value IN VARCHAR2) IS
2643 BEGIN
2644 -- Writing XML Tag and values to XML File
2645 -- utl_file.put_line(p_l_fp,'<' || p_tagname || '>' || p_value || '</' || p_tagname || '>' );
2646 -- New Format XFDF
2647 utl_file.put_line(p_l_fp,'<field name="' || p_tagname || '">');
2648 utl_file.put_line(p_l_fp,'<value>' || p_value || '</value>' );
2649 utl_file.put_line(p_l_fp,'</field>');
2650 END WriteXMLvalues;
2651
2652
2653
2654 END pay_kw_annual_reports;