[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR_CHARGE_HEADERS_PKG
Source
1 PACKAGE BODY IGI_ITR_CHARGE_HEADERS_PKG as
2 -- $Header: igiitrsb.pls 120.5.12000000.1 2007/09/12 10:32:39 mbremkum 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
13
14 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
15 X_It_Header_Id IN OUT NOCOPY NUMBER,
16 X_Set_Of_Books_Id NUMBER,
17 X_Name VARCHAR2,
18 X_It_Period_Name VARCHAR2,
19 X_Submit_Flag VARCHAR2,
20 X_It_Originator_Id VARCHAR2,
21 X_Gl_Date DATE,
22 X_Currency_Code VARCHAR2,
23 X_Encumbrance_Type_Id NUMBER,
24 X_Employee_Id NUMBER,
25 X_Entered_Dr NUMBER,
26 X_Entered_Cr NUMBER,
27 X_Submit_Date DATE,
28 X_Charge_Center_Id NUMBER,
29 X_Creation_Date DATE,
30 X_Created_By NUMBER,
31 X_Last_Update_Login NUMBER,
32 X_Last_Update_Date DATE,
33 X_Last_Updated_By NUMBER
34 ) IS
35
36 CURSOR C IS SELECT rowid
37 FROM igi_itr_charge_headers
38 WHERE it_header_id = X_It_Header_Id;
39
40 CURSOR C2 IS SELECT igi_itr_charge_headers_s.nextval FROM sys.dual;
41
42 BEGIN
43
44 IF X_It_Header_Id is null THEN
45 OPEN C2;
46 FETCH C2 INTO X_It_Header_Id;
47 CLOSE C2;
48 END IF;
49
50 INSERT INTO igi_itr_charge_headers(
51 it_header_id
52 ,set_of_books_id
53 ,name
54 ,it_period_name
55 ,submit_flag
56 ,it_originator_id
57 ,gl_date
58 ,currency_code
59 ,encumbrance_type_id
60 ,employee_id
61 ,entered_dr
62 ,entered_cr
63 ,submit_date
64 ,charge_center_id
65 ,creation_date
66 ,created_by
67 ,last_update_login
68 ,last_update_date
69 ,last_updated_by
70 ) VALUES (
71 X_It_Header_Id
72 ,X_Set_Of_Books_Id
73 ,X_Name
74 ,X_It_Period_Name
75 ,X_Submit_Flag
76 ,X_It_Originator_Id
77 ,X_Gl_Date
78 ,X_Currency_Code
79 ,X_Encumbrance_Type_Id
80 ,X_Employee_Id
81 ,X_Entered_Dr
82 ,X_Entered_Cr
83 ,X_Submit_Date
84 ,X_Charge_Center_Id
85 ,X_Creation_date
86 ,X_Created_By
87 ,X_Last_Update_Login
88 ,X_Last_Update_Date
89 ,X_Last_Updated_By
90 );
91
92 OPEN C;
93 FETCH C INTO X_Rowid;
94 IF (C%NOTFOUND) THEN
95 CLOSE C;
96 RAISE NO_DATA_FOUND;
97 END IF;
98 CLOSE C;
99
100 END Insert_Row;
101
102
103 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
104 X_It_Header_Id NUMBER,
105 X_Set_Of_Books_Id NUMBER,
106 X_Name VARCHAR2,
107 X_It_Period_Name VARCHAR2,
108 X_Submit_Flag VARCHAR2,
109 X_It_Originator_Id VARCHAR2,
110 X_Gl_Date DATE,
111 X_Currency_Code VARCHAR2,
112 X_Encumbrance_Type_Id NUMBER,
113 X_Employee_Id NUMBER,
114 X_Entered_Dr NUMBER,
115 X_Entered_Cr NUMBER,
116 X_Submit_Date DATE,
117 X_Charge_Center_Id NUMBER,
118 X_Creation_Date DATE,
119 X_Created_By NUMBER,
120 X_Last_Update_Login NUMBER,
121 X_Last_Update_Date DATE,
122 X_Last_Updated_By NUMBER
123 ) IS
124
125 CURSOR C IS
126 SELECT *
127 FROM igi_itr_charge_headers
128 WHERE rowid = X_Rowid
129 FOR UPDATE of it_header_id NOWAIT;
130
131 Recinfo C%ROWTYPE;
132
133
134 BEGIN
135
136 OPEN C;
137 FETCH C INTO Recinfo;
138 IF (C%NOTFOUND) THEN
139 CLOSE C;
140 fnd_message.set_name('FND','FORM_RECORD_DELETED');
141 IF( l_excep_level >= l_debug_level) THEN
142 FND_LOG.MESSAGE(l_excep_level,'igi.plsql.igiitrsb.IGI_ITR_CHARGE_HEADERS_PKG.lock_row.msg1', FALSE);
143 END IF;
144 app_exception.raise_exception;
145 END IF;
146 CLOSE C;
147 IF (
148 (Recinfo.it_header_id = X_It_Header_Id)
149 AND (Recinfo.set_of_books_id = X_Set_Of_Books_Id)
150 AND (Recinfo.name = X_Name)
151 AND (Recinfo.it_period_name = X_It_Period_Name)
152 AND (Recinfo.submit_flag = X_Submit_Flag)
153 AND (Recinfo.it_originator_id = X_It_Originator_Id)
154 AND (Recinfo.gl_date = X_Gl_Date)
155 AND (Recinfo.currency_code = X_Currency_Code)
156 AND ( (Recinfo.encumbrance_type_id = X_Encumbrance_Type_Id)
157 OR ( (Recinfo.encumbrance_type_id IS NULL)
158 AND(X_Encumbrance_Type_Id IS NULL) ))
159 AND ( (Recinfo.employee_id = X_Employee_Id)
160 OR ( (Recinfo.employee_id IS NULL)
161 AND (X_Employee_Id IS NULL) ))
162 AND ( (Recinfo.entered_dr = X_Entered_Dr)
163 OR ( (Recinfo.entered_dr IS NULL)
164 AND (X_Entered_Dr IS NULL) ))
165 AND ( (Recinfo.entered_cr = X_Entered_Cr)
166 OR ( (Recinfo.entered_cr IS NULL)
167 AND (X_Entered_Cr IS NULL) ))
168 AND ( (Recinfo.submit_date = X_Submit_Date)
169 OR ( (Recinfo.submit_date IS NULL)
170 AND (X_Submit_Date IS NULL) ))
171 AND (Recinfo.charge_center_id = X_Charge_Center_Id)
172 AND (Recinfo.creation_date = X_Creation_Date)
173 AND (Recinfo.created_by = X_Created_By)
174 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
175 OR ( (Recinfo.last_update_login IS NULL)
176 AND (X_Last_Update_Login IS NULL) ))
177 AND ( (Recinfo.last_update_date = X_Last_Update_Date)
178 OR ( (Recinfo.last_update_date IS NULL)
179 AND (X_Last_Update_Date IS NULL) ))
180 AND ( (Recinfo.last_updated_by = X_Last_Updated_By)
181 OR ( (Recinfo.last_updated_by IS NULL)
182 AND (X_Last_Updated_By IS NULL) ))
183 ) THEN
184 return;
185 ELSE
186 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
187 IF( l_excep_level >= l_debug_level) THEN
188 FND_LOG.MESSAGE(l_excep_level,'igi.plsql.igiitrsb.IGI_ITR_CHARGE_HEADERS_PKG.lock_row.msg2', FALSE);
189 END IF;
190 APP_EXCEPTION.raise_exception;
191 END IF;
192
193 END Lock_Row;
194
195
196 PROCEDURE Update_Row(X_Rowid VARCHAR2,
197 X_It_Header_Id NUMBER,
198 X_Set_Of_Books_Id NUMBER,
199 X_Name VARCHAR2,
200 X_It_Period_Name VARCHAR2,
201 X_Submit_Flag VARCHAR2,
202 X_It_Originator_Id VARCHAR2,
203 X_Gl_Date DATE,
204 X_Currency_Code VARCHAR2,
205 X_Encumbrance_Type_Id NUMBER,
206 X_Employee_Id NUMBER,
207 X_Entered_Dr NUMBER,
208 X_Entered_Cr NUMBER,
209 X_Submit_Date DATE,
210 X_Charge_Center_Id NUMBER,
211 X_Creation_Date DATE,
212 X_Created_By NUMBER,
213 X_Last_Update_Login NUMBER,
214 X_Last_Update_Date DATE,
215 X_Last_Updated_By NUMBER
216 ) IS
217 BEGIN
218
219 UPDATE igi_itr_charge_headers
220 SET
221 it_header_id = X_It_Header_Id,
222 set_of_books_id = X_Set_Of_Books_Id,
223 name = X_Name,
224 it_period_name = X_It_Period_Name,
225 submit_flag = X_Submit_Flag,
226 it_originator_id = X_It_Originator_Id,
227 gl_date = X_Gl_Date,
228 currency_code = X_Currency_Code,
229 encumbrance_type_id = X_Encumbrance_Type_Id,
230 employee_id = X_Employee_Id,
231 entered_dr = X_Entered_Dr,
232 entered_cr = X_Entered_Cr,
233 submit_date = X_Submit_Date,
234 charge_center_id = X_Charge_Center_Id,
235 creation_date = X_Creation_Date,
236 created_by = X_Created_By,
237 last_update_login = X_Last_Update_Login,
238 last_update_date = X_Last_Update_Date,
239 last_updated_by = X_Last_Updated_By
240 WHERE rowid = X_Rowid;
241
242 IF SQL%NOTFOUND THEN
243 raise NO_DATA_FOUND;
244 END IF;
245
246 END Update_Row;
247
248
249
250
251
252
253
254
255
256 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
257
258 -- OPSF(I) ITR Bug 1764441 22-May-2001 S Brewer Start(1)
259 CURSOR c_get_it_header_id(p_rowid VARCHAR2)
260 IS
261 SELECT it_header_id
262 FROM igi_itr_charge_headers
263 WHERE rowid = p_rowid;
264
265 l_it_header_id NUMBER;
266 -- OPSF(I) ITR Bug 1764441 22-May-2001 S Brewer End(1)
267
268 BEGIN
269
270 -- OPSF(I) ITR Bug 1764441 22-May-2001 S Brewer Start(2)
271 -- Delete any existing lines from the charge lines table before
272 -- deleting the charge header
273 OPEN c_get_it_header_id(X_Rowid);
274 FETCH c_get_it_header_id INTO l_it_header_id;
275 IF c_get_it_header_id%FOUND THEN
276 igi_itr_charge_lines_pkg.delete_lines(l_it_header_id);
277 END IF;
278 CLOSE c_get_it_header_id;
279 -- OPSF(I) ITR Bug 1764441 22-May-2001 S Brewer End(2)
280
281
282 DELETE FROM igi_itr_charge_headers
283 WHERE rowid = X_Rowid;
284
285 IF (SQL%NOTFOUND) THEN
286 raise NO_DATA_FOUND;
287 END IF;
288 END Delete_Row;
289
290
291 PROCEDURE check_unique( x_rowid VARCHAR2,
292 x_name VARCHAR2,
293 x_set_of_books_id NUMBER) IS
294
295 CURSOR c_dup IS
296 SELECT 'Duplicate'
297 FROM igi_itr_charge_headers ch
298 WHERE ch.name = x_name
299 AND ch.set_of_books_id = x_set_of_books_id
300 AND (x_rowid IS NULL
301 OR
302 ch.rowid <> x_rowid);
303
304 dummy VARCHAR2(100);
305
306 BEGIN
307
308 OPEN c_dup;
309 FETCH c_dup INTO dummy;
310
311 IF c_dup%FOUND THEN
312 CLOSE c_dup;
313 fnd_message.set_name('IGI','IGI_ITR_DPL_ITR');
314 IF( l_error_level >= l_debug_level) THEN
315 FND_LOG.MESSAGE(l_error_level,'igi.plsql.igiitrqb.IGI_ITR_CHARGE_HEADERS_PKG.check_unique.msg3', FALSE);
316 END IF;
317 app_exception.raise_exception;
318 END IF;
319
320 CLOSE c_dup;
321
322 END check_unique;
323
324
325 END IGI_ITR_CHARGE_HEADERS_PKG;