[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_DA_PAIR_PKG
Source
1 package body IGS_CA_DA_PAIR_PKG AS
2 /* $Header: IGSCI10B.pls 115.4 2002/11/28 23:01:56 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_CA_DA_PAIR%RowType;
5 new_references IGS_CA_DA_PAIR%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_related_dt_alias IN VARCHAR2 DEFAULT NULL,
12 x_creation_date IN DATE DEFAULT NULL,
13 x_created_by IN NUMBER DEFAULT NULL,
14 x_last_update_date IN DATE DEFAULT NULL,
15 x_last_updated_by IN NUMBER DEFAULT NULL,
16 x_last_update_login IN NUMBER DEFAULT NULL
17 ) AS
18
19 CURSOR cur_old_ref_values IS
20 SELECT *
21 FROM IGS_CA_DA_PAIR
22 WHERE rowid = x_rowid;
23
24 BEGIN
25
26 l_rowid := x_rowid;
27
28 -- Code for setting the Old and New Reference Values.
29 -- Populate Old Values.
30 Open cur_old_ref_values;
31 Fetch cur_old_ref_values INTO old_references;
32 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
33 Close cur_old_ref_values;
34 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35 IGS_GE_MSG_STACK.ADD;
36 App_Exception.Raise_Exception;
37 Return;
38 END IF;
39 Close cur_old_ref_values;
40
41 -- Populate New Values.
42 new_references.dt_alias := x_dt_alias;
43 new_references.related_dt_alias := x_related_dt_alias;
44 IF (p_action = 'UPDATE') THEN
45 new_references.creation_date := old_references.creation_date;
46 new_references.created_by := old_references.created_by;
47 ELSE
48 new_references.creation_date := x_creation_date;
49 new_references.created_by := x_created_by;
50 END IF;
51 new_references.last_update_date := x_last_update_date;
52 new_references.last_updated_by := x_last_updated_by;
53 new_references.last_update_login := x_last_update_login;
54
55 END Set_Column_Values;
56
57 -- Trigger description :-
58 -- "OSS_TST".trg_dap_br_i
59 -- BEFORE INSERT
60 -- ON IGS_CA_DA_PAIR
61 -- FOR EACH ROW
62
63 PROCEDURE BeforeRowInsert1(
64 p_inserting IN BOOLEAN DEFAULT FALSE,
65 p_updating IN BOOLEAN DEFAULT FALSE,
66 p_deleting IN BOOLEAN DEFAULT FALSE
67 ) AS
68 v_message_name varchar2(30);
69 BEGIN
70 -- Validate that the related_dt_alias is not closed
71 IF IGS_CA_VAL_DAP.calp_val_dap_da(
72 new_references.related_dt_alias,
73 v_message_name) = FALSE THEN
74 Fnd_Message.Set_Name('IGS',v_message_name);
75 IGS_GE_MSG_STACK.ADD;
76 APP_EXCEPTION.RAISE_EXCEPTION;
77 END IF;
78
79
80
81 END BeforeRowInsert1;
82
83 PROCEDURE Check_Constraints (
84 column_name IN VARCHAR2 DEFAULT NULL,
85 column_value IN VARCHAR2 DEFAULT NULL)
86 AS
87 BEGIN
88
89 IF column_name is null then
90 null;
91 ELSIF upper(column_name) = 'RELATED_DT_ALIAS' Then
92 new_references.related_dt_alias := column_value;
93 ELSIF upper(column_name) = 'DT_ALIAS' Then
94 new_references.dt_alias := column_value;
95 End if;
96
97 If upper(Column_name)= 'RELATED_DT_ALIAS' Or column_name is null then
98 If UPPER(new_references.related_dt_alias) <> new_references.related_dt_alias Then
99 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 End if;
103 End if;
104
105 If upper(Column_name)= 'DT_ALIAS' Or column_name is null then
106 If UPPER(new_references.dt_alias) <> new_references.dt_alias Then
107 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 End if;
111 End if;
112
113 If column_name is null then
114 If new_references.related_dt_alias = new_references.dt_alias Then
115 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
116 IGS_GE_MSG_STACK.ADD;
117 App_Exception.Raise_Exception;
118 End if;
119 End if;
120
121 END Check_Constraints;
122
123 PROCEDURE Check_Parent_Existance AS
124 BEGIN
125
126 IF (((old_references.dt_alias = new_references.dt_alias)) OR
127 ((new_references.dt_alias IS NULL))) THEN
128 NULL;
129 ELSE
130 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
131 new_references.dt_alias
132 ) Then
133 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
134 IGS_GE_MSG_STACK.ADD;
135 app_exception.raise_exception;
136 END IF;
137 END IF;
138
139 IF (((old_references.related_dt_alias = new_references.related_dt_alias)) OR
140 ((new_references.related_dt_alias IS NULL))) THEN
141 NULL;
142 ELSE
143 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
144 new_references.related_dt_alias
145 ) Then
146 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
147 IGS_GE_MSG_STACK.ADD;
148 app_exception.raise_exception;
149 END IF;
150 END IF;
151
152 END Check_Parent_Existance;
153
154 FUNCTION Get_PK_For_Validation (
155 x_dt_alias IN VARCHAR2,
156 x_related_dt_alias IN VARCHAR2
157 ) RETURN BOOLEAN AS
158
159 CURSOR cur_rowid IS
160 SELECT rowid
161 FROM IGS_CA_DA_PAIR
162 WHERE dt_alias = x_dt_alias
163 AND related_dt_alias = x_related_dt_alias
164 FOR UPDATE NOWAIT;
165
166 lv_rowid cur_rowid%RowType;
167
168 BEGIN
169
170 Open cur_rowid;
171 Fetch cur_rowid INTO lv_rowid;
172 IF (cur_rowid%FOUND) THEN
173 Close cur_rowid;
174 Return(TRUE);
175 Else
176 Close cur_rowid;
177 Return(FALSE);
178 END IF;
179
180 END Get_PK_For_Validation;
181
182 PROCEDURE GET_FK_IGS_CA_DA (
183 x_dt_alias IN VARCHAR2
184 ) AS
185
186 CURSOR cur_rowid IS
187 SELECT rowid
188 FROM IGS_CA_DA_PAIR
189 WHERE dt_alias = x_dt_alias
190 OR related_dt_alias = x_dt_alias;
191
192 lv_rowid cur_rowid%RowType;
193
194 BEGIN
195
196 Open cur_rowid;
197 Fetch cur_rowid INTO lv_rowid;
198 IF (cur_rowid%FOUND) THEN
199 Close cur_rowid;
200 Fnd_Message.Set_Name ('IGS', 'IGS_CA_DAP_DA_FK');
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203 Return;
204 END IF;
205 Close cur_rowid;
206
207 END GET_FK_IGS_CA_DA;
208
209
210 PROCEDURE Before_DML (
211 p_action IN VARCHAR2,
212 x_rowid IN VARCHAR2 DEFAULT NULL,
213 x_dt_alias IN VARCHAR2 DEFAULT NULL,
214 x_related_dt_alias IN VARCHAR2 DEFAULT NULL,
215 x_creation_date IN DATE DEFAULT NULL,
216 x_created_by IN NUMBER DEFAULT NULL,
217 x_last_update_date IN DATE DEFAULT NULL,
218 x_last_updated_by IN NUMBER DEFAULT NULL,
219 x_last_update_login IN NUMBER DEFAULT NULL
220 ) AS
221 BEGIN
222
223 Set_Column_Values (
224 p_action,
225 x_rowid,
226 x_dt_alias,
227 x_related_dt_alias,
228 x_creation_date,
229 x_created_by,
230 x_last_update_date,
231 x_last_updated_by,
232 x_last_update_login
233 );
234
235 IF (p_action = 'INSERT') THEN
236 -- Call all the procedures related to Before Insert.
237 BeforeRowInsert1 ( p_inserting => TRUE );
238 if get_pk_for_validation(
239 new_references.dt_alias ,
240 new_references.related_dt_alias
241 ) Then
242 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
243 IGS_GE_MSG_STACK.ADD;
244 app_exception.raise_exception;
245 end if;
246 check_constraints;
247 Check_Parent_Existance;
248
249 ELSIF (p_action = 'UPDATE') THEN
250 -- Call all the procedures related to Before Update.
251 Null;
252 Check_Constraints;
253 Check_Parent_Existance;
254 ELSIF (p_action = 'DELETE') THEN
255 -- Call all the procedures related to Before Delete.
256 Null;
257
258 ELSIF (p_action = 'VALIDATE_INSERT') THEN
259 if get_pk_for_validation(
260 new_references.dt_alias ,
261 new_references.related_dt_alias
262 ) Then
263 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
264 IGS_GE_MSG_STACK.ADD;
265 app_exception.raise_exception;
266 end if;
267 check_constraints;
268 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
269 Check_Constraints;
270 ELSIF (p_action = 'VALIDATE_DELETE') THEN
271 null;
272
273 END IF;
274
275 END Before_DML;
276
277 procedure INSERT_ROW (
278 X_ROWID in out NOCOPY VARCHAR2,
279 X_DT_ALIAS in VARCHAR2,
280 X_RELATED_DT_ALIAS in VARCHAR2,
281 X_MODE in VARCHAR2 default 'R'
282 ) AS
283 cursor C is select ROWID from IGS_CA_DA_PAIR
284 where DT_ALIAS = X_DT_ALIAS
285 and RELATED_DT_ALIAS = X_RELATED_DT_ALIAS;
286 X_LAST_UPDATE_DATE DATE;
287 X_LAST_UPDATED_BY NUMBER;
288 X_LAST_UPDATE_LOGIN NUMBER;
289 begin
290 X_LAST_UPDATE_DATE := SYSDATE;
291 if(X_MODE = 'I') then
292 X_LAST_UPDATED_BY := 1;
293 X_LAST_UPDATE_LOGIN := 0;
294 elsif (X_MODE = 'R') then
295 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
296 if X_LAST_UPDATED_BY is NULL then
297 X_LAST_UPDATED_BY := -1;
298 end if;
299 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
300 if X_LAST_UPDATE_LOGIN is NULL then
301 X_LAST_UPDATE_LOGIN := -1;
302 end if;
303 else
304 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
305 IGS_GE_MSG_STACK.ADD;
306 app_exception.raise_exception;
307 end if;
308 Before_DML (
309 p_action =>'INSERT',
310 x_rowid =>X_ROWID,
311 x_dt_alias =>X_DT_ALIAS,
312 x_related_dt_alias =>X_RELATED_DT_ALIAS,
313 x_creation_date =>X_LAST_UPDATE_DATE,
314 x_created_by =>X_LAST_UPDATED_BY,
315 x_last_update_date =>X_LAST_UPDATE_DATE,
316 x_last_updated_by =>X_LAST_UPDATED_BY,
317 x_last_update_login =>X_LAST_UPDATE_LOGIN
318 );
319 insert into IGS_CA_DA_PAIR (
320 DT_ALIAS,
321 RELATED_DT_ALIAS,
322 CREATION_DATE,
323 CREATED_BY,
324 LAST_UPDATE_DATE,
325 LAST_UPDATED_BY,
326 LAST_UPDATE_LOGIN
327 ) values (
328 NEW_REFERENCES.DT_ALIAS,
329 NEW_REFERENCES.RELATED_DT_ALIAS,
330 X_LAST_UPDATE_DATE,
331 X_LAST_UPDATED_BY,
332 X_LAST_UPDATE_DATE,
333 X_LAST_UPDATED_BY,
334 X_LAST_UPDATE_LOGIN
335 );
336
337 open c;
338 fetch c into X_ROWID;
339 if (c%notfound) then
340 close c;
341 raise no_data_found;
342 end if;
343 close c;
344 end INSERT_ROW;
345
346 procedure LOCK_ROW (
347 X_ROWID in VARCHAR2,
348 X_DT_ALIAS in VARCHAR2,
349 X_RELATED_DT_ALIAS in VARCHAR2
350 ) AS
351 cursor c1 is select ROWID
352 from IGS_CA_DA_PAIR
353 where ROWID = X_ROWID
354 for update nowait;
355 tlinfo c1%rowtype;
356
357 begin
358 open c1;
359 fetch c1 into tlinfo;
360 if (c1%notfound) then
361 close c1;
362 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
363 IGS_GE_MSG_STACK.ADD;
364 app_exception.raise_exception;
365 return;
366 end if;
367 close c1;
368 return;
369 end LOCK_ROW;
370
371 procedure DELETE_ROW (
372 X_ROWID in VARCHAR2
373 ) AS
374 begin
375 Before_DML (
376 p_action =>'DELETE',
377 x_rowid =>X_ROWID
378 );
379 delete from IGS_CA_DA_PAIR
380 where ROWID = X_ROWID;
381 if (sql%notfound) then
382 raise no_data_found;
383 end if;
384 end DELETE_ROW;
385
386 end IGS_CA_DA_PAIR_PKG;