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