[Home] [Help]
PACKAGE BODY: APPS.CE_AUTO_BANK_UNREC
Source
1 PACKAGE BODY CE_AUTO_BANK_UNREC AS
2 /* $Header: ceaurecb.pls 120.20.12020000.2 2012/07/11 07:21:49 vnetan ship $ */
3
4 l_DEBUG VARCHAR2(1);
5
6 FUNCTION body_revision RETURN VARCHAR2 IS
7 BEGIN
8 RETURN '$Revision: 120.20.12020000.2 $';
9 END body_revision;
10
11 FUNCTION spec_revision RETURN VARCHAR2 IS
12 BEGIN
13 RETURN G_spec_revision;
14 END spec_revision;
15
16 /* ---------------------------------------------------------------------
17 | PUBLIC PROCEDURE |
18 | Unreconcile_All |
19 | |
20 | DESCRIPTION |
21 | Unreconcile all the reconciled transactions in given bank |
22 | statement |
23 -----------------------------------------------------------------------*/
24 PROCEDURE unreconcile_all(
25 errbuf OUT NOCOPY VARCHAR2,
26 retcode OUT NOCOPY NUMBER,
27 X_bank_account_id IN NUMBER,
28 X_statement_number IN VARCHAR2,
29 X_statement_line_id IN NUMBER,
30 X_display_debug IN VARCHAR2,
31 X_debug_path IN VARCHAR2,
32 X_debug_file IN VARCHAR2
33 )IS
34 /* 2738067 Added statement_header_id to the selected fields in the cursor.*/
35 CURSOR C_reconciled IS
36 SELECT row_id,
37 statement_header_number,
38 statement_line_id,
39 trx_type,
40 clearing_trx_type,
41 batch_id,
42 trx_id,
43 cash_receipt_id,
44 trx_date,
45 gl_date,
46 status,
47 cleared_date,
48 amount,
49 bank_errors,
50 bank_charges,
51 bank_account_amount,
52 bank_currency_code,
53 exchange_rate_type,
54 exchange_rate_date,
55 exchange_rate,
56 statement_complete_flag,
57 statement_header_id, org_id, legal_entity_id, bank_account_id
58 FROM CE_RECONCILED_TRANSACTIONS_V
59 WHERE bank_account_id = X_bank_account_id
60 AND statement_header_number = NVL(X_statement_number,statement_header_number)
61 AND statement_line_id = NVL(X_statement_line_id,statement_line_id)
62 ORDER BY statement_header_number, statement_line_id;
63
64 /* 2853915 Added the nvl condition for statement_header_number */
65 CURSOR C_oifs(P_BANK_ACCOUNT_ID number) IS
66 SELECT RECON_OI_FLOAT_STATUS
67 FROM CE_BANK_ACCOUNTS
68 WHERE BANK_ACCOUNT_ID = NVL(X_bank_account_id, P_BANK_ACCOUNT_ID);
69
70 /* SELECT open_interface_float_status
71 FROM CE_SYSTEM_PARAMETERS_ALL sys
72 where exists ( select ACCOUNT_OWNER_ORG_ID
73 from CE_BANK_ACCOUNTS --ACCTS_GT_V --ce_BANK_ACCOUNTS_v
74 where bank_account_id = X_bank_account_id
75 and ACCOUNT_OWNER_ORG_ID = sys.legal_entity_id);*/
76
77 l_cnt NUMBER := 0;
78 l_status VARCHAR2(30);
79 l_statement_complete_flag VARCHAR2(1);
80 l_next_statement_complete_flag VARCHAR2(1);
81 /*Bug 3847491 Added*/
82
83 cash_receipt_history_id NUMBER;
84 match_correction_type VARCHAR2(30);
85 l_stmt_stmt_num CE_STATEMENT_HEADERS.STATEMENT_NUMBER%TYPE;
86 l_trx_stmt_num CE_STATEMENT_HEADERS.STATEMENT_NUMBER%TYPE;
87 l_skip_lock_unclear VARCHAR2(1) := 'N';
88 l_app_id NUMBER;
89 l_set_of_books_id NUMBER;
90 l_count NUMBER;
91 l_count_ap_ar NUMBER; -- 13644150: Added
92 l_status1 VARCHAR2(30);
93 l_status2 VARCHAR2(30);
94 p_bank_account_id NUMBER;
95 l_recon_accounting_flag VARCHAR2(2):= 'Y'; --10102287
96
97 BEGIN
98 l_DEBUG := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
99 cep_standard.debug('>>CE_AUTO_BANK_UNREC.unreconcile_all'||sysdate);
100 cep_standard.debug('::Request Parameters::');
101 cep_standard.debug('X_bank_account_id='|| X_bank_account_id);
102 cep_standard.debug('X_statement_number='|| X_statement_number);
103 cep_standard.debug('X_statement_line_id ='|| X_statement_line_id);
104
105 -- populate ce_security_profiles_tmp table with ce_security_profiles_v
106 CEP_STANDARD.init_security;
107
108 cep_standard.debug('::Fetching reconciled transactions...');
109 FOR C_rec IN C_reconciled LOOP
110 -- re-init loop variables
111 l_skip_lock_unclear := 'N'; -- Bug 3427433 added this line.
112 l_recon_accounting_flag := 'Y'; -- Bug 13644150 added this line.
113 l_app_id := NULL; -- Bug 13644150 added this line.
114 l_count := 0;
115 l_count_ap_ar := 0;
116 l_status := NULL;
117 l_next_statement_complete_flag := NULL;
118
119 cep_standard.debug('--------- new statement line/trx -------------');
120 cep_standard.debug('C_rec.bank_account_id=' || C_rec.bank_account_id||
121 ', C_rec.org_id='||C_rec.org_id||
122 ', C_rec.legal_entity_id='||C_rec.legal_entity_id||
123 ', C_rec.statement_header_id='|| C_rec.statement_header_id);
124 cep_standard.debug('C_rec.row_id=' || C_rec.row_id||
125 ', C_rec.statement_line_id=' || C_rec.statement_line_id||
126 ', C_rec.status=' || C_rec.status||
127 ', C_rec.amount=' || C_rec.amount||
128 ', C_rec.bank_account_amount=' || C_rec.bank_account_amount);
129 cep_standard.debug('C_rec.trx_date=' || C_rec.trx_date||
130 ', C_rec.gl_date=' || to_char(C_rec.gl_date,'YYYY/MM/DD')||
131 ', C_rec.cleared_date=' || C_rec.cleared_date);
132 cep_standard.debug('C_rec.trx_type=' || C_rec.trx_type ||
133 ', C_rec.clearing_trx_type=' || C_rec.clearing_trx_type||
134 ', C_rec.batch_id=' || C_rec.batch_id ||
135 ', C_rec.trx_id=' || C_rec.trx_id||
136 ', C_rec.cash_receipt_id=' || C_rec.cash_receipt_id);
137
138 -- BUG 4914608 SYSTEM PARAMETERS changes
139 P_BANK_ACCOUNT_ID := NVL(X_BANK_ACCOUNT_ID, C_rec.bank_account_id);
140
141 OPEN C_oifs(P_BANK_ACCOUNT_ID);
142 FETCH C_oifs INTO l_status;
143 CLOSE C_oifs;
144 cep_standard.debug('l_status='||l_status);
145
146 /* 2738067
147 Read Statement_complete_flag from ce_statement_headers since
148 the field in the view need not be populated always */
149 SELECT cesh.statement_complete_flag
150 INTO l_statement_complete_flag
151 FROM ce_statement_headers cesh
152 WHERE cesh.statement_header_id = C_rec.statement_header_id;
153
154 cep_standard.debug('l_statement_complete_flag='||l_statement_complete_flag);
155
156 IF nvl(l_statement_complete_flag, 'N') <> 'Y'
157 THEN
158 /* 13644150: added clearing_trx_type 'CASHFLOW' */
159 IF C_rec.clearing_trx_type in ('PAYMENT','CASH','MISC','CASHFLOW')
160 THEN -- bug 5999462
161 IF C_rec.clearing_trx_type = 'PAYMENT'
162 THEN
163 l_app_id := 200;
164 /* 10102287: check for recon_acctg_flag */
165 select RECON_ACCOUNTING_FLAG
166 into l_recon_accounting_flag
167 from ap_system_parameters_all asp
168 where asp.org_id = C_rec.org_id ;
169 ELSIF C_rec.clearing_trx_type = 'CASHFLOW' /* 13644150: Added */
170 THEN
171 l_app_id := 260;
172 ELSE
173 l_app_id := 222;
174 END IF;
175
176 cep_standard.debug('l_app_id='||l_app_id||
177 ', l_recon_accounting_flag='||l_recon_accounting_flag);
178
179 /* 13644150: For AP/AR transactions check to see if AP/AR accounting period is open */
180 cep_standard.debug('Checking if period is open or future...');
181 cep_standard.debug('C_rec.gl_date=' || to_char(C_rec.gl_date,'YYYY/MM/DD') );
182
183 /* 10102287: no need to check for open period if trx is payment and recon_acctg = 'N' */
184 IF (l_app_id = 200 AND l_recon_accounting_flag = 'N')
185 THEN
186 l_count := 1;
187 l_count_ap_ar := 1;
188 cep_standard.debug('RECON_ACCTG=N, period check not required');
189 ELSE
190 IF (l_app_id = 200 or l_app_id = 222)
191 THEN
192 -- Check to see if period is open or future
193 SELECT count(1)
194 INTO l_count_ap_ar /* 13644150: count for ap/ar period */
195 FROM gl_period_statuses glp,
196 ce_system_parameters sys,
197 ce_bank_accounts ba
198 WHERE glp.application_id = l_app_id
199 AND glp.set_of_books_id = sys.set_of_books_id
200 AND glp.adjustment_period_flag = 'N'
201 AND glp.closing_status in ('O','F')
202 AND to_char(C_rec.gl_date,'YYYY/MM/DD') between to_char(glp.start_date,'YYYY/MM/DD') and to_char(glp.end_date,'YYYY/MM/DD')
203 AND sys.legal_entity_id = ba.account_owner_org_id
204 AND ba.bank_account_id = C_rec.bank_account_id;
205
206 ELSE
207 cep_standard.debug('Skipping check for ap/ar period open');
208 l_count_ap_ar := 1;
209 END IF;
210
211 /* 13644150: Check for period open or future in GL */
212 SELECT count(1)
213 INTO l_count
214 FROM gl_period_statuses glp,
215 ce_system_parameters sys,
216 ce_bank_accounts ba
217 WHERE glp.application_id = 101
218 AND glp.set_of_books_id = sys.set_of_books_id
219 AND glp.adjustment_period_flag = 'N'
220 AND glp.closing_status in ('O','F')
221 AND to_char(C_rec.gl_date,'YYYY/MM/DD') between to_char(glp.start_date,'YYYY/MM/DD') and to_char(glp.end_date,'YYYY/MM/DD')
222 AND sys.legal_entity_id = ba.account_owner_org_id
223 AND ba.bank_account_id = C_rec.bank_account_id;
224 END IF;
225
226 cep_standard.debug('l_count='||l_count||
227 ', l_count_ap_ar='||l_count_ap_ar||
228 ', C_rec.status='||C_rec.status);
229
230 -- If period is closed don't proceed to lock and unclear
231 -- Bug 3427050 added the AND in the following IF
232 -- Bug 13644150: added l_count_ap_ar
233 IF (l_count = 0 OR l_count_ap_ar = 0)
234 AND C_rec.status NOT IN ('STOP INITIATED','VOIDED')
235 THEN
236 l_skip_lock_unclear := 'Y';
237 END IF;
238 END IF; -- end ('PAYMENT','CASH','MISC','CASHFLOW')
239
240 IF (C_rec.clearing_trx_type <> 'ROI_LINE') or (l_status is null) THEN
241 l_status := C_rec.status;
242 cep_standard.debug('l_status='||l_status);
243 END IF;
244
245 IF (C_rec.clearing_trx_type = 'STATEMENT') THEN
246 match_correction_type := 'REVERSAL';
247 -- bug 2993811 do not call lock_transaction and unclear_process for second statement line
248 -- bug 3208354 compare the statuses of the two statement lines to be UNRECONCILED for skipping
249 select statement_number,status
250 into l_stmt_stmt_num, l_status1
251 from ce_statement_headers hd, ce_statement_lines ln
252 where C_rec.statement_line_id = ln.statement_line_id
253 and ln.statement_header_id = hd.statement_header_id;
254
255 /* Bug 3847491. If the line being reconciled has been
256 reconcile against another statement line, then check that
257 the statement to which this statement line belongs is
258 not marked complete*/
259 l_next_statement_complete_flag := 'N';
260
261 select statement_number,status,hd.statement_complete_flag
262 into l_trx_stmt_num,l_status2, l_next_statement_complete_flag
263 from ce_statement_headers hd, ce_statement_lines ln
264 where C_rec.trx_id = ln.statement_line_id
265 and ln.statement_header_id = hd.statement_header_id;
266
267 cep_standard.debug('l_stmt_stmt_num='||l_stmt_stmt_num||
268 ', l_status1='||l_status1);
269 cep_standard.debug('l_trx_stmt_num='||l_trx_stmt_num||
270 ' ,l_status2='||l_status2||
271 ', l_next_statement_complete_flag='||l_next_statement_complete_flag);
272
273 -- stmt number is the same and it is the second stmt ln
274 -- bug 3208354 changed the following if condition
275 if (l_stmt_stmt_num = l_trx_stmt_num) and (l_status1 = l_status2)
276 and (l_status1 = 'UNRECONCILED')
277 then
278 l_skip_lock_unclear :='Y';
279 else
280 l_skip_lock_unclear :='N';
281 end if;
282 ELSE
283 match_correction_type := NULL;
284 END IF;
285
286 cep_standard.debug('match_correction_type='||match_correction_type);
287 cep_standard.debug('l_skip_lock_unclear='||l_skip_lock_unclear);
288
289 /* Bug 3847491 added the IF condition nelow */
290 IF(nvl(l_next_statement_complete_flag,'N') <> 'Y')
291 THEN
292 IF (l_skip_lock_unclear = 'N')
293 THEN
294 cep_standard.debug('call CE_AUTO_BANK_MATCH.lock_transaction');
295
296 CE_AUTO_BANK_MATCH.lock_transaction(
297 X_RECONCILE_FLAG => 'Y',
298 X_CALL_MODE => 'M',
299 X_TRX_TYPE => C_rec.trx_type,
300 X_CLEARING_TRX_TYPE => C_rec.clearing_trx_type,
301 X_TRX_ROWID => C_rec.row_id,
302 X_BATCH_BA_AMOUNT => C_rec.bank_account_amount,
303 X_MATCH_CORRECTION_TYPE => MATCH_CORRECTION_TYPE);
304
305
306 IF l_DEBUG in ('Y', 'C') THEN
307 cep_standard.debug('call CE_AUTO_BANK_CLEAR.unclear_process');
308 END IF;
309
310 CE_AUTO_BANK_CLEAR.unclear_process(
311 passin_mode => 'MANUAL',
312 X_header_or_line => 'HEADERS',
313 tx_type => C_rec.trx_type,
314 clearing_trx_type => C_rec.clearing_trx_type,
315 batch_id => C_rec.batch_id,
316 trx_id => C_rec.trx_id,
317 cash_receipt_id => C_rec.cash_receipt_id,
318 trx_date => C_rec.trx_date,
319 gl_date => C_rec.gl_date,
320 cash_receipt_history_id => cash_receipt_history_id,
321 stmt_line_id => C_rec.statement_line_id,
322 status => l_status,
323 cleared_date => C_rec.cleared_date,
324 transaction_amount => C_rec.amount,
325 error_amount => C_rec.bank_errors,
326 charge_amount => C_rec.bank_charges,
327 currency_code => C_rec.bank_currency_code,
328 xtype => C_rec.exchange_rate_type,
329 xdate => C_rec.exchange_rate_date,
330 xrate => C_rec.exchange_rate,
331 org_id => C_rec.org_id,
332 legal_entity_id => C_rec.legal_entity_id);
333
334 cep_standard.debug('Unreconciled statement line ID:'||to_char(C_rec.statement_line_id));
335 l_cnt := l_cnt + 1;
336 ELSE
337 cep_standard.debug('No change for statement line ID:'||to_char(C_rec.statement_line_id));
338 END IF; --(l_skip_lock_unclear = 'N')
339 END IF; -- (l_next_statement_complete_flag <> 'Y')
340
341 IF (l_cnt = CE_AUTO_BANK_REC.G_lines_per_commit)
342 THEN
343 COMMIT;
344 l_cnt := 0;
345 END IF;
346 END IF;
347 END LOOP;
348
349 cep_standard.debug('<<CE_AUTO_BANK_UNREC.unreconcile_all '||sysdate);
350 retcode := 0;
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_UNREC.unreconcile_all '||sysdate);
355 cep_standard.debug(sqlerrm);
356 RAISE;
357 END UNRECONCILE_ALL;
358
359 END CE_AUTO_BANK_UNREC;