1 PACKAGE BODY AME_ATTRIBUTE_USAGES_API AS
2 /* $Header: ameauapi.pkb 120.3 2006/03/10 07:19:52 pvelugul noship $ */
3 X_AME_INSTALLATION_LEVEL varchar2(255);
4
5 function CALCULATE_USE_COUNT
6 (X_ATTRIBUTE_ID ame_attribute_usages.attribute_id%type,
7 X_APPLICATION_ID ame_attribute_usages.application_id%type)
8 return integer as
9 cursor RULE_CURSOR
10 (X_APPLICATION_ID ame_attribute_usages.application_id%type) is
11 select AME_RULE_USAGES.RULE_ID, AME_RULES.ACTION_ID
12 from AME_RULES, AME_RULE_USAGES
13 where AME_RULES.RULE_ID = AME_RULE_USAGES.RULE_ID
14 and AME_RULE_USAGES.ITEM_ID = X_APPLICATION_ID
15 and ((sysdate between AME_RULES.START_DATE
16 and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
17 or (sysdate < AME_RULES.START_DATE
18 and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
19 AME_RULES.START_DATE + (1/86400))))
20 and ((sysdate between AME_RULE_USAGES.START_DATE
21 and nvl(AME_RULE_USAGES.END_DATE - (1/86400), sysdate))
22 or (sysdate < AME_RULE_USAGES.START_DATE
23 and AME_RULE_USAGES.START_DATE < nvl(AME_RULE_USAGES.END_DATE,
24 AME_RULE_USAGES.START_DATE + (1/86400))));
25 MANDATORY_COUNT integer;
26 NEW_USE_COUNT integer;
27 RULE_COUNT integer;
28 TEMP_COUNT integer;
29 begin
30 NEW_USE_COUNT := 0;
31 select COUNT(*)
32 into MANDATORY_COUNT
33 from AME_MANDATORY_ATTRIBUTES
34 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
35 and ACTION_TYPE_ID = ame_util.mandAttActionTypeId
36 and SYSDATE between START_DATE
37 and nvl(END_DATE - (1/86400), sysdate);
38 if (MANDATORY_COUNT > 0) then
39 NEW_USE_COUNT := 0;
40 else
41 for TEMPRULE in RULE_CURSOR(X_APPLICATION_ID => X_APPLICATION_ID) loop
42 select count(*)
43 into TEMP_COUNT
44 from AME_CONDITIONS,
45 AME_CONDITION_USAGES
46 where AME_CONDITIONS.ATTRIBUTE_ID = X_ATTRIBUTE_ID
47 and AME_CONDITIONS.CONDITION_ID = AME_CONDITION_USAGES.CONDITION_ID
48 and AME_CONDITION_USAGES.RULE_ID = TEMPRULE.RULE_ID
49 and sysdate between AME_CONDITIONS.START_DATE
50 and nvl(AME_CONDITIONS.END_DATE - (1/86400), sysdate)
51 and ((sysdate between AME_CONDITION_USAGES.START_DATE
52 and nvl(AME_CONDITION_USAGES.END_DATE - (1/86400), sysdate))
53 or (sysdate < AME_CONDITION_USAGES.START_DATE
54 and AME_CONDITION_USAGES.START_DATE <
55 nvl(AME_CONDITION_USAGES.END_DATE,
56 AME_CONDITION_USAGES.START_DATE + (1/86400))));
57 if(TEMP_COUNT > 0) then
58 NEW_USE_COUNT := NEW_USE_COUNT + 1;
59 else
60 if TEMPRULE.ACTION_ID is null then
61 -- action_id is already migrated from ame_rules to ame_action_usages
62 select count(*)
63 into TEMP_COUNT
64 from AME_MANDATORY_ATTRIBUTES,
65 AME_ACTIONS,
66 AME_ACTION_USAGES
67 where AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID
68 and AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID =
69 AME_ACTIONS.ACTION_TYPE_ID
70 and AME_ACTIONS.ACTION_ID = AME_ACTION_USAGES.ACTION_ID
71 and AME_ACTION_USAGES.RULE_ID = TEMPRULE.RULE_ID
72 and sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE
73 and nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate)
74 and sysdate between AME_ACTIONS.START_DATE
75 and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
76 and ((sysdate between AME_ACTION_USAGES.START_DATE
77 and nvl(AME_ACTION_USAGES.END_DATE - (1/86400), sysdate))
78 or (sysdate < AME_ACTION_USAGES.START_DATE
79 and AME_ACTION_USAGES.START_DATE < nvl(AME_ACTION_USAGES.END_DATE,
80 AME_ACTION_USAGES.START_DATE + (1/86400))));
81 else
82 -- action_id is yet to be migrated from ame_rules to ame_action_usages
83 select count(*)
84 into TEMP_COUNT
85 from AME_MANDATORY_ATTRIBUTES,
86 AME_ACTIONS,
87 AME_RULES
88 where AME_MANDATORY_ATTRIBUTES.ATTRIBUTE_ID = X_ATTRIBUTE_ID
89 and AME_MANDATORY_ATTRIBUTES.ACTION_TYPE_ID =
90 AME_ACTIONS.ACTION_TYPE_ID
91 and AME_ACTIONS.ACTION_ID = AME_RULES.ACTION_ID
92 and AME_RULES.RULE_ID = TEMPRULE.RULE_ID
93 and sysdate between AME_MANDATORY_ATTRIBUTES.START_DATE
94 and nvl(AME_MANDATORY_ATTRIBUTES.END_DATE - (1/86400), sysdate)
95 and sysdate between AME_ACTIONS.START_DATE
96 and nvl(AME_ACTIONS.END_DATE - (1/86400), sysdate)
97 and ((sysdate between AME_RULES.START_DATE
98 and nvl(AME_RULES.END_DATE - (1/86400), sysdate))
99 or (sysdate < AME_RULES.START_DATE
100 and AME_RULES.START_DATE < nvl(AME_RULES.END_DATE,
101 AME_RULES.START_DATE + (1/86400))));
102 end if;
103 if(TEMP_COUNT > 0) then
104 NEW_USE_COUNT := NEW_USE_COUNT + 1;
105 end if;
106 end if;
107 end loop;
108 end if;
109 return(NEW_USE_COUNT);
110 exception
111 when others then
112 ame_util.runtimeException('ame_attribute_usages_api',
113 'CALCULATE_USE_COUNT',
114 sqlcode,
115 sqlerrm);
116 raise;
117 return(null);
118 end CALCULATE_USE_COUNT;
119 function IS_SEED_USG_RULE_MODIFIED
120 (X_ATTRIBUTE_ID ame_attribute_usages.attribute_id%type,
121 X_APPLICATION_ID ame_attribute_usages.application_id%type)
122 return boolean as
123 --
124 -- Verify whether the attribute is a SEEDed attribute and is/was in use
125 --
126 cursor CSR_USAGE_IS_SEEDED is
127 select 'Y'
128 from AME_ATTRIBUTE_USAGES
129 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
130 and APPLICATION_ID = X_APPLICATION_ID
131 and LAST_UPDATED_BY not in (1,120)
132 and CREATED_BY in (1,120)
133 and sysdate between START_DATE
134 and nvl(END_DATE - (1/86400), sysdate)
135 and exists (select null
136 from AME_ATTRIBUTE_USAGES
137 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
138 and APPLICATION_ID = X_APPLICATION_ID
139 group by ATTRIBUTE_ID, APPLICATION_ID
140 having max(USE_COUNT) > 0)
141 and not exists (select null
142 from AME_ATTRIBUTES ATTR1,
143 AME_ATTRIBUTE_USAGES ATTRU1
144 where ATTR1.ATTRIBUTE_ID = X_ATTRIBUTE_ID
145 and ATTRU1.APPLICATION_ID = X_APPLICATION_ID
146 and ATTR1.ATTRIBUTE_ID = ATTRU1.ATTRIBUTE_ID
147 and ATTR1.LAST_UPDATED_BY not in (1,120)
148 and ATTR1.CREATION_DATE = ATTRU1.CREATION_DATE);
149 usageIsSeeded varchar2(1);
150 begin
151 open CSR_USAGE_IS_SEEDED;
152 fetch CSR_USAGE_IS_SEEDED
153 into usageIsSeeded;
154 if CSR_USAGE_IS_SEEDED%notfound then
155 return(false);
156 end if;
157 return(true);
158 exception
159 when others then
160 ame_util.runtimeException('ame_attribute_usages_api',
161 'IS_SEED_USG_RULE_MODIFIED',
162 sqlcode,
163 sqlerrm);
164 raise;
165 return(false);
166 end IS_SEED_USG_RULE_MODIFIED;
167 /*******************************************************************************************
168 PROCEDURE: PRESERVE_LINE_ITEM_ID_LIST
169 IN PARAMETERS : X_ATTRIBUTE_ID -> To get 'line_item'(Y/N) value from ame_attributes table
170 X_IS_STATIC -> Procedure written for query_string modification
171 and query_string exists only for dynamic usages
172 X_LINE_ITEM_ID_QUERY -> Query from calling_apps table whose occurance in
173 X_QUERY_STRING_INOUT will be replaced by
174 :lineItemIdList
175 OUT PARAMETER : X_QUERY_STRING_INOUT
176 FUNCTIONALITY : This procedure is called only for AME 1159 and prior instances of AME.
177 For dynamic usages of line item attributes, AME 11510 does not use
178 :lineItemIdList as a placeholder for line_item_id_list. Instead 11510
179 has the line_item_id_query as the subquery in the query_string.
180 When uploading 11510 format data into AME 1159 and prior instances we
181 need to search for this subquery in query_string and replace it by
182 :lineItemIdList.
183 Logic: Both query_string and line_item_id_query are converted into a single line format
184 by replacing all occurances of newline(10),carriage return(13) and tabspaces with
185 space(32). Then the line_item_id_query is truncated to eliminate order by clause.
186 The position of the line_item_id_query in the query_string is located and is
187 replaced by :line_item_id_list
188 *******************************************************************************************/
189 procedure REMOVE_EXTRA_SPACES(strIn in out nocopy varchar2) is
190 begin
191 loop
192 if trim(strIn) is null or instrb(strIn,fnd_global.local_chr(32)||fnd_global.local_chr(32)) = 0 then
193 exit;
194 end if;
195 strIn:=replace (strIn,fnd_global.local_chr(32)||fnd_global.local_chr(32),fnd_global.local_chr(32));
196 end loop;
197 end REMOVE_EXTRA_SPACES;
198 procedure PRESERVE_LINE_ITEM_ID_LIST(
199 X_ATTRIBUTE_ID number,
200 X_IS_STATIC varchar2,
201 X_LINE_ITEM_ID_QUERY varchar2,
202 X_QUERY_STRING_INOUT in out nocopy varchar2
203 ) is
204 X_LINE_ITEM varchar2(1);
205 queryString varchar2(4000);
206 lineItemQuery varchar2(4000);
207 lowerQueryString varchar2(4000);
208 lowerLineItemQuery varchar2(4000);
209 querySubstring varchar2(4000);
210 orderByPosition integer;
211 positionForLineItemIdList integer;
212 begin
213 --check if the line item id query and query string both are not null
214 if trim(X_LINE_ITEM_ID_QUERY) is null
215 or trim(X_QUERY_STRING_INOUT) is null then
216 return;
217 end if;
218 --if ldt is in prior AME 11510 format, return since no changes required
219 if instrb(X_LINE_ITEM_ID_QUERY,':lineItemIdList') > 0 then
220 return;
221 end if;
222 select LINE_ITEM
223 into X_LINE_ITEM
224 from ame_attributes
225 where ATTRIBUTE_ID=X_ATTRIBUTE_ID
226 and SYSDATE between START_DATE
227 and nvl(END_DATE - (1/86400), sysdate);
228 --return if this attribute is not line_item or if it has a static usage
229 if X_LINE_ITEM <> 'Y' OR X_IS_STATIC = 'Y' then
230 return;
231 end if;
232 queryString :=X_QUERY_STRING_INOUT;
233 lineItemQuery :=X_LINE_ITEM_ID_QUERY;
234
235 queryString := replace(queryString,fnd_global.local_chr(9),fnd_global.local_chr(32));
236 queryString := replace(queryString,fnd_global.local_chr(10),fnd_global.local_chr(32));
237 queryString := replace(queryString,fnd_global.local_chr(13),fnd_global.local_chr(32));
238 REMOVE_EXTRA_SPACES(queryString);
239 queryString := replace(queryString,'('||fnd_global.local_chr(32),'(');
240 queryString := replace(queryString,fnd_global.local_chr(32)||')',')');
241
242 lineItemQuery := replace(lineItemQuery,fnd_global.local_chr(9),fnd_global.local_chr(32));
243 lineItemQuery := replace(lineItemQuery,fnd_global.local_chr(10),fnd_global.local_chr(32));
244 lineItemQuery := replace(lineItemQuery,fnd_global.local_chr(13),fnd_global.local_chr(32));
245 REMOVE_EXTRA_SPACES(lineItemQuery);
246 --remove leading and trailing spaces from lineItemQuery
247 lineItemQuery := trim(lineItemQuery);
248
249 lowerQueryString :=lower(queryString);
250 lowerLineItemquery :=lower(lineItemQuery);
251
252 orderByPosition := instrb(lowerLineItemquery, 'order by', -1);
253 lineItemQuery := substrb(lineItemQuery, 1, orderByPosition -2);
254 lowerLineItemquery :=lower(lineItemQuery);
255
256 positionForLineItemIdList := instrb(lowerQueryString,lowerLineItemquery);
257
258 if(positionForLineItemIdList>0) then
259 --find the portion of the query_string which matches line_item_query in original CASE
260 querySubstring :=substrb(queryString,positionForLineItemIdList,lengthb(lineItemQuery));
261 X_QUERY_STRING_INOUT :=replace(queryString,'('||querySubstring||')',':lineItemIdList');
262 end if;
263 end PRESERVE_LINE_ITEM_ID_LIST;
264 procedure RECTIFY_RULE_MOD_SEED_USAGE
265 (X_ATTRIBUTE_ID in ame_attribute_usages.attribute_id%type,
266 X_APPLICATION_ID in ame_attribute_usages.application_id%type,
267 X_LAST_UPDATE_DATE in varchar2,
268 X_CURRENT_LAST_UPDATE_DATE in out nocopy varchar2 ) is
269 -- get all the seeded attributes impacted by the rules, when created first time
270 -- using the particular attribute
271 cursor ATTRIBUTE_USAGE_DATE_CUR (startDateIn date) is
272 select rowid,
273 END_DATE
274 from AME_ATTRIBUTE_USAGES
275 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
276 and APPLICATION_ID = X_APPLICATION_ID
277 and START_DATE >= STARTDATEIN
278 order by START_DATE;
279 creationDate date;
280 endDate date;
281 lastUpdateDate date;
282 minStartDate date;
283 oneSecond number := 1/86400;
284 recCounter integer;
285 startDate date;
286 begin
290 into minStartDate
287 if(to_date(X_CURRENT_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS') >=
288 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')) then
289 select min(START_DATE)
291 from AME_ATTRIBUTE_USAGES
292 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
293 and APPLICATION_ID = X_APPLICATION_ID
294 and LAST_UPDATED_BY not in (1,120);
295 recCounter := 0;
296 for rec in ATTRIBUTE_USAGE_DATE_CUR(startDateIn => minStartDate)
297 loop
298 creationDate := minStartDate + (recCounter*oneSecond);
299 startDate := creationDate;
300 lastUpdateDate := minStartDate + ((recCounter+1)*oneSecond);
301 if rec.end_date is null or rec.end_date = AME_SEED_UTILITY.END_OF_TIME then
302 endDate := AME_SEED_UTILITY.END_OF_TIME;
303 X_CURRENT_LAST_UPDATE_DATE := lastUpdateDate;
304 else
305 endDate := lastUpdateDate;
306 end if;
307 update ame_attribute_usages
308 set start_date = startDate,
309 end_date = endDate,
310 creation_date = creationDate,
311 last_update_date = lastUpdateDate
312 where rowid = rec.rowid;
313 recCounter := recCounter + 1;
314 end loop;
315 end if;
316 update AME_ATTRIBUTE_USAGES
317 set LAST_UPDATED_BY = 1
318 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
319 and APPLICATION_ID = X_APPLICATION_ID;
320 exception
321 when others then
322 ame_util.runtimeException('ame_attribute_usages_api',
323 'RECTIFY_RULE_MOD_SEED_USAGE',
324 sqlcode,
325 sqlerrm);
326 raise;
327 end RECTIFY_RULE_MOD_SEED_USAGE;
328 procedure OWNER_TO_WHO (
329 X_OWNER in VARCHAR2,
330 X_CREATED_BY out nocopy NUMBER,
331 X_LAST_UPDATED_BY out nocopy NUMBER,
332 X_LAST_UPDATE_LOGIN out nocopy NUMBER
333 ) is
334 begin
335 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
336 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
337 X_LAST_UPDATE_LOGIN := 0;
338 end OWNER_TO_WHO;
339 procedure VALIDATE_IS_STATIC (
340 X_IS_STATIC in VARCHAR2
341 )
342 is
343 invalidIsStaticException exception;
344 errorCode integer;
345 errorMessage ame_util.longestStringType;
346 begin
347 if X_IS_STATIC NOT in
348 (ame_util.booleanFalse, ame_util.booleanTrue)
349 then
350 raise invalidIsStaticException;
351 end if;
352 exception
353 when invalidIsStaticException then
354 errorCode := -20001;
355 errorMessage :=
356 'OAM is attempting to upload an attribute usage that is neither static or dynamic. ';
357 ame_util.runtimeException(packageNameIn => 'ame_attribute_usages_api',
358 routineNameIn => 'validate_is_static',
359 exceptionNumberIn => errorCode,
360 exceptionStringIn => errorMessage);
361 raise_application_error(errorCode,
362 errorMessage);
363 when others then
364 ame_util.runtimeException('ame_attribute_usages_api',
365 'validate_is_static',
366 sqlcode,
367 sqlerrm);
368 raise;
369 end VALIDATE_IS_STATIC;
370 procedure VALIDATE_REJECTION (
371 X_QUERY_STRING in VARCHAR2
372 )
373 is
374 invalidRejectionException exception;
375 errorCode integer;
376 errorMessage ame_util.longestStringType;
377 begin
378 if X_QUERY_STRING NOT in
379 (ame_util.continueAllOtherItems,
380 ame_util.continueOtherSubItems,
381 ame_util.stopAllItems)
382 then
383 raise invalidRejectionException;
384 end if;
385 exception
386 when invalidRejectionException then
387 errorCode := -20001;
388 errorMessage :=
389 'OAM is attempting to upload a REJECTION RESPONSE attribute with an invalid usage. ';
390 ame_util.runtimeException(packageNameIn => 'ame_attribute_usages_api',
391 routineNameIn => 'validate_rejection',
392 exceptionNumberIn => errorCode,
393 exceptionStringIn => errorMessage);
394 raise_application_error(errorCode,
395 errorMessage);
396 when others then
397 ame_util.runtimeException('ame_attribute_usages_api',
398 'validate_rejection',
399 sqlcode,
400 sqlerrm);
401 raise;
402 end VALIDATE_REJECTION;
403 procedure KEY_TO_IDS (
404 X_ATTRIBUTE_NAME in VARCHAR2,
405 X_APPLICATION_NAME in VARCHAR2,
406 X_VALUE_SET_NAME in VARCHAR2,
407 X_USAGES_ROWID out nocopy VARCHAR2,
408 X_ATTRIBUTE_ID out nocopy NUMBER,
409 X_APPLICATION_ID out nocopy NUMBER,
410 X_VALUE_SET_ID out nocopy NUMBER,
411 X_CURRENT_USER_EDITABLE out nocopy VARCHAR2,
412 X_CURRENT_OWNER out nocopy NUMBER,
413 X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
417 cursor CSR_GET_ATTRIBUTE_ID
414 X_LINE_ITEM_ID_QUERY out nocopy VARCHAR2,
415 X_CURRENT_OVN out nocopy NUMBER
416 ) is
418 (
419 X_ATTRIBUTE_NAME in VARCHAR2
420 ) is
421 select ATTRIBUTE_ID
422 from AME_ATTRIBUTES
423 where NAME = X_ATTRIBUTE_NAME
424 and sysdate between START_DATE
425 and nvl(END_DATE - (1/86400), sysdate);
426
427 cursor CSR_GET_APPLICATION_ID
428 (
429 X_APPLICATION_NAME in VARCHAR2
430 ) is
431 select APPLICATION_ID, LINE_ITEM_ID_QUERY
432 from AME_CALLING_APPS
433 where APPLICATION_NAME = X_APPLICATION_NAME
434 and sysdate between START_DATE
435 and nvl(END_DATE - (1/86400), sysdate);
436
437 cursor CSR_GET_CURRENT_USAGE
438 (
439 X_ATTRIBUTE_NAME in varchar2,
440 X_APPLICATION_NAME in varchar2
441 ) is
442 select ROWID, USER_EDITABLE,
443 LAST_UPDATED_BY,
444 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
445 nvl(OBJECT_VERSION_NUMBER,1)
446 from AME_ATTRIBUTE_USAGES
447 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
448 and APPLICATION_ID = X_APPLICATION_ID
449 and sysdate between START_DATE
450 and nvl(END_DATE - (1/86400), sysdate);
451
452 cursor CSR_VALUE_SET
453 (
454 X_VALUE_SET_NAME in varchar2
455 ) is
456 select FLEX_VALUE_SET_ID
457 from FND_FLEX_VALUE_SETS
458 where FLEX_VALUE_SET_NAME = X_VALUE_SET_NAME;
459 begin
460 X_CURRENT_OVN := 1;
461 open CSR_GET_ATTRIBUTE_ID (
462 X_ATTRIBUTE_NAME
463 );
464 fetch CSR_GET_ATTRIBUTE_ID into X_ATTRIBUTE_ID;
465 if (CSR_GET_ATTRIBUTE_ID%notfound) then
466 X_ATTRIBUTE_ID := null;
467 end if;
468 close CSR_GET_ATTRIBUTE_ID;
469
470 open CSR_GET_APPLICATION_ID (
471 X_APPLICATION_NAME
472 );
473 fetch CSR_GET_APPLICATION_ID into X_APPLICATION_ID, X_LINE_ITEM_ID_QUERY;
474 if (CSR_GET_APPLICATION_ID%notfound) then
475 X_APPLICATION_ID := null;
476 end if;
477 close CSR_GET_APPLICATION_ID;
478
479 if (X_APPLICATION_ID is not null) and
480 (X_ATTRIBUTE_ID is not null) then
481 open CSR_GET_CURRENT_USAGE (
482 X_ATTRIBUTE_ID,
483 X_APPLICATION_ID
484 );
485 fetch CSR_GET_CURRENT_USAGE into X_USAGES_ROWID,
486 X_CURRENT_USER_EDITABLE,
487 X_CURRENT_OWNER,
488 X_CURRENT_LAST_UPDATE_DATE,
489 X_CURRENT_OVN;
490 if (CSR_GET_CURRENT_USAGE%notfound) then
491 X_USAGES_ROWID := null;
492 end if;
493 close CSR_GET_CURRENT_USAGE;
494 else
495 X_USAGES_ROWID := null;
496 end if;
497
498 if X_VALUE_SET_NAME is null then
499 X_VALUE_SET_ID := null;
500 else
501 open CSR_VALUE_SET(X_VALUE_SET_NAME);
502 fetch CSR_VALUE_SET into X_VALUE_SET_ID;
503 if CSR_VALUE_SET%notfound then
504 X_VALUE_SET_ID := NULL;
505 end if;
506 close CSR_VALUE_SET;
507 end if;
508 end KEY_TO_IDS;
509 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
510 X_CURRENT_OWNER in NUMBER,
511 X_LAST_UPDATE_DATE in VARCHAR2,
512 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
513 X_CUSTOM_MODE in VARCHAR2 default null)
514 return boolean as
515 begin
516 return AME_SEED_UTILITY.MERGE_ROW_TEST
517 (X_OWNER => X_OWNER
518 ,X_CURRENT_OWNER => X_CURRENT_OWNER
519 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
520 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
521 ,X_CUSTOM_MODE => X_CUSTOM_MODE
522 );
523 end DO_UPDATE_INSERT;
524
525 procedure GET_LINE_ITEM_CLASS_QUERY(
526 X_APPLICATION_ID in NUMBER,
527 X_LINE_ITEM_ID_QUERY_OUT out nocopy VARCHAR2
528 ) is
529 cursor CSR_GET_LINE_ITEM_CLASS_QUERY is
530 select ICLU.ITEM_ID_QUERY
531 from AME_ITEM_CLASSES ICLS,
532 AME_ITEM_CLASS_USAGES ICLU
533 where ICLU.APPLICATION_ID = X_APPLICATION_ID
534 and ICLS.ITEM_CLASS_ID = ICLU.ITEM_CLASS_ID
535 and ICLS.NAME = ame_util.lineitemitemclassname
536 and sysdate between ICLS.START_DATE and nvl(ICLS.END_DATE - (1/86400), sysdate)
537 and sysdate between ICLU.START_DATE and nvl(ICLU.END_DATE - (1/86400), sysdate);
538 begin
539 open CSR_GET_LINE_ITEM_CLASS_QUERY;
540 fetch CSR_GET_LINE_ITEM_CLASS_QUERY
541 into X_LINE_ITEM_ID_QUERY_OUT;
542 if CSR_GET_LINE_ITEM_CLASS_QUERY%notfound then
543 X_LINE_ITEM_ID_QUERY_OUT := null;
544 end if;
545 close CSR_GET_LINE_ITEM_CLASS_QUERY;
546 end GET_LINE_ITEM_CLASS_QUERY;
547
548 procedure QUERY_STRING_VALIDATION(
549 X_QUERY_STRING in VARCHAR2,
550 X_LINE_ITEM_ID_QUERY in VARCHAR2,
551 X_QUERY_STRING_OUT out nocopy VARCHAR2
552 ) is
553 X_BY_POSITION INTEGER;
554 X_ORDER_BY_POSITION INTEGER;
555 X_ORDER_POSITION INTEGER;
556 X_LINE_ITEM_QUERY ame_calling_apps.line_item_id_query%type;
557 X_TEMP_LINE_QUERY ame_calling_apps.line_item_id_query%type;
558 begin
562 X_ORDER_BY_POSITION := instrb(X_TEMP_LINE_QUERY, 'ORDER BY', -1);
559 /* remove the order by clause from the line_item_id_query before
560 replacing the place holder column :lineItemIdList with '('||X_LINE_ITEM_ID_QUERY||')'*/
561 X_TEMP_LINE_QUERY := upper(X_LINE_ITEM_ID_QUERY);
563 X_ORDER_POSITION := instrb(X_TEMP_LINE_QUERY, 'ORDER', -1);
564 X_BY_POSITION := instrb(X_TEMP_LINE_QUERY, 'BY', X_ORDER_POSITION+5);
565 if X_ORDER_BY_POSITION > 0 then
566 X_LINE_ITEM_QUERY := substrb(X_LINE_ITEM_ID_QUERY,1, X_ORDER_BY_POSITION-1);
567 elsif X_ORDER_POSITION > 0 and X_BY_POSITION > 0 then
568 -- Replace the blank space with tab space
569 X_TEMP_LINE_QUERY := replace(X_TEMP_LINE_QUERY, fnd_global.local_chr(9), fnd_global.local_chr(32));
570 -- Replace the blank space with new-line
571 X_TEMP_LINE_QUERY := replace(X_TEMP_LINE_QUERY, fnd_global.local_chr(10), fnd_global.local_chr(32));
572 -- Replace the blank space with carraige-return
573 X_TEMP_LINE_QUERY := replace(X_TEMP_LINE_QUERY, fnd_global.local_chr(13), fnd_global.local_chr(32));
574 -- Extract the characters between order and by
575 X_TEMP_LINE_QUERY := substrb(X_TEMP_LINE_QUERY, X_ORDER_POSITION+5, X_BY_POSITION - (X_ORDER_POSITION+5));
576 if trim(X_TEMP_LINE_QUERY) is null then
577 X_LINE_ITEM_QUERY := substrb(X_LINE_ITEM_ID_QUERY, 1, X_ORDER_POSITION -1);
578 end if;
579 end if;
580 X_QUERY_STRING_OUT := replace(X_QUERY_STRING, ':lineItemIdList',
581 '('|| X_LINE_ITEM_QUERY ||')');
582 end QUERY_STRING_VALIDATION;
583
584 procedure INSERT_ROW (
585 X_ATTRIBUTE_ID in NUMBER,
586 X_APPLICATION_ID in NUMBER,
587 X_QUERY_STRING in VARCHAR2,
588 X_USE_COUNT in NUMBER,
589 X_IS_STATIC in VARCHAR2,
590 X_CREATED_BY in NUMBER,
591 X_CREATION_DATE in DATE,
592 X_LAST_UPDATED_BY in NUMBER,
593 X_LAST_UPDATE_DATE in DATE,
594 X_LAST_UPDATE_LOGIN in NUMBER,
595 X_START_DATE in DATE,
596 X_USER_EDITABLE in VARCHAR2,
597 X_VALUE_SET_ID in NUMBER,
598 X_OBJECT_VERSION_NUMBER in NUMBER)
599 is
600 begin
601 insert into AME_ATTRIBUTE_USAGES
602 (ATTRIBUTE_ID,
603 APPLICATION_ID,
604 QUERY_STRING,
605 USE_COUNT,
606 IS_STATIC,
607 CREATED_BY,
608 CREATION_DATE,
609 LAST_UPDATED_BY,
610 LAST_UPDATE_DATE,
611 LAST_UPDATE_LOGIN,
612 START_DATE,
613 END_DATE,
614 USER_EDITABLE,
615 VALUE_SET_ID,
616 OBJECT_VERSION_NUMBER
617 ) values (
618 X_ATTRIBUTE_ID,
619 X_APPLICATION_ID,
620 X_QUERY_STRING,
621 X_USE_COUNT,
622 X_IS_STATIC,
623 X_CREATED_BY,
624 X_CREATION_DATE,
625 X_LAST_UPDATED_BY,
626 X_LAST_UPDATE_DATE,
627 X_LAST_UPDATE_LOGIN,
628 X_START_DATE,
629 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
630 X_USER_EDITABLE,
631 X_VALUE_SET_ID,
632 X_OBJECT_VERSION_NUMBER
633 );
634 end INSERT_ROW;
635
636 procedure UPDATE_ROW (
637 X_USAGES_ROWID in VARCHAR2,
638 X_END_DATE in DATE)
639 is
640 begin
641 update AME_ATTRIBUTE_USAGES set
642 END_DATE = X_END_DATE
643 where ROWID = X_USAGES_ROWID;
644 end UPDATE_ROW;
645
646 procedure FORCE_UPDATE_ROW (
647 X_ROWID in VARCHAR2,
648 X_QUERY_STRING in VARCHAR2,
649 X_USE_COUNT in NUMBER,
650 X_IS_STATIC in VARCHAR2,
651 X_USER_EDITABLE in VARCHAR2,
652 X_VALUE_SET_ID in NUMBER,
653 X_CREATED_BY in NUMBER,
654 X_CREATION_DATE in DATE,
655 X_LAST_UPDATED_BY in NUMBER,
656 X_LAST_UPDATE_DATE in DATE,
657 X_LAST_UPDATE_LOGIN in NUMBER,
658 X_START_DATE in DATE,
659 X_END_DATE in DATE,
660 X_OBJECT_VERSION_NUMBER in NUMBER
661 ) is
662 begin
663 update AME_ATTRIBUTE_USAGES
664 set QUERY_STRING = X_QUERY_STRING,
665 USE_COUNT = X_USE_COUNT,
666 IS_STATIC = X_IS_STATIC,
667 USER_EDITABLE = X_USER_EDITABLE,
668 VALUE_SET_ID = X_VALUE_SET_ID,
669 CREATED_BY = X_CREATED_BY,
670 CREATION_DATE = X_CREATION_DATE,
671 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
672 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
673 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
674 START_DATE = X_START_DATE,
675 END_DATE = X_END_DATE,
676 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
677 where ROWID = X_ROWID;
678 end FORCE_UPDATE_ROW;
679
680 procedure DELETE_ROW (
681 X_ATTRIBUTE_ID in NUMBER,
682 X_APPLICATION_ID in NUMBER
683 ) is
684 begin
685 delete from AME_ATTRIBUTE_USAGES
686 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
687 and APPLICATION_ID = X_APPLICATION_ID;
688
689 if (sql%notfound) then
690 raise no_data_found;
691 end if;
692 end DELETE_ROW;
693
694 procedure LOAD_ROW (
695 X_ATTRIBUTE_NAME in VARCHAR2,
696 X_APPLICATION_NAME in VARCHAR2,
697 X_QUERY_STRING in VARCHAR2,
698 X_USER_EDITABLE in VARCHAR2,
699 X_IS_STATIC in VARCHAR2,
700 X_USE_COUNT in VARCHAR2,
701 X_VALUE_SET_NAME in VARCHAR2,
702 X_OWNER in VARCHAR2,
703 X_LAST_UPDATE_DATE in VARCHAR2,
704 X_CUSTOM_MODE in VARCHAR2
705 )
706 is
707 X_ATTRIBUTE_ID NUMBER;
708 X_APPLICATION_ID NUMBER;
709 X_CREATED_BY NUMBER;
710 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
711 X_CURRENT_OWNER VARCHAR2(100);
712 X_CALCULATED_USE_COUNT ame_attribute_usages.use_count%type;
713 X_CURRENT_USER_EDITABLE ame_attribute_usages.user_editable%type;
714 X_LAST_UPDATED_BY NUMBER;
715 X_LAST_UPDATE_LOGIN NUMBER;
716 X_LINE_ATTRIBUTE_NAME ame_attributes.name%type := null;
717 X_LINE_ITEM_ID_QUERY ame_attribute_usages.query_string%type;
718 X_QUERY_STRING_OUT ame_attribute_usages.query_string%type;
719 X_USAGES_ROWID ROWID;
720 X_VALUE_SET_ID NUMBER;
721 X_CURRENT_OVN NUMBER;
722 begin
723 --
724 -- checking for EVALUATE_PRIORITIES_PER_LINE_ITEM
728 --check if ame full patch for 11510 is applied
725 -- and USE_RESTRICTIVE_LINE_ITEM_EVALUATION attributes
726 -- being uploaded
727 --
729 X_AME_INSTALLATION_LEVEL:= fnd_profile.value('AME_INSTALLATION_LEVEL');
730 --if full patch is not applied, and 11510 ldt is uploaded, do not upload use_workflow and rejection_response usages
731 if (X_AME_INSTALLATION_LEVEL is null) and
732 (X_ATTRIBUTE_NAME in (ame_util.useWorkflowAttribute
733 ,ame_util.rejectionResponseAttribute
734 ,'REPEAT_SUBSTITUTIONS'
735 ,ame_util.nonDefStartingPointPosAttr
736 ,ame_util.nonDefPosStructureAttr
737 ,ame_util.transactionReqPositionAttr
738 ,ame_util.topPositionIdAttribute)
739 ) then
740 return;
741 end if;
742
743 if X_AME_INSTALLATION_LEVEL is not null then
744 if X_ATTRIBUTE_NAME = ame_util.evalPrioritiesPerLIAttribute
745 then X_LINE_ATTRIBUTE_NAME := ame_util.evalPrioritiesPerItemAttribute;
746 end if;
747 if X_ATTRIBUTE_NAME = ame_util.restrictiveLIEvalAttribute
748 then X_LINE_ATTRIBUTE_NAME := ame_util.restrictiveItemEvalAttribute;
749 end if;
750 else
751 if X_ATTRIBUTE_NAME = ame_util.evalPrioritiesPerItemAttribute
752 then X_LINE_ATTRIBUTE_NAME := ame_util.evalPrioritiesPerLIAttribute;
753 end if;
754 if X_ATTRIBUTE_NAME = ame_util.restrictiveItemEvalAttribute
755 then X_LINE_ATTRIBUTE_NAME := ame_util.restrictiveLIEvalAttribute;
756 end if;
757 end if;
758 --
759 -- validation is_static column
760 VALIDATE_IS_STATIC (
761 X_IS_STATIC
762 );
763 -- validation rejection_response attributes
764 if X_ATTRIBUTE_NAME = ame_util.rejectionResponseAttribute then
765 VALIDATE_REJECTION(
766 X_QUERY_STRING
767 );
768 end if;
769 -- retrieve information for the current row
770 KEY_TO_IDS (
771 nvl(X_LINE_ATTRIBUTE_NAME,X_ATTRIBUTE_NAME),
772 X_APPLICATION_NAME,
773 X_VALUE_SET_NAME,
774 X_USAGES_ROWID,
775 X_ATTRIBUTE_ID,
776 X_APPLICATION_ID,
777 X_VALUE_SET_ID,
778 X_CURRENT_USER_EDITABLE,
779 X_CURRENT_OWNER,
780 X_CURRENT_LAST_UPDATE_DATE,
781 X_LINE_ITEM_ID_QUERY,
782 X_CURRENT_OVN);
783 -- obtain who column details
784 OWNER_TO_WHO (
785 X_OWNER,
786 X_CREATED_BY,
787 X_LAST_UPDATED_BY,
788 X_LAST_UPDATE_LOGIN
789 );
790 begin
791 -- the current row was not found insert a new row
792 -- and there is a valid application and valid attribute detected
793 if (X_ATTRIBUTE_ID is not null) and
794 (X_APPLICATION_ID is not null) then
798 if trim(X_QUERY_STRING_OUT) is not null then
795 -- validate the value of query string upon lineItemIdList entries if
796 -- ame11510 full patch is applied
797 X_QUERY_STRING_OUT := X_QUERY_STRING;
799 if X_AME_INSTALLATION_LEVEL is not null then
800 if (instrb(X_QUERY_STRING, ':lineItemIdList', -1) > 0) and
801 (X_LINE_ITEM_ID_QUERY is not null) then
802 QUERY_STRING_VALIDATION(
803 X_QUERY_STRING,
804 X_LINE_ITEM_ID_QUERY,
805 X_QUERY_STRING_OUT);
806 elsif (instrb(X_QUERY_STRING, ':lineItemIdList', -1) > 0) and
807 (X_LINE_ITEM_ID_QUERY is null) then
808 -- when 11510 patch is already applied, get LINE_ITEM_ID_QUERY from
809 -- ame_item_class_usages
810 GET_LINE_ITEM_CLASS_QUERY(X_APPLICATION_ID,
811 X_LINE_ITEM_ID_QUERY);
812 if X_LINE_ITEM_ID_QUERY is not null then
813 QUERY_STRING_VALIDATION(
814 X_QUERY_STRING,
815 X_LINE_ITEM_ID_QUERY,
816 X_QUERY_STRING_OUT);
817 end if;
818 end if;
819 else
820 PRESERVE_LINE_ITEM_ID_LIST(
821 X_ATTRIBUTE_ID => X_ATTRIBUTE_ID
822 ,X_IS_STATIC => X_IS_STATIC
823 ,X_LINE_ITEM_ID_QUERY => X_LINE_ITEM_ID_QUERY
824 ,X_QUERY_STRING_INOUT => X_QUERY_STRING_OUT
825 );
826 end if;
827 end if;
828 if (X_USAGES_ROWID is null) then
829 INSERT_ROW (
830 X_ATTRIBUTE_ID,
831 X_APPLICATION_ID,
832 X_QUERY_STRING_OUT,
833 to_number(X_USE_COUNT),
834 X_IS_STATIC,
835 X_CREATED_BY,
836 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
837 X_LAST_UPDATED_BY,
838 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
839 X_LAST_UPDATE_LOGIN,
840 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
841 X_USER_EDITABLE,
842 X_VALUE_SET_ID,
843 1);
844 -- the current row was found end date the current row
845 -- insert a row with the same attribute id
846 else
847 if(AME_SEED_UTILITY.IS_SEED_USER(X_CURRENT_OWNER) = false and
848 IS_SEED_USG_RULE_MODIFIED(X_ATTRIBUTE_ID => X_ATTRIBUTE_ID,
849 X_APPLICATION_ID => X_APPLICATION_ID)) then
850 RECTIFY_RULE_MOD_SEED_USAGE(X_ATTRIBUTE_ID => X_ATTRIBUTE_ID,
851 X_APPLICATION_ID => X_APPLICATION_ID,
852 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
853 X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE);
854 X_CURRENT_OWNER := AME_SEED_UTILITY.USER_ID_OF_SEED_USER;
855 end if;
856 if X_CUSTOM_MODE = 'FORCE' then
857 X_CALCULATED_USE_COUNT := CALCULATE_USE_COUNT(X_ATTRIBUTE_ID => X_ATTRIBUTE_ID,
858 X_APPLICATION_ID => X_APPLICATION_ID);
859 FORCE_UPDATE_ROW (
863 X_IS_STATIC,
860 X_USAGES_ROWID,
861 X_QUERY_STRING_OUT,
862 X_CALCULATED_USE_COUNT,
864 X_USER_EDITABLE,
865 X_VALUE_SET_ID,
866 X_CREATED_BY,
867 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
868 X_LAST_UPDATED_BY,
869 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
870 X_LAST_UPDATE_LOGIN,
871 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
872 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
873 X_CURRENT_OVN + 1);
874 else
875 if DO_UPDATE_INSERT
876 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
877 X_CURRENT_OWNER,
878 X_LAST_UPDATE_DATE,
879 X_CURRENT_LAST_UPDATE_DATE) then
880 UPDATE_ROW (
881 X_USAGES_ROWID,
882 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
883 X_CALCULATED_USE_COUNT := CALCULATE_USE_COUNT(X_ATTRIBUTE_ID => X_ATTRIBUTE_ID,
884 X_APPLICATION_ID => X_APPLICATION_ID);
885 INSERT_ROW (
886 X_ATTRIBUTE_ID,
887 X_APPLICATION_ID,
888 X_QUERY_STRING_OUT,
889 X_CALCULATED_USE_COUNT,
890 X_IS_STATIC,
891 X_CREATED_BY,
892 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
893 X_LAST_UPDATED_BY,
894 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
895 X_LAST_UPDATE_LOGIN,
896 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
897 X_USER_EDITABLE,
898 X_VALUE_SET_ID,
899 X_CURRENT_OVN + 1);
900 end if;
901 end if;
902 end if;
903 end if;
904 end;
905 exception
906 when others then
907 ame_util.runtimeException('ame_attribute_usages_api',
908 'load_row',
909 sqlcode,
910 sqlerrm);
911 raise;
912 end LOAD_ROW;
913
914 procedure LOAD_SEED_ROW
915 (X_ATTRIBUTE_NAME in varchar2
916 ,X_APPLICATION_NAME in varchar2
917 ,X_QUERY_STRING in varchar2
918 ,X_USER_EDITABLE in varchar2
919 ,X_IS_STATIC in varchar2
920 ,X_USE_COUNT in varchar2
921 ,X_VALUE_SET_NAME in varchar2
922 ,X_OWNER in varchar2
923 ,X_LAST_UPDATE_DATE in varchar2
924 ,X_UPLOAD_MODE in varchar2
925 ,X_CUSTOM_MODE in varchar2
926 ) as
927 begin
928 if X_UPLOAD_MODE = 'NLS' then
929 null;
930 else
931 LOAD_ROW
932 (X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME
933 ,X_APPLICATION_NAME => X_APPLICATION_NAME
934 ,X_QUERY_STRING => X_QUERY_STRING
935 ,X_USER_EDITABLE => X_USER_EDITABLE
936 ,X_IS_STATIC => X_IS_STATIC
937 ,X_USE_COUNT => X_USE_COUNT
938 ,X_VALUE_SET_NAME => X_VALUE_SET_NAME
939 ,X_OWNER => X_OWNER
940 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
941 ,X_CUSTOM_MODE => X_CUSTOM_MODE
942 );
943 end if;
944 end LOAD_SEED_ROW;
945 END AME_ATTRIBUTE_USAGES_API;
[Home] [Help]