DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_CA_DA_INST_OFCNT_PKG

Source


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