[Home] [Help]
PACKAGE BODY: APPS.IGS_RE_THS_EXAM_HIST_PKG
Source
1 package body IGS_RE_THS_EXAM_HIST_PKG as
2 /* $Header: IGSRI19B.pls 115.5 2002/11/29 03:36:43 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RE_THS_EXAM_HIST_ALL%RowType;
6 new_references IGS_RE_THS_EXAM_HIST_ALL%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_ca_sequence_number IN NUMBER DEFAULT NULL,
13 x_the_sequence_number IN NUMBER DEFAULT NULL,
14 x_creation_dt IN DATE DEFAULT NULL,
15 x_hist_start_dt IN DATE DEFAULT NULL,
16 x_hist_end_dt IN DATE DEFAULT NULL,
17 x_hist_who IN NUMBER DEFAULT NULL,
18 x_submission_dt IN DATE DEFAULT NULL,
19 x_thesis_exam_type IN VARCHAR2 DEFAULT NULL,
20 x_thesis_panel_type IN VARCHAR2 DEFAULT NULL,
21 x_tracking_id IN NUMBER DEFAULT NULL,
22 x_thesis_result_cd IN VARCHAR2 DEFAULT NULL,
23 x_creation_date IN DATE DEFAULT NULL,
24 x_created_by IN NUMBER DEFAULT NULL,
25 x_last_update_date IN DATE DEFAULT NULL,
26 x_last_updated_by IN NUMBER DEFAULT NULL,
27 x_last_update_login IN NUMBER DEFAULT NULL ,
28 x_org_id IN NUMBER DEFAULT NULL
29 ) AS
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_RE_THS_EXAM_HIST_ALL
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 Open cur_old_ref_values;
43 Fetch cur_old_ref_values INTO old_references;
44 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
45 Close cur_old_ref_values;
46 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
47 IGS_GE_MSG_STACK.ADD;
48 App_Exception.Raise_Exception;
49 Return;
50 END IF;
51 Close cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.person_id := x_person_id;
55 new_references.ca_sequence_number := x_ca_sequence_number;
56 new_references.the_sequence_number := x_the_sequence_number;
57 new_references.creation_dt := x_creation_dt;
58 new_references.hist_start_dt := x_hist_start_dt;
59 new_references.hist_end_dt := x_hist_end_dt;
60 new_references.hist_who := x_hist_who;
61 new_references.submission_dt := x_submission_dt;
62 new_references.thesis_exam_type := x_thesis_exam_type;
63 new_references.thesis_panel_type := x_thesis_panel_type;
64 new_references.tracking_id := x_tracking_id;
65 new_references.thesis_result_cd := x_thesis_result_cd;
66 new_references.org_id := x_org_id;
67 IF (p_action = 'UPDATE') THEN
68 new_references.creation_date := old_references.creation_date;
69 new_references.created_by := old_references.created_by;
70 ELSE
71 new_references.creation_date := x_creation_date;
72 new_references.created_by := x_created_by;
73 END IF;
74 new_references.last_update_date := x_last_update_date;
75 new_references.last_updated_by := x_last_updated_by;
76 new_references.last_update_login := x_last_update_login;
77
78 END Set_Column_Values;
79
80 PROCEDURE Check_Constraints (
81 Column_Name in VARCHAR2 DEFAULT NULL ,
82 Column_Value in VARCHAR2 DEFAULT NULL
83 ) AS
84 BEGIN
85
86 IF Column_Name is null then
87 NULL;
88 ELSIF upper(Column_name) = 'CA_SEQUENCE_NUMBER' THEN
89 new_references.CA_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
90 ELSIF upper(Column_name) = 'THE_SEQUENCE_NUMBER' THEN
91 new_references.THE_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
92 ELSIF upper(Column_name) = 'HIST_WHO' THEN
93 new_references.HIST_WHO := COLUMN_VALUE ;
94 ELSIF upper(Column_name) = 'THESIS_EXAM_TYPE' THEN
95 new_references.THESIS_EXAM_TYPE := COLUMN_VALUE ;
96 ELSIF upper(Column_name) = 'THESIS_PANEL_TYPE' THEN
97 new_references.THESIS_PANEL_TYPE := COLUMN_VALUE ;
98 ELSIF upper(Column_name) = 'THESIS_RESULT_CD' THEN
99 new_references.THESIS_RESULT_CD := COLUMN_VALUE ;
100 END IF;
101
102 IF upper(column_name) = 'CA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
103 IF new_references.CA_SEQUENCE_NUMBER < 1 OR new_references.CA_SEQUENCE_NUMBER > 999999 then
104 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception ;
107 END IF;
108 END IF;
109 IF upper(column_name) = 'THE_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
110 IF new_references.THE_SEQUENCE_NUMBER < 1 OR new_references.THE_SEQUENCE_NUMBER > 999999 then
111 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
112 IGS_GE_MSG_STACK.ADD;
113 App_Exception.Raise_Exception ;
114 END IF;
115 END IF;
116
117 IF upper(column_name) = 'THESIS_EXAM_TYPE' OR COLUMN_NAME IS NULL THEN
118 IF new_references.THESIS_EXAM_TYPE <> upper(NEW_REFERENCES.THESIS_EXAM_TYPE) then
119 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception ;
122 END IF;
123 END IF;
124
125 IF upper(column_name) = 'THESIS_PANEL_TYPE' OR COLUMN_NAME IS NULL THEN
126 IF new_references.THESIS_PANEL_TYPE <> upper(NEW_REFERENCES.THESIS_PANEL_TYPE) then
127 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception ;
130 END IF;
131 END IF;
132
133 IF upper(column_name) = 'THESIS_RESULT_CD' OR COLUMN_NAME IS NULL THEN
134 IF new_references.THESIS_RESULT_CD <> upper(NEW_REFERENCES.THESIS_RESULT_CD) then
135 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception ;
138 END IF;
139 END IF;
140 END Check_Constraints ;
141
142
143 FUNCTION Get_PK_For_Validation (
144 x_person_id IN NUMBER,
145 x_ca_sequence_number IN NUMBER,
146 x_the_sequence_number IN NUMBER,
147 x_creation_dt IN DATE,
148 x_hist_start_dt IN DATE
149 ) RETURN BOOLEAN
150 AS
151
152 CURSOR cur_rowid IS
153 SELECT rowid
154 FROM IGS_RE_THS_EXAM_HIST_ALL
155 WHERE person_id = x_person_id
156 AND ca_sequence_number = x_ca_sequence_number
157 AND the_sequence_number = x_the_sequence_number
158 AND creation_dt = x_creation_dt
159 AND hist_start_dt = x_hist_start_dt
160 FOR UPDATE NOWAIT;
161
162 lv_rowid cur_rowid%RowType;
163
164 BEGIN
165
166 Open cur_rowid;
167 Fetch cur_rowid INTO lv_rowid;
168 IF (cur_rowid%FOUND) THEN
169 Close cur_rowid;
170 RETURN(TRUE);
171 ELSE
172 Close cur_rowid;
173 RETURN(FALSE);
174 END IF;
175
176 END Get_PK_For_Validation;
177
178 PROCEDURE Before_DML (
179 p_action IN VARCHAR2,
180 x_rowid IN VARCHAR2 DEFAULT NULL,
181 x_person_id IN NUMBER DEFAULT NULL,
182 x_ca_sequence_number IN NUMBER DEFAULT NULL,
183 x_the_sequence_number IN NUMBER DEFAULT NULL,
184 x_creation_dt IN DATE DEFAULT NULL,
185 x_hist_start_dt IN DATE DEFAULT NULL,
186 x_hist_end_dt IN DATE DEFAULT NULL,
187 x_hist_who IN NUMBER DEFAULT NULL,
188 x_submission_dt IN DATE DEFAULT NULL,
189 x_thesis_exam_type IN VARCHAR2 DEFAULT NULL,
190 x_thesis_panel_type IN VARCHAR2 DEFAULT NULL,
191 x_tracking_id IN NUMBER DEFAULT NULL,
192 x_thesis_result_cd IN VARCHAR2 DEFAULT NULL,
193 x_creation_date IN DATE DEFAULT NULL,
194 x_created_by IN NUMBER DEFAULT NULL,
195 x_last_update_date IN DATE DEFAULT NULL,
196 x_last_updated_by IN NUMBER DEFAULT NULL,
197 x_last_update_login IN NUMBER DEFAULT NULL,
198 x_org_id IN NUMBER DEFAULT NULL
199 ) AS
200 BEGIN
201
202 Set_Column_Values (
203 p_action,
204 x_rowid,
205 x_person_id,
206 x_ca_sequence_number,
207 x_the_sequence_number,
208 x_creation_dt,
209 x_hist_start_dt,
210 x_hist_end_dt,
211 x_hist_who,
212 x_submission_dt,
213 x_thesis_exam_type,
214 x_thesis_panel_type,
215 x_tracking_id,
216 x_thesis_result_cd,
217 x_creation_date,
218 x_created_by,
219 x_last_update_date,
220 x_last_updated_by,
221 x_last_update_login ,
222 x_org_id
223 );
224
225 IF (p_action = 'INSERT') THEN
226 -- Call all the procedures related to Before Insert.
227 IF Get_PK_For_Validation (
228 new_references.person_id,
229 new_references.ca_sequence_number,
230 new_references.the_sequence_number ,
231 new_references.creation_dt ,
232 new_references.hist_start_dt
233 ) THEN
234 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
235 IGS_GE_MSG_STACK.ADD;
236 App_Exception.Raise_Exception;
237 END IF;
238 Check_Constraints;
239 ELSIF (p_action = 'UPDATE') THEN
240 -- Call all the procedures related to Before Update.
241 Check_Constraints;
242 ELSIF (p_action = 'VALIDATE_INSERT') THEN
243 IF Get_PK_For_Validation (
244 new_references.person_id,
245 new_references.ca_sequence_number,
246 new_references.the_sequence_number ,
247 new_references.creation_dt ,
248 new_references.hist_start_dt
249 ) THEN
250 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254 Check_Constraints;
255 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
256 Check_Constraints;
257 END IF;
258 END Before_DML;
259
260 procedure INSERT_ROW (
261 X_ROWID in out NOCOPY VARCHAR2,
262 X_PERSON_ID in NUMBER,
263 X_CA_SEQUENCE_NUMBER in NUMBER,
264 X_THE_SEQUENCE_NUMBER in NUMBER,
265 X_CREATION_DT in DATE,
266 X_HIST_START_DT in DATE,
267 X_HIST_END_DT in DATE,
268 X_HIST_WHO in NUMBER,
269 X_SUBMISSION_DT in DATE,
270 X_THESIS_EXAM_TYPE in VARCHAR2,
271 X_THESIS_PANEL_TYPE in VARCHAR2,
272 X_TRACKING_ID in NUMBER,
273 X_THESIS_RESULT_CD in VARCHAR2,
274 X_MODE in VARCHAR2 default 'R',
275 X_ORG_ID in NUMBER
276 ) as
277 cursor C is select ROWID from IGS_RE_THS_EXAM_HIST_ALL
278 where PERSON_ID = X_PERSON_ID
279 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
280 and THE_SEQUENCE_NUMBER = X_THE_SEQUENCE_NUMBER
281 and CREATION_DT = X_CREATION_DT
282 and HIST_START_DT = X_HIST_START_DT;
283 X_LAST_UPDATE_DATE DATE;
284 X_LAST_UPDATED_BY NUMBER;
285 X_LAST_UPDATE_LOGIN NUMBER;
286 begin
287 X_LAST_UPDATE_DATE := SYSDATE;
288 if(X_MODE = 'I') then
289 X_LAST_UPDATED_BY := 1;
290 X_LAST_UPDATE_LOGIN := 0;
291 elsif (X_MODE = 'R') then
292 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
293 if X_LAST_UPDATED_BY is NULL then
294 X_LAST_UPDATED_BY := -1;
295 end if;
296 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
297 if X_LAST_UPDATE_LOGIN is NULL then
298 X_LAST_UPDATE_LOGIN := -1;
299 end if;
300 else
301 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
302 IGS_GE_MSG_STACK.ADD;
303 app_exception.raise_exception;
304 end if;
305
306 Before_DML (
307 p_action => 'INSERT',
308 x_rowid => X_ROWID,
309 x_person_id => X_PERSON_ID,
310 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
311 x_the_sequence_number => X_THE_SEQUENCE_NUMBER,
312 x_creation_dt => X_CREATION_DT,
313 x_hist_start_dt => X_HIST_START_DT,
314 x_hist_end_dt => X_HIST_END_DT,
315 x_hist_who => X_HIST_WHO,
316 x_submission_dt => X_SUBMISSION_DT,
317 x_thesis_exam_type => X_THESIS_EXAM_TYPE,
318 x_thesis_panel_type => X_THESIS_PANEL_TYPE,
319 x_tracking_id => X_TRACKING_ID,
320 x_thesis_result_cd => X_THESIS_RESULT_CD,
321 x_created_by => X_LAST_UPDATED_BY ,
322 x_creation_date => X_LAST_UPDATE_DATE,
323 x_last_updated_by => X_LAST_UPDATED_BY,
324 x_last_update_date => X_LAST_UPDATE_DATE,
325 x_last_update_login => X_LAST_UPDATE_LOGIN,
326 x_org_id => igs_ge_gen_003.get_org_id
327 );
328 insert into IGS_RE_THS_EXAM_HIST_ALL (
329 PERSON_ID,
330 CA_SEQUENCE_NUMBER,
331 THE_SEQUENCE_NUMBER,
332 CREATION_DT,
333 HIST_START_DT,
334 HIST_END_DT,
335 HIST_WHO,
336 SUBMISSION_DT,
337 THESIS_EXAM_TYPE,
338 THESIS_PANEL_TYPE,
339 TRACKING_ID,
340 THESIS_RESULT_CD,
341 CREATION_DATE,
342 CREATED_BY,
343 LAST_UPDATE_DATE,
344 LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN,
346 ORG_ID
347 ) values (
348 NEW_REFERENCES.PERSON_ID,
349 NEW_REFERENCES.CA_SEQUENCE_NUMBER,
350 NEW_REFERENCES.THE_SEQUENCE_NUMBER,
351 NEW_REFERENCES.CREATION_DT,
352 NEW_REFERENCES.HIST_START_DT,
353 NEW_REFERENCES.HIST_END_DT,
354 NEW_REFERENCES.HIST_WHO,
355 NEW_REFERENCES.SUBMISSION_DT,
356 NEW_REFERENCES.THESIS_EXAM_TYPE,
357 NEW_REFERENCES.THESIS_PANEL_TYPE,
358 NEW_REFERENCES.TRACKING_ID,
359 NEW_REFERENCES.THESIS_RESULT_CD,
360 X_LAST_UPDATE_DATE,
361 X_LAST_UPDATED_BY,
362 X_LAST_UPDATE_DATE,
363 X_LAST_UPDATED_BY,
364 X_LAST_UPDATE_LOGIN,
365 NEW_REFERENCES.ORG_ID
366 );
367
368 open c;
369 fetch c into X_ROWID;
370 if (c%notfound) then
371 close c;
372 raise no_data_found;
373 end if;
374 close c;
375
376 end INSERT_ROW;
377
378 procedure LOCK_ROW (
379 X_ROWID in VARCHAR2,
380 X_PERSON_ID in NUMBER,
381 X_CA_SEQUENCE_NUMBER in NUMBER,
382 X_THE_SEQUENCE_NUMBER in NUMBER,
383 X_CREATION_DT in DATE,
384 X_HIST_START_DT in DATE,
385 X_HIST_END_DT in DATE,
386 X_HIST_WHO in NUMBER,
387 X_SUBMISSION_DT in DATE,
388 X_THESIS_EXAM_TYPE in VARCHAR2,
389 X_THESIS_PANEL_TYPE in VARCHAR2,
390 X_TRACKING_ID in NUMBER,
391 X_THESIS_RESULT_CD in VARCHAR2
392 ) as
393 cursor c1 is select
394 HIST_END_DT,
395 HIST_WHO,
396 SUBMISSION_DT,
397 THESIS_EXAM_TYPE,
398 THESIS_PANEL_TYPE,
399 TRACKING_ID,
400 THESIS_RESULT_CD
401 from IGS_RE_THS_EXAM_HIST_ALL
402 where ROWID = X_ROWID
403 for update nowait;
404 tlinfo c1%rowtype;
405
406 begin
407 open c1 ;
408 fetch c1 into tlinfo ;
409 if (c1%notfound) then
410 close c1;
411 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
412 app_exception.raise_exception;
413 return;
414 end if;
415 close c1;
416
417 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
418 AND (tlinfo.HIST_WHO = X_HIST_WHO)
419 AND ((tlinfo.SUBMISSION_DT = X_SUBMISSION_DT)
420 OR ((tlinfo.SUBMISSION_DT is null)
421 AND (X_SUBMISSION_DT is null)))
422 AND ((tlinfo.THESIS_EXAM_TYPE = X_THESIS_EXAM_TYPE)
423 OR ((tlinfo.THESIS_EXAM_TYPE is null)
424 AND (X_THESIS_EXAM_TYPE is null)))
425 AND ((tlinfo.THESIS_PANEL_TYPE = X_THESIS_PANEL_TYPE)
426 OR ((tlinfo.THESIS_PANEL_TYPE is null)
427 AND (X_THESIS_PANEL_TYPE is null)))
428 AND ((tlinfo.TRACKING_ID = X_TRACKING_ID)
429 OR ((tlinfo.TRACKING_ID is null)
430 AND (X_TRACKING_ID is null)))
431 AND ((tlinfo.THESIS_RESULT_CD = X_THESIS_RESULT_CD)
432 OR ((tlinfo.THESIS_RESULT_CD is null)
433 AND (X_THESIS_RESULT_CD is null)))
434 ) then
435 null;
436 else
437 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
438 app_exception.raise_exception;
439 end if;
440 return;
441 end LOCK_ROW;
442
443 procedure UPDATE_ROW (
444 X_ROWID in VARCHAR2,
445 X_PERSON_ID in NUMBER,
446 X_CA_SEQUENCE_NUMBER in NUMBER,
447 X_THE_SEQUENCE_NUMBER in NUMBER,
448 X_CREATION_DT in DATE,
449 X_HIST_START_DT in DATE,
450 X_HIST_END_DT in DATE,
451 X_HIST_WHO in NUMBER,
452 X_SUBMISSION_DT in DATE,
453 X_THESIS_EXAM_TYPE in VARCHAR2,
454 X_THESIS_PANEL_TYPE in VARCHAR2,
455 X_TRACKING_ID in NUMBER,
456 X_THESIS_RESULT_CD in VARCHAR2,
457 X_MODE in VARCHAR2 default 'R'
458 ) as
459 X_LAST_UPDATE_DATE DATE;
460 X_LAST_UPDATED_BY NUMBER;
461 X_LAST_UPDATE_LOGIN NUMBER;
462 begin
463 X_LAST_UPDATE_DATE := SYSDATE;
464 if(X_MODE = 'I') then
465 X_LAST_UPDATED_BY := 1;
466 X_LAST_UPDATE_LOGIN := 0;
467 elsif (X_MODE = 'R') then
468 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
469 if X_LAST_UPDATED_BY is NULL then
470 X_LAST_UPDATED_BY := -1;
471 end if;
472 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
473 if X_LAST_UPDATE_LOGIN is NULL then
474 X_LAST_UPDATE_LOGIN := -1;
475 end if;
476 else
477 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
478 IGS_GE_MSG_STACK.ADD;
479 app_exception.raise_exception;
480 end if;
481
482 Before_DML (
483 p_action => 'UPDATE',
484 x_rowid => X_ROWID,
485 x_person_id => X_PERSON_ID,
486 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
487 x_the_sequence_number => X_THE_SEQUENCE_NUMBER,
488 x_creation_dt => X_CREATION_DT,
489 x_hist_start_dt => X_HIST_START_DT,
490 x_hist_end_dt => X_HIST_END_DT,
491 x_hist_who => X_HIST_WHO,
492 x_submission_dt => X_SUBMISSION_DT,
493 x_thesis_exam_type => X_THESIS_EXAM_TYPE,
494 x_thesis_panel_type => X_THESIS_PANEL_TYPE,
495 x_tracking_id => X_TRACKING_ID,
496 x_thesis_result_cd => X_THESIS_RESULT_CD,
497 x_created_by => X_LAST_UPDATED_BY ,
498 x_creation_date => X_LAST_UPDATE_DATE,
499 x_last_updated_by => X_LAST_UPDATED_BY,
500 x_last_update_date => X_LAST_UPDATE_DATE,
501 x_last_update_login => X_LAST_UPDATE_LOGIN
502 );
503
504 update IGS_RE_THS_EXAM_HIST_ALL set
505 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
506 HIST_WHO = NEW_REFERENCES.HIST_WHO,
507 SUBMISSION_DT = NEW_REFERENCES.SUBMISSION_DT,
508 THESIS_EXAM_TYPE = NEW_REFERENCES.THESIS_EXAM_TYPE,
509 THESIS_PANEL_TYPE = NEW_REFERENCES.THESIS_PANEL_TYPE,
510 TRACKING_ID = NEW_REFERENCES.TRACKING_ID,
511 THESIS_RESULT_CD = NEW_REFERENCES.THESIS_RESULT_CD,
512 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
513 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
514 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
515
516 where ROWID = X_ROWID
517 ;
518 if (sql%notfound) then
519 raise no_data_found;
520 end if;
521
522 end UPDATE_ROW;
523
524 procedure ADD_ROW (
525 X_ROWID in out NOCOPY VARCHAR2,
526 X_PERSON_ID in NUMBER,
527 X_CA_SEQUENCE_NUMBER in NUMBER,
528 X_THE_SEQUENCE_NUMBER in NUMBER,
529 X_CREATION_DT in DATE,
530 X_HIST_START_DT in DATE,
531 X_HIST_END_DT in DATE,
532 X_HIST_WHO in NUMBER,
533 X_SUBMISSION_DT in DATE,
534 X_THESIS_EXAM_TYPE in VARCHAR2,
535 X_THESIS_PANEL_TYPE in VARCHAR2,
536 X_TRACKING_ID in NUMBER,
537 X_THESIS_RESULT_CD in VARCHAR2,
538 X_MODE in VARCHAR2 default 'R',
539 X_ORG_ID in NUMBER
540 ) as
541 cursor c1 is select rowid from IGS_RE_THS_EXAM_HIST_ALL
542 where PERSON_ID = X_PERSON_ID
543 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
544 and THE_SEQUENCE_NUMBER = X_THE_SEQUENCE_NUMBER
545 and CREATION_DT = X_CREATION_DT
546 and HIST_START_DT = X_HIST_START_DT
547 ;
548 begin
549 open c1;
550 fetch c1 into X_ROWID;
551 if (c1%notfound) then
552 close c1;
553 INSERT_ROW (
554 X_ROWID,
555 X_PERSON_ID,
556 X_CA_SEQUENCE_NUMBER,
557 X_THE_SEQUENCE_NUMBER,
558 X_CREATION_DT,
559 X_HIST_START_DT,
560 X_HIST_END_DT,
561 X_HIST_WHO,
562 X_SUBMISSION_DT,
563 X_THESIS_EXAM_TYPE,
564 X_THESIS_PANEL_TYPE,
565 X_TRACKING_ID,
566 X_THESIS_RESULT_CD,
567 X_MODE,
568 X_ORG_ID);
569 return;
570 end if;
571 close c1;
572 UPDATE_ROW (
573 X_ROWID,
574 X_PERSON_ID,
575 X_CA_SEQUENCE_NUMBER,
576 X_THE_SEQUENCE_NUMBER,
577 X_CREATION_DT,
578 X_HIST_START_DT,
579 X_HIST_END_DT,
580 X_HIST_WHO,
581 X_SUBMISSION_DT,
582 X_THESIS_EXAM_TYPE,
583 X_THESIS_PANEL_TYPE,
584 X_TRACKING_ID,
585 X_THESIS_RESULT_CD,
586 X_MODE);
587 end ADD_ROW;
588
589 procedure DELETE_ROW (
590 X_ROWID in VARCHAR2
591 ) as
592 begin
593
594 Before_DML (
595 p_action => 'DELETE',
596 x_rowid => X_ROWID
597 );
598
599 delete from IGS_RE_THS_EXAM_HIST_ALL
600 where ROWID = X_ROWID;
601
602 if (sql%notfound) then
603 raise no_data_found;
604 end if;
605
606 end DELETE_ROW;
607
608 end IGS_RE_THS_EXAM_HIST_PKG;