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