[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_TYPE_PKG
Source
1 package body IGS_CA_TYPE_PKG AS
2 /* $Header: IGSCI17B.pls 115.9 2003/09/02 08:44:43 svanukur ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_CA_TYPE%RowType;
5 new_references IGS_CA_TYPE%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_cal_type IN VARCHAR2 ,
11 x_description IN VARCHAR2 ,
12 x_s_cal_cat IN VARCHAR2 ,
13 x_abbreviation IN VARCHAR2 ,
14 x_arts_teaching_cal_type_cd IN VARCHAR2 ,
15 x_closed_ind IN VARCHAR2 ,
16 x_notes IN VARCHAR2 ,
17 x_creation_date IN DATE ,
18 x_created_by IN NUMBER ,
19 x_last_update_date IN DATE ,
20 x_last_updated_by IN NUMBER ,
21 x_last_update_login IN NUMBER
22 ) AS
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_CA_TYPE
27 WHERE rowid = x_rowid;
28
29 BEGIN
30
31 l_rowid := x_rowid;
32
33 -- Code for setting the Old and New Reference Values.
34 -- Populate Old Values.
35 Open cur_old_ref_values;
36 Fetch cur_old_ref_values INTO old_references;
37 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
38 Close cur_old_ref_values;
39 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
40 IGS_GE_MSG_STACK.ADD;
41 App_Exception.Raise_Exception;
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45
46 -- Populate New Values.
47 new_references.cal_type := x_cal_type;
48 new_references.description := x_description;
49 new_references.s_cal_cat := x_s_cal_cat;
50 new_references.abbreviation := x_abbreviation;
51 new_references.arts_teaching_cal_type_cd := x_arts_teaching_cal_type_cd;
52 new_references.closed_ind := x_closed_ind;
53 new_references.notes := x_notes;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64
65 END Set_Column_Values;
66
67 -- Trigger description :-
68 -- "OSS_TST".trg_cat_br_iu
69 -- BEFORE INSERT OR UPDATE
70 -- ON IGS_CA_TYPE
71 -- FOR EACH ROW
72
73 PROCEDURE BeforeRowInsertUpdate1(
74 p_inserting IN BOOLEAN ,
75 p_updating IN BOOLEAN ,
76 p_deleting IN BOOLEAN
77 ) AS
78 v_message_name varchar2(30);
79 BEGIN
80 IF p_inserting OR p_updating THEN
81 -- Validate that the ARTS teaching calendar type is not closed
82 IF new_references.arts_teaching_cal_type_cd IS NOT NULL THEN
83 IF IGS_CA_VAL_CAT.calp_val_atctc_clsd(
84 new_references.arts_teaching_cal_type_cd,
85 v_message_name) = FALSE THEN
86 Fnd_Message.Set_Name('IGS',v_message_name);
87 IGS_GE_MSG_STACK.ADD;
88 APP_EXCEPTION.RAISE_EXCEPTION;
89 END IF;
90 END IF;
91 -- Validate that the ARTS teaching calendar type is specified only for
92 -- TEACHING system calendar category
93 IF IGS_CA_VAL_CAT.calp_val_cat_arts_cd(
94 new_references.s_cal_cat,
95 new_references.arts_teaching_cal_type_cd,
96 v_message_name) = FALSE THEN
97 Fnd_Message.Set_Name('IGS',v_message_name);
98 IGS_GE_MSG_STACK.ADD;
99 APP_EXCEPTION.RAISE_EXCEPTION;
100 END IF;
101 END IF;
102 IF p_updating THEN
103 -- Validate that the system calendar category is not being changed
104 -- when the calendar type has active or inactive calendar instances
105 IF (new_references.s_cal_cat <> old_references.s_cal_cat) THEN
106 IF IGS_CA_VAL_CAT.calp_val_sys_cal_cat(
107 new_references.cal_type,
108 v_message_name) = FALSE THEN
109 Fnd_Message.Set_Name('IGS',v_message_name);
110 IGS_GE_MSG_STACK.ADD;
111 APP_EXCEPTION.RAISE_EXCEPTION;
112 END IF;
113 END IF;
114 END IF;
115
116
117 END BeforeRowInsertUpdate1;
118
119 PROCEDURE Check_Constraints (
120 Column_Name IN VARCHAR2 ,
121 Column_Value IN VARCHAR2
122 ) AS
123 BEGIN
124
125 IF Column_Name is NULL THEN
126 NULL;
127 ELSIF upper(Column_Name) = 'ABBREVIATION' then
128 new_references.abbreviation := Column_Value;
129 ELSIF upper(Column_Name) = 'CLOSED_IND' then
130 new_references.closed_ind := Column_Value;
131 ELSIF upper(Column_Name) = 'S_CAL_CAT' then
132 new_references.s_cal_cat := Column_Value;
133 ELSIF upper(Column_Name) = 'CAL_TYPE' then
134 new_references.cal_type := Column_Value;
135 ELSIF upper(Column_Name) = 'ARTS_TEACHING_CAL_TYPE_CD' then
136 new_references.arts_teaching_cal_type_cd := Column_Value;
137 END IF;
138
139 IF upper(Column_Name) = 'ABBREVIATION' OR
140 column_name is NULL THEN
141 IF new_references.abbreviation <> UPPER(new_references.abbreviation) 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 IF upper(Column_Name) = 'S_CAL_CAT' OR
148 column_name is NULL THEN
149 IF new_references.s_cal_cat <> UPPER(new_references.s_cal_cat) THEN
150 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END IF;
154 END IF;
155 IF upper(Column_Name) = 'ARTS_TEACHING_CAL_TYPE_CD' OR
156 column_name is NULL THEN
157 IF new_references.arts_teaching_cal_type_cd <> UPPER(new_references.arts_teaching_cal_type_cd) 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 END IF;
163
164 IF upper(Column_Name) = 'CAL_TYPE' OR
165 column_name is NULL THEN
166 IF new_references.cal_type <> UPPER(new_references.cal_type) THEN
167 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 END IF;
171 END IF;
172 IF upper(Column_Name) = 'CLOSED_IND' OR
173 column_name is NULL THEN
174 IF new_references.closed_ind NOT IN ('Y', 'N') THEN
175 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179 END IF;
180 END Check_Constraints;
181
182 PROCEDURE Check_Parent_Existance AS
183 BEGIN
184
185 IF (((old_references.arts_teaching_cal_type_cd = new_references.arts_teaching_cal_type_cd)) OR
186 ((new_references.arts_teaching_cal_type_cd IS NULL))) THEN
187 NULL;
188 ELSIF NOT IGS_CA_ARTS_TC_CA_CD_PKG.Get_PK_For_Validation (
189 new_references.arts_teaching_cal_type_cd
190 ) THEN
191 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195
196 IF (((old_references.s_cal_cat = new_references.s_cal_cat)) OR
197 ((new_references.s_cal_cat IS NULL))) THEN
198 NULL;
199 ELSE
200 null;
201 END IF;
202
203 END Check_Parent_Existance;
204
205 PROCEDURE Check_Child_Existance AS
206 BEGIN
207
208 IGS_EN_ATD_TYPE_LOAD_PKG.GET_FK_IGS_CA_TYPE (
209 old_references.cal_type
210 );
211
212 IGS_CA_INST_PKG.GET_FK_IGS_CA_TYPE (
213 old_references.cal_type
214 );
215
216 IGS_PS_FEE_TRG_PKG.GET_FK_IGS_CA_TYPE (
217 old_references.cal_type
218 );
219
220 IGS_PS_OFR_PKG.GET_FK_IGS_CA_TYPE (
221 old_references.cal_type
222 );
223
224 IGS_ST_DFT_LOAD_APPO_PKG.GET_FK_IGS_CA_TYPE (
225 old_references.cal_type
226 );
227
228 IGS_PS_PAT_OF_STUDY_PKG.GET_FK_IGS_CA_TYPE (
229 old_references.cal_type
230 );
231
232 IGS_PS_PAT_STUDY_PRD_PKG.GET_FK_IGS_CA_TYPE (
233 old_references.cal_type
234 );
235
236 IGS_PR_RU_CA_TYPE_PKG.GET_FK_IGS_CA_TYPE (
237 old_references.cal_type
238 );
239
240 IGS_AS_UNITASS_ITEM_PKG.GET_FK_IGS_CA_TYPE (
241 old_references.cal_type
242 );
243
244 IGS_FI_UNIT_FEE_TRG_PKG.GET_FK_IGS_CA_TYPE (
245 old_references.cal_type
246 );
247
248 IGS_PS_UNIT_OFR_PKG.GET_FK_IGS_CA_TYPE (
249 old_references.cal_type
250 );
251
252 IGS_AD_CAL_CONF_PKG.GET_FK_IGS_CA_TYPE (
253 old_references.cal_type
254 );
255
256 igs_as_anon_method_pkg.GET_FK_IGS_CA_TYPE (
257 old_references.cal_type
258 );
259
260 igs_en_config_enr_cp_pkg.GET_FK_IGS_CA_TYPE (
261 old_references.cal_type
262 );
263 igs_en_or_unit_wlst_pkg.GET_FK_IGS_CA_TYPE (
264 old_references.cal_type
265 );
266 igs_en_inst_wlst_opt_pkg.GET_FK_IGS_CA_TYPE (
267 old_references.cal_type
268 );
269 END Check_Child_Existance;
270
271 FUNCTION Get_PK_For_Validation (
272 x_cal_type IN VARCHAR2
273 ) RETURN BOOLEAN AS
274
275 CURSOR cur_rowid IS
276 SELECT rowid
277 FROM IGS_CA_TYPE
278 WHERE cal_type = x_cal_type
279 FOR UPDATE NOWAIT;
280
281 lv_rowid cur_rowid%RowType;
282
283 BEGIN
284
285 Open cur_rowid;
286 Fetch cur_rowid INTO lv_rowid;
287 IF (cur_rowid%FOUND) THEN
288 Close cur_rowid;
289 Return (TRUE);
290 ELSE
291 Close cur_rowid;
292 Return (FALSE);
293 END IF;
294
295 END Get_PK_For_Validation;
296
297 PROCEDURE GET_FK_IGS_CA_ARTS_TC_CA_CD (
298 x_arts_teaching_cal_type_cd IN VARCHAR2
299 ) AS
300
301 CURSOR cur_rowid IS
302 SELECT rowid
303 FROM IGS_CA_TYPE
304 WHERE arts_teaching_cal_type_cd = x_arts_teaching_cal_type_cd ;
305
306 lv_rowid cur_rowid%RowType;
307
308 BEGIN
309
310 Open cur_rowid;
311 Fetch cur_rowid INTO lv_rowid;
312 IF (cur_rowid%FOUND) THEN
313 Close cur_rowid;
314 Fnd_Message.Set_Name ('IGS', 'IGS_CA_CAT_ATCTC_FK');
315 IGS_GE_MSG_STACK.ADD;
316 App_Exception.Raise_Exception;
317 Return;
318 END IF;
319 Close cur_rowid;
320
321 END GET_FK_IGS_CA_ARTS_TC_CA_CD;
322
323 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
324 x_s_cal_cat IN VARCHAR2
325 ) AS
326
327 CURSOR cur_rowid IS
328 SELECT rowid
329 FROM IGS_CA_TYPE
330 WHERE s_cal_cat = x_s_cal_cat ;
331
332 lv_rowid cur_rowid%RowType;
333
334 BEGIN
335
336 Open cur_rowid;
337 Fetch cur_rowid INTO lv_rowid;
338 IF (cur_rowid%FOUND) THEN
339 Close cur_rowid;
340 Fnd_Message.Set_Name ('IGS', 'IGS_CA_CAT_LKUP_FK');
341 IGS_GE_MSG_STACK.ADD;
342 App_Exception.Raise_Exception;
343 Return;
344 END IF;
345 Close cur_rowid;
346
347 END GET_FK_IGS_LOOKUPS_VIEW;
348
349 PROCEDURE Before_DML (
350 p_action IN VARCHAR2,
351 x_rowid IN VARCHAR2 ,
352 x_cal_type IN VARCHAR2 ,
353 x_description IN VARCHAR2 ,
354 x_s_cal_cat IN VARCHAR2 ,
355 x_abbreviation IN VARCHAR2 ,
356 x_arts_teaching_cal_type_cd IN VARCHAR2 ,
357 x_closed_ind IN VARCHAR2 ,
358 x_notes IN VARCHAR2 ,
359 x_creation_date IN DATE ,
360 x_created_by IN NUMBER ,
361 x_last_update_date IN DATE ,
362 x_last_updated_by IN NUMBER ,
363 x_last_update_login IN NUMBER
364 ) AS
365 BEGIN
366
367 Set_Column_Values (
368 p_action,
369 x_rowid,
370 x_cal_type,
371 x_description,
372 x_s_cal_cat,
373 x_abbreviation,
374 x_arts_teaching_cal_type_cd,
375 x_closed_ind,
376 x_notes,
377 x_creation_date,
378 x_created_by,
379 x_last_update_date,
380 x_last_updated_by,
381 x_last_update_login
382 );
383
384 IF (p_action = 'INSERT') THEN
385 -- Call all the procedures related to Before Insert.
386 BeforeRowInsertUpdate1 ( p_inserting => TRUE,
387 p_updating => FALSE,
388 p_deleting => FALSE);
389 IF Get_PK_For_Validation ( new_references.cal_type )
390 THEN
391 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
392 IGS_GE_MSG_STACK.ADD;
393 App_Exception.Raise_Exception;
394 END IF;
395 Check_Constraints;
396 Check_Parent_Existance;
397 ELSIF (p_action = 'UPDATE') THEN
398 -- Call all the procedures related to Before Update.
399 BeforeRowInsertUpdate1 ( p_inserting => FALSE,
400 p_updating => TRUE,
401 p_deleting => FALSE);
402 Check_Constraints;
403 Check_Parent_Existance;
404 ELSIF (p_action = 'DELETE') THEN
405 -- Call all the procedures related to Before Delete.
406 Null;
407 Check_Child_Existance;
408 ELSIF (p_action = 'VALIDATE_INSERT') THEN
409 -- Call all the procedures related to Before Insert.
410 IF Get_PK_For_Validation ( new_references.cal_type )
411 THEN
412 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
413 IGS_GE_MSG_STACK.ADD;
414 App_Exception.Raise_Exception;
415 END IF;
416 Check_Constraints;
417 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
418 Check_Constraints;
419 ELSIF (p_action = 'VALIDATE_DELETE') THEN
420 Check_Child_Existance;
421 END IF;
422
423 END Before_DML;
424
425 PROCEDURE After_DML (
426 p_action IN VARCHAR2,
427 x_rowid IN VARCHAR2
428 ) AS
429 BEGIN
430
431 l_rowid := x_rowid;
432
433 IF (p_action = 'INSERT') THEN
434 -- Call all the procedures related to After Insert.
435 Null;
436 ELSIF (p_action = 'UPDATE') THEN
437 -- Call all the procedures related to After Update.
438 Null;
439 ELSIF (p_action = 'DELETE') THEN
440 -- Call all the procedures related to After Delete.
441 Null;
442 END IF;
443
444 END After_DML;
445 procedure INSERT_ROW (
446 X_ROWID in out NOCOPY VARCHAR2,
447 X_CAL_TYPE in VARCHAR2,
448 X_DESCRIPTION in VARCHAR2,
449 X_S_CAL_CAT in VARCHAR2,
450 X_ABBREVIATION in VARCHAR2,
451 X_ARTS_TEACHING_CAL_TYPE_CD in VARCHAR2,
452 X_CLOSED_IND in VARCHAR2,
453 X_NOTES in VARCHAR2,
454 X_MODE in VARCHAR2
455 ) AS
456 cursor C is select ROWID from IGS_CA_TYPE
457 where CAL_TYPE = X_CAL_TYPE;
458 X_LAST_UPDATE_DATE DATE;
459 X_LAST_UPDATED_BY NUMBER;
460 X_LAST_UPDATE_LOGIN NUMBER;
461 begin
462 X_LAST_UPDATE_DATE := SYSDATE;
463 if(X_MODE = 'I') then
464 X_LAST_UPDATED_BY := 1;
465 X_LAST_UPDATE_LOGIN := 0;
466 elsif (X_MODE = 'R') then
467 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
468 if X_LAST_UPDATED_BY is NULL then
469 X_LAST_UPDATED_BY := -1;
470 end if;
471 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
472 if X_LAST_UPDATE_LOGIN is NULL then
473 X_LAST_UPDATE_LOGIN := -1;
474 end if;
475 else
476 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
477 IGS_GE_MSG_STACK.ADD;
478 app_exception.raise_exception;
479 end if;
480 Before_DML (
481 p_action =>'INSERT',
482 x_rowid =>X_ROWID,
483 x_cal_type =>X_CAL_TYPE,
484 x_description =>X_DESCRIPTION,
485 x_s_cal_cat =>X_S_CAL_CAT,
486 x_abbreviation =>X_ABBREVIATION,
487 x_arts_teaching_cal_type_cd =>X_ARTS_TEACHING_CAL_TYPE_CD,
488 x_closed_ind =>NVL(X_CLOSED_IND,'N'),
489 x_notes =>X_NOTES,
490 x_creation_date =>X_LAST_UPDATE_DATE,
491 x_created_by =>X_LAST_UPDATED_BY,
492 x_last_update_date =>X_LAST_UPDATE_DATE,
493 x_last_updated_by =>X_LAST_UPDATED_BY,
494 x_last_update_login =>X_LAST_UPDATE_LOGIN
495 );
496 insert into IGS_CA_TYPE (
497 CAL_TYPE,
498 DESCRIPTION,
499 S_CAL_CAT,
500 ABBREVIATION,
501 ARTS_TEACHING_CAL_TYPE_CD,
502 CLOSED_IND,
503 NOTES,
504 CREATION_DATE,
505 CREATED_BY,
506 LAST_UPDATE_DATE,
507 LAST_UPDATED_BY,
508 LAST_UPDATE_LOGIN
509 ) values (
510 NEW_REFERENCES.CAL_TYPE,
511 NEW_REFERENCES.DESCRIPTION,
512 NEW_REFERENCES.S_CAL_CAT,
513 NEW_REFERENCES.ABBREVIATION,
514 NEW_REFERENCES.ARTS_TEACHING_CAL_TYPE_CD,
515 NEW_REFERENCES.CLOSED_IND,
516 NEW_REFERENCES.NOTES,
517 X_LAST_UPDATE_DATE,
518 X_LAST_UPDATED_BY,
519 X_LAST_UPDATE_DATE,
520 X_LAST_UPDATED_BY,
521 X_LAST_UPDATE_LOGIN
522 );
523
524 open c;
525 fetch c into X_ROWID;
526 if (c%notfound) then
527 close c;
528 raise no_data_found;
529 end if;
530 close c;
531 After_DML (
532 p_action =>'INSERT',
533 x_rowid =>X_ROWID
534 );
535 end INSERT_ROW;
536
537 procedure LOCK_ROW (
538 X_ROWID in VARCHAR2,
539 X_CAL_TYPE in VARCHAR2,
540 X_DESCRIPTION in VARCHAR2,
541 X_S_CAL_CAT in VARCHAR2,
542 X_ABBREVIATION in VARCHAR2,
543 X_ARTS_TEACHING_CAL_TYPE_CD in VARCHAR2,
544 X_CLOSED_IND in VARCHAR2,
545 X_NOTES in VARCHAR2
546 ) AS
547 cursor c1 is select
548 DESCRIPTION,
549 S_CAL_CAT,
550 ABBREVIATION,
551 ARTS_TEACHING_CAL_TYPE_CD,
552 CLOSED_IND,
553 NOTES
554 from IGS_CA_TYPE
555 where ROWID=X_ROWID
556 for update nowait;
557 tlinfo c1%rowtype;
558
559 begin
560 open c1;
561 fetch c1 into tlinfo;
562 if (c1%notfound) then
563 close c1;
564 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
565 IGS_GE_MSG_STACK.ADD;
566 app_exception.raise_exception;
567 return;
568 end if;
569 close c1;
570
571 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
572 AND (tlinfo.S_CAL_CAT = X_S_CAL_CAT)
573 AND ((tlinfo.ABBREVIATION = X_ABBREVIATION)
574 OR ((tlinfo.ABBREVIATION is null)
575 AND (X_ABBREVIATION is null)))
576 AND ((tlinfo.ARTS_TEACHING_CAL_TYPE_CD = X_ARTS_TEACHING_CAL_TYPE_CD)
577 OR ((tlinfo.ARTS_TEACHING_CAL_TYPE_CD is null)
578 AND (X_ARTS_TEACHING_CAL_TYPE_CD is null)))
579 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
580 AND ((tlinfo.NOTES = X_NOTES)
581 OR ((tlinfo.NOTES is null)
582 AND (X_NOTES is null)))
583 ) then
584 null;
585 else
586 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
587 IGS_GE_MSG_STACK.ADD;
588 app_exception.raise_exception;
589 end if;
590 return;
591 end LOCK_ROW;
592
593 procedure UPDATE_ROW (
594 X_ROWID in VARCHAR2,
595 X_CAL_TYPE in VARCHAR2,
596 X_DESCRIPTION in VARCHAR2,
597 X_S_CAL_CAT in VARCHAR2,
598 X_ABBREVIATION in VARCHAR2,
599 X_ARTS_TEACHING_CAL_TYPE_CD in VARCHAR2,
600 X_CLOSED_IND in VARCHAR2,
601 X_NOTES in VARCHAR2,
602 X_MODE in VARCHAR2
603 ) AS
604 X_LAST_UPDATE_DATE DATE;
605 X_LAST_UPDATED_BY NUMBER;
606 X_LAST_UPDATE_LOGIN NUMBER;
607 begin
608 X_LAST_UPDATE_DATE := SYSDATE;
609 if(X_MODE = 'I') then
610 X_LAST_UPDATED_BY := 1;
611 X_LAST_UPDATE_LOGIN := 0;
612 elsif (X_MODE = 'R') then
613 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
614 if X_LAST_UPDATED_BY is NULL then
615 X_LAST_UPDATED_BY := -1;
616 end if;
617 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
618 if X_LAST_UPDATE_LOGIN is NULL then
619 X_LAST_UPDATE_LOGIN := -1;
620 end if;
621 else
622 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
623 IGS_GE_MSG_STACK.ADD;
624 app_exception.raise_exception;
625 end if;
626 Before_DML (
627 p_action =>'UPDATE',
628 x_rowid =>X_ROWID,
629 x_cal_type =>X_CAL_TYPE,
630 x_description =>X_DESCRIPTION,
631 x_s_cal_cat =>X_S_CAL_CAT,
632 x_abbreviation =>X_ABBREVIATION,
633 x_arts_teaching_cal_type_cd =>X_ARTS_TEACHING_CAL_TYPE_CD,
634 x_closed_ind =>X_CLOSED_IND,
635 x_notes =>X_NOTES,
636 x_creation_date =>X_LAST_UPDATE_DATE,
637 x_created_by =>X_LAST_UPDATED_BY,
638 x_last_update_date =>X_LAST_UPDATE_DATE,
639 x_last_updated_by =>X_LAST_UPDATED_BY,
640 x_last_update_login =>X_LAST_UPDATE_LOGIN
641 );
642 update IGS_CA_TYPE set
643 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
644 S_CAL_CAT = NEW_REFERENCES.S_CAL_CAT,
645 ABBREVIATION = NEW_REFERENCES.ABBREVIATION,
646 ARTS_TEACHING_CAL_TYPE_CD = NEW_REFERENCES.ARTS_TEACHING_CAL_TYPE_CD,
647 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
648 NOTES = NEW_REFERENCES.NOTES,
649 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
650 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
651 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
652 where ROWID=X_ROWID
653 ;
654 if (sql%notfound) then
655 raise no_data_found;
656 end if;
657 After_DML (
658 p_action =>'UPDATE',
659 x_rowid =>X_ROWID
660 );
661 end UPDATE_ROW;
662
663 procedure ADD_ROW (
664 X_ROWID in out NOCOPY VARCHAR2,
665 X_CAL_TYPE in VARCHAR2,
666 X_DESCRIPTION in VARCHAR2,
667 X_S_CAL_CAT in VARCHAR2,
668 X_ABBREVIATION in VARCHAR2,
669 X_ARTS_TEACHING_CAL_TYPE_CD in VARCHAR2,
670 X_CLOSED_IND in VARCHAR2,
671 X_NOTES in VARCHAR2,
672 X_MODE in VARCHAR2
673 ) AS
674 cursor c1 is select rowid from IGS_CA_TYPE
675 where CAL_TYPE = X_CAL_TYPE
676 ;
677 begin
678 open c1;
679 fetch c1 into X_ROWID;
680 if (c1%notfound) then
681 close c1;
682 INSERT_ROW (
683 X_ROWID,
684 X_CAL_TYPE,
685 X_DESCRIPTION,
686 X_S_CAL_CAT,
687 X_ABBREVIATION,
688 X_ARTS_TEACHING_CAL_TYPE_CD,
689 X_CLOSED_IND,
690 X_NOTES,
691 X_MODE);
692 return;
693 end if;
694 close c1;
695 UPDATE_ROW (
696 X_ROWID,
697 X_CAL_TYPE,
698 X_DESCRIPTION,
699 X_S_CAL_CAT,
700 X_ABBREVIATION,
701 X_ARTS_TEACHING_CAL_TYPE_CD,
702 X_CLOSED_IND,
703 X_NOTES,
704 X_MODE);
705 end ADD_ROW;
706
707 procedure DELETE_ROW (
708 X_ROWID in VARCHAR2
709 ) AS
710 begin
711 Before_DML (
712 p_action =>'DELETE',
713 x_rowid =>X_ROWID
714 );
715 delete from IGS_CA_TYPE
716 where ROWID=X_ROWID;
717 if (sql%notfound) then
718 raise no_data_found;
719 end if;
720 After_DML (
721 p_action =>'DELETE',
722 x_rowid =>X_ROWID
723 );
724 end DELETE_ROW;
725
726 end IGS_CA_TYPE_PKG;