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