1 PACKAGE BODY IGS_PS_VAL_CTY AS
2 /* $Header: IGSPS37B.pls 115.4 2003/06/09 05:08:06 smvk ship $ */
3
4 --
5 -- Validate IGS_PS_COURSE type government IGS_PS_COURSE type.
6 FUNCTION crsp_val_cty_govt(
7 p_govt_course_type IN NUMBER ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN AS
10 v_closed_ind IGS_PS_TYPE.closed_ind%TYPE;
11 CURSOR c_govt_course_type IS
12 SELECT closed_ind
13 FROM IGS_PS_GOVT_TYPE
14 WHERE govt_course_type = p_govt_course_type;
15 BEGIN
16 OPEN c_govt_course_type;
17 FETCH c_govt_course_type INTO v_closed_ind;
18 IF c_govt_course_type%NOTFOUND THEN
19 p_message_name := NULL;
20 CLOSE c_govt_course_type;
21 RETURN TRUE;
22 ELSIF (v_closed_ind = 'N') THEN
23 p_message_name := NULL;
24 CLOSE c_govt_course_type;
25 RETURN TRUE;
26 ELSE
27 p_message_name := 'IGS_PS_GOVT_PRGTYPE_CLOSED';
28 CLOSE c_govt_course_type;
29 RETURN FALSE;
30 END IF;
31 EXCEPTION
32 WHEN OTHERS THEN
33 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
34 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CTY.crsp_val_cty_govt');
35 IGS_GE_MSG_STACK.ADD;
36 APP_EXCEPTION.RAISE_EXCEPTION;
37 END crsp_val_cty_govt;
38 --
39 -- Validate IGS_PS_COURSE type IGS_PS_COURSE type group code.
40 FUNCTION crsp_val_cty_group(
41 p_course_type_group_cd IN VARCHAR2 ,
42 p_message_name OUT NOCOPY VARCHAR2 )
43 RETURN BOOLEAN AS
44 v_closed_ind IGS_PS_TYPE_GRP.closed_ind%TYPE;
45 CURSOR c_course_type_group IS
46 SELECT closed_ind
47 FROM IGS_PS_TYPE_GRP
48 WHERE course_type_group_cd = p_course_type_group_cd;
49 BEGIN
50 OPEN c_course_type_group;
51 FETCH c_course_type_group INTO v_closed_ind;
52 IF c_course_type_group%NOTFOUND THEN
53 p_message_name := NULL;
54 CLOSE c_course_type_group;
55 RETURN TRUE;
56 ELSIF (v_closed_ind = 'N') THEN
57 p_message_name := NULL;
58 CLOSE c_course_type_group;
59 RETURN TRUE;
60 ELSE
61 p_message_name := 'IGS_PS_PRGTYPE_GRPCD_CLOSED';
62 CLOSE c_course_type_group;
63 RETURN FALSE;
64 END IF;
65 EXCEPTION
66 WHEN OTHERS THEN
67 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
68 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CTY.crsp_val_cty_group');
69 IGS_GE_MSG_STACK.ADD;
70 APP_EXCEPTION.RAISE_EXCEPTION;
71 END crsp_val_cty_group;
72 --
73 -- Validate IGS_PS_COURSE type IGS_PS_AWD IGS_PS_COURSE indicator.
74 FUNCTION crsp_val_cty_award(
75 p_course_type IN VARCHAR2 ,
76 p_award_course_ind IN VARCHAR2 DEFAULT 'N',
77 p_message_name OUT NOCOPY VARCHAR2 )
78 RETURN BOOLEAN AS
79 /***************************************************************
80 Created By :
81 Date Created By :
82 Purpose :
83 Known Limitations,Enhancements or Remarks:
84 Change History :
85 Who When What
86 smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_sel_course_award to select open program awards only.
87 ***************************************************************/
88
89 v_check CHAR;
90 CURSOR c_sel_course_award IS
91 SELECT 'x'
92 FROM IGS_PS_VER cv,
93 IGS_PS_AWARD ca
94 WHERE course_type = p_course_type AND
95 cv.course_cd = ca.course_cd AND
96 cv.version_number = ca.version_number AND
97 ca.closed_ind = 'N';
98 BEGIN
99 IF (p_award_course_ind = 'N') THEN
100 OPEN c_sel_course_award;
101 FETCH c_sel_course_award INTO v_check;
102 -- no IGS_PS_AWARD record should exist for
103 -- IGS_PS_VER that have current IGS_PS_TYPE
104 IF c_sel_course_award%FOUND THEN
105 CLOSE c_sel_course_award;
106 p_message_name := 'IGS_PS_AWARD_PRG_NOTSET_N';
107 RETURN FALSE;
108 END IF;
109 CLOSE c_sel_course_award;
110 END IF;
111 p_message_name := NULL;
112 RETURN TRUE;
113 EXCEPTION
114 WHEN OTHERS THEN
115 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
116 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CTY.crsp_val_cty_award');
117 IGS_GE_MSG_STACK.ADD;
118 APP_EXCEPTION.RAISE_EXCEPTION;
119 END crsp_val_cty_award;
120 END IGS_PS_VAL_CTY;