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_PR_MS_STAT_PKG

Source


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