1 PACKAGE BODY AME_ACTION_USAGES_API AS
2 /* $Header: ameusapi.pkb 120.5 2006/08/23 13:35:07 pvelugul noship $ */
3 X_AME_INSTALLATION_LEVEL varchar2(255);
4 procedure OWNER_TO_WHO (
5 X_OWNER in VARCHAR2,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATED_BY out nocopy NUMBER,
8 X_LAST_UPDATE_LOGIN out nocopy NUMBER
9 ) is
10 begin
11 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
13 X_LAST_UPDATE_LOGIN := 0;
14 end OWNER_TO_WHO;
15
16 procedure KEY_TO_IDS (
17 X_RULE_KEY in VARCHAR2,
18 X_ACTION_TYPE_NAME in VARCHAR2,
19 X_PARAMETER in VARCHAR2,
20 X_RULE_ID out nocopy NUMBER,
21 X_ACTION_ID out nocopy NUMBER,
22 X_ACTION_USAGE_ROWID out nocopy VARCHAR2,
23 X_ACTION_TYPE_ID out nocopy NUMBER,
24 X_APPROVAL_GROUP_ID out nocopy NUMBER,
25 X_CURRENT_OWNER out nocopy NUMBER,
26 X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
27 X_CURRENT_OVN out nocopy NUMBER,
28 X_RULE_ROWID out nocopy VARCHAR2
29 ) is
30 cursor CSR_GET_RULE_ID
31 (
32 X_RULE_KEY in VARCHAR2
33 ) is
34 select RULE_ID
35 from AME_RULES
36 where RULE_KEY = X_RULE_KEY;
37 cursor CSR_GET_ACTION_TYPE_ID
38 (
39 X_ACTION_TYPE_NAME in VARCHAR2
40 ) is
41 select ACTION_TYPE_ID
42 from AME_ACTION_TYPES
43 where NAME = X_ACTION_TYPE_NAME
44 and sysdate between START_DATE
45 and nvl(END_DATE - (1/86400), sysdate);
46 cursor CSR_GET_ACTION
47 (
48 X_ACTION_TYPE_ID in NUMBER,
49 X_PARAMETER in VARCHAR2
50 ) is
51 select ACTION_ID from AME_ACTIONS
52 where ACTION_TYPE_ID = X_ACTION_TYPE_ID
53 and nvl(PARAMETER,'NULL') = nvl(X_PARAMETER,'NULL')
54
55 and sysdate between START_DATE
56 and nvl(END_DATE - (1/86400), sysdate);
57 cursor CSR_GET_CURRENT_ACTION_USAGE
58 (
59 X_RULE_ID in NUMBER,
60 X_ACTION_ID in NUMBER
61 ) is select ROWID,
62 LAST_UPDATED_BY,
63 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
64 nvl(OBJECT_VERSION_NUMBER,1)
65 from AME_ACTION_USAGES
66 where RULE_ID = X_RULE_ID
67 and ACTION_ID = X_ACTION_ID
68 and sysdate between START_DATE
69 and nvl(END_DATE - (1/86400), sysdate);
70 cursor CSR_GET_APPROVAL_GROUP_ID
71 (
72 X_APPROVAL_GROUP_NAME in VARCHAR2
73 ) is
74 select APPROVAL_GROUP_ID
75 from AME_APPROVAL_GROUPS
76 where NAME = X_APPROVAL_GROUP_NAME
77 and sysdate between START_DATE
78 and nvl(end_date - (1/86400), sysdate);
79 cursor CSR_GET_CURRENT_ACTION_USAGE2
80 (
81 X_RULE_ID in NUMBER,
82 X_ACTION_ID in NUMBER
83 ) is select ROWID
84 from AME_RULES
85 where RULE_ID = X_RULE_ID
86 and ACTION_ID = X_ACTION_ID
87 and sysdate between START_DATE
88 and nvl(END_DATE - (1/86400), sysdate);
89 L_PARAMETER VARCHAR2(320);
90 begin
91 X_CURRENT_OVN := 1;
92 L_PARAMETER := X_PARAMETER;
93 open CSR_GET_ACTION_TYPE_ID (
94 X_ACTION_TYPE_NAME
95 );
96 fetch CSR_GET_ACTION_TYPE_ID into X_ACTION_TYPE_ID;
97 if (CSR_GET_ACTION_TYPE_ID%notfound) then
98 X_ACTION_TYPE_ID := null;
99 end if;
100 close CSR_GET_ACTION_TYPE_ID;
101
102 if X_ACTION_TYPE_ID is not null
103 then
104 --
105 -- Determine if the action type is one that is based on groups.
106 --
107 if X_ACTION_TYPE_NAME in (ame_util.preApprovalTypeName
108 ,ame_util.postApprovalTypeName
109 ,ame_util.groupChainApprovalTypeName
110 ) then
111 open CSR_GET_APPROVAL_GROUP_ID(X_PARAMETER);
112 fetch CSR_GET_APPROVAL_GROUP_ID into X_APPROVAL_GROUP_ID;
113 L_PARAMETER := X_APPROVAL_GROUP_ID;
114 if (CSR_GET_APPROVAL_GROUP_ID%notfound) then
115 L_PARAMETER := X_PARAMETER;
116 end if;
117 close CSR_GET_APPROVAL_GROUP_ID;
118 end if;
119 open CSR_GET_ACTION (
120 X_ACTION_TYPE_ID,
121 L_PARAMETER
122 );
123 fetch CSR_GET_ACTION into X_ACTION_ID;
124 if (CSR_GET_ACTION%notfound) then
125 X_ACTION_ID := null;
126 end if;
127 close CSR_GET_ACTION;
128 end if;
129
130 open CSR_GET_RULE_ID(X_RULE_KEY);
131 fetch CSR_GET_RULE_ID into X_RULE_ID;
132 if (CSR_GET_RULE_ID%notfound) then
133 X_RULE_ID := null;
134 end if;
135 close CSR_GET_RULE_ID;
136
137 if (X_ACTION_ID is not null)
138 and (X_RULE_ID is not null)
139 then
140 open CSR_GET_CURRENT_ACTION_USAGE (
141 X_RULE_ID,
142 X_ACTION_ID
143 );
144 fetch CSR_GET_CURRENT_ACTION_USAGE into X_ACTION_USAGE_ROWID,
145 X_CURRENT_OWNER, X_CURRENT_LAST_UPDATE_DATE, X_CURRENT_OVN;
146 if (CSR_GET_CURRENT_ACTION_USAGE%notfound) then
147 X_ACTION_USAGE_ROWID := null;
148 end if;
149 close CSR_GET_CURRENT_ACTION_USAGE;
150 open CSR_GET_CURRENT_ACTION_USAGE2 (
151 X_RULE_ID,
152 X_ACTION_ID
153 );
154 fetch CSR_GET_CURRENT_ACTION_USAGE2 into X_RULE_ROWID;
155 if (CSR_GET_CURRENT_ACTION_USAGE2%notfound) then
156 X_RULE_ROWID := null;
157 end if;
158 close CSR_GET_CURRENT_ACTION_USAGE2;
159 end if;
160
161 end KEY_TO_IDS;
162 procedure VALIDATE_RULE_TYPE (X_RULE_KEY in VARCHAR2) is
163 cursor CSR_GET_RULE_TYPE(X_RULE_KEY in VARCHAR2) is
164 select RULE_TYPE
165 from AME_RULES
166 where RULE_KEY = X_RULE_KEY
167 and sysdate between START_DATE
168 and nvl(END_DATE - (1/86400), sysdate);
169 invalidRuleTypeException exception;
170 errorCode integer;
171 errorMessage ame_util.longestStringType;
172 X_RULE_TYPE integer;
173 begin
174 open CSR_GET_RULE_TYPE(X_RULE_KEY);
175 fetch CSR_GET_RULE_TYPE
176 into X_RULE_TYPE;
177 if CSR_GET_RULE_TYPE%notfound then
178 X_RULE_TYPE := null;
179 end if;
180 close CSR_GET_RULE_TYPE;
181 if (X_RULE_TYPE not in (ame_util.authorityRuleType
182 ,ame_util.exceptionRuleType
183 ,ame_util.preListGroupRuleType
184 ,ame_util.postListGroupRuleType
185 )) then
186 raise invalidRuleTypeException;
187 end if;
188 exception
189 when invalidRuleTypeException then
190 errorCode := -20001;
191 errorMessage := 'AME is attempting to upload usages for an invalid rule type. ';
192 ame_util.runtimeException(packageNameIn => 'ame_action_usages',
193 routineNameIn => 'validate_rule_type',
194 exceptionNumberIn => errorCode,
195 exceptionStringIn => errorMessage);
196 raise_application_error(errorCode,
197 errorMessage);
198 when others then
199 ame_util.runtimeException('ame_action_usages',
200 'validate_rule_type',
201 sqlcode,
202 sqlerrm);
203 raise;
204 end VALIDATE_RULE_TYPE;
205 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
206 X_CURRENT_OWNER in NUMBER,
207 X_LAST_UPDATE_DATE in VARCHAR2,
208 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
209 X_CUSTOM_MODE in VARCHAR2 default null)
210 return boolean as
211 begin
215 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
212 return AME_SEED_UTILITY.MERGE_ROW_TEST
213 (X_OWNER => X_OWNER
214 ,X_CURRENT_OWNER => X_CURRENT_OWNER
216 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
217 ,X_CUSTOM_MODE => X_CUSTOM_MODE
218 );
219 end DO_UPDATE_INSERT;
220 procedure CHANGE_RULE_ATTR_USE_COUNT(X_RULE_ID ame_action_usages.rule_id%type) as
221 CURSOR CSR_GET_ITEM_IDS
222 (
223 X_RULE_ID in integer
224 ) is
225 select ACA.APPLICATION_ID
226 from AME_CALLING_APPS ACA,
227 AME_RULE_USAGES ARU
228 where ACA.APPLICATION_ID = ARU.ITEM_ID
229 and ARU.RULE_ID = X_RULE_ID
230 and sysdate between ARU.START_DATE
231 and nvl(ARU.END_DATE - (1/86400), sysdate);
232 begin
233 for TEMP_APPLICATION_ID in CSR_GET_ITEM_IDS(X_RULE_ID => X_RULE_ID) loop
234 AME_SEED_UTILITY.CHANGE_ATTRIBUTE_USAGES_COUNT(X_RULE_ID => X_RULE_ID
235 ,X_APPLICATION_ID => TEMP_APPLICATION_ID.APPLICATION_ID
236 );
237 end loop;
238 end CHANGE_RULE_ATTR_USE_COUNT;
239 procedure INSERT_ROW (
240 X_RULE_ID in NUMBER,
241 X_ACTION_ID in NUMBER,
242 X_CREATED_BY in NUMBER,
243 X_CREATION_DATE in DATE,
244 X_LAST_UPDATED_BY in NUMBER,
245 X_LAST_UPDATE_DATE in DATE,
246 X_LAST_UPDATE_LOGIN in NUMBER,
247 X_START_DATE in DATE,
248 X_OBJECT_VERSION_NUMBER in NUMBER)
249 is
250 lockHandle varchar2(500);
251 returnValue integer;
252 begin
253 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_ACTION_USAGES.'||X_RULE_ID||X_ACTION_ID
254 ,lockhandle => lockHandle);
255 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0
256 ,release_on_commit => true);
257 if returnValue = 0 then
258 insert into AME_ACTION_USAGES
259 (
260 RULE_ID,
261 ACTION_ID,
262 CREATED_BY,
263 CREATION_DATE,
264 LAST_UPDATED_BY,
265 LAST_UPDATE_DATE,
266 LAST_UPDATE_LOGIN,
267 START_DATE,
268 END_DATE,
269 OBJECT_VERSION_NUMBER
270 ) select
271 X_RULE_ID,
272 X_ACTION_ID,
273 X_CREATED_BY,
274 X_CREATION_DATE,
275 X_LAST_UPDATED_BY,
276 X_LAST_UPDATE_DATE,
277 X_LAST_UPDATE_LOGIN,
278 X_START_DATE,
282 where not exists (select NULL
279 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
280 X_OBJECT_VERSION_NUMBER
281 from sys.dual
283 from AME_ACTION_USAGES
284 where RULE_ID = X_RULE_ID
285 and ACTION_ID = X_ACTION_ID
286 and sysdate between START_DATE
287 and nvl(END_DATE - (1/86400), sysdate));
288 CHANGE_RULE_ATTR_USE_COUNT(X_RULE_ID => X_RULE_ID);
289 end if;
290 end INSERT_ROW;
291
292 procedure UPDATE_ROW (
293 X_ACTION_USAGE_ROWID in VARCHAR2,
294 X_END_DATE in DATE)
295 is
296 begin
297 update AME_ACTION_USAGES set
298 END_DATE = X_END_DATE
299 where ROWID = X_ACTION_USAGE_ROWID;
300 end UPDATE_ROW;
301 procedure DELETE_ROW (
302 X_RULE_ID in NUMBER,
303 X_ACTION_ID in NUMBER
304 ) is
305 begin
306 delete from AME_ACTION_USAGES
307 where RULE_ID = X_RULE_ID
308 and ACTION_ID = X_ACTION_ID;
309 if (sql%notfound) then
310 raise no_data_found;
311 end if;
312 end DELETE_ROW;
313 procedure FORCE_UPDATE_ROW (
314 X_ROWID in VARCHAR2,
315 X_CREATED_BY in NUMBER,
316 X_CREATION_DATE in DATE,
317 X_LAST_UPDATED_BY in NUMBER,
318 X_LAST_UPDATE_DATE in DATE,
319 X_LAST_UPDATE_LOGIN in NUMBER,
320 X_START_DATE in DATE,
321 X_END_DATE in DATE,
322 X_OBJECT_VERSION_NUMBER in NUMBER
323 ) is
324 begin
325 update AME_ACTION_USAGES
326 set CREATED_BY = X_CREATED_BY,
327 CREATION_DATE = X_CREATION_DATE,
328 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
329 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
330 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
331 START_DATE = X_START_DATE,
332 END_DATE = X_END_DATE,
333 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
334 where ROWID = X_ROWID;
335 end FORCE_UPDATE_ROW;
336 procedure LOAD_ROW (
337 X_RULE_KEY in VARCHAR2,
338 X_ACTION_TYPE_NAME in VARCHAR2,
339 X_PARAMETER in VARCHAR2,
340 X_OWNER in VARCHAR2,
341 X_LAST_UPDATE_DATE in VARCHAR2,
342 X_CUSTOM_MODE in VARCHAR2
343 )
344 is
345 X_ACTION_ID NUMBER;
346 X_ACTION_TYPE_ID NUMBER;
347 X_ACTION_USAGE_ROWID ROWID;
348 X_CREATED_BY NUMBER;
349 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
350 X_CURRENT_OWNER NUMBER;
351 X_LAST_UPDATED_BY NUMBER;
352 X_LAST_UPDATE_LOGIN NUMBER;
353 X_APPROVAL_GROUP_ID NUMBER;
354 X_RULE_ID NUMBER:= null;
355 X_CURRENT_OVN NUMBER;
356 X_RULE_ROWID ROWID;
357 begin
358 X_AME_INSTALLATION_LEVEL:= fnd_profile.value('AME_INSTALLATION_LEVEL');
359 -- if AME 11510 full patch is not applied return
365 KEY_TO_IDS (
360 if X_AME_INSTALLATION_LEVEL is null then
361 return;
362 end if;
363 VALIDATE_RULE_TYPE (X_RULE_KEY);
364 -- retrieve information for the current row
366 X_RULE_KEY,
367 X_ACTION_TYPE_NAME,
368 X_PARAMETER,
369 X_RULE_ID,
370 X_ACTION_ID,
371 X_ACTION_USAGE_ROWID,
372 X_ACTION_TYPE_ID,
373 X_APPROVAL_GROUP_ID,
374 X_CURRENT_OWNER,
375 X_CURRENT_LAST_UPDATE_DATE,
376 X_CURRENT_OVN,
377 X_RULE_ROWID
378 );
379 -- obtain who column details
380 OWNER_TO_WHO (
381 X_OWNER,
382 X_CREATED_BY,
383 X_LAST_UPDATED_BY,
384 X_LAST_UPDATE_LOGIN
385 );
386 begin
387 -- the current row was not found insert a new row
388 if (X_ACTION_ID is not null)
389 and (X_RULE_ID is not null)
390 and (X_ACTION_USAGE_ROWID is null and X_RULE_ROWID is null)
391 then
392 INSERT_ROW (
393 X_RULE_ID,
394 X_ACTION_ID,
395 X_CREATED_BY,
396 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
397 X_LAST_UPDATED_BY,
398 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
399 X_LAST_UPDATE_LOGIN,
400 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
401 1);
402 AME_SEED_UTILITY.create_parallel_config
403 (X_ACTION_TYPE_ID
404 ,X_ACTION_TYPE_NAME
405 ,X_ACTION_ID
406 ,X_APPROVAL_GROUP_ID
407 );
408 end if;
409 -- the current row was found end date the current row
410 -- insert a row with the same action type id
411 if (X_ACTION_USAGE_ROWID is not null)
412 then
413 if X_CUSTOM_MODE = 'FORCE' then
414 FORCE_UPDATE_ROW (
415 X_ACTION_USAGE_ROWID,
416 X_CREATED_BY,
417 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
418 X_LAST_UPDATED_BY,
419 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
420 X_LAST_UPDATE_LOGIN,
421 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
422 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
423 X_CURRENT_OVN + 1);
424 end if;
425 end if;
426 end;
427 exception
428 when others then
429 ame_util.runtimeException('ame_action_usages_api',
430 'load_row',
431 sqlcode,
432 sqlerrm);
433 raise;
434 end LOAD_ROW;
435
436 END AME_ACTION_USAGES_API;
[Home] [Help]