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_OR_STATUS_PKG

Source


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