[Home] [Help]
PACKAGE BODY: APPS.AD_PATCH_HIST_REPS
Source
1 package body ad_patch_hist_reps as
2 /* $Header: adphrepb.pls 115.27 2004/09/13 08:49:31 sgadag ship $ */
3
4 maxLineLen number;
5
6 -- Function to write a text line into the teporary table
7
8 function writeStrToDb(lineCounter number,
9 inStr varchar2)
10 return number is
11
12 -- This routine is a code simplification for the two lines that it
13 -- contains. If we don't do this, we have duplicated lines throughout
14 -- the program, not very efficient!
15
16 counter number;
17 begin
18 counter := lineCounter+1;
19 insert into ad_generic_temp (LINE_SEQUENCE,CONTENTS) values(counter,inStr);
20 return counter;
21 end writeStrToDb;
22 --
23 function writeQueryToDb(lineCounter number,
24 sqlErr varchar2 DEFAULT NULL,
25 qryStr varchar2)
26 return number is
27 tmpQry varchar2(4000);
28 tmpStr varchar2(4000);
29 lineCnt number;
30 begin
31 tmpQry := qryStr;
32 lineCnt := writeStrToDb(lineCounter,sqlErr||' Select String is:');
33 while ( length(tmpQry) > 0 ) loop
34 lineCnt := writeStrToDb(lineCnt,substr(tmpQry,1,maxLineLen));
35 tmpStr := substr(tmpQry,maxLineLen+1);
36 tmpQry := tmpStr;
37 end loop;
38 commit;
39 return lineCnt;
40 end writeQueryToDb;
41 --
42 -- writes the xml output from a CLOB variable into the Global temp table
43 -- this function removes <ROW> </ROW> or <ROWSET> </ROWSET> from the output
44 -- further if the output from the CLOB variable is greater than 255 characters
45 -- then writes 255 characters at a time into the Global temp table.
46 --
47 function printClobOut(result IN OUT NOCOPY CLOB,line_num in number) return number is
48 xmlstr varchar2(32767);
49 line varchar2(30000);
50 line1 varchar2(300);
51 line_num_out number;
52 i number;
53 begin
54 line_num_out:=line_num;
55 xmlstr := dbms_lob.SUBSTR(result,32767);
56 loop
57 exit when xmlstr is null;
58 line := substr(xmlstr,1,instr(xmlstr,fnd_global.newline)-1);
59 if instr(line,'ROW') = 0 || instr(line,'?xml version') then
60 --dbms_output.put_line(line);
61 if length(line) > 500 then
62 i:=1;
63 while i <= length(line)
64 loop
65 if ((i + 500) > length(line)) then
66 line_num_out:=writeStrToDb(line_num_out,substr(line,i,(length(line)-i+1)));
67 else
68 line_num_out:=writeStrToDb(line_num_out,substr(line,i,500));
69 end if;
70 i:=i+500;
71 end loop;
72 else
73 line_num_out:=writeStrToDb(line_num_out,line);
74 end if;
75 end if;
76 xmlstr := substr(xmlstr,instr(xmlstr,fnd_global.newline)+1);
77 end loop;
78 return line_num_out;
79 end printClobOut;
80 --
81 --
82 --
83 function printBeginSearch(line_num in number) return number is
84 line varchar2(20);
85 line_num_out number;
86 begin
87 line :='<SEARCH_DETAILS>';
88 line_num_out:=line_num;
89 --dbms_output.put_line(line);
90 line_num_out:=writeStrToDb(line_num_out,line);
91 return line_num_out;
92 end printBeginSearch;
93 --
94 --
95 --
96 function printEndSearch(line_num in number) return number is
97 line varchar2(20);
98 line_num_out number;
99 begin
100 line :='</SEARCH_DETAILS>';
101 line_num_out:=line_num;
102 --dbms_output.put_line(line);
103 line_num_out:=writeStrToDb(line_num_out,line);
104 return line_num_out;
105 end printEndSearch;
106 --
107 --
108 --
109 function printBeginPatch(line_num in number) return number is
110 line varchar2(20);
111 line_num_out number;
112 begin
113 line :='<PATCH_DETAILS>';
114 line_num_out:=line_num;
115 --dbms_output.put_line(line);
116 line_num_out:=writeStrToDb(line_num_out,line);
117 return line_num_out;
118 end printBeginPatch;
119 --
120 --
121 --
122 function printEndPatch(line_num in number) return number is
123 line varchar2(20);
124 line_num_out number;
125 begin
126 line :='</PATCH_DETAILS>';
127 line_num_out:=line_num;
128 --dbms_output.put_line(line);
129 line_num_out:=writeStrToDb(line_num_out,line);
130 return line_num_out;
131 end printEndPatch;
132 --
133 --
134 --
135 function printBeginBug(line_num in number) return number is
136 line varchar2(20);
137 line_num_out number;
138 begin
139 line :='<BUG_DETAILS>';
140 line_num_out:=line_num;
141 --dbms_output.put_line(line);
142 line_num_out:=writeStrToDb(line_num_out,line);
143 return line_num_out;
144 end printBeginBug;
145 --
146 --
147 --
148 function printEndBug(line_num in number) return number is
149 line varchar2(20);
150 line_num_out number;
151 begin
152 line :='</BUG_DETAILS>';
153 line_num_out:=line_num;
154 --dbms_output.put_line(line);
155 line_num_out:=writeStrToDb(line_num_out,line);
156 return line_num_out;
157 end printEndBug;
158 --
159 --
160 --
161 function printBeginAction(line_num in number) return number is
162 line varchar2(20);
163 line_num_out number;
164 begin
165 line :='<ACTION_DETAILS>';
166 line_num_out:=line_num;
167 --dbms_output.put_line(line);
168 line_num_out:=writeStrToDb(line_num_out,line);
169 return line_num_out;
170 end printBeginAction;
171 --
172 --
173 --
174 function printEndAction(line_num in number) return number is
175 line varchar2(20);
176 line_num_out number;
177 begin
178 line :='</ACTION_DETAILS>';
179 line_num_out:=line_num;
180 --dbms_output.put_line(line);
181 line_num_out:=writeStrToDb(line_num_out,line);
182 return line_num_out;
183 end printEndAction;
184 --
185 --
186 --
187 function printBeginXML(line_num in number,is_patch boolean) return number is
188 line1 varchar2(50);
189 line2 varchar2(500);
190 line3 varchar2(20);
191 line4 varchar2(500);
192 line_num_out number;
193 begin
194 line1 :='<?xml version="1.0" ?>';
195 line2 :='<?xml-stylesheet type="text/xsl" href="adptchrep.xsl"?>';
196 line3 :='<ROWSET>';
197 line4 :='<?xml-stylesheet type="text/xsl" href="adfilerep.xsl"?>';
198 line_num_out:=line_num;
199 --dbms_output.put_line(line1);
200 line_num_out:=writeStrToDb(line_num_out,line1);
201 --dbms_output.put_line(line2);
202 if is_patch then
203 line_num_out:=writeStrToDb(line_num_out,line2);
204 else
205 line_num_out:=writeStrToDb(line_num_out,line4);
206 end if;
207 --dbms_output.put_line(line3);
208 line_num_out:=writeStrToDb(line_num_out,line3);
209
210 return line_num_out;
211 end printBeginXML;
212 --
213 --
214 --
215 function printEndXML(line_num in number) return number is
216 line varchar2(20);
217 line_num_out number;
218 begin
219 line :='</ROWSET>';
220 line_num_out:=line_num;
221 --dbms_output.put_line(line);
222 line_num_out:=writeStrToDb(line_num_out,line);
223 return line_num_out;
224 end printEndXML;
225 --
226 --
227 --
228 function get_concat_mergepatches(p_ptch_drvr_id number)
229 return varchar2 is
230
231 l_concat_bugNumber varchar2(30000);
232 l_first_iter boolean; -- first iteration flag
233 l_rem_space number :=0; -- remaining space
234 l_len_till_now number :=0; -- length of l_concat_bugid
235
236 cursor c1(p_patch_driver_id number) is
237 select bug_number from ad_bugs where bug_id in (
238 select bug_id from ad_comprising_patches
239 where patch_driver_id = p_patch_driver_id);
240 begin
241 l_concat_bugNumber := null;
242 l_first_iter := TRUE;
243 for c1_rec in c1(p_ptch_drvr_id) loop
244 if (l_first_iter)
245 then
246 l_concat_bugNumber := c1_rec.bug_number;
247 l_first_iter := FALSE;
248 l_len_till_now :=length(l_concat_bugNumber);
249 else
250 l_rem_space :=(30000 - l_len_till_now);
251
252 -- 2 spaces must ALWAYS be available whenever we are about
253 -- to make this determination.
254
255 if (l_rem_space > length(c1_rec.bug_number) + 2)
256 then
257 l_concat_bugNumber := l_concat_bugNumber || ', '||
258 c1_rec.bug_number;
259 -- Maintain l_len_till_now (Note: 2 is for the comma and space)
260 l_len_till_now := l_len_till_now + 2 +
261 length(c1_rec.bug_number);
262 else
263 -- not enough space, show error message
264 raise_application_error(-20500,'The total of merged patches exceed the displa
265 y limit. Contact Oracle Support group.');
266 exit;
267 end if;
268 end if;
269 end loop;
270 return l_concat_bugNumber;
271 end get_concat_mergepatches;
272 --
273 --
274 --
275 function get_concat_minipks(p_ptch_drvr_id number)
276 return varchar2 is
277
278 l_concat_minipks varchar2(30000); /* intentionally having it 4K to handle
279 the minipacks in Maintenance pack */
280 l_first_iter boolean; -- first iteration flag
281
282 l_rem_space number :=0; -- remaining space
283
284 l_len_till_now number :=0; -- length of l_concat_minipks till now
285
286
287 cursor c1(p_patch_driver_id number) is
288 select patch_level
289 from ad_patch_driver_minipks
290 where patch_driver_id = p_patch_driver_id;
291 begin
292 l_concat_minipks := null;
293 l_first_iter := TRUE;
294
295 for c1_rec in c1(p_ptch_drvr_id) loop
296 if (l_first_iter)
297 then
298 l_concat_minipks := c1_rec.patch_level;
299 l_first_iter := FALSE;
300 l_len_till_now :=length(l_concat_minipks);
301 else
302 l_rem_space :=(30000 - l_len_till_now);
303
304 -- if no space avail, we want to add ", ...". This means that
305 -- 5 spaces must ALWAYS be available whenever we are about
306 -- to make this determination. This implies that we
307 -- always check for len(<patch-level>) + 5, even though we
308 -- we only intend to append <patch-level>.
309
310 if (l_rem_space > length(c1_rec.patch_level) + 5)
311 then
312 l_concat_minipks := l_concat_minipks || ', '||
313 c1_rec.patch_level;
314 -- Maintain l_len_till_now (Note: 2 is for the comma and space)
315 l_len_till_now := l_len_till_now + 2 +
316 length(c1_rec.patch_level);
317 else
318 -- not enough space, just append ", ..." and break the loop
319 l_concat_minipks := l_concat_minipks || ', ...';
320 exit;
321 end if;
322 end if;
323 end loop;
324 return l_concat_minipks;
325 end get_concat_minipks;
326 --
327 --
328 --
329 function get_level_if_one(p_app_ptch_id number)
330 return varchar2 is
331
332 l_patch_type varchar2(30);
333 l_maint_pack_level varchar(30);
334
335 cursor c1(p_patch_driver_id number) is
336 select patch_level
337 from ad_patch_driver_minipks
338 where patch_driver_id = p_patch_driver_id;
339 begin
340 select patch_type, maint_pack_level
341 into l_patch_type, l_maint_pack_level
342 from ad_applied_patches
343 where applied_patch_id = p_app_ptch_id;
344
345 if (l_patch_type = 'MAINTENANCE-PACK')
346 then
347 return l_maint_pack_level;
348 elsif (l_patch_type = 'ONE-OFF')
349 then
350 return NULL;
351 else
352 -- Mini Pack conditional declarations
353 declare
354 l_patch_driver_id number;
355 l_level varchar2(30);
356 l_count number;
357 begin
358 select patch_driver_id
359 into l_patch_driver_id
360 from ad_patch_drivers
361 where applied_patch_id = p_app_ptch_id
362 and driver_type_d_flag = 'Y';
363 l_level := null;
364 l_count := 0;
365
366 for c1_rec in c1(l_patch_driver_id) loop
367 l_count := l_count + 1;
368 l_level := '*';
369 exit when l_count >= 2;
370 l_level := c1_rec.patch_level;
371 end loop;
372 return l_level;
373 exception when no_data_found
374 then
375 return NULL;
376 end;
377 end if;
378
379 return NULL;
380 end get_level_if_one;
381 --
382 --
383 --
384 procedure populate_search_results
385 ( p_query_depth varchar2 default 1, -- PATCHES/BUGS/ACTIONS
386 p_bug_num varchar2 default NULL,
387 p_bug_prod_abbr varchar2 default NULL,
388 p_end_dt_from_v varchar2 default NULL,
389 p_end_dt_to_v varchar2 default NULL,
390 p_patch_nm varchar2 default NULL,
391 p_patch_type varchar2 default NULL,
392 p_level varchar2 default NULL,
393 p_lang varchar2 default NULL,
394 p_appltop_nm varchar2 default NULL,
395 p_limit_to_forms boolean default FALSE,
396 p_limit_to_node boolean default FALSE,
397 p_limit_to_web boolean default FALSE,
398 p_limit_to_admin boolean default FALSE,
399 p_limit_to_db_drvrs boolean default FALSE,
400 p_report_format varchar2 ) is
401
402 TYPE cur_typ IS REF CURSOR;
403 cpatches cur_typ;
404 cbugs cur_typ;
405 cactions cur_typ;
406
407 -- Variables for preparing the cursor
408 l_select1 varchar2(400);
409 l_select2 varchar2(255);
410 l_select3 varchar2(255);
411 l_select4 varchar2(255);
412 l_select5 varchar2(255);
413 l_from1 varchar2(255);
414 l_from_bug varchar(255);
415 l_where1 varchar2(500);
416 l_where2 varchar(500);
417 l_where3 varchar(500);
418 l_where_bug varchar(255);
419 l_order_by1 varchar2(255);
420
421 --Variable to consolidate the into a single string
422
423 query_str varchar2(4000);
424 query_str1 varchar2(4000);
425 tmpQry varchar2(4000);
426 tmpStr varchar2(4000);
427 tmpCnt number;
428 --Variables which stores the Patch data fetched from the cursor
429
430 v_patch_run_id number;
431 v_name varchar2(50);
432 v_language varchar2(4) ;
433 v_patch_name varchar2(120);
434 v_lvl varchar2(30);
435 v_minipks varchar2(30000); /* intentionally having it 30K to handle
436 the minipacks in Maintenance pack */
437 v_comprptch varchar2(30000);/* intentionally having it 30K to handle
438 large number of comprising patches */
439 v_start_date varchar2(16);
440 v_end_date varchar2(16);
441 v_driver_file_name varchar2(30);
442 v_driver_type varchar2(20);
443 v_patch_action_options varchar2(250);
444 v_patch_top varchar2(250);
445 v_platform varchar2(30);
446 v_servertype varchar2(20);
447
448 old_patch_run_id number := NULL;
449
450
451 -- Variables which stores the Bug data fetched from the cursor
452
453 v_bug_number varchar2(30);
454 v_product varchar2(50);
455 v_applied varchar2(3);
456 v_reason varchar2(250);
457 v_patch_run_bug_id number;
458
459 -- Variables which stores the Action data fetched from the cursor
460
461 v_act_actioncode varchar2(30);
462 v_act_executed varchar2(1);
466 v_act_patch_ver varchar2(150);
463 v_act_product varchar2(50);
464 v_act_subdir varchar2(256);
465 v_act_filename varchar2(250);
467 v_act_patch_trlev number;
468 v_act_onsitever varchar2(150);
469 v_act_onsite_trlev number;
470 v_act_pkgverdb varchar2(150);
471 v_act_pkgdb_trlev number;
472 v_act_phase varchar2(10);
473 v_act_Modfr varchar2(30);
474 v_act_args varchar2(2000);
475
476 --Variables which store the formatted Action data
477
478 queryCtx DBMS_XMLquery.ctxType;
479 result CLOB;
480
481 -- Variable to store line numbers
482 v_line_num number := 0;
483
484 -- Flag for printing the unapplied patches
485 v_printed_un_applied varchar2(1);
486
487 -- Set up maximum values for lengths of strings
488 maxPatchTopLen number := 34; -- Matches f_patch_top definition
489 rowdata ad_generic_temp.contents%type;
490 maxActSubDirLen number := 23; -- Matches f_act_subdir
491 l number;
492 b number;
493 i number;
494 L_DATE_FMT constant varchar2(8) := 'MM/DD/RR';
495 begin
496 v_printed_un_applied := 'N';
497 if ( p_report_format = 'TEXT' )
498 then
499 maxLineLen := 132;
500 else
501 maxLineLen := 500;
502 end if;
503
504 -- Do some setup for output later. We use this in two places, so define it
505 -- once and use twice
506 -- Note that we can't use a DECODE statement on boolean variables.
507
508 if (p_limit_to_forms)
509 then
510 query_str1 := 'Y';
511 else
512 query_str1 := 'N';
513 end if;
514
515 if (p_limit_to_node)
516 then
517 query_str1 := query_str1||'/Y';
518 else
519 query_str1 := query_str1||'/N';
520 end if;
521
522 if (p_limit_to_web)
523 then
524 query_str1 := query_str1||'/Y';
525 else
526 query_str1 := query_str1||'/N';
527 end if;
528
529 if (p_limit_to_admin)
530 then
531 query_str1 := query_str1||'/Y';
532 else
533 query_str1 := query_str1||'/N';
534 end if;
535
536 v_line_num:=printBeginXML(v_line_num,TRUE);
537
538 v_line_num:=printBeginSearch(v_line_num);
539
540
541 query_str1:='Select '''||query_str1||''' LIMITTOFORMS from dual';
542
543 queryCtx := DBMS_XMLQuery.newContext(query_str1);
544
545 result := DBMS_XMLQuery.getXML(queryCtx);
546
547 DBMS_XMLQuery.closeContext(queryCtx);
548
549 v_line_num:=printClobOut(result,v_line_num);
550
551
552 if (p_limit_to_db_drvrs)
553 then
554 query_str1 := 'Y';
555 else
556 query_str1 := 'N';
557 end if;
558
559 query_str1:='Select '''||query_str1||''' LIMITTOPATCHES from dual';
560
561 queryCtx := DBMS_XMLQuery.newContext(query_str1);
562
563 result := DBMS_XMLQuery.getXML(queryCtx);
564
565 DBMS_XMLQuery.closeContext(queryCtx);
566
567 v_line_num:=printClobOut(result,v_line_num);
568
569
570
571 query_str1:='Select '''||NVL(p_bug_num,'ALL')||''' BUGNUMBER from dual';
572
573 queryCtx := DBMS_XMLQuery.newContext(query_str1);
574
575
576 result := DBMS_XMLQuery.getXML(queryCtx);
577
578 DBMS_XMLQuery.closeContext(queryCtx);
579
580 v_line_num:=printClobOut(result,v_line_num);
581
582 query_str1:='Select '''||NVL(p_bug_prod_abbr,'ALL')||''' BUG_PRODUCT from dual';
583
584 queryCtx := DBMS_XMLQuery.newContext(query_str1);
585
586
587 result := DBMS_XMLQuery.getXML(queryCtx);
588
589 DBMS_XMLQuery.closeContext(queryCtx);
590
591 v_line_num:=printClobOut(result,v_line_num);
592
593
594
595 query_str1:='Select '''||NVL(p_end_dt_from_v,'ALL')||''' END_DATE_FROM from dual';
596
597 queryCtx := DBMS_XMLQuery.newContext(query_str1);
598
599
600 result := DBMS_XMLQuery.getXML(queryCtx);
601
602 DBMS_XMLQuery.closeContext(queryCtx);
603
604 v_line_num:=printClobOut(result,v_line_num);
605
606 query_str1:='Select '''||NVL(p_end_dt_to_v,'ALL')||''' END_DATE_TO from dual';
607
608 queryCtx := DBMS_XMLQuery.newContext(query_str1);
609
610 result := DBMS_XMLQuery.getXML(queryCtx);
611
612 DBMS_XMLQuery.closeContext(queryCtx);
613
614 v_line_num:=printClobOut(result,v_line_num);
615
616
617 query_str1:='Select '''||NVL(p_patch_nm,'ALL')||''' PATCH_NAME from dual';
618
619 queryCtx := DBMS_XMLQuery.newContext(query_str1);
620
621 result := DBMS_XMLQuery.getXML(queryCtx);
622
623 DBMS_XMLQuery.closeContext(queryCtx);
624
625 v_line_num:=printClobOut(result,v_line_num);
626
627
628 query_str1:='Select '''||NVL(p_patch_type,'ALL')||''' PATCH_TYPE from dual';
629
630 queryCtx := DBMS_XMLQuery.newContext(query_str1);
631
632 result := DBMS_XMLQuery.getXML(queryCtx);
633
634 DBMS_XMLQuery.closeContext(queryCtx);
635
639 query_str1:='Select '''||NVL(p_level,'ALL')||''' PATCH_LEVEL from dual';
636 v_line_num:=printClobOut(result,v_line_num);
637
638
640
641 queryCtx := DBMS_XMLQuery.newContext(query_str1);
642
643 result := DBMS_XMLQuery.getXML(queryCtx);
644
645 DBMS_XMLQuery.closeContext(queryCtx);
646
647 v_line_num:=printClobOut(result,v_line_num);
648
649 query_str1:='Select '''||NVL(p_lang,'ALL')||''' LANGUAGE from dual';
650
651 queryCtx := DBMS_XMLQuery.newContext(query_str1);
652
653 result := DBMS_XMLQuery.getXML(queryCtx);
654
655 DBMS_XMLQuery.closeContext(queryCtx);
656
657 v_line_num:=printClobOut(result,v_line_num);
658
659
660 query_str1:='Select '''||NVL(p_appltop_nm,'ALL')||''' APPL_TOP from dual';
661
662 queryCtx := DBMS_XMLQuery.newContext(query_str1);
663
664 result := DBMS_XMLQuery.getXML(queryCtx);
665
666 DBMS_XMLQuery.closeContext(queryCtx);
667
668 v_line_num:=printClobOut(result,v_line_num);
669
670 v_line_num:=printEndSearch(v_line_num);
671
672
673 --@@TODO: substr everything based on max allowed lengths in report output
674
675 -- Build up the following query:
676
677 -- select pr.patch_run_id, at.name, l.language, aap.patch_name,
678 -- ad_patch_hist_reps.get_level_if_one(aap.applied_patch_id) lvl,
679 -- ad_patch_hist_reps.get_concat_minipks(pd.patch_driver_id) minipks,
680 -- pr.start_date, pr.end_date, pd.driver_file_name,
681 -- decode(pd.driver_type_c_flag, 'Y', 'Copy', null)||
682 -- decode(pd.driver_type_d_flag, 'Y',
683 -- decode(pd.driver_type_c_flag, ''Y'', '',DB'', ''DB''),null)||
684 -- decode(pd.driver_type_g_flag, ''Y'',
685 -- decode(pd.driver_type_c_flag, ''Y'', ',Generate',
686 -- decode(pd.driver_type_d_flag, 'Y',
687 -- ,Generate', 'Generate')), null) driver_type,
688 -- from ad_applied_patches aap, ad_patch_driver_langs l,
689 -- ad_patch_drivers pd, ad_appl_tops at, ad_patch_runs pr
690 -- where pr.appl_top_id = at.appl_top_id
691 -- and pr.patch_driver_id = pd.patch_driver_id
692 -- and pd.applied_patch_id = aap.applied_patch_id
693 -- and pd.patch_driver_id = l.patch_driver_id
694 -- order by at.name, l.language, pr.end_date desc
695
696 -- query depth is 1 2 or 3 then
697
698 if (p_query_depth > 0)
699 then
700 l_from_bug := null;
701 l_where_bug := null;
702 l_where2 := null;
703 l_where3 := null;
704 l_select1 := 'SELECT '||
705 'pr.patch_run_id, '||
706 'at.name, '||
707 'l.language, '||
708 'aap.patch_name, '||
709 'ad_patch_hist_reps.get_level_if_one('||
710 'aap.applied_patch_id) lvl, '||
711 'ad_patch_hist_reps.get_concat_minipks('||
712 'pd.patch_driver_id) minipks, '||
713 'ad_patch_hist_reps.get_concat_mergepatches('||
714 'pd.patch_driver_id) comprptch, '||
715 'to_char(pr.start_date,''mm/dd/rr hh24:mi'') start_date,'||
716 'to_char(pr.end_date,''mm/dd/rr hh24:mi'') end_date ,'||
717 'pd.driver_file_name,';
718
719 -- dbms_output.put_line(l_select1);
720
721 l_select2 := 'decode(pd.driver_type_c_flag,'||'''Y'',''Copy'','||
722 'null)|| '||'decode(pd.driver_type_d_flag,'||
723 '''Y'',decode(pd.driver_type_c_flag,'||
724 '''Y'',''DB'',''DB''),';
725
726 l_select3 := 'null)|| '||'decode(pd.driver_type_g_flag,'||
727 '''Y'',decode(pd.driver_type_c_flag,'||
728 '''Y'',''Generate'','||
729 'decode(pd.driver_type_d_flag,'||
730 '''Y'',''Generate'','||
731 '''Generate'')),'||'null) driver_type,';
732
733 l_select4 := 'pr.patch_action_options,pr.patch_top,pd.platform,';
734
735 l_select5 := ' decode (pr.server_type_admin_flag,''Y'',''Admin'',null)||'||
736 ' decode (pr.server_type_forms_flag,''Y'',''Forms'',null)||'||
737 ' decode (pr.server_type_node_flag ,''Y'',''Node'',null)||'||
738 ' decode (pr.server_type_web_flag,''Y'',''Web'',null) '||
739 'servertype';
740
741 l_from1 := ' FROM ad_applied_patches aap,ad_patch_driver_langs l,'||
742 'ad_patch_drivers pd,ad_appl_tops at,ad_patch_runs pr ';
743
744 l_where1 := ' WHERE pr.appl_top_id = at.appl_top_id '||
745 'and pr.patch_driver_id = pd.patch_driver_id '||
746 'and pd.applied_patch_id = aap.applied_patch_id '||
747 'and pd.patch_driver_id = l.patch_driver_id ';
748
749 l_order_by1:=' ORDER BY '||'at.name,l.language,pr.end_date desc';
750
751 -- if appltop is specified
752 if (p_appltop_nm IS NOT NULL)
753 then
754 l_where2 := l_where2 || ' and at.name ='''||p_appltop_nm||'''';
755 end if;
756
757 --Given a Target/Server Type
758 if (p_limit_to_forms)
759 then
760 l_where2 := l_where2||' and pr.server_type_forms_flag = ''Y''';
761 end if;
762
763 if (p_limit_to_node)
764 then
765 l_where2 := l_where2||' and pr.server_type_node_flag = ''Y''';
769 then
766 end if;
767
768 if (p_limit_to_web)
770 l_where2 := l_where2||' and pr.server_type_web_flag = ''Y''';
771 end if;
772
773 if (p_limit_to_admin)
774 then
775 l_where2 := l_where2||' and pr.server_type_admin_flag = ''Y''';
776 end if;
777
778 -- if language is specified
779 if (p_lang IS NOT NULL)
780 then
781 l_where2 := l_where2||' and l.language = '''||p_lang||'''';
782 end if;
783
784 -- if patch_name is specified then
785 if (p_patch_nm IS NOT NULL)
786 then
787 l_where2 := l_where2||' and upper(aap.patch_name) = '''||p_patch_nm||'''';
788 end if;
789
790 -- if patch-type is specified but not patch-level
791
792 if (p_patch_type IS NOT NULL) and
793 (p_level IS NOT NULL)
794 then
795 l_where2 := l_where2||' and aap.patch_type = '''||p_patch_type||'''';
796 end if;
797
798 -- Given the PatchLevel
799 if (p_level IS NOT NULL)
800 then
801 if (p_patch_type = 'MAINTENANCE-PACK')
802 then
803 --Given the PatchLevel and PatchType is 'MAINTENANCE-PACK'
804 l_where2 := l_where2||' and aap.maint_pack_level = '''||
805 p_level||'''';
806 elsif (p_patch_type = 'PATCH-SET')
807 then
808 --Given the PatchLevel and PatchType is 'PATCH-SET'
809 l_where3 := l_where3||' and aap.applied_patch_id in ('||
810 'select pd2.applied_patch_id '||
811 'from ad_patch_drivers pd2 '||
812 'where pd2.patch_driver_id in ('||
813 'select mi.patch_driver_id '||
814 'from ad_patch_driver_minipks mi '||
815 'where mi.patch_level = '''||p_level||'''';
816 l_where3:=l_where3||'))';
817 else
818 null;
819 --@@ raise unexpected error
820 end if;
821 end if;
822
823 --Given the ":end_date_from" component of Date Range
824 if (p_end_dt_from_v IS NOT NULL)
825 then
826 l_where2 := l_where2||' and pr.end_date >= to_date('''||p_end_dt_from_v||
827 ''', ''';
828 l_where2 := l_where2||L_DATE_FMT||''')';
829 end if;
830
831 --Given the ":end_date_to" component of Date Range
832 if (p_end_dt_to_v IS NOT NULL)
833 then
834 l_where2 := l_where2||
835 ' and pr.end_date < trunc(to_date('''||p_end_dt_to_v||''',''';
836 l_where2 := l_where2||L_DATE_FMT||''') + 1)';
837 end if;
838
839 --If "Only Patches that change the database" is YES
840 if (p_limit_to_db_drvrs)
841 then
842 l_where2 := l_where2 || ' and pd.driver_type_d_flag = ''Y''';
843 end if;
844
845 --If bug# and/or bug-product is given, then additional tables need to be
846 --joined
847 if (p_bug_num IS NOT NULL) or
848 (p_bug_prod_abbr IS NOT NULL)
849 then
850 l_from_bug := l_from_bug ||',ad_patch_run_bugs prb, ad_bugs b';
851 l_where_bug := l_where_bug||' and b.bug_id = prb.bug_id '||
852 ' and prb.patch_run_id = pr.patch_run_id ';
853
854 if (p_bug_num IS NOT NULL)
855 then
856 l_where_bug := l_where_bug||' and b.bug_number ='''||p_bug_num||'''';
857 end if;
858
859 if (p_bug_prod_abbr IS NOT NULL)
860 then
861 l_where_bug := l_where_bug||' and upper(prb.application_short_name) '||
862 '= upper('''||p_bug_prod_abbr||''')';
863 end if;
864 end if;
865
866 query_str := nvl(l_select1,' ') ||nvl(l_select2,' ') ||nvl(l_select3,' ')||
867 nvl(l_select4,' ') ||nvl(l_select5,' ') ||nvl(l_from1 ,' ')||
868 nvl(l_from_bug,' ')||nvl(l_where1 ,' ') ||nvl(l_where2 ,' ')||
869 nvl(l_where3 ,' ')||nvl(l_where_bug,' ')||nvl(l_order_by1,' ');
870
871 --debug code
872 --v_line_num := writeQueryToDb(v_line_num,NULL,query_str);
873 begin
874
875
876 OPEN cpatches FOR query_str ;
877
878 FETCH cpatches
879 INTO v_patch_run_id,v_name ,v_language,v_patch_name,v_lvl,
880 v_minipks,v_comprptch ,v_start_date,v_end_date,v_driver_file_name,
881 v_driver_type ,v_patch_action_options ,v_patch_top,
882 v_platform ,v_servertype;
883 exception when others then
884 --v_line_num := writeQueryToDb(v_line_num,SQLERRM,query_str);
885 return;
886 end;
887
888
889 while cpatches%FOUND LOOP
890 if (old_patch_run_id = v_patch_run_id)
891 then
892 goto fetch_next;
893 end if;
894
895 v_line_num:=printBeginPatch(v_line_num);
896
897 old_patch_run_id := v_patch_run_id;
898
899 query_str1:= 'select ''' ||
900 v_name ||''' APPL_TOP_NAME ,''' ||
901 v_language ||''' LANGUAGE ,''' ||
902 v_patch_name ||''' PATCH_NAME ,''' ||
903 v_lvl ||''' PATCH_LEVEL , ''' ||
904 v_minipks ||''' MINIPACKS ,''' ||
908 v_driver_file_name ||''' DRIVER_NAME ,'''||
905 v_comprptch ||''' COMPRISING_PATCH,''' ||
906 v_start_date ||''' START_DATE , ''' ||
907 v_end_date ||''' END_DATE , ''' ||
909 v_driver_type ||''' DRIVER_TYPE , ''' ||
910 v_patch_action_options ||''' PATCH_OPTIONS , '''||
911 v_patch_top ||''' PATCH_TOP ,''' ||
912 v_platform ||''' PATCH_PLATFORM , ''' ||
913 v_servertype ||''' SERVERTYPE from dual';
914
915
916 queryCtx := DBMS_XMLQuery.newContext(query_str1);
917
918
919 result := DBMS_XMLQuery.getXML(queryCtx);
920
921 DBMS_XMLQuery.closeContext(queryCtx);
922 v_line_num:=printClobOut(result,v_line_num);
923
924
925 if (p_query_depth > 1)
926 then
927 query_str:=
928 'SELECT
929 bgs.BUG_NUMBER,prb.APPLICATION_SHORT_NAME,
930 decode(APPLIED_FLAG,''Y'',''Yes'',''N'',''No'',''No'') APPLIED_FLAG,
931 REASON_NOT_APPLIED, prb.PATCH_RUN_BUG_ID
932 FROM ad_patch_run_bugs prb, ad_bugs bgs
933 WHERE bgs.bug_id=prb.bug_id and prb.PATCH_RUN_ID='||v_patch_run_id;
934
935 -- if the bug number has been passed as arguments then
936
937 if (p_bug_num IS NOT NULL)
938 then
939 query_str := query_str||' and bgs.bug_number='''||p_bug_num||'''' ;
940 end if;
941
942 -- if the product has been passed as arguments then
943
944 if (p_bug_prod_abbr IS NOT NULL)
945 then
946 query_str := query_str||' and prb.application_short_name='''||
947 p_bug_prod_abbr||'''' ;
948 end if;
949
950 query_str := query_str||' order by decode(APPLIED_FLAG,''N'',''Z'','||
951 'APPLIED_FLAG),bgs.bug_number';
952
953
954 -- debug code
955 -- v_line_num := writeQueryToDb(v_line_num,NULL,query_str);
956
957 -- bugs loop starts here
958 begin
959 OPEN cbugs FOR query_str;
960
961 FETCH cbugs into
962 v_bug_number, v_product, v_applied, v_reason, v_patch_run_bug_id;
963
964
965 exception when others then
966 -- v_line_num := writeQueryToDb(v_line_num,SQLERRM,query_str);
967 return;
968 end;
969
970 while cbugs%found loop
971 query_str1:='select '''||
972 v_bug_number||''' BUGNUMBER ,'''||
973 v_product||''' PRODUCT ,'''||
974 v_applied||''' APPLIED_FLAG ,'''||
975 v_reason ||''' REASON_NOT_APPLIED from dual';
976
977 v_line_num:=printBeginBug(v_line_num);
978 queryCtx := DBMS_XMLQuery.newContext(query_str1);
979
980 result := DBMS_XMLQuery.getXML(queryCtx);
981
982 DBMS_XMLQuery.closeContext(queryCtx);
983
984 v_line_num:=printClobOut(result,v_line_num);
985
986
987
988 -- action loop starts here
989 if (p_query_depth > 2) and (v_applied ='Yes')
990 then
991 query_str :=
992 'SELECT cact.ACTION_CODE Action ,
993 prba.EXECUTED_FLAG Executed ,
994 files.APP_SHORT_NAME Product ,
995 files.SUBDIR Directory ,
996 files.FILENAME Filename ,
997 nvl(pver.VERSION,''.'') Patch_Version ,
998 nvl(pver.TRANSLATION_LEVEL,0) PTrans_Level ,
999 nvl(over.VERSION,''.'') Onsite_Version,
1000 nvl(over.TRANSLATION_LEVEL,0) OTrans_Level ,
1001 nvl(dver.VERSION,''.'') DB_Version ,
1002 nvl(dver.TRANSLATION_LEVEL,0) DTrans_Level ,
1003 nvl(cact.ACTION_PHASE,''.'') Phase ,
1004 nvl(cact.ACTION_ARGUMENTS,''.'') Arguments ,
1005 nvl(cact.ACTION_WHAT_SQL_EXEC,''.'') Modifier
1006 FROM ad_files files, ad_bugs bugs, ad_patch_common_actions cact,
1007 ad_file_versions pver, ad_file_versions over,
1008 ad_file_versions dver, ad_patch_run_bug_actions prba,
1009 ad_patch_run_bugs pbug
1010 WHERE pbug.PATCH_RUN_BUG_ID = '||v_patch_run_bug_id||
1011 ' and pbug.PATCH_RUN_BUG_ID = prba.PATCH_RUN_BUG_ID
1012 and cact.COMMON_ACTION_ID = prba.COMMON_ACTION_ID
1013 and pver.FILE_VERSION_ID(+) = prba.PATCH_FILE_VERSION_ID
1014 and over.FILE_VERSION_ID(+) = prba.ONSITE_FILE_VERSION_ID
1015 and dver.FILE_VERSION_ID(+) = prba.ONSITE_PKG_VERSION_IN_DB_ID
1016 and bugs.BUG_ID = pbug.BUG_ID
1017 and files.FILE_ID = prba.FILE_ID
1018 ORDER BY '||
1019 -- The below ORDER-BY decode to be in sync with the sequence
1020 -- of calls to adpaex() in adpmrp().
1021 'decode(cact.ACTION_CODE,''libout'',1,''copy'',2,
1025 '''sql'',8,''exec'',8,''exectier'',8,''genfpll'',9,
1022 ''forcecopy'',3,''libin'',4,''makedir'',5,''link'',6,
1023 ''jcopy'',7,'||
1024 -- sql,exec,exectier intentionally kept at same level
1026 ''genmenu'',10,''genform'',11,''genrpll'',12,''genrep'',13,
1027 ''gengpll'',14,''genogd'',15,''genmesg'',16,''genwfmsg'',17,50),
1028 cact.numeric_phase,cact.numeric_sub_phase,
1029 files.FILENAME,cact.ACTION_ARGUMENTS';
1030
1031 -- debug code
1032 --v_line_num := writeQueryToDb(v_line_num,NULL,query_str);
1033 begin
1034 OPEN cactions FOR query_str;
1035
1036 FETCH cactions
1037 INTO v_act_actioncode,v_act_executed ,v_act_product,v_act_subdir,
1038 v_act_filename ,v_act_patch_ver ,v_act_patch_trlev ,
1039 v_act_onsitever ,v_act_onsite_trlev ,v_act_pkgverdb ,
1040 v_act_pkgdb_trlev ,v_act_phase ,v_act_args ,v_act_Modfr;
1041
1042 exception when others then
1043 --v_line_num := writeQueryToDb(v_line_num,SQLERRM,query_str);
1044 return;
1045 end;
1046
1047
1048 while cactions%found loop
1049
1050 -- Bug 3396387 : When run at depth 3, this produces
1051 -- Null pointer eception in java. The reason is that
1052 -- the action arguments were being passed to the xml
1053 -- without converting single quote to a double quote
1054 -- (quote with the escape character). For example,
1055 -- 'FND FNDCPBWV SYSADMIN 'System Administrator' SYSADMIN'
1056 -- wont work. Its should be passed as
1057 -- FND FNDCPBWV SYSADMIN ''System Administrator'' SYSADMIN
1058 -- The below for loop checks 'v_act_args' and if there are single
1059 -- quote ('), it converts to <escape_character><quote> ('').
1060 -- Similarly, convert double quotes (") to <escape char>" ('").
1061 -- sgadag
1062
1063 tmpQry := '';
1064 while instr(v_act_args,'''') > 0 loop
1065 tmpStr := substr(v_act_args,1,instr(v_act_args,''''));
1066 tmpQry := tmpQry || tmpStr || '''';
1067 v_act_args := substr(v_act_args,instr(v_act_args,'''')+1);
1068 end loop;
1069 v_act_args := tmpQry || v_act_args;
1070
1071
1072 tmpQry := '';
1073 while instr(v_act_args,'"') > 0 loop
1074 tmpStr := '';
1075 if (instr(v_act_args,'"') > 1) then
1076 tmpStr := substr(v_act_args,1,instr(v_act_args,'"')-1);
1077 end if;
1078 tmpQry := tmpQry || tmpStr || '''''"';
1079 v_act_args := substr(v_act_args,instr(v_act_args,'"')+1);
1080 end loop;
1081 v_act_args := tmpQry || v_act_args;
1082
1083
1084
1085
1086 query_str1:='select '''||v_act_actioncode||''' ACTION, '''||
1087 v_act_executed||''' EXECUTED, '''||
1088 v_act_product||''' PRODUCT, '''||
1089 v_act_subdir||''' DIRECTORY, '''||
1090 v_act_filename||''' FILENAME, '''||
1091 v_act_patch_ver||''' PATCH_VERSION, '''||
1092 v_act_patch_trlev||''' PTRANS_LEVEL, '''||
1093 v_act_onsitever||''' ONSITE_VERSION, '''||
1094 v_act_onsite_trlev||''' OTRANS_LEVEL, '''||
1095 v_act_pkgverdb||''' PACKAGE_VERSION, '''||
1096 v_act_pkgdb_trlev||''' PKG_TRANLEVEL, '''||
1097 v_act_phase||''' ACTION_PHASE, '''||
1098 v_act_args||''' ACTION_ARGS, '''||
1099 v_act_Modfr||''' ACTION_MODIFIER from dual';
1100
1101 v_line_num:=printBeginAction(v_line_num);
1102
1103 queryCtx := DBMS_XMLQuery.newContext(query_str1);
1104
1105 result := DBMS_XMLQuery.getXML(queryCtx);
1106
1107 DBMS_XMLQuery.closeContext(queryCtx);
1108
1109 v_line_num:=printClobOut(result,v_line_num);
1110
1111 v_line_num:=printEndAction(v_line_num);
1112
1113 FETCH cactions
1114 INTO v_act_actioncode ,v_act_executed ,v_act_product ,
1115 v_act_subdir ,v_act_filename ,v_act_patch_ver ,
1116 v_act_patch_trlev,v_act_onsitever ,v_act_onsite_trlev ,
1117 v_act_pkgverdb ,v_act_pkgdb_trlev,v_act_phase ,
1118 v_act_args, v_act_Modfr ;
1119
1120 end loop; -- End loop if cactions%found #2
1121 CLOSE cactions;
1122 end if; -- if (p_query_depth > 2) and (v_applied ='Yes')
1123
1124 FETCH cbugs
1125 INTO v_bug_number,v_product,v_applied,v_reason,v_patch_run_bug_id;
1126
1127 v_line_num:=printEndBug(v_line_num);
1128
1129 end loop; -- while cbugs%found loop
1130 CLOSE cbugs;
1131 end if; -- if (p_query_depth > 1)
1132
1133 -- bugs loop ends here
1134 <<fetch_next>>
1135
1136
1137 FETCH cpatches
1138 INTO v_patch_run_id ,v_name ,v_language ,v_patch_name,
1139 v_lvl ,v_minipks ,v_comprptch,v_start_date,
1140 v_end_date ,v_driver_file_name ,v_driver_type,
1141 v_patch_action_options,v_patch_top ,v_platform ,
1142 v_servertype;
1143
1144 v_line_num:=printEndPatch(v_line_num);
1145
1146 end loop; -- cpatches%FOUND LOOP
1147 CLOSE cpatches;
1148 --
1149 v_line_num:=printEndXML(v_line_num);
1150 --
1151 end if; -- if query_depth is 1 ,2 or 3
1152
1153 exception
1154 when no_data_found
1155 then
1156 null;
1157 end populate_search_results;
1158 --
1159 --
1160 --
1161 end ad_patch_hist_reps;