[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_DSCFG_PROCS_PKG
Source
1 PACKAGE BODY FND_OAM_DSCFG_PROCS_PKG as
2 /* $Header: AFOAMDSCPROCB.pls 120.1 2005/11/23 17:03 yawu noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(20) := 'DSCFG_PROCS_PKG.';
8
9 -- cursor for selecting procedures for a given stage, getting procedures for two stages concurrently will
10 -- cause both to fail iteration.
11 CURSOR B_PROCS(p_stage VARCHAR2)
12 IS
13 SELECT /*+ FIRST_ROWS(1) */ P.proc_id, P.proc_type, P.error_is_fatal_flag, P.location, P.executable
14 FROM fnd_oam_dscfg_procs P
15 WHERE P.STAGE = p_stage
16 AND SYSDATE BETWEEN NVL(P.START_DATE, SYSDATE) and NVL(P.END_DATE, SYSDATE)
17 ORDER BY P.priority ASC, P.proc_id ASC;
18
19 -- local state for the last fetched proc
20 TYPE b_proc_state_type IS RECORD
21 (
22 initialized BOOLEAN := FALSE,
23 proc_id NUMBER := NULL,
24 proc_type VARCHAR2(30) := NULL,
25 error_is_fatal_flag VARCHAR2(3) := NULL,
26 location VARCHAR2(2000) := NULL,
27 executable VARCHAR2(2000) := NULL
28 );
29 b_proc_state b_proc_state_type;
30
31 -- variable indicating stage of the last GET call
32 b_last_fetched_stage VARCHAR2(30) := NULL;
33
34 ----------------------------------------
35 -- Public/Private Procedures/Functions
36 ----------------------------------------
37
38 -- Public
39 FUNCTION IS_INITIALIZED
40 RETURN BOOLEAN
41 IS
42 BEGIN
43 RETURN b_proc_state.initialized;
44 END;
45
46 -- Public
47 FUNCTION GET_CURRENT_ID
48 RETURN NUMBER
49 IS
50 BEGIN
51 IF NOT b_proc_state.initialized THEN
52 RAISE NO_DATA_FOUND;
53 END IF;
54
55 RETURN b_proc_state.proc_id;
56 END;
57
58 -- Public
59 FUNCTION GET_CURRENT_TYPE
60 RETURN VARCHAR2
61 IS
62 BEGIN
63 IF NOT b_proc_state.initialized THEN
64 RAISE NO_DATA_FOUND;
65 END IF;
66
67 RETURN b_proc_state.proc_type;
68 END;
69
70 -- Public
71 FUNCTION GET_CURRENT_ERROR_IS_FATAL
72 RETURN VARCHAR2
73 IS
74 BEGIN
75 IF NOT b_proc_state.initialized THEN
76 RAISE NO_DATA_FOUND;
77 END IF;
78
79 RETURN b_proc_state.error_is_fatal_flag;
80 END;
81
82 -- Public
83 FUNCTION GET_CURRENT_LOCATION
84 RETURN VARCHAR2
85 IS
86 BEGIN
87 IF NOT b_proc_state.initialized THEN
88 RAISE NO_DATA_FOUND;
89 END IF;
90
91 RETURN b_proc_state.location;
92 END;
93
94 -- Public
95 FUNCTION GET_CURRENT_EXECUTABLE
96 RETURN VARCHAR2
97 IS
98 BEGIN
99 IF NOT b_proc_state.initialized THEN
100 RAISE NO_DATA_FOUND;
101 END IF;
102
103 RETURN b_proc_state.executable;
104 END;
105
106 -- Private
107 -- Helper to GET_NEXT_PROC/SET_CURRENT_PROC to initialize the package state
108 PROCEDURE INIT_STATE(p_proc_id IN NUMBER,
109 p_proc_type IN VARCHAR2,
110 p_error_is_fatal_flag IN VARCHAR2,
111 p_location IN VARCHAR2,
112 p_executable IN VARCHAR2)
113 IS
114 BEGIN
115 b_proc_state.proc_id := p_proc_id;
116 b_proc_state.proc_type := p_proc_type;
117 b_proc_state.error_is_fatal_flag := p_error_is_fatal_flag;
118 b_proc_state.location := p_location;
119 b_proc_state.executable := p_executable;
120 b_proc_state.initialized := TRUE;
121 END;
122
123 -- Public
124 PROCEDURE GET_NEXT_PROC(p_stage IN VARCHAR2,
125 x_proc_id OUT NOCOPY NUMBER,
126 x_proc_type OUT NOCOPY VARCHAR2,
127 x_error_is_fatal OUT NOCOPY VARCHAR2,
128 x_location OUT NOCOPY VARCHAR2,
129 x_executable OUT NOCOPY VARCHAR2)
130 IS
131 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_NEXT_PROC';
132
133 l_proc_id NUMBER;
134 l_proc_type VARCHAR2(30);
135 l_error_is_fatal_flag VARCHAR2(3);
136 l_location VARCHAR2(2000);
137 l_executable VARCHAR2(2000);
138 BEGIN
139 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
140
141 --make sure the cursor's prep'd
142 IF NOT B_PROCS%ISOPEN THEN
143 OPEN B_PROCS(p_stage);
144 -- and in the right stage
145 ELSIF p_stage IS NULL OR p_stage <> b_last_fetched_stage THEN
146 IF B_PROCS%ISOPEN THEN
147 CLOSE B_PROCS;
148 END IF;
149 OPEN B_PROCS(p_stage);
150 b_last_fetched_stage := p_stage;
151 END IF;
152
153 --get the next row
154 FETCH B_PROCS INTO l_proc_id, l_proc_type, l_error_is_fatal_flag, l_location, l_executable;
155
156 --no more rows left, invalidate the state and return nulls
157 IF B_PROCS%NOTFOUND THEN
158 b_proc_state.initialized := FALSE;
159 RAISE NO_DATA_FOUND;
160 END IF;
161
162 --got a row, set the state and return it
163 INIT_STATE(l_proc_id,
164 l_proc_type,
165 l_error_is_fatal_flag,
166 l_location,
167 l_executable);
168 x_proc_id := l_proc_id;
169 x_proc_type := l_proc_type;
170 x_error_is_fatal := l_error_is_fatal_flag;
171 x_location := l_location;
172 x_executable := l_executable;
173 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 x_proc_id := NULL;
177 x_proc_type := NULL;
178 x_error_is_fatal := NULL;
179 x_location := NULL;
180 x_executable := NULL;
181 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
182 RETURN;
183 WHEN OTHERS THEN
184 x_proc_id := NULL;
185 x_proc_type := NULL;
186 x_error_is_fatal := NULL;
187 x_location := NULL;
188 x_executable := NULL;
189 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
190 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
191 RAISE;
192 END;
193
194 -- Public
195 PROCEDURE SET_CURRENT_PROC(p_proc_id IN NUMBER)
196 IS
197 l_ctxt VARCHAR2(60) := PKG_NAME||'SET_CURRENT_PROC';
198
199 l_proc_id NUMBER;
200 l_proc_type VARCHAR2(30);
201 l_error_is_fatal_flag VARCHAR2(3);
202 l_location VARCHAR2(2000);
203 l_executable VARCHAR2(2000);
204 BEGIN
205 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
206
207 --query out the import proc attributes
208 SELECT proc_id, proc_type, error_is_fatal_flag, location, executable
209 INTO l_proc_id, l_proc_type, l_error_is_fatal_flag, l_location, l_executable
210 FROM fnd_oam_dscfg_procs
211 WHERE proc_id = p_proc_id
212 AND SYSDATE BETWEEN NVL(START_DATE, SYSDATE) and NVL(END_DATE, SYSDATE);
213
214 --set the state
215 INIT_STATE(l_proc_id,
216 l_proc_type,
217 l_error_is_fatal_flag,
218 l_location,
219 l_executable);
220
221 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
222 EXCEPTION
223 WHEN NO_DATA_FOUND THEN
224 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
225 RAISE;
226 WHEN OTHERS THEN
227 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
228 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
229 RAISE;
230 END;
231
232
233 --PROCEDURES REQUIRED BY FNDLOADER
234
235 procedure LOAD_ROW (
236 X_PROC_ID in NUMBER,
237 X_PROC_TYPE IN VARCHAR2,
238 X_STAGE IN VARCHAR2,
239 X_START_DATE IN DATE,
240 X_END_DATE IN DATE,
241 X_PRIORITY IN NUMBER,
242 X_ERROR_IS_FATAL_FLAG IN VARCHAR2,
243 X_LOCATION IN VARCHAR2,
244 X_EXECUTABLE IN VARCHAR2,
245 X_OWNER in VARCHAR2,
246 x_custom_mode in varchar2,
247 X_LAST_UPDATE_DATE in varchar2)
248 is
249 mproc_id number;
250 row_id varchar2(64);
251 f_luby number; -- entity owner in file
252 f_ludate date; -- entity update date in file
253 db_luby number; -- entity owner in db
254 db_ludate date; -- entity update date in db
255 begin
256
257 -- Translate owner to file_last_updated_by
258 f_luby := fnd_load_util.owner_id(x_owner);
259
260 -- Translate char last_update_date to date
261 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
262
263 begin
264 -- check if this PROCS id already exists.
265 select proc_id, LAST_UPDATED_BY, LAST_UPDATE_DATE
266 into mproc_id, db_luby, db_ludate
267 from fnd_oam_dscfg_procs
268 where proc_id = to_number(X_PROC_ID);
269
270 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
271 db_ludate, X_CUSTOM_MODE)) then
272 FND_OAM_DSCFG_PROCS_PKG.UPDATE_ROW (
273 X_PROC_ID => mproc_id,
274 X_PROC_TYPE => X_PROC_TYPE,
275 X_STAGE => X_STAGE,
276 X_START_DATE => X_START_DATE,
277 X_END_DATE => X_END_DATE,
278 X_PRIORITY => X_PRIORITY,
279 X_ERROR_IS_FATAL_FLAG => X_ERROR_IS_FATAL_FLAG,
280 X_LOCATION => X_LOCATION,
281 X_EXECUTABLE => X_EXECUTABLE,
282 X_LAST_UPDATED_BY => f_luby,
283 X_LAST_UPDATE_DATE => f_ludate,
284 X_LAST_UPDATE_LOGIN => 0 );
285
286 end if;
287 exception
288 when NO_DATA_FOUND then
289
290 FND_OAM_DSCFG_PROCS_PKG.INSERT_ROW (
291 X_ROWID => row_id,
292 X_PROC_ID => X_PROC_ID,
293 X_PROC_TYPE => X_PROC_TYPE,
294 X_STAGE => X_STAGE,
295 X_START_DATE => X_START_DATE,
296 X_END_DATE => X_END_DATE,
297 X_PRIORITY => X_PRIORITY,
298 X_ERROR_IS_FATAL_FLAG => X_ERROR_IS_FATAL_FLAG,
299 X_LOCATION => X_LOCATION,
300 X_EXECUTABLE => X_EXECUTABLE,
301 X_CREATION_DATE => f_ludate,
302 X_CREATED_BY => f_luby,
303 X_LAST_UPDATE_DATE => f_ludate,
304 X_LAST_UPDATED_BY => f_luby,
305 X_LAST_UPDATE_LOGIN => 0 );
306 end;
307
308 end LOAD_ROW;
309
310 --INSERT ROW
311 procedure INSERT_ROW (
312 X_ROWID in out nocopy VARCHAR2,
313 X_PROC_ID in NUMBER,
314 X_PROC_TYPE IN VARCHAR2,
315 X_STAGE IN VARCHAR2,
316 X_START_DATE IN DATE,
317 X_END_DATE IN DATE,
318 X_PRIORITY IN NUMBER,
319 X_ERROR_IS_FATAL_FLAG IN VARCHAR2,
320 X_LOCATION IN VARCHAR2,
321 X_EXECUTABLE IN VARCHAR2,
322 X_CREATED_BY in NUMBER,
323 X_CREATION_DATE in DATE,
324 X_LAST_UPDATED_BY in NUMBER,
325 X_LAST_UPDATE_DATE in DATE,
326 X_LAST_UPDATE_LOGIN in NUMBER)
327 is
328 cursor C is select ROWID from FND_OAM_DSCFG_PROCS
329 where PROC_ID = X_PROC_ID;
330 begin
331 insert into FND_OAM_DSCFG_PROCS (
332 PROC_ID,
333 PROC_TYPE,
334 STAGE,
335 START_DATE,
336 END_DATE,
337 PRIORITY,
338 ERROR_IS_FATAL_FLAG,
339 LOCATION,
340 EXECUTABLE,
341 CREATED_BY,
342 CREATION_DATE,
343 LAST_UPDATED_BY,
344 LAST_UPDATE_DATE,
345 LAST_UPDATE_LOGIN
346 ) values (
347 X_PROC_ID,
348 X_PROC_TYPE,
349 X_STAGE,
350 X_START_DATE,
351 X_END_DATE,
352 X_PRIORITY,
353 X_ERROR_IS_FATAL_FLAG,
354 X_LOCATION,
355 X_EXECUTABLE,
356 X_CREATED_BY,
357 X_CREATION_DATE,
358 X_LAST_UPDATED_BY,
359 X_LAST_UPDATE_DATE,
360 X_LAST_UPDATE_LOGIN
361 );
362
363 open c;
364 fetch c into X_ROWID;
365 if (c%notfound) then
366 close c;
367 raise no_data_found;
368 end if;
369 close c;
370
371 end INSERT_ROW;
372
373 --LOCK ROW
374
375 procedure LOCK_ROW (
376 X_ROWID in out nocopy VARCHAR2,
377 X_PROC_ID in NUMBER,
378 X_PROC_TYPE IN VARCHAR2,
379 X_STAGE IN VARCHAR2,
380 X_START_DATE IN DATE,
381 X_END_DATE IN DATE,
382 X_PRIORITY IN NUMBER,
383 X_ERROR_IS_FATAL_FLAG IN VARCHAR2,
384 X_LOCATION IN VARCHAR2,
385 X_EXECUTABLE IN VARCHAR2,
386 X_CREATED_BY in NUMBER,
387 X_CREATION_DATE in DATE,
388 X_LAST_UPDATED_BY in NUMBER,
389 X_LAST_UPDATE_DATE in DATE,
390 X_LAST_UPDATE_LOGIN in NUMBER
391 ) is
392 cursor c is select
393 PROC_ID,
394 PROC_TYPE,
395 STAGE,
396 START_DATE,
397 END_DATE,
398 PRIORITY,
399 ERROR_IS_FATAL_FLAG,
400 LOCATION,
401 EXECUTABLE,
402 CREATED_BY,
403 CREATION_DATE,
404 LAST_UPDATED_BY,
405 LAST_UPDATE_DATE,
406 LAST_UPDATE_LOGIN
407 from FND_OAM_DSCFG_PROCS
408 where PROC_ID = X_PROC_ID
409 for update of PROC_ID nowait;
410 recinfo c%rowtype;
411
412 begin
413 open c;
414 fetch c into recinfo;
415 if (c%notfound) then
416 close c;
417 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
418 app_exception.raise_exception;
419 end if;
420 close c;
421 if ( ((recinfo.START_DATE = X_START_DATE)
422 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
423 AND ((recinfo.END_DATE = X_END_DATE)
424 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
425 AND ((recinfo.PROC_TYPE = X_PROC_TYPE)
426 OR ((recinfo.PROC_TYPE is null) AND (X_PROC_TYPE is null)))
427 AND ((recinfo.STAGE = X_STAGE)
428 OR ((recinfo.STAGE is null) AND (X_STAGE is null)))
429 AND ((recinfo.PRIORITY = X_PRIORITY)
430 OR ((recinfo.PRIORITY is null) AND (X_PRIORITY is null)))
431 AND ((recinfo.ERROR_IS_FATAL_FLAG = X_ERROR_IS_FATAL_FLAG)
432 OR ((recinfo.ERROR_IS_FATAL_FLAG is null) AND (X_ERROR_IS_FATAL_FLAG is null)))
433 AND ((recinfo.LOCATION = X_LOCATION)
434 OR ((recinfo.LOCATION is null) AND (X_LOCATION is null)))
435 AND ((recinfo.EXECUTABLE = X_EXECUTABLE)
436 OR ((recinfo.EXECUTABLE is null) AND (X_EXECUTABLE is null)))
437 ) then
438 null;
439 else
440 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
441 app_exception.raise_exception;
442 end if;
443
444 return;
445 end LOCK_ROW;
446
447 --UPDATE ROW
448
449 procedure UPDATE_ROW (
450 X_PROC_ID in NUMBER,
451 X_PROC_TYPE IN VARCHAR2,
452 X_STAGE IN VARCHAR2,
453 X_START_DATE IN DATE,
454 X_END_DATE IN DATE,
455 X_PRIORITY IN NUMBER,
456 X_ERROR_IS_FATAL_FLAG IN VARCHAR2,
457 X_LOCATION IN VARCHAR2,
458 X_EXECUTABLE IN VARCHAR2,
459 X_LAST_UPDATED_BY in NUMBER,
460 X_LAST_UPDATE_DATE in DATE,
461 X_LAST_UPDATE_LOGIN in NUMBER
462 ) is
463 begin
464 update FND_OAM_DSCFG_PROCS set
465 PROC_TYPE = X_PROC_TYPE,
466 STAGE = X_STAGE,
467 START_DATE = X_START_DATE,
468 END_DATE = X_END_DATE,
469 PRIORITY = X_PRIORITY,
470 ERROR_IS_FATAL_FLAG = X_ERROR_IS_FATAL_FLAG,
471 LOCATION = X_LOCATION,
472 EXECUTABLE = X_EXECUTABLE,
473 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
474 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
475 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
476 where PROC_ID = X_PROC_ID;
477
478 if (sql%notfound) then
479 raise no_data_found;
480 end if;
481
482 end UPDATE_ROW;
483
484 procedure DELETE_ROW (
485 X_PROC_ID in NUMBER
486 ) is
487 begin
488 delete from FND_OAM_DSCFG_PROCS
489 where PROC_ID = X_PROC_ID;
490
491 if (sql%notfound) then
492 raise no_data_found;
493 end if;
494
495 end DELETE_ROW;
496
497
498
499 END FND_OAM_DSCFG_PROCS_PKG;