1 package body AHL_VISITS_PKG as
2 /* $Header: AHLLVSTB.pls 120.4.12020000.2 2012/12/06 23:52:44 sareepar ship $ */
3 -- New internal procedure added by skpathak on 17-FEB-2009 for bug 8223329
4 -- Catch the dup_val_on_index exception and re-insert with current maximum + 1 for visit_number
5 procedure INTERNAL_INSERT_B_ROW (
6 X_ESTIMATED_PRICE in NUMBER,
7 X_PRIORITY_CODE in VARCHAR2,
8 X_PROJECT_TEMPLATE_ID in NUMBER,
9 X_UNIT_SCHEDULE_ID in NUMBER,
10 X_ACTUAL_PRICE in NUMBER,
11 X_OUTSIDE_PARTY_FLAG in VARCHAR2,
12 X_ANY_TASK_CHG_FLAG in VARCHAR2,
13 X_PRICE_LIST_ID in NUMBER,
14 X_CLOSE_DATE_TIME in DATE,
15 X_SCHEDULE_DESIGNATOR in VARCHAR2,
16 X_SERVICE_REQUEST_ID in NUMBER,
17 X_SPACE_CATEGORY_CODE in VARCHAR2,
18 X_VISIT_ID in NUMBER,
19 X_OBJECT_VERSION_NUMBER in NUMBER,
20 X_ORGANIZATION_ID in NUMBER,
21 X_DEPARTMENT_ID in NUMBER,
22 X_STATUS_CODE in VARCHAR2,
23 X_START_DATE_TIME in DATE,
24 X_VISIT_TYPE_CODE in VARCHAR2,
25 X_SIMULATION_PLAN_ID in NUMBER,
26 X_ITEM_INSTANCE_ID in NUMBER,
27 X_INVENTORY_ITEM_ID in NUMBER,
28 X_ASSO_PRIMARY_VISIT_ID in NUMBER,
29 X_SIMULATION_DELETE_FLAG in VARCHAR2,
30 X_TEMPLATE_FLAG in VARCHAR2,
31 X_OUT_OF_SYNC_FLAG in VARCHAR2,
32 X_PROJECT_FLAG in VARCHAR2,
33 X_PROJECT_ID in NUMBER,
34 X_VISIT_NUMBER in NUMBER,
35 X_ITEM_ORGANIZATION_ID in NUMBER,
36 X_ATTRIBUTE_CATEGORY in VARCHAR2,
37 X_ATTRIBUTE1 in VARCHAR2,
38 X_ATTRIBUTE2 in VARCHAR2,
39 X_ATTRIBUTE3 in VARCHAR2,
40 X_ATTRIBUTE4 in VARCHAR2,
41 X_ATTRIBUTE5 in VARCHAR2,
42 X_ATTRIBUTE6 in VARCHAR2,
43 X_ATTRIBUTE7 in VARCHAR2,
44 X_ATTRIBUTE8 in VARCHAR2,
45 X_ATTRIBUTE9 in VARCHAR2,
46 X_ATTRIBUTE10 in VARCHAR2,
47 X_ATTRIBUTE11 in VARCHAR2,
48 X_ATTRIBUTE12 in VARCHAR2,
49 X_ATTRIBUTE13 in VARCHAR2,
50 X_ATTRIBUTE14 in VARCHAR2,
51 X_ATTRIBUTE15 in VARCHAR2,
52 X_INV_LOCATOR_ID in NUMBER, --Added by sowsubra
53 X_COMP_PLANNING_LOC_ID in NUMBER,
54 X_COMP_INREPAIR_LOC_ID in NUMBER,
55 X_CREATION_DATE in DATE,
56 X_CREATED_BY in NUMBER,
57 X_LAST_UPDATE_DATE in DATE,
58 X_LAST_UPDATED_BY in NUMBER,
59 X_LAST_UPDATE_LOGIN in NUMBER,
60 X_TOP_PROJECT_TASK_ID in NUMBER, -- AVIKUKUM :: PIE :: 20-OCT-2010
61 X_PAST_DATED_VISIT_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
62 X_FIRMED_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
63 X_LOCKED_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
64 X_AUTO_VISIT_TYPE_FLAG IN VARCHAR2 := null -- added by tchimira for ER 14015560
65 ) is
66 -- SKPATHAK :: Bug #9171735 :: 20-OCT-2010
67 -- Added three local variables
68 l_count NUMBER;
69 l_visit_number NUMBER;
70 L_MAX_RETRIES NUMBER;
71 begin
72 L_MAX_RETRIES := 50;
73 l_count := 0;
74 l_visit_number := X_VISIT_NUMBER;
75 -- SKPATHAK :: Bug #9171735 :: 02-MAR-2010
76 -- Call insert statement in a loop, till either DUP_VAL_ON_INDEX is not thrown or l_count < L_MAX_RETRIES
77 WHILE l_count < L_MAX_RETRIES LOOP
78 begin
79 insert into AHL_VISITS_B (
80 ESTIMATED_PRICE,
81 PRIORITY_CODE,
82 PROJECT_TEMPLATE_ID,
83 UNIT_SCHEDULE_ID,
84 ACTUAL_PRICE,
85 OUTSIDE_PARTY_FLAG,
86 ANY_TASK_CHG_FLAG,
87 PRICE_LIST_ID,
88 CLOSE_DATE_TIME,
89 SCHEDULE_DESIGNATOR,
90 SERVICE_REQUEST_ID,
91 SPACE_CATEGORY_CODE,
92 VISIT_ID,
93 OBJECT_VERSION_NUMBER,
94 ORGANIZATION_ID,
95 DEPARTMENT_ID,
96 STATUS_CODE,
97 START_DATE_TIME,
98 VISIT_TYPE_CODE,
99 SIMULATION_PLAN_ID,
100 ITEM_INSTANCE_ID,
101 INVENTORY_ITEM_ID,
102 ASSO_PRIMARY_VISIT_ID,
103 SIMULATION_DELETE_FLAG,
104 TEMPLATE_FLAG,
105 OUT_OF_SYNC_FLAG,
106 PROJECT_FLAG,
107 PROJECT_ID,
108 VISIT_NUMBER,
109 ITEM_ORGANIZATION_ID,
110 INV_LOCATOR_ID, --Added by sowsubra
111 COMP_PLANNING_LOC_ID,
112 COMP_INREPAIR_LOC_ID,
113 ATTRIBUTE_CATEGORY,
114 ATTRIBUTE1,
115 ATTRIBUTE2,
116 ATTRIBUTE3,
117 ATTRIBUTE4,
118 ATTRIBUTE5,
119 ATTRIBUTE6,
120 ATTRIBUTE7,
121 ATTRIBUTE8,
122 ATTRIBUTE9,
123 ATTRIBUTE10,
124 ATTRIBUTE11,
125 ATTRIBUTE12,
126 ATTRIBUTE13,
127 ATTRIBUTE14,
128 ATTRIBUTE15,
129 CREATION_DATE,
130 CREATED_BY,
131 LAST_UPDATE_DATE,
132 LAST_UPDATED_BY,
133 LAST_UPDATE_LOGIN,
134 TOP_PROJECT_TASK_ID, -- AVIKUKUM :: PIE :: 20-OCT-2010
135 PAST_DATED_VISIT_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
136 FIRMED_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
137 LOCKED_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
138 AUTO_VISIT_TYPE_FLAG -- added by tchimira for ER 14015560
139 ) values (
140 X_ESTIMATED_PRICE,
141 X_PRIORITY_CODE,
142 X_PROJECT_TEMPLATE_ID,
143 X_UNIT_SCHEDULE_ID,
144 X_ACTUAL_PRICE,
145 X_OUTSIDE_PARTY_FLAG,
146 X_ANY_TASK_CHG_FLAG,
147 X_PRICE_LIST_ID,
148 X_CLOSE_DATE_TIME,
149 X_SCHEDULE_DESIGNATOR,
150 X_SERVICE_REQUEST_ID,
151 X_SPACE_CATEGORY_CODE,
152 X_VISIT_ID,
153 X_OBJECT_VERSION_NUMBER,
154 X_ORGANIZATION_ID,
155 X_DEPARTMENT_ID,
156 X_STATUS_CODE,
157 X_START_DATE_TIME,
158 X_VISIT_TYPE_CODE,
159 X_SIMULATION_PLAN_ID,
160 X_ITEM_INSTANCE_ID,
161 X_INVENTORY_ITEM_ID,
162 X_ASSO_PRIMARY_VISIT_ID,
163 X_SIMULATION_DELETE_FLAG,
164 X_TEMPLATE_FLAG,
165 X_OUT_OF_SYNC_FLAG,
166 X_PROJECT_FLAG,
167 X_PROJECT_ID,
168 L_VISIT_NUMBER, --Changed for bug #9171735 by skpathak
169 X_ITEM_ORGANIZATION_ID,
170 X_INV_LOCATOR_ID, --Added by sowsubra
171 X_COMP_PLANNING_LOC_ID,
172 X_COMP_INREPAIR_LOC_ID,
173 X_ATTRIBUTE_CATEGORY,
174 X_ATTRIBUTE1,
175 X_ATTRIBUTE2,
176 X_ATTRIBUTE3,
177 X_ATTRIBUTE4,
178 X_ATTRIBUTE5,
179 X_ATTRIBUTE6,
180 X_ATTRIBUTE7,
181 X_ATTRIBUTE8,
182 X_ATTRIBUTE9,
183 X_ATTRIBUTE10,
184 X_ATTRIBUTE11,
185 X_ATTRIBUTE12,
186 X_ATTRIBUTE13,
187 X_ATTRIBUTE14,
188 X_ATTRIBUTE15,
189 X_CREATION_DATE,
190 X_CREATED_BY,
191 X_LAST_UPDATE_DATE,
192 X_LAST_UPDATED_BY,
193 X_LAST_UPDATE_LOGIN,
194 X_TOP_PROJECT_TASK_ID, -- AVIKUKUM :: PIE :: 20-OCt-2010
195 X_PAST_DATED_VISIT_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
196 X_FIRMED_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
197 X_LOCKED_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
198 X_AUTO_VISIT_TYPE_FLAG -- added by tchimira for ER 14015560
199 );
200 -- Exit the while loop if the above insert is successful
201 EXIT;
202 -- If the insert is not successful catch DUP_VAL_ON_INDEX and increment the l_count by 1
203 -- Also fetch the current maximum visit number +1 into the local variable l_visit_number
204 EXCEPTION
205 WHEN DUP_VAL_ON_INDEX THEN
206 -- If l_count is L_MAX_RETRIES - 1 and still there is this exception,
207 -- no more retries are permitted, so raise the exception DUP_VAL_ON_INDEX
208 IF (l_count = L_MAX_RETRIES - 1) THEN
209 RAISE DUP_VAL_ON_INDEX;
210 END IF;
211 l_count := l_count + 1;
212 select MAX(visit_number) + 1 INTO l_visit_number FROM Ahl_Visits_B;
213 END; -- Nested block with Exception Handler
214 END LOOP;
215 END INTERNAL_INSERT_B_ROW;
216
217 --------------------
218
219 procedure INSERT_ROW (
220 X_ROWID in out nocopy VARCHAR2,
221 X_VISIT_ID in NUMBER,
222 X_ESTIMATED_PRICE in NUMBER,
223 X_PRIORITY_CODE in VARCHAR2,
224 X_PROJECT_TEMPLATE_ID in NUMBER,
225 X_UNIT_SCHEDULE_ID in NUMBER,
226 X_ACTUAL_PRICE in NUMBER,
227 X_OUTSIDE_PARTY_FLAG in VARCHAR2,
228 X_ANY_TASK_CHG_FLAG in VARCHAR2,
229 X_PRICE_LIST_ID in NUMBER,
230 X_CLOSE_DATE_TIME in DATE,
231 X_SCHEDULE_DESIGNATOR in VARCHAR2,
232 X_SERVICE_REQUEST_ID in NUMBER,
233 X_SPACE_CATEGORY_CODE in VARCHAR2,
234 X_OBJECT_VERSION_NUMBER in NUMBER,
235 X_ORGANIZATION_ID in NUMBER,
236 X_DEPARTMENT_ID in NUMBER,
237 X_STATUS_CODE in VARCHAR2,
238 X_START_DATE_TIME in DATE,
239 X_VISIT_TYPE_CODE in VARCHAR2,
240 X_SIMULATION_PLAN_ID in NUMBER,
241 X_ITEM_INSTANCE_ID in NUMBER,
242 X_INVENTORY_ITEM_ID in NUMBER,
243 X_ASSO_PRIMARY_VISIT_ID in NUMBER,
244 X_SIMULATION_DELETE_FLAG in VARCHAR2,
245 X_TEMPLATE_FLAG in VARCHAR2,
246 X_OUT_OF_SYNC_FLAG in VARCHAR2,
247 X_PROJECT_FLAG in VARCHAR2,
248 X_PROJECT_ID in NUMBER,
249 X_VISIT_NUMBER in NUMBER,
250 X_ITEM_ORGANIZATION_ID in NUMBER,
251 X_ATTRIBUTE_CATEGORY in VARCHAR2,
252 X_ATTRIBUTE1 in VARCHAR2,
253 X_ATTRIBUTE2 in VARCHAR2,
254 X_ATTRIBUTE3 in VARCHAR2,
255 X_ATTRIBUTE4 in VARCHAR2,
256 X_ATTRIBUTE5 in VARCHAR2,
257 X_ATTRIBUTE6 in VARCHAR2,
258 X_ATTRIBUTE7 in VARCHAR2,
259 X_ATTRIBUTE8 in VARCHAR2,
260 X_ATTRIBUTE9 in VARCHAR2,
261 X_ATTRIBUTE10 in VARCHAR2,
262 X_ATTRIBUTE11 in VARCHAR2,
263 X_ATTRIBUTE12 in VARCHAR2,
264 X_ATTRIBUTE13 in VARCHAR2,
265 X_ATTRIBUTE14 in VARCHAR2,
266 X_ATTRIBUTE15 in VARCHAR2,
267 X_VISIT_NAME in VARCHAR2,
268 X_DESCRIPTION in VARCHAR2,
269 X_INV_LOCATOR_ID in NUMBER,
270 X_COMP_PLANNING_LOC_ID in NUMBER,
271 X_COMP_INREPAIR_LOC_ID in NUMBER,
272 X_CREATION_DATE in DATE,
273 X_CREATED_BY in NUMBER,
274 X_LAST_UPDATE_DATE in DATE,
275 X_LAST_UPDATED_BY in NUMBER,
276 X_LAST_UPDATE_LOGIN in NUMBER,
277 X_TOP_PROJECT_TASK_ID in NUMBER, -- AVIKUKUM :: PIE :: 20-OCT-2010
278 X_PAST_DATED_VISIT_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
279 X_FIRMED_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
280 X_LOCKED_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
281 X_AUTO_VISIT_TYPE_FLAG IN VARCHAR2 := null -- added by tchimira for ER 14015560
282 ) is
283 cursor C is select ROWID from AHL_VISITS_B
284 where VISIT_ID = X_VISIT_ID
285 ;
286 begin
287 -- Changed by skpathak on 17-FEB-2009 for bug 8223329
288 -- Call the new internal procedure to insert into AHL_VISITS_B
289 INTERNAL_INSERT_B_ROW (
290 X_ESTIMATED_PRICE,
291 X_PRIORITY_CODE,
292 X_PROJECT_TEMPLATE_ID,
293 X_UNIT_SCHEDULE_ID,
294 X_ACTUAL_PRICE,
295 X_OUTSIDE_PARTY_FLAG,
296 X_ANY_TASK_CHG_FLAG,
297 X_PRICE_LIST_ID,
298 X_CLOSE_DATE_TIME,
299 X_SCHEDULE_DESIGNATOR,
300 X_SERVICE_REQUEST_ID,
301 X_SPACE_CATEGORY_CODE,
302 X_VISIT_ID,
303 X_OBJECT_VERSION_NUMBER,
304 X_ORGANIZATION_ID,
305 X_DEPARTMENT_ID,
306 X_STATUS_CODE,
307 X_START_DATE_TIME,
308 X_VISIT_TYPE_CODE,
309 X_SIMULATION_PLAN_ID,
310 X_ITEM_INSTANCE_ID,
311 X_INVENTORY_ITEM_ID,
312 X_ASSO_PRIMARY_VISIT_ID,
313 X_SIMULATION_DELETE_FLAG,
314 X_TEMPLATE_FLAG,
315 X_OUT_OF_SYNC_FLAG,
316 X_PROJECT_FLAG,
317 X_PROJECT_ID,
318 X_VISIT_NUMBER,
319 X_ITEM_ORGANIZATION_ID,
320 X_ATTRIBUTE_CATEGORY,
321 X_ATTRIBUTE1,
322 X_ATTRIBUTE2,
323 X_ATTRIBUTE3,
324 X_ATTRIBUTE4,
325 X_ATTRIBUTE5,
326 X_ATTRIBUTE6,
327 X_ATTRIBUTE7,
328 X_ATTRIBUTE8,
329 X_ATTRIBUTE9,
330 X_ATTRIBUTE10,
331 X_ATTRIBUTE11,
332 X_ATTRIBUTE12,
333 X_ATTRIBUTE13,
334 X_ATTRIBUTE14,
335 X_ATTRIBUTE15,
336 X_INV_LOCATOR_ID,
337 X_COMP_PLANNING_LOC_ID,
338 X_COMP_INREPAIR_LOC_ID,
339 X_CREATION_DATE,
340 X_CREATED_BY,
341 X_LAST_UPDATE_DATE,
342 X_LAST_UPDATED_BY,
343 X_LAST_UPDATE_LOGIN,
344 X_TOP_PROJECT_TASK_ID, -- AVIKUKUM :: PIE :: 20-OCT-2010
345 X_PAST_DATED_VISIT_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
346 X_FIRMED_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
347 X_LOCKED_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
348 X_AUTO_VISIT_TYPE_FLAG -- added by tchimira for ER 14015560
349 );
350
351 insert into AHL_VISITS_TL (
352 VISIT_ID,
353 LAST_UPDATE_DATE,
354 LAST_UPDATED_BY,
355 CREATION_DATE,
356 CREATED_BY,
357 LAST_UPDATE_LOGIN,
358 DESCRIPTION,
359 VISIT_NAME,
360 LANGUAGE,
361 SOURCE_LANG
362 ) select
363 X_VISIT_ID,
364 X_LAST_UPDATE_DATE,
365 X_LAST_UPDATED_BY,
366 X_CREATION_DATE,
367 X_CREATED_BY,
371 L.LANGUAGE_CODE,
368 X_LAST_UPDATE_LOGIN,
369 X_DESCRIPTION,
370 X_VISIT_NAME,
372 userenv('LANG')
373 from FND_LANGUAGES L
374 where L.INSTALLED_FLAG in ('I', 'B')
375 and not exists
376 (select NULL
377 from AHL_VISITS_TL T
378 where T.VISIT_ID = X_VISIT_ID
379 and T.LANGUAGE = L.LANGUAGE_CODE);
380
381 open c;
382 fetch c into X_ROWID;
383 if (c%notfound) then
384 close c;
385 raise no_data_found;
386 end if;
387 close c;
388
389 end INSERT_ROW;
390
391 procedure LOCK_ROW (
392 X_VISIT_ID in NUMBER,
393 X_ESTIMATED_PRICE in NUMBER,
394 X_PRIORITY_CODE in VARCHAR2,
395 X_PROJECT_TEMPLATE_ID in NUMBER,
396 X_UNIT_SCHEDULE_ID in NUMBER,
397 X_ACTUAL_PRICE in NUMBER,
398 X_OUTSIDE_PARTY_FLAG in VARCHAR2,
399 X_ANY_TASK_CHG_FLAG in VARCHAR2,
400 X_PRICE_LIST_ID in NUMBER,
401 X_CLOSE_DATE_TIME in DATE,
402 X_SCHEDULE_DESIGNATOR in VARCHAR2,
403 X_SERVICE_REQUEST_ID in NUMBER,
404 X_SPACE_CATEGORY_CODE in VARCHAR2,
405 X_OBJECT_VERSION_NUMBER in NUMBER,
406 X_ORGANIZATION_ID in NUMBER,
407 X_DEPARTMENT_ID in NUMBER,
408 X_STATUS_CODE in VARCHAR2,
409 X_START_DATE_TIME in DATE,
410 X_VISIT_TYPE_CODE in VARCHAR2,
411 X_SIMULATION_PLAN_ID in NUMBER,
412 X_ITEM_INSTANCE_ID in NUMBER,
413 X_INVENTORY_ITEM_ID in NUMBER,
414 X_ASSO_PRIMARY_VISIT_ID in NUMBER,
415 X_SIMULATION_DELETE_FLAG in VARCHAR2,
416 X_TEMPLATE_FLAG in VARCHAR2,
417 X_OUT_OF_SYNC_FLAG in VARCHAR2,
418 X_PROJECT_FLAG in VARCHAR2,
419 X_PROJECT_ID in NUMBER,
420 X_VISIT_NUMBER in NUMBER,
421 X_ITEM_ORGANIZATION_ID in NUMBER,
422 X_ATTRIBUTE_CATEGORY in VARCHAR2,
423 X_ATTRIBUTE1 in VARCHAR2,
424 X_ATTRIBUTE2 in VARCHAR2,
425 X_ATTRIBUTE3 in VARCHAR2,
426 X_ATTRIBUTE4 in VARCHAR2,
427 X_ATTRIBUTE5 in VARCHAR2,
428 X_ATTRIBUTE6 in VARCHAR2,
429 X_ATTRIBUTE7 in VARCHAR2,
430 X_ATTRIBUTE8 in VARCHAR2,
431 X_ATTRIBUTE9 in VARCHAR2,
432 X_ATTRIBUTE10 in VARCHAR2,
433 X_ATTRIBUTE11 in VARCHAR2,
434 X_ATTRIBUTE12 in VARCHAR2,
435 X_ATTRIBUTE13 in VARCHAR2,
436 X_ATTRIBUTE14 in VARCHAR2,
437 X_ATTRIBUTE15 in VARCHAR2,
438 X_VISIT_NAME in VARCHAR2,
439 X_DESCRIPTION in VARCHAR2,
440 X_INV_LOCATOR_ID in NUMBER,--Added by sowsubra
441 X_COMP_PLANNING_LOC_ID in NUMBER,
442 X_COMP_INREPAIR_LOC_ID in NUMBER,
443 X_TOP_PROJECT_TASK_ID in NUMBER, -- AVIKUKUM :: PIE :: 20-OCT-2010
444 X_PAST_DATED_VISIT_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
445 X_FIRMED_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
446 X_LOCKED_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
447 X_AUTO_VISIT_TYPE_FLAG IN VARCHAR2 := null -- added by tchimira for ER 14015560
448 ) is
449 cursor c is select
450 ESTIMATED_PRICE,
451 PRIORITY_CODE,
452 PROJECT_TEMPLATE_ID,
453 UNIT_SCHEDULE_ID,
454 ACTUAL_PRICE,
455 OUTSIDE_PARTY_FLAG,
456 ANY_TASK_CHG_FLAG,
457 PRICE_LIST_ID,
458 CLOSE_DATE_TIME,
459 SCHEDULE_DESIGNATOR,
460 SERVICE_REQUEST_ID,
461 SPACE_CATEGORY_CODE,
462 OBJECT_VERSION_NUMBER,
463 ORGANIZATION_ID,
464 DEPARTMENT_ID,
465 STATUS_CODE,
466 START_DATE_TIME,
467 VISIT_TYPE_CODE,
468 SIMULATION_PLAN_ID,
469 ITEM_INSTANCE_ID,
470 INVENTORY_ITEM_ID,
471 ASSO_PRIMARY_VISIT_ID,
472 SIMULATION_DELETE_FLAG,
473 TEMPLATE_FLAG,
474 OUT_OF_SYNC_FLAG,
475 PROJECT_FLAG,
476 PROJECT_ID,
477 VISIT_NUMBER,
478 ITEM_ORGANIZATION_ID,
479 INV_LOCATOR_ID, --Added by sowsubra
480 COMP_PLANNING_LOC_ID,
481 COMP_INREPAIR_LOC_ID,
482 ATTRIBUTE_CATEGORY,
483 ATTRIBUTE1,
484 ATTRIBUTE2,
485 ATTRIBUTE3,
486 ATTRIBUTE4,
487 ATTRIBUTE5,
488 ATTRIBUTE6,
489 ATTRIBUTE7,
490 ATTRIBUTE8,
491 ATTRIBUTE9,
492 ATTRIBUTE10,
493 ATTRIBUTE11,
494 ATTRIBUTE12,
495 ATTRIBUTE13,
496 ATTRIBUTE14,
497 ATTRIBUTE15
498 from AHL_VISITS_B
499 where VISIT_ID = X_VISIT_ID
500 for update of VISIT_ID nowait;
501 recinfo c%rowtype;
502
503 cursor c1 is select
504 VISIT_NAME,
505 DESCRIPTION,
506 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
507 from AHL_VISITS_TL
508 where VISIT_ID = X_VISIT_ID
509 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
510 for update of VISIT_ID nowait;
511 begin
512 open c;
513 fetch c into recinfo;
514 if (c%notfound) then
515 close c;
516 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
517 app_exception.raise_exception;
518 end if;
519 close c;
520 if ( ((recinfo.ESTIMATED_PRICE = X_ESTIMATED_PRICE)
521 OR ((recinfo.ESTIMATED_PRICE is null) AND (X_ESTIMATED_PRICE is null)))
522 /*sowsubra B5854712*/
523 AND ((recinfo.INV_LOCATOR_ID = X_INV_LOCATOR_ID)
524 OR ((recinfo.INV_LOCATOR_ID is null) AND (X_INV_LOCATOR_ID is null)))
525 AND ((recinfo.COMP_PLANNING_LOC_ID = X_COMP_PLANNING_LOC_ID)
526 OR ((recinfo.COMP_PLANNING_LOC_ID is null) AND (X_COMP_PLANNING_LOC_ID is null)))
527 AND ((recinfo.COMP_INREPAIR_LOC_ID = X_COMP_INREPAIR_LOC_ID)
528 OR ((recinfo.COMP_INREPAIR_LOC_ID is null) AND (X_COMP_INREPAIR_LOC_ID is null)))
529 AND ((recinfo.PRIORITY_CODE = X_PRIORITY_CODE)
533 AND ((recinfo.UNIT_SCHEDULE_ID = X_UNIT_SCHEDULE_ID)
530 OR ((recinfo.PRIORITY_CODE is null) AND (X_PRIORITY_CODE is null)))
531 AND ((recinfo.PROJECT_TEMPLATE_ID = X_PROJECT_TEMPLATE_ID)
532 OR ((recinfo.PROJECT_TEMPLATE_ID is null) AND (X_PROJECT_TEMPLATE_ID is null)))
534 OR ((recinfo.UNIT_SCHEDULE_ID is null) AND (X_UNIT_SCHEDULE_ID is null)))
535 AND ((recinfo.ACTUAL_PRICE = X_ACTUAL_PRICE)
536 OR ((recinfo.ACTUAL_PRICE is null) AND (X_ACTUAL_PRICE is null)))
537 AND ((recinfo.OUTSIDE_PARTY_FLAG = X_OUTSIDE_PARTY_FLAG)
538 OR ((recinfo.OUTSIDE_PARTY_FLAG is null) AND (X_OUTSIDE_PARTY_FLAG is null)))
539 AND ((recinfo.ANY_TASK_CHG_FLAG = X_ANY_TASK_CHG_FLAG)
540 OR ((recinfo.ANY_TASK_CHG_FLAG is null) AND (X_ANY_TASK_CHG_FLAG is null)))
541 AND ((recinfo.PRICE_LIST_ID = X_PRICE_LIST_ID)
542 OR ((recinfo.PRICE_LIST_ID is null) AND (X_PRICE_LIST_ID is null)))
543 AND ((recinfo.CLOSE_DATE_TIME = X_CLOSE_DATE_TIME)
544 OR ((recinfo.CLOSE_DATE_TIME is null) AND (X_CLOSE_DATE_TIME is null)))
545 AND ((recinfo.SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR)
546 OR ((recinfo.SCHEDULE_DESIGNATOR is null) AND (X_SCHEDULE_DESIGNATOR is null)))
547 AND ((recinfo.SERVICE_REQUEST_ID = X_SERVICE_REQUEST_ID)
548 OR ((recinfo.SERVICE_REQUEST_ID is null) AND (X_SERVICE_REQUEST_ID is null)))
549 AND ((recinfo.SPACE_CATEGORY_CODE = X_SPACE_CATEGORY_CODE)
550 OR ((recinfo.SPACE_CATEGORY_CODE is null) AND (X_SPACE_CATEGORY_CODE is null)))
551 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
552 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
553 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
554 AND ((recinfo.DEPARTMENT_ID = X_DEPARTMENT_ID)
555 OR ((recinfo.DEPARTMENT_ID is null) AND (X_DEPARTMENT_ID is null)))
556 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
557 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
558 AND ((recinfo.START_DATE_TIME = X_START_DATE_TIME)
559 OR ((recinfo.START_DATE_TIME is null) AND (X_START_DATE_TIME is null)))
560 AND ((recinfo.VISIT_TYPE_CODE = X_VISIT_TYPE_CODE)
561 OR ((recinfo.VISIT_TYPE_CODE is null) AND (X_VISIT_TYPE_CODE is null)))
562 AND ((recinfo.SIMULATION_PLAN_ID = X_SIMULATION_PLAN_ID)
563 OR ((recinfo.SIMULATION_PLAN_ID is null) AND (X_SIMULATION_PLAN_ID is null)))
564 AND ((recinfo.ITEM_INSTANCE_ID = X_ITEM_INSTANCE_ID)
565 OR ((recinfo.ITEM_INSTANCE_ID is null) AND (X_ITEM_INSTANCE_ID is null)))
566 AND ((recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
567 OR ((recinfo.INVENTORY_ITEM_ID is null) AND (X_INVENTORY_ITEM_ID is null)))
568 AND ((recinfo.ASSO_PRIMARY_VISIT_ID = X_ASSO_PRIMARY_VISIT_ID)
569 OR ((recinfo.ASSO_PRIMARY_VISIT_ID is null) AND (X_ASSO_PRIMARY_VISIT_ID is null)))
570 AND ((recinfo.SIMULATION_DELETE_FLAG = X_SIMULATION_DELETE_FLAG)
571 OR ((recinfo.SIMULATION_DELETE_FLAG is null) AND (X_SIMULATION_DELETE_FLAG is null)))
572 AND (recinfo.TEMPLATE_FLAG = X_TEMPLATE_FLAG)
573 AND ((recinfo.OUT_OF_SYNC_FLAG = X_OUT_OF_SYNC_FLAG)
574 OR ((recinfo.OUT_OF_SYNC_FLAG is null) AND (X_OUT_OF_SYNC_FLAG is null)))
575 AND (recinfo.PROJECT_FLAG = X_PROJECT_FLAG)
576 AND ((recinfo.PROJECT_ID = X_PROJECT_ID)
577 OR ((recinfo.PROJECT_ID is null) AND (X_PROJECT_ID is null)))
578 AND (recinfo.VISIT_NUMBER = X_VISIT_NUMBER)
579 AND ((recinfo.ITEM_ORGANIZATION_ID = X_ITEM_ORGANIZATION_ID)
580 OR ((recinfo.ITEM_ORGANIZATION_ID is null) AND (X_ITEM_ORGANIZATION_ID is null)))
581 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
582 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
583 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
584 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
585 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
586 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
587 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
588 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
589 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
590 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
591 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
592 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
593 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
594 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
595 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
596 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
597 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
598 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
599 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
600 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
601 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
602 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
603 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
604 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
605 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
606 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
607 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
608 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
609 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
610 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
611 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
612 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
613 ) then
614 null;
615 else
616 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
617 app_exception.raise_exception;
618 end if;
619
620 for tlinfo in c1 loop
621 if (tlinfo.BASELANG = 'Y') then
622 if ( ((tlinfo.VISIT_NAME = X_VISIT_NAME)
626 ) then
623 OR ((tlinfo.VISIT_NAME is null) AND (X_VISIT_NAME is null)))
624 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
625 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
627 null;
628 else
629 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
630 app_exception.raise_exception;
631 end if;
632 end if;
633 end loop;
634 return;
635 end LOCK_ROW;
636
637 procedure UPDATE_ROW (
638 X_VISIT_ID in NUMBER,
639 X_ESTIMATED_PRICE in NUMBER,
640 X_PRIORITY_CODE in VARCHAR2,
641 X_PROJECT_TEMPLATE_ID in NUMBER,
642 X_UNIT_SCHEDULE_ID in NUMBER,
643 X_ACTUAL_PRICE in NUMBER,
644 X_OUTSIDE_PARTY_FLAG in VARCHAR2,
645 X_ANY_TASK_CHG_FLAG in VARCHAR2,
646 X_PRICE_LIST_ID in NUMBER,
647 X_CLOSE_DATE_TIME in DATE,
648 X_SCHEDULE_DESIGNATOR in VARCHAR2,
649 X_SERVICE_REQUEST_ID in NUMBER,
650 X_SPACE_CATEGORY_CODE in VARCHAR2,
651 X_OBJECT_VERSION_NUMBER in NUMBER,
652 X_ORGANIZATION_ID in NUMBER,
653 X_DEPARTMENT_ID in NUMBER,
654 X_STATUS_CODE in VARCHAR2,
655 X_START_DATE_TIME in DATE,
656 X_VISIT_TYPE_CODE in VARCHAR2,
657 X_SIMULATION_PLAN_ID in NUMBER,
658 X_ITEM_INSTANCE_ID in NUMBER,
659 X_INVENTORY_ITEM_ID in NUMBER,
660 X_ASSO_PRIMARY_VISIT_ID in NUMBER,
661 X_SIMULATION_DELETE_FLAG in VARCHAR2,
662 X_TEMPLATE_FLAG in VARCHAR2,
663 X_OUT_OF_SYNC_FLAG in VARCHAR2,
664 X_PROJECT_FLAG in VARCHAR2,
665 X_PROJECT_ID in NUMBER,
666 X_VISIT_NUMBER in NUMBER,
667 X_ITEM_ORGANIZATION_ID in NUMBER,
668 X_ATTRIBUTE_CATEGORY in VARCHAR2,
669 X_ATTRIBUTE1 in VARCHAR2,
670 X_ATTRIBUTE2 in VARCHAR2,
671 X_ATTRIBUTE3 in VARCHAR2,
672 X_ATTRIBUTE4 in VARCHAR2,
673 X_ATTRIBUTE5 in VARCHAR2,
674 X_ATTRIBUTE6 in VARCHAR2,
675 X_ATTRIBUTE7 in VARCHAR2,
676 X_ATTRIBUTE8 in VARCHAR2,
677 X_ATTRIBUTE9 in VARCHAR2,
678 X_ATTRIBUTE10 in VARCHAR2,
679 X_ATTRIBUTE11 in VARCHAR2,
680 X_ATTRIBUTE12 in VARCHAR2,
681 X_ATTRIBUTE13 in VARCHAR2,
682 X_ATTRIBUTE14 in VARCHAR2,
683 X_ATTRIBUTE15 in VARCHAR2,
684 X_VISIT_NAME in VARCHAR2,
685 X_DESCRIPTION in VARCHAR2,
686 X_INV_LOCATOR_ID in NUMBER, --Added by sowsubra
687 X_COMP_PLANNING_LOC_ID in NUMBER,
688 X_COMP_INREPAIR_LOC_ID in NUMBER,
689 X_LAST_UPDATE_DATE in DATE,
690 X_LAST_UPDATED_BY in NUMBER,
691 X_LAST_UPDATE_LOGIN in NUMBER,
692 X_TOP_PROJECT_TASK_ID in NUMBER, -- AVIKUKUM :: PIE :: 20-OCt-2010
693 X_PAST_DATED_VISIT_FLAG in VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
694 X_FIRMED_FLAG IN VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
695 X_LOCKED_FLAG IN VARCHAR2, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
696 X_ITEM_OWNER_FLAG IN VARCHAR2, --ARUNJK added for Marshalling
697 X_AUTO_VISIT_TYPE_FLAG IN VARCHAR2 := null -- added by tchimira for ER 14015560
698 ) is
699 begin
700 update AHL_VISITS_B set
701 ESTIMATED_PRICE = X_ESTIMATED_PRICE,
702 PRIORITY_CODE = X_PRIORITY_CODE,
703 PROJECT_TEMPLATE_ID = X_PROJECT_TEMPLATE_ID,
704 UNIT_SCHEDULE_ID = X_UNIT_SCHEDULE_ID,
705 ACTUAL_PRICE = X_ACTUAL_PRICE,
706 OUTSIDE_PARTY_FLAG = X_OUTSIDE_PARTY_FLAG,
707 ANY_TASK_CHG_FLAG = X_ANY_TASK_CHG_FLAG,
708 PRICE_LIST_ID = X_PRICE_LIST_ID,
709 CLOSE_DATE_TIME = X_CLOSE_DATE_TIME,
710 SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR,
711 SERVICE_REQUEST_ID = X_SERVICE_REQUEST_ID,
712 SPACE_CATEGORY_CODE = X_SPACE_CATEGORY_CODE,
713 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
714 ORGANIZATION_ID = X_ORGANIZATION_ID,
715 DEPARTMENT_ID = X_DEPARTMENT_ID,
716 STATUS_CODE = X_STATUS_CODE,
717 START_DATE_TIME = X_START_DATE_TIME,
718 VISIT_TYPE_CODE = X_VISIT_TYPE_CODE,
719 SIMULATION_PLAN_ID = X_SIMULATION_PLAN_ID,
720 ITEM_INSTANCE_ID = X_ITEM_INSTANCE_ID,
721 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
722 ASSO_PRIMARY_VISIT_ID = X_ASSO_PRIMARY_VISIT_ID,
723 SIMULATION_DELETE_FLAG = X_SIMULATION_DELETE_FLAG,
724 TEMPLATE_FLAG = X_TEMPLATE_FLAG,
725 OUT_OF_SYNC_FLAG = X_OUT_OF_SYNC_FLAG,
726 PROJECT_FLAG = X_PROJECT_FLAG,
727 PROJECT_ID = X_PROJECT_ID,
728 VISIT_NUMBER = X_VISIT_NUMBER,
729 ITEM_ORGANIZATION_ID = X_ITEM_ORGANIZATION_ID,
730 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
731 ATTRIBUTE1 = X_ATTRIBUTE1,
732 ATTRIBUTE2 = X_ATTRIBUTE2,
733 ATTRIBUTE3 = X_ATTRIBUTE3,
734 ATTRIBUTE4 = X_ATTRIBUTE4,
735 ATTRIBUTE5 = X_ATTRIBUTE5,
736 ATTRIBUTE6 = X_ATTRIBUTE6,
737 ATTRIBUTE7 = X_ATTRIBUTE7,
738 ATTRIBUTE8 = X_ATTRIBUTE8,
739 ATTRIBUTE9 = X_ATTRIBUTE9,
740 ATTRIBUTE10 = X_ATTRIBUTE10,
741 ATTRIBUTE11 = X_ATTRIBUTE11,
742 ATTRIBUTE12 = X_ATTRIBUTE12,
743 ATTRIBUTE13 = X_ATTRIBUTE13,
744 ATTRIBUTE14 = X_ATTRIBUTE14,
745 ATTRIBUTE15 = X_ATTRIBUTE15,
746 INV_LOCATOR_ID = X_INV_LOCATOR_ID, --Added by sowsubra
747 COMP_PLANNING_LOC_ID = X_COMP_PLANNING_LOC_ID,
748 COMP_INREPAIR_LOC_ID = X_COMP_INREPAIR_LOC_ID,
749 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
750 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
751 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
752 TOP_PROJECT_TASK_ID = X_TOP_PROJECT_TASK_ID, -- AVIKUKUM :: PIE :: 20-OCT-2010
753 PAST_DATED_VISIT_FLAG = X_PAST_DATED_VISIT_FLAG , -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
754 FIRMED_FLAG = X_FIRMED_FLAG , -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
755 LOCKED_FLAG = X_LOCKED_FLAG, -- PRAKKUM :: VWPE :: ER 12424063 :: 01-FEB-2011
756 ITEM_OWNER_FLAG = X_ITEM_OWNER_FLAG,
757 AUTO_VISIT_TYPE_FLAG = X_AUTO_VISIT_TYPE_FLAG -- added by tchimira for ER 14015560
758 where VISIT_ID = X_VISIT_ID;
759
760 if (sql%notfound) then
764 update AHL_VISITS_TL set
761 raise no_data_found;
762 end if;
763
765 VISIT_NAME = X_VISIT_NAME,
766 DESCRIPTION = X_DESCRIPTION,
767 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
768 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
769 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
770 SOURCE_LANG = userenv('LANG')
771 where VISIT_ID = X_VISIT_ID
772 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
773
774 if (sql%notfound) then
775 raise no_data_found;
776 end if;
777 end UPDATE_ROW;
778
779 procedure DELETE_ROW (
780 X_VISIT_ID in NUMBER
781 ) is
782 begin
783 delete from AHL_VISITS_TL
784 where VISIT_ID = X_VISIT_ID;
785
786 if (sql%notfound) then
787 raise no_data_found;
788 end if;
789
790 delete from AHL_VISITS_B
791 where VISIT_ID = X_VISIT_ID;
792
793 if (sql%notfound) then
794 raise no_data_found;
795 end if;
796 end DELETE_ROW;
797
798 procedure ADD_LANGUAGE
799 is
800 begin
801 delete from AHL_VISITS_TL T
802 where not exists
803 (select NULL
804 from AHL_VISITS_B B
805 where B.VISIT_ID = T.VISIT_ID
806 );
807
808 update AHL_VISITS_TL T set (
809 VISIT_NAME,
810 DESCRIPTION
811 ) = (select
812 B.VISIT_NAME,
813 B.DESCRIPTION
814 from AHL_VISITS_TL B
815 where B.VISIT_ID = T.VISIT_ID
816 and B.LANGUAGE = T.SOURCE_LANG)
817 where (
818 T.VISIT_ID,
819 T.LANGUAGE
820 ) in (select
821 SUBT.VISIT_ID,
822 SUBT.LANGUAGE
823 from AHL_VISITS_TL SUBB, AHL_VISITS_TL SUBT
824 where SUBB.VISIT_ID = SUBT.VISIT_ID
825 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
826 and (SUBB.VISIT_NAME <> SUBT.VISIT_NAME
827 or (SUBB.VISIT_NAME is null and SUBT.VISIT_NAME is not null)
828 or (SUBB.VISIT_NAME is not null and SUBT.VISIT_NAME is null)
829 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
830 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
831 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
832 ));
833
834 insert into AHL_VISITS_TL (
835 VISIT_ID,
836 LAST_UPDATE_DATE,
837 LAST_UPDATED_BY,
838 CREATION_DATE,
839 CREATED_BY,
840 LAST_UPDATE_LOGIN,
841 DESCRIPTION,
842 VISIT_NAME,
843 LANGUAGE,
844 SOURCE_LANG
845 ) select /*+ ORDERED */
846 B.VISIT_ID,
847 B.LAST_UPDATE_DATE,
848 B.LAST_UPDATED_BY,
849 B.CREATION_DATE,
850 B.CREATED_BY,
851 B.LAST_UPDATE_LOGIN,
852 B.DESCRIPTION,
853 B.VISIT_NAME,
854 L.LANGUAGE_CODE,
855 B.SOURCE_LANG
856 from AHL_VISITS_TL B, FND_LANGUAGES L
857 where L.INSTALLED_FLAG in ('I', 'B')
858 and B.LANGUAGE = userenv('LANG')
859 and not exists
860 (select NULL
861 from AHL_VISITS_TL T
862 where T.VISIT_ID = B.VISIT_ID
863 and T.LANGUAGE = L.LANGUAGE_CODE);
864 end ADD_LANGUAGE;
865
866 end AHL_VISITS_PKG;
[Home] [Help]