[Home] [Help]
PACKAGE BODY: APPS.AME_APPROVAL_GROUPS_LOAD_API
Source
1 PACKAGE BODY AME_APPROVAL_GROUPS_LOAD_API AS
2 /* $Header: ameagapi.pkb 120.4 2005/10/14 04:10:38 ubhat noship $ */
3
4 procedure OWNER_TO_WHO (X_OWNER in VARCHAR2
5 ,X_CREATED_BY out nocopy NUMBER
6 ,X_LAST_UPDATED_BY out nocopy NUMBER
7 ,X_LAST_UPDATE_LOGIN out nocopy NUMBER
8 ) is
9 begin
10 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
11 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATE_LOGIN := 0;
13 end OWNER_TO_WHO;
14
15 procedure INSERT_ROW (X_NAME in VARCHAR2
16 ,X_QUERY_STRING in VARCHAR2
17 ,X_IS_STATIC in VARCHAR2
18 ,X_DESCRIPTION in VARCHAR2
19 ,X_CREATED_BY in NUMBER
20 ,X_CREATION_DATE in DATE
21 ,X_LAST_UPDATED_BY in NUMBER
22 ,X_LAST_UPDATE_DATE in DATE
23 ,X_LAST_UPDATE_LOGIN in NUMBER
24 ,X_START_DATE in DATE
25 ,X_OBJECT_VERSION_NUMBER in NUMBER
26 ,X_APPROVAL_GROUP_ID in out nocopy NUMBER
27 )
28 is
29 begin
30 if (X_APPROVAL_GROUP_ID is null) then
31 select ame_approval_groups_s.nextval
32 into X_APPROVAL_GROUP_ID
33 from sys.dual;
34 end if;
35 insert into AME_APPROVAL_GROUPS
36 (APPROVAL_GROUP_ID
37 ,NAME
38 ,QUERY_STRING
39 ,IS_STATIC
40 ,CREATED_BY
41 ,CREATION_DATE
42 ,LAST_UPDATED_BY
43 ,LAST_UPDATE_DATE
44 ,LAST_UPDATE_LOGIN
45 ,START_DATE
46 ,END_DATE
47 ,DESCRIPTION
48 ,OBJECT_VERSION_NUMBER
49 ) select
50 X_APPROVAL_GROUP_ID
51 ,X_NAME
52 ,X_QUERY_STRING
53 ,X_IS_STATIC
54 ,X_CREATED_BY
55 ,X_CREATION_DATE
56 ,X_LAST_UPDATED_BY
57 ,X_LAST_UPDATE_DATE
58 ,X_LAST_UPDATE_LOGIN
59 ,X_START_DATE
60 ,AME_SEED_UTILITY.GET_DEFAULT_END_DATE
61 ,X_DESCRIPTION
62 ,X_OBJECT_VERSION_NUMBER
63 from sys.dual
64 where not exists (select NULL
65 from AME_APPROVAL_GROUPS
66 where NAME = X_NAME
67 and sysdate between START_DATE
68 and nvl(END_DATE - (1/86400), sysdate));
69 end INSERT_ROW;
70
71 procedure INSERT_TL_ROW (
72 X_APPROVAL_GROUP_ID in NUMBER,
73 X_USER_APPROVAL_GROUP_NAME in VARCHAR2,
74 X_DESCRIPTION in VARCHAR2,
75 X_CREATED_BY in NUMBER,
76 X_CREATION_DATE in DATE,
77 X_LAST_UPDATED_BY in NUMBER,
78 X_LAST_UPDATE_DATE in DATE,
79 X_LAST_UPDATE_LOGIN in NUMBER) is
80 begin
81 if not AME_SEED_UTILITY.MLS_ENABLED then
82 return;
83 end if;
84
85 insert into AME_APPROVAL_GROUPS_TL
86 (APPROVAL_GROUP_ID
87 ,USER_APPROVAL_GROUP_NAME
88 ,DESCRIPTION
89 ,CREATED_BY
90 ,CREATION_DATE
91 ,LAST_UPDATED_BY
92 ,LAST_UPDATE_DATE
93 ,LAST_UPDATE_LOGIN
94 ,LANGUAGE
95 ,SOURCE_LANG
96 ) select X_APPROVAL_GROUP_ID,
97 X_USER_APPROVAL_GROUP_NAME,
98 X_DESCRIPTION,
99 X_CREATED_BY,
100 X_CREATION_DATE,
101 X_LAST_UPDATED_BY,
102 X_LAST_UPDATE_DATE,
103 X_LAST_UPDATE_LOGIN,
104 L.LANGUAGE_CODE,
105 userenv('LANG')
106 from FND_LANGUAGES L
107 where L.INSTALLED_FLAG in ('I', 'B')
108 and not exists (select null
109 from AME_APPROVAL_GROUPS_TL T
110 where T.APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID
111 and T.LANGUAGE = L.LANGUAGE_CODE);
112 END INSERT_TL_ROW;
113
114 procedure KEY_TO_IDS (X_APPROVAL_GROUP_NAME in VARCHAR2
115 ,X_APPROVAL_GROUP_ROWID out nocopy VARCHAR2
116 ,X_APPROVAL_GROUP_ID out nocopy NUMBER
117 ,X_CURRENT_OWNER out nocopy NUMBER
118 ,X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2
119 ,X_CURRENT_OVN out nocopy NUMBER
120 ,X_MAX_START_DATE out nocopy DATE
121 ) is
122 cursor CSR_GET_MAX_START
123 (
124 X_APPROVAL_GROUP_NAME in VARCHAR2
125 ) is
126 select max(START_DATE)
127 from AME_APPROVAL_GROUPS
128 where NAME = X_APPROVAL_GROUP_NAME;
129
130 cursor CSR_GET_CURRENT_APPROVAL_GROUP
131 (
132 X_APPROVAL_GROUP_NAME in VARCHAR2
133 ) is
134 select ROWID, APPROVAL_GROUP_ID,
135 LAST_UPDATED_BY,
136 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
137 nvl(OBJECT_VERSION_NUMBER,1)
138 from AME_APPROVAL_GROUPS
139 where NAME = X_APPROVAL_GROUP_NAME
140 and sysdate between START_DATE
141 and nvl(END_DATE - (1/86400), sysdate)
142 for update of END_DATE;
143
144 begin
145 X_CURRENT_OVN := 1;
146 open CSR_GET_MAX_START(X_APPROVAL_GROUP_NAME);
147 fetch CSR_GET_MAX_START into X_MAX_START_DATE;
148 if (CSR_GET_MAX_START%notfound) then
149 X_MAX_START_DATE := null;
150 end if;
151 close CSR_GET_MAX_START;
152 open CSR_GET_CURRENT_APPROVAL_GROUP(X_APPROVAL_GROUP_NAME);
153 fetch CSR_GET_CURRENT_APPROVAL_GROUP into X_APPROVAL_GROUP_ROWID, X_APPROVAL_GROUP_ID,
154 X_CURRENT_OWNER, X_CURRENT_LAST_UPDATE_DATE, X_CURRENT_OVN;
155 if (CSR_GET_CURRENT_APPROVAL_GROUP%notfound) then
156 X_APPROVAL_GROUP_ID := null;
157 end if;
158 close CSR_GET_CURRENT_APPROVAL_GROUP;
159 end KEY_TO_IDS;
160
161 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
162 X_CURRENT_OWNER in NUMBER,
163 X_LAST_UPDATE_DATE in VARCHAR2,
164 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
165 X_CUSTOM_MODE in VARCHAR2 default null)
166 return boolean as
167 begin
168 return AME_SEED_UTILITY.MERGE_ROW_TEST
169 (X_OWNER => X_OWNER
170 ,X_CURRENT_OWNER => X_CURRENT_OWNER
171 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
172 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
173 ,X_CUSTOM_MODE => X_CUSTOM_MODE
174 );
175 end DO_UPDATE_INSERT;
176
177 function DO_TL_UPDATE_INSERT(X_OWNER in NUMBER,
178 X_CURRENT_OWNER in NUMBER,
179 X_LAST_UPDATE_DATE in VARCHAR2,
180 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
181 X_CREATED_BY in VARCHAR2,
182 X_CUSTOM_MODE in VARCHAR2 default null)
183 return boolean as
184 begin
185 if X_CUSTOM_MODE = 'FORCE' then
186 return true;
187 end if;
188 if AME_SEED_UTILITY.IS_SEED_USER(X_CREATED_BY) then
189 return true;
190 else
191 return AME_SEED_UTILITY.TL_MERGE_ROW_TEST
192 (X_OWNER => X_OWNER
193 ,X_CURRENT_OWNER => X_CURRENT_OWNER
194 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
195 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
196 ,X_CUSTOM_MODE => X_CUSTOM_MODE
197 );
198 end if;
199 return(false);
200 end DO_TL_UPDATE_INSERT;
201
202 procedure UPDATE_ROW (X_APPROVAL_GROUP_ROWID in VARCHAR2
203 ,X_END_DATE in DATE
204 ,X_APPROVAL_GROUP_ID in NUMBER
205 ,X_NAME in VARCHAR2
206 ,X_QUERY_STRING in VARCHAR2
207 ,X_IS_STATIC in VARCHAR2
208 ,X_CREATED_BY in NUMBER
209 ,X_CREATION_DATE in DATE
210 ,X_LAST_UPDATED_BY in NUMBER
211 ,X_LAST_UPDATE_DATE in DATE
212 ,X_LAST_UPDATE_LOGIN in NUMBER
213 ,X_START_DATE in DATE
214 ,X_DESCRIPTION in VARCHAR2
215 ,X_OBJECT_VERSION_NUMBER in NUMBER
216 )
217 is
218 L_APPROVAL_GROUP_ID number;
219 begin
220 L_APPROVAL_GROUP_ID := X_APPROVAL_GROUP_ID;
221 update AME_APPROVAL_GROUPS
222 set END_DATE = X_END_DATE
223 where ROWID = X_APPROVAL_GROUP_ROWID;
224 INSERT_ROW (X_NAME => X_NAME
225 ,X_QUERY_STRING => X_QUERY_STRING
226 ,X_IS_STATIC => X_IS_STATIC
227 ,X_DESCRIPTION => X_DESCRIPTION
228 ,X_CREATED_BY => X_CREATED_BY
229 ,X_CREATION_DATE => X_CREATION_DATE
230 ,X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
231 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
232 ,X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
233 ,X_START_DATE => X_START_DATE
234 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
235 ,X_APPROVAL_GROUP_ID => L_APPROVAL_GROUP_ID
236 );
237 end UPDATE_ROW;
238
239 procedure FORCE_UPDATE_ROW (
240 X_ROWID in VARCHAR2,
241 X_NAME in VARCHAR2,
242 X_QUERY_STRING in VARCHAR2,
243 X_IS_STATIC in VARCHAR2,
244 X_DESCRIPTION in VARCHAR2,
245 X_CREATED_BY in NUMBER,
246 X_CREATION_DATE in DATE,
247 X_LAST_UPDATED_BY in NUMBER,
248 X_LAST_UPDATE_DATE in DATE,
249 X_LAST_UPDATE_LOGIN in NUMBER,
250 X_START_DATE in DATE,
251 X_END_DATE in DATE,
252 X_OBJECT_VERSION_NUMBER in NUMBER
253 ) is
254 begin
255 update AME_APPROVAL_GROUPS
256 set NAME = X_NAME,
257 QUERY_STRING = X_QUERY_STRING,
258 IS_STATIC = X_IS_STATIC,
259 DESCRIPTION = X_DESCRIPTION,
260 CREATED_BY = X_CREATED_BY,
261 CREATION_DATE = X_CREATION_DATE,
262 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
263 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
264 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
265 START_DATE = X_START_DATE,
266 END_DATE = X_END_DATE,
267 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
268 where ROWID = X_ROWID;
269 end FORCE_UPDATE_ROW;
270
271 procedure POPULATE_APP_GRP_MEMBERS(X_APPROVAL_GROUP_ID in NUMBER
272 ,X_QUERY_STRING in VARCHAR2
273 )
274 is
275 begin
276 delete from AME_APPROVAL_GROUP_MEMBERS
277 where APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID;
278 insert into AME_APPROVAL_GROUP_MEMBERS
279 (APPROVAL_GROUP_ID
280 ,PARAMETER_NAME
281 ,PARAMETER
282 ,QUERY_STRING
283 ,ORDER_NUMBER
284 ) select
285 X_APPROVAL_GROUP_ID
286 ,ame_util.approverOamGroupId
287 ,X_APPROVAL_GROUP_ID
288 ,X_QUERY_STRING
289 ,1
290 from sys.dual;
291 update AME_APPROVAL_GROUP_MEMBERS
292 set QUERY_STRING = X_QUERY_STRING
293 where PARAMETER_NAME = ame_util.approverOamGroupId
294 and PARAMETER = to_char(X_APPROVAL_GROUP_ID)
295 and APPROVAL_GROUP_ID <> X_APPROVAL_GROUP_ID;
296 end POPULATE_APP_GRP_MEMBERS;
297
298 procedure CREATE_APPROVAL_CONFIG(X_APPROVAL_GROUP_ID in NUMBER
299 ,X_CREATED_BY in NUMBER
300 ,X_CREATION_DATE in DATE
301 ,X_LAST_UPDATED_BY in NUMBER
302 ,X_LAST_UPDATE_DATE in DATE
303 ,X_LAST_UPDATE_LOGIN in NUMBER
304 ,X_START_DATE in DATE
305 )
306 is
307 cursor get_active_application is
308 select APPLICATION_ID
309 from AME_CALLING_APPS
310 where sysdate between start_date
311 and nvl(end_date - ame_util.oneSecond,sysdate);
312 TYPE APPLICATION_ID_TAB is table of NUMBER index by BINARY_INTEGER;
313 APPLICATION_ID_LIST APPLICATION_ID_TAB;
314 L_ORDER_NUMBER NUMBER;
315 begin
316 AME_SEED_UTILITY.INIT_AME_INSTALLATION_LEVEL;
317 if (AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is not null) and to_number(AME_SEED_UTILITY.AME_INSTALLATION_LEVEL) < 2 then
318 open get_active_application;
319 fetch get_active_application bulk collect into application_id_list;
320 close get_active_application;
321 --
322 -- Now for each active application, create a config record.
323 --
324 for indx in 1..APPLICATION_ID_LIST.count
325 loop
326 --
327 -- Obtain the maximum order number allotted for that application_id.
328 --
329 select nvl(max(order_number),0)+1
330 into L_ORDER_NUMBER
331 from AME_APPROVAL_GROUP_CONFIG
332 where application_id = APPLICATION_ID_LIST(indx)
333 and sysdate between start_date
334 and nvl(end_date - ame_util.oneSecond,sysdate);
335 --
336 -- Now insert a row for each application_id.
337 --
338 insert into AME_APPROVAL_GROUP_CONFIG(
339 APPLICATION_ID
340 ,APPROVAL_GROUP_ID
341 ,VOTING_REGIME
342 ,ORDER_NUMBER
343 ,CREATED_BY
344 ,CREATION_DATE
345 ,LAST_UPDATED_BY
346 ,LAST_UPDATE_DATE
347 ,LAST_UPDATE_LOGIN
348 ,START_DATE
349 ,END_DATE
350 ,OBJECT_VERSION_NUMBER
351 ) select
352 APPLICATION_ID_LIST(indx)
353 ,X_APPROVAL_GROUP_ID
354 ,ame_util.serializedVoting
355 ,L_ORDER_NUMBER
356 ,X_CREATED_BY
357 ,X_CREATION_DATE
358 ,X_LAST_UPDATED_BY
359 ,X_LAST_UPDATE_DATE
360 ,X_LAST_UPDATE_LOGIN
361 ,X_START_DATE
362 ,null
363 ,1
364 from sys.dual;
365 end loop;
366 end if;
367 end CREATE_APPROVAL_CONFIG;
368
369 procedure UPDATE_TL_ROW (
370 X_APPROVAL_GROUP_ID in NUMBER,
371 X_USER_APPROVAL_GROUP_NAME in VARCHAR2,
372 X_DESCRIPTION in VARCHAR2,
373 X_CREATED_BY in NUMBER,
374 X_CREATION_DATE in DATE,
375 X_LAST_UPDATED_BY in NUMBER,
376 X_LAST_UPDATE_DATE in DATE,
377 X_LAST_UPDATE_LOGIN in NUMBER,
378 X_CUSTOM_MODE in VARCHAR2) is
379 X_CURRENT_OWNER NUMBER;
380 X_CURRENT_LAST_UPDATE_DATE DATE;
381 begin
382 if not AME_SEED_UTILITY.MLS_ENABLED then
383 return;
384 end if;
385
386 select LAST_UPDATED_BY,
387 LAST_UPDATE_DATE
388 into X_CURRENT_OWNER,
389 X_CURRENT_LAST_UPDATE_DATE
393
390 FROM AME_APPROVAL_GROUPS_TL
391 WHERE APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID
392 AND LANGUAGE = USERENV('LANG');
394 if DO_UPDATE_INSERT
395 (X_LAST_UPDATED_BY
396 ,X_CURRENT_OWNER
397 ,AME_SEED_UTILITY.DATE_AS_STRING(X_LAST_UPDATE_DATE)
398 ,AME_SEED_UTILITY.DATE_AS_STRING(X_CURRENT_LAST_UPDATE_DATE)
399 ,X_CUSTOM_MODE) then
400 update AME_APPROVAL_GROUPS_TL
401 set USER_APPROVAL_GROUP_NAME = nvl(X_USER_APPROVAL_GROUP_NAME,USER_APPROVAL_GROUP_NAME),
402 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
403 SOURCE_LANG = userenv('LANG'),
404 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
405 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
406 LAST_UPDATE_LOGIN = 0
407 where APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID
408 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
409 end if;
410 exception
411 when no_data_found then
412 null;
413 end UPDATE_TL_ROW;
414
415 procedure LOAD_ROW(X_APPROVAL_GROUP_NAME in VARCHAR2
416 ,X_USER_APPROVAL_GROUP_NAME in VARCHAR2
417 ,X_DESCRIPTION in VARCHAR2
418 ,X_QUERY_STRING in VARCHAR2
419 ,X_IS_STATIC in VARCHAR2
420 ,X_OWNER in VARCHAR2
421 ,X_LAST_UPDATE_DATE in VARCHAR2
422 ,X_CUSTOM_MODE in VARCHAR2
423 )
424 is
425 X_APPROVAL_GROUP_ROWID ROWID;
426 X_APPROVAL_GROUP_ID NUMBER;
427 X_CURRENT_OWNER NUMBER;
428 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
429 X_CURRENT_OVN NUMBER;
430 X_MAX_START_DATE DATE;
431 X_CREATED_BY NUMBER;
432 X_LAST_UPDATED_BY NUMBER;
433 X_LAST_UPDATE_LOGIN NUMBER;
434 lockHandle varchar2(500);
435 returnValue integer;
436 begin
437 DBMS_LOCK.ALLOCATE_UNIQUE
438 (lockname =>'AME_APPROVAL_GROUPS.'||X_APPROVAL_GROUP_NAME
439 ,lockhandle => lockHandle
440 );
441 returnValue := DBMS_LOCK.REQUEST
442 (lockhandle => lockHandle
443 ,timeout => 0
444 ,release_on_commit => true
445 );
446 if returnValue = 0 then
447 KEY_TO_IDS (
448 X_APPROVAL_GROUP_NAME
449 ,X_APPROVAL_GROUP_ROWID
450 ,X_APPROVAL_GROUP_ID
451 ,X_CURRENT_OWNER
452 ,X_CURRENT_LAST_UPDATE_DATE
453 ,X_CURRENT_OVN
454 ,X_MAX_START_DATE
455 );
456 OWNER_TO_WHO (
457 X_OWNER
458 ,X_CREATED_BY
459 ,X_LAST_UPDATED_BY
460 ,X_LAST_UPDATE_LOGIN
461 );
462 begin
463 -- the current row was not found insert a new row
464 if (X_APPROVAL_GROUP_ID is null) then
465 INSERT_ROW (
466 X_APPROVAL_GROUP_NAME
467 ,X_QUERY_STRING
468 ,X_IS_STATIC
469 ,X_DESCRIPTION
470 ,X_CREATED_BY
471 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
472 ,X_LAST_UPDATED_BY
473 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
474 ,X_LAST_UPDATE_LOGIN
475 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
476 ,1
477 ,X_APPROVAL_GROUP_ID
478 );
479 INSERT_TL_ROW
480 (X_APPROVAL_GROUP_ID
481 ,nvl(X_USER_APPROVAL_GROUP_NAME,X_APPROVAL_GROUP_NAME)
482 ,X_DESCRIPTION
483 ,X_CREATED_BY
484 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
485 ,X_LAST_UPDATED_BY
486 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
487 ,X_LAST_UPDATE_LOGIN
488 );
489 --
490 -- Create config records.
491 --
492 CREATE_APPROVAL_CONFIG
493 (X_APPROVAL_GROUP_ID => X_APPROVAL_GROUP_ID
494 ,X_CREATED_BY => X_CREATED_BY
495 ,X_CREATION_DATE => to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
496 ,X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
497 ,X_LAST_UPDATE_DATE => to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
498 ,X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
499 ,X_START_DATE => to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
500 );
501 -- the current row was found end date the current row
502 -- insert a row with the same attribute id
503 else
504 if X_CUSTOM_MODE = 'FORCE' then
505 FORCE_UPDATE_ROW (
506 X_APPROVAL_GROUP_ROWID
507 ,X_APPROVAL_GROUP_NAME
508 ,X_QUERY_STRING
509 ,X_IS_STATIC
510 ,X_DESCRIPTION
511 ,X_CREATED_BY
512 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
513 ,X_LAST_UPDATED_BY
514 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
515 ,X_LAST_UPDATE_LOGIN
516 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
517 ,AME_SEED_UTILITY.GET_DEFAULT_END_DATE
518 ,X_CURRENT_OVN + 1
519 );
520 UPDATE_TL_ROW
521 (X_APPROVAL_GROUP_ID
522 ,nvl(X_USER_APPROVAL_GROUP_NAME,X_APPROVAL_GROUP_NAME)
523 ,X_DESCRIPTION
524 ,X_CREATED_BY
525 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
526 ,X_LAST_UPDATED_BY
527 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
528 ,X_LAST_UPDATE_LOGIN
529 ,X_CUSTOM_MODE
530 );
534 ,X_CURRENT_OWNER
531 else
532 if DO_UPDATE_INSERT
533 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER)
535 ,X_LAST_UPDATE_DATE
536 ,X_CURRENT_LAST_UPDATE_DATE
537 )
538 and (X_MAX_START_DATE is not null)
539 and (to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS') > X_MAX_START_DATE)
540 then
541 UPDATE_ROW (
542 X_APPROVAL_GROUP_ROWID
543 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400)
544 ,X_APPROVAL_GROUP_ID
545 ,X_APPROVAL_GROUP_NAME
546 ,X_QUERY_STRING
547 ,X_IS_STATIC
548 ,X_CREATED_BY
549 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
550 ,X_LAST_UPDATED_BY
551 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
552 ,X_LAST_UPDATE_LOGIN
553 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
554 ,X_DESCRIPTION
555 ,X_CURRENT_OVN + 1
556 );
557 UPDATE_TL_ROW
558 (X_APPROVAL_GROUP_ID
559 ,nvl(X_USER_APPROVAL_GROUP_NAME,X_APPROVAL_GROUP_NAME)
560 ,X_DESCRIPTION
561 ,X_CREATED_BY
562 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
563 ,X_LAST_UPDATED_BY
564 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
565 ,X_LAST_UPDATE_LOGIN
566 ,X_CUSTOM_MODE
567 );
568 end if;
569 end if;
570 end if;
571 end;
572 POPULATE_APP_GRP_MEMBERS(X_APPROVAL_GROUP_ID => X_APPROVAL_GROUP_ID
573 ,X_QUERY_STRING => X_QUERY_STRING
574 );
575 end if;
576 exception
577 when others then
578 ame_util.runtimeException('ame_approval_groups_api'
579 ,'load_row'
580 ,sqlcode
581 ,sqlerrm);
582 raise;
583 end LOAD_ROW;
584
585 procedure DELETE_ROW (X_APPROVAL_GROUP_ID in NUMBER
586 ) is
587 begin
588 if AME_SEED_UTILITY.MLS_ENABLED then
589 delete from AME_APPROVAL_GROUPS_TL
590 where APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID;
591 end if;
592 delete from AME_APPROVAL_GROUPS
593 where APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID;
594 if (sql%notfound) then
595 raise no_data_found;
596 end if;
597 end DELETE_ROW;
598
599 procedure TRANSLATE_ROW
600 (X_APPROVAL_GROUP_NAME in VARCHAR2
601 ,X_USER_APPROVAL_GROUP_NAME in VARCHAR2
602 ,X_DESCRIPTION in VARCHAR2
603 ,X_OWNER in varchar2
604 ,X_LAST_UPDATE_DATE in varchar2
605 ,X_CUSTOM_MODE in varchar2
606 ) as
607 X_CURRENT_OWNER number;
608 X_CURRENT_LAST_UPDATE_DATE varchar2(20);
609 X_CREATED_BY varchar2(100);
610 X_APPROVAL_GROUP_ID number;
611 begin
612 if not AME_SEED_UTILITY.MLS_ENABLED then
613 return;
614 end if;
615 begin
616 select AAGTL.LAST_UPDATED_BY,
617 AME_SEED_UTILITY.DATE_AS_STRING(AAGTL.LAST_UPDATE_DATE),
618 AME_SEED_UTILITY.OWNER_AS_STRING(AAGTL.CREATED_BY),
619 AAG.APPROVAL_GROUP_ID
620 into X_CURRENT_OWNER,
621 X_CURRENT_LAST_UPDATE_DATE,
622 X_CREATED_BY,
623 X_APPROVAL_GROUP_ID
624 from AME_APPROVAL_GROUPS_TL AAGTL,
625 AME_APPROVAL_GROUPS AAG
626 where AAG.NAME = X_APPROVAL_GROUP_NAME
627 and sysdate between AAG.START_DATE and nvl(AAG.END_DATE - (1/86400),sysdate)
628 and AAGTL.APPROVAL_GROUP_ID = AAG.APPROVAL_GROUP_ID
629 and AAGTL.LANGUAGE = userenv('LANG');
630 if DO_TL_UPDATE_INSERT
631 (X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
632 X_CURRENT_OWNER => X_CURRENT_OWNER,
633 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
634 X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE,
635 X_CREATED_BY => X_CREATED_BY,
636 X_CUSTOM_MODE => X_CUSTOM_MODE) then
637 update AME_APPROVAL_GROUPS_TL AAGTL
638 set USER_APPROVAL_GROUP_NAME = nvl(X_USER_APPROVAL_GROUP_NAME,USER_APPROVAL_GROUP_NAME),
639 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
640 SOURCE_LANG = userenv('LANG'),
641 LAST_UPDATE_DATE = AME_SEED_UTILITY.DATE_AS_DATE(X_LAST_UPDATE_DATE),
642 LAST_UPDATED_BY = AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
643 LAST_UPDATE_LOGIN = 0
644 where AAGTL.APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID
645 and userenv('LANG') in (AAGTL.LANGUAGE,AAGTL.SOURCE_LANG);
646 end if;
647 exception
648 when no_data_found then
649 null;
650 end;
651 end TRANSLATE_ROW;
652
653 END AME_APPROVAL_GROUPS_LOAD_API;