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