[Home] [Help]
PACKAGE BODY: APPS.IGS_AV_STND_UNIT_LVL_PKG
Source
1 package body IGS_AV_STND_UNIT_LVL_PKG AS
2 /* $Header: IGSBI06B.pls 120.1 2005/07/06 00:46:47 appldev ship $ */
3 /***** Bug No : 1956374
4 Task : Duplicated Procedures and functions
5 PROCEDURE admp_val_as_aprvd_dt reference is changed
6 admp_val_asu_inst reference is changed
7 admp_val_expiry_dt reference is changed
8 *****/
9 l_rowid VARCHAR2(25);
10 old_references IGS_AV_STND_UNIT_LVL_ALL%RowType;
11 new_references IGS_AV_STND_UNIT_LVL_ALL%RowType;
12 PROCEDURE Set_Column_Values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2,
15 x_person_id IN NUMBER,
16 x_as_course_cd IN VARCHAR2,
17 x_as_version_number IN NUMBER,
18 x_s_adv_stnd_type IN VARCHAR2,
19 x_unit_level IN VARCHAR2,
20 x_crs_group_ind IN VARCHAR2,
21 x_exemption_institution_cd IN VARCHAR2,
22 x_s_adv_stnd_granting_status IN VARCHAR2,
23 x_credit_points IN NUMBER,
24 x_approved_dt IN DATE,
25 x_authorising_person_id IN NUMBER,
26 x_granted_dt IN DATE,
27 x_expiry_dt IN DATE,
28 x_cancelled_dt IN DATE,
29 x_revoked_dt IN DATE,
30 x_comments IN VARCHAR2,
31 X_AV_STND_UNIT_LVL_ID IN NUMBER,
32 X_CAL_TYPE IN VARCHAR2,
33 X_CI_SEQUENCE_NUMBER IN NUMBER,
34 X_INSTITUTION_CD IN VARCHAR2,
35 X_UNIT_DETAILS_ID in NUMBER,
36 X_TST_RSLT_DTLS_ID in NUMBER,
37 x_creation_date IN DATE,
38 x_created_by IN NUMBER,
39 x_last_update_date IN DATE,
40 x_last_updated_by IN NUMBER,
41 x_last_update_login IN NUMBER,
42 x_org_id IN NUMBER,
43 X_DEG_AUD_DETAIL_ID IN NUMBER,
44 X_QUAL_DETS_ID IN NUMBER,
45 X_UNIT_LEVEL_MARK IN NUMBER
46 ) AS
47
48 CURSOR cur_old_ref_values IS
49 SELECT *
50 FROM IGS_AV_STND_UNIT_LVL_ALL
51 WHERE rowid = x_rowid;
52 BEGIN
53 l_rowid := x_rowid;
54 -- Code for setting the Old and New Reference Values.
55 -- Populate Old Values.
56 Open cur_old_ref_values;
57 Fetch cur_old_ref_values INTO old_references;
58 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
59 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
60 Igs_Ge_Msg_Stack.Add;
61 Close cur_old_ref_values;
62 App_Exception.Raise_Exception;
63 Return;
64 END IF;
65 Close cur_old_ref_values;
66
67 -- Populate New Values.
68 new_references.person_id := x_person_id;
69 new_references.as_course_cd := x_as_course_cd;
70 new_references.as_version_number := x_as_version_number;
71 new_references.s_adv_stnd_type := x_s_adv_stnd_type;
72 new_references.UNIT_LEVEL := x_unit_level;
73 new_references.crs_group_ind := x_crs_group_ind;
74 new_references.exemption_institution_cd := x_exemption_institution_cd;
75 new_references.s_adv_stnd_granting_status := x_s_adv_stnd_granting_status;
76 new_references.credit_points := x_credit_points;
77 new_references.approved_dt := x_approved_dt;
78 new_references.authorising_person_id := x_authorising_person_id;
79 new_references.granted_dt := x_granted_dt;
80 new_references.expiry_dt := x_expiry_dt;
81 new_references.cancelled_dt := x_cancelled_dt;
82 new_references.revoked_dt := x_revoked_dt;
83 new_references.comments := x_comments;
84 new_references.AV_STND_UNIT_LVL_ID := X_AV_STND_UNIT_LVL_ID;
85 new_references.CAL_TYPE := x_CAL_TYPE;
86 new_references.CI_SEQUENCE_NUMBER := x_CI_SEQUENCE_NUMBER;
87 new_references.INSTITUTION_CD := x_INSTITUTION_CD;
88 new_references.UNIT_DETAILS_ID := x_UNIT_DETAILS_ID;
89 new_references.TST_RSLT_DTLS_ID := x_TST_RSLT_DTLS_ID;
90 new_references.UNIT_LEVEL_MARK := X_UNIT_LEVEL_MARK;
91
92 IF (p_action = 'UPDATE') THEN
93 new_references.creation_date := old_references.creation_date;
94 new_references.created_by := old_references.created_by;
95 ELSE
96 new_references.creation_date := x_creation_date;
97 new_references.created_by := x_created_by;
98 END IF;
99 new_references.last_update_date := x_last_update_date;
100 new_references.last_updated_by := x_last_updated_by;
101 new_references.last_update_login := x_last_update_login;
102 new_references.org_id := x_org_id;
103 new_references.DEG_AUD_DETAIL_ID := x_DEG_AUD_DETAIL_ID;
104 new_references.QUAL_DETS_ID := X_QUAL_DETS_ID;
105
106 END Set_Column_Values;
107
108 PROCEDURE BeforeRowInsertUpdateDelete1(
109 p_inserting IN BOOLEAN,
110 p_updating IN BOOLEAN,
111 p_deleting IN BOOLEAN,
112 p_adv_stnd_trans IN VARCHAR2 -- This parameter has been added for Career Impact DLD.
113 ) AS
114 v_message_name VARCHAR2(30);
115 v_person_id igs_av_stnd_unit_lvl_all.person_id%TYPE;
116 v_course_cd igs_av_stnd_unit_lvl_all.as_course_cd%TYPE;
117 v_version_number igs_av_stnd_unit_lvl_all.as_version_number%TYPE;
118 v_exemption_institution_cd igs_av_stnd_unit_lvl_all.exemption_institution_cd%TYPE;
119 BEGIN
120 -- Validate conditions on insert (these apply to the trigger only).
121 IF p_inserting THEN
122 IF new_references.s_adv_stnd_type <> 'LEVEL' THEN
123 Fnd_Message.Set_Name('IGS', 'IGS_AV_TYPE_MUSTBE_LEVEL');
124 Igs_Ge_Msg_Stack.Add;
125 App_Exception.Raise_Exception;
126 END IF;
127 IF new_references.s_adv_stnd_granting_status <> 'APPROVED' AND
128 p_adv_stnd_trans = 'N' THEN
129 Fnd_Message.Set_Name('IGS', 'IGS_AV_STATUS_MUSTBE_APPROVED');
130 Igs_Ge_Msg_Stack.Add;
131 App_Exception.Raise_Exception;
132 END IF;
133 END IF;
134 -- Validate Unit Level closed indicator.
135 IF p_inserting OR p_updating THEN
136 IF igs_av_val_asule.advp_val_ule_closed (
137 new_references.UNIT_LEVEL,
138 v_message_name) = FALSE THEN
139 Fnd_Message.Set_Name('IGS', v_message_name);
140 Igs_Ge_Msg_Stack.Add;
141 App_Exception.Raise_Exception;
142 END IF;
143 END IF;
144 -- Validate Advanced Standing Unit Level Approved Date
145 IF (new_references.approved_dt IS NOT NULL) AND
146 (p_inserting OR
147 (NVL(old_references.approved_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
148 new_references.approved_dt)) THEN
149 IF igs_av_val_asu.advp_val_as_dates (
150 new_references.approved_dt,
151 'APPROVED',
152 v_message_name,
153 p_adv_stnd_trans) = FALSE THEN
154 Fnd_Message.Set_Name('IGS', v_message_name);
155 Igs_Ge_Msg_Stack.Add;
156 App_Exception.Raise_Exception;
157 END IF;
158 END IF;
159 -- On update, the granting status cannot be set to 'Granted' from anything
160 -- other than 'Approved'.
161 IF p_updating AND
162 (new_references.s_adv_stnd_granting_status = 'GRANTED') AND
163 (old_references.s_adv_stnd_granting_status <> new_references.s_adv_stnd_granting_status) THEN
164 IF old_references.s_adv_stnd_granting_status = 'REVOKED' THEN
165 Fnd_Message.Set_Name('IGS', 'IGS_AV_CHG_REVOKED_APPROVED');
166 Igs_Ge_Msg_Stack.Add;
167 App_Exception.Raise_Exception;
168 ELSIF old_references.s_adv_stnd_granting_status = 'CANCELLED' THEN
169 Fnd_Message.Set_Name('IGS', 'IGS_AV_CHG_CANCELLED_APPROVED');
170 Igs_Ge_Msg_Stack.Add;
171 App_Exception.Raise_Exception;
172 ELSIF old_references.s_adv_stnd_granting_status = 'EXPIRED' THEN
173 Fnd_Message.Set_Name('IGS', 'IGS_AV_CHG_EXPIRED_APPROVED');
174 Igs_Ge_Msg_Stack.Add;
175 App_Exception.Raise_Exception;
176 END IF;
177 END IF;
178 -- Validate Advanced Standing Unit Level Granted Date
179 IF (new_references.granted_dt IS NOT NULL) AND
180 (p_inserting OR
181 (NVL(old_references.granted_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
182 new_references.granted_dt)) THEN
183 IF igs_av_val_asu.advp_val_as_dates (
184 new_references.granted_dt,
185 'GRANTED',
186 v_message_name,
187 p_adv_stnd_trans) = FALSE THEN
188 Fnd_Message.Set_Name('IGS', v_message_name);
189 Igs_Ge_Msg_Stack.Add;
190 App_Exception.Raise_Exception;
191 END IF;
192 END IF;
193 -- Validate expiry date is greater than current date.
194 IF (new_references.expiry_dt IS NOT NULL) AND
195 (p_inserting OR
196 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
197 new_references.expiry_dt)) THEN
198 IF igs_av_val_asu.advp_val_expiry_dt (
199 new_references.expiry_dt,
200 v_message_name,
201 p_adv_stnd_trans) = FALSE THEN
202 Fnd_Message.Set_Name('IGS', v_message_name);
203 Igs_Ge_Msg_Stack.Add;
204 App_Exception.Raise_Exception;
205 END IF;
206 END IF;
207 -- Validate Advanced Standing Unit Level Cancelled Date
208 IF (new_references.cancelled_dt IS NOT NULL) AND
209 (p_inserting OR
210 (NVL(old_references.cancelled_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
211 new_references.cancelled_dt)) THEN
212 IF igs_av_val_asu.advp_val_as_dates (
213 new_references.cancelled_dt,
214 'CANCELLED',
215 v_message_name) = FALSE THEN
216 Fnd_Message.Set_Name('IGS', v_message_name);
217 Igs_Ge_Msg_Stack.Add;
218 App_Exception.Raise_Exception;
219 END IF;
220 IF igs_av_val_asu.advp_val_as_aprvd_dt (
221 new_references.approved_dt,
222 new_references.cancelled_dt,
223 v_message_name) = FALSE THEN
224 Fnd_Message.Set_Name('IGS', v_message_name);
225 Igs_Ge_Msg_Stack.Add;
226 App_Exception.Raise_Exception;
227 END IF;
228 END IF;
229 -- Validate Advanced Standing Unit Level Revoked Date
230 IF (new_references.revoked_dt IS NOT NULL) AND
231 (p_inserting OR
232 (NVL(old_references.revoked_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
233 new_references.revoked_dt)) THEN
234 IF igs_av_val_asu.advp_val_as_dates (
235 new_references.revoked_dt,
236 'REVOKED',
237 v_message_name) = FALSE THEN
238 Fnd_Message.Set_Name('IGS', v_message_name);
239 Igs_Ge_Msg_Stack.Add;
240 App_Exception.Raise_Exception;
241 END IF;
242 IF igs_av_val_asu.advp_val_as_aprvd_dt (
243 new_references.approved_dt,
244 new_references.revoked_dt,
245 v_message_name) = FALSE THEN
246 Fnd_Message.Set_Name('IGS', v_message_name);
247 Igs_Ge_Msg_Stack.Add;
248 App_Exception.Raise_Exception;
249 END IF;
250 END IF;
251 -- Validate that related date is set for the granting status.
252 IF p_inserting OR (p_updating AND (new_references.s_adv_stnd_granting_status <>
253 old_references.s_adv_stnd_granting_status)) THEN
254 IF new_references.s_adv_stnd_granting_status = 'GRANTED' THEN
255 IF igs_av_val_asu.advp_val_status_dts (
256 'GRANTED',
257 new_references.granted_dt,
258 v_message_name,
259 p_adv_stnd_trans
260 ) = FALSE THEN
261 Fnd_Message.Set_Name('IGS', v_message_name);
262 Igs_Ge_Msg_Stack.Add;
263 App_Exception.Raise_Exception;
264 END IF;
265 ELSIF new_references.s_adv_stnd_granting_status = 'REVOKED' THEN
266 IF igs_av_val_asu.advp_val_status_dts (
267 'REVOKED',
268 new_references.revoked_dt,
269 v_message_name
270 ) = FALSE THEN
271 Fnd_Message.Set_Name('IGS', v_message_name);
272 Igs_Ge_Msg_Stack.Add;
273 App_Exception.Raise_Exception;
274 END IF;
275 ELSIF new_references.s_adv_stnd_granting_status = 'CANCELLED' THEN
276 IF igs_av_val_asu.advp_val_status_dts (
277 'CANCELLED',
278 new_references.cancelled_dt,
279 v_message_name
280 ) = FALSE THEN
281 Fnd_Message.Set_Name('IGS', v_message_name);
282 Igs_Ge_Msg_Stack.Add;
283 App_Exception.Raise_Exception;
284 END IF;
285 ELSIF new_references.s_adv_stnd_granting_status = 'EXPIRED' THEN
286 IF igs_av_val_asu.advp_val_status_dts (
287 'EXPIRED',
288 new_references.expiry_dt,
289 v_message_name
290 ) = FALSE THEN
291 Fnd_Message.Set_Name('IGS', v_message_name);
292 Igs_Ge_Msg_Stack.Add;
293 App_Exception.Raise_Exception;
294 END IF;
295 ELSIF new_references.s_adv_stnd_granting_status = 'APPROVED' THEN
296 IF igs_av_val_asu.advp_val_status_dts (
297 'APPROVED',
298 new_references.approved_dt,
299 v_message_name,
300 p_adv_stnd_trans
301 ) = FALSE THEN
302 Fnd_Message.Set_Name('IGS', v_message_name);
303 Igs_Ge_Msg_Stack.Add;
304 App_Exception.Raise_Exception;
305 END IF;
306 END IF;
307 END IF;
308 -- Validate Advanced Standing Unit Level Authorising Person Id.
309 -- Validate that the authorising person_id is valid and is a staff member.
310 -- Ignore the validation during Program Transfer
311 IF (p_adv_stnd_trans = 'N') THEN
312 IF p_inserting OR
313 (p_updating AND (new_references.authorising_person_id <> old_references.authorising_person_id)) THEN
314 IF igs_co_val_oc.genp_val_prsn_id (
315 new_references.authorising_person_id,
316 v_message_name) = FALSE THEN
317 Fnd_Message.Set_Name('IGS', v_message_name);
318 Igs_Ge_Msg_Stack.Add;
319 App_Exception.Raise_Exception;
320 END IF;
321 IF igs_ad_val_acai.genp_val_staff_prsn (
322 new_references.authorising_person_id,
323 v_message_name) = FALSE THEN
324 Fnd_Message.Set_Name('IGS', v_message_name);
325 Igs_Ge_Msg_Stack.Add;
326 App_Exception.Raise_Exception;
327 END IF;
328 END IF;
329 END IF;
330 --
331 IF p_updating AND ((new_references.s_adv_stnd_granting_status <>
332 old_references.s_adv_stnd_granting_status) and
333 (new_references.s_adv_stnd_granting_status = 'GRANTED'))
334 THEN
335 -- Validate that person is not encumbered when granting.
336 IF igs_en_val_encmb.enrp_val_excld_prsn (
337 new_references.person_id,
338 new_references.as_course_cd,
339 new_references.granted_dt,
340 v_message_name) = FALSE THEN
341 Fnd_Message.Set_Name('IGS', v_message_name);
342 Igs_Ge_Msg_Stack.Add;
343 App_Exception.Raise_Exception;
344 END IF;
345 END IF;
346 -- Validate that exemption institution code is valid.
347 IF p_inserting OR (p_updating AND (new_references.exemption_institution_cd <>
348 old_references.exemption_institution_cd)) THEN
349 IF igs_av_val_asu.advp_val_asu_inst (
350 new_references.exemption_institution_cd,
351 v_message_name) = FALSE THEN
352 Fnd_Message.Set_Name('IGS', v_message_name);
353 Igs_Ge_Msg_Stack.Add;
354 App_Exception.Raise_Exception;
355 END IF;
356 END IF;
357 -- Store column values to update Advanced Standing in AS trigger.
358 IF p_inserting OR p_updating THEN
359 v_person_id := new_references.person_id;
360 v_course_cd := new_references.as_course_cd;
361 v_version_number := new_references.as_version_number;
362 v_exemption_institution_cd := new_references.exemption_institution_cd;
363 ELSE
364 v_person_id := old_references.person_id;
365 v_course_cd := old_references.as_course_cd;
366 v_version_number := old_references.as_version_number;
367 v_exemption_institution_cd := old_references.exemption_institution_cd;
368 END IF;
369 IF igs_av_gen_001.advp_upd_as_totals (
370 v_person_id,
371 v_course_cd,
372 v_version_number,
373 v_message_name,
374 v_exemption_institution_cd) = FALSE THEN
375 Fnd_Message.Set_Name('IGS', v_message_name);
376 Igs_Ge_Msg_Stack.Add;
377 App_Exception.Raise_Exception;
378 END IF;
379 -- Process any advanced standing to do records
380 IF p_inserting THEN
381 igs_pr_gen_003.igs_pr_ins_adv_todo (
382 new_references.person_id,
383 new_references.as_course_cd,
384 new_references.as_version_number,
385 'CREDIT',
386 'CREDIT',
387 new_references.s_adv_stnd_granting_status,
388 new_references.s_adv_stnd_granting_status,
389 new_references.credit_points,
390 new_references.credit_points,
391 NULL,
392 NULL
393 );
394 ELSIF p_updating THEN
395 igs_pr_gen_003.igs_pr_ins_adv_todo (
396 new_references.person_id,
397 new_references.as_course_cd,
398 new_references.as_version_number,
399 'CREDIT',
400 'CREDIT',
401 old_references.s_adv_stnd_granting_status,
402 new_references.s_adv_stnd_granting_status,
403 old_references.credit_points,
404 new_references.credit_points,
405 NULL,
406 NULL
407 );
408 ELSIF p_deleting THEN
409 igs_pr_gen_003.igs_pr_ins_adv_todo (
410 old_references.person_id,
411 old_references.as_course_cd,
412 old_references.as_version_number,
413 'CREDIT',
414 'CREDIT',
415 old_references.s_adv_stnd_granting_status,
416 old_references.s_adv_stnd_granting_status,
417 old_references.credit_points,
418 old_references.credit_points,
419 NULL,
420 NULL
421 );
422 END IF;
423 END BeforeRowInsertUpdateDelete1;
424
425 -- Trigger description :-
426 -- "OSS_TST".trg_asule_ar_iud
427 -- AFTER INSERT OR DELETE OR UPDATE
428 -- ON IGS_AV_STND_UNIT_LVL_ALL
429 -- FOR EACH ROW
430
431 PROCEDURE AfterRowInsertUpdateDelete2(
432 p_inserting IN BOOLEAN,
433 p_updating IN BOOLEAN,
434 p_deleting IN BOOLEAN
435 ) AS
436 v_rowid_saved BOOLEAN := FALSE;
437 v_message_name varchar2(30);
438 v_person_id IGS_AV_STND_UNIT_LVL_ALL.person_id%TYPE;
439 v_course_cd IGS_AV_STND_UNIT_LVL_ALL.as_course_cd%TYPE;
440 v_version_number IGS_AV_STND_UNIT_LVL_ALL.as_version_number%TYPE;
441 BEGIN
442 -- If trigger has not been disabled, perform required processing
443 IF (p_inserting) THEN
444 IF IGS_AV_GEN_001.ADVP_UPD_AS_TOTALS (
445 new_references.person_id,
446 new_references.as_course_cd,
447 new_references.as_version_number,
448 v_message_name,
449 new_references.exemption_institution_cd) = FALSE THEN
450 Fnd_Message.Set_Name('IGS', v_message_name);
451 IGS_GE_MSG_STACK.ADD;
452 App_Exception.Raise_Exception;
453 END IF;
454 ELSE
455 IF IGS_AV_GEN_001.ADVP_UPD_AS_TOTALS (
456 old_references.person_id,
457 old_references.as_course_cd,
458 old_references.as_version_number,
459 v_message_name,
460 old_references.exemption_institution_cd) = FALSE THEN
461 Fnd_Message.Set_Name('IGS', v_message_name);
462 IGS_GE_MSG_STACK.ADD;
463 App_Exception.Raise_Exception;
464 END IF;
465 END IF;
466 END AfterRowInsertUpdateDelete2;
467
468
469 PROCEDURE Check_Constraints (
470 Column_Name IN VARCHAR2,
471 Column_Value IN VARCHAR2
472 )
473 AS
474 BEGIN
475 IF column_name is null then
476 NULL;
477 ELSIF upper(Column_name) = 'S_ADV_STND_TYPE' then
478 new_references.s_adv_stnd_type := column_value;
479 ELSIF upper(Column_name) = 'UNIT_LEVEL' then
480 new_references.UNIT_LEVEL := column_value;
481 ELSIF upper(Column_name) = 'AS_COURSE_CD' then
482 new_references.as_course_cd := column_value;
483 ELSIF upper(Column_name) = 'S_ADV_STND_GRANTING_STATUS' then
484 new_references.s_adv_stnd_granting_status := column_value;
485 ELSIF upper(Column_name) = 'CRS_GROUP_IND' then
486 new_references.crs_group_ind := column_value;
487 ELSIF upper(Column_name) = 'EXEMPTION_INSTITUTION_CD' then
488 new_references.exemption_institution_cd := column_value;
489 ELSIF upper(Column_name) = 'CRS_GROUP_IND' then
490 new_references.crs_group_ind := column_value;
491 ELSIF upper(Column_name) = 'CREDIT_POINTS' then
492 new_references.credit_points := IGS_GE_NUMBER.TO_NUM(column_value);
493 ELSIF upper(Column_name) = 'INSTITUTION_CD' then
494 new_references.institution_cd := column_value;
495 ELSIF upper(Column_name) = 'CAL_TYPE' then
496 new_references.cal_type := column_value;
497 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
498 new_references.ci_sequence_number := IGS_GE_NUMBER.TO_NUM(column_value);
499 End if;
500 IF upper(column_name) = 'S_ADV_STND_TYPE' OR
501 column_name is null Then
502 IF new_references.S_ADV_STND_TYPE <>
503 UPPER(new_references.S_ADV_STND_TYPE) Then
504 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
505 Igs_Ge_Msg_Stack.Add;
506 App_Exception.Raise_Exception;
507 END IF;
508 END IF;
509 IF upper(column_name) = 'UNIT_LEVEL' OR
510 column_name is null Then
511 IF new_references.UNIT_LEVEL <>
512 UPPER(new_references.UNIT_LEVEL) Then
513 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
514 Igs_Ge_Msg_Stack.Add;
515 App_Exception.Raise_Exception;
516 END IF;
517 END IF;
518 IF upper(column_name) = 'AS_COURSE_CD' OR
519 column_name is null Then
520 IF new_references.AS_COURSE_CD <>
521 UPPER(new_references.AS_COURSE_CD) Then
522 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
523 Igs_Ge_Msg_Stack.Add;
524 App_Exception.Raise_Exception;
525 END IF;
526 END IF;
527 IF upper(column_name) = 'S_ADV_STND_GRANTING_STATUS' OR
528 column_name is null Then
529 IF new_references.S_ADV_STND_GRANTING_STATUS <>
530 UPPER(new_references.S_ADV_STND_GRANTING_STATUS) Then
531 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
532 Igs_Ge_Msg_Stack.Add;
533 App_Exception.Raise_Exception;
534 END IF;
535 END IF;
536 IF upper(column_name) = 'CRS_GROUP_IND' OR
537 column_name is null Then
538 IF new_references.crs_group_ind <>
539 UPPER(new_references.crs_group_ind) Then
540 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
541 Igs_Ge_Msg_Stack.Add;
542 App_Exception.Raise_Exception;
543 END IF;
544 END IF;
545
546 IF upper(column_name) = 'CREDIT_POINTS' OR
547 column_name is null Then
548 -- 16-Oct-2002; kdande; Bug# 2627933
549 -- Changed the credit point limit to 999.999 from 99
550 IF new_references.credit_points < 0 OR
551 new_references.credit_points > 999.999 Then
552 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
553 Igs_Ge_Msg_Stack.Add;
554 App_Exception.Raise_Exception;
555 END IF;
556 END IF;
557
558 IF upper(column_name) = 'UNIT_LEVEL_MARK' OR
559 column_name is null Then
560 IF new_references.unit_level_mark < 0 OR
561 new_references.unit_level_mark > 100.000 Then
562 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
563 Igs_Ge_Msg_Stack.Add;
564 App_Exception.Raise_Exception;
565 END IF;
566 END IF;
567
568 IF upper(column_name) = 'CRS_GROUP_IND' OR
569 column_name is null Then
570 IF (new_references.crs_group_ind not in ('Y', 'N')) Then
571 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
572 Igs_Ge_Msg_Stack.Add;
573 App_Exception.Raise_Exception;
574 END IF;
575 END IF;
576 IF upper(column_name) = 'S_ADV_STND_TYPE' OR
577 column_name is null Then
578 IF (new_references.s_adv_stnd_type <> 'LEVEL') Then
579 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
580 Igs_Ge_Msg_Stack.Add;
581 App_Exception.Raise_Exception;
582 END IF;
583 END IF;
584
585 -- Start of addition for Bug no. 1960126
586 --Added qual_dets_id in if condition w.r.t. ARCR032 CCR
587 IF (new_references.institution_cd IS NOT NULL) AND
588 (new_references.unit_details_id IS NULL AND new_references.QUAL_DETS_ID IS NULL ) THEN
589 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
590 Igs_Ge_Msg_Stack.Add;
591 App_Exception.Raise_Exception;
592 END IF;
593
594 IF (new_references.institution_cd IS NULL AND
595 new_references.tst_rslt_dtls_id IS NULL) THEN
596 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
597 Igs_Ge_Msg_Stack.Add;
598 App_Exception.Raise_Exception;
599 END IF;
600
601 --Added qual_dets_id in if condition w.r.t. ARCR032 CCR
602 IF ((new_references.unit_details_id IS NULL AND new_references.tst_rslt_dtls_id IS NULL AND new_references.qual_dets_id IS NULL)
603 OR
604 ((new_references.unit_details_id IS NOT NULL AND new_references.tst_rslt_dtls_id IS NOT NULL) OR
605 (new_references.unit_details_id IS NOT NULL AND new_references.qual_dets_id IS NOT NULL ) OR
606 (new_references.qual_dets_id IS NOT NULL AND new_references.tst_rslt_dtls_id IS NOT NULL))
607
608 ) THEN
609 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
610 Igs_Ge_Msg_Stack.Add;
611 App_Exception.Raise_Exception;
612 END IF;
613
614
615 IF upper(column_name) = 'CAL_TYPE' OR
616 column_name is null THEN
617 IF (new_references.cal_type IS NULL) THEN
618 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
619 Igs_Ge_Msg_Stack.Add;
620 App_Exception.Raise_Exception;
621 END IF;
622 END IF;
623
624 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
625 column_name is null THEN
626 IF (new_references.CI_SEQUENCE_NUMBER IS NULL) THEN
627 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
628 Igs_Ge_Msg_Stack.Add;
629 App_Exception.Raise_Exception;
630 END IF;
631 END IF;
632 -- End of addition for Bug no. 1960126
633
634 END Check_Constraints;
635
636 PROCEDURE check_uniqueness AS
637 /*************************************************************
638 Created By : pkpatel
639 Date Created By : 13-SEP-2001
640 Purpose :
641 Know limitations, enhancements or remarks
642 Change History
643 Who When What
644
645 (reverse chronological order - newest change first)
646 ***************************************************************/
647
648 BEGIN
649 IF get_uk_for_validation (
650 new_references.person_id,
651 new_references.exemption_institution_cd,
652 new_references.unit_details_id,
653 new_references.tst_rslt_dtls_id,
654 new_references.unit_level,
655 new_references.as_course_cd,
656 new_references.as_version_number,
657 new_references.qual_dets_id,
658 new_references.s_adv_stnd_type,
659 new_references.crs_group_ind
660 ) THEN
661 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
662 IGS_GE_MSG_STACK.ADD;
663 app_exception.raise_exception;
664 END IF;
665 END Check_Uniqueness ;
666
667 PROCEDURE Check_Parent_Existance AS
668 BEGIN
669 IF (((old_references.person_id = new_references.person_id) AND
670 (old_references.as_course_cd = new_references.as_course_cd) AND
671 (old_references.as_version_number = new_references.as_version_number)) OR
672 ((new_references.person_id IS NULL) AND
673 (new_references.as_course_cd IS NULL) AND
674 (new_references.as_version_number IS NULL))) THEN
675 NULL;
676 ELSE
677 IF NOT IGS_AV_ADV_STANDING_PKG.Get_PK_For_Validation (
678 new_references.person_id,
679 new_references.as_course_cd,
680 new_references.as_version_number,
681 new_references.exemption_institution_cd
682 ) THEN
683 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
684 Igs_Ge_Msg_Stack.Add;
685 App_Exception.Raise_Exception;
686 END IF;
687 END IF;
688 --Added If condition as part of ARCR032 CCR(Enh bug no:2233334)
689 IF ( (old_references.qual_dets_id = new_references.qual_dets_id) OR
690 (new_references.qual_dets_id IS NULL) ) THEN
691 NULL;
692 ELSE
693 NULL;
694 -- check if the packag IGS_UC_QUAL_DETS_PKG is present in the user_objects
695 DECLARE
696 CURSOR c_exists IS
697 SELECT 1
698 FROM user_objects
699 WHERE object_name = 'IGS_UC_QUAL_DETS_PKG'
700 AND object_type='PACKAGE';
701 l_result BOOLEAN;
702 BEGIN
703 FOR rec_exists IN c_exists
704 LOOP
705
706 EXECUTE IMMEDIATE
707 'BEGIN IF NOT IGS_UC_QUAL_DETS_PKG.Get_PK_For_Validation(:1) THEN Fnd_Message.Set_Name (''FND'', ''FORM_RECORD_DELETED''); Igs_Ge_Msg_Stack.Add; App_Exception.Raise_Exception; END IF; END;'
708 USING
709 new_references.qual_dets_id;
710
711 END LOOP;
712 END;
713 END IF;
714
715 IF (((old_references.authorising_person_id = new_references.authorising_person_id)) OR
716 ((new_references.authorising_person_id IS NULL))) THEN
717 NULL;
718 ELSE
719 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
720 new_references.authorising_person_id
721 ) THEN
722 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
723 Igs_Ge_Msg_Stack.Add;
724 App_Exception.Raise_Exception;
725 END IF;
726 END IF;
727 IF (((old_references.s_adv_stnd_granting_status = new_references.s_adv_stnd_granting_status)) OR
728 ((new_references.s_adv_stnd_granting_status IS NULL))) THEN
729 NULL;
730 ELSE
731 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation (
732 'ADV_STND_GRANTING_STATUS',
733 new_references.s_adv_stnd_granting_status
734 ) THEN
735 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
736 Igs_Ge_Msg_Stack.Add;
737 App_Exception.Raise_Exception;
738 END IF;
739 END IF;
740 IF (((old_references.UNIT_LEVEL = new_references.UNIT_LEVEL)) OR
741 ((new_references.UNIT_LEVEL IS NULL))) THEN
742 NULL;
743 ELSE
744 IF NOT IGS_PS_UNIT_LEVEL_PKG.Get_PK_For_Validation (
745 new_references.UNIT_LEVEL
746 ) THEN
747 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
748 Igs_Ge_Msg_Stack.Add;
749 App_Exception.Raise_Exception;
750 END IF;
751 END IF;
752
753 -- Added for Bug no. 1960126
754 IF (((old_references.cal_type = new_references.cal_type) AND
755 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
756 ((new_references.cal_type IS NULL) OR
757 (new_references.ci_sequence_number IS NULL))) THEN
758 NULL;
759 ELSE
760 IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (new_references.cal_type,
761 new_references.ci_sequence_number) THEN
762 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
763 Igs_Ge_Msg_Stack.Add;
764 App_Exception.Raise_Exception;
765 END IF;
766 END IF;
767
768 END Check_Parent_Existance;
769
770 PROCEDURE Check_Child_Existance AS
771 BEGIN
772 IGS_AV_STD_ULVLBASIS_PKG.GET_FK_IGS_AV_STND_UNIT_LVL (
773 old_references.AV_STND_UNIT_LVL_ID
774 );
775 END Check_Child_Existance;
776
777 Function Get_PK_For_Validation (
778 x_av_stnd_unit_lvl_id IN NUMBER
779 ) Return Boolean
780 AS
781 CURSOR cur_rowid IS
782 SELECT rowid
783 FROM IGS_AV_STND_UNIT_LVL_ALL
784 WHERE av_stnd_unit_lvl_id = x_av_stnd_unit_lvl_id
785 FOR UPDATE NOWAIT;
786 lv_rowid cur_rowid%RowType;
787 BEGIN
788 Open cur_rowid;
789 Fetch cur_rowid INTO lv_rowid;
790 IF (cur_rowid%FOUND) THEN
791 Close cur_rowid;
792 Return (TRUE);
793 ELSE
794 Close cur_rowid;
795 Return (FALSE);
796 END IF;
797 END Get_PK_For_Validation;
798
799 FUNCTION get_uk_for_validation(
800 x_person_id IN NUMBER,
801 x_exemption_institution_cd IN VARCHAR2, /* Modified as per Bug# 2523546 */
802 x_unit_details_id IN NUMBER,
803 x_tst_rslt_dtls_id IN NUMBER,
804 x_unit_level IN VARCHAR2,
805 x_as_course_cd IN VARCHAR2,
806 x_as_version_number IN NUMBER,
807 x_qual_dets_id IN NUMBER, /* Added as per Bug# 2423651 */
808 x_s_adv_stnd_type IN VARCHAR2, /* Added as per Bug# 2523546 */
809 x_crs_group_ind IN VARCHAR2 /* Added as per Bug# 2523546 */
810 ) RETURN BOOLEAN AS
811
812 /*************************************************************
813 Created By :
814 Date Created By :
815 Purpose :
816 Know limitations, enhancements or remarks
817 Change History
818 Who When What
819 Nalin Kumar 02-Jan-2002 Modified the UK definition as per Bug# 2523546
820 (reverse chronological order - newest change first)
821 ***************************************************************/
822
823 CURSOR cur_rowid IS
824 SELECT rowid
825 FROM igs_av_stnd_unit_lvl_all
826 WHERE person_id = x_person_id AND
827 exemption_institution_cd = x_exemption_institution_cd AND
828 ((unit_details_id = x_unit_details_id ) OR (unit_details_id IS NULL AND x_unit_details_id IS NULL)) AND
829 ((tst_rslt_dtls_id = x_tst_rslt_dtls_id) OR ( tst_rslt_dtls_id IS NULL AND x_tst_rslt_dtls_id IS NULL)) AND
830 ((qual_dets_id = x_qual_dets_id) OR ( qual_dets_id IS NULL AND x_qual_dets_id IS NULL)) AND /* Added as per bug#2423651 */
831 unit_level = x_unit_level AND
832 as_course_cd = x_as_course_cd AND
833 as_version_number = x_as_version_number AND
834 s_adv_stnd_type = x_s_adv_stnd_type AND
835 crs_group_ind = x_crs_group_ind AND
836 ((l_rowid is null) or (rowid <> l_rowid));
837
838 lv_rowid cur_rowid%RowType;
839
840 BEGIN
841
842 OPEN cur_rowid;
843 FETCH cur_rowid INTO lv_rowid;
844 IF (cur_rowid%FOUND) THEN
845 CLOSE cur_rowid;
846 RETURN (true);
847 ELSE
848 CLOSE cur_rowid;
849 RETURN(false);
850 END IF;
851 END get_uk_for_validation ;
852
853
854
855 ----CHANGED **********
856
857 PROCEDURE GET_FK_IGS_AV_ADV_STANDING (
858 x_person_id IN NUMBER,
859 x_course_cd IN VARCHAR2,
860 x_version_number IN NUMBER,
861 x_exemption_institution_cd IN VARCHAR2
862 ) AS
863 CURSOR cur_rowid IS
864 SELECT rowid
865 FROM IGS_AV_STND_UNIT_LVL_ALL
866 WHERE person_id = x_person_id
867 AND as_course_cd = x_course_cd
868 AND as_version_number = x_version_number
869 AND exemption_institution_cd=x_exemption_institution_cd ;
870 lv_rowid cur_rowid%RowType;
871 BEGIN
872 Open cur_rowid;
873 Fetch cur_rowid INTO lv_rowid;
874 IF (cur_rowid%FOUND) THEN
875 Fnd_Message.Set_Name ('IGS', 'IGS_AV_ASULE_AS_FK');
876 Igs_Ge_Msg_Stack.Add;
877 Close cur_rowid;
878 App_Exception.Raise_Exception;
879 Return;
880 END IF;
881 Close cur_rowid;
882 END GET_FK_IGS_AV_ADV_STANDING;
883
884
885
886
887 PROCEDURE GET_FK_IGS_PE_PERSON (
888 x_person_id IN NUMBER
889 ) AS
890 CURSOR cur_rowid IS
891 SELECT rowid
892 FROM IGS_AV_STND_UNIT_LVL_ALL
893 WHERE authorising_person_id = x_person_id ;
894 lv_rowid cur_rowid%RowType;
895 BEGIN
896 Open cur_rowid;
897 Fetch cur_rowid INTO lv_rowid;
898 IF (cur_rowid%FOUND) THEN
899 Fnd_Message.Set_Name ('IGS', 'IGS_AV_ASULE_PE_FK');
900 Igs_Ge_Msg_Stack.Add;
901 Close cur_rowid;
902 App_Exception.Raise_Exception;
903 Return;
904 END IF;
905 Close cur_rowid;
906 END GET_FK_IGS_PE_PERSON;
907
908 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW(
909 x_s_adv_stnd_granting_status IN VARCHAR2
910 ) AS
911 CURSOR cur_rowid IS
912 SELECT rowid
913 FROM IGS_AV_STND_UNIT_LVL_ALL
914 WHERE s_adv_stnd_granting_status = x_s_adv_stnd_granting_status ;
915 lv_rowid cur_rowid%RowType;
916 BEGIN
917 Open cur_rowid;
918 Fetch cur_rowid INTO lv_rowid;
919 IF (cur_rowid%FOUND) THEN
920 Fnd_Message.Set_Name ('IGS', 'IGS_AV_ASULE_SLV_FK');
921 Igs_Ge_Msg_Stack.Add;
922 Close cur_rowid;
923 App_Exception.Raise_Exception;
924 Return;
925 END IF;
926 Close cur_rowid;
927 END GET_FK_IGS_LOOKUPS_VIEW;
928
929
930 -- Added for Bug no. 1960126
931 PROCEDURE GET_FK_IGS_CA_INST (
932 x_cal_type IN VARCHAR2,
933 x_ci_sequence_number IN NUMBER
934 ) AS
935 CURSOR cur_rowid IS
936 SELECT rowid
937 FROM IGS_AV_STND_UNIT_LVL_ALL
938 WHERE cal_type = x_cal_type
939 AND ci_sequence_number = x_ci_sequence_number ;
940 lv_rowid cur_rowid%RowType;
941 BEGIN
942 Open cur_rowid;
943 Fetch cur_rowid INTO lv_rowid;
944 IF (cur_rowid%FOUND) THEN
945 Fnd_Message.Set_Name ('IGS', 'IGS_AV_ASULE_CI_FK');
946 Igs_Ge_Msg_Stack.Add;
947 Close cur_rowid;
948 App_Exception.Raise_Exception;
949 Return;
950 END IF;
951 Close cur_rowid;
952 END GET_FK_IGS_CA_INST;
953
954 --Added get_fk_igs_uc_qual_dets procedure as part of ARCR032 CCR(Enh bug no:2233334)
955 PROCEDURE GET_FK_IGS_UC_QUAL_DETS (
956 x_qual_dets_id IN NUMBER
957 ) AS
958 CURSOR cur_rowid IS
959 SELECT rowid
960 FROM IGS_AV_STND_UNIT_LVL_ALL
961 WHERE qual_dets_id = x_qual_dets_id;
962 lv_rowid cur_rowid%RowType;
963 BEGIN
964 Open cur_rowid;
965 Fetch cur_rowid into lv_rowid;
966 IF (cur_rowid%FOUND) THEN
967 Fnd_Message.Set_Name ('IGS', 'IGS_AV_ASULE_UCQD_FK');
968 Igs_Ge_Msg_Stack.Add;
969 Close cur_rowid;
970 App_Exception.Raise_Exception;
971 Return;
972 END IF;
973 Close cur_rowid;
974 END GET_FK_IGS_UC_QUAL_DETS;
975
976 --** Added as per Bug# 2401170
977 PROCEDURE get_fk_igs_ad_term_unitdtls (
978 x_unit_details_id IN NUMBER
979 ) AS
980 CURSOR cur_rowid IS
981 SELECT rowid
982 FROM IGS_AV_STND_UNIT_LVL_ALL
983 WHERE unit_details_id = x_unit_details_id;
984 l_rowid cur_rowid%RowType;
985 BEGIN
986 Open cur_rowid;
987 Fetch cur_rowid INTO l_rowid;
988 IF (cur_rowid%FOUND) THEN
989 Fnd_Message.Set_Name ('IGS', 'IGS_AV_ASULE_TUD_FK');
990 Igs_Ge_Msg_Stack.Add;
991 Close cur_rowid;
992 App_Exception.Raise_Exception;
993 Return;
994 END IF;
995 Close cur_rowid;
996 END get_fk_igs_ad_term_unitdtls;
997
998 PROCEDURE get_fk_igs_ad_tst_rslt_dtls (
999 x_tst_rslt_dtls_id IN NUMBER
1000 ) AS
1001 CURSOR cur_rowid IS
1002 SELECT rowid
1003 FROM IGS_AV_STND_UNIT_LVL_ALL
1004 WHERE tst_rslt_dtls_id = x_tst_rslt_dtls_id;
1005 l_rowid cur_rowid%RowType;
1006 BEGIN
1007 Open cur_rowid;
1008 Fetch cur_rowid INTO l_rowid;
1009 IF (cur_rowid%FOUND) THEN
1010 Fnd_Message.Set_Name ('IGS', 'IGS_AV_ASULE_TRD_FK');
1011 Igs_Ge_Msg_Stack.Add;
1012 Close cur_rowid;
1013 App_Exception.Raise_Exception;
1014 Return;
1015 END IF;
1016 Close cur_rowid;
1017 END get_fk_igs_ad_tst_rslt_dtls;
1018 --** End of new code as per Bug# 2401170
1019
1020
1021
1022 PROCEDURE Before_DML (
1023 p_action IN VARCHAR2,
1024 x_rowid IN VARCHAR2,
1025 x_person_id IN NUMBER,
1026 x_as_course_cd IN VARCHAR2,
1027 x_as_version_number IN NUMBER,
1028 x_s_adv_stnd_type IN VARCHAR2,
1029 x_unit_level IN VARCHAR2,
1030 x_crs_group_ind IN VARCHAR2,
1031 x_exemption_institution_cd IN VARCHAR2,
1032 x_s_adv_stnd_granting_status IN VARCHAR2,
1033 x_credit_points IN NUMBER,
1034 x_approved_dt IN DATE,
1035 x_authorising_person_id IN NUMBER,
1036 x_granted_dt IN DATE,
1037 x_expiry_dt IN DATE,
1038 x_cancelled_dt IN DATE,
1039 x_revoked_dt IN DATE,
1040 x_comments IN VARCHAR2,
1041 X_AV_STND_UNIT_LVL_ID IN NUMBER ,
1042 X_CAL_TYPE IN VARCHAR2,
1043 X_CI_SEQUENCE_NUMBER IN NUMBER,
1044 X_INSTITUTION_CD IN VARCHAR2,
1045 X_UNIT_DETAILS_ID in NUMBER,
1046 X_TST_RSLT_DTLS_ID in NUMBER,
1047 x_creation_date IN DATE,
1048 x_created_by IN NUMBER,
1049 x_last_update_date IN DATE,
1050 x_last_updated_by IN NUMBER,
1051 x_last_update_login IN NUMBER,
1052 x_org_id IN NUMBER,
1053 x_adv_stnd_trans IN VARCHAR2, -- This parameter has been added for Career Impact DLD.
1054 X_DEG_AUD_DETAIL_ID IN NUMBER,
1055 X_QUAL_DETS_ID IN NUMBER,
1056 X_UNIT_LEVEL_MARK IN NUMBER
1057
1058 ) AS
1059 BEGIN
1060 Set_Column_Values (
1061 p_action,
1062 x_rowid,
1063 x_person_id,
1064 x_as_course_cd,
1065 x_as_version_number,
1066 x_s_adv_stnd_type,
1067 x_unit_level,
1068 x_crs_group_ind,
1069 x_exemption_institution_cd,
1070 x_s_adv_stnd_granting_status,
1071 x_credit_points,
1072 x_approved_dt,
1073 x_authorising_person_id,
1074 x_granted_dt,
1075 x_expiry_dt,
1076 x_cancelled_dt,
1077 x_revoked_dt,
1078 x_comments,
1079 X_AV_STND_UNIT_LVL_ID,
1080 X_CAL_TYPE,
1081 X_CI_SEQUENCE_NUMBER,
1082 X_INSTITUTION_CD,
1083 X_UNIT_DETAILS_ID,
1084 X_TST_RSLT_DTLS_ID,
1085 x_creation_date,
1086 x_created_by,
1087 x_last_update_date,
1088 x_last_updated_by,
1089 x_last_update_login,
1090 x_org_id,
1091 X_DEG_AUD_DETAIL_ID,
1092 X_QUAL_DETS_ID,
1093 X_UNIT_LEVEL_MARK
1094 );
1095 IF (p_action = 'INSERT') THEN
1096 -- Call all the procedures related to Before Insert.
1097 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
1098 p_updating => FALSE,
1099 p_deleting => FALSE,
1100 p_adv_stnd_trans => x_adv_stnd_trans);
1101 IF Get_PK_For_Validation (
1102 new_references.av_stnd_unit_lvl_id
1103 ) THEN
1104 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
1105 Igs_Ge_Msg_Stack.Add;
1106 App_Exception.Raise_Exception;
1107 END IF;
1108 check_uniqueness;
1109 Check_Constraints;
1110 Check_Parent_Existance;
1111 ELSIF (p_action = 'UPDATE') THEN
1112 -- Call all the procedures related to Before Update.
1113 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
1114 p_updating => TRUE,
1115 p_deleting => FALSE,
1116 p_adv_stnd_trans => 'N');
1117 check_uniqueness;
1118 Check_Constraints;
1119 Check_Parent_Existance;
1120 ELSIF (p_action = 'DELETE') THEN
1121 -- Call all the procedures related to Before Delete.
1122 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
1123 p_updating => FALSE,
1124 p_deleting => TRUE,
1125 p_adv_stnd_trans => 'N');
1126
1127 Check_Child_Existance;
1128 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1129 IF Get_PK_For_Validation (
1130 new_references.av_stnd_unit_lvl_id
1131 ) THEN
1132 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
1133 Igs_Ge_Msg_Stack.Add;
1134 App_Exception.Raise_Exception;
1135 END IF;
1136 check_uniqueness;
1137 Check_Constraints;
1138 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1139 check_uniqueness;
1140 Check_Constraints;
1141 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1142 Check_Child_Existance;
1143 END IF;
1144 END Before_DML;
1145
1146 PROCEDURE After_DML (
1147 p_action IN VARCHAR2,
1148 x_rowid IN VARCHAR2
1149 ) AS
1150 BEGIN
1151 l_rowid := x_rowid;
1152 IF (p_action = 'INSERT') THEN
1153 -- Call all the procedures related to After Insert.
1154 AfterRowInsertUpdateDelete2( p_inserting => TRUE,
1155 p_updating => FALSE,
1156 p_deleting => FALSE);
1157 ELSIF (p_action = 'UPDATE') THEN
1158 -- Call all the procedures related to After Update.
1159 AfterRowInsertUpdateDelete2( p_inserting => FALSE,
1160 p_updating => TRUE,
1161 p_deleting => FALSE);
1162 ELSIF (p_action = 'DELETE') THEN
1163 -- Call all the procedures related to After Delete.
1164 AfterRowInsertUpdateDelete2( p_inserting => FALSE,
1165 p_updating => FALSE,
1166 p_deleting => TRUE);
1167 END IF;
1168 END After_DML;
1169
1170 procedure INSERT_ROW (
1171 X_ROWID in out NOCOPY VARCHAR2,
1172 X_PERSON_ID in NUMBER,
1173 X_AS_COURSE_CD in VARCHAR2,
1174 X_AS_VERSION_NUMBER in NUMBER,
1175 X_S_ADV_STND_TYPE in out NOCOPY VARCHAR2,
1176 X_UNIT_LEVEL in VARCHAR2,
1177 X_CRS_GROUP_IND in out NOCOPY VARCHAR2,
1178 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
1179 X_S_ADV_STND_GRANTING_STATUS in VARCHAR2,
1180 X_CREDIT_POINTS in NUMBER,
1181 X_APPROVED_DT in DATE,
1182 X_AUTHORISING_PERSON_ID in NUMBER,
1183 X_GRANTED_DT in DATE,
1184 X_EXPIRY_DT in DATE,
1185 X_CANCELLED_DT in DATE,
1186 X_REVOKED_DT in DATE,
1187 X_COMMENTS in VARCHAR2,
1188 X_AV_STND_UNIT_LVL_ID IN OUT NOCOPY NUMBER,
1189 X_CAL_TYPE IN VARCHAR2,
1190 X_CI_SEQUENCE_NUMBER IN NUMBER,
1191 X_INSTITUTION_CD IN VARCHAR2,
1192 X_UNIT_DETAILS_ID in NUMBER,
1193 X_TST_RSLT_DTLS_ID in NUMBER,
1194 X_MODE in VARCHAR2,
1195 X_ORG_ID in NUMBER,
1196 X_ADV_STND_TRANS IN VARCHAR2, -- This parameter has been added for Career Impact DLD.
1197 X_DEG_AUD_DETAIL_ID IN NUMBER,
1198 X_QUAL_DETS_ID IN NUMBER,
1199 X_UNIT_LEVEL_MARK IN NUMBER
1200 ) AS
1201 cursor C is select ROWID from IGS_AV_STND_UNIT_LVL_ALL
1202 where av_stnd_unit_lvl_id = x_av_stnd_unit_lvl_id;
1203
1204 X_LAST_UPDATE_DATE DATE;
1205 X_LAST_UPDATED_BY NUMBER;
1206 X_LAST_UPDATE_LOGIN NUMBER;
1207 X_REQUEST_ID NUMBER ;
1208 X_PROGRAM_ID NUMBER ;
1209 X_PROGRAM_APPLICATION_ID NUMBER ;
1210 X_PROGRAM_UPDATE_DATE DATE;
1211 begin
1212 X_LAST_UPDATE_DATE := SYSDATE;
1213 if(X_MODE = 'I') then
1214 X_LAST_UPDATED_BY := 1;
1215 X_LAST_UPDATE_LOGIN := 0;
1216 elsif (X_MODE IN ('R', 'S')) then
1217 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1218 if X_LAST_UPDATED_BY is NULL then
1219 X_LAST_UPDATED_BY := -1;
1220 end if;
1221 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1222 if X_LAST_UPDATE_LOGIN is NULL then
1223 X_LAST_UPDATE_LOGIN := -1;
1224 end if;
1225 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID ;
1226 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID ;
1227 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID ;
1228 if (X_REQUEST_ID = -1) then
1229 X_REQUEST_ID := NULL ;
1230 X_PROGRAM_ID := NULL ;
1231 X_PROGRAM_APPLICATION_ID := NULL;
1232 X_PROGRAM_UPDATE_DATE := NULL;
1233 else
1234 X_PROGRAM_UPDATE_DATE := SYSDATE ;
1235 end if ;
1236 else
1237 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1238 Igs_Ge_Msg_Stack.Add;
1239 app_exception.raise_exception;
1240 end if;
1241 select IGS_AV_STND_UNIT_LVL_S.NEXTVAL INTO x_av_stnd_unit_lvl_id FROM DUAL;
1242 Before_DML(
1243 p_action=>'INSERT',
1244 x_rowid=>X_ROWID,
1245 x_approved_dt=>X_APPROVED_DT,
1246 x_as_course_cd=>X_AS_COURSE_CD,
1247 x_as_version_number=>X_AS_VERSION_NUMBER,
1248 x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
1249 x_cancelled_dt=>X_CANCELLED_DT,
1250 x_comments=>X_COMMENTS,
1251 x_credit_points=>X_CREDIT_POINTS,
1252 x_crs_group_ind=>NVL(X_CRS_GROUP_IND,'N'),
1253 x_exemption_institution_cd=>X_EXEMPTION_INSTITUTION_CD,
1254 x_expiry_dt=>X_EXPIRY_DT,
1255 x_granted_dt=>X_GRANTED_DT,
1256 x_person_id=>X_PERSON_ID,
1257 x_revoked_dt=>X_REVOKED_DT,
1258 x_s_adv_stnd_granting_status=>X_S_ADV_STND_GRANTING_STATUS,
1259 x_s_adv_stnd_type=>NVL(X_S_ADV_STND_TYPE,'LEVEL'),
1260 x_unit_level=>X_UNIT_LEVEL,
1261 X_AV_STND_UNIT_LVL_ID=>X_AV_STND_UNIT_LVL_ID,
1262 X_CAL_TYPE =>X_CAL_TYPE,
1263 X_CI_SEQUENCE_NUMBER =>X_CI_SEQUENCE_NUMBER,
1264 X_INSTITUTION_CD =>X_INSTITUTION_CD,
1265 X_UNIT_DETAILS_ID =>X_UNIT_DETAILS_ID,
1266 X_TST_RSLT_DTLS_ID =>X_TST_RSLT_DTLS_ID,
1267 x_creation_date=>X_LAST_UPDATE_DATE,
1268 x_created_by=>X_LAST_UPDATED_BY,
1269 x_last_update_date=>X_LAST_UPDATE_DATE,
1270 x_last_updated_by=>X_LAST_UPDATED_BY,
1271 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1272 x_org_id=>igs_ge_gen_003.get_org_id,
1273 x_adv_stnd_trans=>X_ADV_STND_TRANS,
1274 X_DEG_AUD_DETAIL_ID => X_DEG_AUD_DETAIL_ID,
1275 X_QUAL_DETS_ID => X_QUAL_DETS_ID,
1276 X_UNIT_LEVEL_MARK => X_UNIT_LEVEL_MARK
1277 );
1278
1279 IF (x_mode = 'S') THEN
1280 igs_sc_gen_001.set_ctx('R');
1281 END IF;
1282 insert into IGS_AV_STND_UNIT_LVL_ALL (
1283 PERSON_ID,
1284 AS_COURSE_CD,
1285 AS_VERSION_NUMBER,
1286 S_ADV_STND_TYPE,
1287 UNIT_LEVEL,
1288 CRS_GROUP_IND,
1289 EXEMPTION_INSTITUTION_CD,
1290 S_ADV_STND_GRANTING_STATUS,
1291 CREDIT_POINTS,
1292 APPROVED_DT,
1293 AUTHORISING_PERSON_ID,
1294 GRANTED_DT,
1295 EXPIRY_DT,
1296 CANCELLED_DT,
1297 REVOKED_DT,
1298 COMMENTS,
1299 AV_STND_UNIT_LVL_ID,
1300 CAL_TYPE,
1301 CI_SEQUENCE_NUMBER,
1302 INSTITUTION_CD,
1303 UNIT_DETAILS_ID,
1304 TST_RSLT_DTLS_ID,
1305 CREATION_DATE,
1306 CREATED_BY,
1307 LAST_UPDATE_DATE,
1308 LAST_UPDATED_BY,
1309 LAST_UPDATE_LOGIN,
1310 REQUEST_ID,
1311 PROGRAM_ID,
1312 PROGRAM_APPLICATION_ID,
1313 PROGRAM_UPDATE_DATE,
1314 ORG_ID,
1315 DEG_AUD_DETAIL_ID,
1316 QUAL_DETS_ID,
1317 UNIT_LEVEL_MARK
1318 ) values (
1319 NEW_REFERENCES.PERSON_ID,
1320 NEW_REFERENCES.AS_COURSE_CD,
1321 NEW_REFERENCES.AS_VERSION_NUMBER,
1322 NEW_REFERENCES.S_ADV_STND_TYPE,
1323 NEW_REFERENCES.UNIT_LEVEL,
1324 NEW_REFERENCES.CRS_GROUP_IND,
1325 NEW_REFERENCES.EXEMPTION_INSTITUTION_CD,
1326 NEW_REFERENCES.S_ADV_STND_GRANTING_STATUS,
1327 NEW_REFERENCES.CREDIT_POINTS,
1328 NEW_REFERENCES.APPROVED_DT,
1329 NEW_REFERENCES.AUTHORISING_PERSON_ID,
1330 NEW_REFERENCES.GRANTED_DT,
1331 NEW_REFERENCES.EXPIRY_DT,
1332 NEW_REFERENCES.CANCELLED_DT,
1333 NEW_REFERENCES.REVOKED_DT,
1334 NEW_REFERENCES.COMMENTS,
1335 NEW_REFERENCES.AV_STND_UNIT_LVL_ID,
1336 NEW_REFERENCES.CAL_TYPE,
1337 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
1338 NEW_REFERENCES.INSTITUTION_CD,
1339 NEW_REFERENCES.UNIT_DETAILS_ID,
1340 NEW_REFERENCES.TST_RSLT_DTLS_ID,
1341 X_LAST_UPDATE_DATE,
1342 X_LAST_UPDATED_BY,
1343 X_LAST_UPDATE_DATE,
1344 X_LAST_UPDATED_BY,
1345 X_LAST_UPDATE_LOGIN,
1346 X_REQUEST_ID,
1347 X_PROGRAM_ID,
1348 X_PROGRAM_APPLICATION_ID,
1349 X_PROGRAM_UPDATE_DATE,
1350 NEW_REFERENCES.ORG_ID,
1351 NEW_REFERENCES.DEG_AUD_DETAIL_ID,
1352 NEW_REFERENCES.QUAL_DETS_ID,
1353 NEW_REFERENCES.UNIT_LEVEL_MARK
1354 );
1355 IF (x_mode = 'S') THEN
1356 igs_sc_gen_001.unset_ctx('R');
1357 END IF;
1358
1359
1360 open c;
1361 fetch c into X_ROWID;
1362 if (c%notfound) then
1363 close c;
1364 raise no_data_found;
1365 end if;
1366 close c;
1367 After_DML(
1368 p_action => 'INSERT',
1369 x_rowid => X_ROWID
1370 );
1371 EXCEPTION
1372 WHEN OTHERS THEN
1373 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1374 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1375 fnd_message.set_token ('ERR_CD', SQLCODE);
1376 igs_ge_msg_stack.add;
1377 igs_sc_gen_001.unset_ctx('R');
1378 app_exception.raise_exception;
1379 ELSE
1380 igs_sc_gen_001.unset_ctx('R');
1381 RAISE;
1382 END IF;
1383
1384 end INSERT_ROW;
1385
1386 procedure LOCK_ROW (
1387 X_ROWID in VARCHAR2,
1388 X_PERSON_ID in NUMBER,
1389 X_AS_COURSE_CD in VARCHAR2,
1390 X_AS_VERSION_NUMBER in NUMBER,
1391 X_S_ADV_STND_TYPE in VARCHAR2,
1392 X_UNIT_LEVEL in VARCHAR2,
1393 X_CRS_GROUP_IND in VARCHAR2,
1394 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
1395 X_S_ADV_STND_GRANTING_STATUS in VARCHAR2,
1396 X_CREDIT_POINTS in NUMBER,
1397 X_APPROVED_DT in DATE,
1398 X_AUTHORISING_PERSON_ID in NUMBER,
1399 X_GRANTED_DT in DATE,
1400 X_EXPIRY_DT in DATE,
1401 X_CANCELLED_DT in DATE,
1402 X_REVOKED_DT in DATE,
1403 X_COMMENTS in VARCHAR2,
1404 X_AV_STND_UNIT_LVL_ID IN NUMBER,
1405 X_CAL_TYPE IN VARCHAR2,
1406 X_CI_SEQUENCE_NUMBER IN NUMBER,
1407 X_INSTITUTION_CD IN VARCHAR2,
1408 X_UNIT_DETAILS_ID in NUMBER,
1409 X_TST_RSLT_DTLS_ID in NUMBER,
1410 X_DEG_AUD_DETAIL_ID IN NUMBER,
1411 X_QUAL_DETS_ID IN NUMBER,
1412 X_UNIT_LEVEL_MARK IN NUMBER
1413 ) AS
1414 cursor c1 is select
1415 S_ADV_STND_GRANTING_STATUS,
1416 CREDIT_POINTS,
1417 APPROVED_DT,
1418 AUTHORISING_PERSON_ID,
1419 GRANTED_DT,
1420 EXPIRY_DT,
1421 CANCELLED_DT,
1422 REVOKED_DT,
1423 COMMENTS,
1424 AV_STND_UNIT_LVL_ID,
1425 CAL_TYPE,
1426 CI_SEQUENCE_NUMBER,
1427 INSTITUTION_CD,
1428 UNIT_DETAILS_ID,
1429 TST_RSLT_DTLS_ID,
1430 DEG_AUD_DETAIL_ID,
1431 QUAL_DETS_ID,
1432 UNIT_LEVEL_MARK
1433 from IGS_AV_STND_UNIT_LVL_ALL
1434 where ROWID = X_ROWID
1435 for update nowait;
1436 tlinfo c1%rowtype;
1437 begin
1438 open c1;
1439 fetch c1 into tlinfo;
1440 if (c1%notfound) then
1441 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1442 Igs_Ge_Msg_Stack.Add;
1443 close c1;
1444 app_exception.raise_exception;
1445 return;
1446 end if;
1447 close c1;
1448 if ( (tlinfo.S_ADV_STND_GRANTING_STATUS = X_S_ADV_STND_GRANTING_STATUS)
1449 AND (tlinfo.CREDIT_POINTS = X_CREDIT_POINTS)
1450 AND (TRUNC(tlinfo.APPROVED_DT) = TRUNC(X_APPROVED_DT))
1451 AND (tlinfo.AUTHORISING_PERSON_ID = X_AUTHORISING_PERSON_ID)
1452 AND ((TRUNC(tlinfo.GRANTED_DT) = TRUNC(X_GRANTED_DT)) -- Added 'Trunc' to fix bug# 2344136.
1453 OR ((tlinfo.GRANTED_DT is null)
1454 AND (X_GRANTED_DT is null)))
1455 AND ((TRUNC(tlinfo.EXPIRY_DT) = TRUNC(X_EXPIRY_DT))
1456 OR ((tlinfo.EXPIRY_DT is null)
1457 AND (X_EXPIRY_DT is null)))
1458 AND ((TRUNC(tlinfo.CANCELLED_DT) = TRUNC(X_CANCELLED_DT))
1459 OR ((tlinfo.CANCELLED_DT is null)
1460 AND (X_CANCELLED_DT is null)))
1461 AND ((TRUNC(tlinfo.REVOKED_DT) = TRUNC(X_REVOKED_DT))
1462 OR ((tlinfo.REVOKED_DT is null)
1463 AND (X_REVOKED_DT is null)))
1464 AND ((tlinfo.COMMENTS = X_COMMENTS)
1465 OR ((tlinfo.COMMENTS is null)
1466 AND (X_COMMENTS is null)))
1467 AND ((tlinfo.CAL_TYPE = X_CAL_TYPE)
1468 OR ((tlinfo.CAL_TYPE is null)
1469 AND (X_CAL_TYPE is null)))
1470 AND ((tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
1471 OR ((tlinfo.CI_SEQUENCE_NUMBER is null)
1472 AND (X_CI_SEQUENCE_NUMBER is null)))
1473 AND ((tlinfo.INSTITUTION_CD = X_INSTITUTION_CD)
1474 OR ((tlinfo.INSTITUTION_CD is null)
1475 AND (X_INSTITUTION_CD is null)))
1476 AND ((tlinfo.UNIT_DETAILS_ID = X_UNIT_DETAILS_ID)
1477 OR ((tlinfo.UNIT_DETAILS_ID is null)
1478 AND (X_UNIT_DETAILS_ID is null)))
1479 AND ((tlinfo.TST_RSLT_DTLS_ID = X_TST_RSLT_DTLS_ID)
1480 OR ((tlinfo.TST_RSLT_DTLS_ID is null)
1481 AND (X_TST_RSLT_DTLS_ID is null)))
1482 AND ((tlinfo.DEG_AUD_DETAIL_ID = X_DEG_AUD_DETAIL_ID)
1483 OR ((tlinfo.DEG_AUD_DETAIL_ID is null)
1484 AND (X_DEG_AUD_DETAIL_ID is null)))
1485 AND ((tlinfo.QUAL_DETS_ID = X_QUAL_DETS_ID)
1486 OR ((tlinfo.QUAL_DETS_ID is null)
1487 AND (X_QUAL_DETS_ID is null)))
1488 AND ((tlinfo.UNIT_LEVEL_MARK = X_UNIT_LEVEL_MARK)
1489 OR ((tlinfo.UNIT_LEVEL_MARK is null)
1490 AND (X_UNIT_LEVEL_MARK is null)))
1491 ) then
1492 null;
1493 else
1494 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1495 Igs_Ge_Msg_Stack.Add;
1496 app_exception.raise_exception;
1497 end if;
1498 return;
1499 end LOCK_ROW;
1500
1501 procedure UPDATE_ROW (
1502 X_ROWID in VARCHAR2,
1503 X_PERSON_ID in NUMBER,
1504 X_AS_COURSE_CD in VARCHAR2,
1505 X_AS_VERSION_NUMBER in NUMBER,
1506 X_S_ADV_STND_TYPE in VARCHAR2,
1507 X_UNIT_LEVEL in VARCHAR2,
1508 X_CRS_GROUP_IND in VARCHAR2,
1509 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
1510 X_S_ADV_STND_GRANTING_STATUS in VARCHAR2,
1511 X_CREDIT_POINTS in NUMBER,
1512 X_APPROVED_DT in DATE,
1513 X_AUTHORISING_PERSON_ID in NUMBER,
1514 X_GRANTED_DT in DATE,
1515 X_EXPIRY_DT in DATE,
1516 X_CANCELLED_DT in DATE,
1517 X_REVOKED_DT in DATE,
1518 X_COMMENTS in VARCHAR2,
1519 X_AV_STND_UNIT_LVL_ID IN NUMBER,
1520 X_CAL_TYPE IN VARCHAR2,
1521 X_CI_SEQUENCE_NUMBER IN NUMBER,
1522 X_INSTITUTION_CD IN VARCHAR2,
1523 X_UNIT_DETAILS_ID in NUMBER,
1524 X_TST_RSLT_DTLS_ID in NUMBER,
1525 X_DEG_AUD_DETAIL_ID IN NUMBER,
1526 X_MODE in VARCHAR2,
1527 X_QUAL_DETS_ID IN NUMBER,
1528 X_UNIT_LEVEL_MARK IN NUMBER
1529 ) AS
1530
1531 X_LAST_UPDATE_DATE DATE;
1532 X_LAST_UPDATED_BY NUMBER;
1533 X_LAST_UPDATE_LOGIN NUMBER;
1534 X_REQUEST_ID NUMBER;
1535 X_PROGRAM_ID NUMBER ;
1536 X_PROGRAM_APPLICATION_ID NUMBER ;
1537 X_PROGRAM_UPDATE_DATE DATE;
1538 begin
1539 X_LAST_UPDATE_DATE := SYSDATE;
1540 if(X_MODE = 'I') then
1541 X_LAST_UPDATED_BY := 1;
1542 X_LAST_UPDATE_LOGIN := 0;
1543 elsif (X_MODE IN ('R', 'S')) then
1544 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1545 if X_LAST_UPDATED_BY is NULL then
1546 X_LAST_UPDATED_BY := -1;
1547 end if;
1548 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1549 if X_LAST_UPDATE_LOGIN is NULL then
1550 X_LAST_UPDATE_LOGIN := -1;
1551 end if;
1552 else
1553 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1554 Igs_Ge_Msg_Stack.Add;
1555 app_exception.raise_exception;
1556 end if;
1557
1558 Before_DML(
1559 p_action=>'UPDATE',
1560 x_rowid=>X_ROWID,
1561 x_approved_dt=>X_APPROVED_DT,
1562 x_as_course_cd=>X_AS_COURSE_CD,
1563 x_as_version_number=>X_AS_VERSION_NUMBER,
1564 x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
1565 x_cancelled_dt=>X_CANCELLED_DT,
1566 x_comments=>X_COMMENTS,
1567 x_credit_points=>X_CREDIT_POINTS,
1568 x_crs_group_ind=>X_CRS_GROUP_IND,
1569 x_exemption_institution_cd=>X_EXEMPTION_INSTITUTION_CD,
1570 x_expiry_dt=>X_EXPIRY_DT,
1571 x_granted_dt=>X_GRANTED_DT,
1572 x_person_id=>X_PERSON_ID,
1573 x_revoked_dt=>X_REVOKED_DT,
1574 x_s_adv_stnd_granting_status=>X_S_ADV_STND_GRANTING_STATUS,
1575 x_s_adv_stnd_type=>X_S_ADV_STND_TYPE,
1576 x_unit_level=>X_UNIT_LEVEL,
1577 X_AV_STND_UNIT_LVL_ID => X_AV_STND_UNIT_LVL_ID,
1578 X_CAL_TYPE =>X_CAL_TYPE,
1579 X_CI_SEQUENCE_NUMBER =>X_CI_SEQUENCE_NUMBER,
1580 X_INSTITUTION_CD =>X_INSTITUTION_CD,
1581 X_UNIT_DETAILS_ID =>X_UNIT_DETAILS_ID,
1582 X_TST_RSLT_DTLS_ID =>X_TST_RSLT_DTLS_ID,
1583 X_DEG_AUD_DETAIL_ID => X_DEG_AUD_DETAIL_ID ,
1584 x_creation_date=>X_LAST_UPDATE_DATE,
1585 x_created_by=>X_LAST_UPDATED_BY,
1586 x_last_update_date=>X_LAST_UPDATE_DATE,
1587 x_last_updated_by=>X_LAST_UPDATED_BY,
1588 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1589 X_QUAL_DETS_ID => X_QUAL_DETS_ID,
1590 X_UNIT_LEVEL_MARK => X_UNIT_LEVEL_MARK
1591 );
1592 if (X_MODE IN ('R', 'S')) then
1593 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID ;
1594 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID ;
1595 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID ;
1596 if (X_REQUEST_ID = -1) then
1597 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID ;
1598 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID ;
1599 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID ;
1600 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE ;
1601 else
1602 X_PROGRAM_UPDATE_DATE := SYSDATE ;
1603 end if ;
1604 end if ;
1605
1606 IF (x_mode = 'S') THEN
1607 igs_sc_gen_001.set_ctx('R');
1608 END IF;
1609 UPDATE IGS_AV_STND_UNIT_LVL_ALL SET
1610 S_ADV_STND_GRANTING_STATUS = NEW_REFERENCES.S_ADV_STND_GRANTING_STATUS,
1611 CREDIT_POINTS = NEW_REFERENCES.CREDIT_POINTS,
1612 APPROVED_DT = NEW_REFERENCES.APPROVED_DT,
1613 AUTHORISING_PERSON_ID = NEW_REFERENCES.AUTHORISING_PERSON_ID,
1614 GRANTED_DT = NEW_REFERENCES.GRANTED_DT,
1615 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
1616 CANCELLED_DT = NEW_REFERENCES.CANCELLED_DT,
1617 REVOKED_DT = NEW_REFERENCES.REVOKED_DT,
1618 COMMENTS = NEW_REFERENCES.COMMENTS,
1619 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
1620 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
1621 INSTITUTION_CD = NEW_REFERENCES.INSTITUTION_CD,
1622 UNIT_DETAILS_ID = NEW_REFERENCES.UNIT_DETAILS_ID,
1623 TST_RSLT_DTLS_ID = NEW_REFERENCES.TST_RSLT_DTLS_ID,
1624 DEG_AUD_DETAIL_ID = NEW_REFERENCES.DEG_AUD_DETAIL_ID ,
1625 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1626 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1627 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1628 REQUEST_ID = X_REQUEST_ID,
1629 PROGRAM_ID = X_PROGRAM_ID,
1630 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1631 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
1632 QUAL_DETS_ID = X_QUAL_DETS_ID,
1633 UNIT_LEVEL_MARK = X_UNIT_LEVEL_MARK
1634 where rowid = X_ROWID ;
1635 if (sql%notfound) then
1636 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1637 igs_ge_msg_stack.add;
1638 igs_sc_gen_001.unset_ctx('R');
1639 app_exception.raise_exception;
1640 end if;
1641 IF (x_mode = 'S') THEN
1642 igs_sc_gen_001.unset_ctx('R');
1643 END IF;
1644
1645 After_DML(
1646 p_action => 'UPDATE',
1647 x_rowid => X_ROWID
1648 );
1649 EXCEPTION
1650 WHEN OTHERS THEN
1651 IF (SQLCODE = (-28115)) THEN
1652 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1653 fnd_message.set_token ('ERR_CD', SQLCODE);
1654 igs_ge_msg_stack.add;
1655 igs_sc_gen_001.unset_ctx('R');
1656 app_exception.raise_exception;
1657 ELSE
1658 igs_sc_gen_001.unset_ctx('R');
1659 RAISE;
1660 END IF;
1661 end UPDATE_ROW;
1662
1663
1664 procedure ADD_ROW (
1665 X_ROWID in out NOCOPY VARCHAR2,
1666 X_PERSON_ID in NUMBER,
1667 X_AS_COURSE_CD in VARCHAR2,
1668 X_AS_VERSION_NUMBER in NUMBER,
1669 X_S_ADV_STND_TYPE in out NOCOPY VARCHAR2,
1670 X_UNIT_LEVEL in VARCHAR2,
1671 X_CRS_GROUP_IND in out NOCOPY VARCHAR2,
1672 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
1673 X_S_ADV_STND_GRANTING_STATUS in VARCHAR2,
1674 X_CREDIT_POINTS in NUMBER,
1675 X_APPROVED_DT in DATE,
1676 X_AUTHORISING_PERSON_ID in NUMBER,
1677 X_GRANTED_DT in DATE,
1678 X_EXPIRY_DT in DATE,
1679 X_CANCELLED_DT in DATE,
1680 X_REVOKED_DT in DATE,
1681 X_COMMENTS in VARCHAR2,
1682 X_AV_STND_UNIT_LVL_ID IN OUT NOCOPY NUMBER,
1683 X_CAL_TYPE IN VARCHAR2,
1684 X_CI_SEQUENCE_NUMBER IN NUMBER,
1685 X_INSTITUTION_CD IN VARCHAR2,
1686 X_UNIT_DETAILS_ID in NUMBER,
1687 X_TST_RSLT_DTLS_ID in NUMBER,
1688 X_MODE in VARCHAR2,
1689 X_ORG_ID in NUMBER,
1690 X_DEG_AUD_DETAIL_ID IN NUMBER,
1691 X_QUAL_DETS_ID IN NUMBER,
1692 X_UNIT_LEVEL_MARK IN NUMBER
1693 ) AS
1694 cursor c1 is select rowid from IGS_AV_STND_UNIT_LVL_ALL
1695 where AV_STND_UNIT_LVL_ID =X_AV_STND_UNIT_LVL_ID;
1696 begin
1697 open c1;
1698 fetch c1 into X_ROWID ;
1699 if (c1%notfound) then
1700 close c1;
1701 INSERT_ROW (
1702 X_ROWID,
1703 X_PERSON_ID,
1704 X_AS_COURSE_CD,
1705 X_AS_VERSION_NUMBER,
1706 X_S_ADV_STND_TYPE,
1707 X_UNIT_LEVEL,
1708 X_CRS_GROUP_IND,
1709 X_EXEMPTION_INSTITUTION_CD,
1710 X_S_ADV_STND_GRANTING_STATUS,
1711 X_CREDIT_POINTS,
1712 X_APPROVED_DT,
1713 X_AUTHORISING_PERSON_ID,
1714 X_GRANTED_DT,
1715 X_EXPIRY_DT,
1716 X_CANCELLED_DT,
1717 X_REVOKED_DT,
1718 X_COMMENTS,
1719 X_AV_STND_UNIT_LVL_ID,
1720 X_CAL_TYPE,
1721 X_CI_SEQUENCE_NUMBER,
1722 X_INSTITUTION_CD,
1723 X_UNIT_DETAILS_ID,
1724 X_TST_RSLT_DTLS_ID,
1725 X_MODE,
1726 X_ORG_ID,
1727 X_DEG_AUD_DETAIL_ID,
1728 X_QUAL_DETS_ID,
1729 X_UNIT_LEVEL_MARK
1730 );
1731 return;
1732 end if;
1733 close c1;
1734
1735 UPDATE_ROW (
1736 X_ROWID,
1737 X_PERSON_ID,
1738 X_AS_COURSE_CD,
1739 X_AS_VERSION_NUMBER,
1740 X_S_ADV_STND_TYPE,
1741 X_UNIT_LEVEL,
1742 X_CRS_GROUP_IND,
1743 X_EXEMPTION_INSTITUTION_CD,
1744 X_S_ADV_STND_GRANTING_STATUS,
1745 X_CREDIT_POINTS,
1746 X_APPROVED_DT,
1747 X_AUTHORISING_PERSON_ID,
1748 X_GRANTED_DT,
1749 X_EXPIRY_DT,
1750 X_CANCELLED_DT,
1751 X_REVOKED_DT,
1752 X_COMMENTS,
1753 X_AV_STND_UNIT_LVL_ID,
1754 X_CAL_TYPE,
1755 X_CI_SEQUENCE_NUMBER,
1756 X_INSTITUTION_CD,
1757 X_UNIT_DETAILS_ID,
1758 X_TST_RSLT_DTLS_ID,
1759 X_MODE,
1760 X_DEG_AUD_DETAIL_ID,
1761 X_QUAL_DETS_ID,
1762 X_UNIT_LEVEL_MARK
1763 );
1764
1765 end ADD_ROW;
1766
1767
1768 procedure DELETE_ROW (
1769 X_ROWID in VARCHAR2,
1770 x_mode IN VARCHAR2
1771 ) AS
1772 begin
1773 Before_DML(
1774 p_action => 'DELETE',
1775 x_rowid => X_ROWID
1776 );
1777 IF (x_mode = 'S') THEN
1778 igs_sc_gen_001.set_ctx('R');
1779 END IF;
1780 delete from IGS_AV_STND_UNIT_LVL_ALL
1781 where ROWID = X_ROWID ;
1782 if (sql%notfound) then
1783 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1784 igs_ge_msg_stack.add;
1785 igs_sc_gen_001.unset_ctx('R');
1786 app_exception.raise_exception;
1787 end if;
1788 IF (x_mode = 'S') THEN
1789 igs_sc_gen_001.unset_ctx('R');
1790 END IF;
1791
1792 After_DML(
1793 p_action => 'DELETE',
1794 x_rowid => X_ROWID
1795 );
1796 end DELETE_ROW;
1797
1798 end IGS_AV_STND_UNIT_LVL_PKG;