1 PACKAGE BODY igs_ad_prcs_cat_pkg AS
2 /* $Header: IGSAI09B.pls 115.14 2003/10/30 13:10:20 akadam ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_prcs_cat_all%RowType;
5 new_references igs_ad_prcs_cat_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2,
10 x_org_id IN NUMBER,
11 x_admission_cat IN VARCHAR2,
12 x_s_admission_process_type IN VARCHAR2,
13 x_offer_response_offset IN NUMBER,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER,
19 X_CLOSED_IND IN VARCHAR2
20 ) AS
21
22 /*************************************************************
23 Created By : samaresh
24 Date Created By : 15-May-2000
25 Purpose :
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29
30 (reverse chronological order - newest change first)
31 ***************************************************************/
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_AD_PRCS_CAT_ALL
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 Open cur_old_ref_values;
45 Fetch cur_old_ref_values INTO old_references;
46 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
47 Close cur_old_ref_values;
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 Return;
52 END IF;
53 Close cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.org_id := x_org_id;
57 new_references.admission_cat := x_admission_cat;
58 new_references.s_admission_process_type := x_s_admission_process_type;
59 new_references.offer_response_offset := x_offer_response_offset;
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70 new_references.closed_ind := x_closed_ind;
71
72 END Set_Column_Values;
73
74 PROCEDURE BeforeRowInsertUpdateDelete1(
75 p_inserting IN BOOLEAN,
76 p_updating IN BOOLEAN,
77 p_deleting IN BOOLEAN
78 ) AS
79 v_admission_cat IGS_AD_PRCS_CAT_ALL.admission_cat%TYPE;
80 v_message_name varchar2(30);
81 BEGIN
82 -- Set the Admission Category value.
83 IF p_deleting THEN
84 v_admission_cat := old_references.admission_cat;
85 ELSE
86 v_admission_cat := new_references.admission_cat;
87 END IF;
88 -- Validate the admission category closed indicator.
89 IF IGS_AD_VAL_ACCT.admp_val_ac_closed (
90 v_admission_cat,
91 v_message_name) = FALSE THEN
92 Fnd_Message.Set_Name('IGS',v_message_name);
93 IGS_GE_MSG_STACK.ADD;
94 App_Exception.Raise_Exception;
95 END IF;
96 END BeforeRowInsertUpdateDelete1;
97
98
99
100 PROCEDURE Check_Constraints (
101 Column_Name IN VARCHAR2,
102 Column_Value IN VARCHAR2) AS
103 /*************************************************************
104 Created By : samaresh
105 Date Created By : 15-May-2000
106 Purpose :
107 Know limitations, enhancements or remarks
108 Change History
109 Who When What
110
111 (reverse chronological order - newest change first)
112 ***************************************************************/
113
114 BEGIN
115
116 IF column_name IS NULL THEN
117 NULL;
118 ELSIF UPPER(column_name) = 'ADMISSION_CAT' THEN
119 new_references.admission_cat := column_value;
120 ELSIF UPPER(column_name) = 'S_ADMISSION_PROCESS_TYPE' THEN
121 new_references.s_admission_process_type := column_value;
122 ELSIF upper(Column_name) = 'OFFER_RESPONSE_OFFSET' then
123 new_references.offer_response_offset := IGS_GE_NUMBER.TO_NUM(column_value);
124
125 END IF;
126
127
128
129 IF UPPER(Column_Name) = 'ADMISSION_CAT' OR
130 Column_Name IS NULL THEN
131 IF new_references.ADMISSION_CAT <> UPPER(new_references.admission_cat) THEN
132 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
133 IGS_GE_MSG_STACK.ADD;
134 App_Exception.Raise_Exception;
135 END IF;
136 END IF;
137
138 IF UPPER(Column_Name) = 'S_ADMISSION_PROCESS_TYPE' OR
139 Column_Name IS NULL THEN
140 IF new_references.S_ADMISSION_PROCESS_TYPE <> UPPER(new_references.s_admission_process_type) THEN
141 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144 END IF;
145 END IF;
146
147 IF upper(column_name) = 'OFFER_RESPONSE_OFFSET' OR
148 column_name is null Then
149 IF new_references.offer_response_offset < 1 OR
150 new_references.offer_response_offset > 99 Then
151 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
152 IGS_GE_MSG_STACK.ADD;
153 App_Exception.Raise_Exception;
154 END IF;
155 END IF;
156
157 END Check_Constraints;
158
159 PROCEDURE Check_Parent_Existance AS
160 /*************************************************************
161 Created By : samaresh
162 Date Created By : 15-May-2000
163 Purpose :
164 Know limitations, enhancements or remarks
165 Change History
166 Who When What
167
168 (reverse chronological order - newest change first)
169 ***************************************************************/
170
171 BEGIN
172
173 IF (((old_references.admission_cat = new_references.admission_cat)) OR
174 ((new_references.admission_cat IS NULL))) THEN
175 NULL;
176 ELSIF NOT Igs_Ad_Cat_Pkg.Get_PK_For_Validation (
177 new_references.admission_cat ,
178 'N') THEN
179 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END IF;
183
184
185 IF (((old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
186 ((new_references.s_admission_process_type IS NULL))) THEN
187 NULL;
188 ELSE
189 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
190 'ADMISSION_PROCESS_TYPE',
191 new_references.s_admission_process_type
192 ) THEN
193 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END IF;
197 END IF;
198 END Check_Parent_Existance;
199
200 PROCEDURE Check_Child_Existance IS
201 /*************************************************************
202 Created By : samaresh
203 Date Created By : 15-May-2000
204 Purpose :
205 Know limitations, enhancements or remarks
206 Change History
207 Who When What
208 rghosh 04-oct-2002 added IGS_AD_SS_APPL_TYP_PKG.GET_FK_IGS_AD_PRCS_CA
209 since igs_ad_ss_appl_typ is a child of this table
210 as per bug 2599457
211 (reverse chronological order - newest change first)
212 ***************************************************************/
213
214 BEGIN
215 IGS_AD_APPL_PKG.GET_FK_IGS_AD_PRCS_CAT (
216 old_references.admission_cat,
217 old_references.s_admission_process_type
218 );
219
220 IGS_AD_SS_APPL_TYP_PKG.GET_FK_IGS_AD_PRCS_CAT (
221 old_references.admission_cat,
222 old_references.s_admission_process_type
223 );
224
225 IGS_AD_PRD_AD_PRC_CA_PKG.GET_FK_IGS_AD_PRCS_CAT (
226 old_references.admission_cat,
227 old_references.s_admission_process_type
228 );
229
230 IGS_AD_PRCS_CAT_LTR_PKG.GET_FK_IGS_AD_PRCS_CAT (
231 old_references.admission_cat,
232 old_references.s_admission_process_type
233 );
234
235 IGS_AD_PRCS_CAT_STEP_PKG.GET_FK_IGS_AD_PRCS_CAT (
236 old_references.admission_cat,
237 old_references.s_admission_process_type
238 );
239
240 /**********Removed by RRENGARA
241 Reason : As a Part of Build Bug 2602096. Removed the call since this FK check is not needed.
242 Igs_Ad_Interface_Ctl_Pkg.Get_FK_Igs_Ad_Prcs_Cat (
243 old_references.admission_cat,
244 old_references.s_admission_process_type
245 );
246 ***********************/
247
248 END Check_Child_Existance;
249
250 FUNCTION Get_PK_For_Validation (
251 x_admission_cat IN VARCHAR2,
252 x_s_admission_process_type IN VARCHAR2,
253 x_closed_ind IN VARCHAR2
254 ) RETURN BOOLEAN AS
255
256 /*************************************************************
257 Created By : samaresh
258 Date Created By : 15-May-2000
259 Purpose :
260 Know limitations, enhancements or remarks
261 Change History
262 Who When What
263
264 (reverse chronological order - newest change first)
265 ***************************************************************/
266
267 CURSOR cur_rowid IS
268 SELECT rowid
269 FROM igs_ad_prcs_cat_all
270 WHERE admission_cat = x_admission_cat AND
271 s_admission_process_type = x_s_admission_process_type AND
272 closed_ind = NVL(x_closed_ind,closed_ind);
273
274 lv_rowid cur_rowid%RowType;
275
276 BEGIN
277
278 Open cur_rowid;
279 Fetch cur_rowid INTO lv_rowid;
280 IF (cur_rowid%FOUND) THEN
281 Close cur_rowid;
282 Return(TRUE);
283 ELSE
284 Close cur_rowid;
285 Return(FALSE);
286 END IF;
287 END Get_PK_For_Validation;
288
289 PROCEDURE Get_FK_Igs_Ad_Cat (
290 x_admission_cat IN VARCHAR2
291 ) AS
292
293 /*************************************************************
294 Created By : samaresh
295 Date Created By : 15-May-2000
296 Purpose :
297 Know limitations, enhancements or remarks
298 Change History
299 Who When What
300
301 (reverse chronological order - newest change first)
302 ***************************************************************/
303
304 CURSOR cur_rowid IS
305 SELECT rowid
306 FROM IGS_AD_PRCS_CAT_ALL
307 WHERE admission_cat = x_admission_cat ;
308
309 lv_rowid cur_rowid%RowType;
310
311 BEGIN
312
313 Open cur_rowid;
314 Fetch cur_rowid INTO lv_rowid;
315 IF (cur_rowid%FOUND) THEN
316 Close cur_rowid;
317 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APC_AC_FK');
318 IGS_GE_MSG_STACK.ADD;
319 App_Exception.Raise_Exception;
320 Return;
321 END IF;
322 Close cur_rowid;
323
324 END Get_FK_Igs_Ad_Cat;
325
326
327 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW(
328 x_s_admission_process_type IN VARCHAR2
329 ) AS
330 /*************************************************************
331 Created By : samaresh
332 Date Created By : 15-May-2000
333 Purpose :
334 Know limitations, enhancements or remarks
335 Change History
336 Who When What
337
338 (reverse chronological order - newest change first)
339 ***************************************************************/
340 CURSOR cur_rowid IS
341 SELECT rowid
342 FROM IGS_AD_PRCS_CAT_ALL
343 WHERE s_admission_process_type = x_s_admission_process_type ;
344
345 lv_rowid cur_rowid%RowType;
346
347 BEGIN
348
349 Open cur_rowid;
350 Fetch cur_rowid INTO lv_rowid;
351 IF (cur_rowid%FOUND) THEN
352 Close cur_rowid;
353 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APC_SAPT_FK');
354 IGS_GE_MSG_STACK.ADD;
355 App_Exception.Raise_Exception;
356 Return;
357 END IF;
358 Close cur_rowid;
359
360 END GET_FK_IGS_LOOKUPS_VIEW;
361
362
363 PROCEDURE Before_DML (
364 p_action IN VARCHAR2,
365 x_rowid IN VARCHAR2,
366 x_ORG_ID IN NUMBER,
367 x_admission_cat IN VARCHAR2,
368 x_s_admission_process_type IN VARCHAR2,
369 x_offer_response_offset IN NUMBER,
370 x_creation_date IN DATE,
371 x_created_by IN NUMBER,
372 x_last_update_date IN DATE,
373 x_last_updated_by IN NUMBER,
374 x_last_update_login IN NUMBER,
375 x_closed_ind IN VARCHAR2
376 ) AS
377 /*************************************************************
378 Created By : samaresh
379 Date Created By : 15-May-2000
380 Purpose :
381 Know limitations, enhancements or remarks
382 Change History
383 Who When What
384
385 (reverse chronological order - newest change first)
386 ***************************************************************/
387
388 BEGIN
389
390 Set_Column_Values (
391 p_action,
392 x_rowid,
393 x_org_id,
394 x_admission_cat,
395 x_s_admission_process_type,
396 x_offer_response_offset,
397 x_creation_date,
398 x_created_by,
399 x_last_update_date,
400 x_last_updated_by,
401 x_last_update_login,
402 x_closed_ind
403 );
404
405 IF (p_action = 'INSERT') THEN
406 -- Call all the procedures related to Before Insert.
407 beforeRowInsertUpdateDelete1(p_inserting => TRUE);
408 IF Get_Pk_For_Validation(
409 new_references.admission_cat,
410 new_references.s_admission_process_type) THEN
411 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
412 IGS_GE_MSG_STACK.ADD;
413 App_Exception.Raise_Exception;
414 END IF;
415 Check_Constraints;
416 Check_Parent_Existance;
417 ELSIF (p_action = 'UPDATE') THEN
418 -- Call all the procedures related to Before Update.
419 BeforeRowInsertUpdateDelete1 (p_updating => TRUE);
420 Check_Constraints;
421 Check_Parent_Existance;
422 ELSIF (p_action = 'DELETE') THEN
423 -- Call all the procedures related to Before Delete.
424 beforerowinsertupdatedelete1(p_deleting => TRUE);
425 Check_Child_Existance;
426 ELSIF (p_action = 'VALIDATE_INSERT') THEN
427 -- Call all the procedures related to Before Insert.
428 IF Get_PK_For_Validation (
429 new_references.admission_cat,
430 new_references.s_admission_process_type) THEN
431 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
432 IGS_GE_MSG_STACK.ADD;
433 App_Exception.Raise_Exception;
434 END IF;
435 Check_Constraints;
436 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
437 Check_Constraints;
438 ELSIF (p_action = 'VALIDATE_DELETE') THEN
439 Check_Child_Existance;
440 END IF;
441
442 END Before_DML;
443
444 PROCEDURE After_DML (
445 p_action IN VARCHAR2,
446 x_rowid IN VARCHAR2
447 ) IS
448 /*************************************************************
449 Created By : samaresh
450 Date Created By : 15-May-2000
451 Purpose :
452 Know limitations, enhancements or remarks
453 Change History
454 Who When What
455
456 (reverse chronological order - newest change first)
457 ***************************************************************/
458
459 BEGIN
460
461 l_rowid := x_rowid;
462
463 IF (p_action = 'INSERT') THEN
464 -- Call all the procedures related to After Insert.
465 Null;
466 ELSIF (p_action = 'UPDATE') THEN
467 -- Call all the procedures related to After Update.
468 Null;
469 ELSIF (p_action = 'DELETE') THEN
470 -- Call all the procedures related to After Delete.
471 Null;
472 END IF;
473
474 END After_DML;
475
476 procedure INSERT_ROW (
477 X_ROWID in out NOCOPY VARCHAR2,
478 x_ORG_ID IN NUMBER,
479 x_ADMISSION_CAT IN VARCHAR2,
480 x_S_ADMISSION_PROCESS_TYPE IN VARCHAR2,
481 x_OFFER_RESPONSE_OFFSET IN NUMBER,
482 X_MODE in VARCHAR2 ,
483 x_closed_ind IN VARCHAR2
484 ) AS
485 /*************************************************************
486 Created By : samaresh
487 Date Created By : 15-May-2000
488 Purpose :
489 Know limitations, enhancements or remarks
490 Change History
491 Who When What
492
493 (reverse chronological order - newest change first)
494 ***************************************************************/
495
496 cursor C is select ROWID from IGS_AD_PRCS_CAT_ALL
497 where ADMISSION_CAT= X_ADMISSION_CAT
498 and S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE
499 ;
500 X_LAST_UPDATE_DATE DATE ;
501 X_LAST_UPDATED_BY NUMBER ;
502 X_LAST_UPDATE_LOGIN NUMBER ;
503 begin
504 X_LAST_UPDATE_DATE := SYSDATE;
505 if(X_MODE = 'I') then
506 X_LAST_UPDATED_BY := 1;
507 X_LAST_UPDATE_LOGIN := 0;
508 elsif (X_MODE = 'R') then
509 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
510 if X_LAST_UPDATED_BY is NULL then
511 X_LAST_UPDATED_BY := -1;
512 end if;
513 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
514 if X_LAST_UPDATE_LOGIN is NULL then
515 X_LAST_UPDATE_LOGIN := -1;
516 end if;
517 else
518 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
519 IGS_GE_MSG_STACK.ADD;
520 app_exception.raise_exception;
521 end if;
522 Before_DML(
523 p_action=>'INSERT',
524 x_rowid=>X_ROWID,
525 x_org_id => igs_ge_gen_003.get_org_id,
526 x_admission_cat=>X_ADMISSION_CAT,
527 x_s_admission_process_type=>X_S_ADMISSION_PROCESS_TYPE,
528 x_offer_response_offset=>X_OFFER_RESPONSE_OFFSET,
529 x_creation_date=>X_LAST_UPDATE_DATE,
530 x_created_by=>X_LAST_UPDATED_BY,
531 x_last_update_date=>X_LAST_UPDATE_DATE,
532 x_last_updated_by=>X_LAST_UPDATED_BY,
533 x_last_update_login=>X_LAST_UPDATE_LOGIN,
534 x_closed_ind => x_closed_ind);
535 insert into IGS_AD_PRCS_CAT_ALL (
536 ORG_ID
537 ,ADMISSION_CAT
538 ,S_ADMISSION_PROCESS_TYPE
539 ,OFFER_RESPONSE_OFFSET
540 ,CREATION_DATE
541 ,CREATED_BY
542 ,LAST_UPDATE_DATE
543 ,LAST_UPDATED_BY
544 ,LAST_UPDATE_LOGIN
545 ,CLOSED_IND
546 ) values (
547 NEW_REFERENCES.ORG_ID
548 ,NEW_REFERENCES.ADMISSION_CAT
549 ,NEW_REFERENCES.S_ADMISSION_PROCESS_TYPE
550 ,NEW_REFERENCES.OFFER_RESPONSE_OFFSET
551 ,X_LAST_UPDATE_DATE
552 ,X_LAST_UPDATED_BY
553 ,X_LAST_UPDATE_DATE
554 ,X_LAST_UPDATED_BY
555 ,X_LAST_UPDATE_LOGIN
556 ,X_CLOSED_IND
557 );
558 open c;
559 fetch c into X_ROWID;
560 if (c%notfound) then
561 close c;
562 raise no_data_found;
563 end if;
564 close c;
565 After_DML (
566 p_action => 'INSERT' ,
567 x_rowid => X_ROWID );
568 end INSERT_ROW;
569 procedure LOCK_ROW (
570 X_ROWID in VARCHAR2,
571 x_ADMISSION_CAT IN VARCHAR2,
572 x_S_ADMISSION_PROCESS_TYPE IN VARCHAR2,
573 x_OFFER_RESPONSE_OFFSET IN NUMBER,
574 X_CLOSED_IND IN VARCHAR2
575 ) AS
576 /*************************************************************
577 Created By : samaresh
578 Date Created By : 15-May-2000
579 Purpose :
580 Know limitations, enhancements or remarks
581 Change History
582 Who When What
583
584 (reverse chronological order - newest change first)
585 ***************************************************************/
586
587 cursor c1 is select
588 OFFER_RESPONSE_OFFSET,
589 CLOSED_IND
590 from IGS_AD_PRCS_CAT_ALL
591 where ROWID = X_ROWID
592 for update nowait;
593 tlinfo c1%rowtype;
594 begin
595 open c1;
596 fetch c1 into tlinfo;
597 if (c1%notfound) then
598 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
599 IGS_GE_MSG_STACK.ADD;
600 close c1;
601 app_exception.raise_exception;
602 return;
603 end if;
604 close c1;
605 if ( ( (tlinfo.OFFER_RESPONSE_OFFSET = X_OFFER_RESPONSE_OFFSET)
606 OR (tlinfo.closed_ind = x_closed_ind )
607 OR ((tlinfo.OFFER_RESPONSE_OFFSET is null)
608 AND (X_OFFER_RESPONSE_OFFSET is null)))
609 ) then
610 null;
611 else
612 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
613 IGS_GE_MSG_STACK.ADD;
614 app_exception.raise_exception;
615 end if;
616 return;
617 end LOCK_ROW;
618 Procedure UPDATE_ROW (
619 X_ROWID in VARCHAR2,
620 x_ADMISSION_CAT IN VARCHAR2,
621 x_S_ADMISSION_PROCESS_TYPE IN VARCHAR2,
622 x_OFFER_RESPONSE_OFFSET IN NUMBER,
623 X_MODE in VARCHAR2 ,
624 X_CLOSED_IND IN VARCHAR2
625 ) AS
626 /*************************************************************
627 Created By : samaresh
628 Date Created By : 15-May-2000
629 Purpose :
630 Know limitations, enhancements or remarks
631 Change History
632 Who When What
633
634 (reverse chronological order - newest change first)
635 ***************************************************************/
636
637 X_LAST_UPDATE_DATE DATE ;
638 X_LAST_UPDATED_BY NUMBER ;
639 X_LAST_UPDATE_LOGIN NUMBER ;
640 begin
641 X_LAST_UPDATE_DATE := SYSDATE;
642 if(X_MODE = 'I') then
643 X_LAST_UPDATED_BY := 1;
644 X_LAST_UPDATE_LOGIN := 0;
645 elsif (X_MODE = 'R') then
646 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
647 if X_LAST_UPDATED_BY is NULL then
648 X_LAST_UPDATED_BY := -1;
649 end if;
650 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
651 if X_LAST_UPDATE_LOGIN is NULL then
652 X_LAST_UPDATE_LOGIN := -1;
653 end if;
654 else
655 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
656 IGS_GE_MSG_STACK.ADD;
657 app_exception.raise_exception;
658 end if;
659 Before_DML(
660 p_action=>'UPDATE',
661 x_rowid=>X_ROWID,
662 x_admission_cat=>X_ADMISSION_CAT,
663 x_s_admission_process_type=>X_S_ADMISSION_PROCESS_TYPE,
664 x_offer_response_offset=>X_OFFER_RESPONSE_OFFSET,
665 x_creation_date=>X_LAST_UPDATE_DATE,
666 x_created_by=>X_LAST_UPDATED_BY,
667 x_last_update_date=>X_LAST_UPDATE_DATE,
668 x_last_updated_by=>X_LAST_UPDATED_BY,
669 x_last_update_login=>X_LAST_UPDATE_LOGIN,
670 x_closed_ind => x_closed_ind);
671 update IGS_AD_PRCS_CAT_ALL set
672 OFFER_RESPONSE_OFFSET = NEW_REFERENCES.OFFER_RESPONSE_OFFSET,
673 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
674 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
675 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
676 CLOSED_IND = X_CLOSED_IND
677 where ROWID = X_ROWID;
678 if (sql%notfound) then
679 raise no_data_found;
680 end if;
681
682 After_DML (
683 p_action => 'UPDATE' ,
684 x_rowid => X_ROWID
685 );
686 end UPDATE_ROW;
687 procedure ADD_ROW (
688 X_ROWID in out NOCOPY VARCHAR2,
689 x_org_id IN NUMBER,
690 x_ADMISSION_CAT IN VARCHAR2,
691 x_S_ADMISSION_PROCESS_TYPE IN VARCHAR2,
692 x_OFFER_RESPONSE_OFFSET IN NUMBER,
693 X_MODE in VARCHAR2 ,
694 X_CLOSED_IND IN VARCHAR2
695 ) AS
696 /*************************************************************
697 Created By : samaresh
698 Date Created By : 15-May-2000
699 Purpose :
700 Know limitations, enhancements or remarks
701 Change History
702 Who When What
703
704 (reverse chronological order - newest change first)
705 ***************************************************************/
706
707 cursor c1 is select ROWID from IGS_AD_PRCS_CAT_ALL
708 where ADMISSION_CAT= X_ADMISSION_CAT
709 and S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE
710 ;
711 begin
712 open c1;
713 fetch c1 into X_ROWID;
714 if (c1%notfound) then
715 close c1;
716 INSERT_ROW (
717 X_ROWID,
718 x_org_id,
719 X_ADMISSION_CAT,
720 X_S_ADMISSION_PROCESS_TYPE,
721 X_OFFER_RESPONSE_OFFSET,
722 X_MODE,
723 X_CLOSED_IND);
724 return;
725 end if;
726 close c1;
727 UPDATE_ROW (
728 X_ROWID,
729 X_ADMISSION_CAT,
730 X_S_ADMISSION_PROCESS_TYPE,
731 X_OFFER_RESPONSE_OFFSET,
732 X_MODE,
733 X_CLOSED_IND);
734 end ADD_ROW;
735 procedure DELETE_ROW (
736 X_ROWID in VARCHAR2
737 ) AS
738 /*************************************************************
739 Created By : samaresh
740 Date Created By : 15-May-2000
741 Purpose :
742 Know limitations, enhancements or remarks
743 Change History
744 Who When What
745
746 (reverse chronological order - newest change first)
747 ***************************************************************/
748
749 begin
750 Before_DML (
751 p_action => 'DELETE',
752 x_rowid => X_ROWID
753 );
754 delete from IGS_AD_PRCS_CAT_ALL
755 where ROWID = X_ROWID;
756 if (sql%notfound) then
757 raise no_data_found;
758 end if;
759 After_DML (
760 p_action => 'DELETE',
761 x_rowid => X_ROWID
762 );
763 end DELETE_ROW;
764 END igs_ad_prcs_cat_pkg;
[Home] [Help]