[Home] [Help]
PACKAGE BODY: APPS.CE_STAT_HDRS_INF_PKG
Source
1 PACKAGE BODY CE_STAT_HDRS_INF_PKG as
2 /* $Header: cestahib.pls 120.6 2005/06/10 14:34:41 jikumar ship $ */
3 --
4 -- Package
5 -- ce_stat_hdrs_inf_pkg
6 -- Purpose
7 -- To contain routines for ce_hdrs_inf
8 -- History
9 -- XX-XX-95 Kai Pigg Created
10 FUNCTION body_revision RETURN VARCHAR2 IS
11 BEGIN
12
13 RETURN '$Revision: 120.6 $';
14
15 END body_revision;
16
17 FUNCTION spec_revision RETURN VARCHAR2 IS
18 BEGIN
19
20 RETURN G_spec_revision;
21
22 END spec_revision;
23
24 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
25 X_Statement_Number VARCHAR2,
26 X_Bank_Branch_Name VARCHAR2,
27 X_Bank_Account_Num VARCHAR2,
28 X_Statement_Date DATE,
29 X_Check_Digits VARCHAR2,
30 X_Control_Begin_Balance NUMBER,
31 X_Control_End_Balance NUMBER,
32 X_Cashflow_Balance NUMBER,
33 X_Int_Calc_Balance NUMBER,
34 X_Average_Close_Ledger_MTD NUMBER,
35 X_Average_Close_Ledger_YTD NUMBER,
36 X_Average_Close_Available_MTD NUMBER,
37 X_Average_Close_Available_YTD NUMBER,
38 X_One_Day_Float NUMBER,
39 X_Two_Day_Float NUMBER,
40 X_Control_Total_Dr NUMBER,
41 X_Control_Total_Cr NUMBER,
42 X_Control_Dr_Line_Count NUMBER,
43 X_Control_Cr_Line_Count NUMBER,
44 X_Control_Line_Count NUMBER,
45 X_Record_Status_Flag VARCHAR2,
46 X_Currency_Code VARCHAR2,
47 X_Created_By NUMBER,
48 X_Creation_Date DATE,
49 X_Attribute_Category VARCHAR2,
50 X_Attribute1 VARCHAR2,
51 X_Attribute10 VARCHAR2,
52 X_Attribute11 VARCHAR2,
53 X_Attribute12 VARCHAR2,
54 X_Attribute13 VARCHAR2,
55 X_Attribute14 VARCHAR2,
56 X_Attribute15 VARCHAR2,
57 X_Attribute2 VARCHAR2,
58 X_Attribute3 VARCHAR2,
59 X_Attribute4 VARCHAR2,
60 X_Attribute5 VARCHAR2,
61 X_Attribute6 VARCHAR2,
62 X_Attribute7 VARCHAR2,
63 X_Attribute8 VARCHAR2,
64 X_Attribute9 VARCHAR2,
65 --X_Org_Id NUMBER,
66 X_Bank_Name VARCHAR2,
67 X_Subsidiary_flag VARCHAR2
68 ) IS
69 CURSOR C IS SELECT rowid FROM CE_STATEMENT_HEADERS_INT
70 WHERE statement_number = X_Statement_Number
71 AND bank_account_num = X_Bank_Account_Num;
72
73 BEGIN
74
75
76 INSERT INTO CE_STATEMENT_HEADERS_INT(
77 statement_number,
78 bank_branch_name,
79 bank_account_num,
80 statement_date,
81 check_digits,
82 control_begin_balance,
83 control_end_balance,
84 cashflow_balance,
85 int_calc_balance,
86 average_close_ledger_mtd,
87 average_close_ledger_ytd,
88 average_close_available_mtd,
89 average_close_available_ytd,
90 one_day_float,
91 two_day_float,
92 control_total_dr,
93 control_total_cr,
94 control_dr_line_count,
95 control_cr_line_count,
96 control_line_count,
97 record_status_flag,
98 currency_code,
99 created_by,
100 creation_date,
101 attribute_category,
102 attribute1,
103 attribute10,
104 attribute11,
105 attribute12,
106 attribute13,
107 attribute14,
108 attribute15,
109 attribute2,
110 attribute3,
111 attribute4,
112 attribute5,
113 attribute6,
114 attribute7,
115 attribute8,
116 attribute9,
117 bank_name,
118 subsidiary_flag
119
120 ) VALUES (
121
122 X_Statement_Number,
123 X_Bank_Branch_Name,
124 X_Bank_Account_Num,
125 X_Statement_Date,
126 X_Check_Digits,
127 X_Control_Begin_Balance,
128 X_Control_End_Balance,
129 X_Cashflow_Balance,
130 X_Int_Calc_Balance,
131 X_Average_Close_Ledger_MTD,
132 X_Average_Close_Ledger_YTD,
133 X_Average_Close_Available_MTD,
134 X_Average_Close_Available_YTD,
135 X_One_Day_Float,
136 X_Two_Day_Float,
137 X_Control_Total_Dr,
138 X_Control_Total_Cr,
139 X_Control_Dr_Line_Count,
140 X_Control_Cr_Line_Count,
141 X_Control_Line_Count,
142 X_Record_Status_Flag,
143 X_Currency_Code,
144 X_Created_By,
145 X_Creation_Date,
146 X_Attribute_Category,
147 X_Attribute1,
148 X_Attribute10,
149 X_Attribute11,
150 X_Attribute12,
151 X_Attribute13,
152 X_Attribute14,
153 X_Attribute15,
154 X_Attribute2,
155 X_Attribute3,
156 X_Attribute4,
157 X_Attribute5,
158 X_Attribute6,
159 X_Attribute7,
160 X_Attribute8,
161 X_Attribute9,
162 X_Bank_Name,
163 X_Subsidiary_flag
164 );
165
166 OPEN C;
167 FETCH C INTO X_Rowid;
168 if (C%NOTFOUND) then
169 CLOSE C;
170 Raise NO_DATA_FOUND;
171 end if;
172 CLOSE C;
173 END Insert_Row;
174
175
176 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
177 X_Statement_Number VARCHAR2,
178 X_Bank_Branch_Name VARCHAR2,
179 X_Bank_Account_Num VARCHAR2,
180 X_Statement_Date DATE,
181 X_Check_Digits VARCHAR2,
182 X_Control_Begin_Balance NUMBER,
183 X_Control_End_Balance NUMBER,
184 X_Cashflow_Balance NUMBER,
185 X_Int_Calc_Balance NUMBER,
186 X_Average_Close_Ledger_MTD NUMBER,
187 X_Average_Close_Ledger_YTD NUMBER,
188 X_Average_Close_Available_MTD NUMBER,
189 X_Average_Close_Available_YTD NUMBER,
190 X_One_Day_Float NUMBER,
191 X_Two_Day_Float NUMBER,
192 X_Control_Total_Dr NUMBER,
193 X_Control_Total_Cr NUMBER,
194 X_Control_Dr_Line_Count NUMBER,
195 X_Control_Cr_Line_Count NUMBER,
196 X_Control_Line_Count NUMBER,
197 X_Record_Status_Flag VARCHAR2,
198 X_Currency_Code VARCHAR2,
199 X_Attribute_Category VARCHAR2,
200 X_Attribute1 VARCHAR2,
201 X_Attribute10 VARCHAR2,
202 X_Attribute11 VARCHAR2,
203 X_Attribute12 VARCHAR2,
204 X_Attribute13 VARCHAR2,
205 X_Attribute14 VARCHAR2,
206 X_Attribute15 VARCHAR2,
207 X_Attribute2 VARCHAR2,
208 X_Attribute3 VARCHAR2,
209 X_Attribute4 VARCHAR2,
210 X_Attribute5 VARCHAR2,
211 X_Attribute6 VARCHAR2,
212 X_Attribute7 VARCHAR2,
213 X_Attribute8 VARCHAR2,
214 X_Attribute9 VARCHAR2,
215 --X_Org_Id NUMBER,
216 X_Bank_Name VARCHAR2
217 ) IS
218 CURSOR C IS
219 SELECT *
220 FROM CE_STATEMENT_HEADERS_INT
221 WHERE rowid = X_Rowid
222 FOR UPDATE of Statement_Number NOWAIT;
223 Recinfo C%ROWTYPE;
224
225
226 BEGIN
227 OPEN C;
228 FETCH C INTO Recinfo;
229 if (C%NOTFOUND) then
230 CLOSE C;
231 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
232 APP_EXCEPTION.Raise_Exception;
233 end if;
234 CLOSE C;
235 if (
236
237 (Recinfo.statement_number = X_Statement_Number)
238 AND ( (Recinfo.bank_branch_name = X_Bank_Branch_Name)
239 OR ( (Recinfo.bank_branch_name IS NULL)
240 AND (X_Bank_Branch_Name IS NULL)))
241 AND (Recinfo.bank_account_num = X_Bank_Account_Num)
242 AND (Recinfo.statement_date = X_Statement_Date)
243 AND ( (Recinfo.check_digits = X_Check_Digits)
244 OR ( (Recinfo.check_digits IS NULL)
245 AND (X_Check_Digits IS NULL)))
246 AND ( (Recinfo.control_begin_balance = X_Control_Begin_Balance)
247 OR ( (Recinfo.control_begin_balance IS NULL)
248 AND (X_Control_Begin_Balance IS NULL)))
249 AND ( (Recinfo.control_end_balance = X_Control_End_Balance)
250 OR ( (Recinfo.control_end_balance IS NULL)
251 AND (X_Control_End_Balance IS NULL)))
252 AND ( (Recinfo.cashflow_balance = X_Cashflow_Balance)
253 OR ( (Recinfo.cashflow_balance IS NULL)
254 AND (X_Cashflow_Balance IS NULL)))
255 AND ( (Recinfo.int_calc_balance = X_Int_Calc_Balance)
256 OR ( (Recinfo.int_calc_balance IS NULL)
257 AND (X_Int_calc_Balance IS NULL)))
258 AND ( (Recinfo.average_close_ledger_mtd = X_Average_Close_Ledger_MTD)
259 OR ( (Recinfo.average_close_ledger_mtd IS NULL)
260 AND (X_Average_Close_Ledger_MTD IS NULL)))
261 AND ( (Recinfo.average_close_ledger_ytd = X_Average_Close_Ledger_YTD)
262 OR ( (Recinfo.average_close_ledger_ytd IS NULL)
263 AND (X_Average_Close_Ledger_YTD IS NULL)))
264 AND ( (Recinfo.average_close_available_mtd = X_Average_Close_Available_MTD)
265 OR ( (Recinfo.average_close_available_mtd IS NULL)
266 AND (X_Average_Close_Available_MTD IS NULL)))
267 AND ( (Recinfo.average_close_available_ytd = X_Average_Close_Available_YTD)
268 OR ( (Recinfo.average_close_available_ytd IS NULL)
269 AND (X_Average_Close_Available_YTD IS NULL)))
270 AND ( (Recinfo.one_day_float = X_One_Day_Float)
271 OR ( (Recinfo.one_day_float IS NULL)
272 AND (X_One_Day_Float IS NULL)))
273 AND ( (Recinfo.two_day_float = X_Two_Day_Float)
274 OR ( (Recinfo.two_day_float IS NULL)
275 AND (X_Two_Day_Float IS NULL)))
276 AND ( (Recinfo.control_total_dr = X_Control_Total_Dr)
277 OR ( (Recinfo.control_total_dr IS NULL)
278 AND (X_Control_Total_Dr IS NULL)))
279 AND ( (Recinfo.control_total_cr = X_Control_Total_Cr)
280 OR ( (Recinfo.control_total_cr IS NULL)
281 AND (X_Control_Total_Cr IS NULL)))
282 AND ( (Recinfo.control_dr_line_count = X_Control_Dr_Line_Count)
283 OR ( (Recinfo.control_dr_line_count IS NULL)
284 AND (X_Control_Dr_Line_Count IS NULL)))
285 AND ( (Recinfo.control_cr_line_count = X_Control_Cr_Line_Count)
286 OR ( (Recinfo.control_cr_line_count IS NULL)
287 AND (X_Control_Cr_Line_Count IS NULL)))
288 AND ( (Recinfo.control_line_count = X_Control_Line_Count)
289 OR ( (Recinfo.control_line_count IS NULL)
290 AND (X_Control_Line_Count IS NULL)))
291 AND ( (Recinfo.record_status_flag = X_Record_Status_Flag)
292 OR ( (Recinfo.record_status_flag IS NULL)
293 AND (X_Record_Status_Flag IS NULL)))
294 AND ( (Recinfo.currency_code = X_Currency_Code)
295 OR ( (Recinfo.currency_code IS NULL)
296 AND (X_Currency_Code IS NULL)))
297 AND ( (Recinfo.attribute_category = X_Attribute_Category)
298 OR ( (Recinfo.attribute_category IS NULL)
299 AND (X_Attribute_Category IS NULL)))
300 AND ( (Recinfo.attribute1 = X_Attribute1)
301 OR ( (Recinfo.attribute1 IS NULL)
302 AND (X_Attribute1 IS NULL)))
303 AND ( (Recinfo.attribute10 = X_Attribute10)
304 OR ( (Recinfo.attribute10 IS NULL)
305 AND (X_Attribute10 IS NULL)))
306 AND ( (Recinfo.attribute11 = X_Attribute11)
307 OR ( (Recinfo.attribute11 IS NULL)
308 AND (X_Attribute11 IS NULL)))
309 AND ( (Recinfo.attribute12 = X_Attribute12)
310 OR ( (Recinfo.attribute12 IS NULL)
311 AND (X_Attribute12 IS NULL)))
312 AND ( (Recinfo.attribute13 = X_Attribute13)
313 OR ( (Recinfo.attribute13 IS NULL)
314 AND (X_Attribute13 IS NULL)))
315 AND ( (Recinfo.attribute14 = X_Attribute14)
316 OR ( (Recinfo.attribute14 IS NULL)
317 AND (X_Attribute14 IS NULL)))
318 AND ( (Recinfo.attribute15 = X_Attribute15)
319 OR ( (Recinfo.attribute15 IS NULL)
320 AND (X_Attribute15 IS NULL)))
321 AND ( (Recinfo.attribute2 = X_Attribute2)
322 OR ( (Recinfo.attribute2 IS NULL)
323 AND (X_Attribute2 IS NULL)))
324 AND ( (Recinfo.attribute3 = X_Attribute3)
325 OR ( (Recinfo.attribute3 IS NULL)
326 AND (X_Attribute3 IS NULL)))
327 AND ( (Recinfo.attribute4 = X_Attribute4)
328 OR ( (Recinfo.attribute4 IS NULL)
329 AND (X_Attribute4 IS NULL)))
330 AND ( (Recinfo.attribute5 = X_Attribute5)
331 OR ( (Recinfo.attribute5 IS NULL)
332 AND (X_Attribute5 IS NULL)))
333 AND ( (Recinfo.attribute6 = X_Attribute6)
334 OR ( (Recinfo.attribute6 IS NULL)
338 AND (X_Attribute7 IS NULL)))
335 AND (X_Attribute6 IS NULL)))
336 AND ( (Recinfo.attribute7 = X_Attribute7)
337 OR ( (Recinfo.attribute7 IS NULL)
339 AND ( (Recinfo.attribute8 = X_Attribute8)
340 OR ( (Recinfo.attribute8 IS NULL)
341 AND (X_Attribute8 IS NULL)))
342 AND ( (Recinfo.attribute9 = X_Attribute9)
343 OR ( (Recinfo.attribute9 IS NULL)
344 AND (X_Attribute9 IS NULL)))
345 AND ( (Recinfo.bank_name = X_Bank_Name)
346 OR ( (Recinfo.bank_name IS NULL)
347 AND (X_Bank_Name IS NULL)))
348 ) then
349 return;
350 else
351 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
352 APP_EXCEPTION.Raise_Exception;
353 end if;
354 END Lock_Row;
355
356
357
358 PROCEDURE Update_Row(X_Rowid VARCHAR2,
359 X_Statement_Number VARCHAR2,
360 X_Bank_Branch_Name VARCHAR2,
361 X_Bank_Account_Num VARCHAR2,
362 X_Statement_Date DATE,
363 X_Check_Digits VARCHAR2,
364 X_Control_Begin_Balance NUMBER,
365 X_Control_End_Balance NUMBER,
366 X_Cashflow_Balance NUMBER,
367 X_Int_Calc_Balance NUMBER,
368 X_Average_Close_Ledger_MTD NUMBER,
369 X_Average_Close_Ledger_YTD NUMBER,
370 X_Average_Close_Available_MTD NUMBER,
371 X_Average_Close_Available_YTD NUMBER,
372 X_One_Day_Float NUMBER,
373 X_Two_Day_Float NUMBER,
374 X_Control_Total_Dr NUMBER,
375 X_Control_Total_Cr NUMBER,
376 X_Control_Dr_Line_Count NUMBER,
377 X_Control_Cr_Line_Count NUMBER,
378 X_Control_Line_Count NUMBER,
379 X_Record_Status_Flag VARCHAR2,
380 X_Currency_Code VARCHAR2,
381 X_Attribute_Category VARCHAR2,
382 X_Attribute1 VARCHAR2,
383 X_Attribute10 VARCHAR2,
384 X_Attribute11 VARCHAR2,
385 X_Attribute12 VARCHAR2,
386 X_Attribute13 VARCHAR2,
387 X_Attribute14 VARCHAR2,
388 X_Attribute15 VARCHAR2,
389 X_Attribute2 VARCHAR2,
390 X_Attribute3 VARCHAR2,
391 X_Attribute4 VARCHAR2,
392 X_Attribute5 VARCHAR2,
393 X_Attribute6 VARCHAR2,
394 X_Attribute7 VARCHAR2,
395 X_Attribute8 VARCHAR2,
396 X_Attribute9 VARCHAR2,
397 --X_Org_Id NUMBER,
398 X_Bank_Name VARCHAR2,
399 X_Subsidiary_flag VARCHAR2
400 ) IS
401 BEGIN
402 UPDATE CE_STATEMENT_HEADERS_INT
403 SET
404 statement_number = X_Statement_Number,
405 bank_branch_name = X_Bank_Branch_Name,
406 bank_account_num = X_Bank_Account_Num,
407 statement_date = X_Statement_Date,
408 check_digits = X_Check_Digits,
409 control_begin_balance = X_Control_Begin_Balance,
410 control_end_balance = X_Control_End_Balance,
411 cashflow_balance = X_Cashflow_Balance,
412 int_calc_balance = X_Int_Calc_Balance,
413 average_close_ledger_mtd = X_Average_Close_Ledger_MTD,
414 average_close_ledger_ytd = X_Average_Close_Ledger_YTD,
415 average_close_available_mtd = X_Average_Close_Available_MTD,
416 average_close_available_ytd = X_Average_Close_Available_YTD,
417 one_day_float = X_One_Day_Float,
418 two_day_float = X_Two_Day_Float,
419 control_total_dr = X_Control_Total_Dr,
420 control_total_cr = X_Control_Total_Cr,
421 control_dr_line_count = X_Control_Dr_Line_Count,
422 control_cr_line_count = X_Control_Cr_Line_Count,
423 control_line_count = X_Control_Line_Count,
424 record_status_flag = X_Record_Status_Flag,
425 currency_code = X_Currency_Code,
426 attribute_category = X_Attribute_Category,
427 attribute1 = X_Attribute1,
428 attribute10 = X_Attribute10,
429 attribute11 = X_Attribute11,
430 attribute12 = X_Attribute12,
431 attribute13 = X_Attribute13,
432 attribute14 = X_Attribute14,
433 attribute15 = X_Attribute15,
434 attribute2 = X_Attribute2,
435 attribute3 = X_Attribute3,
436 attribute4 = X_Attribute4,
437 attribute5 = X_Attribute5,
438 attribute6 = X_Attribute6,
439 attribute7 = X_Attribute7,
440 attribute8 = X_Attribute8,
441 attribute9 = X_Attribute9,
442 bank_name = X_Bank_Name,
443 subsidiary_flag = X_Subsidiary_flag
444 WHERE rowid = X_Rowid;
445
446 if (SQL%NOTFOUND) then
447 Raise NO_DATA_FOUND;
448 end if;
449 END Update_Row;
450 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
451 BEGIN
452 DELETE FROM CE_STATEMENT_HEADERS_INT
453 WHERE rowid = X_Rowid;
454
455 if (SQL%NOTFOUND) then
456 Raise NO_DATA_FOUND;
457 end if;
458 END Delete_Row;
459
460 PROCEDURE check_unique(X_statement_number VARCHAR2,
461 X_bank_account_num VARCHAR2,
462 X_row_id VARCHAR2) IS
463 CURSOR chk_duplicates is
464 SELECT 'Duplicate'
465 FROM CE_STATEMENT_HEADERS_INT csr
466 WHERE csr.statement_number = X_statement_number
467 AND csr.bank_account_num = X_bank_account_num
468 AND ( X_row_id is null
469 OR csr.rowid <> chartorowid(X_row_id));
470 dummy VARCHAR2(100);
471 BEGIN
472 OPEN chk_duplicates;
473 FETCH chk_duplicates INTO dummy;
474
475 IF chk_duplicates%FOUND THEN
476 CLOSE chk_duplicates;
477 fnd_message.set_name('CE', 'CE_DUPLICATE_STAT_HDRS_INF');
478 app_exception.raise_exception;
479 END IF;
480 CLOSE chk_duplicates;
481
482 EXCEPTION
483 WHEN app_exceptions.application_exception THEN
484 RAISE;
485 WHEN OTHERS THEN
486 fnd_message.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
487 fnd_message.set_token('PROCEDURE', 'ce_stat_hdrs_inf_pkg.check_unique');
488 RAISE;
489 END check_unique;
490 --
491
492 END CE_STAT_HDRS_INF_PKG;