[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.7 2011/09/21 14:52:35 prasashe ship $ */
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 ,APPROVAL_GROUP_MEMBERS_ID
285 ) select
286 X_APPROVAL_GROUP_ID
287 ,ame_util.approverOamGroupId
288 ,X_APPROVAL_GROUP_ID
289 ,X_QUERY_STRING
290 ,1
291 ,AME_APPROVAL_GROUP_MEMBERS_S.NEXTVAL
292 from sys.dual;
293 update AME_APPROVAL_GROUP_MEMBERS
294 set QUERY_STRING = X_QUERY_STRING
295 where PARAMETER_NAME = ame_util.approverOamGroupId
296 and PARAMETER = to_char(X_APPROVAL_GROUP_ID)
297 and APPROVAL_GROUP_ID <> X_APPROVAL_GROUP_ID;
298 end POPULATE_APP_GRP_MEMBERS;
299
300 procedure CREATE_APPROVAL_CONFIG(X_APPROVAL_GROUP_ID in NUMBER
301 ,X_CREATED_BY in NUMBER
302 ,X_CREATION_DATE in DATE
303 ,X_LAST_UPDATED_BY in NUMBER
304 ,X_LAST_UPDATE_DATE in DATE
305 ,X_LAST_UPDATE_LOGIN in NUMBER
306 ,X_START_DATE in DATE
307 )
308 is
309 cursor get_active_application is
310 select APPLICATION_ID
311 from AME_CALLING_APPS
312 where sysdate between start_date
313 and nvl(end_date - ame_util.oneSecond,sysdate);
314 TYPE APPLICATION_ID_TAB is table of NUMBER index by BINARY_INTEGER;
315 APPLICATION_ID_LIST APPLICATION_ID_TAB;
316 L_ORDER_NUMBER NUMBER;
317 begin
318 AME_SEED_UTILITY.INIT_AME_INSTALLATION_LEVEL;
319 if (AME_SEED_UTILITY.AME_INSTALLATION_LEVEL is not null) and to_number(AME_SEED_UTILITY.AME_INSTALLATION_LEVEL) < 2 then
320 open get_active_application;
321 fetch get_active_application bulk collect into application_id_list;
322 close get_active_application;
323 --
324 -- Now for each active application, create a config record.
325 --
326 for indx in 1..APPLICATION_ID_LIST.count
327 loop
328 --
329 -- Obtain the maximum order number allotted for that application_id.
330 --
331 select nvl(max(order_number),0)+1
332 into L_ORDER_NUMBER
333 from AME_APPROVAL_GROUP_CONFIG
334 where application_id = APPLICATION_ID_LIST(indx)
335 and sysdate between start_date
336 and nvl(end_date - ame_util.oneSecond,sysdate);
337 --
338 -- Now insert a row for each application_id.
339 --
340 insert into AME_APPROVAL_GROUP_CONFIG(
341 APPLICATION_ID
342 ,APPROVAL_GROUP_ID
343 ,VOTING_REGIME
344 ,ORDER_NUMBER
345 ,CREATED_BY
346 ,CREATION_DATE
347 ,LAST_UPDATED_BY
348 ,LAST_UPDATE_DATE
349 ,LAST_UPDATE_LOGIN
350 ,START_DATE
351 ,END_DATE
352 ,OBJECT_VERSION_NUMBER
353 ) select
354 APPLICATION_ID_LIST(indx)
355 ,X_APPROVAL_GROUP_ID
356 ,ame_util.serializedVoting
357 ,L_ORDER_NUMBER
358 ,X_CREATED_BY
359 ,X_CREATION_DATE
360 ,X_LAST_UPDATED_BY
361 ,X_LAST_UPDATE_DATE
362 ,X_LAST_UPDATE_LOGIN
363 ,X_START_DATE
364 ,null
365 ,1
366 from sys.dual;
367 end loop;
368 end if;
369 end CREATE_APPROVAL_CONFIG;
370
371 procedure UPDATE_TL_ROW (
372 X_APPROVAL_GROUP_ID in NUMBER,
373 X_USER_APPROVAL_GROUP_NAME in VARCHAR2,
374 X_DESCRIPTION in VARCHAR2,
375 X_CREATED_BY in NUMBER,
376 X_CREATION_DATE in DATE,
377 X_LAST_UPDATED_BY in NUMBER,
378 X_LAST_UPDATE_DATE in DATE,
379 X_LAST_UPDATE_LOGIN in NUMBER,
380 X_CUSTOM_MODE in VARCHAR2) is
381 X_CURRENT_OWNER NUMBER;
382 X_CURRENT_LAST_UPDATE_DATE DATE;
383 begin
384 if not AME_SEED_UTILITY.MLS_ENABLED then
385 return;
386 end if;
387
388 select LAST_UPDATED_BY,
389 LAST_UPDATE_DATE
390 into X_CURRENT_OWNER,
391 X_CURRENT_LAST_UPDATE_DATE
392 FROM AME_APPROVAL_GROUPS_TL
393 WHERE APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID
394 AND LANGUAGE = USERENV('LANG');
395
396 if DO_UPDATE_INSERT
397 (X_LAST_UPDATED_BY
398 ,X_CURRENT_OWNER
399 ,AME_SEED_UTILITY.DATE_AS_STRING(X_LAST_UPDATE_DATE)
400 ,AME_SEED_UTILITY.DATE_AS_STRING(X_CURRENT_LAST_UPDATE_DATE)
401 ,X_CUSTOM_MODE) then
402 update AME_APPROVAL_GROUPS_TL
403 set USER_APPROVAL_GROUP_NAME = nvl(X_USER_APPROVAL_GROUP_NAME,USER_APPROVAL_GROUP_NAME),
404 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
405 SOURCE_LANG = userenv('LANG'),
406 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
407 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
408 LAST_UPDATE_LOGIN = 0
409 where APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID
410 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
411 end if;
412 exception
413 when no_data_found then
414 null;
415 end UPDATE_TL_ROW;
416
417 procedure LOAD_ROW(X_APPROVAL_GROUP_NAME in VARCHAR2
418 ,X_USER_APPROVAL_GROUP_NAME in VARCHAR2
419 ,X_DESCRIPTION in VARCHAR2
420 ,X_QUERY_STRING in VARCHAR2
421 ,X_IS_STATIC in VARCHAR2
422 ,X_OWNER in VARCHAR2
423 ,X_LAST_UPDATE_DATE in VARCHAR2
424 ,X_CUSTOM_MODE in VARCHAR2
425 )
426 is
427 X_APPROVAL_GROUP_ROWID ROWID;
428 X_APPROVAL_GROUP_ID NUMBER;
429 X_CURRENT_OWNER NUMBER;
430 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
431 X_CURRENT_OVN NUMBER;
432 X_MAX_START_DATE DATE;
433 X_CREATED_BY NUMBER;
434 X_LAST_UPDATED_BY NUMBER;
435 X_LAST_UPDATE_LOGIN NUMBER;
436 lockHandle varchar2(500);
437 returnValue integer;
438 begin
439 DBMS_LOCK.ALLOCATE_UNIQUE
440 (lockname =>'AME_APPROVAL_GROUPS.'||X_APPROVAL_GROUP_NAME
441 ,lockhandle => lockHandle
442 );
443 returnValue := DBMS_LOCK.REQUEST
444 (lockhandle => lockHandle
445 ,timeout => 0
446 ,release_on_commit => true
447 );
448 if returnValue = 0 then
449 KEY_TO_IDS (
450 X_APPROVAL_GROUP_NAME
451 ,X_APPROVAL_GROUP_ROWID
452 ,X_APPROVAL_GROUP_ID
453 ,X_CURRENT_OWNER
454 ,X_CURRENT_LAST_UPDATE_DATE
455 ,X_CURRENT_OVN
456 ,X_MAX_START_DATE
457 );
458 OWNER_TO_WHO (
459 X_OWNER
460 ,X_CREATED_BY
461 ,X_LAST_UPDATED_BY
462 ,X_LAST_UPDATE_LOGIN
463 );
464 begin
465 -- the current row was not found insert a new row
466 if (X_APPROVAL_GROUP_ID is null) then
467 INSERT_ROW (
468 X_APPROVAL_GROUP_NAME
469 ,X_QUERY_STRING
470 ,X_IS_STATIC
471 ,X_DESCRIPTION
472 ,X_CREATED_BY
473 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
474 ,X_LAST_UPDATED_BY
475 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
476 ,X_LAST_UPDATE_LOGIN
477 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
478 ,1
479 ,X_APPROVAL_GROUP_ID
480 );
481 INSERT_TL_ROW
482 (X_APPROVAL_GROUP_ID
483 ,nvl(X_USER_APPROVAL_GROUP_NAME,X_APPROVAL_GROUP_NAME)
484 ,X_DESCRIPTION
485 ,X_CREATED_BY
486 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
487 ,X_LAST_UPDATED_BY
488 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
489 ,X_LAST_UPDATE_LOGIN
490 );
491 --
492 -- Create config records.
493 --
494 CREATE_APPROVAL_CONFIG
495 (X_APPROVAL_GROUP_ID => X_APPROVAL_GROUP_ID
496 ,X_CREATED_BY => X_CREATED_BY
497 ,X_CREATION_DATE => to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
498 ,X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
499 ,X_LAST_UPDATE_DATE => to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
500 ,X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
501 ,X_START_DATE => to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
502 );
503 POPULATE_APP_GRP_MEMBERS(X_APPROVAL_GROUP_ID => X_APPROVAL_GROUP_ID
504 ,X_QUERY_STRING => X_QUERY_STRING
505 );
506 -- the current row was found end date the current row
507 -- insert a row with the same attribute id
508 else
509 if X_CUSTOM_MODE = 'FORCE' then
510 FORCE_UPDATE_ROW (
511 X_APPROVAL_GROUP_ROWID
512 ,X_APPROVAL_GROUP_NAME
513 ,X_QUERY_STRING
514 ,X_IS_STATIC
515 ,X_DESCRIPTION
516 ,X_CREATED_BY
517 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
518 ,X_LAST_UPDATED_BY
519 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
520 ,X_LAST_UPDATE_LOGIN
521 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
522 ,AME_SEED_UTILITY.GET_DEFAULT_END_DATE
523 ,X_CURRENT_OVN + 1
524 );
525 UPDATE_TL_ROW
526 (X_APPROVAL_GROUP_ID
527 ,nvl(X_USER_APPROVAL_GROUP_NAME,X_APPROVAL_GROUP_NAME)
528 ,X_DESCRIPTION
529 ,X_CREATED_BY
530 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
531 ,X_LAST_UPDATED_BY
532 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
533 ,X_LAST_UPDATE_LOGIN
534 ,X_CUSTOM_MODE
535 );
536 POPULATE_APP_GRP_MEMBERS(X_APPROVAL_GROUP_ID => X_APPROVAL_GROUP_ID
537 ,X_QUERY_STRING => X_QUERY_STRING
538 );
539 else
540 if DO_UPDATE_INSERT
541 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER)
542 ,X_CURRENT_OWNER
543 ,X_LAST_UPDATE_DATE
544 ,X_CURRENT_LAST_UPDATE_DATE
545 )
546 and (X_MAX_START_DATE is not null)
547 and (to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS') > X_MAX_START_DATE)
548 then
549 UPDATE_ROW (
550 X_APPROVAL_GROUP_ROWID
551 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400)
552 ,X_APPROVAL_GROUP_ID
553 ,X_APPROVAL_GROUP_NAME
554 ,X_QUERY_STRING
555 ,X_IS_STATIC
556 ,X_CREATED_BY
557 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
558 ,X_LAST_UPDATED_BY
559 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
560 ,X_LAST_UPDATE_LOGIN
561 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
562 ,X_DESCRIPTION
563 ,X_CURRENT_OVN + 1
564 );
565 UPDATE_TL_ROW
566 (X_APPROVAL_GROUP_ID
567 ,nvl(X_USER_APPROVAL_GROUP_NAME,X_APPROVAL_GROUP_NAME)
568 ,X_DESCRIPTION
569 ,X_CREATED_BY
570 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
571 ,X_LAST_UPDATED_BY
572 ,to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
573 ,X_LAST_UPDATE_LOGIN
574 ,X_CUSTOM_MODE
575 );
576 POPULATE_APP_GRP_MEMBERS(X_APPROVAL_GROUP_ID => X_APPROVAL_GROUP_ID
577 ,X_QUERY_STRING => X_QUERY_STRING
578 );
579 end if;
580 end if;
581 end if;
582 end;
583 end if;
584 exception
585 when others then
586 ame_util.runtimeException('ame_approval_groups_api'
587 ,'load_row'
588 ,sqlcode
589 ,sqlerrm);
590 raise;
591 end LOAD_ROW;
592
593 procedure DELETE_ROW (X_APPROVAL_GROUP_ID in NUMBER
594 ) is
595 begin
596 if AME_SEED_UTILITY.MLS_ENABLED then
597 delete from AME_APPROVAL_GROUPS_TL
598 where APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID;
599 end if;
600 delete from AME_APPROVAL_GROUPS
601 where APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID;
602 if (sql%notfound) then
603 raise no_data_found;
604 end if;
605 end DELETE_ROW;
606
607 procedure TRANSLATE_ROW
608 (X_APPROVAL_GROUP_NAME in VARCHAR2
609 ,X_USER_APPROVAL_GROUP_NAME in VARCHAR2
610 ,X_DESCRIPTION in VARCHAR2
611 ,X_OWNER in varchar2
612 ,X_LAST_UPDATE_DATE in varchar2
613 ,X_CUSTOM_MODE in varchar2
614 ) as
615 X_CURRENT_OWNER number;
616 X_CURRENT_LAST_UPDATE_DATE varchar2(20);
617 X_CREATED_BY varchar2(100);
618 X_APPROVAL_GROUP_ID number;
619 begin
620 if not AME_SEED_UTILITY.MLS_ENABLED then
621 return;
622 end if;
623 begin
624 select AAGTL.LAST_UPDATED_BY,
625 AME_SEED_UTILITY.DATE_AS_STRING(AAGTL.LAST_UPDATE_DATE),
626 AME_SEED_UTILITY.OWNER_AS_STRING(AAGTL.CREATED_BY),
627 AAG.APPROVAL_GROUP_ID
628 into X_CURRENT_OWNER,
629 X_CURRENT_LAST_UPDATE_DATE,
630 X_CREATED_BY,
631 X_APPROVAL_GROUP_ID
632 from AME_APPROVAL_GROUPS_TL AAGTL,
633 AME_APPROVAL_GROUPS AAG
634 where AAG.NAME = X_APPROVAL_GROUP_NAME
635 and sysdate between AAG.START_DATE and nvl(AAG.END_DATE - (1/86400),sysdate)
636 and AAGTL.APPROVAL_GROUP_ID = AAG.APPROVAL_GROUP_ID
637 and AAGTL.LANGUAGE = userenv('LANG');
638 if DO_TL_UPDATE_INSERT
639 (X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
640 X_CURRENT_OWNER => X_CURRENT_OWNER,
641 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
642 X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE,
643 X_CREATED_BY => X_CREATED_BY,
644 X_CUSTOM_MODE => X_CUSTOM_MODE) then
645 update AME_APPROVAL_GROUPS_TL AAGTL
646 set USER_APPROVAL_GROUP_NAME = nvl(X_USER_APPROVAL_GROUP_NAME,USER_APPROVAL_GROUP_NAME),
647 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
648 SOURCE_LANG = userenv('LANG'),
649 LAST_UPDATE_DATE = AME_SEED_UTILITY.DATE_AS_DATE(X_LAST_UPDATE_DATE),
650 LAST_UPDATED_BY = AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
651 LAST_UPDATE_LOGIN = 0
652 where AAGTL.APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID
653 and userenv('LANG') in (AAGTL.LANGUAGE,AAGTL.SOURCE_LANG);
654 end if;
655 exception
656 when no_data_found then
657 null;
658 end;
659 end TRANSLATE_ROW;
660
661 END AME_APPROVAL_GROUPS_LOAD_API;