[Home] [Help]
PACKAGE BODY: APPS.IGR_I_A_CHARTYP_PKG
Source
1 package body IGR_I_A_CHARTYP_PKG as
2 /* $Header: IGSRH14B.pls 120.0 2005/06/01 20:48:18 appldev noship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGR_I_A_CHARTYP%RowType;
7 new_references IGR_I_A_CHARTYP%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_enquiry_appl_number IN NUMBER DEFAULT NULL,
14 x_enquiry_characteristic_type IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) as
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGR_I_A_CHARTYP
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 Close cur_old_ref_values;
39 App_Exception.Raise_Exception;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.person_id := x_person_id;
46 new_references.enquiry_appl_number := x_enquiry_appl_number;
47 new_references.ENQUIRY_CHARACTERISTIC_TYPE:= x_enquiry_characteristic_type;
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 -- Trigger description :-
62 -- "OSS_TST".trg_eapect_br_iu
63 -- BEFORE INSERT OR UPDATE
64 -- ON IGR_I_A_CHARTYP
65 -- FOR EACH ROW
66
67 PROCEDURE BeforeRowInsertUpdate1(
68 p_inserting IN BOOLEAN DEFAULT FALSE,
69 p_updating IN BOOLEAN DEFAULT FALSE,
70 p_deleting IN BOOLEAN DEFAULT FALSE
71 ) as
72 CURSOR c_deceased(cp_party_id igs_pe_hz_parties.party_id%TYPE) IS
73 SELECT deceased_ind
74 FROM igs_pe_hz_parties
75 WHERE party_id = cp_party_id;
76 v_deceased_ind igs_pe_hz_parties.deceased_ind%TYPE;
77 v_message_name varchar2(30);
78 BEGIN
79 -- Fetch the Deceased Indicator
80 OPEN c_deceased(new_references.person_id);
81 FETCH c_deceased INTO v_deceased_ind;
82 CLOSE c_deceased;
83 -- Validate that the person is not deceased
84 -- No insert, update, delete if a person is deceased
85 IF v_deceased_ind = 'Y' THEN
86 Fnd_Message.Set_Name('IGS', 'IGS_IN_DEC_NO_INQ');
87 IGS_GE_MSG_STACK.ADD;
88 App_Exception.Raise_Exception;
89 END IF;
90
91 -- Validate that inserts/updates are allowed
92 IF p_inserting OR p_updating THEN
93
94 -- Validate enquiry characteristic type closed indicator
95 IF IGR_VAL_ECT.admp_val_ect_closed(new_references.ENQUIRY_CHARACTERISTIC_TYPE,
96 v_message_name) = FALSE THEN
97 Fnd_Message.Set_Name('IGS', v_message_name);
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END IF;
101 END IF;
102
103
104 END BeforeRowInsertUpdate1;
105
106
107 PROCEDURE Check_Parent_Existance as
108 BEGIN
109
110 IF (((old_references.person_id = new_references.person_id) AND
111 (old_references.enquiry_appl_number = new_references.enquiry_appl_number)) OR
112 ((new_references.person_id IS NULL) OR
113 (new_references.enquiry_appl_number IS NULL))) THEN
114 NULL;
115 ELSE
116 IF NOT(IGR_I_APPL_PKG.Get_PK_For_Validation (
117 new_references.person_id,
118 new_references.enquiry_appl_number
119 ))THEN
120 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
121 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_APPL'));
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125 END IF;
126
127 IF (((old_references.ENQUIRY_CHARACTERISTIC_TYPE= new_references.ENQUIRY_CHARACTERISTIC_TYPE)) OR
128 ((new_references.ENQUIRY_CHARACTERISTIC_TYPE IS NULL))) THEN
129 NULL;
130 ELSE
131 IF NOT(IGR_I_E_CHARTYP_PKG.Get_PK_For_Validation (
132 new_references.ENQUIRY_CHARACTERISTIC_TYPE,
133 'N'
134 ))THEN
135 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
136 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_CHR_TYPE'));
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 END IF ;
140 END IF;
141
142 END Check_Parent_Existance;
143
144 PROCEDURE Check_Constraints (
145 Column_Name IN VARCHAR2 DEFAULT NULL,
146 Column_Value IN VARCHAR2 DEFAULT NULL
147 ) as
148 BEGIN
149 IF column_name is null then
150 NULL;
151 ELSIF upper(Column_name) = 'ENQUIRY_CHARACTERISTIC_TYPE' then
152 new_references.enquiry_characteristic_type:= column_value;
153 END IF;
154 IF upper(column_name) = 'ENQUIRY_CHARACTERISTIC_TYPE' OR
155 column_name is null Then
156 IF new_references.enquiry_characteristic_type <> UPPER(new_references.enquiry_characteristic_type) Then
157 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
158 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_CHR_TYPE'));
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 END IF;
163
164 END Check_Constraints;
165
166
167 FUNCTION Get_PK_For_Validation (
168 x_person_id IN NUMBER,
169 x_enquiry_appl_number IN NUMBER,
170 x_enquiry_characteristic_type IN VARCHAR2
171 ) RETURN BOOLEAN AS
172 CURSOR cur_rowid IS
173 SELECT rowid
174 FROM IGR_I_A_CHARTYP
175 WHERE person_id = x_person_id
176 AND enquiry_appl_number = x_enquiry_appl_number
177 AND ENQUIRY_CHARACTERISTIC_TYPE = x_enquiry_characteristic_type
178 FOR UPDATE NOWAIT;
179
180 lv_rowid cur_rowid%RowType;
181
182 BEGIN
183
184 Open cur_rowid;
185 Fetch cur_rowid INTO lv_rowid;
186 IF (cur_rowid%FOUND) THEN
187 Close cur_rowid;
188 Return (TRUE);
189 ELSE
190 Close cur_rowid;
191 Return (FALSE);
192 END IF;
193 Close cur_rowid;
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_CHARTYP
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_EAPECT_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 IGR_I_E_CHARTYP (
226 x_enquiry_characteristic_type IN VARCHAR2
227 ) as
228
229 CURSOR cur_rowid IS
230 SELECT rowid
231 FROM IGR_I_A_CHARTYP
232 WHERE ENQUIRY_CHARACTERISTIC_TYPE = x_enquiry_characteristic_type ;
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_EAPECT_ECT_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 IGR_I_E_CHARTYP;
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_enquiry_characteristic_type 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_enquiry_characteristic_type,
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 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
281 IF Get_PK_For_Validation (
282 new_references.person_id ,
283 new_references.enquiry_appl_number ,
284 new_references.enquiry_characteristic_type
285
286 ) THEN
287 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
288 IGS_GE_MSG_STACK.ADD;
289 App_Exception.Raise_Exception;
290 END IF;
291
292 Check_Constraints;
293 Check_Parent_Existance;
294 ELSIF (p_action = 'UPDATE') THEN
295 -- Call all the procedures related to Before Update.
296 BeforeRowInsertUpdate1 ( p_updating => TRUE );
297 Check_Constraints;
298 Check_Parent_Existance;
299 ELSIF (p_action = 'DELETE') THEN
300 -- Call all the procedures related to Before Delete.
301 BeforeRowInsertUpdate1(p_deleting=>TRUE);
302 ELSIF (p_action = 'VALIDATE_INSERT') THEN
306 new_references.enquiry_characteristic_type
303 IF Get_PK_For_Validation (
304 new_references.person_id ,
305 new_references.enquiry_appl_number ,
307
308 ) THEN
309 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception;
312 END IF;
313 Check_Constraints;
314 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
315 Check_Constraints;
316 ELSIF (p_action = 'VALIDATE_DELETE') THEN
317 NULL;
318 END IF;
319
320 END Before_DML;
321
322
323 procedure INSERT_ROW (
324 X_ROWID in out NOCOPY VARCHAR2,
325 X_PERSON_ID in NUMBER,
326 X_ENQUIRY_APPL_NUMBER in NUMBER,
327 X_ENQUIRY_CHARACTERISTIC_TYPE in VARCHAR2,
328 X_MODE in VARCHAR2 default 'R'
329 ) as
330 cursor C is select ROWID from IGR_I_A_CHARTYP
331 where PERSON_ID = X_PERSON_ID
332 and ENQUIRY_APPL_NUMBER = X_ENQUIRY_APPL_NUMBER
333 and ENQUIRY_CHARACTERISTIC_TYPE = X_ENQUIRY_CHARACTERISTIC_TYPE;
334 X_LAST_UPDATE_DATE DATE;
335 X_LAST_UPDATED_BY NUMBER;
336 X_LAST_UPDATE_LOGIN NUMBER;
337 begin
338 X_LAST_UPDATE_DATE := SYSDATE;
339 if(X_MODE = 'I') then
340 X_LAST_UPDATED_BY := 1;
341 X_LAST_UPDATE_LOGIN := 0;
342 elsif (X_MODE = 'R') then
343 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
344 if X_LAST_UPDATED_BY is NULL then
345 X_LAST_UPDATED_BY := -1;
346 end if;
347 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
348 if X_LAST_UPDATE_LOGIN is NULL then
349 X_LAST_UPDATE_LOGIN := -1;
350 end if;
351 else
352 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
353 IGS_GE_MSG_STACK.ADD;
354 app_exception.raise_exception;
355 end if;
356 Before_DML(
357 p_action=>'INSERT',
358 x_rowid=>X_ROWID,
359 x_enquiry_appl_number=>X_ENQUIRY_APPL_NUMBER,
360 x_enquiry_characteristic_type=>X_ENQUIRY_CHARACTERISTIC_TYPE,
361 x_person_id=>X_PERSON_ID,
362 x_creation_date=>X_LAST_UPDATE_DATE,
363 x_created_by=>X_LAST_UPDATED_BY,
364 x_last_update_date=>X_LAST_UPDATE_DATE,
365 x_last_updated_by=>X_LAST_UPDATED_BY,
366 x_last_update_login=>X_LAST_UPDATE_LOGIN
367 );
368 insert into IGR_I_A_CHARTYP (
369 PERSON_ID,
370 ENQUIRY_APPL_NUMBER,
371 ENQUIRY_CHARACTERISTIC_TYPE,
372 CREATION_DATE,
373 CREATED_BY,
374 LAST_UPDATE_DATE,
375 LAST_UPDATED_BY,
376 LAST_UPDATE_LOGIN
377 ) values (
378 NEW_REFERENCES.PERSON_ID,
379 NEW_REFERENCES.ENQUIRY_APPL_NUMBER,
380 NEW_REFERENCES.ENQUIRY_CHARACTERISTIC_TYPE,
381 X_LAST_UPDATE_DATE,
382 X_LAST_UPDATED_BY,
383 X_LAST_UPDATE_DATE,
384 X_LAST_UPDATED_BY,
385 X_LAST_UPDATE_LOGIN
386 );
387
388 open c;
389 fetch c into X_ROWID;
390 if (c%notfound) then
391 close c;
392 raise no_data_found;
393 end if;
394 close c;
395
396 end INSERT_ROW;
397
398 procedure LOCK_ROW (
399 X_ROWID in VARCHAR2,
400 X_PERSON_ID in NUMBER,
401 X_ENQUIRY_APPL_NUMBER in NUMBER,
402 X_ENQUIRY_CHARACTERISTIC_TYPE in VARCHAR2
403 ) as
404 cursor c1 is select ROWID
405 from IGR_I_A_CHARTYP
406 where ROWID = X_ROWID
407 for update nowait;
408 tlinfo c1%rowtype;
409
410 begin
411 open c1;
412 fetch c1 into tlinfo;
413 if (c1%notfound) then
414 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
415 IGS_GE_MSG_STACK.ADD;
416 close c1;
417 app_exception.raise_exception;
418 return;
419 end if;
420 close c1;
421
422 return;
423 end LOCK_ROW;
424
425
426 procedure DELETE_ROW (
427 X_ROWID in VARCHAR2
428 ) as
429 begin
430 Before_DML(
431 p_action => 'DELETE',
432 x_rowid => X_ROWID
433 );
434
435 delete from IGR_I_A_CHARTYP
436 where ROWID = X_ROWID;
437 if (sql%notfound) then
438 raise no_data_found;
439 end if;
440
441
442 end DELETE_ROW;
443
444 end IGR_I_A_CHARTYP_PKG;