1 PACKAGE BODY IGS_AD_VAL_AAL AS
2 /* $Header: IGSAD17B.pls 115.7 2002/11/28 21:25:55 nsidana ship $ */
3
4 --
5 -- Validate the correspondence type for an admission application letter.
6 FUNCTION admp_val_aal_cort(
7 p_correspondence_type IN VARCHAR2 ,
8 p_admission_cat IN VARCHAR2 ,
9 p_s_admission_process_type IN VARCHAR2 ,
10 p_message_name OUT NOCOPY VARCHAR2 )
11 RETURN BOOLEAN IS
12 BEGIN --admp_val_aal_cort
13 --This module validates the correspondence type to be used
14 --in an admission application letter
15 DECLARE
16 v_slet_exists VARCHAR2(1);
17 v_apcl_exists VARCHAR2(1);
18 v_apcs_exists VARCHAR2(1);
19 CURSOR c_slet IS
20 SELECT 'X'
21 FROM IGS_CO_S_LTR slet
22 WHERE slet.s_letter_reference_type = 'ADM' AND
23 slet.correspondence_type = p_correspondence_type;
24 CURSOR c_apcl IS
25 SELECT 'X'
26 FROM IGS_AD_PRCS_CAT_LTR apcl
27 WHERE apcl.admission_cat = p_admission_cat AND
28 apcl.s_admission_process_type = p_s_admission_process_type;
29 CURSOR c_apcl_2 IS
30 SELECT 'X'
31 FROM IGS_AD_PRCS_CAT_LTR apcl
32 WHERE apcl.admission_cat = p_admission_cat AND
33 apcl.s_admission_process_type = p_s_admission_process_type AND
34 apcl.correspondence_type = p_correspondence_type;
35 CURSOR c_apcs (
36 cp_step_type IGS_AD_PRCS_CAT_STEP.s_admission_step_type%TYPE) IS
37 SELECT 'X'
38 FROM IGS_AD_PRCS_CAT_STEP apcs
39 WHERE apcs.admission_cat = p_admission_cat AND
40 apcs.s_admission_process_type = p_s_admission_process_type AND
41 apcs.s_admission_step_type = cp_step_type AND
42 apcs.step_group_type <> 'TRACK'; --2402377
43 BEGIN
44 -- Set the default message number
45 p_message_name := null;
46 --Check that admissions letters exist with this correspondence type.
47 OPEN c_slet;
48 FETCH c_slet INTO v_slet_exists;
49 IF (c_slet%NOTFOUND) THEN
50 CLOSE c_slet;
51 p_message_name := 'IGS_AD_NOADM_LETTERS_EXIST';
52 RETURN FALSE;
53 END IF;
54 CLOSE c_slet;
55 OPEN c_apcl;
56 FETCH c_apcl INTO v_apcl_exists;
57 IF (c_apcl%FOUND) THEN
58 --Check that a letter with this correspondence exists
59 --for this admission process category.
60 OPEN c_apcl_2;
61 FETCH c_apcl_2 INTO v_apcl_exists;
62 IF (c_apcl_2%NOTFOUND) THEN
63 CLOSE c_apcl;
64 CLOSE c_apcl_2;
65 p_message_name := 'IGS_AD_LETTER_CORTYPE_NOTEXIS';
66 RETURN FALSE;
67 END IF;
68 CLOSE c_apcl_2;
69 END IF;
70 CLOSE c_apcl;
71 --Validate that ACK-APP step defined for this admission process category.
72 IF (p_correspondence_type = 'ACKNOW-LET') THEN
73 OPEN c_apcs('ACK-APP');
74 FETCH c_apcs INTO v_apcs_exists;
75 IF (c_apcs%NOTFOUND) THEN
76 CLOSE c_apcs;
77 p_message_name := 'IGS_AD_CORTYPE_NOTBE_ACKNOW';
78 RETURN FALSE;
79 END IF;
80 CLOSE c_apcs;
81 END IF;
82 --Validate that OUTCOME-LT step defined for this admission process category.
83 IF (p_correspondence_type = 'OUTCOME-LT') THEN
84 OPEN c_apcs('OUTCOME-LT');
85 FETCH c_apcs INTO v_apcs_exists;
86 IF (c_apcs%NOTFOUND) THEN
87 CLOSE c_apcs;
88 p_message_name := 'IGS_AD_CORTYPE_NOTBE_OUTCOME';
89 RETURN FALSE;
90 END IF;
91 CLOSE c_apcs;
92 END IF;
93 RETURN TRUE;
94 END;
95 EXCEPTION
96 WHEN OTHERS THEN
97 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
98 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_AAL.admp_val_aal_cort');
99 IGS_GE_MSG_STACK.ADD;
100 App_Exception.Raise_Exception;
101 END admp_val_aal_cort;
102 --
103 -- Validate if an unsent adm appl letter exists with the same corres type
104 FUNCTION admp_val_aal_exists(
105 p_person_id IN NUMBER ,
106 p_admission_appl_number IN NUMBER ,
107 p_correspondence_type IN VARCHAR2 ,
108 p_message_name OUT NOCOPY VARCHAR2 )
109 RETURN BOOLEAN IS
110 BEGIN -- admp_val_aal_exists
111 -- This module checks if an admission application letter exists
112 -- with the passed correspondence type and checks if it has been sent.
113 DECLARE
114 CURSOR c_aal IS
115 SELECT sequence_number
116 FROM IGS_AD_APPL_LTR
117 WHERE person_id = p_person_id AND
118 admission_appl_number = p_admission_appl_number AND
119 correspondence_type = p_correspondence_type;
120 v_sequence_number IGS_AD_APPL_LTR.sequence_number%TYPE;
121 BEGIN
122 -- Set the default message number
123 p_message_name := null;
124 FOR v_aal_rec IN c_aal LOOP
125 IF IGS_AD_GEN_002.ADMP_GET_AAL_SENT_DT(
126 p_person_id,
127 p_admission_appl_number,
128 p_correspondence_type,
129 v_aal_rec.sequence_number) IS NULL THEN
130 p_message_name := 'IGS_AD_UNISSUED_LETTER_EXISTS';
131 RETURN FALSE;
132 END IF;
133 END LOOP;
134 -- Return the default value
135 RETURN TRUE;
136 END;
137 EXCEPTION
138 WHEN OTHERS THEN
139 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
140 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_AAL.admp_val_aal_exists');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END admp_val_aal_exists;
144 --
145 -- Validate the correspondence type closed indicator.
146 FUNCTION corp_val_cort_closed(
147 p_correspondence_type IN VARCHAR2 ,
148 p_message_name OUT NOCOPY VARCHAR2 )
149 RETURN BOOLEAN IS
150 BEGIN
151 DECLARE
152 v_closed_ind IGS_CO_TYPE.closed_ind%TYPE;
153 CURSOR c_cort(
154 cp_correspondence_type IGS_CO_TYPE.correspondence_type%TYPE) IS
155 SELECT cort.closed_ind
156 FROM IGS_CO_TYPE cort
157 WHERE cort.correspondence_type = cp_correspondence_type;
158 BEGIN
159 -- Validate if the correspondence type is closed
160 p_message_name := null;
161 OPEN c_cort(
162 p_correspondence_type);
163 FETCH c_cort INTO v_closed_ind;
164 IF(c_cort%FOUND = FALSE) THEN
165 CLOSE c_cort;
166 RETURN TRUE;
167 END IF;
168 IF(v_closed_ind = 'Y') THEN
169 CLOSE c_cort;
170 p_message_name := 'IGS_CO_CORTYPE_IS_CLOSED';
171 RETURN FALSE;
172 END IF;
173 CLOSE c_cort;
174 RETURN TRUE;
175 EXCEPTION
176 WHEN OTHERS THEN
177 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
178 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_AAL.corp_val_cort_closed');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END;
182 END corp_val_cort_closed;
183
184 END IGS_AD_VAL_AAL;
[Home] [Help]