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