[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_OS_SEC_ED_SUB_PKG
Source
1 package body IGS_AD_OS_SEC_ED_SUB_PKG as
2 /* $Header: IGSAI40B.pls 115.3 2002/11/28 22:04:52 nsidana ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_AD_OS_SEC_ED_SUB%RowType;
7 new_references IGS_AD_OS_SEC_ED_SUB%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_ose_sequence_number IN NUMBER DEFAULT NULL,
14 x_sequence_number IN NUMBER DEFAULT NULL,
15 x_subject_cd IN VARCHAR2 DEFAULT NULL,
16 x_subject_desc IN VARCHAR2 DEFAULT NULL,
17 x_result_type IN VARCHAR2 DEFAULT NULL,
18 x_result IN VARCHAR2 DEFAULT NULL,
19 x_subject_result_yr IN NUMBER DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM IGS_AD_OS_SEC_ED_SUB
30 WHERE rowid = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 Open cur_old_ref_values;
39 Fetch cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
41 Close cur_old_ref_values;
42 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
43 IGS_GE_MSG_STACK.ADD;
44 App_Exception.Raise_Exception;
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.person_id := x_person_id;
51 new_references.ose_sequence_number := x_ose_sequence_number;
52 new_references.sequence_number := x_sequence_number;
53 new_references.subject_cd := x_subject_cd;
54 new_references.subject_desc := x_subject_desc;
55 new_references.result_type := x_result_type;
56 new_references.result := x_result;
57 new_references.subject_result_yr := x_subject_result_yr;
58 IF (p_action = 'UPDATE') THEN
59 new_references.creation_date := old_references.creation_date;
60 new_references.created_by := old_references.created_by;
61 ELSE
62 new_references.creation_date := x_creation_date;
63 new_references.created_by := x_created_by;
64 END IF;
65 new_references.last_update_date := x_last_update_date;
66 new_references.last_updated_by := x_last_updated_by;
67 new_references.last_update_login := x_last_update_login;
68
69 END Set_Column_Values;
70
71
72 PROCEDURE BeforeRowInsertUpdate1(
73 p_inserting IN BOOLEAN DEFAULT FALSE,
74 p_updating IN BOOLEAN DEFAULT FALSE,
75 p_deleting IN BOOLEAN DEFAULT FALSE
76 ) AS
77 v_message_name varchar2(30);
78 BEGIN
79 -- Ensure that at least one of subject_cd and/or subject_desc is entered
80 IF p_inserting OR p_updating THEN
81 IF IGS_AD_VAL_OSES.admp_val_oses_subj(
82 new_references.subject_cd,
83 new_references.subject_desc,
84 v_message_name) = FALSE THEN
85 Fnd_Message.Set_Name('IGS',v_message_name);
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88 END IF;
89 END IF;
90 -- Validate System admission outcome status IGS_PS_UNIT outcome ind.
91 IF p_inserting OR (old_references.result_type <> new_references.result_type) THEN
92 IF IGS_AD_VAL_OSES.admp_val_teua_sret(
93 new_references.result_type,
94 v_message_name) = FALSE THEN
95 Fnd_Message.Set_Name('IGS',v_message_name);
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 END IF;
99 END IF;
100
101
102 END BeforeRowInsertUpdate1;
103
104 procedure Check_Constraints (
105 Column_Name IN VARCHAR2 DEFAULT NULL,
106 Column_Value IN VARCHAR2 DEFAULT NULL
107 )
108 AS
109 BEGIN
110 IF Column_Name is null then
111 NULL;
112 ELSIF upper(Column_Name) = 'SUBJECT_CD' then
113 new_references.subject_cd := column_value;
114 ELSIF upper(Column_Name) = 'SUBJECT_DESC' then
115 new_references.subject_desc := column_value;
116 ELSIF upper(Column_Name) = 'RESULT_TYPE' then
117 new_references.result_type := column_value;
118 ELSIF upper(Column_Name) = 'RESULT' then
119 new_references.result := column_value;
120 ELSIF upper(Column_Name) = 'OSE_SEQUENCE_NUMBER' then
121 new_references.ose_sequence_number := igs_ge_number.to_num(column_value);
122 ELSIF upper(Column_Name) = 'SEQUENCE_NUMBER' then
123 new_references.sequence_number := igs_ge_number.to_num(column_value);
124 ELSIF upper(Column_Name) = 'SUBJECT_RESULT_YR' then
125 new_references.subject_result_yr := igs_ge_number.to_num(column_value);
126 END IF;
127
128 IF upper(Column_Name) = 'SUBJECT_CD' OR Column_Name IS NULL THEN
129 IF new_references.subject_cd <> UPPER(new_references.subject_cd) 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_DESC' OR Column_Name IS NULL THEN
136 IF new_references.subject_desc <> UPPER(new_references.subject_desc) 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) = 'RESULT_TYPE' OR Column_Name IS NULL THEN
143 IF new_references.result_type <> UPPER(new_references.result_type) 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) = 'RESULT' OR Column_Name IS NULL THEN
150 IF new_references.result <> UPPER(new_references.result) 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 IF upper(Column_Name) = 'OSE_SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
157 IF new_references.ose_sequence_number < 1 OR new_references.ose_sequence_number > 999999 THEN
158 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 END IF;
163 IF upper(Column_Name) = 'SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
164 IF new_references.sequence_number < 1 OR new_references.sequence_number > 999999 THEN
165 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
166 IGS_GE_MSG_STACK.ADD;
167 App_Exception.Raise_Exception;
168 END IF;
169 END IF;
170 IF upper(Column_Name) = 'SUBJECT_RESULT_YR' OR Column_Name IS NULL THEN
171 IF new_references.subject_result_yr < 1900 OR new_references.subject_result_yr > 2050 THEN
172 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177
178 END Check_Constraints;
179
180 PROCEDURE Check_Parent_Existance AS
181 BEGIN
182
183 IF (((old_references.person_id = new_references.person_id) AND
184 (old_references.ose_sequence_number = new_references.ose_sequence_number)) OR
185 ((new_references.person_id IS NULL) OR
186 (new_references.ose_sequence_number IS NULL))) THEN
187 NULL;
188 ELSE
189 IF NOT IGS_AD_OS_SEC_EDU_PKG.Get_PK_For_Validation (
190 new_references.person_id,
191 new_references.ose_sequence_number
192 ) THEN
193 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END IF;
197 END IF;
198
199 END Check_Parent_Existance;
200
201 FUNCTION Get_PK_For_Validation (
202 x_person_id IN NUMBER,
203 x_ose_sequence_number IN NUMBER,
204 x_sequence_number IN NUMBER
205 )return BOOLEAN AS
206
207 CURSOR cur_rowid is
208 SELECT rowid
209 FROM IGS_AD_OS_SEC_ED_SUB
210 WHERE person_id = x_person_id
211 AND ose_sequence_number = x_ose_sequence_number
212 AND sequence_number = x_sequence_number
213 FOR UPDATE NOWAIT;
214
215 lv_rowid cur_rowid%RowType;
216
217 BEGIN
218
219 Open cur_rowid;
220 Fetch cur_rowid INTO lv_rowid;
221 IF (cur_rowid%FOUND) THEN
222 Close cur_rowid;
223 Return(TRUE);
224 ELSE
225 Close cur_rowid;
226 Return(FALSE);
227 END IF;
228
229 END Get_PK_For_Validation;
230
231 PROCEDURE GET_FK_IGS_AD_OS_SEC_EDU (
232 x_person_id IN NUMBER,
233 x_sequence_number IN NUMBER
234 ) AS
235
236 CURSOR cur_rowid IS
237 SELECT rowid
238 FROM IGS_AD_OS_SEC_ED_SUB
239 WHERE person_id = x_person_id
240 AND ose_sequence_number = x_sequence_number ;
241
242 lv_rowid cur_rowid%RowType;
243
244 BEGIN
245
246 Open cur_rowid;
247 Fetch cur_rowid INTO lv_rowid;
248 IF (cur_rowid%FOUND) THEN
249 Close cur_rowid;
250 Fnd_Message.Set_Name ('IGS', 'IGS_AD_OSES_OSE_FK');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 Return;
254 END IF;
255 Close cur_rowid;
256
257 END GET_FK_IGS_AD_OS_SEC_EDU;
258
259 PROCEDURE Before_DML (
260 p_action IN VARCHAR2,
261 x_rowid IN VARCHAR2 DEFAULT NULL,
262 x_person_id IN NUMBER DEFAULT NULL,
263 x_ose_sequence_number IN NUMBER DEFAULT NULL,
264 x_sequence_number IN NUMBER DEFAULT NULL,
265 x_subject_cd IN VARCHAR2 DEFAULT NULL,
266 x_subject_desc IN VARCHAR2 DEFAULT NULL,
267 x_result_type IN VARCHAR2 DEFAULT NULL,
268 x_result IN VARCHAR2 DEFAULT NULL,
269 x_subject_result_yr IN NUMBER DEFAULT NULL,
270 x_creation_date IN DATE DEFAULT NULL,
271 x_created_by IN NUMBER DEFAULT NULL,
272 x_last_update_date IN DATE DEFAULT NULL,
273 x_last_updated_by IN NUMBER DEFAULT NULL,
274 x_last_update_login IN NUMBER DEFAULT NULL
275 ) AS
276 BEGIN
277
278 Set_Column_Values (
279 p_action,
280 x_rowid,
281 x_person_id,
282 x_ose_sequence_number,
283 x_sequence_number,
284 x_subject_cd,
285 x_subject_desc,
286 x_result_type,
287 x_result,
288 x_subject_result_yr,
289 x_creation_date,
290 x_created_by,
291 x_last_update_date,
292 x_last_updated_by,
293 x_last_update_login
294 );
295
296 IF (p_action = 'INSERT') THEN
297 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
298 IF Get_PK_For_Validation (
299 new_references.person_id,
300 new_references.ose_sequence_number,
301 new_references.sequence_number
302 ) THEN
303 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 END IF;
307 Check_Constraints;
308 Check_Parent_Existance;
309 ELSIF (p_action = 'UPDATE') THEN
310 BeforeRowInsertUpdate1 ( p_updating => TRUE );
311 Check_Constraints;
312 Check_Parent_Existance;
313 ELSIF (p_action = 'VALIDATE_INSERT') THEN
314 IF Get_PK_For_Validation (
315 new_references.person_id,
316 new_references.ose_sequence_number,
317 new_references.sequence_number
318 ) THEN
319 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
320 IGS_GE_MSG_STACK.ADD;
321 App_Exception.Raise_Exception;
322 END IF;
323 Check_Constraints;
324 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
325 Check_Constraints;
326 END IF;
327
328 END Before_DML;
329
330 PROCEDURE After_DML (
331 p_action IN VARCHAR2,
332 x_rowid IN VARCHAR2
333 ) AS
334 BEGIN
335
336 l_rowid := x_rowid;
337
338
339 END After_DML;
340
341
342 procedure INSERT_ROW (
343 X_ROWID in out NOCOPY VARCHAR2,
344 X_PERSON_ID in NUMBER,
345 X_OSE_SEQUENCE_NUMBER in NUMBER,
346 X_SEQUENCE_NUMBER in NUMBER,
347 X_SUBJECT_CD in VARCHAR2,
348 X_SUBJECT_DESC in VARCHAR2,
349 X_RESULT_TYPE in VARCHAR2,
350 X_RESULT in VARCHAR2,
351 X_SUBJECT_RESULT_YR in NUMBER,
352 X_MODE in VARCHAR2 default 'R'
353 ) AS
354 cursor C is select ROWID from IGS_AD_OS_SEC_ED_SUB
355 where PERSON_ID = X_PERSON_ID
356 and OSE_SEQUENCE_NUMBER = X_OSE_SEQUENCE_NUMBER
357 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
358 X_LAST_UPDATE_DATE DATE;
359 X_LAST_UPDATED_BY NUMBER;
360 X_LAST_UPDATE_LOGIN NUMBER;
361 begin
362 X_LAST_UPDATE_DATE := SYSDATE;
363 if(X_MODE = 'I') then
364 X_LAST_UPDATED_BY := 1;
365 X_LAST_UPDATE_LOGIN := 0;
366 elsif (X_MODE = 'R') then
367 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
368 if X_LAST_UPDATED_BY is NULL then
369 X_LAST_UPDATED_BY := -1;
370 end if;
371 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
372 if X_LAST_UPDATE_LOGIN is NULL then
373 X_LAST_UPDATE_LOGIN := -1;
374 end if;
375 else
376 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
377 IGS_GE_MSG_STACK.ADD;
378 app_exception.raise_exception;
379 end if;
380
381 Before_DML(
382 p_action=>'INSERT' ,
383 x_rowid=>X_ROWID ,
384 x_person_id => X_PERSON_ID ,
385 x_ose_sequence_number => X_OSE_SEQUENCE_NUMBER ,
386 x_sequence_number => X_SEQUENCE_NUMBER ,
387 x_subject_cd => X_SUBJECT_CD ,
388 x_subject_desc => X_SUBJECT_DESC ,
389 x_result_type => X_RESULT_TYPE ,
390 x_result => X_RESULT ,
391 x_subject_result_yr => X_SUBJECT_RESULT_YR ,
392 x_creation_date=>X_LAST_UPDATE_DATE,
393 x_created_by=>X_LAST_UPDATED_BY ,
394 x_last_update_date=>X_LAST_UPDATE_DATE ,
395 x_last_updated_by=>X_LAST_UPDATED_BY ,
396 x_last_update_login=> X_LAST_UPDATE_LOGIN
397 );
398
399
400 insert into IGS_AD_OS_SEC_ED_SUB (
401 PERSON_ID,
402 OSE_SEQUENCE_NUMBER,
403 SEQUENCE_NUMBER,
404 SUBJECT_CD,
405 SUBJECT_DESC,
406 RESULT_TYPE,
407 RESULT,
408 SUBJECT_RESULT_YR,
409 CREATION_DATE,
410 CREATED_BY,
411 LAST_UPDATE_DATE,
412 LAST_UPDATED_BY,
413 LAST_UPDATE_LOGIN
414 ) values (
415 NEW_REFERENCES.PERSON_ID,
416 NEW_REFERENCES.OSE_SEQUENCE_NUMBER,
417 NEW_REFERENCES.SEQUENCE_NUMBER,
418 NEW_REFERENCES.SUBJECT_CD,
419 NEW_REFERENCES.SUBJECT_DESC,
420 NEW_REFERENCES.RESULT_TYPE,
421 NEW_REFERENCES.RESULT,
422 NEW_REFERENCES.SUBJECT_RESULT_YR,
423 X_LAST_UPDATE_DATE,
424 X_LAST_UPDATED_BY,
425 X_LAST_UPDATE_DATE,
426 X_LAST_UPDATED_BY,
427 X_LAST_UPDATE_LOGIN
428 );
429
430 open c;
431 fetch c into X_ROWID;
432 if (c%notfound) then
433 close c;
434 raise no_data_found;
435 end if;
436 close c;
437
438 After_DML(
439 p_action=>'INSERT',
440 x_rowid=> X_ROWID
441 );
442
443
447 X_ROWID in VARCHAR2 ,
444 end INSERT_ROW;
445
446 procedure LOCK_ROW (
448 X_PERSON_ID in NUMBER,
449 X_OSE_SEQUENCE_NUMBER in NUMBER,
450 X_SEQUENCE_NUMBER in NUMBER,
451 X_SUBJECT_CD in VARCHAR2,
452 X_SUBJECT_DESC in VARCHAR2,
453 X_RESULT_TYPE in VARCHAR2,
454 X_RESULT in VARCHAR2,
455 X_SUBJECT_RESULT_YR in NUMBER
456 ) AS
457 cursor c1 is select
458 SUBJECT_CD,
459 SUBJECT_DESC,
460 RESULT_TYPE,
461 RESULT,
462 SUBJECT_RESULT_YR
463 from IGS_AD_OS_SEC_ED_SUB
464 WHERE ROWID = X_ROWID for update nowait ;
465 tlinfo c1%rowtype;
466
467 begin
468 open c1;
469 fetch c1 into tlinfo;
470 if (c1%notfound) then
471 close c1;
472 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
473 IGS_GE_MSG_STACK.ADD;
474 app_exception.raise_exception;
475 return;
476 end if;
477 close c1;
478
479 if ( ((tlinfo.SUBJECT_CD = X_SUBJECT_CD)
480 OR ((tlinfo.SUBJECT_CD is null)
481 AND (X_SUBJECT_CD is null)))
482 AND ((tlinfo.SUBJECT_DESC = X_SUBJECT_DESC)
483 OR ((tlinfo.SUBJECT_DESC is null)
484 AND (X_SUBJECT_DESC is null)))
485 AND (tlinfo.RESULT_TYPE = X_RESULT_TYPE)
486 AND ((tlinfo.RESULT = X_RESULT)
487 OR ((tlinfo.RESULT is null)
488 AND (X_RESULT is null)))
489 AND ((tlinfo.SUBJECT_RESULT_YR = X_SUBJECT_RESULT_YR)
490 OR ((tlinfo.SUBJECT_RESULT_YR is null)
491 AND (X_SUBJECT_RESULT_YR is null)))
492 ) then
493 null;
494 else
495 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
496 IGS_GE_MSG_STACK.ADD;
497 app_exception.raise_exception;
498 end if;
499 return;
500 end LOCK_ROW;
501
502 procedure UPDATE_ROW (
503 X_ROWID in VARCHAR2 ,
504 X_PERSON_ID in NUMBER,
505 X_OSE_SEQUENCE_NUMBER in NUMBER,
506 X_SEQUENCE_NUMBER in NUMBER,
507 X_SUBJECT_CD in VARCHAR2,
508 X_SUBJECT_DESC in VARCHAR2,
509 X_RESULT_TYPE in VARCHAR2,
510 X_RESULT in VARCHAR2,
511 X_SUBJECT_RESULT_YR in NUMBER,
512 X_MODE in VARCHAR2 default 'R'
513 ) AS
514 X_LAST_UPDATE_DATE DATE;
515 X_LAST_UPDATED_BY NUMBER;
516 X_LAST_UPDATE_LOGIN NUMBER;
517 begin
518 X_LAST_UPDATE_DATE := SYSDATE;
519 if(X_MODE = 'I') then
520 X_LAST_UPDATED_BY := 1;
521 X_LAST_UPDATE_LOGIN := 0;
522 elsif (X_MODE = 'R') then
523 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
524 if X_LAST_UPDATED_BY is NULL then
525 X_LAST_UPDATED_BY := -1;
526 end if;
527 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
528 if X_LAST_UPDATE_LOGIN is NULL then
529 X_LAST_UPDATE_LOGIN := -1;
530 end if;
531 else
532 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
533 IGS_GE_MSG_STACK.ADD;
534 app_exception.raise_exception;
535 end if;
536
537 Before_DML(
538 p_action=>'UPDATE' ,
539 x_rowid=>X_ROWID ,
540 x_person_id => X_PERSON_ID ,
541 x_ose_sequence_number => X_OSE_SEQUENCE_NUMBER ,
542 x_sequence_number => X_SEQUENCE_NUMBER ,
543 x_subject_cd => X_SUBJECT_CD ,
544 x_subject_desc => X_SUBJECT_DESC ,
545 x_result_type => X_RESULT_TYPE ,
546 x_result => X_RESULT ,
547 x_subject_result_yr => X_SUBJECT_RESULT_YR ,
548 x_creation_date=>X_LAST_UPDATE_DATE ,
549 x_created_by=>X_LAST_UPDATED_BY ,
550 x_last_update_date=>X_LAST_UPDATE_DATE ,
551 x_last_updated_by=>X_LAST_UPDATED_BY ,
552 x_last_update_login=> X_LAST_UPDATE_LOGIN
553 );
554
555
556
557 update IGS_AD_OS_SEC_ED_SUB set
558 SUBJECT_CD = NEW_REFERENCES.SUBJECT_CD,
559 SUBJECT_DESC = NEW_REFERENCES.SUBJECT_DESC,
560 RESULT_TYPE = NEW_REFERENCES.RESULT_TYPE,
561 RESULT = NEW_REFERENCES.RESULT,
562 SUBJECT_RESULT_YR = NEW_REFERENCES.SUBJECT_RESULT_YR,
563 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
564 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
565 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
566 where ROWID = X_ROWID ;
567 if (sql%notfound) then
568 raise no_data_found;
569 end if;
570
571 After_DML(
572 p_action=>'UPDATE',
573 x_rowid=> X_ROWID
574 );
575
576 end UPDATE_ROW;
577
578 procedure ADD_ROW (
579 X_ROWID in out NOCOPY VARCHAR2,
580 X_PERSON_ID in NUMBER,
581 X_OSE_SEQUENCE_NUMBER in NUMBER,
582 X_SEQUENCE_NUMBER in NUMBER,
583 X_SUBJECT_CD in VARCHAR2,
584 X_SUBJECT_DESC in VARCHAR2,
585 X_RESULT_TYPE in VARCHAR2,
586 X_RESULT in VARCHAR2,
587 X_SUBJECT_RESULT_YR in NUMBER,
588 X_MODE in VARCHAR2 default 'R'
589 ) AS
590 cursor c1 is select rowid from IGS_AD_OS_SEC_ED_SUB
591 where PERSON_ID = X_PERSON_ID
592 and OSE_SEQUENCE_NUMBER = X_OSE_SEQUENCE_NUMBER
593 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
594 ;
595
596 begin
597 open c1;
598 fetch c1 into X_ROWID;
599 if (c1%notfound) then
600 close c1;
601 INSERT_ROW (
602 X_ROWID,
603 X_PERSON_ID,
604 X_OSE_SEQUENCE_NUMBER,
605 X_SEQUENCE_NUMBER,
606 X_SUBJECT_CD,
607 X_SUBJECT_DESC,
608 X_RESULT_TYPE,
609 X_RESULT,
610 X_SUBJECT_RESULT_YR,
611 X_MODE);
612 return;
613 end if;
614 close c1;
615 UPDATE_ROW (
616 X_ROWID ,
617 X_PERSON_ID,
618 X_OSE_SEQUENCE_NUMBER,
619 X_SEQUENCE_NUMBER,
620 X_SUBJECT_CD,
621 X_SUBJECT_DESC,
622 X_RESULT_TYPE,
623 X_RESULT,
624 X_SUBJECT_RESULT_YR,
625 X_MODE);
626 end ADD_ROW;
627
628 procedure DELETE_ROW (
629 X_ROWID in VARCHAR2
630 ) AS
631 begin
632
633
634 Before_DML(
635 p_action=>'DELETE',
636 x_rowid=> X_ROWID
637 );
638
639 delete from IGS_AD_OS_SEC_ED_SUB
640 where ROWID = X_ROWID;
641 if (sql%notfound) then
642 raise no_data_found;
643 end if;
644
645
646 After_DML(
647 p_action=>'DELETE',
648 x_rowid=> X_ROWID
649 );
650
651 end DELETE_ROW;
652
653 end IGS_AD_OS_SEC_ED_SUB_PKG;