[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_DSCRAM_RUNS_PKG
Source
1 PACKAGE BODY FND_OAM_DSCRAM_RUNS_PKG as
2 /* $Header: AFOAMDSRUNB.pls 120.6 2005/12/19 10:07 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(20) := 'DSCRAM_RUNS_PKG.';
8
9 -- Profile option names controlling whether the product is enabled and whether the instance is in a state where
10 -- scrambling operations are allowed.
11 B_DSCRAM_ENABLED_PROFILE_NAME CONSTANT VARCHAR2(30) := 'OAM_DSCRAM_ENABLED';
12 B_DSCRAM_ALLOWED_PROFILE_NAME CONSTANT VARCHAR2(30) := 'OAM_DSCRAM_ALLOWED';
13
14 B_PROFILE_ENABLED_VALUE CONSTANT VARCHAR2(30) := 'YES';
15
16 ----------------------------------------
17 -- Private Body Variables
18 ----------------------------------------
19 TYPE b_run_cache_type IS RECORD
20 (
21 initialized BOOLEAN := FALSE,
22 run_id NUMBER := NULL,
23 run_stat_id NUMBER := NULL,
24 valid_check_interval NUMBER := NULL,
25 run_mode VARCHAR2(30) := NULL,
26 arg_context FND_OAM_DSCRAM_ARGS_PKG.arg_context,
27 last_validated DATE := NULL,
28 last_validation_ret_sts VARCHAR2(6) := NULL
29 );
30 b_run_info b_run_cache_type;
31
32 ----------------------------------------
33 -- Public/Private Procedures/Functions
34 ----------------------------------------
35
36 -- Public
37 FUNCTION GET_RUN_ID
38 RETURN NUMBER
39 IS
40 BEGIN
41 IF NOT b_run_info.initialized THEN
42 RAISE NO_DATA_FOUND;
43 END IF;
44
45 RETURN b_run_info.run_id;
46 END;
47
48 -- Public
49 FUNCTION GET_RUN_STAT_ID
50 RETURN NUMBER
51 IS
52 BEGIN
53 IF NOT b_run_info.initialized THEN
54 RAISE NO_DATA_FOUND;
55 END IF;
56
57 RETURN b_run_info.run_stat_id;
58 END;
59
60 -- Public
61 FUNCTION GET_VALID_CHECK_INTERVAL
62 RETURN NUMBER
63 IS
64 BEGIN
65 IF NOT b_run_info.initialized THEN
66 RAISE NO_DATA_FOUND;
67 END IF;
68
69 RETURN b_run_info.valid_check_interval;
70 END;
71
72 -- Public
73 FUNCTION GET_RUN_MODE
74 RETURN VARCHAR2
75 IS
76 BEGIN
77 IF NOT b_run_info.initialized THEN
78 RAISE NO_DATA_FOUND;
79 END IF;
80
81 RETURN b_run_info.run_mode;
82 END;
83
84 -- Public
85 PROCEDURE GET_RUN_ARG_CONTEXT(px_arg_context IN OUT NOCOPY FND_OAM_DSCRAM_ARGS_PKG.arg_context)
86 IS
87 BEGIN
88 IF NOT b_run_info.initialized THEN
89 RAISE NO_DATA_FOUND;
90 END IF;
91
92 --return a direct reference
93 px_arg_context := b_run_info.arg_context;
94 END;
95
96 -- Public
97 PROCEDURE SET_RUN_ARG_CONTEXT(p_arg_context IN FND_OAM_DSCRAM_ARGS_PKG.arg_context)
98 IS
99 BEGIN
100 IF NOT b_run_info.initialized THEN
101 RAISE NO_DATA_FOUND;
102 END IF;
103
104 --return a direct reference
105 b_run_info.arg_context := p_arg_context;
106 END;
107
108 -- Public
109 PROCEDURE INITIALIZE_RUN_ARG_CONTEXT(x_return_status OUT NOCOPY VARCHAR2,
110 x_return_msg OUT NOCOPY VARCHAR2)
111 IS
112 BEGIN
113 FND_OAM_DSCRAM_ARGS_PKG.FETCH_RUN_ARG_CONTEXT(b_run_info.run_id,
114 b_run_info.arg_context,
115 x_return_status,
116 x_return_msg);
117 END;
118
119 -- Public API
120 FUNCTION VALIDATE_START_EXECUTION(p_run_id IN NUMBER,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_return_msg OUT NOCOPY VARCHAR2)
123 RETURN BOOLEAN
124 IS
125 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_START_EXECUTION';
126
127 l_prof_value VARCHAR2(20);
128 l_run_dbname VARCHAR2(30);
129 l_status VARCHAR2(30);
130 l_current_dbname VARCHAR2(30);
131 l_run_stat_id NUMBER;
132
133 CURSOR C1
134 IS
135 SELECT run_status, target_dbname
136 FROM fnd_oam_dscram_runs_b
137 WHERE run_id = p_run_id;
138 BEGIN
139 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 x_return_msg := '';
142
143 -- check that data scrambling is enabled first
144 FND_PROFILE.GET(B_DSCRAM_ENABLED_PROFILE_NAME,
145 l_prof_value);
146 IF l_prof_value IS NULL OR UPPER(l_prof_value) <> B_PROFILE_ENABLED_VALUE THEN
147 x_return_msg := 'The Data Scrambling feature is not Enabled.';
148 RAISE PROGRAM_ERROR;
149 END IF;
150
151 -- now check that data scrambling operations are allowed
152 l_prof_value := NULL;
153 FND_PROFILE.GET(B_DSCRAM_ALLOWED_PROFILE_NAME,
154 l_prof_value);
155 IF l_prof_value IS NULL OR UPPER(l_prof_value) <> B_PROFILE_ENABLED_VALUE THEN
156 x_return_msg := 'Data Scrambling operations are not currently allowed.';
157 RAISE PROGRAM_ERROR;
158 END IF;
159
160 --fetch necessary run attributes
161 OPEN C1;
162 FETCH C1 INTO l_status, l_run_dbname;
163 IF C1%NOTFOUND THEN
164 x_return_msg := 'Invalid run_id: ('||p_run_id||')';
165 RAISE PROGRAM_ERROR;
166 END IF;
167 CLOSE C1;
168
169 --make sure the run has been marked as processing by the master controller
170 IF NOT FND_OAM_DSCRAM_UTILS_PKG.STATUS_IS_PROCESSING(l_status) THEN
171 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.CONV_VALIDATE_START_STS_TO_RET(l_status);
172 IF FND_OAM_DSCRAM_UTILS_PKG.RET_STS_IS_ERROR(x_return_status) THEN
173 x_return_msg := 'Invalid run status('||l_status||')';
174 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
175 END IF;
176 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
177 RETURN FALSE;
178 END IF;
179
180 --validate that the run's dbname matches this DB
181 SELECT UPPER(name)
182 INTO l_current_dbname
183 FROM V$DATABASE;
184
185 IF l_current_dbname IS NULL OR l_current_dbname <> l_run_dbname THEN
186 x_return_msg := 'Invalid target dbname, current('||l_current_dbname||'), target('||l_run_dbname||')';
187 RAISE PROGRAM_ERROR;
188 END IF;
189
190 --success
191 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
192 x_return_status := FND_API.G_RET_STS_SUCCESS;
193 RETURN TRUE;
194 EXCEPTION
195 WHEN PROGRAM_ERROR THEN
196 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
197 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
198 RETURN FALSE;
199 WHEN OTHERS THEN
200 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
201 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
204 RETURN FALSE;
205 END;
206
207 -- Public
208 -- Return Statuses:
209 -- SUCCESS, ERROR, ERROR_UNEXP,
210 -- Converted: PROCESSED, STOPPED, ERROR_FATAL, STOP
211 FUNCTION VALIDATE_CONTINUED_EXECUTION(p_force_query IN BOOLEAN,
212 p_recurse IN BOOLEAN,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_return_msg OUT NOCOPY VARCHAR2)
215 RETURN BOOLEAN
216 IS
217 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_CONTINUED_EXECUTION';
218
219 l_status VARCHAR2(30) := NULL;
220
221 CURSOR C1
222 IS
223 SELECT run_status
224 FROM fnd_oam_dscram_runs_b
225 WHERE run_id = b_run_info.run_id;
226 BEGIN
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 x_return_msg := '';
229
230 -- make sure the state's initialized
231 IF NOT b_run_info.initialized THEN
232 RAISE NO_DATA_FOUND;
233 END IF;
234
235 -- check if we should do work or if we can return the cached status
236 IF NOT (p_force_query OR
237 FND_OAM_DSCRAM_UTILS_PKG.VALIDATION_DUE(b_run_info.last_validated)) THEN
238 x_return_status := b_run_info.last_validation_ret_sts;
239 RETURN (x_return_status = FND_API.G_RET_STS_SUCCESS);
240 END IF;
241
242 fnd_oam_debug.log(1, l_ctxt, '>RE-QUERYING<');
243
244 -- re-init the cached fields to allow easy exit
245 b_run_info.last_validation_ret_sts := x_return_status;
246 b_run_info.last_validated := SYSDATE;
247
248 --otherwise, fetch necessary run attributes and evaluate
249 OPEN C1;
250 FETCH C1 INTO l_status;
251 IF C1%NOTFOUND THEN
252 --shouldn't happen since we're using the cached run_id
253 x_return_msg := 'Invalid cached run_id: '||b_run_info.run_id;
254 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
255 RETURN FALSE;
256 END IF;
257 CLOSE C1;
258
259 --make sure the run has been marked as processing by the master controller
260 IF l_status <> FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSING THEN
261 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.CONV_VALIDATE_CONT_STS_TO_RET(l_status);
262 b_run_info.last_validation_ret_sts := x_return_status;
263 IF x_return_status <> FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_PROCESSED THEN
264 x_return_msg := 'Invalid run status('||l_status||')';
265 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
266 END IF;
267 RETURN FALSE;
268 END IF;
269
270 --ignore p_recurse because we're the topmost entity
271
272 --success
273 x_return_status := FND_API.G_RET_STS_SUCCESS;
274 b_run_info.last_validation_ret_sts := x_return_status;
275 b_run_info.last_validated := SYSDATE;
276 RETURN TRUE;
277 EXCEPTION
278 WHEN OTHERS THEN
279 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
280 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282 b_run_info.last_validation_ret_sts := x_return_status;
283 b_run_info.last_validated := SYSDATE;
284 RETURN FALSE;
285 END;
286
287 -- Public
288 -- Return Statuses:
289 -- SUCCESS, ERROR, ERROR_UNEXP
290 PROCEDURE ASSIGN_WORKER_TO_RUN(p_run_id IN NUMBER,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_return_msg OUT NOCOPY VARCHAR2)
293 IS
294 l_ctxt VARCHAR2(60) := PKG_NAME||'ASSIGN_WORKER_TO_RUN';
295
296 l_run_stat_id NUMBER;
297 l_valid_check_interval NUMBER;
298 l_run_mode VARCHAR2(30);
299 l_null NUMBER;
300 l_return_status VARCHAR2(6);
301 l_return_msg VARCHAR2(2048);
302
303 CURSOR C1
304 IS
305 SELECT last_run_stat_id, valid_check_interval, run_mode
306 FROM fnd_oam_dscram_runs_b
307 WHERE run_id = p_run_id;
308 CURSOR C2(c_stat_id NUMBER)
309 IS
310 SELECT 1
311 FROM sys.dual
312 WHERE EXISTS (SELECT 1
313 FROM fnd_oam_dscram_stats
314 WHERE stat_id = c_stat_id);
315 BEGIN
316 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
317 x_return_status := FND_API.G_RET_STS_ERROR;
318 x_return_msg := '';
319
320 --fetch necessary run attributes
321 OPEN C1;
322 FETCH C1 INTO l_run_stat_id, l_valid_check_interval, l_run_mode;
323 IF C1%NOTFOUND THEN
324 x_return_msg := 'Invalid run_id: ('||p_run_id||')';
325 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
326 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
327 RETURN;
328 END IF;
329 CLOSE C1;
330
331 --make sure it's a valid stat id
332 OPEN C2(l_run_stat_id);
333 FETCH C2 into l_null;
334 IF C2%NOTFOUND THEN
335 x_return_msg := 'Invalid run_stat_id: ('||l_run_stat_id||')';
336 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
337 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
338 RETURN;
339 END IF;
340 CLOSE C2;
341
342 fnd_oam_debug.log(1, l_ctxt, 'Run Stat ID: '||l_run_stat_id);
343
344 b_run_info.run_id := p_run_id;
345 b_run_info.run_stat_id := l_run_stat_id;
346 IF l_valid_check_interval IS NULL OR l_valid_check_interval < 0 THEN
347 l_valid_check_interval := 0;
348 fnd_oam_debug.log(1, l_ctxt, 'Entity Validation Polling DISABLED');
349 ELSE
350 fnd_oam_debug.log(1, l_ctxt, 'Entity Validation Polling Interval: '||l_valid_check_interval||' seconds');
351 END IF;
352 b_run_info.valid_check_interval := l_valid_check_interval;
353 b_run_info.run_mode := l_run_mode;
354 b_run_info.last_validated := NULL;
355 b_run_info.initialized := TRUE;
356
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
359 EXCEPTION
360 WHEN OTHERS THEN
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362 x_return_msg := 'Unhandled Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
363 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
364 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
365 END;
366
367 -- Public
368 -- Copied from FND_OAM_DS_PSETS_PKG, seems to mimic standard syntax of calls
369 -- made from FND_TOP/sql/FNDNLINS.sql.
370 PROCEDURE ADD_LANGUAGE
371 IS
372 BEGIN
373
374 delete from FND_OAM_DSCRAM_RUNS_TL T
375 where not exists
376 (select NULL
377 from FND_OAM_DSCRAM_RUNS_B B
378 where B.RUN_ID = T.RUN_ID
379 );
380
381 update FND_OAM_DSCRAM_RUNS_TL T set (
382 DISPLAY_NAME,
383 DESCRIPTION
384 ) = (select
385 B.DISPLAY_NAME,
386 B.DESCRIPTION
387 from FND_OAM_DSCRAM_RUNS_TL B
388 where B.RUN_ID = T.RUN_ID
389 and B.LANGUAGE = T.SOURCE_LANG)
390 where (
391 T.RUN_ID,
392 T.LANGUAGE
393 ) in (select
394 SUBT.RUN_ID,
395 SUBT.LANGUAGE
396 from FND_OAM_DSCRAM_RUNS_TL SUBB, FND_OAM_DSCRAM_RUNS_TL SUBT
397 where SUBB.RUN_ID = SUBT.RUN_ID
398 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
399 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
400 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
401 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
402 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
403 ));
404
405 insert into FND_OAM_DSCRAM_RUNS_TL (
406 RUN_ID,
407 DISPLAY_NAME,
408 DESCRIPTION,
409 CREATED_BY,
410 CREATION_DATE,
411 LAST_UPDATED_BY,
412 LAST_UPDATE_DATE,
413 LAST_UPDATE_LOGIN,
414 LANGUAGE,
415 SOURCE_LANG
416 ) select /*+ ORDERED */
417 B.RUN_ID,
418 B.DISPLAY_NAME,
419 B.DESCRIPTION,
420 B.CREATED_BY,
421 B.CREATION_DATE,
422 B.LAST_UPDATED_BY,
423 B.LAST_UPDATE_DATE,
424 B.LAST_UPDATE_LOGIN,
425 L.LANGUAGE_CODE,
426 B.SOURCE_LANG
427 from FND_OAM_DSCRAM_RUNS_TL B, FND_LANGUAGES L
428 where L.INSTALLED_FLAG in ('I', 'B')
429 and B.LANGUAGE = userenv('LANG')
430 and not exists
431 (select NULL
432 from FND_OAM_DSCRAM_RUNS_TL T
433 where T.RUN_ID = B.RUN_ID
434 and T.LANGUAGE = L.LANGUAGE_CODE);
435
436 END ADD_LANGUAGE;
437
438 END FND_OAM_DSCRAM_RUNS_PKG;