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