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_FI_FUND_SRC_HIST_PKG

Source


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