1 package body ad_pa_validate_criteriaset as
2 /* $Header: adpavacb.pls 120.3 2005/10/06 05:31:19 tjohn ship $ */
3
4 Procedure validate_criteriaset(p_advisor_criteria_id varchar2 )
5 IS
6
7 l_count number :=0;
8
9 BEGIN
10
11 begin
12 select count(1) into l_count from AD_PA_CRITERIA
13 where advisor_criteria_id = LTRIM(RTRIM((p_advisor_criteria_id)));
14 --where upper(advisor_criteria_id) = LTRIM(RTRIM(UPPER(p_advisor_criteria_id)));
15 if l_count > 0 then
16 -- FND_MESSAGE.CLEAR;
17 -- FND_MESSAGE.SET_NAME(application=>'AD', name=>'AD_DUP_AC_MSG');
18 raise_application_error(-20101, 'Duplicate Advisor Criteria Name. Please click on Browser Back arrow and enter a different name. ');
19
20 end if;
21 end;
22 end validate_criteriaSet;
23
24
25 /* This function returns comma separated patches in column Merged_patches in PatchSummary page . Bug # 2813894 -KKSINGH */
26
27 function get_concat_mergepatches(p_ptch_drvr_id number)
28 return varchar2 is
29
30 l_concat_bugNumber varchar2(4096);
31 l_first_iter boolean; -- first iteration flag
32 l_rem_space number :=0; -- remaining space
33 l_len_till_now number :=0; -- length of l_concat_bugid
34
35 cursor c1(p_patch_driver_id number) is
36 --tjohn - 14-SEP-2005 - Bug# 4574467 Changed cursor c1 to have join between two tables and avoid the IN caluse for improved performance.
37 SELECT
38 bug_number
39 FROM
40 ad_bugs ab,
41 ad_comprising_patches acp
42 WHERE
43 ab.bug_id = acp.bug_id and
44 acp.patch_driver_id = p_patch_driver_id;
45
46 begin
47 l_concat_bugNumber := null;
48 l_first_iter := TRUE;
49
50
51 for c1_rec in c1(p_ptch_drvr_id) loop
52 if (l_first_iter)
53 then
54 l_concat_bugNumber := c1_rec.bug_number;
55 l_first_iter := FALSE;
56 l_len_till_now :=length(l_concat_bugNumber);
57
58 else
59 l_rem_space :=(4096 - l_len_till_now);
60
61 -- 2 spaces must ALWAYS be available whenever we are about
62 -- to make this determination.
63
64 if (l_rem_space > length(c1_rec.bug_number) + 2)
65 then
66 l_concat_bugNumber := l_concat_bugNumber || ', '||
67 c1_rec.bug_number;
68 -- Maintain l_len_till_now (Note: 2 is for the comma and space)
69 l_len_till_now := l_len_till_now + 2 +
70 length(c1_rec.bug_number);
71 else
72 -- not enough space, show error message
73 raise_application_error(-20500,'The total of merged patches exceed the display limit. Contact Oracle Support group.');
74 exit;
75 end if;
76 end if;
77 end loop;
78 return l_concat_bugNumber;
79 end get_concat_mergepatches;
80
81
82 function get_concat_minipks(p_ptch_drvr_id number)
83 return varchar2 is
84
85 l_concat_minipks varchar2(4096); /* intentionally having it 4K to handle
86 the minipacks in Maintenance pack */
87 l_first_iter boolean; -- first iteration flag
88
89 l_rem_space number :=0; -- remaining space
90
91 l_len_till_now number :=0; -- length of l_concat_minipks till now
92
93
94 cursor c1(p_patch_driver_id number) is
95 select patch_level
96 from ad_patch_driver_minipks
97 where patch_driver_id = p_patch_driver_id;
98 begin
99 l_concat_minipks := null;
100 l_first_iter := TRUE;
101
102 for c1_rec in c1(p_ptch_drvr_id) loop
103 if (l_first_iter)
104 then
105 l_concat_minipks := c1_rec.patch_level;
106 l_first_iter := FALSE;
107 l_len_till_now :=length(l_concat_minipks);
108 else
109 l_rem_space :=(4096 - l_len_till_now);
110
111 -- if no space avail, we want to add ", ...". This means that
112 -- 5 spaces must ALWAYS be available whenever we are about
113 -- to make this determination. This implies that we
114 -- always check for len(<patch-level>) + 5, even though we
115 -- we only intend to append <patch-level>.
116
117 if (l_rem_space > length(c1_rec.patch_level) + 5)
118 then
119 l_concat_minipks := l_concat_minipks || ', '||
120 c1_rec.patch_level;
121 -- Maintain l_len_till_now (Note: 2 is for the comma and space)
122 l_len_till_now := l_len_till_now + 2 +
123 length(c1_rec.patch_level);
124 else
125 -- not enough space, just append ", ..." and break the loop
126 l_concat_minipks := l_concat_minipks || ', ...';
127 exit;
128 end if;
129 end if;
130 end loop;
131 return l_concat_minipks;
132 end get_concat_minipks;
133
134
135 -- New function for ActionDetails
136 -- Based on the value returned from this function, the status of Details Image in ActionSummary page will be decided for enable or disable
137 function get_jobtiming_details(p_action_id number, p_program_run_id number, p_session_id number)
138 return number is
139 l_number number := 0;
140
141 BEGIN
142 select count(*) into l_number from
143 ad_program_run_task_jobs prtj,
144 ad_files ldr_f,
145 ad_files f,
146 ad_patch_common_actions pca,
147 ad_patch_run_bug_actions prba
148 where prba.action_id = p_action_id
149 and prba.common_action_id = pca.common_action_id
150 and prba.file_id = f.file_id
151 and pca.loader_data_file_id = ldr_f.file_id (+)
152 and nvl(ldr_f.filename, f.filename) = prtj.job_name
153 and UPPER(nvl(ldr_f.app_short_name, f.app_short_name)) = UPPER(prtj.product)
154 and nvl(prtj.arguments,'NA') = nvl(pca.action_arguments,'NA') -- Bug# (3443373)-KKS
155 and prtj.program_run_id = p_program_run_id
156 and prtj.session_id = p_session_id
157 and prtj.phase_name=pca.action_phase
158 and (pca.ACTION_ARGUMENTS is not null or
159 pca.ACTION_WHAT_SQL_EXEC is not null or
160 pca.ACTION_CHECK_OBJ_USERNAME is not null or
161 pca.ACTION_CHECK_OBJ is not null or
162 pca.ACTION_CHECK_OBJ_PASSWD is not null or
163 prtj.start_time is not null or
164 prtj.restart_time is not null or
165 prtj.end_time is not null or
166 prtj.restart_count is not null or
167 prtj.elapsed_time is not null ) ;
168
169 if l_number = 0 then
170 return 0;
171 else
172 return 1 ;
173 end if;
174 end get_jobtiming_details;
175
176 function get_cs_prod_fam_name(p_product_abbr varchar2) return varchar2 is
177
178 l_concat_prod_fam varchar2(200) := '';
179
180 cursor c1(l_product_abbr varchar2) is
181 select product_name
182 from ad_pm_prod_family_map appfm, ad_pm_product_info appi
183 where appfm.product_family_abbreviation = appi.product_abbreviation
184 and appfm.product_abbreviation = l_product_abbr;
185
186 begin
187
188 for c1_rec in c1(p_product_abbr) loop
189 if(length(l_concat_prod_fam) > 0) then
190 l_concat_prod_fam := l_concat_prod_fam || ', '||
191 c1_rec.product_name;
192 else
193 l_concat_prod_fam := c1_rec.product_name;
194 end if;
195 end loop;
196 return l_concat_prod_fam;
197 end get_cs_prod_fam_name;
198
199 function get_cs_prod_fam_abbr(p_product_abbr varchar2) return varchar2 is
200
201 l_concat_prod_fam varchar2(200) := '';
202
203 cursor c1(l_product_abbr varchar2) is
204 select product_family_abbreviation
205 from ad_pm_prod_family_map appfm
206 where appfm.product_abbreviation = l_product_abbr;
207
208 begin
209
210 for c1_rec in c1(p_product_abbr) loop
211 if(length(l_concat_prod_fam) > 0) then
212 l_concat_prod_fam := l_concat_prod_fam || ', '||
213 c1_rec.product_family_abbreviation;
214 else
215 l_concat_prod_fam := c1_rec.product_family_abbreviation;
216 end if;
217 end loop;
218 return l_concat_prod_fam;
219 end get_cs_prod_fam_abbr;
220
221
222 END ad_pa_validate_criteriaset;
[Home] [Help]