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