1 PACKAGE BODY IGS_AD_VAL_ASE AS
2 /* $Header: IGSAD43B.pls 115.4 2002/11/28 21:32:45 nsidana ship $ */
3 -- result_obtained_yr is not null then score and ass_type are not null.
4 FUNCTION ADMP_VAL_ASE_SCOREAT(
5 p_result_obtained_yr IN NUMBER ,
6 p_score IN NUMBER ,
7 p_aus_scndry_edu_ass_type IN VARCHAR2 ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN IS
10 gv_other_detail VARCHAR2(255);
11 BEGIN -- admp_val_ase_scoreat
12 -- Validate that if the IGS_AD_AUS_SEC_EDU.result_obtained_yr is
13 -- not null then the IGS_AD_AUS_SEC_EDU.score and
14 -- IGS_AD_AUS_SEC_EDU.aus_scndry_edu_ass_type are also not null.
15 DECLARE
16 BEGIN
17 p_message_name := Null;
18 IF p_result_obtained_yr IS NOT NULL THEN
19 IF p_score IS NULL OR
20 p_aus_scndry_edu_ass_type IS NULL THEN
21 p_message_name := 'IGS_AD_YEAR_ASSTYPE_SPECIFIED';
22 RETURN FALSE;
23 END IF;
24 END IF;
25 RETURN TRUE;
26 END;
27 EXCEPTION
28 WHEN OTHERS THEN
29 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
30 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ASE.admp_val_ase_scoreat');
31 IGS_GE_MSG_STACK.ADD;
32 App_Exception.Raise_Exception;
33 END admp_val_ase_scoreat;
34 --
35 -- Validate state_cd = ass_type.state_cd or ass_type.state_cd is null
36 FUNCTION ADMP_VAL_ASE_ATSTATE(
37 p_state_cd IN VARCHAR2 ,
38 p_aus_scndry_edu_ass_type IN VARCHAR2 ,
39 p_message_name OUT NOCOPY VARCHAR2 )
40 RETURN BOOLEAN IS
41 gv_other_detail VARCHAR2(255);
42 BEGIN -- admp_val_ase_atstate
43 -- Validate that the IGS_AD_AUS_SEC_EDU.state_cd is the same as the
44 -- IGS_AD_AUSE_ED_AS_TY.state_cd or that the
45 -- IGS_AD_AUSE_ED_AS_TY.state_cd is null.
46 DECLARE
47 v_state_cd IGS_AD_AUS_SEC_EDU.state_cd%TYPE DEFAULT NULL;
48 CURSOR c_aseat IS
49 SELECT state_cd
50 FROM IGS_AD_AUSE_ED_AS_TY
51 WHERE aus_scndry_edu_ass_type = p_aus_scndry_edu_ass_type;
52 BEGIN
53 p_message_name := Null;
54 IF p_aus_scndry_edu_ass_type IS NOT NULL THEN
55 OPEN c_aseat;
56 FETCH c_aseat INTO v_state_cd;
57 CLOSE c_aseat;
58 IF (v_state_cd <> p_state_cd) THEN
59 p_message_name := 'IGS_AD_SEC_ASSTYPE_STCD';
60 RETURN FALSE;
61 END IF;
62 END IF;
63 RETURN TRUE;
64 END;
65 EXCEPTION
66 WHEN OTHERS THEN
67 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
68 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ASE.admp_val_ase_atstate');
69 IGS_GE_MSG_STACK.ADD;
70 App_Exception.Raise_Exception;
71 END admp_val_ase_atstate;
72 --
73 -- Validate that state_cd = IGS_AD_AUS_SEC_ED_SC .state_cd
74 FUNCTION ADMP_VAL_ASE_SCSTATE(
75 p_state_cd IN VARCHAR2 ,
76 p_secondary_school_cd IN VARCHAR2 ,
77 p_message_name OUT NOCOPY VARCHAR2 )
78 RETURN BOOLEAN IS
79 gv_other_detail VARCHAR2(255);
80 BEGIN -- admp_val_ase_scstate
81 -- Validate that the IGS_AD_AUS_SEC_EDU.state_cd is the same as the
82 -- IGS_AD_AUS_SEC_ED_SC.state_cd.
83 DECLARE
84 v_state_cd IGS_AD_AUS_SEC_EDU.state_cd%TYPE DEFAULT NULL;
85 CURSOR c_ases IS
86 SELECT state_cd
87 FROM IGS_AD_AUS_SEC_ED_SC
88 WHERE secondary_school_cd = p_secondary_school_cd;
89 BEGIN
90 p_message_name := Null;
91 IF p_secondary_school_cd IS NOT NULL THEN
92 OPEN c_ases;
93 FETCH c_ases INTO v_state_cd;
94 CLOSE c_ases;
95 IF (v_state_cd <> p_state_cd) THEN
96 p_message_name := 'IGS_AD_SEC_EDU_STCD';
97 RETURN FALSE;
98 END IF;
99 END IF;
100 RETURN TRUE;
101 END;
102 EXCEPTION
103 WHEN OTHERS THEN
104 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
105 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_ASE.admp_val_ase_scstate');
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END admp_val_ase_scstate;
109 --
110 -- Validate if IGS_AD_AUSE_ED_AS_TY is closed.
111 FUNCTION ADMP_VAL_ASEATCLOSED(
112 p_aus_scndry_edu_ass_type IN VARCHAR2 ,
113 p_message_name OUT NOCOPY VARCHAR2 )
114 RETURN BOOLEAN IS
115 gv_other_detail VARCHAR2(255);
116 BEGIN -- admp_val_aseatclosed
117 -- Validate if IGS_AD_AUSE_ED_AS_TY.aus_scndry_edu_ass_type is closed.
118 DECLARE
119 v_closed_ind IGS_AD_AUSE_ED_AS_TY.closed_ind%TYPE DEFAULT NULL;
120 CURSOR c_aseat IS
121 SELECT closed_ind
122 FROM IGS_AD_AUSE_ED_AS_TY
123 WHERE aus_scndry_edu_ass_type = p_aus_scndry_edu_ass_type;
124 BEGIN
125 p_message_name := Null;
126 IF p_aus_scndry_edu_ass_type IS NOT NULL THEN
127 OPEN c_aseat;
128 FETCH c_aseat INTO v_closed_ind;
129 CLOSE c_aseat;
130 IF (v_closed_ind = 'Y') THEN
131 p_message_name := 'IGS_AD_ASSTYPE_CLOSED';
132 RETURN FALSE;
133 END IF;
134 END IF;
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_ASE.admp_val_aseatclosed');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END admp_val_aseatclosed;
144
145 END IGS_AD_VAL_ASE;