[Home] [Help]
PACKAGE BODY: APPS.IGS_AV_ADV_STANDING_PKG
Source
1 package body IGS_AV_ADV_STANDING_PKG AS
2 /* $Header: IGSBI01B.pls 120.0 2005/07/05 16:04:58 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AV_ADV_STANDING_ALL%RowType;
5 new_references IGS_AV_ADV_STANDING_ALL%RowType;
6
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_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_total_exmptn_approved IN NUMBER DEFAULT NULL,
14 x_total_exmptn_granted IN NUMBER DEFAULT NULL,
15 x_total_exmptn_perc_grntd IN NUMBER DEFAULT NULL,
16 x_exemption_institution_cd IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL,
22 x_org_id IN NUMBER DEFAULT NULL
23 ) AS
24
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_AV_ADV_STANDING_ALL
28 WHERE rowid = x_rowid;
29
30 BEGIN
31
32 l_rowid := x_rowid;
33
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 Open cur_old_ref_values;
37 Fetch cur_old_ref_values INTO old_references;
38 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
39 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
40 Igs_Ge_Msg_Stack.Add;
41 Close cur_old_ref_values;
42 App_Exception.Raise_Exception;
43
44 Return;
45 END IF;
46 Close cur_old_ref_values;
47
48 -- Populate New Values.
49 new_references.person_id := x_person_id;
50 new_references.course_cd := x_course_cd;
51 new_references.version_number := x_version_number;
52 new_references.total_exmptn_approved := x_total_exmptn_approved;
53 new_references.total_exmptn_granted := x_total_exmptn_granted;
54 new_references.total_exmptn_perc_grntd := x_total_exmptn_perc_grntd;
55 new_references.exemption_institution_cd := x_exemption_institution_cd;
56 IF (p_action = 'UPDATE') THEN
57 new_references.creation_date := old_references.creation_date;
58 new_references.created_by := old_references.created_by;
59 ELSE
60 new_references.creation_date := x_creation_date;
61 new_references.created_by := x_created_by;
62 END IF;
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66 new_references.org_id := x_org_id;
67
68 END Set_Column_Values;
69
70 -- Trigger description :-
71 -- "OSS_TST".trg_as_br_iu
72 -- BEFORE INSERT OR UPDATE
73 -- ON IGS_AV_ADV_STANDING_ALL
74 -- FOR EACH ROW
75
76 PROCEDURE BeforeRowInsertUpdate1(
77 p_inserting IN BOOLEAN DEFAULT FALSE,
78 p_updating IN BOOLEAN DEFAULT FALSE,
79 p_deleting IN BOOLEAN DEFAULT FALSE
80 ) AS
81 v_message_name varchar2(30);
82 BEGIN
83 -- Validate Advanced Standing IGS_PS_COURSE Code.
84 IF p_inserting THEN
85 IF IGS_AV_VAL_AS.advp_val_as_crs (
86 new_references.person_id,
87 new_references.course_cd,
88 new_references.version_number,
89 v_message_name) = FALSE THEN
90 Fnd_Message.Set_Name('IGS', v_message_name);
91 Igs_Ge_Msg_Stack.Add;
92 App_Exception.Raise_Exception;
93 END IF;
94 END IF;
95
96 END BeforeRowInsertUpdate1;
97
98 --
99 PROCEDURE CHECK_CONSTRAINTS (
100 Column_name IN VARCHAR2 DEFAULT NULL,
101 Column_Value IN VARCHAR2 DEFAULT NULL) AS
102 CURSOR c_local_inst_ind (
103 cp_ins_cd igs_or_institution.institution_cd%TYPE) IS
104 SELECT ins.local_institution_ind
105 FROM igs_or_institution ins
106 WHERE ins.institution_cd = cp_ins_cd;
107 CURSOR cur_program_exempt_totals (
108 cp_course_cd IGS_PS_VER.course_cd%TYPE,
109 cp_version_number IGS_PS_VER.version_number%TYPE,
110 cp_local_ind VARCHAR2) IS
111 SELECT DECODE (cp_local_ind, 'N', NVL (cv.external_adv_stnd_limit, -1),
112 NVL (cv.internal_adv_stnd_limit, -1)) adv_stnd_limit
113 FROM igs_ps_ver cv
114 WHERE cv.course_cd = cp_course_cd
115 AND cv.version_number = cp_version_number;
116 rec_cur_program_exempt_totals cur_program_exempt_totals%ROWTYPE;
117 rec_local_inst_ind c_local_inst_ind%ROWTYPE;
118 l_message_name fnd_new_messages.message_name%TYPE;
119 BEGIN
120
121 IF column_name is null then
122 NULL;
123 ELSIF upper(Column_name) = 'COURSE_CD' then
124 new_references.COURSE_CD := column_value;
125 ELSIF upper(Column_name) = 'TOTAL_EXMPTN_GRANTED' then
126 new_references. TOTAL_EXMPTN_GRANTED := IGS_GE_NUMBER.TO_NUM(column_value);
127 ELSIF upper(Column_name) = 'EXEMPTION_INSTITUTION_CD' then
128 new_references.EXEMPTION_INSTITUTION_CD := column_value;
129 ELSIF upper(Column_name) = 'TOTAL_EXMPTN_PERC_GRNTD' then
130 new_references.TOTAL_EXMPTN_PERC_GRNTD := IGS_GE_NUMBER.TO_NUM(column_value);
131 ELSIF upper(Column_name) = 'TOTAL_EXMPTN_APPROVED' then
132 new_references.TOTAL_EXMPTN_APPROVED := IGS_GE_NUMBER.TO_NUM(column_value);
133 END IF;
134
135 IF upper(column_name) = 'COURSE_CD' OR
136 column_name is null Then
137 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) Then
138 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
139 Igs_Ge_Msg_Stack.Add;
140 App_Exception.Raise_Exception;
141 END IF;
142 END IF;
143 OPEN c_local_inst_ind (new_references.exemption_institution_cd);
144 FETCH c_local_inst_ind INTO rec_local_inst_ind;
145 IF (c_local_inst_ind%NOTFOUND) THEN
146 rec_local_inst_ind.local_institution_ind := 'N';
147 END IF;
148 CLOSE c_local_inst_ind;
149 IF (rec_local_inst_ind.local_institution_ind = 'N') THEN
150 l_message_name := 'IGS_AV_EXCEEDS_PRGVER_EXT_LMT';
151 ELSE
152 l_message_name := 'IGS_AV_EXCEEDS_PRGVER_INT_LMT';
153 END IF;
154 OPEN cur_program_exempt_totals (
155 new_references.course_cd,
156 new_references.version_number,
157 rec_local_inst_ind.local_institution_ind);
158 FETCH cur_program_exempt_totals INTO rec_cur_program_exempt_totals;
159 CLOSE cur_program_exempt_totals;
160 IF upper(column_name) = 'TOTAL_EXMPTN_GRANTED' OR
161 column_name is null Then
162 IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
163 IF new_references.total_exmptn_granted < 0 OR
164 new_references.total_exmptn_granted > rec_cur_program_exempt_totals.adv_stnd_limit Then
165 Fnd_Message.Set_Name ('IGS', l_message_name);
166 Igs_Ge_Msg_Stack.Add;
167 App_Exception.Raise_Exception;
168 END IF;
169 END IF;
170 END IF;
171
172 IF upper(column_name) = 'TOTAL_EXMPTN_PERC_GRNTD' OR
173 column_name is null Then
174 IF new_references.TOTAL_EXMPTN_PERC_GRNTD < 0 OR
175 new_references.TOTAL_EXMPTN_PERC_GRNTD > 100 Then
176 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
177 Igs_Ge_Msg_Stack.Add;
178 App_Exception.Raise_Exception;
179 END IF;
180 END IF;
181
182 IF upper(column_name) = 'TOTAL_EXMPTN_APPROVED' OR
183 column_name is null Then
184 IF (rec_cur_program_exempt_totals.adv_stnd_limit <> -1) THEN
185 IF new_references.TOTAL_EXMPTN_APPROVED < 0 OR
186 new_references.TOTAL_EXMPTN_APPROVED > rec_cur_program_exempt_totals.adv_stnd_limit Then
187 Fnd_Message.Set_Name ('IGS', l_message_name);
188 Igs_Ge_Msg_Stack.Add;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192 END IF;
193 END CHECK_CONSTRAINTS ;
194 --
195
196 PROCEDURE Check_Parent_Existance AS
197 BEGIN
198
199 IF (((old_references.course_cd = new_references.course_cd) AND
200 (old_references.version_number = new_references.version_number)) OR
201 ((new_references.course_cd IS NULL) AND
202 (new_references.version_number IS NULL))) THEN
203 NULL;
204 ELSE
205 ---
206 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
207 new_references.course_cd,
208 new_references.version_number) THEN
209
210 FND_Message.Set_Name('FND','FORM_RECORD_DELETED');
211 Igs_Ge_Msg_Stack.Add;
212 App_Exception.Raise_Exception ;
213
214 END IF;
215 ---
216 END IF;
217
218 IF (((old_references.person_id = new_references.person_id)) OR
219 ((new_references.person_id IS NULL))) THEN
220 NULL;
221 ELSE
222 ---
223 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
224 new_references.person_id) THEN
225
226 FND_Message.Set_Name('FND','FORM_RECORD_DELETED');
227 Igs_Ge_Msg_Stack.Add;
228 App_Exception.Raise_Exception ;
229 END IF;
230 ---
231 END IF;
232
233 END Check_Parent_Existance;
234
235
236 PROCEDURE Check_Child_Existance AS
237 BEGIN
238
239 IGS_AV_STND_UNIT_PKG.GET_FK_IGS_AV_ADV_STANDING (
240 old_references.person_id,
241 old_references.course_cd,
242 old_references.version_number,
243 old_references.exemption_institution_cd
244 );
245
246 IGS_AV_STND_UNIT_LVL_PKG.GET_FK_IGS_AV_ADV_STANDING (
247 old_references.person_id,
248 old_references.course_cd,
249 old_references.version_number,
250 old_references.exemption_institution_cd
251 );
252
253 END Check_Child_Existance;
254
255 FUNCTION Get_PK_For_Validation (
256 x_person_id IN NUMBER,
257 x_course_cd IN VARCHAR2,
258 x_version_number IN NUMBER,
259 x_exemption_institution_cd IN VARCHAR2
260 ) RETURN BOOLEAN AS
261
262 CURSOR cur_rowid IS
263 SELECT rowid
264 FROM IGS_AV_ADV_STANDING_ALL
265 WHERE person_id = x_person_id
266 AND course_cd = x_course_cd
267 AND version_number = x_version_number
268 AND exemption_institution_cd =x_exemption_institution_cd
269 FOR UPDATE NOWAIT;
270
271 lv_rowid cur_rowid%RowType;
272
273 BEGIN
274
275 Open cur_rowid;
276 Fetch cur_rowid INTO lv_rowid;
277 ---
278 IF (cur_rowid%FOUND) THEN
279 Close cur_rowid;
280 Return (TRUE);
281 ELSE
282 Close cur_rowid;
283 Return (FALSE);
284 END IF;
285 ---
286 END Get_PK_For_Validation;
287
288 PROCEDURE GET_FK_IGS_PS_VER (
289 x_course_cd IN VARCHAR2,
290 x_version_number IN NUMBER
291 ) AS
292
293 CURSOR cur_rowid IS
294 SELECT rowid
295 FROM IGS_AV_ADV_STANDING_ALL
296 WHERE course_cd = x_course_cd
297 AND version_number = x_version_number ;
298
299 lv_rowid cur_rowid%RowType;
300
301 BEGIN
302
303 Open cur_rowid;
304 Fetch cur_rowid INTO lv_rowid;
305 IF (cur_rowid%FOUND) THEN
306 Fnd_Message.Set_Name ('IGS', 'IGS_AV_AS_CRV_FK');
307 Igs_Ge_Msg_Stack.Add;
308 Close cur_rowid;
309 App_Exception.Raise_Exception;
310 Return;
311 END IF;
312 END GET_FK_IGS_PS_VER;
313
314 PROCEDURE GET_FK_IGS_PE_PERSON (
315 x_person_id IN NUMBER
316 ) AS
317
318 CURSOR cur_rowid IS
319 SELECT rowid
320 FROM IGS_AV_ADV_STANDING_ALL
321 WHERE person_id = x_person_id ;
322
323 lv_rowid cur_rowid%RowType;
324
325 BEGIN
326
327 Open cur_rowid;
328 Fetch cur_rowid INTO lv_rowid;
329 IF (cur_rowid%FOUND) THEN
330 Fnd_Message.Set_Name ('IGS', 'IGS_AV_AS_PE_FK');
331 Igs_Ge_Msg_Stack.Add;
332 Close cur_rowid;
333 App_Exception.Raise_Exception;
334 Return;
335 END IF;
336 Close cur_rowid;
337
338 END GET_FK_IGS_PE_PERSON;
339
340 PROCEDURE Before_DML (
341 p_action IN VARCHAR2,
342 x_rowid IN VARCHAR2 DEFAULT NULL,
343 x_person_id IN NUMBER DEFAULT NULL,
344 x_course_cd IN VARCHAR2 DEFAULT NULL,
345 x_version_number IN NUMBER DEFAULT NULL,
346 x_total_exmptn_approved IN NUMBER DEFAULT NULL,
347 x_total_exmptn_granted IN NUMBER DEFAULT NULL,
348 x_total_exmptn_perc_grntd IN NUMBER DEFAULT NULL,
349 x_exemption_institution_cd IN VARCHAR2 DEFAULT NULL,
350 x_creation_date IN DATE DEFAULT NULL,
351 x_created_by IN NUMBER DEFAULT NULL,
352 x_last_update_date IN DATE DEFAULT NULL,
353 x_last_updated_by IN NUMBER DEFAULT NULL,
354 x_last_update_login IN NUMBER DEFAULT NULL,
355 x_org_id IN NUMBER DEFAULT NULL
356 ) AS
357 BEGIN
358
359 Set_Column_Values (
360 p_action,
361 x_rowid,
362 x_person_id,
363 x_course_cd,
364 x_version_number,
365 x_total_exmptn_approved,
366 x_total_exmptn_granted,
367 x_total_exmptn_perc_grntd,
368 x_exemption_institution_cd,
369 x_creation_date,
370 x_created_by,
371 x_last_update_date,
372 x_last_updated_by,
373 x_last_update_login,
374 x_org_id
375 );
376
377 IF (p_action = 'INSERT') THEN
378 -- Call all the procedures related to Before Insert.
379 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
380 --
381 IF Get_PK_For_Validation (new_references.person_id,
382 new_references.course_cd, new_references.version_number,new_references.exemption_institution_cd) THEN
383 FND_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC') ;
384 Igs_Ge_Msg_Stack.Add;
385 App_Exception.Raise_Exception ;
386 END IF;
387 --
388 CHECK_CONSTRAINTS;
389 Check_Parent_Existance;
390 ELSIF (p_action = 'UPDATE') THEN
391 -- Call all the procedures related to Before Update.
392 BeforeRowInsertUpdate1 ( p_updating => TRUE );
393 CHECK_CONSTRAINTS;
394 Check_Parent_Existance;
395 ELSIF (p_action = 'DELETE') THEN
396 -- Call all the procedures related to Before Delete.
397 Check_Child_Existance;
398 --
399 ELSIF (P_Action = 'VALIDATE_INSERT') THEN
400 IF Get_PK_For_Validation (new_references.person_id,
401 new_references.course_cd, new_references.version_number,new_references.exemption_institution_cd) THEN
402 FND_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC') ;
403 Igs_Ge_Msg_Stack.Add;
404 App_Exception.Raise_Exception ;
405 END IF;
406 CHECK_CONSTRAINTS;
407 ELSIF (P_Action = 'VALIDATE_UPDATE') THEN
408 CHECK_CONSTRAINTS;
409 ELSIF (P_Action = 'VALIDATE_DELETE') THEN
410 Check_Child_Existance;
411 END IF;
412 --
413 END Before_DML;
414
415 procedure INSERT_ROW (
416 X_ROWID in out NOCOPY VARCHAR2,
417 X_PERSON_ID in NUMBER,
418 X_COURSE_CD in VARCHAR2,
419 X_VERSION_NUMBER in NUMBER,
420 X_TOTAL_EXMPTN_APPROVED in NUMBER,
421 X_TOTAL_EXMPTN_GRANTED in NUMBER,
422 X_TOTAL_EXMPTN_PERC_GRNTD in NUMBER,
423 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
424 X_MODE in VARCHAR2 default 'R',
425 X_ORG_ID in NUMBER
426 ) AS
427 cursor C is select ROWID from IGS_AV_ADV_STANDING_ALL
428 where PERSON_ID = X_PERSON_ID
429 and COURSE_CD = X_COURSE_CD
430 and VERSION_NUMBER = X_VERSION_NUMBER;
431 X_LAST_UPDATE_DATE DATE;
432 X_LAST_UPDATED_BY NUMBER;
433 X_LAST_UPDATE_LOGIN NUMBER;
434 begin
435 X_LAST_UPDATE_DATE := SYSDATE;
436 if(X_MODE = 'I') then
437 X_LAST_UPDATED_BY := 1;
438 X_LAST_UPDATE_LOGIN := 0;
439 elsif (X_MODE IN ('R', 'S')) then
440 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
441 if X_LAST_UPDATED_BY is NULL then
442 X_LAST_UPDATED_BY := -1;
443 end if;
444 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
445 if X_LAST_UPDATE_LOGIN is NULL then
446 X_LAST_UPDATE_LOGIN := -1;
447 end if;
448 else
449 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
450 Igs_Ge_Msg_Stack.Add;
451 app_exception.raise_exception;
452 end if;
453 Before_DML(
454 p_action=>'INSERT',
455 x_rowid=>X_ROWID,
456 x_course_cd=>X_COURSE_CD,
457 x_exemption_institution_cd=>X_EXEMPTION_INSTITUTION_CD,
458 x_person_id=>X_PERSON_ID,
459 x_total_exmptn_approved=>NVL(X_TOTAL_EXMPTN_APPROVED,0),
460 x_total_exmptn_granted=>NVL(X_TOTAL_EXMPTN_GRANTED,0),
461 x_total_exmptn_perc_grntd=>NVL(X_TOTAL_EXMPTN_PERC_GRNTD,0),
462 x_version_number=>X_VERSION_NUMBER,
463 x_creation_date=>X_LAST_UPDATE_DATE,
464 x_created_by=>X_LAST_UPDATED_BY,
465 x_last_update_date=>X_LAST_UPDATE_DATE,
466 x_last_updated_by=>X_LAST_UPDATED_BY,
467 x_last_update_login=>X_LAST_UPDATE_LOGIN,
468 x_org_id=>igs_ge_gen_003.get_org_id
469 );
470 IF (x_mode = 'S') THEN
471 igs_sc_gen_001.set_ctx('R');
472 END IF;
473 insert into IGS_AV_ADV_STANDING_ALL (
474 PERSON_ID,
475 COURSE_CD,
476 VERSION_NUMBER,
477 TOTAL_EXMPTN_APPROVED,
478 TOTAL_EXMPTN_GRANTED,
479 TOTAL_EXMPTN_PERC_GRNTD,
480 EXEMPTION_INSTITUTION_CD,
481 CREATION_DATE,
482 CREATED_BY,
483 LAST_UPDATE_DATE,
484 LAST_UPDATED_BY,
485 LAST_UPDATE_LOGIN,
486 ORG_ID
487 ) values (
488 NEW_REFERENCES.PERSON_ID,
489 NEW_REFERENCES.COURSE_CD,
490 NEW_REFERENCES.VERSION_NUMBER,
491 NEW_REFERENCES.TOTAL_EXMPTN_APPROVED,
492 NEW_REFERENCES.TOTAL_EXMPTN_GRANTED,
493 NEW_REFERENCES.TOTAL_EXMPTN_PERC_GRNTD,
494 NEW_REFERENCES.EXEMPTION_INSTITUTION_CD,
495 X_LAST_UPDATE_DATE,
496 X_LAST_UPDATED_BY,
497 X_LAST_UPDATE_DATE,
498 X_LAST_UPDATED_BY,
499 X_LAST_UPDATE_LOGIN,
500 NEW_REFERENCES.ORG_ID
501 );
502 IF (x_mode = 'S') THEN
503 igs_sc_gen_001.unset_ctx('R');
504 END IF;
505
506
507 open c;
508 fetch c into X_ROWID;
509 if (c%notfound) then
510 close c;
511 raise no_data_found;
512 end if;
513 close c;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
518 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
519 fnd_message.set_token ('ERR_CD', SQLCODE);
520 igs_ge_msg_stack.add;
521 igs_sc_gen_001.unset_ctx('R');
522 app_exception.raise_exception;
523 ELSE
524 igs_sc_gen_001.unset_ctx('R');
525 RAISE;
526 END IF;
527
528 end INSERT_ROW;
529
530 procedure LOCK_ROW (
531 X_ROWID in VARCHAR2,
532 X_PERSON_ID in NUMBER,
533 X_COURSE_CD in VARCHAR2,
534 X_VERSION_NUMBER in NUMBER,
535 X_TOTAL_EXMPTN_APPROVED in NUMBER,
536 X_TOTAL_EXMPTN_GRANTED in NUMBER,
537 X_TOTAL_EXMPTN_PERC_GRNTD in NUMBER,
538 X_EXEMPTION_INSTITUTION_CD in VARCHAR2
539 ) AS
540 cursor c1 is select
541 TOTAL_EXMPTN_APPROVED,
542 TOTAL_EXMPTN_GRANTED,
543 TOTAL_EXMPTN_PERC_GRNTD,
544 EXEMPTION_INSTITUTION_CD
545 from IGS_AV_ADV_STANDING_ALL
546 where ROWID = X_ROWID for update nowait;
547 tlinfo c1%rowtype;
548
549 begin
550 open c1;
551 fetch c1 into tlinfo;
552 if (c1%notfound) then
553 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
554 Igs_Ge_Msg_Stack.Add;
555 close c1;
556 app_exception.raise_exception;
557 return;
558 end if;
559 close c1;
560
561 if ( (tlinfo.TOTAL_EXMPTN_APPROVED = X_TOTAL_EXMPTN_APPROVED)
562 AND (tlinfo.TOTAL_EXMPTN_GRANTED = X_TOTAL_EXMPTN_GRANTED)
563 AND (tlinfo.TOTAL_EXMPTN_PERC_GRNTD = X_TOTAL_EXMPTN_PERC_GRNTD)
564 AND (tlinfo.EXEMPTION_INSTITUTION_CD = X_EXEMPTION_INSTITUTION_CD)
565 ) then
566 null;
567 else
568 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
569 Igs_Ge_Msg_Stack.Add;
570 app_exception.raise_exception;
571 end if;
572 return;
573 end LOCK_ROW;
574
575 procedure UPDATE_ROW (
576 X_ROWID in VARCHAR2,
577 X_PERSON_ID in NUMBER,
578 X_COURSE_CD in VARCHAR2,
579 X_VERSION_NUMBER in NUMBER,
580 X_TOTAL_EXMPTN_APPROVED in NUMBER,
581 X_TOTAL_EXMPTN_GRANTED in NUMBER,
582 X_TOTAL_EXMPTN_PERC_GRNTD in NUMBER,
583 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
584 X_MODE in VARCHAR2 default 'R'
585 ) AS
586 X_LAST_UPDATE_DATE DATE;
587 X_LAST_UPDATED_BY NUMBER;
588 X_LAST_UPDATE_LOGIN NUMBER;
589 begin
590 X_LAST_UPDATE_DATE := SYSDATE;
591 if(X_MODE = 'I') then
592 X_LAST_UPDATED_BY := 1;
593 X_LAST_UPDATE_LOGIN := 0;
594 elsif (X_MODE IN ('R', 'S')) then
595 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
596 if X_LAST_UPDATED_BY is NULL then
597 X_LAST_UPDATED_BY := -1;
598 end if;
599 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
600 if X_LAST_UPDATE_LOGIN is NULL then
601 X_LAST_UPDATE_LOGIN := -1;
602 end if;
603 else
604 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
605 Igs_Ge_Msg_Stack.Add;
606 app_exception.raise_exception;
607 end if;
608 Before_DML(
609 p_action=>'UPDATE',
610 x_rowid=>X_ROWID,
611 x_course_cd=>X_COURSE_CD,
612 x_exemption_institution_cd=>X_EXEMPTION_INSTITUTION_CD,
613 x_person_id=>X_PERSON_ID,
614 x_total_exmptn_approved=>X_TOTAL_EXMPTN_APPROVED,
615 x_total_exmptn_granted=>X_TOTAL_EXMPTN_GRANTED,
616 x_total_exmptn_perc_grntd=>X_TOTAL_EXMPTN_PERC_GRNTD,
617 x_version_number=>X_VERSION_NUMBER,
618 x_creation_date=>X_LAST_UPDATE_DATE,
619 x_created_by=>X_LAST_UPDATED_BY,
620 x_last_update_date=>X_LAST_UPDATE_DATE,
621 x_last_updated_by=>X_LAST_UPDATED_BY,
622 x_last_update_login=>X_LAST_UPDATE_LOGIN
623 );
624 IF (x_mode = 'S') THEN
625 igs_sc_gen_001.set_ctx('R');
626 END IF;
627 update IGS_AV_ADV_STANDING_ALL set
628 TOTAL_EXMPTN_APPROVED = NEW_REFERENCES.TOTAL_EXMPTN_APPROVED,
629 TOTAL_EXMPTN_GRANTED = NEW_REFERENCES.TOTAL_EXMPTN_GRANTED,
630 TOTAL_EXMPTN_PERC_GRNTD = NEW_REFERENCES.TOTAL_EXMPTN_PERC_GRNTD,
631 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
632 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
633 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
634 where ROWID = X_ROWID ;
635 if (sql%notfound) then
636 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
637 igs_ge_msg_stack.add;
638 igs_sc_gen_001.unset_ctx('R');
639 app_exception.raise_exception;
640 end if;
641 IF (x_mode = 'S') THEN
642 igs_sc_gen_001.unset_ctx('R');
643 END IF;
644
645
646 EXCEPTION
647 WHEN OTHERS THEN
648 IF (SQLCODE = (-28115)) THEN
649 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
650 fnd_message.set_token ('ERR_CD', SQLCODE);
651 igs_ge_msg_stack.add;
652 igs_sc_gen_001.unset_ctx('R');
653 app_exception.raise_exception;
654 ELSE
655 igs_sc_gen_001.unset_ctx('R');
656 RAISE;
657 END IF;
658 end UPDATE_ROW;
659
660 procedure ADD_ROW (
661 X_ROWID in out NOCOPY VARCHAR2,
662 X_PERSON_ID in NUMBER,
663 X_COURSE_CD in VARCHAR2,
664 X_VERSION_NUMBER in NUMBER,
665 X_TOTAL_EXMPTN_APPROVED in NUMBER,
666 X_TOTAL_EXMPTN_GRANTED in NUMBER,
667 X_TOTAL_EXMPTN_PERC_GRNTD in NUMBER,
668 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
669 X_MODE in VARCHAR2 default 'R',
670 X_ORG_ID in NUMBER
671 ) AS
672 cursor c1 is select rowid from IGS_AV_ADV_STANDING_ALL
673 where PERSON_ID = X_PERSON_ID
674 and COURSE_CD = X_COURSE_CD
675 and VERSION_NUMBER = X_VERSION_NUMBER
676 ;
677 begin
678 open c1;
679 fetch c1 into X_ROWID ;
680 if (c1%notfound) then
681 close c1;
682 INSERT_ROW (
683 X_ROWID,
684 X_PERSON_ID,
685 X_COURSE_CD,
686 X_VERSION_NUMBER,
687 X_TOTAL_EXMPTN_APPROVED,
688 X_TOTAL_EXMPTN_GRANTED,
689 X_TOTAL_EXMPTN_PERC_GRNTD,
690 X_EXEMPTION_INSTITUTION_CD,
691 X_ORG_ID,
692 X_MODE);
693 return;
694 end if;
695 close c1;
696 UPDATE_ROW (
697 X_ROWID,
698 X_PERSON_ID,
699 X_COURSE_CD,
700 X_VERSION_NUMBER,
701 X_TOTAL_EXMPTN_APPROVED,
702 X_TOTAL_EXMPTN_GRANTED,
703 X_TOTAL_EXMPTN_PERC_GRNTD,
704 X_EXEMPTION_INSTITUTION_CD,
705 X_MODE);
706 end ADD_ROW;
707
708 procedure DELETE_ROW (
709 X_ROWID in VARCHAR2,
710 x_mode IN VARCHAR2) AS
711 begin
712 Before_DML(
713 p_action => 'DELETE',
714 x_rowid => X_ROWID
715 );
716
717
718 IF (x_mode = 'S') THEN
719 igs_sc_gen_001.set_ctx('R');
720 END IF;
721 delete from IGS_AV_ADV_STANDING_ALL
722 where ROWID = X_ROWID ;
723 if (sql%notfound) then
724 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
725 igs_ge_msg_stack.add;
726 igs_sc_gen_001.unset_ctx('R');
727 app_exception.raise_exception;
728 end if;
729 IF (x_mode = 'S') THEN
730 igs_sc_gen_001.unset_ctx('R');
731 END IF;
732
733
734 end DELETE_ROW;
735
736 end IGS_AV_ADV_STANDING_PKG;