[Home] [Help]
PACKAGE BODY: APPS.AME_ACTION_USAGES_API2
Source
1 package BODY AME_ACTION_USAGES_API2 as
2 /* $Header: ameasapi.pkb 120.5 2006/08/23 14:07:40 pvelugul noship $ */
3 procedure KEY_TO_IDS
4 (X_RULE_KEY in varchar2
5 ,X_ACTION_TYPE_NAME in varchar2
6 ,X_PARAMETER in varchar2
7 ,X_PARAMETER_TWO in varchar2
8 ,X_RULE_ID out nocopy number
9 ,X_ACTION_ID out nocopy number
10 ,X_ACTION_TYPE_ID out nocopy number
11 ) as
12 begin
13 begin
14 select ARU.RULE_ID
15 into X_RULE_ID
16 from AME_RULES ARU
17 where ARU.RULE_KEY = X_RULE_KEY
18 and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate);
19 exception
20 when no_data_found then
21 raise_application_error(-20001,'Cannot find rule with Rule Key ' || X_RULE_KEY);
22 end;
23
24 begin
25 select ACT.ACTION_ID,
26 ACT.ACTION_TYPE_ID
27 into X_ACTION_ID,
28 X_ACTION_TYPE_ID
29 from AME_ACTIONS ACT,
30 AME_ACTION_TYPES AAT
31 where ACT.ACTION_TYPE_ID = AAT.ACTION_TYPE_ID
32 and AAT.NAME = X_ACTION_TYPE_NAME
33 and nvl(ACT.PARAMETER,'NULL') = nvl(X_PARAMETER,'NULL')
34 and nvl(ACT.PARAMETER_TWO,'NULL') = nvl(X_PARAMETER_TWO,'NULL')
35 and sysdate between AAT.START_DATE and nvl(AAT.END_DATE - (1/86400),sysdate)
36 and sysdate between ACT.START_DATE and nvl(ACT.END_DATE - (1/86400),sysdate);
37 exception
38 when no_data_found then
39 raise_application_error(-20001,'Cannot find action with Action type ' || X_ACTION_TYPE_NAME ||
40 ' and Parameters ' || X_PARAMETER || ',' || X_PARAMETER_TWO);
41 end;
42 end KEY_TO_IDS;
43
44 procedure VALIDATE_ROW
45 (X_RULE_KEY in varchar2) as
46 X_RULE_TYPE number;
47 begin
48 begin
49 select ARU.RULE_TYPE
50 into X_RULE_TYPE
51 from AME_RULES ARU
52 where ARU.RULE_KEY = X_RULE_KEY
53 and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate);
54 if X_RULE_TYPE not in (1,2,5,6,7) then
55 raise_application_error(-20001,'AME is attempting to upload usages for an invalid rule type');
56 end if;
57 exception
58 when no_data_found then
59 raise_application_error(-20001,'Cannot find rule with Rule Key ' || X_RULE_KEY);
60 end;
61 end VALIDATE_ROW;
62
63 procedure FORMAT_ROW
64 (X_ACTION_TYPE_NAME in varchar2
65 ,X_PARAMETER in out nocopy varchar2
66 ,X_PARAMETER_TWO in out nocopy varchar2
67 ,X_APPROVAL_GROUP_ID out nocopy varchar2
68 ) as
69 L_APPROVAL_GROUP_ID number;
70 begin
71 if X_ACTION_TYPE_NAME in
72 ('pre-chain-of-authority approvals'
73 ,'post-chain-of-authority approvals'
74 ,'approval-group chain of authority') then
75 begin
76 select AAG.APPROVAL_GROUP_ID
77 into L_APPROVAL_GROUP_ID
78 from AME_APPROVAL_GROUPS AAG
79 where AAG.NAME = X_PARAMETER
80 and sysdate between AAG.START_DATE and nvl(AAG.END_DATE - (1/86400),sysdate);
81 X_PARAMETER := to_char(L_APPROVAL_GROUP_ID);
82 X_PARAMETER_TWO := null;
83 X_APPROVAL_GROUP_ID := L_APPROVAL_GROUP_ID;
84 exception
85 when no_data_found then
86 raise_application_error(-20001,'Cannot find approval group ' || X_PARAMETER);
87 end;
88 end if;
89 end FORMAT_ROW;
90
91 procedure CHANGE_RULE_ATTR_USE_COUNT(X_RULE_ID ame_action_usages.rule_id%type) as
92 CURSOR CSR_GET_ITEM_IDS
93 (
94 X_RULE_ID in integer
95 ) is
96 select ACA.APPLICATION_ID
97 from AME_CALLING_APPS ACA,
98 AME_RULE_USAGES ARU
99 where ACA.APPLICATION_ID = ARU.ITEM_ID
100 and ARU.RULE_ID = X_RULE_ID
101 and sysdate between ARU.START_DATE
102 and nvl(ARU.END_DATE - (1/86400), sysdate);
103 begin
104 for TEMP_APPLICATION_ID in CSR_GET_ITEM_IDS(X_RULE_ID => X_RULE_ID) loop
105 AME_SEED_UTILITY.CHANGE_ATTRIBUTE_USAGES_COUNT(X_RULE_ID => X_RULE_ID
106 ,X_APPLICATION_ID => TEMP_APPLICATION_ID.APPLICATION_ID
107 );
108 end loop;
109 end CHANGE_RULE_ATTR_USE_COUNT;
110
111 procedure INSERT_ROW
112 (X_ACTION_ID in number
113 ,X_RULE_ID in number
114 ,X_START_DATE in date
115 ,X_END_DATE in date
116 ,X_CREATED_BY in number
117 ,X_CREATION_DATE in date
118 ,X_LAST_UPDATED_BY in number
119 ,X_LAST_UPDATE_DATE in date
120 ,X_LAST_UPDATE_LOGIN in number
121 ,X_OBJECT_VERSION_NUMBER in number
122 ) as
123 X_LOCK_HANDLE varchar2(500);
124 X_RETURN_VALUE number;
125 begin
126 DBMS_LOCK.ALLOCATE_UNIQUE
127 (LOCKNAME =>'AME_ACTION_USAGES.'||X_RULE_ID||X_ACTION_ID
128 ,LOCKHANDLE => X_LOCK_HANDLE
129 );
130 X_RETURN_VALUE := DBMS_LOCK.REQUEST
131 (LOCKHANDLE => X_LOCK_HANDLE
132 ,TIMEOUT => 0
133 ,RELEASE_ON_COMMIT => true);
134
135 if X_RETURN_VALUE = 0 then
136 insert into AME_ACTION_USAGES
137 (ACTION_ID
138 ,RULE_ID
139 ,START_DATE
140 ,END_DATE
141 ,CREATED_BY
142 ,CREATION_DATE
143 ,LAST_UPDATED_BY
144 ,LAST_UPDATE_DATE
145 ,LAST_UPDATE_LOGIN
146 ,OBJECT_VERSION_NUMBER
147 ) select X_ACTION_ID,
148 X_RULE_ID,
149 X_START_DATE,
150 X_END_DATE,
151 X_CREATED_BY,
152 X_CREATION_DATE,
153 X_LAST_UPDATED_BY,
154 X_LAST_UPDATE_DATE,
155 X_LAST_UPDATE_LOGIN,
156 X_OBJECT_VERSION_NUMBER
157 from dual
158 where not exists (select null
159 from AME_ACTION_USAGES
160 where RULE_ID = X_RULE_ID
161 and ACTION_ID = X_ACTION_ID
162 and sysdate between START_DATE and nvl(END_DATE - (1/86400), sysdate));
163 CHANGE_RULE_ATTR_USE_COUNT(X_RULE_ID => X_RULE_ID);
164 end if;
165 end INSERT_ROW;
166
167 procedure UPDATE_ROW
168 (X_ACTION_ID in number
169 ,X_RULE_ID in number
170 ,X_START_DATE in date
171 ,X_END_DATE in date
172 ,X_CREATED_BY in number
173 ,X_CREATION_DATE in date
174 ,X_LAST_UPDATE_DATE in date
175 ,X_LAST_UPDATED_BY in number
176 ,X_LAST_UPDATE_LOGIN in number
177 ,X_OBJECT_VERSION_NUMBER in number
178 ) as
179 X_LOCK_HANDLE varchar2(500);
180 X_RETURN_VALUE number;
181 begin
182 DBMS_LOCK.ALLOCATE_UNIQUE
183 (LOCKNAME =>'AME_ACTION_USAGES.'||X_RULE_ID||X_ACTION_ID
184 ,LOCKHANDLE => X_LOCK_HANDLE
185 );
186 X_RETURN_VALUE := DBMS_LOCK.REQUEST
187 (LOCKHANDLE => X_LOCK_HANDLE
188 ,TIMEOUT => 0
189 ,RELEASE_ON_COMMIT => true
190 );
191
192 if X_RETURN_VALUE = 0 then
193 update AME_ACTION_USAGES AAU
194 set AAU.END_DATE = X_START_DATE
195 where AAU.ACTION_ID = X_ACTION_ID
196 and AAU.RULE_ID = X_RULE_ID
197 and sysdate between AAU.START_DATE and nvl(AAU.END_DATE - (1/86400),sysdate);
198
199 insert into AME_ACTION_USAGES
200 (ACTION_ID
201 ,RULE_ID
202 ,START_DATE
203 ,END_DATE
204 ,CREATED_BY
205 ,CREATION_DATE
206 ,LAST_UPDATED_BY
207 ,LAST_UPDATE_DATE
208 ,LAST_UPDATE_LOGIN
209 ,OBJECT_VERSION_NUMBER
210 ) values
211 (X_ACTION_ID
212 ,X_RULE_ID
213 ,X_START_DATE
214 ,X_END_DATE
215 ,X_CREATED_BY
216 ,X_CREATION_DATE
217 ,X_LAST_UPDATED_BY
218 ,X_LAST_UPDATE_DATE
219 ,X_LAST_UPDATE_LOGIN
220 ,X_OBJECT_VERSION_NUMBER
221 );
222 CHANGE_RULE_ATTR_USE_COUNT(X_RULE_ID => X_RULE_ID);
223 end if;
224 end UPDATE_ROW;
225
226 procedure FORCE_UPDATE_ROW (
227 X_ROWID in VARCHAR2,
228 X_CREATED_BY in NUMBER,
229 X_CREATION_DATE in DATE,
230 X_LAST_UPDATED_BY in NUMBER,
231 X_LAST_UPDATE_DATE in DATE,
232 X_LAST_UPDATE_LOGIN in NUMBER,
233 X_START_DATE in DATE,
234 X_END_DATE in DATE,
235 X_OBJECT_VERSION_NUMBER in NUMBER
236 ) is
237 begin
238 update AME_ACTION_USAGES
239 set CREATED_BY = X_CREATED_BY,
240 CREATION_DATE = X_CREATION_DATE,
241 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
242 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
243 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
244 START_DATE = X_START_DATE,
245 END_DATE = X_END_DATE,
246 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
247 where ROWID = X_ROWID;
248 end FORCE_UPDATE_ROW;
249
250 procedure LOAD_ROW
251 (X_RULE_KEY in varchar2
252 ,X_ACTION_TYPE_NAME in varchar2
253 ,X_PARAMETER in varchar2
254 ,X_PARAMETER_TWO in varchar2
255 ,X_OWNER in varchar2
256 ,X_LAST_UPDATE_DATE in varchar2
257 ,X_CUSTOM_MODE in varchar2
258 ) as
259 L_RULE_ID number;
260 L_ACTION_ID number;
261 L_END_DATE date;
262 L_DUMMY varchar2(1);
263 L_PARAMETER AME_ACTIONS.PARAMETER%TYPE;
264 L_PARAMETER_TWO AME_ACTIONS.PARAMETER_TWO%TYPE;
265 L_ACTION_TYPE_NAME AME_ACTION_TYPES.NAME%TYPE;
266 L_RULE_KEY AME_RULES.RULE_KEY%TYPE;
267 L_OWNER varchar2(100);
268 L_LAST_UPDATE_DATE varchar2(19);
269 L_OBJECT_VERSION_NUMBER number;
270 L_ROWID ROWID;
271 L_ACTION_TYPE_ID number;
272 L_APPROVAL_GROUP_ID number;
273 L_ACTION_USAGES_COUNT number;
274 begin
275 L_RULE_KEY := X_RULE_KEY;
276 L_ACTION_TYPE_NAME := X_ACTION_TYPE_NAME;
277 L_PARAMETER := X_PARAMETER;
278 L_PARAMETER_TWO := X_PARAMETER_TWO;
279 L_OWNER := X_OWNER;
280 L_LAST_UPDATE_DATE := X_LAST_UPDATE_DATE;
281 L_END_DATE := AME_SEED_UTILITY.GET_DEFAULT_END_DATE;
282
283 VALIDATE_ROW
284 (X_RULE_KEY => L_RULE_KEY);
285
286 FORMAT_ROW
287 (X_ACTION_TYPE_NAME => L_ACTION_TYPE_NAME
288 ,X_PARAMETER => L_PARAMETER
289 ,X_PARAMETER_TWO => L_PARAMETER_TWO
290 ,X_APPROVAL_GROUP_ID => L_APPROVAL_GROUP_ID);
291
292 KEY_TO_IDS
293 (X_RULE_KEY => L_RULE_KEY
294 ,X_ACTION_TYPE_NAME => L_ACTION_TYPE_NAME
295 ,X_PARAMETER => L_PARAMETER
296 ,X_PARAMETER_TWO => L_PARAMETER_TWO
297 ,X_RULE_ID => L_RULE_ID
298 ,X_ACTION_ID => L_ACTION_ID
299 ,X_ACTION_TYPE_ID => L_ACTION_TYPE_ID
303 select nvl(AAU.OBJECT_VERSION_NUMBER,1),
300 );
301
302 begin
304 ROWID
305 into L_OBJECT_VERSION_NUMBER,
306 L_ROWID
307 from AME_ACTION_USAGES AAU
308 where AAU.RULE_ID = L_RULE_ID
309 and AAU.ACTION_ID = L_ACTION_ID
310 and sysdate between AAU.START_DATE and nvl(AAU.END_DATE - (1/86400),sysdate);
311
312 if X_CUSTOM_MODE = 'FORCE' then
313 FORCE_UPDATE_ROW
314 (X_ROWID => L_ROWID
315 ,X_CREATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
316 ,X_CREATION_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
317 ,X_LAST_UPDATE_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
318 ,X_LAST_UPDATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
319 ,X_LAST_UPDATE_LOGIN => 0
320 ,X_START_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
321 ,X_END_DATE => L_END_DATE
322 ,X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER + 1
323 );
324 end if;
325 exception
326 when no_data_found then
327 select count(*)
328 into L_ACTION_USAGES_COUNT
329 from ame_rules
330 where RULE_ID = L_RULE_ID
331 and ACTION_ID = L_ACTION_ID
332 and sysdate between START_DATE
333 and nvl(END_DATE - (1/86400), sysdate);
334
335 if L_ACTION_USAGES_COUNT = 0 then
336 INSERT_ROW
340 ,X_END_DATE => L_END_DATE
337 (X_ACTION_ID => L_ACTION_ID
338 ,X_RULE_ID => L_RULE_ID
339 ,X_START_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
341 ,X_CREATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
342 ,X_CREATION_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
343 ,X_LAST_UPDATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
344 ,X_LAST_UPDATE_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
345 ,X_LAST_UPDATE_LOGIN => 0
346 ,X_OBJECT_VERSION_NUMBER => 1
347 );
348 AME_SEED_UTILITY.CREATE_PARALLEL_CONFIG
349 (L_ACTION_TYPE_ID
350 ,L_ACTION_TYPE_NAME
351 ,L_ACTION_ID
352 ,L_APPROVAL_GROUP_ID
353 );
354 end if;
355 end;
356 end LOAD_ROW;
357
358 function MERGE_ROW_TEST
359 (X_RULE_KEY in varchar2
360 ,X_ACTION_TYPE_NAME in varchar2
361 ,X_PARAMETER in varchar2
362 ,X_PARAMETER_TWO in varchar2
363 ,X_OWNER in varchar2
364 ,X_LAST_UPDATE_DATE in varchar2
365 ,X_UPLOAD_MODE in varchar2
366 ,X_CUSTOM_MODE in varchar2
367 ) return boolean as
368 X_CURRENT_OWNER NUMBER;
369 X_CURRENT_LAST_UPDATE_DATE varchar2(19);
370 begin
371 if X_UPLOAD_MODE = 'NLS' then
372 return false;
373 else
374 begin
375 select CUST.OWNER,
376 CUST.LAST_UPDATE_DATE
377 into X_CURRENT_OWNER,
378 X_CURRENT_LAST_UPDATE_DATE
379 from (select AAU.LAST_UPDATED_BY OWNER,
380 AME_SEED_UTILITY.DATE_AS_STRING(AAU.LAST_UPDATE_DATE) LAST_UPDATE_DATE
381 from AME_ACTIONS ACT,
382 AME_ACTION_TYPES AAT,
383 AME_RULES ARU,
384 AME_ACTION_USAGES AAU
385 where ACT.ACTION_TYPE_ID = AAT.ACTION_TYPE_ID
386 and ACT.ACTION_ID = AAU.ACTION_ID
387 and AAU.RULE_ID = ARU.RULE_ID
388 and AAT.NAME not in
389 ('approval-group chain of authority')
390 and ARU.RULE_TYPE in (1,2,7)
391 and ARU.RULE_KEY = X_RULE_KEY
392 and AAT.NAME = X_ACTION_TYPE_NAME
393 and nvl(ACT.PARAMETER,'NULL') = nvl(X_PARAMETER,'NULL')
394 and nvl(ACT.PARAMETER_TWO,'NULL') = nvl(X_PARAMETER_TWO,'NULL')
395 and sysdate between ACT.START_DATE and nvl(ACT.END_DATE - (1/86400),sysdate)
396 and sysdate between AAT.START_DATE and nvl(AAT.END_DATE - (1/86400),sysdate)
397 and sysdate between AAU.START_DATE and nvl(AAU.END_DATE - (1/86400),sysdate)
398 and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate)
399 union
400 select AAU.LAST_UPDATED_BY OWNER,
401 AME_SEED_UTILITY.DATE_AS_STRING(AAU.LAST_UPDATE_DATE) LAST_UPDATE_DATE
402 from AME_ACTIONS ACT,
406 AME_APPROVAL_GROUPS AAG
403 AME_ACTION_TYPES AAT,
404 AME_RULES ARU,
405 AME_ACTION_USAGES AAU,
407 where ACT.ACTION_TYPE_ID = AAT.ACTION_TYPE_ID
408 and ACT.ACTION_ID = AAU.ACTION_ID
409 and AAU.RULE_ID = ARU.RULE_ID
410 and ACT.PARAMETER = to_char(AAG.APPROVAL_GROUP_ID)
411 and AAT.NAME in
412 ('pre-chain-of-authority approvals'
413 ,'post-chain-of-authority approvals'
414 ,'approval-group chain of authority')
415 and ARU.RULE_TYPE in (1,2,5,6,7)
416 and AAG.IS_STATIC = 'N'
417 and ARU.RULE_KEY = X_RULE_KEY
418 and AAT.NAME = X_ACTION_TYPE_NAME
419 and AAG.NAME = X_PARAMETER
420 and sysdate between ACT.START_DATE and nvl(ACT.END_DATE - (1/86400),sysdate)
421 and sysdate between AAT.START_DATE and nvl(AAT.END_DATE - (1/86400),sysdate)
422 and sysdate between AAU.START_DATE and nvl(AAU.END_DATE - (1/86400),sysdate)
423 and sysdate between ARU.START_DATE and nvl(ARU.END_DATE - (1/86400),sysdate)
424 and sysdate between AAG.START_DATE and nvl(AAG.END_DATE - (1/86400),sysdate)) CUST;
425 exception
426 when no_data_found then
427 return true;
428 end;
429 end if;
430 return AME_SEED_UTILITY.MERGE_ROW_TEST
431 (X_CURRENT_OWNER => X_CURRENT_OWNER
432 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
433 ,X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER)
434 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
435 ,X_CUSTOM_MODE => X_CUSTOM_MODE
436 );
437 end MERGE_ROW_TEST;
438
439 procedure LOAD_SEED_ROW
440 (X_RULE_KEY in varchar2
441 ,X_ACTION_TYPE_NAME in varchar2
442 ,X_PARAMETER in varchar2
443 ,X_PARAMETER_TWO in varchar2
444 ,X_OWNER in varchar2
445 ,X_LAST_UPDATE_DATE in varchar2
446 ,X_UPLOAD_MODE in varchar2
447 ,X_CUSTOM_MODE in varchar2
448 ) as
449 begin
450 AME_SEED_UTILITY.INIT_AME_INSTALLATION_LEVEL;
451
452 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is null then
453 raise_application_error (-20001,'AME is trying to upload action usages to a 11.5.9 or lower instance');
454 end if;
455
456 if X_UPLOAD_MODE = 'NLS' then
457 null;
458 else
459 LOAD_ROW
460 (X_RULE_KEY => X_RULE_KEY
461 ,X_ACTION_TYPE_NAME => X_ACTION_TYPE_NAME
462 ,X_PARAMETER => X_PARAMETER
463 ,X_PARAMETER_TWO => X_PARAMETER_TWO
464 ,X_OWNER => X_OWNER
465 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
466 ,X_CUSTOM_MODE => X_CUSTOM_MODE
467 );
468 end if;
469 end LOAD_SEED_ROW;
470
471 end AME_ACTION_USAGES_API2;