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