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