1 PACKAGE BODY BIS_RSG_PUB_APIS_PKG AS
2 /* $Header: BISRSPAB.pls 120.2 2005/12/07 08:53:38 amitgupt noship $ */
3 /*
4 List of error codes return by the APIS
5 BIS_RSG_NO_RS_FOUND - In case we don't have any data for this request set in our table/wrong req id is given
6 BIS_RSG_SUCCESS - api is successful
7 BIS_RSG_ERR_UNEXPECTED - some unexpected error occurred
8 BIS_RSG_RS_NO_REF_MODE - in case the the refresh mode option is nulll for request set
9 - like in case of Gather Statistics request set
10 BIS_RSG_NO_RS_STANDALONE - There is no request set associated with the request id. This is a standalone request.
11 BIS_RSG_NO_RSDATA_FOUND - There is no data for the request set.
12 */
13 Function Get_RS_Name (p_root_request_id in number, errbuf out NOCOPY varchar2,
14 errcode out NOCOPY varchar2) return varchar2 IS
15 CURSOR CV (req_id in number)IS
16 select request_set_name from fnd_request_sets where request_set_id= req_id;
17
18 CURSOR CV_ROOT(req_id in number) IS
19 select
20 req.argument2
21 from
22 fnd_concurrent_requests req
23 where
24 req.request_id = req_id and
25 req_id=PRIORITY_REQUEST_ID
26 and has_sub_request = 'Y';
27
28 rs_name varchar2(30);
29 rset_id varchar2(20);
30 nrset_id number;
31 BEGIN
32 rs_name := null;
33
34 open cv_root( p_root_request_id);
35 fetch cv_root into rset_id;
36 if(CV_ROOT%NOTFOUND or rset_id is null) THEN
37 fnd_message.set_name('BIS','BIS_RSG_INVALID_ROOT_ID');
38 fnd_message.set_token('RSID',TO_CHAR(p_root_request_id));
39 errbuf := FND_MESSAGE.GET;
40 errcode := 'BIS_RSG_INVALID_ROOT_ID';
41 close cv_root;
42 return null;
43 else
44 BEGIN
45 nrset_id := to_number(rset_id);
46 EXCEPTION
47 WHEN OTHERS THEN
48 fnd_message.set_name('BIS','BIS_RSG_INVALID_ROOT_ID');
49 fnd_message.set_token('RSID',TO_CHAR(p_root_request_id));
50 errbuf := FND_MESSAGE.GET;
51 errcode := 'BIS_RSG_INVALID_ROOT_ID';
52 close cv_root;
53 return null;
54 END;
55 end if;
56
57 open cv(nrset_id);
58 fetch cv into rs_name;
59 if(CV%NOTFOUND) THEN
60 fnd_message.set_name('BIS','BIS_RSG_NO_RS_FOUND');
61 fnd_message.set_token('RSID',TO_CHAR(p_root_request_id));
62 fnd_message.set_token('RSETID',rset_id);
63 errbuf := FND_MESSAGE.GET;
64 errcode := 'BIS_RSG_NO_RS_FOUND';
65 else
66 fnd_message.set_name('BIS','BIS_RSG_SUCCESS');
67 errbuf := FND_MESSAGE.GET;
68 errcode := 'BIS_RSG_SUCCESS';
69 END IF;
70 close cv;
71 return rs_name;
72 EXCEPTION
73 WHEN OTHERS THEN
74 errbuf := sqlerrm;
75 errcode := 'BIS_RSG_ERR_UNEXPECTED';
76 return null;
77 END Get_RS_Name;
78
79 -- Return the name of the request set of which this request is part of
80 -- if not found then return null and set appropriate errcode
81 Function Get_Current_RS_Name (errbuf out NOCOPY varchar2,
82 errcode out NOCOPY varchar2) return varchar2 IS
83 rs_name varchar2(30);
84 l_request_id number;
85 l_root_request_id number;
86 BEGIN
87 rs_name := null;
88 l_request_id := fnd_global.CONC_REQUEST_ID;
89 l_root_request_id := fnd_global.CONC_PRIORITY_REQUEST;
90
91 if(l_request_id = l_root_request_id) then
92 fnd_message.set_name('BIS','BIS_RSG_NO_RS_STANDALONE');
93 fnd_message.set_token('RSID',TO_CHAR(l_request_id));
94 errbuf := FND_MESSAGE.GET;
95 errcode := 'BIS_RSG_NO_RS_STANDALONE';
96 return null;
97 end if;
98
99 rs_name := Get_RS_Name(l_root_request_id,errbuf,errcode);
100 return rs_name;
101 EXCEPTION
102 WHEN OTHERS THEN
103 errbuf := sqlerrm;
104 errcode := 'BIS_RSG_ERR_UNEXPECTED';
105 return null;
106 END Get_Current_RS_Name;
107
108 -- Return the refresh mode of the request set
109 -- if not found then return null and set appropriate errcode
110 Function Get_RS_Refresh_mode (p_req_set_name IN varchar2, errbuf out NOCOPY varchar2,
111 errcode out NOCOPY varchar2) return varchar2 IS
112 CURSOR CV(rs_name in varchar2) is
113 SELECT OPTION_VALUE FROM BIS_REQUEST_SET_OPTIONS where
114 OPTION_NAME='REFRESH_MODE' and REQUEST_SET_NAME=upper(rs_name)
115 and set_app_id = 191;
116
117 l_value varchar2(30);
118 BEGIN
119 l_value := null;
120 open cv(p_req_set_name);
121 fetch cv into l_value;
122 if(CV%NOTFOUND) THEN
123 fnd_message.set_name('BIS','BIS_RSG_NO_RSDATA_FOUND');
124 fnd_message.set_token('RSNAME',p_req_set_name);
125 errbuf := FND_MESSAGE.GET;
126 errcode := 'BIS_RSG_NO_RSDATA_FOUND';
127 elsif l_value is null then
128 fnd_message.set_name('BIS','BIS_RSG_RS_NO_REF_MODE');
129 fnd_message.set_token('RSNAME',p_req_set_name);
130 errbuf := FND_MESSAGE.GET;
131 errcode := 'BIS_RSG_RS_NO_REF_MODE';
132 else
133 fnd_message.set_name('BIS','BIS_RSG_SUCCESS');
134 errbuf := FND_MESSAGE.GET;
135 errcode := 'BIS_RSG_SUCCESS';
136 END IF;
137 close cv;
138 return l_value;
139 EXCEPTION
140 WHEN OTHERS THEN
141 errbuf := sqlerrm;
142 errcode := 'BIS_RSG_ERR_UNEXPECTED';
143 return null;
144 END Get_RS_Refresh_mode;
145
146 -- Return the refresh mode of the request set of which this request is part of
147 -- if not found then return null and set appropriate errcode
148 Function Get_Current_RS_Refresh_mode (errbuf out NOCOPY varchar2,
149 errcode out NOCOPY varchar2) return varchar2 IS
150 rs_name varchar2(30);
151 l_value varchar2(30);
152 BEGIN
153 --get the name of the current request set
154 rs_name := Get_Current_RS_Name(errbuf,errcode);
155 if(rs_name is null) then
156 return null;
157 end if;
158
159 l_value := Get_RS_Refresh_mode(rs_name,errbuf,errcode);
160 return l_value;
161 END;
162
163 -- populate p_content_list with the list of contents of the request set
164 -- if not found then set appropriate errcode
165 -- possible values of p_content_type
166 -- 'PAGE' to get the list pf pages in this request set
167 -- 'REPORT' to get the list of reports in this request set
168 -- nulll to get all the contents of this request set
169 Procedure Get_content_in_RS( p_req_set_name IN varchar2, p_content_list OUT NOCOPY BIS_RSG_CONTENT_LIST,
170 p_content_type IN VARCHAR2,errbuf out NOCOPY varchar2,
171 errcode out NOCOPY varchar2) IS
172 CURSOR CV (rs_name IN varchar2) IS
173 select object_name,object_type from bis_request_set_objects
174 where request_set_name = upper(rs_name) and set_app_id = 191;
175
176 CURSOR CV_TYPE (rs_name in varchar2, con_type in varchar2) IS
177 select object_name,object_type from bis_request_set_objects
178 where request_set_name = upper(rs_name) and object_type = con_type and set_app_id = 191;
179 i number;
180 BEGIN
181 i := 0;
182 p_content_list := BIS_RSG_CONTENT_LIST();
183 if(p_content_type is null) then
184 FOR cv_rec in cv(p_req_set_name) loop
185 i:=i+1;
186 p_content_list.extend;
187 p_content_list(i).name := cv_rec.object_name;
188 p_content_list(i).type := cv_rec.object_type;
189 End loop;
190 else
191 FOR cv_rec in cv_type(p_req_set_name,p_content_type) loop
192 i:=i+1;
193 p_content_list.extend;
194 p_content_list(i).name := cv_rec.object_name;
195 p_content_list(i).type := cv_rec.object_type;
196 End loop;
197 end if;
198
199 if(i=0 and p_content_type is null) THEN
200 fnd_message.set_name('BIS','BIS_RSG_NO_CONTENT_FOUND');
201 fnd_message.set_token('RSNAME',p_req_set_name);
202 errbuf := FND_MESSAGE.GET;
203 errcode := 'BIS_RSG_NO_CONTENT_FOUND';
204 elsif(i=0 and p_content_type ='REPORT') THEN
205 fnd_message.set_name('BIS','BIS_RSG_NO_REPORT_FOUND');
206 fnd_message.set_token('RSNAME',p_req_set_name);
207 errbuf := FND_MESSAGE.GET;
208 errcode := 'BIS_RSG_NO_REPORT_FOUND';
209 elsif(i=0 and p_content_type ='PAGE') THEN
210 fnd_message.set_name('BIS','BIS_RSG_NO_PAGE_FOUND');
211 fnd_message.set_token('RSNAME',p_req_set_name);
212 errbuf := FND_MESSAGE.GET;
213 errcode := 'BIS_RSG_NO_PAGE_FOUND';
214 else
215 fnd_message.set_name('BIS','BIS_RSG_SUCCESS');
216 errbuf := FND_MESSAGE.GET;
217 errcode := 'BIS_RSG_SUCCESS';
218 end if;
219 EXCEPTION
220 WHEN OTHERS THEN
221 errbuf := sqlerrm;
222 errcode := 'BIS_RSG_ERR_UNEXPECTED';
223 END;
224
225 -- populate p_content_list with the list of contents of the request set
226 -- if not found then set appropriate errcode (overloaded)
227 Procedure Get_content_in_RS( p_req_set_name IN varchar2, p_content_list OUT NOCOPY BIS_RSG_CONTENT_LIST,
228 errbuf out NOCOPY varchar2, errcode out NOCOPY varchar2) IS
229 BEGIN
230 Get_content_in_RS(p_req_set_name,p_content_list,NULL,errbuf,errcode);
231 END;
232
233 -- populate p_page_list with the list of page of the request set
234 -- if not found then set appropriate errcode
235 Procedure Get_pages_in_RS ( p_req_set_name IN varchar2, p_page_list out nocopy BIS_RSG_CONTENT_LIST,
236 errbuf out NOCOPY varchar2, errcode out NOCOPY varchar2) IS
237 BEGIN
238 Get_content_in_RS(p_req_set_name,p_page_list,'PAGE',errbuf,errcode);
239 END;
240
241 -- populate p_page_list with the list of page of the request set of which this request is a part of
242 -- if not found then set appropriate errcode
243 Procedure Get_reports_in_RS ( p_req_set_name IN varchar2, p_report_list out nocopy BIS_RSG_CONTENT_LIST,
244 errbuf out NOCOPY varchar2, errcode out NOCOPY varchar2) IS
245 BEGIN
246 Get_content_in_RS(p_req_set_name,p_report_list,'REPORT',errbuf,errcode);
247 END;
248
249 -- populate p_report_list with the list of report of the request set
250 -- if not found then set appropriate errcode
251 Procedure Get_pages_in_current_RS( p_page_list out nocopy BIS_RSG_CONTENT_LIST,
252 errbuf out NOCOPY varchar2, errcode out NOCOPY varchar2)IS
253 rs_name varchar2(30);
254 BEGIN
255 --get the name of the current request set
256 rs_name := Get_Current_RS_Name(errbuf,errcode);
257 if(rs_name is not null) then
258 Get_content_in_RS(rs_name,p_page_list,'PAGE',errbuf,errcode);
259 end if;
260 END;
261
262 -- populate p_report_list with the list of report of the request set of which this request is a part of
263 -- if not found then set appropriate errcode
264 Procedure Get_reports_in_current_RS( p_report_list out nocopy BIS_RSG_CONTENT_LIST,
265 errbuf out NOCOPY varchar2, errcode out NOCOPY varchar2)IS
266 rs_name varchar2(30);
267 BEGIN
268 --get the name of the current request set
269 rs_name := Get_Current_RS_Name(errbuf,errcode);
270 if(rs_name is not null) then
271 Get_content_in_RS(rs_name,p_report_list,'REPORT',errbuf,errcode);
272 end if;
273 END;
274
275 END;
[Home] [Help]