1 package body AHL_VISITS_PKG as
2 /* $Header: AHLLVSTB.pls 120.1 2007/12/18 09:58:57 sowsubra ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_VISIT_ID in NUMBER,
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_OBJECT_VERSION_NUMBER in NUMBER,
19 X_ORGANIZATION_ID in NUMBER,
20 X_DEPARTMENT_ID in NUMBER,
21 X_STATUS_CODE in VARCHAR2,
22 X_START_DATE_TIME in DATE,
23 X_VISIT_TYPE_CODE in VARCHAR2,
24 X_SIMULATION_PLAN_ID in NUMBER,
25 X_ITEM_INSTANCE_ID in NUMBER,
26 X_INVENTORY_ITEM_ID in NUMBER,
27 X_ASSO_PRIMARY_VISIT_ID in NUMBER,
28 X_SIMULATION_DELETE_FLAG in VARCHAR2,
29 X_TEMPLATE_FLAG in VARCHAR2,
30 X_OUT_OF_SYNC_FLAG in VARCHAR2,
31 X_PROJECT_FLAG in VARCHAR2,
32 X_PROJECT_ID in NUMBER,
33 X_VISIT_NUMBER in NUMBER,
34 X_ITEM_ORGANIZATION_ID in NUMBER,
35 X_ATTRIBUTE_CATEGORY in VARCHAR2,
36 X_ATTRIBUTE1 in VARCHAR2,
37 X_ATTRIBUTE2 in VARCHAR2,
38 X_ATTRIBUTE3 in VARCHAR2,
39 X_ATTRIBUTE4 in VARCHAR2,
40 X_ATTRIBUTE5 in VARCHAR2,
41 X_ATTRIBUTE6 in VARCHAR2,
42 X_ATTRIBUTE7 in VARCHAR2,
43 X_ATTRIBUTE8 in VARCHAR2,
44 X_ATTRIBUTE9 in VARCHAR2,
45 X_ATTRIBUTE10 in VARCHAR2,
46 X_ATTRIBUTE11 in VARCHAR2,
47 X_ATTRIBUTE12 in VARCHAR2,
48 X_ATTRIBUTE13 in VARCHAR2,
49 X_ATTRIBUTE14 in VARCHAR2,
50 X_ATTRIBUTE15 in VARCHAR2,
51 X_VISIT_NAME in VARCHAR2,
52 X_DESCRIPTION in VARCHAR2,
53 X_INV_LOCATOR_ID in NUMBER, --Added by sowsubra
54 X_CREATION_DATE in DATE,
55 X_CREATED_BY in NUMBER,
56 X_LAST_UPDATE_DATE in DATE,
57 X_LAST_UPDATED_BY in NUMBER,
58 X_LAST_UPDATE_LOGIN in NUMBER
59 ) is
60 cursor C is select ROWID from AHL_VISITS_B
61 where VISIT_ID = X_VISIT_ID
62 ;
63 begin
64 insert into AHL_VISITS_B (
65 ESTIMATED_PRICE,
66 PRIORITY_CODE,
67 PROJECT_TEMPLATE_ID,
68 UNIT_SCHEDULE_ID,
69 ACTUAL_PRICE,
70 OUTSIDE_PARTY_FLAG,
71 ANY_TASK_CHG_FLAG,
72 PRICE_LIST_ID,
73 CLOSE_DATE_TIME,
74 SCHEDULE_DESIGNATOR,
75 SERVICE_REQUEST_ID,
76 SPACE_CATEGORY_CODE,
77 VISIT_ID,
78 OBJECT_VERSION_NUMBER,
79 ORGANIZATION_ID,
80 DEPARTMENT_ID,
81 STATUS_CODE,
82 START_DATE_TIME,
83 VISIT_TYPE_CODE,
84 SIMULATION_PLAN_ID,
85 ITEM_INSTANCE_ID,
86 INVENTORY_ITEM_ID,
87 ASSO_PRIMARY_VISIT_ID,
88 SIMULATION_DELETE_FLAG,
89 TEMPLATE_FLAG,
90 OUT_OF_SYNC_FLAG,
91 PROJECT_FLAG,
92 PROJECT_ID,
93 VISIT_NUMBER,
94 ITEM_ORGANIZATION_ID,
95 INV_LOCATOR_ID, --Added by sowsubra
96 ATTRIBUTE_CATEGORY,
97 ATTRIBUTE1,
98 ATTRIBUTE2,
99 ATTRIBUTE3,
100 ATTRIBUTE4,
101 ATTRIBUTE5,
102 ATTRIBUTE6,
103 ATTRIBUTE7,
104 ATTRIBUTE8,
105 ATTRIBUTE9,
106 ATTRIBUTE10,
107 ATTRIBUTE11,
108 ATTRIBUTE12,
109 ATTRIBUTE13,
110 ATTRIBUTE14,
111 ATTRIBUTE15,
112 CREATION_DATE,
113 CREATED_BY,
114 LAST_UPDATE_DATE,
115 LAST_UPDATED_BY,
116 LAST_UPDATE_LOGIN
117 ) values (
118 X_ESTIMATED_PRICE,
119 X_PRIORITY_CODE,
120 X_PROJECT_TEMPLATE_ID,
121 X_UNIT_SCHEDULE_ID,
122 X_ACTUAL_PRICE,
123 X_OUTSIDE_PARTY_FLAG,
124 X_ANY_TASK_CHG_FLAG,
125 X_PRICE_LIST_ID,
126 X_CLOSE_DATE_TIME,
127 X_SCHEDULE_DESIGNATOR,
128 X_SERVICE_REQUEST_ID,
129 X_SPACE_CATEGORY_CODE,
130 X_VISIT_ID,
131 X_OBJECT_VERSION_NUMBER,
132 X_ORGANIZATION_ID,
133 X_DEPARTMENT_ID,
134 X_STATUS_CODE,
135 X_START_DATE_TIME,
136 X_VISIT_TYPE_CODE,
137 X_SIMULATION_PLAN_ID,
138 X_ITEM_INSTANCE_ID,
139 X_INVENTORY_ITEM_ID,
140 X_ASSO_PRIMARY_VISIT_ID,
141 X_SIMULATION_DELETE_FLAG,
142 X_TEMPLATE_FLAG,
143 X_OUT_OF_SYNC_FLAG,
144 X_PROJECT_FLAG,
145 X_PROJECT_ID,
146 X_VISIT_NUMBER,
147 X_ITEM_ORGANIZATION_ID,
148 X_INV_LOCATOR_ID, --Added by sowsubra
149 X_ATTRIBUTE_CATEGORY,
150 X_ATTRIBUTE1,
151 X_ATTRIBUTE2,
152 X_ATTRIBUTE3,
153 X_ATTRIBUTE4,
154 X_ATTRIBUTE5,
155 X_ATTRIBUTE6,
156 X_ATTRIBUTE7,
157 X_ATTRIBUTE8,
158 X_ATTRIBUTE9,
159 X_ATTRIBUTE10,
160 X_ATTRIBUTE11,
161 X_ATTRIBUTE12,
162 X_ATTRIBUTE13,
163 X_ATTRIBUTE14,
164 X_ATTRIBUTE15,
165 X_CREATION_DATE,
166 X_CREATED_BY,
167 X_LAST_UPDATE_DATE,
168 X_LAST_UPDATED_BY,
169 X_LAST_UPDATE_LOGIN
170 );
171
172 insert into AHL_VISITS_TL (
173 VISIT_ID,
174 LAST_UPDATE_DATE,
175 LAST_UPDATED_BY,
176 CREATION_DATE,
177 CREATED_BY,
178 LAST_UPDATE_LOGIN,
179 DESCRIPTION,
180 VISIT_NAME,
181 LANGUAGE,
182 SOURCE_LANG
183 ) select
184 X_VISIT_ID,
185 X_LAST_UPDATE_DATE,
186 X_LAST_UPDATED_BY,
187 X_CREATION_DATE,
188 X_CREATED_BY,
189 X_LAST_UPDATE_LOGIN,
190 X_DESCRIPTION,
191 X_VISIT_NAME,
192 L.LANGUAGE_CODE,
193 userenv('LANG')
194 from FND_LANGUAGES L
195 where L.INSTALLED_FLAG in ('I', 'B')
196 and not exists
197 (select NULL
198 from AHL_VISITS_TL T
199 where T.VISIT_ID = X_VISIT_ID
200 and T.LANGUAGE = L.LANGUAGE_CODE);
201
202 open c;
203 fetch c into X_ROWID;
204 if (c%notfound) then
205 close c;
206 raise no_data_found;
207 end if;
208 close c;
209
210 end INSERT_ROW;
211
212 procedure LOCK_ROW (
213 X_VISIT_ID in NUMBER,
214 X_ESTIMATED_PRICE in NUMBER,
215 X_PRIORITY_CODE in VARCHAR2,
216 X_PROJECT_TEMPLATE_ID in NUMBER,
217 X_UNIT_SCHEDULE_ID in NUMBER,
218 X_ACTUAL_PRICE in NUMBER,
219 X_OUTSIDE_PARTY_FLAG in VARCHAR2,
220 X_ANY_TASK_CHG_FLAG in VARCHAR2,
221 X_PRICE_LIST_ID in NUMBER,
222 X_CLOSE_DATE_TIME in DATE,
223 X_SCHEDULE_DESIGNATOR in VARCHAR2,
224 X_SERVICE_REQUEST_ID in NUMBER,
225 X_SPACE_CATEGORY_CODE in VARCHAR2,
226 X_OBJECT_VERSION_NUMBER in NUMBER,
227 X_ORGANIZATION_ID in NUMBER,
228 X_DEPARTMENT_ID in NUMBER,
229 X_STATUS_CODE in VARCHAR2,
230 X_START_DATE_TIME in DATE,
231 X_VISIT_TYPE_CODE in VARCHAR2,
232 X_SIMULATION_PLAN_ID in NUMBER,
233 X_ITEM_INSTANCE_ID in NUMBER,
234 X_INVENTORY_ITEM_ID in NUMBER,
235 X_ASSO_PRIMARY_VISIT_ID in NUMBER,
236 X_SIMULATION_DELETE_FLAG in VARCHAR2,
237 X_TEMPLATE_FLAG in VARCHAR2,
238 X_OUT_OF_SYNC_FLAG in VARCHAR2,
239 X_PROJECT_FLAG in VARCHAR2,
240 X_PROJECT_ID in NUMBER,
241 X_VISIT_NUMBER in NUMBER,
242 X_ITEM_ORGANIZATION_ID in NUMBER,
243 X_ATTRIBUTE_CATEGORY in VARCHAR2,
244 X_ATTRIBUTE1 in VARCHAR2,
245 X_ATTRIBUTE2 in VARCHAR2,
246 X_ATTRIBUTE3 in VARCHAR2,
247 X_ATTRIBUTE4 in VARCHAR2,
248 X_ATTRIBUTE5 in VARCHAR2,
249 X_ATTRIBUTE6 in VARCHAR2,
250 X_ATTRIBUTE7 in VARCHAR2,
251 X_ATTRIBUTE8 in VARCHAR2,
252 X_ATTRIBUTE9 in VARCHAR2,
253 X_ATTRIBUTE10 in VARCHAR2,
254 X_ATTRIBUTE11 in VARCHAR2,
255 X_ATTRIBUTE12 in VARCHAR2,
256 X_ATTRIBUTE13 in VARCHAR2,
257 X_ATTRIBUTE14 in VARCHAR2,
258 X_ATTRIBUTE15 in VARCHAR2,
259 X_VISIT_NAME in VARCHAR2,
260 X_DESCRIPTION in VARCHAR2,
261 X_INV_LOCATOR_ID in NUMBER --Added by sowsubra
262 ) is
263 cursor c is select
264 ESTIMATED_PRICE,
265 PRIORITY_CODE,
266 PROJECT_TEMPLATE_ID,
267 UNIT_SCHEDULE_ID,
268 ACTUAL_PRICE,
269 OUTSIDE_PARTY_FLAG,
270 ANY_TASK_CHG_FLAG,
271 PRICE_LIST_ID,
272 CLOSE_DATE_TIME,
273 SCHEDULE_DESIGNATOR,
274 SERVICE_REQUEST_ID,
275 SPACE_CATEGORY_CODE,
276 OBJECT_VERSION_NUMBER,
277 ORGANIZATION_ID,
278 DEPARTMENT_ID,
279 STATUS_CODE,
280 START_DATE_TIME,
281 VISIT_TYPE_CODE,
282 SIMULATION_PLAN_ID,
283 ITEM_INSTANCE_ID,
284 INVENTORY_ITEM_ID,
285 ASSO_PRIMARY_VISIT_ID,
286 SIMULATION_DELETE_FLAG,
287 TEMPLATE_FLAG,
288 OUT_OF_SYNC_FLAG,
289 PROJECT_FLAG,
290 PROJECT_ID,
291 VISIT_NUMBER,
292 ITEM_ORGANIZATION_ID,
293 INV_LOCATOR_ID, --Added by sowsubra
294 ATTRIBUTE_CATEGORY,
295 ATTRIBUTE1,
296 ATTRIBUTE2,
297 ATTRIBUTE3,
298 ATTRIBUTE4,
299 ATTRIBUTE5,
300 ATTRIBUTE6,
301 ATTRIBUTE7,
302 ATTRIBUTE8,
303 ATTRIBUTE9,
304 ATTRIBUTE10,
305 ATTRIBUTE11,
306 ATTRIBUTE12,
307 ATTRIBUTE13,
308 ATTRIBUTE14,
309 ATTRIBUTE15
310 from AHL_VISITS_B
311 where VISIT_ID = X_VISIT_ID
312 for update of VISIT_ID nowait;
313 recinfo c%rowtype;
314
315 cursor c1 is select
316 VISIT_NAME,
317 DESCRIPTION,
318 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
319 from AHL_VISITS_TL
320 where VISIT_ID = X_VISIT_ID
321 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
322 for update of VISIT_ID nowait;
323 begin
324 open c;
325 fetch c into recinfo;
326 if (c%notfound) then
327 close c;
328 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
329 app_exception.raise_exception;
330 end if;
331 close c;
332 if ( ((recinfo.ESTIMATED_PRICE = X_ESTIMATED_PRICE)
333 OR ((recinfo.ESTIMATED_PRICE is null) AND (X_ESTIMATED_PRICE is null)))
334 /*sowsubra B5854712*/
335 AND ((recinfo.INV_LOCATOR_ID = X_INV_LOCATOR_ID)
336 OR ((recinfo.INV_LOCATOR_ID is null) AND (X_INV_LOCATOR_ID is null)))
337 AND ((recinfo.PRIORITY_CODE = X_PRIORITY_CODE)
338 OR ((recinfo.PRIORITY_CODE is null) AND (X_PRIORITY_CODE is null)))
339 AND ((recinfo.PROJECT_TEMPLATE_ID = X_PROJECT_TEMPLATE_ID)
340 OR ((recinfo.PROJECT_TEMPLATE_ID is null) AND (X_PROJECT_TEMPLATE_ID is null)))
341 AND ((recinfo.UNIT_SCHEDULE_ID = X_UNIT_SCHEDULE_ID)
342 OR ((recinfo.UNIT_SCHEDULE_ID is null) AND (X_UNIT_SCHEDULE_ID is null)))
343 AND ((recinfo.ACTUAL_PRICE = X_ACTUAL_PRICE)
344 OR ((recinfo.ACTUAL_PRICE is null) AND (X_ACTUAL_PRICE is null)))
345 AND ((recinfo.OUTSIDE_PARTY_FLAG = X_OUTSIDE_PARTY_FLAG)
346 OR ((recinfo.OUTSIDE_PARTY_FLAG is null) AND (X_OUTSIDE_PARTY_FLAG is null)))
347 AND ((recinfo.ANY_TASK_CHG_FLAG = X_ANY_TASK_CHG_FLAG)
348 OR ((recinfo.ANY_TASK_CHG_FLAG is null) AND (X_ANY_TASK_CHG_FLAG is null)))
349 AND ((recinfo.PRICE_LIST_ID = X_PRICE_LIST_ID)
350 OR ((recinfo.PRICE_LIST_ID is null) AND (X_PRICE_LIST_ID is null)))
351 AND ((recinfo.CLOSE_DATE_TIME = X_CLOSE_DATE_TIME)
352 OR ((recinfo.CLOSE_DATE_TIME is null) AND (X_CLOSE_DATE_TIME is null)))
353 AND ((recinfo.SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR)
354 OR ((recinfo.SCHEDULE_DESIGNATOR is null) AND (X_SCHEDULE_DESIGNATOR is null)))
355 AND ((recinfo.SERVICE_REQUEST_ID = X_SERVICE_REQUEST_ID)
356 OR ((recinfo.SERVICE_REQUEST_ID is null) AND (X_SERVICE_REQUEST_ID is null)))
357 AND ((recinfo.SPACE_CATEGORY_CODE = X_SPACE_CATEGORY_CODE)
358 OR ((recinfo.SPACE_CATEGORY_CODE is null) AND (X_SPACE_CATEGORY_CODE is null)))
359 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
360 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
361 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
362 AND ((recinfo.DEPARTMENT_ID = X_DEPARTMENT_ID)
363 OR ((recinfo.DEPARTMENT_ID is null) AND (X_DEPARTMENT_ID is null)))
364 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
365 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
366 AND ((recinfo.START_DATE_TIME = X_START_DATE_TIME)
367 OR ((recinfo.START_DATE_TIME is null) AND (X_START_DATE_TIME is null)))
368 AND ((recinfo.VISIT_TYPE_CODE = X_VISIT_TYPE_CODE)
369 OR ((recinfo.VISIT_TYPE_CODE is null) AND (X_VISIT_TYPE_CODE is null)))
370 AND ((recinfo.SIMULATION_PLAN_ID = X_SIMULATION_PLAN_ID)
371 OR ((recinfo.SIMULATION_PLAN_ID is null) AND (X_SIMULATION_PLAN_ID is null)))
372 AND ((recinfo.ITEM_INSTANCE_ID = X_ITEM_INSTANCE_ID)
373 OR ((recinfo.ITEM_INSTANCE_ID is null) AND (X_ITEM_INSTANCE_ID is null)))
374 AND ((recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
375 OR ((recinfo.INVENTORY_ITEM_ID is null) AND (X_INVENTORY_ITEM_ID is null)))
376 AND ((recinfo.ASSO_PRIMARY_VISIT_ID = X_ASSO_PRIMARY_VISIT_ID)
377 OR ((recinfo.ASSO_PRIMARY_VISIT_ID is null) AND (X_ASSO_PRIMARY_VISIT_ID is null)))
378 AND ((recinfo.SIMULATION_DELETE_FLAG = X_SIMULATION_DELETE_FLAG)
379 OR ((recinfo.SIMULATION_DELETE_FLAG is null) AND (X_SIMULATION_DELETE_FLAG is null)))
380 AND (recinfo.TEMPLATE_FLAG = X_TEMPLATE_FLAG)
381 AND ((recinfo.OUT_OF_SYNC_FLAG = X_OUT_OF_SYNC_FLAG)
382 OR ((recinfo.OUT_OF_SYNC_FLAG is null) AND (X_OUT_OF_SYNC_FLAG is null)))
383 AND (recinfo.PROJECT_FLAG = X_PROJECT_FLAG)
384 AND ((recinfo.PROJECT_ID = X_PROJECT_ID)
385 OR ((recinfo.PROJECT_ID is null) AND (X_PROJECT_ID is null)))
386 AND (recinfo.VISIT_NUMBER = X_VISIT_NUMBER)
387 AND ((recinfo.ITEM_ORGANIZATION_ID = X_ITEM_ORGANIZATION_ID)
388 OR ((recinfo.ITEM_ORGANIZATION_ID is null) AND (X_ITEM_ORGANIZATION_ID is null)))
389 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
390 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
391 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
392 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
393 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
394 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
395 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
396 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
397 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
398 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
399 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
400 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
404 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
401 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
402 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
403 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
405 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
406 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
407 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
408 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
409 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
410 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
411 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
412 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
413 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
414 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
415 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
416 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
417 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
418 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
419 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
420 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
421 ) then
422 null;
423 else
424 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
425 app_exception.raise_exception;
426 end if;
427
428 for tlinfo in c1 loop
429 if (tlinfo.BASELANG = 'Y') then
430 if ( ((tlinfo.VISIT_NAME = X_VISIT_NAME)
431 OR ((tlinfo.VISIT_NAME is null) AND (X_VISIT_NAME is null)))
432 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
433 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
434 ) then
435 null;
436 else
437 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
438 app_exception.raise_exception;
439 end if;
440 end if;
441 end loop;
442 return;
443 end LOCK_ROW;
444
445 procedure UPDATE_ROW (
446 X_VISIT_ID in NUMBER,
447 X_ESTIMATED_PRICE in NUMBER,
448 X_PRIORITY_CODE in VARCHAR2,
449 X_PROJECT_TEMPLATE_ID in NUMBER,
450 X_UNIT_SCHEDULE_ID in NUMBER,
451 X_ACTUAL_PRICE in NUMBER,
452 X_OUTSIDE_PARTY_FLAG in VARCHAR2,
453 X_ANY_TASK_CHG_FLAG in VARCHAR2,
454 X_PRICE_LIST_ID in NUMBER,
455 X_CLOSE_DATE_TIME in DATE,
456 X_SCHEDULE_DESIGNATOR in VARCHAR2,
457 X_SERVICE_REQUEST_ID in NUMBER,
458 X_SPACE_CATEGORY_CODE in VARCHAR2,
459 X_OBJECT_VERSION_NUMBER in NUMBER,
460 X_ORGANIZATION_ID in NUMBER,
461 X_DEPARTMENT_ID in NUMBER,
462 X_STATUS_CODE in VARCHAR2,
463 X_START_DATE_TIME in DATE,
464 X_VISIT_TYPE_CODE in VARCHAR2,
465 X_SIMULATION_PLAN_ID in NUMBER,
466 X_ITEM_INSTANCE_ID in NUMBER,
467 X_INVENTORY_ITEM_ID in NUMBER,
468 X_ASSO_PRIMARY_VISIT_ID in NUMBER,
469 X_SIMULATION_DELETE_FLAG in VARCHAR2,
470 X_TEMPLATE_FLAG in VARCHAR2,
471 X_OUT_OF_SYNC_FLAG in VARCHAR2,
472 X_PROJECT_FLAG in VARCHAR2,
473 X_PROJECT_ID in NUMBER,
474 X_VISIT_NUMBER in NUMBER,
475 X_ITEM_ORGANIZATION_ID in NUMBER,
476 X_ATTRIBUTE_CATEGORY in VARCHAR2,
477 X_ATTRIBUTE1 in VARCHAR2,
478 X_ATTRIBUTE2 in VARCHAR2,
479 X_ATTRIBUTE3 in VARCHAR2,
480 X_ATTRIBUTE4 in VARCHAR2,
481 X_ATTRIBUTE5 in VARCHAR2,
482 X_ATTRIBUTE6 in VARCHAR2,
483 X_ATTRIBUTE7 in VARCHAR2,
484 X_ATTRIBUTE8 in VARCHAR2,
485 X_ATTRIBUTE9 in VARCHAR2,
486 X_ATTRIBUTE10 in VARCHAR2,
487 X_ATTRIBUTE11 in VARCHAR2,
488 X_ATTRIBUTE12 in VARCHAR2,
489 X_ATTRIBUTE13 in VARCHAR2,
490 X_ATTRIBUTE14 in VARCHAR2,
491 X_ATTRIBUTE15 in VARCHAR2,
492 X_VISIT_NAME in VARCHAR2,
493 X_DESCRIPTION in VARCHAR2,
494 X_INV_LOCATOR_ID in NUMBER, --Added by sowsubra
495 X_LAST_UPDATE_DATE in DATE,
496 X_LAST_UPDATED_BY in NUMBER,
497 X_LAST_UPDATE_LOGIN in NUMBER
498 ) is
499 begin
500 update AHL_VISITS_B set
501 ESTIMATED_PRICE = X_ESTIMATED_PRICE,
502 PRIORITY_CODE = X_PRIORITY_CODE,
503 PROJECT_TEMPLATE_ID = X_PROJECT_TEMPLATE_ID,
504 UNIT_SCHEDULE_ID = X_UNIT_SCHEDULE_ID,
505 ACTUAL_PRICE = X_ACTUAL_PRICE,
506 OUTSIDE_PARTY_FLAG = X_OUTSIDE_PARTY_FLAG,
507 ANY_TASK_CHG_FLAG = X_ANY_TASK_CHG_FLAG,
508 PRICE_LIST_ID = X_PRICE_LIST_ID,
509 CLOSE_DATE_TIME = X_CLOSE_DATE_TIME,
510 SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR,
511 SERVICE_REQUEST_ID = X_SERVICE_REQUEST_ID,
512 SPACE_CATEGORY_CODE = X_SPACE_CATEGORY_CODE,
513 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
514 ORGANIZATION_ID = X_ORGANIZATION_ID,
515 DEPARTMENT_ID = X_DEPARTMENT_ID,
516 STATUS_CODE = X_STATUS_CODE,
517 START_DATE_TIME = X_START_DATE_TIME,
518 VISIT_TYPE_CODE = X_VISIT_TYPE_CODE,
519 SIMULATION_PLAN_ID = X_SIMULATION_PLAN_ID,
520 ITEM_INSTANCE_ID = X_ITEM_INSTANCE_ID,
521 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
522 ASSO_PRIMARY_VISIT_ID = X_ASSO_PRIMARY_VISIT_ID,
523 SIMULATION_DELETE_FLAG = X_SIMULATION_DELETE_FLAG,
524 TEMPLATE_FLAG = X_TEMPLATE_FLAG,
525 OUT_OF_SYNC_FLAG = X_OUT_OF_SYNC_FLAG,
526 PROJECT_FLAG = X_PROJECT_FLAG,
527 PROJECT_ID = X_PROJECT_ID,
528 VISIT_NUMBER = X_VISIT_NUMBER,
529 ITEM_ORGANIZATION_ID = X_ITEM_ORGANIZATION_ID,
530 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
531 ATTRIBUTE1 = X_ATTRIBUTE1,
532 ATTRIBUTE2 = X_ATTRIBUTE2,
533 ATTRIBUTE3 = X_ATTRIBUTE3,
534 ATTRIBUTE4 = X_ATTRIBUTE4,
535 ATTRIBUTE5 = X_ATTRIBUTE5,
536 ATTRIBUTE6 = X_ATTRIBUTE6,
537 ATTRIBUTE7 = X_ATTRIBUTE7,
538 ATTRIBUTE8 = X_ATTRIBUTE8,
539 ATTRIBUTE9 = X_ATTRIBUTE9,
540 ATTRIBUTE10 = X_ATTRIBUTE10,
541 ATTRIBUTE11 = X_ATTRIBUTE11,
542 ATTRIBUTE12 = X_ATTRIBUTE12,
543 ATTRIBUTE13 = X_ATTRIBUTE13,
544 ATTRIBUTE14 = X_ATTRIBUTE14,
545 ATTRIBUTE15 = X_ATTRIBUTE15,
546 INV_LOCATOR_ID = X_INV_LOCATOR_ID, --Added by sowsubra
547 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
548 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
549 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
550 where VISIT_ID = X_VISIT_ID;
551
552 if (sql%notfound) then
553 raise no_data_found;
554 end if;
555
556 update AHL_VISITS_TL set
557 VISIT_NAME = X_VISIT_NAME,
558 DESCRIPTION = X_DESCRIPTION,
559 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
560 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
561 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
562 SOURCE_LANG = userenv('LANG')
563 where VISIT_ID = X_VISIT_ID
564 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
565
566 if (sql%notfound) then
567 raise no_data_found;
568 end if;
569 end UPDATE_ROW;
570
571 procedure DELETE_ROW (
572 X_VISIT_ID in NUMBER
573 ) is
574 begin
575 delete from AHL_VISITS_TL
576 where VISIT_ID = X_VISIT_ID;
577
578 if (sql%notfound) then
579 raise no_data_found;
580 end if;
581
582 delete from AHL_VISITS_B
583 where VISIT_ID = X_VISIT_ID;
584
585 if (sql%notfound) then
586 raise no_data_found;
587 end if;
588 end DELETE_ROW;
589
590 procedure ADD_LANGUAGE
591 is
592 begin
593 delete from AHL_VISITS_TL T
594 where not exists
595 (select NULL
596 from AHL_VISITS_B B
597 where B.VISIT_ID = T.VISIT_ID
598 );
599
600 update AHL_VISITS_TL T set (
601 VISIT_NAME,
602 DESCRIPTION
603 ) = (select
604 B.VISIT_NAME,
605 B.DESCRIPTION
606 from AHL_VISITS_TL B
607 where B.VISIT_ID = T.VISIT_ID
608 and B.LANGUAGE = T.SOURCE_LANG)
609 where (
610 T.VISIT_ID,
611 T.LANGUAGE
612 ) in (select
613 SUBT.VISIT_ID,
614 SUBT.LANGUAGE
615 from AHL_VISITS_TL SUBB, AHL_VISITS_TL SUBT
616 where SUBB.VISIT_ID = SUBT.VISIT_ID
617 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
618 and (SUBB.VISIT_NAME <> SUBT.VISIT_NAME
619 or (SUBB.VISIT_NAME is null and SUBT.VISIT_NAME is not null)
620 or (SUBB.VISIT_NAME is not null and SUBT.VISIT_NAME is null)
621 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
622 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
623 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
624 ));
625
626 insert into AHL_VISITS_TL (
627 VISIT_ID,
628 LAST_UPDATE_DATE,
629 LAST_UPDATED_BY,
630 CREATION_DATE,
631 CREATED_BY,
632 LAST_UPDATE_LOGIN,
633 DESCRIPTION,
634 VISIT_NAME,
635 LANGUAGE,
636 SOURCE_LANG
637 ) select /*+ ORDERED */
638 B.VISIT_ID,
639 B.LAST_UPDATE_DATE,
640 B.LAST_UPDATED_BY,
641 B.CREATION_DATE,
642 B.CREATED_BY,
643 B.LAST_UPDATE_LOGIN,
644 B.DESCRIPTION,
645 B.VISIT_NAME,
646 L.LANGUAGE_CODE,
647 B.SOURCE_LANG
648 from AHL_VISITS_TL B, FND_LANGUAGES L
649 where L.INSTALLED_FLAG in ('I', 'B')
650 and B.LANGUAGE = userenv('LANG')
651 and not exists
652 (select NULL
653 from AHL_VISITS_TL T
654 where T.VISIT_ID = B.VISIT_ID
655 and T.LANGUAGE = L.LANGUAGE_CODE);
656 end ADD_LANGUAGE;
657
658 end AHL_VISITS_PKG;
[Home] [Help]