[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_VER_RU_PKG
Source
1 package body IGS_PS_UNIT_VER_RU_PKG as
2 /* $Header: IGSPI95B.pls 115.6 2002/11/29 02:43:12 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_UNIT_VER_RU%RowType;
5 new_references IGS_PS_UNIT_VER_RU%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_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_VER_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.version_number := x_version_number;
46 new_references.s_rule_call_cd := x_s_rule_call_cd;
47 new_references.rul_sequence_number := x_rul_sequence_number;
48 IF (p_action = 'UPDATE') THEN
49 new_references.creation_date := old_references.creation_date;
50 new_references.created_by := old_references.created_by;
51 ELSE
52 new_references.creation_date := x_creation_date;
53 new_references.created_by := x_created_by;
54 END IF;
55 new_references.last_update_date := x_last_update_date;
56 new_references.last_updated_by := x_last_updated_by;
57 new_references.last_update_login := x_last_update_login;
58
59 END Set_Column_Values;
60
61 PROCEDURE BeforeRowInsertUpdateDelete1(
62 p_inserting IN BOOLEAN DEFAULT FALSE,
63 p_updating IN BOOLEAN DEFAULT FALSE,
64 p_deleting IN BOOLEAN DEFAULT FALSE
65 ) AS
66 v_message_name Varchar2(30);
67 BEGIN
68 -- Validate the insert/update/delete
69 IF p_inserting OR p_updating THEN
70 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl(
71 new_references.unit_cd,
72 new_references.version_number,
73 v_message_name) = FALSE THEN
74 Fnd_Message.Set_Name('IGS',v_message_name);
75 IGS_GE_MSG_STACK.ADD;
76 App_Exception.Raise_Exception;
77 END IF;
78 ELSE
79 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl(
80 old_references.unit_cd,
81 old_references.version_number,
82 v_message_name) = FALSE THEN
83 Fnd_Message.Set_Name('IGS',v_message_name);
84 IGS_GE_MSG_STACK.ADD;
85 App_Exception.Raise_Exception;
86 END IF;
87 END IF;
88 -- Validate IGS_PS_COURSE Stage IGS_RU_RULE
89 IF p_inserting OR p_updating THEN
90 /*
91 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
92 || Changed the reference of "IGS_PS_VAL_UVR.RULP_VAL_RUL_SRC" to program unit "IGS_FI_VAL_FDFR.RULP_VAL_RUL_SRC". -- kdande
93 */
94 IF IGS_FI_VAL_FDFR.rulp_val_rul_src(
95 new_references.s_rule_call_cd,
96 'UNIT',
97 new_references.rul_sequence_number,
98 v_message_name) = FALSE THEN
99 Fnd_Message.Set_Name('IGS',v_message_name);
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END IF;
103 END IF;
104
105 END BeforeRowInsertUpdateDelete1;
106
107 PROCEDURE Check_Constraints(
108 Column_Name IN VARCHAR2 DEFAULT NULL,
109 Column_Value IN VARCHAR2 DEFAULT NULL)
110 AS
111 BEGIN
112
113 IF Column_Name IS NULL Then
114 NULL;
115 ELSIF Upper(Column_Name)='S_RULE_CALL_CD' Then
116 New_References.s_rule_call_cd := Column_Value;
117 ELSIF Upper(Column_Name)='UNIT_CD' Then
118 New_References.Unit_Cd := Column_Value;
119 END IF;
120
121 IF Upper(Column_Name)='S_RULE_CALL_CD' OR Column_Name IS NULL Then
122 IF New_References.s_rule_call_cd <> UPPER(New_References.s_rule_call_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 IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
130 IF New_References.Unit_Cd <> UPPER(New_References.Unit_CD) Then
131 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136
137 END Check_Constraints;
138
139 PROCEDURE Check_Parent_Existance AS
140 BEGIN
141
142 IF (((old_references.rul_sequence_number = new_references.rul_sequence_number)) OR
143 ((new_references.rul_sequence_number IS NULL))) THEN
144 NULL;
145 ELSE
146 IF NOT IGS_RU_RULE_PKG.Get_PK_For_Validation (
147 new_references.rul_sequence_number) THEN
148 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152
153
154 END IF;
155
156 IF (((old_references.s_rule_call_cd = new_references.s_rule_call_cd)) OR
157 ((new_references.s_rule_call_cd IS NULL))) THEN
158 NULL;
159 ELSE
160 IF NOT IGS_RU_CALL_PKG.Get_PK_For_Validation (
161 new_references.s_rule_call_cd) THEN
162 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
163 IGS_GE_MSG_STACK.ADD;
164 App_Exception.Raise_Exception;
165 END IF;
166
167
168 END IF;
169
170 IF (((old_references.unit_cd = new_references.unit_cd) AND
171 (old_references.version_number = new_references.version_number)) OR
172 ((new_references.unit_cd IS NULL) OR
173 (new_references.version_number IS NULL))) THEN
174 NULL;
175 ELSE
176 IF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
177 new_references.unit_cd,
178 new_references.version_number) THEN
179 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END IF;
183
184
185 END IF;
186
187 END Check_Parent_Existance;
188
189 FUNCTION Get_PK_For_Validation (
190 x_unit_cd IN VARCHAR2,
191 x_version_number IN NUMBER,
192 x_s_rule_call_cd IN VARCHAR2
193 ) RETURN BOOLEAN AS
194
195 CURSOR cur_rowid IS
196 SELECT rowid
197 FROM IGS_PS_UNIT_VER_RU
198 WHERE unit_cd = x_unit_cd
199 AND version_number = x_version_number
200 AND s_rule_call_cd = x_s_rule_call_cd
201 FOR UPDATE NOWAIT;
202
203 lv_rowid cur_rowid%RowType;
204
205 BEGIN
206
207 Open cur_rowid;
208 Fetch cur_rowid INTO lv_rowid;
209 IF (cur_rowid%FOUND) THEN
210 Close cur_rowid;
211 Return(TRUE);
212 ELSE
213 Close cur_rowid;
214 Return(FALSE);
215 END IF;
216
217 END Get_PK_For_Validation;
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_VER_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_UVR_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 GET_FK_IGS_RU_CALL (
246 x_s_rule_call_cd IN VARCHAR2
247 ) AS
248
249 CURSOR cur_rowid IS
250 SELECT rowid
251 FROM IGS_PS_UNIT_VER_RU
252 WHERE s_rule_call_cd = x_s_rule_call_cd ;
253
254 lv_rowid cur_rowid%RowType;
255
256 BEGIN
257
258 Open cur_rowid;
259 Fetch cur_rowid INTO lv_rowid;
260 IF (cur_rowid%FOUND) THEN
261 Close cur_rowid;
262 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UVR_SRC_FK');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 Return;
266 END IF;
267 Close cur_rowid;
268
269 END GET_FK_IGS_RU_CALL;
270
271 PROCEDURE GET_FK_IGS_PS_UNIT_VER (
272 x_unit_cd IN VARCHAR2,
273 x_version_number IN NUMBER
274 ) AS
275
276 CURSOR cur_rowid IS
277 SELECT rowid
278 FROM IGS_PS_UNIT_VER_RU
279 WHERE unit_cd = x_unit_cd
280 AND version_number = x_version_number ;
281
282 lv_rowid cur_rowid%RowType;
283
284 BEGIN
285
286 Open cur_rowid;
287 Fetch cur_rowid INTO lv_rowid;
288 IF (cur_rowid%FOUND) THEN
289 Close cur_rowid;
290 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UVR_UV_FK');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 Return;
294 END IF;
295 Close cur_rowid;
296
297 END GET_FK_IGS_PS_UNIT_VER;
298
299 PROCEDURE Before_DML (
300 p_action IN VARCHAR2,
301 x_rowid IN VARCHAR2 DEFAULT NULL,
302 x_unit_cd IN VARCHAR2 DEFAULT NULL,
303 x_version_number IN NUMBER DEFAULT NULL,
304 x_s_rule_call_cd IN VARCHAR2 DEFAULT NULL,
305 x_rul_sequence_number IN NUMBER DEFAULT NULL,
306 x_creation_date IN DATE DEFAULT NULL,
307 x_created_by IN NUMBER DEFAULT NULL,
308 x_last_update_date IN DATE DEFAULT NULL,
309 x_last_updated_by IN NUMBER DEFAULT NULL,
310 x_last_update_login IN NUMBER DEFAULT NULL
311 ) AS
312 BEGIN
313
314 Set_Column_Values (
315 p_action,
316 x_rowid,
317 x_unit_cd,
318 x_version_number,
319 x_s_rule_call_cd,
320 x_rul_sequence_number,
321 x_creation_date,
322 x_created_by,
323 x_last_update_date,
324 x_last_updated_by,
325 x_last_update_login
326 );
327
328 IF (p_action = 'INSERT') THEN
329 -- Call all the procedures related to Before Insert.
330 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
331 IF Get_PK_For_Validation (New_References.unit_cd,
332 New_References.version_number,
333 New_References.s_rule_call_cd) THEN
334 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 END IF;
338 Check_Constraints;
339 Check_Parent_Existance;
340 ELSIF (p_action = 'UPDATE') THEN
341 -- Call all the procedures related to Before Update.
342 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
343 Check_Constraints;
344 Check_Parent_Existance;
345 ELSIF (p_action = 'DELETE') THEN
346 -- Call all the procedures related to Before Delete.
347 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
348 ELSIF (p_action = 'VALIDATE_INSERT') THEN
349 IF Get_PK_For_Validation (New_References.unit_cd,
350 New_References.version_number,
351 New_References.s_rule_call_cd) THEN
352 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
353 IGS_GE_MSG_STACK.ADD;
354 App_Exception.Raise_Exception;
355 END IF;
356 Check_Constraints;
357 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
358 Check_Constraints;
359
360 END IF;
361
362 END Before_DML;
363
364 PROCEDURE After_DML (
365 p_action IN VARCHAR2,
366 x_rowid IN VARCHAR2
367 ) AS
368 BEGIN
369
370 l_rowid := x_rowid;
371
372
373 END After_DML;
374
375 procedure INSERT_ROW (
376 X_ROWID in out NOCOPY VARCHAR2,
377 X_UNIT_CD in VARCHAR2,
378 X_VERSION_NUMBER in NUMBER,
379 X_S_RULE_CALL_CD in VARCHAR2,
380 X_RUL_SEQUENCE_NUMBER in NUMBER,
381 X_MODE in VARCHAR2 default 'R'
382 ) AS
383 cursor C is select ROWID from IGS_PS_UNIT_VER_RU
384 where UNIT_CD = X_UNIT_CD
385 and VERSION_NUMBER = X_VERSION_NUMBER
386 and S_RULE_CALL_CD = X_S_RULE_CALL_CD;
387 X_LAST_UPDATE_DATE DATE;
388 X_LAST_UPDATED_BY NUMBER;
389 X_LAST_UPDATE_LOGIN NUMBER;
390 begin
391 X_LAST_UPDATE_DATE := SYSDATE;
392 if(X_MODE = 'I') then
393 X_LAST_UPDATED_BY := 1;
394 X_LAST_UPDATE_LOGIN := 0;
395 elsif (X_MODE = 'R') then
396 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
397 if X_LAST_UPDATED_BY is NULL then
398 X_LAST_UPDATED_BY := -1;
399 end if;
400 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
401 if X_LAST_UPDATE_LOGIN is NULL then
402 X_LAST_UPDATE_LOGIN := -1;
403 end if;
404 else
405 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
406 IGS_GE_MSG_STACK.ADD;
407 app_exception.raise_exception;
408 end if;
409
410 Before_DML(
411 p_action => 'INSERT',
412 x_rowid => X_ROWID,
413 x_unit_cd => X_UNIT_CD,
414 x_version_number => X_VERSION_NUMBER,
415 x_s_rule_call_cd => X_S_RULE_CALL_CD,
416 x_rul_sequence_number => X_RUL_SEQUENCE_NUMBER,
417 x_creation_date => X_LAST_UPDATE_DATE,
418 x_created_by => X_LAST_UPDATED_BY,
419 x_last_update_date => X_LAST_UPDATE_DATE,
420 x_last_updated_by => X_LAST_UPDATED_BY,
421 x_last_update_login => X_LAST_UPDATE_LOGIN
422 );
423
424 insert into IGS_PS_UNIT_VER_RU (
425 UNIT_CD,
426 VERSION_NUMBER,
427 S_RULE_CALL_CD,
428 RUL_SEQUENCE_NUMBER,
429 CREATION_DATE,
430 CREATED_BY,
431 LAST_UPDATE_DATE,
432 LAST_UPDATED_BY,
433 LAST_UPDATE_LOGIN
434 ) values (
435 NEW_REFERENCES.UNIT_CD,
436 NEW_REFERENCES.VERSION_NUMBER,
437 NEW_REFERENCES.S_RULE_CALL_CD,
438 NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
439 X_LAST_UPDATE_DATE,
440 X_LAST_UPDATED_BY,
441 X_LAST_UPDATE_DATE,
442 X_LAST_UPDATED_BY,
443 X_LAST_UPDATE_LOGIN
444 );
445
446 open c;
447 fetch c into X_ROWID;
448 if (c%notfound) then
449 close c;
450 raise no_data_found;
451 end if;
452 close c;
453 After_DML (
454 p_action => 'INSERT',
455 x_rowid => X_ROWID
456 );
457
458 end INSERT_ROW;
459
460 procedure LOCK_ROW (
461 X_ROWID in VARCHAR2,
462 X_UNIT_CD in VARCHAR2,
463 X_VERSION_NUMBER in NUMBER,
464 X_S_RULE_CALL_CD in VARCHAR2,
465 X_RUL_SEQUENCE_NUMBER in NUMBER
466 ) AS
467 cursor c1 is select
468 RUL_SEQUENCE_NUMBER
469 from IGS_PS_UNIT_VER_RU
470 where ROWID = X_ROWID for update nowait;
471 tlinfo c1%rowtype;
472
473 begin
474 open c1;
475 fetch c1 into tlinfo;
476 if (c1%notfound) then
477 close c1;
478 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
479 IGS_GE_MSG_STACK.ADD;
480 app_exception.raise_exception;
481 return;
482 end if;
483 close c1;
484
485 if ( (tlinfo.RUL_SEQUENCE_NUMBER = X_RUL_SEQUENCE_NUMBER)
486 ) then
487 null;
488 else
489 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
490 IGS_GE_MSG_STACK.ADD;
491 app_exception.raise_exception;
492 end if;
493 return;
494 end LOCK_ROW;
495
496 procedure UPDATE_ROW (
497 X_ROWID in VARCHAR2,
498 X_UNIT_CD in VARCHAR2,
499 X_VERSION_NUMBER in NUMBER,
500 X_S_RULE_CALL_CD in VARCHAR2,
501 X_RUL_SEQUENCE_NUMBER in NUMBER,
502 X_MODE in VARCHAR2 default 'R'
503 ) AS
504 X_LAST_UPDATE_DATE DATE;
505 X_LAST_UPDATED_BY NUMBER;
506 X_LAST_UPDATE_LOGIN NUMBER;
507 begin
508 X_LAST_UPDATE_DATE := SYSDATE;
509 if(X_MODE = 'I') then
510 X_LAST_UPDATED_BY := 1;
511 X_LAST_UPDATE_LOGIN := 0;
512 elsif (X_MODE = 'R') then
513 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
514 if X_LAST_UPDATED_BY is NULL then
515 X_LAST_UPDATED_BY := -1;
516 end if;
517 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
518 if X_LAST_UPDATE_LOGIN is NULL then
519 X_LAST_UPDATE_LOGIN := -1;
520 end if;
521 else
522 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
523 IGS_GE_MSG_STACK.ADD;
524 app_exception.raise_exception;
525 end if;
526
527 Before_DML(
528 p_action => 'UPDATE',
529 x_rowid => X_ROWID,
530 x_unit_cd => X_UNIT_CD,
531 x_version_number => X_VERSION_NUMBER,
532 x_s_rule_call_cd => X_S_RULE_CALL_CD,
533 x_rul_sequence_number => X_RUL_SEQUENCE_NUMBER,
534 x_creation_date => X_LAST_UPDATE_DATE,
535 x_created_by => X_LAST_UPDATED_BY,
536 x_last_update_date => X_LAST_UPDATE_DATE,
537 x_last_updated_by => X_LAST_UPDATED_BY,
538 x_last_update_login => X_LAST_UPDATE_LOGIN
539 );
540 update IGS_PS_UNIT_VER_RU set
541 RUL_SEQUENCE_NUMBER = NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
542 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
543 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
544 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
545 where ROWID = X_ROWID ;
546 if (sql%notfound) then
547 raise no_data_found;
548 end if;
549 After_DML (
550 p_action => 'UPDATE',
551 x_rowid => X_ROWID
552 );
553 end UPDATE_ROW;
554
555 procedure ADD_ROW (
556 X_ROWID in out NOCOPY VARCHAR2,
557 X_UNIT_CD in VARCHAR2,
558 X_VERSION_NUMBER in NUMBER,
559 X_S_RULE_CALL_CD in VARCHAR2,
560 X_RUL_SEQUENCE_NUMBER in NUMBER,
561 X_MODE in VARCHAR2 default 'R'
562 ) AS
563 cursor c1 is select rowid from IGS_PS_UNIT_VER_RU
564 where UNIT_CD = X_UNIT_CD
565 and VERSION_NUMBER = X_VERSION_NUMBER
566 and S_RULE_CALL_CD = X_S_RULE_CALL_CD
567 ;
568 begin
569 open c1;
570 fetch c1 into X_ROWID;
571 if (c1%notfound) then
572 close c1;
573 INSERT_ROW (
574 X_ROWID,
575 X_UNIT_CD,
576 X_VERSION_NUMBER,
577 X_S_RULE_CALL_CD,
578 X_RUL_SEQUENCE_NUMBER,
579 X_MODE);
580 return;
581 end if;
582 close c1;
583 UPDATE_ROW (
584 X_ROWID,
585 X_UNIT_CD,
586 X_VERSION_NUMBER,
587 X_S_RULE_CALL_CD,
588 X_RUL_SEQUENCE_NUMBER,
589 X_MODE);
590 end ADD_ROW;
591
592 procedure DELETE_ROW (
593 X_ROWID in VARCHAR2
594 ) AS
595 begin
596 Before_DML (
597 p_action => 'DELETE',
598 x_rowid => X_ROWID
599 );
600 delete from IGS_PS_UNIT_VER_RU
601 where ROWID = X_ROWID;
602 if (sql%notfound) then
603 raise no_data_found;
604 end if;
605 After_DML (
606 p_action => 'DELETE',
607 x_rowid => X_ROWID
608 );
609 end DELETE_ROW;
610
611 end IGS_PS_UNIT_VER_RU_PKG;