1 package body AMW_CONSTRAINT_WAIVERS_PKG as
2 /* $Header: amwtcnwb.pls 120.0 2005/12/28 08:42:55 appldev noship $ */
3
4
5 procedure ADD_LANGUAGE
6 is
7 begin
8 delete from AMW_CONSTRAINT_WAIVERS_TL T
9 where not exists
10 (select NULL
11 from AMW_CONSTRAINT_WAIVERS_B B
12 where B.CONSTRAINT_WAIVER_ID = T.CONSTRAINT_WAIVER_ID
13 );
14
15 update AMW_CONSTRAINT_WAIVERS_TL T set (
16 JUSTIFICATION
17 ) = (select
18 B.JUSTIFICATION
19 from AMW_CONSTRAINT_WAIVERS_TL B
20 where B.CONSTRAINT_WAIVER_ID = T.CONSTRAINT_WAIVER_ID
21 and B.LANGUAGE = T.SOURCE_LANG)
22 where (
23 T.CONSTRAINT_WAIVER_ID,
24 T.LANGUAGE
25 ) in (select
26 SUBT.CONSTRAINT_WAIVER_ID,
27 SUBT.LANGUAGE
28 from AMW_CONSTRAINT_WAIVERS_TL SUBB, AMW_CONSTRAINT_WAIVERS_TL SUBT
29 where SUBB.CONSTRAINT_WAIVER_ID = SUBT.CONSTRAINT_WAIVER_ID
30 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
31 and (SUBB.JUSTIFICATION <> SUBT.JUSTIFICATION
32 or (SUBB.JUSTIFICATION is null and SUBT.JUSTIFICATION is not null)
33 or (SUBB.JUSTIFICATION is not null and SUBT.JUSTIFICATION is null)
34 ));
35
36 insert into AMW_CONSTRAINT_WAIVERS_TL (
37 SECURITY_GROUP_ID,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_DATE,
40 CREATED_BY,
41 JUSTIFICATION,
42 SOURCE_LANG,
43 CREATION_DATE,
44 LAST_UPDATE_LOGIN,
45 CONSTRAINT_WAIVER_ID,
46 LANGUAGE
47 ) select /*+ ORDERED */
48 B.SECURITY_GROUP_ID,
49 B.LAST_UPDATED_BY,
50 B.LAST_UPDATE_DATE,
51 B.CREATED_BY,
52 B.JUSTIFICATION,
53 B.SOURCE_LANG,
54 B.CREATION_DATE,
55 B.LAST_UPDATE_LOGIN,
56 B.CONSTRAINT_WAIVER_ID,
57 L.LANGUAGE_CODE
58 from AMW_CONSTRAINT_WAIVERS_TL B, FND_LANGUAGES L
59 where L.INSTALLED_FLAG in ('I', 'B')
60 and B.LANGUAGE = userenv('LANG')
61 and not exists
62 (select NULL
63 from AMW_CONSTRAINT_WAIVERS_TL T
64 where T.CONSTRAINT_WAIVER_ID = B.CONSTRAINT_WAIVER_ID
65 and T.LANGUAGE = L.LANGUAGE_CODE);
66 end ADD_LANGUAGE;
67
68
69 end AMW_CONSTRAINT_WAIVERS_PKG;
[Home] [Help]