1 PACKAGE BODY igs_uc_gen_001 AS
2 /* $Header: IGSUC01B.pls 120.3 2006/08/21 03:51:26 jbaber noship $ */
3
4
5 PROCEDURE cvname_references(p_type IN VARCHAR2,
6 p_appno IN NUMBER,
7 p_surname IN VARCHAR2,
8 p_birthdate IN DATE,
9 p_system_code IN igs_uc_ucas_control.system_code%TYPE,
10 l_result OUT NOCOPY igs_uc_gen_001.cur_step_def) IS
11
12 /******************************************************************
13 Created By : pmarada
14 Date Created By :
15 Purpose : This procedure returns the cursor values to the IGSUC009 pld.
16
17 Known limitations,enhancements,remarks:
18 Change History
19 Who When What
20 jbaber 12-Jul-05 Included check_digit in appno for post 2005 configuration
21 jbaber 19-Aug-05 Modified for UC307 - HERCULES Small System Support
22 NOTE: Should be coded with dynamic ref cursor but this does
23 not work when called from Oracle Forms. WebIV - Note 170881.1
24 ***************************************************************** */
25
26 -- get configured cycle
27 CURSOR c_cycle IS
28 SELECT configured_cycle
29 FROM IGS_UC_DEFAULTS
30 WHERE system_code = NVL(p_system_code, 'U');
31
32 l_cycle IGS_UC_DEFAULTS.configured_cycle%TYPE;
33
34 l_appno_pad IGS_UC_U_CVNAME_2003.appno%TYPE;
35 l_appno IGS_UC_U_CVNAME_2003.appno%TYPE;
36
37 BEGIN
38
39 OPEN c_cycle;
40 FETCH c_cycle INTO l_cycle;
41 CLOSE c_cycle;
42
43 IF l_cycle < 2006 THEN
44
45 IF p_type = 'CVNAMEDET' THEN -- Used this cursor in Enqdet block When New block instance procedure
46 OPEN l_result FOR SELECT appno, checkdigit, surname, forenames, birthdate, sex, title FROM igs_uc_u_cvname_2003 WHERE appno = p_appno;
47 ELSIF p_type = 'FOUND' THEN -- Used this cursor in post forms commit.
48 OPEN l_result FOR SELECT appno FROM igs_uc_u_cvname_2003 WHERE appno = p_appno;
49 ELSIF p_type = 'QUERYFIND' THEN -- this cursor Used in the query find procedure
50 OPEN l_result FOR SELECT appno FROM igs_uc_u_cvname_2003 WHERE (INDEXSURN = UPPER(p_surname) AND TRUNC(BIRTHDATE) = TRUNC(p_birthdate)) OR appno = p_appno;
51 ELSIF p_type = 'CVCOUNT' THEN -- This cursor is used in the queryfind for name search
52 OPEN l_result FOR SELECT count(*) count1 FROM igs_uc_u_cvname_2003 WHERE INDEXSURN = UPPER(p_surname) AND TRUNC(BIRTHDATE) = TRUNC(p_birthdate);
53 END IF;
54 ELSE
55
56 -- use padded and unpadded versions of appno as appno in cvnames may not have leading zero
57 -- alternate method would be to LPAD the where column of the query but this would remove indexing
58 l_appno_pad := LPAD(p_appno || igs_uc_mv_data_upld.get_check_digit(p_appno),9,0);
59 l_appno := p_appno || igs_uc_mv_data_upld.get_check_digit(p_appno);
60
61 IF p_system_code = 'U' THEN
62
63 IF p_type = 'CVNAMEDET' THEN -- Used this cursor in Enqdet block When New block instance procedure
64 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8), checkdigit, surname, forenames, birthdate, sex, title FROM igs_uc_u_cvname_2003 WHERE appno IN (l_appno, l_appno_pad);
65 ELSIF p_type = 'FOUND' THEN -- Used this cursor in post forms commit.
66 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8) FROM igs_uc_u_cvname_2003 WHERE appno IN (l_appno, l_appno_pad);
67 ELSIF p_type = 'QUERYFIND' THEN -- this cursor Used in the query find procedure
68 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8) FROM igs_uc_u_cvname_2003 WHERE (UPPER(surname) = UPPER(p_surname) AND TRUNC(BIRTHDATE) = TRUNC(p_birthdate)) OR appno IN (l_appno, l_appno_pad);
69 ELSIF p_type = 'CVCOUNT' THEN -- This cursor is used in the queryfind for name search
70 OPEN l_result FOR SELECT count(*) count1 FROM igs_uc_u_cvname_2003 WHERE UPPER(surname) = UPPER(p_surname) AND TRUNC(BIRTHDATE) = TRUNC(p_birthdate);
71 END IF;
72
73 ELSIF p_system_code = 'G' THEN
74
75 IF p_type = 'CVNAMEDET' THEN -- Used this cursor in Enqdet block When New block instance procedure
76 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8), checkdigit, surname, forenames, birthdate, sex, title FROM igs_uc_g_cvgname_2006 WHERE appno IN (l_appno, l_appno_pad);
77 ELSIF p_type = 'FOUND' THEN -- Used this cursor in post forms commit.
78 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8) FROM igs_uc_g_cvgname_2006 WHERE appno IN (l_appno, l_appno_pad);
79 ELSIF p_type = 'QUERYFIND' THEN -- this cursor Used in the query find procedure
80 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8) FROM igs_uc_g_cvgname_2006 WHERE (UPPER(surname) = UPPER(p_surname) AND TRUNC(BIRTHDATE) = TRUNC(p_birthdate)) OR appno IN (l_appno, l_appno_pad);
81 ELSIF p_type = 'CVCOUNT' THEN -- This cursor is used in the queryfind for name search
82 OPEN l_result FOR SELECT count(*) count1 FROM igs_uc_g_cvgname_2006 WHERE UPPER(surname) = UPPER(p_surname) AND TRUNC(BIRTHDATE) = TRUNC(p_birthdate);
83 END IF;
84
85 ELSIF p_system_code = 'N' THEN
86
87 IF p_type = 'CVNAMEDET' THEN -- Used this cursor in Enqdet block When New block instance procedure
88 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8), checkdigit, surname, forenames, birthdate, sex, title FROM igs_uc_n_cvnname_2006 WHERE appno IN (l_appno, l_appno_pad);
89 ELSIF p_type = 'FOUND' THEN -- Used this cursor in post forms commit.
90 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8) FROM igs_uc_n_cvnname_2006 WHERE appno IN (l_appno, l_appno_pad);
91 ELSIF p_type = 'QUERYFIND' THEN -- this cursor Used in the query find procedure
92 OPEN l_result FOR SELECT SUBSTR(LPAD(appno,9,0),1,8) FROM igs_uc_n_cvnname_2006 WHERE (UPPER(surname) = UPPER(p_surname) AND TRUNC(BIRTHDATE) = TRUNC(p_birthdate)) OR appno IN (l_appno, l_appno_pad);
93 ELSIF p_type = 'CVCOUNT' THEN -- This cursor is used in the queryfind for name search
94 OPEN l_result FOR SELECT count(*) count1 FROM igs_uc_n_cvnname_2006 WHERE UPPER(surname) = UPPER(p_surname) AND TRUNC(BIRTHDATE) = TRUNC(p_birthdate);
95 END IF;
96
97 END IF;
98
99 END IF;
100
101 END cvname_references;
102
103
104 PROCEDURE ss_identify_trans_page(p_uc_tran_id IN VARCHAR2,
105 p_page_function OUT NOCOPY VARCHAR2) IS
106
107 /******************************************************************
108 Created By : pmarada
109 Date Created By : 08-Nov-03
110 Purpose : This procedure returns target source page function,
111 Used in admissions Enter Decision Outcomes page
112
113 Known limitations,enhancements,remarks:
114 Change History
115 Who When What
116 ayedubat 24-Nov-2003 To add a new cursor, trans_dtls_cur for deriving decision and system code
117 ***************************************************************** */
118 l_system_code igs_uc_transactions.system_code%TYPE;
119 l_decision igs_uc_transactions.decision%TYPE;
120
121 -- Get decision and system code from the transaction record
122 CURSOR trans_dtls_cur( p_uc_tran_id IGS_UC_TRANSACTIONS.uc_tran_id%TYPE) IS
123 SELECT decision, system_code
124 FROM igs_uc_transactions
125 WHERE uc_tran_id = p_uc_tran_id;
126
127 BEGIN
128
129 -- Get the Decision and System Code from the transaction record
130 OPEN trans_dtls_cur ( TO_NUMBER(p_uc_tran_id));
131 FETCH trans_dtls_cur INTO l_decision, l_system_code;
132 CLOSE trans_dtls_cur;
133
134 IF ((l_system_code = 'S' AND l_decision = 'R') OR
135 (l_system_code = 'G' AND l_decision = 'R') OR
136 (l_system_code = 'G' AND l_decision = 'S') OR
137 (l_system_code = 'G' AND l_decision = 'M') OR
138 (l_system_code = 'G' AND l_decision = 'E') OR
139 (l_system_code = 'G' AND l_decision = 'G') OR
140 (l_system_code = 'G' AND l_decision = 'X')) THEN
141
142 p_page_function := 'IGS_UC_REVIEW_TRANSACTIONS';
143
144 ELSE
145
146 p_page_function := 'IGS_UC_ENTER_TRANS_DTLS_PAGE';
147
148 END IF;
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 -- Incase if there is any exception, raise the exception
153 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
154 fnd_message.set_token('NAME','IGS_UC_GEN_001.SS_IDENTIFY_TRANS_PAGE');
155 igs_ge_msg_stack.add;
156 app_exception.raise_exception;
157
158 END ss_identify_trans_page;
159
160 PROCEDURE get_transaction_toy(p_system_code IN VARCHAR2,
161 p_ucas_cycle IN NUMBER,
162 p_transaction_toy OUT NOCOPY VARCHAR2 ) IS
163
164 /******************************************************************
165 Created By : pmarada
166 Date Created By : 10-Mar-04
167 Purpose : This procedure returns the transaction time of year
168 and used in IGSUC21B and IGSUC23B procedures
169
170 Known limitations,enhancements,remarks:
171 Change History
172 Who When What
173 ***************************************************************** */
174
175 CURSOR c_uc_control IS
176 SELECT time_of_year, extra_start_date, last_le_date, transaction_toy_code
177 FROM igs_uc_ucas_control
178 WHERE system_code = p_system_code
179 AND ucas_cycle = p_ucas_cycle;
180
181 l_uc_control c_uc_control%ROWTYPE;
182
183 BEGIN
184
185 OPEN c_uc_control;
186 FETCH c_uc_control INTO l_uc_control;
187 CLOSE c_uc_control;
188
189 p_transaction_toy := NULL;
190 p_transaction_toy := l_uc_control.transaction_toy_code;
191
192 END get_transaction_toy;
193
194 FUNCTION validate_personal_id(p_personal_id IN VARCHAR2) RETURN BOOLEAN IS
195 /******************************************************************
196 Created By : jbaber
197 Date Created By : 10-Jul-06
198 Purpose : This procedure validates the personal id
199
200 Known limitations,enhancements,remarks:
201 Change History
202 Who When What
203 ***************************************************************** */
204
205 personal_id NUMBER;
206
207 BEGIN
208
209 -- Make sure personal id is a number
210 personal_id := TO_NUMBER(p_personal_id);
211
212 -- Check it is within range 1000000000 to 8999999999
213 IF personal_id >= 1000000000 AND personal_id <= 8999999999 THEN
214 RETURN TRUE;
215 END IF;
216
217 RETURN FALSE;
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 RETURN FALSE;
222
223 END validate_personal_id;
224
225
226 END igs_uc_gen_001;
[Home] [Help]