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