1 package BODY AME_SEED_UTILITY as
2 /* $Header: ameseedutility.pkb 120.5 2008/04/11 05:29:46 prasashe noship $ */
3 procedure INIT_AME_INSTALLATION_LEVEL as
4 begin
5 AME_INSTALLATION_LEVEL := FND_PROFILE.VALUE('AME_INSTALLATION_LEVEL');
6 end INIT_AME_INSTALLATION_LEVEL;
7
8 function OWNER_AS_STRING
9 (X_LAST_UPDATED_BY in number
10 ) return varchar2 as
11 begin
12 return FND_LOAD_UTIL.OWNER_NAME(X_LAST_UPDATED_BY);
13 end OWNER_AS_STRING;
14
15 function OWNER_AS_INTEGER
16 (X_LAST_UPDATED_BY in varchar2
17 ) return number as
18 begin
19 return FND_LOAD_UTIL.OWNER_ID(X_LAST_UPDATED_BY);
20 end OWNER_AS_INTEGER;
21
22 function DATE_AS_STRING
23 (X_LAST_UPDATE_DATE in date
24 ) return varchar2 as
25 begin
26 return to_char(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS');
27 end DATE_AS_STRING;
28
29 function DATE_AS_DATE
30 (X_LAST_UPDATE_DATE in varchar2
31 ) return date as
32 begin
33 return to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS');
34 end DATE_AS_DATE;
35
36 function IS_SEED_USER
37 (X_USER in VARCHAR2
38 ) return boolean as
39 L_USER_ID INTEGER;
40 begin
41 L_USER_ID := FND_LOAD_UTIL.OWNER_ID(X_USER);
42 if L_USER_ID in (1,2,120,121) then
43 return true;
44 else
45 return false;
46 end if;
47 end IS_SEED_USER;
48
49 function SEED_USER_ID return integer as
50 begin
51 return 1;
52 end SEED_USER_ID;
53
54 function SEED_USER_NAME return varchar2 as
55 begin
56 return FND_LOAD_UTIL.OWNER_NAME(1);
57 end SEED_USER_NAME;
58
59 function MERGE_ROW_TEST
60 (X_CURRENT_OWNER in number
61 ,X_CURRENT_LAST_UPDATE_DATE in varchar2
62 ,X_OWNER in number
63 ,X_LAST_UPDATE_DATE in varchar2
64 ,X_CUSTOM_MODE in varchar2
65 ) return boolean as
66 begin
67 if X_CUSTOM_MODE = 'FORCE' then
68 return true;
69 end if;
70 if X_LAST_UPDATE_DATE = X_CURRENT_LAST_UPDATE_DATE then
71 return false;
72 end if;
73 return FND_LOAD_UTIL.UPLOAD_TEST
74 (P_FILE_ID => X_OWNER
75 ,P_FILE_LUD => DATE_AS_DATE (X_LAST_UPDATE_DATE)
76 ,P_DB_ID => X_CURRENT_OWNER
77 ,P_DB_LUD => DATE_AS_DATE (X_CURRENT_LAST_UPDATE_DATE)
78 ,P_CUSTOM_MODE => X_CUSTOM_MODE
79 );
80 end MERGE_ROW_TEST;
81
82 function TL_MERGE_ROW_TEST
83 (X_CURRENT_OWNER in number
84 ,X_CURRENT_LAST_UPDATE_DATE in varchar2
85 ,X_OWNER in number
86 ,X_LAST_UPDATE_DATE in varchar2
87 ,X_CUSTOM_MODE in varchar2
88 ) return boolean as
89 begin
90 return FND_LOAD_UTIL.UPLOAD_TEST
91 (P_FILE_ID => X_OWNER
92 ,P_FILE_LUD => DATE_AS_DATE (X_LAST_UPDATE_DATE)
93 ,P_DB_ID => X_CURRENT_OWNER
94 ,P_DB_LUD => DATE_AS_DATE (X_CURRENT_LAST_UPDATE_DATE)
95 ,P_CUSTOM_MODE => X_CUSTOM_MODE
96 );
97 end TL_MERGE_ROW_TEST;
98
99 function GET_DEFAULT_END_DATE return date as
100 begin
101 INIT_AME_INSTALLATION_LEVEL;
102 if AME_INSTALLATION_LEVEL is not null and to_number(AME_INSTALLATION_LEVEL) >= 2 then
103 return END_OF_TIME;
104 else
105 return null;
106 end if;
107 end GET_DEFAULT_END_DATE;
108
109 function CALCULATE_USE_COUNT(X_ATTRIBUTE_ID ame_attribute_usages.attribute_id%type
110 ,X_APPLICATION_ID ame_attribute_usages.application_id%type) return integer as
111 cursor RULE_CURSOR(X_APPLICATION_ID in integer) is
112 select AME_RULE_USAGES.RULE_ID, AME_RULES.ACTION_ID
113 from AME_RULES, AME_RULE_USAGES
114 where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
115 and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
116 and ((sysdate between AME_RULES.START_DATE
117 and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
118 or (sysdate < AME_RULES.START_DATE
119 and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
120 AME_RULES.START_DATE + (1/86400))))
121 and ((sysdate between AME_RULE_USAGES.START_DATE
122 and nvl(AME_RULE_USAGES.END_DATE - (1/86400), sysdate))
123 or (sysdate < AME_RULE_USAGES.START_DATE
124 and AME_RULE_USAGES.START_DATE < nvl(AME_RULE_USAGES.END_DATE,
125 AME_RULE_USAGES.START_DATE + (1/86400))));
126 RULE_COUNT integer;
127 TEMP_COUNT integer;
128 NEW_USE_COUNT integer;
129 begin
130 NEW_USE_COUNT := 0;
131 for TEMPRULE in RULE_CURSOR(X_APPLICATION_ID => X_APPLICATION_ID) loop
132 select count(*)
133 into TEMP_COUNT
134 from AME_CONDITIONS,
135 AME_CONDITION_USAGES
136 where
137 AME_CONDITIONS.ATTRIBUTE_ID = X_ATTRIBUTE_ID and
138 AME_CONDITIONS.CONDITION_ID = AME_CONDITION_USAGES.CONDITION_ID and
139 AME_CONDITION_USAGES.RULE_ID = TEMPRULE.RULE_ID and
140 sysdate between AME_CONDITIONS.START_DATE and
141 nvl(AME_CONDITIONS.END_DATE - (1/86400), sysdate) and
142 ((sysdate between AME_CONDITION_USAGES.START_DATE and
143 nvl(AME_CONDITION_USAGES.END_DATE - (1/86400), sysdate)) or
144 (sysdate < AME_CONDITION_USAGES.START_DATE and
145 AME_CONDITION_USAGES.START_DATE < nvl(AME_CONDITION_USAGES.END_DATE,
146 AME_CONDITION_USAGES.START_DATE + (1/86400))));
147 if(TEMP_COUNT > 0) then
148 NEW_USE_COUNT := NEW_USE_COUNT + 1;
149 else
150 if(TEMPRULE.ACTION_ID is null) then
151 -- action_id is already migrated from ame_rules to ame_action_usages
152 select count(*)
153 into TEMP_COUNT
154 from
155 AME_MANDATORY_ATTRIBUTES,
156 AME_ACTIONS,
157 AME_ACTION_USAGES
158 where
159 AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID and
160 AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
161 AME_ACTIONS.ACTION_ID = AME_ACTION_USAGES.ACTION_ID and
162 AME_ACTION_USAGES.RULE_ID = TEMPRULE.RULE_ID and
163 sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE and
164 nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate) and
165 sysdate between AME_ACTIONS.START_DATE and
166 nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate) and
167 ((sysdate between AME_ACTION_USAGES.START_DATE and
168 nvl(AME_ACTION_USAGES.END_DATE - (1/86400), sysdate)) or
169 (sysdate < AME_ACTION_USAGES.START_DATE and
170 AME_ACTION_USAGES.START_DATE < nvl(AME_ACTION_USAGES.END_DATE,
171 AME_ACTION_USAGES.START_DATE + (1/86400))));
172 else
173 select count(*)
174 into TEMP_COUNT
175 from
176 AME_MANDATORY_ATTRIBUTES,
177 AME_ACTIONS,
178 AME_RULES
179 where
180 AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID and
181 AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
182 AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID and
183 AME_RULES.RULE_ID = TEMPRULE.RULE_ID and
184 sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE and
185 nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate) and
186 sysdate between AME_ACTIONS.START_DATE and
187 nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate) and
188 ((sysdate between AME_RULES.START_DATE and
189 nvl(AME_RULES.END_DATE - (1/86400), sysdate)) or
190 (sysdate < AME_RULES.START_DATE and
191 AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
192 AME_RULES.START_DATE + (1/86400))));
193 end if;
194 if(TEMP_COUNT > 0) then
195 NEW_USE_COUNT := NEW_USE_COUNT + 1;
196 end if;
197 end if;
198 end loop;
199 return(NEW_USE_COUNT);
200 exception
201 when others then
202 ame_util.runtimeException('ame_seed_utility',
203 'calculate_use_count',
204 sqlcode,
205 sqlerrm);
206 raise;
207 return(null);
208 end CALCULATE_USE_COUNT;
209
210 function MLS_ENABLED return boolean as
211 begin
212 INIT_AME_INSTALLATION_LEVEL;
213 if AME_INSTALLATION_LEVEL is not null and to_number(AME_INSTALLATION_LEVEL) >= 2 then
214 return true;
215 else
216 return false;
217 end if;
218 end MLS_ENABLED;
219
220 function USER_ID_OF_SEED_USER return integer as
221 begin
222 return FND_LOAD_UTIL.OWNER_ID(P_NAME => SEED_USER_NAME);
223 end;
224
225 procedure CHANGE_ATTRIBUTE_USAGES_COUNT(X_RULE_ID ame_rule_usages.rule_id%type
226 ,X_APPLICATION_ID ame_rule_usages.item_id%type) is
227 cursor GET_USED_ATTRIBUTES (X_RULE_ID ame_rule_usages.rule_id%type) is
228 select AME_CONDITIONS.ATTRIBUTE_ID
229 from AME_CONDITIONS,
230 AME_CONDITION_USAGES
231 where
232 AME_CONDITIONS.CONDITION_TYPE in (AME_UTIL.ORDINARYCONDITIONTYPE,
233 AME_UTIL.EXCEPTIONCONDITIONTYPE) and
234 AME_CONDITION_USAGES.RULE_ID = X_RULE_ID and
235 AME_CONDITION_USAGES.CONDITION_ID = AME_CONDITIONS.CONDITION_ID and
236 (AME_CONDITIONS.START_DATE <= sysdate and
237 (AME_CONDITIONS.END_DATE is null or sysdate < AME_CONDITIONS.END_DATE)) and
238 ((sysdate between AME_CONDITION_USAGES.START_DATE and
239 nvl(AME_CONDITION_USAGES.END_DATE - (1/86400), sysdate)) or
240 (sysdate < AME_CONDITION_USAGES.START_DATE and
241 AME_CONDITION_USAGES.START_DATE < nvl(AME_CONDITION_USAGES.END_DATE,
242 AME_CONDITION_USAGES.START_DATE + (1/86400))))
243 union
244 select AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID
245 from AME_MANDATORY_ATTRIBUTES,
246 AME_ACTION_USAGES,
247 AME_ACTIONS
248 where
249 AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
250 AME_ACTIONS.ACTION_ID = AME_ACTION_USAGES.ACTION_ID and
251 AME_ACTION_USAGES.RULE_ID = X_RULE_ID and
252 (AME_MANDATORY_ATTRIBUTES.START_DATE <= sysdate and
253 (AME_MANDATORY_ATTRIBUTES.END_DATE is null or sysdate < AME_MANDATORY_ATTRIBUTES.END_DATE)) and
254 ((sysdate between AME_ACTION_USAGES.START_DATE and
255 nvl(AME_ACTION_USAGES.END_DATE - (1/86400), sysdate)) or
256 (sysdate < AME_ACTION_USAGES.START_DATE and
257 AME_ACTION_USAGES.START_DATE < nvl(AME_ACTION_USAGES.END_DATE,AME_ACTION_USAGES.START_DATE
258 + (1/86400)))) and
259 (AME_ACTIONS.START_DATE <= sysdate and
260 (AME_ACTIONS.END_DATE is null or sysdate < AME_ACTIONS.END_DATE))
261 union
262 select AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID
263 from AME_MANDATORY_ATTRIBUTES,
264 AME_RULES,
265 AME_ACTIONS
266 where
267 AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID = AME_ACTIONS.ACTION_TYPE_ID and
268 AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID and
272 (AME_MANDATORY_ATTRIBUTES.END_DATE is null or sysdate < AME_MANDATORY_ATTRIBUTES.END_DATE)) and
269 AME_RULES.ACTION_ID is not null and
270 AME_RULES.RULE_ID = X_RULE_ID and
271 (AME_MANDATORY_ATTRIBUTES.START_DATE <= sysdate and
273 ((sysdate between AME_RULES.START_DATE and
274 nvl(AME_RULES.END_DATE - (1/86400), sysdate)) or
275 (sysdate < AME_RULES.START_DATE and
276 AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,AME_RULES.START_DATE
277 + (1/86400)))) and
278 (AME_ACTIONS.START_DATE <= sysdate and
279 (AME_ACTIONS.END_DATE is null or sysdate < AME_ACTIONS.END_DATE));
280 ATTRIBUTE_IDS_LIST ame_util.idList;
281 X_USE_COUNT ame_attribute_usages.use_count%type;
282 begin
283 for ATTRIBUTE_REC in GET_USED_ATTRIBUTES(X_RULE_ID => X_RULE_ID) loop
284 -- calculate use count
285 X_USE_COUNT := CALCULATE_USE_COUNT(ATTRIBUTE_REC.ATTRIBUTE_ID, X_APPLICATION_ID);
286 -- update ame_attribute_usages
287 update AME_ATTRIBUTE_USAGES
288 set USE_COUNT = X_USE_COUNT
289 where
290 ATTRIBUTE_ID = ATTRIBUTE_REC.ATTRIBUTE_ID and
291 APPLICATION_ID = X_APPLICATION_ID and
292 sysdate between START_DATE and
293 nvl(END_DATE - (1/86400), sysdate);
294 end loop;
295 end CHANGE_ATTRIBUTE_USAGES_COUNT;
296
297 PROCEDURE CREATE_PARALLEL_CONFIG
298 (X_ACTION_TYPE_ID IN INTEGER
299 ,X_ACTION_TYPE_NAME IN VARCHAR2
300 ,X_ACTION_ID IN INTEGER
301 ,X_APPROVAL_GROUP_ID IN INTEGER
302 ) AS
303 CURSOR GROUP_ACTION_TYPE_CURSOR IS
304 SELECT NULL
305 FROM AME_ACTION_TYPES
306 WHERE SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE)
307 AND ACTION_TYPE_ID = X_ACTION_TYPE_ID
308 AND NAME IN ('PRE-CHAIN-OF-AUTHORITY APPROVALS'
309 ,'POST-CHAIN-OF-AUTHORITY APPROVALS'
310 ,'APPROVAL-GROUP CHAIN OF AUTHORITY');
311 CURSOR APPLICATION_USING_RULE_CURSOR IS
312 SELECT DISTINCT
313 ACA.APPLICATION_ID,
314 ACA.START_DATE,
315 ACA.END_DATE,
316 ACA.CREATED_BY,
317 ACA.CREATION_DATE
318 FROM AME_CALLING_APPS ACA,
319 AME_RULES AR,
320 AME_RULE_USAGES ARU,
321 AME_ACTION_USAGES AAU
322 WHERE ACA.APPLICATION_ID = ARU.ITEM_ID
323 AND ARU.RULE_ID = AR.RULE_ID
324 AND AR.RULE_ID = AAU.RULE_ID
325 AND AAU.ACTION_ID = X_ACTION_ID
326 AND SYSDATE BETWEEN ACA.START_DATE AND NVL(ACA.END_DATE,SYSDATE)
327 AND (SYSDATE BETWEEN AR.START_DATE AND NVL(AR.END_DATE,SYSDATE) OR
328 AR.START_DATE > SYSDATE AND NVL(AR.END_DATE,AR.START_DATE + (1/86400)) < AR.START_DATE)
329 AND (SYSDATE BETWEEN ARU.START_DATE AND NVL(ARU.END_DATE,SYSDATE) OR
330 ARU.START_DATE > SYSDATE AND NVL(ARU.END_DATE,ARU.START_DATE + (1/86400)) < ARU.START_DATE)
331 AND (SYSDATE BETWEEN AAU.START_DATE AND NVL(AAU.END_DATE,SYSDATE) OR
332 AAU.START_DATE > SYSDATE AND NVL(AAU.END_DATE,AAU.START_DATE + (1/86400)) < AAU.START_DATE);
333 CURSOR MAX_AT_ORDER_NUMBER_CURSOR(C_APPLICATION_ID INTEGER) IS
334 SELECT MAX(ORDER_NUMBER) + 1
335 FROM AME_ACTION_TYPE_CONFIG
339 SELECT MAX(ORDER_NUMBER) + 1
336 WHERE APPLICATION_ID = C_APPLICATION_ID
337 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
338 CURSOR MAX_AG_ORDER_NUMBER_CURSOR(C_APPLICATION_ID INTEGER) IS
340 FROM AME_APPROVAL_GROUP_CONFIG
341 WHERE APPLICATION_ID = C_APPLICATION_ID
342 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
343 CURSOR COA_ACTION_TYPE_CURSOR IS
344 SELECT NULL
345 FROM AME_ACTION_TYPE_USAGES
346 WHERE ACTION_TYPE_ID = X_ACTION_TYPE_ID
347 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE)
348 AND RULE_TYPE = AME_UTIL.AUTHORITYRULETYPE;
349 X_DUMMY VARCHAR2(10);
350 X_ORDER_NUMBER INTEGER;
351 X_GROUP_BASED_ACTION VARCHAR2(1);
352 X_VOTING_REGIME VARCHAR2(1);
353 X_ACTION_TYPE_ORDER_NUMBER INTEGER;
354 X_APPLICATION_ID INTEGER;
355 X_ACA_START_DATE DATE;
356 X_ACA_END_DATE DATE;
357 X_ACA_CREATED_BY INTEGER;
358 X_ACA_CREATION_DATE DATE;
359 LOCKHANDLE VARCHAR2(500);
360 RETURNVALUE INTEGER;
361 BEGIN
362 INIT_AME_INSTALLATION_LEVEL;
363 IF AME_INSTALLATION_LEVEL IS NULL OR TO_NUMBER(AME_INSTALLATION_LEVEL) < 2 THEN
364 RETURN;
365 END IF;
366 OPEN COA_ACTION_TYPE_CURSOR;
367 FETCH COA_ACTION_TYPE_CURSOR INTO X_VOTING_REGIME;
368 IF COA_ACTION_TYPE_CURSOR%NOTFOUND THEN
369 X_VOTING_REGIME := NULL;
370 ELSE
371 X_VOTING_REGIME := AME_UTIL.SERIALIZEDVOTING;
372 END IF;
373 CLOSE COA_ACTION_TYPE_CURSOR;
374
375 OPEN GROUP_ACTION_TYPE_CURSOR;
376 FETCH GROUP_ACTION_TYPE_CURSOR INTO X_DUMMY;
377 IF GROUP_ACTION_TYPE_CURSOR%FOUND THEN
378 X_GROUP_BASED_ACTION := 'Y';
379 ELSE
380 X_GROUP_BASED_ACTION := 'N';
381 END IF;
382 CLOSE GROUP_ACTION_TYPE_CURSOR;
383
384 OPEN APPLICATION_USING_RULE_CURSOR;
385 LOOP
386 FETCH APPLICATION_USING_RULE_CURSOR
387 INTO X_APPLICATION_ID,
388 X_ACA_START_DATE,
389 X_ACA_END_DATE,
390 X_ACA_CREATED_BY,
391 X_ACA_CREATION_DATE;
392 EXIT WHEN APPLICATION_USING_RULE_CURSOR%NOTFOUND;
393 BEGIN
394 SELECT NULL
395 INTO X_DUMMY
396 FROM AME_ACTION_TYPE_CONFIG
397 WHERE ACTION_TYPE_ID = X_ACTION_TYPE_ID
398 AND APPLICATION_ID = X_APPLICATION_ID
399 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 OPEN MAX_AT_ORDER_NUMBER_CURSOR(X_APPLICATION_ID);
403 FETCH MAX_AT_ORDER_NUMBER_CURSOR INTO X_ORDER_NUMBER;
404 IF MAX_AT_ORDER_NUMBER_CURSOR%NOTFOUND THEN
405 X_ORDER_NUMBER := 1;
406 END IF;
407 CLOSE MAX_AT_ORDER_NUMBER_CURSOR;
408 IF X_ORDER_NUMBER IS NULL THEN
409 X_ORDER_NUMBER := 1;
410 END IF;
411 SELECT DECODE (X_ACTION_TYPE_NAME,
412 AME_UTIL.PREAPPROVALTYPENAME, 1,
413 AME_UTIL.DYNAMICPREAPPROVER, 2,
414 AME_UTIL.ABSOLUTEJOBLEVELTYPENAME, 1,
415 AME_UTIL.RELATIVEJOBLEVELTYPENAME, 2,
416 AME_UTIL.SUPERVISORYLEVELTYPENAME, 3,
417 AME_UTIL.POSITIONTYPENAME, 4,
421 AME_UTIL.LINEITEMJOBLEVELTYPENAME, 8,
418 AME_UTIL.POSITIONLEVELTYPENAME, 5,
419 AME_UTIL.MANAGERFINALAPPROVERTYPENAME, 6,
420 AME_UTIL.FINALAPPROVERONLYTYPENAME, 7,
422 AME_UTIL.DUALCHAINSAUTHORITYTYPENAME, 9,
423 AME_UTIL.GROUPCHAINAPPROVALTYPENAME, 10,
424 AME_UTIL.NONFINALAUTHORITY, 1,
425 AME_UTIL.FINALAUTHORITYTYPENAME, 2,
426 AME_UTIL.SUBSTITUTIONTYPENAME, 1,
427 AME_UTIL.POSTAPPROVALTYPENAME, 1,
428 AME_UTIL.DYNAMICPOSTAPPROVER, 2,
429 X_ORDER_NUMBER)
430 INTO X_ACTION_TYPE_ORDER_NUMBER
431 FROM DUAL;
432 DBMS_LOCK.ALLOCATE_UNIQUE (LOCKNAME =>'AME_ACTION_TYPE_CONFIG.'||X_APPLICATION_ID||X_ACTION_TYPE_ID
433 ,LOCKHANDLE => LOCKHANDLE);
434 RETURNVALUE := DBMS_LOCK.REQUEST(LOCKHANDLE => LOCKHANDLE,TIMEOUT => 0
435 ,RELEASE_ON_COMMIT => TRUE);
436 IF RETURNVALUE = 0 THEN
437 INSERT INTO AME_ACTION_TYPE_CONFIG
438 (APPLICATION_ID
439 ,ACTION_TYPE_ID
440 ,VOTING_REGIME
441 ,ORDER_NUMBER
442 ,CHAIN_ORDERING_MODE
443 ,START_DATE
444 ,END_DATE
445 ,CREATED_BY
446 ,CREATION_DATE
447 ,LAST_UPDATED_BY
448 ,LAST_UPDATE_DATE
449 ,LAST_UPDATE_LOGIN
450 ,OBJECT_VERSION_NUMBER
451 ) SELECT X_APPLICATION_ID
452 ,X_ACTION_TYPE_ID
453 ,X_VOTING_REGIME
454 ,X_ACTION_TYPE_ORDER_NUMBER
455 ,AME_UTIL.SERIALCHAINSMODE
456 ,X_ACA_START_DATE
457 ,X_ACA_END_DATE
458 ,X_ACA_CREATED_BY
459 ,X_ACA_CREATION_DATE
460 ,X_ACA_CREATED_BY
461 ,X_ACA_CREATION_DATE
462 ,0
463 ,1
464 FROM DUAL
465 WHERE NOT EXISTS (SELECT NULL
466 FROM AME_ACTION_TYPE_CONFIG
467 WHERE ACTION_TYPE_ID = X_ACTION_TYPE_ID
468 AND APPLICATION_ID = X_APPLICATION_ID
469 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE - (1/86400),SYSDATE));
470 END IF;
471 END;
472 IF X_GROUP_BASED_ACTION = 'Y' THEN
473 BEGIN
474 SELECT NULL
475 INTO X_DUMMY
476 FROM AME_APPROVAL_GROUP_CONFIG
477 WHERE SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE)
478 AND APPLICATION_ID = X_APPLICATION_ID
479 AND APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID;
480 EXCEPTION
481 WHEN NO_DATA_FOUND THEN
482 OPEN MAX_AG_ORDER_NUMBER_CURSOR(X_APPLICATION_ID);
483 FETCH MAX_AG_ORDER_NUMBER_CURSOR INTO X_ORDER_NUMBER;
484 IF MAX_AG_ORDER_NUMBER_CURSOR%NOTFOUND THEN
485 X_ORDER_NUMBER := 1;
486 END IF;
487 IF X_ORDER_NUMBER IS NULL THEN
488 X_ORDER_NUMBER := 1;
489 END IF;
490 CLOSE MAX_AG_ORDER_NUMBER_CURSOR;
491 DBMS_LOCK.ALLOCATE_UNIQUE (LOCKNAME =>'AME_APPROVAL_GROUP_CONFIG.'||X_APPLICATION_ID||X_APPROVAL_GROUP_ID
492 ,LOCKHANDLE => LOCKHANDLE);
493 RETURNVALUE := DBMS_LOCK.REQUEST(LOCKHANDLE => LOCKHANDLE,TIMEOUT => 0
494 ,RELEASE_ON_COMMIT => TRUE);
495 IF RETURNVALUE = 0 THEN
496 INSERT INTO AME_APPROVAL_GROUP_CONFIG
497 (APPLICATION_ID
498 ,APPROVAL_GROUP_ID
499 ,VOTING_REGIME
500 ,ORDER_NUMBER
501 ,START_DATE
502 ,END_DATE
503 ,CREATED_BY
504 ,CREATION_DATE
505 ,LAST_UPDATED_BY
506 ,LAST_UPDATE_DATE
507 ,LAST_UPDATE_LOGIN
508 ,OBJECT_VERSION_NUMBER
509 ) SELECT X_APPLICATION_ID
510 ,X_APPROVAL_GROUP_ID
511 ,AME_UTIL.ORDERNUMBERVOTING
512 ,X_ORDER_NUMBER
513 ,X_ACA_START_DATE
514 ,X_ACA_END_DATE
515 ,X_ACA_CREATED_BY
516 ,X_ACA_CREATION_DATE
517 ,X_ACA_CREATED_BY
518 ,X_ACA_CREATION_DATE
519 ,0
520 ,1
521 FROM DUAL
522 WHERE NOT EXISTS (SELECT NULL
523 FROM AME_APPROVAL_GROUP_CONFIG
524 WHERE APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID
525 AND APPLICATION_ID = X_APPLICATION_ID
526 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE - (1/86400),SYSDATE));
527 END IF;
528 END;
529 END IF;
530 END LOOP;
531 CLOSE APPLICATION_USING_RULE_CURSOR;
532 END CREATE_PARALLEL_CONFIG;
533
534 end AME_SEED_UTILITY;
[Home] [Help]