1 PACKAGE BODY AD_PATCH_IMPACT_API AS
2 /* $Header: adpaiab.pls 120.3 2006/04/05 03:28:31 msailoz noship $ */
3 /**
4 The Procedure returns the list of patches recommended in the current request set.
5 i.e., this AD API would query the FND Concurrent Request table to get the request
6 ID of the currently running Request Set and use this request ID to query the
7 AD_PA_ANALYSIS_RUN_BUGS table to get the list of bug numbers recommended.
8 **/
9 PROCEDURE get_recommend_patch_list
10 ( a_rec_patch OUT NOCOPY t_rec_patch )
11 IS
12 CURSOR rec_cur(X_anal_req_id Number) IS
13 select bug_number from
14 ad_pa_analysis_run_bugs where analysis_run_id = X_anal_req_id and
15 (analysis_status in ('READY', 'MISSING') or analysis_status is null);
16
17 req_id NUMBER:= 0;
18 anal_req_id NUMBER:= 0;
19 count1 Number default 1;
20 Rows_Exceeded Exception;
21 BEGIN
22 --Get the request ID of currently running Request Set
23 --fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> request ID of currently before...');
24 select priority_request_id into req_id from fnd_concurrent_requests where
25 request_id = fnd_global.conc_request_id;
26 --fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> request ID of currently After...');
27 --Get the request ID of the AD CP within the Request Set
28
29 SELECT
30 fcr.request_id into anal_req_id
31 FROM
32 fnd_application fa,
33 fnd_concurrent_requests fcr,
34 fnd_concurrent_programs fcp
35 WHERE
36 fcr.priority_request_id = req_id and
37 fcr.program_application_id = fcp.application_id and
38 fcp.application_id = fa.application_id and
39 fcr.concurrent_program_id = fcp.concurrent_program_id and
40 fa.application_short_name = 'AD' and
41 fcp.concurrent_program_name in ('PATCHANALYSIS', 'PAANALYSIS','PADOWNLOADPATCHES','PAANALYZEPATCHES','PARECOMMENDPATCHES' ) ;
42
43 --fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> request ID of currently After Select...');
44 --FOR c in rec_cur(167371) LOOP
45 FOR c in rec_cur(anal_req_id) LOOP
46 a_rec_patch(count1) := c.bug_number;
47 count1 := count1 + 1;
48 --fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> request ID of currently After for...');
49 /* if count1 > 20 then
50 RAISE Rows_Exceeded;
51 END if; */
52 END LOOP;
53
54 END get_recommend_patch_list;
55
56
57 /**
58 The Procedure returns the list of patches recommended in the current request set.
59 i.e., this AD API would query the FND Concurrent Request table to get the request
60 ID of the currently running Request Set and use this request ID to query the
61 AD_PA_ANALYSIS_RUN_BUGS table to get the list of bug numbers/baseline/patchid.
62 **/
63 PROCEDURE get_recommend_patch_list
64 ( p_recomm_patch_tab OUT NOCOPY t_recomm_patch_tab )
65 IS
66
67 CURSOR rec_cur(X_anal_req_id NUMBER) IS
68 SELECT aparb.bug_number, aparb.baseline, app.patch_id
69 FROM ad_pa_analysis_run_bugs aparb,
70 ad_pm_patches app
71 WHERE aparb.analysis_run_id = X_anal_req_id
72 AND aparb.bug_number = app.bug_number
73 AND aparb.baseline = app.baseline
74 AND app.patch_metadata_key = 'DEFAULT'
75 AND (aparb.analysis_status IN ('READY', 'MISSING')
76 OR aparb.analysis_status IS NULL);
77
78 req_id NUMBER := 0;
79 anal_req_id NUMBER := 0;
80 count1 NUMBER DEFAULT 1;
81 l_rec t_recomm_patch_rec;
82
83 BEGIN
84
85 -- Initialize the plsql table
86 p_recomm_patch_tab := t_recomm_patch_tab();
87
88 --Get the request ID of currently running Request Set
89 SELECT priority_request_id
90 INTO req_id
91 FROM fnd_concurrent_requests
92 WHERE request_id = fnd_global.conc_request_id;
93
94 --Get the request ID of the AD CP within the Request Set
95 SELECT
96 fcr.request_id into anal_req_id
97 FROM
98 fnd_application fa,
99 fnd_concurrent_requests fcr,
100 fnd_concurrent_programs fcp
101 WHERE
102 fcr.priority_request_id = req_id
103 AND fcr.program_application_id = fcp.application_id
104 AND fcp.application_id = fa.application_id
105 AND fcr.concurrent_program_id = fcp.concurrent_program_id
106 AND fa.application_short_name = 'AD'
107 AND fcp.concurrent_program_name in
108 ('PATCHANALYSIS', 'PAANALYSIS','PADOWNLOADPATCHES','PAANALYZEPATCHES','PARECOMMENDPATCHES' ) ;
109
110 -- For each row store the value in plsql table
111 FOR c IN rec_cur(anal_req_id) LOOP
112
113 l_rec.bug_number := c.bug_number;
114 l_rec.baseline := c.baseline;
115 l_rec.patch_id := c.patch_id;
116
117 p_recomm_patch_tab.extend;
118 p_recomm_patch_tab(count1) := l_rec;
119 count1 := count1 + 1;
120 END LOOP;
121
122 /** For testing purpose
123 IF(p_recomm_patch_tab.COUNT > 0) THEN
124 FOR c IN 1..p_recomm_patch_tab.COUNT LOOP
125 DBMS_OUTPUT.PUT_LINE (p_recomm_patch_tab(c).bug_number ||' '||
126 p_recomm_patch_tab(c).baseline || ' '||
127 p_recomm_patch_tab(c).patch_id );
128 END LOOP;
129 END IF;
130 **/
131
132 END get_recommend_patch_list;
133
134
135 /**
136 This API will return to PIA the Global Snapshot ID.
137 **/
138 PROCEDURE get_global_snapshot_id (snap_id OUT NOCOPY Number)
139 IS
140 BEGIN
141 SELECT
142 snp.snapshot_id into snap_id
143 FROM
144 ad_snapshots snp,
145 ad_appl_tops aat,
146 fnd_product_groups fpg
147 WHERE
148 snp.snapshot_type = 'G' and
149 snp.snapshot_name = 'GLOBAL_VIEW' and
150 snp.appl_top_id = aat.appl_top_id and
151 aat.applications_system_name = fpg.applications_system_name and
152 fpg.product_group_id = 1;
153
154 END get_global_snapshot_id ;
155
156
157 /**
158 PIA CPs would call this PL/SQL API that returns the list of
159 pre-req'ed patches that have not been applied for each recommended patch.
160 API input: recommended patch bug number (obtained from the 1st API)
161 API output: list of pre-req's of this recommended patch that have not been
162 applied to the system.
163 The Function returns 1 in case of error
164 **/
165
166 PROCEDURE get_prereq_list
167 (
168 bug_number_val IN Number,
169 a_prereq_patch OUT NOCOPY t_prereq_patch
170 )
171 IS
172 req_id NUMBER := 0;
173 anal_req_id NUMBER := 0;
174 count1 Number default 1;
175 Rows_Exceeded Exception;
176
177 CURSOR prereq_cur(X_anal_req_id Number , X_bug_number Number) IS
178 SELECT
179 distinct(prereq_bug_number) prereq_bug_num ,
180 prereq_bug_order
181 FROM
182 ad_pa_anal_bug_deps
183 WHERE
184 analysis_run_id = X_anal_req_id and
185 bug_number = X_bug_number
186 order by prereq_bug_order;
187
188 BEGIN
189 --Get the request ID of currently running Request Set:
190
191 SELECT
192 priority_request_id INTO req_id
193 FROM
194 fnd_concurrent_requests
195 WHERE
196 request_id = fnd_global.conc_request_id;
197
198 --Get the request ID of the AD CP within the Request Set
199 SELECT
200 fcr.request_id INTO anal_req_id
201 FROM
202 fnd_application fa,
203 fnd_concurrent_requests fcr,
204 fnd_concurrent_programs fcp
205 WHERE
206 fcr.priority_request_id = req_id and
207 fcr.program_application_id = fcp.application_id and
208 fcp.application_id = fa.application_id and
209 fcr.concurrent_program_id = fcp.concurrent_program_id and
210 fa.application_short_name = 'AD' and
211 fcp.concurrent_program_name in
212 ('PATCHANALYSIS', 'PAANALYSIS','PADOWNLOADPATCHES','PAANALYZEPATCHES','PARECOMMENDPATCHES' ) ;
213
214 FOR c in prereq_cur(anal_req_id,bug_number_val) LOOP
215 a_prereq_patch(count1) := c.prereq_bug_num;
216 count1 := count1 + 1;
217 /* if count1 > 20 then
218 RAISE Rows_Exceeded;
219 END if; */
220 END LOOP;
221
222 END get_prereq_list;
223
224 /**
225 PIA CPs would call this PL/SQL API that returns the list of
226 pre-req'ed patches that have not been applied for each recommended patch
227 for a particular request set.
228 API input: request id (corresponding to PADOWNLOADPATCHES, PAANALYZEPATCHES, PAMERGEPATCHES)
229 API input: recommended patch bug number (obtained from the 1st API)
230 API output: list of pre-req's of this recommended patch that have not been
231 applied to the system.
232 The Function returns 1 in case of error
233 **/
234
235 PROCEDURE get_prereq_list
236 (
237 pRequestId IN Number,
238 pBugNumber IN Number,
239 pPrereqPatches OUT NOCOPY t_prereq_patch
240 )
241 IS
242 count1 Number default 1;
243
244 CURSOR prereq_cur(X_anal_req_id Number , X_bug_number Number) IS
245 SELECT
246 distinct(prereq_bug_number) prereq_bug_num ,
247 prereq_bug_order
248 FROM ad_pa_anal_bug_deps
249 WHERE analysis_run_id = X_anal_req_id AND
250 bug_number = X_bug_number
251 ORDER BY prereq_bug_order;
252
253 BEGIN
254
255 FOR c in prereq_cur(pRequestId, pBugNumber) LOOP
256 pPrereqPatches(count1) := c.prereq_bug_num;
257 -- dbms_output.put_line(c.prereq_bug_num);
258 count1 := count1 + 1;
259 END LOOP;
260
261 END get_prereq_list;
262
263 END AD_PATCH_IMPACT_API;
264
[Home] [Help]