1: PACKAGE BODY igi_exp_dus_pkg AS
2: /* $Header: igiexpwb.pls 120.4.12000000.1 2007/09/13 04:25:09 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;
1: PACKAGE BODY igi_exp_dus_pkg AS
2: /* $Header: igiexpwb.pls 120.4.12000000.1 2007/09/13 04:25:09 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;
2: /* $Header: igiexpwb.pls 120.4.12000000.1 2007/09/13 04:25:09 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;
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;
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:
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);
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_dus_all%ROWTYPE;
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_dus_all%ROWTYPE;
15: new_references igi_exp_dus_all%ROWTYPE;
80: FETCH cur_old_ref_values INTO old_references;
81: IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
82: CLOSE cur_old_ref_values;
83: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
84: --bug 3199481 fnd logging changes: sdixit: start block
85: IF (l_error_level >= l_debug_level ) THEN
86: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.set_column_values',FALSE);
87: END IF;
88: --bug 3199481 fnd logging changes: sdixit: end block
82: CLOSE cur_old_ref_values;
83: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
84: --bug 3199481 fnd logging changes: sdixit: start block
85: IF (l_error_level >= l_debug_level ) THEN
86: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.set_column_values',FALSE);
87: END IF;
88: --bug 3199481 fnd logging changes: sdixit: end block
89:
90: app_exception.raise_exception;
84: --bug 3199481 fnd logging changes: sdixit: start block
85: IF (l_error_level >= l_debug_level ) THEN
86: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.set_column_values',FALSE);
87: END IF;
88: --bug 3199481 fnd logging changes: sdixit: end block
89:
90: app_exception.raise_exception;
91: RETURN;
92: END IF;
160: ELSIF NOT igi_exp_du_type_headers_pkg.get_pk_for_validation (
161: new_references.du_type_header_id
162: ) THEN
163: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
164: --bug 3199481 fnd logging changes: sdixit: start block
165: IF (l_error_level >= l_debug_level ) THEN
166: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.check_parent_existance.msg1',FALSE);
167: END IF;
168: --bug 3199481 fnd logging changes: sdixit: end block
162: ) THEN
163: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
164: --bug 3199481 fnd logging changes: sdixit: start block
165: IF (l_error_level >= l_debug_level ) THEN
166: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.check_parent_existance.msg1',FALSE);
167: END IF;
168: --bug 3199481 fnd logging changes: sdixit: end block
169:
170: app_exception.raise_exception;
164: --bug 3199481 fnd logging changes: sdixit: start block
165: IF (l_error_level >= l_debug_level ) THEN
166: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.check_parent_existance.msg1',FALSE);
167: END IF;
168: --bug 3199481 fnd logging changes: sdixit: end block
169:
170: app_exception.raise_exception;
171: END IF;
172:
176: ELSIF NOT igi_exp_tus_pkg.get_pk_for_validation (
177: new_references.tu_id
178: ) THEN
179: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
180: --bug 3199481 fnd logging changes: sdixit: start block
181: IF (l_error_level >= l_debug_level ) THEN
182: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.check_parent_existance.msg2',FALSE);
183: END IF;
184: --bug 3199481 fnd logging changes: sdixit: end block
178: ) THEN
179: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
180: --bug 3199481 fnd logging changes: sdixit: start block
181: IF (l_error_level >= l_debug_level ) THEN
182: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.check_parent_existance.msg2',FALSE);
183: END IF;
184: --bug 3199481 fnd logging changes: sdixit: end block
185:
186: app_exception.raise_exception;
180: --bug 3199481 fnd logging changes: sdixit: start block
181: IF (l_error_level >= l_debug_level ) THEN
182: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.check_parent_existance.msg2',FALSE);
183: END IF;
184: --bug 3199481 fnd logging changes: sdixit: end block
185:
186: app_exception.raise_exception;
187: END IF;
188: /*
283: FETCH cur_rowid INTO lv_rowid;
284: IF (cur_rowid%FOUND) THEN
285: CLOSE cur_rowid;
286: fnd_message.set_name('FND' ,'FND-CANNOT DELETE MASTER');
287: --bug 3199481 fnd logging changes: sdixit: start block
288: IF (l_error_level >= l_debug_level ) THEN
289: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_igi_exp_du_type_headers',FALSE);
290: END IF;
291: --bug 3199481 fnd logging changes: sdixit: end block
285: CLOSE cur_rowid;
286: fnd_message.set_name('FND' ,'FND-CANNOT DELETE MASTER');
287: --bug 3199481 fnd logging changes: sdixit: start block
288: IF (l_error_level >= l_debug_level ) THEN
289: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_igi_exp_du_type_headers',FALSE);
290: END IF;
291: --bug 3199481 fnd logging changes: sdixit: end block
292:
293: app_exception.raise_exception;
287: --bug 3199481 fnd logging changes: sdixit: start block
288: IF (l_error_level >= l_debug_level ) THEN
289: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_igi_exp_du_type_headers',FALSE);
290: END IF;
291: --bug 3199481 fnd logging changes: sdixit: end block
292:
293: app_exception.raise_exception;
294: RETURN;
295: END IF;
323: FETCH cur_rowid INTO lv_rowid;
324: IF (cur_rowid%FOUND) THEN
325: CLOSE cur_rowid;
326: fnd_message.set_name('FND' ,'FND-CANNOT DELETE MASTER');
327: --bug 3199481 fnd logging changes: sdixit: start block
328: IF (l_error_level >= l_debug_level ) THEN
329: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_igi_exp_tus',FALSE);
330: END IF;
331: --bug 3199481 fnd logging changes: sdixit: end block
325: CLOSE cur_rowid;
326: fnd_message.set_name('FND' ,'FND-CANNOT DELETE MASTER');
327: --bug 3199481 fnd logging changes: sdixit: start block
328: IF (l_error_level >= l_debug_level ) THEN
329: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_igi_exp_tus',FALSE);
330: END IF;
331: --bug 3199481 fnd logging changes: sdixit: end block
332:
333: app_exception.raise_exception;
327: --bug 3199481 fnd logging changes: sdixit: start block
328: IF (l_error_level >= l_debug_level ) THEN
329: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_igi_exp_tus',FALSE);
330: END IF;
331: --bug 3199481 fnd logging changes: sdixit: end block
332:
333: app_exception.raise_exception;
334: RETURN;
335: END IF;
363: FETCH cur_rowid INTO lv_rowid;
364: IF (cur_rowid%FOUND) THEN
365: CLOSE cur_rowid;
366: fnd_message.set_name('FND' ,'FND-CANNOT DELETE MASTER');
367: --bug 3199481 fnd logging changes: sdixit: start block
368: IF (l_error_level >= l_debug_level ) THEN
369: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_po_vendors',FALSE);
370: END IF;
371: --bug 3199481 fnd logging changes: sdixit: end block
365: CLOSE cur_rowid;
366: fnd_message.set_name('FND' ,'FND-CANNOT DELETE MASTER');
367: --bug 3199481 fnd logging changes: sdixit: start block
368: IF (l_error_level >= l_debug_level ) THEN
369: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_po_vendors',FALSE);
370: END IF;
371: --bug 3199481 fnd logging changes: sdixit: end block
372: app_exception.raise_exception;
373: RETURN;
367: --bug 3199481 fnd logging changes: sdixit: start block
368: IF (l_error_level >= l_debug_level ) THEN
369: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.get_fk_po_vendors',FALSE);
370: END IF;
371: --bug 3199481 fnd logging changes: sdixit: end block
372: app_exception.raise_exception;
373: RETURN;
374: END IF;
375: CLOSE cur_rowid;
479: new_references.du_id
480: )
481: ) THEN
482: fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
483: --bug 3199481 fnd logging changes: sdixit: start block
484: IF (l_error_level >= l_debug_level ) THEN
485: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.before_dml.msg1',FALSE);
486: END IF;
487: --bug 3199481 fnd logging changes: sdixit: end block
481: ) THEN
482: fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
483: --bug 3199481 fnd logging changes: sdixit: start block
484: IF (l_error_level >= l_debug_level ) THEN
485: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.before_dml.msg1',FALSE);
486: END IF;
487: --bug 3199481 fnd logging changes: sdixit: end block
488:
489: app_exception.raise_exception;
483: --bug 3199481 fnd logging changes: sdixit: start block
484: IF (l_error_level >= l_debug_level ) THEN
485: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.before_dml.msg1',FALSE);
486: END IF;
487: --bug 3199481 fnd logging changes: sdixit: end block
488:
489: app_exception.raise_exception;
490: END IF;
491: check_parent_existance;
501: new_references.du_id
502: )
503: ) THEN
504: fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
505: --bug 3199481 fnd logging changes: sdixit: start block
506: IF (l_error_level >= l_debug_level ) THEN
507: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.before_dml.msg2',FALSE);
508: END IF;
509: --bug 3199481 fnd logging changes: sdixit: end block
503: ) THEN
504: fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
505: --bug 3199481 fnd logging changes: sdixit: start block
506: IF (l_error_level >= l_debug_level ) THEN
507: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.before_dml.msg2',FALSE);
508: END IF;
509: --bug 3199481 fnd logging changes: sdixit: end block
510:
511: app_exception.raise_exception;
505: --bug 3199481 fnd logging changes: sdixit: start block
506: IF (l_error_level >= l_debug_level ) THEN
507: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.before_dml.msg2',FALSE);
508: END IF;
509: --bug 3199481 fnd logging changes: sdixit: end block
510:
511: app_exception.raise_exception;
512: END IF;
513: ELSIF (p_action = 'VALIDATE_DELETE') THEN
587: x_last_update_login := -1;
588: END IF;
589: ELSE
590: fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
591: --bug 3199481 fnd logging changes: sdixit: start block
592: IF (l_error_level >= l_debug_level ) THEN
593: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.insert_row',FALSE);
594: END IF;
595: --bug 3199481 fnd logging changes: sdixit: end block
589: ELSE
590: fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
591: --bug 3199481 fnd logging changes: sdixit: start block
592: IF (l_error_level >= l_debug_level ) THEN
593: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.insert_row',FALSE);
594: END IF;
595: --bug 3199481 fnd logging changes: sdixit: end block
596:
597: app_exception.raise_exception;
591: --bug 3199481 fnd logging changes: sdixit: start block
592: IF (l_error_level >= l_debug_level ) THEN
593: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.insert_row',FALSE);
594: END IF;
595: --bug 3199481 fnd logging changes: sdixit: end block
596:
597: app_exception.raise_exception;
598: END IF;
599:
825: OPEN c1;
826: FETCH c1 INTO tlinfo;
827: IF (c1%notfound) THEN
828: fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
829: --bug 3199481 fnd logging changes: sdixit: start block
830: IF (l_error_level >= l_debug_level ) THEN
831: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.lock_row.msg1',FALSE);
832: END IF;
833: --bug 3199481 fnd logging changes: sdixit: end block
827: IF (c1%notfound) THEN
828: fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
829: --bug 3199481 fnd logging changes: sdixit: start block
830: IF (l_error_level >= l_debug_level ) THEN
831: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.lock_row.msg1',FALSE);
832: END IF;
833: --bug 3199481 fnd logging changes: sdixit: end block
834:
835: CLOSE c1;
829: --bug 3199481 fnd logging changes: sdixit: start block
830: IF (l_error_level >= l_debug_level ) THEN
831: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.lock_row.msg1',FALSE);
832: END IF;
833: --bug 3199481 fnd logging changes: sdixit: end block
834:
835: CLOSE c1;
836: app_exception.raise_exception;
837: RETURN;
874: ) THEN
875: NULL;
876: ELSE
877: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
878: --bug 3199481 fnd logging changes: sdixit: start block
879: IF (l_error_level >= l_debug_level ) THEN
880: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.lock_row.msg2',FALSE);
881: END IF;
882: --bug 3199481 fnd logging changes: sdixit: end block
876: ELSE
877: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
878: --bug 3199481 fnd logging changes: sdixit: start block
879: IF (l_error_level >= l_debug_level ) THEN
880: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.lock_row.msg2',FALSE);
881: END IF;
882: --bug 3199481 fnd logging changes: sdixit: end block
883:
884: app_exception.raise_exception;
878: --bug 3199481 fnd logging changes: sdixit: start block
879: IF (l_error_level >= l_debug_level ) THEN
880: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.lock_row.msg2',FALSE);
881: END IF;
882: --bug 3199481 fnd logging changes: sdixit: end block
883:
884: app_exception.raise_exception;
885: END IF;
886:
954: x_last_update_login := -1;
955: END IF;
956: ELSE
957: fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
958: --bug 3199481 fnd logging changes: sdixit: start block
959: IF (l_error_level >= l_debug_level ) THEN
960: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.update_row',FALSE);
961: END IF;
962: --bug 3199481 fnd logging changes: sdixit: end block
956: ELSE
957: fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
958: --bug 3199481 fnd logging changes: sdixit: start block
959: IF (l_error_level >= l_debug_level ) THEN
960: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.update_row',FALSE);
961: END IF;
962: --bug 3199481 fnd logging changes: sdixit: end block
963:
964: app_exception.raise_exception;
958: --bug 3199481 fnd logging changes: sdixit: start block
959: IF (l_error_level >= l_debug_level ) THEN
960: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpwb.IGI_EXP_DUS_PKG.update_row',FALSE);
961: END IF;
962: --bug 3199481 fnd logging changes: sdixit: end block
963:
964: app_exception.raise_exception;
965: END IF;
966: