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