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