[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_METHOD_TYPE_PKG
Source
1 package body IGS_EN_METHOD_TYPE_PKG AS
2 /* $Header: IGSEI07B.pls 120.0 2005/06/01 18:30:36 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_EN_METHOD_TYPE%RowType;
5 new_references IGS_EN_METHOD_TYPE%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2,
9 x_enr_method_type IN VARCHAR2,
10 x_description IN VARCHAR2,
11 x_closed_ind IN VARCHAR2,
12 x_creation_date IN DATE,
13 x_created_by IN NUMBER,
14 x_last_update_date IN DATE,
15 x_last_updated_by IN NUMBER,
16 x_last_update_login IN NUMBER,
17 x_self_service IN VARCHAR2,
18 x_ivr_display_ind IN VARCHAR2,
19 x_bulk_job_ind IN VARCHAR2,
20 x_transfer_flag IN VARCHAR2,
21 x_dflt_trans_details_flag IN VARCHAR2
22 ) AS
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_EN_METHOD_TYPE
27 WHERE rowid = x_rowid;
28
29 BEGIN
30
31 l_rowid := x_rowid;
32
33 -- Code for setting the Old and New Reference Values.
34 -- Populate Old Values.
35 Open cur_old_ref_values;
36 Fetch cur_old_ref_values INTO old_references;
37 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 Close cur_old_ref_values;
41 App_Exception.Raise_Exception;
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45
46 -- Populate New Values.
47 new_references.enr_method_type := x_enr_method_type;
48 new_references.description := x_description;
49 new_references.closed_ind := x_closed_ind;
50 new_references.self_service := x_self_service;
51 new_references.ivr_display_ind := NVL(x_ivr_display_ind,'N');
52 new_references.bulk_job_ind := x_bulk_job_ind;
53 new_references.transfer_flag := x_transfer_flag;
54 new_references.dflt_trans_details_flag := x_dflt_trans_details_flag;
55 IF (p_action = 'UPDATE') THEN
56 new_references.creation_date := old_references.creation_date;
57 new_references.created_by := old_references.created_by;
58 ELSE
59 new_references.creation_date := x_creation_date;
60 new_references.created_by := x_created_by;
61 END IF;
62 new_references.last_update_date := x_last_update_date;
63 new_references.last_updated_by := x_last_updated_by;
64 new_references.last_update_login := x_last_update_login;
65
66 END Set_Column_Values;
67
68
69 PROCEDURE Check_Constraints (
70 Column_Name IN VARCHAR2 DEFAULT NULL,
71 Column_Value IN VARCHAR2 DEFAULT NULL
72 ) as
73
74 BEGIN
75
76 -- The following code checks for check constraints on the Columns.
77
78 IF column_name is NULL THEN
79 NULL;
80 ELSIF UPPER(column_name) = 'ENR_METHOD_TYPE' THEN
81 new_references.enr_method_type := column_value;
82 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
83 new_references.closed_ind := column_value;
84 ELSIF UPPER(column_name) = 'BULK_JOB_IND' THEN
85 new_references.bulk_job_ind := column_value;
86 END IF;
87
88 IF ((UPPER (column_name) = 'CLOSED_IND') OR (column_name IS NULL)) THEN
89 IF new_references.closed_ind NOT IN ( 'Y' , 'N' ) THEN
90 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
91 IGS_GE_MSG_STACK.ADD;
92 App_Exception.Raise_Exception;
93 END IF;
94 END IF;
95 IF ((UPPER (column_name) = 'BULK_JOB_IND') OR (column_name IS NULL)) THEN
96 IF new_references.bulk_job_ind NOT IN ( 'Y' , 'N' ) THEN
97 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END IF;
101 END IF;
102
103 IF ((UPPER (column_name) = 'ENR_METHOD_TYPE') OR (column_name IS NULL)) THEN
104 IF (new_references.enr_method_type <> UPPER (new_references.enr_method_type)) THEN
105 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END IF;
109 END IF;
110
111 IF ((UPPER (column_name) = 'TRANSFER_FLAG') OR (column_name IS NULL)) THEN
112 IF new_references.transfer_flag NOT IN ( 'Y' , 'N' ) THEN
113 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
114 IGS_GE_MSG_STACK.ADD;
115 App_Exception.Raise_Exception;
116 END IF;
117 END IF;
118
119 IF ((UPPER (column_name) = 'DFLT_TRANS_DETAILS_FLAG') OR (column_name IS NULL)) THEN
120 IF new_references.dflt_trans_details_flag NOT IN ( 'Y' , 'N' ) THEN
121 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125 END IF;
126
127
128 END Check_Constraints;
129
130 PROCEDURE Check_Child_Existance AS
131 BEGIN
132
133 IGS_EN_CAT_PRC_DTL_PKG.GET_FK_IGS_EN_METHOD_TYPE (
134 old_references.enr_method_type
135 );
136
137 igs_en_cpd_ext_pkg.get_fk_igs_en_method_type(
138 old_references.enr_method_type
139 );
140 END Check_Child_Existance;
141
142 FUNCTION Get_PK_For_Validation (
143 x_enr_method_type IN VARCHAR2
144 ) RETURN BOOLEAN as
145
146 CURSOR cur_rowid IS
147 SELECT rowid
148 FROM IGS_EN_METHOD_TYPE
149 WHERE enr_method_type = x_enr_method_type
150 FOR UPDATE NOWAIT;
151
152 lv_rowid cur_rowid%RowType;
153
154 BEGIN
155
156 Open cur_rowid;
157 Fetch cur_rowid INTO lv_rowid;
158
159 IF (cur_rowid%FOUND) THEN
160 Close cur_rowid;
161 Return(TRUE);
162 ELSE
163 Close cur_rowid;
164 Return(FALSE);
165 END IF;
166
167 END Get_PK_For_Validation;
168
169 PROCEDURE Before_DML (
170 p_action IN VARCHAR2,
171 x_rowid IN VARCHAR2,
172 x_enr_method_type IN VARCHAR2,
173 x_description IN VARCHAR2,
174 x_closed_ind IN VARCHAR2,
175 x_creation_date IN DATE,
176 x_created_by IN NUMBER,
177 x_last_update_date IN DATE,
178 x_last_updated_by IN NUMBER,
179 x_last_update_login IN NUMBER,
180 x_self_service IN VARCHAR2,
181 x_ivr_display_ind IN VARCHAR2,
182 x_bulk_job_ind IN VARCHAR2,
183 x_transfer_flag IN VARCHAR2,
184 x_dflt_trans_details_flag IN VARCHAR2
185 ) AS
186 BEGIN
187
188 Set_Column_Values (
189 p_action,
190 x_rowid,
191 x_enr_method_type,
192 x_description,
193 x_closed_ind,
194 x_creation_date,
195 x_created_by,
196 x_last_update_date,
197 x_last_updated_by,
198 x_last_update_login,
199 x_self_service ,
200 x_ivr_display_ind ,
201 x_bulk_job_ind,
202 x_transfer_flag,
203 x_dflt_trans_details_flag
204 );
205
206 IF (p_action = 'INSERT') THEN
207 -- Call all the procedures related to Before Insert.
208 IF Get_PK_For_Validation(
209 new_references.enr_method_type) THEN
210
211 Fnd_message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
212 IGS_GE_MSG_STACK.ADD;
213 App_Exception.Raise_Exception;
214
215 END IF;
216
217 Check_Constraints;
218 ELSIF (p_action = 'UPDATE') THEN
219 -- Call all the procedures related to Before Update.
220 Check_Constraints;
221 ELSIF (p_action = 'DELETE') THEN
222 -- Call all the procedures related to Before Delete.
223 Check_Child_Existance;
224
225 ELSIF (p_action = 'VALIDATE_INSERT') THEN
226 IF Get_PK_For_Validation ( new_references.enr_method_type) THEN
227 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
228 IGS_GE_MSG_STACK.ADD;
229 App_Exception.Raise_Exception;
230 END IF;
231 Check_Constraints;
232 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
233 Check_Constraints;
234 ELSIF (p_action = 'VALIDATE_DELETE') THEN
235
236 Check_Child_Existance;
237 END IF;
238
239 END Before_DML;
240
241 PROCEDURE After_DML (
242 p_action IN VARCHAR2,
243 x_rowid IN VARCHAR2
244 ) AS
245 BEGIN
246
247 l_rowid := x_rowid;
248
249 IF (p_action = 'INSERT') THEN
250 -- Call all the procedures related to After Insert.
251 Null;
252 ELSIF (p_action = 'UPDATE') THEN
253 -- Call all the procedures related to After Update.
254 Null;
255 ELSIF (p_action = 'DELETE') THEN
256 -- Call all the procedures related to After Delete.
257 Null;
258 END IF;
259
260 END After_DML;
261
262 procedure INSERT_ROW (
263 X_ROWID IN OUT NOCOPY VARCHAR2,
264 X_ENR_METHOD_TYPE IN VARCHAR2,
265 X_DESCRIPTION IN VARCHAR2,
266 X_CLOSED_IND IN VARCHAR2,
267 X_MODE IN VARCHAR2,
268 X_SELF_SERVICE IN VARCHAR2,
269 X_IVR_DISPLAY_IND IN VARCHAR2,
270 X_BULK_JOB_IND IN VARCHAR2,
271 X_TRANSFER_FLAG IN VARCHAR2,
272 X_DFLT_TRANS_DETAILS_FLAG IN VARCHAR2
273 ) AS
274 cursor C is select ROWID from IGS_EN_METHOD_TYPE
275 where ENR_METHOD_TYPE = X_ENR_METHOD_TYPE;
276 X_LAST_UPDATE_DATE DATE;
277 X_LAST_UPDATED_BY NUMBER;
278 X_LAST_UPDATE_LOGIN NUMBER;
279 begin
280 X_LAST_UPDATE_DATE := SYSDATE;
281 if(X_MODE = 'I') then
282 X_LAST_UPDATED_BY := 1;
283 X_LAST_UPDATE_LOGIN := 0;
284 elsif (X_MODE = 'R') then
285 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
286 if X_LAST_UPDATED_BY is NULL then
287 X_LAST_UPDATED_BY := -1;
288 end if;
289 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
290 if X_LAST_UPDATE_LOGIN is NULL then
291 X_LAST_UPDATE_LOGIN := -1;
292 end if;
293 else
294 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
295 IGS_GE_MSG_STACK.ADD;
296 app_exception.raise_exception;
297 end if;
298 Before_DML (
299 p_action =>'INSERT',
300 x_rowid => X_ROWID,
301 x_enr_method_type => X_ENR_METHOD_TYPE,
302 x_description => X_DESCRIPTION,
303 x_closed_ind => NVL(X_CLOSED_IND,'N'),
304 x_creation_date => X_LAST_UPDATE_DATE,
305 x_created_by => X_LAST_UPDATED_BY,
306 x_last_update_date => X_LAST_UPDATE_DATE,
307 x_last_updated_by => X_LAST_UPDATED_BY,
308 x_last_update_login => X_LAST_UPDATE_LOGIN,
309 x_self_service => NVL(X_SELF_SERVICE,'N'),
310 x_ivr_display_ind => NVL(X_ivr_display_ind,'N'),
311 x_bulk_job_ind => NVL(x_bulk_job_ind,'N'),
312 x_transfer_flag => x_transfer_flag,
313 x_dflt_trans_details_flag => x_dflt_trans_details_flag
314 );
315 insert into IGS_EN_METHOD_TYPE (
316 ENR_METHOD_TYPE,
317 DESCRIPTION,
318 CLOSED_IND,
319 CREATION_DATE,
320 CREATED_BY,
321 LAST_UPDATE_DATE,
322 LAST_UPDATED_BY,
323 LAST_UPDATE_LOGIN,
324 SELF_SERVICE,
325 IVR_DISPLAY_IND,
326 BULK_JOB_IND,
327 TRANSFER_FLAG,
328 DFLT_TRANS_DETAILS_FLAG
329 ) values (
330 NEW_REFERENCES.ENR_METHOD_TYPE,
331 NEW_REFERENCES.DESCRIPTION,
332 NEW_REFERENCES.CLOSED_IND,
333 X_LAST_UPDATE_DATE,
334 X_LAST_UPDATED_BY,
335 X_LAST_UPDATE_DATE,
336 X_LAST_UPDATED_BY,
337 X_LAST_UPDATE_LOGIN,
338 NEW_REFERENCES.SELF_SERVICE,
339 NEW_REFERENCES.IVR_DISPLAY_IND,
340 NEW_REFERENCES.BULK_JOB_IND,
341 NEW_REFERENCES.TRANSFER_FLAG,
342 NEW_REFERENCES.DFLT_TRANS_DETAILS_FLAG
343 );
344
345 open c;
346 fetch c into X_ROWID;
347 if (c%notfound) then
348 close c;
349 raise no_data_found;
350 end if;
351 close c;
352 After_DML (
353 p_action =>'INSERT',
354 x_rowid => X_ROWID
355 );
356
357 end INSERT_ROW;
358
359 procedure LOCK_ROW (
360 X_ROWID IN VARCHAR2,
361 X_ENR_METHOD_TYPE IN VARCHAR2,
362 X_DESCRIPTION IN VARCHAR2,
363 X_CLOSED_IND IN VARCHAR2,
364 X_SELF_SERVICE IN VARCHAR2,
365 X_IVR_DISPLAY_IND IN VARCHAR2,
366 X_BULK_JOB_IND IN VARCHAR2,
367 X_TRANSFER_FLAG IN VARCHAR2,
368 X_DFLT_TRANS_DETAILS_FLAG IN VARCHAR2
369 ) AS
370 cursor c1 is select
371 DESCRIPTION,
372 BULK_JOB_IND,
373 CLOSED_IND,
374 SELF_SERVICE,
375 IVR_DISPLAY_IND,
376 TRANSFER_FLAG,
377 DFLT_TRANS_DETAILS_FLAG
378 from IGS_EN_METHOD_TYPE
379 where ROWID = X_ROWID
380 for update nowait;
381 tlinfo c1%rowtype;
382
383 begin
384 open c1;
385 fetch c1 into tlinfo;
386 if (c1%notfound) then
387 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
388 IGS_GE_MSG_STACK.ADD;
389 close c1;
390 app_exception.raise_exception;
391 return;
392 end if;
393 close c1;
394
395 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
396 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
397 AND (tlinfo.BULK_JOB_IND = X_BULK_JOB_IND)
398 AND ((tlinfo.SELF_SERVICE = X_SELF_SERVICE)
399 OR ((tlinfo.SELF_SERVICE is null)
400 AND (X_SELF_SERVICE is null)))
401 AND ((tlinfo.IVR_DISPLAY_IND = X_IVR_DISPLAY_IND)
402 OR ((tlinfo.IVR_DISPLAY_IND is null)
403 AND (X_IVR_DISPLAY_IND is null)))
404 AND ((tlinfo.TRANSFER_FLAG = X_TRANSFER_FLAG)
405 OR ((tlinfo.TRANSFER_FLAG is null)
406 AND (X_TRANSFER_FLAG is null)))
407 AND ((tlinfo.DFLT_TRANS_DETAILS_FLAG = X_DFLT_TRANS_DETAILS_FLAG)
408 OR ((tlinfo.DFLT_TRANS_DETAILS_FLAG is null)
409 AND (X_DFLT_TRANS_DETAILS_FLAG is null)))
410 ) then
411 null;
412 else
413 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
414 IGS_GE_MSG_STACK.ADD;
415 app_exception.raise_exception;
416 end if;
417 return;
418 end LOCK_ROW;
419
420 procedure UPDATE_ROW (
421 X_ROWID IN VARCHAR2,
422 X_ENR_METHOD_TYPE IN VARCHAR2,
423 X_DESCRIPTION IN VARCHAR2,
424 X_CLOSED_IND IN VARCHAR2,
425 X_MODE IN VARCHAR2,
426 X_SELF_SERVICE IN VARCHAR2,
427 X_IVR_DISPLAY_IND IN VARCHAR2,
428 X_BULK_JOB_IND IN VARCHAR2,
429 X_TRANSFER_FLAG IN VARCHAR2,
430 X_DFLT_TRANS_DETAILS_FLAG IN VARCHAR2
431 ) AS
432 X_LAST_UPDATE_DATE DATE;
433 X_LAST_UPDATED_BY NUMBER;
434 X_LAST_UPDATE_LOGIN NUMBER;
435 begin
436 X_LAST_UPDATE_DATE := SYSDATE;
437 if(X_MODE = 'I') then
438 X_LAST_UPDATED_BY := 1;
439 X_LAST_UPDATE_LOGIN := 0;
440 elsif (X_MODE = 'R') then
441 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
442 if X_LAST_UPDATED_BY is NULL then
443 X_LAST_UPDATED_BY := -1;
444 end if;
445 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
446 if X_LAST_UPDATE_LOGIN is NULL then
447 X_LAST_UPDATE_LOGIN := -1;
448 end if;
449 else
450 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
451 IGS_GE_MSG_STACK.ADD;
452 app_exception.raise_exception;
453 end if;
454 Before_DML (
455 p_action =>'UPDATE',
456 x_rowid => X_ROWID,
457 x_enr_method_type => X_ENR_METHOD_TYPE,
458 x_description => X_DESCRIPTION,
459 x_closed_ind => X_CLOSED_IND,
460 x_creation_date => X_LAST_UPDATE_DATE,
461 x_created_by => X_LAST_UPDATED_BY,
462 x_last_update_date => X_LAST_UPDATE_DATE,
463 x_last_updated_by => X_LAST_UPDATED_BY,
464 x_last_update_login => X_LAST_UPDATE_LOGIN,
465 x_self_service => X_SELF_SERVICE,
466 x_ivr_display_ind => X_IVR_DISPLAY_IND,
467 x_bulk_job_ind => X_BULK_JOB_IND,
468 x_transfer_flag => x_transfer_flag,
469 x_dflt_trans_details_flag => x_dflt_trans_details_flag
470 );
471 update IGS_EN_METHOD_TYPE set
472 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
473 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
474 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
475 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
476 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
477 SELF_SERVICE = X_SELF_SERVICE,
478 IVR_DISPLAY_IND = X_IVR_DISPLAY_IND,
479 BULK_JOB_IND = X_BULK_JOB_IND,
480 TRANSFER_FLAG = X_TRANSFER_FLAG,
481 DFLT_TRANS_DETAILS_FLAG = X_DFLT_TRANS_DETAILS_FLAG
482 where ROWID = X_ROWID
483 ;
484 if (sql%notfound) then
485 raise no_data_found;
486 end if;
487 After_DML (
488 p_action =>'UPDATE',
489 x_rowid => X_ROWID
490 );
491 end UPDATE_ROW;
492
493 procedure ADD_ROW (
494 X_ROWID IN OUT NOCOPY VARCHAR2,
495 X_ENR_METHOD_TYPE IN VARCHAR2,
496 X_DESCRIPTION IN VARCHAR2,
497 X_CLOSED_IND IN VARCHAR2,
498 X_MODE IN VARCHAR2,
499 X_SELF_SERVICE IN VARCHAR2,
500 X_IVR_DISPLAY_IND IN VARCHAR2,
501 X_BULK_JOB_IND IN VARCHAR2,
502 X_TRANSFER_FLAG IN VARCHAR2,
503 X_DFLT_TRANS_DETAILS_FLAG IN VARCHAR2
504 ) AS
505 cursor c1 is select rowid from IGS_EN_METHOD_TYPE
506 where ENR_METHOD_TYPE = X_ENR_METHOD_TYPE
507 ;
508 begin
509 open c1;
510 fetch c1 into X_ROWID;
511 if (c1%notfound) then
512 close c1;
513 INSERT_ROW (
514 X_ROWID,
515 X_ENR_METHOD_TYPE,
516 X_DESCRIPTION,
517 X_CLOSED_IND,
518 X_MODE,
519 X_SELF_SERVICE,
520 X_IVR_DISPLAY_IND,
521 X_BULK_JOB_IND,
522 X_TRANSFER_FLAG,
523 X_DFLT_TRANS_DETAILS_FLAG
524 );
525 return;
526 end if;
527 close c1;
528 UPDATE_ROW (
529 X_ROWID,
530 X_ENR_METHOD_TYPE,
531 X_DESCRIPTION,
532 X_CLOSED_IND,
533 X_MODE,
534 X_SELF_SERVICE,
535 X_IVR_DISPLAY_IND,
536 X_BULK_JOB_IND,
537 X_TRANSFER_FLAG,
538 X_DFLT_TRANS_DETAILS_FLAG
539 );
540 end ADD_ROW;
541
542 procedure DELETE_ROW (
543 X_ROWID in VARCHAR2
544 ) AS
545 begin
546 Before_DML (
547 p_action =>'DELETE',
548 x_rowid => X_ROWID
549 );
550 delete from IGS_EN_METHOD_TYPE
551 where ROWID = X_ROWID;
552 if (sql%notfound) then
553 raise no_data_found;
554 end if;
555 After_DML (
556 p_action =>'DELETE',
557 x_rowid => X_ROWID
558 );
559 end DELETE_ROW;
560
561 end IGS_EN_METHOD_TYPE_PKG;