31: BEGIN
32:
33: IF (l_max_stage_num IS NULL OR l_max_stage_num < 1)
34: THEN
35: FND_MESSAGE.SET_NAME('AHL', 'AHL_VWP_ST_PROFILE_NOT_DEF');
36: FND_MSG_PUB.ADD;
37: ELSE
38:
39: -- Get the minimum bound for route stage, based on routes executed before
57: CLOSE get_mr_route_det;
58:
59: IF (l_min_stage > l_max_stage)
60: THEN
61: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTE_SEQ_INV_STAGE');
62: FND_MESSAGE.SET_TOKEN('ROUTE', l_route_num, false);
63: FND_MSG_PUB.ADD;
64: ELSIF (l_route_stage IS NOT NULL AND (l_route_stage < l_min_stage OR l_route_stage > l_max_stage))
65: THEN
58:
59: IF (l_min_stage > l_max_stage)
60: THEN
61: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTE_SEQ_INV_STAGE');
62: FND_MESSAGE.SET_TOKEN('ROUTE', l_route_num, false);
63: FND_MSG_PUB.ADD;
64: ELSIF (l_route_stage IS NOT NULL AND (l_route_stage < l_min_stage OR l_route_stage > l_max_stage))
65: THEN
66: IF (p_route_stage_upd)
64: ELSIF (l_route_stage IS NOT NULL AND (l_route_stage < l_min_stage OR l_route_stage > l_max_stage))
65: THEN
66: IF (p_route_stage_upd)
67: THEN
68: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INV_STAGE_UPD');
69: FND_MESSAGE.SET_TOKEN('ROUTE',l_route_num, false);
70: FND_MESSAGE.SET_TOKEN('MIN',l_min_stage, false);
71: FND_MESSAGE.SET_TOKEN('MAX',l_max_stage, false);
72: FND_MSG_PUB.ADD;
65: THEN
66: IF (p_route_stage_upd)
67: THEN
68: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INV_STAGE_UPD');
69: FND_MESSAGE.SET_TOKEN('ROUTE',l_route_num, false);
70: FND_MESSAGE.SET_TOKEN('MIN',l_min_stage, false);
71: FND_MESSAGE.SET_TOKEN('MAX',l_max_stage, false);
72: FND_MSG_PUB.ADD;
73: ELSE
66: IF (p_route_stage_upd)
67: THEN
68: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INV_STAGE_UPD');
69: FND_MESSAGE.SET_TOKEN('ROUTE',l_route_num, false);
70: FND_MESSAGE.SET_TOKEN('MIN',l_min_stage, false);
71: FND_MESSAGE.SET_TOKEN('MAX',l_max_stage, false);
72: FND_MSG_PUB.ADD;
73: ELSE
74: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTE_SEQ_INV_STAGE');
67: THEN
68: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INV_STAGE_UPD');
69: FND_MESSAGE.SET_TOKEN('ROUTE',l_route_num, false);
70: FND_MESSAGE.SET_TOKEN('MIN',l_min_stage, false);
71: FND_MESSAGE.SET_TOKEN('MAX',l_max_stage, false);
72: FND_MSG_PUB.ADD;
73: ELSE
74: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTE_SEQ_INV_STAGE');
75: FND_MESSAGE.SET_TOKEN('ROUTE', l_route_num, false);
70: FND_MESSAGE.SET_TOKEN('MIN',l_min_stage, false);
71: FND_MESSAGE.SET_TOKEN('MAX',l_max_stage, false);
72: FND_MSG_PUB.ADD;
73: ELSE
74: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTE_SEQ_INV_STAGE');
75: FND_MESSAGE.SET_TOKEN('ROUTE', l_route_num, false);
76: FND_MSG_PUB.ADD;
77: END IF;
78: END IF;
71: FND_MESSAGE.SET_TOKEN('MAX',l_max_stage, false);
72: FND_MSG_PUB.ADD;
73: ELSE
74: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTE_SEQ_INV_STAGE');
75: FND_MESSAGE.SET_TOKEN('ROUTE', l_route_num, false);
76: FND_MSG_PUB.ADD;
77: END IF;
78: END IF;
79:
373: x_return_status:=FND_API.G_RET_STS_SUCCESS;
374:
375: IF g_appln_usage is null
376: THEN
377: FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
378: FND_MSG_PUB.ADD;
379: RETURN;
380: END IF;
381:
381:
382:
383: IF (p_mr_route_Seq_rec.MR_ROUTE_SEQUENCE_ID IS NULL OR p_mr_route_Seq_rec.MR_ROUTE_SEQUENCE_ID=FND_API.G_MISS_NUM) AND p_mr_route_Seq_rec.dml_operation<>'C'
384: THEN
385: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_SEQ_ID_NULL');
386: FND_MSG_PUB.ADD;
387: END IF;
388:
389: IF (p_mr_route_Seq_rec.OBJECT_VERSION_NUMBER IS NULL OR p_mr_route_Seq_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM) and p_mr_route_Seq_rec.dml_operation<>'C'
387: END IF;
388:
389: IF (p_mr_route_Seq_rec.OBJECT_VERSION_NUMBER IS NULL OR p_mr_route_Seq_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM) and p_mr_route_Seq_rec.dml_operation<>'C'
390: THEN
391: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRRD_OBJ_VERSION_NULL');
392: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER ,false);
393: FND_MSG_PUB.ADD;
394: END IF;
395:
388:
389: IF (p_mr_route_Seq_rec.OBJECT_VERSION_NUMBER IS NULL OR p_mr_route_Seq_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM) and p_mr_route_Seq_rec.dml_operation<>'C'
390: THEN
391: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRRD_OBJ_VERSION_NULL');
392: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER ,false);
393: FND_MSG_PUB.ADD;
394: END IF;
395:
396: IF p_mr_ROUTE_SEQ_rec.MR_HEADER_ID IS NULL OR p_mr_ROUTE_SEQ_rec.MR_HEADER_ID=FND_API.G_MISS_NUM
394: END IF;
395:
396: IF p_mr_ROUTE_SEQ_rec.MR_HEADER_ID IS NULL OR p_mr_ROUTE_SEQ_rec.MR_HEADER_ID=FND_API.G_MISS_NUM
397: THEN
398: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
399: FND_MSG_PUB.ADD;
400: ELSE
401: OPEN GetMrDet(p_mr_ROUTE_SEQ_REC.MR_HEADER_ID);
402:
403: FETCH GetMrDet into l_mr_rec;
404:
405: IF GetMrDet%NOTFOUND
406: THEN
407: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EDIT_STATUS_INVALID');
408: FND_MSG_PUB.ADD;
409: ELSE
410: -- Preventive Maintenance Code
411:
412: IF G_APPLN_USAGE='PM'
413: THEN
414: IF l_mr_rec.TYPE_CODE='PROGRAM'
415: THEN
416: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TYPE_CODE_PROGRAM');
417: FND_MSG_PUB.ADD;
418: END IF;
419: END IF;
420: END IF;
425: THEN
426: IF (p_mr_route_Seq_rec.RELATED_MR_ROUTE_ID IS NULL OR
427: p_mr_route_Seq_rec.RELATED_MR_ROUTE_ID=FND_API.G_MISS_NUM) AND p_mr_route_Seq_rec.dml_operation<>'D'
428: THEN
429: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_NULL');
430: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER ,false);
431: FND_MSG_PUB.ADD;
432: ELSE
433: OPEN check_mr_route_id(p_mr_route_Seq_rec.mr_route_id);
426: IF (p_mr_route_Seq_rec.RELATED_MR_ROUTE_ID IS NULL OR
427: p_mr_route_Seq_rec.RELATED_MR_ROUTE_ID=FND_API.G_MISS_NUM) AND p_mr_route_Seq_rec.dml_operation<>'D'
428: THEN
429: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_NULL');
430: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER ,false);
431: FND_MSG_PUB.ADD;
432: ELSE
433: OPEN check_mr_route_id(p_mr_route_Seq_rec.mr_route_id);
434: FETCH check_mr_route_id INTO l_counter2;
433: OPEN check_mr_route_id(p_mr_route_Seq_rec.mr_route_id);
434: FETCH check_mr_route_id INTO l_counter2;
435: IF l_counter2=0
436: THEN
437: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_REL_MR_ROUTE_ID_INV');
438: FND_MESSAGE.SET_TOKEN('ROUTENUM',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
439: FND_MSG_PUB.ADD;
440: END IF;
441: CLOSE check_mr_route_id;
434: FETCH check_mr_route_id INTO l_counter2;
435: IF l_counter2=0
436: THEN
437: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_REL_MR_ROUTE_ID_INV');
438: FND_MESSAGE.SET_TOKEN('ROUTENUM',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
439: FND_MSG_PUB.ADD;
440: END IF;
441: CLOSE check_mr_route_id;
442: END IF;
456: END IF;
457: FETCH check_uniq INTO l_seq_uniqrec;
458: IF check_uniq%found and p_mr_route_Seq_rec.dml_operation='C'
459: THEN
460: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_SEQUENCE_DUP');
461: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
462: FND_MSG_PUB.ADD;
463: ELSIF check_uniq%found and p_mr_route_Seq_rec.dml_operation='U'
464: THEN
457: FETCH check_uniq INTO l_seq_uniqrec;
458: IF check_uniq%found and p_mr_route_Seq_rec.dml_operation='C'
459: THEN
460: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_SEQUENCE_DUP');
461: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
462: FND_MSG_PUB.ADD;
463: ELSIF check_uniq%found and p_mr_route_Seq_rec.dml_operation='U'
464: THEN
465: IF p_mr_route_Seq_rec.MR_ROUTE_SEQUENCE_ID<>l_seq_uniqrec.MR_ROUTE_SEQUENCE_ID
463: ELSIF check_uniq%found and p_mr_route_Seq_rec.dml_operation='U'
464: THEN
465: IF p_mr_route_Seq_rec.MR_ROUTE_SEQUENCE_ID<>l_seq_uniqrec.MR_ROUTE_SEQUENCE_ID
466: THEN
467: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_SEQUENCE_DUP');
468: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
469: FND_MSG_PUB.ADD;
470: END IF;
471: END IF;
464: THEN
465: IF p_mr_route_Seq_rec.MR_ROUTE_SEQUENCE_ID<>l_seq_uniqrec.MR_ROUTE_SEQUENCE_ID
466: THEN
467: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_SEQUENCE_DUP');
468: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
469: FND_MSG_PUB.ADD;
470: END IF;
471: END IF;
472: CLOSE check_UNIQ;
544: -- Changed for 11.5.10 Public API.
545: /*
546: IF (p_x_mr_route_seq_rec.MR_HEADER_ID IS NULL OR p_x_mr_route_seq_rec.MR_HEADER_ID=FND_API.G_MISS_NUM) AND p_x_mr_route_seq_rec.dml_operation<>'D'
547: THEN
548: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
549: FND_MSG_PUB.ADD;
550: END IF;
551: */
552:
551: */
552:
553: IF (p_x_mr_route_seq_rec.ROUTE_NUMBER IS NULL OR p_x_mr_route_seq_rec.ROUTE_NUMBER=FND_API.G_MISS_CHAR) AND p_x_mr_route_seq_rec.dml_operation<>'D'
554: THEN
555: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_NULL');
556: FND_MSG_PUB.ADD;
557: ELSIF p_x_mr_route_seq_rec.dml_operation<>'D'
558: THEN
559: OPEN get_route_num_routeid(p_x_mr_route_seq_rec.mr_route_id);
572: THEN
573: IF G_DEBUG='Y' THEN
574: AHL_DEBUG_PUB.debug( 'Error 1','+DEBUG+');
575: END IF;
576: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
577: FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
578: FND_MSG_PUB.ADD;
579: END IF;
580: CLOSE get_route_frm;
573: IF G_DEBUG='Y' THEN
574: AHL_DEBUG_PUB.debug( 'Error 1','+DEBUG+');
575: END IF;
576: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
577: FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
578: FND_MSG_PUB.ADD;
579: END IF;
580: CLOSE get_route_frm;
581:
599: and mr_route_id=p_x_mr_route_seq_rec.related_mr_route_id;
600:
601: if l_counter3 > 1
602: then
603: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_SELECT_LOV');
604: FND_MSG_PUB.ADD;
605: end if;
606: ELSIF l_counter=0
607: THEN
608: IF G_DEBUG='Y' THEN
609: AHL_DEBUG_PUB.enable_debug;
610: AHL_DEBUG_PUB.debug( 'Error 2','+DEBUG+');
611: END IF;
612: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
613: FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
614: FND_MSG_PUB.ADD;
615: ELSIF l_counter=1
616: THEN
609: AHL_DEBUG_PUB.enable_debug;
610: AHL_DEBUG_PUB.debug( 'Error 2','+DEBUG+');
611: END IF;
612: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
613: FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
614: FND_MSG_PUB.ADD;
615: ELSIF l_counter=1
616: THEN
617: OPEN get_mr_route_id(l_route_id,p_x_mr_route_seq_rec.mr_header_id);
621: IF G_DEBUG='Y' THEN
622: AHL_DEBUG_PUB.enable_debug;
623: AHL_DEBUG_PUB.debug( 'Error 1','+DEBUG+');
624: END IF;
625: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
626: FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER);
627: FND_MSG_PUB.ADD;
628: END IF;
629: CLOSE get_mr_route_id;
622: AHL_DEBUG_PUB.enable_debug;
623: AHL_DEBUG_PUB.debug( 'Error 1','+DEBUG+');
624: END IF;
625: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
626: FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER);
627: FND_MSG_PUB.ADD;
628: END IF;
629: CLOSE get_mr_route_id;
630: END IF;
635: IF (p_x_mr_route_seq_rec.RELATED_MR_ROUTE_ID IS NULL
636: OR p_x_mr_route_seq_rec.RELATED_MR_ROUTE_ID =FND_API.G_MISS_NUM)
637: AND p_x_mr_route_seq_rec.dml_operation<>'D'
638: THEN
639: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_NULL');
640: FND_MESSAGE.SET_TOKEN('RECORD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
641: FND_MSG_PUB.ADD;
642: END IF;
643: END;
636: OR p_x_mr_route_seq_rec.RELATED_MR_ROUTE_ID =FND_API.G_MISS_NUM)
637: AND p_x_mr_route_seq_rec.dml_operation<>'D'
638: THEN
639: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_NULL');
640: FND_MESSAGE.SET_TOKEN('RECORD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
641: FND_MSG_PUB.ADD;
642: END IF;
643: END;
644:
670: START WITH RELATED_MR_ROUTE_ID=P_MR_ROUTE_ID
671: CONNECT BY PRIOR MR_ROUTE_ID = RELATED_MR_ROUTE_ID;
672: EXCEPTION
673: WHEN l_cyclic_loop THEN
674: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTENO_INVALID_CYC');
675: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_ROUTE_NUMBER ,false);
676: FND_MSG_PUB.ADD;
677: x_return_status := FND_API.G_RET_STS_ERROR;
678: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
671: CONNECT BY PRIOR MR_ROUTE_ID = RELATED_MR_ROUTE_ID;
672: EXCEPTION
673: WHEN l_cyclic_loop THEN
674: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTENO_INVALID_CYC');
675: FND_MESSAGE.SET_TOKEN('RECORD',p_mr_ROUTE_NUMBER ,false);
676: FND_MSG_PUB.ADD;
677: x_return_status := FND_API.G_RET_STS_ERROR;
678: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
679: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
825: FETCH get_mr_route_id_type INTO p_x_mr_route_seq_tbl(i).mr_route_id;
826: IF p_x_mr_route_seq_tbl(i).mr_route_id IS NULL
827: THEN
828: x_return_status := FND_API.G_RET_STS_ERROR;
829: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
830: FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_tbl(i).mr_route_number);
831: FND_MSG_PUB.ADD;
832: CLOSE get_mr_route_id_type;
833: RAISE FND_API.G_EXC_ERROR;
826: IF p_x_mr_route_seq_tbl(i).mr_route_id IS NULL
827: THEN
828: x_return_status := FND_API.G_RET_STS_ERROR;
829: FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
830: FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_tbl(i).mr_route_number);
831: FND_MSG_PUB.ADD;
832: CLOSE get_mr_route_id_type;
833: RAISE FND_API.G_EXC_ERROR;
834: END IF;