[Home] [Help]
PACKAGE BODY: APPS.GMA_GME_TEXT_TBL_PKG
Source
1 package body GMA_GME_TEXT_TBL_PKG AS
2 /* $Header: GMAGMETB.pls 115.7 2003/02/24 19:20:33 kmoizudd noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_TEXT_CODE in NUMBER,
7 X_LANG_CODE in VARCHAR2,
8 X_PARAGRAPH_CODE in VARCHAR2,
9 X_SUB_PARACODE in NUMBER,
10 X_LINE_NO in NUMBER,
11 X_TEXT in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from GME_TEXT_TABLE_TL
19 where TEXT_CODE = X_TEXT_CODE
20 and LANG_CODE = X_LANG_CODE
21 and PARAGRAPH_CODE = X_PARAGRAPH_CODE
22 and SUB_PARACODE = X_SUB_PARACODE
23 and LINE_NO = X_LINE_NO
24 and LANGUAGE = userenv('LANG')
25 ;
26 begin
27 insert into GME_TEXT_TABLE_TL (
28 TEXT_CODE,
29 LANG_CODE,
30 PARAGRAPH_CODE,
31 SUB_PARACODE,
32 LINE_NO,
33 TEXT,
34 LAST_UPDATED_BY,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 CREATION_DATE,
38 LAST_UPDATE_LOGIN,
39 LANGUAGE,
40 SOURCE_LANG
41 ) select
42 X_TEXT_CODE,
43 X_LANG_CODE,
44 X_PARAGRAPH_CODE,
45 X_SUB_PARACODE,
46 X_LINE_NO,
47 X_TEXT,
48 X_LAST_UPDATED_BY,
49 X_CREATED_BY,
50 X_LAST_UPDATE_DATE,
51 X_CREATION_DATE,
52 X_LAST_UPDATE_LOGIN,
53 L.LANGUAGE_CODE,
54 userenv('LANG')
55 from FND_LANGUAGES L
56 where L.INSTALLED_FLAG in ('I', 'B')
57 and not exists
58 (select NULL
59 from GME_TEXT_TABLE_TL T
60 where T.TEXT_CODE = X_TEXT_CODE
61 and T.LANG_CODE = X_LANG_CODE
62 and T.PARAGRAPH_CODE = X_PARAGRAPH_CODE
63 and T.SUB_PARACODE = X_SUB_PARACODE
64 and T.LINE_NO = X_LINE_NO
65 and T.LANGUAGE = L.LANGUAGE_CODE);
66
67 open c;
68 fetch c into X_ROWID;
69 if (c%notfound) then
70 close c;
71 raise no_data_found;
72 end if;
73 close c;
74
75 end INSERT_ROW;
76
77 procedure LOCK_ROW (
78 X_TEXT_CODE in NUMBER,
79 X_LANG_CODE in VARCHAR2,
80 X_PARAGRAPH_CODE in VARCHAR2,
81 X_SUB_PARACODE in NUMBER,
82 X_LINE_NO in NUMBER,
83 X_TEXT in VARCHAR2
84 ) is
85 cursor c1 is select
86 TEXT,
87 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
88 from GME_TEXT_TABLE_TL
89 where TEXT_CODE = X_TEXT_CODE
90 and LANG_CODE = X_LANG_CODE
91 and PARAGRAPH_CODE = X_PARAGRAPH_CODE
92 and SUB_PARACODE = X_SUB_PARACODE
93 and LINE_NO = X_LINE_NO
94 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
95 for update of TEXT_CODE nowait;
96 begin
97 for tlinfo in c1 loop
98 if (tlinfo.BASELANG = 'Y') then
99 if ( ((tlinfo.TEXT = X_TEXT)
100 OR ((tlinfo.TEXT is null) AND (X_TEXT is null)))
101 ) then
102 null;
103 else
104 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
105 app_exception.raise_exception;
106 end if;
107 end if;
108 end loop;
109 return;
110 end LOCK_ROW;
111
112 procedure UPDATE_ROW (
113 X_ROW_ID in VARCHAR2,
114 X_TEXT_CODE in NUMBER,
115 X_LANG_CODE in VARCHAR2,
116 X_PARAGRAPH_CODE in VARCHAR2,
117 X_SUB_PARACODE in NUMBER,
118 X_LINE_NO in NUMBER,
119 X_TEXT in VARCHAR2,
120 X_LAST_UPDATE_DATE in DATE,
121 X_LAST_UPDATED_BY in NUMBER,
122 X_LAST_UPDATE_LOGIN in NUMBER
123 ) is
124 L_LINE_NO number;
125 begin
126
127 -- added this select to retrieve the old line number with ROWID
128 select LINE_NO INTO L_LINE_NO
129 from GME_TEXT_TABLE_TL
130 where ROWID=X_ROW_ID;
131
132 update GME_TEXT_TABLE_TL set
133 TEXT = X_TEXT,
134 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
135 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
136 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
137 LINE_NO = X_LINE_NO,
138 SOURCE_LANG = userenv('LANG')
139 where TEXT_CODE = X_TEXT_CODE
140 and PARAGRAPH_CODE = X_PARAGRAPH_CODE
141 and SUB_PARACODE = X_SUB_PARACODE
142 and LINE_NO = L_LINE_NO
143 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
144
145 -- where ROWID = X_ROW_ID;
146 -- bug #1712111 (JKB)
147 -- bug #2747352 (kmoizudd) Modified the Where clause without ROW_ID
148
149
150 if (sql%notfound) then
151 raise no_data_found;
152 end if;
153 end UPDATE_ROW;
154
155 procedure DELETE_ROW (
156 X_TEXT_CODE in NUMBER,
157 X_LANG_CODE in VARCHAR2,
158 X_PARAGRAPH_CODE in VARCHAR2,
159 X_SUB_PARACODE in NUMBER,
160 X_LINE_NO in NUMBER,
161 X_ROW_ID in VARCHAR2
162 ) is
163 begin
164 delete from GME_TEXT_TABLE_TL
165 where TEXT_CODE = X_TEXT_CODE
166 and LANG_CODE = X_LANG_CODE
167 and PARAGRAPH_CODE = X_PARAGRAPH_CODE
168 and SUB_PARACODE = X_SUB_PARACODE
169 and LINE_NO = X_LINE_NO;
170
171 -- where ROWID = X_ROW_ID;
172 -- Bug #1775354 (JKB)
173 -- bug #2747352 (kmoizudd) Modified the Where clause without ROW_ID
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178
179 end DELETE_ROW;
180
181 procedure ADD_LANGUAGE
182 is
183 begin
184 update GME_TEXT_TABLE_TL T set (
185 TEXT
186 ) = (select
187 B.TEXT
188 from GME_TEXT_TABLE_TL B
189 where B.TEXT_CODE = T.TEXT_CODE
190 and B.LANG_CODE = T.LANG_CODE
191 and B.PARAGRAPH_CODE = T.PARAGRAPH_CODE
192 and B.SUB_PARACODE = T.SUB_PARACODE
193 and B.LINE_NO = T.LINE_NO
194 and B.LANGUAGE = T.SOURCE_LANG)
195 where (
196 T.TEXT_CODE,
197 T.LANG_CODE,
198 T.PARAGRAPH_CODE,
199 T.SUB_PARACODE,
200 T.LINE_NO,
201 T.LANGUAGE
202 ) in (select
203 SUBT.TEXT_CODE,
204 SUBT.LANG_CODE,
205 SUBT.PARAGRAPH_CODE,
206 SUBT.SUB_PARACODE,
207 SUBT.LINE_NO,
208 SUBT.LANGUAGE
209 from GME_TEXT_TABLE_TL SUBB, GME_TEXT_TABLE_TL SUBT
210 where SUBB.TEXT_CODE = SUBT.TEXT_CODE
211 and SUBB.LANG_CODE = SUBT.LANG_CODE
212 and SUBB.PARAGRAPH_CODE = SUBT.PARAGRAPH_CODE
213 and SUBB.SUB_PARACODE = SUBT.SUB_PARACODE
214 and SUBB.LINE_NO = SUBT.LINE_NO
215 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
216 and (SUBB.TEXT <> SUBT.TEXT
217 or (SUBB.TEXT is null and SUBT.TEXT is not null)
218 or (SUBB.TEXT is not null and SUBT.TEXT is null)
219 ));
220
221 insert into GME_TEXT_TABLE_TL (
222 TEXT_CODE,
223 LANG_CODE,
224 PARAGRAPH_CODE,
225 SUB_PARACODE,
226 LINE_NO,
227 TEXT,
228 LAST_UPDATED_BY,
229 CREATED_BY,
230 LAST_UPDATE_DATE,
231 CREATION_DATE,
232 LAST_UPDATE_LOGIN,
233 LANGUAGE,
234 SOURCE_LANG
235 ) select
236 B.TEXT_CODE,
237 B.LANG_CODE,
238 B.PARAGRAPH_CODE,
239 B.SUB_PARACODE,
240 B.LINE_NO,
241 B.TEXT,
242 B.LAST_UPDATED_BY,
243 B.CREATED_BY,
244 B.LAST_UPDATE_DATE,
245 B.CREATION_DATE,
246 B.LAST_UPDATE_LOGIN,
247 L.LANGUAGE_CODE,
248 B.SOURCE_LANG
249 from GME_TEXT_TABLE_TL B, FND_LANGUAGES L
250 where L.INSTALLED_FLAG in ('I', 'B')
251 and B.LANGUAGE = userenv('LANG')
252 and not exists
253 (select NULL
254 from GME_TEXT_TABLE_TL T
255 where T.TEXT_CODE = B.TEXT_CODE
256 and T.LANG_CODE = B.LANG_CODE
257 and T.PARAGRAPH_CODE = B.PARAGRAPH_CODE
258 and T.SUB_PARACODE = B.SUB_PARACODE
259 and T.LINE_NO = B.LINE_NO
260 and T.LANGUAGE = L.LANGUAGE_CODE);
261 end ADD_LANGUAGE;
262
263 procedure TRANSLATE_ROW (
264 X_TEXT_CODE in NUMBER,
265 X_LANG_CODE in VARCHAR2,
266 X_PARAGRAPH_CODE in VARCHAR2,
267 X_SUB_PARACODE in NUMBER,
268 X_LINE_NO in NUMBER,
269 X_TEXT in VARCHAR2,
270 X_OWNER in VARCHAR2
271 ) IS
272 BEGIN
273 update GME_TEXT_TABLE_TL set
274 TEXT = X_TEXT,
275 SOURCE_LANG = userenv('LANG'),
276 LAST_UPDATE_DATE = sysdate,
277 LAST_UPDATED_BY = decode(X_OWNER,'SEED',1,0),
278 LAST_UPDATE_LOGIN = 0
279 where TEXT_CODE = X_TEXT_CODE
280 and LANG_CODE = X_LANG_CODE
281 and PARAGRAPH_CODE = X_PARAGRAPH_CODE
282 and SUB_PARACODE = X_SUB_PARACODE
283 and LINE_NO = X_LINE_NO
284 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
285 end TRANSLATE_ROW;
286
287 procedure LOAD_ROW (
288 X_TEXT_CODE in NUMBER,
289 X_LANG_CODE in VARCHAR2,
290 X_PARAGRAPH_CODE in VARCHAR2,
291 X_SUB_PARACODE in NUMBER,
292 X_LINE_NO in NUMBER,
293 X_TEXT in VARCHAR2,
294 X_OWNER in VARCHAR2
295 ) IS
296 l_text_code number(10);
297 l_user_id number:=0;
298 l_row_id VARCHAR2(64);
299 BEGIN
300 IF (X_OWNER ='SEED') THEN
301 l_user_id :=1;
302 END IF;
303
304 SELECT text_code, rowid into l_text_code, l_row_id
305 FROM GME_TEXT_TABLE_TL
306 WHERE TEXT_CODE = X_TEXT_CODE
307 and LANG_CODE = X_LANG_CODE
308 and PARAGRAPH_CODE = X_PARAGRAPH_CODE
309 and SUB_PARACODE = X_SUB_PARACODE
310 and LINE_NO = X_LINE_NO;
311
312 GMA_GME_TEXT_TBL_PKG.UPDATE_ROW( X_ROW_ID => l_row_id,
313 X_TEXT_CODE => X_TEXT_CODE,
314 X_LANG_CODE => X_LANG_CODE,
315 X_PARAGRAPH_CODE => X_PARAGRAPH_CODE,
316 X_SUB_PARACODE => X_SUB_PARACODE,
317 X_LINE_NO => X_LINE_NO,
318 X_TEXT => X_TEXT,
319 X_LAST_UPDATE_DATE => sysdate,
320 X_LAST_UPDATED_BY => l_user_id,
321 X_LAST_UPDATE_LOGIN => 0
322 );
323 -- Bug #1712111 (JKB)
324
325
326
327
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330
331
332 GMA_GME_TEXT_TBL_PKG.INSERT_ROW ( X_ROWID => l_row_id,
333 X_TEXT_CODE => X_TEXT_CODE,
334 X_LANG_CODE => X_LANG_CODE,
335 X_PARAGRAPH_CODE => X_PARAGRAPH_CODE,
336 X_SUB_PARACODE => X_SUB_PARACODE,
337 X_LINE_NO => X_LINE_NO,
338 X_TEXT => X_TEXT,
339 X_CREATION_DATE => sysdate,
340 X_CREATED_BY => l_user_id,
341 X_LAST_UPDATE_DATE => sysdate,
342 X_LAST_UPDATED_BY => l_user_id,
343 X_LAST_UPDATE_LOGIN => 0
344 );
345
346 END LOAD_ROW;
347
348 end GMA_GME_TEXT_TBL_PKG;