1 PACKAGE BODY IGS_PS_VAL_UOo AS
2 /* $Header: IGSPS65B.pls 120.1 2006/01/31 01:52:34 sommukhe noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --sommukhe 12-JAN-2006 Bug #4926548, In crsp_val_uoo_contact for cursor replaced IGS_PE_PERSON with HZ_PARTIES
7 --smadathi 24-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
8 --avenkatr 30-AUG-2001 Bug No 1956374. Removed procedure "crsp_val_crs_ci"
9 -------------------------------------------------------------------------------------------
10 --
11
12 -- Validate IGS_PS_COURSE IGS_AD_LOCATION code.
13 FUNCTION crsp_val_loc_cd(
14 p_location_cd IN VARCHAR2 ,
15 p_message_name OUT NOCOPY VARCHAR2 )
16 RETURN BOOLEAN AS
17 v_location_closed_ind IGS_AD_LOCATION.closed_ind%TYPE;
18 v_location_type IGS_AD_LOCATION.location_type%TYPE;
19 v_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
20 CURSOR c_location_cd(
21 cp_location_cd IGS_AD_LOCATION.location_cd%TYPE) IS
22 SELECT IGS_AD_LOCATION.closed_ind,
23 location_type
24 FROM IGS_AD_LOCATION
25 WHERE location_cd = cp_location_cd;
26 CURSOR c_location_type(
27 cp_location_type IGS_AD_LOCATION_TYPE.location_type%TYPE) IS
28 SELECT s_location_type
29 FROM IGS_AD_LOCATION_TYPE
30 WHERE location_type = cp_location_type;
31 BEGIN
32 -- This module based on the parameter performs validations
33 -- for for the IGS_AD_LOCATION code within the CS and P subsystem
34 p_message_name := NULL;
35 v_location_closed_ind := NULL;
36 -- Test the value of closed indicator
37 OPEN c_location_cd(
38 p_location_cd);
39 FETCH c_location_cd INTO v_location_closed_ind,
40 v_location_type;
41 CLOSE c_location_cd;
42 IF (v_location_closed_ind IS NULL) THEN
43 RETURN TRUE;
44 ELSE
45 IF(v_location_closed_ind = 'Y') THEN
46 p_message_name := 'IGS_PS_LOC_CODE_CLOSED';
47 RETURN FALSE;
48 END IF;
49 END IF;
50 -- Test the value of system IGS_AD_LOCATION type
51 OPEN c_location_type(
52 v_location_type);
53 FETCH c_location_type INTO v_s_location_type;
54 CLOSE c_location_type;
55 IF (NVL(v_s_location_type,'NULL') <> 'CAMPUS') THEN
56 p_message_name := 'IGS_PS_LOC_NOT_TYPE_CAMPUS';
57 RETURN FALSE;
58 END IF;
59 RETURN TRUE;
60
61 END crsp_val_loc_cd;
62 --
63 -- Validate the IGS_PS_UNIT class for IGS_PS_UNIT offering option.
64 FUNCTION crsp_val_uoo_uc(
65 p_unit_class IN VARCHAR2 ,
66 p_message_name OUT NOCOPY VARCHAR2 )
67 RETURN BOOLEAN AS
68 v_closed_ind IGS_AS_UNIT_CLASS.closed_ind%TYPE;
69 CURSOR c_unit_cls_closed_ind IS
70 SELECT closed_ind
71 FROM IGS_AS_UNIT_CLASS
72 WHERE unit_class = p_unit_class AND
73 closed_ind = 'Y';
74 BEGIN
75 OPEN c_unit_cls_closed_ind;
76 FETCH c_unit_cls_closed_ind INTO v_closed_ind;
77 --- If a record was found, then return TRUE, else return FALSE
78 IF c_unit_cls_closed_ind%NOTFOUND THEN
79 p_message_name := NULL;
80 CLOSE c_unit_cls_closed_ind;
81 RETURN TRUE;
82 ELSE
83 p_message_name := 'IGS_PS_UNIT_CLASS_CLOSED';
84 CLOSE c_unit_cls_closed_ind;
85 RETURN FALSE;
86 END IF;
87 EXCEPTION
88 WHEN OTHERS THEN
89 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
90 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UOo.crsp_val_uoo_uc');
91 IGS_GE_MSG_STACK.ADD;
92 App_Exception.Raise_Exception;
93 END crsp_val_uoo_uc;
94 --
95
96 --
97 -- Validate the IGS_PS_UNIT contact for IGS_PS_UNIT offering option is a staff member.
98 FUNCTION crsp_val_uoo_contact(
99 p_person_id IN NUMBER ,
100 p_message_name OUT NOCOPY VARCHAR2 )
101 RETURN BOOLEAN IS
102 BEGIN
103 DECLARE
104 v_staff_member_ind IGS_PE_PERSON.staff_member_ind%TYPE;
105 CURSOR c_staff_member_ind IS
106 SELECT IGS_EN_GEN_003.Get_Staff_Ind(party_id) staff_member_ind
107 FROM hz_parties
108 WHERE party_id = p_person_id;
109 BEGIN
110 OPEN c_staff_member_ind;
111 FETCH c_staff_member_ind INTO v_staff_member_ind;
112 -- this validates whether the IGS_PE_PERSON is a staff
113 -- member or not
114 IF (v_staff_member_ind = 'Y') THEN
115 CLOSE c_staff_member_ind;
116 p_message_name := NULL;
117 RETURN TRUE;
118 ELSE
119 CLOSE c_staff_member_ind;
120 p_message_name := 'IGS_PS_UNIT_CONTATC_UOP';
121 RETURN FALSE;
122 END IF;
123 EXCEPTION
124 WHEN OTHERS THEN
125 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
126 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UOo.crsp_val_uoo_contact');
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END;
130 END crsp_val_uoo_contact;
131 --
132 -- Validate IGS_PS_UNIT Offering Option is active.
133 FUNCTION CRSP_VAL_UOO_INACTIV(
134 p_unit_cd IN VARCHAR2 ,
135 p_version_number IN NUMBER ,
136 p_cal_type IN VARCHAR2 ,
137 p_ci_sequence_number IN NUMBER ,
138 p_location_cd IN VARCHAR2 ,
139 p_unit_class IN VARCHAR2 ,
140 p_message_name OUT NOCOPY VARCHAR2 )
141 RETURN BOOLEAN AS
142 BEGIN
143 DECLARE
144 BEGIN
145 --- Set the default message number
146 p_message_name := NULL;
147 --- Check for an INACTIVE IGS_PS_UNIT version
148 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl(p_unit_cd,
149 p_version_number,
150 p_message_name) = FALSE THEN
151 RETURN FALSE;
152 END IF;
153 --- Check that the calendar type is not closed.
154 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UOo.crsp_val_uo_cal_type
155 IF IGS_AS_VAL_UAI.crsp_val_uo_cal_type(p_cal_type,
156 p_message_name) = FALSE THEN
157 RETURN FALSE;
158 END IF;
159 --- Check for active calendar instance.
160 IF IGS_as_VAL_uai.crsp_val_crs_ci(p_cal_type,
161 p_ci_sequence_number,
162 p_message_name) = FALSE THEN
163 RETURN FALSE;
164 END IF;
165 --- Check for closed IGS_AD_LOCATION code.
166 IF IGS_PS_VAL_UOo.crsp_val_loc_cd(p_location_cd,
167 p_message_name) = FALSE THEN
168 RETURN FALSE;
169 END IF;
170 --- Check for closed IGS_PS_UNIT class.
171 IF IGS_PS_VAL_UOo.crsp_val_uoo_uc(p_unit_class,
172 p_message_name) = FALSE THEN
173 RETURN FALSE;
174 END IF;
175 --- Return the default value
176 RETURN TRUE;
177 END;
178 EXCEPTION
179 WHEN OTHERS THEN
180 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
181 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UOo.crsp_val_uoo_inactiv');
182 IGS_GE_MSG_STACK.ADD;
183 App_Exception.Raise_Exception;
184 END crsp_val_uoo_inactiv;
185 --
186
187 END IGS_PS_VAL_UOo;