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