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