[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PERD_AD_CAT_PKG
Source
1 package body IGS_AD_PERD_AD_CAT_PKG as
2 /* $Header: IGSAI29B.pls 115.11 2003/10/30 13:19:40 rghosh ship $*/
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_PERD_AD_CAT%RowType;
6 new_references IGS_AD_PERD_AD_CAT%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_adm_cal_type IN VARCHAR2 DEFAULT NULL,
12 x_adm_ci_sequence_number IN NUMBER DEFAULT NULL,
13 x_admission_cat IN VARCHAR2 DEFAULT NULL,
14 x_ci_start_dt IN DATE DEFAULT NULL,
15 x_ci_end_dt IN DATE DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22
23 CURSOR cur_old_ref_values IS
24 SELECT *
25 FROM IGS_AD_PERD_AD_CAT
26 WHERE rowid = x_rowid;
27
28 BEGIN
29
30 l_rowid := x_rowid;
31
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37 Close cur_old_ref_values;
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.adm_cal_type := x_adm_cal_type;
47 new_references.adm_ci_sequence_number := x_adm_ci_sequence_number;
48 new_references.admission_cat := x_admission_cat;
49 new_references.ci_start_dt := TRUNC(x_ci_start_dt);
50 new_references.ci_end_dt := TRUNC(x_ci_end_dt);
51 IF (p_action = 'UPDATE') THEN
52 new_references.creation_date := old_references.creation_date;
53 new_references.created_by := old_references.created_by;
54 ELSE
55 new_references.creation_date := x_creation_date;
56 new_references.created_by := x_created_by;
57 END IF;
58 new_references.last_update_date := x_last_update_date;
59 new_references.last_updated_by := x_last_updated_by;
60 new_references.last_update_login := x_last_update_login;
61
62 END Set_Column_Values;
63
64 PROCEDURE BeforeRowInsertUpdate1(
65 p_inserting IN BOOLEAN DEFAULT FALSE,
66 p_updating IN BOOLEAN DEFAULT FALSE,
67 p_deleting IN BOOLEAN DEFAULT FALSE
68 ) AS
69 v_message_name VARCHAR2(30);
70 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
71 v_ci_start_dt IGS_CA_INST.start_dt%TYPE;
72 v_ci_end_dt IGS_CA_INST.end_dt%TYPE;
73 BEGIN
74 IF p_inserting THEN
75 -- Validate the admission calendar instance
76 IF IGS_AD_VAL_APAC.admp_val_apac_ci(
77 new_references.adm_cal_type,
78 new_references.adm_ci_sequence_number,
79 new_references.admission_cat,
80 v_ci_start_dt,
81 v_ci_end_dt,
82 v_message_name) = FALSE THEN
83 Fnd_Message.Set_Name('IGS',v_message_name);
84 IGS_GE_MSG_STACK.ADD;
85 App_Exception.Raise_Exception;
86 ELSE
87 -- Set start and end dates
88 new_references.ci_start_dt :=TRUNC( v_ci_start_dt);
89 new_references.ci_end_dt := TRUNC(v_ci_end_dt);
90 END IF;
91 -- Validate the admission category
92 IF IGS_AD_VAL_ACCT.admp_val_ac_closed(
93 new_references.admission_cat,
94 v_message_name) = FALSE THEN
95 Fnd_Message.Set_Name('IGS',v_message_name);
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 END IF;
99 END IF;
100
101
102 END BeforeRowInsertUpdate1;
103
104 PROCEDURE Check_Constraints (
105 Column_Name IN VARCHAR2 DEFAULT NULL,
106 Column_Value IN VARCHAR2 DEFAULT NULL
107 )
108 AS
109 BEGIN
110
111 IF column_name is null then
112 NULL;
113 ELSIF upper(Column_name) = 'ADM_CAL_TYPE' then
114 new_references.adm_cal_type := column_value;
115 ELSIF upper(Column_name) = 'ADMISSION_CAT' then
116 new_references.admission_cat := column_value;
117 END IF;
118
119 IF upper(column_name) = 'ADM_CAL_TYPE' OR column_name is null Then
120 IF new_references.adm_cal_type <> UPPER(new_references.adm_cal_type) Then
121 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125 END IF;
126
127 IF upper(column_name) = 'ADMISSION_CAT' OR column_name is null Then
128 IF new_references.admission_cat <> UPPER(new_references.admission_cat) Then
129 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
130 IGS_GE_MSG_STACK.ADD;
131 App_Exception.Raise_Exception;
132 END IF;
133 END IF;
134 END Check_Constraints;
135
136 PROCEDURE Check_Parent_Existance AS
137 BEGIN
138
139 IF (((old_references.admission_cat = new_references.admission_cat)) OR
140 ((new_references.admission_cat IS NULL))) THEN
141 NULL;
142 ELSE
143 IF NOT IGS_AD_CAT_PKG.Get_PK_For_Validation (
144 new_references.admission_cat , 'N'
145 ) THEN
146 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 END IF;
151
152 IF (((old_references.adm_cal_type = new_references.adm_cal_type) AND
153 (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number) AND
154 (TRUNC(old_references.ci_start_dt) = new_references.ci_start_dt) AND
155 (TRUNC(old_references.ci_end_dt) = new_references.ci_end_dt)) OR
156 ((new_references.adm_cal_type IS NULL) OR
157 (new_references.adm_ci_sequence_number IS NULL) OR
158 (new_references.ci_start_dt IS NULL) OR
159 (new_references.ci_end_dt IS NULL))) THEN
160 NULL;
161
162 ELSE
163 IF NOT IGS_CA_INST_PKG.Get_UK_For_Validation (
164 new_references.adm_cal_type,
165 new_references.adm_ci_sequence_number,
166 new_references.ci_start_dt,
167 new_references.ci_end_dt
168 ) THEN
169 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172 END IF;
173 END IF;
174 END Check_Parent_Existance;
175
176 PROCEDURE Check_Child_Existance AS
177 BEGIN
178
179 IGS_AD_PRD_AD_PRC_CA_PKG.GET_FK_IGS_AD_PERD_AD_CAT (
180 old_references.adm_cal_type,
181 old_references.adm_ci_sequence_number,
182 old_references.admission_cat
183 );
184
185 IGS_AD_PECRS_OFOP_DT_PKG.GET_FK_IGS_AD_PERD_AD_CAT (
186 old_references.adm_cal_type,
187 old_references.adm_ci_sequence_number,
188 old_references.admission_cat
189 );
190
191 END Check_Child_Existance;
192
193 FUNCTION Get_PK_For_Validation (
194 x_adm_cal_type IN VARCHAR2,
195 x_adm_ci_sequence_number IN NUMBER,
196 x_admission_cat IN VARCHAR2
197 )
198 RETURN BOOLEAN
199 AS
200 CURSOR cur_rowid IS
201 SELECT rowid
202 FROM IGS_AD_PERD_AD_CAT
203 WHERE adm_cal_type = x_adm_cal_type
204 AND adm_ci_sequence_number = x_adm_ci_sequence_number
205 AND admission_cat = x_admission_cat;
206
207 lv_rowid cur_rowid%RowType;
208
209 BEGIN
210
211 Open cur_rowid;
212 Fetch cur_rowid INTO lv_rowid;
213 IF (cur_rowid%FOUND) THEN
214 Close cur_rowid;
215 Return (TRUE);
216 ELSE
217 Close cur_rowid;
218 Return (FALSE);
219 END IF;
220 END Get_PK_For_Validation;
221
222 PROCEDURE GET_FK_IGS_AD_CAT (
223 x_admission_cat IN VARCHAR2
224 ) AS
225
226 CURSOR cur_rowid IS
227 SELECT rowid
228 FROM IGS_AD_PERD_AD_CAT
229 WHERE admission_cat = x_admission_cat ;
230
231 lv_rowid cur_rowid%RowType;
232
233 BEGIN
234
235 Open cur_rowid;
236 Fetch cur_rowid INTO lv_rowid;
237 IF (cur_rowid%FOUND) THEN
238 Close cur_rowid;
239 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APAC_AC_FK');
240 IGS_GE_MSG_STACK.ADD;
241 App_Exception.Raise_Exception;
242 Return;
243 END IF;
244 Close cur_rowid;
245
246 END GET_FK_IGS_AD_CAT;
247
248 PROCEDURE GET_UFK_IGS_CA_INST (
249 x_cal_type IN VARCHAR2,
250 x_sequence_number IN NUMBER,
251 x_start_dt IN DATE,
252 x_end_dt IN DATE
253 ) AS
254
255 CURSOR cur_rowid IS
256 SELECT rowid
257 FROM IGS_AD_PERD_AD_CAT
258 WHERE adm_cal_type = x_cal_type
259 AND adm_ci_sequence_number = x_sequence_number
260 AND TRUNC(ci_start_dt) = TRUNC(x_start_dt)
261 AND TRUNC(ci_end_dt) = TRUNC(x_end_dt) ;
262
263 lv_rowid cur_rowid%RowType;
264
265 BEGIN
266
267 Open cur_rowid;
268 Fetch cur_rowid INTO lv_rowid;
269 IF (cur_rowid%FOUND) THEN
270 Close cur_rowid;
271 Fnd_Message.Set_Name ('IGS', 'IGS_CA_APAC_CI_UFK');
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 Return;
275 END IF;
276 Close cur_rowid;
277
278 END GET_UFK_IGS_CA_INST;
279
280 PROCEDURE Before_DML (
281 p_action IN VARCHAR2,
282 x_rowid IN VARCHAR2 DEFAULT NULL,
283 x_adm_cal_type IN VARCHAR2 DEFAULT NULL,
284 x_adm_ci_sequence_number IN NUMBER DEFAULT NULL,
285 x_admission_cat IN VARCHAR2 DEFAULT NULL,
286 x_ci_start_dt IN DATE DEFAULT NULL,
287 x_ci_end_dt IN DATE DEFAULT NULL,
288 x_creation_date IN DATE DEFAULT NULL,
289 x_created_by IN NUMBER DEFAULT NULL,
290 x_last_update_date IN DATE DEFAULT NULL,
291 x_last_updated_by IN NUMBER DEFAULT NULL,
292 x_last_update_login IN NUMBER DEFAULT NULL
293 ) AS
294 BEGIN
295
296 Set_Column_Values (
297 p_action,
298 x_rowid,
299 x_adm_cal_type,
300 x_adm_ci_sequence_number,
301 x_admission_cat,
302 x_ci_start_dt,
303 x_ci_end_dt,
304 x_creation_date,
305 x_created_by,
306 x_last_update_date,
307 x_last_updated_by,
308 x_last_update_login
309 );
310
311 IF (p_action = 'INSERT') THEN
312 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
313 IF Get_PK_For_Validation (
314 new_references.adm_cal_type,
315 new_references.adm_ci_sequence_number,
316 new_references.admission_cat
317 ) THEN
318 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 END IF;
322 Check_Constraints;
323 Check_Parent_Existance;
324 ELSIF (p_action = 'UPDATE') THEN
325 BeforeRowInsertUpdate1 ( p_updating => TRUE );
326 Check_Constraints;
327 Check_Parent_Existance;
328 ELSIF (p_action = 'DELETE') THEN
329 Check_Child_Existance;
330 ELSIF (p_action = 'VALIDATE_INSERT') THEN
331 IF Get_PK_For_Validation (
332 new_references.adm_cal_type,
333 new_references.adm_ci_sequence_number,
334 new_references.admission_cat
335 ) THEN
336 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
337 IGS_GE_MSG_STACK.ADD;
338 App_Exception.Raise_Exception;
339 END IF;
340 Check_Constraints;
341 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
342 Check_Constraints;
343 ELSIF (p_action = 'VALIDATE_DELETE') THEN
344 Check_Child_Existance;
345 END IF;
346 END Before_DML;
347
348 procedure INSERT_ROW (
349 X_ROWID in out NOCOPY VARCHAR2,
350 X_ADM_CAL_TYPE in VARCHAR2,
351 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
352 X_ADMISSION_CAT in VARCHAR2,
353 X_CI_START_DT in DATE,
354 X_CI_END_DT in DATE,
355 X_MODE in VARCHAR2 default 'R'
356 ) AS
357 cursor C is select ROWID from IGS_AD_PERD_AD_CAT
358 where ADM_CAL_TYPE = X_ADM_CAL_TYPE
359 and ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER
360 and ADMISSION_CAT = X_ADMISSION_CAT;
361 X_LAST_UPDATE_DATE DATE;
362 X_LAST_UPDATED_BY NUMBER;
363 X_LAST_UPDATE_LOGIN NUMBER;
364 X_REQUEST_ID NUMBER;
365 X_PROGRAM_ID NUMBER;
366 X_PROGRAM_APPLICATION_ID NUMBER;
367 X_PROGRAM_UPDATE_DATE DATE;
368 begin
369 X_LAST_UPDATE_DATE := SYSDATE;
370 if(X_MODE = 'I') then
371 X_LAST_UPDATED_BY := 1;
372 X_LAST_UPDATE_LOGIN := 0;
373 elsif (X_MODE = 'R') then
374 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
375 if X_LAST_UPDATED_BY is NULL then
376 X_LAST_UPDATED_BY := -1;
377 end if;
378 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
379 if X_LAST_UPDATE_LOGIN is NULL then
380 X_LAST_UPDATE_LOGIN := -1;
381 end if;
382 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
383 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
384 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
385 if (X_REQUEST_ID = -1) then
386 X_REQUEST_ID := NULL;
387 X_PROGRAM_ID := NULL;
388 X_PROGRAM_APPLICATION_ID := NULL;
389 X_PROGRAM_UPDATE_DATE := NULL;
390 else
391 X_PROGRAM_UPDATE_DATE := SYSDATE;
392 end if;
393 else
394 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
395 IGS_GE_MSG_STACK.ADD;
396 app_exception.raise_exception;
397 end if;
398
399 Before_DML(p_action =>'INSERT',
400 x_rowid => X_ROWID,
401 x_adm_cal_type => X_ADM_CAL_TYPE,
402 x_adm_ci_sequence_number => X_ADM_CI_SEQUENCE_NUMBER,
403 x_admission_cat => X_ADMISSION_CAT,
404 x_ci_start_dt => X_CI_START_DT,
405 x_ci_end_dt => X_CI_END_DT,
406 x_creation_date => X_LAST_UPDATE_DATE,
407 x_created_by => X_LAST_UPDATED_BY,
408 x_last_update_date => X_LAST_UPDATE_DATE,
409 x_last_updated_by => X_LAST_UPDATED_BY,
410 x_last_update_login => X_LAST_UPDATE_LOGIN
411 );
412
413 insert into IGS_AD_PERD_AD_CAT (
414 ADM_CAL_TYPE,
415 ADM_CI_SEQUENCE_NUMBER,
416 ADMISSION_CAT,
417 CI_START_DT,
418 CI_END_DT,
419 CREATION_DATE,
420 CREATED_BY,
421 LAST_UPDATE_DATE,
422 LAST_UPDATED_BY,
423 LAST_UPDATE_LOGIN,
424 REQUEST_ID,
425 PROGRAM_ID,
426 PROGRAM_APPLICATION_ID,
427 PROGRAM_UPDATE_DATE
428 ) values (
429 NEW_REFERENCES.ADM_CAL_TYPE,
430 NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
431 NEW_REFERENCES.ADMISSION_CAT,
432 NEW_REFERENCES.CI_START_DT,
433 NEW_REFERENCES.CI_END_DT,
434 NEW_REFERENCES.CREATION_DATE,
435 NEW_REFERENCES.CREATED_BY,
436 X_LAST_UPDATE_DATE,
437 X_LAST_UPDATED_BY,
438 X_LAST_UPDATE_LOGIN,
439 X_REQUEST_ID,
440 X_PROGRAM_ID,
441 X_PROGRAM_APPLICATION_ID,
442 X_PROGRAM_UPDATE_DATE
443 );
444 open c;
445 fetch c into X_ROWID;
446 if (c%notfound) then
447 close c;
448 raise no_data_found;
449 end if;
450 close c;
451
452 end INSERT_ROW;
453
454 procedure LOCK_ROW (
455 X_ROWID in VARCHAR2,
456 X_ADM_CAL_TYPE in VARCHAR2,
457 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
458 X_ADMISSION_CAT in VARCHAR2,
459 X_CI_START_DT in DATE,
460 X_CI_END_DT in DATE
461 ) AS
462 cursor c1 is select
463 CI_START_DT,
464 CI_END_DT
465 from IGS_AD_PERD_AD_CAT
466 where ROWID = X_ROWID for update nowait;
467 tlinfo c1%rowtype;
468
469 begin
470 open c1;
471 fetch c1 into tlinfo;
472 if (c1%notfound) then
473 close c1;
474 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
475 IGS_GE_MSG_STACK.ADD;
476 app_exception.raise_exception;
477 return;
478 end if;
479 close c1;
480
481 if ( (TRUNC(tlinfo.CI_START_DT) = TRUNC(X_CI_START_DT))
482 AND (TRUNC(tlinfo.CI_END_DT) = TRUNC(X_CI_END_DT))
483 ) then
484 null;
485 else
486 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
487 IGS_GE_MSG_STACK.ADD;
488 app_exception.raise_exception;
489 end if;
490 return;
491 end LOCK_ROW;
492
493 procedure UPDATE_ROW (
494 X_ROWID in VARCHAR2,
495 X_ADM_CAL_TYPE in VARCHAR2,
496 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
497 X_ADMISSION_CAT in VARCHAR2,
498 X_CI_START_DT in DATE,
499 X_CI_END_DT in DATE,
500 X_MODE in VARCHAR2 default 'R'
501 ) AS
502 X_LAST_UPDATE_DATE DATE;
503 X_LAST_UPDATED_BY NUMBER;
504 X_LAST_UPDATE_LOGIN NUMBER;
505 X_REQUEST_ID NUMBER;
506 X_PROGRAM_ID NUMBER;
507 X_PROGRAM_APPLICATION_ID NUMBER;
508 X_PROGRAM_UPDATE_DATE DATE;
509 begin
510 X_LAST_UPDATE_DATE := SYSDATE;
511 if(X_MODE = 'I') then
512 X_LAST_UPDATED_BY := 1;
513 X_LAST_UPDATE_LOGIN := 0;
514 elsif (X_MODE = 'R') then
515 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
516 if X_LAST_UPDATED_BY is NULL then
517 X_LAST_UPDATED_BY := -1;
518 end if;
519 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
520 if X_LAST_UPDATE_LOGIN is NULL then
521 X_LAST_UPDATE_LOGIN := -1;
522 end if;
523 else
524 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
525 IGS_GE_MSG_STACK.ADD;
526 app_exception.raise_exception;
527 end if;
528
529 Before_DML(p_action =>'UPDATE',
530 x_rowid =>X_ROWID,
531 x_adm_cal_type => X_ADM_CAL_TYPE,
532 x_adm_ci_sequence_number => X_ADM_CI_SEQUENCE_NUMBER,
533 x_admission_cat => X_ADMISSION_CAT,
534 x_ci_start_dt => X_CI_START_DT,
535 x_ci_end_dt => X_CI_END_DT,
536 x_creation_date => X_LAST_UPDATE_DATE,
537 x_created_by => X_LAST_UPDATED_BY,
538 x_last_update_date => X_LAST_UPDATE_DATE,
539 x_last_updated_by => X_LAST_UPDATED_BY,
540 x_last_update_login => X_LAST_UPDATE_LOGIN
541 );
542
543 if (X_MODE = 'R') then
544 X_REQUEST_ID :=FND_GLOBAL.CONC_REQUEST_ID;
545 X_PROGRAM_ID :=FND_GLOBAL.CONC_PROGRAM_ID;
546 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
547 if (X_REQUEST_ID = -1) then
548 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
549 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
550 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
551 else
552 X_PROGRAM_UPDATE_DATE := SYSDATE;
553 end if;
554 end if;
555 update IGS_AD_PERD_AD_CAT set
556 CI_START_DT = NEW_REFERENCES.CI_START_DT,
557 CI_END_DT = NEW_REFERENCES.CI_END_DT,
558 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
559 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
560 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
561 REQUEST_ID = X_REQUEST_ID,
562 PROGRAM_ID = X_PROGRAM_ID,
563 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
564 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
565 where ROWID = X_ROWID
566 ;
567 if (sql%notfound) then
568 raise no_data_found;
569 end if;
570 end UPDATE_ROW;
571
572 procedure ADD_ROW (
573 X_ROWID in out NOCOPY VARCHAR2,
574 X_ADM_CAL_TYPE in VARCHAR2,
575 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
576 X_ADMISSION_CAT in VARCHAR2,
577 X_CI_START_DT in DATE,
578 X_CI_END_DT in DATE,
579 X_MODE in VARCHAR2 default 'R'
580 ) AS
581 cursor c1 is select rowid from IGS_AD_PERD_AD_CAT
582 where ADM_CAL_TYPE = X_ADM_CAL_TYPE
583 and ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER
584 and ADMISSION_CAT = X_ADMISSION_CAT
585 ;
586 begin
587 open c1;
588 fetch c1 into X_ROWID;
589 if (c1%notfound) then
590 close c1;
591 INSERT_ROW (
592 X_ROWID,
593 X_ADM_CAL_TYPE,
594 X_ADM_CI_SEQUENCE_NUMBER,
595 X_ADMISSION_CAT,
596 X_CI_START_DT,
597 X_CI_END_DT,
598 X_MODE);
599 return;
600 end if;
601 close c1;
602 UPDATE_ROW (
603 X_ROWID,
604 X_ADM_CAL_TYPE,
605 X_ADM_CI_SEQUENCE_NUMBER,
606 X_ADMISSION_CAT,
607 X_CI_START_DT,
608 X_CI_END_DT,
609 X_MODE);
610 end ADD_ROW;
611
612 procedure DELETE_ROW (
613 X_ROWID in VARCHAR2
614 ) AS
615 begin
616
617 Before_DML(
618 p_action =>'DELETE',
619 x_rowid => X_ROWID
620 );
621 delete from IGS_AD_PERD_AD_CAT
622 where ROWID = X_ROWID;
623 if (sql%notfound) then
624 raise no_data_found;
625 end if;
626 end DELETE_ROW;
627
628 end IGS_AD_PERD_AD_CAT_PKG;