1 package body IGS_GE_S_DSB_TAB_TRG_PKG as
2 /* $Header: IGSMI13B.pls 115.3 2002/11/29 01:12:22 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_GE_S_DSB_TAB_TRG%RowType;
6 new_references IGS_GE_S_DSB_TAB_TRG%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_table_name IN VARCHAR2 DEFAULT NULL,
12 x_session_id IN NUMBER 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_GE_S_DSB_TAB_TRG
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.table_name := x_table_name;
44 new_references.session_id := x_session_id;
45 IF (p_action = 'UPDATE') THEN
46 new_references.creation_date := old_references.creation_date;
47 new_references.created_by := old_references.created_by;
48 ELSE
49 new_references.creation_date := x_creation_date;
50 new_references.created_by := x_created_by;
51 END IF;
52 new_references.last_update_date := x_last_update_date;
53 new_references.last_updated_by := x_last_updated_by;
54 new_references.last_update_login := x_last_update_login;
55
56 END Set_Column_Values;
57
58 PROCEDURE Check_Constraints(
59 Column_Name IN VARCHAR2 DEFAULT NULL,
60 Column_Value IN VARCHAR2 DEFAULT NULL
61 ) as
62 BEGIN
63 IF column_name is null then
64 NULL;
65 ELSIF upper(Column_name) = 'TABLE_NAME' then
66 new_references.table_name := column_value;
67 END IF;
68 IF upper(Column_name) = 'TABLE_NAME' OR column_name is null then
69 IF new_references.table_name <> UPPER(new_references.table_name ) then
70 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
71 IGS_GE_MSG_STACK.ADD;
72 App_Exception.Raise_Exception;
73 END IF;
74 END IF;
75 END Check_Constraints;
76
77 FUNCTION GET_PK_FOR_VALIDATION (
78 x_table_name IN VARCHAR2,
79 x_session_id IN NUMBER
80 ) RETURN BOOLEAN as
81
82 CURSOR cur_rowid IS
83 SELECT rowid
84 FROM IGS_GE_S_DSB_TAB_TRG
85 WHERE table_name = x_table_name
86 AND session_id = x_session_id
87 FOR UPDATE NOWAIT;
88
89 lv_rowid cur_rowid%RowType;
90
91 BEGIN
92
93 Open cur_rowid;
94 Fetch cur_rowid INTO lv_rowid;
95 IF (cur_rowid%FOUND) THEN
96 Close cur_rowid;
97 Return(TRUE);
98 ELSE
99 Close cur_rowid;
100 Return(FALSE);
101 END IF;
102
103 END Get_PK_For_Validation;
104
105 PROCEDURE Before_DML (
106 p_action IN VARCHAR2,
107 x_rowid IN VARCHAR2 DEFAULT NULL,
108 x_table_name IN VARCHAR2 DEFAULT NULL,
109 x_session_id IN NUMBER DEFAULT NULL,
110 x_creation_date IN DATE DEFAULT NULL,
111 x_created_by IN NUMBER DEFAULT NULL,
112 x_last_update_date IN DATE DEFAULT NULL,
113 x_last_updated_by IN NUMBER DEFAULT NULL,
114 x_last_update_login IN NUMBER DEFAULT NULL
115 ) as
116 BEGIN
117
118 Set_Column_Values (
119 p_action,
120 x_rowid,
121 x_table_name,
122 x_session_id,
123 x_creation_date,
124 x_created_by,
125 x_last_update_date,
126 x_last_updated_by,
127 x_last_update_login
128 );
129
130 IF (p_action = 'INSERT') THEN
131 -- Call all the procedures related to Before Insert.
132 IF Get_PK_For_Validation(
133 new_references.table_name,
134 new_references.session_id
135 )THEN
136 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 END IF ;
140 Check_Constraints;
141 ELSIF (p_action = 'UPDATE') THEN
142 -- Call all the procedures related to Before Update.
143 Check_Constraints;
144 ELSIF (p_action = 'DELETE') THEN
145 -- Call all the procedures related to Before Delete.
146 Null;
147 ELSIF (p_action = 'VALIDATE_INSERT') THEN
148 -- Call all the procedures related to Before Insert.
149 IF Get_PK_For_Validation(
150 new_references.table_name,
151 new_references.session_id
152 )THEN
153 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END IF ;
157 Check_Constraints;
158 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
159 -- Call all the procedures related to Before Update.
160 Check_Constraints;
161 ELSIF (p_action = 'VALIDATE_DELETE') THEN
162 -- Call all the procedures related to Before Delete.
163 Null;
164
165 END IF;
166
167 END Before_DML;
168
169 PROCEDURE After_DML (
170 p_action IN VARCHAR2,
171 x_rowid IN VARCHAR2
172 ) as
173 BEGIN
174
175 l_rowid := x_rowid;
176
177 IF (p_action = 'INSERT') THEN
178 -- Call all the procedures related to After Insert.
179 Null;
180 ELSIF (p_action = 'UPDATE') THEN
181 -- Call all the procedures related to After Update.
182 Null;
183 ELSIF (p_action = 'DELETE') THEN
184 -- Call all the procedures related to After Delete.
185 Null;
186 END IF;
187
188 END After_DML;
189
190
191 procedure INSERT_ROW (
192 X_ROWID in out NOCOPY VARCHAR2,
193 X_TABLE_NAME in VARCHAR2,
194 X_SESSION_ID in NUMBER,
195 X_MODE in VARCHAR2 default 'R'
196 ) as
197 gv_other_detail varchar2(255);
198
199 cursor C is select ROWID from IGS_GE_S_DSB_TAB_TRG
200 where TABLE_NAME = X_TABLE_NAME
201 and SESSION_ID = X_SESSION_ID;
202 X_LAST_UPDATE_DATE DATE;
203 X_LAST_UPDATED_BY NUMBER;
204 X_LAST_UPDATE_LOGIN NUMBER;
205 begin
206 X_LAST_UPDATE_DATE := SYSDATE;
207 if(X_MODE = 'I') then
208 X_LAST_UPDATED_BY := 1;
209 X_LAST_UPDATE_LOGIN := 0;
210 elsif (X_MODE = 'R') then
211 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
212 if X_LAST_UPDATED_BY is NULL then
213 X_LAST_UPDATED_BY := -1;
214 end if;
215 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
216 if X_LAST_UPDATE_LOGIN is NULL then
217 X_LAST_UPDATE_LOGIN := -1;
218 end if;
219 else
220 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
221 IGS_GE_MSG_STACK.ADD;
222 app_exception.raise_exception;
223 end if;
224 Before_DML(
225 p_action => 'INSERT',
226 x_rowid => X_ROWID,
227 x_table_name => X_TABLE_NAME,
228 x_session_id => X_SESSION_ID,
229 x_creation_date => X_LAST_UPDATE_DATE,
230 x_created_by => X_LAST_UPDATED_BY,
231 x_last_update_date => X_LAST_UPDATE_DATE,
232 x_last_updated_by => X_LAST_UPDATED_BY,
233 x_last_update_login => X_LAST_UPDATE_LOGIN
234 );
235
236 insert into IGS_GE_S_DSB_TAB_TRG (
237 TABLE_NAME,
238 SESSION_ID,
239 CREATION_DATE,
240 CREATED_BY,
241 LAST_UPDATE_DATE,
242 LAST_UPDATED_BY,
243 LAST_UPDATE_LOGIN
244 ) values (
245 NEW_REFERENCES.TABLE_NAME,
246 NEW_REFERENCES.SESSION_ID,
247 X_LAST_UPDATE_DATE,
248 X_LAST_UPDATED_BY,
249 X_LAST_UPDATE_DATE,
250 X_LAST_UPDATED_BY,
251 X_LAST_UPDATE_LOGIN
252 );
253
254 open c;
255 fetch c into X_ROWID;
256 if (c%notfound) then
257 close c;
258 raise no_data_found;
259 end if;
260 close c;
261 After_DML(
262 p_action => 'INSERT',
263 x_rowid => X_ROWID
264 );
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception ;
271 end INSERT_ROW;
272
273 procedure LOCK_ROW (
274 X_ROWID in VARCHAR2,
275 X_TABLE_NAME in VARCHAR2,
276 X_SESSION_ID in NUMBER
277 ) as
278 cursor c1 is select *
279 from IGS_GE_S_DSB_TAB_TRG
280 for update of TABLE_NAME nowait;
281 tlinfo c1%rowtype;
282
283 begin
284 open c1;
285 fetch c1 into tlinfo;
286 if (c1%notfound) then
287 close c1;
288 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
289 IGS_GE_MSG_STACK.ADD;
290 app_exception.raise_exception;
291 return;
292 end if;
293 close c1;
294 return;
295 end LOCK_ROW;
296
297 procedure DELETE_ROW (
298 X_ROWID in VARCHAR2
299 ) as
300 begin
301 Before_DML(
302 p_action => 'DELETE',
303 x_rowid => X_ROWID
304 );
305
306 delete from IGS_GE_S_DSB_TAB_TRG
307 where ROWID = X_ROWID ;
308 if (sql%notfound) then
309 raise no_data_found;
310 end if;
311 After_DML
312 (
313 p_action => 'DELETE',
314 x_rowid => X_ROWID
315 );
316
317 end DELETE_ROW;
318
319 end IGS_GE_S_DSB_TAB_TRG_PKG;
[Home] [Help]