[Home] [Help]
PACKAGE BODY: APPS.HZ_MGD_MASS_UPDATE_REP_GEN
Source
1 PACKAGE BODY HZ_MGD_MASS_UPDATE_REP_GEN AS
2 /* $Header: ARHCMURB.pls 120.4 2004/02/24 21:46:53 mraymond noship $ */
3
4 /*+=======================================================================+
5 --| Copyright (c) 1999 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+=======================================================================+
9 --| FILENAME |
10 --| ARHCMURB.pls |
11 --| |
12 --| DESCRIPTION |
13 --| Use this package to generate output report for Mass update |
14 --| |
15 --| PROCEDURE LIST |
16 --| Initialize |
17 --| Log |
18 --| Add_Item |
19 --| Generate_Report |
20 --| |
21 --| HISTORY |
22 --| 05/22/2002 tsimmond Created |
23 --| 11/27/2002 tsimmond Updated Added WHENEVER OSERROR EXIT |
24 --| FAILURE ROLLBACK |
25 --+======================================================================*/
26
27
28
29
30 --===================
31 -- COMMENT : PL/SQL Table definition. This table will be used to record
32 -- log information for the exception cases.
33 --===================
34 TYPE REPORT_EXP_REC IS RECORD
35 ( party VARCHAR2(50)
36 , customer VARCHAR2(50)
37 , site VARCHAR2(50)
38 );
39
40
41 TYPE REPORT_EXP_TABLE IS TABLE OF REPORT_EXP_REC
42 INDEX BY BINARY_INTEGER;
43
44
45
46 --===================
47 -- CONSTANTS
48 --===================
49 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_MGD_MASS_UPDATE_REP_GEN';
50 G_ORG_ID NUMBER := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
51
52 --===================
53 -- PUBLIC VARIABLES
54 --===================
55 g_program_type VARCHAR2(30);
56 g_rec_no NUMBER:=0;
57
58 g_mode VARCHAR2(15);
59 g_log_level NUMBER := 5 ;
60 g_log_mode VARCHAR2(240) :=
61 NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') ;
62
63 g_exp_table HZ_MGD_MASS_UPDATE_REP_GEN.REPORT_EXP_TABLE;
64
65
66 --===================
67 -- PRIVATE PROCEDURES AND FUNCTIONS
68 --===================
69
70 --===================
71 -- FUNCTION : Add_Spaces
72 -- PARAMETERS: in_space Number of blank spaces required
73 -- COMMENT : Function will add blank spaces to the report
74 ---
75 --====================
76 FUNCTION Add_Spaces
77 ( in_space IN NUMBER
78 )
79 RETURN VARCHAR2
80 IS
81
82 out_space VARCHAR2(140) := ' ' ;
83
84 BEGIN
85 FOR i in 1..in_space LOOP
86 out_space := out_space || ' ';
87 END LOOP;
88
89 RETURN (out_space);
90
91
92 END Add_Spaces;
93
94 --====================================================
95 -- FUNCTION : Draw_Line PRIVATE
96 -- PARAMETERS: in_length Line Length
97 -- in_type Type of Line ('-' or '=')
98 --
99 -- COMMENT : Function to draw lines in the report
100 --
101 --====================================================
102 FUNCTION Draw_line
103 ( in_length IN NUMBER
104 , in_type IN NUMBER
105 )
106 RETURN VARCHAR2
107 IS
108
109 out_line VARCHAR2(140);
110
111 BEGIN
112 FOR i in 1..in_length LOOP
113 IF in_type = 1
114 THEN
115 out_line := out_line || '-' ;
116 ELSE
117 out_line := out_line || '=' ;
118 END IF;
119 END LOOP;
120
121 RETURN (out_line);
122
123
124 END Draw_Line;
125
126 --========================================================================
127 -- PROCEDURE : Print_Line PRIVATE
128 -- PARAMETERS: p_line IN line to be printed
129 -- COMMENT : Print to output file if called from a concurrent request
130 -- It uses dbms_output otherwise
131 --=======================================================================
132 PROCEDURE Print_Line
133 ( p_line IN VARCHAR2
134 )
135 IS
136 l_line VARCHAR2(132);
137 BEGIN
138 -- SQL*Plus session:
139 IF g_mode='SQL'
140 THEN
141 l_line := '.'||p_line;
142 --DBMS_OUTPUT.put_line(l_line);
143
144 ELSE
145 -- Concurrent request
146 FND_FILE.put_line( FND_FILE.output
147 , p_line
148 );
149 END IF;
150
151
152 END Print_Line;
153
154 --========================================================================
155 -- PROCEDURE : Print_Col_Titles PRIVATE
156 -- COMMENT : This procedure prints Column titles with parties
157 --=======================================================================
158 PROCEDURE Print_Col_Titles_P
159 IS
160 BEGIN
161
162 Print_line(FND_MESSAGE.get_string
163 ('AR','AR_MGD_MASS_UPDATE_COL1')
164 ||add_spaces(52-lengthb(FND_MESSAGE.get_string
165 ('AR','AR_MGD_MASS_UPDATE_COL1')
166 )
167 )
168 ||FND_MESSAGE.get_string
169 ('AR','AR_MGD_MASS_UPDATE_COL2')
170 ||add_spaces(42-lengthb(FND_MESSAGE.get_string
171 ('AR','AR_MGD_MASS_UPDATE_COL2')
172 )
173 )
174 ||FND_MESSAGE.get_string
175 ('AR','AR_MGD_MASS_UPDATE_COL3')
176 ||add_spaces(36-lengthb(FND_MESSAGE.get_string
177 ('AR','AR_MGD_MASS_UPDATE_COL3')
178 )
179 )
180 );
181
182 Print_line(draw_line(50,1)
183 ||add_spaces(2)
184 ||draw_line(40,1)
185 ||add_spaces(2)
186 ||draw_line(36,1)
187 );
188
189
190 END Print_Col_Titles_P;
191
192 --========================================================================
193 -- PROCEDURE : Print_Col_Titles PRIVATE
194 -- COMMENT : This procedure prints Column titles without parties
195 --=======================================================================
196 PROCEDURE Print_Col_Titles
197 IS
198 BEGIN
199
200 Print_line(FND_MESSAGE.get_string
201 ('AR','AR_MGD_MASS_UPDATE_COL2')
202 ||add_spaces(42-lengthb(FND_MESSAGE.get_string
203 ('AR','AR_MGD_MASS_UPDATE_COL2')
204 )
205 )
206 ||FND_MESSAGE.get_string
207 ('AR','AR_MGD_MASS_UPDATE_COL3')
208 );
209
210 Print_line(draw_line(40,1)
211 ||add_spaces(2)
212 ||draw_line(36,1)
213 );
214
215
216 END Print_Col_Titles;
217
218
219 --========================================================================
220 -- FUNCTION : Find_meaning PRIVATE
221 -- PARAMETERS: p_lookup_code
222 -- COMMENT : This procedure returns meaning for specified lookup code
223 --=======================================================================
224 FUNCTION Find_meaning
225 (p_lookup_code IN VARCHAR2
226 )
227 RETURN VARCHAR2
228 IS
229 l_meaning VARCHAR2(80);
230
231 BEGIN
232 SELECT
233 meaning
234 INTO
235 l_meaning
236 FROM fnd_lookups
237 WHERE lookup_code=p_lookup_code
238 AND lookup_type='YES_NO';
239
240 RETURN(l_meaning);
241
242 EXCEPTION
243 WHEN no_data_found
244 THEN RETURN (null);
245
246 WHEN OTHERS THEN
247 HZ_MGD_MASS_UPDATE_REP_GEN.log ( HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_EXCEPTION
248 , 'SQLERRM '|| SQLERRM) ;
249
250 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
251 THEN
252 FND_MSG_PUB.Add_Exc_Msg
253 ( G_PKG_NAME
254 , 'Find_meaning'
255 );
256 END IF;
257 RAISE;
258
259 END Find_meaning;
260
261 --===================
262 -- PUBLIC PROCEDURES AND FUNCTIONS
263 --===================
264
265
266 --=====================================================================
267 -- PROCEDURE : Initialize PUBLIC
268 -- PARAMETERS:
269 --
270 -- COMMENT : This the procedure will initialize the log facility
271 -- and pls/sql tables for vendor conversion.
272 -- It should be called from the top level procedure of
273 -- each concurrent program.
274 --=====================================================================
275 PROCEDURE Initialize
276 IS
277
278 BEGIN
279 -- Checking if the log facility is ON
280 -- Checking if the program is running from SQL Plus
281 IF FND_PROFILE.Value('CONC_REQUEST_ID') is NULL
282 THEN
283 g_mode:='SQL';
284 ELSE
285 g_mode:='SRS';
286 END IF;
287
288 -- g_log_mode := 'ON' ;
289 -- BUG 2040015
290
291 g_log_level := 5 ;
292 g_log_mode := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') ;
293
294 -- Initialize pl/sql tables
295
296 g_exp_table.DELETE;
297
298 END Initialize;
299
300
301 --=======================================================================
302 -- PROCEDURE : Log PUBLIC
303 -- PARAMETERS: p_priority IN priority of the message -
304 -- from highest to lowest:
305 -- G_LOG_ERROR
306 -- G_LOG_EXCEPTION
307 -- G_LOG_EVENT
308 -- G_LOG_PROCEDURE
309 -- G_LOG_STATEMENT
310 -- p_msg IN message to be print on the log file
311 -- COMMENT : Add an entry to the log
312 --=======================================================================
313 PROCEDURE Log
314 ( p_priority IN NUMBER
315 , p_msg IN VARCHAR2
316 )
317 IS
318
319 BEGIN
320 IF NVL(g_mode,'SQL') = 'SQL' THEN
321 -- SQL*Plus session:
322 --DBMS_OUTPUT.put_line(p_msg);
323 NULL;
324 ELSE
325 -- BUG 2040015
326 BEGIN
327 IF NVL(g_log_mode,'N') = 'Y' THEN
328 -- Concurrent request
329 IF NVL(p_priority,5) <= NVL(g_log_level,5) THEN
330 FND_FILE.put_line
331 ( FND_FILE.log
332 , p_msg
333 );
334 END IF;
335 ELSE
336 IF NVL(p_priority,5) <= 3 THEN
337 FND_FILE.put_line
338 ( FND_FILE.log
339 , p_msg
340 );
341 END IF;
342 END IF;
343 END ;
344 END IF;
345 EXCEPTION
346 WHEN OTHERS THEN
347 NULL;
348 END Log;
349
350
351
352 --==========================================================================
353 -- PROCEDURE : Add_Exp_Item PUBLIC
354 -- PARAMETERS: p_party name of the party not updated
355 -- p_customer name of the customer not updated
356 -- p_site name of the customer site not updated
357 --
358 -- COMMENT : This is the procedure to record exception information into g_exp_table.
359 --
360 --==========================================================================
361 PROCEDURE Add_Exp_Item
362 ( p_party IN VARCHAR2
363 , p_customer IN VARCHAR2
364 , p_site IN VARCHAR2
365 )
366 IS
367 BEGIN
368
369 HZ_MGD_MASS_UPDATE_REP_GEN.log
370 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
371 , p_msg => '>> Add_Exp_Item ' );
372
373 g_rec_no:=g_exp_table.COUNT +1;
374
375 g_exp_table(g_rec_no).party:=p_party;
376 g_exp_table(g_rec_no).customer:=p_customer;
377 g_exp_table(g_rec_no).site:=p_site;
378
379 HZ_MGD_MASS_UPDATE_REP_GEN.log
380 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
381 , p_msg => '<< Add_Exp_Item ' );
382
383 END Add_Exp_Item;
384 --====================
385 -- PROCEDURE : Generate_Report PUBLIC
386 -- PARAMETERS: p_cust_prof_class Name of the profile class
387 -- p_currency_code Profile currency
388 -- p_rule_set Name of the rule set
389 --
390 -- COMMENT : This is the procedure to print action information.
391 --====================
392 PROCEDURE Generate_Report
393 ( p_prof_class_id IN NUMBER
394 , p_currency_code IN VARCHAR2
395 , p_profile_class_amount_id IN NUMBER
396 )
397 IS
398
399 l_profile_class VARCHAR2(30);
400 l_operating_unit VARCHAR2(60);
401 l_header_space INTEGER := 0;
402 l_header1_length INTEGER := 0;
403 l_header2_length INTEGER := 0;
404 l_col1_margin INTEGER := 0;
405 l_col2_margin INTEGER := 0;
406 l_col3_margin INTEGER := 0;
407 l_col4_margin INTEGER := 0;
408 l_foot_length INTEGER := 0;
409 l_param_max INTEGER := 0;
410 l_no_data VARCHAR2(1):='N';
411
412 l_header1 VARCHAR2(200);
413 l_header2 VARCHAR2(200);
414 l_text_start VARCHAR2(200);
415 l_rec_count NUMBER:=0;
416
417
418 BEGIN
419
420 HZ_MGD_MASS_UPDATE_REP_GEN.log
421 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
422 , p_msg => '>> Generate_Report ' );
423
424 -------Getting Profile Class Name, operating Unit Name---------------
425
426 HZ_MGD_MASS_UPDATE_REP_GEN.log
427 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
428 , p_msg => ' Getting Profile Class Name, operating Unit Name '
429 );
430
431 BEGIN
432 SELECT
433 name
434 INTO
435 l_profile_class
436 FROM
437 hz_cust_profile_classes
438 WHERE profile_class_id =p_prof_class_id;
439
440 -- HZ_MGD_MASS_UPDATE_REP_GEN.log
441 -- ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
442 -- , p_msg => 'Profile Class Name= '||l_profile_class
443 -- );
444
445 EXCEPTION
446 WHEN NO_DATA_FOUND
447 THEN
448 l_no_data:='Y';
449
450 END;
451
452 BEGIN
453
454 SELECT
455 SUBSTRB(name,1,30)
456 INTO
457 l_operating_unit
458 FROM
459 hr_operating_units
460 WHERE organization_id=G_ORG_ID;
461
462 HZ_MGD_MASS_UPDATE_REP_GEN.log
463 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
464 , p_msg => 'operating Unit Name= '||l_operating_unit
465 );
466
467 EXCEPTION
468 WHEN NO_DATA_FOUND
469 THEN
470
471 l_no_data:='Y';
472
473 HZ_MGD_MASS_UPDATE_REP_GEN.log
474 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
475 , p_msg => 'NO_DATA_FOUND '
476 );
477 END;
478
479 --------Centering Report Header----------
480 --l_header1:=' Mass update credit usages ';
481 --l_header2:=' Exception Report ';
482
483 l_header1:=NVL(FND_MESSAGE.get_string
484 ('AR','AR_MGD_MASS_UPDATE_TITLE1'
485 ),' '
486 );
487
488 l_header1_length :=LENGTHB(l_header1);
489
490 l_header2:=NVL(FND_MESSAGE.get_string
491 ('AR','AR_MGD_MASS_UPDATE_TITLE2'
492 ),' '
493 );
494 l_header2_length :=LENGTHB(l_header2);
495
496
497 l_header_space:=40;
498
499 ---------------Printing Report Header------------------------
500
501 Print_line(add_spaces(G_rpt_page_col-1)
502 );
503 Print_line(add_spaces(1)
504 ||NVL(l_operating_unit,' ')
505 ||add_spaces(l_header_space-LENGTHB(NVL(l_operating_unit,' ')))
506 ||l_header1
507 ||add_spaces(l_header_space-l_header1_length)
508 ||TO_CHAR(sysdate)
509 );
510
511 Print_line(add_spaces(1)
512 ||add_spaces(l_header_space)
513 ||l_header2
514 ||add_spaces(l_header_space-l_header2_length)
515 );
516
517 Print_line(add_spaces(G_rpt_page_col)
518 );
519
520
521 ------------------Centuring Parameters--------------------------
522 l_param_max:=60;
523
524 -----------------Printing Parameters
525 Print_line(add_spaces(l_param_max-LENGTHB(NVL(FND_MESSAGE.get_string
526 ('AR','AR_MGD_MASS_UPDATE_PARAM1'
527 ),' ')
528 )
529 )
530 ||NVL(FND_MESSAGE.get_string
531 ('AR','AR_MGD_MASS_UPDATE_PARAM1'
532 ),' '
533 )
534 ||add_spaces(1)
535 ||l_profile_class
536 );
537 Print_line(add_spaces(l_param_max-LENGTHB(NVL(FND_MESSAGE.get_string
538 ('AR','AR_MGD_MASS_UPDATE_PARAM2'
539 ),' '
540 )
541 )
542 )
543 ||NVL(FND_MESSAGE.get_string
544 ('AR','AR_MGD_MASS_UPDATE_PARAM2'
545 ),' '
546 )
547 ||add_spaces(1)
548 ||p_currency_code
549 );
550
551
552 Print_line(add_spaces(G_rpt_page_col)
553 );
554
555
556 ------------Printing Body of the Report------------------------
557 ------------Check if there is data in the pl/sql table
558
559 l_rec_count:=g_exp_table.COUNT;
560
561 ----------No data, no exceptions--------------------------
562 IF l_rec_count=0
563 THEN
564 -----centuring the text "No exceptions.All the credit profiles have been updated successfully."
565
566 l_text_start:=ROUND((130-lengthb(NVL(FND_MESSAGE.get_string
567 ('AR','AR_MGD_MASS_UPDATE_FOOTER2'
568 ),' '
569 )))/2);
570
571 Print_line(add_spaces(1)
572 ||add_spaces(l_text_start)
573 ||NVL(FND_MESSAGE.get_string
574 ('AR','AR_MGD_MASS_UPDATE_FOOTER2'
575 ),' '
576 )
577 );
578
579 ELSE
580 -----------------Printing Report----------------------
581 IF HZ_MGD_MASS_UPDATE_CP.G_RELEASE='NEW'
582 THEN
583 ---------------Print party----------------
584
585 ---------------Print text-----------------
586 Print_line(add_spaces(1)
587 ||FND_MESSAGE.get_string('AR','AR_MGD_MASS_UPDATE_TEXT1'
588 )
589 );
590 Print_line(add_spaces(1)
591 ||FND_MESSAGE.get_string('AR','AR_MGD_MASS_UPDATE_TEXT2'
592 )
593 );
594 Print_line(add_spaces(1)
595 ||FND_MESSAGE.get_string('AR','AR_MGD_MASS_UPDATE_TEXT3'
596 )
597 );
598
599 Print_line(add_spaces(G_rpt_page_col-1)
600 );
601 Print_line(add_spaces(G_rpt_page_col)
602 );
603
604 --------------Printing Column titles with party---------------
605
606 Print_Col_Titles_P;
607
608 ------------------Printing Columns with party-----------------
609
610 FOR i IN g_exp_table.FIRST .. g_exp_table.LAST
611 LOOP
612 Print_line(NVL(substrb(g_exp_table(i).party,1,50),' ')
613 ||add_spaces(52-lengthb(NVL(g_exp_table(i).party,' ')) )
614 ||NVL(substrb(g_exp_table(i).customer,1,40),' ')
615 ||add_spaces(42-NVL(lengthb(g_exp_table(i).customer),0))
616 ||NVL(g_exp_table(i).site,' ')
617 );
618 END LOOP;
619
620 ELSIF HZ_MGD_MASS_UPDATE_CP.G_RELEASE='OLD'
621 THEN
622 --------------Do not print party---------------
623
624
625 ---------------Print text-----------------
626 -----The following customers/customer sites have not been updated.---
627
628 Print_line(add_spaces(1)
629 ||FND_MESSAGE.get_string('AR','AR_MGD_MASS_UPDATE_TEXT4'
630 )
631 );
632
633
634 Print_line(add_spaces(1)
635 ||FND_MESSAGE.get_string('AR','AR_MGD_MASS_UPDATE_TEXT2'
636 )
637 );
638
639
640 Print_line(add_spaces(1)
641 ||FND_MESSAGE.get_string('AR','AR_MGD_MASS_UPDATE_TEXT5'
642 )
643 );
644
645 Print_line(add_spaces(G_rpt_page_col-1)
646 );
647 Print_line(add_spaces(G_rpt_page_col)
648 );
649
650 --------------Printing Column titles---------------
651
652 Print_Col_Titles;
653
654 --------------Printing Columns--------------------
655
656 FOR i IN g_exp_table.FIRST .. g_exp_table.LAST
657 LOOP
658 Print_line(NVL(substrb(g_exp_table(i).customer,1,40),' ')
659 ||add_spaces(42-lengthb(NVL(g_exp_table(i).customer,' ')))
660 ||NVL(g_exp_table(i).site,' ')
661 );
662 END LOOP;
663 END IF;
664
665 END IF;
666
667 -------------------Printing Footer of the Report-----------------------
668 Print_line(add_spaces(G_rpt_page_col)
669 );
670 Print_line(add_spaces(G_rpt_page_col)
671 );
672
673 Print_line(add_spaces(l_header_space)
674 ||'****** '
675 ||NVL(FND_MESSAGE.get_string
676 ('AR','AR_MGD_MASS_UPDATE_FOOTER1'
677 ),' '
678 )
679 ||' ******'
680 );
681
682 HZ_MGD_MASS_UPDATE_REP_GEN.log
683 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
684 , p_msg => '<< Generate_Report ' );
685
686 END Generate_Report;
687
688 END HZ_MGD_MASS_UPDATE_REP_GEN;