[Home] [Help]
PACKAGE BODY: APPS.CE_AUTO_BANK_REC
Source
1 PACKAGE BODY CE_AUTO_BANK_REC AS
2 /* $Header: ceabrdrb.pls 120.18.12020000.2 2012/07/11 07:21:28 vnetan 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.18.12020000.2 $';
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 l_Report_Option VARCHAR2(2):='A'; --7720709 To exclude Reconcilation Errors in Bank Statemnet Import Execution Report
221
222 BEGIN
223 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
224 CEP_STANDARD.init_security;
225
226 G_ce_debug_flag := l_DEBUG; /* Bug 3364143 added this line */
227 IF l_DEBUG in ('Y', 'C') THEN
228 cep_standard.enable_debug(p_debug_path,
229 p_debug_file);
230
231 cep_standard.debug('>>CE_AUTO_BANK_REC.statement '||sysdate);
232 cep_standard.debug('>>CE_AUTO_BANK_REC.p_option : '|| p_option);
233 cep_standard.debug('>>CE_AUTO_BANK_REC.p_bank_branch_id : '|| p_bank_branch_id);
234 cep_standard.debug('>>CE_AUTO_BANK_REC.p_bank_account_id : '|| p_bank_account_id);
235 cep_standard.debug('>>CE_AUTO_BANK_REC.p_statement_number_from: '|| p_statement_number_from);
236 cep_standard.debug('>>CE_AUTO_BANK_REC.p_statement_number_to: '|| p_statement_number_to);
237 cep_standard.debug('>>CE_AUTO_BANK_REC.p_statement_date_from: '|| p_statement_date_from);
238 cep_standard.debug('>>CE_AUTO_BANK_REC.p_statement_date_to: '|| p_statement_date_to);
239 cep_standard.debug('>>CE_AUTO_BANK_REC.p_gl_date : '|| p_gl_date);
240 cep_standard.debug('>>CE_AUTO_BANK_REC.p_receivables_trx_id : '|| p_receivables_trx_id);
241 cep_standard.debug('>>CE_AUTO_BANK_REC.p_payment_method_id : '|| p_payment_method_id);
242 cep_standard.debug('>>CE_AUTO_BANK_REC.p_nsf_handling : '|| p_nsf_handling);
243 cep_standard.debug('>>CE_AUTO_BANK_REC.p_display_debug : '|| p_display_debug);
244 cep_standard.debug('>>CE_AUTO_BANK_REC.p_debug_path : '|| p_debug_path);
245 cep_standard.debug('>>CE_AUTO_BANK_REC.p_debug_file : '|| p_debug_file);
246 cep_standard.debug('>>CE_AUTO_BANK_REC.p_org_id : '|| p_org_id);
247 cep_standard.debug('>>CE_AUTO_BANK_REC.p_legal_entity_id : '|| p_legal_entity_id);
248 END IF;
249
250 -- cannot pass both org_id and legal_entity_id from 1 value set/parameter
251 -- p_org_id value are concatenated with both org_id and legal_entity_id
252 -- org_id starts with 'O' and legal_entity_id start with 'L'
253 IF (substr(p_org_id,1,1) = 'O') THEN
254 l_org_id := substr(p_org_id,2);
255 l_legal_entity_id := null;
256 ELSIF (substr(p_org_id,1,1) = 'L') THEN
257 l_org_id := null;
258 l_legal_entity_id := substr(p_org_id,2);
259 ELSE
260 l_org_id := p_org_id;
261 l_legal_entity_id := p_legal_entity_id;
262 END IF;
263
264 IF (p_org_id is null and p_legal_entity_id is null) THEN
265 l_org_id := null;
266 l_legal_entity_id := null;
267 END IF;
268
269
270 --mo_global.init('CE');
271
272 IF l_DEBUG in ('Y', 'C') THEN
273 cep_standard.debug('>>CE_AUTO_BANK_REC.l_org_id : '|| l_org_id);
274 cep_standard.debug('>>CE_AUTO_BANK_REC.l_legal_entity_id : '|| l_legal_entity_id);
275 END IF;
276
277 set_parameters(p_option,
278 p_bank_branch_id,
279 p_bank_account_id,
280 p_statement_number_from,
281 p_statement_number_to,
282 p_statement_date_from,
283 p_statement_date_to,
284 p_gl_date,
285 p_receivables_trx_id,
286 p_payment_method_id,
287 p_nsf_handling,
288 p_display_debug,
289 p_debug_path,
290 p_debug_file,
291 p_intra_day_flag,
292 l_org_id,
293 l_legal_entity_id);
294
295 IF l_DEBUG in ('Y', 'C') THEN
296 cep_standard.debug('call CE_SYSTEM_PARAMETERS1_PKG.select_columns');
297 END IF;
298
299 --bug 4914608
300 --IF (l_org_id is not null or l_legal_entity_id is not null) THEN
301 IF (l_legal_entity_id is not null) THEN
302
303 CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
304 CE_AUTO_BANK_REC.G_set_of_books_id,
305 CE_AUTO_BANK_REC.G_cashbook_begin_date,
306 CE_AUTO_BANK_REC.G_show_cleared_flag,
307 CE_AUTO_BANK_REC.G_show_void_payment_flag,
308 CE_AUTO_BANK_REC.G_line_autocreation_flag,
309 CE_AUTO_BANK_REC.G_interface_purge_flag,
310 CE_AUTO_BANK_REC.G_interface_archive_flag,
311 CE_AUTO_BANK_REC.G_lines_per_commit,
312 CE_AUTO_BANK_REC.G_functional_currency,
313 CE_AUTO_BANK_REC.G_sob_short_name,
314 CE_AUTO_BANK_REC.G_account_period_type,
315 CE_AUTO_BANK_REC.G_user_exchange_rate_type,
316 CE_AUTO_BANK_REC.G_chart_of_accounts_id,
317 CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
318 CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
319 CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
320 CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
321 CE_AUTO_BANK_REC.G_legal_entity_id
322 );
323
324 END IF;
325 IF l_DEBUG in ('Y', 'C') THEN
326 cep_standard.debug('end call CE_SYSTEM_PARAMETERS1_PKG.select_columns');
327 cep_standard.debug('CE_AUTO_BANK_REC.G_org_id '|| CE_AUTO_BANK_REC.G_org_id);
328 cep_standard.debug('CE_AUTO_BANK_REC.G_legal_entity_id '|| CE_AUTO_BANK_REC.G_legal_entity_id);
329 END IF;
330
331 select mo_global.GET_CURRENT_ORG_ID
332 into current_org_id
333 from dual;
334
335 -- bug 3782741 set single org, since AR will not allow org_id to be passed
336 IF (CE_AUTO_BANK_REC.G_org_id is not null) THEN
337 IF ((current_org_id is null) or (CE_AUTO_BANK_REC.G_org_id <> current_org_id )) THEN
338 cep_standard.debug('set policy_context '||CE_AUTO_BANK_REC.G_org_id);
339 mo_global.set_policy_context('S',CE_AUTO_BANK_REC.G_org_id);
340
341 END IF;
342 END IF;
343
344 /*
345 IF (CE_AUTO_BANK_REC.G_org_id is not null) THEN
346 IF l_DEBUG in ('Y', 'C') THEN
347 cep_standard.debug('set policy_context '||CE_AUTO_BANK_REC.G_org_id);
348 END IF;
349 mo_global.set_policy_context('S',CE_AUTO_BANK_REC.G_org_id);
350 END IF;
351 */
352 select mo_global.GET_CURRENT_ORG_ID
353 into current_org_id
354 from dual;
355
356 cep_standard.debug('current_org_id =' ||current_org_id );
357
358
359 IF (CE_AUTO_BANK_REC.G_receivables_trx_id IS NOT NULL) THEN
360 select liability_tax_code, asset_tax_code
361 into CE_AUTO_BANK_REC.G_dr_vat_tax_code,
362 CE_AUTO_BANK_REC.G_cr_vat_tax_code
363 from ar_receivables_trx
364 where receivables_trx_id = CE_AUTO_BANK_REC.G_receivables_trx_id;
365 END IF;
366
367 --
368 -- Get the profile values
369 --
370 FND_PROFILE.get('UNIQUE:SEQ_NUMBERS',CE_AUTO_BANK_REC.G_sequence_numbering);
371 FND_PROFILE.get('DISPLAY_INVERSE_RATE',CE_AUTO_BANK_REC.G_inverse_rate);
372
373 IF (p_option IN ('IMPORT', 'ZALL')) THEN
374 CE_AUTO_BANK_IMPORT.import_process;
375 COMMIT;
376 END IF;
377 IF l_DEBUG in ('Y', 'C') THEN
378 cep_standard.debug('<<CE_AUTO_BANK_REC.statement - Return from Import');
379 END IF;
380 IF (p_option IN ('RECONCILE', 'ZALL')) THEN
381 CE_AUTO_BANK_MATCH.match_process;
382 COMMIT;
383 END IF;
384 IF l_DEBUG in ('Y', 'C') THEN
385 cep_standard.debug('<<CE_AUTO_BANK_REC.statement - Return from Reconcile');
386 END IF;
387 --
388 -- Get original request id
389 --
390 fnd_profile.get('CONC_REQUEST_ID', reqid);
391 request_id := to_number(reqid);
392 --
393 -- Get print options
394 --
395 /* Bug 3479531 removed the NOT from the following condition */
396 IF( FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
397 number_of_copies,
398 print_style,
399 printer,
400 save_output_flag))THEN
401 IF l_DEBUG in ('Y', 'C') THEN
402 cep_standard.debug('statement: ' || 'Message: get print options success');
403 END IF;
404 ELSE
405 IF (save_output_flag = 'Y') THEN
406 save_output_bool := TRUE;
407 ELSE
408 save_output_bool := FALSE;
409 END IF;
410
411 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES ('CE',
412 'CEXINERR',
413 printer,
414 print_style,
415 save_output_flag)) THEN
416 IF l_DEBUG in ('Y', 'C') THEN
417 cep_standard.debug('statement: ' || 'Message: get print options failed');
418 END IF;
419 END IF;
420
421 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES ('CE',
422 'CEIMPERR',
423 printer,
424 print_style,
425 save_output_flag)) THEN
426 IF l_DEBUG in ('Y', 'C') THEN
427 cep_standard.debug('statement: ' || 'Message: get print options failed');
428 END IF;
429 END IF;
430 END IF; /* Bug 3479531 placed the END IF here */
431 --
432 -- Set print options
433 --
434 IF (NOT FND_REQUEST.set_print_options(printer,
435 print_style,
436 number_of_copies,
437 save_output_bool)) THEN
438 IF l_DEBUG in ('Y', 'C') THEN
439 cep_standard.debug('statement: ' || 'Set print options failed');
440 END IF;
441 END IF;
442 IF (p_option in ('IMPORT')) THEN
443 l_Report_Option := 'I'; --7720709 To exclude Reconcilation Errors in Bank Statemnet Import Execution Report
444 req_id := FND_REQUEST.SUBMIT_REQUEST('CE',
445 'CEIMPERR',
446 NULL,
447 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'),
448 /* to_date(sysdate),*/
449 FALSE,
450 p_bank_branch_id,
451 p_bank_account_id,
452 p_statement_number_from,
453 p_statement_number_to,
454 p_statement_date_from,
455 p_statement_date_to,
456 p_display_debug,
457 p_display_debug,
458 l_Report_Option -- 7720709 To exclude Reconcilation Errors in Bank Statemnet Import Execution Report
459 );
460 COMMIT;
461 END IF;
462 IF (p_option IN ('RECONCILE', 'ZALL')) THEN
463 -- 13591510: Added case to set l_report_option
464 case p_option
465 when 'RECONCILE' then l_report_option := 'R';
466 when 'ZALL' then l_report_option := 'A';
467 end case;
468
469 req_id := FND_REQUEST.SUBMIT_REQUEST(
470 'CE',
471 'CEXINERR',
472 NULL,
473 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'), /* Bug 4117560 replaced to_date(sysdate) */
474 FALSE,
475 p_bank_branch_id,
476 p_bank_account_id,
477 p_statement_number_from,
478 p_statement_number_to,
479 p_statement_date_from,
480 p_statement_date_to,
481 p_display_debug,
482 p_display_debug,
483 -- 13591510: Passing report option to Report to disable Interface
484 -- errors on Reconciliation Execution report
485 l_report_option );
486
487 COMMIT;
488 END IF;
489 IF (req_id = 0) THEN
490 IF l_DEBUG in ('Y', 'C') THEN
491 cep_standard.debug('statement: ' || 'ERROR submitting concurrent request');
492 END IF;
493 ELSE
494 IF l_DEBUG in ('Y', 'C') THEN
495 cep_standard.debug('statement: ' || 'EXECUTION REPORT SUBMITTED');
496 END IF;
497 END IF;
498 IF l_DEBUG in ('Y', 'C') THEN
499 cep_standard.debug('<<CE_AUTO_BANK_REC.statement '||sysdate);
500 cep_standard.disable_debug(p_display_debug);
501 END IF;
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 IF l_DEBUG in ('Y', 'C') THEN
506 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_REC.statement');
507 END IF;
508 RAISE;
509 END statement;
510
511 END CE_AUTO_BANK_REC;