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