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.IGR_I_A_ITYPE_PKG

Source


1 package body IGR_I_A_ITYPE_Pkg as
2 /* $Header: IGSRH15B.pls 120.0 2005/06/01 22:27:30 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGR_I_A_ITYPE%RowType;
6   new_references IGR_I_A_ITYPE%RowType;
7 PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10     x_person_id IN NUMBER DEFAULT NULL,
11     x_enquiry_appl_number IN NUMBER DEFAULT NULL,
12     x_info_type_id 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   ) as
19 
20     CURSOR cur_old_ref_values IS
21       SELECT   *
22       FROM     IGR_I_A_ITYPE
23       WHERE    rowid = x_rowid;
24 
25   BEGIN
26 
27     l_rowid := x_rowid;
28 
29     -- Code for setting the Old and New Reference Values.
30     -- Populate Old Values.
31     Open cur_old_ref_values;
32     FETCH cur_old_ref_values INTO old_references;
33     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
34       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35       IGS_GE_MSG_STACK.ADD;
36       Close cur_old_ref_values;
37       App_Exception.Raise_Exception;
38       Return;
39     END IF;
40     Close cur_old_ref_values;
41 
42     -- Populate New Values.
43     new_references.person_id := x_person_id;
44     new_references.enquiry_appl_number := x_enquiry_appl_number;
45     new_references.INFO_TYPE_ID:= x_info_type_id;
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 
57   END Set_Column_Values;
58   PROCEDURE BeforeRowInsertUpdateDelete1(
59     p_inserting IN BOOLEAN DEFAULT FALSE,
60     p_updating IN BOOLEAN DEFAULT FALSE,
61     p_deleting IN BOOLEAN DEFAULT FALSE
62     ) as
63    CURSOR c_deceased(cp_party_id igs_pe_hz_parties.party_id%TYPE) IS
64      SELECT deceased_ind
65      FROM igs_pe_hz_parties
66      WHERE party_id = cp_party_id;
67 
68    CURSOR c_information_type (p_info_type_id igr_i_info_types_v.info_type_id%TYPE)   IS
69     SELECT  information_type
70     FROM igr_i_info_types_v
71     WHERE info_type_id =p_info_type_id ;
72 
73    v_deceased_ind igs_pe_hz_parties.deceased_ind%TYPE;
74    v_message_name  varchar2(30);
75    p_enquiry_information_type igr_i_info_types_v.information_type%TYPE;
76   BEGIN
77         -- Fetch the Deceased Indicator
78     OPEN c_deceased(new_references.person_id);
79     FETCH c_deceased INTO v_deceased_ind;
80     CLOSE c_deceased;
81         -- Validate that inserts/updates/deletes are allowed if a person is deceased
82         -- Validate that the person is not deceased
83         IF v_deceased_ind = 'Y' THEN
84            Fnd_Message.Set_Name('IGS', 'IGS_IN_DEC_NO_INQ');
85        IGS_GE_MSG_STACK.ADD;
86            App_Exception.Raise_Exception;
87     END IF;
88 
89     -- Insert IGS_IN_APPLML_PKGITM records for the information_type.
90     IF p_inserting THEN
91     OPEN c_information_type(new_references.info_type_id);
92     FETCH c_information_type INTO p_enquiry_information_type ;
93     CLOSE c_information_type;
94 
95         IF IGR_GEN_001.ADMP_INS_EAP_EITPI(new_references.person_id,
96                       new_references.enquiry_appl_number,
97                       p_enquiry_information_type,
98                       v_message_name) = FALSE THEN
99                         Fnd_Message.Set_Name('IGS', v_message_name);
100             IGS_GE_MSG_STACK.ADD;
101                         App_Exception.Raise_Exception;
102         END IF;
103     END IF;
104 
105    END BeforeRowInsertUpdateDelete1;
106 
107 
108  PROCEDURE Check_Parent_Existance as
109   BEGIN
110 
111     IF (((old_references.person_id = new_references.person_id) AND
112          (old_references.enquiry_appl_number = new_references.enquiry_appl_number)) OR
113         ((new_references.person_id IS NULL) OR
114          (new_references.enquiry_appl_number IS NULL))) THEN
115       NULL;
116     ELSE
117       IF NOT(IGR_I_APPL_PKG.Get_PK_For_Validation (
118         new_references.person_id,
119         new_references.enquiry_appl_number
120         ))THEN
121          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
122          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_APPL'));
123          IGS_GE_MSG_STACK.ADD;
124          App_Exception.Raise_Exception;
125       END IF;
126 
127     END IF;
128 
129     IF (((old_references.INFO_TYPE_ID = new_references.INFO_TYPE_ID)) OR
130         ((new_references.INFO_TYPE_ID IS NULL))) THEN
131       NULL;
132     ELSE
133       IF NOT(IGR_I_PKG_ITEM_PKG.Get_PK_For_Validation (
134         new_references.INFO_TYPE_ID
135         ))THEN
136          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
137          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_INFO_TYPE'));
138          IGS_GE_MSG_STACK.ADD;
139          App_Exception.Raise_Exception;
140       END IF    ;
141     END IF;
142 
143   END Check_Parent_Existance;
144 
145 PROCEDURE Check_Constraints (
146 Column_Name IN  VARCHAR2    DEFAULT NULL,
147 Column_Value    IN  VARCHAR2    DEFAULT NULL
148     ) as
149 BEGIN
150       IF  column_name is null then
151          NULL;
152       ELSIF upper(Column_name) = 'INFO_TYPE_ID' then
153          new_references.INFO_TYPE_ID:= column_value;
154       END IF;
155      IF upper(column_name) = 'INFO_TYPE_ID' OR
156         column_name is null Then
157         IF new_references.INFO_TYPE_ID <> UPPER(new_references.INFO_TYPE_ID) Then
158          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
159          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_INFO_TYPE'));
160          IGS_GE_MSG_STACK.ADD;
161          App_Exception.Raise_Exception;
162         END IF;
163      END IF;
164 
165 END Check_Constraints;
166 
167 
168   FUNCTION   Get_PK_For_Validation (
169     x_person_id IN NUMBER,
170     x_enquiry_appl_number IN NUMBER,
171     x_info_type_id IN VARCHAR2
172     ) RETURN BOOLEAN AS
173     CURSOR cur_rowid IS
174       SELECT   rowid
175       FROM     IGR_I_A_ITYPE
176       WHERE    person_id = x_person_id
177       AND      enquiry_appl_number = x_enquiry_appl_number
178       AND      INFO_TYPE_ID = x_info_type_id
179       FOR UPDATE NOWAIT;
180 
181     lv_rowid cur_rowid%RowType;
182 
183   BEGIN
184 
185     Open cur_rowid;
186     Fetch cur_rowid INTO lv_rowid;
187 IF (cur_rowid%FOUND) THEN
188  Close cur_rowid;
189  Return (TRUE);
190 ELSE
191     Close cur_rowid;
192     Return (FALSE);
193 END IF;
194 
195   END Get_PK_For_Validation;
196 
197   PROCEDURE GET_FK_IGR_I_APPL (
198     x_person_id IN NUMBER,
199     x_enquiry_appl_number IN NUMBER
200     ) as
201 
202     CURSOR cur_rowid IS
203       SELECT   rowid
204       FROM     IGR_I_A_ITYPE
205       WHERE    person_id = x_person_id
206       AND      enquiry_appl_number = x_enquiry_appl_number ;
207 
208     lv_rowid cur_rowid%RowType;
209 
210   BEGIN
211 
212     Open cur_rowid;
213     Fetch cur_rowid INTO lv_rowid;
214     IF (cur_rowid%FOUND) THEN
215       Fnd_Message.Set_Name ('IGS', 'IGS_IN_EAPIT_EAP_FK');
216       IGS_GE_MSG_STACK.ADD;
217       Close cur_rowid;
218       App_Exception.Raise_Exception;
219       Return;
220     END IF;
221     Close cur_rowid;
222 
223   END GET_FK_IGR_I_APPL;
224 
225   PROCEDURE GET_FK_IGR_I_A_ITYPE (
226     x_info_type_id IN VARCHAR2
227     ) as
228 
229     CURSOR cur_rowid IS
230       SELECT   rowid
231       FROM     IGR_I_A_ITYPE
232       WHERE    INFO_TYPE_ID = x_info_type_id ;
233 
234     lv_rowid cur_rowid%RowType;
235 
236   BEGIN
237 
238     Open cur_rowid;
239     Fetch cur_rowid INTO lv_rowid;
240     IF (cur_rowid%FOUND) THEN
241       Fnd_Message.Set_Name ('IGS', 'IGS_IN_EAPIT_EIT_FK');
242       IGS_GE_MSG_STACK.ADD;
243       Close cur_rowid;
244       App_Exception.Raise_Exception;
245       Return;
246     END IF;
247     Close cur_rowid;
248 
249   END GET_FK_IGR_I_A_ITYPE;
250 
251   PROCEDURE Before_DML (
252     p_action IN VARCHAR2,
253     x_rowid IN  VARCHAR2 DEFAULT NULL,
254     x_person_id IN NUMBER DEFAULT NULL,
255     x_enquiry_appl_number IN NUMBER DEFAULT NULL,
256     x_info_type_id IN VARCHAR2 DEFAULT NULL,
257     x_creation_date IN DATE DEFAULT NULL,
258     x_created_by IN NUMBER DEFAULT NULL,
259     x_last_update_date IN DATE DEFAULT NULL,
260     x_last_updated_by IN NUMBER DEFAULT NULL,
261     x_last_update_login IN NUMBER DEFAULT NULL
262   ) as
263   BEGIN
264 
265     Set_Column_Values (
266       p_action,
267       x_rowid,
268       x_person_id,
269       x_enquiry_appl_number,
270       x_info_type_id,
271       x_creation_date,
272       x_created_by,
273       x_last_update_date,
274       x_last_updated_by,
275       x_last_update_login
276     );
277 
278     IF (p_action = 'INSERT') THEN
279       -- Call all the procedures related to Before Insert.
280       BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
281 IF  Get_PK_For_Validation (
282              new_references.person_id   ,
283              new_references.enquiry_appl_number ,
284              new_references.INFO_TYPE_ID
285                          ) THEN
286         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
287         IGS_GE_MSG_STACK.ADD;
288         App_Exception.Raise_Exception;
289 END IF;
290   Check_Constraints;
291       Check_Parent_Existance;
292     ELSIF (p_action = 'UPDATE') THEN
293       -- Call all the procedures related to Before Update.
294       BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
295       Check_Constraints;
296       Check_Parent_Existance;
297     ELSIF (p_action = 'DELETE') THEN
298       -- Call all the procedures related to Before Delete.
299       BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
300     ELSIF (p_action = 'VALIDATE_INSERT') THEN
301       IF  Get_PK_For_Validation (
302              new_references.person_id   ,
303              new_references.enquiry_appl_number ,
304              new_references.INFO_TYPE_ID
305                          ) THEN
306         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
307         IGS_GE_MSG_STACK.ADD;
308         App_Exception.Raise_Exception;
309 END IF;
310             Check_Constraints;
311     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
312             Check_Constraints;
313     ELSIF (p_action = 'VALIDATE_DELETE') THEN
314            NULL;
315     END IF;
316 
317   END Before_DML;
318 
319 
320 procedure INSERT_ROW (
321   X_ROWID in out NOCOPY VARCHAR2,
322   X_PERSON_ID in NUMBER,
323   X_ENQUIRY_APPL_NUMBER in NUMBER,
324   x_info_type_id in VARCHAR2,
325   X_MODE in VARCHAR2 default 'R'
326   ) as
327     cursor C is select ROWID from IGR_I_A_ITYPE
328       where PERSON_ID = X_PERSON_ID
329       and ENQUIRY_APPL_NUMBER = X_ENQUIRY_APPL_NUMBER
330       and INFO_TYPE_ID = x_info_type_id;
331     X_LAST_UPDATE_DATE DATE;
332     X_LAST_UPDATED_BY NUMBER;
333     X_LAST_UPDATE_LOGIN NUMBER;
334 begin
335   X_LAST_UPDATE_DATE := SYSDATE;
336   if(X_MODE = 'I') then
337     X_LAST_UPDATED_BY := 1;
338     X_LAST_UPDATE_LOGIN := 0;
339   elsif (X_MODE = 'R') then
340     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
341     if X_LAST_UPDATED_BY is NULL then
342       X_LAST_UPDATED_BY := -1;
343     end if;
344     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
345     if X_LAST_UPDATE_LOGIN is NULL then
346       X_LAST_UPDATE_LOGIN := -1;
347     end if;
348   else
349         FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
350         IGS_GE_MSG_STACK.ADD;
351             app_exception.raise_exception;
352   end if;
353 
354      Before_DML(
355  p_action=>'INSERT',
356  x_rowid=>X_ROWID,
357  x_enquiry_appl_number=>X_ENQUIRY_APPL_NUMBER,
358  x_info_type_id=>x_info_type_id,
359  x_person_id=>X_PERSON_ID,
360  x_creation_date=>X_LAST_UPDATE_DATE,
361  x_created_by=>X_LAST_UPDATED_BY,
362  x_last_update_date=>X_LAST_UPDATE_DATE,
363  x_last_updated_by=>X_LAST_UPDATED_BY,
364  x_last_update_login=>X_LAST_UPDATE_LOGIN
365  );
366 
367   insert into IGR_I_A_ITYPE (
368     PERSON_ID,
369     ENQUIRY_APPL_NUMBER,
370     INFO_TYPE_ID,
371     CREATION_DATE,
372     CREATED_BY,
373     LAST_UPDATE_DATE,
374     LAST_UPDATED_BY,
375     LAST_UPDATE_LOGIN
376   ) values (
377     NEW_REFERENCES.PERSON_ID,
378     NEW_REFERENCES.ENQUIRY_APPL_NUMBER,
379     NEW_REFERENCES.INFO_TYPE_ID,
380     X_LAST_UPDATE_DATE,
381     X_LAST_UPDATED_BY,
382     X_LAST_UPDATE_DATE,
383     X_LAST_UPDATED_BY,
384     X_LAST_UPDATE_LOGIN
385   );
386 
387   open c;
388   fetch c into X_ROWID;
389   if (c%notfound) then
390     close c;
391     raise no_data_found;
392   end if;
393   close c;
394 end INSERT_ROW;
395 
396 procedure LOCK_ROW (
397    X_ROWID in VARCHAR2,
398   X_PERSON_ID in NUMBER,
399   X_ENQUIRY_APPL_NUMBER in NUMBER,
400   x_info_type_id in VARCHAR2
401 ) as
402   cursor c1 is select ROWID
403     from IGR_I_A_ITYPE
404     where ROWID = X_ROWID
405     for update nowait;
406   tlinfo c1%rowtype;
407 
408 begin
409   open c1;
410   fetch c1 into tlinfo;
411   if (c1%notfound) then
412     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
413     IGS_GE_MSG_STACK.ADD;
414     close c1;
415     app_exception.raise_exception;
416     return;
417   end if;
418   close c1;
419 
420   return;
421 end LOCK_ROW;
422 
423 procedure DELETE_ROW (
424   X_ROWID in VARCHAR2
425 ) as
426 begin
427  Before_DML(
428   p_action => 'DELETE',
429   x_rowid => X_ROWID
430   );
431   delete from IGR_I_A_ITYPE
432   where ROWID = X_ROWID;
433   if (sql%notfound) then
434     raise no_data_found;
435   end if;
436 end DELETE_ROW;
437 
438 end IGR_I_A_ITYPE_Pkg;