[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_NOTE_TYPE_PKG
Source
1 package body IGS_EN_NOTE_TYPE_PKG AS
2 /* $Header: IGSEI21B.pls 120.1 2005/09/08 14:24:25 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_EN_NOTE_TYPE_ALL%RowType;
5 new_references IGS_EN_NOTE_TYPE_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_enr_note_type IN VARCHAR2 DEFAULT NULL,
11 x_description IN VARCHAR2 DEFAULT NULL,
12 x_s_enr_note_type 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_EN_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.enr_note_type := x_enr_note_type;
45 new_references.description := x_description;
46 new_references.s_enr_note_type := x_s_enr_note_type;
47 IF (p_action = 'UPDATE') THEN
48 new_references.creation_date := old_references.creation_date;
49 new_references.created_by := old_references.created_by;
50 ELSE
51 new_references.creation_date := x_creation_date;
52 new_references.created_by := x_created_by;
53 END IF;
54 new_references.last_update_date := x_last_update_date;
55 new_references.last_updated_by := x_last_updated_by;
56 new_references.last_update_login := x_last_update_login;
57 new_references.org_id := x_org_id;
58 END Set_Column_Values;
59
60 procedure Check_constraints(
61 column_name IN VARCHAR2 DEFAULT NULL,
62 column_value IN VARCHAR2 DEFAULT NULL
63 ) AS
64 begin
65 IF column_name is null then
66 NULL;
67 ELSIF upper(column_name) = 'ENR_NOTE_TYPE' then
68 new_references.enr_note_type := column_value;
69 ELSIF upper(column_name) = 'S_ENR_NOTE_TYPE' then
70 new_references.s_enr_note_type := column_value;
71 END IF;
72
73 IF upper(column_name) = 'ENR_NOTE_TYPE' OR
74 column_name is null then
75 if new_references.enr_note_type <> upper(new_references.enr_note_type) then
76 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
77 IGS_GE_MSG_STACK.ADD;
78 App_Exception.Raise_Exception;
79 END IF;
80 end if;
81 IF upper(column_name) = 'S_ENR_NOTE_TYPE' OR
82 column_name is null then
83 if new_references.s_enr_note_type <> upper(new_references.s_enr_note_type) then
84 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
85 IGS_GE_MSG_STACK.ADD;
86 App_Exception.Raise_Exception;
87 END IF;
88 end if;
89
90 END check_constraints;
91
92 PROCEDURE Check_Parent_Existance AS
93 BEGIN
94
95 IF (((old_references.s_enr_note_type = new_references.s_enr_note_type)) OR
96 ((new_references.s_enr_note_type IS NULL))) THEN
97 NULL;
98 ELSE
99 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation (
100 'ENR_NOTE_TYPE',
101 new_references.s_enr_note_type
102 ) THEN
103 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 end if;
107 END IF;
108
109 END Check_Parent_Existance;
110
111 PROCEDURE Check_Child_Existance AS
112 BEGIN
113
114 IGS_AS_SC_ATMPT_NOTE_PKG.GET_FK_IGS_EN_NOTE_TYPE (
115 old_references.enr_note_type
116 );
117
118 END Check_Child_Existance;
119
120 FUNCTION Get_PK_For_Validation (
121 x_enr_note_type IN VARCHAR2
122 ) RETURN BOOLEAN AS
123
124 CURSOR cur_rowid IS
125 SELECT rowid
126 FROM IGS_EN_NOTE_TYPE_ALL
127 WHERE enr_note_type = x_enr_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 GET_FK_IGS_LOOKUPS_VIEW (
147 x_s_enr_note_type IN VARCHAR2
148 ) AS
149
150 CURSOR cur_rowid IS
151 SELECT rowid
152 FROM IGS_EN_NOTE_TYPE_ALL
153 WHERE s_enr_note_type = x_s_enr_note_type ;
154
155 lv_rowid cur_rowid%RowType;
156
157 BEGIN
158
159 Open cur_rowid;
160 Fetch cur_rowid INTO lv_rowid;
161 IF (cur_rowid%FOUND) THEN
162 Close cur_rowid;
163 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ENT_LKUPV_FK');
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 Return;
167 END IF;
168 Close cur_rowid;
169
170 END GET_FK_IGS_LOOKUPS_VIEW;
171
172 PROCEDURE Before_DML (
173 p_action IN VARCHAR2,
174 x_rowid IN VARCHAR2 DEFAULT NULL,
175 x_enr_note_type IN VARCHAR2 DEFAULT NULL,
176 x_description IN VARCHAR2 DEFAULT NULL,
177 x_s_enr_note_type IN VARCHAR2 DEFAULT NULL,
178 x_creation_date IN DATE DEFAULT NULL,
179 x_created_by IN NUMBER DEFAULT NULL,
180 x_last_update_date IN DATE DEFAULT NULL,
181 x_last_updated_by IN NUMBER DEFAULT NULL,
182 x_last_update_login IN NUMBER DEFAULT NULL,
183 x_org_id IN NUMBER DEFAULT NULL
184 ) AS
185 BEGIN
186
187 Set_Column_Values (
188 p_action,
189 x_rowid,
190 x_enr_note_type,
191 x_description,
192 x_s_enr_note_type,
193 x_creation_date,
194 x_created_by,
195 x_last_update_date,
196 x_last_updated_by,
197 x_last_update_login,
198 x_org_id
199 );
200
201 IF (p_action = 'INSERT') THEN
202 -- Call all the procedures related to Before Insert.
203 IF Get_PK_For_Validation (
204 new_references.enr_note_type
205 ) then
206 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 end if;
210 Check_constraints;
211 Check_Parent_Existance;
212 ELSIF (p_action = 'UPDATE') THEN
213 -- Call all the procedures related to Before Update.
214 Check_constraints;
215 Check_Parent_Existance;
216 ELSIF (p_action = 'DELETE') THEN
217 -- Call all the procedures related to Before Delete.
218 Check_Child_Existance;
219 ELSIF (p_action = 'VALIDATE_INSERT') then
220 IF Get_PK_For_Validation (
221 new_references.enr_note_type
222 ) then
223 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
224 IGS_GE_MSG_STACK.ADD;
225 App_Exception.Raise_Exception;
226 end if;
227 Check_constraints;
228 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
229 Check_constraints;
230 ELSIF (p_action = 'VALIDATE_DELETE') THEN
231 Check_Child_Existance;
232 END IF;
233
234 END Before_DML;
235
236 PROCEDURE After_DML (
237 p_action IN VARCHAR2,
238 x_rowid IN VARCHAR2
239 ) AS
240 BEGIN
241
242 l_rowid := x_rowid;
243
244 IF (p_action = 'INSERT') THEN
245 -- Call all the procedures related to After Insert.
246 Null;
247 ELSIF (p_action = 'UPDATE') THEN
248 -- Call all the procedures related to After Update.
249 Null;
250 ELSIF (p_action = 'DELETE') THEN
251 -- Call all the procedures related to After Delete.
252 Null;
253 END IF;
254
255 END After_DML;
256 procedure INSERT_ROW (
257 X_ROWID in out NOCOPY VARCHAR2,
258 X_ENR_NOTE_TYPE in VARCHAR2,
259 X_DESCRIPTION in VARCHAR2,
260 X_S_ENR_NOTE_TYPE in VARCHAR2,
261 X_MODE in VARCHAR2 default 'R',
262 x_org_id IN NUMBER
263 ) AS
264 cursor C is select ROWID from IGS_EN_NOTE_TYPE_ALL
265 where ENR_NOTE_TYPE = X_ENR_NOTE_TYPE;
266 X_LAST_UPDATE_DATE DATE;
267 X_LAST_UPDATED_BY NUMBER;
268 X_LAST_UPDATE_LOGIN NUMBER;
269 begin
270 X_LAST_UPDATE_DATE := SYSDATE;
271 if(X_MODE = 'I') then
272 X_LAST_UPDATED_BY := 1;
273 X_LAST_UPDATE_LOGIN := 0;
274 elsif (X_MODE = 'R') then
275 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
276 if X_LAST_UPDATED_BY is NULL then
277 X_LAST_UPDATED_BY := -1;
278 end if;
279 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
280 if X_LAST_UPDATE_LOGIN is NULL then
281 X_LAST_UPDATE_LOGIN := -1;
282 end if;
283 else
284 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
285 IGS_GE_MSG_STACK.ADD;
286 app_exception.raise_exception;
287 end if;
288 Before_DML (
289 p_action => 'INSERT',
290 x_rowid => X_ROWID,
291 x_enr_note_type => X_ENR_NOTE_TYPE,
292 x_description => X_DESCRIPTION,
293 x_s_enr_note_type => X_S_ENR_NOTE_TYPE,
294 x_last_update_date => X_LAST_UPDATE_DATE,
295 x_last_updated_by => X_LAST_UPDATED_BY,
296 x_last_update_login => X_LAST_UPDATE_LOGIN,
297 x_org_id => igs_ge_gen_003.get_org_id
298 );
299 insert into IGS_EN_NOTE_TYPE_ALL (
300 ENR_NOTE_TYPE,
301 DESCRIPTION,
302 S_ENR_NOTE_TYPE,
303 CREATION_DATE,
304 CREATED_BY,
305 LAST_UPDATE_DATE,
306 LAST_UPDATED_BY,
307 LAST_UPDATE_LOGIN,
308 org_id
309 ) values (
310 NEW_REFERENCES.ENR_NOTE_TYPE,
311 NEW_REFERENCES.DESCRIPTION,
312 NEW_REFERENCES.S_ENR_NOTE_TYPE,
313 X_LAST_UPDATE_DATE,
314 X_LAST_UPDATED_BY,
315 X_LAST_UPDATE_DATE,
316 X_LAST_UPDATED_BY,
317 X_LAST_UPDATE_LOGIN,
318 NEW_REFERENCES.org_id
319 );
320
321 open c;
322 fetch c into X_ROWID;
323 if (c%notfound) then
324 close c;
325 raise no_data_found;
326 end if;
327 close c;
328 After_DML (
329 p_action => 'INSERT',
330 x_rowid => X_ROWID
331 );
332
333 end INSERT_ROW;
334
335 procedure LOCK_ROW (
336 X_ROWID in VARCHAR2,
337 X_ENR_NOTE_TYPE in VARCHAR2,
338 X_DESCRIPTION in VARCHAR2,
339 X_S_ENR_NOTE_TYPE in VARCHAR2
340 ) AS
341 cursor c1 is select
342 DESCRIPTION,
343 S_ENR_NOTE_TYPE
344 from IGS_EN_NOTE_TYPE_ALL
345 where ROWID = X_ROWID for update nowait;
346 tlinfo c1%rowtype;
347
348 begin
349 open c1;
350 fetch c1 into tlinfo;
351 if (c1%notfound) then
352 close c1;
353 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
354 IGS_GE_MSG_STACK.ADD;
355 app_exception.raise_exception;
356 return;
357 end if;
358 close c1;
359
360 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
361 AND ((tlinfo.S_ENR_NOTE_TYPE = X_S_ENR_NOTE_TYPE)
362 OR ((tlinfo.S_ENR_NOTE_TYPE is null)
363 AND (X_S_ENR_NOTE_TYPE is null)))
364 ) then
365 null;
366 else
367 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
368 IGS_GE_MSG_STACK.ADD;
369 app_exception.raise_exception;
370 end if;
371 return;
372 end LOCK_ROW;
373
374 procedure UPDATE_ROW (
375 X_ROWID in VARCHAR2,
376 X_ENR_NOTE_TYPE in VARCHAR2,
377 X_DESCRIPTION in VARCHAR2,
378 X_S_ENR_NOTE_TYPE in VARCHAR2,
379 X_MODE in VARCHAR2 default 'R'
380 ) AS
381 X_LAST_UPDATE_DATE DATE;
382 X_LAST_UPDATED_BY NUMBER;
383 X_LAST_UPDATE_LOGIN NUMBER;
384 begin
385 X_LAST_UPDATE_DATE := SYSDATE;
386 if(X_MODE = 'I') then
387 X_LAST_UPDATED_BY := 1;
388 X_LAST_UPDATE_LOGIN := 0;
389 elsif (X_MODE = 'R') then
390 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
391 if X_LAST_UPDATED_BY is NULL then
392 X_LAST_UPDATED_BY := -1;
393 end if;
394 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
395 if X_LAST_UPDATE_LOGIN is NULL then
396 X_LAST_UPDATE_LOGIN := -1;
397 end if;
398 else
399 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
400 IGS_GE_MSG_STACK.ADD;
401 app_exception.raise_exception;
402 end if;
403 Before_DML (
404 p_action => 'UPDATE',
405 x_rowid => X_ROWID,
406 x_enr_note_type => X_ENR_NOTE_TYPE,
407 x_description => X_DESCRIPTION,
408 x_s_enr_note_type => X_S_ENR_NOTE_TYPE,
409 x_last_update_date => X_LAST_UPDATE_DATE,
410 x_last_updated_by => X_LAST_UPDATED_BY,
411 x_last_update_login => X_LAST_UPDATE_LOGIN
412 );
413 update IGS_EN_NOTE_TYPE_ALL set
414 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
415 S_ENR_NOTE_TYPE = NEW_REFERENCES.S_ENR_NOTE_TYPE,
416 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
417 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
418 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
419 where ROWID = X_ROWID;
420 if (sql%notfound) then
421 raise no_data_found;
422 end if;
423 After_DML (
424 p_action => 'UPDATE',
425 x_rowid => X_ROWID
426 );
427 end UPDATE_ROW;
428
429 procedure ADD_ROW (
430 X_ROWID in out NOCOPY VARCHAR2,
431 X_ENR_NOTE_TYPE in VARCHAR2,
432 X_DESCRIPTION in VARCHAR2,
433 X_S_ENR_NOTE_TYPE in VARCHAR2,
434 X_MODE in VARCHAR2 default 'R',
435 x_org_id IN NUMBER
436 ) AS
437 cursor c1 is select rowid from IGS_EN_NOTE_TYPE_ALL
438 where ENR_NOTE_TYPE = X_ENR_NOTE_TYPE
439 ;
440
441 begin
442 open c1;
443 fetch c1 into X_ROWID;
444 if (c1%notfound) then
445 close c1;
446 INSERT_ROW (
447 X_ROWID,
448 X_ENR_NOTE_TYPE,
449 X_DESCRIPTION,
450 X_S_ENR_NOTE_TYPE,
451 X_MODE,
452 x_org_id);
453 return;
454 end if;
455 close c1;
456 UPDATE_ROW (
457 X_ROWID,
458 X_ENR_NOTE_TYPE,
459 X_DESCRIPTION,
460 X_S_ENR_NOTE_TYPE,
461 X_MODE);
462 end ADD_ROW;
463
464 procedure DELETE_ROW (X_ROWID in VARCHAR2
465 ) AS
466 begin
467 Before_DML (
468 p_action => 'DELETE',
469 x_rowid => X_ROWID
470 );
471 delete from IGS_EN_NOTE_TYPE_ALL
472 where ROWID = X_ROWID;
473 if (sql%notfound) then
474 raise no_data_found;
475 end if;
476 After_DML (
477 p_action => 'DELETE',
478 x_rowid => X_ROWID
479 );
480 end DELETE_ROW;
481
482 end IGS_EN_NOTE_TYPE_PKG;