[Home] [Help]
PACKAGE BODY: APPS.PAY_EBRA_DIAGNOSTICS
Source
1 PACKAGE BODY pay_ebra_diagnostics AS
2 /* $Header: payrundiag.pkb 120.0 2005/05/29 10:49 appldev noship $ */
3 --
4 /*****************************************************************************
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_ebra_diagnostic
22
23 File :
24
25 Description : This package is used to create report many
26 error condition in Ebra architecture.
27 Output from the report can be in
28
29 - HTML
30 - CSV
31 - TAB
32
33 Change List
34 -----------
35 Date Name Vers Bug No Description
36 ---- ---- ------ ------- -----------
37 17-aug-2004 djoshi 115.0 Created.
38 in pay_payroll_actions to improve
39 performance.
40 03-sep-2004 djoshi 115.2 changed the format for date to
41 show four year
42 09-Sep-2004 kvsankar 115.3 3651755 Modified the cursors
43 c_get_valid_count
44 c_get_attribute_count
45 c_run_balance_status
46 c_attrib_bal
47 c_attribute_validation
48 to retriece data correctly
49 09-Sep-2004 kvsankar 115.4 3651755 Modified check_balance_status
50 function to use l_trunc_date
51 16-sep-2004 djoshi 115.5 Reverted the changes that were
52 made with legislation sepcific
53 code. Also added the code
54 to have valid from date to null
55 in case of invalid Status.
56 Code was modified to support
57 additoinal parameter of SRS.
58 11-nov-2004 djoshi 115.6 4004320 Corrected the balance
59
60 *****************************************************************************/
61
62 /************************************************************
63 ** Local Package Variables
64 ************************************************************/
65 gv_title VARCHAR2(100) := ' Run Balance Diagnostic Report ';
66
67 gv_title_sec1 VARCHAR2(100) := ' Run Balance Status ';
68 gv_title_sec2 VARCHAR2(100) := ' Balance Attribute Status';
69 gv_title_sec3 VARCHAR2(100) := ' Balances By Attribute ';
70 gv_title_sec4 VARCHAR2(100) := ' Incorrect Run Balance and Attribute Setup';
71 gv_Invalid VARCHAR2(100);
72 gv_valid VARCHAR2(100);
73 gc_csv_delimiter VARCHAR2(1) := ',';
74 gc_csv_data_delimiter VARCHAR2(1) := '"';
75
76 gv_html_start_data VARCHAR2(5) := '<td>' ;
77 gv_html_end_data VARCHAR2(5) := '</td>' ;
78
79 gv_package_name VARCHAR2(50) := 'pay_us_ebra_diagnostic';
80
81
82 /************************************************************
83 ** Function returns the string with the HTML Header tags
84 ************************************************************/
85
86 FUNCTION formated_header_string
87 (p_input_string in varchar2
88 ,p_output_file_type in varchar2
89 )
90 RETURN VARCHAR2
91 IS
92
93 lv_format varchar2(1000);
94
95 BEGIN
96 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
97 if p_output_file_type = 'CSV' then
98 hr_utility.set_location(gv_package_name || '.formated_header_string', 20);
99 lv_format := p_input_string;
100 elsif p_output_file_type = 'HTML' then
101 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
102 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
103 '</B></H1></CENTER></HEAD>';
104 end if;
105
106 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
107 return lv_format;
108
109 END formated_header_string;
110
111
112 /******************************************************************
113 ** Function Returns the formated input string based on the
114 ** Output format. If the format is CSV then the values are returned
115 ** seperated by comma (,). If the format is HTML then the returned
116 ** string as the HTML tags. The parameter p_bold only works for
117 ** the HTML format.
118 ******************************************************************/
119 FUNCTION formated_data_string
120 (p_input_string in varchar2
121 ,p_output_file_type in varchar2
122 ,p_bold in varchar2
123 )
124 RETURN VARCHAR2
125 IS
126
127 lv_format varchar2(1000);
128 lv_bold varchar2(10);
129 BEGIN
130 lv_bold := nvl(p_bold,'N');
131 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
132 if p_output_file_type = 'CSV' then
133 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
134 lv_format := gc_csv_data_delimiter || p_input_string ||
135 gc_csv_data_delimiter || gc_csv_delimiter;
136 elsif p_output_file_type = 'HTML' then
137 if p_input_string is null then
138 hr_utility.set_location(gv_package_name || '.formated_data_string', 30);
139 lv_format := gv_html_start_data || ' ' || gv_html_end_data;
140 else
141 if lv_bold = 'Y' then
142 hr_utility.set_location(gv_package_name || '.formated_data_string', 40);
143 lv_format := gv_html_start_data || '<b> ' || p_input_string
144 || '</b>' || gv_html_end_data;
145 else
146 hr_utility.set_location(gv_package_name || '.formated_data_string', 50);
147 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
148 end if;
149 end if;
150 end if;
151
152 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
153 return lv_format;
154
155 END formated_data_string;
156
157
158 FUNCTION formated_header_sec1(
159 p_output_file_type in varchar2
160 )RETURN VARCHAR2
161 IS
162
163 lv_format1 varchar2(32000);
164
165 BEGIN
166
167 hr_utility.set_location(gv_package_name || '.formated_header_state', 10);
168 lv_format1 :=
169 formated_data_string (p_input_string => 'Balance Name '
170 ,p_bold => 'Y'
171 ,p_output_file_type => p_output_file_type) ||
172 formated_data_string (p_input_string => 'Dimension Name '
173 ,p_bold => 'Y'
177 ,p_output_file_type => p_output_file_type) ||
174 ,p_output_file_type => p_output_file_type) ||
175 formated_data_string (p_input_string => 'Valid From Date'
176 ,p_bold => 'Y'
178 formated_data_string (p_input_string => 'Balance Status'
179 ,p_bold => 'Y'
180 ,p_output_file_type => p_output_file_type) ;
181
182
183 hr_utility.trace('Static Label1 = ' || lv_format1);
184
185 return lv_format1 ;
186
187 hr_utility.set_location(gv_package_name || '.formated_header_sec1', 40);
188
189 END formated_header_sec1;
190
191
192 FUNCTION formated_header_sec2(
193 p_output_file_type in varchar2
194 )RETURN VARCHAR2
195 IS
196
197 lv_format1 varchar2(32000);
198
199 BEGIN
200
201 hr_utility.set_location(gv_package_name || '.formated_header_state', 10);
202 lv_format1 :=
203 formated_data_string (p_input_string => 'Attribute Name '
204 ,p_bold => 'Y'
205 ,p_output_file_type => p_output_file_type) ||
206 formated_data_string (p_input_string => 'Valid From Date'
207 ,p_bold => 'Y'
208 ,p_output_file_type => p_output_file_type) ||
209 formated_data_string (p_input_string => 'Attribute Status'
210 ,p_bold => 'Y'
211 ,p_output_file_type => p_output_file_type) ;
212
213
214 hr_utility.trace('Static Label1 = ' || lv_format1);
215
216 return lv_format1 ;
217
218 hr_utility.set_location(gv_package_name || '.formated_header_sec2', 40);
219
220 END formated_header_sec2;
221
222
223
224 FUNCTION formated_header_sec3(
225 p_output_file_type in varchar2
226 )RETURN VARCHAR2
227 IS
228
229 lv_format1 varchar2(32000);
230
231 BEGIN
232
233 hr_utility.set_location(gv_package_name || '.formated_header_state', 10);
234 lv_format1 :=
235 formated_data_string (p_input_string => 'Attribute Name '
236 ,p_bold => 'Y'
237 ,p_output_file_type => p_output_file_type) ||
238 formated_data_string (p_input_string => 'Balance Name '
239 ,p_bold => 'Y'
240 ,p_output_file_type => p_output_file_type) ||
241 formated_data_string (p_input_string => 'Dimension Name '
242 ,p_bold => 'Y'
243 ,p_output_file_type => p_output_file_type);
244
245
246 hr_utility.trace('Static Label1 = ' || lv_format1);
247
248 return lv_format1 ;
249
250 hr_utility.set_location(gv_package_name || '.formated_header_sec3', 40);
251
252 END formated_header_sec3;
253
254
255 FUNCTION formated_header_sec4(
256 p_output_file_type in varchar2
257 )RETURN VARCHAR2
258 IS
259
260 lv_format1 varchar2(32000);
261 lv_format2 varchar2(32000);
262
263 BEGIN
264
265 hr_utility.set_location(gv_package_name || '.formated_header_sec4', 10);
266 lv_format1 :=
267 formated_data_string (p_input_string => 'Business Group Name '
268 ,p_bold => 'Y'
269 ,p_output_file_type => p_output_file_type) ||
270
271 formated_data_string (p_input_string => 'Attribute Name '
272 ,p_bold => 'Y'
273 ,p_output_file_type => p_output_file_type) ||
274
275 formated_data_string (p_input_string => 'Balance Name '
276 ,p_bold => 'Y'
277 ,p_output_file_type => p_output_file_type) ||
278
279 formated_data_string (p_input_string => 'Dimension Name '
280 ,p_bold => 'Y'
281 ,p_output_file_type => p_output_file_type) ||
282
283 formated_data_string (p_input_string => 'Save Run Balance'
284 ,p_bold => 'Y'
285 ,p_output_file_type => p_output_file_type) ;
286
287
288
289 hr_utility.trace('Static Label1 = ' || lv_format1);
290
291 return lv_format1 ;
292
293 hr_utility.set_location(gv_package_name || '.formated_header_sec4', 40);
294
295 END formated_header_sec4;
296
297
298
299
300 FUNCTION formated_validation_detail(
301 p_output_file_type varchar2
302 ,p_input_1 varchar2
303 ,p_input_2 varchar2
304 ,p_input_3 varchar2
305 ,p_input_4 varchar2
306 ,p_input_5 varchar2
307 ) RETURN varchar2
308 IS
309
310 lv_format1 varchar2(22000);
311 lv_format2 varchar2(10000);
312
313 BEGIN
314
315 hr_utility.set_location(gv_package_name || '.formated_validation_detail', 10);
316 lv_format1 :=
317 formated_data_string (p_input_string => p_input_1
321 ,p_bold => 'N'
318 ,p_bold => 'N'
319 ,p_output_file_type => p_output_file_type) ||
320 formated_data_string (p_input_string => p_input_2
322 ,p_output_file_type => p_output_file_type) ||
323 formated_data_string (p_input_string => p_input_3
324 ,p_bold => 'N'
325 ,p_output_file_type => p_output_file_type) ||
326 formated_data_string (p_input_string => p_input_4
327 ,p_bold => 'N'
328 ,p_output_file_type => p_output_file_type) ||
329 formated_data_string (p_input_string => p_input_5
330 ,p_bold => 'N'
331 ,p_output_file_type => p_output_file_type)
332 ;
333
334
335 hr_utility.trace('Static Label1 = ' || lv_format1);
336
337 hr_utility.set_location(gv_package_name || '.formated_validation_details', 30);
338
339 return lv_format1 ;
340
341 END formated_validation_detail;
342
343
344 FUNCTION check_balance_status(
345 p_start_date in date,
346 p_business_group_id in number,
347 p_attribute_id in number,
348 p_legislation_code in varchar2
349 )
350
351 RETURN VARCHAR2
352 IS
353
354 /*************************************************************
355 ** Cursor to check if the attribute_name passed as parameter
356 ** exists or not.
357 **************************************************************/
358
359 CURSOR c_get_valid_count(cp_start_date in date,
360 cp_business_group_id in per_business_groups.business_group_id%type,
361 cp_attribute_id in number) IS
362 select /*+ ORDERED */ count(*)
363 from
364 pay_balance_attributes pba,
365 pay_balance_validation pbv
366 where pba.attribute_id = cp_attribute_id
367 and (pba.business_group_id = cp_business_group_id
368 or pba.legislation_code = p_legislation_code)
369 and pba.defined_balance_id = pbv.defined_balance_id
370 and pbv.business_group_id = cp_business_group_id
371 and NVL(pbv.balance_load_date, cp_start_date) <= cp_start_date
372 and nvl(pbv.run_balance_status, 'I') = 'V';
373
374 CURSOR c_get_attribute_count(cp_attribute_id in number,
375 cp_business_group_id in per_business_groups.business_group_id%type) IS
376
377 select count(*)
378 from
379 pay_balance_attributes pba
380 where pba.attribute_id = cp_attribute_id
381 and (pba.business_group_id = cp_business_group_id
382 or pba.legislation_code = p_legislation_code);
383
384 ln_attribute_exists NUMBER(1);
385 ln_valid_bal_exists NUMBER(1);
386 lv_return_status VARCHAR2(10);
387 lv_package_stage VARCHAR2(50) := 'check_balance_status';
388
389 l_attribute_count number;
390 l_valid_count number;
391 l_trunc_date date; /* Bug 3258868 */
392
393 BEGIN
394 hr_utility.trace('Start of Procedure '||lv_package_stage);
395 hr_utility.set_location(lv_package_stage,10);
396
397
398
399 -- Validate if the attribute passed as parameter exists
400
401 hr_utility.set_location(lv_package_stage,30);
402
403 l_trunc_date := NVL(p_start_date, fnd_date.canonical_to_date('0001/01/01 00:00:00') ) ;
404
405 open c_get_valid_count(l_trunc_date,
406 p_business_group_id,
407 p_attribute_id );
408 fetch c_get_valid_count into l_valid_count;
409 close c_get_valid_count;
410
411 hr_utility.trace('Valid Count for '
412 ||p_attribute_id||' is '||to_char(l_valid_count));
413
414 open c_get_attribute_count( p_attribute_id, p_business_group_id );
415 fetch c_get_attribute_count into l_attribute_count;
416 close c_get_attribute_count;
417
418 hr_utility.trace('Attribute Count for '||p_attribute_id||' is '||to_char(l_attribute_count));
419
420 if l_valid_count = l_attribute_count then
421
422 hr_utility.set_location(lv_package_stage,40);
423 lv_return_status := gv_valid;
424 else
425
426 hr_utility.set_location(lv_package_stage,50);
427 hr_utility.trace('Balance Status is Invalid for Attribute -> ' ||p_attribute_id);
428 lv_return_status := gv_invalid;
429 end if;
430
431 hr_utility.trace('End of Procedure ' || lv_package_stage);
432 return(lv_return_status);
433
434
435 EXCEPTION
436 WHEN others THEN
437 hr_utility.set_location(lv_package_stage,60);
438 hr_utility.trace('Invalid Attribute Name');
439 raise_application_error(-20101, 'Error in check_balance_status');
440 raise;
441 END check_balance_status;
442
443 FUNCTION formated_detail4(
444 p_output_file_type varchar2
445 ,p_input_1 varchar2
446 ,p_input_2 varchar2
447 ,p_input_3 varchar2
448 ,p_input_4 varchar2
449 ) RETURN varchar2
450 IS
451
452 lv_format1 varchar2(22000);
456
453 lv_format2 varchar2(10000);
454
455 BEGIN
457 hr_utility.set_location(gv_package_name || '.formated_detail4', 10);
458 lv_format1 :=
459 formated_data_string (p_input_string => p_input_1
460 ,p_bold => 'N'
461 ,p_output_file_type => p_output_file_type) ||
462 formated_data_string (p_input_string => p_input_2
463 ,p_bold => 'N'
464 ,p_output_file_type => p_output_file_type) ||
465 formated_data_string (p_input_string => p_input_3
466 ,p_bold => 'N'
467 ,p_output_file_type => p_output_file_type) ||
468 formated_data_string (p_input_string => p_input_4
469 ,p_bold => 'N'
470 ,p_output_file_type => p_output_file_type)
471 ;
472
473
474 hr_utility.trace('Static Label1 = ' || lv_format1);
475
479
476 hr_utility.set_location(gv_package_name || '.formated__detail4', 30);
477
478 return lv_format1 ;
480 END formated_detail4;
481
482
483 FUNCTION formated_detail3(
484 p_output_file_type varchar2
485 ,p_input_1 varchar2
486 ,p_input_2 varchar2
487 ,p_input_3 varchar2
488 ) RETURN varchar2
489 IS
490
491 lv_format1 varchar2(22000);
492 lv_format2 varchar2(10000);
493
494 BEGIN
495
496 hr_utility.set_location(gv_package_name || '.formated_detail3', 10);
497 lv_format1 :=
498 formated_data_string (p_input_string => p_input_1
499 ,p_bold => 'N'
500 ,p_output_file_type => p_output_file_type) ||
501 formated_data_string (p_input_string => p_input_2
502 ,p_bold => 'N'
503 ,p_output_file_type => p_output_file_type) ||
504 formated_data_string (p_input_string => p_input_3
505 ,p_bold => 'N'
506 ,p_output_file_type => p_output_file_type)
507 ;
508
509
510 hr_utility.trace('Static Label1 = ' || lv_format1);
511
512 hr_utility.set_location(gv_package_name || '.formated__detail3', 30);
513
514 return lv_format1 ;
515
516 END formated_detail3;
517
518 FUNCTION formated_detail(
519 p_output_file_type varchar2
520 ,p_input_1 varchar2
521 ,p_input_2 varchar2
522 ,p_input_3 varchar2
523 ,p_input_4 varchar2
524 ,p_input_5 varchar2
525 ,p_input_6 varchar2
526 ,p_input_7 varchar2
527 ) RETURN varchar2
528 IS
529
530 lv_format1 varchar2(22000);
531 lv_format2 varchar2(10000);
532
533 BEGIN
534
535 hr_utility.set_location(gv_package_name || '.formated_detail', 10);
536 lv_format1 :=
537 formated_data_string (p_input_string => p_input_1
538 ,p_bold => 'N'
539 ,p_output_file_type => p_output_file_type) ||
540 formated_data_string (p_input_string => p_input_2
541 ,p_bold => 'N'
542 ,p_output_file_type => p_output_file_type) ||
543 formated_data_string (p_input_string => p_input_3
544 ,p_bold => 'N'
545 ,p_output_file_type => p_output_file_type) ||
546 formated_data_string (p_input_string => p_input_4
547 ,p_bold => 'N'
548 ,p_output_file_type => p_output_file_type) ||
549 formated_data_string (p_input_string => p_input_5
550 ,p_bold => 'N'
551 ,p_output_file_type => p_output_file_type) ||
552 formated_data_string (p_input_string => P_input_6
553 ,p_bold => 'N'
554 ,p_output_file_type => p_output_file_type) ||
555 formated_data_string (p_input_string => p_input_7
556 ,p_bold => 'N'
557 ,p_output_file_type => p_output_file_type)
558 ;
559
560
561 hr_utility.trace('Static Label1 = ' || lv_format1);
562
563 hr_utility.set_location(gv_package_name || '.formated_detail', 30);
564
565 return lv_format1 ;
566
567 END formated_detail;
568
569
570
571
572
573 /*****************************************************************
574 ** This procedure is called from the Concurrent Request. Based on
575 ** paramaters selected in SRS the report will
576 **
577 **
578 **
579 *****************************************************************/
580
581 PROCEDURE ebra_diagnostics
582 (errbuf OUT nocopy varchar2,
583 retcode OUT nocopy number,
584 p_output_file_type IN VARCHAR2,
585 p_attribute_balance IN VARCHAR2
586 )
587 IS
588
589
590 /************************************************************
591 ** get_legi_Cd : Cursor to get legislation code and Business
592 group name
593 ** Parameter : Business Group Id from SRS
594 ** Return : 1.Legislation Code,
595 2.Name of the Business group
596
597 ************************************************************/
598
599 cursor c_get_leg_cd(cp_business_group_id number) is
600 select org_information9,hou.name
601 from hr_organization_information hoi,hr_all_organization_units hou
602 where hoi.organization_id = cp_business_group_id
603 and hoi.org_information_context = 'Business Group Information'
604 and hou.organization_id = hoi.organization_id;
605
606
607 /**************************************************************
608 Cursor for : Get the seeded Attributes definitions
609 : for the Legislation related to business
610 : group
611 Parameter for the Cursor :
612 cp_seeded_att_details : Get attribute
616 CURSOR c_seeded_att( cp_legislation_code varchar2)
613 Returns
614 Attribute_id, Attribute Name
615 **************************************************************/
617 IS
618 SELECT attribute_id, attribute_name
619 FROM
620 pay_bal_attribute_Definitions pbad
621 WHERE pbad.legislation_code = cp_legislation_code
622 ORDER BY attribute_name;
623
624 /**************************************************************
625 Cursor for : Get User defined Attributes definitions
626 : for a business group
627 Parameter for the Cursor :
628 cp_userdef_att_details : Business group
629 Returns
630 Attribute_id, Attribute Name
631 **************************************************************/
632 CURSOR c_userdef_att( cp_business_group_id number)
633 IS
634 SELECT attribute_id, attribute_name
635 FROM
636 pay_bal_attribute_Definitions pbad
637 WHERE pbad.business_group_id = cp_business_group_id
638 order by attribute_name;
639
640 /**************************************************************
641 Cursor for : Get meaning of Attribute Status
642 :
643 Parameter for the Cursor :
644
645 Returns
646 Attribute_status
647 **************************************************************/
648 CURSOR c_valid_invalid
649 IS
650 SELECT hl1.meaning,hl2.meaning
651 FROM hr_lookups hl1,hr_lookups hl2
652 WHERE hl2.lookup_type = 'RUN_BALANCE_STATUS'
653 AND hl2.lookup_code = 'V'
654 AND hl1.lookup_type = 'RUN_BALANCE_STATUS'
655 AND hl1.lookup_code = 'I';
656
657 /**************************************************************
658 Cursor for : To get info on status of defined_balances
659 Parameter for the Cursor :
660 cp_attribute_id : attribute_id
661 cp_business_group_id : business_group_id
662 Returns
663 1. Balance_Name
664 2. Dimension_name
665 3. Load_date
666 4. run_balance_status
667
668 **************************************************************/
669
670 CURSOR c_run_balance_status(
671 cp_business_group_id number)
672
673 IS
674 SELECT pbt.balance_name, pbd.dimension_name
675 , decode(pbv.run_balance_status, 'I', null
676 ,to_char(pbv.balance_load_date,'yyyy/mm/dd')) balance_load_date
677 ,nvl(hl.meaning, hl2.meaning) Status
678 FROM
679 pay_defined_balances pdb, pay_balance_types pbt
680 , pay_balance_dimensions pbd
681 , per_business_groups pbg, hr_lookups hl
682 , pay_balance_validation pbv
683 , hr_lookups hl2
684 WHERE pdb.balance_type_id = pbt.balance_type_id
685 AND pdb.balance_dimension_id = pbd.balance_dimension_id
686 AND pdb.save_run_balance = 'Y'
687 AND ((pdb.legislation_code = pbg.legislation_code)
688 or (pdb.business_group_id = pbg.business_group_id))
689 AND pbg.business_group_id = cp_business_group_id
690 AND hl.lookup_type(+) = 'RUN_BALANCE_STATUS'
691 AND hl.lookup_code(+) = pbv.run_balance_status
692 AND pbv.defined_balance_id (+) = pdb.defined_balance_id
693 AND pbv.business_group_id (+) = nvl(pdb.business_group_id, cp_business_group_id)
694 AND hl2.lookup_type = 'RUN_BALANCE_STATUS'
695 AND hl2.lookup_code = 'I'
696 ORDER BY STATUS,BALANCE_LOAD_DATE,BALANCE_NAME,DIMENSION_NAME ;
697
698
699
700
701 /**************************************************************
702 Cursor for : Find status of the attribute
703 Parameter for the Cursor :
704 cp_business_group_id : business_group_id
705 Returns
706 1. Attribute_name
707 4. Load Date
708 **************************************************************/
709
710 CURSOR c_attrib_status( cp_business_group_id number,
711 cp_attribute_id number)
712 IS
713 select distinct pba.attribute_id,
714 max(balance_load_date)
715 FROM
716 PAY_BALANCE_VALIDATION PBV
717 ,PAY_BALANCE_ATTRIBUTES PBA
718 WHERE
719 PBV.business_group_id = cp_business_group_id
720 AND PBA.attribute_id = cp_attribute_id
721 AND PBV.defined_balance_id = pba.defined_balance_id
722 group by attribute_id;
723
724
725 /**************************************************************
726 Cursor for :
727
728
729 Parameter for the Cursor :
730 cp_attribute_id : attribute_id
731
732 Returns
733 1. Attribute_name
734 2. Balance_name
735 3. Dimension_name
736 **************************************************************/
737 CURSOR c_attrib_bal(cp_business_group_id number,
738 cp_attribute_id number
739 ,cp_legislation_code varchar2 )
740 IS
741 SELECT
742 PBT.balance_name
743 ,DIM.DIMENSION_NAME
744 FROM
748 , PAY_BALANCE_ATTRIBUTES PBA
745 PAY_BALANCE_DIMENSIONS DIM
746 , PAY_BALANCE_TYPES PBT
747 , PAY_DEFINED_BALANCES PDB
749 WHERE
750 PBA.attribute_id = cp_attribute_id
751 and PBA.defined_balance_id = PDB.defined_balance_id
752 and PDB.balance_type_id = PBT.balance_type_id
753 and (PBT.business_group_id = cp_business_group_id or
754 PBT.legislation_code = cp_legislation_code)
755 and PDB.balance_dimension_id = DIM.balance_dimension_id;
756
757 /**************************************************************
758 Cursor for : Find if the Balance is in Attribute but
759 Does not have save run Balancei
760 or not found in PAY_BALANCE_VALIDATIONS
761 Parameter for the Cursor :
762 cp_attribute_id : attribute_id
763 cp_business_group_id : business_group_id
764 Returns
765 1. Attribute_name
766 2. Dimension_name
767 3. Balance_name
768 4. run_balance_status
769 **************************************************************/
770 CURSOR c_attribute_validation( cp_business_group_id number
771 ,cp_legislation_code varchar2)
772 IS
773 SELECT
774 BAD.ATTRIBUTE_NAME
775 ,PBT.balance_name
776 ,DIM.DIMENSION_NAME
777 ,PDB.save_run_balance
778 FROM
779 PAY_BALANCE_ATTRIBUTES PBA
780 , PAY_BAL_ATTRIBUTE_DEFINITIONS BAD
781 , PAY_DEFINED_BALANCES PDB
782 , PAY_BALANCE_TYPES PBT
783 , PAY_BALANCE_DIMENSIONS DIM
784 Where pba.attribute_id = bad.attribute_id
785 and pba.defined_balance_id = pdb.defined_balance_id
786 and pdb.balance_type_id = pbt.balance_type_id
787 and pdb.balance_dimension_id = dim.balance_dimension_id
788 and pdb.save_run_balance is NULL
789 and ((pba.business_group_id = cp_business_group_id and pba.legislation_code is NULL) OR
790 (pba.legislation_code = cp_legislation_code and pba.business_group_id is NULL))
791 and nvl(bad.legislation_code, cp_legislation_code) = cp_legislation_Code
792 /* and not Exists
793 (select 1 from pay_balance_validation PBV
794 where PBV.defined_balance_id = PBA.defined_balance_id
795 )*/
796 order by bad.attribute_name;
797
798
799 /*************************************************************
800 ** Local Variables
801 *************************************************************/
802 lvc_attribute_id VARCHAR2(150);
803 lvc_attribute_name VARCHAR2(150);
804 lvc_balance_name VARCHAR2(150);
805 lvc_dimension_name VARCHAR2(150);
806 lvc_save_run_balance_status VARCHAR2(150);
807 lvc_legislation_code VARCHAR2(150);
808 lvc_Business_group_name VARCHAR2(150);
809 lvc_date_time VARCHAR2(150);
810 lvc_load_date date;
811 lvd_start_date date;
812 lvc_start_date VARCHAR2(150);
813 lvc_balance_status VARCHAR2(100);
814 lvc_load_dates_status VARCHAR2(150);
815 lvn_business_group_id number;
816 lvn_attribute_id number;
817 lvn_start_date date;
818 lb_print_row BOOLEAN := FALSE;
819
820 lv_header_label VARCHAR2(32000);
821 /* Changed from 32000 to 22000 and 100000 */
822 lv_header_label1 VARCHAR2(22000);
823 lv_header_label2 VARCHAR2(10000);
824 lv_report_asgn VARCHAR2(1) := 'N';
825 lv_data_row VARCHAR2(32000);
826
827 lvn_count number := 0;
828 lvc_message varchar2(32000);
829 BEGIN
830 hr_utility.set_location(gv_package_name || 'Get Legislation code', 10);
831
832 lvc_date_time := to_char(sysdate,'mm/dd/yyyy HH:MI');
833
834 lvn_business_group_id := fnd_global.per_business_group_id;
835
836 hr_utility.trace(' lvn_business_group_id = ' || lvn_business_group_id);
837
838
839 /* Get the Meaning of Valid and Invalid Status */
840
841
842 open c_valid_invalid;
843 FETCH c_valid_invalid into gv_invalid,gv_valid;
844 CLOSE c_valid_invalid;
845
846 /* Initialize status to Invalid */
847
848 lvc_balance_status := gv_invalid;
849
850 /* Print Header for the FIle */
851 fnd_file.put_line(fnd_file.output, formated_header_string(
852 gv_title || ':- ( ' || lvc_date_time || ' )'
853 ,p_output_file_type
854 ));
855
856
857 /* Leave 4 blank line */
858 for i in 1..4 LOOP
859 fnd_file.put_line(fnd_file.output, formated_header_string(
860 ' '
861 ,p_output_file_type
862 ));
863 END LOOP;
864
865
866
867 /* STEP 1 : Get the Legislation Code */
868
869 hr_utility.trace('P_OUTPUT_FILE_TYPE = ' || p_output_file_type );
870
871 OPEN c_get_leg_cd( lvn_business_group_id );
872 FETCH c_get_leg_cd INTO lvc_legislation_code,lvc_business_group_name;
873 CLOSE c_get_leg_cd;
874
875 hr_utility.trace('Lvc_legislation_code = ' || nvl(lvc_legislation_code,'NULL'));
876 hr_utility.set_location(gv_package_name || '', 20);
877
881 /* STEP 2: Run Balance Status Section */
878
879
880
882
883 fnd_file.put_line(fnd_file.output, formated_header_string(
884 gv_title_sec1 || ' '
885 ,p_output_file_type
886 ));
887
888 IF p_output_file_type ='HTML' THEN
889 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
890 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
891 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
892 END IF;
893
894 /* Write the column Header */
895
896 fnd_file.put_line(fnd_file.output,formated_header_sec1( p_output_file_type));
897
898 IF p_output_file_type ='HTML' THEN
899 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
900 END IF;
901
902 FOR i in c_run_balance_status(lvn_business_Group_id) LOOP
903
904 lv_data_row := formated_detail4(
905 p_output_file_type
906 ,i.balance_name
907 ,i.dimension_name
908 ,i.balance_load_date
909 ,i.status
910 );
911
912 if p_output_file_type ='HTML' then
913 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
914 end if;
915 hr_utility.trace(lv_data_row);
916 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
917 END LOOP ;
918
919 IF p_output_file_type ='HTML' THEN
920 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
921 END IF;
922
923
924 /* STEP 3: FOR the SEEDED Attribute */
925 /* Leave 4 blank line */
926 FOR i in 1..4 LOOP
927 fnd_file.put_line(fnd_file.output, formated_header_string(
928 ' '
929 ,p_output_file_type
930 ));
931 END LOOP;
932
933 fnd_file.put_line(fnd_file.output, formated_header_string(
934 gv_title_sec2 || ' '
935 ,p_output_file_type
936 ));
937
938 IF p_output_file_type ='HTML' THEN
939 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
940 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
941 END IF;
942
943 /* Write the column Header */
944
945 fnd_file.put_line(fnd_file.output,formated_header_sec2( p_output_file_type));
946
947 IF p_output_file_type ='HTML' THEN
948 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
949 END IF;
950
951 For i in c_seeded_att( lvc_legislation_code ) loop
952 hr_utility.trace( 'Attribute name :- ' || i.attribute_name);
953
954 /* get the max date for attribute */
955
956 open c_attrib_status( lvn_business_group_id ,
957 i.attribute_id );
958 fetch c_attrib_status into lvn_attribute_id,lvd_start_date; -- 3651755
959 close c_attrib_status;
960
961 lvc_balance_status := check_balance_status(
962 lvd_start_date ,
963 lvn_business_Group_id,
964 lvn_attribute_id,
965 lvc_legislation_code );
966
967 hr_utility.trace('Returned status is ' || lvc_balance_status );
968 /* if the Status is INVALID then we will not give the valid date*/
969 IF lvc_balance_status = gv_invalid THEN
970 lvd_start_date := NULL;
971 END IF;
972
973 lv_data_row := formated_detail3(
974 p_output_file_type
975 ,i.attribute_name
976 ,to_Char(lvd_start_date,'yyyy/mm/dd')
977 ,lvc_balance_status
978 );
979
980 if p_output_file_type ='HTML' then
981 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
982 end if;
983 hr_utility.trace(lv_data_row);
984 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
985
986
987 END LOOP ; /* c_seeded_att */
988
989 /* STEP 5 : For USER DEFINED attributes*/
990
991 For i in c_userdef_att( lvn_business_group_id ) loop
992 hr_utility.trace( 'Attribute name :- ' || i.attribute_name);
993
994 /* get the attribute_id and max date for attribute */
995
996 open c_attrib_status( lvn_business_group_id ,
997 i.attribute_id );
998 fetch c_attrib_status into lvn_attribute_id,lvd_start_date;
999 close c_attrib_status;
1000
1001 lvc_balance_status := check_balance_status(
1002 lvd_start_date ,
1003 lvn_business_Group_id,
1004 lvn_attribute_id,
1005 lvc_legislation_code );
1006 /* if the Status is INVALID then we will not give the valid date*/
1007 IF lvc_balance_status = gv_invalid THEN
1008 lvd_start_date := NULL;
1009 END IF;
1010
1011 lv_data_row := formated_detail3(
1012 p_output_file_type
1016 );
1013 ,i.attribute_name
1014 ,to_Char(lvd_start_date,'yyyy/mm/dd')
1015 ,lvc_balance_status
1017
1018 if p_output_file_type ='HTML' then
1019 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1020 end if;
1021 hr_utility.trace(lv_data_row);
1022 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1023
1024
1025 END LOOP ; /* c_userdef_att */
1026
1027 IF p_output_file_type ='HTML' THEN
1028 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1029 END IF;
1030
1031
1032
1033 /* Step 6 section 3 */
1034
1035 /* If the Parameter has been selected in SRS as Yes then only Execute this Section 3 */
1036
1037 IF p_attribute_balance = 'Y' THEN
1038
1039 FOR i in 1..4 LOOP
1040 fnd_file.put_line(fnd_file.output, formated_header_string(
1041 ' '
1042 ,p_output_file_type
1043 ));
1044 END LOOP;
1045
1046 fnd_file.put_line(fnd_file.output, formated_header_string(
1047 gv_title_sec3 || ' '
1048 ,p_output_file_type
1049 ));
1050
1051 IF p_output_file_type ='HTML' THEN
1052 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1053 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1054 END IF;
1055
1056 /* Write the column Header */
1057
1058 fnd_file.put_line(fnd_file.output,formated_header_sec3( p_output_file_type));
1059
1060 IF p_output_file_type ='HTML' THEN
1061 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1062 END IF;
1063
1064 For i in c_seeded_att( lvc_legislation_code ) loop
1065 hr_utility.trace( 'Attribute name :- ' || i.attribute_name);
1066
1067 /* write the balance and dimension name */
1068
1069 FOR J IN c_attrib_bal(lvn_business_group_id, i.attribute_id,lvc_legislation_code) loop
1070
1071 lv_data_row := formated_detail3(
1072 p_output_file_type
1073 ,i.attribute_name
1074 ,j.balance_name
1075 ,j.dimension_name
1076 );
1077
1078 if p_output_file_type ='HTML' then
1079 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1080 end if;
1081 hr_utility.trace(lv_data_row);
1082 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1083 END LOOP;
1084
1085 END LOOP ; /* c_seeded_att */
1086
1087 For i in c_userdef_att( lvn_business_group_id ) loop
1088 hr_utility.trace( 'Attribute name :- ' || i.attribute_name);
1089
1090 /* write the balance and dimension name */
1091
1092 FOR J IN c_attrib_bal(lvn_business_group_id, i.attribute_id,lvc_legislation_code) loop
1093
1094 lv_data_row := formated_detail3(
1095 p_output_file_type
1096 ,i.attribute_name
1097 ,j.balance_name
1098 ,j.dimension_name
1099 );
1100
1101 if p_output_file_type ='HTML' then
1102 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1103 end if;
1104 hr_utility.trace(lv_data_row);
1105 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1106 END LOOP;
1107
1108 END LOOP ; /* c_userdef_att */
1109
1110 IF p_output_file_type ='HTML' THEN
1111 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1112
1113 END IF;
1114
1115 END IF ; /* IF p_attribute_balance = 'Y' */
1116
1117
1118
1119 /* STEP 8 Last section */
1120 hr_utility.trace('STEP 8 Validation Section ');
1121
1122 FOR j in c_attribute_validation( lvn_business_group_id
1123 ,lvc_legislation_code )
1124 LOOP
1125 lvn_count := lvn_count +1;
1126 /* Print only first time */
1127 IF lvn_count = 1 THEN
1128
1129 fnd_file.put_line(fnd_file.output, formated_header_string(
1130 gv_title_sec4 || ' '
1131 ,p_output_file_type
1132 ));
1133
1134
1135
1136 IF p_output_file_type ='HTML' THEN
1137 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1138 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1139 END IF;
1140
1141 /* Write the column Header */
1142
1143 fnd_file.put_line(fnd_file.output,formated_header_sec4( p_output_file_type));
1144
1145 IF p_output_file_type ='HTML' THEN
1146 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1147 END IF;
1148
1149 END IF; /* lvn_count */
1150
1151 lv_data_row := formated_validation_detail(
1152 p_output_file_type
1153 ,lvc_business_group_name
1154 ,j.attribute_name
1155 ,j.balance_name
1156 ,j.dimension_name
1157 ,j.save_run_balance
1158 );
1159
1160 if p_output_file_type ='HTML' then
1161 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1162 end if;
1163 hr_utility.trace(lv_data_row);
1164 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1165
1166
1167 END LOOP; /* c_attrib_details */
1168
1169
1170 IF p_output_file_type ='HTML' THEN
1171 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1172 END IF;
1173
1174 IF p_output_file_type ='HTML' THEN
1175 UPDATE fnd_concurrent_requests
1176 SET output_file_type = 'HTML'
1177 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1178 END IF;
1179
1180 end ebra_diagnostics;
1181 --begin
1182 --hr_utility.trace_on(null, 'ORACLE');
1183 end pay_ebra_diagnostics;