[Home] [Help]
PACKAGE BODY: APPS.IGS_TR_NOTE_TYPE_PKG
Source
1 PACKAGE BODY igs_tr_note_type_pkg AS
2 /* $Header: IGSTI08B.pls 115.5 2002/11/29 04:15:54 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_tr_note_type%ROWTYPE;
6 new_references igs_tr_note_type%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_trk_note_type IN VARCHAR2 DEFAULT NULL,
12 x_description 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 igs_tr_note_type
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 CLOSE cur_old_ref_values;
35 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
36 igs_ge_msg_stack.add;
37 app_exception.raise_exception;
38 RETURN;
39 END IF;
40 CLOSE cur_old_ref_values;
41
42 -- Populate New Values.
43 new_references.trk_note_type := x_trk_note_type;
44 new_references.description := x_description;
45 IF (p_action = 'UPDATE') THEN
46 new_references.creation_date := old_references.creation_date;
47 new_references.created_by := old_references.created_by;
48 ELSE
49 new_references.creation_date := x_creation_date;
50 new_references.created_by := x_created_by;
51 END IF;
52 new_references.last_update_date := x_last_update_date;
53 new_references.last_updated_by := x_last_updated_by;
54 new_references.last_update_login := x_last_update_login;
55
56 END set_column_values;
57
58 PROCEDURE check_constraints (
59 column_name IN VARCHAR2 DEFAULT NULL,
60 column_value IN VARCHAR2 DEFAULT NULL
61 )AS
62 BEGIN
63
64 IF column_name IS NULL THEN
65 NULL;
66 ELSIF UPPER(column_name) = 'TRK_NOTE_TYPE' THEN
67 new_references.trk_note_type:= column_value ;
68 END IF ;
69
70 IF UPPER(column_name) = 'TRK_NOTE_TYPE' OR column_name IS NULL THEN
71 IF new_references.trk_note_type<> UPPER(new_references.trk_note_type) THEN
72 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
73 igs_ge_msg_stack.add;
74 app_exception.raise_exception ;
75 END IF;
76 END IF ;
77
78 END check_constraints;
79
80 PROCEDURE check_child_existance AS
81 BEGIN
82
83 igs_tr_group_note_pkg.get_fk_igs_tr_note_type (
84 old_references.trk_note_type
85 );
86
87 igs_tr_item_note_pkg.get_fk_igs_tr_note_type (
88 old_references.trk_note_type
89 );
90
91 igs_tr_step_note_pkg.get_fk_igs_tr_note_type (
92 old_references.trk_note_type
93 );
94
95 igs_tr_typ_step_note_pkg.get_fk_igs_tr_note_type (
96 old_references.trk_note_type
97 );
98
99 END check_child_existance;
100
101 FUNCTION get_pk_for_validation (
102 x_trk_note_type IN VARCHAR2
103 )RETURN BOOLEAN AS
104
105 CURSOR cur_rowid IS
106 SELECT ROWID
107 FROM igs_tr_note_type
108 WHERE trk_note_type = x_trk_note_type
109 FOR UPDATE NOWAIT;
110
111 lv_rowid cur_rowid%ROWTYPE;
112
113 BEGIN
114
115 OPEN cur_rowid;
116 FETCH cur_rowid INTO lv_rowid;
117 IF (cur_rowid%FOUND) THEN
118 CLOSE cur_rowid;
119 RETURN (TRUE);
120 ELSE
121 CLOSE cur_rowid;
122 RETURN (FALSE);
123 END IF;
124
125 END get_pk_for_validation;
126
127 PROCEDURE before_dml (
128 p_action IN VARCHAR2,
129 x_rowid IN VARCHAR2 DEFAULT NULL,
130 x_trk_note_type IN VARCHAR2 DEFAULT NULL,
131 x_description IN VARCHAR2 DEFAULT NULL,
132 x_creation_date IN DATE DEFAULT NULL,
133 x_created_by IN NUMBER DEFAULT NULL,
134 x_last_update_date IN DATE DEFAULT NULL,
135 x_last_updated_by IN NUMBER DEFAULT NULL,
136 x_last_update_login IN NUMBER DEFAULT NULL
137 ) AS
138 BEGIN
139
140 set_column_values (
141 p_action,
142 x_rowid,
143 x_trk_note_type,
144 x_description,
145 x_creation_date,
146 x_created_by,
147 x_last_update_date,
148 x_last_updated_by,
149 x_last_update_login
150 );
151
152 IF (p_action = 'INSERT') THEN
153 IF get_pk_for_validation (
154 new_references.trk_note_type
155 ) THEN
156 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
157 igs_ge_msg_stack.add;
158 app_exception.raise_exception;
159 END IF;
160 check_constraints;
161
162 ELSIF (p_action = 'UPDATE') THEN
163 check_constraints;
164
165 ELSIF (p_action = 'DELETE') THEN
166 check_child_existance;
167
168 ELSIF (p_action = 'VALIDATE_INSERT') THEN
169 IF get_pk_for_validation ( new_references.trk_note_type) THEN
170 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
171 igs_ge_msg_stack.add;
172 app_exception.raise_exception;
173 END IF;
174 check_constraints;
175
176 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
177 check_constraints;
178
179 ELSIF (p_action = 'VALIDATE_DELETE') THEN
180 check_child_existance;
181 END IF;
182
183 END before_dml;
184
185 PROCEDURE after_dml (
186 p_action IN VARCHAR2,
187 x_rowid IN VARCHAR2
188 ) AS
189 BEGIN
190
191 l_rowid := x_rowid;
192
193 END after_dml;
194
195 PROCEDURE insert_row (
196 x_rowid IN OUT NOCOPY VARCHAR2,
197 x_trk_note_type IN VARCHAR2,
198 x_description IN VARCHAR2,
199 x_mode IN VARCHAR2 DEFAULT 'R'
200 ) AS
201
202 CURSOR c IS
203 SELECT ROWID
204 FROM igs_tr_note_type
205 WHERE trk_note_type = x_trk_note_type;
206
207 x_last_update_date DATE;
208 x_last_updated_by NUMBER;
209 x_last_update_login NUMBER;
210
211 BEGIN
212 x_last_update_date := SYSDATE;
213
214 IF(x_mode = 'I') THEN
215 x_last_updated_by := 1;
216 x_last_update_login := 0;
217
218 ELSIF (x_mode = 'R') THEN
219 x_last_updated_by := fnd_global.user_id;
220 IF x_last_updated_by IS NULL THEN
221 x_last_updated_by := -1;
222 END IF;
223 x_last_update_login :=fnd_global.login_id;
224 IF x_last_update_login IS NULL THEN
225 x_last_update_login := -1;
226 END IF;
227
228 ELSE
229 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
230 igs_ge_msg_stack.add;
231 app_exception.raise_exception;
232 END IF;
233
234 before_dml(p_action =>'INSERT',
235 x_rowid =>x_rowid,
236 x_trk_note_type => x_trk_note_type,
237 x_description => x_description,
238 x_creation_date => x_last_update_date,
239 x_created_by => x_last_updated_by,
240 x_last_update_date => x_last_update_date,
241 x_last_updated_by => x_last_updated_by,
242 x_last_update_login => x_last_update_login
243 );
244
245 INSERT INTO igs_tr_note_type (
246 trk_note_type,
247 description,
248 creation_date,
249 created_by,
250 last_update_date,
251 last_updated_by,
252 last_update_login
253 ) VALUES (
254 new_references.trk_note_type,
255 new_references.description,
256 x_last_update_date,
257 x_last_updated_by,
258 x_last_update_date,
259 x_last_updated_by,
260 x_last_update_login
261 );
262
263 OPEN c;
264 FETCH c INTO x_rowid;
265 IF (c%NOTFOUND) THEN
266 CLOSE c;
267 RAISE no_data_found;
268 END IF;
269 CLOSE c;
270
271 after_dml(
272 p_action =>'INSERT',
273 x_rowid => x_rowid
274 );
275
276 END insert_row;
277
278 PROCEDURE lock_row (
279 x_rowid IN VARCHAR2,
280 x_trk_note_type IN VARCHAR2,
281 x_description IN VARCHAR2
282 ) AS
283
284 CURSOR c1 IS
285 SELECT description
286 FROM igs_tr_note_type
287 WHERE ROWID = x_rowid
288 FOR UPDATE NOWAIT;
289
290 tlinfo c1%ROWTYPE;
291
292 BEGIN
293
294 OPEN c1;
295 FETCH c1 INTO tlinfo;
296 IF (c1%NOTFOUND) THEN
297 CLOSE c1;
298 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
299 igs_ge_msg_stack.add;
300 app_exception.raise_exception;
301 RETURN;
302 END IF;
303 CLOSE c1;
304
305 IF ( (tlinfo.description = x_description) ) THEN
306 NULL;
307 ELSE
308 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
309 igs_ge_msg_stack.add;
310 app_exception.raise_exception;
311 END IF;
312 RETURN;
313
314 END lock_row;
315
316 PROCEDURE update_row (
317 x_rowid IN VARCHAR2,
318 x_trk_note_type IN VARCHAR2,
319 x_description IN VARCHAR2,
320 x_mode IN VARCHAR2 DEFAULT 'R'
321 ) AS
322
323 x_last_update_date DATE;
324 x_last_updated_by NUMBER;
325 x_last_update_login NUMBER;
326
327 BEGIN
328
329 x_last_update_date := SYSDATE;
330
331 IF(x_mode = 'I') THEN
332 x_last_updated_by := 1;
333 x_last_update_login := 0;
334
335 ELSIF (x_mode = 'R') THEN
336 x_last_updated_by := fnd_global.user_id;
337 IF x_last_updated_by IS NULL THEN
338 x_last_updated_by := -1;
339 END IF;
340 x_last_update_login :=fnd_global.login_id;
341 IF x_last_update_login IS NULL THEN
342 x_last_update_login := -1;
343 END IF;
344
345 ELSE
346 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
347 igs_ge_msg_stack.add;
348 app_exception.raise_exception;
349 END IF;
350
351 before_dml(p_action =>'UPDATE',
352 x_rowid =>x_rowid,
353 x_trk_note_type => x_trk_note_type,
354 x_description => x_description,
355 x_creation_date => x_last_update_date,
356 x_created_by => x_last_updated_by,
357 x_last_update_date => x_last_update_date,
358 x_last_updated_by => x_last_updated_by,
359 x_last_update_login => x_last_update_login
360 );
361
362 UPDATE igs_tr_note_type SET
363 description = new_references.description,
364 last_update_date = x_last_update_date,
365 last_updated_by = x_last_updated_by,
366 last_update_login = x_last_update_login
367 WHERE ROWID = x_rowid;
368
369 IF (SQL%NOTFOUND) THEN
370 RAISE no_data_found;
371 END IF;
372
373 after_dml(
374 p_action =>'UPDATE',
375 x_rowid => x_rowid
376 );
377
378 END update_row;
379
380 PROCEDURE add_row (
381 x_rowid IN OUT NOCOPY VARCHAR2,
382 x_trk_note_type IN VARCHAR2,
383 x_description IN VARCHAR2,
384 x_mode IN VARCHAR2 DEFAULT 'R'
385 ) AS
386
387 CURSOR c1 IS
388 SELECT ROWID
389 FROM igs_tr_note_type
390 WHERE trk_note_type = x_trk_note_type;
391 BEGIN
392 OPEN c1;
393 FETCH c1 INTO x_rowid;
394 IF (c1%NOTFOUND) THEN
395 CLOSE c1;
396 insert_row ( x_rowid, x_trk_note_type, x_description, x_mode);
397 RETURN;
398 END IF;
399 CLOSE c1;
400 update_row (
401 x_rowid,
402 x_trk_note_type,
403 x_description,
404 x_mode
405 );
406
407 END add_row;
408
409 PROCEDURE delete_row (
410 x_rowid IN VARCHAR2
411 ) AS
412
413 BEGIN
414
415 before_dml(
416 p_action =>'DELETE',
417 x_rowid => x_rowid
418 );
419
420 DELETE FROM igs_tr_note_type WHERE ROWID = x_rowid;
421 IF (SQL%NOTFOUND) THEN
422 RAISE no_data_found;
423 END IF;
424 after_dml(
425 p_action =>'DELETE',
426 x_rowid => x_rowid
427 );
428
429 END delete_row;
430
431 END igs_tr_note_type_pkg;