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_ANL_LOAD_U_LN_PKG

Source


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