1 PACKAGE BODY IGS_PS_VAL_ATL AS
2 /* $Header: IGSPS10B.pls 115.7 2002/11/29 02:55:59 nsidana ship $
3 Change History:
4 WHO WHEN WHAT
5 ayedubat 17-MAY-2001 Added one new procedure,chk_mandatory_ref_cd
6 -- avenkatr 29-AUG-2001 Bug Id : 1956374. Removed Function "crsp_val_att_closed"
7 -------------------------------------------------------------------------------------------
8 */
9
10
11
12
13
14 -- Validate the calendar type SI_CA_S_CA_CAT = 'LOAD' and closed_ind
15 FUNCTION crsp_val_atl_cat(
16 p_cal_type IN VARCHAR2 ,
17 p_message_name OUT NOCOPY VARCHAR2 )
18 RETURN BOOLEAN AS
19 BEGIN
20 DECLARE
21 v_closed_ind IGS_CA_TYPE.closed_ind%TYPE;
22 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
23 CURSOR c_ci_scc_ct IS
24 SELECT closed_ind,
25 s_cal_cat
26 FROM IGS_CA_TYPE
27 WHERE cal_type = p_cal_type;
28 BEGIN
29 -- This module validates the clendar category and
30 -- closed indicator for an IGS_EN_ATD_TYPE_LOAD record
31 -- being inserted.
32 p_message_name := NULL;
33 OPEN c_ci_scc_ct;
34 FETCH c_ci_scc_ct into v_closed_ind,
35 v_s_cal_cat;
36 IF (c_ci_scc_ct%NOTFOUND) THEN
37 CLOSE c_ci_scc_ct;
38 p_message_name := NULL;
39 RETURN TRUE;
40 END IF;
41 IF (v_closed_ind = 'Y') THEN
42 CLOSE c_ci_scc_ct;
43 p_message_name := 'IGS_CA_CALTYPE_CLOSED';
44 RETURN FALSE;
45 END IF;
46 IF (v_s_cal_cat <> 'LOAD') THEN
47 CLOSE c_ci_scc_ct;
48 p_message_name := 'IGS_PS_CALTYPE_MUSTBE_LOADCAL';
49 RETURN FALSE;
50 END IF;
51 --- Return the default value
52 CLOSE c_ci_scc_ct;
53 p_message_name := NULL;
54 RETURN TRUE;
55 EXCEPTION
56 WHEN OTHERS THEN
57 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
58 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_010.CRSP_VAL_ATL_CAT');
59 IGS_GE_MSG_STACK.ADD;
60 App_Exception.Raise_Exception;
61 END;
62 END CRSP_VAL_ATL_CAT;
63
64
65 -- To validate the att type load ranges
66 FUNCTION crsp_val_atl_range(
67 p_attendance_type IN VARCHAR2 ,
68 p_cal_type IN VARCHAR2 ,
69 p_lower_enr_load_range IN NUMBER ,
70 p_upper_enr_load_range IN NUMBER ,
71 p_message_name OUT NOCOPY VARCHAR2 )
72 RETURN boolean AS
73 BEGIN
74 DECLARE
75 v_p_lower_enr_load_range IGS_EN_ATD_TYPE_LOAD.lower_enr_load_range%TYPE;
76 v_p_upper_enr_load_range IGS_EN_ATD_TYPE_LOAD.upper_enr_load_range%TYPE;
77 CURSOR c_lelr_uelr IS
78 SELECT lower_enr_load_range,
79 upper_enr_load_range
80 FROM IGS_EN_ATD_TYPE_LOAD
81 WHERE cal_type = p_cal_type AND
82 attendance_type <> p_attendance_type AND
83 ((p_lower_enr_load_range >= lower_enr_load_range AND
84 p_lower_enr_load_range <= upper_enr_load_range) OR
85 (p_upper_enr_load_range >= lower_enr_load_range AND
86 p_upper_enr_load_range <= upper_enr_load_range) OR
87 (p_lower_enr_load_range < lower_enr_load_range AND
88 p_upper_enr_load_range > upper_enr_load_range));
89 BEGIN
90 -- This module validates the attendance type load range for
91 -- a lower range which exceeds an upper range and
92 -- overlaps between more than one IGS_EN_ATD_TYPE_LOAD record in
93 -- the same load calendar type.
94 p_message_name := NULL;
95 IF (p_lower_enr_load_range > p_upper_enr_load_range) THEN
96 p_message_name := 'IGS_PS_LOW_ENRLOAD_BELOW';
97 RETURN FALSE;
98 END IF;
99 OPEN c_lelr_uelr;
100 FETCH c_lelr_uelr into v_p_lower_enr_load_range,
101 v_p_upper_enr_load_range;
102 IF (c_lelr_uelr%FOUND) THEN
103 p_message_name := 'IGS_PS_LOADCAL_NO_OVERLAPP';
104 CLOSE c_lelr_uelr;
105 RETURN FALSE;
106 END IF;
107 CLOSE c_lelr_uelr;
108 -- Return the default value
109 RETURN TRUE;
110 EXCEPTION
111 WHEN OTHERS THEN
112 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
113 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_010.CRSP_VAL_ATL_RANGE');
114 IGS_GE_MSG_STACK.ADD;
115 App_Exception.Raise_Exception;
116 END;
117 END CRSP_VAL_ATL_RANGE;
118
119 -- Check weather the mandatory reference type is set or not corresponding to a particular reference code type
120 FUNCTION chk_mandatory_ref_cd(
121 p_reference_type IN VARCHAR2)
122 RETURN BOOLEAN AS
123 BEGIN
124 DECLARE
125 l_mandatory_flag IGS_GE_REF_CD_TYPE.mandatory_flag%TYPE;
126 CURSOR c_mandatory_flag IS
127 SELECT mandatory_flag
128 FROM IGS_GE_REF_CD_TYPE
129 WHERE reference_cd_type = p_reference_type;
130 BEGIN
131 OPEN c_mandatory_flag;
132 FETCH c_mandatory_flag INTO l_mandatory_flag;
133 CLOSE c_mandatory_flag;
134 IF l_mandatory_flag='Y' THEN
135 RETURN TRUE;
136 ELSE
137 RETURN FALSE;
138 END IF;
139 EXCEPTION
140 WHEN OTHERS THEN
141 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
142 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_ATL.CHK_MANDATORY_REF_CD');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END;
146 END chk_mandatory_ref_cd;
147
148 END IGS_PS_VAL_ATL;
[Home] [Help]