[Home] [Help]
PACKAGE BODY: APPS.CE_STAT_LINES_INF_PKG
Source
1 PACKAGE BODY CE_STAT_LINES_INF_PKG as
2 /* $Header: cestalib.pls 120.1 2002/11/12 21:25:24 bhchung ship $ */
3 FUNCTION body_revision RETURN VARCHAR2 IS
4 BEGIN
5
6 RETURN '$Revision: 120.1 $';
7
8 END body_revision;
9
10 FUNCTION spec_revision RETURN VARCHAR2 IS
11 BEGIN
12
13 RETURN G_spec_revision;
14
15 END spec_revision;
16
17 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
18 X_Bank_Account_Num VARCHAR2,
19 X_Statement_Number VARCHAR2,
20 X_Line_Number NUMBER,
21 X_Trx_Date DATE,
22 X_Trx_Code VARCHAR2,
23 X_Effective_Date DATE,
24 X_Trx_Text VARCHAR2,
25 X_Invoice_Text VARCHAR2,
26 X_Bank_Account_Text VARCHAR2,
27 X_Amount NUMBER,
28 X_Charges_Amount NUMBER,
29 X_Currency_Code VARCHAR2,
30 X_Exchange_Rate NUMBER,
31 X_user_exchange_rate_type VARCHAR2,
32 X_exchange_rate_date DATE,
33 X_original_amount NUMBER,
34 X_Bank_Trx_Number VARCHAR2,
35 X_Customer_Text VARCHAR2,
36 X_Created_By NUMBER,
37 X_Creation_Date DATE,
38 X_Last_Updated_By NUMBER,
39 X_Last_Update_Date DATE,
40 X_Attribute_Category VARCHAR2,
41 X_Attribute1 VARCHAR2,
42 X_Attribute10 VARCHAR2,
43 X_Attribute11 VARCHAR2,
44 X_Attribute12 VARCHAR2,
45 X_Attribute13 VARCHAR2,
46 X_Attribute14 VARCHAR2,
47 X_Attribute15 VARCHAR2,
48 X_Attribute2 VARCHAR2,
49 X_Attribute3 VARCHAR2,
50 X_Attribute4 VARCHAR2,
51 X_Attribute5 VARCHAR2,
52 X_Attribute6 VARCHAR2,
53 X_Attribute7 VARCHAR2,
54 X_Attribute8 VARCHAR2,
55 X_Attribute9 VARCHAR2
56 ) IS
57 CURSOR C IS SELECT rowid FROM CE_STATEMENT_LINES_INTERFACE
58 WHERE statement_number = X_Statement_Number
59 AND bank_account_num = X_Bank_Account_Num
60 AND line_number = X_Line_Number;
61
62 BEGIN
63
64
65 INSERT INTO CE_STATEMENT_LINES_INTERFACE(
66 bank_account_num,
67 statement_number,
68 line_number,
69 trx_date,
70 trx_code,
71 effective_date,
72 trx_text,
73 invoice_text,
74 bank_account_text,
75 amount,
76 charges_amount,
77 currency_code,
78 exchange_rate,
79 user_exchange_rate_type,
80 exchange_rate_date,
81 original_amount,
82 bank_trx_number,
83 customer_text,
84 created_by,
85 creation_date,
86 last_updated_by,
87 last_update_date,
88 attribute_category,
89 attribute1,
90 attribute10,
91 attribute11,
92 attribute12,
93 attribute13,
94 attribute14,
95 attribute15,
96 attribute2,
97 attribute3,
98 attribute4,
99 attribute5,
100 attribute6,
101 attribute7,
102 attribute8,
103 attribute9
104
105 ) VALUES (
106
107 X_Bank_Account_Num,
108 X_Statement_Number,
109 X_Line_Number,
110 X_Trx_Date,
111 X_Trx_Code,
112 X_Effective_Date,
113 X_Trx_Text,
114 X_Invoice_Text,
115 X_Bank_Account_Text,
116 X_Amount,
117 X_Charges_Amount,
118 X_Currency_Code,
119 X_Exchange_Rate,
120 X_user_exchange_rate_type,
121 X_exchange_rate_date,
122 X_original_amount,
123 X_Bank_Trx_Number,
124 X_Customer_Text,
125 X_Created_By,
126 X_Creation_Date,
127 X_Last_Updated_By,
128 X_Last_Update_Date,
129 X_Attribute_Category,
130 X_Attribute1,
131 X_Attribute10,
132 X_Attribute11,
133 X_Attribute12,
134 X_Attribute13,
135 X_Attribute14,
136 X_Attribute15,
137 X_Attribute2,
138 X_Attribute3,
139 X_Attribute4,
140 X_Attribute5,
141 X_Attribute6,
142 X_Attribute7,
143 X_Attribute8,
144 X_Attribute9
145 );
146
147 OPEN C;
148 FETCH C INTO X_Rowid;
149 if (C%NOTFOUND) then
150 CLOSE C;
151 Raise NO_DATA_FOUND;
152 end if;
153 CLOSE C;
154 END Insert_Row;
155
156
157 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
158 X_Bank_Account_Num VARCHAR2,
159 X_Statement_Number VARCHAR2,
160 X_Line_Number NUMBER,
161 X_Trx_Date DATE,
162 X_Trx_Code VARCHAR2,
163 X_Effective_Date DATE,
164 X_Trx_Text VARCHAR2,
165 X_Invoice_Text VARCHAR2,
166 X_Bank_Account_Text VARCHAR2,
167 X_Amount NUMBER,
168 X_Charges_Amount NUMBER,
169 X_Currency_Code VARCHAR2,
170 X_Exchange_Rate NUMBER,
171 X_user_exchange_rate_type VARCHAR2,
172 X_exchange_rate_date DATE,
173 X_original_amount NUMBER,
177 X_Attribute1 VARCHAR2,
174 X_Bank_Trx_Number VARCHAR2,
175 X_Customer_Text VARCHAR2,
176 X_Attribute_Category VARCHAR2,
178 X_Attribute10 VARCHAR2,
179 X_Attribute11 VARCHAR2,
180 X_Attribute12 VARCHAR2,
181 X_Attribute13 VARCHAR2,
182 X_Attribute14 VARCHAR2,
183 X_Attribute15 VARCHAR2,
184 X_Attribute2 VARCHAR2,
185 X_Attribute3 VARCHAR2,
186 X_Attribute4 VARCHAR2,
187 X_Attribute5 VARCHAR2,
188 X_Attribute6 VARCHAR2,
189 X_Attribute7 VARCHAR2,
190 X_Attribute8 VARCHAR2,
191 X_Attribute9 VARCHAR2
192 ) IS
193 CURSOR C IS
194 SELECT *
195 FROM CE_STATEMENT_LINES_INTERFACE
196 WHERE rowid = X_Rowid
197 FOR UPDATE of Statement_Number NOWAIT;
198 Recinfo C%ROWTYPE;
199
200
201 BEGIN
202 OPEN C;
203 FETCH C INTO Recinfo;
204 if (C%NOTFOUND) then
205 CLOSE C;
206 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
207 APP_EXCEPTION.Raise_Exception;
208 end if;
209 CLOSE C;
210 if (
211
212 (Recinfo.bank_account_num = X_Bank_Account_Num)
213 AND (Recinfo.statement_number = X_Statement_Number)
214 AND (Recinfo.line_number = X_Line_Number)
215 AND (Recinfo.trx_date = X_Trx_Date)
216 AND ( (Recinfo.trx_code = X_Trx_Code)
217 OR ( (Recinfo.trx_code IS NULL)
218 AND (X_Trx_Code IS NULL)))
219 AND ( (Recinfo.effective_date = X_Effective_Date)
220 OR ( (Recinfo.effective_date IS NULL)
221 AND (X_Effective_Date IS NULL)))
222 AND ( (Recinfo.trx_text = X_Trx_Text)
223 OR ( (Recinfo.trx_text IS NULL)
224 AND (X_Trx_Text IS NULL)))
225 AND ( (Recinfo.invoice_text = X_Invoice_Text)
226 OR ( (Recinfo.invoice_text IS NULL)
227 AND (X_Invoice_Text IS NULL)))
228 AND ( (Recinfo.bank_account_text = X_Bank_Account_Text)
229 OR ( (Recinfo.Bank_Account_text IS NULL)
230 AND (X_Bank_Account_Text IS NULL)))
231 AND ( (Recinfo.amount = X_Amount)
232 OR ( (Recinfo.amount IS NULL)
233 AND (X_Amount IS NULL)))
234 AND ( (Recinfo.charges_amount = X_Charges_Amount)
235 OR ( (Recinfo.charges_amount IS NULL)
236 AND (X_Charges_Amount IS NULL)))
237 AND ( (Recinfo.currency_code = X_Currency_Code)
238 OR ( (Recinfo.currency_code IS NULL)
239 AND (X_Currency_Code IS NULL)))
240 AND ( (Recinfo.exchange_rate = X_Exchange_Rate)
241 OR ( (Recinfo.exchange_rate IS NULL)
242 AND (X_Exchange_Rate IS NULL)))
243 AND ( (Recinfo.user_exchange_rate_type = X_user_exchange_rate_type)
244 OR ( (Recinfo.user_exchange_rate_type IS NULL)
245 AND (X_user_exchange_rate_type IS NULL)))
246 AND ( (Recinfo.exchange_rate_date = X_exchange_rate_date)
247 OR ( (Recinfo.exchange_rate_date IS NULL)
248 AND (X_exchange_rate_date IS NULL)))
249 AND ( (Recinfo.original_amount = X_original_amount )
250 OR ( (Recinfo.original_amount IS NULL)
251 AND (X_original_amount IS NULL)))
252 AND ( (Recinfo.bank_trx_number = X_Bank_Trx_Number)
253 OR ( (Recinfo.bank_trx_number IS NULL)
254 AND (X_Bank_Trx_Number IS NULL)))
255 AND ( (Recinfo.customer_text = X_Customer_Text)
256 OR ( (Recinfo.customer_text IS NULL)
257 AND (X_Customer_Text IS NULL)))
258 AND ( (Recinfo.attribute_category = X_Attribute_Category)
259 OR ( (Recinfo.attribute_category IS NULL)
260 AND (X_Attribute_Category IS NULL)))
261 AND ( (Recinfo.attribute1 = X_Attribute1)
262 OR ( (Recinfo.attribute1 IS NULL)
263 AND (X_Attribute1 IS NULL)))
264 AND ( (Recinfo.attribute10 = X_Attribute10)
265 OR ( (Recinfo.attribute10 IS NULL)
266 AND (X_Attribute10 IS NULL)))
267 AND ( (Recinfo.attribute11 = X_Attribute11)
268 OR ( (Recinfo.attribute11 IS NULL)
269 AND (X_Attribute11 IS NULL)))
270 AND ( (Recinfo.attribute12 = X_Attribute12)
271 OR ( (Recinfo.attribute12 IS NULL)
272 AND (X_Attribute12 IS NULL)))
273 AND ( (Recinfo.attribute13 = X_Attribute13)
274 OR ( (Recinfo.attribute13 IS NULL)
275 AND (X_Attribute13 IS NULL)))
276 AND ( (Recinfo.attribute14 = X_Attribute14)
277 OR ( (Recinfo.attribute14 IS NULL)
278 AND (X_Attribute14 IS NULL)))
279 AND ( (Recinfo.attribute15 = X_Attribute15)
280 OR ( (Recinfo.attribute15 IS NULL)
281 AND (X_Attribute15 IS NULL)))
282 AND ( (Recinfo.attribute2 = X_Attribute2)
286 OR ( (Recinfo.attribute3 IS NULL)
283 OR ( (Recinfo.attribute2 IS NULL)
284 AND (X_Attribute2 IS NULL)))
285 AND ( (Recinfo.attribute3 = X_Attribute3)
287 AND (X_Attribute3 IS NULL)))
288 AND ( (Recinfo.attribute4 = X_Attribute4)
289 OR ( (Recinfo.attribute4 IS NULL)
290 AND (X_Attribute4 IS NULL)))
291 AND ( (Recinfo.attribute5 = X_Attribute5)
292 OR ( (Recinfo.attribute5 IS NULL)
296 AND (X_Attribute6 IS NULL)))
293 AND (X_Attribute5 IS NULL)))
294 AND ( (Recinfo.attribute6 = X_Attribute6)
295 OR ( (Recinfo.attribute6 IS NULL)
297 AND ( (Recinfo.attribute7 = X_Attribute7)
298 OR ( (Recinfo.attribute7 IS NULL)
299 AND (X_Attribute7 IS NULL)))
300 AND ( (Recinfo.attribute8 = X_Attribute8)
301 OR ( (Recinfo.attribute8 IS NULL)
302 AND (X_Attribute8 IS NULL)))
303 AND ( (Recinfo.attribute9 = X_Attribute9)
304 OR ( (Recinfo.attribute9 IS NULL)
305 AND (X_Attribute9 IS NULL)))
306 ) then
307 return;
308 else
309 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
310 APP_EXCEPTION.Raise_Exception;
311 end if;
312 END Lock_Row;
313
314
315
316 PROCEDURE Update_Row(X_Rowid VARCHAR2,
317 X_Bank_Account_Num VARCHAR2,
318 X_Statement_Number VARCHAR2,
319 X_Line_Number NUMBER,
320 X_Trx_Date DATE,
321 X_Trx_Code VARCHAR2,
322 X_Effective_Date DATE,
323 X_Trx_Text VARCHAR2,
324 X_Invoice_Text VARCHAR2,
325 X_Bank_Account_Text VARCHAR2,
326 X_Amount NUMBER,
327 X_Charges_Amount NUMBER,
328 X_Currency_Code VARCHAR2,
329 X_Exchange_Rate NUMBER,
330 X_user_exchange_rate_type VARCHAR2,
331 X_exchange_rate_date DATE,
332 X_original_amount NUMBER,
333 X_Bank_Trx_Number VARCHAR2,
334 X_Customer_Text VARCHAR2,
335 X_Last_Updated_By NUMBER,
336 X_Last_Update_Date DATE,
337 X_Attribute_Category VARCHAR2,
338 X_Attribute1 VARCHAR2,
339 X_Attribute10 VARCHAR2,
340 X_Attribute11 VARCHAR2,
341 X_Attribute12 VARCHAR2,
342 X_Attribute13 VARCHAR2,
343 X_Attribute14 VARCHAR2,
344 X_Attribute15 VARCHAR2,
345 X_Attribute2 VARCHAR2,
346 X_Attribute3 VARCHAR2,
347 X_Attribute4 VARCHAR2,
348 X_Attribute5 VARCHAR2,
349 X_Attribute6 VARCHAR2,
350 X_Attribute7 VARCHAR2,
351 X_Attribute8 VARCHAR2,
352 X_Attribute9 VARCHAR2
353
354 ) IS
355 BEGIN
356 UPDATE CE_STATEMENT_LINES_INTERFACE
357 SET
358 bank_account_num = X_Bank_Account_Num,
359 statement_number = X_Statement_Number,
363 effective_date = X_Effective_Date,
360 line_number = X_Line_Number,
361 trx_date = X_Trx_Date,
362 trx_code = X_Trx_Code,
364 trx_text = X_Trx_Text,
365 invoice_text = X_Invoice_Text,
366 bank_account_text = X_Bank_Account_Text,
367 amount = X_Amount,
368 charges_amount = X_Charges_Amount,
369 currency_code = X_Currency_Code,
370 exchange_rate = X_Exchange_Rate,
371 user_exchange_rate_type = X_user_exchange_rate_type,
372 exchange_rate_date = X_exchange_rate_date,
373 original_amount = X_original_amount,
374 bank_trx_number = X_Bank_Trx_Number,
375 customer_text = X_Customer_Text,
376 last_updated_by = X_Last_Updated_By,
377 last_update_date = X_Last_Update_Date,
378 attribute_category = X_Attribute_Category,
379 attribute1 = X_Attribute1,
380 attribute10 = X_Attribute10,
381 attribute11 = X_Attribute11,
382 attribute12 = X_Attribute12,
383 attribute13 = X_Attribute13,
384 attribute14 = X_Attribute14,
385 attribute15 = X_Attribute15,
386 attribute2 = X_Attribute2,
387 attribute3 = X_Attribute3,
388 attribute4 = X_Attribute4,
389 attribute5 = X_Attribute5,
390 attribute6 = X_Attribute6,
391 attribute7 = X_Attribute7,
392 attribute8 = X_Attribute8,
393 attribute9 = X_Attribute9
394 WHERE rowid = X_Rowid;
395
396 if (SQL%NOTFOUND) then
397 Raise NO_DATA_FOUND;
398 end if;
399 END Update_Row;
400 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
401 BEGIN
402 DELETE FROM CE_STATEMENT_LINES_INTERFACE
403 WHERE rowid = X_Rowid;
404
405 if (SQL%NOTFOUND) then
406 Raise NO_DATA_FOUND;
407 end if;
408 END Delete_Row;
409
410 PROCEDURE check_unique(X_statement_number VARCHAR2,
411 X_bank_account_num VARCHAR2,
412 X_line_number NUMBER,
413 X_row_id VARCHAR2) IS
414 CURSOR chk_duplicates is
415 SELECT 'Duplicate'
416 FROM CE_STATEMENT_LINES_INTERFACE csr
417 WHERE csr.statement_number = X_statement_number
418 AND csr.bank_account_num = X_bank_account_num
419 AND csr.line_number = X_line_number
420 AND ( X_row_id is null
421 OR csr.rowid <> chartorowid(X_row_id));
422 dummy VARCHAR2(100);
423 BEGIN
424 OPEN chk_duplicates;
425 FETCH chk_duplicates INTO dummy;
429 fnd_message.set_name('CE', 'CE_DUPLICATE_STAT_LINES_INF');
426
427 IF chk_duplicates%FOUND THEN
428 CLOSE chk_duplicates;
430 app_exception.raise_exception;
431 END IF;
432 CLOSE chk_duplicates;
433
434 EXCEPTION
435 WHEN app_exceptions.application_exception THEN
436 RAISE;
437 WHEN OTHERS THEN
438 fnd_message.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
439 fnd_message.set_token('PROCEDURE', 'ce_stat_lines_inf_pkg.check_unique'
440 );
441 RAISE;
442 END check_unique;
443
444 END CE_STAT_LINES_INF_PKG;