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_FLD_STDY_HIST_PKG

Source


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