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