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