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_NOTE_TYPE_PKG

Source


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