[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_VAL_DAIO
Source
1 PACKAGE BODY IGS_CA_VAL_DAIO AS
2 /* $Header: IGSCA08B.pls 115.4 2002/11/28 22:57:59 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --avenkatr 29-AUG-2001 Bug Id : 1956374. Removed procedure "calp_val_holidat_cat"
7 -------------------------------------------------------------------------------------------
8
9 --
10 -- Validate insert of IGS_CA_DA_INST_OFST
11 FUNCTION CALP_VAL_DAIO_INS(
12 p_dt_alias IN VARCHAR2 ,
13 p_dai_sequence_number IN NUMBER ,
14 p_cal_type IN VARCHAR2 ,
15 p_ci_sequence_number IN NUMBER ,
16 p_offset_dt_alias IN VARCHAR2 ,
17 p_offset_dai_sequence_number IN NUMBER ,
18 p_offset_cal_type IN VARCHAR2 ,
19 p_offset_ci_sequence_number IN NUMBER ,
20 p_message_name OUT NOCOPY VARCHAR2 )
21 RETURN boolean AS
22 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
23 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
24 v_other_detail VARCHAR(255);
25 v_alias_value DATE;
26 v_dt_alias_status IGS_CA_STAT.s_cal_status%TYPE;
27 v_offset_dt_alias_status IGS_CA_STAT.s_cal_status%TYPE;
28 CURSOR c_dt_alias_instance_offset (
29 cp_dt_alias IGS_CA_DA_INST_OFST.dt_alias%TYPE,
30 cp_dai_sequence_number IGS_CA_DA_INST_OFST.dai_sequence_number%TYPE,
31 cp_cal_type IGS_CA_DA_INST_OFST.cal_type%TYPE,
32 cp_ci_sequence_number IGS_CA_DA_INST_OFST.ci_sequence_number%TYPE)
33 IS
34 SELECT *
35 FROM IGS_CA_DA_INST_OFST
36 WHERE dt_alias = cp_dt_alias
37 AND dai_sequence_number = cp_dai_sequence_number
38 AND cal_type = cp_cal_type
39 AND ci_sequence_number = cp_ci_sequence_number
40 AND ((offset_dt_alias <> p_offset_dt_alias)
41 OR (offset_dai_sequence_number <> p_offset_dai_sequence_number)
42 OR (offset_cal_type <> p_offset_cal_type)
43 OR (offset_ci_sequence_number <> p_offset_ci_sequence_number));
44 CURSOR c_cal_instance(cp_cal_type IGS_CA_INST.cal_type%TYPE,
45 cp_sequence_number IGS_CA_INST.sequence_number%TYPE)
46 IS
47 SELECT *
48 FROM IGS_CA_INST
49 WHERE cal_type = cp_cal_type and
50 sequence_number = cp_sequence_number;
51 CURSOR c_cal_status(cp_cal_status IGS_CA_STAT.cal_status%TYPE)
52 IS
53 SELECT *
54 FROM IGS_CA_STAT
55 WHERE cal_status = cp_cal_status;
56 FUNCTION find_daio(
57 p_org_dt_alias_inst_ofst IGS_CA_DA_INST_OFST.dt_alias%TYPE,
58 p_org_dai_seq_number IGS_CA_DA_INST_OFST.dai_sequence_number%TYPE,
59 p_org_cal_type IGS_CA_DA_INST_OFST.cal_type%TYPE,
60 p_org_ci_seq_number IGS_CA_DA_INST_OFST.ci_sequence_number%TYPE,
61 p_dt_alias IGS_CA_DA_INST_OFST.dt_alias%TYPE,
62 p_dai_seq_number IGS_CA_DA_INST_OFST.dai_sequence_number%TYPE,
63 p_cal_type IGS_CA_DA_INST_OFST.cal_type%TYPE,
64 p_ci_seq_number IGS_CA_DA_INST_OFST.ci_sequence_number%TYPE)
65 RETURN BOOLEAN AS
66 v_dt_alias_instance_offset_rec IGS_CA_DA_INST_OFST%ROWTYPE;
67 CURSOR c_dt_alias_instance_offset (
68 cp_dt_alias IGS_CA_DA_INST_OFST.dt_alias%TYPE,
69 cp_dai_sequence_number IGS_CA_DA_INST_OFST.dai_sequence_number%TYPE,
70 cp_cal_type IGS_CA_DA_INST_OFST.cal_type%TYPE,
71 cp_ci_sequence_number IGS_CA_DA_INST_OFST.ci_sequence_number%TYPE)
72 IS
73 SELECT *
74 FROM IGS_CA_DA_INST_OFST
75 WHERE dt_alias = cp_dt_alias
76 AND dai_sequence_number = cp_dai_sequence_number
77 AND cal_type = cp_cal_type
78 AND ci_sequence_number = cp_ci_sequence_number;
79 BEGIN
80 IF (c_dt_alias_instance_offset%ISOPEN = FALSE) THEN
81 OPEN c_dt_alias_instance_offset(p_dt_alias,
82 p_dai_seq_number,
83 p_cal_type,
84 p_ci_seq_number);
85 END IF;
86 LOOP
87 FETCH c_dt_alias_instance_offset
88 INTO v_dt_alias_instance_offset_rec;
89 IF (c_dt_alias_instance_offset%NOTFOUND) THEN
90 IF (c_dt_alias_instance_offset%ISOPEN) THEN
91 CLOSE c_dt_alias_instance_offset;
92 END IF;
93 RETURN TRUE;
94 END IF;
95 IF (v_dt_alias_instance_offset_rec.offset_dt_alias =
96 p_org_dt_alias_inst_ofst AND
97 v_dt_alias_instance_offset_rec.offset_dai_sequence_number =
98 p_org_dai_seq_number AND
99 v_dt_alias_instance_offset_rec.offset_cal_type = p_org_cal_type AND
100 v_dt_alias_instance_offset_rec.offset_ci_sequence_number =
101 p_org_ci_seq_number) THEN
102 IF (c_dt_alias_instance_offset%ISOPEN) THEN
103 CLOSE c_dt_alias_instance_offset;
104 END IF;
105 RETURN FALSE;
106 ELSE
107 IF (find_daio(p_org_dt_alias_inst_ofst,
108 p_org_dai_seq_number,
109 p_org_cal_type,
110 p_org_ci_seq_number,
111 v_dt_alias_instance_offset_rec.offset_dt_alias,
112 v_dt_alias_instance_offset_rec.offset_dai_sequence_number,
113 v_dt_alias_instance_offset_rec.offset_cal_type,
114 v_dt_alias_instance_offset_rec.offset_ci_sequence_number) = TRUE) THEN
115 IF (c_dt_alias_instance_offset%ISOPEN) THEN
116 CLOSE c_dt_alias_instance_offset;
117 END IF;
118 RETURN TRUE;
119 ELSE
120 IF (c_dt_alias_instance_offset%ISOPEN) THEN
121 CLOSE c_dt_alias_instance_offset;
122 END IF;
123 RETURN FALSE;
124 END IF;
125 END IF;
126 END LOOP;
127 END find_daio;
128 BEGIN
129 FOR v_dt_alias_instance_offset_rec
130 IN c_dt_alias_instance_offset(p_dt_alias,
131 p_dai_sequence_number,
132 p_cal_type,
133 p_ci_sequence_number)
134 LOOP
135 p_message_name := 'IGS_CA_DTALIAS_INST_EXISTS';
136 RETURN FALSE;
137 END LOOP;
138 IF (p_dt_alias = p_offset_dt_alias AND
139 p_dai_sequence_number = p_offset_dai_sequence_number AND
140 p_cal_type = p_offset_cal_type AND
141 p_ci_sequence_number = p_offset_ci_sequence_number) THEN
142 p_message_name := 'IGS_CA_DTALIAS_NOT_OFFSET';
143 RETURN FALSE;
144 END IF;
145 FOR v_cal_istance_rec IN c_cal_instance(p_cal_type, p_ci_sequence_number)
146 LOOP
147 FOR v_cal_status IN c_cal_status(v_cal_istance_rec.cal_status)
148 LOOP
149 v_dt_alias_status := v_cal_status.s_cal_status;
150 END LOOP;
151 END LOOP;
152 FOR v_cal_istance_rec IN c_cal_instance(p_offset_cal_type,
153 p_offset_ci_sequence_number)
154 LOOP
155 FOR v_cal_status IN c_cal_status(v_cal_istance_rec.cal_status)
156 LOOP
157 v_offset_dt_alias_status := v_cal_status.s_cal_status;
158 END LOOP;
159 END LOOP;
160 IF(v_dt_alias_status = cst_active AND
161 v_offset_dt_alias_status = cst_planned) THEN
162 p_message_name := 'IGS_CA_DTALIAS_CANNOTBE_OFF';
163 RETURN FALSE;
164 END IF;
165 IF (find_daio(p_dt_alias,
166 p_dai_sequence_number,
167 p_cal_type,
168 p_ci_sequence_number,
169 p_offset_dt_alias,
170 p_offset_dai_sequence_number,
171 p_offset_cal_type,
172 p_offset_ci_sequence_number) = FALSE) THEN
173 p_message_name := 'IGS_CA_INVALID_DTALIAS_OFF';
174 RETURN FALSE;
175 ELSE
176 p_message_name := NULL;
177 RETURN TRUE;
178 END IF;
179 EXCEPTION
180 WHEN OTHERS THEN
181 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
182 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_DAIO.calp_val_daio_ins');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END calp_val_daio_ins;
186 --
187 -- Validate if a IGS_CA_DA_INST_OFST can be deleted.
188 FUNCTION CALP_VAL_DAIO_DEL(
189 p_dt_alias IN VARCHAR2 ,
190 p_dai_sequence_number IN NUMBER ,
191 p_cal_type IN VARCHAR2 ,
192 p_ci_sequence_number IN NUMBER ,
193 p_offset_dt_alias IN VARCHAR2 ,
194 p_offset_dai_sequence_number IN NUMBER ,
195 p_offset_cal_type IN VARCHAR2 ,
196 p_offset_ci_sequence_number IN NUMBER ,
197 p_message_name OUT NOCOPY VARCHAR2 )
198 RETURN boolean AS
199 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
200 v_dt_alias_instance_rec IGS_CA_DA_INST%ROWTYPE;
201 v_other_detail VARCHAR2(255);
202 e_no_dt_alias_instance EXCEPTION;
203 CURSOR c_dt_alias_instance
204 IS
205 SELECT *
206 FROM IGS_CA_DA_INST
207 WHERE dt_alias = p_dt_alias and
208 sequence_number = p_dai_sequence_number and
209 cal_type = p_cal_type and
210 ci_sequence_number = p_ci_sequence_number;
211 CURSOR c_cal_instance(cp_cal_type IGS_CA_INST.cal_type%TYPE,
212 cp_sequence_number IGS_CA_INST.sequence_number%TYPE)
213 IS
214 SELECT *
215 FROM IGS_CA_INST
216 WHERE cal_type = cp_cal_type and
217 sequence_number = cp_sequence_number;
218 CURSOR c_cal_status(cp_cal_status IGS_CA_STAT.cal_status%TYPE)
219 IS
220 SELECT *
221 FROM IGS_CA_STAT
222 WHERE cal_status = cp_cal_status;
223 BEGIN
224 OPEN c_dt_alias_instance;
225 LOOP
226 FETCH c_dt_alias_instance
227 INTO v_dt_alias_instance_rec;
228 IF (c_dt_alias_instance%NOTFOUND) THEN
229 RAISE e_no_dt_alias_instance;
230 END IF;
231 FOR v_cal_istance_rec IN c_cal_instance(p_cal_type, p_ci_sequence_number)
232 LOOP
233 FOR v_cal_status IN c_cal_status(v_cal_istance_rec.cal_status)
234 LOOP
235 IF (v_cal_status.s_cal_status = cst_planned) THEN
236 CLOSE c_dt_alias_instance;
237 p_message_name := NULL;
238 RETURN TRUE;
239 END IF;
240 END LOOP;
241 END LOOP;
242 IF (v_dt_alias_instance_rec.absolute_val IS NULL) THEN
243 CLOSE c_dt_alias_instance;
244 p_message_name := 'IGS_CA_DTALIAS_USEDTO_DERIVE';
245 RETURN FALSE;
246 ELSE
247 CLOSE c_dt_alias_instance;
248 p_message_name := Null;
249 RETURN TRUE;
250 END IF;
251 END LOOP;
252 CLOSE c_dt_alias_instance;
253 p_message_name := NULL;
254 RETURN TRUE;
255 EXCEPTION
256 WHEN e_no_dt_alias_instance THEN
257 CLOSE c_dt_alias_instance;
258 p_message_name := NULL;
259 RETURN TRUE;
260 WHEN OTHERS THEN
261 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
262 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_DAIO.calp_val_daio_del');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END calp_val_daio_del;
266 END IGS_CA_VAL_DAIO;