[Home] [Help]
PACKAGE BODY: APPS.PAY_KW_MONTHLY_REPORTS
Source
1 PACKAGE BODY pay_kw_monthly_reports AS
2 /* $Header: pykwmonr.pkb 120.15.12020000.7 2012/12/17 09:27:22 bkeshary ship $ */
3
4 lg_format_mask varchar2(50);
5 g_report_old VARCHAR2(10);
6 PROCEDURE set_currency_mask
7 (p_business_group_id IN NUMBER) IS
8 /* Cursor to retrieve Currency */
9 CURSOR csr_currency IS
10 SELECT org_information10
11 FROM hr_organization_information
12 WHERE organization_id = p_business_group_id
13 AND org_information_context = 'Business Group Information';
14 l_currency VARCHAR2(40);
15 BEGIN
16 OPEN csr_currency;
17 FETCH csr_currency into l_currency;
18 CLOSE csr_currency;
19 lg_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
20 END set_currency_mask;
21
22 PROCEDURE report166
23 (p_request_id NUMBER
24 ,p_report VARCHAR2
25 ,p_business_group_id NUMBER
26 ,p_employer_id NUMBER
27 ,p_effective_month VARCHAR2
28 ,p_effective_year VARCHAR2
29 ,p_arrears NUMBER DEFAULT 0
30 ,p_arrears2 NUMBER DEFAULT 0 /* changes in 166 - Aug 2012 Bug 14704605 */
31 ,p_arrears3 NUMBER DEFAULT 0
32 ,p_not_in_rep_167 NUMBER DEFAULT 0
33 ,p_add_supp_insu_1997 NUMBER DEFAULT 0
34 ,l_xfdf_blob OUT NOCOPY BLOB
35 )
36 AS
37
38 l_local_nationality VARCHAR2(80);
39
40 /*Cursor for fetching Employer SSN*/
41 CURSOR csr_employer_ssn IS
42 SELECT LPAD(org_information4,9,'0')
43 FROM hr_organization_information
44 WHERE organization_id = p_employer_id
45 AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
46
47 /*Cursor for fetching Employer Name*/
48 CURSOR csr_employer_name IS
49 SELECT name
50 FROM hr_organization_units
51 WHERE organization_id = p_employer_id;
52
53 /*Cursor for fetching defined balance id*/
54 CURSOR csr_get_def_bal_id(p_user_name VARCHAR2) IS
55 SELECT u.creator_id
56 FROM ff_user_entities u,
57 ff_database_items d
58 WHERE d.user_name = p_user_name
59 AND u.user_entity_id = d.user_entity_id
60 AND u.legislation_code = 'KW'
61 AND u.business_group_id is null
62 AND u.creator_type = 'B';
63
64 l_employer_name hr_organization_units.name%TYPE;
65 l_employer_ssn NUMBER;
66 l_basic_si_base_id NUMBER;
67 l_supp_si_base_id NUMBER;
68 l_ee_basic_si_id NUMBER;
69 l_er_basic_si_id NUMBER;
70 l_ee_supp_si_id NUMBER;
71 l_er_supp_si_id NUMBER;
72 l_add_si_id NUMBER;
73 l_pf_id NUMBER;
74 l_ee_pf_id NUMBER;
75 l_er_pf_id NUMBER;
76 l_basic_si_base_val NUMBER;
77 l_supp_si_base_val NUMBER;
78 l_ee_basic_si_val NUMBER;
79 l_er_basic_si_val NUMBER;
80 l_ee_supp_si_val NUMBER;
81 l_er_supp_si_val NUMBER;
82 l_add_si_val NUMBER;
83 l_pf_val NUMBER;
84 l_ee_pf_val NUMBER;
85 l_er_pf_val NUMBER;
86 l_input_date VARCHAR2(30);
87 l_effective_date DATE;
88 l_curr_date VARCHAR2(30);
89 l_file_name1 VARCHAR2(50);
90 l_total_amount NUMBER;
91 l_ee_b NUMBER;
92 l_ee_s NUMBER;
93 l_er_b NUMBER;
94 l_er_s NUMBER;
95 l_tot_pf_val NUMBER;
96 l_tot_basic_val NUMBER;
97 l_tot_supp_val NUMBER;
98
99 l_fm_total_amount VARCHAR2(50);
100 l_fm_ee_b VARCHAR2(50);
101 l_fm_ee_s VARCHAR2(50);
102 l_fm_er_b VARCHAR2(50);
103 l_fm_er_s VARCHAR2(50);
104 l_fm_basic_si_base_val VARCHAR2(50);
105 l_fm_supp_si_base_val VARCHAR2(50);
106 l_fm_ee_basic_si_val VARCHAR2(50);
107 l_fm_er_basic_si_val VARCHAR2(50);
108 l_fm_ee_supp_si_val VARCHAR2(50);
109 l_fm_er_supp_si_val VARCHAR2(50);
110 l_fm_add_si_val VARCHAR2(50);
111 l_fm_ee_pf_val VARCHAR2(50);
112 l_fm_er_pf_val VARCHAR2(50);
113 l_fm_arrears VARCHAR2(50);
114 l_fm_arrears2 VARCHAR2(50);
115 l_fm_arrears3 VARCHAR2(50);
116 l_fm_arrears4 VARCHAR2(50);
117 l_fm_arrears5 VARCHAR2(50);
118 l_fm_tot_pf_val VARCHAR2(50);
119 l_fm_tot_basic_val VARCHAR2(50);
120 l_fm_tot_supp_val VARCHAR2(50);
121 l_fm_add_si_val_tot VARCHAR2(50);
122 l_fm_arrears4_tot VARCHAR2(50);
123 l_fm_arrears5_tot VARCHAR2(50);
124
125 l_effective_month VARCHAR2(50);
126
127 l_ee_b_arr_id NUMBER;
128 l_ee_s_arr_id NUMBER;
129 l_add_arr_id NUMBER;
130
131 BEGIN
132 l_fm_total_amount := NULL;
133 l_fm_ee_b := NULL;
134 l_fm_ee_s := NULL;
135 l_fm_er_b := NULL;
136 l_fm_er_s := NULL;
137 l_fm_basic_si_base_val := NULL;
138 l_fm_supp_si_base_val := NULL;
139 l_fm_ee_basic_si_val := NULL;
140 l_fm_er_basic_si_val := NULL;
141 l_fm_ee_supp_si_val := NULL;
142 l_fm_er_supp_si_val := NULL;
143 l_fm_add_si_val := NULL;
144 l_fm_ee_pf_val := NULL;
145 l_fm_er_pf_val := NULL;
146 l_fm_arrears := NULL;
147 l_fm_arrears2 := NULL;
148 l_fm_arrears3 := NULL;
149 l_fm_arrears4 := NULL;
150 l_fm_arrears5 := NULL;
151 l_fm_tot_pf_val := NULL;
152 l_fm_tot_basic_val := NULL;
153 l_fm_tot_supp_val := NULL;
154 l_fm_add_si_val_tot := NULL;
155 l_fm_arrears4_tot := NULL;
156 l_fm_arrears5_tot := NULL;
157
158
159 set_currency_mask(p_business_group_id);
160 l_input_date := '01-'||p_effective_month||'-'||p_effective_year;
161 l_effective_date := last_day(to_date(l_input_date,'DD-MM-YYYY'));
162 /*l_eff_term_date := to_date('28-'||to_char(l_effective_date,'MM-YYYY'),'DD-MM-YYYY');*/
163 INSERT INTO fnd_sessions (session_id, effective_date)
164 VALUES (userenv('sessionid'), l_effective_date);
165
166 l_local_nationality := NULL;
167 BEGIN
168 SELECT org_information1
169 INTO l_local_nationality
170 FROM hr_organization_information
171 WHERE org_information_context = 'KW_BG_DETAILS'
172 AND organization_id = p_business_group_id;
173 EXCEPTION
174 WHEN OTHERS THEN
175 l_local_nationality := NULL;
176 END;
177
178 -- To clear the PL/SQL Table values.
179 vXMLTable.DELETE;
180 vCtr := 1;
181 hr_utility.set_location('Entering report166 ',10);
182
183 l_effective_month := hr_general.decode_lookup('KW_GREGORIAN_MONTH', p_effective_month);
184
185 vXMLTable(vCtr).TagName := 'R166-MONTH';
186 vXMLTable(vCtr).TagValue := l_effective_month;
187 vctr := vctr + 1;
188
189 vXMLTable(vCtr).TagName := 'R166-YEAR';
190 vXMLTable(vCtr).TagValue := p_effective_year;
191 vctr := vctr + 1;
192
193 vXMLTable(vCtr).TagName := 'R166-G-YYYY';
194 vXMLTable(vCtr).TagValue := TO_CHAR(sysdate,'YYYY');
195 vctr := vctr + 1;
196
197 vXMLTable(vCtr).TagName := 'R166-G-MM';
198 vXMLTable(vCtr).TagValue := TO_CHAR(sysdate,'MM');
199 vctr := vctr + 1;
200
201 vXMLTable(vCtr).TagName := 'R166-G-DD';
202 vXMLTable(vCtr).TagValue := TO_CHAR(sysdate,'DD');
203 vctr := vctr + 1;
204
205 /*Fetch Employer SSN*/
206 OPEN csr_employer_ssn;
207 FETCH csr_employer_ssn INTO l_employer_ssn;
208 CLOSE csr_employer_ssn;
209
210 vXMLTable(vCtr).TagName := 'R166-SSN-1';
211 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,1,1);
212 vctr := vctr + 1;
213 vXMLTable(vCtr).TagName := 'R166-SSN-2';
214 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,2,1);
215 vctr := vctr + 1;
216 vXMLTable(vCtr).TagName := 'R166-SSN-3';
217 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,3,1);
218 vctr := vctr + 1;
219 vXMLTable(vCtr).TagName := 'R166-SSN-4';
220 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,4,1);
221 vctr := vctr + 1;
222 vXMLTable(vCtr).TagName := 'R166-SSN-5';
223 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,5,1);
224 vctr := vctr + 1;
225 vXMLTable(vCtr).TagName := 'R166-SSN-6';
226 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,6,1);
227 vctr := vctr + 1;
228 vXMLTable(vCtr).TagName := 'R166-SSN-7';
229 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,7,1);
230 vctr := vctr + 1;
231 vXMLTable(vCtr).TagName := 'R166-SSN-8';
232 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,8,1);
233 vctr := vctr + 1;
234 vXMLTable(vCtr).TagName := 'R166-SSN-9';
235 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,9,1);
236 vctr := vctr + 1;
237
238 /*Fetch Employer Name*/
239 OPEN csr_employer_name;
240 FETCH csr_employer_name INTO l_employer_name;
241 CLOSE csr_employer_name;
242
243 vXMLTable(vCtr).TagName := 'R166-NAME';
244 vXMLTable(vCtr).TagValue := l_employer_name;
245 vctr := vctr + 1;
246
247 l_basic_si_base_id := 0;
248 l_supp_si_base_id := 0;
249 l_ee_basic_si_id := 0;
250 l_er_basic_si_id := 0;
251 l_ee_supp_si_id := 0;
252 l_er_supp_si_id := 0;
253 l_add_si_id := 0;
254 l_ee_pf_id := 0;
255 l_er_pf_id := 0;
256
257 l_ee_b_arr_id := 0;
258 l_ee_s_arr_id := 0;
259 l_add_arr_id := 0;
260
261 /*Fetch Defined Balance Id*/
262 OPEN csr_get_def_bal_id('BASIC_SOCIAL_INSURANCE_BASE_EMPLOYER_MONTH');
263 FETCH csr_get_def_bal_id INTO l_basic_si_base_id;
264 CLOSE csr_get_def_bal_id;
265
266 OPEN csr_get_def_bal_id('SUPPLEMENTARY_SOCIAL_INSURANCE_BASE_EMPLOYER_MONTH');
267 FETCH csr_get_def_bal_id INTO l_supp_si_base_id;
268 CLOSE csr_get_def_bal_id;
269
270 OPEN csr_get_def_bal_id('EMPLOYEE_BASIC_SOCIAL_INSURANCE_EMPLOYER_MONTH');
271 FETCH csr_get_def_bal_id INTO l_ee_basic_si_id;
272 CLOSE csr_get_def_bal_id;
273
274 OPEN csr_get_def_bal_id('EMPLOYER_BASIC_SOCIAL_INSURANCE_EMPLOYER_MONTH');
275 FETCH csr_get_def_bal_id INTO l_er_basic_si_id;
276 CLOSE csr_get_def_bal_id;
277
278 OPEN csr_get_def_bal_id('EMPLOYEE_SUPPLEMENTARY_SOCIAL_INSURANCE_EMPLOYER_MONTH');
279 FETCH csr_get_def_bal_id INTO l_ee_supp_si_id;
280 CLOSE csr_get_def_bal_id;
281
282 OPEN csr_get_def_bal_id('EMPLOYER_SUPPLEMENTARY_SOCIAL_INSURANCE_EMPLOYER_MONTH');
283 FETCH csr_get_def_bal_id INTO l_er_supp_si_id;
284 CLOSE csr_get_def_bal_id;
285
286 OPEN csr_get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_EMPLOYER_MONTH');
287 FETCH csr_get_def_bal_id INTO l_add_si_id;
288 CLOSE csr_get_def_bal_id;
289
290 OPEN csr_get_def_bal_id('EMPLOYEE_PENSION_FUND_EMPLOYER_MONTH');
291 FETCH csr_get_def_bal_id INTO l_ee_pf_id;
292 CLOSE csr_get_def_bal_id;
293
294 OPEN csr_get_def_bal_id('EMPLOYER_PENSION_FUND_EMPLOYER_MONTH');
295 FETCH csr_get_def_bal_id INTO l_er_pf_id;
296 CLOSE csr_get_def_bal_id;
297
298 OPEN csr_get_def_bal_id('EMPLOYEE_BASIC_SOCIAL_INSURANCE_ARREARS_EMPLOYER_MONTH');
299 FETCH csr_get_def_bal_id INTO l_ee_b_arr_id;
300 CLOSE csr_get_def_bal_id;
301
302 OPEN csr_get_def_bal_id('EMPLOYEE_SUPPLEMENTARY_SOCIAL_INSURANCE_ARREARS_EMPLOYER_MONTH');
303 FETCH csr_get_def_bal_id INTO l_ee_s_arr_id;
304 CLOSE csr_get_def_bal_id;
305
306 OPEN csr_get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_ARREARS_EMPLOYER_MONTH');
307 FETCH csr_get_def_bal_id INTO l_add_arr_id;
308 CLOSE csr_get_def_bal_id;
309
310
311
312 /*Set Contexts and then fetch the balance values*/
313 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(l_effective_date));
314 pay_balance_pkg.set_context('TAX_UNIT_ID', p_employer_id);
315 l_basic_si_base_val := pay_balance_pkg.get_value(l_basic_si_base_id,NULL);
316 l_fm_basic_si_base_val := to_char(l_basic_si_base_val,lg_format_mask);
317
318 vXMLTable(vCtr).TagName := 'R166-B-BASE-D';
319 --vXMLTable(vCtr).TagValue := TRUNC(l_basic_si_base_val);
320 vXMLTable(vCtr).TagValue := substr(l_fm_basic_si_base_val,1,length(l_fm_basic_si_base_val)-4);
321 vctr := vctr + 1;
322 vXMLTable(vCtr).TagName := 'R166-B-BASE-F';
323 --vXMLTable(vCtr).TagValue := l_basic_si_base_val - TRUNC(l_basic_si_base_val);
324 vXMLTable(vCtr).TagValue := substr(l_fm_basic_si_base_val,length(l_fm_basic_si_base_val)-2);
325 vctr := vctr + 1;
326
327 l_supp_si_base_val := pay_balance_pkg.get_value(l_supp_si_base_id,NULL);
328 l_fm_supp_si_base_val := to_char(l_supp_si_base_val,lg_format_mask);
329 vXMLTable(vCtr).TagName := 'R166-S-BASE-D';
330 --vXMLTable(vCtr).TagValue := TRUNC(l_supp_si_base_val);
331 vXMLTable(vCtr).TagValue := substr(l_fm_supp_si_base_val,1,length(l_fm_supp_si_base_val)-4);
332 vctr := vctr + 1;
333 vXMLTable(vCtr).TagName := 'R166-S-BASE-F';
334 --vXMLTable(vCtr).TagValue := l_supp_si_base_val - TRUNC(l_supp_si_base_val);
335 vXMLTable(vCtr).TagValue := substr(l_fm_supp_si_base_val,length(l_fm_supp_si_base_val)-2);
336 vctr := vctr + 1;
337
338 l_ee_pf_val := pay_balance_pkg.get_value(l_ee_pf_id,NULL);
339 l_fm_ee_pf_val := to_char(l_ee_pf_val,lg_format_mask);
340 vXMLTable(vCtr).TagName := 'R166-EE-PF-D';
341 --vXMLTable(vCtr).TagValue := TRUNC(l_ee_pf_val);
342 vXMLTable(vCtr).TagValue := substr(l_fm_ee_pf_val,1,length(l_fm_ee_pf_val)-4);
343 vctr := vctr + 1;
344 vXMLTable(vCtr).TagName := 'R166-EE-PF-F';
345 --vXMLTable(vCtr).TagValue := l_ee_pf_val - TRUNC(l_ee_pf_val);
346 vXMLTable(vCtr).TagValue := substr(l_fm_ee_pf_val,length(l_fm_ee_pf_val)-2);
347 vctr := vctr + 1;
348
349 l_er_pf_val := pay_balance_pkg.get_value(l_er_pf_id,NULL);
350 l_fm_er_pf_val := to_char(l_er_pf_val,lg_format_mask);
351 vXMLTable(vCtr).TagName := 'R166-ER-PF-D';
352 --vXMLTable(vCtr).TagValue := TRUNC(l_er_pf_val);
353 vXMLTable(vCtr).TagValue := substr(l_fm_er_pf_val,1,length(l_fm_er_pf_val)-4);
354 vctr := vctr + 1;
355 vXMLTable(vCtr).TagName := 'R166-ER-PF-F';
356 --vXMLTable(vCtr).TagValue := l_er_pf_val - TRUNC(l_er_pf_val);
357 vXMLTable(vCtr).TagValue := substr(l_fm_er_pf_val,length(l_fm_er_pf_val)-2);
358 vctr := vctr + 1;
359
360 /* added for Oct 2012 report 166 changes Bug 14704605 */
361
362 l_tot_pf_val := l_ee_pf_val + l_er_pf_val;
363 l_fm_tot_pf_val := to_char(l_tot_pf_val,lg_format_mask);
364 vXMLTable(vCtr).TagName := 'R166-TOT-PF-D';
365 --vXMLTable(vCtr).TagValue := TRUNC(l_ee_pf_val);
366 vXMLTable(vCtr).TagValue := substr(l_fm_tot_pf_val,1,length(l_fm_tot_pf_val)-4);
367 vctr := vctr + 1;
368 vXMLTable(vCtr).TagName := 'R166-TOT-PF-F';
369 --vXMLTable(vCtr).TagValue := l_ee_pf_val - TRUNC(l_ee_pf_val);
370 vXMLTable(vCtr).TagValue := substr(l_fm_tot_pf_val,length(l_fm_tot_pf_val)-2);
371 vctr := vctr + 1;
372
373 /* end 2012 */
374
375 /*l_ee_basic_si_val := pay_balance_pkg.get_value(l_ee_basic_si_id,NULL);
376 l_ee_supp_si_val := pay_balance_pkg.get_value(l_ee_supp_si_id,NULL);*/
377
378 l_ee_basic_si_val := pay_balance_pkg.get_value(l_ee_basic_si_id,NULL) + pay_balance_pkg.get_value(l_ee_b_arr_id,NULL);
379 l_ee_supp_si_val := pay_balance_pkg.get_value(l_ee_supp_si_id,NULL) + pay_balance_pkg.get_value(l_ee_s_arr_id,NULL);
380
381 IF l_ee_basic_si_val <> 0 THEN
382 l_ee_b := l_ee_basic_si_val - ((l_ee_basic_si_val/(l_ee_basic_si_val+l_ee_supp_si_val))*l_ee_pf_val);
383 ELSE
384 l_ee_b := 0;
385 END IF;
386 IF l_ee_supp_si_val <> 0 THEN
387 l_ee_s := l_ee_supp_si_val - ((l_ee_supp_si_val/(l_ee_basic_si_val+l_ee_supp_si_val))*l_ee_pf_val);
388 ELSE
389 l_ee_s := 0;
390 END IF;
391
392 l_fm_ee_b := to_char(l_ee_b,lg_format_mask);
393 vXMLTable(vCtr).TagName := 'R166-EE-B-D';
394 --vXMLTable(vCtr).TagValue := TRUNC(l_ee_b);
395 vXMLTable(vCtr).TagValue := substr(l_fm_ee_b,1,length(l_fm_ee_b)-4);
396 vctr := vctr + 1;
397 vXMLTable(vCtr).TagName := 'R166-EE-B-F';
398 --vXMLTable(vCtr).TagValue := (l_ee_b) - TRUNC(l_ee_b);
399 vXMLTable(vCtr).TagValue := substr(l_fm_ee_b,length(l_fm_ee_b)-2);
400 vctr := vctr + 1;
401
402 l_fm_ee_s := to_char(l_ee_s,lg_format_mask);
403 vXMLTable(vCtr).TagName := 'R166-EE-S-D';
404 --vXMLTable(vCtr).TagValue := TRUNC(l_ee_s);
405 vXMLTable(vCtr).TagValue := substr(l_fm_ee_s,1,length(l_fm_ee_s)-4);
406 vctr := vctr + 1;
407 vXMLTable(vCtr).TagName := 'R166-EE-S-F';
408 --vXMLTable(vCtr).TagValue := l_ee_s - TRUNC(l_ee_s);
409 vXMLTable(vCtr).TagValue := substr(l_fm_ee_s,length(l_fm_ee_s)-2);
410 vctr := vctr + 1;
411
412 l_er_basic_si_val := pay_balance_pkg.get_value(l_er_basic_si_id,NULL);
413 l_er_supp_si_val := pay_balance_pkg.get_value(l_er_supp_si_id,NULL);
414
415 IF l_er_basic_si_val <> 0 THEN
416 l_er_b := l_er_basic_si_val - ((l_er_basic_si_val/(l_er_basic_si_val+l_er_supp_si_val))*l_er_pf_val);
417 ELSE
418 l_er_b := 0;
419 END IF;
420 IF l_er_supp_si_val <> 0 THEN
421 l_er_s := l_er_supp_si_val - ((l_er_supp_si_val/(l_er_basic_si_val+l_er_supp_si_val))*l_er_pf_val);
422 ELSE
423 l_er_s := 0;
424 END IF;
425
426 l_fm_er_b := to_char(l_er_b,lg_format_mask);
427 vXMLTable(vCtr).TagName := 'R166-ER-B-D';
428 --vXMLTable(vCtr).TagValue := TRUNC(l_er_b);
429 vXMLTable(vCtr).TagValue := substr(l_fm_er_b,1,length(l_fm_er_b)-4);
430 vctr := vctr + 1;
431 vXMLTable(vCtr).TagName := 'R166-ER-B-F';
432 --vXMLTable(vCtr).TagValue := l_er_b - TRUNC(l_er_b);
433 vXMLTable(vCtr).TagValue := substr(l_fm_er_b,length(l_fm_er_b)-2);
434 vctr := vctr + 1;
435
436 l_fm_er_s := to_char(l_er_s,lg_format_mask);
437 vXMLTable(vCtr).TagName := 'R166-ER-S-D';
438 --vXMLTable(vCtr).TagValue := TRUNC(l_er_s);
439 vXMLTable(vCtr).TagValue := substr(l_fm_er_s,1,length(l_fm_er_s)-4);
440 vctr := vctr + 1;
441 vXMLTable(vCtr).TagName := 'R166-ER-S-F';
442 --vXMLTable(vCtr).TagValue := l_er_s - TRUNC(l_er_s);
443 vXMLTable(vCtr).TagValue := substr(l_fm_er_s,length(l_fm_er_s)-2);
444 vctr := vctr + 1;
445
446 /* added for Oct 2012 report 166 changes Bug 14704605 */
447
448 l_tot_basic_val := l_ee_b + l_er_b ;
449 l_fm_tot_basic_val := to_char(l_tot_basic_val,lg_format_mask);
450 vXMLTable(vCtr).TagName := 'R166-TOT-BASIC-D';
451 --vXMLTable(vCtr).TagValue := TRUNC(l_ee_pf_val);
452 vXMLTable(vCtr).TagValue := substr(l_fm_tot_basic_val,1,length(l_fm_tot_basic_val)-4);
453 vctr := vctr + 1;
454 vXMLTable(vCtr).TagName := 'R166-TOT-BASIC-F';
455 --vXMLTable(vCtr).TagValue := l_ee_pf_val - TRUNC(l_ee_pf_val);
456 vXMLTable(vCtr).TagValue := substr(l_fm_tot_basic_val,length(l_fm_tot_basic_val)-2);
457 vctr := vctr + 1;
458
459 l_tot_supp_val := l_ee_s + l_er_s ;
460 l_fm_tot_supp_val := to_char(l_tot_supp_val,lg_format_mask);
461 vXMLTable(vCtr).TagName := 'R166-TOT-SUPP-D';
462 --vXMLTable(vCtr).TagValue := TRUNC(l_ee_pf_val);
463 vXMLTable(vCtr).TagValue := substr(l_fm_tot_supp_val,1,length(l_fm_tot_supp_val)-4);
464 vctr := vctr + 1;
465 vXMLTable(vCtr).TagName := 'R166-TOT-SUPP-F';
466 --vXMLTable(vCtr).TagValue := l_ee_pf_val - TRUNC(l_ee_pf_val);
467 vXMLTable(vCtr).TagValue := substr(l_fm_tot_supp_val,length(l_fm_tot_supp_val)-2);
468 vctr := vctr + 1;
469
470 /* end 2012 */
471
472 l_add_si_val := pay_balance_pkg.get_value(l_add_si_id,NULL) + pay_balance_pkg.get_value(l_add_arr_id,NULL);
473
474
475 /*Code added to sum the total of Report167 as per the enhancement to Report167*/
476 DECLARE
477 CURSOR csr_get_assacts IS
478 SELECT distinct asg.assignment_id
479 ,paa.assignment_action_id
480 FROM per_all_assignments_f asg /*per_assignments_f asg*/
481 ,pay_assignment_actions paa
482 ,pay_payroll_actions ppa
483 ,hr_soft_coding_keyflex hscl
484 ,per_all_people_f ppf /*per_people_f ppf*/
485 WHERE asg.assignment_id = paa.assignment_id
486 AND asg.person_id = ppf.person_id
487 AND ppf.nationality = l_local_nationality
488 AND paa.payroll_action_id = ppa.payroll_action_id
489 AND ppa.action_type in ('R','Q')
490 AND ppa.action_status = 'C'
491 AND paa.action_status IN ('C','S') --10375683
492 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
493 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
494 AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
495 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
496 AND hscl.segment1 = to_char(p_employer_id);
497 rec_get_assacts csr_get_assacts%ROWTYPE;
498 l_deduction_amt NUMBER;
499 l_tot_deduction NUMBER;
500 BEGIN
501 l_deduction_amt := 0;
502 l_tot_deduction := 0;
503 OPEN csr_get_assacts;
504 LOOP
505 FETCH csr_get_assacts INTO rec_get_assacts;
506 EXIT WHEN csr_get_assacts%NOTFOUND;
507 l_deduction_amt := 0;
508 SELECT NVL(SUM(rrv.RESULT_VALUE),0)
509 INTO l_deduction_amt
510 FROM pay_element_entries_f pee
511 ,pay_run_results prr
512 ,pay_run_result_values rrv
513 ,pay_input_values_f piv
514 WHERE rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
515 AND prr.assignment_action_id = rec_get_assacts.assignment_action_id
516 AND prr.element_entry_id = pee.element_entry_id
517 AND pee.assignment_id = rec_get_assacts.assignment_id
518 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'))
519 AND pee.entry_information3 is not null
520 AND pee.entry_information3 IN ('71','65','72','999','82','73','85')
521 AND rrv.result_value IS NOT NULL
522 AND rrv.input_value_id = piv.input_value_id
523 AND piv.name = 'Pay Value'
524 AND prr.element_type_id = piv.element_type_id
525 AND pee.element_type_id = piv.element_type_id
526 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'));
527 l_tot_deduction := l_tot_deduction + l_deduction_amt;
528 END LOOP;
529 CLOSE csr_get_assacts;
530 IF p_effective_year >= '2006' THEN
531 l_add_si_val := 0;
532 ELSE
533 l_tot_deduction := 0;
534 END IF;
535 l_add_si_val := l_add_si_val + l_tot_deduction;
536 END;
537
538 l_fm_add_si_val := to_char(l_add_si_val,lg_format_mask);
539 vXMLTable(vCtr).TagName := 'R166-A-S-D';
540 --vXMLTable(vCtr).TagValue := TRUNC(l_add_si_val);
541 vXMLTable(vCtr).TagValue := substr(l_fm_add_si_val,1,length(l_fm_add_si_val)-4);
542 vctr := vctr + 1;
543 vXMLTable(vCtr).TagName := 'R166-A-S-F';
544 --vXMLTable(vCtr).TagValue := l_add_si_val - TRUNC(l_add_si_val);
545 vXMLTable(vCtr).TagValue := substr(l_fm_add_si_val,length(l_fm_add_si_val)-2);
546 vctr := vctr + 1;
547
548 l_fm_arrears := to_char(p_arrears,lg_format_mask);
549 vXMLTable(vCtr).TagName := 'R166-OTH-D';
550 --vXMLTable(vCtr).TagValue := TRUNC(p_arrears);
551 vXMLTable(vCtr).TagValue := substr(l_fm_arrears,1,length(l_fm_arrears)-4);
552 vctr := vctr + 1;
553 vXMLTable(vCtr).TagName := 'R166-OTH-F';
554 --vXMLTable(vCtr).TagValue := p_arrears - TRUNC(p_arrears);
555 vXMLTable(vCtr).TagValue := substr(l_fm_arrears,length(l_fm_arrears)-2);
556 vctr := vctr + 1;
557
558 /* added 2012 */
559 l_fm_add_si_val_tot := to_char(l_add_si_val,lg_format_mask);
560 vXMLTable(vCtr).TagName := 'R166-A-S-D-TOT';
561 --vXMLTable(vCtr).TagValue := TRUNC(l_add_si_val);
562 vXMLTable(vCtr).TagValue := substr(l_fm_add_si_val_tot,1,length(l_fm_add_si_val_tot)-4);
563 vctr := vctr + 1;
564 vXMLTable(vCtr).TagName := 'R166-A-S-F-TOT';
565 --vXMLTable(vCtr).TagValue := l_add_si_val - TRUNC(l_add_si_val);
566 vXMLTable(vCtr).TagValue := substr(l_fm_add_si_val_tot,length(l_fm_add_si_val_tot)-2);
567 vctr := vctr + 1;
568 /* added 2012 */
569
570 /* following added for Oct 2012 166 report changes Bug 14704605 */
571 -- ,p_arrears2 NUMBER DEFAULT 0 /* changes in 166 - Aug 2012 */
572 -- ,p_arrears3 NUMBER DEFAULT 0
573 -- ,p_not_in_rep_167 NUMBER DEFAULT 0
574 -- ,p_add_supp_insu_1997 NUMBER DEFAULT 0
575
576 l_fm_arrears2 := to_char(p_arrears2,lg_format_mask);
577 vXMLTable(vCtr).TagName := 'R166-OTH-2-D';
578 --vXMLTable(vCtr).TagValue := TRUNC(p_arrears);
579 vXMLTable(vCtr).TagValue := substr(l_fm_arrears2,1,length(l_fm_arrears2)-4);
580 vctr := vctr + 1;
581 vXMLTable(vCtr).TagName := 'R166-OTH-2-F';
582 --vXMLTable(vCtr).TagValue := p_arrears - TRUNC(p_arrears);
583 vXMLTable(vCtr).TagValue := substr(l_fm_arrears2,length(l_fm_arrears2)-2);
584 vctr := vctr + 1;
585
586 l_fm_arrears3 := to_char(p_arrears3,lg_format_mask);
587 vXMLTable(vCtr).TagName := 'R166-OTH-3-D';
588 --vXMLTable(vCtr).TagValue := TRUNC(p_arrears);
589 vXMLTable(vCtr).TagValue := substr(l_fm_arrears3,1,length(l_fm_arrears3)-4);
590 vctr := vctr + 1;
591 vXMLTable(vCtr).TagName := 'R166-OTH-3-F';
592 --vXMLTable(vCtr).TagValue := p_arrears - TRUNC(p_arrears);
593 vXMLTable(vCtr).TagValue := substr(l_fm_arrears3,length(l_fm_arrears3)-2);
594 vctr := vctr + 1;
595
596
597 l_fm_arrears4 := to_char(p_not_in_rep_167,lg_format_mask);
598 vXMLTable(vCtr).TagName := 'R166-OTH-4-D';
599 --vXMLTable(vCtr).TagValue := TRUNC(p_arrears);
600 vXMLTable(vCtr).TagValue := substr(l_fm_arrears4,1,length(l_fm_arrears4)-4);
601 vctr := vctr + 1;
602 vXMLTable(vCtr).TagName := 'R166-OTH-4-F';
603 --vXMLTable(vCtr).TagValue := p_arrears - TRUNC(p_arrears);
604 vXMLTable(vCtr).TagValue := substr(l_fm_arrears4,length(l_fm_arrears4)-2);
605 vctr := vctr + 1;
606
607 l_fm_arrears5 := to_char(p_add_supp_insu_1997,lg_format_mask);
608 vXMLTable(vCtr).TagName := 'R166-OTH-5-D';
609 --vXMLTable(vCtr).TagValue := TRUNC(p_arrears);
610 vXMLTable(vCtr).TagValue := substr(l_fm_arrears5,1,length(l_fm_arrears5)-4);
611 vctr := vctr + 1;
612 vXMLTable(vCtr).TagName := 'R166-OTH-5-F';
613 --vXMLTable(vCtr).TagValue := p_arrears - TRUNC(p_arrears);
614 vXMLTable(vCtr).TagValue := substr(l_fm_arrears5,length(l_fm_arrears5)-2);
615 vctr := vctr + 1;
616
617 l_fm_arrears4_tot := to_char(p_not_in_rep_167,lg_format_mask);
618 vXMLTable(vCtr).TagName := 'R166-OTH-4-D-TOT';
619 --vXMLTable(vCtr).TagValue := TRUNC(p_arrears);
620 vXMLTable(vCtr).TagValue := substr(l_fm_arrears4_tot,1,length(l_fm_arrears4_tot)-4);
621 vctr := vctr + 1;
622 vXMLTable(vCtr).TagName := 'R166-OTH-4-F-TOT';
623 --vXMLTable(vCtr).TagValue := p_arrears - TRUNC(p_arrears);
624 vXMLTable(vCtr).TagValue := substr(l_fm_arrears4_tot,length(l_fm_arrears4_tot)-2);
625 vctr := vctr + 1;
626
627 l_fm_arrears5_tot := to_char(p_add_supp_insu_1997,lg_format_mask);
628 vXMLTable(vCtr).TagName := 'R166-OTH-5-D-TOT';
629 --vXMLTable(vCtr).TagValue := TRUNC(p_arrears);
630 vXMLTable(vCtr).TagValue := substr(l_fm_arrears5_tot,1,length(l_fm_arrears5_tot)-4);
631 vctr := vctr + 1;
632 vXMLTable(vCtr).TagName := 'R166-OTH-5-F-TOT';
633 --vXMLTable(vCtr).TagValue := p_arrears - TRUNC(p_arrears);
634 vXMLTable(vCtr).TagValue := substr(l_fm_arrears5_tot,length(l_fm_arrears5_tot)-2);
635 vctr := vctr + 1;
636
637 /* end 2012 */
638
639
640 /*l_total_amount := l_ee_basic_si_val + l_er_basic_si_val + l_ee_supp_si_val + l_er_supp_si_val + l_add_si_val + l_pf_val;*/
641 -- l_total_amount := l_ee_b + l_er_b + l_ee_s + l_er_s + l_add_si_val + l_ee_pf_val + l_er_pf_val;
642 l_total_amount := l_ee_basic_si_val + l_er_basic_si_val + l_ee_supp_si_val + l_er_supp_si_val + l_add_si_val + NVL(p_arrears,0)
643 + NVL(p_arrears2,0) + NVL(p_arrears3,0) + NVL(p_not_in_rep_167,0) +NVL(p_add_supp_insu_1997,0); -- added for aug 2012 bug 14704605
644 l_fm_total_amount := to_char(l_total_amount,lg_format_mask);
645 vXMLTable(vCtr).TagName := 'R166-TOT-D';
646 --vXMLTable(vCtr).TagValue := TRUNC(l_total_amount);
647 vXMLTable(vCtr).TagValue := substr(l_fm_total_amount,1,length(l_fm_total_amount)-4);
648 vctr := vctr + 1;
649 vXMLTable(vCtr).TagName := 'R166-TOT-F';
650 --vXMLTable(vCtr).TagValue := l_total_amount - TRUNC(l_total_amount);
651 vXMLTable(vCtr).TagValue := substr(l_fm_total_amount,length(l_fm_total_amount)-2);
652 vctr := vctr + 1;
653
654 vXMLTable(vCtr).TagName := 'R166-TOT1-D';
655 --vXMLTable(vCtr).TagValue := TRUNC(l_total_amount);
656 vXMLTable(vCtr).TagValue := substr(l_fm_total_amount,1,length(l_fm_total_amount)-4);
657 vctr := vctr + 1;
658 vXMLTable(vCtr).TagName := 'R166-TOT1-F';
659 --vXMLTable(vCtr).TagValue := l_total_amount - TRUNC(l_total_amount);
660 vXMLTable(vCtr).TagValue := substr(l_fm_total_amount,length(l_fm_total_amount)-2);
661 vctr := vctr + 1;
662
663
664 hr_utility.set_location('Finished creating xml data for Procedure report166 ',20);
665
666 WritetoCLOB ( l_xfdf_blob );
667
668 EXCEPTION
669 WHEN utl_file.invalid_path then
670 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
671 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
672 hr_utility.raise_error;
673 --
674 WHEN utl_file.invalid_mode then
675 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
676 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
677 hr_utility.raise_error;
678 --
679 WHEN utl_file.invalid_filehandle then
680 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
681 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
682 hr_utility.raise_error;
683 --
684 WHEN utl_file.invalid_operation then
685 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
686 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
687 hr_utility.raise_error;
688 --
689 WHEN utl_file.read_error then
690 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
691 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
692 hr_utility.raise_error;
693 --
694 WHEN others THEN
695 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
696 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
697 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
698 hr_utility.raise_error;
699
700 END report166;
701 ------------------------------------------------------------------------------------
702
703 PROCEDURE report167
704 (p_request_id NUMBER
705 ,p_report VARCHAR2
706 ,p_business_group_id NUMBER
707 ,p_employer_id NUMBER
708 ,p_effective_month VARCHAR2
709 ,p_effective_year VARCHAR2
710 ,l_xfdf_blob OUT NOCOPY BLOB
711 )
712 --,p_output_fname OUT NOCOPY VARCHAR2)
713 AS
714
715
716 l_effective_date DATE;
717 l_local_nationality VARCHAR2(80);
718 l_user_format VARCHAR2(80);
719
720 /*Cursor for fetching Employer SSN*/
721 CURSOR csr_employer_ssn IS
722 SELECT LPAD(org_information4,9,'0')
723 FROM hr_organization_information
724 WHERE organization_id = p_employer_id
725 AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
726
727 /*Cursor for fetching Employer Name*/
728 CURSOR csr_employer_name IS
729 SELECT name
730 FROM hr_organization_units
731 WHERE organization_id = p_employer_id;
732
733 /*Cursor for fetching defined balance id*/
734 CURSOR csr_get_def_bal_id(p_user_name VARCHAR2) IS
735 SELECT u.creator_id
736 FROM ff_user_entities u,
737 ff_database_items d
738 WHERE d.user_name = p_user_name
739 AND u.user_entity_id = d.user_entity_id
740 AND u.legislation_code = 'KW'
741 AND u.business_group_id is null
742 AND u.creator_type = 'B';
743
744 /*Cursor for fetching list of employees*/
745 CURSOR csr_get_emp IS
746 SELECT distinct asg.person_id
747 ,paa.assignment_action_id
748 ,hscl.segment2
749 FROM per_assignments_f asg /*per_all_assignments_f asg*/
750 ,pay_assignment_actions paa
751 ,pay_payroll_actions ppa
752 ,hr_soft_coding_keyflex hscl
753 ,per_people_f ppf /*per_all_people_f ppf*/
754 WHERE asg.assignment_id = paa.assignment_id
755 AND asg.person_id = ppf.person_id
756 AND ppf.nationality = l_local_nationality
757 AND paa.payroll_action_id = ppa.payroll_action_id
758 AND ppa.action_type in ('R','Q')
759 AND ppa.action_status = 'C'
760 AND paa.action_status IN ('C','S') --10375683
761 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
762 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
763 AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
764 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
765 AND hscl.segment1 = to_char(p_employer_id);
766 rec_get_emp csr_get_emp%ROWTYPE;
767
768 /*Cursor for fetching employee name*/
769 /*CURSOR csr_get_emp_name(p_person_id NUMBER) IS
770 SELECT --SUBSTR(full_name,1,30)
771 hr_person_name.get_person_name
772 (p_person_id
773 ,l_effective_date
774 ,'DISPLAY_NAME'
775 ,l_user_format)
776 FROM per_people_f ppf --per_all_people_f ppf
777 WHERE person_id = p_person_id
778 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
779 rec_get_emp_name csr_get_emp_name%ROWTYPE;*/
780
781 TYPE asi_emp_rec IS RECORD
782 (person_id NUMBER
783 ,assignment_action_id NUMBER
784 ,ssn NUMBER
785 ,full_name VARCHAR2(240)
786 ,asi_value NUMBER);
787 TYPE asi_emp_rec_table IS TABLE OF asi_emp_rec INDEX BY BINARY_INTEGER;
788 t_asi_emp_rec asi_emp_rec_table;
789
790
791 l_employer_name hr_organization_units.name%TYPE;
792 l_employer_ssn NUMBER;
793 l_add_si_id NUMBER;
794 l_add_si_arr_id NUMBER;
795 l_add_si_val NUMBER;
796 l_input_date VARCHAR2(30);
797 l_curr_date VARCHAR2(30);
798 l_full_name per_all_people_f.full_name%TYPE;
799 l_total_amount NUMBER;
800 i NUMBER;
801 j NUMBER;
802 l_count NUMBER;
803
804 l_xfdf_string CLOB;
805 l_str1 varchar2(240);
806 l_str2 varchar2(240);
807 l_str3 varchar2(240);
808 l_str4 varchar2(240);
809 l_str5 varchar2(240);
810 l_str6 varchar2(240);
811 l_str7 varchar2(240);
812 l_str8 varchar2(240);
813 l_str9 varchar2(240);
814 l_str10 varchar2(240);
815 l_str_ser varchar2(240);
816 l_str_er_name varchar2(240);
817 l_str_er_ssn_9 varchar2(240);
818 l_str_er_ssn_8 varchar2(240);
819 l_str_er_ssn_7 varchar2(240);
820 l_str_er_ssn_6 varchar2(240);
821 l_str_er_ssn_5 varchar2(240);
822 l_str_er_ssn_4 varchar2(240);
823 l_str_er_ssn_3 varchar2(240);
824 l_str_er_ssn_2 varchar2(240);
825 l_str_er_ssn_1 varchar2(240);
826 l_str_er_ssn_0 varchar2(240);
827 l_str_er_ssn_0a varchar2(240);
828 l_str_month varchar2(240);
829 l_str_year varchar2(240);
830 l_str_total_af varchar2(240);
831 l_str_total_ad varchar2(240);
832 l_str_total_f varchar2(240);
833 l_str_total_d varchar2(240);
834
835 l_asi_processed NUMBER;
836 l_effective_month VARCHAR2(50);
837
838 l_fm_asi_value VARCHAR2(50);
839 l_fm_total_value VARCHAR2(50);
840
841
842 BEGIN
843 g_report_old := 'Y';
844 set_currency_mask(p_business_group_id);
845 l_fm_asi_value := NULL;
846 l_input_date := '01-'||p_effective_month||'-'||p_effective_year;
847 l_effective_date := last_day(to_date(l_input_date,'DD-MM-YYYY'));
848 /*l_eff_term_date := to_date('28-'||to_char(l_effective_date,'MM-YYYY'),'DD-MM-YYYY');*/
849 INSERT INTO fnd_sessions (session_id, effective_date)
850 VALUES (userenv('sessionid'), l_effective_date);
851
852 l_user_format := NVL(FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G');
853 l_local_nationality := NULL;
854 BEGIN
855 SELECT org_information1
856 INTO l_local_nationality
857 FROM hr_organization_information
858 WHERE org_information_context = 'KW_BG_DETAILS'
859 AND organization_id = p_business_group_id;
860 EXCEPTION
861 WHEN OTHERS THEN
862 l_local_nationality := NULL;
863 END;
864
865 -- To clear the PL/SQL Table values.
866 hr_utility.set_location('Entering report167_old ',10);
867
868 /*Fetch Employer SSN*/
869 OPEN csr_employer_ssn;
870 FETCH csr_employer_ssn INTO l_employer_ssn;
871 CLOSE csr_employer_ssn;
872
873 /*Fetch Employer Name*/
874 OPEN csr_employer_name;
875 FETCH csr_employer_name INTO l_employer_name;
876 CLOSE csr_employer_name;
877
878 l_effective_month := hr_general.decode_lookup('KW_GREGORIAN_MONTH', p_effective_month);
879
880 l_str1 := '<NAME>'||'<![CDATA['||l_employer_name||']]>'||'</NAME>'; /* Bug No : 8299900 */
881 l_str2 := '<SSN-1>'||SUBSTR(l_employer_ssn,1,1)||'</SSN-1>';
882 l_str3 := '<SSN-2>'||SUBSTR(l_employer_ssn,2,1)||'</SSN-2>';
883 l_str4 := '<SSN-3>'||SUBSTR(l_employer_ssn,3,1)||'</SSN-3>';
884 l_str5 := '<SSN-4>'||SUBSTR(l_employer_ssn,4,1)||'</SSN-4>';
885 l_str6 := '<SSN-5>'||SUBSTR(l_employer_ssn,5,1)||'</SSN-5>';
886 l_str7 := '<SSN-6>'||SUBSTR(l_employer_ssn,6,1)||'</SSN-6>';
887 l_str8 := '<SSN-7>'||SUBSTR(l_employer_ssn,7,1)||'</SSN-7>';
888 l_str9 := '<SSN-8>'||SUBSTR(l_employer_ssn,8,1)||'</SSN-8>';
889 l_str10 := '<SSN-9>'||SUBSTR(l_employer_ssn,9,1)||'</SSN-9>';
890 l_str_month := '<MONTH>'||l_effective_month||'</MONTH>';
891 l_str_year := '<YEAR>'||p_effective_year||'</YEAR>';
892
893 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
894 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
895 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob,TRUE);
896 clob_to_blob(l_xfdf_string,l_xfdf_blob);
897
898 l_add_si_id := 0;
899 l_add_si_arr_id := 0;
900
901 /*Fetch Defined Balance Id*/
902 OPEN csr_get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_ASG_RUN');
903 FETCH csr_get_def_bal_id INTO l_add_si_id;
904 CLOSE csr_get_def_bal_id;
905
906 OPEN csr_get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_ARREARS_ASG_RUN');
907 FETCH csr_get_def_bal_id INTO l_add_si_arr_id;
908 CLOSE csr_get_def_bal_id;
909
910 /*Set Contexts and then fetch the balance values*/
911 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(l_effective_date));
912 l_total_amount := 0;
913 l_add_si_val := 0;
914 i := 0;
915
916 OPEN csr_get_emp;
917 LOOP
918 FETCH csr_get_emp INTO rec_get_emp;
919 EXIT WHEN csr_get_emp%NOTFOUND;
920 l_add_si_val := 0;
921 l_add_si_val := pay_balance_pkg.get_value(l_add_si_id,rec_get_emp.assignment_action_id) +
922 pay_balance_pkg.get_value(l_add_si_arr_id,rec_get_emp.assignment_action_id);
923 IF l_add_si_val > 0 THEN
924
925 i := i + 1;
926 l_full_name := NULL;
927 /*OPEN csr_get_emp_name(rec_get_emp.person_id);
928 FETCH csr_get_emp_name INTO l_full_name;
929 CLOSE csr_get_emp_name;*/
930 l_full_name := hr_person_name.get_person_name
931 (p_person_id => rec_get_emp.person_id
932 ,p_effective_date => l_effective_date
933 ,p_format_name => 'DISPLAY_NAME'
934 ,p_user_format_choice => l_user_format);
935
936 t_asi_emp_rec(i).person_id := rec_get_emp.person_id;
937 t_asi_emp_rec(i).assignment_action_id := rec_get_emp.assignment_action_id;
938 t_asi_emp_rec(i).full_name := l_full_name;
939 t_asi_emp_rec(i).ssn := rec_get_emp.segment2;
940 t_asi_emp_rec(i).asi_value := l_add_si_val;
941
942 END IF;
943 END LOOP;
944 CLOSE csr_get_emp;
945
946 j := 1;
947 dbms_lob.writeAppend( l_xfdf_string, length('<START>'),'<START>');
948 dbms_lob.writeAppend( l_xfdf_string, length('<EMP-REC>'),'<EMP-REC>');
949 l_str_er_name := '<NAME>'||'<![CDATA['||l_employer_name||']]>'||'</NAME>'; /* Bug No : 8299900 */
950 l_str_er_ssn_9 := '<SSN-9>'||SUBSTR(l_employer_ssn,9,1)||'</SSN-9>';
951 l_str_er_ssn_8 := '<SSN-8>'||SUBSTR(l_employer_ssn,8,1)||'</SSN-8>';
952 l_str_er_ssn_7 := '<SSN-7>'||SUBSTR(l_employer_ssn,7,1)||'</SSN-7>';
953 l_str_er_ssn_6 := '<SSN-6>'||SUBSTR(l_employer_ssn,6,1)||'</SSN-6>';
954 l_str_er_ssn_5 := '<SSN-5>'||SUBSTR(l_employer_ssn,5,1)||'</SSN-5>';
955 l_str_er_ssn_4 := '<SSN-4>'||SUBSTR(l_employer_ssn,4,1)||'</SSN-4>';
956 l_str_er_ssn_3 := '<SSN-3>'||SUBSTR(l_employer_ssn,3,1)||'</SSN-3>';
957 l_str_er_ssn_2 := '<SSN-2>'||SUBSTR(l_employer_ssn,2,1)||'</SSN-2>';
958 l_str_er_ssn_1 := '<SSN-1>'||SUBSTR(l_employer_ssn,1,1)||'</SSN-1>';
959 l_str_er_ssn_0 := '<SSN-0>'||'0'||'</SSN-0>';
960 l_str_er_ssn_0a := '<SSN-0A>'||'0'||'</SSN-0A>';
961 l_str_month := '<MONTH>'||l_effective_month||'</MONTH>';
962 l_str_year := '<YEAR>'||p_effective_year||'</YEAR>';
963 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name), l_str_er_name);
964 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_9), l_str_er_ssn_9);
965 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_8), l_str_er_ssn_8);
966 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_7), l_str_er_ssn_7);
967 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_6), l_str_er_ssn_6);
968 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_5), l_str_er_ssn_5);
969 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_4), l_str_er_ssn_4);
970 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_3), l_str_er_ssn_3);
971 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_2), l_str_er_ssn_2);
972 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_1), l_str_er_ssn_1);
973 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_0), l_str_er_ssn_0);
974 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_0a), l_str_er_ssn_0a);
975 dbms_lob.writeAppend( l_xfdf_string, length(l_str_month), l_str_month);
976 dbms_lob.writeAppend( l_xfdf_string, length(l_str_year), l_str_year);
977 --dbms_lob.writeAppend( l_xfdf_string, length('</EMP-REC>'),'</EMP-REC>');
978 IF i > 0 THEN
979 l_asi_processed := 0;
980 ELSE
981 l_asi_processed := 1;
982 dbms_lob.writeAppend( l_xfdf_string, length('</EMP-REC>'),'</EMP-REC>');
983 END IF;
984
985 WHILE l_asi_processed <> 1 LOOP
986
987 --Writing data for new employees
988 l_count := 0;
989 IF j > 10 THEN
990 l_str_total_ad := '<ASI-TOT-D>'||substr(l_fm_total_value,1,length(l_fm_total_value)-4)||'</ASI-TOT-D>';
991 l_str_total_af := '<ASI-TOT-F>'||substr(l_fm_total_value,length(l_fm_total_value)-2)||'</ASI-TOT-F>';
992 l_str_total_d := '<TOT-D>'||substr(l_fm_total_value,1,length(l_fm_total_value)-4)||'</TOT-D>';
993 l_str_total_f := '<TOT-F>'||substr(l_fm_total_value,length(l_fm_total_value)-2)||'</TOT-F>';
994 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_ad), l_str_total_ad);
995 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_af), l_str_total_af);
996 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_d), l_str_total_d);
997 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_f), l_str_total_f);
998 dbms_lob.writeAppend( l_xfdf_string, length('</EMP-REC>'),'</EMP-REC>');
999
1000 dbms_lob.writeAppend( l_xfdf_string, length('<EMP-REC>'),'<EMP-REC>');
1001 l_str_er_name := '<NAME>'||'<![CDATA['||l_employer_name||']]>'||'</NAME>'; /* Bug No : 8299900 */
1002 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name), l_str_er_name);
1003 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_9), l_str_er_ssn_9);
1004 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_8), l_str_er_ssn_8);
1005 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_7), l_str_er_ssn_7);
1006 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_6), l_str_er_ssn_6);
1007 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_5), l_str_er_ssn_5);
1008 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_4), l_str_er_ssn_4);
1009 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_3), l_str_er_ssn_3);
1010 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_2), l_str_er_ssn_2);
1011 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_1), l_str_er_ssn_1);
1012 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_0), l_str_er_ssn_0);
1013 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_0a), l_str_er_ssn_0a);
1014 dbms_lob.writeAppend( l_xfdf_string, length(l_str_month), l_str_month);
1015 dbms_lob.writeAppend( l_xfdf_string, length(l_str_year), l_str_year);
1016 END IF;
1017
1018
1019 WHILE j <= i LOOP
1020
1021 l_count := l_count+1;
1022
1023 l_fm_asi_value := to_char(t_asi_emp_rec(j).asi_value,lg_format_mask);
1024 l_str_ser := '<SER-'||l_count||'>'||j||'</SER-'||l_count||'>';
1025 l_str4 := '<EMPLOYEE-NAME-'||l_count||'>'||substr(t_asi_emp_rec(j).full_name,1,30)||'</EMPLOYEE-NAME-'||l_count||'>';
1026 l_str5 := '<EMPLOYEE-SSN-'||l_count||'>'||t_asi_emp_rec(j).ssn||'</EMPLOYEE-SSN-'||l_count||'>';
1027 l_str6 := '<ASI-D-'||l_count||'>'||substr(l_fm_asi_value,1,length(l_fm_asi_value)-4)||'</ASI-D-'||l_count||'>';
1028 l_str7 := '<ASI-F-'||l_count||'>'||substr(l_fm_asi_value,length(l_fm_asi_value)-2)||'</ASI-F-'||l_count||'>';
1029 l_str8 := '<TOT-D-'||l_count||'>'||substr(l_fm_asi_value,1,length(l_fm_asi_value)-4)||'</TOT-D-'||l_count||'>';
1030 l_str9 := '<TOT-F-'||l_count||'>'||substr(l_fm_asi_value,length(l_fm_asi_value)-2)||'</TOT-F-'||l_count||'>';
1031
1032 dbms_lob.writeAppend( l_xfdf_string, length(l_str_ser), l_str_ser);
1033 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4);
1034 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5);
1035 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6);
1036 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7);
1037 dbms_lob.writeAppend( l_xfdf_string, length(l_str8), l_str8);
1038 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
1039 --dbms_lob.writeAppend( l_xfdf_string, length('</EMP-REC>'),'</EMP-REC>');
1040
1041 l_total_amount := l_total_amount + t_asi_emp_rec(j).asi_value;
1042 l_fm_total_value := to_char(l_total_amount,lg_format_mask);
1043
1044 j := j + 1;
1045 IF j > i THEN
1046 l_asi_processed := 1;
1047 END IF;
1048
1049 IF l_count = 10 THEN
1050 EXIT;
1051 END IF;
1052
1053 END LOOP;
1054
1055 END LOOP;
1056 IF i > 0 THEN
1057 l_str_total_ad := '<ASI-TOT-D>'||substr(l_fm_total_value,1,length(l_fm_total_value)-4)||'</ASI-TOT-D>';
1058 l_str_total_af := '<ASI-TOT-F>'||substr(l_fm_total_value,length(l_fm_total_value)-2)||'</ASI-TOT-F>';
1059 l_str_total_d := '<TOT-D>'||substr(l_fm_total_value,1,length(l_fm_total_value)-4)||'</TOT-D>';
1060 l_str_total_f := '<TOT-F>'||substr(l_fm_total_value,length(l_fm_total_value)-2)||'</TOT-F>';
1061 l_str10 := '</EMP-REC>';
1062 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_ad), l_str_total_ad);
1063 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_af), l_str_total_af);
1064 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_d), l_str_total_d);
1065 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_f), l_str_total_f);
1066 dbms_lob.writeAppend( l_xfdf_string, length(l_str10), l_str10);
1067 END IF;
1068
1069 dbms_lob.writeAppend( l_xfdf_string, length('</START>'),'</START>');
1070
1071 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob,TRUE);
1072 clob_to_blob(l_xfdf_string,l_xfdf_blob);
1073
1074 hr_utility.set_location('Finished creating xml data for Procedure report167 ',20);
1075
1076 EXCEPTION
1077 WHEN utl_file.invalid_path then
1078 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
1079 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1080 hr_utility.raise_error;
1081 --
1082 WHEN utl_file.invalid_mode then
1083 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
1084 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1085 hr_utility.raise_error;
1086 --
1087 WHEN utl_file.invalid_filehandle then
1088 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
1089 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1090 hr_utility.raise_error;
1091 --
1092 WHEN utl_file.invalid_operation then
1093 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
1094 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1095 hr_utility.raise_error;
1096 --
1097 WHEN utl_file.read_error then
1098 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
1099 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1100 hr_utility.raise_error;
1101 --
1102 WHEN others THEN
1103 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
1104 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
1105 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1106 hr_utility.raise_error;
1107
1108 END report167;
1109
1110 -------------------------------------------------------------------------------------------
1111
1112 PROCEDURE report167_2006
1113 (p_request_id NUMBER
1114 ,p_report VARCHAR2
1115 ,p_business_group_id NUMBER
1116 ,p_employer_id NUMBER
1117 ,p_effective_month VARCHAR2
1118 ,p_effective_year VARCHAR2
1119 ,l_xfdf_blob OUT NOCOPY BLOB
1120 )
1121 --,p_output_fname OUT NOCOPY VARCHAR2)
1122 AS
1123
1124
1125 l_effective_date DATE;
1126 l_local_nationality VARCHAR2(80);
1127 l_user_format VARCHAR2(80);
1128
1129 /*Cursor for fetching Employer SSN*/
1130 CURSOR csr_employer_ssn IS
1131 SELECT LPAD(org_information4,9,'0')
1132 FROM hr_organization_information
1133 WHERE organization_id = p_employer_id
1134 AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
1135
1136 /*Cursor for fetching Employer Name*/
1137 CURSOR csr_employer_name IS
1138 SELECT name
1139 FROM hr_organization_units
1140 WHERE organization_id = p_employer_id;
1141
1142 /*Cursor for fetching defined balance id*/
1143 CURSOR csr_get_def_bal_id(p_user_name VARCHAR2) IS
1144 SELECT u.creator_id
1145 FROM ff_user_entities u,
1146 ff_database_items d
1147 WHERE d.user_name = p_user_name
1148 AND u.user_entity_id = d.user_entity_id
1149 AND u.legislation_code = 'KW'
1150 AND u.business_group_id is null
1151 AND u.creator_type = 'B';
1152
1153 /*Cursor for fetching list of employees*/
1154 CURSOR csr_get_emp IS
1155 SELECT distinct asg.person_id
1156 ,paa.assignment_action_id
1157 ,hscl.segment2
1158 ,asg.assignment_id
1159 FROM per_assignments_f asg /*per_all_assignments_f asg*/
1160 ,pay_assignment_actions paa
1161 ,pay_payroll_actions ppa
1162 ,hr_soft_coding_keyflex hscl
1163 ,per_people_f ppf /*per_all_people_f ppf*/
1164 WHERE asg.assignment_id = paa.assignment_id
1165 AND asg.person_id = ppf.person_id
1166 AND ppf.nationality = l_local_nationality
1167 AND paa.payroll_action_id = ppa.payroll_action_id
1168 AND ppa.action_type in ('R','Q')
1169 AND ppa.action_status = 'C'
1170 AND paa.action_status IN ('C','S') --10375683
1171 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
1172 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
1173 AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
1174 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1175 AND hscl.segment1 = to_char(p_employer_id);
1176 rec_get_emp csr_get_emp%ROWTYPE;
1177
1178 /*Cursor for fetching employee name*/
1179 /*CURSOR csr_get_emp_name(p_person_id NUMBER) IS
1180 SELECT --SUBSTR(full_name,1,30)
1181 hr_person_name.get_person_name
1182 (p_person_id
1183 ,l_effective_date
1184 ,'DISPLAY_NAME'
1185 ,l_user_format)
1186 FROM per_people_f ppf --per_all_people_f ppf
1187 WHERE person_id = p_person_id
1188 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
1189 rec_get_emp_name csr_get_emp_name%ROWTYPE;*/
1190
1191 /*Cursor for fetching deduction values*/
1192 CURSOR csr_get_ded_value(p_deduction_code VARCHAR2) IS
1193 SELECT NVL(SUM(rrv.RESULT_VALUE),0)
1194 FROM pay_element_entries_f pee
1195 ,pay_run_results prr
1196 ,pay_run_result_values rrv
1197 ,pay_input_values_f piv
1198 WHERE rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
1199 AND prr.assignment_action_id = rec_get_emp.assignment_action_id
1200 AND prr.element_entry_id = pee.element_entry_id
1201 AND pee.assignment_id = rec_get_emp.assignment_id
1202 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'))
1203 AND pee.entry_information3 is not null
1204 AND pee.entry_information3 IN (p_deduction_code)
1205 /*( SELECT i.value
1206 FROM pay_user_column_instances_f i
1207 ,pay_user_rows_f r
1208 ,pay_user_columns c
1209 ,pay_user_tables t
1210 WHERE (i.legislation_code = 'KW' AND i.business_group_id IS NULL)
1211 AND (r.legislation_code = 'KW' AND r.business_group_id IS NULL)
1212 AND c.legislation_code = 'KW'
1213 AND t.legislation_code = 'KW'
1214 AND UPPER(t.user_table_name) = UPPER('KW_DEDUCTION_MAPPING')
1215 AND t.user_table_id = r.user_table_id
1216 AND t.user_table_id = c.user_table_id
1217 AND r.row_low_range_or_name = p_deduction_code
1218 AND r.user_row_id = i.user_row_id
1219 AND UPPER(c.user_column_name) = UPPER('DEDUCTION_TYPE')
1220 AND c.user_column_id = i.user_column_id
1221 AND TRUNC(l_effective_date,'MM') BETWEEN r.effective_start_date AND r.effective_end_date
1222 AND TRUNC(l_effective_date,'MM') BETWEEN i.effective_start_date AND i.effective_end_date
1223 )*/
1224 AND rrv.result_value IS NOT NULL
1225 AND rrv.input_value_id = piv.input_value_id
1226 AND piv.name = 'Pay Value'
1227 AND prr.element_type_id = piv.element_type_id
1228 AND pee.element_type_id = piv.element_type_id
1229 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'));
1230
1231 /* Cursor for fetching Employer's Address */
1232 CURSOR csr_get_address IS
1233 SELECT address_line_1 || decode(address_line_2,null,null,',') || address_line_2 || decode(postal_code,null,null,',')|| postal_code
1234 FROM hr_locations hl
1235 , hr_organization_units hou /*hr_all_organization_units hou*/
1236 WHERE hl.location_id = hou.location_id
1237 AND hou.organization_id = p_employer_id;
1238
1239 l_emp_address VARCHAR2(1000);
1240
1241
1242 TYPE asi_emp_rec IS RECORD
1243 (person_id NUMBER
1244 ,assignment_action_id NUMBER
1245 ,ssn NUMBER
1246 ,full_name VARCHAR2(240)
1247 ,asi_value NUMBER
1248 ,jps_b_value NUMBER
1249 ,ul_b_value NUMBER
1250 ,ci_b_value NUMBER
1251 ,ua_b_value NUMBER
1252 ,jps_c_value NUMBER
1253 ,ci_c_value NUMBER
1254 ,ua_c_value NUMBER
1255 ,emp_tot_value NUMBER);
1256 TYPE asi_emp_rec_table IS TABLE OF asi_emp_rec INDEX BY BINARY_INTEGER;
1257 t_asi_emp_rec asi_emp_rec_table;
1258
1259
1260 l_employer_name hr_organization_units.name%TYPE;
1261 l_employer_ssn NUMBER;
1262 l_add_si_id NUMBER;
1263 l_add_si_arr_id NUMBER;
1264 l_add_si_val NUMBER;
1265 l_input_date VARCHAR2(30);
1266 l_curr_date VARCHAR2(30);
1267 l_full_name per_all_people_f.full_name%TYPE;
1268 l_total_amount NUMBER;
1269 i NUMBER;
1270 j NUMBER;
1271 l_count NUMBER;
1272
1273 l_xfdf_string CLOB;
1274 l_str1 varchar2(240);
1275 l_str2 varchar2(240);
1276 l_str3 varchar2(240);
1277 l_str4 varchar2(240);
1278 l_str5 varchar2(240);
1279 l_str6 varchar2(240);
1280 l_str7 varchar2(240);
1281 l_str8 varchar2(240);
1282 l_str9 varchar2(240);
1283 l_str10 varchar2(240);
1284 l_str11 varchar2(240);
1285 l_str12 varchar2(240);
1286 l_str13 varchar2(240);
1287 l_str14 varchar2(240);
1288 l_str_ser varchar2(240);
1289 l_str_er_name varchar2(240);
1290 l_str_er_ssn_9 varchar2(240);
1291 l_str_er_ssn_8 varchar2(240);
1292 l_str_er_ssn_7 varchar2(240);
1293 l_str_er_ssn_6 varchar2(240);
1294 l_str_er_ssn_5 varchar2(240);
1295 l_str_er_ssn_4 varchar2(240);
1296 l_str_er_ssn_3 varchar2(240);
1297 l_str_er_ssn_2 varchar2(240);
1298 l_str_er_ssn_1 varchar2(240);
1299 l_str_er_ssn_0 varchar2(240);
1300 l_str_er_ssn_0a varchar2(240);
1301 l_str_month varchar2(240);
1302 l_str_year varchar2(240);
1303 l_str_total_af varchar2(240);
1304 l_str_total_ad varchar2(240);
1305 l_str_total_f varchar2(240);
1306 l_str_total_d varchar2(240);
1307
1308 l_str_er_addr varchar2(1000);
1309 l_str_er_ssn varchar2(240);
1310
1311 l_asi_processed NUMBER;
1312 l_effective_month VARCHAR2(50);
1313
1314 l_fm_asi_value VARCHAR2(50);
1315 l_fm_total_value VARCHAR2(50);
1316
1317 l_fm_jps_b_value VARCHAR2(50);
1318 l_fm_ul_b_value VARCHAR2(50);
1319 l_fm_ci_b_value VARCHAR2(50);
1320 l_fm_ua_b_value VARCHAR2(50);
1321 l_fm_jps_c_value VARCHAR2(50);
1322 l_fm_ci_c_value VARCHAR2(50);
1323 l_fm_ua_c_value VARCHAR2(50);
1324 l_fm_emp_total_value VARCHAR2(50);
1325
1326 l_jps_b_val NUMBER;
1327 l_ul_b_val NUMBER;
1328 l_ci_b_val NUMBER;
1329 l_ua_b_val NUMBER;
1330 l_jps_c_val NUMBER;
1331 l_ci_c_val NUMBER;
1332 l_ua_c_val NUMBER;
1333 l_emp_tot_val NUMBER;
1334
1335 l_str_rep_yyyy VARCHAR2(20);
1336 l_str_rep_mm VARCHAR2(20);
1337 l_str_rep_dd VARCHAR2(20);
1338
1339 BEGIN
1340 g_report_old := 'N';
1341 set_currency_mask(p_business_group_id);
1342 l_fm_asi_value := NULL;
1343 l_fm_jps_b_value := NULL;
1344 l_fm_ul_b_value := NULL;
1345 l_fm_ci_b_value := NULL;
1346 l_fm_ua_b_value := NULL;
1347 l_fm_jps_c_value := NULL;
1348 l_fm_ci_c_value := NULL;
1349 l_fm_ua_c_value := NULL;
1350
1351 l_input_date := '01-'||p_effective_month||'-'||p_effective_year;
1352 l_effective_date := last_day(to_date(l_input_date,'DD-MM-YYYY'));
1353 /*l_eff_term_date := to_date('28-'||to_char(l_effective_date,'MM-YYYY'),'DD-MM-YYYY');*/
1354 INSERT INTO fnd_sessions (session_id, effective_date)
1355 VALUES (userenv('sessionid'), l_effective_date);
1356
1357 l_user_format := NVL(FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G');
1358 l_local_nationality := NULL;
1359 BEGIN
1360 SELECT org_information1
1361 INTO l_local_nationality
1362 FROM hr_organization_information
1363 WHERE org_information_context = 'KW_BG_DETAILS'
1364 AND organization_id = p_business_group_id;
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 l_local_nationality := NULL;
1368 END;
1369
1370 -- To clear the PL/SQL Table values.
1371 hr_utility.set_location('Entering report167 ',10);
1372
1373 /*Fetch Employer SSN*/
1374 OPEN csr_employer_ssn;
1375 FETCH csr_employer_ssn INTO l_employer_ssn;
1376 CLOSE csr_employer_ssn;
1377
1378 /*Fetch Employer Name*/
1379 OPEN csr_employer_name;
1380 FETCH csr_employer_name INTO l_employer_name;
1381 CLOSE csr_employer_name;
1382
1383 /*Fetch Employer Address*/
1384 OPEN csr_get_address;
1385 FETCH csr_get_address INTO l_emp_address;
1386 CLOSE csr_get_address;
1387
1388 l_effective_month := hr_general.decode_lookup('KW_GREGORIAN_MONTH', p_effective_month);
1389
1390 l_str1 := '<NAME>'||'<![CDATA['||l_employer_name||']]>'||'</NAME>'; /* Bug No : 8299900 */
1391 /**************l_str2 := '<SSN-1>'||SUBSTR(l_employer_ssn,1,1)||'</SSN-1>';
1392 l_str3 := '<SSN-2>'||SUBSTR(l_employer_ssn,2,1)||'</SSN-2>';
1393 l_str4 := '<SSN-3>'||SUBSTR(l_employer_ssn,3,1)||'</SSN-3>';
1394 l_str5 := '<SSN-4>'||SUBSTR(l_employer_ssn,4,1)||'</SSN-4>';
1395 l_str6 := '<SSN-5>'||SUBSTR(l_employer_ssn,5,1)||'</SSN-5>';
1396 l_str7 := '<SSN-6>'||SUBSTR(l_employer_ssn,6,1)||'</SSN-6>';
1397 l_str8 := '<SSN-7>'||SUBSTR(l_employer_ssn,7,1)||'</SSN-7>';
1398 l_str9 := '<SSN-8>'||SUBSTR(l_employer_ssn,8,1)||'</SSN-8>';
1399 l_str10 := '<SSN-9>'||SUBSTR(l_employer_ssn,9,1)||'</SSN-9>';************/
1400
1401 l_str2 := '<ADDRESS>'||(l_emp_address)||'</ADDRESS>';
1402 l_str3 := '<EMPLOYER-SSN>'||(l_employer_ssn)||'</EMPLOYER-SSN>';
1403 l_str_month := '<Month>'||l_effective_month||'</Month>';
1404 l_str_year := '<Year>'||p_effective_year||'</Year>';
1405
1406 l_str_rep_yyyy := '<YYYY>'||TO_CHAR(SYSDATE,'YYYY')||'</YYYY>';
1407 l_str_rep_mm := '<MM>'||TO_CHAR(SYSDATE,'MM')||'</MM>';
1408 l_str_rep_dd := '<DD>'||TO_CHAR(SYSDATE,'DD')||'</DD>';
1409
1410 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1411 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1412 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob,TRUE);
1413 clob_to_blob(l_xfdf_string,l_xfdf_blob);
1414
1415 l_add_si_id := 0;
1416 l_add_si_arr_id := 0;
1417
1418 /*Fetch Defined Balance Id*/
1419 /*Following code not required as per enhancement to 167*/
1420 /*OPEN csr_get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_ASG_RUN');
1421 FETCH csr_get_def_bal_id INTO l_add_si_id;
1422 CLOSE csr_get_def_bal_id;
1423
1424 OPEN csr_get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_ARREARS_ASG_RUN');
1425 FETCH csr_get_def_bal_id INTO l_add_si_arr_id;
1426 CLOSE csr_get_def_bal_id;*/
1427
1428 /*Set Contexts and then fetch the balance values*/
1429 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(l_effective_date));
1430 l_total_amount := 0;
1431 l_add_si_val := 0;
1432 i := 0;
1433
1434 OPEN csr_get_emp;
1435 LOOP
1436 FETCH csr_get_emp INTO rec_get_emp;
1437 EXIT WHEN csr_get_emp%NOTFOUND;
1438 l_add_si_val := 0;
1439 /*l_add_si_val := pay_balance_pkg.get_value(l_add_si_id,rec_get_emp.assignment_action_id) +
1440 pay_balance_pkg.get_value(l_add_si_arr_id,rec_get_emp.assignment_action_id);*/
1441 l_emp_tot_val := 0;
1442 l_jps_b_val := 0;
1443 l_ul_b_val := 0;
1444 l_ci_b_val := 0;
1445 l_ua_b_val := 0;
1446 l_jps_c_val := 0;
1447 l_ci_c_val := 0;
1448 l_ua_c_val := 0;
1449 /*Fetch value for Joint Past Services:Basic */
1450 OPEN csr_get_ded_value('71');
1451 FETCH csr_get_ded_value INTO l_jps_b_val;
1452 CLOSE csr_get_ded_value;
1453 /*Fetch value for Unpaid Leave:Basic */
1454 OPEN csr_get_ded_value('65');
1455 FETCH csr_get_ded_value INTO l_ul_b_val;
1456 CLOSE csr_get_ded_value;
1457 /*Fetch value for Commutation Installments:Basic */
1458 OPEN csr_get_ded_value('72');
1459 FETCH csr_get_ded_value INTO l_ci_b_val;
1460 CLOSE csr_get_ded_value;
1461 /*Fetch value for Undue Amounts:Basic */
1462 OPEN csr_get_ded_value('999');
1463 FETCH csr_get_ded_value INTO l_ua_b_val;
1464 CLOSE csr_get_ded_value;
1465 /*Fetch value for Joint Past Services:Complementary */
1466 OPEN csr_get_ded_value('82');
1467 FETCH csr_get_ded_value INTO l_jps_c_val;
1468 CLOSE csr_get_ded_value;
1469 /*Fetch value for Commutation Installments:Complamantary */
1470 OPEN csr_get_ded_value('73');
1471 FETCH csr_get_ded_value INTO l_ci_c_val;
1472 CLOSE csr_get_ded_value;
1473 /*Fetch value for Undue Amounts:Complementary */
1474 OPEN csr_get_ded_value('85');
1475 FETCH csr_get_ded_value INTO l_ua_c_val;
1476 CLOSE csr_get_ded_value;
1477 l_emp_tot_val := l_jps_b_val + l_ul_b_val + l_ci_b_val + l_ua_b_val + l_jps_c_val + l_ci_c_val + l_ua_c_val;
1478 IF (NVL(l_emp_tot_val,0) > 0) THEN
1479
1480 i := i + 1;
1481 l_full_name := NULL;
1482 /*OPEN csr_get_emp_name(rec_get_emp.person_id);
1483 FETCH csr_get_emp_name INTO l_full_name;
1484 CLOSE csr_get_emp_name;*/
1485 l_full_name := hr_person_name.get_person_name
1486 (p_person_id => rec_get_emp.person_id
1487 ,p_effective_date => l_effective_date
1488 ,p_format_name => 'DISPLAY_NAME'
1489 ,p_user_format_choice => l_user_format);
1490
1491 t_asi_emp_rec(i).person_id := rec_get_emp.person_id;
1492 t_asi_emp_rec(i).assignment_action_id := rec_get_emp.assignment_action_id;
1493 t_asi_emp_rec(i).full_name := l_full_name;
1494 t_asi_emp_rec(i).ssn := rec_get_emp.segment2;
1495 --t_asi_emp_rec(i).asi_value := l_add_si_val;
1496 t_asi_emp_rec(i).jps_b_value := l_jps_b_val;
1497 t_asi_emp_rec(i).ul_b_value := l_ul_b_val;
1498 t_asi_emp_rec(i).ci_b_value := l_ci_b_val;
1499 t_asi_emp_rec(i).ua_b_value := l_ua_b_val;
1500 t_asi_emp_rec(i).jps_c_value := l_jps_c_val;
1501 t_asi_emp_rec(i).ci_c_value := l_ci_c_val;
1502 t_asi_emp_rec(i).ua_c_value := l_ua_c_val;
1503 t_asi_emp_rec(i).emp_tot_value := l_emp_tot_val;
1504
1505 END IF;
1506 END LOOP;
1507 CLOSE csr_get_emp;
1508
1509 j := 1;
1510 dbms_lob.writeAppend( l_xfdf_string, length('<START>'),'<START>');
1511 dbms_lob.writeAppend( l_xfdf_string, length('<EMP-REC>'),'<EMP-REC>');
1512 l_str_er_name := '<NAME>'||'<![CDATA['||l_employer_name||']]>'||'</NAME>'; /* Bug No : 8299900 */
1513 /**********l_str_er_ssn_9 := '<SSN-9>'||SUBSTR(l_employer_ssn,9,1)||'</SSN-9>';
1514 l_str_er_ssn_8 := '<SSN-8>'||SUBSTR(l_employer_ssn,8,1)||'</SSN-8>';
1515 l_str_er_ssn_7 := '<SSN-7>'||SUBSTR(l_employer_ssn,7,1)||'</SSN-7>';
1516 l_str_er_ssn_6 := '<SSN-6>'||SUBSTR(l_employer_ssn,6,1)||'</SSN-6>';
1517 l_str_er_ssn_5 := '<SSN-5>'||SUBSTR(l_employer_ssn,5,1)||'</SSN-5>';
1518 l_str_er_ssn_4 := '<SSN-4>'||SUBSTR(l_employer_ssn,4,1)||'</SSN-4>';
1519 l_str_er_ssn_3 := '<SSN-3>'||SUBSTR(l_employer_ssn,3,1)||'</SSN-3>';
1520 l_str_er_ssn_2 := '<SSN-2>'||SUBSTR(l_employer_ssn,2,1)||'</SSN-2>';
1521 l_str_er_ssn_1 := '<SSN-1>'||SUBSTR(l_employer_ssn,1,1)||'</SSN-1>';
1522 l_str_er_ssn_0 := '<SSN-0>'||'0'||'</SSN-0>';
1523 l_str_er_ssn_0a := '<SSN-0A>'||'0'||'</SSN-0A>';************/
1524
1525 l_str_er_addr := '<ADDRESS>'||(l_emp_address)||'</ADDRESS>';
1526 l_str_er_ssn := '<EMPLOYER-SSN>'||(l_employer_ssn)||'</EMPLOYER-SSN>';
1527
1528 l_str_month := '<Month>'||l_effective_month||'</Month>';
1529 l_str_year := '<Year>'||p_effective_year||'</Year>';
1530
1531 l_str_rep_yyyy := '<YYYY>'||TO_CHAR(SYSDATE,'YYYY')||'</YYYY>';
1532 l_str_rep_mm := '<MM>'||TO_CHAR(SYSDATE,'MM')||'</MM>';
1533 l_str_rep_dd := '<DD>'||TO_CHAR(SYSDATE,'DD')||'</DD>';
1534
1535 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name), l_str_er_name);
1536 /********dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_9), l_str_er_ssn_9);
1537 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_8), l_str_er_ssn_8);
1538 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_7), l_str_er_ssn_7);
1539 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_6), l_str_er_ssn_6);
1540 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_5), l_str_er_ssn_5);
1541 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_4), l_str_er_ssn_4);
1542 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_3), l_str_er_ssn_3);
1543 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_2), l_str_er_ssn_2);
1544 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_1), l_str_er_ssn_1);
1545 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_0), l_str_er_ssn_0);
1546 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_0a), l_str_er_ssn_0a);**********/
1547
1548 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_addr), l_str_er_addr);
1549 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn), l_str_er_ssn);
1550 dbms_lob.writeAppend( l_xfdf_string, length(l_str_month), l_str_month);
1551 dbms_lob.writeAppend( l_xfdf_string, length(l_str_year), l_str_year);
1552
1553 dbms_lob.writeAppend( l_xfdf_string, length(l_str_rep_yyyy), l_str_rep_yyyy);
1554 dbms_lob.writeAppend( l_xfdf_string, length(l_str_rep_mm), l_str_rep_mm);
1555 dbms_lob.writeAppend( l_xfdf_string, length(l_str_rep_dd), l_str_rep_dd);
1556
1557 --dbms_lob.writeAppend( l_xfdf_string, length('</EMP-REC>'),'</EMP-REC>');
1558 IF i > 0 THEN
1559 l_asi_processed := 0;
1560 ELSE
1561 l_asi_processed := 1;
1562 dbms_lob.writeAppend( l_xfdf_string, length('</EMP-REC>'),'</EMP-REC>');
1563 END IF;
1564
1565 WHILE l_asi_processed <> 1 LOOP
1566
1567 --Writing data for new employees
1568 l_count := 0;
1569 IF j > 9 THEN
1570 /*****************l_str_total_ad := '<ASI-TOT-D>'||substr(l_fm_total_value,1,length(l_fm_total_value)-4)||'</ASI-TOT-D>';
1571 l_str_total_af := '<ASI-TOT-F>'||substr(l_fm_total_value,length(l_fm_total_value)-2)||'</ASI-TOT-F>';******************/
1572 l_str_total_d := '<TOT-D>'||substr(l_fm_total_value,1,length(l_fm_total_value)-4)||'</TOT-D>';
1573 l_str_total_f := '<TOT-F>'||substr(l_fm_total_value,length(l_fm_total_value)-2)||'</TOT-F>';
1574 /*dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_ad), l_str_total_ad);
1575 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_af), l_str_total_af);*/
1576 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_d), l_str_total_d);
1577 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_f), l_str_total_f);
1578 dbms_lob.writeAppend( l_xfdf_string, length('</EMP-REC>'),'</EMP-REC>');
1579
1580 dbms_lob.writeAppend( l_xfdf_string, length('<EMP-REC>'),'<EMP-REC>');
1581 l_str_er_name := '<NAME>'||'<![CDATA['||l_employer_name||']]>'||'</NAME>'; /* Bug No : 8299900 */
1582 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_name), l_str_er_name);
1583 /******dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_9), l_str_er_ssn_9);
1584 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_8), l_str_er_ssn_8);
1585 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_7), l_str_er_ssn_7);
1586 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_6), l_str_er_ssn_6);
1587 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_5), l_str_er_ssn_5);
1588 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_4), l_str_er_ssn_4);
1589 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_3), l_str_er_ssn_3);
1590 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_2), l_str_er_ssn_2);
1591 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_1), l_str_er_ssn_1);
1592 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_0), l_str_er_ssn_0);
1593 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn_0a), l_str_er_ssn_0a);********/
1594
1595 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_addr), l_str_er_addr);
1596 dbms_lob.writeAppend( l_xfdf_string, length(l_str_er_ssn), l_str_er_ssn);
1597
1598 dbms_lob.writeAppend( l_xfdf_string, length(l_str_month), l_str_month);
1599 dbms_lob.writeAppend( l_xfdf_string, length(l_str_year), l_str_year);
1600
1601 dbms_lob.writeAppend( l_xfdf_string, length(l_str_rep_yyyy), l_str_rep_yyyy);
1602 dbms_lob.writeAppend( l_xfdf_string, length(l_str_rep_mm), l_str_rep_mm);
1603 dbms_lob.writeAppend( l_xfdf_string, length(l_str_rep_dd), l_str_rep_dd);
1604 END IF;
1605
1606
1607 WHILE j <= i LOOP
1608
1609 l_count := l_count+1;
1610
1611 --l_fm_asi_value := to_char(t_asi_emp_rec(j).asi_value,lg_format_mask);
1612 l_fm_jps_b_value := to_char(t_asi_emp_rec(j).jps_b_value,lg_format_mask);
1613 l_fm_ul_b_value := to_char(t_asi_emp_rec(j).ul_b_value,lg_format_mask);
1614 l_fm_ci_b_value := to_char(t_asi_emp_rec(j).ci_b_value,lg_format_mask);
1615 l_fm_ua_b_value := to_char(t_asi_emp_rec(j).ua_b_value,lg_format_mask);
1616 l_fm_jps_c_value := to_char(t_asi_emp_rec(j).jps_c_value,lg_format_mask);
1617 l_fm_ci_c_value := to_char(t_asi_emp_rec(j).ci_c_value,lg_format_mask);
1618 l_fm_ua_c_value := to_char(t_asi_emp_rec(j).ua_c_value,lg_format_mask);
1619 l_fm_emp_total_value := to_char(t_asi_emp_rec(j).emp_tot_value,lg_format_mask);
1620
1621 l_str_ser := '<SER-'||l_count||'>'||j||'</SER-'||l_count||'>';
1622 l_str4 := '<EMPLOYEE-NAME-'||l_count||'>'||substr(t_asi_emp_rec(j).full_name,1,30)||'</EMPLOYEE-NAME-'||l_count||'>';
1623 l_str5 := '<EMPLOYEE-SSN-'||l_count||'>'||t_asi_emp_rec(j).ssn||'</EMPLOYEE-SSN-'||l_count||'>';
1624 /******************l_str6 := '<ASI-D-'||l_count||'>'||substr(l_fm_asi_value,1,length(l_fm_asi_value)-4)||'</ASI-D-'||l_count||'>';
1625 l_str7 := '<ASI-F-'||l_count||'>'||substr(l_fm_asi_value,length(l_fm_asi_value)-2)||'</ASI-F-'||l_count||'>';******************/
1626
1627 l_str6 := '<JPS-B-'||l_count||'>'||(l_fm_jps_b_value)||'</JPS-B-'||l_count||'>';
1628 l_str7 := '<UL-B-'||l_count||'>'||(l_fm_ul_b_value)||'</UL-B-'||l_count||'>';
1629 l_str8 := '<CI-B-'||l_count||'>'||(l_fm_ci_b_value)||'</CI-B-'||l_count||'>';
1630 l_str9 := '<UA-B-'||l_count||'>'||(l_fm_ua_b_value)||'</UA-B-'||l_count||'>';
1631 l_str10 := '<JPS-C-'||l_count||'>'||(l_fm_jps_c_value)||'</JPS-C-'||l_count||'>';
1632 l_str11 := '<CI-C-'||l_count||'>'||(l_fm_ci_c_value)||'</CI-C-'||l_count||'>';
1633 l_str12 := '<UA-C-'||l_count||'>'||(l_fm_ua_c_value)||'</UA-C-'||l_count||'>';
1634
1635 /********* l_str13 := '<TOT-D-'||l_count||'>'||substr(l_fm_asi_value,1,length(l_fm_asi_value)-4)||'</TOT-D-'||l_count||'>';
1636 l_str14 := '<TOT-F-'||l_count||'>'||substr(l_fm_asi_value,length(l_fm_asi_value)-2)||'</TOT-F-'||l_count||'>';*********/
1637
1638 l_str13 := '<TOT-D-'||l_count||'>'||substr(l_fm_emp_total_value,1,length(l_fm_emp_total_value)-4)||'</TOT-D-'||l_count||'>';
1639 l_str14 := '<TOT-F-'||l_count||'>'||substr(l_fm_emp_total_value,length(l_fm_emp_total_value)-2)||'</TOT-F-'||l_count||'>';
1640
1641 dbms_lob.writeAppend( l_xfdf_string, length(l_str_ser), l_str_ser);
1642 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4);
1643 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5);
1644 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6);
1645 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7);
1646 dbms_lob.writeAppend( l_xfdf_string, length(l_str8), l_str8);
1647 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
1648 dbms_lob.writeAppend( l_xfdf_string, length(l_str10), l_str10);
1649 dbms_lob.writeAppend( l_xfdf_string, length(l_str11), l_str11);
1650 dbms_lob.writeAppend( l_xfdf_string, length(l_str12), l_str12);
1651 dbms_lob.writeAppend( l_xfdf_string, length(l_str13), l_str13);
1652 dbms_lob.writeAppend( l_xfdf_string, length(l_str14), l_str14);
1653 --dbms_lob.writeAppend( l_xfdf_string, length('</EMP-REC>'),'</EMP-REC>');
1654
1655 l_total_amount := l_total_amount + t_asi_emp_rec(j).emp_tot_value;
1656 l_fm_total_value := to_char(l_total_amount,lg_format_mask);
1657
1658 j := j + 1;
1659 IF j > i THEN
1660 l_asi_processed := 1;
1661 END IF;
1662
1663 IF l_count = 9 THEN
1664 EXIT;
1665 END IF;
1666
1667 END LOOP;
1668
1669 END LOOP;
1670 IF i > 0 THEN
1671 /*********************** l_str_total_ad := '<ASI-TOT-D>'||substr(l_fm_total_value,1,length(l_fm_total_value)-4)||'</ASI-TOT-D>';
1672 l_str_total_af := '<ASI-TOT-F>'||substr(l_fm_total_value,length(l_fm_total_value)-2)||'</ASI-TOT-F>';*************************/
1673 l_str_total_d := '<TOT-D>'||substr(l_fm_total_value,1,length(l_fm_total_value)-4)||'</TOT-D>';
1674 l_str_total_f := '<TOT-F>'||substr(l_fm_total_value,length(l_fm_total_value)-2)||'</TOT-F>';
1675 l_str10 := '</EMP-REC>';
1676 /*********************dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_ad), l_str_total_ad);
1677 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_af), l_str_total_af);**************************/
1678 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_d), l_str_total_d);
1679 dbms_lob.writeAppend( l_xfdf_string, length(l_str_total_f), l_str_total_f);
1680 dbms_lob.writeAppend( l_xfdf_string, length(l_str10), l_str10);
1681 END IF;
1682
1683 dbms_lob.writeAppend( l_xfdf_string, length('</START>'),'</START>');
1684
1685 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob,TRUE);
1686 clob_to_blob(l_xfdf_string,l_xfdf_blob);
1687
1688 hr_utility.set_location('Finished creating xml data for Procedure report167 ',20);
1689
1690 EXCEPTION
1691 WHEN utl_file.invalid_path then
1692 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
1693 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1694 hr_utility.raise_error;
1695 --
1696 WHEN utl_file.invalid_mode then
1697 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
1698 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1699 hr_utility.raise_error;
1700 --
1701 WHEN utl_file.invalid_filehandle then
1702 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
1703 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1704 hr_utility.raise_error;
1705 --
1706 WHEN utl_file.invalid_operation then
1707 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
1708 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1709 hr_utility.raise_error;
1710 --
1711 WHEN utl_file.read_error then
1712 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
1713 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1714 hr_utility.raise_error;
1715 --
1716 WHEN others THEN
1717 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
1718 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
1719 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1720 hr_utility.raise_error;
1721
1722 END report167_2006;
1723 -------------------------------------------------------------------------------------------
1724
1725 PROCEDURE report168
1726 (p_request_id NUMBER
1727 ,p_report VARCHAR2
1728 ,p_business_group_id NUMBER
1729 ,p_employer_id NUMBER
1730 ,p_effective_month VARCHAR2
1731 ,p_effective_year VARCHAR2
1732 ,l_xfdf_blob OUT NOCOPY BLOB
1733 )
1734 AS
1735
1736 l_effective_date DATE;
1737 l_user_format VARCHAR2(80);
1738 TYPE new_assact_rec IS RECORD
1739 (person_id NUMBER
1740 ,assignment_action_id NUMBER
1741 ,date_start DATE);
1742 TYPE t_new_assact_table IS TABLE OF new_assact_rec INDEX BY BINARY_INTEGER;
1743 t_new_store_assact t_new_assact_table;
1744
1745 TYPE ter_assact_rec IS RECORD
1746 (person_id NUMBER
1747 ,assignment_action_id NUMBER
1748 ,actual_termination_date DATE
1749 ,date_earned DATE);
1750 TYPE t_ter_assact_table IS TABLE OF ter_assact_rec INDEX BY BINARY_INTEGER;
1751 t_ter_store_assact t_ter_assact_table;
1752
1753 TYPE cha_assact_rec IS RECORD
1754 (person_id NUMBER
1755 ,assignment_action_id NUMBER
1756 ,date_earned DATE
1757 ,changed_salary NUMBER
1758 ,assignment_id NUMBER);
1759 TYPE t_cha_assact_table IS TABLE OF cha_assact_rec INDEX BY BINARY_INTEGER;
1760 t_cha_store_assact t_cha_assact_table;
1761
1762 /* TYPE assact_rec IS RECORD
1763 (person_id NUMBER
1764 ,assignment_id NUMBER
1765 ,assignment_action_id NUMBER);
1766 TYPE t_assact_table IS TABLE OF assact_rec INDEX BY BINARY_INTEGER;
1767 t_store_assact t_assact_table;
1768 */
1769
1770 /*Cursor for fetching Employer SSN*/
1771 CURSOR csr_employer_ssn IS
1772 SELECT LPAD(org_information4,9,'0')
1773 FROM hr_organization_information
1774 WHERE organization_id = p_employer_id
1775 AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
1776
1777 /*Cursor for fetching Employer Name*/
1778 CURSOR csr_employer_name IS
1779 SELECT name
1780 FROM hr_organization_units
1781 WHERE organization_id = p_employer_id;
1782
1783 /*Cursor for fetching defined balance id*/
1784 CURSOR csr_get_def_bal_id(p_user_name VARCHAR2) IS
1785 SELECT u.creator_id
1786 FROM ff_user_entities u,
1787 ff_database_items d
1788 WHERE d.user_name = p_user_name
1789 AND u.user_entity_id = d.user_entity_id
1790 AND u.legislation_code = 'KW'
1791 AND u.business_group_id is null
1792 AND u.creator_type = 'B';
1793
1794 l_local_nationality VARCHAR2(80);
1795
1796 /*Cursor for fetching list of new employees*/
1797 CURSOR csr_get_new_emp IS
1798 SELECT distinct asg.person_id
1799 ,paa.assignment_action_id
1800 ,pos.date_start
1801 FROM per_assignments_f asg /*per_all_assignments_f asg*/
1802 ,pay_assignment_actions paa
1803 ,pay_payroll_actions ppa
1804 ,hr_soft_coding_keyflex hscl
1805 ,per_periods_of_service pos
1806 ,per_people_f ppf /*per_all_people_f ppf*/
1807 WHERE asg.assignment_id = paa.assignment_id
1808 AND asg.person_id = ppf.person_id
1809 AND ppf.nationality = l_local_nationality
1810 AND paa.payroll_action_id = ppa.payroll_action_id
1811 AND pos.period_of_service_id = asg.period_of_service_id
1812 AND ppa.action_type in ('R','Q')
1813 AND ppa.action_status = 'C'
1814 AND paa.action_status IN ('C','S') --10375683
1815 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
1816 AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
1817 AND trunc(pos.date_start, 'MM') = trunc(l_effective_date, 'MM')
1818 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
1819 AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
1820 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1821 AND hscl.segment1 = to_char(p_employer_id);
1822 rec_get_new_emp csr_get_new_emp%ROWTYPE;
1823
1824
1825 /*Cursor for fetching list of terminated employees*/
1826 CURSOR csr_get_ter_emp IS
1827 SELECT distinct asg.person_id
1828 ,paa.assignment_action_id
1829 ,pos.actual_termination_date
1830 ,ppa.date_earned
1831 FROM per_assignments_f asg /*per_all_assignments_f asg*/
1832 ,pay_assignment_actions paa
1833 ,pay_payroll_actions ppa
1834 ,hr_soft_coding_keyflex hscl
1835 ,per_periods_of_service pos
1836 ,per_people_f ppf /*per_all_people_f ppf*/
1837 WHERE asg.assignment_id = paa.assignment_id
1838 AND asg.person_id = ppf.person_id
1839 AND ppf.nationality = l_local_nationality
1840 AND paa.payroll_action_id = ppa.payroll_action_id
1841 AND pos.period_of_service_id = asg.period_of_service_id
1842 AND ppa.action_type in ('R','Q')
1843 AND ppa.action_status = 'C'
1844 AND paa.action_status IN ('C','S') --10375683
1845 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
1846 AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') = TRUNC(l_effective_date, 'MM')
1847 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
1848 AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
1849 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1850 AND hscl.segment1 = to_char(p_employer_id);
1851 rec_get_ter_emp csr_get_ter_emp%ROWTYPE;
1852
1853 /*Cursor for fetching effective date of salary change*/
1854 CURSOR csr_get_salary_date (p_person_id NUMBER) IS
1855 SELECT date_earned, paa.assignment_action_id
1856 FROM per_assignments_f asg /*per_all_assignments_f asg*/
1857 ,pay_assignment_actions paa
1858 ,pay_payroll_actions ppa
1859 ,per_periods_of_service pos
1860 WHERE asg.assignment_id = paa.assignment_id
1861 AND paa.payroll_action_id = ppa.payroll_action_id
1862 AND pos.period_of_service_id = asg.period_of_service_id
1863 AND ppa.action_type in ('R','Q')
1864 AND ppa.action_status = 'C'
1865 AND paa.action_status IN ('C','S') --10375683
1866 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
1867 AND trunc(ppa.date_earned, 'MM') < TRUNC(l_effective_date, 'MM')
1868 AND asg.person_id = p_person_id
1869 order by date_earned desc;
1870 rec_get_salary_date csr_get_salary_date%ROWTYPE;
1871
1872 /*Cursor for fetching list of employees who are neither new nor terminated*/
1873 CURSOR csr_get_cha_emp IS
1874 SELECT distinct asg.person_id
1875 ,asg.assignment_id
1876 ,paa.assignment_action_id
1877 ,date_earned
1878 FROM per_assignments_f asg /*per_all_assignments_f asg*/
1879 ,pay_assignment_actions paa
1880 ,pay_payroll_actions ppa
1881 ,hr_soft_coding_keyflex hscl
1882 ,per_periods_of_service pos
1883 ,per_people_f ppf /*per_all_people_f ppf*/
1884 WHERE asg.assignment_id = paa.assignment_id
1885 AND asg.person_id = ppf.person_id
1886 AND ppf.nationality = l_local_nationality
1887 AND paa.payroll_action_id = ppa.payroll_action_id
1888 AND pos.period_of_service_id = asg.period_of_service_id
1889 AND ppa.action_type in ('R','Q')
1890 AND ppa.action_status = 'C'
1891 AND paa.action_status IN ('C','S') --10375683
1892 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
1893 AND trunc(pos.date_start, 'MM') <> trunc(l_effective_date, 'MM')
1894 AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
1895 AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') >= TRUNC(l_effective_date, 'MM')
1896 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
1897 AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
1898 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1899 AND hscl.segment1 = to_char(p_employer_id);
1900 rec_get_cha_emp csr_get_cha_emp%ROWTYPE;
1901
1902
1903 /*Cursor for fetching employee name*/
1904 CURSOR csr_get_emp_name(p_person_id NUMBER) IS
1905 SELECT /*full_name
1906 hr_person_name.get_person_name
1907 (p_person_id
1908 ,l_effective_date
1909 ,'DISPLAY_NAME'
1910 ,l_user_format)*/
1911 national_identifier
1912 FROM per_people_f ppf /*per_all_people_f ppf*/
1913 WHERE person_id = p_person_id
1914 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
1915 rec_get_emp_name csr_get_emp_name%ROWTYPE;
1916
1917
1918 /* Cursor for fetching Defined balance ids from Org EIT for Social Allowance Oct 2012*/
1919 CURSOR csr_get_def_bal_ids (l_emp_id number) IS
1920 SELECT ORG_INFORMATION1
1921 FROM HR_ORGANIZATION_INFORMATION
1922 WHERE Organization_id = l_emp_id
1923 AND org_information_context = 'KW_SI_DETAILS';
1924
1925
1926 /* Cursor to fetch assignment_action_id corresponding to first_date_earned to calculate social allowance Oct 2012*/
1927 CURSOR csr_get_assact_one (l_assignment_id number,l_date date) IS
1928 select paa.assignment_action_id
1929 from pay_payroll_actions ppa, pay_assignment_actions paa
1930 Where paa.assignment_id = l_assignment_id
1931 and paa.payroll_action_id = ppa.payroll_action_id
1932 and ppa.action_type in ('R','Q')
1933 and ppa.action_status = 'C'
1934 and paa.action_status IN ('C','S') --10375683
1935 and trunc(ppa.date_earned,'MM') = trunc(l_date,'MM') ;
1936
1937
1938 /*Cursor for fetching list of employees*/
1939 /* CURSOR csr_get_emp (l_employer_id number, l_date date , l_nat varchar2) IS
1940 SELECT distinct asg.person_id
1941 ,asg.assignment_id
1942 ,paa.assignment_action_id
1943 FROM per_assignments_f asg
1944 ,pay_assignment_actions paa
1945 ,pay_payroll_actions ppa
1946 ,hr_soft_coding_keyflex hscl
1947 ,per_people_f ppf
1948 WHERE asg.assignment_id = paa.assignment_id
1949 AND paa.payroll_action_id = ppa.payroll_action_id
1950 AND ppa.action_type in ('R','Q')
1951 AND ppa.action_status = 'C'
1952 AND paa.action_status in ('C','S') --10375683
1953 AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
1954 AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
1955 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1956 AND hscl.segment1 = to_char(l_employer_id)
1957 AND ppf.person_id = asg.person_id
1958 AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
1959 AND ppf.nationality = l_nat;
1960 rec_get_emp csr_get_emp%ROWTYPE;
1961 */
1962
1963
1964
1965 l_employer_name hr_organization_units.name%TYPE;
1966 l_employer_ssn NUMBER;
1967 l_subject_si_id NUMBER;
1968 l_subject_si_val NUMBER;
1969 l_input_date VARCHAR2(30);
1970 l_full_name per_all_people_f.full_name%TYPE;
1971 l_civil_id per_all_people_f.national_identifier%TYPE;
1972 l_salary_effective_date DATE;
1973 l_diff_exist NUMBER := 0;
1974 l_prev_salary NUMBER;
1975 l_new_count NUMBER;
1976 l_new_exist NUMBER;
1977 l_recordS_exist NUMBER;
1978 i NUMBER; /*For new employees*/
1979 j NUMBER; /*For new employees*/
1980 k NUMBER; /*For terminated employees*/
1981 l NUMBER; /*For terminated employees*/
1982 m NUMBER; /*For changed employees*/
1983 n NUMBER; /*For changed employees*/
1984 l_all_processed NUMBER;
1985 l_new_processed NUMBER;
1986 l_ter_processed NUMBER;
1987 l_cha_processed NUMBER;
1988
1989 l_fm_subject_si_val VARCHAR2(50) := NULL;
1990 l_fm_changed_salary VARCHAR2(50) := NULL;
1991 l_effective_month VARCHAR2(50);
1992 l_social_id number;
1993 l_first_date_earned date;
1994 l_assact_one number;
1995 l_first_social number(15,3);
1996 l_fm_l_first_social varchar2(100);
1997 l_changed_sal number;
1998
1999 BEGIN
2000 -- hr_utility.trace_on(null,'BPK');
2001 -- hr_utility.set_location('entering 168',10);
2002
2003 set_currency_mask(p_business_group_id);
2004 l_input_date := '01-'||p_effective_month||'-'||p_effective_year;
2005 l_effective_date := last_day(to_date(l_input_date,'DD-MM-YYYY'));
2006 /*l_eff_term_date := to_date('28-'||to_char(l_effective_date,'MM-YYYY'),'DD-MM-YYYY');*/
2007
2008 hr_utility.set_location('l_input_date '|| l_input_date,20);
2009 hr_utility.set_location('l_effective_date'|| l_effective_date,30);
2010
2011 INSERT INTO fnd_sessions (session_id, effective_date)
2012 VALUES (userenv('sessionid'), l_effective_date);
2013
2014 l_user_format := NVL(FND_PROFILE.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G');
2015 l_local_nationality := NULL;
2016 BEGIN
2017 SELECT org_information1
2018 INTO l_local_nationality
2019 FROM hr_organization_information
2020 WHERE org_information_context = 'KW_BG_DETAILS'
2021 AND organization_id = p_business_group_id;
2022 EXCEPTION
2023 WHEN OTHERS THEN
2024 l_local_nationality := NULL;
2025 END;
2026
2027 -- To clear the PL/SQL Table values.
2028 vXMLTable.DELETE;
2029 vCtr := 1;
2030 hr_utility.set_location('Entering report168 ',10);
2031
2032 l_effective_month := hr_general.decode_lookup('KW_GREGORIAN_MONTH', p_effective_month);
2033
2034 vXMLTable(vCtr).TagName := 'R168-MONTH';
2035 vXMLTable(vCtr).TagValue := l_effective_month;
2036 vctr := vctr + 1;
2037
2038 vXMLTable(vCtr).TagName := 'R168-YEAR';
2039 vXMLTable(vCtr).TagValue := p_effective_year;
2040 vctr := vctr + 1;
2041
2042 vXMLTable(vCtr).TagName := 'R168-G-YYYY';
2043 vXMLTable(vCtr).TagValue := TO_CHAR(sysdate,'YYYY');
2044 vctr := vctr + 1;
2045
2046 vXMLTable(vCtr).TagName := 'R168-G-MM';
2047 vXMLTable(vCtr).TagValue := TO_CHAR(sysdate,'MM');
2048 vctr := vctr + 1;
2049
2050 vXMLTable(vCtr).TagName := 'R168-G-DD';
2051 vXMLTable(vCtr).TagValue := TO_CHAR(sysdate,'DD');
2052 vctr := vctr + 1;
2053
2054 /*Fetch Employer SSN*/
2055 OPEN csr_employer_ssn;
2056 FETCH csr_employer_ssn INTO l_employer_ssn;
2057 CLOSE csr_employer_ssn;
2058
2059 vXMLTable(vCtr).TagName := 'R168-SSN-1';
2060 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,1,1);
2061 vctr := vctr + 1;
2062 vXMLTable(vCtr).TagName := 'R168-SSN-2';
2063 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,2,1);
2064 vctr := vctr + 1;
2065 vXMLTable(vCtr).TagName := 'R168-SSN-3';
2066 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,3,1);
2067 vctr := vctr + 1;
2068 vXMLTable(vCtr).TagName := 'R168-SSN-4';
2069 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,4,1);
2070 vctr := vctr + 1;
2071 vXMLTable(vCtr).TagName := 'R168-SSN-5';
2072 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,5,1);
2073 vctr := vctr + 1;
2074 vXMLTable(vCtr).TagName := 'R168-SSN-6';
2075 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,6,1);
2076 vctr := vctr + 1;
2077 vXMLTable(vCtr).TagName := 'R168-SSN-7';
2078 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,7,1);
2079 vctr := vctr + 1;
2080 vXMLTable(vCtr).TagName := 'R168-SSN-8';
2081 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,8,1);
2082 vctr := vctr + 1;
2083 vXMLTable(vCtr).TagName := 'R168-SSN-9';
2084 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_ssn,9,1);
2085 vctr := vctr + 1;
2086
2087 /*Fetch Employer Name*/
2088 OPEN csr_employer_name;
2089 FETCH csr_employer_name INTO l_employer_name;
2090 CLOSE csr_employer_name;
2091
2092 vXMLTable(vCtr).TagName := 'R168-NAME';
2093 vXMLTable(vCtr).TagValue := l_employer_name;
2094 vctr := vctr + 1;
2095
2096 l_subject_si_id := 0;
2097
2098 /*Fetch Defined Balance Id*/
2099 OPEN csr_get_def_bal_id('SUBJECT_TO_SOCIAL_INSURANCE_ASG_RUN');
2100 FETCH csr_get_def_bal_id INTO l_subject_si_id;
2101 CLOSE csr_get_def_bal_id;
2102
2103 /* Oct 2012 for fetching Social allowance */
2104 OPEN csr_get_def_bal_ids(p_employer_id);
2105 FETCH csr_get_def_bal_ids into l_social_id;
2106 CLOSE csr_get_def_bal_ids;
2107
2108 /* OPEN csr_get_emp(p_employer_id , l_effective_date ,l_local_nationality);
2109 LOOP
2110 FETCH csr_get_emp INTO rec_get_emp;
2111 EXIT WHEN csr_get_emp%NOTFOUND;
2112 i := i + 1;
2113 t_store_assact(i).person_id := rec_get_emp.person_id;
2114 t_store_assact(i).assignment_id := rec_get_emp.assignment_id;
2115 t_store_assact(i).assignment_action_id := rec_get_emp.assignment_action_id;
2116 END LOOP;
2117 CLOSE csr_get_emp; */
2118
2119
2120 /*Set Contexts and then fetch the balance values*/
2121 l_subject_si_val := 0;
2122
2123 i := 0;
2124 k := 0;
2125 m := 0;
2126
2127 OPEN csr_get_new_emp;
2128 LOOP
2129 FETCH csr_get_new_emp INTO rec_get_new_emp;
2130 EXIT WHEN csr_get_new_emp%NOTFOUND;
2131 i := i + 1;
2132 t_new_store_assact(i).person_id := rec_get_new_emp.person_id;
2133 t_new_store_assact(i).assignment_action_id := rec_get_new_emp.assignment_action_id;
2134 t_new_store_assact(i).date_start := rec_get_new_emp.date_start;
2135 END LOOP;
2136 CLOSE csr_get_new_emp;
2137
2138 OPEN csr_get_ter_emp;
2139 LOOP
2140 FETCH csr_get_ter_emp INTO rec_get_ter_emp;
2141 EXIT WHEN csr_get_ter_emp%NOTFOUND;
2142 k := k + 1;
2143 t_ter_store_assact(k).person_id := rec_get_ter_emp.person_id;
2144 t_ter_store_assact(k).assignment_action_id := rec_get_ter_emp.assignment_action_id;
2145 t_ter_store_assact(k).actual_termination_date := rec_get_ter_emp.actual_termination_date;
2146 t_ter_store_assact(k).date_earned := rec_get_ter_emp.date_earned;
2147 END LOOP;
2148 CLOSE csr_get_ter_emp;
2149
2150 OPEN csr_get_cha_emp;
2151 LOOP
2152 FETCH csr_get_cha_emp INTO rec_get_cha_emp;
2153 EXIT WHEN csr_get_cha_emp%NOTFOUND;
2154 l_diff_exist := 0;
2155 l_subject_si_val := 0;
2156 l_subject_si_val := pay_balance_pkg.get_value(l_subject_si_id,rec_get_cha_emp.assignment_action_id);
2157 l_salary_effective_date := rec_get_cha_emp.date_earned;
2158 OPEN csr_get_salary_date (rec_get_cha_emp.person_id);
2159 LOOP
2160 FETCH csr_get_salary_date INTO rec_get_salary_date;
2161 EXIT WHEN csr_get_salary_date%NOTFOUND;
2162 l_prev_salary := pay_balance_pkg.get_value(l_subject_si_id,rec_get_salary_date.assignment_action_id);
2163 IF l_prev_salary <> l_subject_si_val THEN
2164 l_diff_exist := 1;
2165 EXIT;
2166 END IF;
2167 EXIT;
2168 END LOOP;
2169 CLOSE csr_get_salary_date;
2170 IF l_diff_exist = 1 THEN
2171 m := m + 1;
2172
2173 t_cha_store_assact(m).person_id := rec_get_cha_emp.person_id;
2174 t_cha_store_assact(m).assignment_action_id := rec_get_cha_emp.assignment_action_id;
2175 t_cha_store_assact(m).date_earned := rec_get_cha_emp.date_earned;
2176 t_cha_store_assact(m).changed_salary := l_subject_si_val;
2177 t_cha_store_assact(m).assignment_id := rec_get_cha_emp.assignment_id; -- oct 2012
2178 END IF;
2179 END LOOP;
2180 CLOSE csr_get_cha_emp;
2181
2182 j := 1;
2183 l := 1;
2184 n := 1;
2185 IF i > 0 THEN
2186 l_new_processed := 0;
2187 ELSE
2188 l_new_processed := 1;
2189 END IF;
2190 IF k > 0 THEN
2191 l_ter_processed := 0;
2192 ELSE
2193 l_ter_processed := 1;
2194 END IF;
2195 IF m > 0 THEN
2196 l_cha_processed := 0;
2197 ELSE
2198 l_cha_processed := 1;
2199 END IF;
2200
2201 l_all_processed := 0;
2202 WHILE l_all_processed <> 1 LOOP
2203
2204 if l_effective_date <= to_date('30-09-2012','DD-MM-YYYY') then
2205 hr_utility.set_location('inside sep 2012 or less'|| l_effective_date,30);
2206 --Writing data for new employees
2207 l_new_count := 0;
2208 WHILE j <= i LOOP
2209
2210 l_subject_si_val := 0;
2211 l_subject_si_val := pay_balance_pkg.get_value(l_subject_si_id,t_new_store_assact(j).assignment_action_id);
2212 l_civil_id := NULL;
2213 l_full_name := NULL;
2214 OPEN csr_get_emp_name(t_new_store_assact(j).person_id);
2215 FETCH csr_get_emp_name INTO /*l_full_name,*/ l_civil_id;
2216 CLOSE csr_get_emp_name;
2217
2218 l_full_name := hr_person_name.get_person_name
2219 (p_person_id => t_new_store_assact(j).person_id
2220 ,p_effective_date => l_effective_date
2221 ,p_format_name => 'FULL_NAME' --'DISPLAY_NAME'
2222 ,p_user_format_choice => l_user_format);
2223
2224 l_new_count := l_new_count+1;
2225
2226 l_fm_subject_si_val := to_char(l_subject_si_val,lg_format_mask);
2227
2228 vXMLTable(vCtr).TagName := 'R168-N-SEQ-'||l_new_count;
2229 vXMLTable(vCtr).TagValue := j;
2230 vctr := vctr + 1;
2231 vXMLTable(vCtr).TagName := 'R168-NEE-NAME-'||l_new_count;
2232 vXMLTable(vCtr).TagValue := SUBSTR(l_full_name,1,30);
2233 vctr := vctr + 1;
2234 vXMLTable(vCtr).TagName := 'R168-NEE-CID-'||l_new_count;
2235 vXMLTable(vCtr).TagValue := l_civil_id;
2236 vctr := vctr + 1;
2237 vXMLTable(vCtr).TagName := 'R168-NEE-HIRE-'||l_new_count;
2238 vXMLTable(vCtr).TagValue := t_new_store_assact(j).date_start;
2239 vctr := vctr + 1;
2240 vXMLTable(vCtr).TagName := 'R168-NEE-SAL-D-'||l_new_count;
2241 --vXMLTable(vCtr).TagValue := TRUNC(l_subject_si_val);
2242 vXMLTable(vCtr).TagValue := substr(l_fm_subject_si_val,1,length(l_fm_subject_si_val)-4);
2243 vctr := vctr + 1;
2244 vXMLTable(vCtr).TagName := 'R168-NEE-SAL-F-'||l_new_count;
2245 --vXMLTable(vCtr).TagValue := l_subject_si_val - TRUNC(l_subject_si_val);
2246 vXMLTable(vCtr).TagValue := substr(l_fm_subject_si_val,length(l_fm_subject_si_val)-2);
2247 vctr := vctr + 1;
2248
2249 j := j + 1;
2250 IF j > i THEN
2251 l_new_processed := 1;
2252 END IF;
2253
2254 IF l_new_count = 8 THEN
2255 EXIT;
2256 END IF;
2257
2258 END LOOP;
2259
2260
2261 --Writing data for terminated employees
2262 l_new_count := 0;
2263 WHILE l <= k LOOP
2264 l_subject_si_val := 0;
2265 l_subject_si_val := pay_balance_pkg.get_value(l_subject_si_id,t_ter_store_assact(l).assignment_action_id);
2266 l_civil_id := NULL;
2267 l_full_name := NULL;
2268 OPEN csr_get_emp_name(t_ter_store_assact(l).person_id);
2269 FETCH csr_get_emp_name INTO /*l_full_name,*/ l_civil_id;
2270
2271
2272 CLOSE csr_get_emp_name;
2273 l_full_name := hr_person_name.get_person_name
2274 (p_person_id => t_ter_store_assact(l).person_id
2275 ,p_effective_date => l_effective_date
2276 ,p_format_name => 'FULL_NAME' --'DISPLAY_NAME'
2277 ,p_user_format_choice => l_user_format);
2278
2279
2280 l_new_count := l_new_count+1;
2281
2282 l_fm_subject_si_val := to_char(l_subject_si_val,lg_format_mask);
2283
2284 vXMLTable(vCtr).TagName := 'R168-T-SEQ-'||l_new_count;
2285 vXMLTable(vCtr).TagValue := l;
2286 vctr := vctr + 1;
2287 vXMLTable(vCtr).TagName := 'R168-TEE-NAME-'||l_new_count;
2288 vXMLTable(vCtr).TagValue := SUBSTR(l_full_name,1,30);
2289 vctr := vctr + 1;
2290 vXMLTable(vCtr).TagName := 'R168-TEE-CID-'||l_new_count;
2291 vXMLTable(vCtr).TagValue := l_civil_id;
2292 vctr := vctr + 1;
2293 vXMLTable(vCtr).TagName := 'R168-TEE-TER-'||l_new_count;
2294 vXMLTable(vCtr).TagValue := t_ter_store_assact(l).actual_termination_date;
2295 vctr := vctr + 1;
2296 vXMLTable(vCtr).TagName := 'R168-TEE-SAL-D-'||l_new_count;
2297 --vXMLTable(vCtr).TagValue := TRUNC(l_subject_si_val);
2298 vXMLTable(vCtr).TagValue := substr(l_fm_subject_si_val,1,length(l_fm_subject_si_val)-4);
2299 vctr := vctr + 1;
2300 vXMLTable(vCtr).TagName := 'R168-TEE-SAL-F-'||l_new_count;
2301 --vXMLTable(vCtr).TagValue := l_subject_si_val - TRUNC(l_subject_si_val);
2302 vXMLTable(vCtr).TagValue := substr(l_fm_subject_si_val,length(l_fm_subject_si_val)-2);
2303 vctr := vctr + 1;
2304
2305 l_salary_effective_date := t_ter_store_assact(l).date_earned;
2306 OPEN csr_get_salary_date (t_ter_store_assact(l).person_id);
2307 LOOP
2308 FETCH csr_get_salary_date INTO rec_get_salary_date;
2309 EXIT WHEN csr_get_salary_date%NOTFOUND;
2310 l_prev_salary := pay_balance_pkg.get_value(l_subject_si_id,rec_get_salary_date.assignment_action_id);
2311 IF l_prev_salary <> l_subject_si_val THEN
2312 EXIT;
2313 ELSE
2314 l_salary_effective_date := rec_get_salary_date.date_earned;
2315 END IF;
2316 END LOOP;
2317 CLOSE csr_get_salary_date;
2318
2319 vXMLTable(vCtr).TagName := 'R168-TEE-SAL-DATE-'||l_new_count;
2320 vXMLTable(vCtr).TagValue := TRUNC(l_salary_effective_date);
2321 vctr := vctr + 1;
2322
2323 l := l + 1;
2324 IF l > k THEN
2325 l_ter_processed := 1;
2326 END IF;
2327 IF l_new_count = 8 THEN
2328 EXIT;
2329 END IF;
2330
2331 END LOOP;
2332
2333 --Writing data for employees with changed salary
2334 l_new_count := 0;
2335 WHILE n <= m LOOP
2336 l_subject_si_val := 0;
2337 l_subject_si_val := pay_balance_pkg.get_value(l_subject_si_id,t_cha_store_assact(n).assignment_action_id);
2338 l_civil_id := NULL;
2339 l_full_name := NULL;
2340 OPEN csr_get_emp_name(t_cha_store_assact(n).person_id);
2341 FETCH csr_get_emp_name INTO /*l_full_name,*/ l_civil_id;
2342 CLOSE csr_get_emp_name;
2343 l_full_name := hr_person_name.get_person_name
2344 (p_person_id => t_cha_store_assact(n).person_id
2345 ,p_effective_date => l_effective_date
2346 ,p_format_name => 'FULL_NAME' --'DISPLAY_NAME'
2347 ,p_user_format_choice => l_user_format);
2348
2349
2350 l_new_count := l_new_count+1;
2351
2352 l_fm_changed_salary := to_char(t_cha_store_assact(n).changed_salary,lg_format_mask);
2353 vXMLTable(vCtr).TagName := 'R168-C-SEQ-'||l_new_count;
2354 vXMLTable(vCtr).TagValue := n;
2355 vctr := vctr + 1;
2356 vXMLTable(vCtr).TagName := 'R168-CEE-NAME-'||l_new_count;
2357 vXMLTable(vCtr).TagValue := SUBSTR(l_full_name,1,30);
2358 vctr := vctr + 1;
2359 vXMLTable(vCtr).TagName := 'R168-CEE-CID-'||l_new_count;
2360 vXMLTable(vCtr).TagValue := l_civil_id;
2361 vctr := vctr + 1;
2362 vXMLTable(vCtr).TagName := 'R168-CEE-SAL-D-'||l_new_count;
2363 --vXMLTable(vCtr).TagValue := TRUNC(t_cha_store_assact(n).changed_salary);
2364 vXMLTable(vCtr).TagValue := substr(l_fm_changed_salary,1,length(l_fm_changed_salary)-4);
2365 vctr := vctr + 1;
2366 vXMLTable(vCtr).TagName := 'R168-CEE-SAL-F-'||l_new_count;
2367 --vXMLTable(vCtr).TagValue := t_cha_store_assact(n).changed_salary - TRUNC(t_cha_store_assact(n).changed_salary);
2368 vXMLTable(vCtr).TagValue := substr(l_fm_changed_salary,length(l_fm_changed_salary)-2);
2369 vctr := vctr + 1;
2370 vXMLTable(vCtr).TagName := 'R168-CEE-SAL-DATE-'||l_new_count;
2371 vXMLTable(vCtr).TagValue := TRUNC(t_cha_store_assact(n).date_earned);
2372 vctr := vctr + 1;
2373
2374 n := n + 1;
2375 IF n > m THEN
2376 l_cha_processed := 1;
2377 END IF;
2378 IF l_new_count = 10 THEN
2379 EXIT;
2380 END IF;
2381
2382 END LOOP;
2383
2384 else
2385 hr_utility.set_location('inside oct 2012 or more'|| l_effective_date,30);
2386 l_new_count := 0;
2387 -- WHILE j <= i or l <= k LOOP /*loop for both new and terminated sicne now we report both in a single table */
2388 while j <= i loop
2389 l_subject_si_val := 0;
2390 l_subject_si_val := pay_balance_pkg.get_value(l_subject_si_id,t_new_store_assact(j).assignment_action_id);
2391 l_civil_id := NULL;
2392 l_full_name := NULL;
2393
2394 hr_utility.set_location('l_subject_si_val'|| l_subject_si_val,30);
2395
2396 OPEN csr_get_emp_name(t_new_store_assact(j).person_id);
2397 FETCH csr_get_emp_name INTO /*l_full_name,*/ l_civil_id;
2398 CLOSE csr_get_emp_name;
2399
2400 hr_utility.set_location('l_civil_id'|| l_civil_id,30);
2401
2402 l_full_name := hr_person_name.get_person_name
2403 (p_person_id => t_new_store_assact(j).person_id
2404 ,p_effective_date => l_effective_date
2405 ,p_format_name => 'FULL_NAME' --'DISPLAY_NAME'
2406 ,p_user_format_choice => l_user_format);
2407
2408 hr_utility.set_location('l_full_name'|| l_full_name,30);
2409
2410 l_new_count := l_new_count+1;
2411
2412 l_fm_subject_si_val := to_char(l_subject_si_val,lg_format_mask);
2413
2414 hr_utility.set_location('l_fm_subject_si_val'|| l_fm_subject_si_val,30);
2415
2416 -- vXMLTable(vCtr).TagName := 'R168-N-SEQ-'||l_new_count;
2417 -- vXMLTable(vCtr).TagValue := j;
2418 -- vctr := vctr + 1;
2419 vXMLTable(vCtr).TagName := 'R168-NEE-NAME-'||l_new_count;
2420 vXMLTable(vCtr).TagValue := SUBSTR(l_full_name,1,30);
2421 vctr := vctr + 1;
2422 vXMLTable(vCtr).TagName := 'R168-NEE-CID-'||l_new_count;
2423 vXMLTable(vCtr).TagValue := l_civil_id;
2424 vctr := vctr + 1;
2425 vXMLTable(vCtr).TagName := 'R168-NEE-HIRE-'||l_new_count;
2426 vXMLTable(vCtr).TagValue := t_new_store_assact(j).date_start;
2427 vctr := vctr + 1;
2428 -- vXMLTable(vCtr).TagName := 'R168-NEE-SAL-D-'||l_new_count;
2429 --vXMLTable(vCtr).TagValue := TRUNC(l_subject_si_val);
2430 -- vXMLTable(vCtr).TagValue := substr(l_fm_subject_si_val,1,length(l_fm_subject_si_val)-4);
2431 -- vctr := vctr + 1;
2432 -- vXMLTable(vCtr).TagName := 'R168-NEE-SAL-F-'||l_new_count;
2433 --vXMLTable(vCtr).TagValue := l_subject_si_val - TRUNC(l_subject_si_val);
2434 -- vXMLTable(vCtr).TagValue := substr(l_fm_subject_si_val,length(l_fm_subject_si_val)-2);
2435 -- vctr := vctr + 1;
2436
2437 j := j + 1;
2438 hr_utility.set_location('j'|| j,30);
2439
2440 IF j > i THEN
2441 l_new_processed := 1;
2442 hr_utility.set_location('l_new_processed'|| l_new_processed,30);
2443 END IF;
2444
2445 IF l_new_count = 4 THEN /* changed from 8 to 4 for the new template */
2446 EXIT;
2447 END IF;
2448
2449 end loop;
2450
2451 while l <= k LOOP
2452
2453 IF l_new_count = 4 THEN /* changed from 8 to 4 for the new template */
2454 EXIT;
2455 END IF;
2456
2457 l_subject_si_val := 0;
2458 l_subject_si_val := pay_balance_pkg.get_value(l_subject_si_id,t_ter_store_assact(l).assignment_action_id);
2459 l_civil_id := NULL;
2460 l_full_name := NULL;
2461
2462 OPEN csr_get_emp_name(t_ter_store_assact(l).person_id);
2463 FETCH csr_get_emp_name INTO /*l_full_name,*/ l_civil_id;
2464 CLOSE csr_get_emp_name;
2465
2466 hr_utility.set_location('l_civil_id'|| l_civil_id,30);
2467 l_full_name := hr_person_name.get_person_name
2468 (p_person_id => t_ter_store_assact(l).person_id
2469 ,p_effective_date => l_effective_date
2470 ,p_format_name => 'FULL_NAME' --'DISPLAY_NAME'
2471 ,p_user_format_choice => l_user_format);
2472
2473 hr_utility.set_location('l_full_name'|| l_full_name,30);
2474
2475 l_new_count := l_new_count+1;
2476
2477 l_fm_subject_si_val := to_char(l_subject_si_val,lg_format_mask);
2478
2479
2480 hr_utility.set_location('l_subject_si_val'|| l_subject_si_val,30);
2481
2482 hr_utility.set_location('l_fm_subject_si_val'|| l_fm_subject_si_val,30);
2483
2484 /* vXMLTable(vCtr).TagName := 'R168-T-SEQ-'||l_new_count;
2485 vXMLTable(vCtr).TagValue := l;
2486 vctr := vctr + 1;
2487 */
2488 vXMLTable(vCtr).TagName := 'R168-NEE-NAME-'||l_new_count; /* changed */
2489 vXMLTable(vCtr).TagValue := SUBSTR(l_full_name,1,30);
2490 vctr := vctr + 1;
2491 vXMLTable(vCtr).TagName := 'R168-NEE-CID-'||l_new_count; /* changed */
2492 vXMLTable(vCtr).TagValue := l_civil_id;
2493 vctr := vctr + 1;
2494 vXMLTable(vCtr).TagName := 'R168-TEE-TER-'||l_new_count;
2495 vXMLTable(vCtr).TagValue := t_ter_store_assact(l).actual_termination_date;
2496 vctr := vctr + 1;
2497 -- vXMLTable(vCtr).TagName := 'R168-TEE-SAL-D-'||l_new_count;
2498 --vXMLTable(vCtr).TagValue := TRUNC(l_subject_si_val);
2499 -- vXMLTable(vCtr).TagValue := substr(l_fm_subject_si_val,1,length(l_fm_subject_si_val)-4);
2500 -- vctr := vctr + 1;
2501 -- vXMLTable(vCtr).TagName := 'R168-TEE-SAL-F-'||l_new_count;
2502 --vXMLTable(vCtr).TagValue := l_subject_si_val - TRUNC(l_subject_si_val);
2503 -- vXMLTable(vCtr).TagValue := substr(l_fm_subject_si_val,length(l_fm_subject_si_val)-2);
2504 -- vctr := vctr + 1;
2505
2506 /* l_salary_effective_date := t_ter_store_assact(l).date_earned;
2507 OPEN csr_get_salary_date (t_ter_store_assact(l).person_id);
2508 LOOP
2509 FETCH csr_get_salary_date INTO rec_get_salary_date;
2510 EXIT WHEN csr_get_salary_date%NOTFOUND;
2511 l_prev_salary := pay_balance_pkg.get_value(l_subject_si_id,rec_get_salary_date.assignment_action_id);
2512 IF l_prev_salary <> l_subject_si_val THEN
2513 EXIT;
2514 ELSE
2515 l_salary_effective_date := rec_get_salary_date.date_earned;
2516 END IF;
2517 END LOOP;
2518 CLOSE csr_get_salary_date;
2519
2520
2521 hr_utility.set_location('l_salary_effective_date'|| l_salary_effective_date,30);
2522
2523 vXMLTable(vCtr).TagName := 'R168-TEE-SAL-DATE-'||l_new_count;
2524 vXMLTable(vCtr).TagValue := TRUNC(l_salary_effective_date);
2525 vctr := vctr + 1;
2526 */
2527
2528 l := l + 1;
2529 IF l > k THEN
2530 l_ter_processed := 1;
2531 END IF;
2532 end loop;
2533
2534 -- IF l_new_count = 4 THEN /* changed from 8 to 4 for the new template */
2535 -- EXIT;
2536 -- END IF;
2537
2538 -- END LOOP;
2539
2540 --Writing data for employees with changed salary
2541 l_new_count := 0;
2542 WHILE n <= m LOOP
2543 hr_utility.set_location('BPK Writing data for employees with changed salary',30);
2544 l_subject_si_val := 0;
2545 l_subject_si_val := pay_balance_pkg.get_value(l_subject_si_id,t_cha_store_assact(n).assignment_action_id);
2546 l_civil_id := NULL;
2547 l_full_name := NULL;
2548 l_assact_one := null;
2549
2550 hr_utility.set_location('l_subject_si_val'|| l_subject_si_val,30);
2551
2552 OPEN csr_get_emp_name(t_cha_store_assact(n).person_id);
2553 FETCH csr_get_emp_name INTO /*l_full_name,*/ l_civil_id;
2554 CLOSE csr_get_emp_name;
2555 l_full_name := hr_person_name.get_person_name
2556 (p_person_id => t_cha_store_assact(n).person_id
2557 ,p_effective_date => l_effective_date
2558 ,p_format_name => 'FULL_NAME' --'DISPLAY_NAME'
2559 ,p_user_format_choice => l_user_format);
2560 hr_utility.set_location('l_full_name'|| l_full_name,30);
2561
2562 /* Get the assact id corresponding to the first_assact_date calculated above Oct 2012 for report 168*/
2563 OPEN csr_get_assact_one (t_cha_store_assact(n).assignment_id,t_cha_store_assact(n).date_earned);
2564 FETCH csr_get_assact_one into l_assact_one;
2565 CLOSE csr_get_assact_one;
2566 hr_utility.set_location('l_social_id'|| l_social_id ,30);
2567 hr_utility.set_location('l_assact_one'|| l_assact_one ,30);
2568 If l_social_id is not null THEN
2569 If l_assact_one is not null then
2570 l_first_social := pay_balance_pkg.get_value(l_social_id,l_assact_one);
2571 Else
2572 l_first_social := 0;
2573 End If;
2574 Else
2575 l_first_social := 0;
2576 End If;
2577 hr_utility.set_location('l_first_social'|| l_first_social ,30);
2578 l_new_count := l_new_count+1;
2579 hr_utility.set_location('t_cha_store_assact(n).changed_salary'|| t_cha_store_assact(n).changed_salary ,30);
2580 l_changed_sal := t_cha_store_assact(n).changed_salary - l_first_social; /* salary = salary - Social Allowance */
2581
2582 -- l_changed_sal := l_subject_si_val - l_first_social;
2583
2584 hr_utility.set_location('l_changed_sal'|| l_changed_sal ,30);
2585
2586 l_fm_l_first_social := to_char(l_first_social,lg_format_mask); -- added for Oct 2012 report 168 change
2587
2588 -- l_fm_changed_salary := to_char(t_cha_store_assact(n).changed_salary,lg_format_mask);
2589 l_fm_changed_salary := to_char(l_changed_sal,lg_format_mask);
2590
2591 hr_utility.set_location('l_fm_l_first_social'|| l_fm_l_first_social ,30);
2592
2593 hr_utility.set_location('l_fm_changed_salary'|| l_fm_changed_salary ,30);
2594
2595 /* vXMLTable(vCtr).TagName := 'R168-C-SEQ-'||l_new_count;
2596 vXMLTable(vCtr).TagValue := n;
2597 vctr := vctr + 1;
2598 */
2599 vXMLTable(vCtr).TagName := 'R168-CEE-NAME-'||l_new_count;
2600 vXMLTable(vCtr).TagValue := SUBSTR(l_full_name,1,30);
2601 vctr := vctr + 1;
2602 vXMLTable(vCtr).TagName := 'R168-CEE-CID-'||l_new_count;
2603 vXMLTable(vCtr).TagValue := l_civil_id;
2604 vctr := vctr + 1;
2605 vXMLTable(vCtr).TagName := 'R168-CEE-SAL-D-'||l_new_count;
2606 --vXMLTable(vCtr).TagValue := TRUNC(t_cha_store_assact(n).changed_salary);
2607 vXMLTable(vCtr).TagValue := substr(l_fm_changed_salary,1,length(l_fm_changed_salary)-4);
2608 vctr := vctr + 1;
2609 vXMLTable(vCtr).TagName := 'R168-CEE-SAL-F-'||l_new_count;
2610 --vXMLTable(vCtr).TagValue := t_cha_store_assact(n).changed_salary - TRUNC(t_cha_store_assact(n).changed_salary);
2611 vXMLTable(vCtr).TagValue := substr(l_fm_changed_salary,length(l_fm_changed_salary)-2);
2612 vctr := vctr + 1;
2613 vXMLTable(vCtr).TagName := 'R168-CEE-SAL-DATE-'||l_new_count;
2614 vXMLTable(vCtr).TagValue := TRUNC(t_cha_store_assact(n).date_earned);
2615 vctr := vctr + 1;
2616
2617 /* added for Oct 2012 change */
2618 vXMLTable(vCtr).TagName := 'R168-CEE-SOC-ALLOW-D-'||l_new_count;
2619 vXMLTable(vCtr).TagValue := substr(l_fm_l_first_social,1,length(l_fm_l_first_social)-4);
2620 vctr := vctr + 1;
2621
2622 vXMLTable(vCtr).TagName := 'R168-CEE-SOC-ALLOW-F-'||l_new_count;
2623 vXMLTable(vCtr).TagValue := substr(l_fm_l_first_social,length(l_fm_l_first_social)-2);
2624 vctr := vctr + 1;
2625
2626
2627 n := n + 1;
2628 IF n > m THEN
2629 l_cha_processed := 1;
2630 END IF;
2631 IF l_new_count = 5 THEN /* changed from 10 to 5 */
2632 EXIT;
2633 END IF;
2634
2635 END LOOP;
2636
2637 end if;
2638
2639 hr_utility.set_location('out to page-break',30);
2640
2641 vXMLTable(vCtr).TagName := 'PAGE-BK';
2642 vXMLTable(vCtr).TagValue := ' ';
2643 vctr := vctr + 1;
2644
2645 IF l_ter_processed = 1 AND l_new_processed = 1 AND l_cha_processed = 1 THEN
2646 l_all_processed := 1;
2647 END IF;
2648
2649 END LOOP;
2650
2651 hr_utility.set_location('Finished creating xml data for Procedure report168 ',20);
2652
2653 WritetoCLOB ( l_xfdf_blob );
2654
2655 EXCEPTION
2656 WHEN utl_file.invalid_path then
2657 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
2658 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2659 hr_utility.raise_error;
2660 --
2661 WHEN utl_file.invalid_mode then
2662 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
2663 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2664 hr_utility.raise_error;
2665 --
2666 WHEN utl_file.invalid_filehandle then
2667 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
2668 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2669 hr_utility.raise_error;
2670 --
2671 WHEN utl_file.invalid_operation then
2672 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
2673 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2674 hr_utility.raise_error;
2675 --
2676 WHEN utl_file.read_error then
2677 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
2678 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2679 hr_utility.raise_error;
2680 --
2681 WHEN others THEN
2682 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
2683 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
2684 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2685 hr_utility.raise_error;
2686
2687 END report168;
2688
2689 -------------------------------------------------------------------------------------------
2690
2691
2692
2693
2694 ----------------------------------------------
2695 PROCEDURE WritetoCLOB
2696 (p_xfdf_blob out nocopy blob)
2697 IS
2698 l_xfdf_string clob;
2699 l_str1 varchar2(1000);
2700 l_str2 varchar2(20);
2701 l_str3 varchar2(20);
2702 l_str4 varchar2(20);
2703 l_str5 varchar2(20);
2704 l_str6 varchar2(30);
2705 l_str7 varchar2(1000);
2706 l_str8 varchar2(240);
2707 l_str9 varchar2(240);
2708 BEGIN
2709 hr_utility.set_location('Entered Procedure Write to clob ',100);
2710 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
2711 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
2712 <fields> ' ;
2713 l_str2 := '<field name="';
2714 l_str3 := '">';
2715 l_str4 := '<value>' ;
2716 l_str5 := '</value> </field>' ;
2717 l_str6 := '</fields> </xfdf>';
2718 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
2719 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
2720 <fields>
2721 </fields> </xfdf>';
2722 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
2723 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
2724 if vXMLTable.COUNT > 0 then
2725 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
2726 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
2727 l_str8 := vXMLTable(ctr_table).TagName;
2728 l_str9 := vXMLTable(ctr_table).TagValue;
2729 if (l_str9 is not null) then
2730 /* Added CDATA to handle special characters Bug No:8299900 */
2731 l_str9 := '<![CDATA['||l_str9||']]>';
2732 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
2733 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
2734 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
2735 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
2736 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
2737 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
2738 elsif (l_str9 is null and l_str8 is not null) then
2739 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
2740 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
2741 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
2742 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
2743 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
2744 else
2745 null;
2746 end if;
2747 END LOOP;
2748 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
2749 else
2750 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
2751 end if;
2752 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
2753 clob_to_blob(l_xfdf_string,p_xfdf_blob);
2754 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
2755 --return p_xfdf_blob;
2756 EXCEPTION
2757 WHEN OTHERS then
2758 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
2759 HR_UTILITY.RAISE_ERROR;
2760 END WritetoCLOB;
2761 ----------------------------------------------------------------
2762 Procedure clob_to_blob
2763 (p_clob clob,
2764 p_blob IN OUT NOCOPY Blob)
2765 is
2766 l_length_clob number;
2767 l_offset pls_integer;
2768 l_varchar_buffer varchar2(32767);
2769 l_raw_buffer raw(32767);
2770 l_buffer_len number;
2771 l_chunk_len number;
2772 l_blob blob;
2773 g_nls_db_char varchar2(60);
2774
2775 l_raw_buffer_len pls_integer;
2776 l_blob_offset pls_integer := 1;
2777
2778 begin
2779 l_buffer_len := 20000;
2780 hr_utility.set_location('Entered Procedure clob to blob',120);
2781 select userenv('LANGUAGE') into g_nls_db_char from dual;
2782 l_length_clob := dbms_lob.getlength(p_clob);
2783 l_offset := 1;
2784 while l_length_clob > 0 loop
2785 hr_utility.trace('l_length_clob '|| l_length_clob);
2786 if l_length_clob < l_buffer_len then
2787 l_chunk_len := l_length_clob;
2788 else
2789 l_chunk_len := l_buffer_len;
2790 end if;
2791 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
2792 --l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
2793 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char);
2794 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));
2795
2796 fnd_file.put_line(fnd_file.log, l_varchar_buffer);
2797
2798 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
2799 --dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
2800 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
2801 l_blob_offset := l_blob_offset + l_raw_buffer_len;
2802
2803 l_offset := l_offset + l_chunk_len;
2804 l_length_clob := l_length_clob - l_chunk_len;
2805 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
2806 end loop;
2807 hr_utility.set_location('Finished Procedure clob to blob ',130);
2808 end clob_to_blob;
2809
2810 ------------------------------------------------------------------
2811 Procedure fetch_pdf_blob
2812 (p_report in varchar2,
2813 p_effective_month varchar2,
2814 p_effective_year varchar2,
2815 p_pdf_blob OUT NOCOPY blob)
2816 IS
2817 BEGIN
2818 IF (p_report='REPORT166') THEN
2819 /* Bug 9719231 */
2820 IF last_day(to_date('01-' || p_effective_month || '-' || p_effective_year,'DD-MM-YYYY')) between last_day(to_date('01-01-2005','DD-MM-YYYY')) and last_day(to_date('01-07-2010','DD-MM-YYYY')) then
2821 Select file_data
2822 Into p_pdf_blob
2823 From fnd_lobs
2824 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_R166_ar_KW.pdf'
2825 and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
2826 ELSIF last_day(to_date('01-' || p_effective_month || '-' || p_effective_year,'DD-MM-YYYY')) between last_day(to_date('01-08-2010','DD-MM-YYYY')) and last_day(to_date('01-09-2012','DD-MM-YYYY')) then
2827 -- ELSE
2828 /* 2012 Oct 166 change Bug 14704605 */
2829 Select file_data
2830 Into p_pdf_blob
2831 From fnd_lobs
2832 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_166_10_ar_KW.pdf'
2833 and effective_start_date between to_date('01-08-2010','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
2834
2835 ELSE
2836 Select file_data
2837 Into p_pdf_blob
2838 From fnd_lobs
2839 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_166_12_ar_KW.pdf'
2840 and effective_start_date between to_date('01-10-2012','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
2841
2842 END IF;
2843 ELSIF (p_report = 'REPORT167') THEN
2844 IF g_report_old = 'Y' THEN
2845 Select file_data
2846 Into p_pdf_blob
2847 From fnd_lobs
2848 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAYR167_ar_KW.rtf'
2849 and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
2850 ELSE
2851 Select file_data
2852 Into p_pdf_blob
2853 From fnd_lobs
2854 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_167_06_ar_KW.rtf'
2855 and effective_start_date between to_date('01-01-2006','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
2856 END IF;
2857
2858 ELSIF (p_report ='REPORT168') THEN
2859 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
2860 Select file_data
2861 Into p_pdf_blob
2862 From fnd_lobs
2863 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_R168_ar_KW.pdf'
2864 and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
2865 ELSE
2866 Select file_data
2867 Into p_pdf_blob
2868 From fnd_lobs
2869 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_168_12_ar_KW.pdf'
2870 and effective_start_date between to_date('01-10-2012','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
2871
2872 END IF;
2873 END IF;
2874 EXCEPTION
2875 when no_data_found then
2876 null;
2877 END fetch_pdf_blob;
2878
2879 -------------------------------------------------------------------
2880
2881
2882 PROCEDURE WritetoXML (
2883 p_request_id in number,
2884 p_report in varchar2,
2885 p_output_fname out nocopy varchar2)
2886 IS
2887 p_l_fp UTL_FILE.FILE_TYPE;
2888 l_audit_log_dir varchar2(500);
2889 l_file_name varchar2(50);
2890 l_check_flag number;
2891 BEGIN
2892 l_audit_log_dir := '/sqlcom/outbound';
2893 /*Msg in the temorary table*/
2894 --insert into tstmsg values('Entered the procedure WritetoXML.');
2895 -----------------------------------------------------------------------------
2896 -- Writing into XML File
2897 -----------------------------------------------------------------------------
2898 -- Assigning the File name.
2899 l_file_name := to_char(p_request_id) || '.xml';
2900 -- Getting the Util file directory name.mostly it'll be /sqlcom/outbound )
2901 BEGIN
2902 SELECT value
2903 INTO l_audit_log_dir
2904 FROM v$parameter
2905 WHERE LOWER(name) = 'utl_file_dir';
2906 -- Check whether more than one util file directory is found
2907 IF INSTR(l_audit_log_dir,',') > 0 THEN
2908 l_audit_log_dir := substr(l_audit_log_dir,1,instr(l_audit_log_dir,',')-1);
2909 END IF;
2910 EXCEPTION
2911 when no_data_found then
2912 null;
2913 END;
2914 -- Find out whether the OS is MS or Unix based
2915 -- If it's greater than 0, it's unix based environment
2916 IF INSTR(l_audit_log_dir,'/') > 0 THEN
2917 p_output_fname := l_audit_log_dir || '/' || l_file_name;
2918 ELSE
2919 p_output_fname := l_audit_log_dir || '\' || l_file_name;
2920 END IF;
2921 -- getting Agency name
2922 p_l_fp := utl_file.fopen(l_audit_log_dir,l_file_name,'A');
2923 utl_file.put_line(p_l_fp,'<?xml version="1.0" encoding="UTF-8"?>');
2924 utl_file.put_line(p_l_fp,'<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">');
2925 -- Writing from and to dates
2926 utl_file.put_line(p_l_fp,'<fields>');
2927 -- Write the header fields to XML File.
2928 --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') );
2929 --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') );
2930 -- Loop through PL/SQL Table and write the values into the XML File.
2931 -- Need to try FORALL instead of FOR
2932 IF vXMLTable.count >0 then
2933
2934 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
2935 WriteXMLvalues(p_l_fp,vXMLTable(ctr_table).TagName ,vXMLTable(ctr_table).TagValue);
2936 END LOOP;
2937 END IF;
2938 -- Write the end tag and close the XML File.
2939 utl_file.put_line(p_l_fp,'</fields>');
2940 utl_file.put_line(p_l_fp,'</xfdf>');
2941 utl_file.fclose(p_l_fp);
2942 /*Msg in the temorary table*/
2943 --insert into tstmsg values('Leaving the procedure WritetoXML.');
2944 END WritetoXML;
2945 PROCEDURE WriteXMLvalues( p_l_fp utl_file.file_type,p_tagname IN VARCHAR2, p_value IN VARCHAR2) IS
2946 BEGIN
2947 -- Writing XML Tag and values to XML File
2948 -- utl_file.put_line(p_l_fp,'<' || p_tagname || '>' || p_value || '</' || p_tagname || '>' );
2949 -- New Format XFDF
2950 utl_file.put_line(p_l_fp,'<field name="' || p_tagname || '">');
2951 utl_file.put_line(p_l_fp,'<value>' || p_value || '</value>' );
2952 utl_file.put_line(p_l_fp,'</field>');
2953 END WriteXMLvalues;
2954
2955
2956
2957 END pay_kw_monthly_reports;