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_UNT_INLV_HIST_PKG

Source


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