[Home] [Help]
PACKAGE BODY: APPS.IGS_RE_THESIS_HIST_PKG
Source
1 package body IGS_RE_THESIS_HIST_PKG as
2 /* $Header: IGSRI17B.pls 115.6 2002/11/29 03:36:11 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_RE_THESIS_HIST_ALL%RowType;
6 new_references IGS_RE_THESIS_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_sequence_number IN NUMBER DEFAULT NULL,
14 x_hist_start_dt IN DATE DEFAULT NULL,
15 x_hist_end_dt IN DATE DEFAULT NULL,
16 x_hist_who IN NUMBER DEFAULT NULL,
17 x_title IN VARCHAR2 DEFAULT NULL,
18 x_final_title_ind IN VARCHAR2 DEFAULT NULL,
19 x_short_title IN VARCHAR2 DEFAULT NULL,
20 x_abbreviated_title IN VARCHAR2 DEFAULT NULL,
21 x_thesis_result_cd IN VARCHAR2 DEFAULT NULL,
22 x_expected_submission_dt IN DATE DEFAULT NULL,
23 x_date_of_library_lodgement IN DATE DEFAULT NULL,
24 x_library_catalogue_number IN VARCHAR2 DEFAULT NULL,
25 x_embargo_expiry_dt IN DATE DEFAULT NULL,
26 x_thesis_format IN VARCHAR2 DEFAULT NULL,
27 x_logical_delete_dt IN DATE DEFAULT NULL,
28 x_embargo_details IN VARCHAR2 DEFAULT NULL,
29 x_thesis_topic IN VARCHAR2 DEFAULT NULL,
30 x_citation IN VARCHAR2 DEFAULT NULL,
31 x_comments IN VARCHAR2 DEFAULT NULL,
32 x_creation_date IN DATE DEFAULT NULL,
33 x_created_by IN NUMBER DEFAULT NULL,
34 x_last_update_date IN DATE DEFAULT NULL,
35 x_last_updated_by IN NUMBER DEFAULT NULL,
36 x_last_update_login IN NUMBER DEFAULT NULL ,
37 x_org_id IN NUMBER DEFAULT NULL
38 ) AS
39
40 CURSOR cur_old_ref_values IS
41 SELECT *
42 FROM IGS_RE_THESIS_HIST_ALL
43 WHERE rowid = x_rowid;
44
45 BEGIN
46
47 l_rowid := x_rowid;
48
49 -- Code for setting the Old and New Reference Values.
50 -- Populate Old Values.
51 Open cur_old_ref_values;
52 Fetch cur_old_ref_values INTO old_references;
53 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
54 Close cur_old_ref_values;
55 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
56 IGS_GE_MSG_STACK.ADD;
57 App_Exception.Raise_Exception;
58 Return;
59 END IF;
60 Close cur_old_ref_values;
61
62 -- Populate New Values.
63 new_references.person_id := x_person_id;
64 new_references.ca_sequence_number := x_ca_sequence_number;
65 new_references.sequence_number := x_sequence_number;
66 new_references.hist_start_dt := x_hist_start_dt;
67 new_references.hist_end_dt := x_hist_end_dt;
68 new_references.hist_who := x_hist_who;
69 new_references.title := x_title;
70 new_references.final_title_ind := x_final_title_ind;
71 new_references.short_title := x_short_title;
72 new_references.abbreviated_title := x_abbreviated_title;
73 new_references.thesis_result_cd := x_thesis_result_cd;
74 new_references.expected_submission_dt := x_expected_submission_dt;
75 new_references.date_of_library_lodgement := x_date_of_library_lodgement;
76 new_references.library_catalogue_number := x_library_catalogue_number;
77 new_references.embargo_expiry_dt := x_embargo_expiry_dt;
78 new_references.thesis_format := x_thesis_format;
79 new_references.logical_delete_dt := x_logical_delete_dt;
80 new_references.embargo_details := x_embargo_details;
81 new_references.thesis_topic := x_thesis_topic;
82 new_references.citation := x_citation;
83 new_references.comments := x_comments;
84 new_references.org_id := x_org_id;
85 IF (p_action = 'UPDATE') THEN
86 new_references.creation_date := old_references.creation_date;
87 new_references.created_by := old_references.created_by;
88 ELSE
89 new_references.creation_date := x_creation_date;
90 new_references.created_by := x_created_by;
91 END IF;
92 new_references.last_update_date := x_last_update_date;
93 new_references.last_updated_by := x_last_updated_by;
94 new_references.last_update_login := x_last_update_login;
95
96 END Set_Column_Values;
97
98 PROCEDURE Check_Constraints (
99 Column_Name in VARCHAR2 DEFAULT NULL ,
100 Column_Value in VARCHAR2 DEFAULT NULL
101 ) AS
102 BEGIN
103
104 IF Column_Name is null then
105 NULL;
106 ELSIF upper(Column_name) = 'CA_SEQUENCE_NUMBER' THEN
107 new_references.CA_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
108 ELSIF upper(Column_name) = 'FINAL_TITLE_IND' THEN
109 new_references.FINAL_TITLE_IND := COLUMN_VALUE ;
110 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' THEN
111 new_references.SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
112 ELSIF upper(Column_name) = 'LIBRARY_CATALOGUE_NUMBER' THEN
113 new_references.LIBRARY_CATALOGUE_NUMBER := COLUMN_VALUE ;
114 ELSIF upper(Column_name) = 'THESIS_FORMAT' THEN
115 new_references.THESIS_FORMAT := COLUMN_VALUE ;
116 ELSIF upper(Column_name) = 'THESIS_RESULT_CD' THEN
117 new_references.THESIS_RESULT_CD := COLUMN_VALUE ;
118 END IF;
119
120 IF upper(column_name) = 'CA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
121 IF new_references.CA_SEQUENCE_NUMBER < 1 OR new_references.CA_SEQUENCE_NUMBER > 999999 then
122 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception ;
125 END IF;
126 END IF;
127 IF upper(column_name) = 'FINAL_TITLE_IND' OR COLUMN_NAME IS NULL THEN
128 IF new_references.FINAL_TITLE_IND <> upper(NEW_REFERENCES.FINAL_TITLE_IND) OR
129 new_references.FINAL_TITLE_IND NOT IN ('Y', 'N') 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) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
136 IF new_references.SEQUENCE_NUMBER < 1 OR new_references.SEQUENCE_NUMBER > 999999 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) = 'LIBRARY_CATALOGUE_NUMBER' OR COLUMN_NAME IS NULL THEN
143 IF new_references.LIBRARY_CATALOGUE_NUMBER <> NEW_REFERENCES.LIBRARY_CATALOGUE_NUMBER 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)= 'THESIS_FORMAT' OR COLUMN_NAME IS NULL THEN
150 IF new_references.THESIS_FORMAT <> NEW_REFERENCES.THESIS_FORMAT 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) = 'THESIS_RESULT_CD' OR COLUMN_NAME IS NULL THEN
157 IF new_references.THESIS_RESULT_CD <> upper(NEW_REFERENCES.THESIS_RESULT_CD) 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 END Check_Constraints ;
164
165
166 FUNCTION Get_PK_For_Validation (
167 x_person_id IN NUMBER,
168 x_ca_sequence_number IN NUMBER,
169 x_sequence_number IN NUMBER,
170 x_hist_start_dt IN DATE
171 )
172 RETURN BOOLEAN
173 AS
174
175 CURSOR cur_rowid IS
176 SELECT rowid
177 FROM IGS_RE_THESIS_HIST_ALL
178 WHERE person_id = x_person_id
179 AND ca_sequence_number = x_ca_sequence_number
180 AND sequence_number = x_sequence_number
181 AND hist_start_dt = x_hist_start_dt
182 FOR UPDATE NOWAIT;
183
184 lv_rowid cur_rowid%RowType;
185
186 BEGIN
187
188 Open cur_rowid;
189 Fetch cur_rowid INTO lv_rowid;
190 IF (cur_rowid%FOUND) THEN
191 Close cur_rowid;
192 RETURN(TRUE);
193 ELSE
194 Close cur_rowid;
195 RETURN(FALSE);
196 END IF;
197
198 END Get_PK_For_Validation;
199
200 PROCEDURE Before_DML (
201 p_action IN VARCHAR2,
202 x_rowid IN VARCHAR2 DEFAULT NULL,
203 x_person_id IN NUMBER DEFAULT NULL,
204 x_ca_sequence_number IN NUMBER DEFAULT NULL,
205 x_sequence_number IN NUMBER DEFAULT NULL,
206 x_hist_start_dt IN DATE DEFAULT NULL,
207 x_hist_end_dt IN DATE DEFAULT NULL,
208 x_hist_who IN NUMBER DEFAULT NULL,
209 x_title IN VARCHAR2 DEFAULT NULL,
210 x_final_title_ind IN VARCHAR2 DEFAULT NULL,
211 x_short_title IN VARCHAR2 DEFAULT NULL,
212 x_abbreviated_title IN VARCHAR2 DEFAULT NULL,
213 x_thesis_result_cd IN VARCHAR2 DEFAULT NULL,
214 x_expected_submission_dt IN DATE DEFAULT NULL,
215 x_date_of_library_lodgement IN DATE DEFAULT NULL,
216 x_library_catalogue_number IN VARCHAR2 DEFAULT NULL,
217 x_embargo_expiry_dt IN DATE DEFAULT NULL,
218 x_thesis_format IN VARCHAR2 DEFAULT NULL,
219 x_logical_delete_dt IN DATE DEFAULT NULL,
220 x_embargo_details IN VARCHAR2 DEFAULT NULL,
221 x_thesis_topic IN VARCHAR2 DEFAULT NULL,
222 x_citation IN VARCHAR2 DEFAULT NULL,
223 x_comments IN VARCHAR2 DEFAULT NULL,
224 x_creation_date IN DATE DEFAULT NULL,
225 x_created_by IN NUMBER DEFAULT NULL,
226 x_last_update_date IN DATE DEFAULT NULL,
227 x_last_updated_by IN NUMBER DEFAULT NULL,
228 x_last_update_login IN NUMBER DEFAULT NULL,
229 x_org_id IN NUMBER DEFAULT NULL
230 ) AS
231 BEGIN
232
233 Set_Column_Values (
234 p_action,
235 x_rowid,
236 x_person_id,
237 x_ca_sequence_number,
238 x_sequence_number,
239 x_hist_start_dt,
240 x_hist_end_dt,
241 x_hist_who,
242 x_title,
243 x_final_title_ind,
244 x_short_title,
245 x_abbreviated_title,
246 x_thesis_result_cd,
247 x_expected_submission_dt,
248 x_date_of_library_lodgement,
249 x_library_catalogue_number,
250 x_embargo_expiry_dt,
251 x_thesis_format,
252 x_logical_delete_dt,
253 x_embargo_details,
254 x_thesis_topic,
255 x_citation,
256 x_comments,
257 x_creation_date,
258 x_created_by,
259 x_last_update_date,
260 x_last_updated_by,
261 x_last_update_login ,
262 x_org_id
263 );
264
265 IF (p_action = 'INSERT') THEN
266 -- Call all the procedures related to Before Insert.
267 IF Get_PK_For_Validation (
268 new_references.person_id,
269 new_references.ca_sequence_number,
270 new_references.sequence_number,
271 new_references.hist_start_dt
272 ) THEN
273 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
274 IGS_GE_MSG_STACK.ADD;
275 App_Exception.Raise_Exception;
276 END IF;
277 Check_Constraints;
278 ELSIF (p_action = 'UPDATE') THEN
279 -- Call all the procedures related to Before Update.
280 Check_Constraints;
281 ELSIF (p_action = 'VALIDATE_INSERT') THEN
282 IF Get_PK_For_Validation (
283 new_references.person_id,
284 new_references.ca_sequence_number,
285 new_references.sequence_number,
286 new_references.hist_start_dt
287 ) THEN
288 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
289 IGS_GE_MSG_STACK.ADD;
290 App_Exception.Raise_Exception;
291 END IF;
292 Check_Constraints;
293 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
294 Check_Constraints;
295 END IF;
296 END Before_DML;
297
298 procedure INSERT_ROW (
299 X_ROWID in out NOCOPY VARCHAR2,
300 X_PERSON_ID in NUMBER,
301 X_CA_SEQUENCE_NUMBER in NUMBER,
302 X_SEQUENCE_NUMBER in NUMBER,
303 X_HIST_START_DT in DATE,
304 X_HIST_END_DT in DATE,
305 X_HIST_WHO in NUMBER,
306 X_TITLE in VARCHAR2,
307 X_FINAL_TITLE_IND in VARCHAR2,
308 X_SHORT_TITLE in VARCHAR2,
309 X_ABBREVIATED_TITLE in VARCHAR2,
310 X_THESIS_RESULT_CD in VARCHAR2,
311 X_EXPECTED_SUBMISSION_DT in DATE,
312 X_DATE_OF_LIBRARY_LODGEMENT in DATE,
313 X_LIBRARY_CATALOGUE_NUMBER in VARCHAR2,
314 X_EMBARGO_EXPIRY_DT in DATE,
315 X_THESIS_FORMAT in VARCHAR2,
316 X_LOGICAL_DELETE_DT in DATE,
317 X_EMBARGO_DETAILS in VARCHAR2,
318 X_THESIS_TOPIC in VARCHAR2,
319 X_CITATION in VARCHAR2,
320 X_COMMENTS in VARCHAR2,
321 X_MODE in VARCHAR2 default 'R',
322 X_ORG_ID in NUMBER
323 ) as
324 cursor C is select ROWID from IGS_RE_THESIS_HIST_ALL
325 where PERSON_ID = X_PERSON_ID
326 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
327 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
328 and HIST_START_DT = X_HIST_START_DT;
329 X_LAST_UPDATE_DATE DATE;
330 X_LAST_UPDATED_BY NUMBER;
331 X_LAST_UPDATE_LOGIN NUMBER;
332 begin
333 X_LAST_UPDATE_DATE := SYSDATE;
334 if(X_MODE = 'I') then
335 X_LAST_UPDATED_BY := 1;
336 X_LAST_UPDATE_LOGIN := 0;
337 elsif (X_MODE = 'R') then
338 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
339 if X_LAST_UPDATED_BY is NULL then
340 X_LAST_UPDATED_BY := -1;
341 end if;
342 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
343 if X_LAST_UPDATE_LOGIN is NULL then
344 X_LAST_UPDATE_LOGIN := -1;
345 end if;
346 else
347 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
348 IGS_GE_MSG_STACK.ADD;
349 app_exception.raise_exception;
350 end if;
351
352 Before_DML (
353 p_action => 'INSERT',
354 x_rowid => X_ROWID,
355 x_person_id => X_PERSON_ID,
356 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
357 x_sequence_number => X_SEQUENCE_NUMBER,
358 x_hist_start_dt => X_HIST_START_DT,
359 x_hist_end_dt => X_HIST_END_DT,
360 x_hist_who => X_HIST_WHO,
361 x_title => X_TITLE,
362 x_final_title_ind => X_FINAL_TITLE_IND,
363 x_short_title => X_SHORT_TITLE,
364 x_abbreviated_title => X_ABBREVIATED_TITLE,
365 x_thesis_result_cd => X_THESIS_RESULT_CD,
366 x_expected_submission_dt => X_EXPECTED_SUBMISSION_DT,
367 x_date_of_library_lodgement => X_DATE_OF_LIBRARY_LODGEMENT,
368 x_library_catalogue_number => X_LIBRARY_CATALOGUE_NUMBER,
369 x_embargo_expiry_dt => X_EMBARGO_EXPIRY_DT,
370 x_thesis_format => X_THESIS_FORMAT,
371 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
372 x_embargo_details => X_EMBARGO_DETAILS,
373 x_thesis_topic => X_THESIS_TOPIC,
374 x_citation => X_CITATION,
375 x_comments => X_COMMENTS,
376 x_created_by => X_LAST_UPDATED_BY ,
377 x_creation_date => X_LAST_UPDATE_DATE,
378 x_last_updated_by => X_LAST_UPDATED_BY,
379 x_last_update_date => X_LAST_UPDATE_DATE,
380 x_last_update_login => X_LAST_UPDATE_LOGIN,
381 x_org_id => igs_ge_gen_003.get_org_id
382 );
383
384
385 insert into IGS_RE_THESIS_HIST_ALL (
386 PERSON_ID,
387 CA_SEQUENCE_NUMBER,
388 SEQUENCE_NUMBER,
389 HIST_START_DT,
390 HIST_END_DT,
391 HIST_WHO,
392 TITLE,
393 FINAL_TITLE_IND,
394 SHORT_TITLE,
395 ABBREVIATED_TITLE,
396 THESIS_RESULT_CD,
397 EXPECTED_SUBMISSION_DT,
398 DATE_OF_LIBRARY_LODGEMENT,
399 LIBRARY_CATALOGUE_NUMBER,
400 EMBARGO_EXPIRY_DT,
401 THESIS_FORMAT,
402 LOGICAL_DELETE_DT,
403 EMBARGO_DETAILS,
404 THESIS_TOPIC,
405 CITATION,
406 COMMENTS,
407 CREATION_DATE,
408 CREATED_BY,
409 LAST_UPDATE_DATE,
410 LAST_UPDATED_BY,
411 LAST_UPDATE_LOGIN,
412 ORG_ID
413 ) values (
414 NEW_REFERENCES.PERSON_ID,
415 NEW_REFERENCES.CA_SEQUENCE_NUMBER,
416 NEW_REFERENCES.SEQUENCE_NUMBER,
417 NEW_REFERENCES.HIST_START_DT,
418 NEW_REFERENCES.HIST_END_DT,
419 NEW_REFERENCES.HIST_WHO,
420 NEW_REFERENCES.TITLE,
421 NEW_REFERENCES.FINAL_TITLE_IND,
422 NEW_REFERENCES.SHORT_TITLE,
423 NEW_REFERENCES.ABBREVIATED_TITLE,
424 NEW_REFERENCES.THESIS_RESULT_CD,
425 NEW_REFERENCES.EXPECTED_SUBMISSION_DT,
426 NEW_REFERENCES.DATE_OF_LIBRARY_LODGEMENT,
427 NEW_REFERENCES.LIBRARY_CATALOGUE_NUMBER,
428 NEW_REFERENCES.EMBARGO_EXPIRY_DT,
429 NEW_REFERENCES.THESIS_FORMAT,
430 NEW_REFERENCES.LOGICAL_DELETE_DT,
431 NEW_REFERENCES.EMBARGO_DETAILS,
432 NEW_REFERENCES.THESIS_TOPIC,
433 NEW_REFERENCES.CITATION,
434 NEW_REFERENCES.COMMENTS,
435 X_LAST_UPDATE_DATE,
436 X_LAST_UPDATED_BY,
437 X_LAST_UPDATE_DATE,
438 X_LAST_UPDATED_BY,
439 X_LAST_UPDATE_LOGIN,
440 NEW_REFERENCES.ORG_ID
441 );
442
443 open c;
444 fetch c into X_ROWID;
445 if (c%notfound) then
446 close c;
447 raise no_data_found;
448 end if;
449 close c;
450 end INSERT_ROW;
451
452 procedure LOCK_ROW (
453 X_ROWID in VARCHAR2,
454 X_PERSON_ID in NUMBER,
455 X_CA_SEQUENCE_NUMBER in NUMBER,
456 X_SEQUENCE_NUMBER in NUMBER,
457 X_HIST_START_DT in DATE,
458 X_HIST_END_DT in DATE,
459 X_HIST_WHO in NUMBER,
460 X_TITLE in VARCHAR2,
461 X_FINAL_TITLE_IND in VARCHAR2,
462 X_SHORT_TITLE in VARCHAR2,
463 X_ABBREVIATED_TITLE in VARCHAR2,
464 X_THESIS_RESULT_CD in VARCHAR2,
465 X_EXPECTED_SUBMISSION_DT in DATE,
466 X_DATE_OF_LIBRARY_LODGEMENT in DATE,
467 X_LIBRARY_CATALOGUE_NUMBER in VARCHAR2,
468 X_EMBARGO_EXPIRY_DT in DATE,
469 X_THESIS_FORMAT in VARCHAR2,
470 X_LOGICAL_DELETE_DT in DATE,
471 X_EMBARGO_DETAILS in VARCHAR2,
472 X_THESIS_TOPIC in VARCHAR2,
473 X_CITATION in VARCHAR2,
474 X_COMMENTS in VARCHAR2
475 ) as
476 cursor c1 is select
477 HIST_END_DT,
478 HIST_WHO,
479 TITLE,
480 FINAL_TITLE_IND,
481 SHORT_TITLE,
482 ABBREVIATED_TITLE,
483 THESIS_RESULT_CD,
484 EXPECTED_SUBMISSION_DT,
485 DATE_OF_LIBRARY_LODGEMENT,
486 LIBRARY_CATALOGUE_NUMBER,
487 EMBARGO_EXPIRY_DT,
488 THESIS_FORMAT,
489 LOGICAL_DELETE_DT,
490 EMBARGO_DETAILS,
491 THESIS_TOPIC,
492 CITATION,
493 COMMENTS
494 from IGS_RE_THESIS_HIST_ALL
495 where ROWID = X_ROWID
496 for update nowait;
497 tlinfo c1%rowtype;
498
499 begin
500 open c1;
501 fetch c1 into tlinfo;
502 if (c1%notfound) then
503 close c1;
504 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
505 app_exception.raise_exception;
506 return;
507 end if;
508 close c1;
509
510 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
511 AND (tlinfo.HIST_WHO = X_HIST_WHO)
512 AND ((tlinfo.TITLE = X_TITLE)
513 OR ((tlinfo.TITLE is null)
514 AND (X_TITLE is null)))
515 AND ((tlinfo.FINAL_TITLE_IND = X_FINAL_TITLE_IND)
516 OR ((tlinfo.FINAL_TITLE_IND is null)
517 AND (X_FINAL_TITLE_IND is null)))
518 AND ((tlinfo.SHORT_TITLE = X_SHORT_TITLE)
519 OR ((tlinfo.SHORT_TITLE is null)
520 AND (X_SHORT_TITLE is null)))
521 AND ((tlinfo.ABBREVIATED_TITLE = X_ABBREVIATED_TITLE)
522 OR ((tlinfo.ABBREVIATED_TITLE is null)
523 AND (X_ABBREVIATED_TITLE is null)))
524 AND ((tlinfo.THESIS_RESULT_CD = X_THESIS_RESULT_CD)
525 OR ((tlinfo.THESIS_RESULT_CD is null)
526 AND (X_THESIS_RESULT_CD is null)))
527 AND ((tlinfo.EXPECTED_SUBMISSION_DT = X_EXPECTED_SUBMISSION_DT)
528 OR ((tlinfo.EXPECTED_SUBMISSION_DT is null)
529 AND (X_EXPECTED_SUBMISSION_DT is null)))
530 AND ((tlinfo.DATE_OF_LIBRARY_LODGEMENT = X_DATE_OF_LIBRARY_LODGEMENT)
531 OR ((tlinfo.DATE_OF_LIBRARY_LODGEMENT is null)
532 AND (X_DATE_OF_LIBRARY_LODGEMENT is null)))
533 AND ((tlinfo.LIBRARY_CATALOGUE_NUMBER = X_LIBRARY_CATALOGUE_NUMBER)
534 OR ((tlinfo.LIBRARY_CATALOGUE_NUMBER is null)
535 AND (X_LIBRARY_CATALOGUE_NUMBER is null)))
536 AND ((tlinfo.EMBARGO_EXPIRY_DT = X_EMBARGO_EXPIRY_DT)
537 OR ((tlinfo.EMBARGO_EXPIRY_DT is null)
538 AND (X_EMBARGO_EXPIRY_DT is null)))
539 AND ((tlinfo.THESIS_FORMAT = X_THESIS_FORMAT)
540 OR ((tlinfo.THESIS_FORMAT is null)
541 AND (X_THESIS_FORMAT is null)))
542 AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
543 OR ((tlinfo.LOGICAL_DELETE_DT is null)
544 AND (X_LOGICAL_DELETE_DT is null)))
545 AND ((tlinfo.EMBARGO_DETAILS = X_EMBARGO_DETAILS)
546 OR ((tlinfo.EMBARGO_DETAILS is null)
547 AND (X_EMBARGO_DETAILS is null)))
548 AND ((tlinfo.THESIS_TOPIC = X_THESIS_TOPIC)
549 OR ((tlinfo.THESIS_TOPIC is null)
550 AND (X_THESIS_TOPIC is null)))
551 AND ((tlinfo.CITATION = X_CITATION)
552 OR ((tlinfo.CITATION is null)
553 AND (X_CITATION is null)))
554 AND ((tlinfo.COMMENTS = X_COMMENTS)
555 OR ((tlinfo.COMMENTS is null)
556 AND (X_COMMENTS is null)))
557
558 ) then
559 null;
560 else
561 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
562 app_exception.raise_exception;
563 end if;
564 return;
565 end LOCK_ROW;
566
567 procedure UPDATE_ROW (
568 X_ROWID in VARCHAR2,
569 X_PERSON_ID in NUMBER,
570 X_CA_SEQUENCE_NUMBER in NUMBER,
571 X_SEQUENCE_NUMBER in NUMBER,
572 X_HIST_START_DT in DATE,
573 X_HIST_END_DT in DATE,
574 X_HIST_WHO in NUMBER,
575 X_TITLE in VARCHAR2,
576 X_FINAL_TITLE_IND in VARCHAR2,
577 X_SHORT_TITLE in VARCHAR2,
578 X_ABBREVIATED_TITLE in VARCHAR2,
579 X_THESIS_RESULT_CD in VARCHAR2,
580 X_EXPECTED_SUBMISSION_DT in DATE,
581 X_DATE_OF_LIBRARY_LODGEMENT in DATE,
582 X_LIBRARY_CATALOGUE_NUMBER in VARCHAR2,
583 X_EMBARGO_EXPIRY_DT in DATE,
584 X_THESIS_FORMAT in VARCHAR2,
585 X_LOGICAL_DELETE_DT in DATE,
586 X_EMBARGO_DETAILS in VARCHAR2,
587 X_THESIS_TOPIC in VARCHAR2,
588 X_CITATION in VARCHAR2,
589 X_COMMENTS in VARCHAR2,
590 X_MODE in VARCHAR2 default 'R'
591 ) as
592 X_LAST_UPDATE_DATE DATE;
593 X_LAST_UPDATED_BY NUMBER;
594 X_LAST_UPDATE_LOGIN NUMBER;
595 begin
596 X_LAST_UPDATE_DATE := SYSDATE;
597 if(X_MODE = 'I') then
598 X_LAST_UPDATED_BY := 1;
599 X_LAST_UPDATE_LOGIN := 0;
600 elsif (X_MODE = 'R') then
601 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
602 if X_LAST_UPDATED_BY is NULL then
603 X_LAST_UPDATED_BY := -1;
604 end if;
605 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
606 if X_LAST_UPDATE_LOGIN is NULL then
607 X_LAST_UPDATE_LOGIN := -1;
608 end if;
609 else
610 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
611 IGS_GE_MSG_STACK.ADD;
612 app_exception.raise_exception;
613 end if;
614
615 Before_DML (
616 p_action => 'UPDATE',
617 x_rowid => X_ROWID,
618 x_person_id => X_PERSON_ID,
619 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
620 x_sequence_number => X_SEQUENCE_NUMBER,
621 x_hist_start_dt => X_HIST_START_DT,
622 x_hist_end_dt => X_HIST_END_DT,
623 x_hist_who => X_HIST_WHO,
624 x_title => X_TITLE,
625 x_final_title_ind => X_FINAL_TITLE_IND,
626 x_short_title => X_SHORT_TITLE,
627 x_abbreviated_title => X_ABBREVIATED_TITLE,
628 x_thesis_result_cd => X_THESIS_RESULT_CD,
629 x_expected_submission_dt => X_EXPECTED_SUBMISSION_DT,
630 x_date_of_library_lodgement => X_DATE_OF_LIBRARY_LODGEMENT,
631 x_library_catalogue_number => X_LIBRARY_CATALOGUE_NUMBER,
632 x_embargo_expiry_dt => X_EMBARGO_EXPIRY_DT,
633 x_thesis_format => X_THESIS_FORMAT,
634 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
635 x_embargo_details => X_EMBARGO_DETAILS,
636 x_thesis_topic => X_THESIS_TOPIC,
637 x_citation => X_CITATION,
638 x_comments => X_COMMENTS,
639 x_created_by => X_LAST_UPDATED_BY ,
640 x_creation_date => X_LAST_UPDATE_DATE,
641 x_last_updated_by => X_LAST_UPDATED_BY,
642 x_last_update_date => X_LAST_UPDATE_DATE,
643 x_last_update_login => X_LAST_UPDATE_LOGIN
644 );
645
646 update IGS_RE_THESIS_HIST_ALL set
647 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
648 HIST_WHO = NEW_REFERENCES.HIST_WHO,
649 TITLE = NEW_REFERENCES.TITLE,
650 FINAL_TITLE_IND = NEW_REFERENCES.FINAL_TITLE_IND,
651 SHORT_TITLE = NEW_REFERENCES.SHORT_TITLE,
652 ABBREVIATED_TITLE = NEW_REFERENCES.ABBREVIATED_TITLE,
653 THESIS_RESULT_CD = NEW_REFERENCES.THESIS_RESULT_CD,
654 EXPECTED_SUBMISSION_DT = NEW_REFERENCES.EXPECTED_SUBMISSION_DT,
655 DATE_OF_LIBRARY_LODGEMENT = NEW_REFERENCES.DATE_OF_LIBRARY_LODGEMENT,
656 LIBRARY_CATALOGUE_NUMBER = NEW_REFERENCES.LIBRARY_CATALOGUE_NUMBER,
657 EMBARGO_EXPIRY_DT = NEW_REFERENCES.EMBARGO_EXPIRY_DT,
658 THESIS_FORMAT = NEW_REFERENCES.THESIS_FORMAT,
659 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
660 EMBARGO_DETAILS = NEW_REFERENCES.EMBARGO_DETAILS,
661 THESIS_TOPIC = NEW_REFERENCES.THESIS_TOPIC,
662 CITATION = NEW_REFERENCES.CITATION,
663 COMMENTS = NEW_REFERENCES.COMMENTS,
664 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
665 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
666 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
667 where ROWID = X_ROWID
668 ;
669 if (sql%notfound) then
670 raise no_data_found;
671 end if;
672
673 end UPDATE_ROW;
674
675 procedure ADD_ROW (
676 X_ROWID in out NOCOPY VARCHAR2,
677 X_PERSON_ID in NUMBER,
678 X_CA_SEQUENCE_NUMBER in NUMBER,
679 X_SEQUENCE_NUMBER in NUMBER,
680 X_HIST_START_DT in DATE,
681 X_HIST_END_DT in DATE,
682 X_HIST_WHO in NUMBER,
683 X_TITLE in VARCHAR2,
684 X_FINAL_TITLE_IND in VARCHAR2,
685 X_SHORT_TITLE in VARCHAR2,
686 X_ABBREVIATED_TITLE in VARCHAR2,
687 X_THESIS_RESULT_CD in VARCHAR2,
688 X_EXPECTED_SUBMISSION_DT in DATE,
689 X_DATE_OF_LIBRARY_LODGEMENT in DATE,
690 X_LIBRARY_CATALOGUE_NUMBER in VARCHAR2,
691 X_EMBARGO_EXPIRY_DT in DATE,
692 X_THESIS_FORMAT in VARCHAR2,
693 X_LOGICAL_DELETE_DT in DATE,
694 X_EMBARGO_DETAILS in VARCHAR2,
695 X_THESIS_TOPIC in VARCHAR2,
696 X_CITATION in VARCHAR2,
697 X_COMMENTS in VARCHAR2,
698 X_MODE in VARCHAR2 default 'R',
699 X_ORG_ID in NUMBER
700 ) as
701 cursor c1 is select rowid from IGS_RE_THESIS_HIST_ALL
702 where PERSON_ID = X_PERSON_ID
703 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
704 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
705 and HIST_START_DT = X_HIST_START_DT
706 ;
707 begin
708 open c1;
709 fetch c1 into X_ROWID;
710 if (c1%notfound) then
711 close c1;
712 INSERT_ROW (
713 X_ROWID,
714 X_PERSON_ID,
715 X_CA_SEQUENCE_NUMBER,
716 X_SEQUENCE_NUMBER,
717 X_HIST_START_DT,
718 X_HIST_END_DT,
719 X_HIST_WHO,
720 X_TITLE,
721 X_FINAL_TITLE_IND,
722 X_SHORT_TITLE,
723 X_ABBREVIATED_TITLE,
724 X_THESIS_RESULT_CD,
725 X_EXPECTED_SUBMISSION_DT,
726 X_DATE_OF_LIBRARY_LODGEMENT,
727 X_LIBRARY_CATALOGUE_NUMBER,
728 X_EMBARGO_EXPIRY_DT,
729 X_THESIS_FORMAT,
730 X_LOGICAL_DELETE_DT,
731 X_EMBARGO_DETAILS,
732 X_THESIS_TOPIC,
733 X_CITATION,
734 X_COMMENTS,
735 X_MODE,
736 X_ORG_ID);
737 return;
738 end if;
739 close c1;
740 UPDATE_ROW (
741 X_ROWID,
742 X_PERSON_ID,
743 X_CA_SEQUENCE_NUMBER,
744 X_SEQUENCE_NUMBER,
745 X_HIST_START_DT,
746 X_HIST_END_DT,
747 X_HIST_WHO,
748 X_TITLE,
749 X_FINAL_TITLE_IND,
750 X_SHORT_TITLE,
751 X_ABBREVIATED_TITLE,
752 X_THESIS_RESULT_CD,
753 X_EXPECTED_SUBMISSION_DT,
754 X_DATE_OF_LIBRARY_LODGEMENT,
755 X_LIBRARY_CATALOGUE_NUMBER,
756 X_EMBARGO_EXPIRY_DT,
757 X_THESIS_FORMAT,
758 X_LOGICAL_DELETE_DT,
759 X_EMBARGO_DETAILS,
760 X_THESIS_TOPIC,
761 X_CITATION,
762 X_COMMENTS,
763 X_MODE);
764 end ADD_ROW;
765
766 procedure DELETE_ROW (
767 X_ROWID in VARCHAR2
768 ) as
769 begin
770
771 Before_DML (
772 p_action => 'DELETE',
773 x_rowid => X_ROWID
774 );
775
776 delete from IGS_RE_THESIS_HIST_ALL
777 where ROWID = X_ROWID;
778 if (sql%notfound) then
779 raise no_data_found;
780 end if;
781
782 end DELETE_ROW;
783
784 end IGS_RE_THESIS_HIST_PKG;