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_AD_PRCS_CAT_LTR_PKG

Source


1 package body IGS_AD_PRCS_CAT_LTR_PKG AS
2 /* $Header: IGSAI36B.pls 115.6 2003/10/30 13:20:05 rghosh ship $*/
3   l_rowid VARCHAR2(25);
4   old_references IGS_AD_PRCS_CAT_LTR%RowType;
5   new_references IGS_AD_PRCS_CAT_LTR%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10     x_admission_cat IN VARCHAR2 DEFAULT NULL,
11     x_s_admission_process_type IN VARCHAR2 DEFAULT NULL,
12     x_correspondence_type IN VARCHAR2 DEFAULT NULL,
13     x_letter_reference_number IN NUMBER 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_AD_PRCS_CAT_LTR
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.admission_cat := x_admission_cat;
45     new_references.s_admission_process_type := x_s_admission_process_type;
46     new_references.correspondence_type := x_correspondence_type;
47     new_references.letter_reference_number := x_letter_reference_number;
48     IF (p_action = 'UPDATE') THEN
49       new_references.creation_date := old_references.creation_date;
50       new_references.created_by := old_references.created_by;
51     ELSE
52       new_references.creation_date := x_creation_date;
53       new_references.created_by := x_created_by;
54     END IF;
55     new_references.last_update_date := x_last_update_date;
56     new_references.last_updated_by := x_last_updated_by;
57     new_references.last_update_login := x_last_update_login;
58 
59   END Set_Column_Values;
60 
61   PROCEDURE BeforeRowInsertUpdate1(
62     p_inserting IN BOOLEAN DEFAULT FALSE,
63     p_updating IN BOOLEAN DEFAULT FALSE,
64     p_deleting IN BOOLEAN DEFAULT FALSE
65     ) AS
66 	v_message_name	VARCHAR2(30);
67   BEGIN
68 	-- Validate the System Letter
69 	IF p_inserting
70 	OR (old_references.correspondence_type <> new_references.correspondence_type)
71 	OR (old_references.letter_reference_number <> new_references.letter_reference_number) THEN
72 		-- Validate that the s_letter is not closed.
73 		IF IGS_AD_VAL_APCL.corp_val_slet_closed(
74 					new_references.correspondence_type,
75 					new_references.letter_reference_number,
76 					v_message_name) = FALSE THEN
77 					Fnd_Message.Set_Name('IGS',v_message_name);
78 					IGS_GE_MSG_STACK.ADD;
79 					App_Exception.Raise_Exception;
80 		END IF;
81 		-- Validate that the s_letter is of type 'ADM'
82 		IF IGS_AD_VAL_APCL.corp_val_slet_slrt(
83 					new_references.correspondence_type,
84 					new_references.letter_reference_number,
85 					'ADM',
86 					v_message_name) = FALSE THEN
87 				Fnd_Message.Set_Name('IGS',v_message_name);
88 				IGS_GE_MSG_STACK.ADD;
89 				App_Exception.Raise_Exception;
90 		END IF;
91 	END IF;
92 
93   END BeforeRowInsertUpdate1;
94 
95 PROCEDURE Check_Constraints (
96 	 Column_Name	IN	VARCHAR2	DEFAULT NULL,
97 	 Column_Value 	IN	VARCHAR2	DEFAULT NULL
98 )
99  AS
100  BEGIN
101  IF  column_name is null then
102      NULL;
103  ELSIF upper(Column_name) = 'ADMISSION_CAT' then
104      new_references.admission_cat := column_value;
105  ELSIF upper(Column_name) = 'S_ADMISSION_PROCESS_TYPE' then
106      new_references.s_admission_process_type := column_value;
107  ELSIF upper(Column_name) = 'CORRESPONDENCE_TYPE' then
108      new_references.correspondence_type := column_value;
109 END IF;
110 
111 IF upper(column_name) = 'ADMISSION_CAT' OR
112 	 column_name is null Then
113      IF new_references.admission_cat <> UPPER(new_references.admission_cat) Then
114        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
115 	   IGS_GE_MSG_STACK.ADD;
116        App_Exception.Raise_Exception;
117      END IF;
118 END IF;
119 
120 IF upper(column_name) = 'CORRESPONDENCE_TYPE' OR
121      column_name is null Then
122      IF new_references.correspondence_type <> UPPER(new_references.correspondence_type) Then
123        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
124 	   IGS_GE_MSG_STACK.ADD;
125        App_Exception.Raise_Exception;
126       END IF;
127 END IF;
128 
129 IF upper(column_name) = 'S_ADMISSION_PROCESS_TYPE' OR
130      column_name is null Then
131      IF new_references.s_admission_process_type <> UPPER(new_references.s_admission_process_type) Then
132        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
133 	   IGS_GE_MSG_STACK.ADD;
134        App_Exception.Raise_Exception;
135      END IF;
136 END IF;
137 END Check_Constraints;
138 
139   PROCEDURE Check_Parent_Existance AS
140   BEGIN
141 
142     IF (((old_references.admission_cat = new_references.admission_cat) AND
143          (old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
144         ((new_references.admission_cat IS NULL) OR
145          (new_references.s_admission_process_type IS NULL))) THEN
146       NULL;
147     ELSE
148      IF NOT IGS_AD_PRCS_CAT_PKG.Get_PK_For_Validation (
149         new_references.admission_cat,
150         new_references.s_admission_process_type,
151         'N'
152         ) THEN
153      Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
154 	 IGS_GE_MSG_STACK.ADD;
155      App_Exception.Raise_Exception;
156 	 END IF;
157     END IF;
158 
159     IF (((old_references.correspondence_type = new_references.correspondence_type) AND
160          (old_references.letter_reference_number = new_references.letter_reference_number)) OR
161         ((new_references.correspondence_type IS NULL) OR
162          (new_references.letter_reference_number IS NULL))) THEN
163       NULL;
164     ELSE
165      IF NOT IGS_CO_S_LTR_PKG.Get_PK_For_Validation (
166         new_references.correspondence_type,
167         new_references.letter_reference_number
168         ) THEN
169      Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
170 	 IGS_GE_MSG_STACK.ADD;
171      App_Exception.Raise_Exception;
172 	 END IF;
173     END IF;
174 
175   END Check_Parent_Existance;
176 
177 FUNCTION Get_PK_For_Validation (
178     x_admission_cat IN VARCHAR2,
179     x_s_admission_process_type IN VARCHAR2,
180     x_correspondence_type IN VARCHAR2
181     )
182 RETURN BOOLEAN
183 AS
184 
185     CURSOR cur_rowid IS
186       SELECT   rowid
187       FROM     IGS_AD_PRCS_CAT_LTR
188       WHERE    admission_cat = x_admission_cat
189       AND      s_admission_process_type = x_s_admission_process_type
190       AND      correspondence_type = x_correspondence_type
191       FOR UPDATE NOWAIT;
192 
193     lv_rowid cur_rowid%RowType;
194 
195   BEGIN
196 
197     Open cur_rowid;
198     Fetch cur_rowid INTO lv_rowid;
199  IF (cur_rowid%FOUND) THEN
200        Close cur_rowid;
201        Return (TRUE);
202  ELSE
203        Close cur_rowid;
204        Return (FALSE);
205  END IF;
206 
207   END Get_PK_For_Validation;
208 
209   PROCEDURE GET_FK_IGS_AD_PRCS_CAT (
210     x_admission_cat IN VARCHAR2,
211     x_s_admission_process_type IN VARCHAR2
212     ) AS
213 
214     CURSOR cur_rowid IS
215       SELECT   rowid
216       FROM     IGS_AD_PRCS_CAT_LTR
217       WHERE    admission_cat = x_admission_cat
218       AND      s_admission_process_type = x_s_admission_process_type ;
219 
220     lv_rowid cur_rowid%RowType;
221 
222   BEGIN
223 
224     Open cur_rowid;
225     Fetch cur_rowid INTO lv_rowid;
226     IF (cur_rowid%FOUND) THEN
227       Close cur_rowid;
228       Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCL_APC_FK');
229 	  IGS_GE_MSG_STACK.ADD;
230       App_Exception.Raise_Exception;
231       Return;
232     END IF;
233     Close cur_rowid;
234 
235   END GET_FK_IGS_AD_PRCS_CAT;
236 
237   PROCEDURE GET_FK_IGS_CO_S_LTR (
238     x_correspondence_type IN VARCHAR2,
239     x_letter_reference_number IN NUMBER
240     ) AS
241 
242     CURSOR cur_rowid IS
243       SELECT   rowid
244       FROM     IGS_AD_PRCS_CAT_LTR
245       WHERE    correspondence_type = x_correspondence_type
246       AND      letter_reference_number = x_letter_reference_number ;
247 
248     lv_rowid cur_rowid%RowType;
249 
250   BEGIN
251 
252     Open cur_rowid;
253     Fetch cur_rowid INTO lv_rowid;
254     IF (cur_rowid%FOUND) THEN
255       Close cur_rowid;
256       Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCL_SLET_FK');
257 	  IGS_GE_MSG_STACK.ADD;
258       App_Exception.Raise_Exception;
259       Return;
260     END IF;
261     Close cur_rowid;
262 
263   END GET_FK_IGS_CO_S_LTR;
264 
265   PROCEDURE Before_DML (
266     p_action IN VARCHAR2,
267     x_rowid IN VARCHAR2 DEFAULT NULL,
268     x_admission_cat IN VARCHAR2 DEFAULT NULL,
269     x_s_admission_process_type IN VARCHAR2 DEFAULT NULL,
270     x_correspondence_type IN VARCHAR2 DEFAULT NULL,
271     x_letter_reference_number IN NUMBER DEFAULT NULL,
272     x_creation_date IN DATE DEFAULT NULL,
273     x_created_by IN NUMBER DEFAULT NULL,
274     x_last_update_date IN DATE DEFAULT NULL,
275     x_last_updated_by IN NUMBER DEFAULT NULL,
276     x_last_update_login IN NUMBER DEFAULT NULL
277   ) AS
278   BEGIN
279 
280     Set_Column_Values (
281       p_action,
282       x_rowid,
283       x_admission_cat,
284       x_s_admission_process_type,
285       x_correspondence_type,
286       x_letter_reference_number,
287       x_creation_date,
288       x_created_by,
289       x_last_update_date,
290       x_last_updated_by,
291       x_last_update_login
292     );
293 
294  IF (p_action = 'INSERT') THEN
295        -- Call all the procedures related to Before Insert.
296      BeforeRowInsertUpdate1 ( p_inserting => TRUE );
297       IF  Get_PK_For_Validation (
298           new_references.admission_cat,
299           new_references.s_admission_process_type,
300           new_references.correspondence_type
301 		) THEN
302          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
303 		 IGS_GE_MSG_STACK.ADD;
304           App_Exception.Raise_Exception;
305       END IF;
306       Check_Constraints;
307       Check_Parent_Existance;
308  ELSIF (p_action = 'UPDATE') THEN
309        -- Call all the procedures related to Before Update.
310        BeforeRowInsertUpdate1 ( p_updating => TRUE );
311        Check_Constraints;
312        Check_Parent_Existance;
313  ELSIF (p_action = 'VALIDATE_INSERT') THEN
314       IF  Get_PK_For_Validation (
315           new_references.admission_cat,
316           new_references.s_admission_process_type,
317           new_references.correspondence_type
318 		) THEN
319          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
320 		 IGS_GE_MSG_STACK.ADD;
321           App_Exception.Raise_Exception;
322       END IF;
323       Check_Constraints;
324  ELSIF (p_action = 'VALIDATE_UPDATE') THEN
325        Check_Constraints;
326   END IF;
327   END Before_DML;
328 
329   PROCEDURE After_DML (
330     p_action IN VARCHAR2,
331     x_rowid IN VARCHAR2
332   ) AS
333   BEGIN
334 
335     l_rowid := x_rowid;
336 
337   END After_DML;
338 
339 procedure INSERT_ROW (
340   X_ROWID in out NOCOPY VARCHAR2,
341   X_ADMISSION_CAT in VARCHAR2,
342   X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
343   X_CORRESPONDENCE_TYPE in VARCHAR2,
344   X_LETTER_REFERENCE_NUMBER in NUMBER,
345   X_MODE in VARCHAR2 default 'R'
346   ) AS
347     cursor C is select ROWID from IGS_AD_PRCS_CAT_LTR
348       where ADMISSION_CAT = X_ADMISSION_CAT
349       and S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE
350       and CORRESPONDENCE_TYPE = X_CORRESPONDENCE_TYPE;
351     X_LAST_UPDATE_DATE DATE;
352     X_LAST_UPDATED_BY NUMBER;
353     X_LAST_UPDATE_LOGIN NUMBER;
354 begin
355   X_LAST_UPDATE_DATE := SYSDATE;
356   if(X_MODE = 'I') then
357     X_LAST_UPDATED_BY := 1;
358     X_LAST_UPDATE_LOGIN := 0;
359   elsif (X_MODE = 'R') then
360     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
361     if X_LAST_UPDATED_BY is NULL then
362       X_LAST_UPDATED_BY := -1;
363     end if;
364     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
365     if X_LAST_UPDATE_LOGIN is NULL then
366       X_LAST_UPDATE_LOGIN := -1;
367     end if;
368   else
369     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
370 	IGS_GE_MSG_STACK.ADD;
371     app_exception.raise_exception;
372   end if;
373 
374   Before_DML(p_action =>'INSERT',
375   x_rowid =>X_ROWID,
376   x_admission_cat => X_ADMISSION_CAT,
377   x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
378   x_correspondence_type => X_CORRESPONDENCE_TYPE,
379   x_letter_reference_number=> X_LETTER_REFERENCE_NUMBER,
380   x_creation_date => X_LAST_UPDATE_DATE,
381   x_created_by => X_LAST_UPDATED_BY,
382   x_last_update_date => X_LAST_UPDATE_DATE,
383   x_last_updated_by => X_LAST_UPDATED_BY,
384   x_last_update_login => X_LAST_UPDATE_LOGIN
385   );
386   insert into IGS_AD_PRCS_CAT_LTR (
387     ADMISSION_CAT,
388     S_ADMISSION_PROCESS_TYPE,
389     CORRESPONDENCE_TYPE,
390     LETTER_REFERENCE_NUMBER,
391     CREATION_DATE,
392     CREATED_BY,
393     LAST_UPDATE_DATE,
394     LAST_UPDATED_BY,
395     LAST_UPDATE_LOGIN
396   ) values (
397     NEW_REFERENCES.ADMISSION_CAT,
398     NEW_REFERENCES.S_ADMISSION_PROCESS_TYPE,
399     NEW_REFERENCES.CORRESPONDENCE_TYPE,
400     NEW_REFERENCES.LETTER_REFERENCE_NUMBER,
401     X_LAST_UPDATE_DATE,
402     X_LAST_UPDATED_BY,
403     X_LAST_UPDATE_DATE,
404     X_LAST_UPDATED_BY,
405     X_LAST_UPDATE_LOGIN
406   );
407 
408   open c;
409   fetch c into X_ROWID;
410   if (c%notfound) then
411     close c;
412     raise no_data_found;
413   end if;
414   close c;
415 After_DML(
416  p_action =>'INSERT',
417  x_rowid => X_ROWID
418 );
419 end INSERT_ROW;
420 
421 procedure LOCK_ROW (
422   X_ROWID in VARCHAR2,
423   X_ADMISSION_CAT in VARCHAR2,
424   X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
425   X_CORRESPONDENCE_TYPE in VARCHAR2,
426   X_LETTER_REFERENCE_NUMBER in NUMBER
427 ) AS
428   cursor c1 is select
429       LETTER_REFERENCE_NUMBER
430     from IGS_AD_PRCS_CAT_LTR
431     where ROWID = X_ROWID for update nowait;
432   tlinfo c1%rowtype;
433 
434 begin
435   open c1;
436   fetch c1 into tlinfo;
437   if (c1%notfound) then
438     close c1;
439     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
440 	IGS_GE_MSG_STACK.ADD;
441     app_exception.raise_exception;
442     return;
443   end if;
444   close c1;
445 
446   if ( (tlinfo.LETTER_REFERENCE_NUMBER = X_LETTER_REFERENCE_NUMBER)
447   ) then
448     null;
449   else
450     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
451 	IGS_GE_MSG_STACK.ADD;
452     app_exception.raise_exception;
453   end if;
454   return;
455 end LOCK_ROW;
456 
457 procedure UPDATE_ROW (
458   X_ROWID in VARCHAR2,
459   X_ADMISSION_CAT in VARCHAR2,
460   X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
461   X_CORRESPONDENCE_TYPE in VARCHAR2,
462   X_LETTER_REFERENCE_NUMBER in NUMBER,
463   X_MODE in VARCHAR2 default 'R'
464   ) AS
465     X_LAST_UPDATE_DATE DATE;
466     X_LAST_UPDATED_BY NUMBER;
467     X_LAST_UPDATE_LOGIN NUMBER;
468 begin
469   X_LAST_UPDATE_DATE := SYSDATE;
470   if(X_MODE = 'I') then
471     X_LAST_UPDATED_BY := 1;
472     X_LAST_UPDATE_LOGIN := 0;
473   elsif (X_MODE = 'R') then
474     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
475     if X_LAST_UPDATED_BY is NULL then
476       X_LAST_UPDATED_BY := -1;
477     end if;
478     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
479     if X_LAST_UPDATE_LOGIN is NULL then
480       X_LAST_UPDATE_LOGIN := -1;
481     end if;
482   else
483     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
484 	IGS_GE_MSG_STACK.ADD;
485     app_exception.raise_exception;
486   end if;
487   Before_DML(p_action =>'UPDATE',
488   x_rowid =>X_ROWID,
489   x_admission_cat => X_ADMISSION_CAT,
490   x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
491   x_correspondence_type => X_CORRESPONDENCE_TYPE,
492   x_letter_reference_number=> X_LETTER_REFERENCE_NUMBER,
493   x_creation_date => X_LAST_UPDATE_DATE,
494   x_created_by => X_LAST_UPDATED_BY,
495   x_last_update_date => X_LAST_UPDATE_DATE,
496   x_last_updated_by => X_LAST_UPDATED_BY,
497   x_last_update_login => X_LAST_UPDATE_LOGIN
498   );
499   update IGS_AD_PRCS_CAT_LTR set
500     LETTER_REFERENCE_NUMBER = NEW_REFERENCES.LETTER_REFERENCE_NUMBER,
501     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
502     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
503     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
504   where ROWID = X_ROWID
505   ;
506   if (sql%notfound) then
507     raise no_data_found;
508   end if;
509 After_DML(
510  p_action =>'UPDATE',
511  x_rowid => X_ROWID
512 );
513 end UPDATE_ROW;
514 
515 procedure ADD_ROW (
516   X_ROWID in out NOCOPY VARCHAR2,
517   X_ADMISSION_CAT in VARCHAR2,
518   X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
519   X_CORRESPONDENCE_TYPE in VARCHAR2,
520   X_LETTER_REFERENCE_NUMBER in NUMBER,
521   X_MODE in VARCHAR2 default 'R'
522   ) AS
523   cursor c1 is select rowid from IGS_AD_PRCS_CAT_LTR
524      where ADMISSION_CAT = X_ADMISSION_CAT
525      and S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE
526      and CORRESPONDENCE_TYPE = X_CORRESPONDENCE_TYPE
527   ;
528 
529 begin
530   open c1;
531   fetch c1 into X_ROWID;
532   if (c1%notfound) then
533     close c1;
534     INSERT_ROW (
535      X_ROWID,
536      X_ADMISSION_CAT,
537      X_S_ADMISSION_PROCESS_TYPE,
538      X_CORRESPONDENCE_TYPE,
539      X_LETTER_REFERENCE_NUMBER,
540      X_MODE);
541     return;
542   end if;
543   close c1;
544   UPDATE_ROW (
545    X_ROWID,
546    X_ADMISSION_CAT,
547    X_S_ADMISSION_PROCESS_TYPE,
548    X_CORRESPONDENCE_TYPE,
549    X_LETTER_REFERENCE_NUMBER,
550    X_MODE);
551 end ADD_ROW;
552 
553 procedure DELETE_ROW (
554   X_ROWID in VARCHAR2
555 ) AS
556 begin
557 Before_DML(
558  p_action =>'DELETE',
559  x_rowid => X_ROWID
560 );
561   delete from IGS_AD_PRCS_CAT_LTR
562   where ROWID = X_ROWID;
563   if (sql%notfound) then
564     raise no_data_found;
565   end if;
566 After_DML(
567  p_action =>'DELETE',
568  x_rowid => X_ROWID
569 );
570 end DELETE_ROW;
571 
572 end IGS_AD_PRCS_CAT_LTR_PKG;