DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGI_EXP_DU_TYPE_DETAILS_PKG

Source


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