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