1 package body AMW_WORK_EXT_PKG as
2 /* $Header: amwtwkxb.pls 120.0 2005/12/05 10:31:28 appldev noship $ */
3
4
5 procedure ADD_LANGUAGE
6 is
7 begin
8 delete from AMW_WORK_EXT_TL T
9 where not exists
10 (select NULL
11 from AMW_WORK_EXT_B B
12 where B.EXTENSION_ID = T.EXTENSION_ID
13 );
14
15 update AMW_WORK_EXT_TL T set (
16 TL_EXT_ATTR1,TL_EXT_ATTR2,TL_EXT_ATTR3,TL_EXT_ATTR4,TL_EXT_ATTR5,
17 TL_EXT_ATTR6,TL_EXT_ATTR7,TL_EXT_ATTR8,TL_EXT_ATTR9,TL_EXT_ATTR10,
18 TL_EXT_ATTR11,TL_EXT_ATTR12,TL_EXT_ATTR13,TL_EXT_ATTR14,TL_EXT_ATTR15,
19 TL_EXT_ATTR16,TL_EXT_ATTR17,TL_EXT_ATTR18,TL_EXT_ATTR19,TL_EXT_ATTR20,
20 TL_EXT_ATTR21,TL_EXT_ATTR22,TL_EXT_ATTR23,TL_EXT_ATTR24,TL_EXT_ATTR25,
21 TL_EXT_ATTR26,TL_EXT_ATTR27,TL_EXT_ATTR28,TL_EXT_ATTR29,TL_EXT_ATTR30,
22 TL_EXT_ATTR31,TL_EXT_ATTR32,TL_EXT_ATTR33,TL_EXT_ATTR34,TL_EXT_ATTR35,
23 TL_EXT_ATTR36,TL_EXT_ATTR37,TL_EXT_ATTR38,TL_EXT_ATTR39,TL_EXT_ATTR40
24 ) = (select
25 B.TL_EXT_ATTR1,B.TL_EXT_ATTR2,B.TL_EXT_ATTR3,B.TL_EXT_ATTR4,B.TL_EXT_ATTR5,
26 B.TL_EXT_ATTR6,B.TL_EXT_ATTR7,B.TL_EXT_ATTR8,B.TL_EXT_ATTR9,B.TL_EXT_ATTR10,
27 B.TL_EXT_ATTR11,B.TL_EXT_ATTR12,B.TL_EXT_ATTR13,B.TL_EXT_ATTR14,B.TL_EXT_ATTR15,
28 B.TL_EXT_ATTR16,B.TL_EXT_ATTR17,B.TL_EXT_ATTR18,B.TL_EXT_ATTR19,B.TL_EXT_ATTR20,
29 B.TL_EXT_ATTR21,B.TL_EXT_ATTR22,B.TL_EXT_ATTR23,B.TL_EXT_ATTR24,B.TL_EXT_ATTR25,
30 B.TL_EXT_ATTR26,B.TL_EXT_ATTR27,B.TL_EXT_ATTR28,B.TL_EXT_ATTR29,B.TL_EXT_ATTR30,
31 B.TL_EXT_ATTR31,B.TL_EXT_ATTR32,B.TL_EXT_ATTR33,B.TL_EXT_ATTR34,B.TL_EXT_ATTR35,
32 B.TL_EXT_ATTR36,B.TL_EXT_ATTR37,B.TL_EXT_ATTR38,B.TL_EXT_ATTR39,B.TL_EXT_ATTR40
33 from AMW_WORK_EXT_TL B
34 where B.EXTENSION_ID = T.EXTENSION_ID
35 and B.LANGUAGE = T.SOURCE_LANG)
36 where (
37 T.EXTENSION_ID,
38 T.LANGUAGE
39 ) in (select
40 SUBT.EXTENSION_ID,
41 SUBT.LANGUAGE
42 from AMW_WORK_EXT_TL SUBB, AMW_WORK_EXT_TL SUBT
43 where SUBB.EXTENSION_ID = SUBT.EXTENSION_ID
44 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
45 and (SUBB.TL_EXT_ATTR1 <> SUBT.TL_EXT_ATTR1
46 or (SUBB.TL_EXT_ATTR1 is null and SUBT.TL_EXT_ATTR1 is not null)
47 or (SUBB.TL_EXT_ATTR1 is not null and SUBT.TL_EXT_ATTR1 is null)
48 or SUBB.TL_EXT_ATTR2 <> SUBT.TL_EXT_ATTR2
49 or (SUBB.TL_EXT_ATTR2 is null and SUBT.TL_EXT_ATTR2 is not null)
50 or (SUBB.TL_EXT_ATTR2 is not null and SUBT.TL_EXT_ATTR2 is null)
51 or SUBB.TL_EXT_ATTR3 <> SUBT.TL_EXT_ATTR3
52 or (SUBB.TL_EXT_ATTR3 is null and SUBT.TL_EXT_ATTR3 is not null)
53 or (SUBB.TL_EXT_ATTR3 is not null and SUBT.TL_EXT_ATTR3 is null)
54 or SUBB.TL_EXT_ATTR4 <> SUBT.TL_EXT_ATTR4
55 or (SUBB.TL_EXT_ATTR4 is null and SUBT.TL_EXT_ATTR4 is not null)
56 or (SUBB.TL_EXT_ATTR4 is not null and SUBT.TL_EXT_ATTR4 is null)
57 or SUBB.TL_EXT_ATTR5 <> SUBT.TL_EXT_ATTR5
58 or (SUBB.TL_EXT_ATTR5 is null and SUBT.TL_EXT_ATTR5 is not null)
59 or (SUBB.TL_EXT_ATTR5 is not null and SUBT.TL_EXT_ATTR5 is null)
60 or SUBB.TL_EXT_ATTR6 <> SUBT.TL_EXT_ATTR6
61 or (SUBB.TL_EXT_ATTR6 is null and SUBT.TL_EXT_ATTR6 is not null)
62 or (SUBB.TL_EXT_ATTR6 is not null and SUBT.TL_EXT_ATTR6 is null)
63 or SUBB.TL_EXT_ATTR7 <> SUBT.TL_EXT_ATTR7
64 or (SUBB.TL_EXT_ATTR7 is null and SUBT.TL_EXT_ATTR7 is not null)
65 or (SUBB.TL_EXT_ATTR7 is not null and SUBT.TL_EXT_ATTR7 is null)
66 or SUBB.TL_EXT_ATTR8 <> SUBT.TL_EXT_ATTR8
67 or (SUBB.TL_EXT_ATTR8 is null and SUBT.TL_EXT_ATTR8 is not null)
68 or (SUBB.TL_EXT_ATTR8 is not null and SUBT.TL_EXT_ATTR8 is null)
69 or SUBB.TL_EXT_ATTR9 <> SUBT.TL_EXT_ATTR9
70 or (SUBB.TL_EXT_ATTR9 is null and SUBT.TL_EXT_ATTR9 is not null)
71 or (SUBB.TL_EXT_ATTR9 is not null and SUBT.TL_EXT_ATTR9 is null)
72 or SUBB.TL_EXT_ATTR10 <> SUBT.TL_EXT_ATTR10
73 or (SUBB.TL_EXT_ATTR10 is null and SUBT.TL_EXT_ATTR10 is not null)
74 or (SUBB.TL_EXT_ATTR10 is not null and SUBT.TL_EXT_ATTR10 is null)
75 or SUBB.TL_EXT_ATTR11 <> SUBT.TL_EXT_ATTR11
76 or (SUBB.TL_EXT_ATTR11 is null and SUBT.TL_EXT_ATTR11 is not null)
77 or (SUBB.TL_EXT_ATTR11 is not null and SUBT.TL_EXT_ATTR11 is null)
78 or SUBB.TL_EXT_ATTR12 <> SUBT.TL_EXT_ATTR12
79 or (SUBB.TL_EXT_ATTR12 is null and SUBT.TL_EXT_ATTR12 is not null)
80 or (SUBB.TL_EXT_ATTR12 is not null and SUBT.TL_EXT_ATTR12 is null)
81 or SUBB.TL_EXT_ATTR13 <> SUBT.TL_EXT_ATTR13
82 or (SUBB.TL_EXT_ATTR13 is null and SUBT.TL_EXT_ATTR13 is not null)
83 or (SUBB.TL_EXT_ATTR13 is not null and SUBT.TL_EXT_ATTR13 is null)
84 or SUBB.TL_EXT_ATTR14 <> SUBT.TL_EXT_ATTR14
85 or (SUBB.TL_EXT_ATTR14 is null and SUBT.TL_EXT_ATTR14 is not null)
86 or (SUBB.TL_EXT_ATTR14 is not null and SUBT.TL_EXT_ATTR14 is null)
87 or SUBB.TL_EXT_ATTR15 <> SUBT.TL_EXT_ATTR15
88 or (SUBB.TL_EXT_ATTR15 is null and SUBT.TL_EXT_ATTR15 is not null)
89 or (SUBB.TL_EXT_ATTR15 is not null and SUBT.TL_EXT_ATTR15 is null)
90 or SUBB.TL_EXT_ATTR16 <> SUBT.TL_EXT_ATTR16
91 or (SUBB.TL_EXT_ATTR16 is null and SUBT.TL_EXT_ATTR16 is not null)
92 or (SUBB.TL_EXT_ATTR16 is not null and SUBT.TL_EXT_ATTR16 is null)
93 or SUBB.TL_EXT_ATTR17 <> SUBT.TL_EXT_ATTR17
94 or (SUBB.TL_EXT_ATTR17 is null and SUBT.TL_EXT_ATTR17 is not null)
95 or (SUBB.TL_EXT_ATTR17 is not null and SUBT.TL_EXT_ATTR17 is null)
96 or SUBB.TL_EXT_ATTR18 <> SUBT.TL_EXT_ATTR18
97 or (SUBB.TL_EXT_ATTR18 is null and SUBT.TL_EXT_ATTR18 is not null)
98 or (SUBB.TL_EXT_ATTR18 is not null and SUBT.TL_EXT_ATTR18 is null)
99 or SUBB.TL_EXT_ATTR19 <> SUBT.TL_EXT_ATTR19
100 or (SUBB.TL_EXT_ATTR19 is null and SUBT.TL_EXT_ATTR19 is not null)
101 or (SUBB.TL_EXT_ATTR19 is not null and SUBT.TL_EXT_ATTR19 is null)
102 or SUBB.TL_EXT_ATTR20 <> SUBT.TL_EXT_ATTR20
103 or (SUBB.TL_EXT_ATTR20 is null and SUBT.TL_EXT_ATTR20 is not null)
104 or (SUBB.TL_EXT_ATTR20 is not null and SUBT.TL_EXT_ATTR20 is null)
105 or SUBB.TL_EXT_ATTR21 <> SUBT.TL_EXT_ATTR21
106 or (SUBB.TL_EXT_ATTR21 is null and SUBT.TL_EXT_ATTR21 is not null)
107 or (SUBB.TL_EXT_ATTR21 is not null and SUBT.TL_EXT_ATTR21 is null)
108 or SUBB.TL_EXT_ATTR22 <> SUBT.TL_EXT_ATTR22
109 or (SUBB.TL_EXT_ATTR22 is null and SUBT.TL_EXT_ATTR22 is not null)
110 or (SUBB.TL_EXT_ATTR22 is not null and SUBT.TL_EXT_ATTR22 is null)
111 or SUBB.TL_EXT_ATTR23 <> SUBT.TL_EXT_ATTR23
112 or (SUBB.TL_EXT_ATTR23 is null and SUBT.TL_EXT_ATTR23 is not null)
113 or (SUBB.TL_EXT_ATTR23 is not null and SUBT.TL_EXT_ATTR23 is null)
114 or SUBB.TL_EXT_ATTR24 <> SUBT.TL_EXT_ATTR24
115 or (SUBB.TL_EXT_ATTR24 is null and SUBT.TL_EXT_ATTR24 is not null)
116 or (SUBB.TL_EXT_ATTR24 is not null and SUBT.TL_EXT_ATTR24 is null)
117 or SUBB.TL_EXT_ATTR25 <> SUBT.TL_EXT_ATTR25
118 or (SUBB.TL_EXT_ATTR25 is null and SUBT.TL_EXT_ATTR25 is not null)
119 or (SUBB.TL_EXT_ATTR25 is not null and SUBT.TL_EXT_ATTR25 is null)
120 or SUBB.TL_EXT_ATTR26 <> SUBT.TL_EXT_ATTR26
121 or (SUBB.TL_EXT_ATTR26 is null and SUBT.TL_EXT_ATTR26 is not null)
122 or (SUBB.TL_EXT_ATTR26 is not null and SUBT.TL_EXT_ATTR26 is null)
123 or SUBB.TL_EXT_ATTR27 <> SUBT.TL_EXT_ATTR27
124 or (SUBB.TL_EXT_ATTR27 is null and SUBT.TL_EXT_ATTR27 is not null)
125 or (SUBB.TL_EXT_ATTR27 is not null and SUBT.TL_EXT_ATTR27 is null)
126 or SUBB.TL_EXT_ATTR28 <> SUBT.TL_EXT_ATTR28
127 or (SUBB.TL_EXT_ATTR28 is null and SUBT.TL_EXT_ATTR28 is not null)
128 or (SUBB.TL_EXT_ATTR28 is not null and SUBT.TL_EXT_ATTR28 is null)
129 or SUBB.TL_EXT_ATTR29 <> SUBT.TL_EXT_ATTR29
130 or (SUBB.TL_EXT_ATTR29 is null and SUBT.TL_EXT_ATTR29 is not null)
131 or (SUBB.TL_EXT_ATTR29 is not null and SUBT.TL_EXT_ATTR29 is null)
132 or SUBB.TL_EXT_ATTR30 <> SUBT.TL_EXT_ATTR30
133 or (SUBB.TL_EXT_ATTR30 is null and SUBT.TL_EXT_ATTR30 is not null)
134 or (SUBB.TL_EXT_ATTR30 is not null and SUBT.TL_EXT_ATTR30 is null)
135 or SUBB.TL_EXT_ATTR31 <> SUBT.TL_EXT_ATTR31
136 or (SUBB.TL_EXT_ATTR31 is null and SUBT.TL_EXT_ATTR31 is not null)
137 or (SUBB.TL_EXT_ATTR31 is not null and SUBT.TL_EXT_ATTR31 is null)
138 or SUBB.TL_EXT_ATTR32 <> SUBT.TL_EXT_ATTR32
139 or (SUBB.TL_EXT_ATTR32 is null and SUBT.TL_EXT_ATTR32 is not null)
140 or (SUBB.TL_EXT_ATTR32 is not null and SUBT.TL_EXT_ATTR32 is null)
141 or SUBB.TL_EXT_ATTR33 <> SUBT.TL_EXT_ATTR33
142 or (SUBB.TL_EXT_ATTR33 is null and SUBT.TL_EXT_ATTR33 is not null)
143 or (SUBB.TL_EXT_ATTR33 is not null and SUBT.TL_EXT_ATTR33 is null)
144 or SUBB.TL_EXT_ATTR34 <> SUBT.TL_EXT_ATTR34
145 or (SUBB.TL_EXT_ATTR34 is null and SUBT.TL_EXT_ATTR34 is not null)
146 or (SUBB.TL_EXT_ATTR34 is not null and SUBT.TL_EXT_ATTR34 is null)
147 or SUBB.TL_EXT_ATTR35 <> SUBT.TL_EXT_ATTR35
148 or (SUBB.TL_EXT_ATTR35 is null and SUBT.TL_EXT_ATTR35 is not null)
149 or (SUBB.TL_EXT_ATTR35 is not null and SUBT.TL_EXT_ATTR35 is null)
150 or SUBB.TL_EXT_ATTR36 <> SUBT.TL_EXT_ATTR36
151 or (SUBB.TL_EXT_ATTR36 is null and SUBT.TL_EXT_ATTR36 is not null)
152 or (SUBB.TL_EXT_ATTR36 is not null and SUBT.TL_EXT_ATTR36 is null)
153 or SUBB.TL_EXT_ATTR37 <> SUBT.TL_EXT_ATTR37
154 or (SUBB.TL_EXT_ATTR37 is null and SUBT.TL_EXT_ATTR37 is not null)
155 or (SUBB.TL_EXT_ATTR37 is not null and SUBT.TL_EXT_ATTR37 is null)
156 or SUBB.TL_EXT_ATTR38 <> SUBT.TL_EXT_ATTR38
157 or (SUBB.TL_EXT_ATTR38 is null and SUBT.TL_EXT_ATTR38 is not null)
158 or (SUBB.TL_EXT_ATTR38 is not null and SUBT.TL_EXT_ATTR38 is null)
159 or SUBB.TL_EXT_ATTR39 <> SUBT.TL_EXT_ATTR39
160 or (SUBB.TL_EXT_ATTR39 is null and SUBT.TL_EXT_ATTR39 is not null)
161 or (SUBB.TL_EXT_ATTR39 is not null and SUBT.TL_EXT_ATTR39 is null)
162 or SUBB.TL_EXT_ATTR40 <> SUBT.TL_EXT_ATTR40
163 or (SUBB.TL_EXT_ATTR40 is null and SUBT.TL_EXT_ATTR40 is not null)
164 or (SUBB.TL_EXT_ATTR40 is not null and SUBT.TL_EXT_ATTR40 is null)
165 ));
166
167 insert into AMW_WORK_EXT_TL (
168 LANGUAGE,
169 SOURCE_LANG,
170 LAST_UPDATED_BY,
171 LAST_UPDATE_DATE,
172 CREATED_BY,
173 CREATION_DATE,
174 LAST_UPDATE_LOGIN,
175 EXTENSION_ID,
176 WORK_ID,
177 WORK_ELEM_ID,
178 ATTR_GROUP_ID,
179 WORK_TYPE_ID,
180 AUDIT_PROJECT_ID,
181 TL_EXT_ATTR1,TL_EXT_ATTR2,TL_EXT_ATTR3,TL_EXT_ATTR4,TL_EXT_ATTR5,
182 TL_EXT_ATTR6,TL_EXT_ATTR7,TL_EXT_ATTR8,TL_EXT_ATTR9,TL_EXT_ATTR10,
183 TL_EXT_ATTR11,TL_EXT_ATTR12,TL_EXT_ATTR13,TL_EXT_ATTR14,TL_EXT_ATTR15,
184 TL_EXT_ATTR16,TL_EXT_ATTR17,TL_EXT_ATTR18,TL_EXT_ATTR19,TL_EXT_ATTR20,
185 TL_EXT_ATTR21,TL_EXT_ATTR22,TL_EXT_ATTR23,TL_EXT_ATTR24,TL_EXT_ATTR25,
186 TL_EXT_ATTR26,TL_EXT_ATTR27,TL_EXT_ATTR28,TL_EXT_ATTR29,TL_EXT_ATTR30,
187 TL_EXT_ATTR31,TL_EXT_ATTR32,TL_EXT_ATTR33,TL_EXT_ATTR34,TL_EXT_ATTR35,
188 TL_EXT_ATTR36,TL_EXT_ATTR37,TL_EXT_ATTR38,TL_EXT_ATTR39,TL_EXT_ATTR40
189 ) select /*+ ORDERED */
190 L.LANGUAGE_CODE,
191 B.SOURCE_LANG,
192 B.LAST_UPDATED_BY,
193 B.LAST_UPDATE_DATE,
194 B.CREATED_BY,
195 B.CREATION_DATE,
196 B.LAST_UPDATE_LOGIN,
197 B.EXTENSION_ID,
198 B.WORK_ID,
199 B.WORK_ELEM_ID,
200 B.ATTR_GROUP_ID,
201 B.WORK_TYPE_ID,
202 B.AUDIT_PROJECT_ID,
203 B.TL_EXT_ATTR1,B.TL_EXT_ATTR2,B.TL_EXT_ATTR3,B.TL_EXT_ATTR4,B.TL_EXT_ATTR5,
204 B.TL_EXT_ATTR6,B.TL_EXT_ATTR7,B.TL_EXT_ATTR8,B.TL_EXT_ATTR9,B.TL_EXT_ATTR10,
205 B.TL_EXT_ATTR11,B.TL_EXT_ATTR12,B.TL_EXT_ATTR13,B.TL_EXT_ATTR14,B.TL_EXT_ATTR15,
206 B.TL_EXT_ATTR16,B.TL_EXT_ATTR17,B.TL_EXT_ATTR18,B.TL_EXT_ATTR19,B.TL_EXT_ATTR20,
207 B.TL_EXT_ATTR21,B.TL_EXT_ATTR22,B.TL_EXT_ATTR23,B.TL_EXT_ATTR24,B.TL_EXT_ATTR25,
208 B.TL_EXT_ATTR26,B.TL_EXT_ATTR27,B.TL_EXT_ATTR28,B.TL_EXT_ATTR29,B.TL_EXT_ATTR30,
209 B.TL_EXT_ATTR31,B.TL_EXT_ATTR32,B.TL_EXT_ATTR33,B.TL_EXT_ATTR34,B.TL_EXT_ATTR35,
210 B.TL_EXT_ATTR36,B.TL_EXT_ATTR37,B.TL_EXT_ATTR38,B.TL_EXT_ATTR39,B.TL_EXT_ATTR40
211 from AMW_WORK_EXT_TL B, FND_LANGUAGES L
212 where L.INSTALLED_FLAG in ('I', 'B')
213 and B.LANGUAGE = userenv('LANG')
214 and not exists
215 (select NULL
216 from AMW_WORK_EXT_TL T
217 where T.EXTENSION_ID = B.EXTENSION_ID
218 and T.LANGUAGE = L.LANGUAGE_CODE);
219 end ADD_LANGUAGE;
220
221
222 end AMW_WORK_EXT_PKG;