[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_ENC_DFLT_EFT_PKG
Source
1 package body IGS_FI_ENC_DFLT_EFT_PKG AS
2 /* $Header: IGSSI16B.pls 115.6 2003/10/15 09:38:50 ssaleem ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_FI_ENC_DFLT_EFT%RowType;
6 new_references IGS_FI_ENC_DFLT_EFT%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_encumbrance_type IN VARCHAR2 DEFAULT NULL,
11 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
12 x_comments IN VARCHAR2 DEFAULT NULL,
13 x_creation_date IN DATE DEFAULT NULL,
14 x_created_by IN NUMBER DEFAULT NULL,
15 x_last_update_date IN DATE DEFAULT NULL,
16 x_last_updated_by IN NUMBER DEFAULT NULL,
17 x_last_update_login IN NUMBER DEFAULT NULL
18 ) AS
19 CURSOR cur_old_ref_values IS
20 SELECT *
21 FROM IGS_FI_ENC_DFLT_EFT
22 WHERE rowid = x_rowid;
23 BEGIN
24 l_rowid := x_rowid;
25 -- Code for setting the Old and New Reference Values.
26 -- Populate Old Values.
27 Open cur_old_ref_values;
28 Fetch cur_old_ref_values INTO old_references;
29 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
30 Close cur_old_ref_values;
31 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
32 IGS_GE_MSG_STACK.ADD;
33 App_Exception.Raise_Exception;
34 Return;
35 END IF;
36 Close cur_old_ref_values;
37 -- Populate New Values.
38 new_references.encumbrance_type := x_encumbrance_type;
39 new_references.s_encmb_effect_type := x_s_encmb_effect_type;
40 new_references.comments := x_comments;
41 IF (p_action = 'UPDATE') THEN
42 new_references.creation_date := old_references.creation_date;
43 new_references.created_by := old_references.created_by;
44 ELSE
45 new_references.creation_date := x_creation_date;
46 new_references.created_by := x_created_by;
47 END IF;
48 new_references.last_update_date := x_last_update_date;
49 new_references.last_updated_by := x_last_updated_by;
50 new_references.last_update_login := x_last_update_login;
51 END Set_Column_Values;
52 -- Trigger description :-
53 -- "OSS_TST".trg_etde_br_iud
54 -- BEFORE INSERT OR DELETE OR UPDATE
55 -- ON IGS_FI_ENC_DFLT_EFT
56 -- FOR EACH ROW
57 PROCEDURE BeforeRowInsertUpdateDelete1(
58 p_inserting IN BOOLEAN DEFAULT FALSE,
59 p_updating IN BOOLEAN DEFAULT FALSE,
60 p_deleting IN BOOLEAN DEFAULT FALSE
61 ) AS
62 v_message_name varchar2(30);
63 BEGIN
64 -- Validate ENCUMBRANCE TYPE.
65 IF p_inserting OR p_updating THEN
66 IF IGS_EN_VAL_ETDE.enrp_val_et_closed (
67 new_references.encumbrance_type,
68 v_message_name) = FALSE THEN
69 Fnd_Message.Set_Name('IGS',v_message_name);
70 IGS_GE_MSG_STACK.ADD;
71 App_Exception.Raise_Exception;
72 END IF;
73 END IF;
74 -- Validate SYSTEM ENCUMBRANCE EFFECT TYPE.
75 IF p_inserting OR
76 (p_updating AND (old_references.s_encmb_effect_type <> new_references.s_encmb_effect_type)) THEN
77 IF IGS_EN_VAL_ETDE.enrp_val_seet_closed (
78 new_references.s_encmb_effect_type,
79 v_message_name) = FALSE THEN
80 Fnd_Message.Set_Name('IGS',v_message_name);
81 IGS_GE_MSG_STACK.ADD;
82 App_Exception.Raise_Exception;
83 END IF;
84 END IF;
85 END BeforeRowInsertUpdateDelete1;
86 PROCEDURE Check_Constraints (
87 Column_Name IN VARCHAR2 DEFAULT NULL,
88 Column_Value IN VARCHAR2 DEFAULT NULL
89 )
90 AS
91 BEGIN
92 IF column_name is null then
93 NULL;
94 ELSIF upper(Column_name) = 'ENCUMBRANCE_TYPE' then
95 new_references.encumbrance_type := column_value;
96 ELSIF upper(Column_name) = 'S_ENCMB_EFFECT_TYPE' then
97 new_references.s_encmb_effect_type := column_value;
98 End if;
99
100 IF upper(column_name) = 'ENCUMBRANCE_TYPE' OR
101 column_name is null Then
102 IF new_references.ENCUMBRANCE_TYPE <>
103 UPPER(new_references.ENCUMBRANCE_TYPE) Then
104 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception;
107 END IF;
108 END IF;
109
110 IF upper(column_name) = 'S_ENCMB_EFFECT_TYPE' OR
111 column_name is null Then
112 IF new_references.S_ENCMB_EFFECT_TYPE <>
113 UPPER(new_references.S_ENCMB_EFFECT_TYPE) Then
114 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception;
117 END IF;
118 END IF;
119 END Check_Constraints;
120 PROCEDURE Check_Parent_Existance AS
121 BEGIN
122 IF (((old_references.encumbrance_type = new_references.encumbrance_type)) OR
123 ((new_references.encumbrance_type IS NULL))) THEN
124 NULL;
125 ELSE
126 IF NOT IGS_FI_ENCMB_TYPE_PKG.Get_PK_For_Validation (
127 new_references.encumbrance_type
128 )
129 THEN
130 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133 END IF;
134 END IF;
135 IF (((old_references.s_encmb_effect_type = new_references.s_encmb_effect_type)) OR
136 ((new_references.s_encmb_effect_type IS NULL))) THEN
137 NULL;
138
139 ELSE
140 IF NOT IGS_EN_ENCMB_EFCTTYP_Pkg.Get_PK_For_Validation (
141 new_references.s_encmb_effect_type
142 ) THEN
143 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
144 IGS_GE_MSG_STACK.ADD;
145 App_Exception.Raise_Exception;
146 END IF;
147 END IF;
148 END Check_Parent_Existance;
149
150 PROCEDURE GET_FK_IGS_FI_ENCMB_TYPE (
151 x_encumbrance_type IN VARCHAR2
152 ) AS
153 CURSOR cur_rowid IS
154 SELECT rowid
155 FROM IGS_FI_ENC_DFLT_EFT
156 WHERE encumbrance_type = x_encumbrance_type ;
157 lv_rowid cur_rowid%RowType;
158 BEGIN
159 Open cur_rowid;
160 Fetch cur_rowid INTO lv_rowid;
161 IF (cur_rowid%FOUND) THEN
162 Close cur_rowid;
163 Fnd_Message.Set_Name ('IGS', 'IGS_FI_ETDE_ET_FK');
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 Return;
167 END IF;
168 Close cur_rowid;
169 END GET_FK_IGS_FI_ENCMB_TYPE;
170
171 Function Get_PK_For_Validation (
172 x_encumbrance_type IN VARCHAR2,
173 x_s_encmb_effect_type IN VARCHAR2
174 ) Return Boolean
175 AS
176 CURSOR cur_rowid IS
177 SELECT rowid
178 FROM IGS_FI_ENC_DFLT_EFT
179 WHERE encumbrance_type = x_encumbrance_type
180 AND s_encmb_effect_type = x_s_encmb_effect_type
181 FOR UPDATE NOWAIT;
182 lv_rowid cur_rowid%RowType;
183 BEGIN
184 Open cur_rowid;
185 Fetch cur_rowid INTO lv_rowid;
186 IF (cur_rowid%FOUND) THEN
187 Close cur_rowid;
188 Return (TRUE);
189 ELSE
190 Close cur_rowid;
191 Return (FALSE);
192 END IF;
193 END Get_PK_For_Validation;
194
195
196 PROCEDURE Before_DML (
197 p_action IN VARCHAR2,
198 x_rowid IN VARCHAR2 DEFAULT NULL,
199 x_encumbrance_type IN VARCHAR2 DEFAULT NULL,
200 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
201 x_comments IN VARCHAR2 DEFAULT NULL,
202 x_creation_date IN DATE DEFAULT NULL,
203 x_created_by IN NUMBER DEFAULT NULL,
204 x_last_update_date IN DATE DEFAULT NULL,
205 x_last_updated_by IN NUMBER DEFAULT NULL,
206 x_last_update_login IN NUMBER DEFAULT NULL
207 ) AS
208 BEGIN
209 Set_Column_Values (
210 p_action,
211 x_rowid,
212 x_encumbrance_type,
213 x_s_encmb_effect_type,
214 x_comments,
215 x_creation_date,
216 x_created_by,
217 x_last_update_date,
218 x_last_updated_by,
219 x_last_update_login
220 );
221 IF (p_action = 'INSERT') THEN
222 -- Call all the procedures related to Before Insert.
223 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
224 IF Get_PK_For_Validation (
225 new_references.encumbrance_type ,
226 new_references.s_encmb_effect_type
227 ) THEN
228 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception;
231 END IF;
232 Check_Constraints;
233 Check_Parent_Existance;
234 ELSIF (p_action = 'UPDATE') THEN
235 -- Call all the procedures related to Before Update.
236 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
237 Check_Constraints;
238 Check_Parent_Existance;
239 ELSIF (p_action = 'DELETE') THEN
240 -- Call all the procedures related to Before Delete.
241 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
242 ELSIF (p_action = 'VALIDATE_INSERT') THEN
243 IF Get_PK_For_Validation (
244 new_references.encumbrance_type ,
245 new_references.s_encmb_effect_type
246 ) THEN
247 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
248 IGS_GE_MSG_STACK.ADD;
249 App_Exception.Raise_Exception;
250 END IF;
251 Check_Constraints;
252 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
253 Check_Constraints;
254 ELSIF (p_action = 'VALIDATE_DELETE') THEN
255 Null;
256 END IF;
257 END Before_DML;
258 procedure INSERT_ROW (
259 X_ROWID in out NOCOPY VARCHAR2,
260 X_ENCUMBRANCE_TYPE in VARCHAR2,
261 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
262 X_COMMENTS in VARCHAR2,
263 X_MODE in VARCHAR2 default 'R'
264 ) AS
265 cursor C is select ROWID from IGS_FI_ENC_DFLT_EFT
266 where ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
267 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE;
268 X_LAST_UPDATE_DATE DATE;
269 X_LAST_UPDATED_BY NUMBER;
270 X_LAST_UPDATE_LOGIN NUMBER;
271 begin
272 X_LAST_UPDATE_DATE := SYSDATE;
273 if(X_MODE = 'I') then
274 X_LAST_UPDATED_BY := 1;
275 X_LAST_UPDATE_LOGIN := 0;
276 elsif (X_MODE = 'R') then
277 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
278 if X_LAST_UPDATED_BY is NULL then
279 X_LAST_UPDATED_BY := -1;
280 end if;
281 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
282 if X_LAST_UPDATE_LOGIN is NULL then
283 X_LAST_UPDATE_LOGIN := -1;
284 end if;
285 else
286 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
287 IGS_GE_MSG_STACK.ADD;
288 app_exception.raise_exception;
289 end if;
290 Before_DML (
291 p_action => 'INSERT',
292 x_rowid => x_rowid,
293 x_encumbrance_type => x_encumbrance_type,
294 x_s_encmb_effect_type => x_s_encmb_effect_type,
295 x_comments => x_comments,
296 x_creation_date => X_LAST_UPDATE_DATE,
297 x_created_by => X_LAST_UPDATED_BY,
298 x_last_update_date => X_LAST_UPDATE_DATE,
299 x_last_updated_by => X_LAST_UPDATED_BY,
300 x_last_update_login => X_LAST_UPDATE_LOGIN
301 );
302 insert into IGS_FI_ENC_DFLT_EFT (
303 ENCUMBRANCE_TYPE,
304 S_ENCMB_EFFECT_TYPE,
305 COMMENTS,
306 CREATION_DATE,
307 CREATED_BY,
308 LAST_UPDATE_DATE,
309 LAST_UPDATED_BY,
310 LAST_UPDATE_LOGIN
311 ) values (
312 NEW_REFERENCES.ENCUMBRANCE_TYPE,
313 NEW_REFERENCES.S_ENCMB_EFFECT_TYPE,
314 NEW_REFERENCES.COMMENTS,
315 X_LAST_UPDATE_DATE,
316 X_LAST_UPDATED_BY,
317 X_LAST_UPDATE_DATE,
318 X_LAST_UPDATED_BY,
319 X_LAST_UPDATE_LOGIN
320 );
321 open c;
322 fetch c into X_ROWID;
323 if (c%notfound) then
324 close c;
325 raise no_data_found;
326 end if;
327 close c;
328 end INSERT_ROW;
329 procedure LOCK_ROW (
330 X_ROWID in VARCHAR2,
331 X_ENCUMBRANCE_TYPE in VARCHAR2,
332 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
333 X_COMMENTS in VARCHAR2
334 ) AS
335 cursor c1 is select
336 COMMENTS
337 from IGS_FI_ENC_DFLT_EFT
338 where ROWID = X_ROWID
339 for update nowait;
340 tlinfo c1%rowtype;
341 begin
342 open c1;
343 fetch c1 into tlinfo;
344 if (c1%notfound) then
345 close c1;
346 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
347 IGS_GE_MSG_STACK.ADD;
348 app_exception.raise_exception;
349 return;
350 end if;
351 close c1;
352 if ( ((tlinfo.COMMENTS = X_COMMENTS)
353 OR ((tlinfo.COMMENTS is null)
354 AND (X_COMMENTS is null)))
355 ) then
356 null;
357 else
358 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
359 IGS_GE_MSG_STACK.ADD;
360 app_exception.raise_exception;
361 end if;
362 return;
363 end LOCK_ROW;
364 procedure UPDATE_ROW (
365 X_ROWID in VARCHAR2,
366 X_ENCUMBRANCE_TYPE in VARCHAR2,
367 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
368 X_COMMENTS in VARCHAR2,
369 X_MODE in VARCHAR2 default 'R'
370 ) AS
371 X_LAST_UPDATE_DATE DATE;
372 X_LAST_UPDATED_BY NUMBER;
373 X_LAST_UPDATE_LOGIN NUMBER;
374 begin
375 X_LAST_UPDATE_DATE := SYSDATE;
376 if(X_MODE = 'I') then
377 X_LAST_UPDATED_BY := 1;
378 X_LAST_UPDATE_LOGIN := 0;
379 elsif (X_MODE = 'R') then
380 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
381 if X_LAST_UPDATED_BY is NULL then
382 X_LAST_UPDATED_BY := -1;
383 end if;
384 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
385 if X_LAST_UPDATE_LOGIN is NULL then
386 X_LAST_UPDATE_LOGIN := -1;
387 end if;
388 else
389 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
390 IGS_GE_MSG_STACK.ADD;
391 app_exception.raise_exception;
392 end if;
393 Before_DML (
394 p_action => 'UPDATE',
395 x_rowid => x_rowid,
396 x_encumbrance_type => x_encumbrance_type,
397 x_s_encmb_effect_type => x_s_encmb_effect_type,
398 x_comments => x_comments,
399 x_creation_date => X_LAST_UPDATE_DATE,
400 x_created_by => X_LAST_UPDATED_BY,
401 x_last_update_date => X_LAST_UPDATE_DATE,
402 x_last_updated_by => X_LAST_UPDATED_BY,
403 x_last_update_login => X_LAST_UPDATE_LOGIN
404 );
405 update IGS_FI_ENC_DFLT_EFT set
406 COMMENTS = NEW_REFERENCES.COMMENTS,
407 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
408 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
409 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
410 where ROWID = X_ROWID;
411 if (sql%notfound) then
412 raise no_data_found;
413 end if;
414 end UPDATE_ROW;
415 procedure ADD_ROW (
416 X_ROWID in out NOCOPY VARCHAR2,
417 X_ENCUMBRANCE_TYPE in VARCHAR2,
418 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
419 X_COMMENTS in VARCHAR2,
420 X_MODE in VARCHAR2 default 'R'
421 ) AS
422 cursor c1 is select rowid from IGS_FI_ENC_DFLT_EFT
423 where ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
424 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
425 ;
426 begin
427 open c1;
428 fetch c1 into X_ROWID;
429 if (c1%notfound) then
430 close c1;
431 INSERT_ROW (
432 X_ROWID,
433 X_ENCUMBRANCE_TYPE,
434 X_S_ENCMB_EFFECT_TYPE,
435 X_COMMENTS,
436 X_MODE);
437 return;
438 end if;
439 close c1;
440 UPDATE_ROW (
441 X_ROWID,
442 X_ENCUMBRANCE_TYPE,
443 X_S_ENCMB_EFFECT_TYPE,
444 X_COMMENTS,
445 X_MODE);
446 end ADD_ROW;
447 procedure DELETE_ROW (
448 X_ROWID in VARCHAR2
449 ) AS
450 begin
451 Before_DML(
452 p_action => 'DELETE',
453 x_rowid => X_ROWID
454 );
455 delete from IGS_FI_ENC_DFLT_EFT
456 where ROWID = X_ROWID;
457 if (sql%notfound) then
458 raise no_data_found;
459 end if;
460 end DELETE_ROW;
461 end IGS_FI_ENC_DFLT_EFT_PKG;