1: PACKAGE BODY igi_exp_tus_pkg AS
2: /* $Header: igiexpzb.pls 120.5.12000000.1 2007/09/13 04:25:30 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_tus_pkg AS
2: /* $Header: igiexpzb.pls 120.5.12000000.1 2007/09/13 04:25:30 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: igiexpzb.pls 120.5.12000000.1 2007/09/13 04:25:30 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_tus_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_tus_all%ROWTYPE;
15: new_references igi_exp_tus_all%ROWTYPE;
76: FETCH cur_old_ref_values INTO old_references;
77: IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
78: CLOSE cur_old_ref_values;
79: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
80: --bug 3199481 fnd logging changes: sdixit: start block
81: IF (l_error_level >= l_debug_level ) THEN
82: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.set_column_values',FALSE);
83: END IF;
84: --bug 3199481 fnd logging changes: sdixit: end block
78: CLOSE cur_old_ref_values;
79: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
80: --bug 3199481 fnd logging changes: sdixit: start block
81: IF (l_error_level >= l_debug_level ) THEN
82: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.set_column_values',FALSE);
83: END IF;
84: --bug 3199481 fnd logging changes: sdixit: end block
85: app_exception.raise_exception;
86: RETURN;
80: --bug 3199481 fnd logging changes: sdixit: start block
81: IF (l_error_level >= l_debug_level ) THEN
82: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.set_column_values',FALSE);
83: END IF;
84: --bug 3199481 fnd logging changes: sdixit: end block
85: app_exception.raise_exception;
86: RETURN;
87: END IF;
88: CLOSE cur_old_ref_values;
151: ELSIF NOT igi_exp_apprv_profiles_pkg.get_pk_for_validation (
152: new_references.apprv_profile_id
153: ) THEN
154: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
155: --bug 3199481 fnd logging changes: sdixit: start block
156: IF (l_error_level >= l_debug_level ) THEN
157: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.check_parent_existance.msg1',FALSE);
158: END IF;
159: --bug 3199481 fnd logging changes: sdixit: end block
153: ) THEN
154: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
155: --bug 3199481 fnd logging changes: sdixit: start block
156: IF (l_error_level >= l_debug_level ) THEN
157: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.check_parent_existance.msg1',FALSE);
158: END IF;
159: --bug 3199481 fnd logging changes: sdixit: end block
160: app_exception.raise_exception;
161: END IF;
155: --bug 3199481 fnd logging changes: sdixit: start block
156: IF (l_error_level >= l_debug_level ) THEN
157: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.check_parent_existance.msg1',FALSE);
158: END IF;
159: --bug 3199481 fnd logging changes: sdixit: end block
160: app_exception.raise_exception;
161: END IF;
162:
163: IF (((old_references.tu_type_header_id = new_references.tu_type_header_id)) OR
166: ELSIF NOT igi_exp_tu_type_headers_pkg.get_pk_for_validation (
167: new_references.tu_type_header_id
168: ) THEN
169: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
170: --bug 3199481 fnd logging changes: sdixit: start block
171: IF (l_error_level >= l_debug_level ) THEN
172: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.check_parent_existance.msg2',FALSE);
173: END IF;
174: --bug 3199481 fnd logging changes: sdixit: end block
168: ) THEN
169: fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
170: --bug 3199481 fnd logging changes: sdixit: start block
171: IF (l_error_level >= l_debug_level ) THEN
172: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.check_parent_existance.msg2',FALSE);
173: END IF;
174: --bug 3199481 fnd logging changes: sdixit: end block
175: app_exception.raise_exception;
176: END IF;
170: --bug 3199481 fnd logging changes: sdixit: start block
171: IF (l_error_level >= l_debug_level ) THEN
172: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.check_parent_existance.msg2',FALSE);
173: END IF;
174: --bug 3199481 fnd logging changes: sdixit: end block
175: app_exception.raise_exception;
176: END IF;
177:
178: END check_parent_existance;
256: FETCH cur_rowid INTO lv_rowid;
257: IF (cur_rowid%FOUND) THEN
258: CLOSE cur_rowid;
259: fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
260: --bug 3199481 fnd logging changes: sdixit: start block
261: IF (l_error_level >= l_debug_level ) THEN
262: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.get_fk_igi_exp_apprv_profiles',FALSE);
263: END IF;
264: --bug 3199481 fnd logging changes: sdixit: end block
258: CLOSE cur_rowid;
259: fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
260: --bug 3199481 fnd logging changes: sdixit: start block
261: IF (l_error_level >= l_debug_level ) THEN
262: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.get_fk_igi_exp_apprv_profiles',FALSE);
263: END IF;
264: --bug 3199481 fnd logging changes: sdixit: end block
265: app_exception.raise_exception;
266: RETURN;
260: --bug 3199481 fnd logging changes: sdixit: start block
261: IF (l_error_level >= l_debug_level ) THEN
262: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.get_fk_igi_exp_apprv_profiles',FALSE);
263: END IF;
264: --bug 3199481 fnd logging changes: sdixit: end block
265: app_exception.raise_exception;
266: RETURN;
267: END IF;
268: CLOSE cur_rowid;
295: FETCH cur_rowid INTO lv_rowid;
296: IF (cur_rowid%FOUND) THEN
297: CLOSE cur_rowid;
298: fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
299: --bug 3199481 fnd logging changes: sdixit: start block
300: IF (l_error_level >= l_debug_level ) THEN
301: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.get_fk_igi_exp_tu_type_headers ',FALSE);
302: END IF;
303: --bug 3199481 fnd logging changes: sdixit: end block
297: CLOSE cur_rowid;
298: fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
299: --bug 3199481 fnd logging changes: sdixit: start block
300: IF (l_error_level >= l_debug_level ) THEN
301: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.get_fk_igi_exp_tu_type_headers ',FALSE);
302: END IF;
303: --bug 3199481 fnd logging changes: sdixit: end block
304: app_exception.raise_exception;
305: RETURN;
299: --bug 3199481 fnd logging changes: sdixit: start block
300: IF (l_error_level >= l_debug_level ) THEN
301: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.get_fk_igi_exp_tu_type_headers ',FALSE);
302: END IF;
303: --bug 3199481 fnd logging changes: sdixit: end block
304: app_exception.raise_exception;
305: RETURN;
306: END IF;
307: CLOSE cur_rowid;
403: new_references.tu_id
404: )
405: ) THEN
406: fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
407: --bug 3199481 fnd logging changes: sdixit: start block
408: IF (l_error_level >= l_debug_level ) THEN
409: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.before_dml.msg1',FALSE);
410: END IF;
411: --bug 3199481 fnd logging changes: sdixit: end block
405: ) THEN
406: fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
407: --bug 3199481 fnd logging changes: sdixit: start block
408: IF (l_error_level >= l_debug_level ) THEN
409: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.before_dml.msg1',FALSE);
410: END IF;
411: --bug 3199481 fnd logging changes: sdixit: end block
412: app_exception.raise_exception;
413: END IF;
407: --bug 3199481 fnd logging changes: sdixit: start block
408: IF (l_error_level >= l_debug_level ) THEN
409: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.before_dml.msg1',FALSE);
410: END IF;
411: --bug 3199481 fnd logging changes: sdixit: end block
412: app_exception.raise_exception;
413: END IF;
414: check_parent_existance;
415: ELSIF (p_action = 'UPDATE') THEN
421: new_references.tu_id
422: )
423: ) THEN
424: fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
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.igiexpzb.IGI_EXP_TUS_PKG.before_dml.msg2',FALSE);
428: END IF;
429: --bug 3199481 fnd logging changes: sdixit: end block
423: ) THEN
424: fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
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.igiexpzb.IGI_EXP_TUS_PKG.before_dml.msg2',FALSE);
428: END IF;
429: --bug 3199481 fnd logging changes: sdixit: end block
430: app_exception.raise_exception;
431: END IF;
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.igiexpzb.IGI_EXP_TUS_PKG.before_dml.msg2',FALSE);
428: END IF;
429: --bug 3199481 fnd logging changes: sdixit: end block
430: app_exception.raise_exception;
431: END IF;
432: END IF;
433:
501: x_last_update_login := -1;
502: END IF;
503: ELSE
504: fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
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.igiexpzb.IGI_EXP_TUS_PKG.insert_row',FALSE);
508: END IF;
509: --bug 3199481 fnd logging changes: sdixit: end block
503: ELSE
504: fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
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.igiexpzb.IGI_EXP_TUS_PKG.insert_row',FALSE);
508: END IF;
509: --bug 3199481 fnd logging changes: sdixit: end block
510: app_exception.raise_exception;
511: END IF;
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.igiexpzb.IGI_EXP_TUS_PKG.insert_row',FALSE);
508: END IF;
509: --bug 3199481 fnd logging changes: sdixit: end block
510: app_exception.raise_exception;
511: END IF;
512:
513: SELECT igi_exp_tus_s1.NEXTVAL
722: OPEN c1;
723: FETCH c1 INTO tlinfo;
724: IF (c1%notfound) THEN
725: fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
726: --bug 3199481 fnd logging changes: sdixit: start block
727: IF (l_error_level >= l_debug_level ) THEN
728: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.lock_row',FALSE);
729: END IF;
730: --bug 3199481 fnd logging changes: sdixit: end block
724: IF (c1%notfound) THEN
725: fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
726: --bug 3199481 fnd logging changes: sdixit: start block
727: IF (l_error_level >= l_debug_level ) THEN
728: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.lock_row',FALSE);
729: END IF;
730: --bug 3199481 fnd logging changes: sdixit: end block
731: CLOSE c1;
732: app_exception.raise_exception;
726: --bug 3199481 fnd logging changes: sdixit: start block
727: IF (l_error_level >= l_debug_level ) THEN
728: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.lock_row',FALSE);
729: END IF;
730: --bug 3199481 fnd logging changes: sdixit: end block
731: CLOSE c1;
732: app_exception.raise_exception;
733: RETURN;
734: END IF;
766: ) THEN
767: NULL;
768: ELSE
769: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
770: --bug 3199481 fnd logging changes: sdixit: start block
771: IF (l_error_level >= l_debug_level ) THEN
772: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.lock_row',FALSE);
773: END IF;
774: --bug 3199481 fnd logging changes: sdixit: end block
768: ELSE
769: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
770: --bug 3199481 fnd logging changes: sdixit: start block
771: IF (l_error_level >= l_debug_level ) THEN
772: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.lock_row',FALSE);
773: END IF;
774: --bug 3199481 fnd logging changes: sdixit: end block
775: app_exception.raise_exception;
776: END IF;
770: --bug 3199481 fnd logging changes: sdixit: start block
771: IF (l_error_level >= l_debug_level ) THEN
772: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.lock_row',FALSE);
773: END IF;
774: --bug 3199481 fnd logging changes: sdixit: end block
775: app_exception.raise_exception;
776: END IF;
777:
778: RETURN;
841: x_last_update_login := -1;
842: END IF;
843: ELSE
844: fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
845: --bug 3199481 fnd logging changes: sdixit: start block
846: IF (l_error_level >= l_debug_level ) THEN
847: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.update_row',FALSE);
848: END IF;
849: --bug 3199481 fnd logging changes: sdixit: end block
843: ELSE
844: fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
845: --bug 3199481 fnd logging changes: sdixit: start block
846: IF (l_error_level >= l_debug_level ) THEN
847: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.update_row',FALSE);
848: END IF;
849: --bug 3199481 fnd logging changes: sdixit: end block
850: app_exception.raise_exception;
851: END IF;
845: --bug 3199481 fnd logging changes: sdixit: start block
846: IF (l_error_level >= l_debug_level ) THEN
847: FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpzb.IGI_EXP_TUS_PKG.update_row',FALSE);
848: END IF;
849: --bug 3199481 fnd logging changes: sdixit: end block
850: app_exception.raise_exception;
851: END IF;
852:
853: before_dml(