[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_APPROVALS_PKG
Source
1 package body JTF_UM_APPROVALS_PKG as
2 /* $Header: JTFUMAWB.pls 120.7 2006/03/13 09:13:38 vimohan ship $ */
3 procedure INSERT_ROW (
4 X_APPROVAL_ID out NOCOPY NUMBER,
5 X_EFFECTIVE_END_DATE in DATE,
6 X_APPROVAL_KEY in VARCHAR2,
7 X_ENABLED_FLAG in VARCHAR2,
8 X_WF_ITEM_TYPE in VARCHAR2,
9 X_EFFECTIVE_START_DATE in DATE,
10 X_APPLICATION_ID in NUMBER,
11 X_APPROVAL_NAME in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER,
18 X_USE_PENDING_REQ_FLAG in VARCHAR2
19 ) is
20 begin
21 insert into JTF_UM_APPROVALS_B (
22 EFFECTIVE_END_DATE,
23 APPROVAL_ID,
24 APPROVAL_KEY,
25 ENABLED_FLAG,
26 WF_ITEM_TYPE,
27 EFFECTIVE_START_DATE,
28 APPLICATION_ID,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN,
34 USE_PENDING_REQ_FLAG
35 ) values (
36 X_EFFECTIVE_END_DATE,
37 JTF_UM_APPROVALS_B_S.NEXTVAL,
38 X_APPROVAL_KEY,
39 X_ENABLED_FLAG,
40 X_WF_ITEM_TYPE,
41 X_EFFECTIVE_START_DATE,
42 X_APPLICATION_ID,
43 X_CREATION_DATE,
44 X_CREATED_BY,
45 X_LAST_UPDATE_DATE,
46 X_LAST_UPDATED_BY,
47 X_LAST_UPDATE_LOGIN,
48 X_USE_PENDING_REQ_FLAG
49 ) RETURNING APPROVAL_ID INTO X_APPROVAL_ID;
50
51 insert into JTF_UM_APPROVALS_TL (
52 LAST_UPDATE_LOGIN,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_DATE,
55 DESCRIPTION,
56 CREATED_BY,
57 CREATION_DATE,
58 APPROVAL_ID,
59 APPROVAL_NAME,
60 APPLICATION_ID,
61 LANGUAGE,
62 SOURCE_LANG
63 ) select
64 X_LAST_UPDATE_LOGIN,
65 X_LAST_UPDATED_BY,
66 X_LAST_UPDATE_DATE,
67 X_DESCRIPTION,
68 X_CREATED_BY,
69 X_CREATION_DATE,
70 X_APPROVAL_ID,
71 X_APPROVAL_NAME,
72 X_APPLICATION_ID,
73 L.LANGUAGE_CODE,
74 userenv('LANG')
75 from FND_LANGUAGES L
76 where L.INSTALLED_FLAG in ('I', 'B')
77 and not exists
78 (select NULL
79 from JTF_UM_APPROVALS_TL T
80 where T.APPROVAL_ID = X_APPROVAL_ID
81 and T.LANGUAGE = L.LANGUAGE_CODE);
82
83 end INSERT_ROW;
84
85 procedure INSERT_APPROVERS_ROW (
86 X_APPROVER_ID out NOCOPY NUMBER,
87 X_APPROVAL_ID in NUMBER,
88 X_APPROVAL_SEQ in NUMBER,
89 X_EFFECTIVE_START_DATE in DATE,
90 X_CREATED_BY in NUMBER,
91 X_CREATION_DATE in DATE,
92 X_LAST_UPDATED_BY in NUMBER,
93 X_LAST_UPDATE_DATE in DATE,
94 X_LAST_UPDATE_LOGIN in NUMBER,
95 X_USER_ID in NUMBER,
96 X_ORG_PARTY_ID in NUMBER
97 ) is
98 begin
99 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => 'JTF.UM.PLSQL.BUGTEST',
100 p_message => 'bef insert approver');
101 insert into JTF_UM_APPROVERS (
102 APPROVER_ID,
103 APPROVAL_ID,
104 APPROVER_SEQ,
105 EFFECTIVE_START_DATE,
106 CREATED_BY,
107 CREATION_DATE,
108 LAST_UPDATED_BY,
109 LAST_UPDATE_DATE,
110 LAST_UPDATE_LOGIN,
111 USER_ID,
112 ORG_PARTY_ID
113 ) values (
114 JTF_UM_APPROVERS_S.NEXTVAL,
115 X_APPROVAL_ID,
116 X_APPROVAL_SEQ,
117 X_EFFECTIVE_START_DATE,
118 X_CREATED_BY,
119 X_CREATION_DATE,
120 X_LAST_UPDATED_BY,
121 X_LAST_UPDATE_DATE,
122 X_LAST_UPDATE_LOGIN,
123 X_USER_ID,
124 X_ORG_PARTY_ID
125 ) RETURNING APPROVER_ID INTO X_APPROVER_ID;
126
127 end INSERT_APPROVERS_ROW;
128
129 procedure LOCK_ROW (
130 X_APPROVAL_ID in NUMBER,
131 X_EFFECTIVE_END_DATE in DATE,
132 X_APPROVAL_KEY in VARCHAR2,
133 X_ENABLED_FLAG in VARCHAR2,
134 X_WF_ITEM_TYPE in VARCHAR2,
135 X_EFFECTIVE_START_DATE in DATE,
136 X_APPLICATION_ID in NUMBER,
137 X_APPROVAL_NAME in VARCHAR2,
138 X_DESCRIPTION in VARCHAR2
139 ) is
140 cursor c is select
141 EFFECTIVE_END_DATE,
142 APPROVAL_KEY,
143 ENABLED_FLAG,
144 WF_ITEM_TYPE,
145 EFFECTIVE_START_DATE,
146 APPLICATION_ID
147 from JTF_UM_APPROVALS_B
148 where APPROVAL_ID = X_APPROVAL_ID
149 for update of APPROVAL_ID nowait;
150 recinfo c%rowtype;
151
152 cursor c1 is select
153 APPROVAL_NAME,
154 DESCRIPTION,
155 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
156 from JTF_UM_APPROVALS_TL
157 where APPROVAL_ID = X_APPROVAL_ID
158 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
159 for update of APPROVAL_ID nowait;
160 begin
161 open c;
162 fetch c into recinfo;
163 if (c%notfound) then
164 close c;
165 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
166 app_exception.raise_exception;
167 end if;
168 close c;
169 if ( ((recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
170 OR ((recinfo.EFFECTIVE_END_DATE is null) AND (X_EFFECTIVE_END_DATE is null)))
171 AND (recinfo.APPROVAL_KEY = X_APPROVAL_KEY)
172 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
173 AND (recinfo.WF_ITEM_TYPE = X_WF_ITEM_TYPE)
174 AND (recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
175 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
176 ) then
177 null;
178 else
179 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
180 app_exception.raise_exception;
181 end if;
182
183 for tlinfo in c1 loop
184 if (tlinfo.BASELANG = 'Y') then
185 if ( (tlinfo.APPROVAL_NAME = X_APPROVAL_NAME)
186 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
187 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
188 ) then
189 null;
190 else
191 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
192 app_exception.raise_exception;
193 end if;
194 end if;
195 end loop;
196 return;
197 end LOCK_ROW;
198
199 procedure UPDATE_ROW (
200 X_APPROVAL_ID in NUMBER,
201 X_APPROVAL_KEY in VARCHAR2,
202 X_ENABLED_FLAG in VARCHAR2,
203 X_WF_ITEM_TYPE in VARCHAR2,
204 X_APPLICATION_ID in NUMBER,
205 X_APPROVAL_NAME in VARCHAR2,
206 X_DESCRIPTION in VARCHAR2,
207 X_EFFECTIVE_END_DATE in DATE,
208 X_LAST_UPDATE_DATE in DATE,
209 X_LAST_UPDATED_BY in NUMBER,
210 X_LAST_UPDATE_LOGIN in NUMBER,
211 X_USE_PENDING_REQ_FLAG in VARCHAR2
212 ) is
213 begin
214 update JTF_UM_APPROVALS_B set
215 APPROVAL_KEY = X_APPROVAL_KEY,
216 ENABLED_FLAG = X_ENABLED_FLAG,
217 WF_ITEM_TYPE = X_WF_ITEM_TYPE,
218 APPLICATION_ID = X_APPLICATION_ID,
219 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
220 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
222 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
223 USE_PENDING_REQ_FLAG = X_USE_PENDING_REQ_FLAG
224 where APPROVAL_ID = X_APPROVAL_ID;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229
230 update JTF_UM_APPROVALS_TL set
231 APPROVAL_NAME = X_APPROVAL_NAME,
232 DESCRIPTION = X_DESCRIPTION,
233 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
234 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
235 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
236 SOURCE_LANG = userenv('LANG')
237 where APPROVAL_ID = X_APPROVAL_ID
238 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243 end UPDATE_ROW;
244
245
246
247 --For this procedure, if APPROVAL_ID passed as input is NULL, then create a new record
248 -- otherwise, modify the existing record.
249
250 procedure LOAD_ROW (
251 X_APPROVAL_ID IN NUMBER,
252 X_EFFECTIVE_START_DATE IN DATE,
253 X_EFFECTIVE_END_DATE IN DATE,
254 X_OWNER IN VARCHAR2,
255 X_APPLICATION_ID IN NUMBER,
256 X_ENABLED_FLAG IN VARCHAR2,
257 X_WF_ITEM_TYPE IN VARCHAR2,
258 X_USE_PENDING_REQ_FLAG IN VARCHAR2,
259 X_APPROVAL_KEY IN VARCHAR2,
260 X_APPROVAL_NAME IN VARCHAR2,
261 X_DESCRIPTION IN VARCHAR2,
262 x_last_update_date in varchar2 default NULL,
263 X_CUSTOM_MODE in varchar2 default NULL
264 ) is
265 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
266 l_approval_id NUMBER := 0;
267 f_luby number; -- entity owner in file
268 f_ludate date; -- entity update date in file
269 db_luby number; -- entity owner in db
270 db_ludate date; -- entity update date in db
271
272 begin
273 -- if (x_owner = 'SEED') then
274 -- l_user_id := 1;
275 -- end if;
276
277 -- Translate owner to file_last_updated_by
278 f_luby := fnd_load_util.owner_id(x_owner);
279
280 -- Translate char last_update_date to date
281 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
282
283
284 -- If APPROVAL_ID passed in NULL, insert the record
285 if ( X_APPROVAL_ID is NULL ) THEN
286 INSERT_ROW(
287 X_APPROVAL_ID => l_approval_id,
288 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
289 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
290 X_APPLICATION_ID => X_APPLICATION_ID,
291 X_ENABLED_FLAG => X_ENABLED_FLAG,
292 X_WF_ITEM_TYPE => X_WF_ITEM_TYPE,
293 X_USE_PENDING_REQ_FLAG => X_USE_PENDING_REQ_FLAG,
294 X_APPROVAL_KEY => X_APPROVAL_KEY,
295 X_APPROVAL_NAME => X_APPROVAL_NAME,
296 X_DESCRIPTION => X_DESCRIPTION,
297 X_CREATION_DATE => f_ludate,
298 X_CREATED_BY => f_luby,
299 X_LAST_UPDATE_DATE => f_ludate,
300 X_LAST_UPDATED_BY => f_luby,
301 X_LAST_UPDATE_LOGIN => l_user_id
302 );
303 else
304 -- This select stmnt also checks if
305 -- there is a row for this app_id and this app_short_name
306 -- Exception is thrown otherwise.
307 select LAST_UPDATED_BY, LAST_UPDATE_DATE
308 into db_luby, db_ludate
309 FROM JTF_UM_APPROVALS_B
310 where APPROVAL_ID = X_APPROVAL_ID;
311
312 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
313 db_ludate, X_CUSTOM_MODE)) then
314
315 UPDATE_ROW(
316 X_APPROVAL_ID => X_APPROVAL_ID,
317 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
318 X_APPLICATION_ID => X_APPLICATION_ID,
319 X_ENABLED_FLAG => X_ENABLED_FLAG,
320 X_WF_ITEM_TYPE => X_WF_ITEM_TYPE,
321 X_USE_PENDING_REQ_FLAG => X_USE_PENDING_REQ_FLAG,
322 X_APPROVAL_KEY => X_APPROVAL_KEY,
323 X_APPROVAL_NAME => X_APPROVAL_NAME,
324 X_DESCRIPTION => X_DESCRIPTION,
325 X_LAST_UPDATE_DATE => f_ludate,
326 X_LAST_UPDATED_BY => f_luby,
327 X_LAST_UPDATE_LOGIN => l_user_id
328 );
329
330 end if;
331 end if;
332
333 end LOAD_ROW;
334
335
336
337 procedure UPDATE_APPROVERS_ROW (
338 X_APPROVER_ID in NUMBER,
339 X_APPROVAL_ID in NUMBER,
340 X_APPROVAL_SEQ in NUMBER,
341 X_LAST_UPDATED_BY in NUMBER,
342 X_LAST_UPDATE_DATE in DATE,
343 X_LAST_UPDATE_LOGIN in NUMBER,
344 X_USER_ID in NUMBER,
345 X_ORG_PARTY_ID in NUMBER
346 ) is
347 begin
348 update JTF_UM_APPROVERS set
349 APPROVAL_ID = X_APPROVAL_ID,
350 APPROVER_SEQ = X_APPROVAL_SEQ,
351 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
352 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
353 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
354 USER_ID = X_USER_ID,
355 ORG_PARTY_ID = X_ORG_PARTY_ID
356 where APPROVER_ID = X_APPROVER_ID;
357
358 if (sql%notfound) then
359 raise no_data_found;
360 end if;
361
362 end UPDATE_APPROVERS_ROW;
363
364 -- To overload this API, LAST_UPDATED_BY and LAST_UPDATE_LOGIN have same value and so only 1 is passed
365 procedure UPDATE_APPROVERS_ROW (
366 X_APPROVER_ID in NUMBER,
367 X_APPROVAL_ID in NUMBER,
368 X_APPROVER_SEQ in NUMBER,
369 X_LAST_UPDATED_BY in NUMBER,
370 X_LAST_UPDATE_DATE in DATE,
371 X_EFFECTIVE_END_DATE in DATE,
372 X_USER_ID in NUMBER
373 ) is
374 begin
375 update JTF_UM_APPROVERS set
376 APPROVAL_ID = X_APPROVAL_ID,
377 APPROVER_SEQ = X_APPROVER_SEQ,
378 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
379 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
380 LAST_UPDATE_LOGIN = X_LAST_UPDATED_BY,
381 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
382 USER_ID = X_USER_ID
383 where APPROVER_ID = X_APPROVER_ID;
384
385 if (sql%notfound) then
386 raise no_data_found;
387 end if;
388
389 end UPDATE_APPROVERS_ROW;
390
391 procedure CREATE_APPROVERS_ROW (
392 X_APPROVER_ID out NOCOPY NUMBER,
393 X_APPROVAL_ID in NUMBER,
394 X_APPROVER_SEQ in NUMBER,
395 X_USER_ID in NUMBER,
396 X_EFFECTIVE_START_DATE in DATE,
397 X_EFFECTIVE_END_DATE in DATE,
398 X_CREATION_DATE in DATE,
399 X_CREATED_BY in NUMBER,
400 X_LAST_UPDATE_DATE in DATE,
401 X_LAST_UPDATED_BY in NUMBER,
402 X_LAST_UPDATE_LOGIN in NUMBER
403 ) is
404 cursor C is select ROWID from JTF_UM_APPROVERS
405 where APPROVER_ID = X_APPROVER_ID
406 ;
407 begin
408 insert into JTF_UM_APPROVERS (
409 EFFECTIVE_END_DATE,
410 APPROVAL_ID,
411 USER_ID,
412 APPROVER_SEQ,
413 EFFECTIVE_START_DATE,
414 APPROVER_ID,
415 CREATION_DATE,
416 CREATED_BY,
420 ) values (
417 LAST_UPDATE_DATE,
418 LAST_UPDATED_BY,
419 LAST_UPDATE_LOGIN
421 X_EFFECTIVE_END_DATE,
422 X_APPROVAL_ID,
423 X_USER_ID,
424 X_APPROVER_SEQ,
425 X_EFFECTIVE_START_DATE,
426 JTF_UM_APPROVERS_S.NEXTVAL,
427 X_CREATION_DATE,
428 X_CREATED_BY,
429 X_LAST_UPDATE_DATE,
430 X_LAST_UPDATED_BY,
431 X_LAST_UPDATE_LOGIN
432 ) RETURNING APPROVER_ID INTO X_APPROVER_ID;
433
434 open c;
435 if (c%notfound) then
436 close c;
437 raise no_data_found;
438 end if;
439 close c;
440
441 end CREATE_APPROVERS_ROW;
442
443 procedure LOAD_APPROVERS_ROW(
444 X_APPROVAL_ID IN NUMBER,
445 X_APPROVER_SEQ IN NUMBER,
446 X_USER_ID IN NUMBER,
447 X_EFFECTIVE_START_DATE IN DATE,
448 X_EFFECTIVE_END_DATE IN DATE,
449 X_OWNER IN VARCHAR2,
450 x_last_update_date in varchar2 default NULL,
451 X_CUSTOM_MODE in varchar2 default NULL
452 ) is
453
454 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
455 l_approver_id NUMBER := 0;
456 h_record_exists NUMBER := 0;
457
458 f_luby number; -- entity owner in file
459 f_ludate date; -- entity update date in file
460 db_luby number; -- entity owner in db
461 db_ludate date; -- entity update date in db
462
463
464 begin
465 -- if (x_owner = 'SEED') then
466 -- l_user_id := 1;
467 -- end if;
468
469 select count(*)
470 into h_record_exists
471 from JTF_UM_APPROVERS
472 where USER_ID = X_USER_ID
473 and APPROVAL_ID = X_APPROVAL_ID
474 and APPROVER_SEQ = X_APPROVER_SEQ
475 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
476
477 -- Translate owner to file_last_updated_by
478 f_luby := fnd_load_util.owner_id(x_owner);
479
480 -- Translate char last_update_date to date
481 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
482
483
484 -- TRY update, and if it fails, insert
485
486 if ( h_record_exists = 0 ) then
487
488 CREATE_APPROVERS_ROW(
489 X_APPROVER_ID => l_approver_id,
490 X_APPROVAL_ID => X_APPROVAL_ID,
491 X_APPROVER_SEQ => X_APPROVER_SEQ,
492 X_USER_ID => X_USER_ID,
493 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
494 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
495 X_CREATION_DATE => f_ludate,
496 X_CREATED_BY => f_luby,
497 X_LAST_UPDATE_DATE => f_ludate,
498 X_LAST_UPDATED_BY => f_luby,
499 X_LAST_UPDATE_LOGIN => l_user_id
500 );
501 else
502 -- selecting the approver_id as it is needed for update
503 select APPROVER_ID
504 into l_approver_id
505 from JTF_UM_APPROVERS
506 where USER_ID = X_USER_ID
507 and APPROVAL_ID = X_APPROVAL_ID
508 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
509
510
511
512
513 -- This select stmnt also checks if
514 -- there is a row for this app_id and this app_short_name
515 -- Exception is thrown otherwise.
516 select LAST_UPDATED_BY, LAST_UPDATE_DATE
517 into db_luby, db_ludate
518 FROM JTF_UM_APPROVERS
519 where APPROVER_ID = l_approver_id;
520
521 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
522 db_ludate, X_CUSTOM_MODE)) then
523
524
525 UPDATE_APPROVERS_ROW(
526 X_APPROVER_ID => l_approver_id,
527 X_APPROVAL_ID => X_APPROVAL_ID,
528 X_APPROVER_SEQ => X_APPROVER_SEQ,
529 X_USER_ID => X_USER_ID,
530 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
531 X_LAST_UPDATE_DATE => f_ludate,
532 X_LAST_UPDATED_BY => f_luby
533 );
534 end if;
535
536 end if;
537
538 end LOAD_APPROVERS_ROW;
539
540 procedure DELETE_ROW (
541 X_APPROVAL_ID in NUMBER
542 ) is
543 begin
544 delete from JTF_UM_APPROVALS_TL
545 where APPROVAL_ID = X_APPROVAL_ID;
546
547 if (sql%notfound) then
548 raise no_data_found;
549 end if;
550
551 delete from JTF_UM_APPROVALS_B
552 where APPROVAL_ID = X_APPROVAL_ID;
553
554 if (sql%notfound) then
555 raise no_data_found;
556 end if;
557 end DELETE_ROW;
558
559 procedure DELETE_APPROVERS_ROW (
560 X_APPROVER_ID in NUMBER
561 ) is
562 begin
563 delete from JTF_UM_APPROVERS
564 where APPROVER_ID = X_APPROVER_ID;
565
566 if (sql%notfound) then
567 raise no_data_found;
568 end if;
569 end DELETE_APPROVERS_ROW;
570
571 procedure ADD_LANGUAGE
572 is
573 begin
574 delete from JTF_UM_APPROVALS_TL T
575 where not exists
576 (select NULL
577 from JTF_UM_APPROVALS_B B
578 where B.APPROVAL_ID = T.APPROVAL_ID
579 );
580
581 update JTF_UM_APPROVALS_TL T set (
582 APPROVAL_NAME,
583 DESCRIPTION
584 ) = (select
585 B.APPROVAL_NAME,
586 B.DESCRIPTION
587 from JTF_UM_APPROVALS_TL B
588 where B.APPROVAL_ID = T.APPROVAL_ID
589 and B.LANGUAGE = T.SOURCE_LANG)
590 where (
591 T.APPROVAL_ID,
592 T.LANGUAGE
593 ) in (select
594 SUBT.APPROVAL_ID,
595 SUBT.LANGUAGE
596 from JTF_UM_APPROVALS_TL SUBB, JTF_UM_APPROVALS_TL SUBT
597 where SUBB.APPROVAL_ID = SUBT.APPROVAL_ID
598 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
599 and (SUBB.APPROVAL_NAME <> SUBT.APPROVAL_NAME
600 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
601 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
602 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
603 ));
604
605 insert into JTF_UM_APPROVALS_TL (
606 LAST_UPDATE_LOGIN,
607 LAST_UPDATED_BY,
608 LAST_UPDATE_DATE,
609 DESCRIPTION,
610 CREATED_BY,
611 CREATION_DATE,
612 APPROVAL_ID,
613 APPROVAL_NAME,
614 APPLICATION_ID,
615 LANGUAGE,
616 SOURCE_LANG
617 ) select /*+ ORDERED */
618 B.LAST_UPDATE_LOGIN,
619 B.LAST_UPDATED_BY,
620 B.LAST_UPDATE_DATE,
621 B.DESCRIPTION,
622 B.CREATED_BY,
623 B.CREATION_DATE,
624 B.APPROVAL_ID,
625 B.APPROVAL_NAME,
626 B.APPLICATION_ID,
627 L.LANGUAGE_CODE,
628 B.SOURCE_LANG
629 from JTF_UM_APPROVALS_TL B, FND_LANGUAGES L
630 where L.INSTALLED_FLAG in ('I', 'B')
631 and B.LANGUAGE = userenv('LANG')
632 and not exists
633 (select NULL
634 from JTF_UM_APPROVALS_TL T
635 where T.APPROVAL_ID = B.APPROVAL_ID
636 and T.LANGUAGE = L.LANGUAGE_CODE);
637 end ADD_LANGUAGE;
638
639 procedure TRANSLATE_ROW (
640 X_APPROVAL_ID in NUMBER, -- key field
641 X_APPROVAL_NAME in VARCHAR2, -- translated name
642 X_DESCRIPTION in VARCHAR2, -- translated description
643 X_OWNER in VARCHAR2, -- owner field
644 x_last_update_date in varchar2 default NULL,
645 X_CUSTOM_MODE in varchar2 default NULL
646 ) is
647
648 f_luby number; -- entity owner in file
649 f_ludate date; -- entity update date in file
650 db_luby number; -- entity owner in db
651 db_ludate date; -- entity update date in db
652
653 begin
654
655 -- Translate owner to file_last_updated_by
656 f_luby := fnd_load_util.owner_id(x_owner);
657
658 -- Translate char last_update_date to date
659 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
660
661 -- This select stmnt also checks if
662 -- there is a row for this app_id and this app_short_name
663 -- Exception is thrown otherwise.
664 select LAST_UPDATED_BY, LAST_UPDATE_DATE
665 into db_luby, db_ludate
666 FROM JTF_UM_APPROVALS_TL
667 where APPROVAL_ID = X_APPROVAL_ID
668 and LANGUAGE = userenv('LANG');
669
670 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
671 db_ludate, X_CUSTOM_MODE)) then
672 update JTF_UM_APPROVALS_TL set
673 APPROVAL_NAME = X_APPROVAL_NAME,
674 DESCRIPTION = X_DESCRIPTION,
675 LAST_UPDATE_DATE = f_ludate,
676 LAST_UPDATED_BY = f_luby,
677 LAST_UPDATE_LOGIN = 0,
678 SOURCE_LANG = userenv('LANG')
679 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
680 and APPROVAL_ID = X_APPROVAL_ID;
681 end if;
682
683 end TRANSLATE_ROW;
684
685 function is_approval_overridden(
686 p_approval_id IN NUMBER,
687 p_org_party_id IN NUMBER
688 )
689 return varchar2 is
690 cursor ap is select approval_id from jtf_um_approvers
691 where approval_id = p_approval_id
692 and org_party_id = p_org_party_id
693 and (effective_end_date is null or effective_end_date > sysdate);
694 p_result varchar2(1):= 'N';
695 p_ap_id NUMBER:= -1;
696 begin
697
701
698 open ap;
699 fetch ap into p_ap_id;
700 close ap;
702 if p_ap_id <> -1 then
703 p_result := 'Y';
704 end if;
705 return p_result;
706 end is_approval_overridden;
707
708 end JTF_UM_APPROVALS_PKG;