[Home] [Help]
PACKAGE BODY: APPS.IGS_TR_GROUP_NOTE_PKG
Source
1 PACKAGE BODY igs_tr_group_note_pkg AS
2 /* $Header: IGSTI11B.pls 115.5 2002/11/29 04:16:43 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_tr_group_note%ROWTYPE;
6 new_references igs_tr_group_note%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_tracking_group_id IN NUMBER DEFAULT NULL,
12 x_reference_number IN NUMBER DEFAULT NULL,
13 x_trk_note_type IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20
21 CURSOR cur_old_ref_values IS
22 SELECT *
23 FROM igs_tr_group_note
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
35 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
36 CLOSE cur_old_ref_values;
37 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
38 igs_ge_msg_stack.add;
39 app_exception.raise_exception;
40 RETURN;
41 END IF;
42
43 CLOSE cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.tracking_group_id := x_tracking_group_id;
47 new_references.reference_number := x_reference_number;
48 new_references.trk_note_type := x_trk_note_type;
49
50 IF (p_action = 'UPDATE') THEN
51 new_references.creation_date := old_references.creation_date;
52 new_references.created_by := old_references.created_by;
53
54 ELSE
55 new_references.creation_date := x_creation_date;
56 new_references.created_by := x_created_by;
57
58 END IF;
59 new_references.last_update_date := x_last_update_date;
60 new_references.last_updated_by := x_last_updated_by;
61 new_references.last_update_login := x_last_update_login;
62
63 END set_column_values;
64
65 PROCEDURE check_constraints (
66 column_name IN VARCHAR2 DEFAULT NULL,
67 column_value IN VARCHAR2 DEFAULT NULL
68 )AS
69 BEGIN
70
71 IF column_name IS NULL THEN
72 NULL;
73 ELSIF UPPER(column_name) = 'TRK_NOTE_TYPE' THEN
74 new_references.trk_note_type:= column_value ;
75 END IF ;
76
77 IF UPPER(column_name) = 'TRK_NOTE_TYPE' OR column_name IS NULL THEN
78 IF new_references.trk_note_type<> UPPER(new_references.trk_note_type) THEN
79 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
80 igs_ge_msg_stack.add;
81 app_exception.raise_exception ;
82 END IF;
83 END IF ;
84
85 END check_constraints;
86
87 PROCEDURE check_parent_existance AS
88 BEGIN
89
90 IF (((old_references.reference_number = new_references.reference_number)) OR
91 ((new_references.reference_number IS NULL))) THEN
92 NULL;
93
94 ELSE
95 IF NOT igs_ge_note_pkg.get_pk_for_validation ( new_references.reference_number ) THEN
96 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
97 igs_ge_msg_stack.add;
98 app_exception.raise_exception;
99 END IF;
100 END IF;
101
102 IF (((old_references.tracking_group_id = new_references.tracking_group_id)) OR
103 ((new_references.tracking_group_id IS NULL))) THEN
104 NULL;
105
106 ELSE
107 IF NOT igs_tr_group_pkg.get_pk_for_validation ( new_references.tracking_group_id ) THEN
108 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
109 igs_ge_msg_stack.add;
110 app_exception.raise_exception;
111 END IF;
112 END IF;
113
114 IF (((old_references.trk_note_type = new_references.trk_note_type)) OR
115 ((new_references.trk_note_type IS NULL))) THEN
116 NULL;
117 ELSE
118 IF NOT igs_tr_note_type_pkg.get_pk_for_validation ( new_references.trk_note_type ) THEN
119 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123 END IF;
124
125 END check_parent_existance;
126
127 FUNCTION get_pk_for_validation (
128 x_tracking_group_id IN NUMBER,
129 x_reference_number IN NUMBER
130 )
131 RETURN BOOLEAN AS
132
133 CURSOR cur_rowid IS
134 SELECT ROWID
135 FROM igs_tr_group_note
136 WHERE tracking_group_id = x_tracking_group_id
137 AND reference_number = x_reference_number
138 FOR UPDATE NOWAIT;
139
140 lv_rowid cur_rowid%ROWTYPE;
141
142 BEGIN
143
144 OPEN cur_rowid;
145 FETCH cur_rowid INTO lv_rowid;
146
147 IF (cur_rowid%FOUND) THEN
148 CLOSE cur_rowid;
149 RETURN (TRUE);
150 ELSE
151 CLOSE cur_rowid;
152 RETURN (FALSE);
153 END IF;
154
155 END get_pk_for_validation;
156
157 PROCEDURE get_fk_igs_ge_note (
158 x_reference_number IN NUMBER
159 ) AS
160
161 CURSOR cur_rowid IS
162 SELECT ROWID
163 FROM igs_tr_group_note
164 WHERE reference_number = x_reference_number ;
165
166 lv_rowid cur_rowid%ROWTYPE;
167
168 BEGIN
169
170 OPEN cur_rowid;
171 FETCH cur_rowid INTO lv_rowid;
172 IF (cur_rowid%FOUND) THEN
173 CLOSE cur_rowid;
174 fnd_message.set_name ('IGS', 'IGS_TR_TGN_NOTE_FK');
175 igs_ge_msg_stack.add;
176 app_exception.raise_exception;
177 RETURN;
178 END IF;
179 CLOSE cur_rowid;
180
181 END get_fk_igs_ge_note;
182
183 PROCEDURE get_fk_igs_tr_group (
184 x_tracking_group_id IN NUMBER
185 ) AS
186
187 CURSOR cur_rowid IS
188 SELECT ROWID
189 FROM igs_tr_group_note
190 WHERE tracking_group_id = x_tracking_group_id ;
191
192 lv_rowid cur_rowid%ROWTYPE;
193
194 BEGIN
195
196 OPEN cur_rowid;
197 FETCH cur_rowid INTO lv_rowid;
198 IF (cur_rowid%FOUND) THEN
199 CLOSE cur_rowid;
200 fnd_message.set_name ('IGS', 'IGS_TR_TGN_TG_FK');
201 igs_ge_msg_stack.add;
202 app_exception.raise_exception;
203 RETURN;
204 END IF;
205 CLOSE cur_rowid;
206
207 END get_fk_igs_tr_group;
208
209 PROCEDURE get_fk_igs_tr_note_type (
210 x_trk_note_type IN VARCHAR2
211 ) AS
212
213 CURSOR cur_rowid IS
214 SELECT ROWID
215 FROM igs_tr_group_note
216 WHERE trk_note_type = x_trk_note_type ;
217
218 lv_rowid cur_rowid%ROWTYPE;
219
220 BEGIN
221
222 OPEN cur_rowid;
223 FETCH cur_rowid INTO lv_rowid;
224 IF (cur_rowid%FOUND) THEN
225 CLOSE cur_rowid;
226 fnd_message.set_name ('IGS', 'IGS_TR_TGN_TNT_FK');
227 igs_ge_msg_stack.add;
228 app_exception.raise_exception;
229 RETURN;
230 END IF;
231 CLOSE cur_rowid;
232
233 END get_fk_igs_tr_note_type;
234
235 PROCEDURE before_dml (
236 p_action IN VARCHAR2,
237 x_rowid IN VARCHAR2 DEFAULT NULL,
238 x_tracking_group_id IN NUMBER DEFAULT NULL,
239 x_reference_number IN NUMBER DEFAULT NULL,
240 x_trk_note_type IN VARCHAR2 DEFAULT NULL,
241 x_creation_date IN DATE DEFAULT NULL,
242 x_created_by IN NUMBER DEFAULT NULL,
243 x_last_update_date IN DATE DEFAULT NULL,
244 x_last_updated_by IN NUMBER DEFAULT NULL,
245 x_last_update_login IN NUMBER DEFAULT NULL
246 ) AS
247 BEGIN
248
249 set_column_values (
250 p_action,
251 x_rowid,
252 x_tracking_group_id,
253 x_reference_number,
254 x_trk_note_type,
255 x_creation_date,
256 x_created_by,
257 x_last_update_date,
258 x_last_updated_by,
259 x_last_update_login
260 );
261
262 IF (p_action = 'INSERT') THEN
263 IF get_pk_for_validation ( new_references.tracking_group_id, new_references.reference_number) THEN
264 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
265 igs_ge_msg_stack.add;
266 app_exception.raise_exception;
267 END IF;
268 check_constraints;
269 check_parent_existance;
270
271 ELSIF (p_action = 'UPDATE') THEN
272 check_constraints;
273 check_parent_existance;
274
275 ELSIF (p_action = 'VALIDATE_INSERT') THEN
276 IF get_pk_for_validation ( new_references.tracking_group_id, new_references.reference_number) THEN
277 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
278 igs_ge_msg_stack.add;
279 app_exception.raise_exception;
280 END IF;
281 check_constraints;
282
283 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
284 check_constraints;
285 END IF;
286
287 END before_dml;
288
289 PROCEDURE after_dml (
290 p_action IN VARCHAR2,
291 x_rowid IN VARCHAR2
292 ) AS
293 BEGIN
294
295 l_rowid := x_rowid;
296
297 END after_dml;
298
299 PROCEDURE insert_row (
300 x_rowid IN OUT NOCOPY VARCHAR2,
301 x_tracking_group_id IN NUMBER,
302 x_reference_number IN NUMBER,
303 x_trk_note_type IN VARCHAR2,
304 x_mode IN VARCHAR2 DEFAULT 'R'
305 ) AS
306
307 CURSOR c IS
308 SELECT ROWID
309 FROM igs_tr_group_note
310 WHERE tracking_group_id = x_tracking_group_id
311 AND reference_number = x_reference_number;
312
313 x_last_update_date DATE;
314 x_last_updated_by NUMBER;
315 x_last_update_login NUMBER;
316
317 BEGIN
318
319 x_last_update_date := SYSDATE;
320 IF(x_mode = 'I') THEN
321 x_last_updated_by := 1;
322 x_last_update_login := 0;
323 ELSIF (x_mode = 'R') THEN
324 x_last_updated_by := fnd_global.user_id;
325
326 IF x_last_updated_by IS NULL THEN
327 x_last_updated_by := -1;
328 END IF;
329
330 x_last_update_login :=fnd_global.login_id;
331 IF x_last_update_login IS NULL THEN
332 x_last_update_login := -1;
333 END IF;
334 ELSE
335 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
336 igs_ge_msg_stack.add;
337 app_exception.raise_exception;
338 END IF;
339
340 before_dml(p_action =>'INSERT',
341 x_rowid =>x_rowid,
342 x_tracking_group_id => x_tracking_group_id,
343 x_reference_number=> x_reference_number,
344 x_trk_note_type => x_trk_note_type,
345 x_creation_date => x_last_update_date,
346 x_created_by => x_last_updated_by,
347 x_last_update_date => x_last_update_date,
348 x_last_updated_by => x_last_updated_by,
349 x_last_update_login => x_last_update_login
350 );
351
352 INSERT INTO igs_tr_group_note (
353 tracking_group_id,
354 reference_number,
355 trk_note_type,
356 creation_date,
357 created_by,
358 last_update_date,
359 last_updated_by,
360 last_update_login
361 ) VALUES (
362 new_references.tracking_group_id,
363 new_references.reference_number,
364 new_references.trk_note_type,
365 x_last_update_date,
366 x_last_updated_by,
367 x_last_update_date,
368 x_last_updated_by,
369 x_last_update_login
370 );
371
372 OPEN c;
373 FETCH c INTO x_rowid;
374 IF (c%NOTFOUND) THEN
375 CLOSE c;
376 RAISE no_data_found;
377 END IF;
378 CLOSE c;
379
380 after_dml(
381 p_action =>'INSERT',
382 x_rowid => x_rowid
383 );
384
385 END insert_row;
386
387 PROCEDURE lock_row (
388 x_rowid IN VARCHAR2,
389 x_tracking_group_id IN NUMBER,
390 x_reference_number IN NUMBER,
391 x_trk_note_type IN VARCHAR2
392 ) AS
393
394 CURSOR c1 IS
395 SELECT trk_note_type
396 FROM igs_tr_group_note
397 WHERE ROWID = x_rowid
398 FOR UPDATE NOWAIT;
399
400 tlinfo c1%ROWTYPE;
401
402 BEGIN
403
404 OPEN c1;
405 FETCH c1 INTO tlinfo;
406 IF (c1%NOTFOUND) THEN
407 CLOSE c1;
408 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
409 igs_ge_msg_stack.add;
410 app_exception.raise_exception;
411 RETURN;
412 END IF;
413 CLOSE c1;
414
415 IF ( (tlinfo.trk_note_type = x_trk_note_type)) THEN
416 NULL;
417 ELSE
418 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
419 igs_ge_msg_stack.add;
420 app_exception.raise_exception;
421 END IF;
422 RETURN;
423
424 END lock_row;
425
426 PROCEDURE update_row (
427 x_rowid IN VARCHAR2,
428 x_tracking_group_id IN NUMBER,
429 x_reference_number IN NUMBER,
430 x_trk_note_type IN VARCHAR2,
431 x_mode IN VARCHAR2 DEFAULT 'R'
432 ) AS
433 x_last_update_date DATE;
434 x_last_updated_by NUMBER;
435 x_last_update_login NUMBER;
436 BEGIN
437
438 x_last_update_date := SYSDATE;
439
440 IF(x_mode = 'I') THEN
441 x_last_updated_by := 1;
442 x_last_update_login := 0;
443
444 ELSIF (x_mode = 'R') THEN
445
446 x_last_updated_by := fnd_global.user_id;
447 IF x_last_updated_by IS NULL THEN
448 x_last_updated_by := -1;
449 END IF;
450
451 x_last_update_login :=fnd_global.login_id;
452 IF x_last_update_login IS NULL THEN
453 x_last_update_login := -1;
454 END IF;
455
456 ELSE
457 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
458 igs_ge_msg_stack.add;
459 app_exception.raise_exception;
460 END IF;
461 before_dml(p_action =>'UPDATE',
462 x_rowid =>x_rowid,
463 x_tracking_group_id => x_tracking_group_id,
464 x_reference_number=> x_reference_number,
465 x_trk_note_type => x_trk_note_type,
466 x_creation_date => x_last_update_date,
467 x_created_by => x_last_updated_by,
468 x_last_update_date => x_last_update_date,
469 x_last_updated_by => x_last_updated_by,
470 x_last_update_login => x_last_update_login
471 );
472
473 UPDATE igs_tr_group_note SET
474 trk_note_type = new_references.trk_note_type,
475 last_update_date = x_last_update_date,
476 last_updated_by = x_last_updated_by,
477 last_update_login = x_last_update_login
478 WHERE ROWID = x_rowid;
479
480 IF (SQL%NOTFOUND) THEN
481 RAISE no_data_found;
482 END IF;
483
484 after_dml(
485 p_action =>'UPDATE',
486 x_rowid => x_rowid
487 );
488
489 END update_row;
490
491 PROCEDURE add_row (
492 x_rowid IN OUT NOCOPY VARCHAR2,
493 x_tracking_group_id IN NUMBER,
494 x_reference_number IN NUMBER,
495 x_trk_note_type IN VARCHAR2,
496 x_mode IN VARCHAR2 DEFAULT 'R'
497 ) AS
498
499 CURSOR c1 IS
500 SELECT ROWID
501 FROM igs_tr_group_note
502 WHERE tracking_group_id = x_tracking_group_id
503 AND reference_number = x_reference_number;
504
505 BEGIN
506
507 OPEN c1;
508 FETCH c1 INTO x_rowid;
509 IF (c1%NOTFOUND) THEN
510 CLOSE c1;
511
512 insert_row (
513 x_rowid,
514 x_tracking_group_id,
515 x_reference_number,
516 x_trk_note_type,
517 x_mode
518 );
519 RETURN;
520 END IF;
521 CLOSE c1;
522
523 update_row (
524 x_rowid,
525 x_tracking_group_id,
526 x_reference_number,
527 x_trk_note_type,
528 x_mode);
529
530 END add_row;
531
532 PROCEDURE delete_row (
533 x_rowid IN VARCHAR2
534 ) AS
535 BEGIN
536
537 before_dml(
538 p_action =>'DELETE',
539 x_rowid => x_rowid
540 );
541
542 DELETE FROM igs_tr_group_note WHERE ROWID = x_rowid;
543 IF (SQL%NOTFOUND) THEN
544 RAISE no_data_found;
545 END IF;
546
547 after_dml(
548 p_action =>'DELETE',
549 x_rowid => x_rowid
550 );
551 END delete_row;
552
553 END igs_tr_group_note_pkg;