[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS_CERT_NI_NUMBERS_PKG
Source
1 PACKAGE BODY IGI_CIS_CERT_NI_NUMBERS_PKG AS
2 /* $Header: igicisbb.pls 115.9 2003/12/17 13:34:07 hkaniven ship $ */
3
4
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 l_path VARCHAR2(50) := 'IGI.PLSQL.igicisbb.IGI_CIS_CERT_NI_NUMBERS_PKG.';
13
14
15 PROCEDURE Lock_Row(p_row_id VARCHAR2
16 ,p_tax_rate_id NUMBER
17 ,p_ni_number VARCHAR2) IS
18
19 CURSOR c_lock IS
20 SELECT *
21 FROM igi_cis_cert_ni_numbers_all
22 WHERE rowid = p_row_id
23 FOR UPDATE OF tax_rate_id NOWAIT;
24
25 l_lock_rec c_lock%ROWTYPE;
26 BEGIN
27 OPEN c_lock;
28 FETCH c_lock INTO l_lock_rec;
29 IF (c_lock%NOTFOUND) THEN
30 CLOSE c_lock;
31
32 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
33 IF ( l_excep_level >= l_debug_level ) THEN
34 FND_LOG.MESSAGE (l_excep_level, l_path || 'Lock_Row',FALSE);
35 END IF;
36 APP_EXCEPTION.Raise_Exception;
37 END IF;
38 CLOSE c_lock;
39
40 IF l_lock_rec.tax_rate_id = p_tax_rate_id AND
41 l_lock_rec.ni_number = p_ni_number
42 THEN
43 RETURN;
44 ELSE
45 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
46 IF ( l_excep_level >= l_debug_level ) THEN
47 FND_LOG.MESSAGE (l_excep_level, l_path || 'Lock_Row',FALSE);
48 END IF;
49 APP_EXCEPTION.Raise_Exception;
50 END IF;
51 END Lock_Row;
52
53
54 PROCEDURE Insert_Row(p_row_id IN OUT NOCOPY VARCHAR2
55 ,p_org_id NUMBER
56 ,p_tax_rate_id NUMBER
57 ,p_ni_number VARCHAR2
58 ,p_creation_date DATE
59 ,p_created_by NUMBER
60 ,p_last_update_date DATE
61 ,p_last_updated_by NUMBER
62 ,p_last_update_login NUMBER
63 ,p_calling_sequence IN OUT NOCOPY VARCHAR2) IS
64
65 l_debug_info VARCHAR2(100);
66
67 --changed row_id to rowid
68 --by sdixit 4/7/03 for MOAC
69 CURSOR c_insert IS SELECT rowid
70 FROM igi_cis_cert_ni_numbers
71 WHERE tax_rate_id = p_tax_rate_id;
72
73 BEGIN
74 -- Update the calling sequence
75 p_calling_sequence := 'IGI_CIS_CERT_NI_NUMBERS_PKG.Insert_Row<-' ||
76 p_calling_sequence;
77
78 l_debug_info := 'Inserting into IGI_CIS_CERT_NI_NUMBERS_ALL';
79
80 INSERT INTO igi_cis_cert_ni_numbers_all
81 (tax_rate_id
82 ,ni_number
83 ,creation_date
84 ,created_by
85 ,last_update_date
86 ,last_updated_by
87 ,last_update_login
88 ,org_id)
89 VALUES
90 (p_tax_rate_id
91 ,p_ni_number
92 ,p_creation_date
93 ,p_created_by
94 ,p_last_update_date
95 ,p_last_updated_by
96 ,p_last_update_login
97 ,p_org_id);
98
99 l_debug_info := 'Open cursor c_insert';
100 OPEN c_insert;
101 l_debug_info := 'Fetch cursor c_insert';
102 FETCH c_insert INTO p_row_id;
103 IF c_insert%NOTFOUND THEN
104 l_debug_info := 'Close cursor c_insert NOTFOUND';
105 CLOSE c_insert;
106 RAISE No_Data_Found;
107 END IF;
108 l_debug_info := 'Close cursor c_insert';
109 CLOSE c_insert;
110 EXCEPTION
111 WHEN OTHERS THEN
112 FND_MESSAGE.Set_Name('SQLAP','AP_DEBUG');
113 FND_MESSAGE.Set_Token('ERROR',SQLERRM);
114 FND_MESSAGE.Set_Token('CALLING_SEQUENCE',p_calling_sequence);
115 FND_MESSAGE.Set_Token('DEBUG_INFO',l_debug_info);
116
117 IF ( l_unexp_level >= l_debug_level ) THEN
118 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
119 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
120 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
121 FND_LOG.MESSAGE ( l_unexp_level,l_path || 'Insert Row', FALSE);
122 END IF;
123
124 APP_EXCEPTION.Raise_Exception;
125 END Insert_Row;
126
127
128 PROCEDURE Update_Row(p_row_id VARCHAR2
129 ,p_ni_number VARCHAR2
130 ,p_last_update_date DATE
131 ,p_last_updated_by NUMBER
132 ,p_last_update_login NUMBER) IS
133 BEGIN
134 UPDATE igi_cis_cert_ni_numbers_all
135 SET ni_number = p_ni_number
136 ,last_update_date = p_last_update_date
137 ,last_updated_by = p_last_updated_by
138 ,last_update_login = p_last_update_login
139 WHERE rowid = p_row_id;
140
141 IF SQL%NOTFOUND THEN
142 RAISE No_Data_Found;
143 END IF;
144 END Update_Row;
145
146 END IGI_CIS_CERT_NI_NUMBERS_PKG;