[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_REVISION_PVT
Source
1 PACKAGE BODY AHL_FMP_MR_REVISION_PVT AS
2 /* $Header: AHLVMRRB.pls 120.5.12010000.2 2008/12/29 01:03:58 sracha ship $ */
3
4 G_PKG_NAME VARCHAR2(30):='AHL_FMP_MR_REVISION_PVT';
5 G_DEBUG VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
6 G_APPLN_USAGE VARCHAR2(30) :=LTRIM(RTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
7
8 TYPE TEMP_MR_ROUTE_REC IS RECORD
9 (
10 OLD_MR_ROUTE_ID NUMBER,
11 NEW_MR_ROUTE_ID NUMBER
12 );
13
14 TYPE TEMP_MR_ROUTE_TBL IS TABLE OF TEMP_MR_ROUTE_REC INDEX BY BINARY_INTEGER;
15
16
17
18 PROCEDURE VALIDATE_MR_REV
19 (
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 p_source_mr_header_id IN NUMBER
24 )
25 AS
26 CURSOR CheckAnyExistingMr(C_TITLE VARCHAR2)
27 is
28 Select count(title)
29 From AHL_MR_HEADERS_APP_V
30 Where title=C_TITLE
31 And (MR_STATUS_CODE='DRAFT'
32 OR MR_STATUS_CODE='APPROVAL_REJECTED'
33 OR MR_STATUS_CODE='APPROVAL_PENDING')
34 AND MR_HEADER_ID > p_source_mr_header_id ;
35
36 Cursor CheckCurrentlyActive(C_TITLE VARCHAR2,
37 C_MR_HEADER_ID NUMBER,
38 C_VERSION_NUMBER NUMBER)
39 Is
40 Select count(*)
41 From ahl_mr_headers_APP_V
42 Where title=C_TITLE
43 -- And mr_status_code='COMPLETE'
44 And mr_header_id >C_MR_HEADER_ID
45 And version_number>C_VERSION_NUMBER;
46
47 l_status VARCHAR2(30);
48 l_appln_code AHL_MR_HEADERS_B.APPLICATION_USG_CODE%TYPE;
49 l_title AHL_MR_HEADERS_B.TITLE%TYPE;
50 l_version_number NUMBER:=0;
51 l_counter NUMBER:=0;
52 l_check_flag VARCHAR2(1):='N';
53 BEGIN
54 x_return_status:=fnd_api.g_ret_sts_success;
55
56
57 IF G_DEBUG='Y' THEN
58 AHL_DEBUG_PUB.enable_debug;
59 END IF;
60
61 IF g_appln_usage is null
62 THEN
63 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
64 FND_MSG_PUB.ADD;
65 RETURN;
66 ELSIF (g_appln_usage = 'PM')
67 THEN
68 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_MRR_PM_INSTALL');
69 FND_MSG_PUB.ADD;
70 RETURN;
71 END IF;
72
73
74 Select MR_STATUS_CODE,TITLE,VERSION_NUMBER
75 into l_status,l_title,l_version_number
76 From ahl_mr_headers_app_v
77 Where mr_header_id=p_source_mr_header_id;
78
79
80 IF SQL%ROWCOUNT>0
81 THEN
82 IF l_status<>'COMPLETE'
83 THEN
84 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_CANNOT_CREATE_REV');
85 FND_MESSAGE.SET_TOKEN('RECORD',l_title,false);
86 FND_MSG_PUB.ADD;
87 l_check_flag:='N';
88 ELSE
89 l_check_flag:='Y';
90 END IF;
91
92 IF l_check_flag='Y'
93 THEN
94
95 OPEN CheckAnyExistingMr(upper(l_title));
96 FETCH CheckAnyExistingMr INTO l_counter;
97 IF CheckAnyExistingMr%FOUND
98 THEN
99 IF l_counter>0
100 THEN
101 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_REVISION_CREATED');
102 FND_MESSAGE.SET_TOKEN('RECORD',l_title,false);
103 FND_MSG_PUB.ADD;
104 l_check_flag:='N';
105 END IF;
106 END IF;
107 CLOSE CheckAnyExistingMr;
108 END IF;
109 END IF;
110
111 IF l_check_flag='Y'
112 THEN
113 OPEN CheckCurrentlyActive(l_title,p_source_mr_header_id,l_version_number);
114 FETCH CheckCurrentlyActive INTO l_counter;
115 IF CheckCurrentlyActive%FOUND
116 THEN
117 IF l_counter>0
118 THEN
119 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_NOT_LATEST');
120 FND_MSG_PUB.ADD;
121 END IF;
122 END IF;
123 CLOSE CheckCurrentlyActive;
124 END IF;
125
126
127 EXCEPTION
128 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
129 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
131 p_count => x_msg_count,
132 p_data => x_msg_data);
133 IF G_DEBUG='Y' THEN
134 AHL_DEBUG_PUB.disable_debug;
135 END IF;
136 WHEN FND_API.G_EXC_ERROR THEN
137 X_return_status := FND_API.G_RET_STS_ERROR;
138 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
139 p_count => x_msg_count,
140 p_data => X_msg_data);
141 IF G_DEBUG='Y' THEN
142 AHL_DEBUG_PUB.disable_debug;
143 END IF;
144
145 WHEN OTHERS THEN
146 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
148 THEN
149 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_REVISION_PVT',
150 p_procedure_name => 'VALIDATE_MR_REV',
151 p_error_text => SUBSTR(SQLERRM,1,240));
152 END IF;
153 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
154 p_count => x_msg_count,
155 p_data => X_msg_data);
156
157 IF G_DEBUG='Y' THEN
158 AHL_DEBUG_PUB.disable_debug;
159 END IF;
160
161 END;
162
163 PROCEDURE CREATE_MR_REVISION
164 (
165 p_api_version IN NUMBER:=1.0,
166 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
167 p_commit IN VARCHAR2:=FND_API.G_FALSE,
168 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
169 p_default IN VARCHAR2:=FND_API.G_FALSE,
170 p_module_type IN VARCHAR2,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 p_source_mr_header_id IN NUMBER,
175 x_new_mr_header_id OUT NOCOPY NUMBER
176 )
177 AS
178 CURSOR LckGetHeader
179 is
180 select mr_header_id
181 from AHL_MR_HEADERS_APP_V
182 where mr_header_id=p_source_mr_header_id;
183
184 CURSOR CurGetHeaderdet
185 Is
186 SELECT
187 MR_HEADER_ID,
188 OBJECT_VERSION_NUMBER,
189 LAST_UPDATE_DATE,
190 LAST_UPDATED_BY,
191 CREATION_DATE,
192 CREATED_BY,
193 LAST_UPDATE_LOGIN,
194 TITLE,
195 VERSION_NUMBER,
196 PRECEDING_MR_HEADER_ID,
197 CATEGORY_CODE,
198 SERVICE_TYPE_CODE,
199 MR_STATUS_CODE,
200 IMPLEMENT_STATUS_CODE,
201 REPETITIVE_FLAG,
202 SHOW_REPETITIVE_CODE,
203 WHICHEVER_FIRST_CODE,
204 COPY_ACCOMPLISHMENT_FLAG,
205 PROGRAM_TYPE_CODE,
206 PROGRAM_SUBTYPE_CODE,
207 EFFECTIVE_FROM,
208 EFFECTIVE_TO,
209 REVISION,
210 BILLING_ITEM_ID,
211 BILLING_ORG_ID,
212 SPACE_CATEGORY_CODE,
213 QA_INSPECTION_TYPE_CODE,
214 DESCRIPTION,
215 COMMENTS,
216 SERVICE_REQUEST_TEMPLATE_ID,
217 TYPE_CODE,
218 DOWN_TIME,
219 UOM_CODE,
220 ATTRIBUTE_CATEGORY,
221 ATTRIBUTE1,
222 ATTRIBUTE2,
223 ATTRIBUTE3,
224 ATTRIBUTE4,
225 ATTRIBUTE5,
226 ATTRIBUTE6,
227 ATTRIBUTE7,
228 ATTRIBUTE8,
229 ATTRIBUTE9,
230 ATTRIBUTE10,
231 ATTRIBUTE11,
232 ATTRIBUTE12,
233 ATTRIBUTE13,
234 ATTRIBUTE14,
235 ATTRIBUTE15,
236 AUTO_SIGNOFF_FLAG,
237 COPY_INIT_ACCOMPL_FLAG,
238 COPY_DEFERRALS_FLAG,
239 APPLICATION_USG_CODE
240 from AHL_MR_HEADERS_APP_V
241 where mr_header_id=p_source_mr_header_id;
242
243 CURSOR CurGetDocTitledet
244 is
245 select
246 A.DOC_TITLE_ASSO_ID,
247 A.OBJECT_VERSION_NUMBER,
248 A.LAST_UPDATE_DATE,
249 A.LAST_UPDATED_BY,
250 A.CREATION_DATE,
251 A.CREATED_BY,
252 A.LAST_UPDATE_LOGIN,
253 A.DOC_REVISION_ID,
254 A.ASO_OBJECT_TYPE_CODE,
255 A.ASO_OBJECT_ID,
256 A.DOCUMENT_ID,
257 A.USE_LATEST_REV_FLAG,
258 A.SERIAL_NO,
259 A.SECURITY_GROUP_ID,
260 A.ATTRIBUTE_CATEGORY,
261 A.ATTRIBUTE1,
262 A.ATTRIBUTE2,
263 A.ATTRIBUTE3,
264 A.ATTRIBUTE4,
265 A.ATTRIBUTE5,
266 A.ATTRIBUTE6,
267 A.ATTRIBUTE7,
268 A.ATTRIBUTE8,
269 A.ATTRIBUTE9,
270 A.ATTRIBUTE10,
271 A.ATTRIBUTE11,
272 A.ATTRIBUTE12,
273 A.ATTRIBUTE13,
274 A.ATTRIBUTE14,
275 A.ATTRIBUTE15,
276 A.SOURCE_REF_CODE,
277 b.chapter,
278 b.section,
279 b.subject,
280 b.page,
281 b.figure,
282 b.note
283 from AHL_DOC_TITLE_ASSOS_B A,AHL_DOC_TITLE_ASSOS_TL B
284 where A.ASO_OBJECT_TYPE_CODE='MR'
285 AND A.ASO_OBJECT_ID=p_source_mr_header_id
286 and A.doc_title_asso_id=B.doc_title_asso_id
287 AND B.LANGUAGE=USERENV('LANG')
288 AND A.ASO_OBJECT_ID NOT IN (SELECT DOCUMENT_ID
289 FROM AHL_DOC_REVISIONS_VL
290 WHERE DOCUMENT_ID=A.ASO_OBJECT_ID
291 AND NVL(REVISION_STATUS_CODE,'CURRENT')='OBSOLETE');
292
293 l_doc_title_asso_id NUMBER:=0;
294
295 -- Routes
296 CURSOR CurGetRoutedet
297 IS
298 SELECT
299 MR_ROUTE_ID,
300 OBJECT_VERSION_NUMBER,
301 LAST_UPDATE_DATE,
302 LAST_UPDATED_BY,
303 CREATION_DATE,
304 CREATED_BY,
305 LAST_UPDATE_LOGIN,
306 MR_HEADER_ID,
307 ROUTE_ID,
308 SECURITY_GROUP_ID,
309 ATTRIBUTE_CATEGORY,
310 ATTRIBUTE1,
311 ATTRIBUTE2,
312 ATTRIBUTE3,
313 ATTRIBUTE4,
314 ATTRIBUTE5,
315 ATTRIBUTE6,
316 ATTRIBUTE7,
317 ATTRIBUTE8,
318 ATTRIBUTE9,
319 ATTRIBUTE10,
320 ATTRIBUTE11,
321 ATTRIBUTE12,
322 ATTRIBUTE13,
323 ATTRIBUTE14,
324 ATTRIBUTE15,
325 STAGE
326 FROM AHL_MR_ROUTES A
327 WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID
328 AND ROUTE_ID IN (SELECT ROUTE_ID
329 FROM AHL_ROUTES_B
330 WHERE ROUTE_ID=A.ROUTE_ID
331 AND NVL(END_DATE_ACTIVE,sysdate+1)>SYSDATE
332 AND REVISION_STATUS_CODE='COMPLETE'
333 );
334
335 l_TEMP_MR_ROUTE_TBL TEMP_MR_ROUTE_TBL;
336
337 -- Route Sequences
338 CURSOR CurGetRouteSeqDet(C_MR_ROUTE_ID NUMBER)
339 iS
340 SELECT MR_ROUTE_SEQUENCE_ID,
341 OBJECT_VERSION_NUMBER,
342 LAST_UPDATE_DATE,
343 LAST_UPDATED_BY,
344 CREATION_DATE,
345 CREATED_BY,
346 LAST_UPDATE_LOGIN,
347 MR_ROUTE_ID,
348 RELATED_MR_ROUTE_ID,
349 SEQUENCE_CODE,
350 ATTRIBUTE_CATEGORY,
351 ATTRIBUTE1,
352 ATTRIBUTE2,
353 ATTRIBUTE3,
354 ATTRIBUTE4,
355 ATTRIBUTE5,
356 ATTRIBUTE6,
357 ATTRIBUTE7,
358 ATTRIBUTE8,
359 ATTRIBUTE9,
360 ATTRIBUTE10,
361 ATTRIBUTE11,
362 ATTRIBUTE12,
363 ATTRIBUTE13,
364 ATTRIBUTE14,
365 ATTRIBUTE15
366 FROM AHL_MR_ROUTE_SEQUENCES C
367 WHERE MR_ROUTE_ID=C_MR_ROUTE_ID
368 AND EXISTS
369 (SELECT MR_ROUTE_ID
370 FROM AHL_MR_ROUTES A
371 WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID
372 AND MR_ROUTE_ID=C.RELATED_MR_ROUTE_ID
373 AND ROUTE_ID IN (SELECT ROUTE_ID
374 FROM AHL_ROUTES_B
375 WHERE ROUTE_ID=A.ROUTE_ID
376 AND NVL(END_DATE_ACTIVE,sysdate+1)>SYSDATE
377 AND REVISION_STATUS_CODE='COMPLETE'
378 )
379 );
380
381
382 l_mr_route_seq_rec CurGetRouteSeqDet%rowtype;
383 l_seq_mr_route_id NUMBER:=0;
384 l_seq_rel_mr_route_id NUMBER:=0;
385 l_mr_route_index NUMBER:=0;
386
387
388 -- Effectivity
389 CURSOR CurGetEffectDet
390 IS
391 SELECT
392 MR_EFFECTIVITY_ID,
393 OBJECT_VERSION_NUMBER,
394 LAST_UPDATE_DATE,
395 LAST_UPDATED_BY,
396 CREATION_DATE,
397 CREATED_BY,
398 LAST_UPDATE_LOGIN,
399 MR_HEADER_ID,
400 NAME,
401 THRESHOLD_DATE,
402 INVENTORY_ITEM_ID,
403 INVENTORY_ORG_ID,
404 RELATIONSHIP_ID,
405 PC_NODE_ID,
406 DEFAULT_FLAG,
407 PROGRAM_DURATION,
408 PROGRAM_DURATION_UOM_CODE,
409 SECURITY_GROUP_ID,
410 ATTRIBUTE_CATEGORY,
411 ATTRIBUTE1,
412 ATTRIBUTE2,
413 ATTRIBUTE3,
414 ATTRIBUTE4,
415 ATTRIBUTE5,
416 ATTRIBUTE6,
417 ATTRIBUTE7,
418 ATTRIBUTE8,
419 ATTRIBUTE9,
420 ATTRIBUTE10,
421 ATTRIBUTE11,
422 ATTRIBUTE12,
423 ATTRIBUTE13,
424 ATTRIBUTE14,
425 ATTRIBUTE15
426 FROM AHL_MR_EFFECTIVITIES_APP_V
427 WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID;
428
429 CURSOR CurGetMrIntervals(C_MR_EFFECTIVITY_ID NUMBER)
430 IS
431 SELECT
432 MR_INTERVAL_ID,
433 OBJECT_VERSION_NUMBER,
434 LAST_UPDATE_DATE,
435 LAST_UPDATED_BY,
436 CREATION_DATE,
437 CREATED_BY,
438 LAST_UPDATE_LOGIN,
439 MR_EFFECTIVITY_ID,
440 COUNTER_ID,
441 INTERVAL_VALUE,
442 EARLIEST_DUE_VALUE,
443 START_VALUE,
444 STOP_VALUE,
445 START_DATE,
446 STOP_DATE,
447 TOLERANCE_BEFORE,
448 TOLERANCE_AFTER,
449 SECURITY_GROUP_ID,
450 ATTRIBUTE_CATEGORY,
451 ATTRIBUTE1,
452 ATTRIBUTE2,
453 ATTRIBUTE3,
454 ATTRIBUTE4,
455 ATTRIBUTE5,
456 ATTRIBUTE6,
457 ATTRIBUTE7,
458 ATTRIBUTE8,
459 ATTRIBUTE9,
460 ATTRIBUTE10,
461 ATTRIBUTE11,
462 ATTRIBUTE12,
463 ATTRIBUTE13,
464 ATTRIBUTE14,
465 ATTRIBUTE15,
466 RESET_VALUE
467 FROM AHL_MR_INTERVALS_APP_V
468 WHERE MR_EFFECTIVITY_ID=C_MR_EFFECTIVITY_ID;
469 l_interval_rec CurGetMrIntervals%rowtype;
470
471
472 -- Effectivity Details
473 CURSOR CurGetEffectDTLS(C_MR_EFFECTIVITY_ID NUMBER)
474 IS
475 SELECT
476 MR_EFFECTIVITY_DETAIL_ID,
477 OBJECT_VERSION_NUMBER,
478 LAST_UPDATE_DATE,
479 LAST_UPDATED_BY,
480 CREATION_DATE,
481 CREATED_BY,
482 LAST_UPDATE_LOGIN,
483 MR_EFFECTIVITY_ID,
484 EXCLUDE_FLAG,
485 MANUFACTURER_ID,
486 COUNTRY_CODE,
487 SERIAL_NUMBER_FROM,
488 SERIAL_NUMBER_TO,
489 MANUFACTURE_DATE_FROM,
490 MANUFACTURE_DATE_TO,
491 SECURITY_GROUP_ID,
492 ATTRIBUTE_CATEGORY,
493 ATTRIBUTE1,
494 ATTRIBUTE2,
495 ATTRIBUTE3,
496 ATTRIBUTE4,
497 ATTRIBUTE5,
498 ATTRIBUTE6,
499 ATTRIBUTE7,
500 ATTRIBUTE8,
501 ATTRIBUTE9,
502 ATTRIBUTE10,
503 ATTRIBUTE11,
504 ATTRIBUTE12,
505 ATTRIBUTE13,
506 ATTRIBUTE14,
507 ATTRIBUTE15
508 FROM AHL_MR_EFFECTIVITY_DTLS_APP_V
509 WHERE MR_EFFECTIVITY_ID=C_MR_EFFECTIVITY_ID;
510
511 CURSOR CurGetRelationDet
512 IS
513 SELECT
514 MR_RELATIONSHIP_ID,
515 OBJECT_VERSION_NUMBER,
516 LAST_UPDATE_DATE,
517 LAST_UPDATED_BY,
518 CREATION_DATE,
519 CREATED_BY,
520 LAST_UPDATE_LOGIN,
521 MR_HEADER_ID,
522 RELATED_MR_HEADER_ID,
523 RELATIONSHIP_CODE,
524 SECURITY_GROUP_ID,
525 ATTRIBUTE_CATEGORY,
526 ATTRIBUTE1,
527 ATTRIBUTE2,
528 ATTRIBUTE3,
529 ATTRIBUTE4,
530 ATTRIBUTE5,
531 ATTRIBUTE6,
532 ATTRIBUTE7,
533 ATTRIBUTE8,
534 ATTRIBUTE9,
535 ATTRIBUTE10,
536 ATTRIBUTE11,
537 ATTRIBUTE12,
538 ATTRIBUTE13,
539 ATTRIBUTE14,
540 ATTRIBUTE15
541 FROM AHL_MR_RELATIONSHIPS_APP_V A
542 WHERE (MR_HEADER_ID=P_SOURCE_MR_HEADER_ID or RELATED_MR_HEADER_ID=P_SOURCE_MR_HEADER_ID)
543 AND EXISTS(SELECT MR_HEADER_ID
544 FROM AHL_MR_HEADERS_APP_V
545 WHERE ( MR_HEADER_ID=A.MR_HEADER_ID
546 OR MR_HEADER_ID=A.RELATED_MR_HEADER_ID)
547 AND MR_STATUS_CODE<>'TERMINATED'
548 AND NVL(EFFECTIVE_TO,SYSDATE+1) >SYSDATE);
549
550
551 l_rel_rec CurGetRelationDet%rowtype;
552
553 l_mr_effect_dtls_rec CurGetEffectDTLS%rowtype;
554
555 Cursor CurGetMrVisitTypes
556 Is
557 SELECT
558 MR_VISIT_TYPE_ID,
559 OBJECT_VERSION_NUMBER,
560 LAST_UPDATE_DATE,
561 LAST_UPDATED_BY,
562 CREATION_DATE,
563 CREATED_BY,
564 LAST_UPDATE_LOGIN,
565 MR_VISIT_TYPE_CODE,
566 MR_HEADER_ID,
567 SECURITY_GROUP_ID,
568 ATTRIBUTE_CATEGORY,
569 ATTRIBUTE1,
570 ATTRIBUTE2,
571 ATTRIBUTE3,
572 ATTRIBUTE4,
573 ATTRIBUTE5,
574 ATTRIBUTE6,
575 ATTRIBUTE7,
576 ATTRIBUTE8,
577 ATTRIBUTE9,
578 ATTRIBUTE10,
579 ATTRIBUTE11,
580 ATTRIBUTE12,
581 ATTRIBUTE13,
582 ATTRIBUTE14,
583 ATTRIBUTE15
584 FROM AHL_MR_VISIT_TYPES
585 WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID;
586
587 l_mrvsttype_rec CurGetMrVisitTypes%rowtype;
588 l_row_id VARCHAR2(30);
589 l_mr_relationship_id NUMBER:=0;
590 l_old_mr_route_id NUMBER:=0;
591 l_new_mr_route_id NUMBER:=0;
592 l_new_mr_route_seq_id NUMBER:=0;
593 l_new_mr_effectivity_id NUMBER:=0;
594 l_old_mr_effectivity_id NUMBER:=0;
595 l_new_mr_effectivity_dtl_id NUMBER:=0;
596 l_old_mr_effectivity_dtl_id NUMBER:=0;
597 l_version_number NUMBER:=0;
598
599 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MR_REVISION';
600 l_api_version CONSTANT NUMBER := 1.0;
601 l_msg_count NUMBER;
602 l_msg_data VARCHAR2(2000);
603 l_date DATE;
604 BEGIN
605
606 SAVEPOINT CREATE_MR_REVISION_PVT;
607
608 IF G_DEBUG='Y' THEN
609 AHL_DEBUG_PUB.enable_debug;
610 AHL_DEBUG_PUB.debug( ' START CREATE MR REVISION ');
611 END IF;
612
613 -- Standard call to check for call compatibility.
614
615 IF FND_API.to_boolean(p_init_msg_list) THEN
616 FND_MSG_PUB.initialize;
617 END IF;
618
619 -- Initialize API return status to success
620
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
623 -- Initialize message list if p_init_msg_list is set to TRUE.
624 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
625 p_api_version,
626 l_api_name,G_PKG_NAME) THEN
627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
628 END IF;
629 --Start of API Body
630
631 VALIDATE_MR_REV
632 (
633 x_return_status =>x_return_Status,
634 x_msg_count =>l_msg_count,
635 x_msg_data =>l_msg_data,
636 p_source_mr_header_id =>p_source_mr_header_id
637 );
638
639 l_msg_count := FND_MSG_PUB.count_msg;
640
641 IF l_msg_count > 0 THEN
642 X_msg_count := l_msg_count;
643 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646
647
648 --Start of API Body
649
650
651 for l_mr_header_rec in CurGetHeaderdet
652 loop
653 l_date:=sysdate;
654 IF l_mr_header_Rec.EFFECTIVE_FROM>SYSDATE
655 THEN
656 l_date:=l_mr_header_Rec.EFFECTIVE_FROM;
657 END IF;
658
659 l_version_number:=l_mr_header_Rec.version_number + 1;
660 AHL_MR_HEADERS_PKG.INSERT_ROW (
661 X_MR_HEADER_ID =>x_new_mr_header_id,
662 X_OBJECT_VERSION_NUMBER =>1,
663 X_CATEGORY_CODE =>l_mr_header_Rec.CATEGORY_CODE,
664 X_SERVICE_TYPE_CODE =>l_mr_header_Rec.SERVICE_TYPE_CODE,
665 X_MR_STATUS_CODE =>'DRAFT',
666 X_IMPLEMENT_STATUS_CODE =>l_mr_header_Rec.IMPLEMENT_STATUS_CODE,
667 X_REPETITIVE_FLAG =>l_mr_header_Rec.REPETITIVE_FLAG,
668 X_SHOW_REPETITIVE_CODE =>l_mr_header_Rec.SHOW_REPETITIVE_CODE,
669 X_WHICHEVER_FIRST_CODE =>l_mr_header_Rec.WHICHEVER_FIRST_CODE,
670 X_COPY_ACCOMPLISHMENT_FLAG=> 'Y',-- defaulting l_mr_header_Rec.COPY_ACCOMPLISHMENT_FLAG,
671 X_PROGRAM_TYPE_CODE =>l_mr_header_Rec.PROGRAM_TYPE_CODE ,
672 X_PROGRAM_SUBTYPE_CODE =>l_mr_header_Rec.PROGRAM_SUBTYPE_CODE,
673 X_EFFECTIVE_FROM =>l_date,
674 X_EFFECTIVE_TO =>NULL,
675 X_REVISION =>l_mr_header_Rec.REVISION,
676 X_ATTRIBUTE_CATEGORY =>l_mr_header_Rec.ATTRIBUTE_CATEGORY,
677 X_ATTRIBUTE1 =>l_mr_header_Rec.ATTRIBUTE1,
678 X_ATTRIBUTE2 =>l_mr_header_Rec.ATTRIBUTE2,
679 X_ATTRIBUTE3 =>l_mr_header_Rec.ATTRIBUTE3,
680 X_ATTRIBUTE4 =>l_mr_header_Rec.ATTRIBUTE4,
681 X_ATTRIBUTE5 =>l_mr_header_Rec.ATTRIBUTE5,
682 X_ATTRIBUTE6 =>l_mr_header_Rec.ATTRIBUTE6,
683 X_ATTRIBUTE7 =>l_mr_header_Rec.ATTRIBUTE7,
684 X_ATTRIBUTE8 =>l_mr_header_Rec.ATTRIBUTE8,
685 X_ATTRIBUTE9 =>l_mr_header_Rec.ATTRIBUTE9,
686 X_ATTRIBUTE10 =>l_mr_header_Rec.ATTRIBUTE10,
687 X_ATTRIBUTE11 =>l_mr_header_Rec.ATTRIBUTE11,
688 X_ATTRIBUTE12 =>l_mr_header_Rec.ATTRIBUTE12,
689 X_ATTRIBUTE13 =>l_mr_header_Rec.ATTRIBUTE13,
690 X_ATTRIBUTE14 =>l_mr_header_Rec.ATTRIBUTE14,
691 X_ATTRIBUTE15 =>l_mr_header_Rec.ATTRIBUTE15,
692 X_TITLE =>l_mr_header_Rec.TITLE,
693 X_VERSION_NUMBER =>l_version_number,
694 X_PRECEDING_MR_HEADER_ID=>l_mr_header_Rec.PRECEDING_MR_HEADER_ID,
695 X_SERVICE_REQUEST_TEMPLATE_ID=>l_mr_header_Rec.SERVICE_REQUEST_TEMPLATE_ID,
696 X_TYPE_CODE =>l_mr_header_Rec.TYPE_CODE,
697 X_DOWN_TIME =>l_mr_header_Rec.DOWN_TIME,
698 X_UOM_CODE =>l_mr_header_Rec.UOM_CODE,
699 X_DESCRIPTION =>l_mr_header_Rec.DESCRIPTION,
700 X_COMMENTS =>l_mr_header_Rec.COMMENTS,
701 X_SPACE_CATEGORY_CODE =>l_mr_header_Rec.SPACE_CATEGORY_CODE,
702 X_QA_INSPECTION_TYPE_CODE =>l_mr_header_Rec.QA_INSPECTION_TYPE_CODE,
703 X_BILLING_ITEM_ID =>l_mr_header_Rec.BILLING_ITEM_ID,
704 X_AUTO_SIGNOFF_FLAG =>l_mr_header_Rec.AUTO_SIGNOFF_FLAG,
705 -- defaulting to Yes for these attributes when new revision is created
706 X_COPY_INIT_ACCOMPL_FLAG =>'Y',
707 X_COPY_DEFERRALS_FLAG =>'Y',
708 X_CREATION_DATE =>sysdate,
709 X_CREATED_BY =>fnd_global.user_id,
710 X_LAST_UPDATE_DATE =>sysdate,
711 X_LAST_UPDATED_BY =>fnd_global.user_id,
712 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
713 end loop;
714
715
716 for l_association_rec in CurGetDocTitledet
717 loop
718
719 SELECT AHL_DOC_TITLE_ASSOS_B_S.Nextval INTO
720 l_doc_title_asso_id from DUAL;
721
722
723 AHL_DOC_TITLE_ASSOS_PKG.INSERT_ROW(
724 X_ROWID =>l_row_id,
725 X_DOC_TITLE_ASSO_ID =>l_doc_title_asso_id,
726 X_SERIAL_NO =>l_association_rec.serial_no,
727 X_ATTRIBUTE_CATEGORY =>l_association_rec.attribute_category,
728 X_ATTRIBUTE1 =>l_association_rec.attribute1,
729 X_ATTRIBUTE2 =>l_association_rec.attribute2,
730 X_ATTRIBUTE3 =>l_association_rec.attribute3,
731 X_ATTRIBUTE4 =>l_association_rec.attribute4,
732 X_ATTRIBUTE5 =>l_association_rec.attribute5,
733 X_ATTRIBUTE6 =>l_association_rec.attribute6,
734 X_ATTRIBUTE7 =>l_association_rec.attribute7,
735 X_ATTRIBUTE8 =>l_association_rec.attribute8,
736 X_ATTRIBUTE9 =>l_association_rec.attribute9,
737 X_ATTRIBUTE10 =>l_association_rec.attribute10,
738 X_ATTRIBUTE11 =>l_association_rec.attribute11,
739 X_ATTRIBUTE12 =>l_association_rec.attribute12,
740 X_ATTRIBUTE13 =>l_association_rec.attribute13,
741 X_ATTRIBUTE14 =>l_association_rec.attribute14,
742 X_ATTRIBUTE15 =>l_association_rec.attribute15,
743 X_ASO_OBJECT_TYPE_CODE =>l_association_rec.aso_object_type_code,
744 X_SOURCE_REF_CODE =>l_association_rec.source_ref_code,
745 X_ASO_OBJECT_ID =>x_new_mr_header_id,
746 X_DOCUMENT_ID =>l_association_rec.document_id,
747 X_USE_LATEST_REV_FLAG =>l_association_rec.use_latest_rev_flag,
748 X_DOC_REVISION_ID =>l_association_rec.doc_revision_id,
749 X_OBJECT_VERSION_NUMBER =>1,
750 X_CHAPTER =>l_association_rec.chapter,
751 X_SECTION =>l_association_rec.section,
752 X_SUBJECT =>l_association_rec.subject,
753 X_FIGURE =>l_association_rec.figure,
754 X_PAGE =>l_association_rec.page,
755 X_NOTE =>l_association_rec.note,
756 X_CREATION_DATE =>sysdate,
757 X_CREATED_BY =>fnd_global.user_id,
758 X_LAST_UPDATE_DATE =>sysdate,
759 X_LAST_UPDATED_BY =>fnd_global.user_id,
760 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
761
762 end loop;
763
764 for l_mr_route_rec in CurGetRoutedet
765 loop
766
767
768 l_old_mr_route_id:=l_mr_route_Rec.mr_route_id;
769
770 AHL_MR_ROUTES_PKG.INSERT_ROW (
771 X_MR_ROUTE_ID =>l_new_mr_ROUTE_ID,
772 X_STAGE =>l_mr_route_rec.STAGE,
773 X_OBJECT_VERSION_NUMBER =>1,
774 X_MR_HEADER_ID =>x_new_mr_header_id,
775 X_ROUTE_ID =>l_mr_route_Rec.ROUTE_ID,
776 X_ATTRIBUTE_CATEGORY =>l_mr_route_Rec.ATTRIBUTE_CATEGORY,
777 X_ATTRIBUTE1 =>l_mr_route_Rec.ATTRIBUTE1,
778 X_ATTRIBUTE2 =>l_mr_route_Rec.ATTRIBUTE2,
779 X_ATTRIBUTE3 =>l_mr_route_Rec.ATTRIBUTE3,
780 X_ATTRIBUTE4 =>l_mr_route_Rec.ATTRIBUTE4,
781 X_ATTRIBUTE5 =>l_mr_route_Rec.ATTRIBUTE5,
782 X_ATTRIBUTE6 =>l_mr_route_Rec.ATTRIBUTE6,
783 X_ATTRIBUTE7 =>l_mr_route_Rec.ATTRIBUTE7,
784 X_ATTRIBUTE8 =>l_mr_route_Rec.ATTRIBUTE8,
785 X_ATTRIBUTE9 =>l_mr_route_Rec.ATTRIBUTE9,
786 X_ATTRIBUTE10 =>l_mr_route_Rec.ATTRIBUTE11,
787 X_ATTRIBUTE11 =>l_mr_route_Rec.ATTRIBUTE12,
788 X_ATTRIBUTE12 =>l_mr_route_Rec.ATTRIBUTE13,
789 X_ATTRIBUTE13 =>l_mr_route_Rec.ATTRIBUTE14,
790 X_ATTRIBUTE14 =>l_mr_route_Rec.ATTRIBUTE15,
791 X_ATTRIBUTE15 =>l_mr_route_Rec.ATTRIBUTE15,
792 X_CREATION_DATE =>sysdate,
793 X_CREATED_BY =>fnd_global.user_id,
794 X_LAST_UPDATE_DATE =>sysdate,
795 X_LAST_UPDATED_BY =>fnd_global.user_id,
796 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
797 L_MR_ROUTE_INDEX:=L_MR_ROUTE_INDEX+1;
798 l_temp_mr_route_tbl(L_MR_ROUTE_INDEX).OLD_MR_ROUTE_ID:=l_old_mr_route_id;
799 l_temp_mr_route_tbl(L_MR_ROUTE_INDEX).NEW_MR_ROUTE_ID:=l_new_mr_ROUTE_ID;
800 End loop;
801
802
803 -- Route Sequences
804
805 FOR I IN l_temp_mr_route_tbl.FIRST.. l_temp_mr_route_tbl.LAST
806 LOOP
807
808 OPEN CurGetRouteSeqDet(l_temp_mr_route_tbl(I).OLD_MR_ROUTE_ID);
809 loop
810
811 FETCH CurGetRouteSeqDet INTO l_mr_route_seq_rec;
812 EXIT WHEN CurGetRouteSeqDet%NOTFOUND;
813
814 l_seq_mr_route_id :=l_temp_mr_route_tbl(I).NEW_MR_ROUTE_ID;
815
816 FOR J IN l_temp_mr_route_tbl.FIRST..l_temp_mr_route_tbl.LAST
817 LOOP
818 IF l_temp_mr_route_tbl(J).OLD_MR_ROUTE_ID=l_mr_route_seq_rec.related_mr_route_id
819 THEN
820 l_seq_rel_mr_route_id:=l_temp_mr_route_tbl(J).NEW_MR_ROUTE_ID;
821 EXIT WHEN l_temp_mr_route_tbl(J).OLD_MR_ROUTE_ID=l_mr_route_seq_rec.related_mr_route_id;
822 END IF;
823 END LOOP;
824
825
826 AHL_MR_ROUTE_SEQUENCES_PKG.INSERT_ROW (
827 X_MR_ROUTE_SEQUENCE_ID =>l_new_mr_route_seq_id,
828 X_RELATED_MR_ROUTE_ID =>l_seq_rel_mr_route_id,
829 X_SEQUENCE_CODE =>l_mr_route_seq_rec.SEQUENCE_CODE,
830 X_MR_ROUTE_ID =>l_seq_mr_route_id,
831 X_OBJECT_VERSION_NUMBER =>1,
832 X_ATTRIBUTE_CATEGORY =>l_mr_route_seq_rec.ATTRIBUTE_CATEGORY,
833 X_ATTRIBUTE1 =>l_mr_route_seq_rec.ATTRIBUTE1,
834 X_ATTRIBUTE2 =>l_mr_route_seq_rec.ATTRIBUTE2,
835 X_ATTRIBUTE3 =>l_mr_route_seq_rec.ATTRIBUTE3,
836 X_ATTRIBUTE4 =>l_mr_route_seq_rec.ATTRIBUTE4,
837 X_ATTRIBUTE5 =>l_mr_route_seq_rec.ATTRIBUTE5,
838 X_ATTRIBUTE6 =>l_mr_route_seq_rec.ATTRIBUTE6,
839 X_ATTRIBUTE7 =>l_mr_route_seq_rec.ATTRIBUTE7,
840 X_ATTRIBUTE8 =>l_mr_route_seq_rec.ATTRIBUTE8,
841 X_ATTRIBUTE9 =>l_mr_route_seq_rec.ATTRIBUTE9,
842 X_ATTRIBUTE10 =>l_mr_route_seq_rec.ATTRIBUTE10,
843 X_ATTRIBUTE11 =>l_mr_route_seq_rec.ATTRIBUTE11,
844 X_ATTRIBUTE12 =>l_mr_route_seq_rec.ATTRIBUTE12,
845 X_ATTRIBUTE13 =>l_mr_route_seq_rec.ATTRIBUTE13,
846 X_ATTRIBUTE14 =>l_mr_route_seq_rec.ATTRIBUTE14,
847 X_ATTRIBUTE15 =>l_mr_route_seq_rec.ATTRIBUTE15,
848 X_CREATION_DATE =>sysdate,
849 X_CREATED_BY =>fnd_global.user_id,
850 X_LAST_UPDATE_DATE =>sysdate,
851 X_LAST_UPDATED_BY =>fnd_global.user_id,
852 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
853 end loop;
854 CLOSE CurGetRouteSeqDet;
855
856 END LOOP;
857 -- Effectivity
858 for l_mr_eff_rec in CurGetEffectDet
859 loop
860 l_old_mr_effectivity_id:=l_mr_eff_rec.mr_effectivity_id;
861
862 INSERT INTO AHL_MR_EFFECTIVITIES
863 (
864 MR_EFFECTIVITY_ID,
865 OBJECT_VERSION_NUMBER,
866 LAST_UPDATE_DATE,
867 LAST_UPDATED_BY,
868 CREATION_DATE,
869 CREATED_BY,
870 LAST_UPDATE_LOGIN,
871 MR_HEADER_ID,
872 NAME,
873 THRESHOLD_DATE,
874 INVENTORY_ITEM_ID,
875 INVENTORY_ORG_ID,
876 RELATIONSHIP_ID,
877 PC_NODE_ID,
878 ATTRIBUTE_CATEGORY,
879 ATTRIBUTE1,
880 ATTRIBUTE2,
881 ATTRIBUTE3,
882 ATTRIBUTE4,
883 ATTRIBUTE5,
884 ATTRIBUTE6,
885 ATTRIBUTE7,
886 ATTRIBUTE8,
887 ATTRIBUTE9,
888 ATTRIBUTE10,
889 ATTRIBUTE11,
890 ATTRIBUTE12,
891 ATTRIBUTE13,
892 ATTRIBUTE14,
893 ATTRIBUTE15
894 )
895 VALUES
896 (
897 AHL_MR_EFFECTIVITIES_S.NEXTVAL,
898 1,
899 SYSDATE,
900 fnd_global.user_id,
901 SYSDATE,
902 fnd_global.user_id,
903 fnd_global.user_id,
904 x_new_mr_header_id,
905 l_mr_eff_rec.NAME,
906 l_mr_eff_rec.THRESHOLD_DATE,
907 l_mr_eff_rec.INVENTORY_ITEM_ID,
908 l_mr_eff_rec.INVENTORY_ORG_ID,
909 l_mr_eff_rec.RELATIONSHIP_ID,
910 l_mr_eff_rec.PC_NODE_ID,
911 l_mr_eff_rec.ATTRIBUTE_CATEGORY,
912 l_mr_eff_rec.ATTRIBUTE1,
913 l_mr_eff_rec.ATTRIBUTE2,
914 l_mr_eff_rec.ATTRIBUTE3,
915 l_mr_eff_rec.ATTRIBUTE4,
916 l_mr_eff_rec.ATTRIBUTE5,
917 l_mr_eff_rec.ATTRIBUTE6,
918 l_mr_eff_rec.ATTRIBUTE7,
919 l_mr_eff_rec.ATTRIBUTE8,
920 l_mr_eff_rec.ATTRIBUTE9,
921 l_mr_eff_rec.ATTRIBUTE10,
922 l_mr_eff_rec.ATTRIBUTE11,
923 l_mr_eff_rec.ATTRIBUTE12,
924 l_mr_eff_rec.ATTRIBUTE13,
925 l_mr_eff_rec.ATTRIBUTE14,
926 l_mr_eff_rec.ATTRIBUTE15
927 )
928 RETURNING mr_effectivity_id INTO l_new_mr_effectivity_id;
929 OPEN CurGetMrIntervals(l_old_mr_effectivity_id);
930 LOOP
931
932 FETCH CurGetMrIntervals into l_interval_rec;
933
934 IF CurGetMrIntervals%FOUND
935 THEN
936
937 l_interval_rec.OBJECT_VERSION_NUMBER:=1;
938
939 INSERT INTO AHL_MR_INTERVALS
940 (
941 MR_INTERVAL_ID,
942 OBJECT_VERSION_NUMBER,
943 LAST_UPDATE_DATE,
944 LAST_UPDATED_BY,
945 CREATION_DATE,
946 CREATED_BY,
947 LAST_UPDATE_LOGIN,
948 MR_EFFECTIVITY_ID,
949 COUNTER_ID,
950 INTERVAL_VALUE,
951 EARLIEST_DUE_VALUE,
952 START_VALUE,
953 STOP_VALUE,
954 START_DATE,
955 STOP_DATE,
956 TOLERANCE_BEFORE,
957 TOLERANCE_AFTER,
958 SECURITY_GROUP_ID,
959 ATTRIBUTE_CATEGORY,
960 ATTRIBUTE1,
961 ATTRIBUTE2,
962 ATTRIBUTE3,
963 ATTRIBUTE4,
964 ATTRIBUTE5,
965 ATTRIBUTE6,
966 ATTRIBUTE7,
967 ATTRIBUTE8,
968 ATTRIBUTE9,
969 ATTRIBUTE10,
970 ATTRIBUTE11,
971 ATTRIBUTE12,
972 ATTRIBUTE13,
973 ATTRIBUTE14,
974 ATTRIBUTE15
975 )
976 VALUES
977 (
978 AHL_MR_INTERVALS_S.NEXTVAL,
979 l_interval_rec.OBJECT_VERSION_NUMBER,
980 sysdate,
981 fnd_global.user_id,
982 sysdate,
983 fnd_global.user_id,
984 fnd_global.user_id,
985 l_new_mr_effectivity_id,
986 l_interval_rec.COUNTER_ID,
987 l_interval_rec.INTERVAL_VALUE,
988 l_interval_rec.EARLIEST_DUE_VALUE,
989 l_interval_rec.START_VALUE,
990 l_interval_rec.STOP_VALUE,
991 l_interval_rec.START_DATE,
992 l_interval_rec.STOP_DATE,
993 l_interval_rec.TOLERANCE_BEFORE,
994 l_interval_rec.TOLERANCE_AFTER,
995 l_interval_rec.SECURITY_GROUP_ID,
996 l_interval_rec.ATTRIBUTE_CATEGORY,
997 l_interval_rec.ATTRIBUTE1,
998 l_interval_rec.ATTRIBUTE2,
999 l_interval_rec.ATTRIBUTE3,
1000 l_interval_rec.ATTRIBUTE4,
1001 l_interval_rec.ATTRIBUTE5,
1002 l_interval_rec.ATTRIBUTE6,
1003 l_interval_rec.ATTRIBUTE7,
1004 l_interval_rec.ATTRIBUTE8,
1005 l_interval_rec.ATTRIBUTE9,
1006 l_interval_rec.ATTRIBUTE10,
1007 l_interval_rec.ATTRIBUTE11,
1008 l_interval_rec.ATTRIBUTE12,
1009 l_interval_rec.ATTRIBUTE13,
1010 l_interval_rec.ATTRIBUTE14,
1011 l_interval_rec.ATTRIBUTE15
1012 );
1013 ELSE
1014 EXIT WHEN CurGetMrIntervals%NOTFOUND;
1015 END IF;
1016
1017 END LOOP;
1018
1019 CLOSE CurGetMrIntervals;
1020
1021 OPEN CurGetEffectDTLS(l_old_mr_effectivity_id);
1022 loop
1023 FETCH CurGetEffectDTLS INTO l_mr_effect_dtls_rec;
1024
1025 IF CurGetEffectDTLS%FOUND
1026 THEN
1027
1028 INSERT INTO AHL_MR_EFFECTIVITY_DTLS
1029 (
1030 MR_EFFECTIVITY_DETAIL_ID,
1031 MR_EFFECTIVITY_ID,
1032 EXCLUDE_FLAG,
1033 MANUFACTURER_ID,
1034 COUNTRY_CODE,
1035 SERIAL_NUMBER_FROM,
1036 SERIAL_NUMBER_TO,
1037 MANUFACTURE_DATE_FROM,
1038 MANUFACTURE_DATE_TO,
1039 OBJECT_VERSION_NUMBER,
1040 LAST_UPDATE_DATE,
1041 LAST_UPDATED_BY,
1042 CREATION_DATE,
1043 CREATED_BY,
1044 LAST_UPDATE_LOGIN,
1045 ATTRIBUTE_CATEGORY,
1046 ATTRIBUTE1,
1047 ATTRIBUTE2,
1048 ATTRIBUTE3,
1049 ATTRIBUTE4,
1050 ATTRIBUTE5,
1051 ATTRIBUTE6,
1052 ATTRIBUTE7,
1053 ATTRIBUTE8,
1054 ATTRIBUTE9,
1055 ATTRIBUTE10,
1056 ATTRIBUTE11,
1057 ATTRIBUTE12,
1058 ATTRIBUTE13,
1059 ATTRIBUTE14,
1060 ATTRIBUTE15
1061 )
1062 VALUES
1063 (
1064 AHL_MR_EFFECTIVITY_DTLS_S.NEXTVAL,
1065 l_new_mr_EFFECTIVITY_ID,
1066 l_mr_effect_dtls_rec.EXCLUDE_FLAG,
1067 l_mr_effect_dtls_rec.MANUFACTURER_ID,
1068 l_mr_effect_dtls_rec.COUNTRY_CODE,
1069 l_mr_effect_dtls_rec.SERIAL_NUMBER_FROM,
1070 l_mr_effect_dtls_rec.SERIAL_NUMBER_TO,
1071 l_mr_effect_dtls_rec.MANUFACTURE_DATE_FROM,
1072 l_mr_effect_dtls_rec.MANUFACTURE_DATE_TO,
1073 1,
1074 SYSDATE,
1075 fnd_global.user_id,
1076 SYSDATE,
1077 fnd_global.user_id,
1078 fnd_global.user_id,
1079 l_mr_effect_dtls_rec.ATTRIBUTE_CATEGORY,
1080 l_mr_effect_dtls_rec.ATTRIBUTE1,
1081 l_mr_effect_dtls_rec.ATTRIBUTE2,
1082 l_mr_effect_dtls_rec.ATTRIBUTE3,
1083 l_mr_effect_dtls_rec.ATTRIBUTE4,
1084 l_mr_effect_dtls_rec.ATTRIBUTE5,
1085 l_mr_effect_dtls_rec.ATTRIBUTE6,
1086 l_mr_effect_dtls_rec.ATTRIBUTE7,
1087 l_mr_effect_dtls_rec.ATTRIBUTE8,
1088 l_mr_effect_dtls_rec.ATTRIBUTE9,
1089 l_mr_effect_dtls_rec.ATTRIBUTE10,
1090 l_mr_effect_dtls_rec.ATTRIBUTE11,
1091 l_mr_effect_dtls_rec.ATTRIBUTE12,
1092 l_mr_effect_dtls_rec.ATTRIBUTE13,
1093 l_mr_effect_dtls_rec.ATTRIBUTE14,
1094 l_mr_effect_dtls_rec.ATTRIBUTE15
1095 );
1096 ELSE
1097 EXIT WHEN CurGetEffectDTLS%NOTFOUND;
1098 END IF;
1099 end loop;
1100 CLOSE CurGetEffectDTLS;
1101 End loop;
1102
1103 for l_mr_relation_rec in CurGetRelationDet
1104 loop
1105
1106 IF l_mr_relation_Rec.MR_HEADER_ID=p_source_mr_header_id
1107 THEN
1108 -- Parent Relation
1109 l_rel_rec.mr_header_id :=x_new_mr_header_id;
1110 l_rel_rec.RELATED_MR_HEADER_ID :=l_mr_relation_Rec.RELATED_MR_HEADER_ID;
1111 ELSE
1112 -- Child Relation
1113 l_rel_rec.mr_header_id :=l_mr_relation_Rec.MR_HEADER_ID;
1114 l_rel_rec.related_mr_header_id :=x_new_mr_header_id;
1115 END IF;
1116
1117 INSERT INTO AHL_MR_RELATIONSHIPS(
1118 MR_RELATIONSHIP_ID,
1119 OBJECT_VERSION_NUMBER,
1120 LAST_UPDATE_DATE,
1121 LAST_UPDATED_BY,
1122 CREATION_DATE,
1123 CREATED_BY,
1124 LAST_UPDATE_LOGIN,
1125 MR_HEADER_ID,
1126 RELATED_MR_HEADER_ID,
1127 RELATIONSHIP_CODE,
1128 ATTRIBUTE_CATEGORY,
1129 ATTRIBUTE1,
1130 ATTRIBUTE2,
1131 ATTRIBUTE3,
1132 ATTRIBUTE4,
1133 ATTRIBUTE5,
1134 ATTRIBUTE6,
1135 ATTRIBUTE7,
1136 ATTRIBUTE8,
1137 ATTRIBUTE9,
1138 ATTRIBUTE10,
1139 ATTRIBUTE11,
1140 ATTRIBUTE12,
1141 ATTRIBUTE13,
1142 ATTRIBUTE14,
1143 ATTRIBUTE15)
1144 values(
1145 AHL_MR_RELATIONSHIPS_S.NEXTVAL,
1146 1,
1147 SYSDATE,
1148 fnd_global.user_id,
1149 SYSDATE,
1150 fnd_global.user_id,
1151 fnd_global.user_id,
1152 l_rel_Rec.MR_HEADER_ID,
1153 l_rel_Rec.RELATED_MR_HEADER_ID,
1154 l_mr_relation_Rec.RELATIONSHIP_CODE,
1155 l_mr_relation_Rec.ATTRIBUTE_CATEGORY,
1156 l_mr_relation_Rec.ATTRIBUTE1,
1157 l_mr_relation_Rec.ATTRIBUTE2,
1158 l_mr_relation_Rec.ATTRIBUTE3,
1159 l_mr_relation_Rec.ATTRIBUTE4,
1160 l_mr_relation_Rec.ATTRIBUTE5,
1161 l_mr_relation_Rec.ATTRIBUTE6,
1162 l_mr_relation_Rec.ATTRIBUTE7,
1163 l_mr_relation_Rec.ATTRIBUTE8,
1164 l_mr_relation_Rec.ATTRIBUTE9,
1165 l_mr_relation_Rec.ATTRIBUTE10,
1166 l_mr_relation_Rec.ATTRIBUTE11,
1167 l_mr_relation_Rec.ATTRIBUTE12,
1168 l_mr_relation_Rec.ATTRIBUTE13,
1169 l_mr_relation_Rec.ATTRIBUTE14,
1170 l_mr_relation_Rec.ATTRIBUTE15);
1171 END LOOP;
1172 -- start visit types
1173
1174 FOR l_mrvisttype_rec in CurGetMrVisitTypes
1175 LOOP
1176 INSERT INTO AHL_MR_VISIT_TYPES
1177 (
1178 MR_VISIT_TYPE_ID,
1179 OBJECT_VERSION_NUMBER,
1180 LAST_UPDATE_DATE,
1181 LAST_UPDATED_BY,
1182 CREATION_DATE,
1183 CREATED_BY,
1184 LAST_UPDATE_LOGIN,
1185 MR_HEADER_ID,
1186 SECURITY_GROUP_ID,
1187 ATTRIBUTE_CATEGORY,
1188 ATTRIBUTE1,
1189 ATTRIBUTE2,
1190 ATTRIBUTE3,
1191 ATTRIBUTE4,
1192 ATTRIBUTE5,
1193 ATTRIBUTE6,
1194 ATTRIBUTE7,
1195 ATTRIBUTE8,
1196 ATTRIBUTE9,
1197 ATTRIBUTE10,
1198 ATTRIBUTE11,
1199 ATTRIBUTE12,
1200 ATTRIBUTE13,
1201 ATTRIBUTE14,
1202 ATTRIBUTE15,
1203 MR_VISIT_TYPE_CODE
1204 )
1205 VALUES
1206 (
1207 AHL_MR_VISIT_TYPES_S.NEXTVAL,
1208 1,
1209 SYSDATE,
1210 fnd_global.user_id,
1211 SYSDATE,
1212 fnd_global.user_id,
1213 fnd_global.user_id,
1214 x_new_MR_HEADER_ID,
1215 l_mrvisttype_rec.SECURITY_GROUP_ID,
1216 l_mrvisttype_rec.ATTRIBUTE_CATEGORY,
1217 l_mrvisttype_rec.ATTRIBUTE1,
1218 l_mrvisttype_rec.ATTRIBUTE2,
1219 l_mrvisttype_rec.ATTRIBUTE3,
1220 l_mrvisttype_rec.ATTRIBUTE4,
1221 l_mrvisttype_rec.ATTRIBUTE5,
1222 l_mrvisttype_rec.ATTRIBUTE6,
1223 l_mrvisttype_rec.ATTRIBUTE7,
1224 l_mrvisttype_rec.ATTRIBUTE8,
1225 l_mrvisttype_rec.ATTRIBUTE9,
1226 l_mrvisttype_rec.ATTRIBUTE10,
1227 l_mrvisttype_rec.ATTRIBUTE11,
1228 l_mrvisttype_rec.ATTRIBUTE12,
1229 l_mrvisttype_rec.ATTRIBUTE13,
1230 l_mrvisttype_rec.ATTRIBUTE14,
1231 l_mrvisttype_rec.ATTRIBUTE15,
1232 l_mrvisttype_rec.MR_VISIT_TYPE_CODE
1233 );
1234 END LOOP;
1235
1236 l_msg_count := FND_MSG_PUB.count_msg;
1237
1238
1239 IF l_msg_count > 0 THEN
1240 X_msg_count := l_msg_count;
1241 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1242 RAISE FND_API.G_EXC_ERROR;
1243 END IF;
1244
1245 IF FND_API.TO_BOOLEAN(p_commit) THEN
1246 COMMIT;
1247 END IF;
1248
1249 -- Check if API is called in debug mode. If yes, disable debug.
1250
1251 IF G_DEBUG='Y' THEN
1252 AHL_DEBUG_PUB.DEBUG('CREATION OF MR_REVISION IS COMPLETE');
1253 AHL_DEBUG_PUB.disable_debug;
1254 END IF;
1255
1256 EXCEPTION
1257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1258 ROLLBACK TO CREATE_MR_REVISION_PVT;
1259 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1260 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1261 p_count => x_msg_count,
1262 p_data => x_msg_data);
1263 IF G_DEBUG='Y' THEN
1264 AHL_DEBUG_PUB.DEBUG('CREATION OF MR_REVISION IS NOT COMPLETE');
1265 AHL_DEBUG_PUB.disable_debug;
1266 END IF;
1267
1268
1269 WHEN FND_API.G_EXC_ERROR THEN
1270 ROLLBACK TO CREATE_MR_REVISION_PVT;
1271 X_return_status := FND_API.G_RET_STS_ERROR;
1272 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1273 p_count => x_msg_count,
1274 p_data => X_msg_data);
1275 IF G_DEBUG='Y' THEN
1276 AHL_DEBUG_PUB.DEBUG('CREATION OF MR_REVISION IS NOT COMPLETE');
1277 AHL_DEBUG_PUB.disable_debug;
1278 END IF;
1279
1280 WHEN OTHERS THEN
1281 ROLLBACK TO CREATE_MR_REVISION_PVT;
1282 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1283 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1284 THEN
1285 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_REVISION_PVT',
1286 p_procedure_name => 'CREATE_MR_REVISION',
1287 p_error_text => SUBSTR(SQLERRM,1,240));
1288 END IF;
1289 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1290 p_count => x_msg_count,
1291 p_data => X_msg_data);
1292 IF G_DEBUG='Y' THEN
1293 AHL_DEBUG_PUB.DEBUG('CREATION OF MR_REVISION IS NOT COMPLETE');
1294 AHL_DEBUG_PUB.DEBUG(SQLERRM||' IN '||L_API_NAME);
1295 AHL_DEBUG_PUB.disable_debug;
1296 END IF;
1297
1298 END;
1299
1300 PROCEDURE INITIATE_MR_APPROVAL
1301 (
1302 p_api_version IN NUMBER:=1.0,
1303 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
1304 p_commit IN VARCHAR2:=FND_API.G_FALSE,
1305 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
1306 p_default IN VARCHAR2:=FND_API.G_FALSE,
1307 p_module_type IN VARCHAR2,
1308 x_return_status OUT NOCOPY VARCHAR2,
1309 x_msg_count OUT NOCOPY NUMBER,
1310 x_msg_data OUT NOCOPY VARCHAR2,
1311 p_source_mr_header_id IN NUMBER,
1312 p_object_Version_number IN NUMBER,
1313 p_apprv_type IN VARCHAR2:='COMPLETE'
1314 )
1315 AS
1316 l_counter NUMBER:=0;
1317 l_status VARCHAR2(30);
1318 l_upd_mr_status_code VARCHAR2(30);
1319 l_object VARCHAR2(30):='FMPMR';
1320 l_approval_type VARCHAR2(100):='CONCEPT';
1321 l_active VARCHAR2(50):= 'N';
1322 l_process_name VARCHAR2(50):='AHLGAPP';
1323 l_item_type VARCHAR2(50);
1324 l_msg_count NUMBER;
1325 l_msg_data VARCHAR2(2000);
1326 l_activity_id NUMBER:=p_source_mr_header_id;
1327 l_Status VARCHAR2(1);
1328 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1329 l_object_Version_number NUMBER:=p_object_version_number;
1330
1331 Cursor GetHeaderInfo(C_MR_HEADER_ID NUMBER)
1332 IS
1333 SELECT MR_HEADER_ID,
1334 TITLE,
1335 VERSION_NUMBER,
1336 MR_STATUS_CODE,
1337 EFFECTIVE_FROM,
1338 EFFECTIVE_TO,
1339 TYPE_CODE
1340 FROM AHL_MR_HEADERS_APP_V
1341 WHERE MR_HEADER_ID=C_MR_HEADER_ID
1342 and object_version_number=p_object_Version_number;
1343 l_mr_rec GetHeaderInfo%ROWTYPE;
1344
1345 Cursor GetHeaderInfo1(C_TITLE VARCHAR2,C_VERSION_NUMBER NUMBER)
1346 IS
1347 SELECT MR_HEADER_ID,
1348 TITLE,
1349 VERSION_NUMBER,
1350 MR_STATUS_CODE,
1351 EFFECTIVE_FROM,
1352 EFFECTIVE_TO
1353 FROM AHL_MR_HEADERS_APP_V
1354 WHERE TITLE=C_TITLE
1355 And version_number=c_version_number-1;
1356 l_mr_rec1 GetHeaderInfo1%ROWTYPE;
1357
1358 l_mr_appr_enabled VARCHAR2(30);
1359 l_check_flag VARCHAR2(1):='Y';
1360 l_program_id NUMBER;
1361 l_pm_activity_id NUMBER;
1362 l_contract_ref_exists VARCHAR2(1);
1363
1364 l_approved_status_code VARCHAR2(30);
1365 BEGIN
1366 SAVEPOINT INITIATE_MR_APPROVAL_PVT;
1367
1368 IF G_DEBUG='Y' THEN
1369 AHL_DEBUG_PUB.enable_debug;
1370 END IF;
1371
1372 IF G_DEBUG='Y' THEN
1373 AHL_DEBUG_PUB.debug( 'Start Initiate_MR_Approval');
1374 END IF;
1375
1376 IF FND_API.to_boolean(l_init_msg_list) THEN
1377 FND_MSG_PUB.initialize;
1378 END IF;
1379
1380
1381 x_return_status := FND_API.G_RET_STS_SUCCESS;
1382
1383
1384 l_mr_appr_enabled:=FND_PROFILE.VALUE('AHL_FMP_MR_APPRV_ENABLED');
1385
1386 IF (G_APPLN_USAGE = 'PM') THEN
1387 l_mr_appr_enabled:=nvl(l_mr_appr_enabled,'N');
1388 ELSE
1389 l_mr_appr_enabled:=nvl(l_mr_appr_enabled,'Y');
1390 END IF;
1391
1392 IF G_DEBUG='Y' THEN
1393 AHL_DEBUG_PUB.debug( 'l_mr_appr_enabled : ' || l_mr_appr_enabled);
1394 AHL_DEBUG_PUB.debug( 'P_APPRV_TYPE : ' || P_APPRV_TYPE);
1395 END IF;
1396
1397 IF p_source_mr_header_id is null or
1398 p_source_mr_header_id=FND_API.G_MISS_NUM
1399 THEN
1400 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_INVALID');
1401 FND_MSG_PUB.ADD;
1402 l_check_flag:='N';
1403 ELSE
1404 open GetHeaderInfo(p_source_mr_header_id);
1405
1406 fetch GetHeaderInfo into l_mr_rec;
1407 If GetHeaderInfo%FOUND
1408 Then
1409
1410 -- If P_APPRV_TYPE='COMPLETE'
1411 If P_APPRV_TYPE IN ('COMPLETE','COMPLETE_DCALC')
1412 Then
1413 l_upd_mr_status_code:='APPROVAL_PENDING';
1414
1415 IF trunc(l_mr_Rec.effective_from)<trunc(sysdate)
1416 THEN
1417 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ST_DATE_LESSER_SYSDATE');
1418 FND_MSG_PUB.ADD;
1419 END IF;
1420
1421 If l_mr_rec.MR_STATUS_CODE<>'DRAFT'
1422 AND l_mr_rec.MR_STATUS_CODE<>'APPROVAL_REJECTED'
1423 Then
1424 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_CANNOT_APRV');
1425 FND_MSG_PUB.ADD;
1426 End if;
1427 ElsIf P_APPRV_TYPE='TERMINATE'
1428 Then
1429 IF G_APPLN_USAGE='PM'
1430 THEN
1431
1432 IF l_mr_rec.type_code='ACTIVITY'
1433 THEN
1434 l_pm_activity_id:=p_source_mr_header_id;
1435 l_program_id:=null;
1436 ELSIF l_mr_rec.type_code='PROGRAM'
1437 THEN
1438 l_pm_Activity_id:=null;
1439 l_program_id:=p_source_mr_header_id;
1440 END IF;
1441
1442 IF G_DEBUG='Y' THEN
1443 AHL_DEBUG_PUB.debug( ' Before Call to OKS_PM_ENTITLEMENTS_PUB.Check_PM_Exists');
1444 AHL_DEBUG_PUB.debug( 'l_pm_activity_id'||l_pm_activity_id);
1445 AHL_DEBUG_PUB.debug( 'l_program_id'||l_program_id);
1446 END IF;
1447
1448
1449 OKS_PM_ENTITLEMENTS_PUB.Check_PM_Exists
1450 (
1451 p_api_version =>p_api_version,
1452 p_init_msg_list =>FND_API.G_FALSE,
1453 p_pm_program_id =>l_program_id,
1454 p_pm_activity_id =>l_pm_activity_id,
1455 x_return_status =>x_return_status,
1456 x_msg_count =>x_msg_count,
1457 x_msg_data =>x_msg_data,
1458 x_pm_reference_exists =>l_contract_ref_exists
1459 );
1460
1461 IF G_DEBUG='Y' THEN
1462 AHL_DEBUG_PUB.debug( 'After call to OKS_PM_ENTITLEMENTS_PUB.Check_PM_Exists');
1463 END IF;
1464
1465
1466 --l_contract_ref_exists:='Y'; -- TEMP CHECK
1467 --l_contract_ref_exists:=NULL; -- TEMP CHECK
1468 --l_contract_ref_exists:=FND_API.G_MISS_CHAR; -- TEMP CHECK
1469
1470 IF l_contract_ref_exists='Y'
1471 THEN
1472 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_CONTRACTS_EXIST');
1473 FND_MESSAGE.SET_TOKEN('MRTITLE',l_mr_Rec.TITLE);
1474 FND_MSG_PUB.ADD;
1475
1476 IF G_DEBUG='Y' THEN
1477 AHL_DEBUG_PUB.debug( 'l_contract_ref_exists---->'||l_contract_ref_exists);
1478 END IF;
1479
1480 ELSIF l_contract_ref_exists IS NULL OR l_contract_ref_exists=FND_API.G_MISS_CHAR
1481 THEN
1482 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_INVALID_RET_PARAM');
1483 FND_MSG_PUB.ADD;
1484
1485 IF G_DEBUG='Y' THEN
1486 AHL_DEBUG_PUB.debug( 'l_contract_ref_exists----> is either null or g_misschar'||l_contract_ref_exists);
1487 END IF;
1488
1489 END IF;
1490
1491
1492
1493 END IF;
1494
1495 -- END OF CHECK FOR CONTRACTS EXISTING WHEN TERMINATING IN PM MODE
1496
1497 l_upd_mr_status_code:='TERMINATE_PENDING';
1498 If l_mr_rec.MR_STATUS_CODE<>'COMPLETE' OR
1499 (l_mr_rec.EFFECTIVE_TO IS NOT NULL
1500 AND l_mr_rec.EFFECTIVE_TO<>FND_API.G_MISS_DATE)
1501 Then
1502 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_CANNOT_TERMIN');
1503 FND_MSG_PUB.ADD;
1504 End if;
1505 End If;
1506 -- If mr_header_id is invalid or not found
1507 ElsIf GetHeaderInfo%NOTFOUND
1508 Then
1509 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_INVALID');
1510 FND_MSG_PUB.ADD;
1511 l_check_flag:='N';
1512 End If;
1513 Close GetHeaderInfo;
1514 End If;
1515
1516
1517 -- If P_APPRV_TYPE='COMPLETE' and l_check_flag='Y'
1518 If P_APPRV_TYPE IN ('COMPLETE','COMPLETE_DCALC') and l_check_flag='Y'
1519 Then
1520
1521 If l_mr_rec.version_number>1 and (l_mr_rec.MR_STATUS_CODE='DRAFT'
1522 or l_mr_rec.MR_STATUS_CODE='APPROVAL_REJECTED')
1523 Then
1524 Open GetHeaderInfo1(upper(l_mr_rec.TITLE),
1525 l_mr_rec.VERSION_NUMBER);
1526 Fetch GetHeaderInfo1 Into l_mr_rec1;
1527 If GetHeaderInfo1%FOUND
1528 Then
1529 If trunc(l_mr_Rec.effective_from) < trunc(l_mr_Rec1.effective_from)
1530 TheN
1531 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ST_DATE_LESSER');
1532 FND_MESSAGE.SET_TOKEN('FIELD',l_mr_Rec1.effective_from);
1533 FND_MSG_PUB.ADD;
1534 End If;
1535 End If;
1536 Close GetHeaderInfo1;
1537 End if;
1538
1539 SELECT COUNT(*) INTO l_counter
1540 FROM AHL_MR_ROUTES A
1541 WHERE MR_HEADER_ID=l_activity_id
1542 AND ROUTE_ID IN (SELECT ROUTE_ID FROM AHL_ROUTES_APP_V
1543 WHERE ROUTE_ID=A.ROUTE_id
1544 AND NVL(END_DATE_ACTIVE,SYSDATE+1)>SYSDATE
1545 AND REVISION_STATUS_CODE='COMPLETE');
1546
1547 If G_APPLN_USAGE='PM' and l_mr_Rec.type_code<>'PROGRAM'
1548 Then
1549 If l_counter=0
1550 TheN
1551 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_INIT_APPRV_ABORT');
1552 FND_MSG_PUB.ADD;
1553 End If;
1554 ElsIf G_APPLN_USAGE<>'PM'
1555 Then
1556 IF l_counter=0
1557 Then
1558 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_INIT_APPRV_ABORT');
1559 FND_MSG_PUB.ADD;
1560 End If;
1561 End If;
1562
1563 End If;
1564 /*
1565
1566 If P_APPRV_TYPE='TERMINATE' and l_check_flag='Y'
1567 Then
1568 SELECT COUNT(*) INTO l_counter
1569 FROM AHL_MR_HEADERS_APP_V
1570 WHERE MR_HEADER_ID>l_activity_id
1571 And Title=l_mr_rec.title
1572 And Version_number >l_mr_rec.version_number;
1573 IF l_counter>0
1574 Then
1575 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TERMIN_OLD');
1576 FND_MESSAGE.SET_TOKEN('TITLE',l_mr_Rec.TITLE);
1577 FND_MSG_PUB.ADD;
1578 End If;
1579 IF l_mr_rec.effective_to IS NOT NULL
1580 Then
1581 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TERMINATED');
1582 FND_MESSAGE.SET_TOKEN('TITLE',l_mr_Rec.title);
1583 FND_MSG_PUB.ADD;
1584 End If;
1585 End if;
1586 */
1587 l_msg_count := FND_MSG_PUB.count_msg;
1588
1589 If G_DEBUG='Y' THEN
1590 AHL_DEBUG_PUB.debug( 'Check Error l_msg_count:'||l_msg_count);
1591 End If;
1592
1593 IF l_msg_count > 0
1594 THEN
1595 X_msg_count := l_msg_count;
1596 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1597 RAISE FND_API.G_EXC_ERROR;
1598 END IF;
1599
1600 IF(P_APPRV_TYPE = 'COMPLETE_DCALC')THEN
1601 l_approved_status_code := 'APPROVED_DCALC';
1602 ELSE
1603 l_approved_status_code := 'APPROVED';
1604 END IF;
1605
1606 -- Start work Flow Process
1607 IF (l_mr_appr_enabled = 'Y')
1608 THEN
1609 ahl_utility_pvt.get_wf_process_name(
1610 p_object =>l_object,
1611 p_application_usg_code =>g_appln_usage,
1612 x_active =>l_active,
1613 x_process_name =>l_process_name ,
1614 x_item_type =>l_item_type,
1615 x_return_status =>x_return_status,
1616 x_msg_count =>l_msg_count,
1617 x_msg_data =>l_msg_data);
1618 END IF;
1619
1620 IF l_ACTIVE='Y'
1621 THEN
1622 UPDATE AHL_MR_HEADERS_B
1623 SET MR_STATUS_CODE=l_upd_mr_status_code,
1624 OBJECT_VERSION_number=object_version_number+1
1625 WHERE MR_HEADER_ID=p_source_mr_header_id
1626 And OBJECT_VERSION_NUMBER=p_object_Version_number;
1627
1628 IF sql%rowcount=0
1629 THEN
1630 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1631 FND_MSG_PUB.ADD;
1632 ELSE
1633
1634 Ahl_generic_aprv_pvt.Start_Wf_Process(
1635 P_OBJECT =>l_object,
1636 P_ACTIVITY_ID =>l_activity_id,
1637 P_APPROVAL_TYPE =>'CONCEPT',
1638 P_OBJECT_VERSION_NUMBER =>p_object_version_number+1,
1639 P_ORIG_STATUS_CODE =>'ACTIVE',
1640 -- P_NEW_STATUS_CODE =>'APPROVED',
1641 P_NEW_STATUS_CODE => l_approved_status_code,
1642 P_REJECT_STATUS_CODE =>'REJECTED',
1643 P_REQUESTER_USERID =>fnd_global.user_id,
1644 P_NOTES_FROM_REQUESTER =>'',
1645 P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
1646 P_ITEM_TYPE =>'AHLGAPP',
1647 p_application_usg_code =>G_APPLN_USAGE
1648 );
1649 END IF;
1650 ELSE
1651 UPDATE AHL_MR_HEADERS_B
1652 SET MR_STATUS_CODE=L_UPD_MR_STATUS_CODE,
1653 OBJECT_VERSION_number=OBJECT_VERSION_number+1
1654 WHERE MR_HEADER_ID=p_source_mr_header_id
1655 AND OBJECT_VERSION_NUMBER=p_object_Version_number;
1656
1657 IF sql%rowcount=0
1658 THEN
1659 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1660 FND_MSG_PUB.ADD;
1661 END IF;
1662
1663 AHL_FMP_MR_REVISION_PVT.COMPLETE_MR_REVISION
1664 (
1665 p_api_version =>1.0,
1666 p_init_msg_list =>FND_API.G_FALSE,
1667 p_commit =>FND_API.G_FALSE,
1668 p_validation_level =>NULL,
1669 p_default =>NULL,
1670 p_module_type =>NULL,
1671 x_return_status =>x_return_status,
1672 x_msg_count =>x_msg_count ,
1673 x_msg_data =>x_msg_data ,
1674 -- p_appr_status =>'APPROVED',
1675 p_appr_status => l_approved_status_code,
1676 p_mr_header_id =>l_activity_id,
1677 p_object_version_number =>l_object_version_number+1
1678 );
1679
1680 END IF;
1681
1682 l_msg_count := FND_MSG_PUB.count_msg;
1683
1684 IF l_msg_count > 0
1685 THEN
1686 X_msg_count := l_msg_count;
1687 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1688 RAISE FND_API.G_EXC_ERROR;
1689 END IF;
1690
1691
1692 IF FND_API.TO_BOOLEAN(p_commit) THEN
1693 COMMIT;
1694 END IF;
1695
1696 EXCEPTION
1697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1698 ROLLBACK TO INITIATE_MR_APPROVAL_PVT;
1699 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1700 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1701 p_count => x_msg_count,
1702 p_data => x_msg_data);
1703 IF G_DEBUG='Y' THEN
1704 AHL_DEBUG_PUB.disable_debug;
1705 END IF;
1706
1707
1708 WHEN FND_API.G_EXC_ERROR THEN
1709 ROLLBACK TO INITIATE_MR_APPROVAL_PVT;
1710 X_return_status := FND_API.G_RET_STS_ERROR;
1711 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1712 p_count => x_msg_count,
1713 p_data => X_msg_data);
1714 IF G_DEBUG='Y' THEN
1715 AHL_DEBUG_PUB.disable_debug;
1716 END IF;
1717
1718 WHEN OTHERS THEN
1719 ROLLBACK TO INITIATE_MR_APPROVAL_PVT;
1720 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1721 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1722 THEN
1723 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_REVISION_PVT',
1724 p_procedure_name => 'INITIATE_MR_APPROVAL',
1725 p_error_text => SUBSTR(SQLERRM,1,240));
1726 END IF;
1727 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1728 p_count => x_msg_count,
1729 p_data => X_msg_data);
1730 IF G_DEBUG='Y' THEN
1731 AHL_DEBUG_PUB.disable_debug;
1732 END IF;
1733
1734 END;
1735
1736
1737 PROCEDURE COMPLETE_MR_REVISION
1738 (
1739 p_api_version IN NUMBER:=1.0,
1740 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
1741 p_commit IN VARCHAR2:=FND_API.G_FALSE,
1742 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
1743 p_default IN VARCHAR2:=FND_API.G_FALSE,
1744 p_module_type IN VARCHAR2,
1745 x_return_status OUT NOCOPY VARCHAR2,
1746 x_msg_count OUT NOCOPY NUMBER,
1747 x_msg_data OUT NOCOPY VARCHAR2,
1748 p_appr_status IN VARCHAR2,
1749 p_mr_header_id IN NUMBER,
1750 p_object_version_number IN NUMBER
1751 )
1752 AS
1753
1754 CURSOR GetMR_headerDet(C_MR_HEADER_ID NUMBER)
1755 IS
1756 SELECT MR_HEADER_ID,
1757 VERSION_NUMBER,
1758 EFFECTIVE_FROM,
1759 TITLE,
1760 MR_STATUS_CODE,
1761 APPLICATION_USG_CODE
1762 FROM AHL_MR_HEADERS_B
1763 WHERE MR_HEADER_ID=C_MR_HEADER_ID;
1764
1765 CURSOR GetPrevMR_headerid(C_VERSION_NUMBER NUMBER,C_TITLE VARCHAR2,C_APP_CODE VARCHAR2)
1766 IS
1767 SELECT MR_HEADER_ID,
1768 VERSION_NUMBER,
1769 EFFECTIVE_FROM,
1770 TITLE,
1771 MR_STATUS_CODE
1772 FROM AHL_MR_HEADERS_B
1773 WHERE TITLE=C_TITLE
1774 AND VERSION_NUMBER=C_VERSION_NUMBER-1
1775 AND APPLICATION_USG_CODE=C_APP_CODE;
1776
1777 l_mr_rec GetMR_headerDet%rowtype;
1778 l_prev_mr_rec GetPrevMR_headerid%rowtype;
1779 l_status VARCHAR2(30);
1780 l_mr_status VARCHAR2(30);
1781 l_check_flag VARCHAR2(1):='N';
1782 l_check_flag2 VARCHAR2(1):='N';
1783 l_check_flag3 VARCHAR2(1):='Y';
1784 l_api_name CONSTANT VARCHAR2(30):='COMPLETE_MR_REVISION';
1785 l_api_version CONSTANT NUMBER := 1.0;
1786 l_msg_count NUMBER;
1787 l_msg_data VARCHAR2(2000);
1788 l_fr_date DATE:=SYSDATE;
1789 l_to_Date DATE:=SYSDATE;
1790 l_commit VARCHAR2(10):=FND_API.G_TRUE;
1791
1792 /* Vo comments: No need to copy newer revisions of the MR to the ATA Sequences,
1793 * the User will need to add them on a case-to-case basis...
1794 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
1795 l_prev_program_type VARCHAR2(30);
1796 l_program_type VARCHAR2(30);
1797 -- Tamal [MEL/CDL RM-FMP Enhancements] Ends here...
1798 */
1799
1800 l_req_id number;
1801
1802 BEGIN
1803
1804 SAVEPOINT COMPLETE_MR_REVISION_PVT;
1805
1806 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1807 IF G_DEBUG='Y' THEN
1808 AHL_DEBUG_PUB.enable_debug;
1809 AHL_DEBUG_PUB.debug( 'p_appr_Status'||p_appr_status);
1810 AHL_DEBUG_PUB.debug( 'Header Id '||p_mr_header_id);
1811 END IF;
1812
1813 IF p_mr_header_id is not null and p_mr_header_id<>fnd_api.g_miss_num
1814 THEN
1815 OPEN GetMR_headerDet(p_mr_header_id);
1816 FETCH GetMR_headerDet INTO l_mr_rec;
1817
1818 IF GetMR_headerDet%NOTFOUND
1819 THEN
1820 l_check_flag:='N';
1821 ELSE
1822 IF p_appr_status IN ('APPROVED', 'APPROVED_DCALC')
1823 THEN
1824 IF l_mr_rec.mr_status_code='APPROVAL_PENDING'
1825 THEN
1826 l_status:='COMPLETE';
1827 ELSIF l_mr_rec.mr_status_code='TERMINATE_PENDING'
1828 THEN
1829 l_status:='TERMINATED';
1830 END IF;
1831 l_check_flag:='Y';
1832 ELSE
1833 l_check_flag:='N';
1834 l_status:='APPROVAL_REJECTED';
1835 IF l_mr_rec.mr_status_code='TERMINATE_PENDING'
1836 THEN
1837 l_status:='COMPLETE';
1838 END IF;
1839 l_check_flag3:='N';
1840 UPDATE AHL_MR_HEADERS_B
1841 SET MR_STATUS_CODE=DECODE(MR_STATUS_CODE,'APPROVAL_PENDING','APPROVAL_REJECTED','TERMINATE_PENDING','COMPLETE')
1842 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
1843 END IF;
1844
1845 IF l_mr_rec.effective_from >sysdate
1846 THEN
1847 l_fr_date:=l_mr_rec.effective_from;
1848 l_to_date:=l_mr_rec.effective_from;
1849 ELSE
1850 l_fr_date:=sysdate;
1851 l_to_date:=sysdate;
1852 END IF;
1853 END IF;
1854
1855 CLOSE GetMR_headerDet;
1856
1857 IF l_check_flag='Y' and l_mr_rec.version_number=1
1858 THEN
1859 IF l_mr_rec.MR_STATUS_CODE='APPROVAL_PENDING'
1860 THEN
1861 UPDATE AHL_MR_HEADERS_B
1862 SET MR_STATUS_CODE=l_status,
1863 EFFECTIVE_FROM=L_FR_DATE,
1864 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1865 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
1866 ELSIF l_mr_rec.MR_STATUS_CODE='TERMINATE_PENDING'
1867 THEN
1868 UPDATE AHL_MR_HEADERS_B
1869 SET MR_STATUS_CODE=l_status,
1870 EFFECTIVE_TO=nvl(EFFECTIVE_TO,L_TO_DATE),
1871 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1872 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
1873
1874 END IF;
1875
1876 IF L_MR_REC.MR_STATUS_CODE='TERMINATE_PENDING'
1877 THEN
1878 IF G_DEBUG='Y'
1879 THEN
1880 AHL_DEBUG_PUB.debug( 'l_status:'||l_status);
1881 AHL_DEBUG_PUB.debug( 'Before Call to Terminate MR Instances');
1882 END IF;
1883
1884 AHL_UMP_UNITMAINT_PUB.Terminate_MR_Instances(
1885 p_api_version =>l_api_version,
1886 p_init_msg_list =>FND_API.G_FALSE,
1887 p_commit =>p_commit,
1888 p_validation_level =>p_validation_level,
1889 p_default =>p_default,
1890 p_module_type =>p_module_type,
1891 p_old_mr_header_id =>l_mr_rec.MR_HEADER_ID,
1892 p_old_mr_title =>l_mr_rec.TITLE,
1893 p_old_version_number =>l_mr_rec.VERSION_NUMBER,
1894 p_new_mr_header_id =>NULL,
1895 p_new_mr_title =>NULL,
1896 p_new_version_number =>NULL,
1897 x_return_status =>x_return_Status,
1898 x_msg_count =>l_msg_count,
1899 x_msg_data =>l_msg_data);
1900
1901 IF FND_MSG_PUB.count_msg > 0
1902 THEN
1903 IF G_DEBUG='Y' THEN
1904 AHL_DEBUG_PUB.debug( 'Terminate inst fail');
1905 END IF;
1906
1907 END IF;
1908
1909 END IF;
1910
1911
1912
1913 l_check_flag:='Y';
1914
1915 l_check_flag2:='Y';
1916
1917 ELSIF l_check_flag='Y' and l_mr_rec.version_number>1
1918 THEN
1919 AHL_DEBUG_PUB.debug( ' For version_number >1');
1920
1921 OPEN GetPrevMR_headerid(l_mr_rec.version_number,
1922 l_mr_rec.title,
1923 l_mr_rec.application_usg_code);
1924
1925 FETCH GetPrevMR_headerid INTO l_prev_mr_rec;
1926
1927 IF GetPrevMR_headerid%NOTFOUND
1928 THEN
1929 l_check_flag2:='N';
1930 ELSE
1931 l_check_flag2:='Y';
1932 END IF;
1933
1934 CLOSE GetPrevMR_headerid;
1935
1936 IF l_check_flag2='Y'
1937 THEN
1938
1939 IF l_mr_rec.MR_STATUS_CODE='APPROVAL_PENDING'
1940 THEN
1941 UPDATE AHL_MR_HEADERS_B
1942 SET MR_STATUS_CODE=l_status,
1943 EFFECTIVE_FROM=L_FR_DATE,
1944 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1945 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
1946
1947 UPDATE AHL_MR_HEADERS_B
1948 SET EFFECTIVE_TO=L_TO_DATE,
1949 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1950 WHERE MR_HEADER_ID=l_prev_mr_rec.MR_HEADER_ID;
1951
1952 /* Vo comments: No need to copy newer revisions of the MR to the ATA Sequences,
1953 * the User will need to add them on a case-to-case basis...
1954
1955 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
1956
1957 -- Verify whether both old revisions of the MR are M Procedures...
1958 SELECT program_type_code
1959 INTO l_prev_program_type
1960 FROM ahl_mr_headers_app_v
1961 WHERE mr_header_id = l_prev_mr_rec.MR_HEADER_ID;
1962
1963 SELECT program_type_code
1964 INTO l_program_type
1965 FROM ahl_mr_headers_app_v
1966 WHERE mr_header_id = P_MR_HEADER_ID;
1967
1968 -- If old revision of the MO_PROC is being made inactive, need to associated the new revision to ATA Sequences too
1969 IF (l_prev_program_type = 'MO_PROC' AND l_program_type = 'MO_PROC')
1970 THEN
1971 AHL_MEL_CDL_ATA_SEQS_PVT.Copy_MO_Proc_Revision
1972 (
1973 -- Standard IN params
1974 p_api_version => 1.0,
1975 p_init_msg_list => FND_API.G_FALSE,
1976 p_commit => FND_API.G_FALSE,
1977 p_validation_level => p_validation_level,
1978 p_default => p_default,
1979 p_module_type => p_module_type,
1980 -- Standard OUT params
1981 x_return_status => x_return_status,
1982 x_msg_count => l_msg_count,
1983 x_msg_data => l_msg_data,
1984 -- Procedure IN, OUT, IN/OUT params
1985 p_old_mr_header_id => l_prev_mr_rec.MR_HEADER_ID,
1986 p_new_mr_header_id => P_MR_HEADER_ID
1987 );
1988 END IF;
1989 -- Tamal [MEL/CDL RM-FMP Enhancements] Ends here...
1990
1991 */
1992
1993 ELSIF l_mr_rec.MR_STATUS_CODE='TERMINATE_PENDING'
1994 THEN
1995 UPDATE AHL_MR_HEADERS_B
1996 SET MR_STATUS_CODE=l_status,
1997 EFFECTIVE_TO=NVL(EFFECTIVE_TO,l_to_date),
1998 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1999 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
2000 END IF;
2001
2002 IF l_check_flag3='Y'
2003 THEN
2004 IF l_check_flag2='Y' AND l_check_flag='Y'
2005 THEN
2006
2007 IF FND_MSG_PUB.count_msg > 0
2008 THEN
2009 IF G_DEBUG='Y' THEN
2010 AHL_DEBUG_PUB.debug( 'Error Before TerminateInstances');
2011 END IF;
2012 X_msg_count := l_msg_count;
2013 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2014 RAISE FND_API.G_EXC_ERROR;
2015 END IF;
2016
2017 IF G_DEBUG='Y'
2018 THEN
2019 AHL_DEBUG_PUB.debug( 'l_status:'||l_status);
2020 END IF;
2021
2022 IF L_MR_REC.MR_STATUS_CODE='APPROVAL_PENDING'
2023 THEN
2024
2025 AHL_UMP_UNITMAINT_PUB.Terminate_MR_Instances(
2026 p_api_version =>l_api_version,
2027 p_init_msg_list =>FND_API.G_FALSE,
2028 p_commit =>p_commit,
2029 p_validation_level =>p_validation_level,
2030 p_default =>p_default,
2031 p_module_type =>p_module_type,
2032 p_old_mr_header_id =>l_prev_mr_rec.MR_HEADER_ID,
2033 p_old_mr_title =>l_prev_mr_rec.TITLE,
2034 p_old_version_number =>l_prev_mr_rec.VERSION_NUMBER,
2035 p_new_mr_header_id =>l_mr_rec.MR_HEADER_ID,
2036 p_new_mr_title =>l_mr_rec.TITLE,
2037 p_new_version_number =>l_mr_rec.VERSION_NUMBER,
2038 x_return_status =>x_return_Status,
2039 x_msg_count =>l_msg_count,
2040 x_msg_data =>l_msg_data);
2041 ELSIF L_MR_REC.MR_STATUS_CODE='TERMINATE_PENDING'
2042 THEN
2043 AHL_UMP_UNITMAINT_PUB.Terminate_MR_Instances(
2044 p_api_version =>l_api_version,
2045 p_init_msg_list =>FND_API.G_FALSE,
2046 p_commit =>p_commit,
2047 p_validation_level =>p_validation_level,
2048 p_default =>p_default,
2049 p_module_type =>p_module_type,
2050 p_old_mr_header_id =>l_mr_rec.MR_HEADER_ID,
2051 p_old_mr_title =>l_mr_rec.TITLE,
2052 p_old_version_number =>l_mr_rec.VERSION_NUMBER,
2053 p_new_mr_header_id =>NULL,
2054 p_new_mr_title =>NULL,
2055 p_new_version_number =>null,
2056 x_return_status =>x_return_Status,
2057 x_msg_count =>l_msg_count,
2058 x_msg_data =>l_msg_data);
2059
2060 END IF;
2061 END IF;
2062
2063 IF FND_MSG_PUB.count_msg > 0
2064 THEN
2065
2066 IF G_DEBUG='Y' THEN
2067 AHL_DEBUG_PUB.debug( 'Terminate inst fail');
2068 END IF;
2069
2070 END IF;
2071 END IF;
2072 END IF;
2073 END IF;
2074 END IF;
2075
2076 l_msg_count := FND_MSG_PUB.count_msg;
2077 IF l_msg_count > 0
2078 THEN
2079 IF G_DEBUG='Y' THEN
2080 AHL_DEBUG_PUB.debug( 'Failed To Complete:');
2081 END IF;
2082 X_msg_count := l_msg_count;
2083 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2084 RAISE FND_API.G_EXC_ERROR;
2085 END IF;
2086
2087 IF p_appr_status = 'APPROVED_DCALC' THEN
2088 l_req_id := fnd_request.submit_request('AHL','AHLWUEFF',NULL,NULL,FALSE,
2089 l_prev_mr_rec.MR_HEADER_ID, l_mr_rec.MR_HEADER_ID);
2090 IF (l_req_id = 0 OR l_req_id IS NULL) THEN
2091 IF G_debug = 'Y' THEN
2092 AHL_DEBUG_PUB.debug('Tried to submit concurrent request but failed');
2093 END IF;
2094 ELSE
2095 IF G_debug = 'Y' THEN
2096 AHL_DEBUG_PUB.debug('submit concurrent request : ' || l_req_id);
2097 END IF;
2098 END IF;
2099 END IF;
2100
2101 IF G_DEBUG='Y' THEN
2102 AHL_DEBUG_PUB.debug( 'Before commit Complete_mr_revision ');
2103 END IF;
2104
2105 IF FND_API.TO_BOOLEAN(p_commit) THEN
2106 COMMIT;
2107 END IF;
2108 EXCEPTION
2109 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2110 ROLLBACK TO COMPLETE_MR_REVISION_PVT;
2111 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2112 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2113 p_count => x_msg_count,
2114 p_data => x_msg_data);
2115 IF G_DEBUG='Y' THEN
2116 AHL_DEBUG_PUB.disable_debug;
2117 END IF;
2118 WHEN FND_API.G_EXC_ERROR THEN
2119 ROLLBACK TO COMPLETE_MR_REVISION_PVT;
2120 X_return_status := FND_API.G_RET_STS_ERROR;
2121 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2122 p_count => x_msg_count,
2123 p_data => X_msg_data);
2124 IF G_DEBUG='Y' THEN
2125 AHL_DEBUG_PUB.disable_debug;
2126 END IF;
2127
2128 WHEN OTHERS THEN
2129 ROLLBACK TO COMPLETE_MR_REVISION_PVT;
2130 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2131 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2132 THEN
2133 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_REVISION_PVT',
2134 p_procedure_name => 'COMPLETE_MR_REVISION',
2135 p_error_text => SUBSTR(SQLERRM,1,240));
2136 END IF;
2137 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2138 p_count => x_msg_count,
2139 p_data => X_msg_data);
2140
2141 IF G_DEBUG='Y' THEN
2142 AHL_DEBUG_PUB.disable_debug;
2143 END IF;
2144
2145 END;
2146
2147 PROCEDURE VALIDATE_MR_REVISION
2148 (
2149 p_api_version IN NUMBER:=1.0,
2150 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
2151 p_commit IN VARCHAR2:=FND_API.G_FALSE,
2152 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
2153 p_default IN VARCHAR2:=FND_API.G_FALSE,
2154 p_module_type IN VARCHAR2,
2155 x_return_status OUT NOCOPY VARCHAR2,
2156 x_msg_count OUT NOCOPY NUMBER,
2157 x_msg_data OUT NOCOPY VARCHAR2,
2158 p_source_mr_header_id IN NUMBER,
2159 p_object_version_number IN NUMBER
2160 )
2161 AS
2162 l_counter1 NUMBER:=0;
2163 l_counter2 NUMBER:=0;
2164 l_appln_code VARCHAR2(30);
2165 BEGIN
2166
2167 SAVEPOINT VALIDATE_MR_REVISION;
2168
2169 IF G_APPLN_USAGE IS NULL
2170 THEN
2171 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
2172 FND_MSG_PUB.ADD;
2173 RETURN;
2174 END IF;
2175
2176
2177 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2178
2179 SELECT count(*) into l_counter1
2180 FROM AHL_MR_HEADERS_APP_V
2181 Where mr_header_id=p_source_mr_header_id;
2182
2183 IF l_counter1=0
2184 THEN
2185 FND_MESSAGE.SET_NAME('AHL','AHL_MR_HEADER_ID_INVALID');
2186 FND_MSG_PUB.ADD;
2187 END IF;
2188
2189 SELECT count(*) into l_counter2
2190 FROM AHL_MR_HEADERS_APP_V
2191 Where mr_header_id=p_source_mr_header_id
2192 And mr_status_code='DRAFT' or mr_status_code='APPROVAL_REJECTED';
2193
2194 IF l_counter2=0
2195 THEN
2196 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2197 FND_MSG_PUB.ADD;
2198 END IF;
2199
2200 EXCEPTION
2201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2202 ROLLBACK TO VALIDATE_MR_REVISION;
2203 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2204 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2205 p_count => x_msg_count,
2206 p_data => x_msg_data);
2207 IF G_DEBUG='Y' THEN
2208 AHL_DEBUG_PUB.disable_debug;
2209 END IF;
2210
2211 WHEN FND_API.G_EXC_ERROR THEN
2212 ROLLBACK TO VALIDATE_MR_REVISION;
2213 X_return_status := FND_API.G_RET_STS_ERROR;
2214 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2215 p_count => x_msg_count,
2216 p_data => X_msg_data);
2217 IF G_DEBUG='Y' THEN
2218 AHL_DEBUG_PUB.disable_debug;
2219 END IF;
2220
2221 WHEN OTHERS THEN
2222 ROLLBACK TO VALIDATE_MR_REVISION;
2223 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2224 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2225 THEN
2226 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME ,
2227 p_procedure_name => 'VALIDATE_MR_REVISION',
2228 p_error_text => SUBSTR(SQLERRM,1,240));
2229 END IF;
2230 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2231 p_count => x_msg_count,
2232 p_data => X_msg_data);
2233 IF G_DEBUG='Y' THEN
2234 AHL_DEBUG_PUB.disable_debug;
2235 END IF;
2236 END;
2237 END AHL_FMP_MR_REVISION_PVT;