[Home] [Help]
PACKAGE BODY: APPS.CE_AUTO_BANK_REC
Source
1 PACKAGE BODY CE_AUTO_BANK_REC AS
2 /* $Header: ceabrdrb.pls 120.17 2006/01/18 22:02:25 lkwan 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.17 $';
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 | PRIVATE PROCEDURE |
23 | find_gl_period |
24 | |
25 | DESCRIPTION |
26 | Procedure to find valid GL period |
27 --------------------------------------------------------------------- */
28
29 FUNCTION find_gl_period(p_date DATE,
30 p_app_id NUMBER) RETURN BOOLEAN IS
31 dummy NUMBER;
32
33 BEGIN
34
35 IF l_DEBUG in ('Y', 'C') THEN
36 cep_standard.debug('>>CE_AUTO_BANK_REC.find_gl_period');
37 END IF;
38
39 SELECT 1
40 INTO dummy
41 FROM gl_period_statuses
42 WHERE application_id = p_app_id
43 AND set_of_books_id = CE_AUTO_BANK_REC.G_set_of_books_id
44 AND adjustment_period_flag = 'N'
45 AND closing_status in ('O','F')
46 AND p_date between start_date and end_date;
47 /* AND to_date(p_date) between start_date and end_date;*/
48
49 IF l_DEBUG in ('Y', 'C') THEN
50 cep_standard.debug('<<CE_AUTO_BANK_REC.find_gl_period');
51 END IF;
52 return(TRUE);
53
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 IF l_DEBUG in ('Y', 'C') THEN
57 cep_standard.debug('find_gl_period() has no data found.');
58 END IF;
59 RETURN FALSE;
60 WHEN OTHERS THEN
61 IF l_DEBUG in ('Y', 'C') THEN
62 cep_standard.debug('EXCEPTION:CE_AUTO_BANK_REC.find_gl_period:OTHERS');
63 END IF;
64 RAISE;
65 END find_gl_period;
66
67 /* ---------------------------------------------------------------------
68 | PRIVATE PROCEDURE |
69 | set_parameters |
70 | |
71 | DESCRIPTION |
72 | Procedure to set parameter values into globals |
73 | CALLED BY |
74 | statement |
75 | REQUIRES |
76 | all parameters of CE_AUTO_BANK_REC.statement |
77 --------------------------------------------------------------------- */
78 PROCEDURE set_parameters(p_option VARCHAR2,
79 p_bank_branch_id NUMBER,
80 p_bank_account_id NUMBER,
81 p_statement_number_from VARCHAR2,
82 p_statement_number_to VARCHAR2,
83 p_statement_date_from VARCHAR2,
84 p_statement_date_to VARCHAR2,
85 p_gl_date VARCHAR2,
86 p_receivables_trx_id NUMBER,
87 p_payment_method_id NUMBER,
88 p_nsf_handling VARCHAR2,
89 p_display_debug VARCHAR2,
90 p_debug_path VARCHAR2,
91 p_debug_file VARCHAR2,
92 p_intra_day_flag VARCHAR2,
93 p_org_id NUMBER,
94 p_legal_entity_id NUMBER) IS
95 BEGIN
96 IF l_DEBUG in ('Y', 'C') THEN
97 cep_standard.debug('>>CE_AUTO_BANK_REC.set_parameters');
98 END IF;
99 CE_AUTO_BANK_REC.G_option := p_option;
100 CE_AUTO_BANK_REC.G_bank_branch_id := p_bank_branch_id;
101 CE_AUTO_BANK_REC.G_bank_account_id := p_bank_account_id;
102 CE_AUTO_BANK_REC.G_statement_number_from := p_statement_number_from;
103 CE_AUTO_BANK_REC.G_statement_number_to := p_statement_number_to;
104
105 /* bug 1619492
106 loader and autoreconciliation should not store the timestamp of a date */
107 CE_AUTO_BANK_REC.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
108 CE_AUTO_BANK_REC.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
109 CE_AUTO_BANK_REC.G_gl_date := to_date(p_gl_date,'YYYY/MM/DD HH24:MI:SS');
110 CE_AUTO_BANK_REC.G_gl_date_original := to_date(p_gl_date,'YYYY/MM/DD HH24:MI:SS');
111 CE_AUTO_BANK_REC.G_receivables_trx_id := p_receivables_trx_id;
112 CE_AUTO_BANK_REC.G_payment_method_id := p_payment_method_id;
113 CE_AUTO_BANK_REC.G_nsf_handling := p_nsf_handling;
114 CE_AUTO_BANK_REC.G_display_debug := p_display_debug;
115 CE_AUTO_BANK_REC.G_debug_path := p_debug_path;
116 CE_AUTO_BANK_REC.G_debug_file := p_debug_file;
117
118 CE_AUTO_BANK_REC.G_intra_day_flag := p_intra_day_flag;
119 CE_AUTO_BANK_REC.G_org_id := p_org_id;
120 CE_AUTO_BANK_REC.G_legal_entity_id := p_legal_entity_id;
121
122 IF l_DEBUG in ('Y', 'C') THEN
123 show_parameters;
124 cep_standard.debug('<<CE_AUTO_BANK_REC.set_parameters');
125 END IF;
126 END set_parameters;
127
128 PROCEDURE show_parameters IS
129 BEGIN
130 cep_standard.debug('G_option = '|| CE_AUTO_BANK_REC.G_option);
131 cep_standard.debug('G_statement_number_from = ' || CE_AUTO_BANK_REC.G_statement_number_from);
132 cep_standard.debug('G_statement_number_to = ' || CE_AUTO_BANK_REC.G_statement_number_to);
133 cep_standard.debug('G_statement_date_from = ' || CE_AUTO_BANK_REC.G_statement_date_from);
134 cep_standard.debug('G_statement_date_to = ' || CE_AUTO_BANK_REC.G_statement_date_to);
135 cep_standard.debug('G_bank_branch_id = ' || CE_AUTO_BANK_REC.G_bank_branch_id);
136 cep_standard.debug('G_bank_account_id = ' || CE_AUTO_BANK_REC.G_bank_account_id);
137 cep_standard.debug('G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
138 cep_standard.debug('G_gl_date_original = ' || CE_AUTO_BANK_REC.G_gl_date_original);
139 cep_standard.debug('G_receivables_trx_id = ' || CE_AUTO_BANK_REC.G_receivables_trx_id);
140 cep_standard.debug('G_payment_method_id = ' || CE_AUTO_BANK_REC.G_payment_method_id);
141 cep_standard.debug('G_nsf_handling = ' || CE_AUTO_BANK_REC.G_nsf_handling);
142 cep_standard.debug('G_display_debug = ' || CE_AUTO_BANK_REC.G_display_debug);
143 cep_standard.debug('G_debug_path = ' || CE_AUTO_BANK_REC.G_debug_path);
144 cep_standard.debug('G_debug_file = ' || CE_AUTO_BANK_REC.G_debug_file);
145 cep_standard.debug('G_org_id = ' || CE_AUTO_BANK_REC.G_org_id);
146 cep_standard.debug('G_legal_entity_id = ' || CE_AUTO_BANK_REC.G_legal_entity_id);
147 END show_parameters;
148
149 /* ---------------------------------------------------------------------
150 | PRIVATE PROCEDURE |
151 | statement |
152 | |
153 | DESCRIPTION |
154 | Auto Bank Rec has two main functions, import a statement from |
155 | the interface tables or reconcile a previously imported state- |
156 | ment. |
157 | |
158 | p_option may take the following values |
159 | IMPORT Validate and if no errors exist, transfer the |
160 | statement held in the interface tables. |
161 | RECONCILE Match, receconcile and clear statement lines |
162 | held within the statement tables. The statement|
163 | being reconciled must have previously been |
164 | imported. |
165 | ZALL Import statement and reconcile the lines. |
166 | |
167 | CALLS |
168 | import_process |
169 | match_process |
170 | |
171 | REQUIRES |
172 | p_option |
173 | p_bank_branch_id |
174 | p_bank_account_id |
175 | p_statement_number_from |
176 | p_statement_number_to |
177 | p_statement_date_from |
178 | p_statement_date_to |
179 | p_gl_date |
180 | p_nsf_handling |
181 | p_debug_mode |
182 | |
183 | RETURNS |
184 | errbuf |
185 | retcode |
186 | |
187 | HISTORY |
188 --------------------------------------------------------------------- */
189 PROCEDURE statement ( errbuf OUT NOCOPY VARCHAR2,
190 retcode OUT NOCOPY NUMBER,
191 p_option VARCHAR2,
192 p_bank_branch_id NUMBER,
193 p_bank_account_id NUMBER,
194 p_statement_number_from VARCHAR2,
195 p_statement_number_to VARCHAR2,
196 p_statement_date_from VARCHAR2,
197 p_statement_date_to VARCHAR2,
198 p_gl_date VARCHAR2,
199 p_org_id VARCHAR2,
200 p_legal_entity_id VARCHAR2,
201 p_receivables_trx_id NUMBER,
202 p_payment_method_id NUMBER,
203 p_nsf_handling VARCHAR2,
204 p_display_debug VARCHAR2,
205 p_debug_path VARCHAR2,
206 p_debug_file VARCHAR2,
207 p_intra_day_flag VARCHAR2) IS
208 req_id NUMBER;
209 request_id NUMBER;
210 reqid VARCHAR2(30);
211 number_of_copies number;
212 printer VARCHAR2(30);
213 print_style VARCHAR2(30);
214 save_output_flag VARCHAR2(30);
215 save_output_bool BOOLEAN;
216 ignore_trx_id NUMBER;
217 l_org_id NUMBER;
218 l_legal_entity_id NUMBER;
219 current_org_id NUMBER;
220
221 BEGIN
222 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
223 CEP_STANDARD.init_security;
224
225 G_ce_debug_flag := l_DEBUG; /* Bug 3364143 added this line */
226 IF l_DEBUG in ('Y', 'C') THEN
227 cep_standard.enable_debug(p_debug_path,
228 p_debug_file);
229
230 cep_standard.debug('>>CE_AUTO_BANK_REC.statement '||sysdate);
231 cep_standard.debug('>>CE_AUTO_BANK_REC.p_option : '|| p_option);
232 cep_standard.debug('>>CE_AUTO_BANK_REC.p_bank_branch_id : '|| p_bank_branch_id);
233 cep_standard.debug('>>CE_AUTO_BANK_REC.p_bank_account_id : '|| p_bank_account_id);
234 cep_standard.debug('>>CE_AUTO_BANK_REC.p_statement_number_from: '|| p_statement_number_from);
235 cep_standard.debug('>>CE_AUTO_BANK_REC.p_statement_number_to: '|| p_statement_number_to);
236 cep_standard.debug('>>CE_AUTO_BANK_REC.p_statement_date_from: '|| p_statement_date_from);
237 cep_standard.debug('>>CE_AUTO_BANK_REC.p_statement_date_to: '|| p_statement_date_to);
238 cep_standard.debug('>>CE_AUTO_BANK_REC.p_gl_date : '|| p_gl_date);
239 cep_standard.debug('>>CE_AUTO_BANK_REC.p_receivables_trx_id : '|| p_receivables_trx_id);
240 cep_standard.debug('>>CE_AUTO_BANK_REC.p_payment_method_id : '|| p_payment_method_id);
241 cep_standard.debug('>>CE_AUTO_BANK_REC.p_nsf_handling : '|| p_nsf_handling);
242 cep_standard.debug('>>CE_AUTO_BANK_REC.p_display_debug : '|| p_display_debug);
243 cep_standard.debug('>>CE_AUTO_BANK_REC.p_debug_path : '|| p_debug_path);
244 cep_standard.debug('>>CE_AUTO_BANK_REC.p_debug_file : '|| p_debug_file);
245 cep_standard.debug('>>CE_AUTO_BANK_REC.p_org_id : '|| p_org_id);
246 cep_standard.debug('>>CE_AUTO_BANK_REC.p_legal_entity_id : '|| p_legal_entity_id);
247 END IF;
248
249 -- cannot pass both org_id and legal_entity_id from 1 value set/parameter
250 -- p_org_id value are concatenated with both org_id and legal_entity_id
251 -- org_id starts with 'O' and legal_entity_id start with 'L'
252 IF (substr(p_org_id,1,1) = 'O') THEN
253 l_org_id := substr(p_org_id,2);
254 l_legal_entity_id := null;
255 ELSIF (substr(p_org_id,1,1) = 'L') THEN
256 l_org_id := null;
257 l_legal_entity_id := substr(p_org_id,2);
258 ELSE
259 l_org_id := p_org_id;
260 l_legal_entity_id := p_legal_entity_id;
261 END IF;
262
263 IF (p_org_id is null and p_legal_entity_id is null) THEN
264 l_org_id := null;
265 l_legal_entity_id := null;
266 END IF;
267
268
269 --mo_global.init('CE');
270
271 IF l_DEBUG in ('Y', 'C') THEN
272 cep_standard.debug('>>CE_AUTO_BANK_REC.l_org_id : '|| l_org_id);
273 cep_standard.debug('>>CE_AUTO_BANK_REC.l_legal_entity_id : '|| l_legal_entity_id);
274 END IF;
275
276 set_parameters(p_option,
277 p_bank_branch_id,
278 p_bank_account_id,
279 p_statement_number_from,
280 p_statement_number_to,
281 p_statement_date_from,
282 p_statement_date_to,
283 p_gl_date,
284 p_receivables_trx_id,
285 p_payment_method_id,
286 p_nsf_handling,
287 p_display_debug,
288 p_debug_path,
289 p_debug_file,
290 p_intra_day_flag,
291 l_org_id,
292 l_legal_entity_id);
293
294 IF l_DEBUG in ('Y', 'C') THEN
295 cep_standard.debug('call CE_SYSTEM_PARAMETERS1_PKG.select_columns');
296 END IF;
297
298 --bug 4914608
299 --IF (l_org_id is not null or l_legal_entity_id is not null) THEN
300 IF (l_legal_entity_id is not null) THEN
301
302 CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
303 CE_AUTO_BANK_REC.G_set_of_books_id,
304 CE_AUTO_BANK_REC.G_cashbook_begin_date,
305 CE_AUTO_BANK_REC.G_show_cleared_flag,
306 CE_AUTO_BANK_REC.G_show_void_payment_flag,
307 CE_AUTO_BANK_REC.G_line_autocreation_flag,
308 CE_AUTO_BANK_REC.G_interface_purge_flag,
309 CE_AUTO_BANK_REC.G_interface_archive_flag,
310 CE_AUTO_BANK_REC.G_lines_per_commit,
311 CE_AUTO_BANK_REC.G_functional_currency,
312 CE_AUTO_BANK_REC.G_sob_short_name,
313 CE_AUTO_BANK_REC.G_account_period_type,
314 CE_AUTO_BANK_REC.G_user_exchange_rate_type,
315 CE_AUTO_BANK_REC.G_chart_of_accounts_id,
316 CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
317 CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
318 CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
319 CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
320 CE_AUTO_BANK_REC.G_legal_entity_id
321 );
322
323 END IF;
324 IF l_DEBUG in ('Y', 'C') THEN
325 cep_standard.debug('end call CE_SYSTEM_PARAMETERS1_PKG.select_columns');
326 cep_standard.debug('CE_AUTO_BANK_REC.G_org_id '|| CE_AUTO_BANK_REC.G_org_id);
327 cep_standard.debug('CE_AUTO_BANK_REC.G_legal_entity_id '|| CE_AUTO_BANK_REC.G_legal_entity_id);
328 END IF;
329
330 select mo_global.GET_CURRENT_ORG_ID
331 into current_org_id
332 from dual;
333
334 -- bug 3782741 set single org, since AR will not allow org_id to be passed
335 IF (CE_AUTO_BANK_REC.G_org_id is not null) THEN
336 IF ((current_org_id is null) or (CE_AUTO_BANK_REC.G_org_id <> current_org_id )) THEN
337 cep_standard.debug('set policy_context '||CE_AUTO_BANK_REC.G_org_id);
338 mo_global.set_policy_context('S',CE_AUTO_BANK_REC.G_org_id);
339
340 END IF;
341 END IF;
342
343 /*
344 IF (CE_AUTO_BANK_REC.G_org_id is not null) THEN
345 IF l_DEBUG in ('Y', 'C') THEN
346 cep_standard.debug('set policy_context '||CE_AUTO_BANK_REC.G_org_id);
347 END IF;
348 mo_global.set_policy_context('S',CE_AUTO_BANK_REC.G_org_id);
349 END IF;
350 */
351 select mo_global.GET_CURRENT_ORG_ID
352 into current_org_id
353 from dual;
354
355 cep_standard.debug('current_org_id =' ||current_org_id );
356
357
358 IF (CE_AUTO_BANK_REC.G_receivables_trx_id IS NOT NULL) THEN
359 select liability_tax_code, asset_tax_code
360 into CE_AUTO_BANK_REC.G_dr_vat_tax_code,
361 CE_AUTO_BANK_REC.G_cr_vat_tax_code
362 from ar_receivables_trx
363 where receivables_trx_id = CE_AUTO_BANK_REC.G_receivables_trx_id;
364 END IF;
365
366 --
367 -- Get the profile values
368 --
369 FND_PROFILE.get('UNIQUE:SEQ_NUMBERS',CE_AUTO_BANK_REC.G_sequence_numbering);
370 FND_PROFILE.get('DISPLAY_INVERSE_RATE',CE_AUTO_BANK_REC.G_inverse_rate);
371
372 IF (p_option IN ('IMPORT', 'ZALL')) THEN
373 CE_AUTO_BANK_IMPORT.import_process;
374 COMMIT;
375 END IF;
376 IF l_DEBUG in ('Y', 'C') THEN
377 cep_standard.debug('<<CE_AUTO_BANK_REC.statement - Return from Import');
378 END IF;
379 IF (p_option IN ('RECONCILE', 'ZALL')) THEN
380 CE_AUTO_BANK_MATCH.match_process;
381 COMMIT;
382 END IF;
383 IF l_DEBUG in ('Y', 'C') THEN
384 cep_standard.debug('<<CE_AUTO_BANK_REC.statement - Return from Reconcile');
385 END IF;
386 --
387 -- Get original request id
388 --
389 fnd_profile.get('CONC_REQUEST_ID', reqid);
390 request_id := to_number(reqid);
391 --
392 -- Get print options
393 --
394 /* Bug 3479531 removed the NOT from the following condition */
395 IF( FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
396 number_of_copies,
397 print_style,
398 printer,
399 save_output_flag))THEN
400 IF l_DEBUG in ('Y', 'C') THEN
401 cep_standard.debug('statement: ' || 'Message: get print options success');
402 END IF;
403 ELSE
404 IF (save_output_flag = 'Y') THEN
405 save_output_bool := TRUE;
406 ELSE
407 save_output_bool := FALSE;
408 END IF;
409
410 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES ('CE',
411 'CEXINERR',
412 printer,
413 print_style,
414 save_output_flag)) THEN
415 IF l_DEBUG in ('Y', 'C') THEN
416 cep_standard.debug('statement: ' || 'Message: get print options failed');
417 END IF;
418 END IF;
419
420 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES ('CE',
421 'CEIMPERR',
422 printer,
423 print_style,
424 save_output_flag)) THEN
425 IF l_DEBUG in ('Y', 'C') THEN
426 cep_standard.debug('statement: ' || 'Message: get print options failed');
427 END IF;
428 END IF;
429 END IF; /* Bug 3479531 placed the END IF here */
430 --
431 -- Set print options
432 --
433 IF (NOT FND_REQUEST.set_print_options(printer,
434 print_style,
435 number_of_copies,
436 save_output_bool)) THEN
437 IF l_DEBUG in ('Y', 'C') THEN
438 cep_standard.debug('statement: ' || 'Set print options failed');
439 END IF;
440 END IF;
441 IF (p_option in ('IMPORT')) THEN
442 req_id := FND_REQUEST.SUBMIT_REQUEST('CE',
443 'CEIMPERR',
444 NULL,
445 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'),
446 /* to_date(sysdate),*/
447 FALSE,
448 p_bank_branch_id,
449 p_bank_account_id,
450 p_statement_number_from,
451 p_statement_number_to,
452 p_statement_date_from,
453 p_statement_date_to,
454 p_display_debug,
455 p_display_debug);
456 COMMIT;
457 END IF;
458 IF (p_option IN ('RECONCILE', 'ZALL')) THEN
459 req_id := FND_REQUEST.SUBMIT_REQUEST('CE',
460 'CEXINERR',
461 NULL,
462 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'), /* Bug 4117560 replaced to_date(sysdate) */
463 FALSE,
464 p_bank_branch_id,
465 p_bank_account_id,
466 p_statement_number_from,
467 p_statement_number_to,
468 p_statement_date_from,
469 p_statement_date_to,
470 --l_org_id,
471 --l_legal_entity_id,
472 p_display_debug,
473 p_display_debug);
474 COMMIT;
475 END IF;
476 IF (req_id = 0) THEN
477 IF l_DEBUG in ('Y', 'C') THEN
478 cep_standard.debug('statement: ' || 'ERROR submitting concurrent request');
479 END IF;
480 ELSE
481 IF l_DEBUG in ('Y', 'C') THEN
482 cep_standard.debug('statement: ' || 'EXECUTION REPORT SUBMITTED');
483 END IF;
484 END IF;
485 IF l_DEBUG in ('Y', 'C') THEN
486 cep_standard.debug('<<CE_AUTO_BANK_REC.statement '||sysdate);
487 cep_standard.disable_debug(p_display_debug);
488 END IF;
489
490 EXCEPTION
491 WHEN OTHERS THEN
492 IF l_DEBUG in ('Y', 'C') THEN
493 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_REC.statement');
494 END IF;
495 RAISE;
496 END statement;
497
498 END CE_AUTO_BANK_REC;