1 PACKAGE BODY IGS_ST_VAL_GDHC AS
2 /* $Header: IGSST07B.pls 115.4 2002/11/29 04:11:45 nsidana ship $ */
3 --
4 -- Ensure the start and end dates don't overlap with other records.
5 FUNCTION stap_val_gdhc_ovrlp(
6 p_govt_discipline_group_cd IN VARCHAR2 ,
7 p_start_dt IN DATE ,
8 p_end_dt IN DATE ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN AS
11 BEGIN
12 DECLARE
13 CURSOR c_gdhc_rec IS
14 SELECT gdhc.start_dt,
15 gdhc.end_dt
16 FROM IGS_FI_GV_DSP_HEC_CN gdhc
17 WHERE gdhc.govt_discipline_group_cd = p_govt_discipline_group_cd AND
18 gdhc.start_dt <> p_start_dt;
19 BEGIN
20 -- this module validates the IGS_FI_GV_DSP_HEC_CN table
21 -- to ensure that for records with the same
22 -- govt_discipline_group_cd and govt_hecs_cntrbtn_band that
23 -- the data ranges don't overlap
24 --- Set the default message number
25 p_message_name := NULL;
26 -- looping through the records validating for data overlaps
27 -- do not validate against the record passed in
28 FOR v_gdhc_rec IN c_gdhc_rec LOOP
29 IF (v_gdhc_rec.end_dt IS NOT NULL) THEN
30 IF (p_start_dt BETWEEN v_gdhc_rec.start_dt AND v_gdhc_rec.end_dt) THEN
31 p_message_name := 'IGS_ST_ST_DT_BETW_ST_END_DATE';
32 RETURN FALSE;
33 END IF;
34 IF (p_end_dt IS NOT NULL) THEN
35 IF (p_end_dt BETWEEN v_gdhc_rec.start_dt AND v_gdhc_rec.end_dt) THEN
36 p_message_name := 'IGS_ST_END_DT_BETW_ST_END_DT';
37 RETURN FALSE;
38 END IF;
39 IF (p_start_dt <= v_gdhc_rec.start_dt AND
40 p_end_dt >= v_gdhc_rec.end_dt) THEN
41 p_message_name := 'IGS_ST_DT_OVERLAP_GROUP_CODE';
42 RETURN FALSE;
43 END IF;
44 ELSE -- p_end_dt IS NULL
45 IF (p_start_dt <= v_gdhc_rec.start_dt OR
46 p_start_dt <= v_gdhc_rec.end_dt) THEN
47 p_message_name := 'IGS_ST_OPEN_DT_RANGE_OVERLAPS';
48 RETURN FALSE;
49 END IF;
50 END IF;
51 ELSE
52 IF (p_start_dt >= v_gdhc_rec.start_dt OR
53 p_end_dt >= v_gdhc_rec.start_dt) THEN
54 p_message_name := 'IGS_ST_DT_OVERLAP_WITH_ST_DT';
55 RETURN FALSE;
56 END IF;
57 END IF;
58 END LOOP;
59 RETURN TRUE;
60 EXCEPTION
61 WHEN OTHERS THEN
62 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
63 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_VAL_GDHC.stap_val_gdhc_ovrlp');
64 IGS_GE_MSG_STACK.ADD;
65 App_Exception.Raise_Exception;
66 END;
67 END stap_val_gdhc_ovrlp;
68 --
69 --
70 -- Validate that only one record has an open end date.
71 FUNCTION stap_val_gdhc_open(
72 p_govt_discipline_group_cd IN VARCHAR2 ,
73 p_start_dt IN DATE ,
74 p_message_name OUT NOCOPY VARCHAR2 )
75 RETURN BOOLEAN AS
76 BEGIN
77 -- this module validates the IGS_FI_GV_DSP_HEC_CN table
78 -- to ensure that for records with the same
79 -- govt_discipline_group_cd and govt_hecs_cntrbtn_band that
80 -- only one record has a NULL end_dt
81 DECLARE
82 v_start_dt DATE;
83 CURSOR c_gdhc_rec IS
84 SELECT gdhc.start_dt
85 FROM IGS_FI_GV_DSP_HEC_CN gdhc
86 WHERE gdhc.govt_discipline_group_cd = p_govt_discipline_group_cd AND
87 gdhc.start_dt <> p_start_dt AND
88 gdhc.end_dt IS NULL;
89 BEGIN
90 -- set the default message number
91 p_message_name := NULL;
92 -- select all organisational units
93 OPEN c_gdhc_rec;
94 FETCH c_gdhc_rec INTO v_start_dt;
95 IF (c_gdhc_rec%NOTFOUND) THEN
96 CLOSE c_gdhc_rec;
97 RETURN TRUE;
98 ELSE
99 CLOSE c_gdhc_rec;
100 p_message_name := 'IGS_ST_ENTER_END_DATE';
101 RETURN FALSE;
102 END IF;
103 -- set the default return type
104 RETURN TRUE;
105 EXCEPTION
106 WHEN OTHERS THEN
107 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
108 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_VAL_GDHC.stap_val_gdhc_open');
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END;
112 END stap_val_gdhc_open;
113 --
114 -- Ensure the govt discipline group id not closed.
115 FUNCTION stap_val_gdhc_gd(
116 p_govt_discipline_group_cd IN VARCHAR2 ,
117 p_message_name OUT NOCOPY VARCHAR2 )
118 RETURN BOOLEAN AS
119 BEGIN
120 DECLARE
121 CURSOR c_gd IS
122 SELECT gd.closed_ind
123 FROM IGS_PS_GOVT_DSCP gd
124 WHERE gd.govt_discipline_group_cd = p_govt_discipline_group_cd;
125 v_closed_ind IGS_PS_GOVT_DSCP.closed_ind%TYPE;
126 BEGIN
127 --- Validate the govt_discipline_group_cd in the IGS_FI_GV_DSP_HEC_CN is
128 --- not closed.
129 --- Set the message number and return false to indicate that the government
130 --- discipline group is closed.
131 --- Set the default message number
132 p_message_name := NULL;
133 OPEN c_gd;
134 FETCH c_gd INTO v_closed_ind;
135 IF c_gd%NOTFOUND THEN
136 CLOSE c_gd;
137 RETURN TRUE;
138 END IF;
139 CLOSE c_gd;
140 IF v_closed_ind = 'Y' THEN
141 p_message_name := 'IGS_FI_GOVTDISC_GRPCD_CLOSED' ;
142 RETURN FALSE;
143 END IF;
144 --- Return the default value
145 RETURN TRUE;
146 END;
147 EXCEPTION
148 WHEN OTHERS THEN
149 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
150 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_VAL_GDHC.stap_val_gdhc_gd');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END stap_val_gdhc_gd;
154 --
155 -- Validate that end date is null or >= start date.
156 FUNCTION stap_val_gdhc_end_dt(
157 p_start_dt IN DATE ,
158 p_end_dt IN DATE ,
159 p_message_name OUT NOCOPY VARCHAR2 )
160 RETURN BOOLEAN AS
161 BEGIN
162 -- this module validates the IGS_FI_GV_DSP_HEC_CN table
163 -- to ensure that if the end_dt is not null, it is greater
164 -- than or equal to the start_dt
165 DECLARE
166 BEGIN
167 -- set the default message number
168 p_message_name := NULL;
169 IF (p_end_dt IS NOT NULL) THEN
170 -- checking if the end_dt is less
171 -- than the start_dt, and if it is,
172 -- set the message number
173 IF (p_end_dt < p_start_dt) THEN
174 p_message_name := 'IGS_GE_END_DT_GE_ST_DATE';
175 RETURN FALSE;
176 END IF;
177 END IF;
178 -- set the default return type
179 RETURN TRUE;
180 EXCEPTION
181 WHEN OTHERS THEN
182 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
183 FND_MESSAGE.SET_TOKEN('NAME','IGS_ST_VAL_GDHC.stap_val_gdhc_end_dt');
184 IGS_GE_MSG_STACK.ADD;
185 App_Exception.Raise_Exception;
186 END;
187 END stap_val_gdhc_end_dt;
188 END IGS_ST_VAL_GDHC;
[Home] [Help]