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