eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_PS_UNIT_DISC_CRT_PKG

Source


1 package body IGS_PS_UNIT_DISC_CRT_PKG as
2 /* $Header: IGSPI78B.pls 115.6 2003/10/30 13:31:28 rghosh ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_PS_UNIT_DISC_CRT%RowType;
6   new_references IGS_PS_UNIT_DISC_CRT%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_unit_discont_dt_alias IN VARCHAR2 DEFAULT NULL,
12     x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
13     x_delete_ind IN VARCHAR2 DEFAULT NULL,
14     x_dflt_ind IN VARCHAR2 DEFAULT NULL,
15     x_creation_date IN DATE DEFAULT NULL,
16     x_created_by IN NUMBER DEFAULT NULL,
17     x_last_update_date IN DATE DEFAULT NULL,
18     x_last_updated_by IN NUMBER DEFAULT NULL,
19     x_last_update_login IN NUMBER DEFAULT NULL
20   ) AS
21 
22     CURSOR cur_old_ref_values IS
23       SELECT   *
24       FROM     IGS_PS_UNIT_DISC_CRT
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_discont_dt_alias := x_unit_discont_dt_alias;
46     new_references.administrative_unit_status := x_administrative_unit_status;
47     new_references.delete_ind := x_delete_ind;
48     new_references.dflt_ind := x_dflt_ind;
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 BeforeRowInsertUpdate1(
63     p_inserting IN BOOLEAN DEFAULT FALSE,
64     p_updating IN BOOLEAN DEFAULT FALSE,
65     p_deleting IN BOOLEAN DEFAULT FALSE
66     ) AS
67 	v_message_name		Varchar2(30);
68   BEGIN
69 	-- Validate that inserts/updates are allowed
70 	IF  p_inserting OR p_updating THEN
71 	    IF	IGS_EN_VAL_UDDC.ENRP_VAL_AUS_CLOSED(new_references.administrative_unit_status
72 						,v_message_name) = FALSE THEN
73 			Fnd_Message.Set_Name('IGS',v_message_name);
74       IGS_GE_MSG_STACK.ADD;
75 			App_Exception.Raise_Exception;
76 	    END IF;
77 	    IF	IGS_EN_VAL_UDDC.ENRP_VAL_AUS_DISCONT(new_references.administrative_unit_status,
78 					v_message_name) = FALSE THEN
79 		Fnd_Message.Set_Name('IGS',v_message_name);
80       IGS_GE_MSG_STACK.ADD;
81 		App_Exception.Raise_Exception;
82 	    END IF;
83 	    IF	IGS_EN_VAL_UDDC.ENRP_VAL_TEACHING_DA(new_references.unit_discont_dt_alias,
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 	    IF	IGS_EN_VAL_UDDC.ENRP_VAL_UDDC_FIELDS(new_references.administrative_unit_status						,new_references.delete_ind
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;
94 	    END IF;
95 	END IF;
96 
97 
98   END BeforeRowInsertUpdate1;
99 
100 PROCEDURE Check_Constraints(
101 				Column_Name 	IN	VARCHAR2	DEFAULT NULL,
102 				Column_Value 	IN	VARCHAR2	DEFAULT NULL)
103 AS
104 BEGIN
105 
106 	IF Column_Name IS NULL Then
107 		NULL;
108 	ELSIF Upper(Column_Name)='ADMINISTRATIVE_UNIT_STATUS' Then
109 		New_References.administrative_unit_status := Column_Value;
110 	ELSIF Upper(Column_Name)='UNIT_DISCONT_DT_ALIAS' Then
111 		New_References.unit_discont_dt_alias := Column_Value;
112 	ELSIF Upper(Column_Name)='DFLT_IND' Then
113 		New_References.dflt_ind := Column_Value;
114 	ELSIF Upper(Column_Name)='DELETE_IND' Then
115 		New_References.delete_ind := Column_Value;
116 	END IF;
117 
118 	IF Upper(Column_Name)='ADMINISTRATIVE_UNIT_STATUS' OR Column_Name IS NULL Then
119 		IF New_References.administrative_unit_status <> UPPER(New_References.administrative_unit_status) Then
120 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
121       IGS_GE_MSG_STACK.ADD;
122 			        App_Exception.Raise_Exception;
123 		END IF;
124 	END IF;
125 
126 	IF Upper(Column_Name)='UNIT_DISCONT_DT_ALIAS' OR Column_Name IS NULL Then
127 		IF New_References.unit_discont_dt_alias <> UPPER(New_References.unit_discont_dt_alias) Then
128 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
129       IGS_GE_MSG_STACK.ADD;
130 			        App_Exception.Raise_Exception;
131 		END IF;
132 	END IF;
133 
134 	IF Upper(Column_Name)='DFLT_IND' OR Column_Name IS NULL Then
135 		IF New_References.dflt_ind NOT IN ('Y','N') Then
136 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
137       IGS_GE_MSG_STACK.ADD;
138 			        App_Exception.Raise_Exception;
139 		END IF;
140 	END IF;
141 
142 	IF Upper(Column_Name)='DELETE_IND' OR Column_Name IS NULL Then
143 		IF New_References.delete_ind NOT IN ('Y','N') Then
144 			        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
145       IGS_GE_MSG_STACK.ADD;
146 			        App_Exception.Raise_Exception;
147 		END IF;
148 	END IF;
149 
150 END Check_Constraints;
151 
152   PROCEDURE Check_Parent_Existance AS
153   BEGIN
154 
155     IF (((old_references.administrative_unit_status = new_references.administrative_unit_status)) OR
156         ((new_references.administrative_unit_status IS NULL))) THEN
157       NULL;
158     ELSE
159       IF NOT IGS_AD_ADM_UNIT_STAT_PKG.Get_PK_For_Validation (new_references.administrative_unit_status , 'N') THEN
160 				  Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
161       IGS_GE_MSG_STACK.ADD;
162 			        App_Exception.Raise_Exception;
163 	END IF;
164 
165     END IF;
166 
167     IF (((old_references.unit_discont_dt_alias = new_references.unit_discont_dt_alias)) OR
168         ((new_references.unit_discont_dt_alias IS NULL))) THEN
169       NULL;
170     ELSE
171       IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (new_references.unit_discont_dt_alias) THEN
172 				  Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
173       IGS_GE_MSG_STACK.ADD;
174 			        App_Exception.Raise_Exception;
175 	END IF;
176 
177     END IF;
178 
179   END Check_Parent_Existance;
180 
181   FUNCTION Get_PK_For_Validation (
182     x_unit_discont_dt_alias IN VARCHAR2
183     ) RETURN BOOLEAN AS
184 
185     CURSOR cur_rowid IS
186       SELECT   rowid
187       FROM     IGS_PS_UNIT_DISC_CRT
188       WHERE    unit_discont_dt_alias = x_unit_discont_dt_alias
189       FOR UPDATE NOWAIT;
190 
191     lv_rowid cur_rowid%RowType;
192 
193   BEGIN
194 
195     Open cur_rowid;
196     Fetch cur_rowid INTO lv_rowid;
197    IF (cur_rowid%FOUND) THEN
198 	Close cur_rowid;
199       Return(TRUE);
200     ELSE
201 	Close cur_rowid;
202       Return(FALSE);
203     END IF;
204   END Get_PK_For_Validation;
205 
206   PROCEDURE GET_FK_IGS_CA_DA (
207     x_dt_alias IN VARCHAR2
208     ) AS
209 
210     CURSOR cur_rowid IS
211       SELECT   rowid
212       FROM     IGS_PS_UNIT_DISC_CRT
213       WHERE    unit_discont_dt_alias = x_dt_alias ;
214 
215     lv_rowid cur_rowid%RowType;
216 
217   BEGIN
218 
219     Open cur_rowid;
220     Fetch cur_rowid INTO lv_rowid;
221     IF (cur_rowid%FOUND) THEN
222       Close cur_rowid;
223       Fnd_Message.Set_Name ('IGS', 'IGS_PS_UDDC_DA_FK');
224       IGS_GE_MSG_STACK.ADD;
225       App_Exception.Raise_Exception;
226       Return;
227     END IF;
228     Close cur_rowid;
229 
230   END GET_FK_IGS_CA_DA;
231 
232   PROCEDURE Before_DML (
233     p_action IN VARCHAR2,
234     x_rowid IN VARCHAR2 DEFAULT NULL,
235     x_unit_discont_dt_alias IN VARCHAR2 DEFAULT NULL,
236     x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
237     x_delete_ind IN VARCHAR2 DEFAULT NULL,
238     x_dflt_ind IN VARCHAR2 DEFAULT NULL,
239     x_creation_date IN DATE DEFAULT NULL,
240     x_created_by IN NUMBER DEFAULT NULL,
241     x_last_update_date IN DATE DEFAULT NULL,
242     x_last_updated_by IN NUMBER DEFAULT NULL,
243     x_last_update_login IN NUMBER DEFAULT NULL
244   ) AS
245   BEGIN
246 
247     Set_Column_Values (
248       p_action,
249       x_rowid,
250       x_unit_discont_dt_alias,
251       x_administrative_unit_status,
252       x_delete_ind,
253       x_dflt_ind,
254       x_creation_date,
255       x_created_by,
256       x_last_update_date,
257       x_last_updated_by,
258       x_last_update_login
259     );
260 
261     IF (p_action = 'INSERT') THEN
262       -- Call all the procedures related to Before Insert.
263       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
264 	 IF Get_PK_For_Validation (New_References.unit_discont_dt_alias) THEN
265 			Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
266       IGS_GE_MSG_STACK.ADD;
267 		      App_Exception.Raise_Exception;
268 	   END IF;
269 	   Check_Constraints;
270          Check_Parent_Existance;
271     ELSIF (p_action = 'UPDATE') THEN
272       -- Call all the procedures related to Before Update.
273       BeforeRowInsertUpdate1 ( p_updating => TRUE );
274    Check_Constraints;
275       Check_Parent_Existance;
276 
277 ELSIF (p_action = 'VALIDATE_INSERT') THEN
278 	 IF Get_PK_For_Validation (New_References.unit_discont_dt_alias) THEN
279 			Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
280       IGS_GE_MSG_STACK.ADD;
281 		      App_Exception.Raise_Exception;
282 	   END IF;
283 	   Check_Constraints;
284    ELSIF (p_action = 'VALIDATE_UPDATE') THEN
285 	   Check_Constraints;
286 
287     END IF;
288 
289   END Before_DML;
290 
291   PROCEDURE After_DML (
292     p_action IN VARCHAR2,
293     x_rowid IN VARCHAR2
294   ) AS
295   BEGIN
296 
297     l_rowid := x_rowid;
298 
299 
300   END After_DML;
301 
302 
303 procedure INSERT_ROW (
304   X_ROWID in out NOCOPY VARCHAR2,
305   X_UNIT_DISCONT_DT_ALIAS in VARCHAR2,
306   X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
307   X_DELETE_IND in VARCHAR2,
308   X_DFLT_IND in VARCHAR2,
309   X_MODE in VARCHAR2 default 'R'
310   ) AS
311     cursor C is select ROWID from IGS_PS_UNIT_DISC_CRT
312       where UNIT_DISCONT_DT_ALIAS = X_UNIT_DISCONT_DT_ALIAS;
313     X_LAST_UPDATE_DATE DATE;
314     X_LAST_UPDATED_BY NUMBER;
315     X_LAST_UPDATE_LOGIN NUMBER;
316 begin
317   X_LAST_UPDATE_DATE := SYSDATE;
318   if(X_MODE = 'I') then
319     X_LAST_UPDATED_BY := 1;
320     X_LAST_UPDATE_LOGIN := 0;
321   elsif (X_MODE = 'R') then
322     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
323     if X_LAST_UPDATED_BY is NULL then
324       X_LAST_UPDATED_BY := -1;
325     end if;
326     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
327     if X_LAST_UPDATE_LOGIN is NULL then
328       X_LAST_UPDATE_LOGIN := -1;
329     end if;
330   else
331     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
332       IGS_GE_MSG_STACK.ADD;
333     app_exception.raise_exception;
334   end if;
335 
336   Before_DML(
337   p_action => 'INSERT',
338   x_rowid =>  X_ROWID,
339   x_unit_discont_dt_alias => X_UNIT_DISCONT_DT_ALIAS,
340   x_administrative_unit_status => X_ADMINISTRATIVE_UNIT_STATUS,
341   x_delete_ind => NVL(X_DELETE_IND,'N'),
342   x_dflt_ind => NVL(X_DFLT_IND,'Y'),
343   x_creation_date => X_LAST_UPDATE_DATE,
344   x_created_by => X_LAST_UPDATED_BY,
345   x_last_update_date => X_LAST_UPDATE_DATE,
346   x_last_updated_by => X_LAST_UPDATED_BY,
347   x_last_update_login => X_LAST_UPDATE_LOGIN
348   );
349 
350   insert into IGS_PS_UNIT_DISC_CRT (
351     UNIT_DISCONT_DT_ALIAS,
352     ADMINISTRATIVE_UNIT_STATUS,
353     DELETE_IND,
354     DFLT_IND,
355     CREATION_DATE,
356     CREATED_BY,
357     LAST_UPDATE_DATE,
358     LAST_UPDATED_BY,
359     LAST_UPDATE_LOGIN
360   ) values (
361     NEW_REFERENCES.UNIT_DISCONT_DT_ALIAS,
362     NEW_REFERENCES.ADMINISTRATIVE_UNIT_STATUS,
363     NEW_REFERENCES.DELETE_IND,
364     NEW_REFERENCES.DFLT_IND,
365     X_LAST_UPDATE_DATE,
366     X_LAST_UPDATED_BY,
367     X_LAST_UPDATE_DATE,
368     X_LAST_UPDATED_BY,
369     X_LAST_UPDATE_LOGIN
370   );
371 
372   open c;
373   fetch c into X_ROWID;
374   if (c%notfound) then
375     close c;
376     raise no_data_found;
377   end if;
378   close c;
379   After_DML (
380      p_action => 'INSERT',
381      x_rowid => X_ROWID
382     );
383 
384 end INSERT_ROW;
385 
386 procedure LOCK_ROW (
387   X_ROWID IN VARCHAR2,
388   X_UNIT_DISCONT_DT_ALIAS in VARCHAR2,
389   X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
390   X_DELETE_IND in VARCHAR2,
391   X_DFLT_IND in VARCHAR2
392 ) AS
393   cursor c1 is select
394       ADMINISTRATIVE_UNIT_STATUS,
395       DELETE_IND,
396       DFLT_IND
397     from IGS_PS_UNIT_DISC_CRT
398     where ROWID = X_ROWID
399     for update nowait;
400   tlinfo c1%rowtype;
401 
402 begin
403   open c1;
404   fetch c1 into tlinfo;
405   if (c1%notfound) then
406     close c1;
407     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
408       IGS_GE_MSG_STACK.ADD;
409     app_exception.raise_exception;
410     return;
411   end if;
412   close c1;
413 
414       if ( ((tlinfo.ADMINISTRATIVE_UNIT_STATUS = X_ADMINISTRATIVE_UNIT_STATUS)
415            OR ((tlinfo.ADMINISTRATIVE_UNIT_STATUS is null)
416                AND (X_ADMINISTRATIVE_UNIT_STATUS is null)))
417       AND (tlinfo.DELETE_IND = X_DELETE_IND)
418       AND (tlinfo.DFLT_IND = X_DFLT_IND)
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_DISCONT_DT_ALIAS in VARCHAR2,
432   X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
433   X_DELETE_IND in VARCHAR2,
434   X_DFLT_IND in VARCHAR2,
435   X_MODE in VARCHAR2 default 'R'
436   ) AS
437     X_LAST_UPDATE_DATE DATE;
438     X_LAST_UPDATED_BY NUMBER;
439     X_LAST_UPDATE_LOGIN NUMBER;
440 begin
441   X_LAST_UPDATE_DATE := SYSDATE;
442   if(X_MODE = 'I') then
443     X_LAST_UPDATED_BY := 1;
444     X_LAST_UPDATE_LOGIN := 0;
445   elsif (X_MODE = 'R') then
446     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
447     if X_LAST_UPDATED_BY is NULL then
448       X_LAST_UPDATED_BY := -1;
449     end if;
450     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
451     if X_LAST_UPDATE_LOGIN is NULL then
452       X_LAST_UPDATE_LOGIN := -1;
453     end if;
454   else
455     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
456       IGS_GE_MSG_STACK.ADD;
457     app_exception.raise_exception;
458   end if;
459 
460   Before_DML(
461   p_action => 'UPDATE',
462   x_rowid =>  X_ROWID,
463   x_unit_discont_dt_alias => X_UNIT_DISCONT_DT_ALIAS,
464   x_administrative_unit_status => X_ADMINISTRATIVE_UNIT_STATUS,
465   x_delete_ind => X_DELETE_IND,
466   x_dflt_ind => X_DFLT_IND,
467   x_creation_date => X_LAST_UPDATE_DATE,
468   x_created_by => X_LAST_UPDATED_BY,
469   x_last_update_date => X_LAST_UPDATE_DATE,
470   x_last_updated_by => X_LAST_UPDATED_BY,
471   x_last_update_login => X_LAST_UPDATE_LOGIN
472   );
473   update IGS_PS_UNIT_DISC_CRT set
474     ADMINISTRATIVE_UNIT_STATUS = NEW_REFERENCES.ADMINISTRATIVE_UNIT_STATUS,
475     DELETE_IND = NEW_REFERENCES.DELETE_IND,
476     DFLT_IND = NEW_REFERENCES.DFLT_IND,
477     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
478     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
479     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
480     where ROWID = X_ROWID
481   ;
482   if (sql%notfound) then
483     raise no_data_found;
484   end if;
485   After_DML (
486      p_action => 'UPDATE',
487      x_rowid => X_ROWID
488     );
489 
490 end UPDATE_ROW;
491 
492 procedure ADD_ROW (
493   X_ROWID in out NOCOPY VARCHAR2,
494   X_UNIT_DISCONT_DT_ALIAS in VARCHAR2,
495   X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
496   X_DELETE_IND in VARCHAR2,
497   X_DFLT_IND in VARCHAR2,
498   X_MODE in VARCHAR2 default 'R'
499   ) AS
500   cursor c1 is select rowid from IGS_PS_UNIT_DISC_CRT
501      where UNIT_DISCONT_DT_ALIAS = X_UNIT_DISCONT_DT_ALIAS
502   ;
503 begin
504   open c1;
505   fetch c1 into X_ROWID;
506   if (c1%notfound) then
507     close c1;
508     INSERT_ROW (
509      X_ROWID,
510      X_UNIT_DISCONT_DT_ALIAS,
511      X_ADMINISTRATIVE_UNIT_STATUS,
512      X_DELETE_IND,
513      X_DFLT_IND,
514      X_MODE);
515     return;
516   end if;
517   close c1;
518   UPDATE_ROW (
519    X_ROWID,
520    X_UNIT_DISCONT_DT_ALIAS,
521    X_ADMINISTRATIVE_UNIT_STATUS,
522    X_DELETE_IND,
523    X_DFLT_IND,
524    X_MODE);
525 end ADD_ROW;
526 
527 procedure DELETE_ROW (
528   X_ROWID in VARCHAR2
529 ) AS
530 begin
531   Before_DML (
532      p_action => 'DELETE',
533      x_rowid => X_ROWID
534     );
535   delete from IGS_PS_UNIT_DISC_CRT
536     where ROWID = X_ROWID;
537   if (sql%notfound) then
538     raise no_data_found;
539   end if;
540   After_DML (
541      p_action => 'DELETE',
542      x_rowid => X_ROWID
543     );
544 
545 end DELETE_ROW;
546 
547 end IGS_PS_UNIT_DISC_CRT_PKG;