DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGI_EXP_DU_TYPE_HEADERS_PKG

Source


1 PACKAGE BODY igi_exp_du_type_headers_pkg AS
2 /* $Header: igiexpsb.pls 120.5.12000000.1 2007/09/13 04:24:33 mbremkum ship $ */
3 
4 --following variables added for bug 3199481: fnd logging changes: sdixit
5    l_debug_level number	:=	FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6    l_state_level number	:=	FND_LOG.LEVEL_STATEMENT;
7    l_proc_level number	:=	FND_LOG.LEVEL_PROCEDURE;
8    l_event_level number	:=	FND_LOG.LEVEL_EVENT;
9    l_excep_level number	:=	FND_LOG.LEVEL_EXCEPTION;
10    l_error_level number	:=	FND_LOG.LEVEL_ERROR;
11    l_unexp_level number	:=	FND_LOG.LEVEL_UNEXPECTED;
12 
13   l_rowid VARCHAR2(25);
14   old_references igi_exp_du_type_headers_all%ROWTYPE;
15   new_references igi_exp_du_type_headers_all%ROWTYPE;
16 
17   PROCEDURE set_column_values (
18     p_action                            IN     VARCHAR2,
19     x_rowid                             IN     VARCHAR2    ,
20     x_du_type_header_id                 IN     NUMBER      ,
21     x_du_type_name                      IN     VARCHAR2    ,
22     x_du_type_desc                      IN     VARCHAR2    ,
23     x_application_id                    IN     NUMBER      ,
24     x_stp_only                          IN     VARCHAR2    ,
25     x_stp_site_only                     IN     VARCHAR2    ,
26     x_start_date                        IN     DATE        ,
27     x_end_date                          IN     DATE        ,
28     x_org_id                            IN     NUMBER      ,
29     x_creation_date                     IN     DATE        ,
30     x_created_by                        IN     NUMBER      ,
31     x_last_update_date                  IN     DATE        ,
32     x_last_updated_by                   IN     NUMBER      ,
33     x_last_update_login                 IN     NUMBER      ) AS
34   /*
35   ||  Created By : [email protected]
36   ||  Created On : 18-OCT-2001
37   ||  Purpose : Initialises the Old and New references for the columns of the table.
38   ||  Known limitations, enhancements or remarks :
39   ||  Change History :
40   ||  Who             When            What
41   ||  (reverse chronological order - newest change first)
42   */
43 
44     CURSOR cur_old_ref_values IS
45       SELECT   *
46       FROM     IGI_EXP_DU_TYPE_HEADERS_ALL
47       WHERE    rowid = x_rowid;
48 
49   BEGIN
50 
51     l_rowid := x_rowid;
52 
53     -- Code for setting the Old and New Reference Values.
54     -- Populate Old Values.
55     OPEN cur_old_ref_values;
56     FETCH cur_old_ref_values INTO old_references;
57     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
58       CLOSE cur_old_ref_values;
59       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
60    --bug 3199481 fnd logging changes: sdixit: start block
61        IF (l_error_level >=  l_debug_level ) THEN
62           FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpsb.IGI_EXP_DU_TYPE_HEADERS_PKG.set_column_values',FALSE);
63        END IF;
64    --bug 3199481 fnd logging changes: sdixit: end block
65 
66       app_exception.raise_exception;
67       RETURN;
68     END IF;
69     CLOSE cur_old_ref_values;
70 
71     -- Populate New Values.
72     new_references.du_type_header_id                 := x_du_type_header_id;
73     new_references.du_type_name                      := x_du_type_name;
74     new_references.du_type_desc                      := x_du_type_desc;
75     new_references.application_id                    := x_application_id;
76     new_references.stp_only                          := x_stp_only;
77     new_references.stp_site_only                     := x_stp_site_only;
78     new_references.start_date                        := x_start_date;
79     new_references.end_date                          := x_end_date;
80     new_references.org_id                            := x_org_id;
81 
82     IF (p_action = 'UPDATE') THEN
83       new_references.creation_date                   := old_references.creation_date;
84       new_references.created_by                      := old_references.created_by;
85     ELSE
86       new_references.creation_date                   := x_creation_date;
87       new_references.created_by                      := x_created_by;
88     END IF;
89 
90     new_references.last_update_date                  := x_last_update_date;
91     new_references.last_updated_by                   := x_last_updated_by;
92     new_references.last_update_login                 := x_last_update_login;
93 
94   END set_column_values;
95 
96 
97   PROCEDURE check_child_existance IS
98   /*
99   ||  Created By : [email protected]
100   ||  Created On : 18-OCT-2001
101   ||  Purpose : Checks for the existance of Child records.
102   ||  Known limitations, enhancements or remarks :
103   ||  Change History :
104   ||  Who             When            What
105   ||  (reverse chronological order - newest change first)
106   */
107   BEGIN
108 
109       igi_exp_du_type_details_pkg.get_fk_igi_exp_du_type_headers (
110       old_references.du_type_header_id
111     );
112 /*
113     igi_exp_tu_type_details_pkg.get_fk_igi_exp_du_type_headers (
114       old_references.du_type_header_id
115     );
116 */
117 NULL;
118   END check_child_existance;
119 
120 
121   FUNCTION get_pk_for_validation (
122     x_du_type_header_id                 IN     NUMBER
123   ) RETURN BOOLEAN AS
124   /*
125   ||  Created By : [email protected]
126   ||  Created On : 18-OCT-2001
127   ||  Purpose : Validates the Primary Key of the table.
128   ||  Known limitations, enhancements or remarks :
129   ||  Change History :
130   ||  Who             When            What
131   ||  (reverse chronological order - newest change first)
132   */
133     CURSOR cur_rowid IS
134       SELECT   rowid
135       FROM     igi_exp_du_type_headers_all
136       WHERE    du_type_header_id = x_du_type_header_id
137       FOR UPDATE NOWAIT;
138 
139     lv_rowid cur_rowid%RowType;
140 
141   BEGIN
142 
143     OPEN cur_rowid;
144     FETCH cur_rowid INTO lv_rowid;
145     IF (cur_rowid%FOUND) THEN
146       CLOSE cur_rowid;
147       RETURN(TRUE);
148     ELSE
149       CLOSE cur_rowid;
150       RETURN(FALSE);
151     END IF;
152 
153   END get_pk_for_validation;
154 
155 
156   PROCEDURE before_dml (
157     p_action                            IN     VARCHAR2,
158     x_rowid                             IN     VARCHAR2    ,
159     x_du_type_header_id                 IN     NUMBER      ,
160     x_du_type_name                      IN     VARCHAR2    ,
161     x_du_type_desc                      IN     VARCHAR2    ,
162     x_application_id                    IN     NUMBER      ,
163     x_stp_only                          IN     VARCHAR2    ,
164     x_stp_site_only                     IN     VARCHAR2    ,
165     x_start_date                        IN     DATE        ,
166     x_end_date                          IN     DATE        ,
167     x_org_id                            IN     NUMBER      ,
168     x_creation_date                     IN     DATE        ,
169     x_created_by                        IN     NUMBER      ,
170     x_last_update_date                  IN     DATE        ,
171     x_last_updated_by                   IN     NUMBER      ,
172     x_last_update_login                 IN     NUMBER      ) AS
173   /*
174   ||  Created By : [email protected]
175   ||  Created On : 18-OCT-2001
176   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
177   ||            Trigger Handlers for the table, before any DML operation.
178   ||  Known limitations, enhancements or remarks :
179   ||  Change History :
180   ||  Who             When            What
181   ||  (reverse chronological order - newest change first)
182   */
183   BEGIN
184 
185     set_column_values (
186       p_action,
187       x_rowid,
188       x_du_type_header_id,
189       x_du_type_name,
190       x_du_type_desc,
191       x_application_id,
192       x_stp_only,
193       x_stp_site_only,
194       x_start_date,
195       x_end_date,
196       x_org_id,
197       x_creation_date,
198       x_created_by,
199       x_last_update_date,
200       x_last_updated_by,
201       x_last_update_login
202     );
203 
204     IF (p_action = 'INSERT') THEN
205       -- Call all the procedures related to Before Insert.
206       IF ( get_pk_for_validation(
207              new_references.du_type_header_id
208            )
209          ) THEN
210         fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
211    --bug 3199481 fnd logging changes: sdixit: start block
212        IF (l_error_level >=  l_debug_level ) THEN
213           FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpsb.IGI_EXP_DU_TYPE_HEADERS_PKG.before_dml.msg1',FALSE);
214        END IF;
215    --bug 3199481 fnd logging changes: sdixit: end block
216 
217         app_exception.raise_exception;
218       END IF;
219     ELSIF (p_action = 'DELETE') THEN
220       -- Call all the procedures related to Before Delete.
221       check_child_existance;
222     ELSIF (p_action = 'VALIDATE_INSERT') THEN
223       -- Call all the procedures related to Before Insert.
224       IF ( get_pk_for_validation (
225              new_references.du_type_header_id
226            )
227          ) THEN
228         fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
229    --bug 3199481 fnd logging changes: sdixit: start block
230        IF (l_error_level >=  l_debug_level ) THEN
231           FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpsb.IGI_EXP_DU_TYPE_HEADERS_PKG.before_dml.msg2',FALSE);
232        END IF;
233    --bug 3199481 fnd logging changes: sdixit: end block
234 
235         app_exception.raise_exception;
236       END IF;
237     ELSIF (p_action = 'VALIDATE_DELETE') THEN
238       check_child_existance;
239     END IF;
240 
241   END before_dml;
242 
243 
244   PROCEDURE insert_row (
245     x_rowid                             IN OUT NOCOPY VARCHAR2,
246     x_du_type_header_id                 IN OUT NOCOPY NUMBER,
247     x_du_type_name                      IN     VARCHAR2,
248     x_du_type_desc                      IN     VARCHAR2,
249     x_application_id                    IN     NUMBER,
250     x_stp_only                          IN     VARCHAR2,
251     x_stp_site_only                     IN     VARCHAR2,
252     x_start_date                        IN     DATE,
253     x_end_date                          IN     DATE,
254     x_org_id                            IN     NUMBER,
255     x_mode                              IN     VARCHAR2 ) AS
256   /*
257   ||  Created By : [email protected]
258   ||  Created On : 18-OCT-2001
259   ||  Purpose : Handles the INSERT DML logic for the table.
260   ||  Known limitations, enhancements or remarks :
261   ||  Change History :
262   ||  Who             When            What
263   ||  (reverse chronological order - newest change first)
264   */
265     CURSOR c IS
266       SELECT   rowid
267       FROM     igi_exp_du_type_headers_all
268       WHERE    du_type_header_id                 = x_du_type_header_id;
269 
270     x_last_update_date           DATE;
271     x_last_updated_by            NUMBER;
272     x_last_update_login          NUMBER;
273 
274   BEGIN
275 
276     x_last_update_date := SYSDATE;
277     IF (x_mode = 'I') THEN
278       x_last_updated_by := 1;
279       x_last_update_login := 0;
280     ELSIF (x_mode = 'R') THEN
281       x_last_updated_by := fnd_global.user_id;
282       IF (x_last_updated_by IS NULL) THEN
283         x_last_updated_by := -1;
284       END IF;
285       x_last_update_login := fnd_global.login_id;
286       IF (x_last_update_login IS NULL) THEN
287         x_last_update_login := -1;
288       END IF;
289     ELSE
290       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
291    --bug 3199481 fnd logging changes: sdixit: start block
292        IF (l_error_level >=  l_debug_level ) THEN
293           FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpsb.IGI_EXP_DU_TYPE_HEADERS_PKG.insert_row',FALSE);
294        END IF;
295    --bug 3199481 fnd logging changes: sdixit: end block
296 
297       app_exception.raise_exception;
298     END IF;
299 
300     SELECT    igi_exp_du_type_headers_s1.NEXTVAL
301     INTO      x_du_type_header_id
302     FROM      dual;
303 
304     before_dml(
305       p_action                            => 'INSERT',
306       x_rowid                             => x_rowid,
307       x_du_type_header_id                 => x_du_type_header_id,
308       x_du_type_name                      => x_du_type_name,
309       x_du_type_desc                      => x_du_type_desc,
310       x_application_id                    => x_application_id,
311       x_stp_only                          => x_stp_only,
312       x_stp_site_only                     => x_stp_site_only,
313       x_start_date                        => x_start_date,
314       x_end_date                          => x_end_date,
315       x_org_id                            => x_org_id,
316       x_creation_date                     => x_last_update_date,
317       x_created_by                        => x_last_updated_by,
318       x_last_update_date                  => x_last_update_date,
319       x_last_updated_by                   => x_last_updated_by,
320       x_last_update_login                 => x_last_update_login
321     );
322 
323     INSERT INTO igi_exp_du_type_headers_all (
324       du_type_header_id,
325       du_type_name,
326       du_type_desc,
327       application_id,
328       stp_only,
329       stp_site_only,
330       start_date,
331       end_date,
332       org_id,
333       creation_date,
334       created_by,
335       last_update_date,
336       last_updated_by,
337       last_update_login
338     ) VALUES (
339       new_references.du_type_header_id,
340       new_references.du_type_name,
341       new_references.du_type_desc,
342       new_references.application_id,
343       new_references.stp_only,
344       new_references.stp_site_only,
345       new_references.start_date,
346       new_references.end_date,
347       new_references.org_id,
348       x_last_update_date,
349       x_last_updated_by,
350       x_last_update_date,
351       x_last_updated_by,
352       x_last_update_login
353     );
354 
355     OPEN c;
356     FETCH c INTO x_rowid;
357     IF (c%NOTFOUND) THEN
358       CLOSE c;
359       RAISE NO_DATA_FOUND;
360     END IF;
361     CLOSE c;
362 
363   END insert_row;
364 
365 
366   PROCEDURE lock_row (
367     x_rowid                             IN     VARCHAR2,
368     x_du_type_header_id                 IN     NUMBER,
369     x_du_type_name                      IN     VARCHAR2,
370     x_du_type_desc                      IN     VARCHAR2,
371     x_application_id                    IN     NUMBER,
372     x_stp_only                          IN     VARCHAR2,
373     x_stp_site_only                     IN     VARCHAR2,
374     x_start_date                        IN     DATE,
375     x_end_date                          IN     DATE,
376     x_org_id                            IN     NUMBER
377   ) AS
378   /*
379   ||  Created By : [email protected]
380   ||  Created On : 18-OCT-2001
381   ||  Purpose : Handles the LOCK mechanism for the table.
382   ||  Known limitations, enhancements or remarks :
383   ||  Change History :
384   ||  Who             When            What
385   ||  (reverse chronological order - newest change first)
386   */
387     CURSOR c1 IS
388       SELECT
389         du_type_name,
390         du_type_desc,
391         application_id,
392         stp_only,
393         stp_site_only,
394         start_date,
395         end_date,
396         org_id
397       FROM  igi_exp_du_type_headers_all
398       WHERE rowid = x_rowid
399       FOR UPDATE NOWAIT;
400 
401     tlinfo c1%ROWTYPE;
402 
403   BEGIN
404 
405     OPEN c1;
406     FETCH c1 INTO tlinfo;
407     IF (c1%notfound) THEN
408       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
409    --bug 3199481 fnd logging changes: sdixit: start block
410        IF (l_error_level >=  l_debug_level ) THEN
411           FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpsb.IGI_EXP_DU_TYPE_HEADERS_PKG.lock_row',FALSE);
412        END IF;
413    --bug 3199481 fnd logging changes: sdixit: end block
414 
415       CLOSE c1;
416       app_exception.raise_exception;
417       RETURN;
418     END IF;
419     CLOSE c1;
420 
421     IF (
422         (tlinfo.du_type_name = x_du_type_name)
423         AND ((tlinfo.du_type_desc = x_du_type_desc) OR ((tlinfo.du_type_desc IS NULL) AND (X_du_type_desc IS NULL)))
424         AND (tlinfo.application_id = x_application_id)
425         AND (tlinfo.stp_only = x_stp_only)
426         AND (tlinfo.stp_site_only = x_stp_site_only)
427         AND (tlinfo.start_date = x_start_date)
428         AND ((tlinfo.end_date = x_end_date) OR ((tlinfo.end_date IS NULL) AND (X_end_date IS NULL)))
429         AND (tlinfo.org_id = x_org_id)
430        ) THEN
431       NULL;
432     ELSE
433       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
434    --bug 3199481 fnd logging changes: sdixit: start block
435        IF (l_error_level >=  l_debug_level ) THEN
436           FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpsb.IGI_EXP_DU_TYPE_HEADERS_PKG.lock_row',FALSE);
437        END IF;
438    --bug 3199481 fnd logging changes: sdixit: end block
439 
440       app_exception.raise_exception;
441     END IF;
442 
443     RETURN;
444 
445   END lock_row;
446 
447 
448   PROCEDURE update_row (
449     x_rowid                             IN     VARCHAR2,
450     x_du_type_header_id                 IN     NUMBER,
451     x_du_type_name                      IN     VARCHAR2,
452     x_du_type_desc                      IN     VARCHAR2,
453     x_application_id                    IN     NUMBER,
454     x_stp_only                          IN     VARCHAR2,
455     x_stp_site_only                     IN     VARCHAR2,
456     x_start_date                        IN     DATE,
457     x_end_date                          IN     DATE,
458     x_org_id                            IN     NUMBER,
459     x_mode                              IN     VARCHAR2 ) AS
460   /*
461   ||  Created By : [email protected]
462   ||  Created On : 18-OCT-2001
463   ||  Purpose : Handles the UPDATE DML logic for the table.
464   ||  Known limitations, enhancements or remarks :
465   ||  Change History :
466   ||  Who             When            What
467   ||  (reverse chronological order - newest change first)
468   */
469     x_last_update_date           DATE ;
470     x_last_updated_by            NUMBER;
471     x_last_update_login          NUMBER;
472 
473   BEGIN
474 
475     x_last_update_date := SYSDATE;
476     IF (X_MODE = 'I') THEN
477       x_last_updated_by := 1;
478       x_last_update_login := 0;
479     ELSIF (x_mode = 'R') THEN
480       x_last_updated_by := fnd_global.user_id;
481       IF x_last_updated_by IS NULL THEN
482         x_last_updated_by := -1;
483       END IF;
484       x_last_update_login := fnd_global.login_id;
485       IF (x_last_update_login IS NULL) THEN
486         x_last_update_login := -1;
487       END IF;
488     ELSE
489       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
490    --bug 3199481 fnd logging changes: sdixit: start block
491        IF (l_error_level >=  l_debug_level ) THEN
492           FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpsb.IGI_EXP_DU_TYPE_HEADERS_PKG.update_row',FALSE);
493        END IF;
494    --bug 3199481 fnd logging changes: sdixit: end block
495 
496       app_exception.raise_exception;
497     END IF;
498 
499     before_dml(
500       p_action                            => 'UPDATE',
501       x_rowid                             => x_rowid,
502       x_du_type_header_id                 => x_du_type_header_id,
503       x_du_type_name                      => x_du_type_name,
504       x_du_type_desc                      => x_du_type_desc,
505       x_application_id                    => x_application_id,
506       x_stp_only                          => x_stp_only,
507       x_stp_site_only                     => x_stp_site_only,
508       x_start_date                        => x_start_date,
509       x_end_date                          => x_end_date,
510       x_org_id                            => x_org_id,
511       x_creation_date                     => x_last_update_date,
512       x_created_by                        => x_last_updated_by,
513       x_last_update_date                  => x_last_update_date,
514       x_last_updated_by                   => x_last_updated_by,
515       x_last_update_login                 => x_last_update_login
516     );
517 
518     UPDATE igi_exp_du_type_headers_all
519       SET
520         du_type_name                      = new_references.du_type_name,
521         du_type_desc                      = new_references.du_type_desc,
522         application_id                    = new_references.application_id,
523         stp_only                          = new_references.stp_only,
524         stp_site_only                     = new_references.stp_site_only,
525         start_date                        = new_references.start_date,
526         end_date                          = new_references.end_date,
527         org_id                            = new_references.org_id,
528         last_update_date                  = x_last_update_date,
529         last_updated_by                   = x_last_updated_by,
530         last_update_login                 = x_last_update_login
531       WHERE rowid = x_rowid;
532 
533     IF (SQL%NOTFOUND) THEN
534       RAISE NO_DATA_FOUND;
535     END IF;
536 
537   END update_row;
538 
539 
540   PROCEDURE add_row (
541     x_rowid                             IN OUT NOCOPY VARCHAR2,
542     x_du_type_header_id                 IN OUT NOCOPY NUMBER,
543     x_du_type_name                      IN     VARCHAR2,
544     x_du_type_desc                      IN     VARCHAR2,
545     x_application_id                    IN     NUMBER,
546     x_stp_only                          IN     VARCHAR2,
547     x_stp_site_only                     IN     VARCHAR2,
548     x_start_date                        IN     DATE,
549     x_end_date                          IN     DATE,
550     x_org_id                            IN     NUMBER,
551     x_mode                              IN     VARCHAR2 ) AS
552   /*
553   ||  Created By : [email protected]
554   ||  Created On : 18-OCT-2001
555   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
556   ||  Known limitations, enhancements or remarks :
557   ||  Change History :
558   ||  Who             When            What
559   ||  (reverse chronological order - newest change first)
560   */
561     CURSOR c1 IS
562       SELECT   rowid
563       FROM     igi_exp_du_type_headers_all
564       WHERE    du_type_header_id                 = x_du_type_header_id;
565 
566   BEGIN
567 
568     OPEN c1;
569     FETCH c1 INTO x_rowid;
570     IF (c1%NOTFOUND) THEN
571       CLOSE c1;
572 
573       insert_row (
574         x_rowid,
575         x_du_type_header_id,
576         x_du_type_name,
577         x_du_type_desc,
578         x_application_id,
579         x_stp_only,
580         x_stp_site_only,
581         x_start_date,
582         x_end_date,
583         x_org_id,
584         x_mode
585       );
586       RETURN;
587     END IF;
588     CLOSE c1;
589 
590     update_row (
591       x_rowid,
592       x_du_type_header_id,
593       x_du_type_name,
594       x_du_type_desc,
595       x_application_id,
596       x_stp_only,
597       x_stp_site_only,
598       x_start_date,
599       x_end_date,
600       x_org_id,
601       x_mode
602     );
603 
604   END add_row;
605 
606 
607   PROCEDURE delete_row (
608     x_rowid IN VARCHAR2
609   ) AS
610   /*
611   ||  Created By : [email protected]
612   ||  Created On : 18-OCT-2001
613   ||  Purpose : Handles the DELETE DML logic for the table.
614   ||  Known limitations, enhancements or remarks :
615   ||  Change History :
616   ||  Who             When            What
617   ||  (reverse chronological order - newest change first)
618   */
619   BEGIN
620 
621     before_dml (
622       p_action => 'DELETE',
623       x_rowid => x_rowid
624     );
625 
626     DELETE FROM igi_exp_du_type_headers_all
627     WHERE rowid = x_rowid;
628 
629     IF (SQL%NOTFOUND) THEN
630       RAISE NO_DATA_FOUND;
631     END IF;
632 
633   END delete_row;
634 
635 
636 END igi_exp_du_type_headers_pkg;