1 package body IEC_G_REP_COLS_PKG as
2 /* $Header: IECREPCB.pls 115.12 2003/08/22 20:42:25 hhuang ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID out nocopy VARCHAR2,
6 X_COLUMN_ID in NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_VALUE in VARCHAR2,
9 X_NAME in VARCHAR2,
10 X_INTERNAL_COLUMN_NAME in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from IEC_G_REP_COLS_B
18 where COLUMN_ID = X_COLUMN_ID
19 ;
20 begin
21 x_rowid := NULL;
22
23 insert into IEC_G_REP_COLS_B (
24 COLUMN_ID,
25 OBJECT_VERSION_NUMBER,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_COLUMN_ID,
33 X_OBJECT_VERSION_NUMBER,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_LAST_UPDATE_LOGIN
39 );
40
41 insert into IEC_G_REP_COLS_TL (
42 VALUE,
43 NAME,
44 INTERNAL_COLUMN_NAME,
45 COLUMN_ID,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN,
51 OBJECT_VERSION_NUMBER,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_VALUE,
56 X_NAME,
57 X_INTERNAL_COLUMN_NAME,
58 X_COLUMN_ID,
59 X_CREATED_BY,
60 X_CREATION_DATE,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATED_BY,
63 X_LAST_UPDATE_LOGIN,
64 X_OBJECT_VERSION_NUMBER,
65 L.LANGUAGE_CODE,
66 userenv('LANG')
67 from FND_LANGUAGES L
68 where L.INSTALLED_FLAG in ('I', 'B')
69 and not exists
70 (select NULL
71 from IEC_G_REP_COLS_TL T
72 where T.COLUMN_ID = X_COLUMN_ID
73 and T.LANGUAGE = L.LANGUAGE_CODE);
74
75 open c;
76 fetch c into X_ROWID;
77 if (c%notfound) then
78 close c;
79 raise no_data_found;
80 end if;
81 close c;
82
83 end INSERT_ROW;
84
85 procedure LOCK_ROW (
86 X_COLUMN_ID in NUMBER,
87 X_OBJECT_VERSION_NUMBER in NUMBER,
88 X_VALUE in VARCHAR2,
89 X_NAME in VARCHAR2,
90 X_INTERNAL_COLUMN_NAME in VARCHAR2
91 ) is
92 cursor c is select
93 OBJECT_VERSION_NUMBER
94 from IEC_G_REP_COLS_B
95 where COLUMN_ID = X_COLUMN_ID
96 for update of COLUMN_ID nowait;
97 recinfo c%rowtype;
98
99 cursor c1 is select
100 VALUE,
101 NAME,
102 INTERNAL_COLUMN_NAME,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from IEC_G_REP_COLS_TL
105 where COLUMN_ID = X_COLUMN_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of COLUMN_ID nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
118 null;
119 else
120 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
121 app_exception.raise_exception;
122 end if;
123
124 for tlinfo in c1 loop
125 if (tlinfo.BASELANG = 'Y') then
126 if ( ((tlinfo.VALUE = X_VALUE)
127 OR ((tlinfo.VALUE is null) AND (X_VALUE is null)))
128 AND ((tlinfo.NAME = X_NAME)
129 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
130 AND (tlinfo.INTERNAL_COLUMN_NAME = X_INTERNAL_COLUMN_NAME)
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137 end if;
138 end loop;
139 return;
140 end LOCK_ROW;
141
142 procedure UPDATE_ROW (
143 X_COLUMN_ID in NUMBER,
144 X_OBJECT_VERSION_NUMBER in NUMBER,
145 X_VALUE in VARCHAR2,
146 X_NAME in VARCHAR2,
147 X_INTERNAL_COLUMN_NAME in VARCHAR2,
148 X_LAST_UPDATE_DATE in DATE,
149 X_LAST_UPDATED_BY in NUMBER,
150 X_LAST_UPDATE_LOGIN in NUMBER
151 ) is
152 begin
153 update IEC_G_REP_COLS_B set
154 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
155 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
156 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
157 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
158 where COLUMN_ID = X_COLUMN_ID;
159
160 if (sql%notfound) then
161 raise no_data_found;
162 end if;
163
164 update IEC_G_REP_COLS_TL set
165 VALUE = X_VALUE,
166 NAME = X_NAME,
167 INTERNAL_COLUMN_NAME = X_INTERNAL_COLUMN_NAME,
168 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
169 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
171 SOURCE_LANG = userenv('LANG')
172 where COLUMN_ID = X_COLUMN_ID
173 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178 end UPDATE_ROW;
179
180 procedure DELETE_ROW (
181 X_COLUMN_ID in NUMBER
182 ) is
183 begin
184 delete from IEC_G_REP_COLS_TL
185 where COLUMN_ID = X_COLUMN_ID;
186
187 if (sql%notfound) then
188 raise no_data_found;
189 end if;
190
191 delete from IEC_G_REP_COLS_B
192 where COLUMN_ID = X_COLUMN_ID;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end DELETE_ROW;
198
199 procedure ADD_LANGUAGE
200 is
201 begin
202 delete from IEC_G_REP_COLS_TL T
203 where not exists
204 (select NULL
205 from IEC_G_REP_COLS_B B
206 where B.COLUMN_ID = T.COLUMN_ID
207 );
208
209 update IEC_G_REP_COLS_TL T set (
210 VALUE,
211 NAME,
212 INTERNAL_COLUMN_NAME
213 ) = (select
214 B.VALUE,
215 B.NAME,
216 B.INTERNAL_COLUMN_NAME
217 from IEC_G_REP_COLS_TL B
218 where B.COLUMN_ID = T.COLUMN_ID
219 and B.LANGUAGE = T.SOURCE_LANG)
220 where (
221 T.COLUMN_ID,
222 T.LANGUAGE
223 ) in (select
224 SUBT.COLUMN_ID,
225 SUBT.LANGUAGE
226 from IEC_G_REP_COLS_TL SUBB, IEC_G_REP_COLS_TL SUBT
227 where SUBB.COLUMN_ID = SUBT.COLUMN_ID
228 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
229 and (SUBB.VALUE <> SUBT.VALUE
230 or (SUBB.VALUE is null and SUBT.VALUE is not null)
231 or (SUBB.VALUE is not null and SUBT.VALUE is null)
232 or SUBB.NAME <> SUBT.NAME
233 or (SUBB.NAME is null and SUBT.NAME is not null)
234 or (SUBB.NAME is not null and SUBT.NAME is null)
235 or SUBB.INTERNAL_COLUMN_NAME <> SUBT.INTERNAL_COLUMN_NAME
236 ));
237
238 insert into IEC_G_REP_COLS_TL (
239 VALUE,
240 NAME,
241 INTERNAL_COLUMN_NAME,
242 COLUMN_ID,
243 CREATED_BY,
244 CREATION_DATE,
245 LAST_UPDATE_DATE,
246 LAST_UPDATED_BY,
247 LAST_UPDATE_LOGIN,
248 OBJECT_VERSION_NUMBER,
249 LANGUAGE,
250 SOURCE_LANG
251 ) select /*+ ORDERED */
252 B.VALUE,
253 B.NAME,
254 B.INTERNAL_COLUMN_NAME,
255 B.COLUMN_ID,
256 B.CREATED_BY,
257 B.CREATION_DATE,
258 B.LAST_UPDATE_DATE,
259 B.LAST_UPDATED_BY,
260 B.LAST_UPDATE_LOGIN,
261 B.OBJECT_VERSION_NUMBER,
262 L.LANGUAGE_CODE,
263 B.SOURCE_LANG
264 from IEC_G_REP_COLS_TL B, FND_LANGUAGES L
265 where L.INSTALLED_FLAG in ('I', 'B')
266 and B.LANGUAGE = userenv('LANG')
267 and not exists
268 (select NULL
269 from IEC_G_REP_COLS_TL T
270 where T.COLUMN_ID = B.COLUMN_ID
271 and T.LANGUAGE = L.LANGUAGE_CODE);
272 end ADD_LANGUAGE;
273
274 procedure LOAD_ROW (
275 X_COLUMN_ID in NUMBER,
276 X_VALUE in VARCHAR2,
277 X_NAME in VARCHAR2,
278 X_INTERNAL_COLUMN_NAME in VARCHAR2,
279 X_OWNER in VARCHAR2
280 ) is
281
282 USER_ID NUMBER := 0;
283 ROW_ID VARCHAR2(500);
284 begin
285
286 if (X_OWNER = 'SEED') then
287 USER_ID := 1;
288 end if;
289
290 UPDATE_ROW (X_COLUMN_ID, 0, X_VALUE, X_NAME, X_INTERNAL_COLUMN_NAME, SYSDATE, USER_ID, 0);
291
292 exception
293 when no_data_found then
294 INSERT_ROW (ROW_ID, X_COLUMN_ID, 0, X_VALUE, X_NAME, X_INTERNAL_COLUMN_NAME, SYSDATE, USER_ID, SYSDATE, USER_ID, 0);
295
296 end LOAD_ROW;
297
298 procedure TRANSLATE_ROW (
299 X_COLUMN_ID in NUMBER,
300 X_VALUE in VARCHAR2,
301 X_NAME in VARCHAR2,
302 X_INTERNAL_COLUMN_NAME in VARCHAR2,
303 X_OWNER in VARCHAR2
304 ) is
305 begin
306
307 -- only UPDATE rows that have not been altered by user
308
309 update IEC_G_REP_COLS_TL set
310 VALUE = X_VALUE,
311 SOURCE_LANG = userenv('LANG'),
312 NAME = X_NAME,
313 INTERNAL_COLUMN_NAME = X_INTERNAL_COLUMN_NAME,
314 LAST_UPDATE_DATE = SYSDATE,
315 LAST_UPDATED_BY = DECODE(X_OWNER, 'SEED', 1, 0),
316 LAST_UPDATE_LOGIN = 0
317 where COLUMN_ID = X_COLUMN_ID
318 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
319
320 end TRANSLATE_ROW;
321
322 end IEC_G_REP_COLS_PKG;
[Home] [Help]