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