1 package body AHL_VISIT_TYPE_STAGES_PKG as
2 /* $Header: AHLLVTSB.pls 120.0.12020000.2 2012/12/06 23:57:17 sareepar noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_VISIT_TYPE_STAGE_ID in NUMBER,
6 X_VISIT_TYPE_ID in NUMBER,
7 X_DURATION in NUMBER,
8 X_STAGE_NUMBER in NUMBER,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_ATTRIBUTE_CATEGORY in VARCHAR2,
11 X_ATTRIBUTE1 in VARCHAR2,
12 X_ATTRIBUTE2 in VARCHAR2,
13 X_ATTRIBUTE3 in VARCHAR2,
14 X_ATTRIBUTE4 in VARCHAR2,
15 X_ATTRIBUTE5 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 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_VISIT_TYPE_STAGES_B
34 where VISIT_TYPE_STAGE_ID = X_VISIT_TYPE_STAGE_ID
35 ;
36 begin
37 insert into AHL_VISIT_TYPE_STAGES_B (
38 VISIT_TYPE_STAGE_ID,
39 VISIT_TYPE_ID,
40 DURATION,
41 STAGE_NUMBER,
42 OBJECT_VERSION_NUMBER,
43 ATTRIBUTE_CATEGORY,
44 ATTRIBUTE1,
45 ATTRIBUTE2,
46 ATTRIBUTE3,
47 ATTRIBUTE4,
48 ATTRIBUTE5,
49 ATTRIBUTE6,
50 ATTRIBUTE7,
51 ATTRIBUTE8,
52 ATTRIBUTE9,
53 ATTRIBUTE10,
54 ATTRIBUTE11,
55 ATTRIBUTE12,
56 ATTRIBUTE13,
57 ATTRIBUTE14,
58 ATTRIBUTE15,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN
64 ) values (
65 X_VISIT_TYPE_STAGE_ID,
66 X_VISIT_TYPE_ID,
67 X_DURATION,
68 X_STAGE_NUMBER,
69 X_OBJECT_VERSION_NUMBER,
70 X_ATTRIBUTE_CATEGORY,
71 X_ATTRIBUTE1,
72 X_ATTRIBUTE2,
73 X_ATTRIBUTE3,
74 X_ATTRIBUTE4,
75 X_ATTRIBUTE5,
76 X_ATTRIBUTE6,
77 X_ATTRIBUTE7,
78 X_ATTRIBUTE8,
79 X_ATTRIBUTE9,
80 X_ATTRIBUTE10,
81 X_ATTRIBUTE11,
82 X_ATTRIBUTE12,
83 X_ATTRIBUTE13,
84 X_ATTRIBUTE14,
85 X_ATTRIBUTE15,
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_VISIT_TYPE_STAGES_TL (
94 VISIT_TYPE_STAGE_ID,
95 STAGE_NAME,
96 LAST_UPDATE_DATE,
97 LAST_UPDATED_BY,
98 CREATION_DATE,
99 CREATED_BY,
100 LAST_UPDATE_LOGIN,
101 LANGUAGE,
102 SOURCE_LANG
103 ) select
104 X_VISIT_TYPE_STAGE_ID,
105 X_STAGE_NAME,
106 X_LAST_UPDATE_DATE,
107 X_LAST_UPDATED_BY,
108 X_CREATION_DATE,
109 X_CREATED_BY,
110 X_LAST_UPDATE_LOGIN,
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_VISIT_TYPE_STAGES_TL T
118 where T.VISIT_TYPE_STAGE_ID = X_VISIT_TYPE_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_VISIT_TYPE_STAGE_ID in NUMBER,
133 X_VISIT_TYPE_ID in NUMBER,
134 X_DURATION in NUMBER,
135 X_STAGE_NUMBER in NUMBER,
136 X_OBJECT_VERSION_NUMBER in NUMBER,
137 X_ATTRIBUTE_CATEGORY in VARCHAR2,
138 X_ATTRIBUTE1 in VARCHAR2,
139 X_ATTRIBUTE2 in VARCHAR2,
140 X_ATTRIBUTE3 in VARCHAR2,
141 X_ATTRIBUTE4 in VARCHAR2,
142 X_ATTRIBUTE5 in VARCHAR2,
143 X_ATTRIBUTE6 in VARCHAR2,
144 X_ATTRIBUTE7 in VARCHAR2,
145 X_ATTRIBUTE8 in VARCHAR2,
146 X_ATTRIBUTE9 in VARCHAR2,
147 X_ATTRIBUTE10 in VARCHAR2,
148 X_ATTRIBUTE11 in VARCHAR2,
149 X_ATTRIBUTE12 in VARCHAR2,
150 X_ATTRIBUTE13 in VARCHAR2,
151 X_ATTRIBUTE14 in VARCHAR2,
152 X_ATTRIBUTE15 in VARCHAR2,
153 X_STAGE_NAME in VARCHAR2
154 ) is
155 cursor c is select
156 VISIT_TYPE_ID,
157 DURATION,
158 STAGE_NUMBER,
159 OBJECT_VERSION_NUMBER,
160 ATTRIBUTE_CATEGORY,
161 ATTRIBUTE1,
162 ATTRIBUTE2,
163 ATTRIBUTE3,
164 ATTRIBUTE4,
165 ATTRIBUTE5,
166 ATTRIBUTE6,
167 ATTRIBUTE7,
168 ATTRIBUTE8,
169 ATTRIBUTE9,
170 ATTRIBUTE10,
171 ATTRIBUTE11,
172 ATTRIBUTE12,
173 ATTRIBUTE13,
174 ATTRIBUTE14,
175 ATTRIBUTE15
176 from AHL_VISIT_TYPE_STAGES_B
177 where VISIT_TYPE_STAGE_ID = X_VISIT_TYPE_STAGE_ID
178 for update of VISIT_TYPE_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_VISIT_TYPE_STAGES_TL
185 where VISIT_TYPE_STAGE_ID = X_VISIT_TYPE_STAGE_ID
186 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
187 for update of VISIT_TYPE_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.VISIT_TYPE_ID = X_VISIT_TYPE_ID)
198 AND ((recinfo.DURATION = X_DURATION)
199 OR ((recinfo.DURATION is null) AND (X_DURATION is null)))
200 AND (recinfo.STAGE_NUMBER = X_STAGE_NUMBER)
201 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
202 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
203 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
204 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
205 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
206 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
207 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
208 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
209 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
210 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
211 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
212 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
213 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
214 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
215 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
216 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
217 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
218 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
219 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
220 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
221 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
222 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
223 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
224 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
225 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
226 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
227 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
228 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
229 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
230 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
231 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
232 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
233 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 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_VISIT_TYPE_STAGE_ID in NUMBER,
258 X_VISIT_TYPE_ID in NUMBER,
259 X_DURATION in NUMBER,
260 X_STAGE_NUMBER in NUMBER,
261 X_OBJECT_VERSION_NUMBER in NUMBER,
262 X_ATTRIBUTE_CATEGORY in VARCHAR2,
263 X_ATTRIBUTE1 in VARCHAR2,
264 X_ATTRIBUTE2 in VARCHAR2,
265 X_ATTRIBUTE3 in VARCHAR2,
266 X_ATTRIBUTE4 in VARCHAR2,
267 X_ATTRIBUTE5 in VARCHAR2,
268 X_ATTRIBUTE6 in VARCHAR2,
269 X_ATTRIBUTE7 in VARCHAR2,
270 X_ATTRIBUTE8 in VARCHAR2,
271 X_ATTRIBUTE9 in VARCHAR2,
272 X_ATTRIBUTE10 in VARCHAR2,
273 X_ATTRIBUTE11 in VARCHAR2,
274 X_ATTRIBUTE12 in VARCHAR2,
275 X_ATTRIBUTE13 in VARCHAR2,
276 X_ATTRIBUTE14 in VARCHAR2,
277 X_ATTRIBUTE15 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_VISIT_TYPE_STAGES_B set
285 VISIT_TYPE_ID = X_VISIT_TYPE_ID,
286 DURATION = X_DURATION,
287 STAGE_NUMBER = X_STAGE_NUMBER,
288 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
289 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
290 ATTRIBUTE1 = X_ATTRIBUTE1,
291 ATTRIBUTE2 = X_ATTRIBUTE2,
292 ATTRIBUTE3 = X_ATTRIBUTE3,
293 ATTRIBUTE4 = X_ATTRIBUTE4,
294 ATTRIBUTE5 = X_ATTRIBUTE5,
295 ATTRIBUTE6 = X_ATTRIBUTE6,
296 ATTRIBUTE7 = X_ATTRIBUTE7,
297 ATTRIBUTE8 = X_ATTRIBUTE8,
298 ATTRIBUTE9 = X_ATTRIBUTE9,
299 ATTRIBUTE10 = X_ATTRIBUTE10,
300 ATTRIBUTE11 = X_ATTRIBUTE11,
301 ATTRIBUTE12 = X_ATTRIBUTE12,
302 ATTRIBUTE13 = X_ATTRIBUTE13,
303 ATTRIBUTE14 = X_ATTRIBUTE14,
304 ATTRIBUTE15 = X_ATTRIBUTE15,
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 VISIT_TYPE_STAGE_ID = X_VISIT_TYPE_STAGE_ID;
309
310 if (sql%notfound) then
311 raise no_data_found;
312 end if;
313
314 update AHL_VISIT_TYPE_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 VISIT_TYPE_STAGE_ID = X_VISIT_TYPE_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_VISIT_TYPE_STAGE_ID in NUMBER
330 ) is
331 begin
332 delete from AHL_VISIT_TYPE_STAGES_TL
333 where VISIT_TYPE_STAGE_ID = X_VISIT_TYPE_STAGE_ID;
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338
339 delete from AHL_VISIT_TYPE_STAGES_B
340 where VISIT_TYPE_STAGE_ID = X_VISIT_TYPE_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_VISIT_TYPE_STAGES_TL T
351 where not exists
352 (select NULL
353 from AHL_VISIT_TYPE_STAGES_B B
354 where B.VISIT_TYPE_STAGE_ID = T.VISIT_TYPE_STAGE_ID
355 );
356
357 update AHL_VISIT_TYPE_STAGES_TL T set (
358 STAGE_NAME
359 ) = (select
360 B.STAGE_NAME
361 from AHL_VISIT_TYPE_STAGES_TL B
362 where B.VISIT_TYPE_STAGE_ID = T.VISIT_TYPE_STAGE_ID
363 and B.LANGUAGE = T.SOURCE_LANG)
364 where (
365 T.VISIT_TYPE_STAGE_ID,
366 T.LANGUAGE
367 ) in (select
368 SUBT.VISIT_TYPE_STAGE_ID,
369 SUBT.LANGUAGE
370 from AHL_VISIT_TYPE_STAGES_TL SUBB, AHL_VISIT_TYPE_STAGES_TL SUBT
371 where SUBB.VISIT_TYPE_STAGE_ID = SUBT.VISIT_TYPE_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_VISIT_TYPE_STAGES_TL (
379 VISIT_TYPE_STAGE_ID,
380 STAGE_NAME,
381 LAST_UPDATE_DATE,
382 LAST_UPDATED_BY,
383 CREATION_DATE,
384 CREATED_BY,
385 LAST_UPDATE_LOGIN,
386 LANGUAGE,
387 SOURCE_LANG
388 ) select /*+ ORDERED */
389 B.VISIT_TYPE_STAGE_ID,
390 B.STAGE_NAME,
391 B.LAST_UPDATE_DATE,
392 B.LAST_UPDATED_BY,
393 B.CREATION_DATE,
394 B.CREATED_BY,
395 B.LAST_UPDATE_LOGIN,
396 L.LANGUAGE_CODE,
397 B.SOURCE_LANG
398 from AHL_VISIT_TYPE_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_VISIT_TYPE_STAGES_TL T
404 where T.VISIT_TYPE_STAGE_ID = B.VISIT_TYPE_STAGE_ID
405 and T.LANGUAGE = L.LANGUAGE_CODE);
406 end ADD_LANGUAGE;
407
408 end AHL_VISIT_TYPE_STAGES_PKG;
[Home] [Help]