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