1 PACKAGE BODY IGS_OR_VAL_LOC AS
2 /* $Header: IGSOR05B.pls 115.7 2002/11/29 01:46:53 nsidana ship $ */
3 --msrinivi 24-AUG-2001 Bug No. 1956374 .The function genp_val_prsn_id removed
4 -- Validate the IGS_AD_LOCATION type.
5 FUNCTION orgp_val_loc_type(
6 p_location_type IN VARCHAR2 ,
7 p_message_name OUT NOCOPY VARCHAR2 )
8 RETURN BOOLEAN IS
9 CURSOR c_lot IS
10 SELECT closed_ind
11 FROM IGS_AD_LOCATION_TYPE
12 WHERE location_type = p_location_type
13 AND closed_ind = 'Y';
14 v_other_detail VARCHAR2(255);
15 BEGIN
16 p_message_name := NULL;
17 FOR lot IN c_lot LOOP
18 p_message_name := 'IGS_OR_LOC_TYPE_CLOSED';
19 RETURN FALSE;
20 END LOOP;
21 RETURN TRUE;
22 EXCEPTION
23 WHEN OTHERS THEN
24 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
25 IGS_GE_MSG_STACK.ADD;
26 App_Exception.Raise_Exception ;
27 END orgp_val_loc_type;
28 --
29 -- Retrofitted
30 FUNCTION assp_val_loc_coord(
31 p_location_type IGS_AD_LOCATION_ALL.location_type%TYPE ,
32 p_coord_person_id IGS_AD_LOCATION_ALL.coord_person_id%TYPE ,
33 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
34 RETURN BOOLEAN AS
35 gv_other_detail VARCHAR2(255);
36 BEGIN -- assp_val_loc_coord
37 -- Validate that the co-ordinator has been specified when the location
38 -- is an examination IGS_AD_LOCATION (ie; the system location type is 'EXAM_CTR').
39 DECLARE
40 v_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
41 BEGIN
42 -- Set the default message number
43 p_message_name := NULL;
44 -- Fetch system location type.
45 v_s_location_type := IGS_OR_GEN_001.ORGP_GET_S_LOC_TYPE2(
46 p_location_type);
47 IF (NVL(v_s_location_type, '-1') = 'EXAM_CTR') THEN
48 IF (p_coord_person_id IS NULL) THEN
49 -- The co-ordinator must be set for an examination location.
50 p_message_name := 'IGS_AS_COORD_SET_EXAMLOC';
51 RETURN FALSE;
52 END IF;
53 END IF;
54 -- Validation successful
55 RETURN TRUE;
56 END;
57 EXCEPTION
58 WHEN OTHERS THEN
59 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
60 IGS_GE_MSG_STACK.ADD;
61 App_Exception.Raise_Exception ;
62 END assp_val_loc_coord;
63 --
64 -- Retrofitted
65 FUNCTION assp_val_loc_ve_open(
66 p_location_cd IGS_AD_LOCATION_ALL.location_cd%TYPE ,
67 p_location_type IGS_AD_LOCATION_ALL.location_type%TYPE ,
68 p_closed_ind IGS_AD_LOCATION_ALL.closed_ind%TYPE ,
69 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
70 RETURN BOOLEAN AS
71 gv_other_detail VARCHAR2(255);
72 BEGIN -- assp_val_loc_ve_open
73 -- Validate that an examination location can not be closed if any
74 -- related IGS_GR_VENUE records are not closed.
75 DECLARE
76 v_s_location_type IGS_AD_LOCATION.location_type%TYPE;
77 CURSOR c_ve IS
78 SELECT 'x'
79 FROM IGS_GR_VENUE
80 WHERE exam_location_cd = p_location_cd AND
81 closed_ind = 'N';
82 v_ve_exists VARCHAR2(1);
83 BEGIN
84 -- Set the default message number
85 p_message_name := NULL;
86 IF (p_closed_ind = 'Y') THEN
87 -- Fetch system location type.
88 v_s_location_type := IGS_OR_GEN_001.ORGP_GET_S_LOC_TYPE2(
89 p_location_type);
90 IF (NVL(v_s_location_type, '-1') = 'EXAM_CTR') THEN
91 OPEN c_ve;
92 FETCH c_ve INTO v_ve_exists;
93 IF (c_ve%FOUND) THEN
94 -- An examination location may not be
95 -- closed while open venue records exist.
96 CLOSE c_ve;
97 p_message_name := 'IGS_AS_EXAMLOC_NOTBE_CLOSED';
98 RETURN FALSE;
99 END IF;
100 CLOSE c_ve;
101 END IF;
102 END IF;
103 -- Validation successful
104 RETURN TRUE;
105 END;
106 EXCEPTION
107 WHEN OTHERS THEN
108 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception ;
111 END assp_val_loc_ve_open;
112 --
113 -- Retrofitted
114 FUNCTION assp_val_loc_ve_xist(
115 p_location_cd IGS_AD_LOCATION_ALL.location_cd%TYPE ,
116 p_new_location_type IGS_AD_LOCATION_ALL.location_type%TYPE ,
117 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
118 RETURN BOOLEAN AS
119 gv_other_detail VARCHAR2(255);
120 BEGIN -- assp_val_loc_ve_xist
121 -- Validate that the exam location can not be changed to a non-exam
122 -- location once venue or other examination related tables are related.
123 DECLARE
124 cst_exam_ctr CONSTANT VARCHAR2(10) := 'EXAM_CTR';
125 v_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
126 CURSOR c_ve IS
127 SELECT 'x'
128 FROM IGS_GR_VENUE
129 WHERE exam_location_cd = p_location_cd;
130 v_ve_exists VARCHAR2(1);
131 BEGIN
132 -- Set the default message number
133 p_message_name := NULL;
134 -- Fetch system location type for p_new_location_type.
135 v_s_location_type := IGS_OR_GEN_001.ORGP_GET_S_LOC_TYPE2(
136 p_new_location_type);
137 IF (NVL(v_s_location_type, '-1') <> cst_exam_ctr) THEN
138 OPEN c_ve;
139 FETCH c_ve INTO v_ve_exists;
140 IF (c_ve%FOUND) THEN
141 -- An examination location may not be changed to a
142 -- non-exam location because venue details already exist.
143 CLOSE c_ve;
144 p_message_name := 'IGS_AS_EXAMLOC_NOTCHG';
145 RETURN FALSE;
146 END IF;
147 CLOSE c_ve;
148 END IF;
149 -- Validation successful
150 RETURN TRUE;
151 END;
152 EXCEPTION
153 WHEN OTHERS THEN
154 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception ;
157 END assp_val_loc_ve_xist;
158
159 --
160
161 END IGS_OR_VAL_LOC;