[Home] [Help]
PACKAGE BODY: APPS.IGS_RE_THESIS_PKG
Source
1 PACKAGE BODY IGS_RE_THESIS_PKG AS
2 /* $Header: IGSRI15B.pls 120.1 2005/07/04 00:42:16 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --Nishikant 19NOV2002 Bug#2661533. In the procedure BeforeRowInsertUpdate1 the calls of the functions
7 -- igs_re_val_the.resp_val_the_expct, igs_re_val_the.resp_val_the_embrg,
8 -- igs_re_val_the.resp_val_the_thr got modified to add one more parameer p_legacy.
9 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_re_val_the.genp_val_sdtt_sess
10 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
11 -------------------------------------------------------------------------------------------
12 l_rowid VARCHAR2(25);
13 old_references IGS_RE_THESIS_ALL%RowType;
14 new_references IGS_RE_THESIS_ALL%RowType;
15 PROCEDURE Set_Column_Values (
16 p_action IN VARCHAR2,
17 x_rowid IN VARCHAR2,
18 x_person_id IN NUMBER,
19 x_ca_sequence_number IN NUMBER,
20 x_sequence_number IN NUMBER,
21 x_title IN VARCHAR2,
22 x_final_title_ind IN VARCHAR2,
23 x_short_title IN VARCHAR2,
24 x_abbreviated_title IN VARCHAR2,
25 x_thesis_result_cd IN VARCHAR2,
26 x_expected_submission_dt IN DATE,
27 x_library_lodgement_dt IN DATE,
28 x_library_catalogue_number IN VARCHAR2,
29 x_embargo_expiry_dt IN DATE,
30 x_thesis_format IN VARCHAR2,
31 x_logical_delete_dt IN DATE,
32 x_embargo_details IN VARCHAR2,
33 x_thesis_topic IN VARCHAR2,
34 x_citation IN VARCHAR2,
35 x_comments IN VARCHAR2,
36 x_creation_date IN DATE,
37 x_created_by IN NUMBER,
38 x_last_update_date IN DATE,
39 x_last_updated_by IN NUMBER,
40 x_last_update_login IN NUMBER ,
41 x_org_id IN NUMBER
42 ) AS
43 CURSOR cur_old_ref_values IS
44 SELECT *
45 FROM IGS_RE_THESIS_ALL
46 WHERE rowid = x_rowid;
47 BEGIN
48 l_rowid := x_rowid;
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 -- Populate New Values.
62 new_references.person_id := x_person_id;
63 new_references.ca_sequence_number := x_ca_sequence_number;
64 new_references.sequence_number := x_sequence_number;
65 new_references.title := x_title;
66 new_references.final_title_ind := x_final_title_ind;
67 new_references.short_title := x_short_title;
68 new_references.abbreviated_title := x_abbreviated_title;
69 new_references.thesis_result_cd := x_thesis_result_cd;
70 new_references.expected_submission_dt := x_expected_submission_dt;
71 new_references.library_lodgement_dt := x_library_lodgement_dt;
72 new_references.library_catalogue_number := x_library_catalogue_number;
73 new_references.embargo_expiry_dt := x_embargo_expiry_dt;
74 new_references.thesis_format := x_thesis_format;
75 new_references.logical_delete_dt := x_logical_delete_dt;
76 new_references.embargo_details := x_embargo_details;
77 new_references.thesis_topic := x_thesis_topic;
78 new_references.citation := x_citation;
79 new_references.comments := x_comments;
80 new_references.org_id := x_org_id;
81 IF (p_action = 'UPDATE') THEN
82 new_references.creation_date := old_references.creation_date;
83 new_references.created_by := old_references.created_by;
84 ELSE
85 new_references.creation_date := x_creation_date;
86 new_references.created_by := x_created_by;
87 END IF;
88 new_references.last_update_date := x_last_update_date;
89 new_references.last_updated_by := x_last_updated_by;
90 new_references.last_update_login := x_last_update_login;
91 END Set_Column_Values;
92 PROCEDURE BeforeRowInsertUpdate1(
93 p_inserting IN BOOLEAN,
94 p_updating IN BOOLEAN,
95 p_deleting IN BOOLEAN
96 ) AS
97 v_message_name VARCHAR2(30);
98 v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
99 BEGIN
100 -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
101 -- as a result of IGS_PS_COURSE transfer
102 IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
103 IF p_inserting THEN
104 -- Call function to get the status to stop mutating trigger.
105 v_thesis_status := IGS_RE_GEN_002.resp_get_the_status( new_references.person_id,
106 new_references.ca_sequence_number,
107 new_references.sequence_number,
108 'Y',
109 new_references.logical_delete_dt,
110 new_references.thesis_result_cd);
111 ELSIF p_updating THEN
112 -- Call function with old values as most validations are based
113 -- on the status on the screen.
114 v_thesis_status := IGS_RE_GEN_002.resp_get_the_status( new_references.person_id,
115 new_references.ca_sequence_number,
116 new_references.sequence_number,
117 'Y',
118 old_references.logical_delete_dt,
119 old_references.thesis_result_cd);
120 END IF;
121 IF p_updating THEN
122 -- If any field except logical deletion date is changing then validate
123 -- whether update is possible.
124 IF old_references.title <> new_references.title OR
125 old_references.final_title_ind <> new_references.final_title_ind OR
126 NVL(old_references.short_title,' ') <> NVL(new_references.short_title,' ') OR
127 NVL(old_references.abbreviated_title,' ') <> NVL(new_references.abbreviated_title,' ') OR
128 NVL(old_references.thesis_result_cd,' ') <> NVL(new_references.thesis_result_cd,' ') OR
129 NVL(old_references.expected_submission_dt,igs_ge_date.igsdate('1900/01/01')) <>
130 NVL(new_references.expected_submission_dt,igs_ge_date.igsdate('1900/01/01')) OR
131 NVL(old_references.library_lodgement_dt,igs_ge_date.igsdate('1900/01/01')) <>
132 NVL(new_references.library_lodgement_dt,igs_ge_date.igsdate('1900/01/01')) OR
133 NVL(old_references.library_catalogue_number,' ') <>
134 NVL(new_references.library_catalogue_number,' ') OR
135 NVL(old_references.embargo_expiry_dt,igs_ge_date.igsdate('1900/01/01')) <>
136 NVL(new_references.embargo_expiry_dt,igs_ge_date.igsdate('1900/01/01')) OR
137 NVL(old_references.thesis_format,' ') <> NVL(new_references.thesis_format,' ') OR
138 NVL(old_references.embargo_details,' ') <> NVL(new_references.embargo_details,' ') OR
139 NVL(old_references.thesis_topic,' ') <> NVL(new_references.thesis_topic,' ') OR
140 NVL(old_references.citation,' ') <> NVL(new_references.citation,' ') OR
141 NVL(old_references.comments,' ') <> NVL(new_references.comments,' ') THEN
142 IF IGS_RE_VAL_THE.resp_val_the_upd(new_references.logical_delete_dt,
143 v_message_name) = FALSE THEN
144 Fnd_Message.Set_Name ('IGS', v_message_name);
145 IGS_GE_MSG_STACK.ADD;
146 App_Exception.Raise_Exception;
147 END IF;
148 END IF;
149 END IF;
150 IF p_updating THEN
151 -- Validate the IGS_RE_THESIS final title indicator.
152 IF old_references.final_title_ind <> new_references.final_title_ind THEN
153 IF IGS_RE_VAL_THE.resp_val_the_fnl( new_references.person_id,
154 new_references.ca_sequence_number,
155 new_references.sequence_number,
156 new_references.final_title_ind,
157 v_thesis_status,
158 v_message_name) = FALSE THEN
159 Fnd_Message.Set_Name ('IGS', v_message_name);
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163 END IF;
164 -- Validate the IGS_RE_THESIS IGS_PE_TITLE
165 IF old_references.title <> new_references.title THEN
166 IF IGS_RE_VAL_THE.resp_val_the_ttl( old_references.title,
167 new_references.title,
168 old_references.thesis_result_cd,
169 v_message_name) = FALSE THEN
170 Fnd_Message.Set_Name ('IGS', v_message_name);
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 END IF;
174 END IF;
175 END IF;
176 IF p_inserting OR
177 ( p_updating AND
178 NVL(old_references.embargo_details,' ') <> NVL(new_references.embargo_details,' ') OR
179 NVL(old_references.embargo_expiry_dt,igs_ge_date.igsdate('1900/01/01')) <>
180 NVL(new_references.embargo_expiry_dt,igs_ge_date.igsdate('1900/01/01'))) THEN
181 -- Validate embargo details
182 IF IGS_RE_VAL_THE.resp_val_the_embrg( new_references.embargo_details,
183 old_references.embargo_expiry_dt,
184 new_references.embargo_expiry_dt,
185 v_thesis_status,
186 'N', --p_legacy parameter
187 v_message_name) = FALSE THEN
188 Fnd_Message.Set_Name ('IGS', v_message_name);
189 IGS_GE_MSG_STACK.ADD;
190 App_Exception.Raise_Exception;
191 END IF;
192 END IF;
193 IF p_inserting OR
194 ( p_updating AND
195 NVL(old_references.citation,' ') <> NVL(new_references.citation,' ')) THEN
196 -- Validate citation
197 IF IGS_RE_VAL_THE.resp_val_the_ctn( v_thesis_status,
198 new_references.citation,
199 v_message_name) = FALSE THEN
200 Fnd_Message.Set_Name ('IGS', v_message_name);
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203 END IF;
204 END IF;
205 IF p_inserting OR
206 ( p_updating AND
207 NVL(old_references.library_catalogue_number,' ') <>
208 NVL(new_references.library_catalogue_number,' ') OR
209 NVL(old_references.library_lodgement_dt,igs_ge_date.igsdate('1900/01/01')) <>
210 NVL(new_references.library_lodgement_dt,igs_ge_date.igsdate('1900/01/01'))) THEN
211 -- Validate library details
212 IF IGS_RE_VAL_THE.resp_val_the_lbry( new_references.person_id,
213 new_references.ca_sequence_number,
214 new_references.sequence_number,
215 new_references.library_catalogue_number,
216 new_references.library_lodgement_dt,
217 v_thesis_status,
218 v_message_name) = FALSE THEN
219 Fnd_Message.Set_Name ('IGS', v_message_name);
220 IGS_GE_MSG_STACK.ADD;
221 App_Exception.Raise_Exception;
222 END IF;
223 END IF;
224 IF p_inserting OR
225 (p_updating AND
226 NVL(old_references.thesis_result_cd,' ') <> NVL(new_references.thesis_result_cd,' ')) THEN
227 -- Validate IGS_RE_THESIS result code
228 IF IGS_RE_VAL_THE.resp_val_the_thr( new_references.person_id,
229 new_references.ca_sequence_number,
230 new_references.sequence_number,
231 new_references.thesis_result_cd,
232 v_thesis_status,
233 'N', --p_legacy parameter
234 v_message_name) = FALSE THEN
235 Fnd_Message.Set_Name ('IGS', v_message_name);
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238 END IF;
239 END IF;
240 IF p_updating THEN
241 IF old_references.logical_delete_dt IS NULL AND new_references.logical_delete_dt IS NOT NULL THEN
242 IF IGS_RE_VAL_THE.resp_val_the_del( new_references.person_id,
243 new_references.ca_sequence_number,
244 new_references.sequence_number,
245 new_references.logical_delete_dt,
246 v_thesis_status,
247 v_message_name) = FALSE THEN
248 Fnd_Message.Set_Name ('IGS', v_message_name);
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END IF;
252 END IF;
253 END IF;
254 IF p_updating AND
255 ( NVL(old_references.logical_delete_dt,igs_ge_date.igsdate('1900/01/01')) <>
256 NVL(new_references.logical_delete_dt,igs_ge_date.igsdate('1900/01/01'))) THEN
257 IF IGS_RE_VAL_THE.resp_val_the_del_dt( old_references.logical_delete_dt,
258 new_references.logical_delete_dt,
259 v_message_name) = FALSE THEN
260 Fnd_Message.Set_Name ('IGS', v_message_name);
261 IGS_GE_MSG_STACK.ADD;
262 App_Exception.Raise_Exception;
263 END IF;
264 END IF;
265 IF p_inserting OR
266 ( p_updating AND
267 ( new_references.expected_submission_dt IS NOT NULL AND
268 NVL(old_references.expected_submission_dt,igs_ge_date.igsdate('1900/01/01')) <>
269 new_references.expected_submission_dt)) THEN
270 IF IGS_RE_VAL_THE.resp_val_the_expct( new_references.person_id,
271 new_references.ca_sequence_number,
272 new_references.expected_submission_dt,
273 'N', --p_legacy
274 v_message_name) = FALSE THEN
275 Fnd_Message.Set_Name ('IGS', v_message_name);
276 IGS_GE_MSG_STACK.ADD;
277 App_Exception.Raise_Exception;
278 END IF;
279 END IF;
280 END IF;
281 END BeforeRowInsertUpdate1;
282 PROCEDURE AfterRowUpdateDelete2(
283 p_inserting IN BOOLEAN,
284 p_updating IN BOOLEAN,
285 p_deleting IN BOOLEAN
286 ) AS
287
288 l_deleted igs_re_thesis.final_title_ind%TYPE;
289
290 BEGIN
291 IF p_updating OR p_deleting THEN
292 IGS_RE_GEN_003.RESP_INS_THE_HIST(old_references.person_id,
293 old_references.ca_sequence_number,
294 old_references.sequence_number,
295 old_references.title,
296 new_references.title,
297 old_references.final_title_ind,
298 new_references.final_title_ind,
299 old_references.short_title,
300 new_references.short_title,
301 old_references.abbreviated_title,
302 new_references.abbreviated_title,
303 old_references.thesis_result_cd,
304 new_references.thesis_result_cd,
305 old_references.expected_submission_dt,
306 new_references.expected_submission_dt,
307 old_references.library_lodgement_dt,
308 new_references.library_lodgement_dt,
309 old_references.library_catalogue_number,
310 new_references.library_catalogue_number,
311 old_references.embargo_expiry_dt,
312 new_references.embargo_expiry_dt,
313 old_references.thesis_format,
314 new_references.thesis_format,
315 old_references.logical_delete_dt,
316 new_references.logical_delete_dt,
317 old_references.embargo_details,
318 new_references.embargo_details,
319 old_references.thesis_topic,
320 new_references.thesis_topic,
321 old_references.citation,
322 new_references.citation,
323 old_references.comments,
324 new_references.comments,
325 old_references.last_updated_by,
326 new_references.last_updated_by,
327 old_references.last_update_date,
328 new_references.last_update_date);
329 END IF;
330
331
332 -- Bug # 2829275 . UK Correspondence.The thesis event is raised when there is a change in thesis attributes.
333
334
335 IF new_references.logical_delete_dt IS NOT NULL THEN
336 l_deleted := 'Y';
337 ELSIF new_references.logical_delete_dt IS NULL THEN
338 l_deleted := 'N';
339 END IF;
340
341 IF p_inserting
342 OR (p_updating AND (
343 (new_references.title <> old_references.title
344 OR (new_references.thesis_topic <> old_references.thesis_topic)
345 OR (new_references.thesis_topic IS NULL AND old_references.thesis_topic IS NOT NULL)
346 OR (new_references.thesis_topic IS NOT NULL AND old_references.thesis_topic IS NULL)
347 )
348 OR (new_references.final_title_ind <> old_references.final_title_ind AND new_references.final_title_ind = 'Y')
349 OR ( old_references.logical_delete_dt IS NULL AND new_references.logical_delete_dt IS NOT NULL)
350 OR ( old_references.logical_delete_dt IS NOT NULL AND new_references.logical_delete_dt IS NULL) )) THEN
351
352
353 igs_re_workflow.rethesis_event (
354 p_personid => new_references.person_id,
355 p_ca_seq_num => new_references.ca_sequence_number,
356 p_thesistopic => new_references.thesis_topic,
357 p_thesistitle => new_references.title,
358 p_approved => new_references.final_title_ind,
359 p_deleted => l_deleted
360 );
361
362 END IF;
363
364 END AfterRowUpdateDelete2;
365 PROCEDURE Check_Constraints (
366 Column_Name in VARCHAR2 ,
367 Column_Value in VARCHAR2
368 ) AS
369 BEGIN
370 IF Column_Name is null then
371 NULL;
372 ELSIF upper(Column_name) = 'CA_SEQUENCE_NUMBER' THEN
373 new_references.CA_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
374 ELSIF upper(Column_name) = 'FINAL_TITLE_IND' THEN
375 new_references.FINAL_TITLE_IND := COLUMN_VALUE ;
376 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' THEN
377 new_references.SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
378 ELSIF upper(Column_name) = 'LIBRARY_CATALOGUE_NUMBER' THEN
379 new_references.LIBRARY_CATALOGUE_NUMBER := COLUMN_VALUE ;
380 ELSIF upper(Column_name) = 'THESIS_FORMAT' THEN
381 new_references.THESIS_FORMAT := COLUMN_VALUE ;
382 ELSIF upper(Column_name) = 'THESIS_RESULT_CD' THEN
383 new_references.THESIS_RESULT_CD := COLUMN_VALUE ;
384 END IF;
385 IF upper(column_name) = 'CA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
386 IF new_references.CA_SEQUENCE_NUMBER < 1 OR new_references.CA_SEQUENCE_NUMBER > 999999 then
387 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
388 IGS_GE_MSG_STACK.ADD;
389 App_Exception.Raise_Exception ;
390 END IF;
391 END IF;
392 IF upper(column_name) = 'FINAL_TITLE_IND' OR COLUMN_NAME IS NULL THEN
393 IF new_references.FINAL_TITLE_IND <> upper(NEW_REFERENCES.FINAL_TITLE_IND) OR
394 new_references.FINAL_TITLE_IND NOT IN ('Y', 'N') then
395 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
396 IGS_GE_MSG_STACK.ADD;
397 App_Exception.Raise_Exception ;
398 END IF;
399 END IF;
400 IF upper(column_name) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
401 IF new_references.SEQUENCE_NUMBER < 1 OR new_references.SEQUENCE_NUMBER > 999999 then
402 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
403 IGS_GE_MSG_STACK.ADD;
404 App_Exception.Raise_Exception ;
405 END IF;
406 END IF;
407 IF upper(column_name) = 'LIBRARY_CATALOGUE_NUMBER' OR COLUMN_NAME IS NULL THEN
408 IF new_references.LIBRARY_CATALOGUE_NUMBER <> NEW_REFERENCES.LIBRARY_CATALOGUE_NUMBER then
409 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
410 IGS_GE_MSG_STACK.ADD;
411 App_Exception.Raise_Exception ;
412 END IF;
413 END IF;
414 IF upper(column_name) = 'THESIS_FORMAT' OR COLUMN_NAME IS NULL THEN
415 IF new_references.THESIS_FORMAT <> NEW_REFERENCES.THESIS_FORMAT then
416 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
417 IGS_GE_MSG_STACK.ADD;
418 App_Exception.Raise_Exception ;
419 END IF;
420 END IF;
421 IF upper(column_name) = 'THESIS_RESULT_CD' OR COLUMN_NAME IS NULL THEN
422 IF new_references.THESIS_RESULT_CD <> upper(NEW_REFERENCES.THESIS_RESULT_CD) then
423 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
424 App_Exception.Raise_Exception ;
425 END IF;
426 END IF;
427 END Check_Constraints ;
428 PROCEDURE Check_Parent_Existance AS
429 BEGIN
430 IF (((old_references.person_id = new_references.person_id) AND
431 (old_references.ca_sequence_number = new_references.ca_sequence_number)) OR
432 ((new_references.person_id IS NULL) OR
433 (new_references.ca_sequence_number IS NULL))) THEN
434 NULL;
435 ELSE
436 IF NOT IGS_RE_CANDIDATURE_PKG.Get_PK_For_Validation (
437 new_references.person_id,
438 new_references.ca_sequence_number
439 ) THEN
440 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
441 IGS_GE_MSG_STACK.ADD;
442 App_Exception.Raise_Exception;
443 END IF;
444 END IF;
445 IF (((old_references.thesis_result_cd = new_references.thesis_result_cd)) OR
446 ((new_references.thesis_result_cd IS NULL))) THEN
447 NULL;
448 ELSE
449 IF NOT IGS_RE_THESIS_RESULT_PKG.Get_PK_For_Validation (
450 new_references.thesis_result_cd
451 ) THEN
452 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
453 IGS_GE_MSG_STACK.ADD;
454 App_Exception.Raise_Exception;
455 END IF;
456 END IF;
457 END Check_Parent_Existance;
458 PROCEDURE Check_Child_Existance AS
459 BEGIN
460 IGS_RE_THESIS_EXAM_PKG.GET_FK_IGS_RE_THESIS (
461 old_references.person_id,
462 old_references.ca_sequence_number,
463 old_references.sequence_number
464 );
465 END Check_Child_Existance;
466 FUNCTION Get_PK_For_Validation (
467 x_person_id IN NUMBER,
468 x_ca_sequence_number IN NUMBER,
469 x_sequence_number IN NUMBER
470 ) RETURN BOOLEAN AS
471 CURSOR cur_rowid IS
472 SELECT rowid
473 FROM IGS_RE_THESIS_ALL
474 WHERE person_id = x_person_id
475 AND ca_sequence_number = x_ca_sequence_number
476 AND sequence_number = x_sequence_number
477 FOR UPDATE NOWAIT;
478 lv_rowid cur_rowid%RowType;
479 BEGIN
480 Open cur_rowid;
481 Fetch cur_rowid INTO lv_rowid;
482 IF (cur_rowid%FOUND) THEN
483 Close cur_rowid;
484 RETURN(TRUE);
485 ELSE
486 Close cur_rowid;
487 RETURN(FALSE);
488 END IF;
489 END Get_PK_For_Validation;
490 PROCEDURE GET_FK_IGS_RE_CANDIDATURE (
491 x_person_id IN NUMBER,
492 x_sequence_number IN NUMBER
493 ) AS
494 CURSOR cur_rowid IS
495 SELECT rowid
496 FROM IGS_RE_THESIS_ALL
497 WHERE person_id = x_person_id
498 AND ca_sequence_number = x_sequence_number ;
499 lv_rowid cur_rowid%RowType;
500 BEGIN
501 Open cur_rowid;
502 Fetch cur_rowid INTO lv_rowid;
503 IF (cur_rowid%FOUND) THEN
504 Close cur_rowid;
505 Fnd_Message.Set_Name ('IGS', 'IGS_RE_THE_CA_FK');
506 IGS_GE_MSG_STACK.ADD;
507 App_Exception.Raise_Exception;
508 Return;
509 END IF;
510 Close cur_rowid;
511 END GET_FK_IGS_RE_CANDIDATURE;
512 PROCEDURE GET_FK_IGS_RE_THESIS_RESULT (
513 x_thesis_result_cd IN VARCHAR2
514 ) AS
515 CURSOR cur_rowid IS
516 SELECT rowid
517 FROM IGS_RE_THESIS_ALL
518 WHERE thesis_result_cd = x_thesis_result_cd ;
519 lv_rowid cur_rowid%RowType;
520 BEGIN
521 Open cur_rowid;
522 Fetch cur_rowid INTO lv_rowid;
523 IF (cur_rowid%FOUND) THEN
524 Close cur_rowid;
525 Fnd_Message.Set_Name ('IGS', 'IGS_RE_THE_THR_FK');
526 IGS_GE_MSG_STACK.ADD;
527 App_Exception.Raise_Exception;
528 Return;
529 END IF;
530 Close cur_rowid;
531 END GET_FK_IGS_RE_THESIS_RESULT;
532 PROCEDURE Before_DML (
533 p_action IN VARCHAR2,
534 x_rowid IN VARCHAR2,
535 x_person_id IN NUMBER,
536 x_ca_sequence_number IN NUMBER,
537 x_sequence_number IN NUMBER,
538 x_title IN VARCHAR2,
539 x_final_title_ind IN VARCHAR2,
540 x_short_title IN VARCHAR2,
541 x_abbreviated_title IN VARCHAR2,
542 x_thesis_result_cd IN VARCHAR2,
543 x_expected_submission_dt IN DATE,
544 x_library_lodgement_dt IN DATE,
545 x_library_catalogue_number IN VARCHAR2,
546 x_embargo_expiry_dt IN DATE,
547 x_thesis_format IN VARCHAR2,
548 x_logical_delete_dt IN DATE,
549 x_embargo_details IN VARCHAR2,
550 x_thesis_topic IN VARCHAR2,
551 x_citation IN VARCHAR2,
552 x_comments IN VARCHAR2,
553 x_creation_date IN DATE,
554 x_created_by IN NUMBER,
555 x_last_update_date IN DATE,
556 x_last_updated_by IN NUMBER,
557 x_last_update_login IN NUMBER,
558 x_org_id IN NUMBER
559 ) AS
560 BEGIN
561 Set_Column_Values (
562 p_action,
563 x_rowid,
564 x_person_id,
565 x_ca_sequence_number,
566 x_sequence_number,
567 x_title,
568 x_final_title_ind,
569 x_short_title,
570 x_abbreviated_title,
571 x_thesis_result_cd,
572 x_expected_submission_dt,
573 x_library_lodgement_dt,
574 x_library_catalogue_number,
575 x_embargo_expiry_dt,
576 x_thesis_format,
577 x_logical_delete_dt,
578 x_embargo_details,
579 x_thesis_topic,
580 x_citation,
581 x_comments,
582 x_creation_date,
583 x_created_by,
584 x_last_update_date,
585 x_last_updated_by,
586 x_last_update_login ,
587 x_org_id
588 );
589 IF (p_action = 'INSERT') THEN
590 -- Call all the procedures related to Before Insert.
591 BeforeRowInsertUpdate1 ( p_inserting => TRUE,
592 p_updating => FALSE,
593 p_deleting => FALSE);
594 IF Get_PK_For_Validation (
595 new_references.person_id,
596 new_references.ca_sequence_number,
597 new_references.sequence_number
598 ) THEN
599 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
600 IGS_GE_MSG_STACK.ADD;
601 App_Exception.Raise_Exception;
602 END IF;
603 Check_Constraints;
604 Check_Parent_Existance;
605 ELSIF (p_action = 'UPDATE') THEN
606 -- Call all the procedures related to Before Update.
607 BeforeRowInsertUpdate1 ( p_inserting => FALSE,
608 p_updating => TRUE,
609 p_deleting => FALSE);
610 Check_Constraints;
611 Check_Parent_Existance;
612 ELSIF (p_action = 'DELETE') THEN
613 -- Call all the procedures related to Before Delete.
614 Check_Child_Existance;
615 ELSIF (p_action = 'VALIDATE_INSERT') THEN
616 IF Get_PK_For_Validation (
617 new_references.person_id,
618 new_references.ca_sequence_number,
619 new_references.sequence_number
620 ) THEN
621 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
622 IGS_GE_MSG_STACK.ADD;
623 App_Exception.Raise_Exception;
624 END IF;
625 Check_Constraints;
626 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
627 Check_Constraints;
628 ELSIF (p_action = 'VALIDATE_DELETE') THEN
629 Check_Child_Existance;
630 END IF;
631 END Before_DML;
632 PROCEDURE After_DML (
633 p_action IN VARCHAR2,
634 x_rowid IN VARCHAR2
635 ) AS
636 BEGIN
637 l_rowid := x_rowid;
638 IF (p_action = 'UPDATE') THEN
639 -- Call all the procedures related to After Update.
640 AfterRowUpdateDelete2 ( p_inserting => FALSE,
641 p_updating => TRUE,
642 p_deleting => FALSE);
643 ELSIF (p_action = 'DELETE') THEN
644 -- Call all the procedures related to After Delete.
645 AfterRowUpdateDelete2 ( p_inserting => FALSE,
646 p_updating => FALSE,
647 p_deleting => TRUE);
648 ELSIF (p_action = 'INSERT') THEN
649
650 AfterRowUpdateDelete2 ( p_inserting => TRUE,
651 p_updating => FALSE,
652 p_deleting => FALSE);
653
654 END IF;
655 END After_DML;
656 procedure INSERT_ROW (
657 X_ROWID in out NOCOPY VARCHAR2,
658 X_PERSON_ID in NUMBER,
659 X_CA_SEQUENCE_NUMBER in NUMBER,
660 X_SEQUENCE_NUMBER in NUMBER,
661 X_TITLE in VARCHAR2,
662 X_FINAL_TITLE_IND in VARCHAR2,
663 X_SHORT_TITLE in VARCHAR2,
664 X_ABBREVIATED_TITLE in VARCHAR2,
665 X_THESIS_RESULT_CD in VARCHAR2,
666 X_EXPECTED_SUBMISSION_DT in DATE,
667 X_LIBRARY_LODGEMENT_DT in DATE,
668 X_LIBRARY_CATALOGUE_NUMBER in VARCHAR2,
669 X_EMBARGO_EXPIRY_DT in DATE,
670 X_THESIS_FORMAT in VARCHAR2,
671 X_LOGICAL_DELETE_DT in DATE,
672 X_EMBARGO_DETAILS in VARCHAR2,
673 X_THESIS_TOPIC in VARCHAR2,
674 X_CITATION in VARCHAR2,
675 X_COMMENTS in VARCHAR2,
676 X_MODE in VARCHAR2 ,
677 X_ORG_ID in NUMBER
678 ) as
679 cursor C is select ROWID from IGS_RE_THESIS_ALL
680 where PERSON_ID = X_PERSON_ID
681 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
682 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
683 X_LAST_UPDATE_DATE DATE;
684 X_LAST_UPDATED_BY NUMBER;
685 X_LAST_UPDATE_LOGIN NUMBER;
686 begin
687 X_LAST_UPDATE_DATE := SYSDATE;
688 if(X_MODE = 'I') then
689 X_LAST_UPDATED_BY := 1;
690 X_LAST_UPDATE_LOGIN := 0;
691 elsif (X_MODE IN ('R', 'S')) then
692 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
693 if X_LAST_UPDATED_BY is NULL then
694 X_LAST_UPDATED_BY := -1;
695 end if;
696 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
697 if X_LAST_UPDATE_LOGIN is NULL then
698 X_LAST_UPDATE_LOGIN := -1;
699 end if;
700 else
701 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
702 IGS_GE_MSG_STACK.ADD;
703 app_exception.raise_exception;
704 end if;
705 Before_DML (
706 p_action => 'INSERT',
707 x_rowid => X_ROWID,
708 x_person_id => X_PERSON_ID,
709 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
710 x_sequence_number => X_SEQUENCE_NUMBER,
711 x_title => X_TITLE,
712 x_final_title_ind => NVL(X_FINAL_TITLE_IND, 'N'),
713 x_short_title => X_SHORT_TITLE,
714 x_abbreviated_title => X_ABBREVIATED_TITLE,
715 x_thesis_result_cd => X_THESIS_RESULT_CD,
716 x_expected_submission_dt => X_EXPECTED_SUBMISSION_DT,
717 x_library_lodgement_dt => X_LIBRARY_LODGEMENT_DT,
718 x_library_catalogue_number => X_LIBRARY_CATALOGUE_NUMBER,
719 x_embargo_expiry_dt => X_EMBARGO_EXPIRY_DT,
720 x_thesis_format => X_THESIS_FORMAT,
721 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
722 x_embargo_details => X_EMBARGO_DETAILS,
723 x_thesis_topic => X_THESIS_TOPIC,
724 x_citation => X_CITATION,
725 x_comments => X_COMMENTS,
726 x_created_by => X_LAST_UPDATED_BY ,
727 x_creation_date => X_LAST_UPDATE_DATE,
728 x_last_updated_by => X_LAST_UPDATED_BY,
729 x_last_update_date => X_LAST_UPDATE_DATE,
730 x_last_update_login => X_LAST_UPDATE_LOGIN,
731 x_org_id => igs_ge_gen_003.get_org_id
732 );
733 IF (x_mode = 'S') THEN
734 igs_sc_gen_001.set_ctx('R');
735 END IF;
736 insert into IGS_RE_THESIS_ALL (
737 PERSON_ID,
738 CA_SEQUENCE_NUMBER,
739 SEQUENCE_NUMBER,
740 TITLE,
741 FINAL_TITLE_IND,
742 SHORT_TITLE,
743 ABBREVIATED_TITLE,
744 THESIS_RESULT_CD,
745 EXPECTED_SUBMISSION_DT,
746 LIBRARY_LODGEMENT_DT,
747 LIBRARY_CATALOGUE_NUMBER,
748 EMBARGO_EXPIRY_DT,
749 THESIS_FORMAT,
750 LOGICAL_DELETE_DT,
751 EMBARGO_DETAILS,
752 THESIS_TOPIC,
753 CITATION,
754 COMMENTS,
755 CREATION_DATE,
756 CREATED_BY,
757 LAST_UPDATE_DATE,
758 LAST_UPDATED_BY,
759 LAST_UPDATE_LOGIN,
760 ORG_ID
761 ) values (
762 NEW_REFERENCES.PERSON_ID,
763 NEW_REFERENCES.CA_SEQUENCE_NUMBER,
764 NEW_REFERENCES.SEQUENCE_NUMBER,
765 NEW_REFERENCES.TITLE,
766 NEW_REFERENCES.FINAL_TITLE_IND,
767 NEW_REFERENCES.SHORT_TITLE,
768 NEW_REFERENCES.ABBREVIATED_TITLE,
769 NEW_REFERENCES.THESIS_RESULT_CD,
770 NEW_REFERENCES.EXPECTED_SUBMISSION_DT,
771 NEW_REFERENCES.LIBRARY_LODGEMENT_DT,
772 NEW_REFERENCES.LIBRARY_CATALOGUE_NUMBER,
773 NEW_REFERENCES.EMBARGO_EXPIRY_DT,
774 NEW_REFERENCES.THESIS_FORMAT,
775 NEW_REFERENCES.LOGICAL_DELETE_DT,
776 NEW_REFERENCES.EMBARGO_DETAILS,
777 NEW_REFERENCES.THESIS_TOPIC,
778 NEW_REFERENCES.CITATION,
779 NEW_REFERENCES.COMMENTS,
780 X_LAST_UPDATE_DATE,
781 X_LAST_UPDATED_BY,
782 X_LAST_UPDATE_DATE,
783 X_LAST_UPDATED_BY,
784 X_LAST_UPDATE_LOGIN,
785 NEW_REFERENCES.ORG_ID
786 );
787 IF (x_mode = 'S') THEN
788 igs_sc_gen_001.unset_ctx('R');
789 END IF;
790
791 open c;
792 fetch c into X_ROWID;
793 if (c%notfound) then
794 close c;
795 raise no_data_found;
796 end if;
797 close c;
798
799 After_DML (
800 p_action => 'INSERT',
801 x_rowid => X_ROWID
802 );
803
804
805 EXCEPTION
806 WHEN OTHERS THEN
807 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
808 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
809 fnd_message.set_token ('ERR_CD', SQLCODE);
810 igs_ge_msg_stack.add;
811 igs_sc_gen_001.unset_ctx('R');
812 app_exception.raise_exception;
813 ELSE
814 igs_sc_gen_001.unset_ctx('R');
815 RAISE;
816 END IF;
817
818 end INSERT_ROW;
819 procedure LOCK_ROW (
820 X_ROWID in VARCHAR2,
821 X_PERSON_ID in NUMBER,
822 X_CA_SEQUENCE_NUMBER in NUMBER,
823 X_SEQUENCE_NUMBER in NUMBER,
824 X_TITLE in VARCHAR2,
825 X_FINAL_TITLE_IND in VARCHAR2,
826 X_SHORT_TITLE in VARCHAR2,
827 X_ABBREVIATED_TITLE in VARCHAR2,
828 X_THESIS_RESULT_CD in VARCHAR2,
829 X_EXPECTED_SUBMISSION_DT in DATE,
830 X_LIBRARY_LODGEMENT_DT in DATE,
831 X_LIBRARY_CATALOGUE_NUMBER in VARCHAR2,
832 X_EMBARGO_EXPIRY_DT in DATE,
833 X_THESIS_FORMAT in VARCHAR2,
834 X_LOGICAL_DELETE_DT in DATE,
835 X_EMBARGO_DETAILS in VARCHAR2,
836 X_THESIS_TOPIC in VARCHAR2,
837 X_CITATION in VARCHAR2,
838 X_COMMENTS in VARCHAR2
839 ) as
840 cursor c1 is select
841 TITLE,
842 FINAL_TITLE_IND,
843 SHORT_TITLE,
844 ABBREVIATED_TITLE,
845 THESIS_RESULT_CD,
846 EXPECTED_SUBMISSION_DT,
847 LIBRARY_LODGEMENT_DT,
848 LIBRARY_CATALOGUE_NUMBER,
849 EMBARGO_EXPIRY_DT,
850 THESIS_FORMAT,
851 LOGICAL_DELETE_DT,
852 EMBARGO_DETAILS,
853 THESIS_TOPIC,
854 CITATION,
855 COMMENTS
856 from IGS_RE_THESIS_ALL
857 where ROWID = X_ROWID
858 for update nowait;
859 tlinfo c1%rowtype;
860 begin
861 open c1;
862 fetch c1 into tlinfo;
863 if (c1%notfound) then
864 close c1;
865 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
866 app_exception.raise_exception;
867 return;
868 end if;
869 close c1;
870 if ( (tlinfo.TITLE = X_TITLE)
871 AND (tlinfo.FINAL_TITLE_IND = X_FINAL_TITLE_IND)
872 AND ((tlinfo.SHORT_TITLE = X_SHORT_TITLE)
873 OR ((tlinfo.SHORT_TITLE is null)
874 AND (X_SHORT_TITLE is null)))
875 AND ((tlinfo.ABBREVIATED_TITLE = X_ABBREVIATED_TITLE)
876 OR ((tlinfo.ABBREVIATED_TITLE is null)
877 AND (X_ABBREVIATED_TITLE is null)))
878 AND ((tlinfo.THESIS_RESULT_CD = X_THESIS_RESULT_CD)
879 OR ((tlinfo.THESIS_RESULT_CD is null)
880 AND (X_THESIS_RESULT_CD is null)))
881 AND ((tlinfo.EXPECTED_SUBMISSION_DT = X_EXPECTED_SUBMISSION_DT)
882 OR ((tlinfo.EXPECTED_SUBMISSION_DT is null)
883 AND (X_EXPECTED_SUBMISSION_DT is null)))
884 AND ((tlinfo.LIBRARY_LODGEMENT_DT = X_LIBRARY_LODGEMENT_DT)
885 OR ((tlinfo.LIBRARY_LODGEMENT_DT is null)
886 AND (X_LIBRARY_LODGEMENT_DT is null)))
887 AND ((tlinfo.LIBRARY_CATALOGUE_NUMBER = X_LIBRARY_CATALOGUE_NUMBER)
888 OR ((tlinfo.LIBRARY_CATALOGUE_NUMBER is null)
889 AND (X_LIBRARY_CATALOGUE_NUMBER is null)))
890 AND ((tlinfo.EMBARGO_EXPIRY_DT = X_EMBARGO_EXPIRY_DT)
891 OR ((tlinfo.EMBARGO_EXPIRY_DT is null)
892 AND (X_EMBARGO_EXPIRY_DT is null)))
893 AND ((tlinfo.THESIS_FORMAT = X_THESIS_FORMAT)
894 OR ((tlinfo.THESIS_FORMAT is null)
895 AND (X_THESIS_FORMAT is null)))
896 AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
897 OR ((tlinfo.LOGICAL_DELETE_DT is null)
898 AND (X_LOGICAL_DELETE_DT is null)))
899 AND ((tlinfo.EMBARGO_DETAILS = X_EMBARGO_DETAILS)
900 OR ((tlinfo.EMBARGO_DETAILS is null)
901 AND (X_EMBARGO_DETAILS is null)))
902 AND ((tlinfo.THESIS_TOPIC = X_THESIS_TOPIC)
903 OR ((tlinfo.THESIS_TOPIC is null)
904 AND (X_THESIS_TOPIC is null)))
905 AND ((tlinfo.CITATION = X_CITATION)
906 OR ((tlinfo.CITATION is null)
907 AND (X_CITATION is null)))
908 AND ((tlinfo.COMMENTS = X_COMMENTS)
909 OR ((tlinfo.COMMENTS is null)
910 AND (X_COMMENTS is null)))
911
912 ) then
913 null;
914 else
915 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
916 app_exception.raise_exception;
917 end if;
918 return;
919 end LOCK_ROW;
920 procedure UPDATE_ROW (
921 X_ROWID in VARCHAR2,
922 X_PERSON_ID in NUMBER,
923 X_CA_SEQUENCE_NUMBER in NUMBER,
924 X_SEQUENCE_NUMBER in NUMBER,
925 X_TITLE in VARCHAR2,
926 X_FINAL_TITLE_IND in VARCHAR2,
927 X_SHORT_TITLE in VARCHAR2,
928 X_ABBREVIATED_TITLE in VARCHAR2,
929 X_THESIS_RESULT_CD in VARCHAR2,
930 X_EXPECTED_SUBMISSION_DT in DATE,
931 X_LIBRARY_LODGEMENT_DT in DATE,
932 X_LIBRARY_CATALOGUE_NUMBER in VARCHAR2,
933 X_EMBARGO_EXPIRY_DT in DATE,
934 X_THESIS_FORMAT in VARCHAR2,
935 X_LOGICAL_DELETE_DT in DATE,
936 X_EMBARGO_DETAILS in VARCHAR2,
937 X_THESIS_TOPIC in VARCHAR2,
938 X_CITATION in VARCHAR2,
939 X_COMMENTS in VARCHAR2,
940 X_MODE in VARCHAR2
941 ) as
942 X_LAST_UPDATE_DATE DATE;
943 X_LAST_UPDATED_BY NUMBER;
944 X_LAST_UPDATE_LOGIN NUMBER;
945 begin
946 X_LAST_UPDATE_DATE := SYSDATE;
947 if(X_MODE = 'I') then
948 X_LAST_UPDATED_BY := 1;
949 X_LAST_UPDATE_LOGIN := 0;
950 elsif (X_MODE IN ('R', 'S')) then
951 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
952 if X_LAST_UPDATED_BY is NULL then
953 X_LAST_UPDATED_BY := -1;
954 end if;
955 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
956 if X_LAST_UPDATE_LOGIN is NULL then
957 X_LAST_UPDATE_LOGIN := -1;
958 end if;
959 else
960 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
961 IGS_GE_MSG_STACK.ADD;
962 app_exception.raise_exception;
963 end if;
964 Before_DML (
965 p_action => 'UPDATE',
966 x_rowid => X_ROWID,
967 x_person_id => X_PERSON_ID,
968 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
969 x_sequence_number => X_SEQUENCE_NUMBER,
970 x_title => X_TITLE,
971 x_final_title_ind => X_FINAL_TITLE_IND,
972 x_short_title => X_SHORT_TITLE,
973 x_abbreviated_title => X_ABBREVIATED_TITLE,
974 x_thesis_result_cd => X_THESIS_RESULT_CD,
975 x_expected_submission_dt => X_EXPECTED_SUBMISSION_DT,
976 x_library_lodgement_dt => X_LIBRARY_LODGEMENT_DT,
977 x_library_catalogue_number => X_LIBRARY_CATALOGUE_NUMBER,
978 x_embargo_expiry_dt => X_EMBARGO_EXPIRY_DT,
979 x_thesis_format => X_THESIS_FORMAT,
980 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
981 x_embargo_details => X_EMBARGO_DETAILS,
982 x_thesis_topic => X_THESIS_TOPIC,
983 x_citation => X_CITATION,
984 x_comments => X_COMMENTS,
985 x_created_by => X_LAST_UPDATED_BY ,
986 x_creation_date => X_LAST_UPDATE_DATE,
987 x_last_updated_by => X_LAST_UPDATED_BY,
988 x_last_update_date => X_LAST_UPDATE_DATE,
989 x_last_update_login => X_LAST_UPDATE_LOGIN
990 );
991 IF (x_mode = 'S') THEN
992 igs_sc_gen_001.set_ctx('R');
993 END IF;
994 update IGS_RE_THESIS_ALL set
995 TITLE = NEW_REFERENCES.TITLE,
996 FINAL_TITLE_IND = NEW_REFERENCES.FINAL_TITLE_IND,
997 SHORT_TITLE = NEW_REFERENCES.SHORT_TITLE,
998 ABBREVIATED_TITLE = NEW_REFERENCES.ABBREVIATED_TITLE,
999 THESIS_RESULT_CD = NEW_REFERENCES.THESIS_RESULT_CD,
1000 EXPECTED_SUBMISSION_DT = NEW_REFERENCES.EXPECTED_SUBMISSION_DT,
1001 LIBRARY_LODGEMENT_DT = NEW_REFERENCES.LIBRARY_LODGEMENT_DT,
1002 LIBRARY_CATALOGUE_NUMBER = NEW_REFERENCES.LIBRARY_CATALOGUE_NUMBER,
1003 EMBARGO_EXPIRY_DT = NEW_REFERENCES.EMBARGO_EXPIRY_DT,
1004 THESIS_FORMAT = NEW_REFERENCES.THESIS_FORMAT,
1005 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
1006 EMBARGO_DETAILS = NEW_REFERENCES.EMBARGO_DETAILS,
1007 THESIS_TOPIC = NEW_REFERENCES.THESIS_TOPIC,
1008 CITATION = NEW_REFERENCES.CITATION,
1009 COMMENTS = NEW_REFERENCES.COMMENTS,
1010 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1011 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1012 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1013 where ROWID = X_ROWID
1014 ;
1015 if (sql%notfound) then
1016 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1017 igs_ge_msg_stack.add;
1018 igs_sc_gen_001.unset_ctx('R');
1019 app_exception.raise_exception;
1020 end if;
1021 IF (x_mode = 'S') THEN
1022 igs_sc_gen_001.unset_ctx('R');
1023 END IF;
1024
1025 After_DML (
1026 p_action => 'UPDATE',
1027 x_rowid => X_ROWID
1028 );
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 IF (SQLCODE = (-28115)) THEN
1032 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1033 fnd_message.set_token ('ERR_CD', SQLCODE);
1034 igs_ge_msg_stack.add;
1035 igs_sc_gen_001.unset_ctx('R');
1036 app_exception.raise_exception;
1037 ELSE
1038 igs_sc_gen_001.unset_ctx('R');
1039 RAISE;
1040 END IF;
1041
1042 end UPDATE_ROW;
1043 procedure ADD_ROW (
1044 X_ROWID in out NOCOPY VARCHAR2,
1045 X_PERSON_ID in NUMBER,
1046 X_CA_SEQUENCE_NUMBER in NUMBER,
1047 X_SEQUENCE_NUMBER in NUMBER,
1048 X_TITLE in VARCHAR2,
1049 X_FINAL_TITLE_IND in VARCHAR2,
1050 X_SHORT_TITLE in VARCHAR2,
1051 X_ABBREVIATED_TITLE in VARCHAR2,
1052 X_THESIS_RESULT_CD in VARCHAR2,
1053 X_EXPECTED_SUBMISSION_DT in DATE,
1054 X_LIBRARY_LODGEMENT_DT in DATE,
1055 X_LIBRARY_CATALOGUE_NUMBER in VARCHAR2,
1056 X_EMBARGO_EXPIRY_DT in DATE,
1057 X_THESIS_FORMAT in VARCHAR2,
1058 X_LOGICAL_DELETE_DT in DATE,
1059 X_EMBARGO_DETAILS in VARCHAR2,
1060 X_THESIS_TOPIC in VARCHAR2,
1061 X_CITATION in VARCHAR2,
1062 X_COMMENTS in VARCHAR2,
1063 X_MODE in VARCHAR2 ,
1064 X_ORG_ID in NUMBER
1065 ) as
1066 cursor c1 is select rowid from IGS_RE_THESIS_ALL
1067 where PERSON_ID = X_PERSON_ID
1068 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
1069 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1070 ;
1071 begin
1072 open c1;
1073 fetch c1 into X_ROWID;
1074 if (c1%notfound) then
1075 close c1;
1076 INSERT_ROW (
1077 X_ROWID,
1078 X_PERSON_ID,
1079 X_CA_SEQUENCE_NUMBER,
1080 X_SEQUENCE_NUMBER,
1081 X_TITLE,
1082 X_FINAL_TITLE_IND,
1083 X_SHORT_TITLE,
1084 X_ABBREVIATED_TITLE,
1085 X_THESIS_RESULT_CD,
1086 X_EXPECTED_SUBMISSION_DT,
1087 X_LIBRARY_LODGEMENT_DT,
1088 X_LIBRARY_CATALOGUE_NUMBER,
1089 X_EMBARGO_EXPIRY_DT,
1090 X_THESIS_FORMAT,
1091 X_LOGICAL_DELETE_DT,
1092 X_EMBARGO_DETAILS,
1093 X_THESIS_TOPIC,
1094 X_CITATION,
1095 X_COMMENTS,
1096 X_MODE,
1097 X_ORG_ID);
1098 return;
1099 end if;
1100 close c1;
1101 UPDATE_ROW (
1102 X_ROWID,
1103 X_PERSON_ID,
1104 X_CA_SEQUENCE_NUMBER,
1105 X_SEQUENCE_NUMBER,
1106 X_TITLE,
1107 X_FINAL_TITLE_IND,
1108 X_SHORT_TITLE,
1109 X_ABBREVIATED_TITLE,
1110 X_THESIS_RESULT_CD,
1111 X_EXPECTED_SUBMISSION_DT,
1112 X_LIBRARY_LODGEMENT_DT,
1113 X_LIBRARY_CATALOGUE_NUMBER,
1114 X_EMBARGO_EXPIRY_DT,
1115 X_THESIS_FORMAT,
1116 X_LOGICAL_DELETE_DT,
1117 X_EMBARGO_DETAILS,
1118 X_THESIS_TOPIC,
1119 X_CITATION,
1120 X_COMMENTS,
1121 X_MODE);
1122 end ADD_ROW;
1123 procedure DELETE_ROW (
1124 X_ROWID in VARCHAR2,
1125 x_mode IN VARCHAR2
1126 ) as
1127 begin
1128 Before_DML (
1129 p_action => 'DELETE',
1130 x_rowid => X_ROWID
1131 );
1132 IF (x_mode = 'S') THEN
1133 igs_sc_gen_001.set_ctx('R');
1134 END IF;
1135 delete from IGS_RE_THESIS_ALL
1136 where ROWID = X_ROWID;
1137 if (sql%notfound) then
1138 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1139 igs_ge_msg_stack.add;
1140 igs_sc_gen_001.unset_ctx('R');
1141 app_exception.raise_exception;
1142 end if;
1143 IF (x_mode = 'S') THEN
1144 igs_sc_gen_001.unset_ctx('R');
1145 END IF;
1146
1147 After_DML (
1148 p_action => 'DELETE',
1149 x_rowid => X_ROWID
1150 );
1151 end DELETE_ROW;
1152 end IGS_RE_THESIS_PKG;