[Home] [Help]
PACKAGE BODY: APPS.AME_ATTRIBUTES_API
Source
1 package BODY AME_ATTRIBUTES_API as
2 /* $Header: ameatapi.pkb 120.5.12010000.2 2008/12/26 11:01:04 prasashe ship $ */
3 procedure VALIDATE_ROW
4 (X_ATTRIBUTE_TYPE in varchar2) as
5 begin
6 if X_ATTRIBUTE_TYPE not in ('boolean'
7 ,'number'
8 ,'string'
9 ,'currency'
10 ,'date') then
11 raise_application_error (-20001,'AME is trying to upload a attribute of invalid type ' || X_ATTRIBUTE_TYPE);
12 end if;
13 end VALIDATE_ROW;
14
15 procedure KEY_TO_IDS
16 (X_ATTRIBUTE_NAME in varchar2
17 ,X_ORIG_SYSTEM in varchar2
18 ,X_ITEM_CLASS_NAME in varchar2
19 ,X_LINE_ITEM in out nocopy varchar2
20 ,X_APPROVER_TYPE_ID out nocopy number
21 ,X_ITEM_CLASS_ID out nocopy number
22 ) as
23 L_ORIG_SYSTEM AME_APPROVER_TYPES.ORIG_SYSTEM%TYPE;
24 begin
25 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is null then
26 X_APPROVER_TYPE_ID := null;
27 X_ITEM_CLASS_ID := 0;
28 if X_ITEM_CLASS_NAME is not null then
29 if X_ITEM_CLASS_NAME = 'header' then
30 X_LINE_ITEM := 'N';
31 else
32 X_LINE_ITEM := 'Y';
33 end if;
34 end if;
35 else
36 if X_ORIG_SYSTEM is null then
37 if X_ATTRIBUTE_NAME in ('FIRST_STARTING_POINT_PERSON_ID',
38 'JOB_LEVEL_NON_DEFAULT_STARTING_POINT_PERSON_ID',
39 'LINE_ITEM_STARTING_POINT_PERSON_ID',
40 'SECOND_STARTING_POINT_PERSON_ID',
41 'SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID',
42 'TOP_SUPERVISOR_PERSON_ID',
43 'TRANSACTION_REQUESTOR_PERSON_ID') then
44 L_ORIG_SYSTEM := 'PER';
45 elsif X_ATTRIBUTE_NAME in ('TRANSACTION_REQUESTOR_USER_ID') then
46 L_ORIG_SYSTEM := 'FND_USR';
47 else
48 L_ORIG_SYSTEM := null;
49 end if;
50 else
51 L_ORIG_SYSTEM := X_ORIG_SYSTEM;
52 end if;
53 if L_ORIG_SYSTEM is null then
54 X_APPROVER_TYPE_ID := null;
55 else
56 begin
57 select AAT.APPROVER_TYPE_ID
58 into X_APPROVER_TYPE_ID
59 from AME_APPROVER_TYPES AAT
60 where AAT.ORIG_SYSTEM = L_ORIG_SYSTEM
61 and sysdate between AAT.START_DATE and nvl(AAT.END_DATE - (1/86400),sysdate);
62 exception
63 when no_data_found then
64 X_APPROVER_TYPE_ID := null;
65 end;
66 end if;
67 if X_ITEM_CLASS_NAME is null and X_LINE_ITEM is not null then
68 if X_LINE_ITEM = 'Y' then
69 begin
70 X_LINE_ITEM := null;
71 select AIC.ITEM_CLASS_ID
72 into X_ITEM_CLASS_ID
73 from AME_ITEM_CLASSES AIC
74 where AIC.NAME = 'line item'
75 and sysdate between AIC.START_DATE and nvl(AIC.END_DATE - (1/86400),sysdate);
76 exception
77 when no_data_found then
78 X_ITEM_CLASS_ID := 2;
79 end;
80 else
81 begin
82 X_LINE_ITEM := null;
83 select AIC.ITEM_CLASS_ID
84 into X_ITEM_CLASS_ID
85 from AME_ITEM_CLASSES AIC
86 where AIC.NAME = 'header'
87 and sysdate between AIC.START_DATE and nvl(AIC.END_DATE - (1/86400),sysdate);
88 exception
89 when no_data_found then
90 X_ITEM_CLASS_ID := 1;
91 end;
92 end if;
93 else
94 begin
95 X_LINE_ITEM := null;
96 select AIC.ITEM_CLASS_ID
97 into X_ITEM_CLASS_ID
98 from AME_ITEM_CLASSES AIC
99 where AIC.NAME = X_ITEM_CLASS_NAME
100 and sysdate between AIC.START_DATE and nvl(AIC.END_DATE - (1/86400),sysdate);
101 exception
102 when no_data_found then
103 X_ITEM_CLASS_ID := 1;
104 end;
105 end if;
106 end if;
107 end KEY_TO_IDS;
108
109 procedure FORMAT_KEY
110 (X_ATTRIBUTE_NAME in out nocopy varchar2
111 ) as
112 begin
113 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is null then
114 if X_ATTRIBUTE_NAME = 'EVALUATE_PRIORITIES_PER_ITEM' then
115 X_ATTRIBUTE_NAME := 'EVALUATE_PRIORITIES_PER_LINE_ITEM';
116 elsif X_ATTRIBUTE_NAME = 'USE_RESTRICTIVE_ITEM_EVALUATION' then
117 X_ATTRIBUTE_NAME := 'USE_RESTRICTIVE_LINE_ITEM_EVALUATION';
118 end if;
119 else
120 if X_ATTRIBUTE_NAME = 'EVALUATE_PRIORITIES_PER_LINE_ITEM' then
121 X_ATTRIBUTE_NAME := 'EVALUATE_PRIORITIES_PER_ITEM';
122 elsif X_ATTRIBUTE_NAME = 'USE_RESTRICTIVE_LINE_ITEM_EVALUATION' then
123 X_ATTRIBUTE_NAME := 'USE_RESTRICTIVE_ITEM_EVALUATION';
124 end if;
125 end if;
126 end FORMAT_KEY;
127
128 procedure INSERT_ROW
129 (X_ATTRIBUTE_NAME in varchar2
130 ,X_ATTRIBUTE_TYPE in varchar2
131 ,X_DESCRIPTION in varchar2
132 ,X_LINE_ITEM in varchar2
133 ,X_ITEM_CLASS_ID in number
134 ,X_APPROVER_TYPE_ID in number
135 ,X_START_DATE in date
136 ,X_END_DATE in date
137 ,X_CREATED_BY in number
138 ,X_CREATION_DATE in date
139 ,X_LAST_UPDATED_BY in number
140 ,X_LAST_UPDATE_DATE in date
141 ,X_LAST_UPDATE_LOGIN in number
142 ,X_OBJECT_VERSION_NUMBER in number
143 ) as
144 X_ATTRIBUTE_ID number;
145 X_LOCK_HANDLE varchar2(500);
146 X_RETURN_VALUE number;
147 begin
148 DBMS_LOCK.ALLOCATE_UNIQUE
149 (LOCKNAME =>'AME_ATTRIBUTES.'||X_ATTRIBUTE_NAME
150 ,LOCKHANDLE => X_LOCK_HANDLE
151 );
152 X_RETURN_VALUE := DBMS_LOCK.REQUEST
153 (LOCKHANDLE => X_LOCK_HANDLE
154 ,TIMEOUT => 0
155 ,RELEASE_ON_COMMIT => true);
156
157 if X_RETURN_VALUE = 0 then
158 select AME_ATTRIBUTES_S.NEXTVAL
159 into X_ATTRIBUTE_ID
160 from dual;
161
162 insert into AME_ATTRIBUTES
163 (ATTRIBUTE_ID
164 ,NAME
165 ,ATTRIBUTE_TYPE
166 ,DESCRIPTION
167 ,LINE_ITEM
168 ,ITEM_CLASS_ID
169 ,APPROVER_TYPE_ID
170 ,CREATED_BY
171 ,CREATION_DATE
172 ,LAST_UPDATED_BY
173 ,LAST_UPDATE_DATE
174 ,LAST_UPDATE_LOGIN
175 ,START_DATE
176 ,END_DATE
177 ,OBJECT_VERSION_NUMBER
178 ) select X_ATTRIBUTE_ID,
179 X_ATTRIBUTE_NAME,
180 X_ATTRIBUTE_TYPE,
181 X_DESCRIPTION,
182 X_LINE_ITEM,
183 X_ITEM_CLASS_ID,
184 X_APPROVER_TYPE_ID,
185 X_CREATED_BY,
186 X_CREATION_DATE,
187 X_LAST_UPDATED_BY,
188 X_LAST_UPDATE_DATE,
189 X_LAST_UPDATE_LOGIN,
190 X_START_DATE,
191 X_END_DATE,
192 X_OBJECT_VERSION_NUMBER
193 from dual where not exists (select null
194 from AME_ATTRIBUTES
195 where NAME = X_ATTRIBUTE_NAME
196 and sysdate between START_DATE and nvl(END_DATE - (1/86400), sysdate));
197
198 if not AME_SEED_UTILITY.MLS_ENABLED then
199 return;
200 end if;
201
202 insert into AME_ATTRIBUTES_TL
203 (ATTRIBUTE_ID
204 ,DESCRIPTION
205 ,CREATED_BY
206 ,CREATION_DATE
207 ,LAST_UPDATED_BY
208 ,LAST_UPDATE_DATE
209 ,LAST_UPDATE_LOGIN
210 ,LANGUAGE
211 ,SOURCE_LANG
212 ) select X_ATTRIBUTE_ID,
213 X_DESCRIPTION,
214 X_CREATED_BY,
215 X_CREATION_DATE,
216 X_LAST_UPDATED_BY,
217 X_LAST_UPDATE_DATE,
218 X_LAST_UPDATE_LOGIN,
219 L.LANGUAGE_CODE,
220 userenv('LANG')
221 from FND_LANGUAGES L
222 where L.INSTALLED_FLAG in ('I', 'B')
223 and not exists (select null
224 from AME_ATTRIBUTES_TL T
225 where T.ATTRIBUTE_ID = X_ATTRIBUTE_ID
226 and T.LANGUAGE = L.LANGUAGE_CODE);
227 end if;
228 end INSERT_ROW;
229
230 procedure UPDATE_ROW
231 (X_ATTRIBUTE_ID in number
232 ,X_ATTRIBUTE_NAME in varchar2
233 ,X_ATTRIBUTE_TYPE in varchar2
234 ,X_DESCRIPTION in varchar2
235 ,X_LINE_ITEM in varchar2
236 ,X_ITEM_CLASS_ID in number
237 ,X_APPROVER_TYPE_ID in number
238 ,X_START_DATE in date
239 ,X_END_DATE in date
240 ,X_CREATED_BY in number
241 ,X_CREATION_DATE in date
242 ,X_LAST_UPDATED_BY in number
243 ,X_LAST_UPDATE_DATE in date
244 ,X_LAST_UPDATE_LOGIN in number
245 ,X_OBJECT_VERSION_NUMBER in number
246 ) as
247 X_LOCK_HANDLE varchar2(500);
248 X_RETURN_VALUE number;
249 l_current_start_date date;
250 begin
251
252 DBMS_LOCK.ALLOCATE_UNIQUE
253 (LOCKNAME =>'AME_ATTRIBUTES.'||X_ATTRIBUTE_ID
254 ,LOCKHANDLE => X_LOCK_HANDLE
255 );
256 X_RETURN_VALUE := DBMS_LOCK.REQUEST
257 (LOCKHANDLE => X_LOCK_HANDLE
258 ,TIMEOUT => 0
259 ,RELEASE_ON_COMMIT => true);
260
261 if X_RETURN_VALUE = 0 then
262 begin
263 select START_DATE
264 into l_current_start_date
265 from AME_ATTRIBUTES
266 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
267 and sysdate between START_DATE and nvl(END_DATE-(1/86400),sysdate);
268 if l_current_start_date >= X_CREATION_DATE then
269 return;
270 end if;
271 exception
272 when others then
273 null;
274 end;
275 begin
276 update AME_ATTRIBUTES AA
277 set AA.END_DATE = X_START_DATE
278 where AA.ATTRIBUTE_ID = X_ATTRIBUTE_ID
279 and sysdate between AA.START_DATE and nvl(AA.END_DATE - (1/86400),sysdate);
280 exception
281 when DUP_VAL_ON_INDEX then
282 update AME_ATTRIBUTES AA
283 set END_DATE = X_START_DATE - (1/86400)
284 where AA.ATTRIBUTE_ID = X_ATTRIBUTE_ID
285 and sysdate between AA.START_DATE and nvl(AA.END_DATE - (1/86400),sysdate);
286 end;
287
288 insert into AME_ATTRIBUTES
289 (ATTRIBUTE_ID
290 ,NAME
291 ,ATTRIBUTE_TYPE
292 ,DESCRIPTION
293 ,LINE_ITEM
294 ,ITEM_CLASS_ID
295 ,APPROVER_TYPE_ID
296 ,START_DATE
297 ,END_DATE
298 ,CREATED_BY
299 ,CREATION_DATE
300 ,LAST_UPDATED_BY
301 ,LAST_UPDATE_DATE
302 ,LAST_UPDATE_LOGIN
303 ,OBJECT_VERSION_NUMBER
304 ) values
305 (X_ATTRIBUTE_ID
306 ,X_ATTRIBUTE_NAME
307 ,X_ATTRIBUTE_TYPE
308 ,X_DESCRIPTION
312 ,X_START_DATE
309 ,X_LINE_ITEM
310 ,X_ITEM_CLASS_ID
311 ,X_APPROVER_TYPE_ID
313 ,X_END_DATE
314 ,X_CREATED_BY
315 ,X_CREATION_DATE
316 ,X_LAST_UPDATED_BY
317 ,X_LAST_UPDATE_DATE
318 ,X_LAST_UPDATE_LOGIN
319 ,X_OBJECT_VERSION_NUMBER
320 );
321
322 if not AME_SEED_UTILITY.MLS_ENABLED then
323 return;
324 end if;
325
326 update AME_ATTRIBUTES_TL
327 set DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
328 SOURCE_LANG = userenv('LANG'),
329 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
330 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
331 LAST_UPDATE_LOGIN = 0
332 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
333 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
334 end if;
335 end UPDATE_ROW;
336
337 procedure FORCE_UPDATE_ROW (
338 X_ROWID in VARCHAR2,
339 X_ATTRIBUTE_ID in number,
340 X_ATTRIBUTE_TYPE in varchar2,
341 X_DESCRIPTION in varchar2,
342 X_LINE_ITEM in varchar2,
343 X_ITEM_CLASS_ID in number,
344 X_APPROVER_TYPE_ID in number,
345 X_CREATED_BY in NUMBER,
346 X_CREATION_DATE in DATE,
347 X_LAST_UPDATED_BY in NUMBER,
348 X_LAST_UPDATE_DATE in DATE,
349 X_LAST_UPDATE_LOGIN in NUMBER,
350 X_START_DATE in DATE,
351 X_END_DATE in DATE,
352 X_OBJECT_VERSION_NUMBER in NUMBER
353 ) is
354 begin
355 update AME_ATTRIBUTES
356 set ATTRIBUTE_TYPE = X_ATTRIBUTE_TYPE,
357 DESCRIPTION = X_DESCRIPTION,
358 LINE_ITEM = X_LINE_ITEM,
359 ITEM_CLASS_ID = X_ITEM_CLASS_ID,
360 APPROVER_TYPE_ID = X_APPROVER_TYPE_ID,
361 CREATED_BY = X_CREATED_BY,
362 CREATION_DATE = X_CREATION_DATE,
363 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
364 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
365 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
366 START_DATE = X_START_DATE,
367 END_DATE = X_END_DATE,
368 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
369 where ROWID = X_ROWID;
370
371 if not AME_SEED_UTILITY.MLS_ENABLED then
372 return;
373 end if;
374
375 update AME_ATTRIBUTES_TL
376 set DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
377 SOURCE_LANG = userenv('LANG'),
378 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
379 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
380 LAST_UPDATE_LOGIN = 0
381 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
382 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
383 end FORCE_UPDATE_ROW;
384
385 procedure LOAD_ROW
386 (X_ATTRIBUTE_NAME in varchar2
387 ,X_ATTRIBUTE_TYPE in varchar2
388 ,X_DESCRIPTION in varchar2
389 ,X_LINE_ITEM in varchar2
390 ,X_ORIG_SYSTEM in varchar2
391 ,X_ITEM_CLASS_NAME in varchar2
392 ,X_OWNER in varchar2
393 ,X_LAST_UPDATE_DATE in varchar2
394 ,X_CUSTOM_MODE in varchar2
395 ) as
396 L_ATTRIBUTE_NAME AME_ATTRIBUTES.NAME%TYPE;
397 L_ATTRIBUTE_TYPE AME_ATTRIBUTES.ATTRIBUTE_TYPE%TYPE;
398 L_DESCRIPTION AME_ATTRIBUTES_TL.DESCRIPTION%TYPE;
399 L_LINE_ITEM AME_ATTRIBUTES.LINE_ITEM%TYPE;
400 L_ORIG_SYSTEM AME_APPROVER_TYPES.ORIG_SYSTEM%TYPE;
401 L_ITEM_CLASS_NAME AME_ITEM_CLASSES.NAME%TYPE;
402 L_ITEM_CLASS_ID number;
403 L_APPROVER_TYPE_ID number;
404 L_ATTRIBUTE_ID number;
405 L_OWNER varchar2(100);
406 L_LAST_UPDATE_DATE varchar2(19);
407 L_END_DATE date;
408 L_DUMMY varchar2(1);
409 L_OBJECT_VERSION_NUMBER number;
410 L_ROWID ROWID;
411 begin
412 L_ATTRIBUTE_NAME := X_ATTRIBUTE_NAME;
413 L_ATTRIBUTE_TYPE := X_ATTRIBUTE_TYPE;
414 L_DESCRIPTION := X_DESCRIPTION;
415 L_LINE_ITEM := X_LINE_ITEM;
416 L_ORIG_SYSTEM := X_ORIG_SYSTEM;
417 L_ITEM_CLASS_NAME := X_ITEM_CLASS_NAME;
418 L_OWNER := X_OWNER;
419 L_LAST_UPDATE_DATE := X_LAST_UPDATE_DATE;
420 L_END_DATE := AME_SEED_UTILITY.GET_DEFAULT_END_DATE;
421
422 VALIDATE_ROW
423 (X_ATTRIBUTE_TYPE => L_ATTRIBUTE_TYPE);
424
425 KEY_TO_IDS
426 (X_ATTRIBUTE_NAME => L_ATTRIBUTE_NAME
427 ,X_ORIG_SYSTEM => L_ORIG_SYSTEM
428 ,X_ITEM_CLASS_NAME => L_ITEM_CLASS_NAME
429 ,X_LINE_ITEM => L_LINE_ITEM
430 ,X_APPROVER_TYPE_ID => L_APPROVER_TYPE_ID
431 ,X_ITEM_CLASS_ID => L_ITEM_CLASS_ID
432 );
433
434 begin
435 select AA.ATTRIBUTE_ID,
436 nvl(AA.OBJECT_VERSION_NUMBER,1),
437 ROWID
438 into L_ATTRIBUTE_ID,
439 L_OBJECT_VERSION_NUMBER,
443 and sysdate between AA.START_DATE and nvl(AA.END_DATE - (1/86400),sysdate);
440 L_ROWID
441 from AME_ATTRIBUTES AA
442 where AA.NAME = X_ATTRIBUTE_NAME
444
445 if X_CUSTOM_MODE = 'FORCE' then
446 FORCE_UPDATE_ROW
447 (X_ROWID => L_ROWID
448 ,X_ATTRIBUTE_ID => L_ATTRIBUTE_ID
449 ,X_ATTRIBUTE_TYPE => L_ATTRIBUTE_TYPE
450 ,X_DESCRIPTION => L_DESCRIPTION
451 ,X_LINE_ITEM => L_LINE_ITEM
452 ,X_ITEM_CLASS_ID => L_ITEM_CLASS_ID
453 ,X_APPROVER_TYPE_ID => L_APPROVER_TYPE_ID
454 ,X_CREATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
455 ,X_CREATION_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
456 ,X_LAST_UPDATE_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
457 ,X_LAST_UPDATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
458 ,X_LAST_UPDATE_LOGIN => 0
459 ,X_START_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
460 ,X_END_DATE => L_END_DATE
461 ,X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER + 1
462 );
463 else
464 UPDATE_ROW
465 (X_ATTRIBUTE_ID => L_ATTRIBUTE_ID
466 ,X_ATTRIBUTE_NAME => L_ATTRIBUTE_NAME
467 ,X_ATTRIBUTE_TYPE => L_ATTRIBUTE_TYPE
468 ,X_DESCRIPTION => L_DESCRIPTION
469 ,X_LINE_ITEM => L_LINE_ITEM
470 ,X_APPROVER_TYPE_ID => L_APPROVER_TYPE_ID
471 ,X_ITEM_CLASS_ID => L_ITEM_CLASS_ID
472 ,X_START_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
473 ,X_END_DATE => L_END_DATE
474 ,X_CREATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
475 ,X_CREATION_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
476 ,X_LAST_UPDATE_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
477 ,X_LAST_UPDATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
478 ,X_LAST_UPDATE_LOGIN => 0
479 ,X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER + 1
480 );
481 end if;
482 exception
483 when no_data_found then
484 INSERT_ROW
485 (X_ATTRIBUTE_NAME => L_ATTRIBUTE_NAME
486 ,X_ATTRIBUTE_TYPE => L_ATTRIBUTE_TYPE
487 ,X_DESCRIPTION => L_DESCRIPTION
488 ,X_LINE_ITEM => L_LINE_ITEM
489 ,X_APPROVER_TYPE_ID => L_APPROVER_TYPE_ID
490 ,X_ITEM_CLASS_ID => L_ITEM_CLASS_ID
491 ,X_START_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
492 ,X_END_DATE => L_END_DATE
493 ,X_CREATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
494 ,X_CREATION_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
495 ,X_LAST_UPDATED_BY => AME_SEED_UTILITY.OWNER_AS_INTEGER(L_OWNER)
496 ,X_LAST_UPDATE_DATE => AME_SEED_UTILITY.DATE_AS_DATE(L_LAST_UPDATE_DATE)
497 ,X_LAST_UPDATE_LOGIN => 0
498 ,X_OBJECT_VERSION_NUMBER => 1
499 );
500 end;
501 end LOAD_ROW;
502
503 procedure TRANSLATE_ROW
504 (X_ATTRIBUTE_NAME in varchar2
505 ,X_DESCRIPTION in varchar2
506 ,X_OWNER in varchar2
507 ,X_LAST_UPDATE_DATE in varchar2
508 ) as
509 X_ATTRIBUTE_ID number;
510 begin
511 if not AME_SEED_UTILITY.MLS_ENABLED then
512 return;
513 end if;
514
515 begin
516 select AA.ATTRIBUTE_ID
517 into X_ATTRIBUTE_ID
518 from AME_ATTRIBUTES_TL AATL,
519 AME_ATTRIBUTES AA
520 where AA.NAME = X_ATTRIBUTE_NAME
521 and AA.ATTRIBUTE_ID = AATL.ATTRIBUTE_ID
522 and AATL.LANGUAGE = userenv('LANG')
523 and sysdate between AA.START_DATE and nvl(AA.END_DATE - (1/86400),sysdate);
524
525 update AME_ATTRIBUTES_TL AATL
526 set DESCRIPTION = nvl(X_DESCRIPTION,AATL.DESCRIPTION),
527 SOURCE_LANG = userenv('LANG'),
528 LAST_UPDATE_DATE = AME_SEED_UTILITY.DATE_AS_DATE(X_LAST_UPDATE_DATE),
529 LAST_UPDATED_BY = AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
530 LAST_UPDATE_LOGIN = 0
531 where AATL.ATTRIBUTE_ID = X_ATTRIBUTE_ID
532 and userenv('LANG') in (AATL.LANGUAGE,AATL.SOURCE_LANG);
533 exception
534 when no_data_found then
535 null;
536 end;
537 end TRANSLATE_ROW;
538
539 function MERGE_ROW_TEST
540 (X_ATTRIBUTE_NAME in varchar2
541 ,X_OWNER in varchar2
542 ,X_LAST_UPDATE_DATE in varchar2
543 ,X_UPLOAD_MODE in varchar2
544 ,X_CUSTOM_MODE in varchar2
545 ) return boolean as
546 X_CURRENT_OWNER NUMBER;
547 X_CURRENT_LAST_UPDATE_DATE varchar2(19);
548 X_CREATED_BY varchar2(100);
549 begin
550 if X_UPLOAD_MODE = 'NLS' then
551 begin
552 select AATL.LAST_UPDATED_BY,
553 AME_SEED_UTILITY.DATE_AS_STRING(AATL.LAST_UPDATE_DATE),
554 AME_SEED_UTILITY.OWNER_AS_STRING(AATL.CREATED_BY)
555 into X_CURRENT_OWNER,
556 X_CURRENT_LAST_UPDATE_DATE,
557 X_CREATED_BY
558 from AME_ATTRIBUTES_TL AATL,
559 AME_ATTRIBUTES AA
560 where AATL.ATTRIBUTE_ID = AA.ATTRIBUTE_ID
561 and AA.NAME = X_ATTRIBUTE_NAME
562 and sysdate between AA.START_DATE and nvl(AA.END_DATE - (1/86400),sysdate)
563 and AATL.LANGUAGE = userenv('LANG');
564 if AME_SEED_UTILITY.IS_SEED_USER(X_CREATED_BY) then
565 return true;
566 else
567 return AME_SEED_UTILITY.TL_MERGE_ROW_TEST
568 (X_CURRENT_OWNER => X_CURRENT_OWNER
569 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
570 ,X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER)
571 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
572 ,X_CUSTOM_MODE => X_CUSTOM_MODE
573 );
574 end if;
575 exception
576 when no_data_found then
577 return true;
578 end;
579 else
580 begin
581 select AA.LAST_UPDATED_BY,
582 AME_SEED_UTILITY.DATE_AS_STRING(AA.LAST_UPDATE_DATE)
583 into X_CURRENT_OWNER,
584 X_CURRENT_LAST_UPDATE_DATE
585 from AME_ATTRIBUTES AA
586 where AA.NAME = X_ATTRIBUTE_NAME
587 and sysdate between AA.START_DATE and nvl(AA.END_DATE - (1/86400),sysdate);
588 return AME_SEED_UTILITY.MERGE_ROW_TEST
589 (X_CURRENT_OWNER => X_CURRENT_OWNER
590 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
591 ,X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER)
595 exception
592 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
593 ,X_CUSTOM_MODE => X_CUSTOM_MODE
594 );
596 when no_data_found then
597 return true;
598 end;
599 end if;
600 end MERGE_ROW_TEST;
601
602 procedure LOAD_SEED_ROW
603 (X_ATTRIBUTE_NAME in varchar2
604 ,X_ATTRIBUTE_TYPE in varchar2
605 ,X_DESCRIPTION in varchar2
606 ,X_LINE_ITEM in varchar2
607 ,X_ORIG_SYSTEM in varchar2
608 ,X_ITEM_CLASS_NAME in varchar2
609 ,X_OWNER in varchar2
610 ,X_LAST_UPDATE_DATE in varchar2
611 ,X_UPLOAD_MODE in varchar2
612 ,X_CUSTOM_MODE in varchar2
613 ) as
614 L_ATTRIBUTE_NAME AME_ATTRIBUTES.NAME%TYPE;
615 begin
616 AME_SEED_UTILITY.INIT_AME_INSTALLATION_LEVEL;
617
618 L_ATTRIBUTE_NAME := X_ATTRIBUTE_NAME;
619
620 if AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is null then
621 if X_ITEM_CLASS_NAME is not null and
622 X_ITEM_CLASS_NAME not in ('header'
623 ,'line item') then
624 return;
625 end if;
626 if X_ATTRIBUTE_NAME in ('USE_WORKFLOW'
627 ,'REJECTION_RESPONSE'
628 ,'REPEAT_SUBSTITUTIONS'
629 ,'NON_DEFAULT_STARTING_POINT_POSITION_ID'
630 ,'NON_DEFAULT_POSITION_STRUCTURE_ID'
631 ,'TRANSACTION_REQUESTOR_POSITION_ID'
632 ,'TOP_POSITION_ID') then
633 return;
634 end if;
635 end if;
636
637 FORMAT_KEY
638 (X_ATTRIBUTE_NAME => L_ATTRIBUTE_NAME);
639
640 if MERGE_ROW_TEST
641 (X_ATTRIBUTE_NAME => L_ATTRIBUTE_NAME
642 ,X_OWNER => X_OWNER
643 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
644 ,X_UPLOAD_MODE => X_UPLOAD_MODE
645 ,X_CUSTOM_MODE => X_CUSTOM_MODE
646 ) then
647 if X_UPLOAD_MODE = 'NLS' then
648 TRANSLATE_ROW
649 (X_ATTRIBUTE_NAME => L_ATTRIBUTE_NAME
650 ,X_DESCRIPTION => X_DESCRIPTION
651 ,X_OWNER => X_OWNER
652 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
653 );
654 else
655 LOAD_ROW
656 (X_ATTRIBUTE_NAME => L_ATTRIBUTE_NAME
657 ,X_ATTRIBUTE_TYPE => X_ATTRIBUTE_TYPE
658 ,X_DESCRIPTION => X_DESCRIPTION
659 ,X_LINE_ITEM => X_LINE_ITEM
660 ,X_ORIG_SYSTEM => X_ORIG_SYSTEM
661 ,X_ITEM_CLASS_NAME => X_ITEM_CLASS_NAME
662 ,X_OWNER => X_OWNER
663 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
664 ,X_CUSTOM_MODE => X_CUSTOM_MODE
665 );
666 end if;
667 end if;
668 end LOAD_SEED_ROW;
669
670 procedure DELETE_ROW
671 (X_ATTRIBUTE_NAME in varchar2
672 ) as
673 begin
674 if AME_SEED_UTILITY.MLS_ENABLED then
675 delete from AME_ATTRIBUTES_TL
676 where ATTRIBUTE_ID in (select ATTRIBUTE_ID
677 from AME_ATTRIBUTES
678 where NAME = X_ATTRIBUTE_NAME);
679 end if;
680 delete from AME_ATTRIBUTES
681 where NAME = X_ATTRIBUTE_NAME;
682 if sql%notfound then
683 raise no_data_found;
684 end if;
685 end DELETE_ROW;
686
687 end AME_ATTRIBUTES_API;