1 PACKAGE BODY IGS_PS_VAL_CST AS
2 /* $Header: IGSPS36B.pls 115.4 2002/11/29 03:02:26 nsidana ship $ */
3
4 ----------------------------------------------------------------------------
5 -- Change History :
6 -- Who When What
7 -- avenkatr 30-AUG-2001 Bug Id 1956374. Removed procedure "crsp_val_iud_crv_dtl"
8 ----------------------------------------------------------------------------
9
10 -- Validate if the IGS_PS_COURSE stage type is unique for this IGS_PS_COURSE version
11 FUNCTION crsp_val_cst_cstt(
12 p_course_cd IN IGS_PS_STAGE.course_cd%TYPE ,
13 p_version_number IN IGS_PS_STAGE.version_number%TYPE ,
14 p_sequence_number IN IGS_PS_STAGE.sequence_number%TYPE ,
15 p_course_stage_type IN IGS_PS_STAGE.course_stage_type%TYPE ,
16 p_message_name OUT NOCOPY VARCHAR2 )
17 RETURN BOOLEAN AS
18 BEGIN -- crsp_val_cst_cstt
19 -- Validate that the IGS_PS_STAGE_TYPE is unique for
20 -- IGS_PS_STAGE records for the same IGS_PS_VER.
21 DECLARE
22 CURSOR c_cst IS
23 SELECT 'x'
24 FROM IGS_PS_STAGE
25 WHERE course_cd = p_course_cd AND
26 version_number = p_version_number AND
27 sequence_number <> NVL(p_sequence_number, -1) AND
28 course_stage_type = p_course_stage_type;
29 v_c_cst_exists VARCHAR2(1) DEFAULT NULL;
30 BEGIN
31 p_message_name := NULL;
32 -- Check parameters
33 IF p_course_cd IS NULL OR
34 p_version_number IS NULL OR
35 p_course_stage_type IS NULL THEN
36 --parameters are invalid
37 RETURN TRUE;
38 END IF;
39 -- Check that no record exists for the same IGS_PS_VER
40 -- with the same course_cd.
41 OPEN c_cst;
42 FETCH c_cst INTO v_c_cst_exists;
43 IF (c_cst%FOUND) THEN
44 CLOSE c_cst;
45 p_message_name := 'IGS_PS_PRG_STAGETYOE_UNIQUE';
46 RETURN FALSE;
47 END IF;
48 CLOSE c_cst;
49 RETURN TRUE;
50 EXCEPTION
51 WHEN OTHERS THEN
52 IF (c_cst%ISOPEN) THEN
53 CLOSE c_cst;
54 END IF;
55 APP_EXCEPTION.RAISE_EXCEPTION;
56 END;
57 EXCEPTION
58 WHEN OTHERS THEN
59 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
60 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CST.crsp_val_cst_cstt');
61 IGS_GE_MSG_STACK.ADD;
62 APP_EXCEPTION.RAISE_EXCEPTION;
63 END crsp_val_cst_cstt;
64 --
65 -- Validate the IGS_PS_COURSE stage type closed indicator.
66 FUNCTION crsp_val_cstt_closed(
67 p_course_stage_type IN IGS_PS_STAGE_TYPE.course_stage_type%TYPE ,
68 p_message_name OUT NOCOPY VARCHAR2 )
69 RETURN BOOLEAN AS
70 BEGIN -- crsp_val_cstt_closed
71 -- Validate the IGS_PS_COURSE stage type closed indicator
72 DECLARE
73 v_closed_ind IGS_PS_STAGE_TYPE.closed_ind%TYPE;
74 CURSOR c_cstt IS
75 SELECT closed_ind
76 FROM IGS_PS_STAGE_TYPE
77 WHERE course_stage_type = p_course_stage_type;
78 BEGIN
79 OPEN c_cstt;
80 FETCH c_cstt INTO v_closed_ind;
81 IF (c_cstt%NOTFOUND) THEN
82 CLOSE c_cstt;
83 p_message_name := NULL;
84 RETURN TRUE;
85 END IF;
86 CLOSE c_cstt;
87 IF (v_closed_ind = 'Y') THEN
88 p_message_name := 'IGS_PS_STAGE_TYPE_STATUS_CLOS';
89 RETURN FALSE;
90 END IF;
91 p_message_name := NULL;
92 RETURN TRUE;
93 EXCEPTION
94 WHEN OTHERS THEN
95 IF (c_cstt%ISOPEN) THEN
96 CLOSE c_cstt;
97 END IF;
98 RAISE;
99 END;
100 EXCEPTION
101 WHEN OTHERS THEN
102 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
103 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CST.crsp_val_cstt_closed');
104 IGS_GE_MSG_STACK.ADD;
105 APP_EXCEPTION.RAISE_EXCEPTION;
106 END crsp_val_cstt_closed;
107
108 END IGS_PS_VAL_CST;
[Home] [Help]