[Home] [Help]
PACKAGE BODY: APPS.OKE_APPROVAL_PATHS_PKG
Source
1 PACKAGE BODY OKE_APPROVAL_PATHS_PKG AS
2 /* $Header: OKEAPVPB.pls 120.1 2005/05/27 16:02:31 appldev $ */
3 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_approval_paths_pkg.';
4 --
5 -- Table Handler Procedures
6 --
7 PROCEDURE INSERT_ROW
8 ( X_ROWID IN OUT NOCOPY VARCHAR2
9 , X_APPROVAL_PATH_ID IN OUT NOCOPY NUMBER
10 , X_SIGNATURE_REQUIRED_FLAG IN VARCHAR2
11 , X_SIGNATORY_ROLE_ID IN NUMBER
12 , X_NAME IN VARCHAR2
13 , X_DESCRIPTION IN VARCHAR2
14 , X_START_DATE_ACTIVE IN DATE
15 , X_END_DATE_ACTIVE IN DATE
16 , X_CREATION_DATE IN DATE
17 , X_CREATED_BY IN NUMBER
18 , X_LAST_UPDATE_DATE IN DATE
19 , X_LAST_UPDATED_BY IN NUMBER
20 , X_LAST_UPDATE_LOGIN IN NUMBER
21 , X_RECORD_VERSION_NUMBER IN OUT NOCOPY NUMBER
22 ) IS
23
24 CURSOR c IS
25 SELECT ROWID
26 FROM OKE_APPROVAL_PATHS
27 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID;
28
29 BEGIN
30
31 IF ( X_APPROVAL_PATH_ID IS NULL ) THEN
32 SELECT OKE_APPROVAL_PATHS_S.NEXTVAL
33 INTO X_APPROVAL_PATH_ID
34 FROM DUAL;
35 END IF;
36
37 X_RECORD_VERSION_NUMBER := 1;
38
39 INSERT INTO OKE_APPROVAL_PATHS
40 ( APPROVAL_PATH_ID
41 , SIGNATURE_REQUIRED_FLAG
42 , SIGNATORY_ROLE_ID
43 , RECORD_VERSION_NUMBER
44 , START_DATE_ACTIVE
45 , END_DATE_ACTIVE
46 , CREATION_DATE
47 , CREATED_BY
48 , LAST_UPDATE_DATE
49 , LAST_UPDATED_BY
50 , LAST_UPDATE_LOGIN
51 ) VALUES
52 ( X_APPROVAL_PATH_ID
53 , X_SIGNATURE_REQUIRED_FLAG
54 , X_SIGNATORY_ROLE_ID
55 , X_RECORD_VERSION_NUMBER
56 , X_START_DATE_ACTIVE
57 , X_END_DATE_ACTIVE
58 , X_CREATION_DATE
59 , X_CREATED_BY
60 , X_LAST_UPDATE_DATE
61 , X_LAST_UPDATED_BY
62 , X_LAST_UPDATE_LOGIN
63 );
64
65 INSERT INTO OKE_APPROVAL_PATHS_TL
66 ( APPROVAL_PATH_ID
67 , CREATION_DATE
68 , CREATED_BY
69 , LAST_UPDATE_DATE
70 , LAST_UPDATED_BY
71 , LAST_UPDATE_LOGIN
72 , NAME
73 , DESCRIPTION
74 , LANGUAGE
75 , SOURCE_LANG
76 )
77 SELECT X_APPROVAL_PATH_ID
78 , X_CREATION_DATE
79 , X_CREATED_BY
80 , X_LAST_UPDATE_DATE
81 , X_LAST_UPDATED_BY
82 , X_LAST_UPDATE_LOGIN
83 , X_NAME
84 , X_DESCRIPTION
85 , L.LANGUAGE_CODE
86 , USERENV('LANG')
87 FROM FND_LANGUAGES L
88 WHERE L.INSTALLED_FLAG IN ('I', 'B')
89 AND NOT EXISTS
90 (SELECT NULL
91 FROM OKE_APPROVAL_PATHS_TL T
92 WHERE T.APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
93 AND T.LANGUAGE = L.LANGUAGE_CODE);
94
95 OPEN c;
96 FETCH c INTO X_ROWID;
97 IF (c%notfound) THEN
98 CLOSE c;
99 RAISE NO_DATA_FOUND;
100 END IF;
101 CLOSE c;
102
103 END INSERT_ROW;
104
105
106 PROCEDURE LOCK_ROW
107 ( X_APPROVAL_PATH_ID IN NUMBER
108 , X_RECORD_VERSION_NUMBER IN NUMBER
109 ) IS
110
111 CURSOR C IS
112 SELECT RECORD_VERSION_NUMBER
113 FROM OKE_APPROVAL_PATHS
114 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
115 FOR UPDATE OF APPROVAL_PATH_ID NOWAIT;
116 RecInfo c%rowtype;
117
118 BEGIN
119 OPEN C;
120 FETCH C INTO RecInfo;
121 IF (c%notfound) THEN
122 CLOSE c;
123 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
124 APP_EXCEPTION.RAISE_EXCEPTION;
125 END IF;
126 CLOSE c;
127 IF (RecInfo.RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER) THEN
128 NULL;
129 ELSE
130 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
131 APP_EXCEPTION.RAISE_EXCEPTION;
132 END IF;
133
134 RETURN;
135
136 END LOCK_ROW;
137
138
139 PROCEDURE UPDATE_ROW
140 ( X_APPROVAL_PATH_ID IN NUMBER
141 , X_SIGNATURE_REQUIRED_FLAG IN VARCHAR2
142 , X_SIGNATORY_ROLE_ID IN NUMBER
143 , X_NAME IN VARCHAR2
144 , X_DESCRIPTION IN VARCHAR2
145 , X_START_DATE_ACTIVE IN DATE
146 , X_END_DATE_ACTIVE IN DATE
147 , X_LAST_UPDATE_DATE IN DATE
148 , X_LAST_UPDATED_BY IN NUMBER
149 , X_LAST_UPDATE_LOGIN IN NUMBER
150 , X_RECORD_VERSION_NUMBER OUT NOCOPY NUMBER
151 ) IS
152
153 CURSOR c IS
154 SELECT RECORD_VERSION_NUMBER
155 FROM OKE_APPROVAL_PATHS
156 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID;
157
158 BEGIN
159
160 UPDATE OKE_APPROVAL_PATHS
161 SET SIGNATURE_REQUIRED_FLAG = X_SIGNATURE_REQUIRED_FLAG
162 , SIGNATORY_ROLE_ID = X_SIGNATORY_ROLE_ID
163 , START_DATE_ACTIVE = X_START_DATE_ACTIVE
164 , END_DATE_ACTIVE = X_END_DATE_ACTIVE
165 , LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
166 , LAST_UPDATED_BY = X_LAST_UPDATED_BY
167 , LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
168 , RECORD_VERSION_NUMBER = RECORD_VERSION_NUMBER + 1
169 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID;
170
171 IF (sql%notfound) THEN
172 RAISE NO_DATA_FOUND;
173 END IF;
174
175 OPEN c;
176 FETCH c INTO X_RECORD_VERSION_NUMBER;
177 IF (c%notfound) THEN
178 CLOSE c;
179 RAISE NO_DATA_FOUND;
180 END IF;
181 CLOSE c;
182
183 UPDATE OKE_APPROVAL_PATHS_TL
184 SET NAME = X_NAME
185 , DESCRIPTION = X_DESCRIPTION
186 , LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
187 , LAST_UPDATED_BY = X_LAST_UPDATED_BY
188 , LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
189 , SOURCE_LANG = USERENV('LANG')
190 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
191 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
192
193 IF (sql%notfound) THEN
194 RAISE NO_DATA_FOUND;
195 END IF;
196
197 END UPDATE_ROW;
198
199
200 PROCEDURE DELETE_ROW
201 ( X_APPROVAL_PATH_ID IN NUMBER
202 ) IS
203 BEGIN
204
205 DELETE FROM OKE_APPROVAL_PATHS_TL
206 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID;
207
208 IF (sql%notfound) THEN
209 RAISE NO_DATA_FOUND;
210 END IF;
211
212 DELETE FROM OKE_APPROVAL_PATHS
213 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID;
214
215 IF (sql%notfound) THEN
216 RAISE NO_DATA_FOUND;
217 END IF;
218
219 END DELETE_ROW;
220
221
222 PROCEDURE LOAD_ROW
223 ( X_APPROVAL_PATH_ID IN NUMBER
224 , X_NAME IN VARCHAR2
225 , X_DESCRIPTION IN VARCHAR2
226 , X_SIGNATURE_REQUIRED_FLAG IN VARCHAR2
227 , X_SIGNATORY_ROLE_ID IN NUMBER
228 , X_START_DATE_ACTIVE IN DATE
229 , X_END_DATE_ACTIVE IN DATE
230 , X_LAST_UPDATE_DATE IN DATE
231 , X_LAST_UPDATED_BY IN NUMBER
232 , X_CUSTOM_MODE IN VARCHAR2
233 ) IS
234
235 l_approval_path_id NUMBER := X_APPROVAL_PATH_ID;
236 l_rowid VARCHAR2(30);
237 l_record_version_number NUMBER;
238
239 db_luby NUMBER; -- entity owner in db
240 db_ludate DATE; -- entity update date in db
241
242 BEGIN
243
244 BEGIN
245
246 SELECT LAST_UPDATE_DATE , LAST_UPDATED_BY
247 INTO db_ludate , db_luby
248 FROM OKE_APPROVAL_PATHS
249 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID;
250
251 --
252 -- Update record, honoring customization mode.
253 -- Record should be updated only if:
254 -- a. CUSTOM_MODE = FORCE, or
255 -- b. file owner is USER, db owner is ORACLE
256 -- c. owners are the same, and file_date > db_date
257 --
258 IF ( FND_LOAD_UTIL.UPLOAD_TEST
259 ( X_LAST_UPDATED_BY
260 , X_LAST_UPDATE_DATE
261 , db_luby
262 , db_ludate
263 , X_CUSTOM_MODE ) ) THEN
264
265 UPDATE_ROW
266 ( X_APPROVAL_PATH_ID => l_approval_path_id
267 , X_SIGNATURE_REQUIRED_FLAG => X_SIGNATURE_REQUIRED_FLAG
268 , X_SIGNATORY_ROLE_ID => X_SIGNATORY_ROLE_ID
269 , X_NAME => X_NAME
270 , X_DESCRIPTION => X_DESCRIPTION
271 , X_START_DATE_ACTIVE => X_START_DATE_ACTIVE
272 , X_END_DATE_ACTIVE => X_END_DATE_ACTIVE
273 , X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
274 , X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
275 , X_LAST_UPDATE_LOGIN => NULL
276 , X_RECORD_VERSION_NUMBER => l_record_version_number
277 );
278
279 END IF;
280
281 EXCEPTION
282 WHEN NO_DATA_FOUND THEN
283 -- Record doesn't exist - insert in all cases
284 INSERT_ROW
285 ( X_ROWID => l_rowid
286 , X_APPROVAL_PATH_ID => l_approval_path_id
287 , X_SIGNATURE_REQUIRED_FLAG => X_SIGNATURE_REQUIRED_FLAG
288 , X_SIGNATORY_ROLE_ID => X_SIGNATORY_ROLE_ID
289 , X_NAME => X_NAME
290 , X_DESCRIPTION => X_DESCRIPTION
291 , X_START_DATE_ACTIVE => X_START_DATE_ACTIVE
292 , X_END_DATE_ACTIVE => X_END_DATE_ACTIVE
293 , X_CREATION_DATE => X_LAST_UPDATE_DATE
294 , X_CREATED_BY => X_LAST_UPDATED_BY
295 , X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
296 , X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
297 , X_LAST_UPDATE_LOGIN => NULL
298 , X_RECORD_VERSION_NUMBER => l_record_version_number
299 );
300 END;
301
302 --
303 -- If CUSTOM_MODE is FORCE, tramp all approval steps
304 --
305 IF ( X_CUSTOM_MODE = 'FORCE' ) THEN
306 DELETE FROM OKE_APPROVAL_STEPS
307 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID;
308 END IF;
309
310 END LOAD_ROW;
311
312
313 PROCEDURE TRANSLATE_ROW
314 ( X_APPROVAL_PATH_ID IN NUMBER
315 , X_NAME IN VARCHAR2
316 , X_DESCRIPTION IN VARCHAR2
317 , X_LAST_UPDATE_DATE IN DATE
318 , X_LAST_UPDATED_BY IN NUMBER
319 , X_CUSTOM_MODE IN VARCHAR2
320 ) IS
321
322 db_luby NUMBER; -- entity owner in db
323 db_ludate DATE; -- entity update date in db
324
325 BEGIN
326
327 SELECT LAST_UPDATE_DATE , LAST_UPDATED_BY
328 INTO db_ludate , db_luby
329 FROM OKE_APPROVAL_PATHS_TL
330 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
331 AND USERENV('LANG') = LANGUAGE;
332
333 --
334 -- Update record, honoring customization mode.
335 -- Record should be updated only if:
336 -- a. CUSTOM_MODE = FORCE, or
337 -- b. file owner is USER, db owner is ORACLE
338 -- c. owners are the same, and file_date > db_date
339 --
340 IF ( FND_LOAD_UTIL.UPLOAD_TEST
341 ( X_LAST_UPDATED_BY
342 , X_LAST_UPDATE_DATE
343 , db_luby
344 , db_ludate
345 , X_CUSTOM_MODE ) ) THEN
346
347 UPDATE OKE_APPROVAL_PATHS_TL
348 SET NAME = X_NAME
349 , DESCRIPTION = X_DESCRIPTION
350 , LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
351 , LAST_UPDATED_BY = X_LAST_UPDATED_BY
352 , SOURCE_LANG = USERENV('LANG')
353 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
354 AND USERENV('LANG') IN ( LANGUAGE , SOURCE_LANG );
355
356 END IF;
357
358 END TRANSLATE_ROW;
359
360
361 PROCEDURE ADD_LANGUAGE
362 IS
363 BEGIN
364
365 DELETE FROM OKE_APPROVAL_PATHS_TL T
366 WHERE NOT EXISTS (
367 SELECT NULL
368 FROM OKE_APPROVAL_PATHS B
369 WHERE B.APPROVAL_PATH_ID = T.APPROVAL_PATH_ID
370 );
371
372 UPDATE OKE_APPROVAL_PATHS_TL T SET
373 ( NAME , DESCRIPTION ) = (
374 SELECT B.NAME
375 , B.DESCRIPTION
376 FROM OKE_APPROVAL_PATHS_TL B
377 WHERE B.APPROVAL_PATH_ID = T.APPROVAL_PATH_ID
378 AND B.LANGUAGE = T.SOURCE_LANG)
379 WHERE ( T.APPROVAL_PATH_ID , T.LANGUAGE ) IN (
380 SELECT SUBT.APPROVAL_PATH_ID
381 , SUBT.LANGUAGE
382 FROM OKE_APPROVAL_PATHS_TL SUBB
383 , OKE_APPROVAL_PATHS_TL SUBT
384 WHERE SUBB.APPROVAL_PATH_ID = SUBT.APPROVAL_PATH_ID
385 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
386 AND (SUBB.NAME <> SUBT.NAME
387 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION )
388 );
389
390 INSERT INTO OKE_APPROVAL_PATHS_TL
391 ( APPROVAL_PATH_ID
392 , CREATION_DATE
393 , CREATED_BY
394 , LAST_UPDATE_DATE
395 , LAST_UPDATED_BY
396 , LAST_UPDATE_LOGIN
397 , NAME
398 , DESCRIPTION
399 , LANGUAGE
400 , SOURCE_LANG
401 )
402 SELECT B.APPROVAL_PATH_ID
403 , B.CREATION_DATE
404 , B.CREATED_BY
405 , B.LAST_UPDATE_DATE
406 , B.LAST_UPDATED_BY
407 , B.LAST_UPDATE_LOGIN
408 , B.NAME
409 , B.DESCRIPTION
410 , L.LANGUAGE_CODE
411 , B.SOURCE_LANG
412 FROM OKE_APPROVAL_PATHS_TL B, FND_LANGUAGES L
413 WHERE L.INSTALLED_FLAG IN ('I', 'B')
414 AND B.LANGUAGE = USERENV('LANG')
415 AND NOT EXISTS (
416 SELECT NULL
417 FROM OKE_APPROVAL_PATHS_TL T
418 WHERE T.APPROVAL_PATH_ID = B.APPROVAL_PATH_ID
419 AND T.LANGUAGE = L.LANGUAGE_CODE
420 );
421
422 END ADD_LANGUAGE;
423
424
425 --
426 -- Utility Functions and Procedures
427 --
428
429 --
430 -- Approval_Steps returns a semi-colon separated list that is used
431 -- to cache the approval hierarchy in the beginnin of the aproval
432 -- process. The cached hierarchy should be stored as an WF item
433 -- attribute and can be interpreted by the Next_Approval_Step()
434 -- procedure
435 --
436 FUNCTION Approval_Steps
437 ( ApprovalPath IN NUMBER
438 ) RETURN VARCHAR2 IS
439
440 CURSOR a IS
441 SELECT approval_sequence || ',' || approver_role_id || ';' approval_step
442 FROM oke_approval_steps
443 WHERE approval_path_id = ApprovalPath
444 ORDER BY approval_sequence ASC;
445 arec a%rowtype;
446
447 steps VARCHAR2(4000);
448
449 BEGIN
450
451 steps := ';';
452 FOR arec IN a LOOP
453 steps := steps || arec.approval_step;
454 END LOOP;
455 RETURN ( steps );
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 RETURN ( ';' );
460
461 END Approval_Steps;
462
463
464 --
465 -- Next_Approval_Step returns the next approval step based on the
466 -- approval steps cached at the beginning of the approval process
467 --
468 PROCEDURE Next_Approval_Step
469 ( ApprovalSteps IN VARCHAR2
470 , LastApprovalSeq IN NUMBER
471 , ApprovalSeq OUT NOCOPY NUMBER
472 , ApproverRole OUT NOCOPY NUMBER
473 ) IS
474 l_api_name CONSTANT VARCHAR2(30) := 'Next_Approval_Step';
475 i NUMBER;
476 s NUMBER;
477 e NUMBER;
478 NextStep VARCHAR2(30);
479
480 BEGIN
481
482 IF ( LastApprovalSeq = 0 ) THEN
483 s := 2;
484 e := instr( substr( ApprovalSteps , s , 4000 ) , ';' ) - 1;
485 ELSE
486 i := instr( ApprovalSteps , ';' || LastApprovalSeq || ',' );
487 s := instr( substr( ApprovalSteps , i+1 , 4000 ) , ';' ) + i + 1;
488 e := instr( substr( ApprovalSteps , s , 4000 ) , ';' ) - 1;
489 END IF;
490
491 NextStep := substr( ApprovalSteps , s , e );
492
493 ApprovalSeq := to_number( substr( NextStep , 1 , instr( NextStep , ',' ) - 1) );
494 ApproverRole := to_number( substr( NextStep , instr( NextStep , ',' ) + 1
495 , length( NextStep ) - 2 ) );
496
497 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
498 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module||l_api_name,'ApprovalSteps = ' || ApprovalSteps);
499 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module||l_api_name,'i = ' || i);
500 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module||l_api_name,'s = ' || s);
501 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module||l_api_name,'e = ' || e);
502 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module||l_api_name,'Next Step = ' || NextStep);
503 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module||l_api_name,'Next Seq = ' || ApprovalSeq);
504 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,g_module||l_api_name,'Next Role = ' || ApproverRole);
505 END IF;
506
507 END Next_Approval_Step;
508
509
510 END OKE_APPROVAL_PATHS_PKG;