1 PACKAGE BODY IGS_AV_VAL_AS AS
2 /* $Header: IGSAV02B.pls 120.2 2006/03/27 01:32:59 shimitta noship $ */
3 -- To validate the advanced standing IGS_PS_COURSE code.
4 -- shimitta 7-Mar-2006 Modified as in Bug# 5068233
5 FUNCTION advp_val_as_crs(
6 p_person_id IN NUMBER ,
7 p_course_cd IN VARCHAR2 ,
8 p_version_number IN NUMBER ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN IS
11 gv_other_detail VARCHAR2(255);
12 BEGIN
13 DECLARE
14 CURSOR c_adv_stnd_v(
15 cp_person_id IGS_AV_ADV_STANDING.person_id%TYPE,
16 cp_course_cd IGS_AV_ADV_STANDING.course_cd%TYPE,
17 cp_version_number IGS_AV_ADV_STANDING.version_number%TYPE) IS
18 SELECT spa.course_cd
19 FROM IGS_EN_STDNT_PS_ATT spa
20 WHERE spa.person_id = cp_person_id AND
21 spa.course_cd = cp_course_cd AND
22 spa.version_number = cp_version_number AND
23 spa.course_attempt_status IN
24 ('ENROLLED', 'INACTIVE', 'INTERMIT', 'UNCONFIRM', 'DISCONTIN','COMPLETED');
25
26 BEGIN
27 -- Validate that IGS_AV_ADV_STANDING.course_cd is valid for the purposes of
28 -- Advanced Standing.
29 -- It must be contained within a IGS_EN_STDNT_PS_ATT by the nominated
30 -- person_id, with a status of 'enrolled', 'inactive', 'intermit' or
31 -- 'unconfirm'.
32 p_message_name := null;
33 -- Validate input parameters.
34 IF(p_person_id IS NULL OR p_course_cd IS NULL) THEN
35 RETURN TRUE;
36 END IF;
37 -- Validate that IGS_PS_COURSE code is valid.
38 FOR v_adv_stnd_rec IN c_adv_stnd_v(
39 p_person_id,
40 p_course_cd,
41 p_version_number) LOOP
42 RETURN TRUE;
43 END LOOP;
44 p_message_name := 'IGS_GE_INVALID_VALUE';
45 RETURN FALSE;
46 EXCEPTION
47 WHEN OTHERS THEN
48 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
49 Fnd_Message.Set_Token('NAME','IGS_AV_VAL_AS.ADVP_VAL_AS_CRS');
50 Igs_Ge_Msg_Stack.Add;
51 App_Exception.Raise_Exception;
52 END;
53 END advp_val_as_crs;
54 --
55 -- To validate the advanced standing major exemption IGS_OR_INSTITUTION code.
56 FUNCTION advp_val_as_inst(
57 p_exempt_inst IN VARCHAR2 ,
58 p_message_name OUT NOCOPY VARCHAR2 )
59 RETURN BOOLEAN IS
60 gv_other_detail VARCHAR2(255);
61 BEGIN
62 DECLARE
63 CURSOR c_exempt_inst_v(
64 cp_exempt_inst IGS_AV_ADV_STANDING.exemption_institution_cd%TYPE) IS
65 SELECT ihp.oss_org_unit_cd exemption_institution_cd
66 FROM igs_pe_hz_parties ihp
67 where ihp.inst_org_ind = 'I'
68 AND ihp.oi_govt_institution_cd IS NOT NULL
69 AND ihp.oss_org_unit_cd = cp_exempt_inst
70 UNION ALL
71 SELECT lk.lookup_code exemption_institution_cd
72 FROM igs_lookup_values lk
73 WHERE lk.lookup_type = 'OR_INST_EXEMPTIONS'
74 AND lk.enabled_flag = 'Y'
75 AND lk.lookup_code = cp_exempt_inst;
76 BEGIN
77 -- Validate that IGS_AV_ADV_STANDING.exemption_institution_cd) is valid.
78 -- The status is not considered, as it is allowable to select an inactive
79 -- IGS_OR_INSTITUTION for advanced standing basis details.
80 p_message_name := null;
81 -- Validate input parameters.
82 IF(p_exempt_inst IS NULL) THEN
83 RETURN TRUE;
84 END IF;
85 -- Validate that exemption IGS_OR_INSTITUTION is valid.
86 FOR v_exempt_inst_rec IN c_exempt_inst_v(
87 p_exempt_inst) LOOP
88 RETURN TRUE;
89 END LOOP;
90 p_message_name := 'IGS_GE_INVALID_VALUE';
91 RETURN FALSE;
92 EXCEPTION
93 WHEN OTHERS THEN
94 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
95 Fnd_Message.Set_Token('NAME','IGS_AV_VAL_AS.ADVP_VAL_AS_INST');
96 Igs_Ge_Msg_Stack.Add;
97 App_Exception.Raise_Exception;
98 END;
99 END advp_val_as_inst;
100 END IGS_AV_VAL_AS;
[Home] [Help]