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_GE_NOTE_PKG

Source


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