[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.11.12020000.2 2012/12/13 05:07:43 shnatu 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 l_program_type_code AHL_MR_HEADERS_B.program_type_code%type;
185 CURSOR CurGetHeaderdet
186 Is
187 SELECT
188 MR_HEADER_ID,
189 OBJECT_VERSION_NUMBER,
190 LAST_UPDATE_DATE,
191 LAST_UPDATED_BY,
192 CREATION_DATE,
193 CREATED_BY,
194 LAST_UPDATE_LOGIN,
195 TITLE,
196 VERSION_NUMBER,
197 -- pdoki, Preceding MR Header ID is obsoleted after SBE Project.
198 PRECEDING_MR_HEADER_ID,
199 CATEGORY_CODE,
200 SERVICE_TYPE_CODE,
201 MR_STATUS_CODE,
202 IMPLEMENT_STATUS_CODE,
203 REPETITIVE_FLAG,
204 SHOW_REPETITIVE_CODE,
205 WHICHEVER_FIRST_CODE,
206 COPY_ACCOMPLISHMENT_FLAG,
207 PROGRAM_TYPE_CODE,
208 PROGRAM_SUBTYPE_CODE,
209 EFFECTIVE_FROM,
210 EFFECTIVE_TO,
211 REVISION,
212 BILLING_ITEM_ID,
213 BILLING_ORG_ID,
214 SPACE_CATEGORY_CODE,
215 --sareepar service actegory rank
216 SERVICE_CATEGORY_RANK,
217 QA_INSPECTION_TYPE_CODE,
218 DESCRIPTION,
219 COMMENTS,
220 SERVICE_REQUEST_TEMPLATE_ID,
221 TYPE_CODE,
222 DOWN_TIME,
223 --CHANDANK, 29-Sep-2010, added supplier warranty ID for supplier warranty project
224 WARRANTY_TEMPLATE_ID,
225 UOM_CODE,
226 ATTRIBUTE_CATEGORY,
227 ATTRIBUTE1,
228 ATTRIBUTE2,
229 ATTRIBUTE3,
230 ATTRIBUTE4,
231 ATTRIBUTE5,
232 ATTRIBUTE6,
233 ATTRIBUTE7,
234 ATTRIBUTE8,
235 ATTRIBUTE9,
236 ATTRIBUTE10,
237 ATTRIBUTE11,
238 ATTRIBUTE12,
239 ATTRIBUTE13,
240 ATTRIBUTE14,
241 ATTRIBUTE15,
242 AUTO_SIGNOFF_FLAG,
243 COPY_INIT_ACCOMPL_FLAG,
244 COPY_DEFERRALS_FLAG,
245 APPLICATION_USG_CODE
246 from AHL_MR_HEADERS_APP_V
247 where mr_header_id=p_source_mr_header_id;
248
249 CURSOR CurGetDocTitledet
250 is
251 select
252 A.DOC_TITLE_ASSO_ID,
253 A.OBJECT_VERSION_NUMBER,
254 A.LAST_UPDATE_DATE,
255 A.LAST_UPDATED_BY,
256 A.CREATION_DATE,
257 A.CREATED_BY,
258 A.LAST_UPDATE_LOGIN,
259 A.DOC_REVISION_ID,
260 A.ASO_OBJECT_TYPE_CODE,
261 A.ASO_OBJECT_ID,
262 A.DOCUMENT_ID,
263 A.USE_LATEST_REV_FLAG,
264 A.SERIAL_NO,
265 A.SECURITY_GROUP_ID,
266 A.ATTRIBUTE_CATEGORY,
267 A.ATTRIBUTE1,
268 A.ATTRIBUTE2,
269 A.ATTRIBUTE3,
270 A.ATTRIBUTE4,
271 A.ATTRIBUTE5,
272 A.ATTRIBUTE6,
273 A.ATTRIBUTE7,
274 A.ATTRIBUTE8,
275 A.ATTRIBUTE9,
276 A.ATTRIBUTE10,
277 A.ATTRIBUTE11,
278 A.ATTRIBUTE12,
279 A.ATTRIBUTE13,
280 A.ATTRIBUTE14,
281 A.ATTRIBUTE15,
282 A.SOURCE_REF_CODE,
283 b.chapter,
284 b.section,
285 b.subject,
286 b.page,
287 b.figure,
288 b.note
289 from AHL_DOC_TITLE_ASSOS_B A,AHL_DOC_TITLE_ASSOS_TL B
290 where A.ASO_OBJECT_TYPE_CODE='MR'
291 AND A.ASO_OBJECT_ID=p_source_mr_header_id
292 and A.doc_title_asso_id=B.doc_title_asso_id
293 AND B.LANGUAGE=USERENV('LANG')
294 AND A.ASO_OBJECT_ID NOT IN (SELECT DOCUMENT_ID
295 FROM AHL_DOC_REVISIONS_VL
296 WHERE DOCUMENT_ID=A.ASO_OBJECT_ID
297 AND NVL(REVISION_STATUS_CODE,'CURRENT')='OBSOLETE');
298
299 l_doc_title_asso_id NUMBER:=0;
300
301 -- Routes
302 CURSOR CurGetRoutedet
303 IS
304 SELECT
305 MR_ROUTE_ID,
306 OBJECT_VERSION_NUMBER,
307 LAST_UPDATE_DATE,
308 LAST_UPDATED_BY,
309 CREATION_DATE,
310 CREATED_BY,
311 LAST_UPDATE_LOGIN,
312 MR_HEADER_ID,
313 ROUTE_ID,
314 --MANESING::VWP Enhancements, 21-Jan-2011, added stage type code
315 STAGE_TYPE_CODE,
316 SECURITY_GROUP_ID,
317 ATTRIBUTE_CATEGORY,
318 ATTRIBUTE1,
319 ATTRIBUTE2,
320 ATTRIBUTE3,
321 ATTRIBUTE4,
322 ATTRIBUTE5,
323 ATTRIBUTE6,
324 ATTRIBUTE7,
325 ATTRIBUTE8,
326 ATTRIBUTE9,
327 ATTRIBUTE10,
328 ATTRIBUTE11,
329 ATTRIBUTE12,
330 ATTRIBUTE13,
331 ATTRIBUTE14,
332 ATTRIBUTE15,
333 STAGE
334 FROM AHL_MR_ROUTES A
335 WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID
336 AND ROUTE_ID IN (SELECT ROUTE_ID
337 FROM AHL_ROUTES_B
338 WHERE ROUTE_ID=A.ROUTE_ID
339 AND NVL(END_DATE_ACTIVE,sysdate+1)>SYSDATE
340 AND REVISION_STATUS_CODE='COMPLETE'
341 );
342
343 l_TEMP_MR_ROUTE_TBL TEMP_MR_ROUTE_TBL;
344
345 -- Route Sequences
346 CURSOR CurGetRouteSeqDet(C_MR_ROUTE_ID NUMBER)
347 iS
348 SELECT MR_ROUTE_SEQUENCE_ID,
349 OBJECT_VERSION_NUMBER,
350 LAST_UPDATE_DATE,
351 LAST_UPDATED_BY,
352 CREATION_DATE,
353 CREATED_BY,
354 LAST_UPDATE_LOGIN,
355 MR_ROUTE_ID,
356 RELATED_MR_ROUTE_ID,
357 SEQUENCE_CODE,
358 ATTRIBUTE_CATEGORY,
359 ATTRIBUTE1,
360 ATTRIBUTE2,
361 ATTRIBUTE3,
362 ATTRIBUTE4,
363 ATTRIBUTE5,
364 ATTRIBUTE6,
365 ATTRIBUTE7,
366 ATTRIBUTE8,
367 ATTRIBUTE9,
368 ATTRIBUTE10,
369 ATTRIBUTE11,
370 ATTRIBUTE12,
371 ATTRIBUTE13,
372 ATTRIBUTE14,
373 ATTRIBUTE15
374 FROM AHL_MR_ROUTE_SEQUENCES C
375 WHERE MR_ROUTE_ID=C_MR_ROUTE_ID
376 AND EXISTS
377 (SELECT MR_ROUTE_ID
378 FROM AHL_MR_ROUTES A
379 WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID
380 AND MR_ROUTE_ID=C.RELATED_MR_ROUTE_ID
381 AND ROUTE_ID IN (SELECT ROUTE_ID
382 FROM AHL_ROUTES_B
383 WHERE ROUTE_ID=A.ROUTE_ID
384 AND NVL(END_DATE_ACTIVE,sysdate+1)>SYSDATE
385 AND REVISION_STATUS_CODE='COMPLETE'
386 )
387 );
388
389
390 l_mr_route_seq_rec CurGetRouteSeqDet%rowtype;
391 l_seq_mr_route_id NUMBER:=0;
392 l_seq_rel_mr_route_id NUMBER:=0;
393 l_mr_route_index NUMBER:=0;
394
395
396 -- Effectivity
397 CURSOR CurGetEffectDet
398 IS
399 SELECT
400 MR_EFFECTIVITY_ID,
401 OBJECT_VERSION_NUMBER,
402 LAST_UPDATE_DATE,
403 LAST_UPDATED_BY,
404 CREATION_DATE,
405 CREATED_BY,
406 LAST_UPDATE_LOGIN,
407 MR_HEADER_ID,
408 NAME,
409 THRESHOLD_DATE,
410 INVENTORY_ITEM_ID,
411 INVENTORY_ORG_ID,
412 RELATIONSHIP_ID,
413 PC_NODE_ID,
414 DEFAULT_FLAG,
415 PROGRAM_DURATION,
416 PROGRAM_DURATION_UOM_CODE,
417 FLEET_HEADER_ID, -- SANSATPA added for MR effectivity UI
418 SECURITY_GROUP_ID,
419 ATTRIBUTE_CATEGORY,
420 ATTRIBUTE1,
421 ATTRIBUTE2,
422 ATTRIBUTE3,
423 ATTRIBUTE4,
424 ATTRIBUTE5,
425 ATTRIBUTE6,
426 ATTRIBUTE7,
427 ATTRIBUTE8,
428 ATTRIBUTE9,
429 ATTRIBUTE10,
430 ATTRIBUTE11,
431 ATTRIBUTE12,
432 ATTRIBUTE13,
433 ATTRIBUTE14,
434 ATTRIBUTE15
435 FROM AHL_MR_EFFECTIVITIES_APP_V
436 WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID;
437
438 CURSOR CurGetMrIntervals(C_MR_EFFECTIVITY_ID NUMBER)
439 IS
440 SELECT
441 MR_INTERVAL_ID,
442 OBJECT_VERSION_NUMBER,
443 LAST_UPDATE_DATE,
444 LAST_UPDATED_BY,
445 CREATION_DATE,
446 CREATED_BY,
447 LAST_UPDATE_LOGIN,
448 MR_EFFECTIVITY_ID,
449 COUNTER_ID,
450 INTERVAL_VALUE,
451 EARLIEST_DUE_VALUE,
452 START_VALUE,
453 STOP_VALUE,
454 START_DATE,
455 CALC_DUEDATE_RULE_CODE, --pdoki added for ADAT ER
456 STOP_DATE,
457 TOLERANCE_BEFORE,
458 TOLERANCE_AFTER,
459 SECURITY_GROUP_ID,
460 ATTRIBUTE_CATEGORY,
461 ATTRIBUTE1,
462 ATTRIBUTE2,
463 ATTRIBUTE3,
464 ATTRIBUTE4,
465 ATTRIBUTE5,
466 ATTRIBUTE6,
467 ATTRIBUTE7,
468 ATTRIBUTE8,
469 ATTRIBUTE9,
470 ATTRIBUTE10,
471 ATTRIBUTE11,
472 ATTRIBUTE12,
473 ATTRIBUTE13,
474 ATTRIBUTE14,
475 ATTRIBUTE15,
476 RESET_VALUE
477 FROM AHL_MR_INTERVALS_APP_V
478 WHERE MR_EFFECTIVITY_ID=C_MR_EFFECTIVITY_ID;
479 l_interval_rec CurGetMrIntervals%rowtype;
480
481
482 -- Effectivity Details
483 CURSOR CurGetEffectDTLS(C_MR_EFFECTIVITY_ID NUMBER)
484 IS
485 SELECT
486 MR_EFFECTIVITY_DETAIL_ID,
487 OBJECT_VERSION_NUMBER,
488 LAST_UPDATE_DATE,
489 LAST_UPDATED_BY,
490 CREATION_DATE,
491 CREATED_BY,
492 LAST_UPDATE_LOGIN,
493 MR_EFFECTIVITY_ID,
494 EXCLUDE_FLAG,
495 MANUFACTURER_ID,
496 COUNTRY_CODE,
497 SERIAL_NUMBER_FROM,
498 SERIAL_NUMBER_TO,
499 MANUFACTURE_DATE_FROM,
500 MANUFACTURE_DATE_TO,
501 SECURITY_GROUP_ID,
502 ATTRIBUTE_CATEGORY,
503 ATTRIBUTE1,
504 ATTRIBUTE2,
505 ATTRIBUTE3,
506 ATTRIBUTE4,
507 ATTRIBUTE5,
508 ATTRIBUTE6,
509 ATTRIBUTE7,
510 ATTRIBUTE8,
511 ATTRIBUTE9,
512 ATTRIBUTE10,
513 ATTRIBUTE11,
514 ATTRIBUTE12,
515 ATTRIBUTE13,
516 ATTRIBUTE14,
517 ATTRIBUTE15
518 FROM AHL_MR_EFFECTIVITY_DTLS_APP_V
519 WHERE MR_EFFECTIVITY_ID=C_MR_EFFECTIVITY_ID;
520
521 -- Effectivity Details
522 CURSOR CurGetEffectExtDTLS(C_MR_EFFECTIVITY_ID NUMBER)
523 IS
524 SELECT
525 MR_EFFECTIVITY_EXT_DTL_ID,
526 OBJECT_VERSION_NUMBER,
527 LAST_UPDATE_DATE,
528 LAST_UPDATED_BY,
529 CREATION_DATE,
530 CREATED_BY,
531 LAST_UPDATE_LOGIN,
532 MR_EFFECTIVITY_ID,
533 EXCLUDE_FLAG,
534 EFFECT_EXT_DTL_REC_TYPE,
535 OWNER_ID,
536 LOCATION_TYPE_CODE,
537 CSI_EXT_ATTRIBUTE_CODE,
538 CSI_EXT_ATTRIBUTE_VALUE,
539 SECURITY_GROUP_ID,
540 ATTRIBUTE_CATEGORY,
541 ATTRIBUTE1,
542 ATTRIBUTE2,
543 ATTRIBUTE3,
544 ATTRIBUTE4,
545 ATTRIBUTE5,
546 ATTRIBUTE6,
547 ATTRIBUTE7,
548 ATTRIBUTE8,
549 ATTRIBUTE9,
550 ATTRIBUTE10,
551 ATTRIBUTE11,
552 ATTRIBUTE12,
553 ATTRIBUTE13,
554 ATTRIBUTE14,
555 ATTRIBUTE15
556 FROM AHL_MR_EFFECTIVITY_EXT_DTLS
557 WHERE MR_EFFECTIVITY_ID=C_MR_EFFECTIVITY_ID;
558
559 l_mr_effect_ext_dtls_rec CurGetEffectExtDTLS%rowtype;
560
561 CURSOR CurGetRelationDet
562 IS
563 SELECT
564 MR_RELATIONSHIP_ID,
565 OBJECT_VERSION_NUMBER,
566 LAST_UPDATE_DATE,
567 LAST_UPDATED_BY,
568 CREATION_DATE,
569 CREATED_BY,
570 LAST_UPDATE_LOGIN,
571 MR_HEADER_ID,
572 RELATED_MR_HEADER_ID,
573 RELATIONSHIP_CODE,
574 SECURITY_GROUP_ID,
575 ATTRIBUTE_CATEGORY,
576 ATTRIBUTE1,
577 ATTRIBUTE2,
578 ATTRIBUTE3,
579 ATTRIBUTE4,
580 ATTRIBUTE5,
581 ATTRIBUTE6,
582 ATTRIBUTE7,
583 ATTRIBUTE8,
584 ATTRIBUTE9,
585 ATTRIBUTE10,
586 ATTRIBUTE11,
587 ATTRIBUTE12,
588 ATTRIBUTE13,
589 ATTRIBUTE14,
590 ATTRIBUTE15
591 FROM AHL_MR_RELATIONSHIPS_APP_V A
592 WHERE (MR_HEADER_ID=P_SOURCE_MR_HEADER_ID or RELATED_MR_HEADER_ID=P_SOURCE_MR_HEADER_ID)
593 AND EXISTS(SELECT MR_HEADER_ID
594 FROM AHL_MR_HEADERS_APP_V
595 WHERE ( MR_HEADER_ID=A.MR_HEADER_ID
596 OR MR_HEADER_ID=A.RELATED_MR_HEADER_ID)
597 AND MR_STATUS_CODE<>'TERMINATED'
598 AND NVL(EFFECTIVE_TO,SYSDATE+1) >SYSDATE);
599
600
601 l_rel_rec CurGetRelationDet%rowtype;
602
603 l_mr_effect_dtls_rec CurGetEffectDTLS%rowtype;
604
605 Cursor CurGetMrVisitTypes
606 Is
607 SELECT
608 MR_VISIT_TYPE_ID,
609 OBJECT_VERSION_NUMBER,
610 LAST_UPDATE_DATE,
611 LAST_UPDATED_BY,
612 CREATION_DATE,
613 CREATED_BY,
614 LAST_UPDATE_LOGIN,
615 MR_VISIT_TYPE_CODE,
616 MR_HEADER_ID,
617 SECURITY_GROUP_ID,
618 ATTRIBUTE_CATEGORY,
619 ATTRIBUTE1,
620 ATTRIBUTE2,
621 ATTRIBUTE3,
622 ATTRIBUTE4,
623 ATTRIBUTE5,
624 ATTRIBUTE6,
625 ATTRIBUTE7,
626 ATTRIBUTE8,
627 ATTRIBUTE9,
628 ATTRIBUTE10,
629 ATTRIBUTE11,
630 ATTRIBUTE12,
631 ATTRIBUTE13,
632 ATTRIBUTE14,
633 ATTRIBUTE15
634 FROM AHL_MR_VISIT_TYPES
635 WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID;
636
637 l_mrvsttype_rec CurGetMrVisitTypes%rowtype;
638 --Changes for SBE begin: MR Loop/Chain relationships
639 CURSOR start_mr_loop_chain_relns_csr(p_mr_header_id_csr NUMBER)
640 IS
641 SELECT rel.MR_RELATIONSHIP_ID,
642 rel.OBJECT_VERSION_NUMBER,
643 rel.LAST_UPDATE_DATE,
644 rel.LAST_UPDATED_BY,
645 rel.CREATION_DATE,
646 rel.CREATED_BY,
647 rel.LAST_UPDATE_LOGIN,
648 rel.MR_HEADER_ID,
649 rel.START_MR_RELATIONSHIP_ID,
650 rel.RELATIONSHIP_CODE,
651 rel.SEQUENCE_NUMBER,
652 rel.SECURITY_GROUP_ID,
653 rel.ATTRIBUTE_CATEGORY,
654 rel.ATTRIBUTE1,
655 rel.ATTRIBUTE2,
656 rel.ATTRIBUTE3,
657 rel.ATTRIBUTE4,
658 rel.ATTRIBUTE5,
659 rel.ATTRIBUTE6,
660 rel.ATTRIBUTE7,
661 rel.ATTRIBUTE8,
662 rel.ATTRIBUTE9,
663 rel.ATTRIBUTE10,
664 rel.ATTRIBUTE11,
665 rel.ATTRIBUTE12,
666 rel.ATTRIBUTE13,
667 rel.ATTRIBUTE14,
668 rel.ATTRIBUTE15
669 FROM ahl_mr_loop_chain_relns rel,
670 ahl_mr_headers_b mr
671 WHERE mr.mr_header_id = rel.mr_header_id
672 AND rel.mr_relationship_id IN
673 (
674 SELECT rel.MR_RELATIONSHIP_ID
675 FROM ahl_mr_loop_chain_relns rel
676 WHERE start_mr_relationship_id IN
677 (SELECT mr_relationship_id
678 FROM ahl_mr_loop_chain_relns
679 WHERE mr_header_id = p_mr_header_id_csr
680 )
681 )
682 AND TRUNC(sysdate) <= TRUNC(NVL(MR.effective_to,sysdate+1))
683 AND mr.mr_status_code <> 'TERMINATED'
684 ORDER BY rel.START_MR_RELATIONSHIP_ID,rel.sequence_number;
685
686 CURSOR mr_loop_chain_relns_csr(p_mr_header_id_csr NUMBER)
687 IS
688 SELECT rel.MR_RELATIONSHIP_ID,
689 rel.OBJECT_VERSION_NUMBER,
690 rel.LAST_UPDATE_DATE,
691 rel.LAST_UPDATED_BY,
692 rel.CREATION_DATE,
693 rel.CREATED_BY,
694 rel.LAST_UPDATE_LOGIN,
695 rel.MR_HEADER_ID,
696 rel.START_MR_RELATIONSHIP_ID,
697 rel.RELATIONSHIP_CODE,
698 rel.SEQUENCE_NUMBER,
699 rel.SECURITY_GROUP_ID,
700 rel.ATTRIBUTE_CATEGORY,
701 rel.ATTRIBUTE1,
702 rel.ATTRIBUTE2,
703 rel.ATTRIBUTE3,
704 rel.ATTRIBUTE4,
705 rel.ATTRIBUTE5,
706 rel.ATTRIBUTE6,
707 rel.ATTRIBUTE7,
708 rel.ATTRIBUTE8,
709 rel.ATTRIBUTE9,
710 rel.ATTRIBUTE10,
711 rel.ATTRIBUTE11,
712 rel.ATTRIBUTE12,
713 rel.ATTRIBUTE13,
714 rel.ATTRIBUTE14,
715 rel.ATTRIBUTE15
716 FROM ahl_mr_loop_chain_relns rel,
717 ahl_mr_headers_b mr
718 WHERE mr.mr_header_id = rel.mr_header_id
719 AND rel.mr_relationship_id IN
720 (SELECT mr_relationship_id
721 FROM ahl_mr_loop_chain_relns
722 WHERE mr_header_id = p_mr_header_id_csr
723 AND mr_relationship_id <> start_mr_relationship_id
724 )
725 AND TRUNC(sysdate) <= TRUNC(NVL(MR.effective_to,sysdate+1))
726 AND mr.mr_status_code <> 'TERMINATED'
727 ORDER BY rel.START_MR_RELATIONSHIP_ID,rel.sequence_number;
728
729 l_loop_chain_rel_rec mr_loop_chain_relns_csr%rowtype;
730 l_start_loop_chain_rel_rec start_mr_loop_chain_relns_csr%rowtype;
731 l_mr_loop_chain_rel_id NUMBER;
732 l_start_mr_rel_id NUMBER;
733 --Changes for SBE Ends
734
735 l_row_id VARCHAR2(30);
736 l_mr_relationship_id NUMBER:=0;
737 l_old_mr_route_id NUMBER:=0;
738 l_new_mr_route_id NUMBER:=0;
739 l_new_mr_route_seq_id NUMBER:=0;
740 l_new_mr_effectivity_id NUMBER:=0;
741 l_old_mr_effectivity_id NUMBER:=0;
742 l_new_mr_effectivity_dtl_id NUMBER:=0;
743 l_old_mr_effectivity_dtl_id NUMBER:=0;
744 l_version_number NUMBER:=0;
745
746 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MR_REVISION';
747 l_api_version CONSTANT NUMBER := 1.0;
748 l_msg_count NUMBER;
749 l_msg_data VARCHAR2(2000);
750 l_date DATE;
751 BEGIN
752
753 SAVEPOINT CREATE_MR_REVISION_PVT;
754
755 IF G_DEBUG='Y' THEN
756 AHL_DEBUG_PUB.enable_debug;
757 AHL_DEBUG_PUB.debug( ' START CREATE MR REVISION ');
758 END IF;
759
760 -- Standard call to check for call compatibility.
761
762 IF FND_API.to_boolean(p_init_msg_list) THEN
763 FND_MSG_PUB.initialize;
764 END IF;
765
766 -- Initialize API return status to success
767
768 x_return_status := FND_API.G_RET_STS_SUCCESS;
769
770 -- Initialize message list if p_init_msg_list is set to TRUE.
771 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
772 p_api_version,
773 l_api_name,G_PKG_NAME) THEN
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
775 END IF;
776 --Start of API Body
777
778 VALIDATE_MR_REV
779 (
780 x_return_status =>x_return_Status,
781 x_msg_count =>l_msg_count,
782 x_msg_data =>l_msg_data,
783 p_source_mr_header_id =>p_source_mr_header_id
784 );
785
786 l_msg_count := FND_MSG_PUB.count_msg;
787
788 IF l_msg_count > 0 THEN
789 X_msg_count := l_msg_count;
790 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793
794
795 --Start of API Body
796
797
798 for l_mr_header_rec in CurGetHeaderdet
799 loop
800 l_date:=sysdate;
801 IF l_mr_header_Rec.EFFECTIVE_FROM>SYSDATE
802 THEN
803 l_date:=l_mr_header_Rec.EFFECTIVE_FROM;
804 END IF;
805
806 l_version_number:=l_mr_header_Rec.version_number + 1;
807 AHL_MR_HEADERS_PKG.INSERT_ROW (
808 X_MR_HEADER_ID =>x_new_mr_header_id,
809 X_OBJECT_VERSION_NUMBER =>1,
810 X_CATEGORY_CODE =>l_mr_header_Rec.CATEGORY_CODE,
811 X_SERVICE_TYPE_CODE =>l_mr_header_Rec.SERVICE_TYPE_CODE,
812 X_MR_STATUS_CODE =>'DRAFT',
813 X_IMPLEMENT_STATUS_CODE =>l_mr_header_Rec.IMPLEMENT_STATUS_CODE,
814 X_REPETITIVE_FLAG =>l_mr_header_Rec.REPETITIVE_FLAG,
815 X_SHOW_REPETITIVE_CODE =>l_mr_header_Rec.SHOW_REPETITIVE_CODE,
816 X_WHICHEVER_FIRST_CODE =>l_mr_header_Rec.WHICHEVER_FIRST_CODE,
817 X_COPY_ACCOMPLISHMENT_FLAG=> 'Y',-- defaulting l_mr_header_Rec.COPY_ACCOMPLISHMENT_FLAG,
818 X_PROGRAM_TYPE_CODE =>l_mr_header_Rec.PROGRAM_TYPE_CODE ,
819 X_PROGRAM_SUBTYPE_CODE =>l_mr_header_Rec.PROGRAM_SUBTYPE_CODE,
820 X_EFFECTIVE_FROM =>l_date,
821 X_EFFECTIVE_TO =>NULL,
822 X_REVISION =>l_mr_header_Rec.REVISION,
823 X_ATTRIBUTE_CATEGORY =>l_mr_header_Rec.ATTRIBUTE_CATEGORY,
824 X_ATTRIBUTE1 =>l_mr_header_Rec.ATTRIBUTE1,
825 X_ATTRIBUTE2 =>l_mr_header_Rec.ATTRIBUTE2,
826 X_ATTRIBUTE3 =>l_mr_header_Rec.ATTRIBUTE3,
827 X_ATTRIBUTE4 =>l_mr_header_Rec.ATTRIBUTE4,
828 X_ATTRIBUTE5 =>l_mr_header_Rec.ATTRIBUTE5,
829 X_ATTRIBUTE6 =>l_mr_header_Rec.ATTRIBUTE6,
830 X_ATTRIBUTE7 =>l_mr_header_Rec.ATTRIBUTE7,
831 X_ATTRIBUTE8 =>l_mr_header_Rec.ATTRIBUTE8,
832 X_ATTRIBUTE9 =>l_mr_header_Rec.ATTRIBUTE9,
833 X_ATTRIBUTE10 =>l_mr_header_Rec.ATTRIBUTE10,
834 X_ATTRIBUTE11 =>l_mr_header_Rec.ATTRIBUTE11,
835 X_ATTRIBUTE12 =>l_mr_header_Rec.ATTRIBUTE12,
836 X_ATTRIBUTE13 =>l_mr_header_Rec.ATTRIBUTE13,
837 X_ATTRIBUTE14 =>l_mr_header_Rec.ATTRIBUTE14,
838 X_ATTRIBUTE15 =>l_mr_header_Rec.ATTRIBUTE15,
839 X_TITLE =>l_mr_header_Rec.TITLE,
840 X_VERSION_NUMBER =>l_version_number,
841 -- pdoki, Preceding MR Header ID is obsoleted after SBE Project.
842 X_PRECEDING_MR_HEADER_ID=>l_mr_header_Rec.PRECEDING_MR_HEADER_ID,
843 X_SERVICE_REQUEST_TEMPLATE_ID=>l_mr_header_Rec.SERVICE_REQUEST_TEMPLATE_ID,
844 X_TYPE_CODE =>l_mr_header_Rec.TYPE_CODE,
845 X_DOWN_TIME =>l_mr_header_Rec.DOWN_TIME,
846 --CHANDANK, 29-Sep-2010, added supplier warranty ID for supplier warranty project
847 X_WARRANTY_TEMPLATE_ID =>l_mr_header_Rec.WARRANTY_TEMPLATE_ID,
848 X_UOM_CODE =>l_mr_header_Rec.UOM_CODE,
849 X_DESCRIPTION =>l_mr_header_Rec.DESCRIPTION,
850 X_COMMENTS =>l_mr_header_Rec.COMMENTS,
851 X_SPACE_CATEGORY_CODE =>l_mr_header_Rec.SPACE_CATEGORY_CODE,
852 --sareepar service category rank
853 X_SERVICE_CATEGORY_RANK =>l_mr_header_Rec.SERVICE_CATEGORY_RANK,
854 X_QA_INSPECTION_TYPE_CODE =>l_mr_header_Rec.QA_INSPECTION_TYPE_CODE,
855 X_BILLING_ITEM_ID =>l_mr_header_Rec.BILLING_ITEM_ID,
856 X_AUTO_SIGNOFF_FLAG =>l_mr_header_Rec.AUTO_SIGNOFF_FLAG,
857 -- defaulting to Yes for these attributes when new revision is created
858 X_COPY_INIT_ACCOMPL_FLAG =>'Y',
859 X_COPY_DEFERRALS_FLAG =>'Y',
860 X_CREATION_DATE =>sysdate,
861 X_CREATED_BY =>fnd_global.user_id,
862 X_LAST_UPDATE_DATE =>sysdate,
863 X_LAST_UPDATED_BY =>fnd_global.user_id,
864 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
865 end loop;
866
867
868 for l_association_rec in CurGetDocTitledet
869 loop
870
871 SELECT AHL_DOC_TITLE_ASSOS_B_S.Nextval INTO
872 l_doc_title_asso_id from DUAL;
873
874
875 AHL_DOC_TITLE_ASSOS_PKG.INSERT_ROW(
876 X_ROWID =>l_row_id,
877 X_DOC_TITLE_ASSO_ID =>l_doc_title_asso_id,
878 X_SERIAL_NO =>l_association_rec.serial_no,
879 X_ATTRIBUTE_CATEGORY =>l_association_rec.attribute_category,
880 X_ATTRIBUTE1 =>l_association_rec.attribute1,
881 X_ATTRIBUTE2 =>l_association_rec.attribute2,
882 X_ATTRIBUTE3 =>l_association_rec.attribute3,
883 X_ATTRIBUTE4 =>l_association_rec.attribute4,
884 X_ATTRIBUTE5 =>l_association_rec.attribute5,
885 X_ATTRIBUTE6 =>l_association_rec.attribute6,
886 X_ATTRIBUTE7 =>l_association_rec.attribute7,
887 X_ATTRIBUTE8 =>l_association_rec.attribute8,
888 X_ATTRIBUTE9 =>l_association_rec.attribute9,
889 X_ATTRIBUTE10 =>l_association_rec.attribute10,
890 X_ATTRIBUTE11 =>l_association_rec.attribute11,
891 X_ATTRIBUTE12 =>l_association_rec.attribute12,
892 X_ATTRIBUTE13 =>l_association_rec.attribute13,
893 X_ATTRIBUTE14 =>l_association_rec.attribute14,
894 X_ATTRIBUTE15 =>l_association_rec.attribute15,
895 X_ASO_OBJECT_TYPE_CODE =>l_association_rec.aso_object_type_code,
896 X_SOURCE_REF_CODE =>l_association_rec.source_ref_code,
897 X_ASO_OBJECT_ID =>x_new_mr_header_id,
898 X_DOCUMENT_ID =>l_association_rec.document_id,
899 X_USE_LATEST_REV_FLAG =>l_association_rec.use_latest_rev_flag,
900 X_DOC_REVISION_ID =>l_association_rec.doc_revision_id,
901 X_OBJECT_VERSION_NUMBER =>1,
902 X_CHAPTER =>l_association_rec.chapter,
903 X_SECTION =>l_association_rec.section,
904 X_SUBJECT =>l_association_rec.subject,
905 X_FIGURE =>l_association_rec.figure,
906 X_PAGE =>l_association_rec.page,
907 X_NOTE =>l_association_rec.note,
908 X_CREATION_DATE =>sysdate,
909 X_CREATED_BY =>fnd_global.user_id,
910 X_LAST_UPDATE_DATE =>sysdate,
911 X_LAST_UPDATED_BY =>fnd_global.user_id,
912 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
913
914 end loop;
915
916 for l_mr_route_rec in CurGetRoutedet
917 loop
918
919
920 l_old_mr_route_id:=l_mr_route_Rec.mr_route_id;
921
922 AHL_MR_ROUTES_PKG.INSERT_ROW (
923 X_MR_ROUTE_ID =>l_new_mr_ROUTE_ID,
924 X_STAGE =>l_mr_route_rec.STAGE,
925 X_OBJECT_VERSION_NUMBER =>1,
926 X_MR_HEADER_ID =>x_new_mr_header_id,
927 X_ROUTE_ID =>l_mr_route_Rec.ROUTE_ID,
928 --MANESING::VWP Enhancements, 21-Jan-2011, added stage type code column
929 X_STAGE_TYPE_CODE =>l_mr_route_Rec.STAGE_TYPE_CODE,
930 X_ATTRIBUTE_CATEGORY =>l_mr_route_Rec.ATTRIBUTE_CATEGORY,
931 X_ATTRIBUTE1 =>l_mr_route_Rec.ATTRIBUTE1,
932 X_ATTRIBUTE2 =>l_mr_route_Rec.ATTRIBUTE2,
933 X_ATTRIBUTE3 =>l_mr_route_Rec.ATTRIBUTE3,
934 X_ATTRIBUTE4 =>l_mr_route_Rec.ATTRIBUTE4,
935 X_ATTRIBUTE5 =>l_mr_route_Rec.ATTRIBUTE5,
936 X_ATTRIBUTE6 =>l_mr_route_Rec.ATTRIBUTE6,
937 X_ATTRIBUTE7 =>l_mr_route_Rec.ATTRIBUTE7,
938 X_ATTRIBUTE8 =>l_mr_route_Rec.ATTRIBUTE8,
939 X_ATTRIBUTE9 =>l_mr_route_Rec.ATTRIBUTE9,
940 X_ATTRIBUTE10 =>l_mr_route_Rec.ATTRIBUTE11,
941 X_ATTRIBUTE11 =>l_mr_route_Rec.ATTRIBUTE12,
942 X_ATTRIBUTE12 =>l_mr_route_Rec.ATTRIBUTE13,
943 X_ATTRIBUTE13 =>l_mr_route_Rec.ATTRIBUTE14,
944 X_ATTRIBUTE14 =>l_mr_route_Rec.ATTRIBUTE15,
945 X_ATTRIBUTE15 =>l_mr_route_Rec.ATTRIBUTE15,
946 X_CREATION_DATE =>sysdate,
947 X_CREATED_BY =>fnd_global.user_id,
948 X_LAST_UPDATE_DATE =>sysdate,
949 X_LAST_UPDATED_BY =>fnd_global.user_id,
950 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
951 L_MR_ROUTE_INDEX:=L_MR_ROUTE_INDEX+1;
952 l_temp_mr_route_tbl(L_MR_ROUTE_INDEX).OLD_MR_ROUTE_ID:=l_old_mr_route_id;
953 l_temp_mr_route_tbl(L_MR_ROUTE_INDEX).NEW_MR_ROUTE_ID:=l_new_mr_ROUTE_ID;
954 End loop;
955
956
957 -- Route Sequences
958
959 FOR I IN l_temp_mr_route_tbl.FIRST.. l_temp_mr_route_tbl.LAST
960 LOOP
961
962 OPEN CurGetRouteSeqDet(l_temp_mr_route_tbl(I).OLD_MR_ROUTE_ID);
963 loop
964
965 FETCH CurGetRouteSeqDet INTO l_mr_route_seq_rec;
966 EXIT WHEN CurGetRouteSeqDet%NOTFOUND;
967
968 l_seq_mr_route_id :=l_temp_mr_route_tbl(I).NEW_MR_ROUTE_ID;
969
970 FOR J IN l_temp_mr_route_tbl.FIRST..l_temp_mr_route_tbl.LAST
971 LOOP
972 IF l_temp_mr_route_tbl(J).OLD_MR_ROUTE_ID=l_mr_route_seq_rec.related_mr_route_id
973 THEN
974 l_seq_rel_mr_route_id:=l_temp_mr_route_tbl(J).NEW_MR_ROUTE_ID;
975 EXIT WHEN l_temp_mr_route_tbl(J).OLD_MR_ROUTE_ID=l_mr_route_seq_rec.related_mr_route_id;
976 END IF;
977 END LOOP;
978
979
980 AHL_MR_ROUTE_SEQUENCES_PKG.INSERT_ROW (
981 X_MR_ROUTE_SEQUENCE_ID =>l_new_mr_route_seq_id,
982 X_RELATED_MR_ROUTE_ID =>l_seq_rel_mr_route_id,
983 X_SEQUENCE_CODE =>l_mr_route_seq_rec.SEQUENCE_CODE,
984 X_MR_ROUTE_ID =>l_seq_mr_route_id,
985 X_OBJECT_VERSION_NUMBER =>1,
986 X_ATTRIBUTE_CATEGORY =>l_mr_route_seq_rec.ATTRIBUTE_CATEGORY,
987 X_ATTRIBUTE1 =>l_mr_route_seq_rec.ATTRIBUTE1,
988 X_ATTRIBUTE2 =>l_mr_route_seq_rec.ATTRIBUTE2,
989 X_ATTRIBUTE3 =>l_mr_route_seq_rec.ATTRIBUTE3,
990 X_ATTRIBUTE4 =>l_mr_route_seq_rec.ATTRIBUTE4,
991 X_ATTRIBUTE5 =>l_mr_route_seq_rec.ATTRIBUTE5,
992 X_ATTRIBUTE6 =>l_mr_route_seq_rec.ATTRIBUTE6,
993 X_ATTRIBUTE7 =>l_mr_route_seq_rec.ATTRIBUTE7,
994 X_ATTRIBUTE8 =>l_mr_route_seq_rec.ATTRIBUTE8,
995 X_ATTRIBUTE9 =>l_mr_route_seq_rec.ATTRIBUTE9,
996 X_ATTRIBUTE10 =>l_mr_route_seq_rec.ATTRIBUTE10,
997 X_ATTRIBUTE11 =>l_mr_route_seq_rec.ATTRIBUTE11,
998 X_ATTRIBUTE12 =>l_mr_route_seq_rec.ATTRIBUTE12,
999 X_ATTRIBUTE13 =>l_mr_route_seq_rec.ATTRIBUTE13,
1000 X_ATTRIBUTE14 =>l_mr_route_seq_rec.ATTRIBUTE14,
1001 X_ATTRIBUTE15 =>l_mr_route_seq_rec.ATTRIBUTE15,
1002 X_CREATION_DATE =>sysdate,
1003 X_CREATED_BY =>fnd_global.user_id,
1004 X_LAST_UPDATE_DATE =>sysdate,
1005 X_LAST_UPDATED_BY =>fnd_global.user_id,
1006 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
1007 end loop;
1008 CLOSE CurGetRouteSeqDet;
1009
1010 END LOOP;
1011 -- Effectivity
1012 for l_mr_eff_rec in CurGetEffectDet
1013 loop
1014 l_old_mr_effectivity_id:=l_mr_eff_rec.mr_effectivity_id;
1015
1016 INSERT INTO AHL_MR_EFFECTIVITIES
1017 (
1018 MR_EFFECTIVITY_ID,
1019 OBJECT_VERSION_NUMBER,
1020 LAST_UPDATE_DATE,
1021 LAST_UPDATED_BY,
1022 CREATION_DATE,
1023 CREATED_BY,
1024 LAST_UPDATE_LOGIN,
1025 MR_HEADER_ID,
1026 NAME,
1027 THRESHOLD_DATE,
1028 INVENTORY_ITEM_ID,
1029 INVENTORY_ORG_ID,
1030 RELATIONSHIP_ID,
1031 PC_NODE_ID,
1032 FLEET_HEADER_ID, -- SANSATPA added for MR effectivity UI
1033 ATTRIBUTE_CATEGORY,
1034 ATTRIBUTE1,
1035 ATTRIBUTE2,
1036 ATTRIBUTE3,
1037 ATTRIBUTE4,
1038 ATTRIBUTE5,
1039 ATTRIBUTE6,
1040 ATTRIBUTE7,
1041 ATTRIBUTE8,
1042 ATTRIBUTE9,
1043 ATTRIBUTE10,
1044 ATTRIBUTE11,
1045 ATTRIBUTE12,
1046 ATTRIBUTE13,
1047 ATTRIBUTE14,
1048 ATTRIBUTE15
1049 )
1050 VALUES
1051 (
1052 AHL_MR_EFFECTIVITIES_S.NEXTVAL,
1053 1,
1054 SYSDATE,
1055 fnd_global.user_id,
1056 SYSDATE,
1057 fnd_global.user_id,
1058 fnd_global.user_id,
1059 x_new_mr_header_id,
1060 l_mr_eff_rec.NAME,
1061 l_mr_eff_rec.THRESHOLD_DATE,
1062 l_mr_eff_rec.INVENTORY_ITEM_ID,
1063 l_mr_eff_rec.INVENTORY_ORG_ID,
1064 l_mr_eff_rec.RELATIONSHIP_ID,
1065 l_mr_eff_rec.PC_NODE_ID,
1066 l_mr_eff_rec.FLEET_HEADER_ID, -- SANSATPA added for MR effectivity UI
1067 l_mr_eff_rec.ATTRIBUTE_CATEGORY,
1068 l_mr_eff_rec.ATTRIBUTE1,
1069 l_mr_eff_rec.ATTRIBUTE2,
1070 l_mr_eff_rec.ATTRIBUTE3,
1071 l_mr_eff_rec.ATTRIBUTE4,
1072 l_mr_eff_rec.ATTRIBUTE5,
1073 l_mr_eff_rec.ATTRIBUTE6,
1074 l_mr_eff_rec.ATTRIBUTE7,
1075 l_mr_eff_rec.ATTRIBUTE8,
1076 l_mr_eff_rec.ATTRIBUTE9,
1077 l_mr_eff_rec.ATTRIBUTE10,
1078 l_mr_eff_rec.ATTRIBUTE11,
1079 l_mr_eff_rec.ATTRIBUTE12,
1080 l_mr_eff_rec.ATTRIBUTE13,
1081 l_mr_eff_rec.ATTRIBUTE14,
1082 l_mr_eff_rec.ATTRIBUTE15
1083 )
1084 RETURNING mr_effectivity_id INTO l_new_mr_effectivity_id;
1085 OPEN CurGetMrIntervals(l_old_mr_effectivity_id);
1086 LOOP
1087
1088 FETCH CurGetMrIntervals into l_interval_rec;
1089
1090 IF CurGetMrIntervals%FOUND
1091 THEN
1092
1093 l_interval_rec.OBJECT_VERSION_NUMBER:=1;
1094
1095 INSERT INTO AHL_MR_INTERVALS
1096 (
1097 MR_INTERVAL_ID,
1098 OBJECT_VERSION_NUMBER,
1099 LAST_UPDATE_DATE,
1100 LAST_UPDATED_BY,
1101 CREATION_DATE,
1102 CREATED_BY,
1103 LAST_UPDATE_LOGIN,
1104 MR_EFFECTIVITY_ID,
1105 COUNTER_ID,
1106 INTERVAL_VALUE,
1107 EARLIEST_DUE_VALUE,
1108 START_VALUE,
1109 STOP_VALUE,
1110 START_DATE,
1111 CALC_DUEDATE_RULE_CODE, --pdoki added for ADAT ER
1112 STOP_DATE,
1113 TOLERANCE_BEFORE,
1114 TOLERANCE_AFTER,
1115 SECURITY_GROUP_ID,
1116 ATTRIBUTE_CATEGORY,
1117 ATTRIBUTE1,
1118 ATTRIBUTE2,
1119 ATTRIBUTE3,
1120 ATTRIBUTE4,
1121 ATTRIBUTE5,
1122 ATTRIBUTE6,
1123 ATTRIBUTE7,
1124 ATTRIBUTE8,
1125 ATTRIBUTE9,
1126 ATTRIBUTE10,
1127 ATTRIBUTE11,
1128 ATTRIBUTE12,
1129 ATTRIBUTE13,
1130 ATTRIBUTE14,
1131 ATTRIBUTE15
1132 )
1133 VALUES
1134 (
1135 AHL_MR_INTERVALS_S.NEXTVAL,
1136 l_interval_rec.OBJECT_VERSION_NUMBER,
1137 sysdate,
1138 fnd_global.user_id,
1139 sysdate,
1140 fnd_global.user_id,
1141 fnd_global.user_id,
1142 l_new_mr_effectivity_id,
1143 l_interval_rec.COUNTER_ID,
1144 l_interval_rec.INTERVAL_VALUE,
1145 l_interval_rec.EARLIEST_DUE_VALUE,
1146 l_interval_rec.START_VALUE,
1147 l_interval_rec.STOP_VALUE,
1148 l_interval_rec.START_DATE,
1149 l_interval_rec.CALC_DUEDATE_RULE_CODE, --pdoki added for ADAT ER
1150 l_interval_rec.STOP_DATE,
1151 l_interval_rec.TOLERANCE_BEFORE,
1152 l_interval_rec.TOLERANCE_AFTER,
1153 l_interval_rec.SECURITY_GROUP_ID,
1154 l_interval_rec.ATTRIBUTE_CATEGORY,
1155 l_interval_rec.ATTRIBUTE1,
1156 l_interval_rec.ATTRIBUTE2,
1157 l_interval_rec.ATTRIBUTE3,
1158 l_interval_rec.ATTRIBUTE4,
1159 l_interval_rec.ATTRIBUTE5,
1160 l_interval_rec.ATTRIBUTE6,
1161 l_interval_rec.ATTRIBUTE7,
1162 l_interval_rec.ATTRIBUTE8,
1163 l_interval_rec.ATTRIBUTE9,
1164 l_interval_rec.ATTRIBUTE10,
1165 l_interval_rec.ATTRIBUTE11,
1166 l_interval_rec.ATTRIBUTE12,
1167 l_interval_rec.ATTRIBUTE13,
1168 l_interval_rec.ATTRIBUTE14,
1169 l_interval_rec.ATTRIBUTE15
1170 );
1171 ELSE
1172 EXIT WHEN CurGetMrIntervals%NOTFOUND;
1173 END IF;
1174
1175 END LOOP;
1176
1177 CLOSE CurGetMrIntervals;
1178
1179 OPEN CurGetEffectDTLS(l_old_mr_effectivity_id);
1180 loop
1181 FETCH CurGetEffectDTLS INTO l_mr_effect_dtls_rec;
1182
1183 IF CurGetEffectDTLS%FOUND
1184 THEN
1185
1186 INSERT INTO AHL_MR_EFFECTIVITY_DTLS
1187 (
1188 MR_EFFECTIVITY_DETAIL_ID,
1189 MR_EFFECTIVITY_ID,
1190 EXCLUDE_FLAG,
1191 MANUFACTURER_ID,
1192 COUNTRY_CODE,
1193 SERIAL_NUMBER_FROM,
1194 SERIAL_NUMBER_TO,
1195 MANUFACTURE_DATE_FROM,
1196 MANUFACTURE_DATE_TO,
1197 OBJECT_VERSION_NUMBER,
1198 LAST_UPDATE_DATE,
1199 LAST_UPDATED_BY,
1200 CREATION_DATE,
1201 CREATED_BY,
1202 LAST_UPDATE_LOGIN,
1203 ATTRIBUTE_CATEGORY,
1204 ATTRIBUTE1,
1205 ATTRIBUTE2,
1206 ATTRIBUTE3,
1207 ATTRIBUTE4,
1208 ATTRIBUTE5,
1209 ATTRIBUTE6,
1210 ATTRIBUTE7,
1211 ATTRIBUTE8,
1212 ATTRIBUTE9,
1213 ATTRIBUTE10,
1214 ATTRIBUTE11,
1215 ATTRIBUTE12,
1216 ATTRIBUTE13,
1217 ATTRIBUTE14,
1218 ATTRIBUTE15
1219 )
1220 VALUES
1221 (
1222 AHL_MR_EFFECTIVITY_DTLS_S.NEXTVAL,
1223 l_new_mr_EFFECTIVITY_ID,
1224 l_mr_effect_dtls_rec.EXCLUDE_FLAG,
1225 l_mr_effect_dtls_rec.MANUFACTURER_ID,
1226 l_mr_effect_dtls_rec.COUNTRY_CODE,
1227 l_mr_effect_dtls_rec.SERIAL_NUMBER_FROM,
1228 l_mr_effect_dtls_rec.SERIAL_NUMBER_TO,
1229 l_mr_effect_dtls_rec.MANUFACTURE_DATE_FROM,
1230 l_mr_effect_dtls_rec.MANUFACTURE_DATE_TO,
1231 1,
1232 SYSDATE,
1233 fnd_global.user_id,
1234 SYSDATE,
1235 fnd_global.user_id,
1236 fnd_global.user_id,
1237 l_mr_effect_dtls_rec.ATTRIBUTE_CATEGORY,
1238 l_mr_effect_dtls_rec.ATTRIBUTE1,
1239 l_mr_effect_dtls_rec.ATTRIBUTE2,
1240 l_mr_effect_dtls_rec.ATTRIBUTE3,
1241 l_mr_effect_dtls_rec.ATTRIBUTE4,
1242 l_mr_effect_dtls_rec.ATTRIBUTE5,
1243 l_mr_effect_dtls_rec.ATTRIBUTE6,
1244 l_mr_effect_dtls_rec.ATTRIBUTE7,
1245 l_mr_effect_dtls_rec.ATTRIBUTE8,
1246 l_mr_effect_dtls_rec.ATTRIBUTE9,
1247 l_mr_effect_dtls_rec.ATTRIBUTE10,
1248 l_mr_effect_dtls_rec.ATTRIBUTE11,
1249 l_mr_effect_dtls_rec.ATTRIBUTE12,
1250 l_mr_effect_dtls_rec.ATTRIBUTE13,
1251 l_mr_effect_dtls_rec.ATTRIBUTE14,
1252 l_mr_effect_dtls_rec.ATTRIBUTE15
1253 );
1254 ELSE
1255 EXIT WHEN CurGetEffectDTLS%NOTFOUND;
1256 END IF;
1257 end loop;
1258 CLOSE CurGetEffectDTLS;
1259
1260 OPEN CurGetEffectExtDTLS(l_old_mr_effectivity_id);
1261 loop
1262 FETCH CurGetEffectExtDTLS INTO l_mr_effect_ext_dtls_rec;
1263
1264 IF CurGetEffectExtDTLS%FOUND
1265 THEN
1266
1267 INSERT INTO AHL_MR_EFFECTIVITY_EXT_DTLS
1268 (
1269 MR_EFFECTIVITY_EXT_DTL_ID,
1270 MR_EFFECTIVITY_ID,
1271 EXCLUDE_FLAG,
1272 EFFECT_EXT_DTL_REC_TYPE,
1273 OWNER_ID,
1274 LOCATION_TYPE_CODE,
1275 CSI_EXT_ATTRIBUTE_CODE,
1276 CSI_EXT_ATTRIBUTE_VALUE,
1277 OBJECT_VERSION_NUMBER,
1278 LAST_UPDATE_DATE,
1279 LAST_UPDATED_BY,
1280 CREATION_DATE,
1281 CREATED_BY,
1282 LAST_UPDATE_LOGIN,
1283 ATTRIBUTE_CATEGORY,
1284 ATTRIBUTE1,
1285 ATTRIBUTE2,
1286 ATTRIBUTE3,
1287 ATTRIBUTE4,
1288 ATTRIBUTE5,
1289 ATTRIBUTE6,
1290 ATTRIBUTE7,
1291 ATTRIBUTE8,
1292 ATTRIBUTE9,
1293 ATTRIBUTE10,
1294 ATTRIBUTE11,
1295 ATTRIBUTE12,
1296 ATTRIBUTE13,
1297 ATTRIBUTE14,
1298 ATTRIBUTE15
1299 )
1300 VALUES
1301 (
1302 AHL_MR_EFFECTIVITY_EXT_DTLS_S.NEXTVAL,
1303 l_new_mr_EFFECTIVITY_ID,
1304 l_mr_effect_ext_dtls_rec.EXCLUDE_FLAG,
1305 l_mr_effect_ext_dtls_rec.EFFECT_EXT_DTL_REC_TYPE,
1306 l_mr_effect_ext_dtls_rec.OWNER_ID,
1307 l_mr_effect_ext_dtls_rec.LOCATION_TYPE_CODE,
1308 l_mr_effect_ext_dtls_rec.CSI_EXT_ATTRIBUTE_CODE,
1309 l_mr_effect_ext_dtls_rec.CSI_EXT_ATTRIBUTE_VALUE,
1310 1,
1311 SYSDATE,
1312 fnd_global.user_id,
1313 SYSDATE,
1314 fnd_global.user_id,
1315 fnd_global.user_id,
1316 l_mr_effect_ext_dtls_rec.ATTRIBUTE_CATEGORY,
1317 l_mr_effect_ext_dtls_rec.ATTRIBUTE1,
1318 l_mr_effect_ext_dtls_rec.ATTRIBUTE2,
1319 l_mr_effect_ext_dtls_rec.ATTRIBUTE3,
1320 l_mr_effect_ext_dtls_rec.ATTRIBUTE4,
1321 l_mr_effect_ext_dtls_rec.ATTRIBUTE5,
1322 l_mr_effect_ext_dtls_rec.ATTRIBUTE6,
1323 l_mr_effect_ext_dtls_rec.ATTRIBUTE7,
1324 l_mr_effect_ext_dtls_rec.ATTRIBUTE8,
1325 l_mr_effect_ext_dtls_rec.ATTRIBUTE9,
1326 l_mr_effect_ext_dtls_rec.ATTRIBUTE10,
1327 l_mr_effect_ext_dtls_rec.ATTRIBUTE11,
1328 l_mr_effect_ext_dtls_rec.ATTRIBUTE12,
1329 l_mr_effect_ext_dtls_rec.ATTRIBUTE13,
1330 l_mr_effect_ext_dtls_rec.ATTRIBUTE14,
1331 l_mr_effect_ext_dtls_rec.ATTRIBUTE15
1332 );
1333 ELSE
1334 EXIT WHEN CurGetEffectExtDTLS%NOTFOUND;
1335 END IF;
1336 end loop;
1337 CLOSE CurGetEffectExtDTLS;
1338
1339 End loop;
1340
1341 for l_mr_relation_rec in CurGetRelationDet
1342 loop
1343
1344 IF l_mr_relation_Rec.MR_HEADER_ID=p_source_mr_header_id
1345 THEN
1346 -- Parent Relation
1347 l_rel_rec.mr_header_id :=x_new_mr_header_id;
1348 l_rel_rec.RELATED_MR_HEADER_ID :=l_mr_relation_Rec.RELATED_MR_HEADER_ID;
1349 ELSE
1350 -- Child Relation
1351 l_rel_rec.mr_header_id :=l_mr_relation_Rec.MR_HEADER_ID;
1352 l_rel_rec.related_mr_header_id :=x_new_mr_header_id;
1353 END IF;
1354
1355 INSERT INTO AHL_MR_RELATIONSHIPS(
1356 MR_RELATIONSHIP_ID,
1357 OBJECT_VERSION_NUMBER,
1358 LAST_UPDATE_DATE,
1359 LAST_UPDATED_BY,
1360 CREATION_DATE,
1361 CREATED_BY,
1362 LAST_UPDATE_LOGIN,
1363 MR_HEADER_ID,
1364 RELATED_MR_HEADER_ID,
1365 RELATIONSHIP_CODE,
1366 ATTRIBUTE_CATEGORY,
1367 ATTRIBUTE1,
1368 ATTRIBUTE2,
1369 ATTRIBUTE3,
1370 ATTRIBUTE4,
1371 ATTRIBUTE5,
1372 ATTRIBUTE6,
1373 ATTRIBUTE7,
1374 ATTRIBUTE8,
1375 ATTRIBUTE9,
1376 ATTRIBUTE10,
1377 ATTRIBUTE11,
1378 ATTRIBUTE12,
1379 ATTRIBUTE13,
1380 ATTRIBUTE14,
1381 ATTRIBUTE15)
1382 values(
1383 AHL_MR_RELATIONSHIPS_S.NEXTVAL,
1384 1,
1385 SYSDATE,
1386 fnd_global.user_id,
1387 SYSDATE,
1388 fnd_global.user_id,
1389 fnd_global.user_id,
1390 l_rel_Rec.MR_HEADER_ID,
1391 l_rel_Rec.RELATED_MR_HEADER_ID,
1392 l_mr_relation_Rec.RELATIONSHIP_CODE,
1393 l_mr_relation_Rec.ATTRIBUTE_CATEGORY,
1394 l_mr_relation_Rec.ATTRIBUTE1,
1395 l_mr_relation_Rec.ATTRIBUTE2,
1396 l_mr_relation_Rec.ATTRIBUTE3,
1397 l_mr_relation_Rec.ATTRIBUTE4,
1398 l_mr_relation_Rec.ATTRIBUTE5,
1399 l_mr_relation_Rec.ATTRIBUTE6,
1400 l_mr_relation_Rec.ATTRIBUTE7,
1401 l_mr_relation_Rec.ATTRIBUTE8,
1402 l_mr_relation_Rec.ATTRIBUTE9,
1403 l_mr_relation_Rec.ATTRIBUTE10,
1404 l_mr_relation_Rec.ATTRIBUTE11,
1405 l_mr_relation_Rec.ATTRIBUTE12,
1406 l_mr_relation_Rec.ATTRIBUTE13,
1407 l_mr_relation_Rec.ATTRIBUTE14,
1408 l_mr_relation_Rec.ATTRIBUTE15);
1409 END LOOP;
1410 -- start visit types
1411
1412 FOR l_mrvisttype_rec in CurGetMrVisitTypes
1413 LOOP
1414 INSERT INTO AHL_MR_VISIT_TYPES
1415 (
1416 MR_VISIT_TYPE_ID,
1417 OBJECT_VERSION_NUMBER,
1418 LAST_UPDATE_DATE,
1419 LAST_UPDATED_BY,
1420 CREATION_DATE,
1421 CREATED_BY,
1422 LAST_UPDATE_LOGIN,
1423 MR_HEADER_ID,
1424 SECURITY_GROUP_ID,
1425 ATTRIBUTE_CATEGORY,
1426 ATTRIBUTE1,
1427 ATTRIBUTE2,
1428 ATTRIBUTE3,
1429 ATTRIBUTE4,
1430 ATTRIBUTE5,
1431 ATTRIBUTE6,
1432 ATTRIBUTE7,
1433 ATTRIBUTE8,
1434 ATTRIBUTE9,
1435 ATTRIBUTE10,
1436 ATTRIBUTE11,
1437 ATTRIBUTE12,
1438 ATTRIBUTE13,
1439 ATTRIBUTE14,
1440 ATTRIBUTE15,
1441 MR_VISIT_TYPE_CODE
1442 )
1443 VALUES
1444 (
1445 AHL_MR_VISIT_TYPES_S.NEXTVAL,
1446 1,
1447 SYSDATE,
1448 fnd_global.user_id,
1449 SYSDATE,
1450 fnd_global.user_id,
1451 fnd_global.user_id,
1452 x_new_MR_HEADER_ID,
1453 l_mrvisttype_rec.SECURITY_GROUP_ID,
1454 l_mrvisttype_rec.ATTRIBUTE_CATEGORY,
1455 l_mrvisttype_rec.ATTRIBUTE1,
1456 l_mrvisttype_rec.ATTRIBUTE2,
1457 l_mrvisttype_rec.ATTRIBUTE3,
1458 l_mrvisttype_rec.ATTRIBUTE4,
1459 l_mrvisttype_rec.ATTRIBUTE5,
1460 l_mrvisttype_rec.ATTRIBUTE6,
1461 l_mrvisttype_rec.ATTRIBUTE7,
1462 l_mrvisttype_rec.ATTRIBUTE8,
1463 l_mrvisttype_rec.ATTRIBUTE9,
1464 l_mrvisttype_rec.ATTRIBUTE10,
1465 l_mrvisttype_rec.ATTRIBUTE11,
1466 l_mrvisttype_rec.ATTRIBUTE12,
1467 l_mrvisttype_rec.ATTRIBUTE13,
1468 l_mrvisttype_rec.ATTRIBUTE14,
1469 l_mrvisttype_rec.ATTRIBUTE15,
1470 l_mrvisttype_rec.MR_VISIT_TYPE_CODE
1471 );
1472 END LOOP;
1473
1474 -- pdoki added for SBE Project, Start.
1475 -- Copies the SB rules from a source MR (original) to destination MR (revised).
1476 select PROGRAM_TYPE_CODE
1477 into l_program_type_code
1478 from ahl_mr_headers_b
1479 where mr_header_id = p_source_mr_header_id;
1480
1481 IF l_program_type_code = 'SERV_BLTN' THEN
1482
1483 AHL_SB_RULES_PVT.Copy_Rules_For_MR
1484 (
1485 p_api_version => 1.0,
1486 p_commit => FND_API.G_FALSE,
1487 p_src_mr_header_id => p_source_mr_header_id,
1488 p_dst_mr_header_id => x_new_mr_header_id,
1489 x_return_status => x_return_status,
1490 x_msg_count => x_msg_count,
1491 x_msg_data => x_msg_data
1492 );
1493
1494 END IF;
1495 -- pdoki added for SBE Project, End.
1496 -- Changes for SBE begins
1497 -- Copying the loop & chain relationships
1498 FOR l_start_loop_chain_rel_rec IN start_mr_loop_chain_relns_csr(p_source_mr_header_id)
1499 LOOP
1500 IF l_start_loop_chain_rel_rec.mr_header_id = p_source_mr_header_id THEN
1501 l_start_loop_chain_rel_rec.mr_header_id := x_new_mr_header_id;
1502 END IF;
1503
1504 l_mr_loop_chain_rel_id := AHL_MR_LOOP_CHAIN_RELNS_S.NEXTVAL;
1505
1506 IF l_start_loop_chain_rel_rec.sequence_number = 1
1507 THEN
1508 -- Set the start mr relationship id
1509 l_start_mr_rel_id := l_mr_loop_chain_rel_id;
1510 END IF;
1511
1512 INSERT INTO AHL_MR_LOOP_CHAIN_RELNS(
1513 MR_RELATIONSHIP_ID,
1514 OBJECT_VERSION_NUMBER,
1515 LAST_UPDATE_DATE,
1516 LAST_UPDATED_BY,
1517 CREATION_DATE,
1518 CREATED_BY,
1519 LAST_UPDATE_LOGIN,
1520 MR_HEADER_ID,
1521 START_MR_RELATIONSHIP_ID,
1522 SEQUENCE_NUMBER,
1523 RELATIONSHIP_CODE,
1524 ATTRIBUTE_CATEGORY,
1525 ATTRIBUTE1,
1526 ATTRIBUTE2,
1527 ATTRIBUTE3,
1528 ATTRIBUTE4,
1529 ATTRIBUTE5,
1530 ATTRIBUTE6,
1531 ATTRIBUTE7,
1532 ATTRIBUTE8,
1533 ATTRIBUTE9,
1534 ATTRIBUTE10,
1535 ATTRIBUTE11,
1536 ATTRIBUTE12,
1537 ATTRIBUTE13,
1538 ATTRIBUTE14,
1539 ATTRIBUTE15)
1540 values(
1541 l_mr_loop_chain_rel_id,
1542 1,
1543 SYSDATE,
1544 fnd_global.user_id,
1545 SYSDATE,
1546 fnd_global.user_id,
1547 fnd_global.user_id,
1548 l_start_loop_chain_rel_rec.MR_HEADER_ID,
1549 l_start_mr_rel_id,
1550 l_start_loop_chain_rel_rec.SEQUENCE_NUMBER,
1551 l_start_loop_chain_rel_rec.RELATIONSHIP_CODE,
1552 l_start_loop_chain_rel_rec.ATTRIBUTE_CATEGORY,
1553 l_start_loop_chain_rel_rec.ATTRIBUTE1,
1554 l_start_loop_chain_rel_rec.ATTRIBUTE2,
1555 l_start_loop_chain_rel_rec.ATTRIBUTE3,
1556 l_start_loop_chain_rel_rec.ATTRIBUTE4,
1557 l_start_loop_chain_rel_rec.ATTRIBUTE5,
1558 l_start_loop_chain_rel_rec.ATTRIBUTE6,
1559 l_start_loop_chain_rel_rec.ATTRIBUTE7,
1560 l_start_loop_chain_rel_rec.ATTRIBUTE8,
1561 l_start_loop_chain_rel_rec.ATTRIBUTE9,
1562 l_start_loop_chain_rel_rec.ATTRIBUTE10,
1563 l_start_loop_chain_rel_rec.ATTRIBUTE11,
1564 l_start_loop_chain_rel_rec.ATTRIBUTE12,
1565 l_start_loop_chain_rel_rec.ATTRIBUTE13,
1566 l_start_loop_chain_rel_rec.ATTRIBUTE14,
1567 l_start_loop_chain_rel_rec.ATTRIBUTE15);
1568 END LOOP;
1569 FOR l_loop_chain_rel_rec IN mr_loop_chain_relns_csr(p_source_mr_header_id)
1570 LOOP
1571 l_loop_chain_rel_rec.mr_header_id := x_new_mr_header_id;
1572
1573 INSERT INTO AHL_MR_LOOP_CHAIN_RELNS(
1574 MR_RELATIONSHIP_ID,
1575 OBJECT_VERSION_NUMBER,
1576 LAST_UPDATE_DATE,
1577 LAST_UPDATED_BY,
1578 CREATION_DATE,
1579 CREATED_BY,
1580 LAST_UPDATE_LOGIN,
1581 MR_HEADER_ID,
1582 START_MR_RELATIONSHIP_ID,
1583 SEQUENCE_NUMBER,
1584 RELATIONSHIP_CODE,
1585 ATTRIBUTE_CATEGORY,
1586 ATTRIBUTE1,
1587 ATTRIBUTE2,
1588 ATTRIBUTE3,
1589 ATTRIBUTE4,
1590 ATTRIBUTE5,
1591 ATTRIBUTE6,
1592 ATTRIBUTE7,
1593 ATTRIBUTE8,
1594 ATTRIBUTE9,
1595 ATTRIBUTE10,
1596 ATTRIBUTE11,
1597 ATTRIBUTE12,
1598 ATTRIBUTE13,
1599 ATTRIBUTE14,
1600 ATTRIBUTE15)
1601 values(
1602 AHL_MR_LOOP_CHAIN_RELNS_S.NEXTVAL,
1603 1,
1604 SYSDATE,
1605 fnd_global.user_id,
1606 SYSDATE,
1607 fnd_global.user_id,
1608 fnd_global.user_id,
1609 l_loop_chain_rel_rec.MR_HEADER_ID,
1610 l_loop_chain_rel_rec.start_mr_relationship_id,
1611 l_loop_chain_rel_rec.SEQUENCE_NUMBER,
1612 l_loop_chain_rel_rec.RELATIONSHIP_CODE,
1613 l_loop_chain_rel_rec.ATTRIBUTE_CATEGORY,
1614 l_loop_chain_rel_rec.ATTRIBUTE1,
1615 l_loop_chain_rel_rec.ATTRIBUTE2,
1616 l_loop_chain_rel_rec.ATTRIBUTE3,
1617 l_loop_chain_rel_rec.ATTRIBUTE4,
1618 l_loop_chain_rel_rec.ATTRIBUTE5,
1619 l_loop_chain_rel_rec.ATTRIBUTE6,
1620 l_loop_chain_rel_rec.ATTRIBUTE7,
1621 l_loop_chain_rel_rec.ATTRIBUTE8,
1622 l_loop_chain_rel_rec.ATTRIBUTE9,
1623 l_loop_chain_rel_rec.ATTRIBUTE10,
1624 l_loop_chain_rel_rec.ATTRIBUTE11,
1625 l_loop_chain_rel_rec.ATTRIBUTE12,
1626 l_loop_chain_rel_rec.ATTRIBUTE13,
1627 l_loop_chain_rel_rec.ATTRIBUTE14,
1628 l_loop_chain_rel_rec.ATTRIBUTE15);
1629 END LOOP;
1630 ---Changes for SBE loop/Chain Ends.
1631 l_msg_count := FND_MSG_PUB.count_msg;
1632
1633 IF l_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1634 X_msg_count := l_msg_count;
1635 X_return_status := FND_API.G_RET_STS_ERROR;
1636 RAISE FND_API.G_EXC_ERROR;
1637 END IF;
1638
1639 IF FND_API.TO_BOOLEAN(p_commit) THEN
1640 COMMIT;
1641 END IF;
1642
1643 -- Check if API is called in debug mode. If yes, disable debug.
1644
1645 IF G_DEBUG='Y' THEN
1646 AHL_DEBUG_PUB.DEBUG('CREATION OF MR_REVISION IS COMPLETE');
1647 AHL_DEBUG_PUB.disable_debug;
1648 END IF;
1649
1650 EXCEPTION
1651 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1652 ROLLBACK TO CREATE_MR_REVISION_PVT;
1653 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1654 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1655 p_count => x_msg_count,
1656 p_data => x_msg_data);
1657 IF G_DEBUG='Y' THEN
1658 AHL_DEBUG_PUB.DEBUG('CREATION OF MR_REVISION IS NOT COMPLETE');
1659 AHL_DEBUG_PUB.disable_debug;
1660 END IF;
1661
1662
1663 WHEN FND_API.G_EXC_ERROR THEN
1664 ROLLBACK TO CREATE_MR_REVISION_PVT;
1665 X_return_status := FND_API.G_RET_STS_ERROR;
1666 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1667 p_count => x_msg_count,
1668 p_data => X_msg_data);
1669 IF G_DEBUG='Y' THEN
1670 AHL_DEBUG_PUB.DEBUG('CREATION OF MR_REVISION IS NOT COMPLETE');
1671 AHL_DEBUG_PUB.disable_debug;
1672 END IF;
1673
1674 WHEN OTHERS THEN
1675 ROLLBACK TO CREATE_MR_REVISION_PVT;
1676 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1677 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1678 THEN
1679 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_REVISION_PVT',
1680 p_procedure_name => 'CREATE_MR_REVISION',
1681 p_error_text => SUBSTR(SQLERRM,1,240));
1682 END IF;
1683 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1684 p_count => x_msg_count,
1685 p_data => X_msg_data);
1686 IF G_DEBUG='Y' THEN
1687 AHL_DEBUG_PUB.DEBUG('CREATION OF MR_REVISION IS NOT COMPLETE');
1688 AHL_DEBUG_PUB.DEBUG(SQLERRM||' IN '||L_API_NAME);
1689 AHL_DEBUG_PUB.disable_debug;
1690 END IF;
1691
1692 END;
1693
1694 PROCEDURE INITIATE_MR_APPROVAL
1695 (
1696 p_api_version IN NUMBER:=1.0,
1697 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
1698 p_commit IN VARCHAR2:=FND_API.G_FALSE,
1699 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
1700 p_default IN VARCHAR2:=FND_API.G_FALSE,
1701 p_module_type IN VARCHAR2,
1702 x_return_status OUT NOCOPY VARCHAR2,
1703 x_msg_count OUT NOCOPY NUMBER,
1704 x_msg_data OUT NOCOPY VARCHAR2,
1705 p_source_mr_header_id IN NUMBER,
1706 p_object_Version_number IN NUMBER,
1707 p_apprv_type IN VARCHAR2:='COMPLETE'
1708 )
1709 AS
1710 l_counter NUMBER:=0;
1711 l_status VARCHAR2(30);
1712 l_upd_mr_status_code VARCHAR2(30);
1713 l_object VARCHAR2(30):='FMPMR';
1714 l_approval_type VARCHAR2(100):='CONCEPT';
1715 l_active VARCHAR2(50):= 'N';
1716 l_process_name VARCHAR2(50):='AHLGAPP';
1717 l_item_type VARCHAR2(50);
1718 l_msg_count NUMBER;
1719 l_msg_data VARCHAR2(2000);
1720 l_activity_id NUMBER:=p_source_mr_header_id;
1721 l_Status VARCHAR2(1);
1722 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1723 l_object_Version_number NUMBER:=p_object_version_number;
1724
1725 Cursor GetHeaderInfo(C_MR_HEADER_ID NUMBER)
1726 IS
1727 SELECT MR_HEADER_ID,
1728 TITLE,
1729 VERSION_NUMBER,
1730 MR_STATUS_CODE,
1731 EFFECTIVE_FROM,
1732 EFFECTIVE_TO,
1733 TYPE_CODE
1734 FROM AHL_MR_HEADERS_APP_V
1735 WHERE MR_HEADER_ID=C_MR_HEADER_ID
1736 and object_version_number=p_object_Version_number;
1737 l_mr_rec GetHeaderInfo%ROWTYPE;
1738
1739 Cursor GetHeaderInfo1(C_TITLE VARCHAR2,C_VERSION_NUMBER NUMBER)
1740 IS
1741 SELECT MR_HEADER_ID,
1742 TITLE,
1743 VERSION_NUMBER,
1744 MR_STATUS_CODE,
1745 EFFECTIVE_FROM,
1746 EFFECTIVE_TO
1747 FROM AHL_MR_HEADERS_APP_V
1748 WHERE TITLE=C_TITLE
1749 And version_number=c_version_number-1;
1750 l_mr_rec1 GetHeaderInfo1%ROWTYPE;
1751
1752 l_mr_appr_enabled VARCHAR2(30);
1753 l_check_flag VARCHAR2(1):='Y';
1754 l_program_id NUMBER;
1755 l_pm_activity_id NUMBER;
1756 l_contract_ref_exists VARCHAR2(1);
1757
1758 l_approved_status_code VARCHAR2(30);
1759 BEGIN
1760 SAVEPOINT INITIATE_MR_APPROVAL_PVT;
1761
1762 IF G_DEBUG='Y' THEN
1763 AHL_DEBUG_PUB.enable_debug;
1764 END IF;
1765
1766 IF G_DEBUG='Y' THEN
1767 AHL_DEBUG_PUB.debug( 'Start Initiate_MR_Approval');
1768 END IF;
1769
1770 IF FND_API.to_boolean(l_init_msg_list) THEN
1771 FND_MSG_PUB.initialize;
1772 END IF;
1773
1774
1775 x_return_status := FND_API.G_RET_STS_SUCCESS;
1776
1777
1778 l_mr_appr_enabled:=FND_PROFILE.VALUE('AHL_FMP_MR_APPRV_ENABLED');
1779
1780 IF (G_APPLN_USAGE = 'PM') THEN
1781 l_mr_appr_enabled:=nvl(l_mr_appr_enabled,'N');
1782 ELSE
1783 l_mr_appr_enabled:=nvl(l_mr_appr_enabled,'Y');
1784 END IF;
1785
1786 IF G_DEBUG='Y' THEN
1787 AHL_DEBUG_PUB.debug( 'l_mr_appr_enabled : ' || l_mr_appr_enabled);
1788 AHL_DEBUG_PUB.debug( 'P_APPRV_TYPE : ' || P_APPRV_TYPE);
1789 END IF;
1790
1791 IF p_source_mr_header_id is null or
1792 p_source_mr_header_id=FND_API.G_MISS_NUM
1793 THEN
1794 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_INVALID');
1795 FND_MSG_PUB.ADD;
1796 l_check_flag:='N';
1797 ELSE
1798 open GetHeaderInfo(p_source_mr_header_id);
1799
1800 fetch GetHeaderInfo into l_mr_rec;
1801 If GetHeaderInfo%FOUND
1802 Then
1803
1804 -- If P_APPRV_TYPE='COMPLETE'
1805 If P_APPRV_TYPE IN ('COMPLETE','COMPLETE_DCALC')
1806 Then
1807 l_upd_mr_status_code:='APPROVAL_PENDING';
1808
1809 IF trunc(l_mr_Rec.effective_from)<trunc(sysdate)
1810 THEN
1811 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ST_DATE_LESSER_SYSDATE');
1812 FND_MSG_PUB.ADD;
1813 END IF;
1814
1815 If l_mr_rec.MR_STATUS_CODE<>'DRAFT'
1816 AND l_mr_rec.MR_STATUS_CODE<>'APPROVAL_REJECTED'
1817 Then
1818 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_CANNOT_APRV');
1819 FND_MSG_PUB.ADD;
1820 End if;
1821 ElsIf P_APPRV_TYPE='TERMINATE'
1822 Then
1823 IF G_APPLN_USAGE='PM'
1824 THEN
1825
1826 IF l_mr_rec.type_code='ACTIVITY'
1827 THEN
1828 l_pm_activity_id:=p_source_mr_header_id;
1829 l_program_id:=null;
1830 ELSIF l_mr_rec.type_code='PROGRAM'
1831 THEN
1832 l_pm_Activity_id:=null;
1833 l_program_id:=p_source_mr_header_id;
1834 END IF;
1835
1836 IF G_DEBUG='Y' THEN
1837 AHL_DEBUG_PUB.debug( ' Before Call to OKS_PM_ENTITLEMENTS_PUB.Check_PM_Exists');
1838 AHL_DEBUG_PUB.debug( 'l_pm_activity_id'||l_pm_activity_id);
1839 AHL_DEBUG_PUB.debug( 'l_program_id'||l_program_id);
1840 END IF;
1841
1842
1843 OKS_PM_ENTITLEMENTS_PUB.Check_PM_Exists
1844 (
1845 p_api_version =>p_api_version,
1846 p_init_msg_list =>FND_API.G_FALSE,
1847 p_pm_program_id =>l_program_id,
1848 p_pm_activity_id =>l_pm_activity_id,
1849 x_return_status =>x_return_status,
1850 x_msg_count =>x_msg_count,
1851 x_msg_data =>x_msg_data,
1852 x_pm_reference_exists =>l_contract_ref_exists
1853 );
1854
1855 IF G_DEBUG='Y' THEN
1856 AHL_DEBUG_PUB.debug( 'After call to OKS_PM_ENTITLEMENTS_PUB.Check_PM_Exists');
1857 END IF;
1858
1859
1860 --l_contract_ref_exists:='Y'; -- TEMP CHECK
1861 --l_contract_ref_exists:=NULL; -- TEMP CHECK
1862 --l_contract_ref_exists:=FND_API.G_MISS_CHAR; -- TEMP CHECK
1863
1864 IF l_contract_ref_exists='Y'
1865 THEN
1866 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_CONTRACTS_EXIST');
1867 FND_MESSAGE.SET_TOKEN('MRTITLE',l_mr_Rec.TITLE);
1868 FND_MSG_PUB.ADD;
1869
1870 IF G_DEBUG='Y' THEN
1871 AHL_DEBUG_PUB.debug( 'l_contract_ref_exists---->'||l_contract_ref_exists);
1872 END IF;
1873
1874 ELSIF l_contract_ref_exists IS NULL OR l_contract_ref_exists=FND_API.G_MISS_CHAR
1875 THEN
1876 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_INVALID_RET_PARAM');
1877 FND_MSG_PUB.ADD;
1878
1879 IF G_DEBUG='Y' THEN
1880 AHL_DEBUG_PUB.debug( 'l_contract_ref_exists----> is either null or g_misschar'||l_contract_ref_exists);
1881 END IF;
1882
1883 END IF;
1884
1885
1886
1887 END IF;
1888
1889 -- END OF CHECK FOR CONTRACTS EXISTING WHEN TERMINATING IN PM MODE
1890
1891 l_upd_mr_status_code:='TERMINATE_PENDING';
1892 If l_mr_rec.MR_STATUS_CODE<>'COMPLETE' OR
1893 (l_mr_rec.EFFECTIVE_TO IS NOT NULL
1894 AND l_mr_rec.EFFECTIVE_TO<>FND_API.G_MISS_DATE)
1895 Then
1896 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_CANNOT_TERMIN');
1897 FND_MSG_PUB.ADD;
1898 End if;
1899 End If;
1900 -- If mr_header_id is invalid or not found
1901 ElsIf GetHeaderInfo%NOTFOUND
1902 Then
1903 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_INVALID');
1904 FND_MSG_PUB.ADD;
1905 l_check_flag:='N';
1906 End If;
1907 Close GetHeaderInfo;
1908 End If;
1909
1910
1911 -- If P_APPRV_TYPE='COMPLETE' and l_check_flag='Y'
1912 If P_APPRV_TYPE IN ('COMPLETE','COMPLETE_DCALC') and l_check_flag='Y'
1913 Then
1914
1915 If l_mr_rec.version_number>1 and (l_mr_rec.MR_STATUS_CODE='DRAFT'
1916 or l_mr_rec.MR_STATUS_CODE='APPROVAL_REJECTED')
1917 Then
1918 Open GetHeaderInfo1(upper(l_mr_rec.TITLE),
1919 l_mr_rec.VERSION_NUMBER);
1920 Fetch GetHeaderInfo1 Into l_mr_rec1;
1921 If GetHeaderInfo1%FOUND
1922 Then
1923 If trunc(l_mr_Rec.effective_from) < trunc(l_mr_Rec1.effective_from)
1924 TheN
1925 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ST_DATE_LESSER');
1926 FND_MESSAGE.SET_TOKEN('FIELD',l_mr_Rec1.effective_from);
1927 FND_MSG_PUB.ADD;
1928 End If;
1929 End If;
1930 Close GetHeaderInfo1;
1931 End if;
1932
1933 SELECT COUNT(*) INTO l_counter
1934 FROM AHL_MR_ROUTES A
1935 WHERE MR_HEADER_ID=l_activity_id
1936 AND ROUTE_ID IN (SELECT ROUTE_ID FROM AHL_ROUTES_APP_V
1937 WHERE ROUTE_ID=A.ROUTE_id
1938 AND NVL(END_DATE_ACTIVE,SYSDATE+1)>SYSDATE
1939 AND REVISION_STATUS_CODE='COMPLETE');
1940
1941 If G_APPLN_USAGE='PM' and l_mr_Rec.type_code<>'PROGRAM'
1942 Then
1943 If l_counter=0
1944 TheN
1945 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_INIT_APPRV_ABORT');
1946 FND_MSG_PUB.ADD;
1947 End If;
1948 ElsIf G_APPLN_USAGE<>'PM'
1949 Then
1950 IF l_counter=0
1951 Then
1952 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_INIT_APPRV_ABORT');
1953 FND_MSG_PUB.ADD;
1954 End If;
1955 End If;
1956
1957 End If;
1958 /*
1959
1960 If P_APPRV_TYPE='TERMINATE' and l_check_flag='Y'
1961 Then
1962 SELECT COUNT(*) INTO l_counter
1963 FROM AHL_MR_HEADERS_APP_V
1964 WHERE MR_HEADER_ID>l_activity_id
1965 And Title=l_mr_rec.title
1966 And Version_number >l_mr_rec.version_number;
1967 IF l_counter>0
1968 Then
1969 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TERMIN_OLD');
1970 FND_MESSAGE.SET_TOKEN('TITLE',l_mr_Rec.TITLE);
1971 FND_MSG_PUB.ADD;
1972 End If;
1973 IF l_mr_rec.effective_to IS NOT NULL
1974 Then
1975 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TERMINATED');
1976 FND_MESSAGE.SET_TOKEN('TITLE',l_mr_Rec.title);
1977 FND_MSG_PUB.ADD;
1978 End If;
1979 End if;
1980 */
1981 l_msg_count := FND_MSG_PUB.count_msg;
1982
1983 If G_DEBUG='Y' THEN
1984 AHL_DEBUG_PUB.debug( 'Check Error l_msg_count:'||l_msg_count);
1985 End If;
1986
1987 IF l_msg_count > 0
1988 THEN
1989 X_msg_count := l_msg_count;
1990 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1991 RAISE FND_API.G_EXC_ERROR;
1992 END IF;
1993
1994 IF(P_APPRV_TYPE = 'COMPLETE_DCALC')THEN
1995 l_approved_status_code := 'APPROVED_DCALC';
1996 ELSE
1997 l_approved_status_code := 'APPROVED';
1998 END IF;
1999
2000 -- Start work Flow Process
2001 IF (l_mr_appr_enabled = 'Y')
2002 THEN
2003 ahl_utility_pvt.get_wf_process_name(
2004 p_object =>l_object,
2005 p_application_usg_code =>g_appln_usage,
2006 x_active =>l_active,
2007 x_process_name =>l_process_name ,
2008 x_item_type =>l_item_type,
2009 x_return_status =>x_return_status,
2010 x_msg_count =>l_msg_count,
2011 x_msg_data =>l_msg_data);
2012 END IF;
2013
2014 IF l_ACTIVE='Y'
2015 THEN
2016 UPDATE AHL_MR_HEADERS_B
2017 SET MR_STATUS_CODE=l_upd_mr_status_code,
2018 OBJECT_VERSION_number=object_version_number+1
2019 WHERE MR_HEADER_ID=p_source_mr_header_id
2020 And OBJECT_VERSION_NUMBER=p_object_Version_number;
2021
2022 IF sql%rowcount=0
2023 THEN
2024 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2025 FND_MSG_PUB.ADD;
2026 ELSE
2027
2028 Ahl_generic_aprv_pvt.Start_Wf_Process(
2029 P_OBJECT =>l_object,
2030 P_ACTIVITY_ID =>l_activity_id,
2031 P_APPROVAL_TYPE =>'CONCEPT',
2032 P_OBJECT_VERSION_NUMBER =>p_object_version_number+1,
2033 P_ORIG_STATUS_CODE =>'ACTIVE',
2034 -- P_NEW_STATUS_CODE =>'APPROVED',
2035 P_NEW_STATUS_CODE => l_approved_status_code,
2036 P_REJECT_STATUS_CODE =>'REJECTED',
2037 P_REQUESTER_USERID =>fnd_global.user_id,
2038 P_NOTES_FROM_REQUESTER =>'',
2039 P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
2040 P_ITEM_TYPE =>'AHLGAPP',
2041 p_application_usg_code =>G_APPLN_USAGE
2042 );
2043 END IF;
2044 ELSE
2045 UPDATE AHL_MR_HEADERS_B
2046 SET MR_STATUS_CODE=L_UPD_MR_STATUS_CODE,
2047 OBJECT_VERSION_number=OBJECT_VERSION_number+1
2048 WHERE MR_HEADER_ID=p_source_mr_header_id
2049 AND OBJECT_VERSION_NUMBER=p_object_Version_number;
2050
2051 IF sql%rowcount=0
2052 THEN
2053 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2054 FND_MSG_PUB.ADD;
2055 END IF;
2056
2057 AHL_FMP_MR_REVISION_PVT.COMPLETE_MR_REVISION
2058 (
2059 p_api_version =>1.0,
2060 p_init_msg_list =>FND_API.G_FALSE,
2061 p_commit =>FND_API.G_FALSE,
2062 p_validation_level =>NULL,
2063 p_default =>NULL,
2064 p_module_type =>NULL,
2065 x_return_status =>x_return_status,
2066 x_msg_count =>x_msg_count ,
2067 x_msg_data =>x_msg_data ,
2068 -- p_appr_status =>'APPROVED',
2069 p_appr_status => l_approved_status_code,
2070 p_mr_header_id =>l_activity_id,
2071 p_object_version_number =>l_object_version_number+1
2072 );
2073
2074 END IF;
2075
2076 l_msg_count := FND_MSG_PUB.count_msg;
2077
2078 IF l_msg_count > 0
2079 THEN
2080 X_msg_count := l_msg_count;
2081 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2082 RAISE FND_API.G_EXC_ERROR;
2083 END IF;
2084
2085
2086 IF FND_API.TO_BOOLEAN(p_commit) THEN
2087 COMMIT;
2088 END IF;
2089
2090 EXCEPTION
2091 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2092 ROLLBACK TO INITIATE_MR_APPROVAL_PVT;
2093 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2094 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2095 p_count => x_msg_count,
2096 p_data => x_msg_data);
2097 IF G_DEBUG='Y' THEN
2098 AHL_DEBUG_PUB.disable_debug;
2099 END IF;
2100
2101
2102 WHEN FND_API.G_EXC_ERROR THEN
2103 ROLLBACK TO INITIATE_MR_APPROVAL_PVT;
2104 X_return_status := FND_API.G_RET_STS_ERROR;
2105 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2106 p_count => x_msg_count,
2107 p_data => X_msg_data);
2108 IF G_DEBUG='Y' THEN
2109 AHL_DEBUG_PUB.disable_debug;
2110 END IF;
2111
2112 WHEN OTHERS THEN
2113 ROLLBACK TO INITIATE_MR_APPROVAL_PVT;
2114 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2115 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2116 THEN
2117 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_REVISION_PVT',
2118 p_procedure_name => 'INITIATE_MR_APPROVAL',
2119 p_error_text => SUBSTR(SQLERRM,1,240));
2120 END IF;
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 END;
2129
2130
2131 PROCEDURE COMPLETE_MR_REVISION
2132 (
2133 p_api_version IN NUMBER:=1.0,
2134 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
2135 p_commit IN VARCHAR2:=FND_API.G_FALSE,
2136 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
2137 p_default IN VARCHAR2:=FND_API.G_FALSE,
2138 p_module_type IN VARCHAR2,
2139 x_return_status OUT NOCOPY VARCHAR2,
2140 x_msg_count OUT NOCOPY NUMBER,
2141 x_msg_data OUT NOCOPY VARCHAR2,
2142 p_appr_status IN VARCHAR2,
2143 p_mr_header_id IN NUMBER,
2144 p_object_version_number IN NUMBER
2145 )
2146 AS
2147
2148 CURSOR GetMR_headerDet(C_MR_HEADER_ID NUMBER)
2149 IS
2150 SELECT MR_HEADER_ID,
2151 VERSION_NUMBER,
2152 EFFECTIVE_FROM,
2153 TITLE,
2154 MR_STATUS_CODE,
2155 APPLICATION_USG_CODE
2156 FROM AHL_MR_HEADERS_B
2157 WHERE MR_HEADER_ID=C_MR_HEADER_ID;
2158
2159 CURSOR GetPrevMR_headerid(C_VERSION_NUMBER NUMBER,C_TITLE VARCHAR2,C_APP_CODE VARCHAR2)
2160 IS
2161 SELECT MR_HEADER_ID,
2162 VERSION_NUMBER,
2163 EFFECTIVE_FROM,
2164 TITLE,
2165 MR_STATUS_CODE
2166 FROM AHL_MR_HEADERS_B
2167 WHERE TITLE=C_TITLE
2168 AND VERSION_NUMBER=C_VERSION_NUMBER-1
2169 AND APPLICATION_USG_CODE=C_APP_CODE;
2170
2171 -- pdoki added for SBE Project, Start.
2172 CURSOR check_sb_rules_exists
2173 (
2174 c_mr_header_id number
2175 )
2176 IS
2177 select 'x'
2178 from AHL_SB_POSITION_RULES
2179 where mr_header_id = c_mr_header_id;
2180
2181 l_dummy_char varchar2(1);
2182 -- pdoki added for SBE Project, End.
2183 --Changes for SBE - Loop/Chain Relns Begins
2184 CURSOR get_mr_loop_chain_relns(p_mr_header_id_csr NUMBER)
2185 IS
2186 SELECT * FROM ahl_mr_loop_chain_relns WHERE
2187 mr_relationship_id IN
2188 (SELECT mr_relationship_id
2189 FROM ahl_mr_loop_chain_relns
2190 WHERE mr_header_id = p_mr_header_id_csr
2191 AND mr_relationship_id <> start_mr_relationship_id
2192 );
2193
2194 l_seq_count NUMBER;
2195 --Changes for SBE - Loop/Chain Relns Ends
2196
2197 l_mr_rec GetMR_headerDet%rowtype;
2198 l_prev_mr_rec GetPrevMR_headerid%rowtype;
2199 l_status VARCHAR2(30);
2200 l_mr_status VARCHAR2(30);
2201 l_check_flag VARCHAR2(1):='N';
2202 l_check_flag2 VARCHAR2(1):='N';
2203 l_check_flag3 VARCHAR2(1):='Y';
2204 l_api_name CONSTANT VARCHAR2(30):='COMPLETE_MR_REVISION';
2205 l_api_version CONSTANT NUMBER := 1.0;
2206 l_msg_count NUMBER;
2207 l_msg_data VARCHAR2(2000);
2208 l_fr_date DATE:=SYSDATE;
2209 l_to_Date DATE:=SYSDATE;
2210 l_commit VARCHAR2(10):=FND_API.G_TRUE;
2211
2212 /* Vo comments: No need to copy newer revisions of the MR to the ATA Sequences,
2213 * the User will need to add them on a case-to-case basis...
2214 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...*/
2215
2216 -- pdoki uncommented for Bug 10312082
2217 l_prev_program_type VARCHAR2(30);
2218 l_program_type VARCHAR2(30);
2219
2220 /*-- Tamal [MEL/CDL RM-FMP Enhancements] Ends here...
2221 */
2222
2223 l_req_id number;
2224
2225 BEGIN
2226
2227 SAVEPOINT COMPLETE_MR_REVISION_PVT;
2228
2229 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2230 IF G_DEBUG='Y' THEN
2231 AHL_DEBUG_PUB.enable_debug;
2232 AHL_DEBUG_PUB.debug( 'p_appr_Status'||p_appr_status);
2233 AHL_DEBUG_PUB.debug( 'Header Id '||p_mr_header_id);
2234 END IF;
2235
2236 IF p_mr_header_id is not null and p_mr_header_id<>fnd_api.g_miss_num
2237 THEN
2238 OPEN GetMR_headerDet(p_mr_header_id);
2239 FETCH GetMR_headerDet INTO l_mr_rec;
2240
2241 IF GetMR_headerDet%NOTFOUND
2242 THEN
2243 l_check_flag:='N';
2244 ELSE
2245 IF p_appr_status IN ('APPROVED', 'APPROVED_DCALC')
2246 THEN
2247 IF l_mr_rec.mr_status_code='APPROVAL_PENDING'
2248 THEN
2249 l_status:='COMPLETE';
2250 ELSIF l_mr_rec.mr_status_code='TERMINATE_PENDING'
2251 THEN
2252 l_status:='TERMINATED';
2253 END IF;
2254 l_check_flag:='Y';
2255 ELSE
2256 l_check_flag:='N';
2257 l_status:='APPROVAL_REJECTED';
2258 IF l_mr_rec.mr_status_code='TERMINATE_PENDING'
2259 THEN
2260 l_status:='COMPLETE';
2261 END IF;
2262 l_check_flag3:='N';
2263 UPDATE AHL_MR_HEADERS_B
2264 SET MR_STATUS_CODE=DECODE(MR_STATUS_CODE,'APPROVAL_PENDING','APPROVAL_REJECTED','TERMINATE_PENDING','COMPLETE')
2265 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
2266 END IF;
2267
2268 IF l_mr_rec.effective_from >sysdate
2269 THEN
2270 l_fr_date:=l_mr_rec.effective_from;
2271 l_to_date:=l_mr_rec.effective_from;
2272 ELSE
2273 l_fr_date:=sysdate;
2274 l_to_date:=sysdate;
2275 END IF;
2276 END IF;
2277
2278 CLOSE GetMR_headerDet;
2279
2280 IF l_check_flag='Y' and l_mr_rec.version_number=1
2281 THEN
2282 IF l_mr_rec.MR_STATUS_CODE='APPROVAL_PENDING'
2283 THEN
2284 UPDATE AHL_MR_HEADERS_B
2285 SET MR_STATUS_CODE=l_status,
2286 EFFECTIVE_FROM=L_FR_DATE,
2287 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
2288 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
2289
2290 -- Begin changes by SURRKUMA for Bug 13959709 on 14-MAY-12
2291 -- Update the SB rules associated to the SB MR while
2292 -- completing the MR for the first revision.
2293
2294 SELECT program_type_code
2295 INTO l_program_type
2296 FROM ahl_mr_headers_app_v
2297 WHERE mr_header_id = P_MR_HEADER_ID;
2298
2299 IF (l_program_type = 'SERV_BLTN') THEN
2300 OPEN check_sb_rules_exists(p_mr_header_id);
2301 FETCH check_sb_rules_exists INTO l_dummy_char;
2302 IF (check_sb_rules_exists%FOUND) THEN
2303 AHL_SB_RULES_PVT.Update_Rules_For_MR
2304 (
2305 p_api_version => 1.0,
2306 p_commit => FND_API.G_FALSE,
2307 p_mr_header_id => p_mr_header_id,
2308 x_return_status => x_return_status,
2309 x_msg_count => x_msg_count,
2310 x_msg_data => x_msg_data
2311 );
2312 END IF;
2313 CLOSE check_sb_rules_exists;
2314 END IF;
2315 -- End of changes by SURRKUMA for Bug 13959709 on 14-MAY-12
2316
2317 ELSIF l_mr_rec.MR_STATUS_CODE='TERMINATE_PENDING'
2318 THEN
2319 UPDATE AHL_MR_HEADERS_B
2320 SET MR_STATUS_CODE=l_status,
2321 EFFECTIVE_TO=nvl(EFFECTIVE_TO,L_TO_DATE),
2322 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
2323 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
2324
2325 END IF;
2326
2327 IF L_MR_REC.MR_STATUS_CODE='TERMINATE_PENDING'
2328 THEN
2329 IF G_DEBUG='Y'
2330 THEN
2331 AHL_DEBUG_PUB.debug( 'l_status:'||l_status);
2332 AHL_DEBUG_PUB.debug( 'Before Call to Terminate MR Instances');
2333 END IF;
2334
2335 AHL_UMP_UNITMAINT_PUB.Terminate_MR_Instances(
2336 p_api_version =>l_api_version,
2337 p_init_msg_list =>FND_API.G_FALSE,
2338 p_commit =>p_commit,
2339 p_validation_level =>p_validation_level,
2340 p_default =>p_default,
2341 p_module_type =>p_module_type,
2342 p_old_mr_header_id =>l_mr_rec.MR_HEADER_ID,
2343 p_old_mr_title =>l_mr_rec.TITLE,
2344 p_old_version_number =>l_mr_rec.VERSION_NUMBER,
2345 p_new_mr_header_id =>NULL,
2346 p_new_mr_title =>NULL,
2347 p_new_version_number =>NULL,
2348 x_return_status =>x_return_Status,
2349 x_msg_count =>l_msg_count,
2350 x_msg_data =>l_msg_data);
2351
2352 IF FND_MSG_PUB.count_msg > 0
2353 THEN
2354 IF G_DEBUG='Y' THEN
2355 AHL_DEBUG_PUB.debug( 'Terminate inst fail');
2356 END IF;
2357
2358 END IF;
2359
2360 END IF;
2361
2362
2363
2364 l_check_flag:='Y';
2365
2366 l_check_flag2:='Y';
2367
2368 ELSIF l_check_flag='Y' and l_mr_rec.version_number>1
2369 THEN
2370 AHL_DEBUG_PUB.debug( ' For version_number >1');
2371
2372 OPEN GetPrevMR_headerid(l_mr_rec.version_number,
2373 l_mr_rec.title,
2374 l_mr_rec.application_usg_code);
2375
2376 FETCH GetPrevMR_headerid INTO l_prev_mr_rec;
2377
2378 IF GetPrevMR_headerid%NOTFOUND
2379 THEN
2380 l_check_flag2:='N';
2381 ELSE
2382 l_check_flag2:='Y';
2383 END IF;
2384
2385 CLOSE GetPrevMR_headerid;
2386
2387 IF l_check_flag2='Y'
2388 THEN
2389
2390 IF l_mr_rec.MR_STATUS_CODE='APPROVAL_PENDING'
2391 THEN
2392 UPDATE AHL_MR_HEADERS_B
2393 SET MR_STATUS_CODE=l_status,
2394 EFFECTIVE_FROM=L_FR_DATE,
2395 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
2396 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
2397
2398 UPDATE AHL_MR_HEADERS_B
2399 SET EFFECTIVE_TO=L_TO_DATE,
2400 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
2401 WHERE MR_HEADER_ID=l_prev_mr_rec.MR_HEADER_ID;
2402
2403 -- Changes for SBE Begin: Delete the relation from MR loop/chain relationships table
2404 /* Added condition for checking if the effective_to date of the prev MR is
2405 is less than or equal to sysdate then delete the relationship */
2406 IF TRUNC(SYSDATE) >= L_TO_DATE THEN
2407 -- Delete the entire rel if its a starting MR
2408
2409 DELETE FROM AHL_MR_LOOP_CHAIN_RELNS
2410 WHERE
2411 start_mr_relationship_id IN
2412 (SELECT mr_relationship_id
2413 FROM ahl_mr_loop_chain_relns
2414 WHERE mr_header_id = l_prev_mr_rec.MR_HEADER_ID
2415 );
2416
2417 -- Check if there exist an MR for the same sequence number,
2418 -- If yes, then delete the MR from the rel
2419 -- If not, then delete the entire rel
2420
2421 FOR l_mr_loop_chain_rel_rec IN get_mr_loop_chain_relns(l_prev_mr_rec.MR_HEADER_ID)
2422 LOOP
2423 SELECT COUNT(sequence_number) INTO l_seq_count FROM ahl_mr_loop_chain_relns
2424 WHERE sequence_number = l_mr_loop_chain_rel_rec.sequence_number
2425 AND start_mr_relationship_id = l_mr_loop_chain_rel_rec.start_mr_relationship_id;
2426
2427 IF l_seq_count <= 1 THEN
2428 DELETE FROM ahl_mr_loop_chain_relns
2429 WHERE start_mr_relationship_id = l_mr_loop_chain_rel_rec.start_mr_relationship_id;
2430
2431 ELSE
2432 DELETE FROM ahl_mr_loop_chain_relns
2433 WHERE mr_relationship_id = l_mr_loop_chain_rel_rec.mr_relationship_id;
2434 END IF;
2435 END LOOP;
2436 END IF;
2437
2438 -- Changes for SBE - Loop/Chain Relns Ends
2439
2440 /* Vo comments: No need to copy newer revisions of the MR to the ATA Sequences,
2441 * the User will need to add them on a case-to-case basis...
2442
2443 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
2444
2445 -- Verify whether both old revisions of the MR are M Procedures...*/
2446
2447 -- pdoki uncommented for Bug 10312082
2448 SELECT program_type_code
2449 INTO l_prev_program_type
2450 FROM ahl_mr_headers_app_v
2451 WHERE mr_header_id = l_prev_mr_rec.MR_HEADER_ID;
2452
2453 SELECT program_type_code
2454 INTO l_program_type
2455 FROM ahl_mr_headers_app_v
2456 WHERE mr_header_id = P_MR_HEADER_ID;
2457
2458 -- If old revision of the MO_PROC is being made inactive, need to associated the new revision to ATA Sequences too
2459 IF (l_prev_program_type = 'MO_PROC' AND l_program_type = 'MO_PROC')
2460 THEN
2461 AHL_MEL_CDL_ATA_SEQS_PVT.Copy_MO_Proc_Revision
2462 (
2463 -- Standard IN params
2464 p_api_version => 1.0,
2465 p_init_msg_list => FND_API.G_FALSE,
2466 p_commit => FND_API.G_FALSE,
2467 p_validation_level => p_validation_level,
2468 p_default => p_default,
2469 p_module_type => p_module_type,
2470 -- Standard OUT params
2471 x_return_status => x_return_status,
2472 x_msg_count => l_msg_count,
2473 x_msg_data => l_msg_data,
2474 -- Procedure IN, OUT, IN/OUT params
2475 p_old_mr_header_id => l_prev_mr_rec.MR_HEADER_ID,
2476 p_new_mr_header_id => P_MR_HEADER_ID
2477 );
2478 END IF;
2479 /*-- Tamal [MEL/CDL RM-FMP Enhancements] Ends here...
2480
2481 */
2482 -- pdoki added for SBE Project, Start.
2483 -- Updates the SB rules for a given MR
2484 IF (l_program_type = 'SERV_BLTN') THEN
2485 OPEN check_sb_rules_exists(p_mr_header_id);
2486 FETCH check_sb_rules_exists INTO l_dummy_char;
2487 IF (check_sb_rules_exists%FOUND)
2488 THEN
2489 AHL_SB_RULES_PVT.Update_Rules_For_MR
2490 (
2491 p_api_version => 1.0,
2492 p_commit => FND_API.G_FALSE,
2493 p_mr_header_id => p_mr_header_id,
2494 x_return_status => x_return_status,
2495 x_msg_count => x_msg_count,
2496 x_msg_data => x_msg_data
2497 );
2498
2499 END IF;
2500 CLOSE check_sb_rules_exists;
2501 END IF;
2502 -- pdoki added for SBE Project, End.
2503
2504 ELSIF l_mr_rec.MR_STATUS_CODE='TERMINATE_PENDING'
2505 THEN
2506 UPDATE AHL_MR_HEADERS_B
2507 SET MR_STATUS_CODE=l_status,
2508 EFFECTIVE_TO=NVL(EFFECTIVE_TO,l_to_date),
2509 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
2510 WHERE MR_HEADER_ID=P_MR_HEADER_ID;
2511 END IF;
2512
2513 IF l_check_flag3='Y'
2514 THEN
2515 IF l_check_flag2='Y' AND l_check_flag='Y'
2516 THEN
2517
2518 IF FND_MSG_PUB.count_msg > 0
2519 THEN
2520 IF G_DEBUG='Y' THEN
2521 AHL_DEBUG_PUB.debug( 'Error Before TerminateInstances');
2522 END IF;
2523 X_msg_count := l_msg_count;
2524 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2525 RAISE FND_API.G_EXC_ERROR;
2526 END IF;
2527
2528 IF G_DEBUG='Y'
2529 THEN
2530 AHL_DEBUG_PUB.debug( 'l_status:'||l_status);
2531 END IF;
2532
2533 IF L_MR_REC.MR_STATUS_CODE='APPROVAL_PENDING'
2534 THEN
2535
2536 IF(TRUNC(NVL(l_to_date,SYSDATE)) > TRUNC(SYSDATE) )THEN
2537 UPDATE AHL_MR_HEADERS_B
2538 SET TERMINATION_REQUIRED_FLAG = 'Y'
2539 WHERE MR_HEADER_ID=l_prev_mr_rec.MR_HEADER_ID;
2540
2541 AHL_UMP_UNITMAINT_PUB.Terminate_MR_Instances(
2542 p_api_version =>l_api_version,
2543 p_init_msg_list =>FND_API.G_FALSE,
2544 p_commit =>p_commit,
2545 p_validation_level =>p_validation_level,
2546 p_default =>p_default,
2547 p_module_type =>p_module_type,
2548 p_old_mr_header_id =>l_prev_mr_rec.MR_HEADER_ID,
2549 p_old_mr_title =>l_prev_mr_rec.TITLE,
2550 p_old_version_number =>l_prev_mr_rec.VERSION_NUMBER,
2551 p_new_mr_header_id =>NULL,
2552 p_new_mr_title =>NULL,
2553 p_new_version_number =>NULL,
2554 x_return_status =>x_return_Status,
2555 x_msg_count =>l_msg_count,
2556 x_msg_data =>l_msg_data);
2557 ELSE
2558
2559 AHL_UMP_UNITMAINT_PUB.Terminate_MR_Instances(
2560 p_api_version =>l_api_version,
2561 p_init_msg_list =>FND_API.G_FALSE,
2562 p_commit =>p_commit,
2563 p_validation_level =>p_validation_level,
2564 p_default =>p_default,
2565 p_module_type =>p_module_type,
2566 p_old_mr_header_id =>l_prev_mr_rec.MR_HEADER_ID,
2567 p_old_mr_title =>l_prev_mr_rec.TITLE,
2568 p_old_version_number =>l_prev_mr_rec.VERSION_NUMBER,
2569 p_new_mr_header_id =>l_mr_rec.MR_HEADER_ID,
2570 p_new_mr_title =>l_mr_rec.TITLE,
2571 p_new_version_number =>l_mr_rec.VERSION_NUMBER,
2572 x_return_status =>x_return_Status,
2573 x_msg_count =>l_msg_count,
2574 x_msg_data =>l_msg_data);
2575 END IF;
2576 ELSIF L_MR_REC.MR_STATUS_CODE='TERMINATE_PENDING'
2577 THEN
2578 AHL_UMP_UNITMAINT_PUB.Terminate_MR_Instances(
2579 p_api_version =>l_api_version,
2580 p_init_msg_list =>FND_API.G_FALSE,
2581 p_commit =>p_commit,
2582 p_validation_level =>p_validation_level,
2583 p_default =>p_default,
2584 p_module_type =>p_module_type,
2585 p_old_mr_header_id =>l_mr_rec.MR_HEADER_ID,
2586 p_old_mr_title =>l_mr_rec.TITLE,
2587 p_old_version_number =>l_mr_rec.VERSION_NUMBER,
2588 p_new_mr_header_id =>NULL,
2589 p_new_mr_title =>NULL,
2590 p_new_version_number =>null,
2591 x_return_status =>x_return_Status,
2592 x_msg_count =>l_msg_count,
2593 x_msg_data =>l_msg_data);
2594
2595 END IF;
2596 END IF;
2597
2598 IF FND_MSG_PUB.count_msg > 0
2599 THEN
2600
2601 IF G_DEBUG='Y' THEN
2602 AHL_DEBUG_PUB.debug( 'Terminate inst fail');
2603 END IF;
2604
2605 END IF;
2606 END IF;
2607 END IF;
2608 END IF;
2609 END IF;
2610
2611 l_msg_count := FND_MSG_PUB.count_msg;
2612 IF l_msg_count > 0
2613 THEN
2614 IF G_DEBUG='Y' THEN
2615 AHL_DEBUG_PUB.debug( 'Failed To Complete:');
2616 END IF;
2617 X_msg_count := l_msg_count;
2618 X_return_status := FND_API.G_RET_STS_ERROR;
2619 RAISE FND_API.G_EXC_ERROR;
2620 END IF;
2621
2622 IF p_appr_status = 'APPROVED_DCALC' THEN
2623 l_req_id := fnd_request.submit_request('AHL','AHLWUEFF',NULL,NULL,FALSE,
2624 l_prev_mr_rec.MR_HEADER_ID, l_mr_rec.MR_HEADER_ID);
2625 IF (l_req_id = 0 OR l_req_id IS NULL) THEN
2626 IF G_debug = 'Y' THEN
2627 AHL_DEBUG_PUB.debug('Tried to submit concurrent request but failed');
2628 END IF;
2629 ELSE
2630 IF G_debug = 'Y' THEN
2631 AHL_DEBUG_PUB.debug('submit concurrent request : ' || l_req_id);
2632 END IF;
2633 END IF;
2634 END IF;
2635
2636 IF G_DEBUG='Y' THEN
2637 AHL_DEBUG_PUB.debug( 'Before commit Complete_mr_revision ');
2638 END IF;
2639
2640 IF FND_API.TO_BOOLEAN(p_commit) THEN
2641 COMMIT;
2642 END IF;
2643 EXCEPTION
2644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2645 ROLLBACK TO COMPLETE_MR_REVISION_PVT;
2646 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2647 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2648 p_count => x_msg_count,
2649 p_data => x_msg_data);
2650 IF G_DEBUG='Y' THEN
2651 AHL_DEBUG_PUB.disable_debug;
2652 END IF;
2653 WHEN FND_API.G_EXC_ERROR THEN
2654 ROLLBACK TO COMPLETE_MR_REVISION_PVT;
2655 X_return_status := FND_API.G_RET_STS_ERROR;
2656 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2657 p_count => x_msg_count,
2658 p_data => X_msg_data);
2659 IF G_DEBUG='Y' THEN
2660 AHL_DEBUG_PUB.disable_debug;
2661 END IF;
2662
2663 WHEN OTHERS THEN
2664 ROLLBACK TO COMPLETE_MR_REVISION_PVT;
2665 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2666 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2667 THEN
2668 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_REVISION_PVT',
2669 p_procedure_name => 'COMPLETE_MR_REVISION',
2670 p_error_text => SUBSTR(SQLERRM,1,240));
2671 END IF;
2672 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2673 p_count => x_msg_count,
2674 p_data => X_msg_data);
2675
2676 IF G_DEBUG='Y' THEN
2677 AHL_DEBUG_PUB.disable_debug;
2678 END IF;
2679
2680 END;
2681
2682 PROCEDURE VALIDATE_MR_REVISION
2683 (
2684 p_api_version IN NUMBER:=1.0,
2685 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
2686 p_commit IN VARCHAR2:=FND_API.G_FALSE,
2687 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
2688 p_default IN VARCHAR2:=FND_API.G_FALSE,
2689 p_module_type IN VARCHAR2,
2690 x_return_status OUT NOCOPY VARCHAR2,
2691 x_msg_count OUT NOCOPY NUMBER,
2692 x_msg_data OUT NOCOPY VARCHAR2,
2693 p_source_mr_header_id IN NUMBER,
2694 p_object_version_number IN NUMBER
2695 )
2696 AS
2697 l_counter1 NUMBER:=0;
2698 l_counter2 NUMBER:=0;
2699 l_appln_code VARCHAR2(30);
2700 BEGIN
2701
2702 SAVEPOINT VALIDATE_MR_REVISION;
2703
2704 IF G_APPLN_USAGE IS NULL
2705 THEN
2706 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
2707 FND_MSG_PUB.ADD;
2708 RETURN;
2709 END IF;
2710
2711
2712 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2713
2714 SELECT count(*) into l_counter1
2715 FROM AHL_MR_HEADERS_APP_V
2716 Where mr_header_id=p_source_mr_header_id;
2717
2718 IF l_counter1=0
2719 THEN
2720 FND_MESSAGE.SET_NAME('AHL','AHL_MR_HEADER_ID_INVALID');
2721 FND_MSG_PUB.ADD;
2722 END IF;
2723
2724 SELECT count(*) into l_counter2
2725 FROM AHL_MR_HEADERS_APP_V
2726 Where mr_header_id=p_source_mr_header_id
2727 And mr_status_code='DRAFT' or mr_status_code='APPROVAL_REJECTED';
2728
2729 IF l_counter2=0
2730 THEN
2731 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2732 FND_MSG_PUB.ADD;
2733 END IF;
2734
2735 EXCEPTION
2736 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2737 ROLLBACK TO VALIDATE_MR_REVISION;
2738 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2739 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2740 p_count => x_msg_count,
2741 p_data => x_msg_data);
2742 IF G_DEBUG='Y' THEN
2743 AHL_DEBUG_PUB.disable_debug;
2744 END IF;
2745
2746 WHEN FND_API.G_EXC_ERROR THEN
2747 ROLLBACK TO VALIDATE_MR_REVISION;
2748 X_return_status := FND_API.G_RET_STS_ERROR;
2749 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2750 p_count => x_msg_count,
2751 p_data => X_msg_data);
2752 IF G_DEBUG='Y' THEN
2753 AHL_DEBUG_PUB.disable_debug;
2754 END IF;
2755
2756 WHEN OTHERS THEN
2757 ROLLBACK TO VALIDATE_MR_REVISION;
2758 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2759 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2760 THEN
2761 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME ,
2762 p_procedure_name => 'VALIDATE_MR_REVISION',
2763 p_error_text => SUBSTR(SQLERRM,1,240));
2764 END IF;
2765 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2766 p_count => x_msg_count,
2767 p_data => X_msg_data);
2768 IF G_DEBUG='Y' THEN
2769 AHL_DEBUG_PUB.disable_debug;
2770 END IF;
2771 END;
2772 END AHL_FMP_MR_REVISION_PVT;