[Home] [Help]
PACKAGE BODY: APPS.IGS_RU_GEN_004
Source
1 PACKAGE BODY IGS_RU_GEN_004 AS
2 /* $Header: IGSRU04B.pls 115.7 2002/11/29 03:39:42 nsidana ship $ */
3
4 Procedure Rulp_Ins_Make_Rule(
5 p_description_number IN NUMBER DEFAULT NULL,
6 p_return_type VARCHAR2 DEFAULT NULL,
7 p_rule_description VARCHAR2 DEFAULT NULL,
8 p_turing_function VARCHAR2 DEFAULT NULL,
9 p_rule_text VARCHAR2 DEFAULT NULL,
10 p_message_rule_text VARCHAR2 DEFAULT NULL,
11 p_description_text VARCHAR2 ,
12 p_group IN NUMBER DEFAULT 1,
13 p_select_group IN NUMBER DEFAULT 1)
14 IS
15 ------------------------------------------------------------------
16 --Created by : nsinha, Oracle India
17 --Date created: 12-Mar-2001
18 --
19 --Purpose: Expand description_number to IGS_RU_DESCRIPTION or group_name
20 -- if invalid set message number and return NULL
21 --
22 --Known limitations/enhancements and/or remarks:
23 --
24 --Change History:
25 --Who When What
26 --nsinha 12-Mar-2002 Bug# 2233951: Moved the content of this
27 -- procedure in Igs_ru_gen_006.Rulp_Ins_Make_Rule
28 -- and called it from there.
29 --
30 -------------------------------------------------------------------
31 BEGIN
32 Igs_ru_gen_006.Rulp_Ins_Make_Rule (
33 p_description_number,
34 p_return_type,
35 p_rule_description,
36 p_turing_function,
37 p_rule_text,
38 p_message_rule_text,
39 p_description_text,
40 p_group,
41 p_select_group);
42 END rulp_ins_make_rule;
43
44 Function Rulp_Ins_Ur_Rule(
45 p_unit_cd IN VARCHAR2 ,
46 p_s_rule_call_cd IN VARCHAR2 ,
47 p_insert_rule_only IN BOOLEAN ,
48 p_rul_sequence_number OUT NOCOPY NUMBER ,
49 p_message_name OUT NOCOPY VARCHAR2 )
50 RETURN BOOLEAN IS
51 ------------------------------------------------------------------
52 --Created by : nsinha, Oracle India
53 --Date created: 12-Mar-2002
54 --
55 --Purpose:
56 -- This module inserts a IGS_PS_UNIT_RU asscoaited with a IGS_PS_UNIT. This involves:
57 -- Creating IGS_PS_UNIT_RU IGS_RU_RULE and associated records (IGS_RU_RULE) when the
58 -- IGS_PS_UNIT_RU does not already exist.
59 -- Set the default message number
60 --
61 --Known limitations/enhancements and/or remarks:
62 --
63 --Change History:
64 --Who When What
65 --nsinha 12-Mar-2002 Bug# 2233951: Modified the logic to
66 -- SELECT the next value of the sequence number
67 -- differently when the data is for SEED DB.
68 --
69 -------------------------------------------------------------------
70 gv_other_detail VARCHAR2(255);
71 MORE_THAN_ONE_UNIT_RULE_RECORD EXCEPTION;
72 BEGIN
73 DECLARE
74 v_rowid VARCHAR2(25);
75 v_rowid_pur VARCHAR2(25);
76 v_count NUMBER(6);
77 v_ur_sequence_number IGS_PS_UNIT_RU.rul_sequence_number%TYPE;
78 s_n_next IGS_RU_RULE.sequence_number%TYPE;
79 CURSOR c_ur IS
80 SELECT ur.rul_sequence_number
81 FROM IGS_PS_UNIT_RU ur
82 WHERE ur.unit_cd = p_unit_cd AND
83 ur.s_rule_call_cd = p_s_rule_call_cd;
84 CURSOR c_ur_count IS
85 SELECT count(*)
86 FROM IGS_PS_UNIT_RU ur
87 WHERE ur.unit_cd = p_unit_cd AND
88 ur.s_rule_call_cd = p_s_rule_call_cd;
89
90 CURSOR C_IGS_RU_RULE_SEQ_NUM_S IS
91 SELECT IGS_RU_RULE_SEQ_NUM_S.NEXTVAL
92 FROM DUAL;
93
94 CURSOR cur_max_plus_one IS
95 SELECT MAX (sequence_number) + 1 sequence_number
96 FROM IGS_RU_RULE
97 WHERE sequence_number < 499999;
98
99 BEGIN
100 p_message_name := Null;
101
102 -- Validate input parameters.
103 IF p_unit_cd IS NULL OR p_s_rule_call_cd IS NULL THEN
104 p_rul_sequence_number := 0;
105 p_message_name := 'IGS_GE_INVALID_VALUE';
106 RETURN FALSE;
107 END IF;
108
109 -- Create IGS_PS_UNIT_RU if it doesn't already exist.
110
111 OPEN c_ur;
112 FETCH c_ur INTO v_ur_sequence_number;
113 p_rul_sequence_number := v_ur_sequence_number;
114 IF (c_ur%NOTFOUND) THEN
115 --
116 -- New description number
117 -- If the User creating this record is DATAMERGE (id = 1) then
118 -- Get the sequence as the existing maximum value + 1
119 -- Else
120 -- Get the next value from the database sequence
121 --
122 IF (fnd_global.user_id = 1) THEN
123 OPEN cur_max_plus_one;
124 FETCH cur_max_plus_one INTO s_n_next;
125 CLOSE cur_max_plus_one;
126 ELSE
127 OPEN C_IGS_RU_RULE_SEQ_NUM_S;
128 FETCH C_IGS_RU_RULE_SEQ_NUM_S INTO s_n_next;
129 IF C_IGS_RU_RULE_SEQ_NUM_S%NOTFOUND THEN
130 RAISE NO_DATA_FOUND;
131 END IF;
132 CLOSE C_IGS_RU_RULE_SEQ_NUM_S;
133 END IF;
134
135 -- Create IGS_PS_UNIT_RU and associated records.
136
137 IGS_RU_RULE_PKG.Insert_Row(
138 x_rowid => v_rowid,
139 x_sequence_number => s_n_next
140 );
141
142 p_rul_sequence_number := s_n_next;
143 IF p_insert_rule_only = FALSE THEN
144 IGS_PS_UNIT_RU_PKG.Insert_Row(
145 x_rowid => v_rowid_pur,
146 x_unit_cd => p_unit_cd,
147 x_s_rule_call_cd => p_s_rule_call_cd,
148 x_rul_sequence_number => s_n_next
149 );
150
151 END IF;
152 END IF;
153 IF (c_ur%FOUND) THEN
154 OPEN c_ur_count;
155 FETCH c_ur_count INTO v_count;
156 IF v_count > 1 THEN
157 CLOSE c_ur;
158 CLOSE c_ur_count;
159 RAISE MORE_THAN_ONE_UNIT_RULE_RECORD;
160 END IF;
161 CLOSE c_ur_count;
162 END IF;
163 CLOSE c_ur;
164 /*
165 Return the default value
166 */
167 RETURN TRUE;
168 END;
169 EXCEPTION
170 WHEN MORE_THAN_ONE_UNIT_RULE_RECORD THEN
171 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
172 IGS_GE_MSG_STACK.ADD;
173 App_Exception.Raise_Exception;
174 WHEN OTHERS THEN
175 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
176 Fnd_Message.Set_Token('NAME','IGS_RU_GEN_004.rulp_ins_ur_rule');
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception;
179 END rulp_ins_ur_rule;
180
181 Function Rulp_Val_Adm_Status(
182 p_letter_parameter_type IN VARCHAR2 ,
183 p_adm_appl_status IN VARCHAR2 ,
184 p_adm_fee_status IN VARCHAR2 ,
185 p_adm_doc_status IN VARCHAR2 ,
186 p_adm_entry_qual_status IN VARCHAR2 ,
187 p_late_adm_fee_status IN VARCHAR2 ,
188 p_adm_outcome_status IN VARCHAR2 ,
189 p_adm_cndtnl_offer_status IN VARCHAR2 ,
190 p_adm_offer_resp_status IN VARCHAR2 ,
191 p_adm_offer_dfrmnt_status IN VARCHAR2 ,
192 p_reconsideration IN BOOLEAN ,
193 p_encumbrance IN BOOLEAN ,
194 p_course_invalid IN BOOLEAN ,
195 p_late IN BOOLEAN ,
196 p_incomplete IN BOOLEAN ,
197 p_correspondence_type VARCHAR2 ,
198 p_valid_alternate BOOLEAN ,
199 p_valid_address BOOLEAN ,
200 p_valid_disability BOOLEAN ,
201 p_valid_visa BOOLEAN ,
202 p_valid_finance BOOLEAN ,
203 p_valid_notes BOOLEAN ,
204 p_valid_statistics BOOLEAN ,
205 p_valid_alias BOOLEAN ,
206 p_valid_tertiary BOOLEAN ,
207 p_valid_aus_sec_ed BOOLEAN ,
208 p_valid_os_sec_ed BOOLEAN ,
209 p_valid_employment BOOLEAN ,
210 p_valid_membership BOOLEAN ,
211 p_valid_dob BOOLEAN ,
212 p_valid_title BOOLEAN ,
213 p_valid_referee BOOLEAN ,
214 p_valid_scholarship BOOLEAN ,
215 p_valid_lang_prof BOOLEAN ,
216 p_valid_interview BOOLEAN ,
217 p_valid_exchange BOOLEAN ,
218 p_valid_adm_test IN BOOLEAN ,
219 p_valid_fee_assess BOOLEAN ,
220 p_valid_cor_category BOOLEAN ,
221 p_valid_enr_category BOOLEAN ,
222 p_valid_research BOOLEAN ,
223 p_valid_rank_app BOOLEAN ,
224 p_valid_completion BOOLEAN ,
225 p_valid_rank_set BOOLEAN ,
226 p_valid_basis_adm BOOLEAN ,
227 p_valid_crs_international BOOLEAN ,
228 p_valid_ass_tracking BOOLEAN ,
229 p_valid_adm_code BOOLEAN ,
230 p_valid_fund_source IN BOOLEAN ,
231 p_valid_location BOOLEAN ,
232 p_valid_att_mode BOOLEAN ,
233 p_valid_att_type BOOLEAN ,
234 p_valid_unit_set BOOLEAN )
235 RETURN VARCHAR2 IS
236 /*
237
238 admission status call stub to senna
239
240 */
241 v_message VARCHAR2(2000);
242 FUNCTION boolean_to_turing (
243 p_boolean BOOLEAN )
244 RETURN VARCHAR2 IS
245 BEGIN
246 IF p_boolean
247 THEN
248 RETURN 'true';
249 ELSE
250 RETURN 'false';
251 END IF;
252 END boolean_to_turing;
253 /*
254
255 rulp_val_adm_status
256
257 */
258 BEGIN
259 RETURN IGS_RU_GEN_001.RULP_VAL_SENNA(p_message=>v_message,
260 p_rule_call_name=>'ADM_STATUS',
261 /*
262 p_person_id=>p_person_id,
263 p_course_cd=>p_course_cd,
264 p_course_version=>p_crs_version_number,
265 */
266 p_param_1=>p_adm_appl_status,
267 p_param_2=>p_adm_fee_status,
268 p_param_3=>p_adm_doc_status,
269 p_param_4=>p_adm_entry_qual_status,
270 p_param_5=>p_late_adm_fee_status,
271 p_param_6=>p_adm_outcome_status,
272 p_param_7=>p_adm_cndtnl_offer_status,
273 p_param_8=>p_adm_offer_resp_status,
274 p_param_9=>p_adm_offer_dfrmnt_status,
275 p_param_10=>boolean_to_turing(p_reconsideration),
276 p_param_11=>boolean_to_turing(p_encumbrance),
277 p_param_12=>boolean_to_turing(p_course_invalid),
278 p_param_13=>boolean_to_turing(p_late),
279 p_param_14=>boolean_to_turing(p_incomplete),
280 p_param_15=>p_correspondence_type,
281 p_param_16=>boolean_to_turing(p_valid_alternate),
282 p_param_17=>boolean_to_turing(p_valid_address),
283 p_param_18=>boolean_to_turing( p_valid_disability),
284 p_param_19=>boolean_to_turing(p_valid_visa),
285 p_param_20=>boolean_to_turing(p_valid_finance),
286 p_param_21=>boolean_to_turing(p_valid_notes),
287 p_param_22=>boolean_to_turing(p_valid_statistics) ,
288 p_param_23=>boolean_to_turing(p_valid_alias),
289 p_param_24=>boolean_to_turing(p_valid_tertiary),
290 p_param_25=>boolean_to_turing(p_valid_aus_sec_ed),
291 p_param_26=>boolean_to_turing(p_valid_os_sec_ed),
292 p_param_27=>boolean_to_turing(p_valid_employment),
293 p_param_28=>boolean_to_turing(p_valid_membership),
294 p_param_29=>boolean_to_turing(p_valid_dob),
295 p_param_30=>boolean_to_turing(p_valid_title),
296 p_param_31=>boolean_to_turing(p_valid_referee),
297 p_param_32=>boolean_to_turing(p_valid_scholarship),
298 p_param_33=>boolean_to_turing(p_valid_lang_prof),
299 p_param_34=>boolean_to_turing(p_valid_interview),
300 p_param_35=>boolean_to_turing(p_valid_exchange),
301 p_param_36=>boolean_to_turing(p_valid_fee_assess),
302 p_param_37=>boolean_to_turing(p_valid_cor_category),
303 p_param_38=>boolean_to_turing(p_valid_enr_category),
304 p_param_39=>boolean_to_turing(p_valid_research),
305 p_param_40=>boolean_to_turing(p_valid_rank_app),
306 p_param_41=>boolean_to_turing(p_valid_completion),
307 p_param_42=>boolean_to_turing(p_valid_rank_set),
308 p_param_43=>boolean_to_turing(p_valid_basis_adm),
309 p_param_44=>boolean_to_turing(p_valid_crs_international),
310 p_param_45=>boolean_to_turing(p_valid_ass_tracking),
311 p_param_46=>boolean_to_turing(p_valid_adm_code),
312 p_param_47=>boolean_to_turing(p_valid_location),
313 p_param_48=>boolean_to_turing(p_valid_att_mode),
314 p_param_49=>boolean_to_turing(p_valid_att_type),
315 p_param_50=>boolean_to_turing(p_valid_unit_set),
316 p_param_51=>p_letter_parameter_type,
317 p_param_52=>boolean_to_turing(p_valid_adm_test),
318 p_param_53=>boolean_to_turing(p_valid_fund_source) );
319 END rulp_val_adm_status;
320
321 Function Rulp_Val_Desc_Rgi(
322 p_description_number IN NUMBER ,
323 p_description_type IN VARCHAR2 ,
324 p_message_name OUT NOCOPY VARCHAR2 )
325 RETURN VARCHAR2 IS
326 ------------------------------------------------------------------
327 --Created by : nsinha, Oracle India
328 --Date created: 12-Mar-2001
329 --
330 --Purpose: Expand description_number to IGS_RU_DESCRIPTION or group_name
331 -- if invalid set message number and return NULL
332 --
333 --Known limitations/enhancements and/or remarks:
334 --
335 --Change History:
336 --Who When What
337 --nsinha 12-Mar-2002 Bug# 2233951: Moved the content of this
338 -- function in Igs_ru_gen_006.Rulp_Val_Desc_Rgi
339 -- and called it from there.
340 --
341 -------------------------------------------------------------------
342 v_description igs_ru_description.rule_description%TYPE;
343 l_message_name VARCHAR2(255);
344 BEGIN
345 v_description := Igs_ru_gen_006.Rulp_Val_Desc_Rgi(
346 p_description_number,
347 p_description_type,
348 l_message_name);
349 IF l_message_name IS NOT NULL THEN
350 p_message_name := l_message_name;
351 RETURN NULL;
352 ELSE
353 RETURN v_description;
354 END IF;
355 END rulp_val_desc_rgi;
356
357 Function Rulp_Val_Gpa(
358 p_person_id IN NUMBER ,
359 p_course_cd IN VARCHAR2 ,
360 p_prg_cal_type IN VARCHAR2 ,
361 p_prg_ci_sequence_number IN NUMBER ,
362 p_best_worst IN VARCHAR2 DEFAULT 'N',
363 p_recommend_ind IN VARCHAR2 DEFAULT 'N')
364 RETURN VARCHAR2 IS
365 /*
366
367 return the gpa for a student
368
369 */
370 v_message VARCHAR2(2000);
371 BEGIN
372 RETURN IGS_RU_GEN_001.RULP_VAL_SENNA (
373 p_rule_call_name=>'GPA',
374 p_person_id=>p_person_id,
375 p_course_cd=>p_course_cd,
376 p_cal_type=>p_prg_cal_type,
377 p_ci_sequence_number=>p_prg_ci_sequence_number,
378 p_param_1=>p_best_worst,
379 p_param_2=>p_recommend_ind,
380 p_message=>v_message );
381 END rulp_val_gpa;
382
383 Function Rulp_Val_Named_Rule(
384 p_return_type VARCHAR2 ,
385 p_rule_name VARCHAR2 ,
386 p_person_id IN NUMBER )
387 RETURN VARCHAR2 IS
388 /*
389
390 Execute the IGS_RU_RULE determined using it return type and name
391
392 */
393 v_rule_number NUMBER;
394 v_message_text VARCHAR2(2000);
395 BEGIN
396 SELECT NR.rul_sequence_number
397 INTO v_rule_number
398 FROM IGS_RU_DESCRIPTION RUD,
399 IGS_RU_NAMED_RULE NR
400 WHERE RUD.s_return_type = p_return_type
401 AND RUD.rule_description = p_rule_name
402 AND NR.rud_sequence_number = RUD.sequence_number;
403 RETURN IGS_RU_GEN_001.RULP_VAL_SENNA (
404 p_rule_number=>v_rule_number,
405 p_person_id=>p_person_id,
406 p_message=>v_message_text );
407 EXCEPTION
408 WHEN NO_DATA_FOUND THEN
409 RETURN NULL;
410 END;
411
412 Function Rulp_Val_Wam(
413 p_person_id IN NUMBER ,
414 p_course_cd IN VARCHAR2 ,
415 p_course_version NUMBER ,
416 p_prg_cal_type IN VARCHAR2 ,
417 p_prg_ci_sequence_number IN NUMBER ,
418 p_recommend_ind IN VARCHAR2 DEFAULT 'N',
419 p_abort_when_missing IN VARCHAR2 DEFAULT 'N')
420 RETURN VARCHAR2 IS
421 /*
422
423 return the wam for a student
424
425 */
426 v_message VARCHAR2(2000);
427 BEGIN
428 RETURN IGS_RU_GEN_001.RULP_VAL_SENNA (
429 p_rule_call_name=>'WAM',
430 p_person_id=>p_person_id,
431 p_course_cd=>p_course_cd,
432 p_course_version=>p_course_version,
433 p_cal_type=>p_prg_cal_type,
434 p_ci_sequence_number=>p_prg_ci_sequence_number,
435 p_param_1=>p_abort_when_missing,
436 p_param_2=>p_recommend_ind,
437 p_message=>v_message );
438 END rulp_val_wam;
439
440 END IGS_RU_GEN_004;