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