DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_CA_DA_INST_PKG

Source


1 package body IGS_CA_DA_INST_PKG AS
2 /* $Header: IGSCI04B.pls 120.0 2005/06/02 03:39:05 appldev noship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_CA_DA_INST%RowType;
5   new_references IGS_CA_DA_INST%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2,
10     x_dt_alias IN VARCHAR2,
11     x_sequence_number IN NUMBER,
12     x_cal_type IN VARCHAR2,
13     x_ci_sequence_number IN NUMBER,
14     x_absolute_val IN DATE,
15     x_creation_date IN DATE,
16     x_created_by IN NUMBER,
17     x_last_update_date IN DATE,
18     x_last_updated_by IN NUMBER,
19     x_last_update_login IN NUMBER
20   ) AS
21   /*----------------------------------------------------------------------------
22   ||  Created By :
23   ||  Created On :
24   ||  Purpose :
25   ||  Known limitations, enhancements or remarks :
26   ||  Change History :
27   ||  Who             When            What
28   ||  (reverse chronological order - newest change first)
29   ||  vvutukur        02-Sep-2002  Bug#2531390.Removed default values for parameters to avoid gscc
30   ||                               warnings.
31   ----------------------------------------------------------------------------*/
32 
33     CURSOR cur_old_ref_values IS
34       SELECT   *
35       FROM     IGS_CA_DA_INST
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.dt_alias := x_dt_alias;
57     new_references.sequence_number := x_sequence_number;
58     new_references.cal_type := x_cal_type;
59     new_references.ci_sequence_number := x_ci_sequence_number;
60     new_references.absolute_val := x_absolute_val;
61     IF (p_action = 'UPDATE') THEN
62       new_references.creation_date := old_references.creation_date;
63       new_references.created_by := old_references.created_by;
64     ELSE
65       new_references.creation_date := x_creation_date;
66       new_references.created_by := x_created_by;
67     END IF;
68     new_references.last_update_date := x_last_update_date;
69     new_references.last_updated_by := x_last_updated_by;
70     new_references.last_update_login := x_last_update_login;
71 
72   END Set_Column_Values;
73 
74   -- Trigger description :-
75   -- "OSS_TST".trg_dai_br_iud
76   -- BEFORE INSERT OR DELETE OR UPDATE
77   -- ON IGS_CA_DA_INST
78   -- FOR EACH ROW
79 
80   PROCEDURE BeforeRowInsertUpdateDelete1(
81     p_inserting IN BOOLEAN,
82     p_updating IN BOOLEAN,
83     p_deleting IN BOOLEAN
84     ) AS
85   /*----------------------------------------------------------------------------
86   ||  Created By :
87   ||  Created On :
88   ||  Purpose :
89   ||  Known limitations, enhancements or remarks :
90   ||  Change History :
91   ||  Who             When            What
92   ||  (reverse chronological order - newest change first)
93   ||  vvutukur        02-Sep-2002  Bug#2531390.Removed default values of parameters to avoid gscc
94   ||                               warnings.
95   ----------------------------------------------------------------------------*/
96 
97 	v_message_name	varchar2(30);
98   BEGIN
99 
100 	IF p_inserting OR p_updating THEN
101 	-- Absolute value must be entered if category is 'HOLIDAY'
102 		IF new_references.absolute_val IS NULL THEN
103 			IF IGS_CA_VAL_DAI.calp_val_holiday_cat (new_references.cal_type,
104 				v_message_name) = TRUE
105 			THEN
106 				Fnd_Message.Set_Name('IGS',v_message_name);
107 				IGS_GE_MSG_STACK.ADD;
108 				APP_EXCEPTION.RAISE_EXCEPTION;
109 			END IF;
110 		END IF;
111 	END IF;
112   END BeforeRowInsertUpdateDelete1;
113 
114   -- Trigger description :-
115   -- "OSS_TST".trg_dai_as_iud
116   -- AFTER INSERT OR DELETE OR UPDATE
117   -- ON IGS_CA_DA_INST
118 
119   PROCEDURE AfterStmtInsertUpdateDelete3(
120     p_inserting IN BOOLEAN,
121     p_updating IN BOOLEAN,
122     p_deleting IN BOOLEAN
123     ) AS
124   /*----------------------------------------------------------------------------
125   ||  Created By :
126   ||  Created On :
127   ||  Purpose :
128   ||  Known limitations, enhancements or remarks :
129   ||  Change History :
130   ||  Who             When            What
131   ||  (reverse chronological order - newest change first)
132   ||  vvutukur        02-Sep-2002  Bug#2531390.Removed default values of the parameters to avoid gscc
133   ||                               warnings.
134   ----------------------------------------------------------------------------*/
135 
136 	v_message_name	varchar2(30);
137   BEGIN
138   	-- Validation routine calls.
139   	IF p_inserting OR p_updating
140   	THEN
141   		-- Validate date alias instance
142   		IF IGS_CA_VAL_DAI.calp_val_dai_upd(NVL (new_references.dt_alias, old_references.dt_alias),
143   			NVL (new_references.sequence_number, old_references.sequence_number),
144   			NVL (new_references.cal_type, old_references.cal_type),
145   			NVL (new_references.ci_sequence_number, old_references.ci_sequence_number),
146   			v_message_name) = FALSE
147   		THEN
148 				Fnd_Message.Set_Name('IGS',v_message_name);
149 				IGS_GE_MSG_STACK.ADD;
150 				APP_EXCEPTION.RAISE_EXCEPTION;
151   		END IF;
152   		IF p_inserting
153   		THEN
154   			-- Validate date alias instance date alias
155   			IF IGS_CA_VAL_DAI.calp_val_dai_da(NVL (new_references.dt_alias, old_references.dt_alias),
156   				NVL (new_references.cal_type, old_references.cal_type),
157   				v_message_name) = FALSE
158   			THEN
159 				Fnd_Message.Set_Name('IGS',v_message_name);
160 				IGS_GE_MSG_STACK.ADD;
161 				APP_EXCEPTION.RAISE_EXCEPTION;
162   			END IF;
163   		END IF;
164   	END IF;
165   END AfterStmtInsertUpdateDelete3;
166 
167   PROCEDURE Check_Constraints (
168 	Column_Name 	IN	VARCHAR2,
169 	Column_Value 	IN	VARCHAR2
170 	)
171 	IS
172 	BEGIN
173 	IF  column_name is null then
174                         NULL;
175                   Elsif UPPER(column_name) = 'CAL_TYPE ' Then
176 				new_references.cal_type := column_value;
177 
178                   Elsif UPPER(column_name) = 'DT_ALIAS' Then
179 				new_references.dt_alias := column_value;
180       end if;
181                  if upper(column_name) = 'CAL_TYPE' or column_name is null Then
182 				if new_references.cal_type <> UPPER( new_references.cal_type) then
183                   		Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
184                   		IGS_GE_MSG_STACK.ADD;
185                   		App_Exception.Raise_Exception;
186 				end if;
187 			end if;
188                   if upper(column_name) = 'DT_ALIAS' or column_name is null Then
189 				if new_references.dt_alias <> UPPER( new_references.dt_alias) then
190                   		Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
191                   		IGS_GE_MSG_STACK.ADD;
192                   		App_Exception.Raise_Exception;
193 				end if;
194 			end if;
195 
196      END Check_Constraints ;
197 
198 
199   PROCEDURE Check_Parent_Existance AS
200   BEGIN
201 
202     IF (((old_references.cal_type = new_references.cal_type) AND
203          (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
204         ((new_references.cal_type IS NULL) OR
205          (new_references.ci_sequence_number IS NULL))) THEN
206       NULL;
207     ELSE
208       IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
209         new_references.cal_type,
210         new_references.ci_sequence_number
211         ) THEN
212      	    Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
213      	    IGS_GE_MSG_STACK.ADD;
214 	    App_Exception.Raise_Exception;
215       END IF;
216     END IF;
217     IF (((old_references.dt_alias = new_references.dt_alias)) OR
218         ((new_references.dt_alias IS NULL))) THEN
219       NULL;
220     ELSE
221       IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
222         new_references.dt_alias
223          ) THEN
224      	    Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
225      	    IGS_GE_MSG_STACK.ADD;
226 	    App_Exception.Raise_Exception;
227       END IF;
228      END IF;
229 
230   END Check_Parent_Existance;
231 
232   PROCEDURE check_Child_Existance
233   /*----------------------------------------------------------------------------
234   ||  Created By :
235   ||  Created On :
236   ||  Purpose :
237   ||  Known limitations, enhancements or remarks :
238   ||  Change History :
239   ||  Who             When            What
240   ||  (reverse chronological order - newest change first)
241   ||  rmaddipa        07-Sep-2004  Enh#3316063 Added call to IGS_FI_TP_RET_SCHD_PKG.GET_FK_IGS_CA_DA_INST
242   ||  vvutukur        26-Aug-2002  Bug#2531390.Removed call to IGS_FI_FEE_PAY_SCHD_PKG.GET_FK_IGS_CA_DA_INST
243   ||                               to remove the foreign key relationship between tables igs_ca_da_inst
244   ||                               and igs_fi_fee_pay_schd.
245   ----------------------------------------------------------------------------*/
246   AS
247   BEGIN
248     IGS_AD_PECRS_OFOP_DT_PKG.GET_FK_IGS_CA_DA_INST (
249       old_references.dt_alias,
250       old_references.sequence_number,
251       old_references.cal_type,
252       old_references.ci_sequence_number
253       );
254 
255     IGS_GR_CRMN_ROUND_PKG.GET_FK_IGS_CA_DA_INST (
256       old_references.dt_alias,
257       old_references.sequence_number,
258       old_references.cal_type,
259       old_references.ci_sequence_number
260       );
261 
262     IGS_CA_DA_INST_OFST_PKG.GET_FK_IGS_CA_DA_INST (
263       old_references.dt_alias,
264       old_references.sequence_number,
265       old_references.cal_type,
266       old_references.ci_sequence_number
267       );
268 
269 
270     IGS_CA_DA_INST_PAIR_PKG.GET_FK_IGS_CA_DA_INST (
271       old_references.dt_alias,
272       old_references.sequence_number,
273       old_references.cal_type,
274       old_references.ci_sequence_number
275       );
276 
277 
278     IGS_AS_EXAM_SESSION_PKG.GET_FK_IGS_CA_DA_INST (
279       old_references.dt_alias,
280       old_references.sequence_number,
281       old_references.cal_type,
282       old_references.ci_sequence_number
283       );
284 
285     IGS_FI_F_CAT_CA_INST_PKG.GET_FK_IGS_CA_DA_INST (
286       old_references.dt_alias,
287       old_references.sequence_number,
288       old_references.cal_type,
289       old_references.ci_sequence_number
290       );
291 
292     IGS_FI_F_CAT_FEE_LBL_PKG.GET_FK_IGS_CA_DA_INST (
293       old_references.dt_alias,
294       old_references.sequence_number,
295       old_references.cal_type,
296       old_references.ci_sequence_number
297       );
298 
299 /* Removed call to    IGS_FI_FEE_ENCMB_PKG.GET_FK_IGS_CA_DA_INST since the TBH is obseleted as part of
300 bug 2126091-sykrishn */
301 
302 --Removed call to IGS_FI_FEE_PAY_SCHD_PKG.GET_FK_IGS_CA_DA_INST as part of SFCR005 CleanUp.Bug#2531390.
303 
304     IGS_FI_FEE_RET_SCHD_PKG.GET_FK_IGS_CA_DA_INST (
305       old_references.dt_alias,
306       old_references.sequence_number,
307       old_references.cal_type,
308       old_references.ci_sequence_number
309       );
310 
311     IGS_FI_F_TYP_CA_INST_PKG.GET_FK_IGS_CA_DA_INST (
312       old_references.dt_alias,
313       old_references.sequence_number,
314       old_references.cal_type,
315       old_references.ci_sequence_number
316       );
317 
318     IGS_GR_CRMN_PKG.GET_FK_IGS_CA_DA_INST (
319       old_references.dt_alias,
320       old_references.sequence_number,
321       old_references.cal_type,
322       old_references.ci_sequence_number
323       );
324     IGS_FI_TP_RET_SCHD_PKG.GET_FK_IGS_CA_DA_INST(
325       x_dt_alias => old_references.dt_alias,
326       x_dai_sequence_number => old_references.sequence_number,
327       x_teach_cal_type => old_references.cal_type,
328       x_teach_ci_sequence_number => old_references.ci_sequence_number
329       );
330   END Check_Child_Existance;
331 
332   FUNCTION Get_PK_For_Validation (
333     x_dt_alias IN VARCHAR2,
334     x_sequence_number IN NUMBER,
335     x_cal_type IN VARCHAR2,
336     x_ci_sequence_number IN NUMBER
337     )RETURN BOOLEAN AS
338 
339     CURSOR cur_rowid IS
340       SELECT   rowid
341       FROM     IGS_CA_DA_INST
342       WHERE    dt_alias = x_dt_alias
343       AND      sequence_number = x_sequence_number
344       AND      cal_type = x_cal_type
345       AND      ci_sequence_number = x_ci_sequence_number
346       FOR UPDATE NOWAIT;
347 
348     lv_rowid cur_rowid%RowType;
349 
350   BEGIN
351 
352     Open cur_rowid;
353     Fetch cur_rowid INTO lv_rowid;
354     IF (cur_rowid%FOUND) THEN
355 	      Close cur_rowid;
356 	      Return (TRUE);
357 	ELSE
358 	      Close cur_rowid;
359 	      Return (FALSE);
360 	END IF;
361 
362   END Get_PK_For_Validation;
363 
364   PROCEDURE GET_FK_IGS_CA_INST (
365     x_cal_type IN VARCHAR2,
366     x_sequence_number IN NUMBER
367     ) AS
368 
369     CURSOR cur_rowid IS
370       SELECT   rowid
371       FROM     IGS_CA_DA_INST
372       WHERE    cal_type = x_cal_type
373       AND      ci_sequence_number = x_sequence_number ;
374 
375     lv_rowid cur_rowid%RowType;
376 
377   BEGIN
378 
379     Open cur_rowid;
380     Fetch cur_rowid INTO lv_rowid;
381     IF (cur_rowid%FOUND) THEN
382       Close cur_rowid;
383       Fnd_Message.Set_Name ('IGS', 'IGS_CA_DAI_CI_FK');
384       IGS_GE_MSG_STACK.ADD;
385       App_Exception.Raise_Exception;
386       Return;
387     END IF;
388     Close cur_rowid;
389 
390   END GET_FK_IGS_CA_INST;
391 
392   PROCEDURE GET_FK_IGS_CA_DA (
393     x_dt_alias IN VARCHAR2
394     ) AS
395 
396     CURSOR cur_rowid IS
397       SELECT   rowid
398       FROM     IGS_CA_DA_INST
399       WHERE    dt_alias = x_dt_alias ;
400 
401     lv_rowid cur_rowid%RowType;
402 
403   BEGIN
404 
405     Open cur_rowid;
406     Fetch cur_rowid INTO lv_rowid;
407     IF (cur_rowid%FOUND) THEN
408       Close cur_rowid;
409       Fnd_Message.Set_Name ('IGS', 'IGS_CA_DAI_DA_FK');
410       IGS_GE_MSG_STACK.ADD;
411       App_Exception.Raise_Exception;
412       Return;
413     END IF;
414     Close cur_rowid;
415 
416   END GET_FK_IGS_CA_DA;
417 
418   PROCEDURE Before_DML (
419     p_action IN VARCHAR2,
420     x_rowid IN VARCHAR2,
421     x_dt_alias IN VARCHAR2,
422     x_sequence_number IN NUMBER,
423     x_cal_type IN VARCHAR2,
424     x_ci_sequence_number IN NUMBER,
425     x_absolute_val IN DATE,
426     x_creation_date IN DATE,
427     x_created_by IN NUMBER,
428     x_last_update_date IN DATE,
429     x_last_updated_by IN NUMBER,
430     x_last_update_login IN NUMBER
431   ) AS
432   /*----------------------------------------------------------------------------
433   ||  Created By :
434   ||  Created On :
435   ||  Purpose :
436   ||  Known limitations, enhancements or remarks :
437   ||  Change History :
438   ||  Who             When            What
439   ||  (reverse chronological order - newest change first)
440   ||  vvutukur        02-Sep-2002  Bug#2531390.The calls to BeforeRowInsertUpdateDelete1 procedure are
441   ||                               modified as defaulting the parametes in that procedure using DEFAULT
442   ||                               clause is removed.Hence passed FALSE to the parameters which were not
443   ||                               passed earlier.
444   ----------------------------------------------------------------------------*/
445   BEGIN
446 
447     Set_Column_Values (
448       p_action,
449       x_rowid,
450       x_dt_alias,
451       x_sequence_number,
452       x_cal_type,
453       x_ci_sequence_number,
454       x_absolute_val,
455       x_creation_date,
456       x_created_by,
457       x_last_update_date,
458       x_last_updated_by,
459       x_last_update_login
460     );
461 
462     IF (p_action = 'INSERT') THEN
463       -- Call all the procedures related to Before Insert.
464       BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
465                                      p_updating  => FALSE,
466 				     p_deleting  => FALSE);
467      IF   Get_PK_For_Validation (
468             new_references.dt_alias ,
469     		new_references.sequence_number ,
470     		new_references.cal_type ,
471     		new_references.ci_sequence_number  ) THEN
472             Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
473             IGS_GE_MSG_STACK.ADD;
474       	App_Exception.Raise_Exception;
475        END IF;
476       CHECK_CONSTRAINTS;
477       Check_Parent_Existance;
478     ELSIF (p_action = 'VALIDATE_INSERT') THEN
479       IF Get_PK_For_Validation (
480             new_references.dt_alias ,
481     		new_references.sequence_number ,
482     		new_references.cal_type ,
483     		new_references.ci_sequence_number  ) THEN
484             Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
485             IGS_GE_MSG_STACK.ADD;
486       	App_Exception.Raise_Exception;
487        END IF;
488         CHECK_CONSTRAINTS;
489     ELSIF (p_action = 'UPDATE') THEN
490       -- Call all the procedures related to Before Update.
491       BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
492                                      p_updating => TRUE,
493 				     p_deleting => FALSE);
494       CHECK_CONSTRAINTS;
495       Check_Parent_Existance;
496     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
497      Check_Constraints;
498      ELSIF (p_action = 'DELETE') THEN
499       -- Call all the procedures related to Before Delete.
500       BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
501                                      p_updating => FALSE,
502                                      p_deleting => TRUE );
503       Check_Child_Existance;
504 
505     ELSIF (p_action = 'VALIDATE_DELETE') THEN
506        Check_Child_Existance;
507     END IF;
508 
509   END Before_DML;
510 
511   PROCEDURE After_DML (
512     p_action IN VARCHAR2,
513     x_rowid IN VARCHAR2
514   ) AS
515   /*----------------------------------------------------------------------------
516   ||  Created By :
517   ||  Created On :
518   ||  Purpose :
519   ||  Known limitations, enhancements or remarks :
520   ||  Change History :
521   ||  Who             When            What
522   ||  (reverse chronological order - newest change first)
523   ||  vvutukur        02-Sep-2002  Bug#2531390.The calls to AfterStmtInsertUpdateDelete3 procedure, are
524   ||                               modified because defaulting the parametes in that procedure using DEFAULT
525   ||                               clause is removed.Hence passed FALSE to the parameters which were not
526   ||                               passed earlier.
527   ----------------------------------------------------------------------------*/
528   BEGIN
529 
530     l_rowid := x_rowid;
531 
532     IF (p_action = 'INSERT') THEN
533       -- Call all the procedures related to After Insert.
534       AfterStmtInsertUpdateDelete3 ( p_inserting => TRUE,
535                                      p_updating  => FALSE,
536 				     p_deleting  => FALSE);
537     ELSIF (p_action = 'UPDATE') THEN
538       -- Call all the procedures related to After Update.
539       AfterStmtInsertUpdateDelete3 ( p_inserting => FALSE,
540                                      p_updating => TRUE,
541 				     p_deleting  => FALSE);
542     ELSIF (p_action = 'DELETE') THEN
543       -- Call all the procedures related to After Delete.
544       AfterStmtInsertUpdateDelete3 ( p_inserting => FALSE,
545                                      p_updating  => FALSE,
546                                      p_deleting => TRUE );
547     END IF;
548 
549   END After_DML;
550 
551 procedure INSERT_ROW (
552   X_ROWID in out NOCOPY VARCHAR2,
553   X_DT_ALIAS in VARCHAR2,
554   X_SEQUENCE_NUMBER in NUMBER,
555   X_CAL_TYPE in VARCHAR2,
556   X_CI_SEQUENCE_NUMBER in NUMBER,
557   X_ABSOLUTE_VAL in DATE,
558   X_MODE in VARCHAR2
559   ) AS
560     cursor C is select ROWID from IGS_CA_DA_INST
561       where DT_ALIAS = X_DT_ALIAS
562       and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
563       and CAL_TYPE = X_CAL_TYPE
564       and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER;
565     X_LAST_UPDATE_DATE DATE;
566     X_LAST_UPDATED_BY NUMBER;
567     X_LAST_UPDATE_LOGIN NUMBER;
568 begin
569   X_LAST_UPDATE_DATE := SYSDATE;
570   if(X_MODE = 'I') then
571     X_LAST_UPDATED_BY := 1;
572     X_LAST_UPDATE_LOGIN := 0;
573   elsif (X_MODE = 'R') then
574     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
575     if X_LAST_UPDATED_BY is NULL then
576       X_LAST_UPDATED_BY := -1;
577     end if;
578     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
579     if X_LAST_UPDATE_LOGIN is NULL then
580       X_LAST_UPDATE_LOGIN := -1;
581     end if;
582   else
583     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
584     IGS_GE_MSG_STACK.ADD;
585     app_exception.raise_exception;
586   end if;
587 Before_DML (
588     p_action =>'INSERT',
589     x_rowid =>X_ROWID,
590     x_dt_alias =>X_DT_ALIAS,
591     x_sequence_number =>X_SEQUENCE_NUMBER,
592     x_cal_type =>X_CAL_TYPE,
593     x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
594     x_absolute_val =>X_ABSOLUTE_VAL,
595     x_creation_date =>X_LAST_UPDATE_DATE,
596     x_created_by =>X_LAST_UPDATED_BY,
597     x_last_update_date =>X_LAST_UPDATE_DATE,
598     x_last_updated_by =>X_LAST_UPDATED_BY,
599     x_last_update_login =>X_LAST_UPDATE_LOGIN
600   );
601   insert into IGS_CA_DA_INST (
602     DT_ALIAS,
603     SEQUENCE_NUMBER,
604     CAL_TYPE,
605     CI_SEQUENCE_NUMBER,
606     ABSOLUTE_VAL,
607     CREATION_DATE,
608     CREATED_BY,
609     LAST_UPDATE_DATE,
610     LAST_UPDATED_BY,
611     LAST_UPDATE_LOGIN
612   ) values (
613     NEW_REFERENCES.DT_ALIAS,
614     NEW_REFERENCES.SEQUENCE_NUMBER,
615     NEW_REFERENCES.CAL_TYPE,
616     NEW_REFERENCES.CI_SEQUENCE_NUMBER,
617     NEW_REFERENCES.ABSOLUTE_VAL,
618     X_LAST_UPDATE_DATE,
619     X_LAST_UPDATED_BY,
620     X_LAST_UPDATE_DATE,
621     X_LAST_UPDATED_BY,
622     X_LAST_UPDATE_LOGIN
623   );
624 
625   open c;
626   fetch c into X_ROWID;
627   if (c%notfound) then
628     close c;
629     raise no_data_found;
630   end if;
631   close c;
632 After_DML (
633     p_action =>'INSERT',
634     x_rowid =>X_ROWID
635   );
636 end INSERT_ROW;
637 
638 procedure LOCK_ROW (
639   X_ROWID in VARCHAR2,
640   X_DT_ALIAS in VARCHAR2,
641   X_SEQUENCE_NUMBER in NUMBER,
642   X_CAL_TYPE in VARCHAR2,
643   X_CI_SEQUENCE_NUMBER in NUMBER,
644   X_ABSOLUTE_VAL in DATE
645 ) AS
646   cursor c1 is select
647       ABSOLUTE_VAL
648     from IGS_CA_DA_INST
649     where ROWID = X_ROWID
650     for update nowait;
651   tlinfo c1%rowtype;
652 
653 begin
654   open c1;
655   fetch c1 into tlinfo;
656   if (c1%notfound) then
657     close c1;
658     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
659     IGS_GE_MSG_STACK.ADD;
660     app_exception.raise_exception;
661     return;
662   end if;
663   close c1;
664 
665       if ( ((tlinfo.ABSOLUTE_VAL = X_ABSOLUTE_VAL)
666            OR ((tlinfo.ABSOLUTE_VAL is null)
667                AND (X_ABSOLUTE_VAL is null)))
668   ) then
669     null;
670   else
671     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
672     IGS_GE_MSG_STACK.ADD;
673     app_exception.raise_exception;
674   end if;
675   return;
676 end LOCK_ROW;
677 
678 procedure UPDATE_ROW (
679   X_ROWID in VARCHAR2,
680   X_DT_ALIAS in VARCHAR2,
681   X_SEQUENCE_NUMBER in NUMBER,
682   X_CAL_TYPE in VARCHAR2,
683   X_CI_SEQUENCE_NUMBER in NUMBER,
684   X_ABSOLUTE_VAL in DATE,
685   X_MODE in VARCHAR2
686   ) AS
687     X_LAST_UPDATE_DATE DATE;
688     X_LAST_UPDATED_BY NUMBER;
689     X_LAST_UPDATE_LOGIN NUMBER;
690 begin
691   X_LAST_UPDATE_DATE := SYSDATE;
692   if(X_MODE = 'I') then
693     X_LAST_UPDATED_BY := 1;
694     X_LAST_UPDATE_LOGIN := 0;
695   elsif (X_MODE = 'R') then
696     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
697     if X_LAST_UPDATED_BY is NULL then
698       X_LAST_UPDATED_BY := -1;
699     end if;
700     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
701     if X_LAST_UPDATE_LOGIN is NULL then
702       X_LAST_UPDATE_LOGIN := -1;
703     end if;
704   else
705     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
706     IGS_GE_MSG_STACK.ADD;
707     app_exception.raise_exception;
708   end if;
709 Before_DML (
710     p_action =>'UPDATE',
711     x_rowid =>X_ROWID,
712     x_dt_alias =>X_DT_ALIAS,
713     x_sequence_number =>X_SEQUENCE_NUMBER,
714     x_cal_type =>X_CAL_TYPE,
715     x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
716     x_absolute_val =>X_ABSOLUTE_VAL,
717     x_creation_date =>X_LAST_UPDATE_DATE,
718     x_created_by =>X_LAST_UPDATED_BY,
719     x_last_update_date =>X_LAST_UPDATE_DATE,
720     x_last_updated_by =>X_LAST_UPDATED_BY,
721     x_last_update_login =>X_LAST_UPDATE_LOGIN
722   );
723   update IGS_CA_DA_INST set
724     ABSOLUTE_VAL = NEW_REFERENCES.ABSOLUTE_VAL,
725     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
726     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
727     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
728   where ROWID=X_ROWID
729   ;
730   if (sql%notfound) then
731     raise no_data_found;
732   end if;
733 After_DML (
734     p_action =>'UPDATE',
735     x_rowid =>X_ROWID
736   );
737 end UPDATE_ROW;
738 
739 procedure ADD_ROW (
740   X_ROWID in out NOCOPY VARCHAR2,
741   X_DT_ALIAS in VARCHAR2,
742   X_SEQUENCE_NUMBER in NUMBER,
743   X_CAL_TYPE in VARCHAR2,
744   X_CI_SEQUENCE_NUMBER in NUMBER,
745   X_ABSOLUTE_VAL in DATE,
746   X_MODE in VARCHAR2
747   ) AS
748   cursor c1 is select rowid from IGS_CA_DA_INST
749      where DT_ALIAS = X_DT_ALIAS
750      and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
751      and CAL_TYPE = X_CAL_TYPE
752      and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
753   ;
754 begin
755   open c1;
756   fetch c1 into X_ROWID;
757   if (c1%notfound) then
758     close c1;
759     INSERT_ROW (
760      X_ROWID,
761      X_DT_ALIAS,
762      X_SEQUENCE_NUMBER,
763      X_CAL_TYPE,
764      X_CI_SEQUENCE_NUMBER,
765      X_ABSOLUTE_VAL,
766      X_MODE);
767     return;
768   end if;
769   close c1;
770   UPDATE_ROW (
771    X_ROWID,
772    X_DT_ALIAS,
773    X_SEQUENCE_NUMBER,
774    X_CAL_TYPE,
775    X_CI_SEQUENCE_NUMBER,
776    X_ABSOLUTE_VAL,
777    X_MODE);
778 end ADD_ROW;
779 
780 procedure DELETE_ROW (X_ROWID in VARCHAR2
781 ) AS
782 begin
783 After_DML (
784     p_action =>'DELETE',
785     x_rowid =>X_ROWID
786   );
787   delete from IGS_CA_DA_INST
788   where ROWID = X_ROWID;
789   if (sql%notfound) then
790     raise no_data_found;
791   end if;
792 After_DML (
793     p_action =>'DELETE',
794     x_rowid =>X_ROWID
795   );
796 end DELETE_ROW;
797 
798 end IGS_CA_DA_INST_PKG;