[Home] [Help]
PACKAGE BODY: APPS.CSD_FLOW_STATUSES_PKG
Source
1 package body CSD_FLOW_STATUSES_PKG as
2 /* $Header: csdtflsb.pls 120.0 2005/06/14 10:16:01 vkjain noship $ */
3
4 procedure INSERT_ROW (
5 -- P_ROWID in out nocopy VARCHAR2,
6 PX_FLOW_STATUS_ID in out nocopy NUMBER,
7 P_FLOW_STATUS_CODE in VARCHAR2,
8 P_STATUS_CODE in VARCHAR2,
9 P_SEEDED_FLAG in VARCHAR2,
10 P_OBJECT_VERSION_NUMBER in NUMBER,
11 P_EXTERNAL_DISPLAY_STATUS in VARCHAR2,
12 P_ATTRIBUTE_CATEGORY in VARCHAR2,
13 P_ATTRIBUTE1 in VARCHAR2,
14 P_ATTRIBUTE2 in VARCHAR2,
15 P_ATTRIBUTE3 in VARCHAR2,
16 P_ATTRIBUTE4 in VARCHAR2,
17 P_ATTRIBUTE5 in VARCHAR2,
18 P_ATTRIBUTE6 in VARCHAR2,
19 P_ATTRIBUTE7 in VARCHAR2,
20 P_ATTRIBUTE8 in VARCHAR2,
21 P_ATTRIBUTE9 in VARCHAR2,
22 P_ATTRIBUTE10 in VARCHAR2,
23 P_ATTRIBUTE11 in VARCHAR2,
24 P_ATTRIBUTE12 in VARCHAR2,
25 P_ATTRIBUTE13 in VARCHAR2,
26 P_ATTRIBUTE14 in VARCHAR2,
27 P_ATTRIBUTE15 in VARCHAR2,
28 P_CREATION_DATE in DATE,
29 P_CREATED_BY in NUMBER,
30 P_LAST_UPDATE_DATE in DATE,
31 P_LAST_UPDATED_BY in NUMBER,
32 P_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34
35 cursor C is
36 select ROWID
37 from CSD_FLOW_STATUSES_B
38 where FLOW_STATUS_ID = PX_FLOW_STATUS_ID;
39
40 P_ROWID ROWID;
41
42 begin
43
44 select CSD_FLOW_STATUSES_S1.nextval
45 into PX_FLOW_STATUS_ID
46 from dual;
47
48 insert into CSD_FLOW_STATUSES_B (
49 ATTRIBUTE10,
50 ATTRIBUTE11,
51 ATTRIBUTE12,
52 ATTRIBUTE13,
53 ATTRIBUTE14,
54 ATTRIBUTE15,
55 FLOW_STATUS_ID,
56 FLOW_STATUS_CODE,
57 STATUS_CODE,
58 SEEDED_FLAG,
59 ATTRIBUTE8,
60 ATTRIBUTE9,
61 ATTRIBUTE7,
62 ATTRIBUTE_CATEGORY,
63 ATTRIBUTE1,
64 ATTRIBUTE2,
65 ATTRIBUTE3,
66 ATTRIBUTE4,
67 ATTRIBUTE5,
68 ATTRIBUTE6,
69 OBJECT_VERSION_NUMBER,
70 CREATION_DATE,
71 CREATED_BY,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 LAST_UPDATE_LOGIN
75 ) values (
76 P_ATTRIBUTE10,
77 P_ATTRIBUTE11,
78 P_ATTRIBUTE12,
79 P_ATTRIBUTE13,
80 P_ATTRIBUTE14,
81 P_ATTRIBUTE15,
82 PX_FLOW_STATUS_ID,
83 P_FLOW_STATUS_CODE,
84 P_STATUS_CODE,
85 P_SEEDED_FLAG,
86 P_ATTRIBUTE8,
87 P_ATTRIBUTE9,
88 P_ATTRIBUTE7,
89 P_ATTRIBUTE_CATEGORY,
90 P_ATTRIBUTE1,
91 P_ATTRIBUTE2,
92 P_ATTRIBUTE3,
93 P_ATTRIBUTE4,
94 P_ATTRIBUTE5,
95 P_ATTRIBUTE6,
96 P_OBJECT_VERSION_NUMBER,
97 P_CREATION_DATE,
98 P_CREATED_BY,
99 P_LAST_UPDATE_DATE,
100 P_LAST_UPDATED_BY,
101 P_LAST_UPDATE_LOGIN
102 );
103
104 insert into CSD_FLOW_STATUSES_TL (
105 FLOW_STATUS_ID,
106 EXTERNAL_DISPLAY_STATUS,
107 CREATED_BY,
108 CREATION_DATE,
109 LAST_UPDATED_BY,
110 LAST_UPDATE_DATE,
111 LAST_UPDATE_LOGIN,
112 LANGUAGE,
113 SOURCE_LANG
114 ) select
115 PX_FLOW_STATUS_ID,
116 P_EXTERNAL_DISPLAY_STATUS,
117 P_CREATED_BY,
118 P_CREATION_DATE,
119 P_LAST_UPDATED_BY,
120 P_LAST_UPDATE_DATE,
121 P_LAST_UPDATE_LOGIN,
122 L.LANGUAGE_CODE,
123 userenv('LANG')
124 from FND_LANGUAGES L
125 where L.INSTALLED_FLAG in ('I', 'B')
126 and not exists
127 (select NULL
128 from CSD_FLOW_STATUSES_TL T
129 where T.FLOW_STATUS_ID = PX_FLOW_STATUS_ID
130 and T.LANGUAGE = L.LANGUAGE_CODE);
131
132 open c;
133 fetch c into P_ROWID;
134 if (c%notfound) then
135 close c;
136 raise no_data_found;
137 end if;
138 close c;
139
140 end INSERT_ROW;
141
142 procedure LOCK_ROW (
143 P_FLOW_STATUS_ID in NUMBER,
144 P_OBJECT_VERSION_NUMBER in NUMBER
145
146 /*
147 P_ATTRIBUTE10 in VARCHAR2,
148 P_ATTRIBUTE11 in VARCHAR2,
149 P_ATTRIBUTE12 in VARCHAR2,
150 P_ATTRIBUTE13 in VARCHAR2,
151 P_ATTRIBUTE14 in VARCHAR2,
152 P_ATTRIBUTE15 in VARCHAR2,
153 P_FLOW_STATUS_CODE in VARCHAR2,
154 P_STATUS_CODE in VARCHAR2,
155 P_SEEDED_FLAG in VARCHAR2,
156 P_ATTRIBUTE8 in VARCHAR2,
157 P_ATTRIBUTE9 in VARCHAR2,
158 P_ATTRIBUTE7 in VARCHAR2,
159 P_ATTRIBUTE_CATEGORY in VARCHAR2,
160 P_ATTRIBUTE1 in VARCHAR2,
161 P_ATTRIBUTE2 in VARCHAR2,
162 P_ATTRIBUTE3 in VARCHAR2,
163 P_ATTRIBUTE4 in VARCHAR2,
164 P_ATTRIBUTE5 in VARCHAR2,
165 P_ATTRIBUTE6 in VARCHAR2,
166 P_OBJECT_VERSION_NUMBER in NUMBER,
167 P_EXTERNAL_DISPLAY_STATUS in VARCHAR2
168 */
169 ) is
170 cursor c is select
171 OBJECT_VERSION_NUMBER
172 from CSD_FLOW_STATUSES_B
173 where FLOW_STATUS_ID = P_FLOW_STATUS_ID
174 for update of FLOW_STATUS_ID nowait;
175 recinfo c%rowtype;
176
177 begin
178
179 open c;
180 fetch c into recinfo;
181 if (c%notfound) then
182 close c;
183 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
184 app_exception.raise_exception;
185 end if;
186 close c;
187
188 if(recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER) then
189 null;
190 else
191 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
192 app_exception.raise_exception;
193 end if;
194
195 /*
196 for tlinfo in c1 loop
197 if (tlinfo.BASELANG = 'Y') then
198 if ( ((tlinfo.EXTERNAL_DISPLAY_STATUS = P_EXTERNAL_DISPLAY_STATUS)
199 OR ((tlinfo.EXTERNAL_DISPLAY_STATUS is null) AND (P_EXTERNAL_DISPLAY_STATUS is null)))
200 ) then
201 null;
202 else
203 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
204 app_exception.raise_exception;
205 end if;
206 end if;
207 end loop;
208 */
209
210 return;
211 end LOCK_ROW;
212
213 procedure UPDATE_ROW (
214 P_FLOW_STATUS_ID in NUMBER,
215 P_FLOW_STATUS_CODE in VARCHAR2,
216 P_STATUS_CODE in VARCHAR2,
217 P_SEEDED_FLAG in VARCHAR2,
218 P_OBJECT_VERSION_NUMBER in NUMBER,
219 P_EXTERNAL_DISPLAY_STATUS in VARCHAR2,
220 P_ATTRIBUTE_CATEGORY in VARCHAR2,
221 P_ATTRIBUTE1 in VARCHAR2,
222 P_ATTRIBUTE2 in VARCHAR2,
223 P_ATTRIBUTE3 in VARCHAR2,
224 P_ATTRIBUTE4 in VARCHAR2,
225 P_ATTRIBUTE5 in VARCHAR2,
226 P_ATTRIBUTE6 in VARCHAR2,
227 P_ATTRIBUTE7 in VARCHAR2,
228 P_ATTRIBUTE8 in VARCHAR2,
229 P_ATTRIBUTE9 in VARCHAR2,
230 P_ATTRIBUTE10 in VARCHAR2,
231 P_ATTRIBUTE11 in VARCHAR2,
232 P_ATTRIBUTE12 in VARCHAR2,
233 P_ATTRIBUTE13 in VARCHAR2,
234 P_ATTRIBUTE14 in VARCHAR2,
235 P_ATTRIBUTE15 in VARCHAR2,
236 P_LAST_UPDATE_DATE in DATE,
237 P_LAST_UPDATED_BY in NUMBER,
238 P_LAST_UPDATE_LOGIN in NUMBER
239 ) is
240 begin
241 update CSD_FLOW_STATUSES_B set
242 ATTRIBUTE10 = P_ATTRIBUTE10,
243 ATTRIBUTE11 = P_ATTRIBUTE11,
244 ATTRIBUTE12 = P_ATTRIBUTE12,
245 ATTRIBUTE13 = P_ATTRIBUTE13,
246 ATTRIBUTE14 = P_ATTRIBUTE14,
247 ATTRIBUTE15 = P_ATTRIBUTE15,
248 FLOW_STATUS_CODE = P_FLOW_STATUS_CODE,
249 STATUS_CODE = P_STATUS_CODE,
250 SEEDED_FLAG = P_SEEDED_FLAG,
251 ATTRIBUTE8 = P_ATTRIBUTE8,
252 ATTRIBUTE9 = P_ATTRIBUTE9,
253 ATTRIBUTE7 = P_ATTRIBUTE7,
254 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
255 ATTRIBUTE1 = P_ATTRIBUTE1,
256 ATTRIBUTE2 = P_ATTRIBUTE2,
257 ATTRIBUTE3 = P_ATTRIBUTE3,
258 ATTRIBUTE4 = P_ATTRIBUTE4,
259 ATTRIBUTE5 = P_ATTRIBUTE5,
260 ATTRIBUTE6 = P_ATTRIBUTE6,
261 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER + 1,
262 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
263 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
264 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
265 where FLOW_STATUS_ID = P_FLOW_STATUS_ID AND
266 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER;
267
268 if (sql%notfound) then
269 raise no_data_found;
270 end if;
271
272 update CSD_FLOW_STATUSES_TL set
273 EXTERNAL_DISPLAY_STATUS = P_EXTERNAL_DISPLAY_STATUS,
274 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
275 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
276 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
277 SOURCE_LANG = userenv('LANG')
278 where FLOW_STATUS_ID = P_FLOW_STATUS_ID
279 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
280
281 if (sql%notfound) then
282 raise no_data_found;
283 end if;
284 end UPDATE_ROW;
285
286 procedure DELETE_ROW (
287 P_FLOW_STATUS_ID in NUMBER
288 ) is
289 begin
290 delete from CSD_FLOW_STATUSES_TL
291 where FLOW_STATUS_ID = P_FLOW_STATUS_ID;
292
293 if (sql%notfound) then
294 raise no_data_found;
295 end if;
296
297 delete from CSD_FLOW_STATUSES_B
298 where FLOW_STATUS_ID = P_FLOW_STATUS_ID;
299
300 if (sql%notfound) then
301 raise no_data_found;
302 end if;
303 end DELETE_ROW;
304
305 procedure ADD_LANGUAGE
306 is
307 begin
308 delete from CSD_FLOW_STATUSES_TL T
309 where not exists
310 (select NULL
311 from CSD_FLOW_STATUSES_B B
312 where B.FLOW_STATUS_ID = T.FLOW_STATUS_ID
313 );
314
315 update CSD_FLOW_STATUSES_TL T set (
316 EXTERNAL_DISPLAY_STATUS
317 ) = (select
318 B.EXTERNAL_DISPLAY_STATUS
319 from CSD_FLOW_STATUSES_TL B
320 where B.FLOW_STATUS_ID = T.FLOW_STATUS_ID
321 and B.LANGUAGE = T.SOURCE_LANG)
322 where (
323 T.FLOW_STATUS_ID,
324 T.LANGUAGE
325 ) in (select
326 SUBT.FLOW_STATUS_ID,
327 SUBT.LANGUAGE
328 from CSD_FLOW_STATUSES_TL SUBB, CSD_FLOW_STATUSES_TL SUBT
329 where SUBB.FLOW_STATUS_ID = SUBT.FLOW_STATUS_ID
330 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
331 and (SUBB.EXTERNAL_DISPLAY_STATUS <> SUBT.EXTERNAL_DISPLAY_STATUS
332 or (SUBB.EXTERNAL_DISPLAY_STATUS is null and SUBT.EXTERNAL_DISPLAY_STATUS is not null)
333 or (SUBB.EXTERNAL_DISPLAY_STATUS is not null and SUBT.EXTERNAL_DISPLAY_STATUS is null)
334 ));
335
336 insert into CSD_FLOW_STATUSES_TL (
337 FLOW_STATUS_ID,
338 EXTERNAL_DISPLAY_STATUS,
339 CREATED_BY,
340 CREATION_DATE,
341 LAST_UPDATED_BY,
342 LAST_UPDATE_DATE,
343 LAST_UPDATE_LOGIN,
344 LANGUAGE,
345 SOURCE_LANG
346 ) select /*+ ORDERED */
347 B.FLOW_STATUS_ID,
348 B.EXTERNAL_DISPLAY_STATUS,
349 B.CREATED_BY,
350 B.CREATION_DATE,
351 B.LAST_UPDATED_BY,
352 B.LAST_UPDATE_DATE,
353 B.LAST_UPDATE_LOGIN,
354 L.LANGUAGE_CODE,
355 B.SOURCE_LANG
356 from CSD_FLOW_STATUSES_TL B, FND_LANGUAGES L
357 where L.INSTALLED_FLAG in ('I', 'B')
358 and B.LANGUAGE = userenv('LANG')
359 and not exists
360 (select NULL
361 from CSD_FLOW_STATUSES_TL T
362 where T.FLOW_STATUS_ID = B.FLOW_STATUS_ID
363 and T.LANGUAGE = L.LANGUAGE_CODE);
364 end ADD_LANGUAGE;
365
366 end CSD_FLOW_STATUSES_PKG;