DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_TR_TYPE_PKG

Source


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