[Home] [Help]
PACKAGE BODY: APPS.BIL_BI_UTIL_COLLECTION_PKG
Source
1 PACKAGE BODY bil_bi_util_collection_pkg AS
2 /*$Header: bilbutcb.pls 120.1 2006/03/27 22:04:23 jmahendr noship $*/
3
4 -- Declare global variables
5 g_debug BOOLEAN;
6
7
8 FUNCTION get_global_rate_p (code IN varchar2, rate_Date IN date)
9 RETURN NUMBER
10 parallel_enable IS
11 BEGIN
12 return FII_CURRENCY.Get_Global_Rate_Primary(code,rate_date);
13 END get_global_rate_p ;
14
15
16 FUNCTION get_global_rate_s (code IN varchar2, rate_Date IN date)
17 RETURN NUMBER
18 parallel_enable IS
19 BEGIN
20 return FII_CURRENCY.Get_Global_Rate_secondary(code,rate_date);
21 END get_global_rate_s ;
22
23
24 -- **********************************************************************
25 -- FUNCTION get_schema_name
26 --
27 -- Purpose:
28 -- To retrieve the schema name for the given Application Short Name
29 --
30 -- **********************************************************************
31
32 FUNCTION get_schema_name(p_appl_name IN VARCHAR2)
33 RETURN VARCHAR2 IS
34 l_status VARCHAR2(1000);
35 l_industry VARCHAR2(1000);
36 l_schema_name VARCHAR2(1000);
37 l_return BOOLEAN;
38 l_app_name VARCHAR2(100);
39 BEGIN
40
41 -- Call the FND proc to return schema name
42 l_return := FND_INSTALLATION.GET_APP_INFO(p_appl_name,l_status,l_industry,l_schema_name);
43 RETURN l_schema_name;
44
45 EXCEPTION
46 WHEN OTHERS THEN
47 BEGIN
48 SELECT u.oracle_username
49 INTO l_schema_name
50 FROM fnd_application a,
51 fnd_product_installations i,
52 fnd_oracle_userid u
53 WHERE a.application_short_name = l_app_name
54 AND a.application_id = i.application_id
55 AND u.oracle_id = i.oracle_id;
56 RETURN l_schema_name;
57 EXCEPTION
58 WHEN OTHERS THEN
59 RETURN NULL;
60 END;
61
62 END get_schema_name;
63
64 -- **********************************************************************
65 -- FUNCTION get_apps_schema_name
66 --
67 -- Purpose:
68 -- To retrieve the apps schema name
69 --
70 -- **********************************************************************
71
72 FUNCTION get_apps_schema_name
73 RETURN VARCHAR2 IS
74 l_apps_schema_name VARCHAR2(200);
75 BEGIN
76 SELECT u.oracle_username
77 INTO l_apps_schema_name
78 FROM fnd_oracle_userid u
79 WHERE u.oracle_id = 900;
80 RETURN l_apps_schema_name;
81 EXCEPTION
82 WHEN OTHERS THEN
83 RETURN NULL;
84 END get_apps_schema_name;
85
86
87 -- **********************************************************************
88 -- PROCEDURE analyze_table
89 --
90 -- Purpose:
91 -- To Analyze the input Sales Intelligence table from the BIL
92 -- schema
93 --
94 -- **********************************************************************
95
96 PROCEDURE analyze_table (p_tbl_name IN VARCHAR2,
97 p_cascade IN BOOLEAN,
98 p_est_pct IN NUMBER,
99 p_granularity IN VARCHAR2) IS
100
101 l_schema_name VARCHAR2(400);
102 BEGIN
103
104 l_schema_name := get_schema_name(p_appl_name => 'BIL');
105
106 FND_STATS.gather_table_stats(
107 ownname=> l_schema_name,
108 tabName=> p_tbl_name,
109 cascade=> p_cascade,
110 degree=> bis_common_parameters.get_degree_of_parallelism,
111 percent=> p_est_pct,
112 granularity=> p_granularity);
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 NULL;
117 END analyze_table;
118
119
120 -- ********************************************************************
121 -- PROCEDURE drop_table
122 --
123 -- Purpose:
124 -- To drop the table specified.
125 --
126 -- ********************************************************************
127
128 PROCEDURE drop_table (p_table_name IN varchar2) is
129 l_stmt varchar2(400);
130 l_schema_name VARCHAR2(400);
131 l_error EXCEPTION;
132 PRAGMA EXCEPTION_INIT (l_error, -942);
133
134 BEGIN
135
136 l_schema_name := get_schema_name(p_appl_name => 'BIL');
137 l_stmt:='DROP TABLE '|| l_schema_name || '.' || p_table_name;
138
139 EXECUTE IMMEDIATE l_stmt;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 NULL;
144 END drop_table;
145
146
147 -- ********************************************************************
148 -- PROCEDURE truncate_table
149 --
150 -- Purpose:
151 -- To remove all the records from the table specified.
152 --
153 -- ********************************************************************
154
155 PROCEDURE truncate_table (p_table_name IN varchar2) is
156 l_stmt varchar2(400);
157 l_schema_name VARCHAR2(400);
158
159 BEGIN
160 l_schema_name := get_schema_name(p_appl_name => 'BIL');
161 l_stmt:='TRUNCATE TABLE '|| l_schema_name || '.'|| p_table_name;
162
163 -- Since Fact table has a log
164 IF (UPPER(p_table_name) IN ('BIL_BI_FST_DTL_F', 'BIL_BI_OPDTL_F', 'BIL_BI_PIPELINE_F')) THEN
165 l_stmt := l_stmt || ' PURGE MATERIALIZED VIEW LOG ';
166 END IF;
167
168 EXECUTE IMMEDIATE l_stmt;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 NULL;
173 END truncate_table;
174
175 -- ********************************************************************
176 -- FUNCTION get_profile_value
177 --
178 -- Purpose:
179 -- To get the profile value of the specified parameter
180 --
181 -- ********************************************************************
182
183 FUNCTION get_profile_value (p_profile_parameter IN VARCHAR2)
184 RETURN VARCHAR2
185 IS
186 l_profile_value VARCHAR2(400);
187 BEGIN
188 l_profile_value := FND_PROFILE.VALUE(p_profile_parameter);
189 RETURN l_profile_value;
190 EXCEPTION
191 WHEN OTHERS THEN
192 RETURN NULL;
193 END get_profile_value;
194
195
196 -- **********************************************************************
197 -- FUNCTION chkLogLevel
198 --
199 -- Purpose
200 -- To check if log is Enabled for Messages
201 -- This function is a wrapper on FND APIs for OA Common Error
202 -- logging framework
203 --
204 -- p_log_level = Severity; valid values are -
205 -- 1. Statement Level (FND_LOG.LEVEL_STATEMENT)
206 -- 2. Procedure Level (FND_LOG.LEVEL_PROCEDURE)
207 -- 3. Event Level (FND_LOG.LEVEL_EVENT)
208 -- 4. Exception Level (FND_LOG.LEVEL_EXCEPTION)
209 -- 5. Error Level (FND_LOG.LEVEL_ERROR)
210 -- 6. Unexpected Level (FND_LOG.LEVEL_UNEXPECTED)
211 --
212 -- Output values:-
213 -- = TRUE if FND Log is Enabled or BIS Log is Enabled
214 -- = FALSE if both are DISABLED
215 --
216 --
217 -- **********************************************************************
218
219 FUNCTION chkLogLevel (p_log_level IN NUMBER) RETURN BOOLEAN IS
220 BEGIN
221 g_debug := NVL(BIS_COLLECTION_UTILITIES.g_debug,FALSE);
222 IF (p_log_level >= fnd_log.G_CURRENT_RUNTIME_LEVEL) -- FND log is enabled
223 OR NVL(BIS_COLLECTION_UTILITIES.g_debug,FALSE) -- BIS Log is enabled
224 THEN
225 RETURN TRUE;
226 END IF;
227 RETURN FALSE;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 NULL;
232
233 END chkLogLevel;
234
235
236 -- **********************************************************************
237 -- PROCEDURE writeLog
238 --
239 -- Purpose:
240 -- To log Messages
241 -- This procedure is a wrapper on FND APIs for OA Common Error
242 -- logging framework for Severity = Statement(1), Procedure(2)
243 -- , Event(3), Expected (4) and Error (5)
244 --
245 -- Input Variables :-
246 -- p_log_level = Severity; valid values are -
247 -- 1. Statement Level (FND_LOG.LEVEL_STATEMENT)
248 -- 2. Procedure Level (FND_LOG.LEVEL_PROCEDURE)
249 -- 3. Event Level (FND_LOG.LEVEL_EVENT)
250 -- 4. Exception Level (FND_LOG.LEVEL_EXCEPTION)
251 -- 5. Error Level (FND_LOG.LEVEL_ERROR)
252 -- 6. Unexpected Level (FND_LOG.LEVEL_UNEXPECTED)
253 -- p_module = Module Source Details
254 -- p_msg = Message String
255 -- p_force_log = Force message in log file. Default False.
256 --
257 -- **********************************************************************
258
259 PROCEDURE writeLog
260 (
261 p_log_level IN NUMBER,
262 p_module IN VARCHAR2,
263 p_msg IN VARCHAR2,
264 p_force_log IN BOOLEAN DEFAULT FALSE
265 )
266 IS
267 l_msg VARCHAR2(40);
268
269 BEGIN
270
271 -- Log errors in concurrent request output file and log file forcefully
272
273 /*
274 Log error/unexpected errors irrespective of the log level profile setup.
275 TB checked up with weijun if this is the intended func.
276 */
277
278 l_msg := TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS');
279 IF (p_log_level IN (fnd_log.LEVEL_ERROR, fnd_log.LEVEL_UNEXPECTED)) THEN
280 BIS_COLLECTION_UTILITIES.put_line_out(p_msg);
281
282 --this line will put the gist of the error message in the log file
283 BIS_COLLECTION_UTILITIES.put_line(l_msg || ' : ' || p_module || ' : ' || p_msg,p_log_level);
284
285
286 ELSE -- Log message in concurrent request log file
287 BIS_COLLECTION_UTILITIES.put_line(l_msg || ' : ' || p_module || ' : ' || p_msg,p_log_level);
288 END IF; -- p_log_level
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 NULL;
293
294 END writeLog;
295
296
297 -- **********************************************************************
298 -- FUNCTION get_user_profile_name
299 --
300 -- Purpose
301 -- To return the User PRofile Name
302 --
303 -- Input value = p_profile_name = Profile Name
304 --
305 -- Output values:-
306 -- = Profile Option Name for the User
307 --
308 --
309 -- **********************************************************************
310
311 FUNCTION get_user_profile_name (p_profile_name IN VARCHAR2) RETURN VARCHAR2 IS
312 l_name varchar2(240) ;
313 l_proc VARCHAR2(100) ;
314 BEGIN
315 l_proc := 'get_user_profile_name.';
316
317 SELECT user_profile_option_name
318 INTO l_name
319 FROM fnd_profile_options_tl tl
320 WHERE tl.profile_option_name = p_profile_name
321 AND tl.LANGUAGE = userenv('LANG');
322
323 RETURN l_name;
324
325 EXCEPTION
326 WHEN OTHERS THEN
327 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
328 fnd_message.set_token('ERRNO' ,SQLCODE);
329 fnd_message.set_token('REASON', SQLERRM);
330 fnd_message.set_token('ROUTINE', l_proc);
331 RAISE;
332
333 END get_user_profile_name;
334
335
336 END bil_bi_util_collection_pkg;