1 PACKAGE BODY AHL_VWP_STAGES_PKG as
2 /* $Header: AHLLSTGB.pls 120.0 2005/05/26 01:45:29 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_STAGE_ID in NUMBER,
6 X_ATTRIBUTE3 in VARCHAR2,
7 X_ATTRIBUTE4 in VARCHAR2,
8 X_ATTRIBUTE5 in VARCHAR2,
9 X_ATTRIBUTE6 in VARCHAR2,
10 X_ATTRIBUTE7 in VARCHAR2,
11 X_ATTRIBUTE8 in VARCHAR2,
12 X_ATTRIBUTE9 in VARCHAR2,
13 X_ATTRIBUTE10 in VARCHAR2,
14 X_ATTRIBUTE11 in VARCHAR2,
15 X_ATTRIBUTE12 in VARCHAR2,
16 X_ATTRIBUTE13 in VARCHAR2,
17 X_ATTRIBUTE14 in VARCHAR2,
18 X_ATTRIBUTE15 in VARCHAR2,
19 X_STAGE_NUM in NUMBER,
20 X_VISIT_ID in NUMBER,
21 X_DURATION in NUMBER,
22 X_OBJECT_VERSION_NUMBER in NUMBER,
23 X_ATTRIBUTE_CATEGORY in VARCHAR2,
24 X_ATTRIBUTE1 in VARCHAR2,
25 X_ATTRIBUTE2 in VARCHAR2,
26 X_STAGE_NAME in VARCHAR2,
27 X_CREATION_DATE in DATE,
28 X_CREATED_BY in NUMBER,
29 X_LAST_UPDATE_DATE in DATE,
30 X_LAST_UPDATED_BY in NUMBER,
31 X_LAST_UPDATE_LOGIN in NUMBER
32 ) is
33 cursor C is select ROWID from AHL_VWP_STAGES_B
34 where STAGE_ID = X_STAGE_ID
35 ;
36 begin
37 insert into AHL_VWP_STAGES_B (
38 ATTRIBUTE3,
39 ATTRIBUTE4,
40 ATTRIBUTE5,
41 ATTRIBUTE6,
42 ATTRIBUTE7,
43 ATTRIBUTE8,
44 ATTRIBUTE9,
45 ATTRIBUTE10,
46 ATTRIBUTE11,
47 ATTRIBUTE12,
48 ATTRIBUTE13,
49 ATTRIBUTE14,
50 ATTRIBUTE15,
51 STAGE_ID,
52 STAGE_NUM,
53 VISIT_ID,
54 DURATION,
55 OBJECT_VERSION_NUMBER,
56 ATTRIBUTE_CATEGORY,
57 ATTRIBUTE1,
58 ATTRIBUTE2,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN
64 ) values (
65 X_ATTRIBUTE3,
66 X_ATTRIBUTE4,
67 X_ATTRIBUTE5,
68 X_ATTRIBUTE6,
69 X_ATTRIBUTE7,
70 X_ATTRIBUTE8,
71 X_ATTRIBUTE9,
72 X_ATTRIBUTE10,
73 X_ATTRIBUTE11,
74 X_ATTRIBUTE12,
75 X_ATTRIBUTE13,
76 X_ATTRIBUTE14,
77 X_ATTRIBUTE15,
78 X_STAGE_ID,
79 X_STAGE_NUM,
80 X_VISIT_ID,
81 X_DURATION,
82 X_OBJECT_VERSION_NUMBER,
83 X_ATTRIBUTE_CATEGORY,
84 X_ATTRIBUTE1,
85 X_ATTRIBUTE2,
86 X_CREATION_DATE,
87 X_CREATED_BY,
88 X_LAST_UPDATE_DATE,
89 X_LAST_UPDATED_BY,
90 X_LAST_UPDATE_LOGIN
91 );
92
93 insert into AHL_VWP_STAGES_TL (
94 STAGE_ID,
95 LAST_UPDATE_DATE,
96 LAST_UPDATED_BY,
97 CREATION_DATE,
98 CREATED_BY,
99 LAST_UPDATE_LOGIN,
100 STAGE_NAME,
101 LANGUAGE,
102 SOURCE_LANG
103 ) select
104 X_STAGE_ID,
105 X_LAST_UPDATE_DATE,
106 X_LAST_UPDATED_BY,
107 X_CREATION_DATE,
108 X_CREATED_BY,
109 X_LAST_UPDATE_LOGIN,
110 X_STAGE_NAME,
111 L.LANGUAGE_CODE,
112 userenv('LANG')
113 from FND_LANGUAGES L
114 where L.INSTALLED_FLAG in ('I', 'B')
115 and not exists
116 (select NULL
117 from AHL_VWP_STAGES_TL T
118 where T.STAGE_ID = X_STAGE_ID
119 and T.LANGUAGE = L.LANGUAGE_CODE);
120
121 open c;
122 fetch c into X_ROWID;
123 if (c%notfound) then
124 close c;
125 raise no_data_found;
126 end if;
127 close c;
128
129 end INSERT_ROW;
130
131 procedure LOCK_ROW (
132 X_STAGE_ID in NUMBER,
133 X_ATTRIBUTE3 in VARCHAR2,
134 X_ATTRIBUTE4 in VARCHAR2,
135 X_ATTRIBUTE5 in VARCHAR2,
136 X_ATTRIBUTE6 in VARCHAR2,
137 X_ATTRIBUTE7 in VARCHAR2,
138 X_ATTRIBUTE8 in VARCHAR2,
139 X_ATTRIBUTE9 in VARCHAR2,
140 X_ATTRIBUTE10 in VARCHAR2,
141 X_ATTRIBUTE11 in VARCHAR2,
142 X_ATTRIBUTE12 in VARCHAR2,
143 X_ATTRIBUTE13 in VARCHAR2,
144 X_ATTRIBUTE14 in VARCHAR2,
145 X_ATTRIBUTE15 in VARCHAR2,
146 X_STAGE_NUM in NUMBER,
147 X_VISIT_ID in NUMBER,
148 X_DURATION in NUMBER,
149 X_OBJECT_VERSION_NUMBER in NUMBER,
150 X_ATTRIBUTE_CATEGORY in VARCHAR2,
151 X_ATTRIBUTE1 in VARCHAR2,
152 X_ATTRIBUTE2 in VARCHAR2,
153 X_STAGE_NAME in VARCHAR2
154 ) is
155 cursor c is select
156 ATTRIBUTE3,
157 ATTRIBUTE4,
158 ATTRIBUTE5,
159 ATTRIBUTE6,
160 ATTRIBUTE7,
161 ATTRIBUTE8,
162 ATTRIBUTE9,
163 ATTRIBUTE10,
164 ATTRIBUTE11,
165 ATTRIBUTE12,
166 ATTRIBUTE13,
167 ATTRIBUTE14,
168 ATTRIBUTE15,
169 STAGE_NUM,
170 VISIT_ID,
171 DURATION,
172 OBJECT_VERSION_NUMBER,
173 ATTRIBUTE_CATEGORY,
174 ATTRIBUTE1,
175 ATTRIBUTE2
176 from AHL_VWP_STAGES_B
177 where STAGE_ID = X_STAGE_ID
178 for update of STAGE_ID nowait;
179 recinfo c%rowtype;
180
181 cursor c1 is select
182 STAGE_NAME,
183 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
184 from AHL_VWP_STAGES_TL
185 where STAGE_ID = X_STAGE_ID
186 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
187 for update of STAGE_ID nowait;
188 begin
189 open c;
190 fetch c into recinfo;
191 if (c%notfound) then
192 close c;
193 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
194 app_exception.raise_exception;
195 end if;
196 close c;
197 if ( ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
198 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
199 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
200 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
201 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
202 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
203 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
204 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
205 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
206 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
207 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
208 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
209 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
210 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
211 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
212 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
213 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
214 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
215 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
216 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
217 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
218 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
219 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
220 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
221 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
222 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
223 AND (recinfo.STAGE_NUM = X_STAGE_NUM)
224 AND (recinfo.VISIT_ID = X_VISIT_ID)
225 AND ((recinfo.DURATION = X_DURATION)
226 OR ((recinfo.DURATION is null) AND (X_DURATION is null)))
227 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
228 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
229 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
230 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
231 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
232 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
233 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
234 ) then
235 null;
236 else
237 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
238 app_exception.raise_exception;
239 end if;
240
241 for tlinfo in c1 loop
242 if (tlinfo.BASELANG = 'Y') then
243 if ( ((tlinfo.STAGE_NAME = X_STAGE_NAME)
244 OR ((tlinfo.STAGE_NAME is null) AND (X_STAGE_NAME is null)))
245 ) then
246 null;
247 else
248 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
249 app_exception.raise_exception;
250 end if;
251 end if;
252 end loop;
253 return;
254 end LOCK_ROW;
255
256 procedure UPDATE_ROW (
257 X_STAGE_ID in NUMBER,
258 X_ATTRIBUTE3 in VARCHAR2,
259 X_ATTRIBUTE4 in VARCHAR2,
260 X_ATTRIBUTE5 in VARCHAR2,
261 X_ATTRIBUTE6 in VARCHAR2,
262 X_ATTRIBUTE7 in VARCHAR2,
263 X_ATTRIBUTE8 in VARCHAR2,
264 X_ATTRIBUTE9 in VARCHAR2,
265 X_ATTRIBUTE10 in VARCHAR2,
266 X_ATTRIBUTE11 in VARCHAR2,
267 X_ATTRIBUTE12 in VARCHAR2,
268 X_ATTRIBUTE13 in VARCHAR2,
269 X_ATTRIBUTE14 in VARCHAR2,
270 X_ATTRIBUTE15 in VARCHAR2,
271 X_STAGE_NUM in NUMBER,
272 X_VISIT_ID in NUMBER,
273 X_DURATION in NUMBER,
274 X_OBJECT_VERSION_NUMBER in NUMBER,
275 X_ATTRIBUTE_CATEGORY in VARCHAR2,
276 X_ATTRIBUTE1 in VARCHAR2,
277 X_ATTRIBUTE2 in VARCHAR2,
278 X_STAGE_NAME in VARCHAR2,
279 X_LAST_UPDATE_DATE in DATE,
280 X_LAST_UPDATED_BY in NUMBER,
281 X_LAST_UPDATE_LOGIN in NUMBER
282 ) is
283 begin
284 update AHL_VWP_STAGES_B set
285 ATTRIBUTE3 = X_ATTRIBUTE3,
286 ATTRIBUTE4 = X_ATTRIBUTE4,
287 ATTRIBUTE5 = X_ATTRIBUTE5,
288 ATTRIBUTE6 = X_ATTRIBUTE6,
289 ATTRIBUTE7 = X_ATTRIBUTE7,
290 ATTRIBUTE8 = X_ATTRIBUTE8,
291 ATTRIBUTE9 = X_ATTRIBUTE9,
292 ATTRIBUTE10 = X_ATTRIBUTE10,
293 ATTRIBUTE11 = X_ATTRIBUTE11,
294 ATTRIBUTE12 = X_ATTRIBUTE12,
295 ATTRIBUTE13 = X_ATTRIBUTE13,
296 ATTRIBUTE14 = X_ATTRIBUTE14,
297 ATTRIBUTE15 = X_ATTRIBUTE15,
298 STAGE_NUM = X_STAGE_NUM,
299 VISIT_ID = X_VISIT_ID,
300 DURATION = X_DURATION,
301 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
302 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
303 ATTRIBUTE1 = X_ATTRIBUTE1,
304 ATTRIBUTE2 = X_ATTRIBUTE2,
305 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
306 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
307 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
308 where STAGE_ID = X_STAGE_ID;
309
310 if (sql%notfound) then
311 raise no_data_found;
312 end if;
313
314 update AHL_VWP_STAGES_TL set
315 STAGE_NAME = X_STAGE_NAME,
316 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
317 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
318 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
319 SOURCE_LANG = userenv('LANG')
320 where STAGE_ID = X_STAGE_ID
321 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
322
323 if (sql%notfound) then
324 raise no_data_found;
325 end if;
326 end UPDATE_ROW;
327
328 procedure DELETE_ROW (
329 X_STAGE_ID in NUMBER
330 ) is
331 begin
332 delete from AHL_VWP_STAGES_TL
333 where STAGE_ID = X_STAGE_ID;
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338
339 delete from AHL_VWP_STAGES_B
340 where STAGE_ID = X_STAGE_ID;
341
342 if (sql%notfound) then
343 raise no_data_found;
344 end if;
345 end DELETE_ROW;
346
347 procedure ADD_LANGUAGE
348 is
349 begin
350 delete from AHL_VWP_STAGES_TL T
351 where not exists
352 (select NULL
353 from AHL_VWP_STAGES_B B
354 where B.STAGE_ID = T.STAGE_ID
355 );
356
357 update AHL_VWP_STAGES_TL T set (
358 STAGE_NAME
359 ) = (select
360 B.STAGE_NAME
361 from AHL_VWP_STAGES_TL B
362 where B.STAGE_ID = T.STAGE_ID
363 and B.LANGUAGE = T.SOURCE_LANG)
364 where (
365 T.STAGE_ID,
366 T.LANGUAGE
367 ) in (select
368 SUBT.STAGE_ID,
369 SUBT.LANGUAGE
370 from AHL_VWP_STAGES_TL SUBB, AHL_VWP_STAGES_TL SUBT
371 where SUBB.STAGE_ID = SUBT.STAGE_ID
372 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
373 and (SUBB.STAGE_NAME <> SUBT.STAGE_NAME
374 or (SUBB.STAGE_NAME is null and SUBT.STAGE_NAME is not null)
375 or (SUBB.STAGE_NAME is not null and SUBT.STAGE_NAME is null)
376 ));
377
378 insert into AHL_VWP_STAGES_TL (
379 STAGE_ID,
380 LAST_UPDATE_DATE,
381 LAST_UPDATED_BY,
382 CREATION_DATE,
383 CREATED_BY,
384 LAST_UPDATE_LOGIN,
385 STAGE_NAME,
386 LANGUAGE,
387 SOURCE_LANG
388 ) select /*+ ORDERED */
389 B.STAGE_ID,
390 B.LAST_UPDATE_DATE,
391 B.LAST_UPDATED_BY,
392 B.CREATION_DATE,
393 B.CREATED_BY,
394 B.LAST_UPDATE_LOGIN,
395 B.STAGE_NAME,
396 L.LANGUAGE_CODE,
397 B.SOURCE_LANG
398 from AHL_VWP_STAGES_TL B, FND_LANGUAGES L
399 where L.INSTALLED_FLAG in ('I', 'B')
400 and B.LANGUAGE = userenv('LANG')
401 and not exists
402 (select NULL
403 from AHL_VWP_STAGES_TL T
404 where T.STAGE_ID = B.STAGE_ID
405 and T.LANGUAGE = L.LANGUAGE_CODE);
406 end ADD_LANGUAGE;
407
408 end AHL_VWP_STAGES_PKG;
[Home] [Help]