[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_GEN_005
Source
1 PACKAGE BODY IGS_PS_GEN_005 AS
2 /* $Header: IGSPS05B.pls 115.5 2002/11/29 02:54:25 nsidana ship $ */
3
4 FUNCTION CRSP_DEL_TRO_HIST(
5 p_unit_cd IN VARCHAR2 ,
6 p_version_number IN NUMBER ,
7 p_cal_type IN VARCHAR2 ,
8 p_ci_sequence_number IN NUMBER ,
9 p_location_cd IN VARCHAR2 ,
10 p_unit_class IN VARCHAR2 ,
11 p_org_unit_cd IN VARCHAR2 ,
12 p_ou_start_dt IN DATE ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 e_resource_busy_exception EXCEPTION;
16 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
17 BEGIN -- crsp_del_tro_hist
18 -- This module will delete the history records associated with a
19 -- IGS_PS_TCH_RESP_OVRD record.
20 DECLARE
21 CURSOR c_troh IS
22 SELECT Rowid,unit_cd
23 FROM IGS_PS_TCH_RSOV_HIST troh
24 WHERE troh.unit_cd = p_unit_cd AND
25 troh.version_number = p_version_number AND
26 troh.cal_type = p_cal_type AND
27 troh.ci_sequence_number = p_ci_sequence_number AND
28 troh.location_cd = p_location_cd AND
29 troh.unit_class = p_unit_class AND
30 troh.org_unit_cd = p_org_unit_cd AND
31 troh.ou_start_dt = p_ou_start_dt
32 FOR UPDATE OF unit_cd NOWAIT;
33 BEGIN
34 p_message_name := NULL;
35
36 FOR v_troh_rec IN c_troh LOOP
37 -- Delete the current record.
38
39 IGS_PS_TCH_RSOV_HIST_PKG.Delete_Row(X_ROWID => v_troh_rec.Rowid);
40
41 END LOOP;
42
43 -- If processing successful then
44 RETURN TRUE;
45 EXCEPTION
46 -- If an exception raised indicating a lock on any of the records in the
47 -- select set, then want to handle the exception by returning false and
48 -- an error message from this routine.
49 WHEN e_resource_busy_exception THEN
50 IF (c_troh%ISOPEN) THEN
51 CLOSE c_troh;
52 END IF;
53 p_message_name := 'IGS_PS_UNABLE_TO_DELETE';
54 RETURN FALSE;
55 WHEN OTHERS THEN
56 IF (c_troh%ISOPEN) THEN
57 CLOSE c_troh;
58 END IF;
59 App_Exception.Raise_Exception;
60 END;
61 EXCEPTION
62 WHEN OTHERS THEN
63 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
64 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_005.CRSP_DEL_TRO_HIST');
65 IGS_GE_MSG_STACK.ADD;
66 App_Exception.Raise_Exception;
67
68 END crsp_del_tro_hist;
69
70 FUNCTION crsp_ins_calul(
71 p_course_cd IN VARCHAR2 ,
72 p_version_number IN NUMBER ,
73 p_yr_num IN NUMBER ,
74 p_effective_start_dt IN DATE ,
75 p_message_name OUT NOCOPY VARCHAR2 )
76 RETURN BOOLEAN AS
77 BEGIN -- crsp_ins_calul
78 -- copy the IGS_PS_COURSE annual load IGS_PS_UNIT links from the most recent dated
79 -- IGS_PS_COURSE annual load to the IGS_PS_COURSE annual load passed to this
80 -- module.
81 DECLARE
82 v_effective_start_dt DATE;
83 v_unit_cd IGS_PS_ANL_LOAD_U_LN.unit_cd%TYPE;
84 v_uv_version_number IGS_PS_ANL_LOAD_U_LN.uv_version_number%TYPE;
85 v_s_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
86 v_rec_inserted_cnt NUMBER(5) DEFAULT 0;
87 CURSOR c_max_eff_start_dt IS
88 SELECT max(effective_start_dt)
89 FROM IGS_PS_ANL_LOAD
90 WHERE course_cd = p_course_cd AND
91 version_number = p_version_number AND
92 yr_num = p_yr_num AND
93 effective_start_dt < p_effective_start_dt;
94 CURSOR c_calul (cp_effective_start_dt DATE) IS
95 SELECT calul.unit_cd,
96 calul.uv_version_number,
97 us.s_unit_status
98 FROM IGS_PS_ANL_LOAD_U_LN calul,
99 IGS_PS_UNIT_VER uv,
100 IGS_PS_UNIT_STAT us
101 WHERE calul.course_cd = p_course_cd AND
102 calul.crv_version_number = p_version_number AND
103 calul.yr_num = p_yr_num AND
104 calul.effective_start_dt = cp_effective_start_dt AND
105 calul.unit_cd = uv.unit_cd AND
106 calul.uv_version_number = uv.version_number AND
107 uv.unit_status = us.unit_status;
108
109 x_rowid Varchar2(25);
110 BEGIN
111 OPEN c_max_eff_start_dt;
112 FETCH c_max_eff_start_dt INTO v_effective_start_dt;
113 CLOSE c_max_eff_start_dt;
114 IF (v_effective_start_dt IS NULL) THEN
115 p_message_name := 'IGS_PS_NOPRV_PRGANNUAL_COPY';
116 RETURN TRUE;
117 END IF;
118 v_rec_inserted_cnt := 0;
119 -- loop through all selected IGS_PS_UNIT links, insert IGS_PS_COURSE annual load passed in.
120 OPEN c_calul(v_effective_start_dt);
121 LOOP
122 FETCH c_calul INTO v_unit_cd,
123 v_uv_version_number,
124 v_s_unit_status;
125 EXIT WHEN c_calul%NOTFOUND;
126 -- Do not insert records with system status of 'INACTIVE'
127 IF (v_s_unit_status <> 'INACTIVE') THEN
128 v_rec_inserted_cnt := v_rec_inserted_cnt + 1;
129
130 IGS_PS_ANL_LOAD_U_LN_PKG.Insert_Row(
131 X_ROWID => x_rowid,
132 X_COURSE_CD => p_course_cd,
133 X_CRV_VERSION_NUMBER => p_version_number,
134 X_EFFECTIVE_START_DT => p_effective_start_dt,
135 X_YR_NUM => p_yr_num,
136 X_UV_VERSION_NUMBER => v_uv_version_number,
137 X_UNIT_CD => v_unit_cd,
138 X_MODE => 'R');
139
140 END IF;
141 END LOOP;
142 -- no IGS_PS_ANL_LOAD_U_LN record inserted
143 IF (v_rec_inserted_cnt = 0) THEN
144 p_message_name := 'IGS_PS_NOPRG_ANNUAL_LOADLINKS';
145 -- all IGS_PS_ANL_LOAD_U_LN records inserted
146 ELSIF (v_rec_inserted_cnt = c_calul%ROWCOUNT) THEN
147 p_message_name := 'IGS_PS_SUCCESS_COPY_PRGANNUAL';
148 -- some IGS_PS_ANL_LOAD_U_LN record inserted
149 ELSE
150 p_message_name := 'IGS_PS_SOME_PRGANNUAL_LOADLIN';
151 END IF;
152 CLOSE c_calul;
153 RETURN TRUE;
154 EXCEPTION
155 WHEN OTHERS THEN
156 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
157 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_005.crsp_ins_calul');
158 IGS_GE_MSG_STACK.ADD;
159 App_Exception.Raise_Exception;
160 END;
161 END crsp_ins_calul;
162
163
164 PROCEDURE CRSP_INS_TRO_HIST(
165 p_unit_cd IN VARCHAR2 ,
166 p_version_number IN NUMBER ,
167 p_cal_type IN VARCHAR2 ,
168 p_ci_sequence_number IN NUMBER ,
169 p_location_cd IN VARCHAR2 ,
170 p_unit_class IN VARCHAR2 ,
171 p_org_unit_cd IN VARCHAR2 ,
172 p_ou_start_dt IN DATE ,
173 p_new_percentage IN NUMBER ,
174 p_old_percentage IN NUMBER ,
175 p_new_update_who IN VARCHAR2 ,
176 p_old_update_who IN VARCHAR2 ,
177 p_new_update_on IN DATE ,
178 p_old_update_on IN DATE )
179 AS
180 BEGIN -- crsp_ins_tro_hist
181 -- Insert a teaching_responsibility_ovrd_hist record.
182 DECLARE
183 v_hist_start_dt IGS_PS_TCH_RSOV_HIST.hist_start_dt%TYPE;
184 v_hist_end_dt IGS_PS_TCH_RSOV_HIST.hist_end_dt%TYPE;
185 v_hist_who IGS_PS_TCH_RSOV_HIST.hist_who%TYPE;
186 l_org_id NUMBER(15);
187 x_rowid Varchar2(25);
188 BEGIN
189 IF p_new_percentage <> p_old_percentage THEN
190 v_hist_start_dt := p_old_update_on;
191 v_hist_end_dt := p_new_update_on;
192 v_hist_who := p_old_update_who;
193 l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
194 IGS_PS_TCH_RSOV_HIST_PKG.Insert_Row(
195 X_ROWID => x_rowid,
196 X_UNIT_CD => p_unit_cd,
197 X_CAL_TYPE => p_cal_type,
198 X_CI_SEQUENCE_NUMBER => p_ci_sequence_number,
199 X_VERSION_NUMBER => p_version_number,
200 X_LOCATION_CD => p_location_cd,
201 X_ORG_UNIT_CD => p_org_unit_cd,
202 X_HIST_START_DT => v_hist_start_dt,
203 X_OU_START_DT => p_ou_start_dt,
204 X_UNIT_CLASS => p_unit_class,
205 X_HIST_END_DT => v_hist_end_dt,
206 X_HIST_WHO => v_hist_who,
207 X_PERCENTAGE => p_old_percentage,
208 X_MODE => 'R',
209 X_ORG_ID => l_org_id);
210 END IF;
211 END;
212 EXCEPTION
213 WHEN OTHERS THEN
214 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
215 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_005.CRSP_INS_TRO_HIST');
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END crsp_ins_tro_hist;
219
220 PROCEDURE crsp_ins_tr_hist(
221 p_unit_cd IN VARCHAR2 ,
222 p_version_number IN NUMBER ,
223 p_org_unit_cd IN VARCHAR2 ,
224 p_ou_start_dt IN DATE ,
225 p_last_update_on IN DATE ,
226 p_update_on IN DATE ,
227 p_last_update_who IN VARCHAR2 ,
228 p_percentage IN NUMBER )
229 AS
230 CURSOR c_unit_status(
231 cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
232 cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
233 SELECT IGS_PS_UNIT_STAT.s_unit_status
234 FROM IGS_PS_UNIT_STAT,IGS_PS_UNIT_VER
235 WHERE IGS_PS_UNIT_VER.unit_cd = cp_unit_cd AND
236 IGS_PS_UNIT_VER.version_number = cp_version_number AND
237 IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;
238 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
239 v_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
240 x_rowid VARCHAR2(25);
241 l_org_id NUMBER(15);
242 BEGIN
243 OPEN c_unit_status(
244 p_unit_cd,
245 p_version_number);
246 FETCH c_unit_status INTO v_unit_status;
247 CLOSE c_unit_status;
248 IF(v_unit_status = cst_active) THEN
249
250 l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
251 IGS_PS_TCH_RESP_HIST_PKG.Insert_Row(
252 X_ROWID => x_rowid,
253 X_UNIT_CD => p_unit_cd,
254 X_VERSION_NUMBER => p_version_number,
255 X_OU_START_DT => p_ou_start_dt,
256 X_HIST_START_DT => p_last_update_on,
257 X_ORG_UNIT_CD => p_org_unit_cd,
258 X_HIST_END_DT => p_update_on,
259 X_HIST_WHO => p_last_update_who,
260 X_PERCENTAGE => p_percentage,
261 X_MODE => 'R',
262 X_ORG_ID => l_org_id);
263 END IF;
264 EXCEPTION
265 WHEN OTHERS THEN
266 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
267 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_005.crsp_ins_tr_hist');
268 IGS_GE_MSG_STACK.ADD;
269 App_Exception.Raise_Exception;
270 END crsp_ins_tr_hist;
271
272 PROCEDURE crsp_ins_ud_hist(
273 p_unit_cd IN VARCHAR2 ,
274 p_version_number IN NUMBER ,
275 p_discipline_group_cd IN VARCHAR2 ,
276 p_last_update_on IN DATE ,
277 p_update_on IN DATE ,
278 p_last_update_who IN VARCHAR2 ,
279 p_percentage IN NUMBER )
280 AS
281 CURSOR c_unit_status(
282 cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
283 cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
284 SELECT IGS_PS_UNIT_STAT.s_unit_status
285 FROM IGS_PS_UNIT_STAT,IGS_PS_UNIT_VER
286 WHERE IGS_PS_UNIT_VER.unit_cd = cp_unit_cd AND
287 IGS_PS_UNIT_VER.version_number = cp_version_number AND
288 IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;
289 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
290 v_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
291 X_rowid VARCHAR2(25);
292 l_org_id NUMBER(15);
293 BEGIN
294 OPEN c_unit_status(
295 p_unit_cd,
296 p_version_number);
297 FETCH c_unit_status INTO v_unit_status;
298 CLOSE c_unit_status;
299 IF(v_unit_status = cst_active) THEN
300
301 l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
302
303 IGS_PS_UNT_DSCP_HIST_PKG.Insert_Row(
304 X_ROWID => x_rowid,
305 X_UNIT_CD => p_unit_cd,
306 X_HIST_START_DT => p_last_update_on,
307 X_DISCIPLINE_GROUP_CD => p_discipline_group_cd,
308 X_VERSION_NUMBER => p_version_number,
309 X_HIST_END_DT => p_update_on,
310 X_HIST_WHO => p_last_update_who,
311 X_PERCENTAGE => p_percentage,
312 X_MODE => 'R',
313 X_ORG_ID => l_org_id);
314 END IF;
315 EXCEPTION
316 WHEN OTHERS THEN
317 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
318 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_005.crsp_ins_ud_hist');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 END crsp_ins_ud_hist;
322
323 PROCEDURE crsp_ins_urc_hist(
324 p_unit_cd IN VARCHAR2 ,
325 p_version_number IN NUMBER ,
326 p_reference_cd_type IN VARCHAR2 ,
327 p_reference_cd IN VARCHAR2 ,
328 p_last_update_on IN DATE ,
329 p_update_on IN DATE ,
330 p_last_update_who IN VARCHAR2 ,
331 p_description IN VARCHAR2 )
332 AS
333 CURSOR c_unit_status(
334 cp_unit_cd IGS_PS_UNIT_VER.unit_cd%TYPE,
335 cp_version_number IGS_PS_UNIT_VER.version_number%TYPE) IS
336 SELECT IGS_PS_UNIT_STAT.s_unit_status
337 FROM IGS_PS_UNIT_STAT,IGS_PS_UNIT_VER
338 WHERE IGS_PS_UNIT_VER.unit_cd = cp_unit_cd AND
339 IGS_PS_UNIT_VER.version_number = cp_version_number AND
340 IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;
341 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
342 v_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
343 x_rowid VARCHAR2(25);
344 l_org_id NUMBER(15);
345 BEGIN
346 OPEN c_unit_status(
347 p_unit_cd,
348 p_version_number);
349 FETCH c_unit_status INTO v_unit_status;
350 CLOSE c_unit_status;
351 IF(v_unit_status = cst_active) THEN
352 l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
353 IGS_PS_UNIT_REF_HIST_PKG.Insert_Row(
354 X_ROWID => x_rowid,
355 X_UNIT_CD => p_unit_cd,
356 X_HIST_START_DT => p_last_update_on,
357 X_REFERENCE_CD => p_reference_cd,
358 X_VERSION_NUMBER => p_version_number,
359 X_REFERENCE_CD_TYPE => p_reference_cd_type,
360 X_HIST_END_DT => p_update_on,
361 X_HIST_WHO => p_last_update_who,
362 X_DESCRIPTION => p_description,
363 X_MODE => 'R',
364 X_ORG_ID => l_org_id);
365 END IF;
366 EXCEPTION
367 WHEN OTHERS THEN
368 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
369 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_005.crsp_ins_urc_hist');
370 IGS_GE_MSG_STACK.ADD;
371 App_Exception.Raise_Exception;
372 END crsp_ins_urc_hist;
373
374 END IGS_PS_GEN_005;