[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_DFT_LOAD_APPO_PKG
Source
1 package body IGS_ST_DFT_LOAD_APPO_PKG as
2 /* $Header: IGSVI02B.pls 115.3 2002/11/29 04:31:06 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_ST_DFT_LOAD_APPO%RowType;
6 new_references IGS_ST_DFT_LOAD_APPO%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_cal_type IN VARCHAR2 DEFAULT NULL,
12 x_ci_sequence_number IN NUMBER DEFAULT NULL,
13 x_teach_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_percentage IN NUMBER DEFAULT NULL,
15 x_second_percentage IN NUMBER 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_ST_DFT_LOAD_APPO
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.cal_type := x_cal_type;
47 new_references.ci_sequence_number := x_ci_sequence_number;
48 new_references.teach_cal_type := x_teach_cal_type;
49 new_references.percentage := x_percentage;
50 new_references.second_percentage := x_second_percentage;
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 BeforeRowInsertUpdateDelete1(
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_cal_type IGS_ST_DFT_LOAD_APPO.cal_type%TYPE;
71 v_ci_sequence_number IGS_ST_DFT_LOAD_APPO.ci_sequence_number%TYPE;
72 BEGIN
73 IF p_inserting OR p_updating THEN
74 v_cal_type := new_references.cal_type;
75 v_ci_sequence_number := new_references.ci_sequence_number;
76 ELSE
77 v_cal_type := old_references.cal_type;
78 v_ci_sequence_number := old_references.ci_sequence_number;
79 END IF;
80 -- Validate if insert, update or delete is allowed.
81 IF IGS_EN_VAL_DLA.stap_val_ci_status (
82 v_cal_type,
83 v_ci_sequence_number,
84 v_message_name) = FALSE THEN
85 Fnd_Message.Set_Name('IGS',v_message_name);
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88 END IF;
89 -- Validate that inserts/updates are allowed
90 IF p_inserting OR p_updating THEN
91 IF IGS_EN_VAL_DLA.ENRP_VAL_DLA_CAT_LD(new_references.cal_type
92 ,v_message_name) = FALSE THEN
93 Fnd_Message.Set_Name('IGS',v_message_name);
94 IGS_GE_MSG_STACK.ADD;
95 App_Exception.Raise_Exception;
96 END IF;
97 IF IGS_EN_VAL_DLA.ENRP_VAL_DLA_CAT(new_references.teach_cal_type
98 ,v_message_name) = FALSE THEN
99 Fnd_Message.Set_Name('IGS',v_message_name);
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END IF;
103 END IF;
104
105
106 END BeforeRowInsertUpdateDelete1;
107
108 PROCEDURE Check_Constraints (
109 Column_Name IN VARCHAR2 DEFAULT NULL ,
110 Column_Value IN VARCHAR2 DEFAULT NULL
111 ) as
112 Begin
113 IF Column_Name is null THEN
114 NULL;
115 ELSIF upper(Column_name) = 'CAL_TYPE' THEN
116 new_references.CAL_TYPE:= COLUMN_VALUE ;
117
118 ELSIF upper(Column_name) = 'TEACH_CAL_TYPE' THEN
119 new_references.TEACH_CAL_TYPE:= COLUMN_VALUE ;
120
121 ELSIF upper(Column_name) = 'PERCENTAGE' THEN
122 new_references.PERCENTAGE:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
123
124 ELSIF upper(Column_name) = 'SECOND_PERCENTAGE' THEN
125 new_references.SECOND_PERCENTAGE:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
126
127 END IF ;
128
129 IF upper(Column_name) = 'CAL_TYPE' OR COLUMN_NAME IS NULL THEN
130 IF new_references.CAL_TYPE<> upper(new_references.CAL_TYPE) then
131 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception ;
134 END IF;
135
136 END IF ;
137
138 IF upper(Column_name) = 'TEACH_CAL_TYPE' OR COLUMN_NAME IS NULL THEN
139 IF new_references.TEACH_CAL_TYPE<> upper(new_references.TEACH_CAL_TYPE) then
140 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception ;
143 END IF;
144
145 END IF ;
146
147 IF upper(Column_name) = 'PERCENTAGE' OR COLUMN_NAME IS NULL THEN
148 IF new_references.PERCENTAGE < 0 or new_references.PERCENTAGE > 100 then
149 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
150 IGS_GE_MSG_STACK.ADD;
151 App_Exception.Raise_Exception ;
152 END IF;
153
154 END IF ;
155
156 IF upper(Column_name) = 'SECOND_PERCENTAGE' OR COLUMN_NAME IS NULL THEN
157 IF new_references.SECOND_PERCENTAGE < 0 or new_references.SECOND_PERCENTAGE > 100 then
158 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception ;
161 END IF;
162
163 END IF ;
164
165 END Check_Constraints;
166
167
168
169 PROCEDURE Check_Parent_Existance as
170 BEGIN
171
172 IF (((old_references.teach_cal_type = new_references.teach_cal_type)) OR
173 ((new_references.teach_cal_type IS NULL))) THEN
174 NULL;
175 ELSE
176 IF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
177 new_references.cal_type
178 ) 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 IF (((old_references.cal_type = new_references.cal_type) AND
185 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
186 ((new_references.cal_type IS NULL) OR
187 (new_references.ci_sequence_number IS NULL))) THEN
188 NULL;
189 ELSE
190 IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
191 new_references.cal_type,
192 new_references.ci_sequence_number
193 ) THEN
194 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
195 IGS_GE_MSG_STACK.ADD;
196 App_Exception.Raise_Exception;
197 END IF;
198 END IF;
199 END IF ;
200 END Check_Parent_Existance;
201
202 PROCEDURE Check_Child_Existance as
203 BEGIN
204
205 IGS_AD_ADM_UT_STT_LD_PKG.GET_FK_IGS_ST_DFT_LOAD_APPO (
206 old_references.cal_type,
207 old_references.ci_sequence_number,
208 old_references.teach_cal_type
209 );
210
211 IGS_ST_GVTSEMLOAD_OV_PKG.GET_FK_IGS_ST_DFT_LOAD_APPO (
212 old_references.cal_type,
213 old_references.ci_sequence_number,
214 old_references.teach_cal_type
215 );
216
217 IGS_ST_UNT_LOAD_APPO_PKG.GET_FK_IGS_ST_DFT_LOAD_APPO (
218 old_references.cal_type,
219 old_references.ci_sequence_number,
220 old_references.teach_cal_type
221 );
222
223 END Check_Child_Existance;
224
225 FUNCTION Get_PK_For_Validation (
226 x_cal_type IN VARCHAR2,
227 x_ci_sequence_number IN NUMBER,
228 x_teach_cal_type IN VARCHAR2
229 ) RETURN BOOLEAN
230 as
231
232 CURSOR cur_rowid IS
233 SELECT rowid
234 FROM IGS_ST_DFT_LOAD_APPO
235 WHERE cal_type = x_cal_type
236 AND ci_sequence_number = x_ci_sequence_number
237 AND teach_cal_type = x_teach_cal_type
238 FOR UPDATE NOWAIT;
239
240 lv_rowid cur_rowid%RowType;
241
242 BEGIN
243
244 Open cur_rowid;
245 Fetch cur_rowid INTO lv_rowid;
246 IF (cur_rowid%FOUND) THEN
247 Close cur_rowid;
248 Return (TRUE);
249 ELSE
250 Close cur_rowid;
251 Return (FALSE);
252 END IF;
253
254 END Get_PK_For_Validation;
255
256 PROCEDURE GET_FK_IGS_CA_TYPE (
257 x_cal_type IN VARCHAR2
258 ) as
259
260 CURSOR cur_rowid IS
261 SELECT rowid
262 FROM IGS_ST_DFT_LOAD_APPO
263 WHERE teach_cal_type = x_cal_type ;
264
265 lv_rowid cur_rowid%RowType;
266
267 BEGIN
268
269 Open cur_rowid;
270 Fetch cur_rowid INTO lv_rowid;
271 IF (cur_rowid%FOUND) THEN
272 Close cur_rowid;
273 Fnd_Message.Set_Name ('IGS', 'IGS_ST_DLA_CAT_FK');
274 IGS_GE_MSG_STACK.ADD;
275 App_Exception.Raise_Exception;
276 Return;
277 END IF;
278 Close cur_rowid;
279
280 END GET_FK_IGS_CA_TYPE;
281
282 PROCEDURE GET_FK_IGS_CA_INST (
283 x_cal_type IN VARCHAR2,
284 x_sequence_number IN NUMBER
285 ) as
286
287 CURSOR cur_rowid IS
288 SELECT rowid
289 FROM IGS_ST_DFT_LOAD_APPO
290 WHERE cal_type = x_cal_type
291 AND ci_sequence_number = x_sequence_number ;
292
293 lv_rowid cur_rowid%RowType;
294
295 BEGIN
296
297 Open cur_rowid;
298 Fetch cur_rowid INTO lv_rowid;
299 IF (cur_rowid%FOUND) THEN
300 Close cur_rowid;
301 Fnd_Message.Set_Name ('IGS', 'IGS_ST_DLA_CI_FK');
302 IGS_GE_MSG_STACK.ADD;
303 App_Exception.Raise_Exception;
304 Return;
305 END IF;
306 Close cur_rowid;
307
308 END GET_FK_IGS_CA_INST;
309
310 PROCEDURE Before_DML (
311 p_action IN VARCHAR2,
312 x_rowid IN VARCHAR2 DEFAULT NULL,
313 x_cal_type IN VARCHAR2 DEFAULT NULL,
314 x_ci_sequence_number IN NUMBER DEFAULT NULL,
315 x_teach_cal_type IN VARCHAR2 DEFAULT NULL,
316 x_percentage IN NUMBER DEFAULT NULL,
317 x_second_percentage IN NUMBER DEFAULT NULL,
318 x_creation_date IN DATE DEFAULT NULL,
319 x_created_by IN NUMBER DEFAULT NULL,
320 x_last_update_date IN DATE DEFAULT NULL,
321 x_last_updated_by IN NUMBER DEFAULT NULL,
322 x_last_update_login IN NUMBER DEFAULT NULL
323 ) as
324 BEGIN
325
326 Set_Column_Values (
327 p_action,
328 x_rowid,
329 x_cal_type,
330 x_ci_sequence_number,
331 x_teach_cal_type,
332 x_percentage,
333 x_second_percentage,
334 x_creation_date,
335 x_created_by,
336 x_last_update_date,
337 x_last_updated_by,
338 x_last_update_login
339 );
340
341 IF (p_action = 'INSERT') THEN
342 -- Call all the procedures related to Before Insert.
343 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
344 IF Get_PK_For_Validation (
345 new_references.cal_type ,
346 new_references.ci_sequence_number ,
347 new_references.teach_cal_type
348 ) THEN
349 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception;
352 END IF;
353 Check_Constraints;
354 Check_Parent_Existance;
355 ELSIF (p_action = 'UPDATE') THEN
356 -- Call all the procedures related to Before Update.
357 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
358 Check_Constraints;
359 Check_Parent_Existance;
360 ELSIF (p_action = 'DELETE') THEN
361 -- Call all the procedures related to Before Delete.
362 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
363 Check_Child_Existance;
364 ELSIF (p_action = 'VALIDATE_INSERT') THEN
365 IF Get_PK_For_Validation (
366 new_references.cal_type ,
367 new_references.ci_sequence_number ,
368 new_references.teach_cal_type
369 ) THEN
370 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
371 IGS_GE_MSG_STACK.ADD;
372 App_Exception.Raise_Exception;
373 END IF;
374 Check_Constraints;
375 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
376 Check_Constraints;
377
378 ELSIF (p_action = 'VALIDATE_DELETE') THEN
379 Check_Child_Existance;
380
381 END IF;
382
383 END Before_DML;
384
385 PROCEDURE After_DML (
386 p_action IN VARCHAR2,
387 x_rowid IN VARCHAR2
388 ) as
389 BEGIN
390
391 l_rowid := x_rowid;
392
393
394 END After_DML;
395
396
397
398 procedure INSERT_ROW (
399 X_ROWID in out NOCOPY VARCHAR2,
400 X_CAL_TYPE in VARCHAR2,
401 X_CI_SEQUENCE_NUMBER in NUMBER,
402 X_TEACH_CAL_TYPE in VARCHAR2,
403 X_PERCENTAGE in NUMBER,
404 X_SECOND_PERCENTAGE in NUMBER,
405 X_MODE in VARCHAR2 default 'R'
406 ) as
407 cursor C is select ROWID from IGS_ST_DFT_LOAD_APPO
408 where CAL_TYPE = X_CAL_TYPE
409 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
410 and TEACH_CAL_TYPE = X_TEACH_CAL_TYPE;
411 X_LAST_UPDATE_DATE DATE;
412 X_LAST_UPDATED_BY NUMBER;
413 X_LAST_UPDATE_LOGIN NUMBER;
414 begin
415 X_LAST_UPDATE_DATE := SYSDATE;
416 if(X_MODE = 'I') then
417 X_LAST_UPDATED_BY := 1;
418 X_LAST_UPDATE_LOGIN := 0;
419 elsif (X_MODE = 'R') then
420 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
421 if X_LAST_UPDATED_BY is NULL then
422 X_LAST_UPDATED_BY := -1;
423 end if;
424 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
425 if X_LAST_UPDATE_LOGIN is NULL then
426 X_LAST_UPDATE_LOGIN := -1;
427 end if;
428 else
429 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
430 IGS_GE_MSG_STACK.ADD;
431 app_exception.raise_exception;
432 end if;
433
434
435 Before_DML(
436 p_action=>'INSERT' ,
437 x_rowid=>X_ROWID ,
438 x_cal_type => X_CAL_TYPE,
439 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER ,
440 x_teach_cal_type => X_TEACH_CAL_TYPE,
441 x_percentage => NVL(X_PERCENTAGE,100) ,
442 x_second_percentage => X_SECOND_PERCENTAGE ,
443 x_creation_date => X_LAST_UPDATE_DATE ,
444 x_created_by=>X_LAST_UPDATED_BY ,
445 x_last_update_date=>X_LAST_UPDATE_DATE ,
446 x_last_updated_by=>X_LAST_UPDATED_BY ,
447 x_last_update_login=> X_LAST_UPDATE_LOGIN
448 );
449
450 insert into IGS_ST_DFT_LOAD_APPO (
451 CAL_TYPE,
452 CI_SEQUENCE_NUMBER,
453 TEACH_CAL_TYPE,
454 PERCENTAGE,
455 SECOND_PERCENTAGE,
456 CREATION_DATE,
457 CREATED_BY,
458 LAST_UPDATE_DATE,
459 LAST_UPDATED_BY,
460 LAST_UPDATE_LOGIN
461 ) values (
462 NEW_REFERENCES.CAL_TYPE,
463 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
464 NEW_REFERENCES.TEACH_CAL_TYPE,
465 NEW_REFERENCES.PERCENTAGE,
466 NEW_REFERENCES.SECOND_PERCENTAGE,
467 X_LAST_UPDATE_DATE,
468 X_LAST_UPDATED_BY,
469 X_LAST_UPDATE_DATE,
470 X_LAST_UPDATED_BY,
471 X_LAST_UPDATE_LOGIN
472 );
473
474 open c;
475 fetch c into X_ROWID;
476 if (c%notfound) then
477 close c;
478 raise no_data_found;
479 end if;
480 close c;
481
482 After_DML(
483 p_action=>'INSERT',
484 x_rowid=> X_ROWID
485 );
486
487 end INSERT_ROW;
488
489 procedure LOCK_ROW (
490 X_ROWID in VARCHAR2,
491 X_CAL_TYPE in VARCHAR2,
492 X_CI_SEQUENCE_NUMBER in NUMBER,
493 X_TEACH_CAL_TYPE in VARCHAR2,
494 X_PERCENTAGE in NUMBER,
495 X_SECOND_PERCENTAGE in NUMBER
496 ) as
497 cursor c1 is select
498 PERCENTAGE,
499 SECOND_PERCENTAGE
500 from IGS_ST_DFT_LOAD_APPO
501 where ROWID = X_ROWID for update nowait;
502 tlinfo c1%rowtype;
503
504 begin
505 open c1;
506 fetch c1 into tlinfo;
507 if (c1%notfound) then
508 close c1;
509 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
510 IGS_GE_MSG_STACK.ADD;
511 app_exception.raise_exception;
512 return;
513 end if;
514 close c1;
515
516 if ( (tlinfo.PERCENTAGE = X_PERCENTAGE)
517 AND ((tlinfo.SECOND_PERCENTAGE = X_SECOND_PERCENTAGE)
518 OR ((tlinfo.SECOND_PERCENTAGE is null)
519 AND (X_SECOND_PERCENTAGE is null)))
520 ) then
521 null;
522 else
523 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
524 IGS_GE_MSG_STACK.ADD;
525 app_exception.raise_exception;
526 end if;
527 return;
528 end LOCK_ROW;
529
530 procedure UPDATE_ROW (
531 X_ROWID in VARCHAR2,
532 X_CAL_TYPE in VARCHAR2,
533 X_CI_SEQUENCE_NUMBER in NUMBER,
534 X_TEACH_CAL_TYPE in VARCHAR2,
535 X_PERCENTAGE in NUMBER,
536 X_SECOND_PERCENTAGE in NUMBER,
537 X_MODE in VARCHAR2 default 'R'
538 ) as
539 X_LAST_UPDATE_DATE DATE;
540 X_LAST_UPDATED_BY NUMBER;
541 X_LAST_UPDATE_LOGIN NUMBER;
542 begin
543 X_LAST_UPDATE_DATE := SYSDATE;
544 if(X_MODE = 'I') then
545 X_LAST_UPDATED_BY := 1;
546 X_LAST_UPDATE_LOGIN := 0;
547 elsif (X_MODE = 'R') then
548 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
549 if X_LAST_UPDATED_BY is NULL then
550 X_LAST_UPDATED_BY := -1;
551 end if;
552 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
553 if X_LAST_UPDATE_LOGIN is NULL then
554 X_LAST_UPDATE_LOGIN := -1;
555 end if;
556 else
557 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
558 IGS_GE_MSG_STACK.ADD;
559 app_exception.raise_exception;
560 end if;
561
562 Before_DML(
563 p_action=>'UPDATE' ,
564 x_rowid=>X_ROWID ,
565 x_cal_type => X_CAL_TYPE,
566 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER ,
567 x_teach_cal_type => X_TEACH_CAL_TYPE,
568 x_percentage => X_PERCENTAGE ,
569 x_second_percentage => X_SECOND_PERCENTAGE ,
570 x_creation_date => X_LAST_UPDATE_DATE ,
571 x_created_by=>X_LAST_UPDATED_BY ,
572 x_last_update_date=>X_LAST_UPDATE_DATE ,
573 x_last_updated_by=>X_LAST_UPDATED_BY ,
574 x_last_update_login=> X_LAST_UPDATE_LOGIN
575 );
576
577
578 update IGS_ST_DFT_LOAD_APPO set
579 PERCENTAGE = NEW_REFERENCES.PERCENTAGE,
580 SECOND_PERCENTAGE = NEW_REFERENCES.SECOND_PERCENTAGE,
581 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
582 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
583 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
584 where ROWID = X_ROWID
585 ;
586 if (sql%notfound) then
587 raise no_data_found;
588 end if;
589
590 After_DML(
591 p_action=>'UPDATE',
592 x_rowid=> X_ROWID
593 );
594
595
596 end UPDATE_ROW;
597
598 procedure ADD_ROW (
599 X_ROWID in out NOCOPY VARCHAR2,
600 X_CAL_TYPE in VARCHAR2,
601 X_CI_SEQUENCE_NUMBER in NUMBER,
602 X_TEACH_CAL_TYPE in VARCHAR2,
603 X_PERCENTAGE in NUMBER,
604 X_SECOND_PERCENTAGE in NUMBER,
605 X_MODE in VARCHAR2 default 'R'
606 ) as
607 cursor c1 is select rowid from IGS_ST_DFT_LOAD_APPO
608 where CAL_TYPE = X_CAL_TYPE
609 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
610 and TEACH_CAL_TYPE = X_TEACH_CAL_TYPE
611 ;
612
613 begin
614 open c1;
615 fetch c1 into X_ROWID;
616 if (c1%notfound) then
617 close c1;
618 INSERT_ROW (
619 X_ROWID,
620 X_CAL_TYPE,
621 X_CI_SEQUENCE_NUMBER,
622 X_TEACH_CAL_TYPE,
623 X_PERCENTAGE,
624 X_SECOND_PERCENTAGE,
625 X_MODE);
626 return;
627 end if;
628 close c1;
629 UPDATE_ROW (
630 X_ROWID,
631 X_CAL_TYPE,
632 X_CI_SEQUENCE_NUMBER,
633 X_TEACH_CAL_TYPE,
634 X_PERCENTAGE,
635 X_SECOND_PERCENTAGE,
636 X_MODE);
637 end ADD_ROW;
638
639 procedure DELETE_ROW (
640 X_ROWID in VARCHAR2
641 ) as
642 begin
643
644 Before_DML(
645 p_action=>'DELETE',
646 x_rowid=> X_ROWID
647 );
648
649 delete from IGS_ST_DFT_LOAD_APPO
650 where ROWID = X_ROWID;
651 if (sql%notfound) then
652 raise no_data_found;
653 end if;
654
655
656 After_DML(
657 p_action=>'DELETE',
658 x_rowid=> X_ROWID
659 );
660
661
662 end DELETE_ROW;
663
664 end IGS_ST_DFT_LOAD_APPO_PKG;