[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_RU_PKG
Source
1 package body IGS_PS_UNIT_RU_PKG as
2 /* $Header: IGSPI90B.pls 115.7 2002/11/29 02:41:32 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_UNIT_RU%RowType;
6 new_references IGS_PS_UNIT_RU%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_s_rule_call_cd IN VARCHAR2 DEFAULT NULL,
13 x_rul_sequence_number IN NUMBER 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 ) AS
20
21 CURSOR cur_old_ref_values IS
22 SELECT *
23 FROM IGS_PS_UNIT_RU
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.s_rule_call_cd := x_s_rule_call_cd;
46 new_references.rul_sequence_number := x_rul_sequence_number;
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 Check_Constraints(
61 Column_Name IN VARCHAR2 DEFAULT NULL,
62 Column_Value IN VARCHAR2 DEFAULT NULL)
63 AS
64 BEGIN
65
66 IF Column_Name IS NULL Then
67 NULL;
68 ELSIF Upper(Column_Name)='S_RULE_CALL_CD' Then
69 New_References.s_rule_call_cd := Column_Value;
70 ELSIF Upper(Column_Name)='UNIT_CD' Then
71 New_References.Unit_Cd := Column_Value;
72 END IF;
73
74 IF Upper(Column_Name)='S_RULE_CALL_CD' OR Column_Name IS NULL Then
75 IF New_References.S_Rule_Call_Cd <> UPPER(New_References.S_Rule_Call_Cd) Then
76 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
77 IGS_GE_MSG_STACK.ADD;
78 App_Exception.Raise_Exception;
79 END IF;
80 END IF;
81
82 IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
83 IF New_References.Unit_Cd <> UPPER(New_References.Unit_CD) Then
84 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
85 IGS_GE_MSG_STACK.ADD;
86 App_Exception.Raise_Exception;
87 END IF;
88 END IF;
89
90 END Check_Constraints;
91
92 PROCEDURE Check_Parent_Existance AS
93 BEGIN
94
95 IF (((old_references.s_rule_call_cd = new_references.s_rule_call_cd)) OR
96 ((new_references.s_rule_call_cd IS NULL))) THEN
97 NULL;
98 ELSE
99 IF NOT IGS_RU_CALL_PKG.Get_PK_For_Validation (
100 new_references.s_rule_call_cd) THEN
101 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105
106
107 END IF;
108
109 IF (((old_references.unit_cd = new_references.unit_cd)) OR
110 ((new_references.unit_cd IS NULL))) THEN
111 NULL;
112 ELSE
113 IF NOT IGS_PS_UNIT_PKG.Get_PK_For_Validation (
114 new_references.unit_cd) THEN
115 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
116 IGS_GE_MSG_STACK.ADD;
117 App_Exception.Raise_Exception;
118 END IF;
119
120
121 END IF;
122
123 IF (((old_references.rul_sequence_number = new_references.rul_sequence_number)) OR
124 ((new_references.rul_sequence_number IS NULL))) THEN
125 NULL;
126 ELSE
127 IF NOT IGS_RU_RULE_PKG.Get_PK_For_Validation (
128 new_references.rul_sequence_number) THEN
129 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
130 IGS_GE_MSG_STACK.ADD;
131 App_Exception.Raise_Exception;
132 END IF;
133
134
135 END IF;
136
137 END Check_Parent_Existance;
138
139 FUNCTION Get_PK_For_Validation (
140 x_unit_cd IN VARCHAR2,
141 x_s_rule_call_cd IN VARCHAR2
142 ) RETURN BOOLEAN AS
143
144 CURSOR cur_rowid IS
145 SELECT rowid
146 FROM IGS_PS_UNIT_RU
147 WHERE unit_cd = x_unit_cd
148 AND s_rule_call_cd = x_s_rule_call_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
165 END Get_PK_For_Validation;
166
167 PROCEDURE GET_FK_IGS_RU_CALL (
168 x_s_rule_call_cd IN VARCHAR2
169 ) AS
170
171 CURSOR cur_rowid IS
172 SELECT rowid
173 FROM IGS_PS_UNIT_RU
174 WHERE s_rule_call_cd = x_s_rule_call_cd ;
175
176 lv_rowid cur_rowid%RowType;
177
178 BEGIN
179
180 Open cur_rowid;
181 Fetch cur_rowid INTO lv_rowid;
182 IF (cur_rowid%FOUND) THEN
183 Close cur_rowid;
184 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UR_SRC_FK');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 Return;
188 END IF;
189 Close cur_rowid;
190
191 END GET_FK_IGS_RU_CALL;
192
193 PROCEDURE GET_FK_IGS_PS_UNIT (
194 x_unit_cd IN VARCHAR2
195 ) AS
196
197 CURSOR cur_rowid IS
198 SELECT rowid
199 FROM IGS_PS_UNIT_RU
200 WHERE unit_cd = x_unit_cd ;
201
202 lv_rowid cur_rowid%RowType;
203
204 BEGIN
205
206 Open cur_rowid;
207 Fetch cur_rowid INTO lv_rowid;
208 IF (cur_rowid%FOUND) THEN
209 Close cur_rowid;
210 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UR_UN_FK');
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception;
213 Return;
214 END IF;
215 Close cur_rowid;
216
217 END GET_FK_IGS_PS_UNIT;
218
219 PROCEDURE GET_FK_IGS_RU_RULE (
220 x_sequence_number IN NUMBER
221 ) AS
222
223 CURSOR cur_rowid IS
224 SELECT rowid
225 FROM IGS_PS_UNIT_RU
226 WHERE rul_sequence_number = x_sequence_number ;
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_UR_RUL_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_RU_RULE;
244
245 PROCEDURE Before_DML (
246 p_action IN VARCHAR2,
247 x_rowid IN VARCHAR2 DEFAULT NULL,
248 x_unit_cd IN VARCHAR2 DEFAULT NULL,
249 x_s_rule_call_cd IN VARCHAR2 DEFAULT NULL,
250 x_rul_sequence_number IN NUMBER DEFAULT NULL,
251 x_creation_date IN DATE DEFAULT NULL,
252 x_created_by IN NUMBER DEFAULT NULL,
253 x_last_update_date IN DATE DEFAULT NULL,
254 x_last_updated_by IN NUMBER DEFAULT NULL,
255 x_last_update_login IN NUMBER DEFAULT NULL
256 ) AS
257 BEGIN
258
259 Set_Column_Values (
260 p_action,
261 x_rowid,
262 x_unit_cd,
263 x_s_rule_call_cd,
264 x_rul_sequence_number,
265 x_creation_date,
266 x_created_by,
267 x_last_update_date,
268 x_last_updated_by,
269 x_last_update_login
270 );
271
272 IF (p_action = 'INSERT') THEN
273 -- Call all the procedures related to Before Insert.
274 IF Get_PK_For_Validation (New_References.unit_cd,
275 New_References.s_rule_call_cd) THEN
276 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
277 IGS_GE_MSG_STACK.ADD;
278 App_Exception.Raise_Exception;
279 END IF;
280 Check_Constraints;
281 Check_Parent_Existance;
282 ELSIF (p_action = 'UPDATE') THEN
283 -- Call all the procedures related to Before Update.
284 Check_Constraints;
285 Check_Parent_Existance;
286
287
288 ELSIF (p_action = 'VALIDATE_INSERT') THEN
289 IF Get_PK_For_Validation (New_References.unit_cd,
290 New_References.s_rule_call_cd) THEN
291 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
292 IGS_GE_MSG_STACK.ADD;
293 App_Exception.Raise_Exception;
294 END IF;
295 Check_Constraints;
296 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
297 Check_Constraints;
298
299 END IF;
300
301 END Before_DML;
302
303 PROCEDURE After_DML (
304 p_action IN VARCHAR2,
305 x_rowid IN VARCHAR2
306 ) AS
307 BEGIN
308
309 l_rowid := x_rowid;
310
311
312 END After_DML;
313
314 procedure INSERT_ROW (
315 X_ROWID in out NOCOPY VARCHAR2,
316 X_UNIT_CD in VARCHAR2,
317 X_S_RULE_CALL_CD in VARCHAR2,
318 X_RUL_SEQUENCE_NUMBER in NUMBER,
319 X_MODE in VARCHAR2 default 'R'
320 ) AS
321 cursor C is select ROWID from IGS_PS_UNIT_RU
322 where UNIT_CD = X_UNIT_CD
323 and S_RULE_CALL_CD = X_S_RULE_CALL_CD;
324 X_LAST_UPDATE_DATE DATE;
325 X_LAST_UPDATED_BY NUMBER;
326 X_LAST_UPDATE_LOGIN NUMBER;
327 begin
328 X_LAST_UPDATE_DATE := SYSDATE;
329 if(X_MODE = 'I') then
330 X_LAST_UPDATED_BY := 1;
331 X_LAST_UPDATE_LOGIN := 0;
332 elsif (X_MODE = 'R') then
333 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
334 if X_LAST_UPDATED_BY is NULL then
335 X_LAST_UPDATED_BY := -1;
336 end if;
337 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
338 if X_LAST_UPDATE_LOGIN is NULL then
339 X_LAST_UPDATE_LOGIN := -1;
340 end if;
341 else
342 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
343 IGS_GE_MSG_STACK.ADD;
344 app_exception.raise_exception;
345 end if;
346
347 Before_DML(
348 p_action => 'INSERT',
349 x_rowid => X_ROWID,
350 x_unit_cd => X_UNIT_CD,
351 x_s_rule_call_cd => X_S_RULE_CALL_CD,
352 x_rul_sequence_number => X_RUL_SEQUENCE_NUMBER,
353 x_creation_date => X_LAST_UPDATE_DATE,
354 x_created_by => X_LAST_UPDATED_BY,
355 x_last_update_date => X_LAST_UPDATE_DATE,
356 x_last_updated_by => X_LAST_UPDATED_BY,
357 x_last_update_login => X_LAST_UPDATE_LOGIN
358 );
359
360 insert into IGS_PS_UNIT_RU (
361 UNIT_CD,
362 S_RULE_CALL_CD,
363 RUL_SEQUENCE_NUMBER,
364 CREATION_DATE,
365 CREATED_BY,
366 LAST_UPDATE_DATE,
367 LAST_UPDATED_BY,
368 LAST_UPDATE_LOGIN
369 ) values (
370 NEW_REFERENCES.UNIT_CD,
371 NEW_REFERENCES.S_RULE_CALL_CD,
372 NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
373 X_LAST_UPDATE_DATE,
374 X_LAST_UPDATED_BY,
375 X_LAST_UPDATE_DATE,
376 X_LAST_UPDATED_BY,
377 X_LAST_UPDATE_LOGIN
378 );
379
380 open c;
381 fetch c into X_ROWID;
382 if (c%notfound) then
383 close c;
384 raise no_data_found;
385 end if;
386 close c;
387 After_DML (
388 p_action => 'INSERT',
389 x_rowid => X_ROWID
390 );
391
392 end INSERT_ROW;
393
394 procedure LOCK_ROW (
395 X_ROWID in VARCHAR2,
396 X_UNIT_CD in VARCHAR2,
397 X_S_RULE_CALL_CD in VARCHAR2,
398 X_RUL_SEQUENCE_NUMBER in NUMBER
399 ) AS
400 cursor c1 is select
401 RUL_SEQUENCE_NUMBER
402 from IGS_PS_UNIT_RU
403 where ROWID = X_ROWID for update nowait;
404 tlinfo c1%rowtype;
405
406 begin
407 open c1;
408 fetch c1 into tlinfo;
409 if (c1%notfound) then
410 close c1;
411 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
412 IGS_GE_MSG_STACK.ADD;
413 app_exception.raise_exception;
414 return;
415 end if;
416 close c1;
417
418 if ( (tlinfo.RUL_SEQUENCE_NUMBER = X_RUL_SEQUENCE_NUMBER)
419 ) then
420 null;
421 else
422 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
423 IGS_GE_MSG_STACK.ADD;
424 app_exception.raise_exception;
425 end if;
426 return;
427 end LOCK_ROW;
428
429 procedure UPDATE_ROW (
430 X_ROWID in VARCHAR2,
431 X_UNIT_CD in VARCHAR2,
432 X_S_RULE_CALL_CD in VARCHAR2,
433 X_RUL_SEQUENCE_NUMBER in NUMBER,
434 X_MODE in VARCHAR2 default 'R'
435 ) AS
436 X_LAST_UPDATE_DATE DATE;
437 X_LAST_UPDATED_BY NUMBER;
438 X_LAST_UPDATE_LOGIN NUMBER;
439 begin
440 X_LAST_UPDATE_DATE := SYSDATE;
441 if(X_MODE = 'I') then
442 X_LAST_UPDATED_BY := 1;
443 X_LAST_UPDATE_LOGIN := 0;
444 elsif (X_MODE = 'R') then
445 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
446 if X_LAST_UPDATED_BY is NULL then
447 X_LAST_UPDATED_BY := -1;
448 end if;
449 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
450 if X_LAST_UPDATE_LOGIN is NULL then
451 X_LAST_UPDATE_LOGIN := -1;
452 end if;
453 else
454 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
455 IGS_GE_MSG_STACK.ADD;
456 app_exception.raise_exception;
457 end if;
458
459 Before_DML(
460 p_action => 'UPDATE',
461 x_rowid => X_ROWID,
462 x_unit_cd => X_UNIT_CD,
463 x_s_rule_call_cd => X_S_RULE_CALL_CD,
464 x_rul_sequence_number => X_RUL_SEQUENCE_NUMBER,
465 x_creation_date => X_LAST_UPDATE_DATE,
466 x_created_by => X_LAST_UPDATED_BY,
467 x_last_update_date => X_LAST_UPDATE_DATE,
468 x_last_updated_by => X_LAST_UPDATED_BY,
469 x_last_update_login => X_LAST_UPDATE_LOGIN
470 );
471
472 update IGS_PS_UNIT_RU set
473 RUL_SEQUENCE_NUMBER = NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
474 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
475 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
476 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
477 where ROWID = X_ROWID
478 ;
479 if (sql%notfound) then
480 raise no_data_found;
481 end if;
482 After_DML (
483 p_action => 'UPDATE',
484 x_rowid => X_ROWID
485 );
486
487 end UPDATE_ROW;
488
489 procedure ADD_ROW (
490 X_ROWID in out NOCOPY VARCHAR2,
491 X_UNIT_CD in VARCHAR2,
492 X_S_RULE_CALL_CD in VARCHAR2,
493 X_RUL_SEQUENCE_NUMBER in NUMBER,
494 X_MODE in VARCHAR2 default 'R'
495 ) AS
496 cursor c1 is select rowid from IGS_PS_UNIT_RU
497 where UNIT_CD = X_UNIT_CD
498 and S_RULE_CALL_CD = X_S_RULE_CALL_CD
499 ;
500 begin
501 open c1;
502 fetch c1 into X_ROWID;
503 if (c1%notfound) then
504 close c1;
505 INSERT_ROW (
506 X_ROWID,
507 X_UNIT_CD,
508 X_S_RULE_CALL_CD,
509 X_RUL_SEQUENCE_NUMBER,
510 X_MODE);
511 return;
512 end if;
513 close c1;
514 UPDATE_ROW (
515 X_ROWID,
516 X_UNIT_CD,
517 X_S_RULE_CALL_CD,
518 X_RUL_SEQUENCE_NUMBER,
519 X_MODE);
520 end ADD_ROW;
521
522 procedure DELETE_ROW (
523 X_ROWID in VARCHAR2
524 ) AS
525 begin
526 Before_DML (
527 p_action => 'DELETE',
528 x_rowid => X_ROWID
529 );
530 delete from IGS_PS_UNIT_RU
531 where ROWID = X_ROWID;
532 if (sql%notfound) then
533 raise no_data_found;
534 end if;
535 After_DML (
536 p_action => 'DELETE',
537 x_rowid => X_ROWID
538 );
539 end DELETE_ROW;
540
541 end IGS_PS_UNIT_RU_PKG;