1 package body PQP_EXCP_RPT AS
2 /* $Header: pqpexrcp.pkb 120.0 2005/05/29 01:56:54 appldev noship $ */
3
4 procedure PQP_EXR_CSV_FORMAT (errbuf OUT NOCOPY VARCHAR2
5 ,retcode OUT NOCOPY NUMBER
6 ,p_ppa_finder IN VARCHAR2
7 ,p_report_date IN VARCHAR2
8 ,p_business_group_id IN NUMBER
9 ,p_report_name IN VARCHAR2
10 ,p_group_name IN VARCHAR2
11 ,p_override_variance_type IN VARCHAR2
12 ,p_override_variance_value IN NUMBER
13 )
14 IS
15
16 --
17 -- Cursor to get the row from the pay_us_rpt_totals table
18 --
19 CURSOR c_exception_report_data IS
20 SELECT
21 fnd_number.canonical_to_number(purt.business_group_id) business_group_id,
22 fnd_number.canonical_to_number(purt.tax_unit_id) payroll_action_id,
23 fnd_number.canonical_to_number(purt.value1) current_balance,
24 fnd_number.canonical_to_number(purt.value2) previous_balance,
25 fnd_number.canonical_to_number(purt.attribute1) balance_type_id,
26 fnd_number.canonical_to_number(purt.attribute2) report_id,
27 fnd_number.canonical_to_number(purt.attribute3) group_id,
28 fnd_number.canonical_to_number(purt.attribute4) consolidation_set_id,
29 fnd_number.canonical_to_number(purt.attribute5) payroll_id,
30 fnd_number.canonical_to_number(purt.attribute6) assignment_id,
31 purt.attribute14 full_name,
32 purt.attribute7 last_name,
33 purt.attribute8 first_name,
34 purt.attribute10 middle_name,
35 purt.attribute9 national_id,
36 purt.attribute11 effective_date,
37 purt.attribute13 assignment_number
38 FROM pay_us_rpt_totals purt
39 WHERE organization_id=p_ppa_finder
40 AND purt.attribute12 = p_ppa_finder
41 ORDER BY purt.attribute7;
42 -- End of Cursor
43
44 --
45 -- Cursor to get the payroll name
46 --
47 CURSOR c_get_payroll_name(
48 p_payroll_id pay_payrolls_f.payroll_id%TYPE,
49 p_consolidation_set_id pay_payrolls_f.consolidation_set_id%TYPE,
50 p_business_group_id pay_payrolls_f.business_group_id%TYPE,
51 p_date DATE
52 )
53 IS
54 SELECT payroll_name
55 FROM pay_payrolls_f
56 WHERE payroll_id=p_payroll_id
57 AND consolidation_set_id=p_consolidation_set_id
58 AND (business_group_id = p_business_group_id
59 OR business_group_id IS NULL)
60 AND trunc(p_date) between effective_start_date and effective_end_date;
61 -- End of cursor
62
63 --
64 -- Cursor to get the consolidation set name
65 --
66 CURSOR c_get_consolidation_set_name(
67 p_consolidation_set_id pay_consolidation_sets.consolidation_set_id%TYPE,
68 p_business_group_id pay_consolidation_sets.business_group_id%TYPE
69 )
70 IS
71 SELECT consolidation_set_name
72 from pay_consolidation_sets
73 WHERE consolidation_set_id=p_consolidation_set_id
74 AND (business_group_id =p_business_group_id
75 OR business_group_id IS NULL);
76 -- End of cursor
77
78 --
79 -- Cursor to get the balance name
80 --
81 CURSOR c_get_balance_name(
82 p_balance_type_id pay_balance_types.balance_type_id%TYPE
83 )
84 IS
85 SELECT DISTINCT balance_name
86 FROM pay_balance_types
87 WHERE balance_type_id=p_balance_type_id;
88
89 --
90 -- Cursor to get the leg_code and the currency code
91 --
92 CURSOR c_get_codes(
93 p_business_group_id per_business_groups.business_group_id%TYPE
94 )
95 IS
96 SELECT Distinct legislation_code,currency_code
97 FROM per_business_groups
98 WHERE business_group_id =p_business_group_id;
99 -- End of cursor
100
101
102 --
103 -- Cursor to get the values for variance value, comparison value,
104 -- report name and output format
105 --
106 CURSOR c_get_values(
107 p_report_id pqp_exception_reports.exception_report_id%TYPE,
108 p_business_group_id pqp_exception_reports.business_group_id%TYPE,
109 p_legislation_code pqp_exception_reports.legislation_code%TYPE
110 )
111 IS
112 SELECT variance_value
113 ,comparison_value
114 ,exception_report_name
115 ,output_format
116 ,balance_dimension_id
117 FROM pqp_exception_reports
118 WHERE exception_report_id=p_report_id
119 AND (business_group_id IS NULL
120 OR business_group_id=p_business_group_id)
121 AND (legislation_code IS NULL
122 OR legislation_code=p_legislation_code) ;
123 -- End of cursor
124
125
126 --
127 -- Cursor to get the comparison type from look up
128 --
129 CURSOR c_get_comparison_type(
130 p_report_id pqp_exception_reports.exception_report_id%TYPE,
131 p_business_group_id pqp_exception_reports.business_group_id%TYPE,
132 p_legislation_code pqp_exception_reports.legislation_code%TYPE
133 )
134 IS
135 SELECT meaning
136 FROM hr_lookups
137 WHERE lookup_type='PQP_COMPARISON_TYPE'
138 AND lookup_code=(
139 SELECT comparison_type
140 FROM pqp_exception_reports
141 WHERE exception_report_id=p_report_id
142 AND (business_group_id IS NULL
143 OR business_group_id=p_business_group_id)
144 AND (legislation_code IS NULL
145 OR legislation_code=p_legislation_code)) ;
146 -- End of cursor
147
148 --
149 -- Cursor to get the variance type from the lookup
150 --
151 CURSOR c_get_variance_type(
152 p_report_id pqp_exception_reports.exception_report_id%TYPE,
153 p_business_group_id pqp_exception_reports.business_group_id%TYPE,
154 p_legislation_code pqp_exception_reports.legislation_code%TYPE
155 )
156 IS
157 SELECT distinct hrl.meaning
158 FROM pqp_exception_reports per,
159 hr_lookups hrl
160 WHERE per.exception_report_id=p_report_id
161 AND (per.business_group_id IS NULL
162 OR per.business_group_id=p_business_group_id)
163 AND (per.legislation_code IS NULL
164 OR per.legislation_code=p_legislation_code)
165 AND hrl.lookup_type = 'PQP_VARIANCE_TYPES'
166 AND hrl.lookup_code = per.variance_type;
167 -- End of cursor
168
169 --
170 -- Cursor to get the variance operator from the look up
171 --
172 CURSOR c_get_variance_operator(
173 p_report_id pqp_exception_reports.exception_report_id%TYPE,
174 p_business_group_id pqp_exception_reports.business_group_id%TYPE,
175 p_legislation_code pqp_exception_reports.legislation_code%TYPE
176 )
177 IS
178 SELECT meaning
179 FROM HR_LOOKUPS HRL
180 WHERE HRL.LOOKUP_TYPE = 'PQP_OPERATOR_TYPES'
181 AND HRL.ENABLED_FLAG = 'Y'
182 AND LOOKUP_CODE =(
183 SELECT distinct variance_operator
184 FROM pqp_exception_reports
185 WHERE exception_report_id=p_report_id
186 AND (business_group_id IS NULL
187 OR business_group_id=p_business_group_id)
188 AND (legislation_code IS NULL
189 OR legislation_code=p_legislation_code));
190 -- End of cursor
191
192 --
193 -- Cursor to get the exception report group name
194 --
195 CURSOR c_get_exception_group_name(
196 p_group_id pqp_exception_report_groups.exception_group_id%TYPE
197 )
198 IS
199 SELECT EXCEPTION_GROUP_NAME
200 FROM pqp_exception_report_groups
201 WHERE EXCEPTION_GROUP_ID = p_group_id;
202 -- End of cursor
203
204 --
205 -- Cursor to get the dimension name
206 --
207 CURSOR c_get_dimension_name(
208 p_dimension_id pay_balance_dimensions.balance_dimension_id%TYPE
209 )
210 IS
211 select pbd.dimension_name dimension_name
212 from pay_balance_dimensions pbd,
213 pay_defined_balances pdb,
214 pqp_exception_report_suffix ers
215 where pbd.database_item_suffix = ers.database_item_suffix
216 and ers.legislation_code = userenv('LANG')
217 and pbd.balance_dimension_id = pdb.balance_dimension_id
218 and pdb.balance_dimension_id = p_dimension_id;
219 -- End of cursor
220
221 --
222 -- Cursor to get the output format
223 --
224 CURSOR c_get_output_format
225 IS
226 SELECT output_format
227 FROM pqp_exception_reports
228 WHERE exception_report_name = p_report_name
229 AND (business_group_id= p_business_group_id
230 OR business_group_id IS NULL)
231 UNION
232 SELECT DISTINCT output_format
233 FROM pqp_exception_report_groups
234 WHERE exception_group_name = p_group_name
235 AND (business_group_id= p_business_group_id
236 OR business_group_id IS NULL);
237 -- End of cursor
238
239
240 --
241 -- Variables used to keep backup (old/Previous) values
242 --
243 l_payroll_id_old pay_payrolls_f.payroll_id%TYPE;
244 l_consolidation_set_id_old pay_consolidation_sets.consolidation_set_id%TYPE;
245 l_balance_type_id_old pay_balance_types.balance_type_id%TYPE;
246 l_business_group_id_old pqp_exception_reports.business_group_id%TYPE;
247 l_report_id_old pqp_exception_reports.exception_report_id%TYPE;
248
249 --
250 -- Local Variables
251 --
252 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
253 l_consolidation_set_name pay_consolidation_sets.consolidation_set_name%TYPE;
254 l_balance_name pay_balance_types.balance_name%TYPE;
255 l_dimension_id pqp_exception_reports.balance_dimension_id%TYPE;
256 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
257 l_legislation_code per_business_groups.legislation_code%TYPE;
258 l_currency_code per_business_groups.currency_code%TYPE;
259 l_currency_format varchar2(40);
260 l_variance_value pqp_exception_reports.variance_value%TYPE;
261 l_comparison_value pqp_exception_reports.comparison_value%TYPE;
262 l_exception_report_name pqp_exception_reports.exception_report_name%TYPE;
263 l_exception_group_name pqp_exception_report_groups.exception_group_name%TYPE;
264 l_output_format pqp_exception_reports.output_format%TYPE;
265 l_comparison_type varchar2(80);
266 l_variance_type pqp_exception_reports.variance_type%TYPE;
267 l_variance_operator varchar2(80);
268 l_business_group_name varchar2(80);
269 l_variance varchar2(100);
270 l_full_name per_all_people_f.full_name%TYPE;
271 l_difference number(10,2);
272 l_header_printed boolean;
273 i number;
274
275 begin
276 -- hr_utility.trace_on(NULL,'YYY');
277 hr_utility.trace('Entering Procedure PQP_EXR_CSV_FORMAT....');
278 hr_utility.trace('Fnd_log.output :'||fnd_file.output);
279 hr_utility.trace('Fnd_log.Log :'||fnd_file.log);
280 hr_utility.trace('ppa_finder :'||p_ppa_finder);
281 hr_utility.trace('report_date :'||p_report_date);
282
283 --
284 -- Get the output format
285 --
286 OPEN c_get_output_format;
287 FETCH c_get_output_format INTO l_output_format;
288 CLOSE c_get_output_format;
289 -- Close the cursor
290 --
291 -- If the output Format type is TXT then exit
292 --
293 IF l_output_format = 'TXT' THEN
294 hr_utility.trace('Exited the Conc. Proc. when OutputFormat = TXT');
295 RAISE NO_DATA_FOUND;
296 END IF;
297
298
299
300 --
301 -- Initialize all the Backup Variables to -1 before Looping
302 --
303 l_payroll_id_old := -1;
304 l_consolidation_set_id_old := -1;
305 l_balance_type_id_old := -1;
306 l_business_group_id_old := -1;
307 l_report_id_old := -1;
308 l_header_printed := FALSE;
309
310 --
311 -- Loop for all the rows found in the pay_us_rpt_totals table
312 --
313 FOR r_excp_rpt_data IN c_exception_report_data
314 LOOP
315
316
317 hr_utility.trace('report_id :'|| r_excp_rpt_data.report_id);
318 hr_utility.trace('group_id :'|| r_excp_rpt_data.group_id);
319 hr_utility.trace('Payroll id : ' || r_excp_rpt_data.payroll_id);
320 hr_utility.trace('Con Set id : ' || r_excp_rpt_data.consolidation_set_id);
321 hr_utility.trace('Bg ID : ' || r_excp_rpt_data.business_group_id);
322
323
324 IF r_excp_rpt_data.payroll_id <> l_payroll_id_old THEN
325 --
326 -- Get the Payroll Name
327 --
328 OPEN c_get_payroll_name(r_excp_rpt_data.payroll_id
329 ,r_excp_rpt_data.consolidation_set_id
330 ,r_excp_rpt_data.business_group_id
331 ,fnd_date.canonical_to_date(p_report_date)
332 );
333 FETCH c_get_payroll_name INTO l_payroll_name;
334 CLOSE c_get_payroll_name;
335 -- Close cursor
336
337 hr_utility.trace('Payroll Name : ' || l_payroll_name);
338 l_payroll_id_old := r_excp_rpt_data.payroll_id;
339 END IF;
340
341
342 IF r_excp_rpt_data.consolidation_set_id <> l_consolidation_set_id_old THEN
343 --
344 -- Get the consolidation set name
345 --
346 OPEN c_get_consolidation_set_name(r_excp_rpt_data.consolidation_set_id
347 ,r_excp_rpt_data.business_group_id);
348 FETCH c_get_consolidation_set_name INTO l_consolidation_set_name;
349 CLOSE c_get_consolidation_set_name;
350 -- Close the cursor
351 l_consolidation_set_id_old := r_excp_rpt_data.consolidation_set_id;
352 END IF;
353
354
355 IF r_excp_rpt_data.balance_type_id <> l_balance_type_id_old THEN
356 --
357 -- Get the balance name
358 --
359 OPEN c_get_balance_name(r_excp_rpt_data.balance_type_id);
360 FETCH c_get_balance_name INTO l_balance_name;
361 CLOSE c_get_balance_name;
362 l_balance_type_id_old := r_excp_rpt_data.balance_type_id;
363
364 END IF;
365
366 IF r_excp_rpt_data.business_group_id <> l_business_group_id_old THEN
367 --
368 -- Get the leg_code and the currency code
369 --
370 OPEN c_get_codes(r_excp_rpt_data.business_group_id);
371 FETCH c_get_codes INTO l_legislation_code,l_currency_code;
372 CLOSE c_get_codes;
373 -- Close the cursor
374 --
375 -- Get the business group name
376 --
377 l_business_group_name := hr_reports.get_business_group(
378 r_excp_rpt_data.business_group_id);
379
380 l_business_group_id_old := r_excp_rpt_data.business_group_id;
381 END IF;
382
383
384 IF r_excp_rpt_data.report_id <> l_report_id_old THEN
385 --
386 -- Get the variance value, comparison value, report name, output format
387 --
388 OPEN c_get_values(r_excp_rpt_data.report_id
389 ,r_excp_rpt_data.business_group_id
390 ,l_legislation_code);
391 FETCH c_get_values INTO l_variance_value,
392 l_comparison_value,
393 l_exception_report_name,
394 l_output_format,
395 l_dimension_id;
396 CLOSE c_get_values;
397 -- Close the cursor
398
399 --
400 -- Get the dimension name
401 --
402 OPEN c_get_dimension_name(l_dimension_id);
403 FETCH c_get_dimension_name INTO l_dimension_name;
404 CLOSE c_get_dimension_name;
405
406 --
407 -- Get the comparison type
408 --
409 OPEN c_get_comparison_type(r_excp_rpt_data.report_id
410 ,r_excp_rpt_data.business_group_id
411 ,l_legislation_code);
412 FETCH c_get_comparison_type INTO l_comparison_type;
413 CLOSE c_get_comparison_type;
414
415 --
416 -- get the variance type
417 --
418 OPEN c_get_variance_type(r_excp_rpt_data.report_id
419 ,r_excp_rpt_data.business_group_id
420 ,l_legislation_code);
421 FETCH c_get_variance_type INTO l_variance_type;
422 CLOSE c_get_variance_type;
423
424 --
425 -- Check if there is override Variance Type/Value
426 --
427 IF p_override_variance_type IS NOT NULL THEN
428 l_variance_type := p_override_variance_type;
429 END IF;
430
431 IF p_override_variance_value IS NOT NULL THEN
432 l_variance_value := p_override_variance_value;
433 END IF;
434
435 --
436 -- Get the variance operator
437 --
438 OPEN c_get_variance_operator(r_excp_rpt_data.report_id
439 ,r_excp_rpt_data.business_group_id
440 ,l_legislation_code);
441 FETCH c_get_variance_operator INTO l_variance_operator;
442 CLOSE c_get_variance_operator;
443
444 l_report_id_old := r_excp_rpt_data.report_id;
445
446 END IF;
447
448 -- Code below is not being used and hence commented
449 --
450 -- Setup the Variance string
451 --
452 --IF l_variance_type = 'A' or l_variance_type = 'Amount' THEN
453 -- l_variance := l_currency_code || ' ';
454 --END IF;
455 --l_variance := l_variance || l_variance_value;
456
457 --IF l_variance_type = 'P' or l_variance_type = 'Period' THEN
458 -- l_variance := l_variance || ' %';
459 --END IF;
460 --l_variance := l_variance || ' (' || l_variance_operator || ')';
461 -- Code comment ends
462
463 --
464 -- Calculate the difference in the previoue balance and
465 -- the current balance.
466 -- skutteti - changed previous minus current to current minus previous
467 -- to be consistent with the text format report
468
469 l_difference := r_excp_rpt_data.current_balance -
470 r_excp_rpt_data.previous_balance;
471
472 --
473 -- Print the header if not printed already
474 --
475 IF NOT l_header_printed THEN
476 fnd_file.put(fnd_file.output,'"Effective Date","');
477 -- BUG #3008584, changed from Payroll date to report parameter date.
478 fnd_file.put_line(fnd_file.output,fnd_date.canonical_to_date(p_report_date) || '"');
479
480 fnd_file.put(fnd_file.output,'"Business Group Name","');
481 fnd_file.put_line(fnd_file.output,l_business_group_name||'"');
482
483 fnd_file.put(fnd_file.output,'"Consolidation Set Name","');
484 fnd_file.put_line(fnd_file.output,l_consolidation_set_name||'"');
485
486 --
487 -- If its a exception report group,
488 -- then print the group name in the header
489 --
490 IF r_excp_rpt_data.group_id IS NOT NULL THEN
491 --
492 -- Get the group name from group id
493 --
494 OPEN c_get_exception_group_name(r_excp_rpt_data.group_id);
495 FETCH c_get_exception_group_name INTO l_exception_group_name;
496 CLOSE c_get_exception_group_name;
497 -- Close the cursor
498
499 -- Print the group name
500 fnd_file.put(fnd_file.output,'"Exception Group Name","');
501 fnd_file.put_line(fnd_file.output, l_exception_group_name ||'"');
502 END IF;
503
504 --
505 -- Print a Blank row between the heading and the main report body.
506 --
507 fnd_file.put_line(fnd_file.output,' ');
508
509 --
510 -- Printing the SpreadSheet Table Labels
511 --
512 fnd_file.put(fnd_file.output,'"Exception Report Name",');
513 fnd_file.put(fnd_file.output,'"Payroll Name",');
514 fnd_file.put(fnd_file.output,'"Employee Full Name",');
515 fnd_file.put(fnd_file.output,'"Assignment Number","Balance Name",');
516 fnd_file.put(fnd_file.output,'"Balance Dimension Name",');
517 fnd_file.put(fnd_file.output,'"Comparison Type",');
518 fnd_file.put(fnd_file.output,'"Comparison Value",');
519 fnd_file.put(fnd_file.output,'"Variance Type",');
520 fnd_file.put(fnd_file.output,'"Variance Operator",');
521 fnd_file.put(fnd_file.output,'"Variance Value",');
522 fnd_file.put(fnd_file.output,'"Previous Balance","Current Balance",');
523 fnd_file.put(fnd_file.output,'"Difference",');
524 fnd_file.put(fnd_file.output,'"Employee Last Name",');
525 fnd_file.put(fnd_file.output,'"Employee First Name",');
526 fnd_file.put_line(fnd_file.output,'"Employee Middle Name"');
527 l_header_printed := TRUE;
528 END IF;
529
530 --
531 -- Check if the Legislation Code is US/GB,
532 -- Change the fullname format to 'Last_name,First_Name'
533 --
534 IF l_legislation_code = 'US' OR l_legislation_code = 'GB' THEN
535 hr_utility.trace('Changing the Name Format.....');
536 l_full_name := r_excp_rpt_data.last_name || ',' ||
537 r_excp_rpt_data.first_name || ' ' || r_excp_rpt_data.middle_name;
538 ELSE
539 l_full_name := r_excp_rpt_data.full_name;
540 END IF;
541
542 -- Get the Currency format code
543 l_currency_format := fnd_currency.get_format_mask(l_currency_code, 30);
544
545 fnd_file.put(fnd_file.output,'"' || l_exception_report_name|| '","');
546 fnd_file.put(fnd_file.output,l_payroll_name || '","');
547 fnd_file.put(fnd_file.output,l_full_name || '","');
548 fnd_file.put(fnd_file.output,r_excp_rpt_data.assignment_number || '","');
549 fnd_file.put(fnd_file.output,l_balance_name || '","');
550 fnd_file.put(fnd_file.output,l_dimension_name ||'","');
551 fnd_file.put(fnd_file.output,l_comparison_type || '","');
552 fnd_file.put(fnd_file.output,l_comparison_value || '","');
553 fnd_file.put(fnd_file.output,l_variance_type || '","');
554 fnd_file.put(fnd_file.output,l_variance_operator || '","');
555 fnd_file.put(fnd_file.output,l_variance_value || '","');
556 fnd_file.put(fnd_file.output,TO_CHAR(r_excp_rpt_data.previous_balance, l_currency_format) || '","' );
557 fnd_file.put(fnd_file.output,TO_CHAR(r_excp_rpt_data.current_balance, l_currency_format) || '","' );
558 fnd_file.put(fnd_file.output,TO_CHAR(l_difference, l_currency_format)||'","');
559 fnd_file.put(fnd_file.output,r_excp_rpt_data.last_name||'","');
560 fnd_file.put(fnd_file.output,r_excp_rpt_data.first_name||'","');
561 fnd_file.put_line(fnd_file.output,r_excp_rpt_data.middle_name||'"');
562
563 END LOOP;
564 -- Loop Ends here
565 IF l_output_format = 'CSV' THEN
566 DELETE FROM pay_us_rpt_totals
567 WHERE organization_id =p_ppa_finder
568 AND attribute12 = p_ppa_finder;
569 commit;
570 hr_utility.trace('Deleted the rows from the pay_us_rpt_totals');
571 END IF;
572
573 EXCEPTION
574 WHEN NO_DATA_FOUND THEN
575 hr_utility.trace('Final Exit!');
576 null;
577 hr_utility.trace('Leaving Procedure PQP_EXR_CSV_FORMAT....');
578 -- hr_utility.trace_off;
579
580 -- Added by tmehra for nocopy changes Feb'03
581
582 WHEN OTHERS THEN
583 hr_utility.trace('Entering exception when others:');
584 retcode := null;
585 raise;
586
587
588 End PQP_EXR_CSV_FORMAT;
589
590 End PQP_EXCP_RPT;
[Home] [Help]