DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_PS_UNIT_DSCP_PKG

Source


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