[Home] [Help]
PACKAGE BODY: APPS.CE_ZBA_DEAL_GENERATION
Source
1 PACKAGE BODY CE_ZBA_DEAL_GENERATION AS
2 /* $Header: cezdgenb.pls 120.16.12020000.4 2012/08/28 12:37:13 vnetan ship $ */
3 --
4 -- MAIN CURSORS
5 --
6 CURSOR r_branch_cursor(
7 p_bank_branch_id NUMBER,
8 p_bank_account_id NUMBER
9 ) IS
10 SELECT cba.bank_account_id
11 FROM ce_bank_accounts cba
12 WHERE cba.bank_branch_id = p_bank_branch_id
13 AND cba.bank_account_id = NVL(p_bank_account_id, cba.bank_account_id)
14 ORDER BY cba.bank_account_name;
15
16
17 CURSOR r_bank_cursor(
18 p_statement_number_from VARCHAR2,
19 p_statement_number_to VARCHAR2,
20 p_statement_date_from DATE,
21 p_statement_date_to DATE,
22 p_bank_account_id NUMBER
23 ) IS
24 SELECT csh.statement_header_id,
25 csh.statement_number,
26 csh.statement_date,
27 csh.check_digits,
28 csh.gl_date,
29 cba.currency_code,
30 cba.multi_currency_allowed_flag,
31 cba.check_digits,
32 csh.rowid,
33 NVL(csh.statement_complete_flag,'N'),
34 csh.org_id
35 FROM ce_bank_accounts cba,
36 ce_statement_headers csh
37 WHERE cba.bank_account_id = NVL(p_bank_account_id,cba.bank_account_id)
38 AND cba.bank_account_id = csh.bank_account_id
39 AND csh.statement_number BETWEEN
40 NVL(p_statement_number_from,csh.statement_number)
41 AND NVL(p_statement_number_to,csh.statement_number)
42 AND csh.statement_date BETWEEN -- Bug 10102381 : Added
43 NVL(p_statement_date_from,csh.statement_date) -- Bug 11686554 Removed to_date
44 AND NVL(p_statement_date_to,csh.statement_date) -- Bug 11686554 Removed to_date
45 AND NVL(csh.statement_complete_flag,'N') = 'N';
46
47 CURSOR line_cursor(csh_statement_header_id NUMBER) IS
48 SELECT sl.rowid,
49 sl.statement_line_id,
50 cd.receivables_trx_id,
51 cd.receipt_method_id,
52 cd.create_misc_trx_flag,
53 cd.matching_against,
54 cd.correction_method,
55 rm.name,
56 sl.exchange_rate_type,
57 sl.exchange_rate_date,
58 sl.exchange_rate,
59 sl.currency_code,
60 sl.trx_type,
61 decode(cd.PAYROLL_PAYMENT_FORMAT_ID,
62 null, NVL(cd.reconcile_flag,'X'),
63 decode(cd.reconcile_flag, 'PAY', 'PAY_EFT',
64 NVL(cd.reconcile_flag,'X'))),
65 'NONE',
66 NULL,
67 NULL,
68 sl.original_amount,
69 ppt.payment_type_name
70 FROM pay_payment_types ppt,
71 ar_receipt_methods rm,
72 ce_transaction_codes cd,
73 ce_statement_lines sl
74 WHERE rm.receipt_method_id(+) = cd.receipt_method_id
75 AND cd.transaction_code_id(+) = sl.trx_code_id
76 AND cd.payroll_payment_format_id = ppt.payment_type_id (+)
77 AND csh_statement_date
78 between nvl(cd.start_date, csh_statement_date)
79 and nvl(cd.end_date, csh_statement_date)
80 AND sl.status = 'UNRECONCILED'
81 AND sl.statement_header_id = csh_statement_header_id
82 AND sl.trx_type IN ('SWEEP_IN', 'SWEEP_OUT')
83 ORDER BY
84 DECODE(sl.trx_type, 'NSF', 5, 'REJECTED', 5,
85 decode(nvl(cd.matching_against,'MISC'), 'MISC', 3, 'MS', 2, 1)),
86 DECODE(nvl(cd.matching_against,'MISC'), 'MISC', 0,
87 to_char(sl.trx_date, 'J')) DESC;
88
89 FUNCTION body_revision RETURN VARCHAR2 IS
90 BEGIN
91 RETURN '$Revision: 120.16.12020000.4 $';
92 END body_revision;
93
94 FUNCTION spec_revision RETURN VARCHAR2 IS
95 BEGIN
96 RETURN G_spec_revision;
97 END spec_revision;
98
99 FUNCTION get_security_account_type(p_account_type VARCHAR2)
100 RETURN VARCHAR2 IS
101 v_acct_type VARCHAR2(25);
102 BEGIN
103 v_acct_type := FND_PROFILE.VALUE_WNPS('CE_BANK_ACCOUNT_SECURITY_ACCESS');
104 IF (v_acct_type = 'ALL' AND p_account_type <> 'EXTERNAL')
105 THEN
106 v_acct_type := p_account_type;
107 END IF;
108 RETURN v_acct_type;
109 END get_security_account_type;
110
111 /* ---------------------------------------------------------------------
112 | PRIVATE PROCEDURE |
113 | xtr_shared_account |
114 | |
115 | DESCRIPTION |
116 | verify the bank account is a shared account or AP-only account |
117 | |
118 | CALLED BY |
119 | zba_generation |
120 ----------------------------------------------------------------------*/
121 PROCEDURE xtr_shared_account(X_ACCOUNT_RESULT OUT NOCOPY VARCHAR2)
122 IS
123 X_ERROR_MSG VARCHAR2(1000);
124
125 BEGIN
126 cep_standard.debug('>> CE_ZBA_DEAL_GENERATION.xtr_shared_account stub <<');
127 -- cep_standard.debug('ORG_ID = '|| CE_ZBA_DEAL_GENERATION.csh_org_id);
128 -- cep_standard.debug('BANK_ACCOUNT_ID = '|| CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
129 -- cep_standard.debug('CURRENCY_CODE = '|| CE_ZBA_DEAL_GENERATION.cba_bank_currency);
130 --
131 -- XTR_WRAPPER_API_P.bank_account_verification(
132 -- P_ORG_ID => CE_ZBA_DEAL_GENERATION.csh_org_id,
133 -- P_AP_BANK_ACCOUNT_ID => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
134 -- P_CURRENCY_CODE => CE_ZBA_DEAL_GENERATION.cba_bank_currency,
135 -- P_RESULT => X_ACCOUNT_RESULT,
136 -- P_ERROR_MSG => X_ERROR_MSG);
137 --
138 -- cep_standard.debug('x_account_result = ' || x_account_result);
139 -- cep_standard.debug('x_error_msg = ' || x_error_msg);
140 EXCEPTION
141 WHEN OTHERS THEN
142 cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.xtr_shared_account');
143 RAISE;
144 END xtr_shared_account;
145
146
147 /* ---------------------------------------------------------------------
148 | PRIVATE PROCEDURE |
149 | lock_statement |
150 | |
151 | DESCRIPTION |
152 | Using the rowid, lock the statement regular way |
153 | |
154 | CALLED BY |
155 | zba_generation |
156 | |
157 | REQUIRES |
158 | lockhandle |
159 ----------------------------------------------------------------------*/
160 FUNCTION lock_statement(lockhandle IN OUT NOCOPY VARCHAR2)
161 RETURN BOOLEAN IS
162 x_statement_header_id CE_STATEMENT_HEADERS.statement_header_id%TYPE;
163 lock_status NUMBER;
164 expiration_secs NUMBER;
165 lockname VARCHAR2(128);
166 lockmode NUMBER;
167 timeout NUMBER;
168 release_on_commit BOOLEAN;
169 BEGIN
170 SELECT statement_header_id
171 INTO x_statement_header_id
172 FROM ce_statement_headers
173 WHERE rowid = CE_ZBA_DEAL_GENERATION.csh_rowid
174 FOR UPDATE OF statement_header_id NOWAIT;
175
176 cep_standard.debug('Regular statement lock OK');
177 lockname := CE_ZBA_DEAL_GENERATION.csh_rowid;
178 timeout := 1;
179 lockmode := 6;
180 expiration_secs := 10;
181 release_on_commit := FALSE;
182
183 -- dbms_lock of row to deal with other locking
184 cep_standard.debug('Allocating unique');
185 dbms_lock.allocate_unique (lockname, lockhandle, expiration_secs);
186 lock_status := dbms_lock.request( lockhandle, lockmode, timeout, release_on_commit );
187
188 IF (lock_status <> 0) THEN
189 lock_status := dbms_lock.release(lockhandle);
190 RAISE APP_EXCEPTIONS.record_lock_exception;
191 END IF;
192
193 RETURN(TRUE);
194 EXCEPTION
195 WHEN APP_EXCEPTIONS.record_lock_exception THEN
196 cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement - record_lock_exception');
197 RETURN(FALSE);
198 WHEN OTHERS THEN
199 cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement - others');
200 RAISE;
201 RETURN(FALSE);
202 END lock_statement;
203
204 /* ---------------------------------------------------------------------
205 | PRIVATE PROCEDURE |
206 | lock_statement_line |
207 | |
208 | DESCRIPTION |
209 | Using the rowid, retrieve the statement line details. |
210 | |
211 | CALLED BY |
212 | zba_generation |
213 --------------------------------------------------------------------- */
214 FUNCTION lock_statement_line RETURN BOOLEAN IS
215 BEGIN
216 SELECT statement_line_id,
217 trx_date,
218 trx_type,
219 trx_code_id,
220 bank_trx_number,
221 invoice_text,
222 bank_account_text,
223 amount,
224 NVL(charges_amount,0),
225 currency_code,
226 line_number,
227 customer_text,
228 effective_date,
229 original_amount
230 INTO CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
231 CE_ZBA_DEAL_GENERATION.csl_trx_date,
232 CE_ZBA_DEAL_GENERATION.csl_trx_type,
233 CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
234 CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
235 CE_ZBA_DEAL_GENERATION.csl_invoice_text,
236 CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
237 CE_ZBA_DEAL_GENERATION.csl_amount,
238 CE_ZBA_DEAL_GENERATION.csl_charges_amount,
239 CE_ZBA_DEAL_GENERATION.csl_currency_code,
240 CE_ZBA_DEAL_GENERATION.csl_line_number,
241 CE_ZBA_DEAL_GENERATION.csl_customer_text,
242 CE_ZBA_DEAL_GENERATION.csl_effective_date,
243 CE_ZBA_DEAL_GENERATION.csl_original_amount
244 FROM ce_statement_lines
245 WHERE rowid = CE_ZBA_DEAL_GENERATION.csl_rowid
246 FOR UPDATE OF status NOWAIT;
247
248 RETURN(TRUE);
249 EXCEPTION
250 WHEN APP_EXCEPTIONS.record_lock_exception THEN
251 cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement_line - record_lock_exception');
252 return(FALSE);
253 WHEN OTHERS THEN
254 cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.lock_statement_line - others');
255 RAISE;
256 return(FALSE);
257 END lock_statement_line;
258
259 /* ---------------------------------------------------------------------
260 | PRIVATE FUNCTION |
261 | get_min_statement_line_id |
262 | |
263 | DESCRIPTION |
264 | |
265 | CALLED BY |
266 | zba_generation |
267 | |
268 | RETURNS |
269 | csl_statement_line_id Minimum statement line indentifier |
270 --------------------------------------------------------------------- */
271 FUNCTION get_min_statement_line_id RETURN NUMBER IS
272 min_statement_line NUMBER;
273 min_statement_line_num NUMBER;
274 BEGIN
275 SELECT min(line_number)
276 INTO min_statement_line_num
277 FROM ce_statement_lines
278 WHERE statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id;
279
280 SELECT statement_line_id
281 INTO min_statement_line
282 FROM ce_statement_lines
283 WHERE line_number = min_statement_line_num
284 AND statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id;
285
286 RETURN (min_statement_line);
287 EXCEPTION
288 WHEN OTHERS THEN
289 cep_standard.debug('EXCEPTION: CE_ZBA_DEAL_GENERATION.get_min_statement_line_id');
290 RAISE;
291 END get_min_statement_line_id;
292
293 /* ---------------------------------------------------------------------
294 | PRIVATE PROCEDURE |
295 | set_parameters |
296 | |
297 | DESCRIPTION |
298 | Procedure to set parameter values into globals |
299 | CALLED BY |
300 | zba_generation |
301 | REQUIRES |
302 | all parameters of CE_ZBA_DEAL_GENERATION.zba_generation |
303 ----------------------------------------------------------------------*/
304 PROCEDURE set_parameters(
305 p_bank_branch_id NUMBER,
306 p_bank_account_id NUMBER,
307 p_statement_number_from VARCHAR2,
308 p_statement_number_to VARCHAR2,
309 p_statement_date_from VARCHAR2,
310 p_statement_date_to VARCHAR2,
311 p_display_debug VARCHAR2,
312 p_debug_path VARCHAR2,
313 p_debug_file VARCHAR2
314 ) IS
315 BEGIN
316 CE_ZBA_DEAL_GENERATION.G_bank_branch_id := p_bank_branch_id;
317 CE_ZBA_DEAL_GENERATION.G_bank_account_id := p_bank_account_id;
318 CE_ZBA_DEAL_GENERATION.G_statement_number_from := p_statement_number_from;
319 CE_ZBA_DEAL_GENERATION.G_statement_number_to := p_statement_number_to;
320 CE_ZBA_DEAL_GENERATION.G_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
321 CE_ZBA_DEAL_GENERATION.G_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
322 CE_ZBA_DEAL_GENERATION.G_display_debug := p_display_debug;
323 CE_ZBA_DEAL_GENERATION.G_debug_path := p_debug_path;
324 CE_ZBA_DEAL_GENERATION.G_debug_file := p_debug_file;
325 END set_parameters;
326
327 /* ---------------------------------------------------------------------
328 | PRIVATE FUNCTION |
329 | break_bank_link |
330 | |
331 | DESCRIPTION |
332 | |
333 | CALLED BY |
334 | |
335 | RETURNS |
336 --------------------------------------------------------------------- */
337 FUNCTION break_bank_link(p_ap_bank_account_id NUMBER)
338 RETURN BOOLEAN IS
339 code_row_count NUMBER;
340 line_row_count NUMBER;
341 p_statement_header_id NUMBER;
342 i NUMBER;
343
344 CURSOR header_cursor IS
345 SELECT statement_header_id
346 FROM ce_statement_headers
347 WHERE bank_account_id = p_ap_bank_account_id;
348 BEGIN
349 cep_standard.debug('<<CE_ZBA_DEAL_GENERATION.break_bank_link');
350 SELECT count(1)
351 INTO code_row_count
352 FROM ce_transaction_codes
353 WHERE bank_account_id = p_ap_bank_account_id
354 AND trx_type in ('SWEEP_IN', 'SWEEP_OUT');
355
356 IF (code_row_count > 0) THEN
357 RETURN (FALSE);
358 ELSE
359 OPEN header_cursor;
360 i := 0;
361 LOOP
362 FETCH header_cursor INTO p_statement_header_id;
363 IF (header_cursor%ROWCOUNT = i)
364 THEN
365 IF i = 0 THEN
366 RETURN TRUE;
367 END IF;
368 EXIT;
369 ELSE
370 i := i + 1;
371 END IF;
372 SELECT count(1)
373 INTO line_row_count
374 FROM ce_statement_lines
375 WHERE statement_header_id = p_statement_header_id
376 AND trx_type in ('SWEEP_IN', 'SWEEP_OUT');
377
378 cep_standard.debug('line_row_count = '||line_row_count);
379
380 IF (line_row_count > 0) THEN
381 G_sweep_flag := TRUE;
382 RETURN (FALSE);
383 ELSE
384 RETURN (TRUE);
385 END IF;
386 EXIT WHEN G_sweep_flag = TRUE;
387 END LOOP;
388 CLOSE header_cursor;
389 END IF;
390 cep_standard.debug('<<CE_ZBA_DEAL_GENERATION.break_bank_link');
391 END break_bank_link;
392
393
394 /*========================================================================+
395 | PRIVATE PROCEDURE |
396 | zba_generation |
397 | |
398 | DESCRIPTION |
399 | Main procedure of sweep transactions generation |
400 | |
401 | ARGUMENTS |
402 | IN: |
403 | p_bank_branch_id Bank_branch_id |
404 | p_bank_account_id Bank_account_id |
405 | p_statement_number_from Statement number from |
406 | p_statement_number_to Statement number to |
407 | p_statement_date_from Statement Date from |
408 | p_statement_date_to Statement Date to |
409 | |
410 | p_display_debug Debug message flag (Y/N) |
411 | p_debug_path Debug path name if specified |
412 | p_debug_file Debug file name if specified |
413 | |
414 | CALLS |
415 +========================================================================*/
416 PROCEDURE zba_generation (
417 errbuf OUT NOCOPY VARCHAR2,
418 retcode OUT NOCOPY NUMBER,
419 p_bank_branch_id NUMBER,
420 p_bank_account_id NUMBER,
421 p_statement_number_from VARCHAR2,
422 p_statement_number_to VARCHAR2,
423 p_statement_date_from VARCHAR2,
424 p_statement_date_to VARCHAR2,
425 p_display_debug VARCHAR2,
426 p_debug_path VARCHAR2,
427 p_debug_file VARCHAR2
428 ) IS
429
430 i NUMBER;
431 j NUMBER;
432
433 error_statement_line_id CE_STATEMENT_LINES.statement_line_id%TYPE;
434 lockhandle VARCHAR2(128);
435 lock_status NUMBER;
436 statement_line_count NUMBER;
437 rec_status NUMBER;
438 row_count NUMBER;
439 x_account_result VARCHAR2(50);
440
441 x_offset_bank_account_id NUMBER;
442 x_cashpool_id NUMBER;
443
444 req_id NUMBER;
445 request_id NUMBER;
446 reqid VARCHAR2(30);
447 number_of_copies NUMBER;
448 printer VARCHAR2(30);
449 print_style VARCHAR2(30);
450 save_output_flag VARCHAR2(30);
451 save_output_bool BOOLEAN;
452 cp_match_bool BOOLEAN;
453
454 l_success_flag VARCHAR2(1);
455 l_deal_type VARCHAR2(3);
456 l_deal_num NUMBER;
457 l_transaction_num NUMBER;
458 l_offset_deal_num NUMBER;
459 l_offset_transaction_num NUMBER;
460
461 l_count NUMBER;
462 l_msg_count NUMBER;
463 l_error_msg VARCHAR2(255);
464 l_cashflows_created_flag VARCHAR2(1);
465
466 l_bank_acct_text ce_statement_lines.bank_account_text%TYPE; -- Bug # 7829965
467 l_dst_bank_acct_id ce_statement_headers.bank_account_id%TYPE :=0; -- Bug # 7829965
468
469 l_acct_type ce_cashpool_sub_accts.type%TYPE; -- Bug 14512483
470 l_offset_acct_type ce_cashpool_sub_accts.type%TYPE; -- Bug 14512483
471
472 BEGIN
473 cep_standard.debug('>>CE_ZBA_DEAL_GENERATION.zba_generation');
474 cep_standard.debug('p_bank_branch_id : '|| p_bank_branch_id);
475 cep_standard.debug('p_bank_account_id : '|| p_bank_account_id);
476 cep_standard.debug('p_statement_number_from: '|| p_statement_number_from);
477 cep_standard.debug('p_statement_number_to : '|| p_statement_number_to);
478 cep_standard.debug('p_statement_date_from : '|| p_statement_date_from);
479 cep_standard.debug('p_statement_date_to : '|| p_statement_date_to);
480
481 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
482 CEP_STANDARD.init_security;
483
484 set_parameters(
485 p_bank_branch_id,
486 p_bank_account_id,
487 p_statement_number_from,
488 p_statement_number_to,
489 p_statement_date_from,
490 p_statement_date_to,
491 p_display_debug,
492 p_debug_path,
493 p_debug_file);
494
495 cep_standard.debug('Opening r_branch_cursor');
496 OPEN r_branch_cursor(
497 CE_ZBA_DEAL_GENERATION.G_bank_branch_id,
498 CE_ZBA_DEAL_GENERATION.G_bank_account_id);
499 j := 0;
500 LOOP
501 cep_standard.debug('Fetching r_branch_cursor');
502 FETCH r_branch_cursor INTO CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
503 cep_standard.debug('r_branch_cursor%ROWCOUNT = ' || r_branch_cursor%ROWCOUNT );
504
505 IF (r_branch_cursor%ROWCOUNT = j)
506 THEN
507 cep_standard.debug('Exiting Branch Loop...');
508 EXIT;
509 ELSE
510 j := r_branch_cursor%ROWCOUNT;
511 END IF;
512
513 cep_standard.debug('Opening r_bank_cursor');
514 cep_standard.debug('G_statement_number_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_from);
515 cep_standard.debug('G_statement_number_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_number_to);
516 cep_standard.debug('G_statement_date_from = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_from);
517 cep_standard.debug('G_statement_date_to = ' || CE_ZBA_DEAL_GENERATION.G_statement_date_to);
518 cep_standard.debug('csh_bank_account_id = ' || CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
519
520 OPEN r_bank_cursor (
521 CE_ZBA_DEAL_GENERATION.G_statement_number_from,
522 CE_ZBA_DEAL_GENERATION.G_statement_number_to,
523 CE_ZBA_DEAL_GENERATION.G_statement_date_from,
524 CE_ZBA_DEAL_GENERATION.G_statement_date_to,
525 CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
526 i := 0;
527 LOOP
528 cep_standard.debug('Fetching r_bank_cursor');
529 FETCH r_bank_cursor INTO
530 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
531 CE_ZBA_DEAL_GENERATION.csh_statement_number,
532 CE_ZBA_DEAL_GENERATION.csh_statement_date,
533 CE_ZBA_DEAL_GENERATION.csh_check_digits,
534 CE_ZBA_DEAL_GENERATION.csh_statement_gl_date,
535 CE_ZBA_DEAL_GENERATION.cba_bank_currency,
536 CE_ZBA_DEAL_GENERATION.cba_multi_currency_flag,
537 CE_ZBA_DEAL_GENERATION.cba_check_digits,
538 CE_ZBA_DEAL_GENERATION.csh_rowid,
539 CE_ZBA_DEAL_GENERATION.csh_statement_complete_flag,
540 CE_ZBA_DEAL_GENERATION.csh_org_id;
541 cep_standard.debug('statement_header_id:' || CE_ZBA_DEAL_GENERATION.csh_statement_header_id );
542 cep_standard.debug('r_bank_cursor%ROWCOUNT = ' || r_bank_cursor%ROWCOUNT );
543 IF (r_bank_cursor%ROWCOUNT = i)
544 THEN
545 cep_standard.debug('Exiting Bank Loop...');
546 EXIT;
547 ELSE
548 i := r_bank_cursor%ROWCOUNT;
549 END IF;
550 cep_standard.debug('Itertion Number = ' || i);
551
552 -- Clean up error table
553 CE_ZBA_DEAL_INF_PKG.delete_row(
554 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
555 to_number(NULL));
556
557 -- Validate existing of unreconciled sweep lines
558 select count(1)
559 into row_count
560 from ce_statement_lines
561 where statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id
562 and trx_type in ('SWEEP_IN', 'SWEEP_OUT');
563
564 cep_standard.debug('sweep row_count = '||row_count);
565
566 IF (row_count = 0)
567 THEN
568 CE_ZBA_DEAL_INF_PKG.insert_row(
569 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
570 to_number(NULL),
571 'CE_NO_SWEEP_STMT_LINE');
572 -- Bug 11686554 CONTINUE is not supported in oracle 10g
573 -- CONTINUE; -- Bug 12854251 Changed EXIT to CONTINUE
574 cep_standard.debug('no sweep lines in statement');
575 ELSE --Validate the bank account is an authorized treasury account
576 IF fnd_profile.value('CE_BANK_ACCOUNT_TRANSFERS') = 'XTR'
577 THEN
578 SELECT count(1)
579 INTO l_count
580 FROM ce_bank_accounts ba, ce_bank_acct_uses_all bau
581 WHERE ba.bank_account_id = CE_ZBA_DEAL_GENERATION.csh_bank_account_id
582 AND bau.bank_account_id = ba.bank_account_id
583 AND ba.xtr_use_allowed_flag = 'Y'
584 AND bau.authorized_flag = 'Y';
585
586 cep_standard.debug('authorised l_count = '||row_count);
587 IF l_count = 0
588 THEN
589 CE_ZBA_DEAL_INF_PKG.insert_row (
590 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
591 to_number(NULL),
592 'CE_XTR_INVALID_ACCT');
593 cep_standard.debug('Exiting For Invalid Account ...');
594 EXIT;
595 END IF;
596 END IF;
597 -- END IF; -- Bug 11686554: END IF moved to the end of the loop.
598
599 cep_standard.debug('check the check digits');
600 IF(NVL(LTRIM(NVL(CE_ZBA_DEAL_GENERATION.csh_check_digits, 'NO DIGIT'),'0'),'0') =
601 NVL(LTRIM(NVL(CE_ZBA_DEAL_GENERATION.cba_check_digits, 'NO DIGIT'),'0'),'0'))
602 THEN
603 cep_standard.debug('csh_check_digits = '||csh_check_digits);
604 cep_standard.debug('cba_check_digits = '||cba_check_digits);
605
606 cep_standard.debug('Lock the statement');
607 IF (lock_statement(lockhandle))
608 THEN
609 IF (csh_statement_complete_flag = 'N')
610 THEN
611 statement_line_count := 0;
612
613 -- Read in all the lines on the statement for the selected bank
614 -- account.
615 cep_standard.debug('Opening line_cursor');
616 OPEN line_cursor (CE_ZBA_DEAL_GENERATION.csh_statement_header_id);
617 LOOP
618 FETCH line_cursor INTO
619 CE_ZBA_DEAL_GENERATION.csl_rowid,
620 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
621 CE_ZBA_DEAL_GENERATION.csl_receivables_trx_id,
622 CE_ZBA_DEAL_GENERATION.csl_receipt_method_id,
623 CE_ZBA_DEAL_GENERATION.csl_create_misc_trx_flag,
624 CE_ZBA_DEAL_GENERATION.csl_matching_against,
625 CE_ZBA_DEAL_GENERATION.csl_correction_method,
626 CE_ZBA_DEAL_GENERATION.csl_receipt_method_name,
627 CE_ZBA_DEAL_GENERATION.csl_exchange_rate_type,
628 CE_ZBA_DEAL_GENERATION.csl_exchange_rate_date,
629 CE_ZBA_DEAL_GENERATION.csl_exchange_rate,
630 CE_ZBA_DEAL_GENERATION.csl_currency_code,
631 CE_ZBA_DEAL_GENERATION.csl_line_trx_type,
632 CE_ZBA_DEAL_GENERATION.csl_reconcile_flag,
633 CE_ZBA_DEAL_GENERATION.csl_match_found,
634 CE_ZBA_DEAL_GENERATION.csl_match_type,
635 CE_ZBA_DEAL_GENERATION.csl_clearing_trx_type,
636 CE_ZBA_DEAL_GENERATION.csl_original_amount,
637 CE_ZBA_DEAL_GENERATION.csl_payroll_payment_format;
638 EXIT WHEN line_cursor%NOTFOUND OR line_cursor%NOTFOUND IS NULL;
639 cep_standard.debug('csl_statement_line_id = '|| CE_ZBA_DEAL_GENERATION.csl_statement_line_id);
640
641 cep_standard.debug('check if reconciled');
642 select count(*)
643 into rec_status
644 from ce_statement_reconcils_all
645 where statement_line_id = CE_ZBA_DEAL_GENERATION.csl_statement_line_id
646 and nvl(status_flag, 'U') = 'M'
647 and nvl(current_record_flag, 'Y') = 'Y';
648 cep_standard.debug('rec_status='||rec_status);
649
650 if (rec_status = 0)
651 then
652 statement_line_count := statement_line_count + 1;
653 --
654 -- Clear ce_zba_deal_inf table
655 --
656 CE_ZBA_DEAL_INF_PKG.delete_row(
657 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
658 CE_ZBA_DEAL_GENERATION.csl_statement_line_id);
659
660 IF (lock_statement_line)
661 THEN
662 cep_standard.debug('csl_amount = '||csl_amount);
663 --Validate zero amount line
664 IF (CE_ZBA_DEAL_GENERATION.csl_amount = 0)
665 THEN
666 CE_ZBA_DEAL_INF_PKG.insert_row (
667 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
668 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
669 'CE_ZBA_ZERO_AMOUNT');
670 END IF;
671
672 --Validate different currencies between statement header and line
673 IF (CE_ZBA_DEAL_GENERATION.cba_bank_currency <>
674 nvl(CE_ZBA_DEAL_GENERATION.csl_currency_code, CE_ZBA_DEAL_GENERATION.cba_bank_currency))
675 THEN
676 CE_ZBA_DEAL_INF_PKG.insert_row (
677 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
678 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
679 'CE_ZBA_DIFF_CURRENCY');
680 END IF;
681 cep_standard.debug('csl_currency_code = '||csl_currency_code);
682 cep_standard.debug('cba_bank_currency = '||cba_bank_currency);
683
684 /* Bug # 7829965 Start */
685 BEGIN
686 SELECT bank_account_text
687 INTO l_bank_acct_text
688 FROM ce_statement_lines
689 WHERE statement_line_id = CE_ZBA_DEAL_GENERATION.csl_statement_line_id;
690
691 SELECT cps. account_id
692 INTO l_dst_bank_acct_id
693 FROM ce_bank_accounts cba,
694 ce_cashpools cp,
695 CE_CASHPOOL_SUB_ACCTS cps
696 WHERE cba.bank_account_id = cp.conc_account_id
697 AND cp.cashpool_id = cps.cashpool_id
698 AND cps.type in ('ACCT','CONC','INV','FUND') /* bug 14140123 */
699 AND cps.account_id IN (SELECT bank_account_id
700 FROM ce_bank_accounts
701 WHERE bank_account_num = l_bank_acct_text);
702
703 EXCEPTION
704 WHEN OTHERS
705 THEN
706 -- The Exception is not handled here when the agent bank account
707 -- is not found as it is already handled in the code.
708 cep_standard.debug('7829965 - Exception - Others');
709 NULL;
710 END;
711 /* Bug # 7829965 End */
712
713 IF(CE_ZBA_DEAL_GENERATION.csh_bank_account_id <> l_dst_bank_acct_id)
714 THEN -- Bug # 7829965 -- If Source and Destination Accounts are not the same
715 --
716 -- Deal Generation
717 --
718 cep_standard.debug('Check accounts and cashpool');
719 cep_standard.debug('p_header_bank_account_id=>'||CE_ZBA_DEAL_GENERATION.csh_bank_account_id);
720 cep_standard.debug('p_offset_bank_account_num=>'||CE_ZBA_DEAL_GENERATION.csl_bank_account_text);
721 cep_standard.debug('p_trx_type=>'||CE_ZBA_DEAL_GENERATION.csl_trx_type);
722 cep_standard.debug('p_trx_date=>'||CE_ZBA_DEAL_GENERATION.csl_trx_date);
723 cep_standard.debug('p_offset_bank_account_id='||x_offset_bank_account_id);
724 cep_standard.debug('p_cashpool_id='||x_cashpool_id);
725 cp_match_bool := CE_LEVELING_UTILS.Match_Cashpool(
726 p_header_bank_account_id => CE_ZBA_DEAL_GENERATION.csh_bank_account_id,
727 p_offset_bank_account_num => CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
728 p_trx_type => CE_ZBA_DEAL_GENERATION.csl_trx_type,
729 p_trx_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
730 p_offset_bank_account_id => x_offset_bank_account_id,
731 p_cashpool_id => x_cashpool_id);
732
733 IF (cp_match_bool)
734 THEN -- found matching cash pool
735 CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id := x_offset_bank_account_id;
736 CE_ZBA_DEAL_GENERATION.p_cashpool_id := x_cashpool_id;
737
738 -- Bug 14512483 Start
739 -- Validations for Cashpools with 2 Concentration accounts
740 SELECT TYPE
741 INTO L_ACCT_TYPE
742 FROM CE_CASHPOOL_SUB_ACCTS
743 WHERE CASHPOOL_ID = X_CASHPOOL_ID
744 AND ACCOUNT_ID = CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
745
746 SELECT TYPE
747 INTO L_OFFSET_ACCT_TYPE
748 FROM CE_CASHPOOL_SUB_ACCTS
749 WHERE CASHPOOL_ID = X_CASHPOOL_ID
750 AND ACCOUNT_ID = X_OFFSET_BANK_ACCOUNT_ID;
751
752 cep_standard.debug('L_ACCT_TYPE = '||L_ACCT_TYPE);
753 cep_standard.debug('L_OFFSET_ACCT_TYPE = '||L_OFFSET_ACCT_TYPE);
754 cep_standard.debug('X_CASHPOOL_ID = '||x_cashpool_id);
755 cep_standard.debug('CE_ZBA_DEAL_GENERATION.csl_trx_type = '||CE_ZBA_DEAL_GENERATION.csl_trx_type);
756
757
758 IF (L_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
759 THEN
760 cep_standard.debug('Can not sweep in for Funding concentration account..');
761 CE_ZBA_DEAL_INF_PKG.insert_row (
762 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
763 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
764 'CE_ZBA_NO_FUND_SWEEP_IN');
765
766 ELSIF (L_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT')
767 THEN
768 cep_standard.debug('Can not sweep out for Investment concentration account..');
769 CE_ZBA_DEAL_INF_PKG.insert_row (
770 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
771 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
772 'CE_ZBA_NO_INV_SWEEP_OUT');
773
774 ELSIF (L_OFFSET_ACCT_TYPE = 'FUND' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_OUT' )
775 THEN
776 cep_standard.debug('Can not sweep in for Funding concentration account..');
777 CE_ZBA_DEAL_INF_PKG.insert_row (
778 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
779 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
780 'CE_ZBA_NO_FUND_SWEEP_IN_SUB');
781
782 ELSIF (L_OFFSET_ACCT_TYPE = 'INV' AND CE_ZBA_DEAL_GENERATION.csl_trx_type = 'SWEEP_IN')
783 THEN
784 cep_standard.debug('Can not sweep out for Investment concentration account..');
785 CE_ZBA_DEAL_INF_PKG.insert_row (
786 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
787 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
788 'CE_ZBA_NO_INV_SWEEP_OUT_SUB');
789
790 -- Bug 14512483 End
791 ELSE
792 -- Bug5122576. The from and to bank accounts will be determined
793 -- in the Generate_Fund_Transfer api. From here, the
794 -- p_from_bank_account_id will always be the statement line's
795 -- bank account and p_to_bank_Account_id will always be the
796 -- offset bank account
797
798 CE_ZBA_DEAL_GENERATION.p_from_bank_account_id :=
799 CE_ZBA_DEAL_GENERATION.csh_bank_account_id;
800 CE_ZBA_DEAL_GENERATION.p_to_bank_account_id :=
801 CE_ZBA_DEAL_GENERATION.p_offset_bank_account_id;
802
803 CE_LEVELING_UTILS.Generate_Fund_Transfer(
804 X_from_bank_account_id => CE_ZBA_DEAL_GENERATION.p_from_bank_account_id,
805 X_to_bank_account_id => CE_ZBA_DEAL_GENERATION.p_to_bank_account_id,
806 X_cashpool_id => CE_ZBA_DEAL_GENERATION.p_cashpool_id,
807 X_amount => CE_ZBA_DEAL_GENERATION.csl_amount,
808 X_transfer_date => CE_ZBA_DEAL_GENERATION.csl_trx_date,
809 X_settlement_authorized => 'Y',
810 X_accept_limit_error => 'Y',
811 X_request_id => null,
812 X_deal_type => l_deal_type,
813 X_deal_no => l_deal_num,
814 X_trx_number => l_transaction_num,
815 X_offset_deal_no => l_offset_deal_num,
816 X_offset_trx_number => l_offset_transaction_num,
817 X_success_flag => l_success_flag,
818 X_statement_line_id => CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
819 X_msg_count => l_msg_count,
820 X_cashflows_created_flag => l_cashflows_created_flag,
821 X_called_by_flag => 'Z');
822
823 IF l_success_flag = 'Y' THEN
824 INSERT INTO CE_ZBA_DEAL_MESSAGES(
825 application_short_name,
826 statement_header_id,
827 statement_line_id,
828 creation_date,
829 created_by,
830 deal_type,
831 deal_num,
832 transaction_num,
833 cashpool_id,
834 cashflows_created_flag,
835 offset_deal_num,
836 offset_transaction_num,
837 deal_status_flag)
838 VALUES (
839 'CE',
840 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
841 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
842 sysdate,
843 NVL(FND_GLOBAL.user_id,-1),
844 l_deal_type,
845 l_deal_num,
846 l_transaction_num,
847 CE_ZBA_DEAL_GENERATION.p_cashpool_id,
848 l_cashflows_created_flag,
849 l_offset_deal_num,
850 l_offset_transaction_num,
851 'Y');
852 ELSE
853 /*12349973*/
854 IF l_msg_count IS NOT NULL THEN
855 FOR i IN 1..l_msg_count
856 LOOP
857 INSERT INTO CE_ZBA_DEAL_MESSAGES(
858 application_short_name,
859 statement_header_id,
860 statement_line_id,
861 message_name,
862 creation_date,
863 created_by,
864 deal_status_flag,
865 cashpool_id)
866 VALUES (
867 'CE',
868 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
869 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
870 FND_MSG_PUB.get(1, FND_API.G_FALSE),
871 sysdate,
872 NVL(FND_GLOBAL.user_id,-1),
873 'N',
874 CE_ZBA_DEAL_GENERATION.p_cashpool_id);
875 FND_MSG_PUB.delete_msg(1);
876 END LOOP;
877 END IF;
878 END IF;
879 END IF; -- Bug 14512483 Validation for Cashpool with 2 Concntration Accounts.
880 ELSE -- cp_match_bool = false
881 cep_standard.debug('No matching cashpool found');
882 CE_ZBA_DEAL_INF_PKG.insert_row(
883 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
884 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
885 'CE_NO_CASHPOOL_MATCH');
886 END IF;
887 /* Bug # 7829965 Start */
888 ELSE -- Source and Destination Accounts are the same
889 cep_standard.debug('Source and Destination Accounts are the same');
890 CE_ZBA_DEAL_INF_PKG.insert_row(
891 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
892 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
893 'CE_BAT_INVALID_BACCNTS');
894 END IF;
895 /* Bug # 7829965 End */
896 ELSE -- statement line is locked
897 cep_standard.debug('statement line is locked');
898 CE_ZBA_DEAL_INF_PKG.insert_row(
899 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
900 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
901 'CE_LINE_LOCKED');
902 END IF;
903
904 IF (statement_line_count = CE_AUTO_BANK_REC.G_lines_per_commit)
905 THEN
906 COMMIT;
907 statement_line_count := 0;
908 END IF;
909 END IF; -- if rec_status = 0
910 END LOOP; -- statement lines
911
912 CLOSE line_cursor;
913
914 ELSE -- statement complete flag is Y
915
916 error_statement_line_id := get_min_statement_line_id;
917 CE_ZBA_DEAL_INF_PKG.delete_row(
918 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
919 error_statement_line_id);
920 CE_ZBA_DEAL_INF_PKG.insert_row(
921 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
922 error_statement_line_id,
923 'CE_STATEMENT_COMPLETED');
924 END IF; -- statement complete flag check
925
926 ELSE -- statement is locked
927 CE_ZBA_DEAL_INF_PKG.delete_row(CE_ZBA_DEAL_GENERATION.csh_statement_header_id, to_number(NULL));
928 CE_ZBA_DEAL_INF_PKG.insert_row(
929 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
930 to_number(NULL),
931 'CE_LOCK_STATEMENT_HEADER_ERR');
932 END IF; -- statement lock check
933 lock_status := dbms_lock.release(lockhandle);
934 ELSE -- check digits failed
935 cep_standard.debug('Account/Statement check digit mismatch');
936 CE_ZBA_DEAL_INF_PKG.delete_row(
937 CE_ZBA_DEAL_GENERATION.csh_statement_header_id, to_number(NULL));
938 CE_ZBA_DEAL_INF_PKG.insert_row(
939 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
940 to_number(NULL),
941 'CE_CHECK_DIGITS');
942 END IF; -- check_digits
943 END IF; -- Bug 11686554 Introduced end if to continue loop for next statement header.
944 END LOOP; -- statement headers
945 CLOSE r_bank_cursor;
946 END LOOP;
947 CLOSE r_branch_cursor;
948 --
949 -- Get original request id
950 --
951 fnd_profile.get('CONC_REQUEST_ID', reqid);
952 request_id := to_number(reqid);
953 --
954 -- Get print options
955 --
956 IF(FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(
957 request_id,
958 number_of_copies,
959 print_style,
960 printer,
961 save_output_flag))
962 THEN
963 cep_standard.debug('zba_generation: ' || 'Message: get print options success');
964 ELSE
965 IF (save_output_flag = 'Y') THEN
966 save_output_bool := TRUE;
967 ELSE
968 save_output_bool := FALSE;
969 END IF;
970
971 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES (
972 'CE', 'CEZBAERR', printer, print_style, save_output_flag))
973 THEN
974 cep_standard.debug('zba_generation: ' || 'Message: get print options failed');
975 END IF;
976 END IF;
977 -- Set print options
978 IF (NOT FND_REQUEST.set_print_options(
979 printer, print_style, number_of_copies, save_output_bool))
980 THEN
981 cep_standard.debug('zba_generation: ' || 'Set print options failed');
982 END IF;
983 req_id := FND_REQUEST.SUBMIT_REQUEST(
984 'CE',
985 'CEZBAERR',
986 NULL,
987 to_char(sysdate,'YYYY/MM/DD'),
988 FALSE,
989 p_bank_branch_id,
990 p_bank_account_id,
991 p_statement_number_from,
992 p_statement_number_to,
993 p_statement_date_from,
994 p_statement_date_to,
995 p_display_debug,
996 p_display_debug);
997 COMMIT;
998
999 IF (req_id = 0) THEN
1000 cep_standard.debug('zba_generation: ' || 'ERROR submitting concurrent request');
1001 ELSE
1002 cep_standard.debug('zba_generation: ' || 'EXECUTION REPORT SUBMITTED');
1003 END IF;
1004
1005 cep_standard.debug('<<CE_ZBA_DEAL_GENERATION.zba_generation');
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 cep_standard.debug(' EXCEPTION: CE_ZBA_DEAL_GENERATION.zba_generation - OTHERS');
1009 IF r_branch_cursor%ISOPEN THEN
1010 CLOSE r_branch_cursor;
1011 END IF;
1012 IF r_bank_cursor%ISOPEN THEN
1013 CLOSE r_bank_cursor;
1014 END IF;
1015 IF line_cursor%ISOPEN THEN
1016 CLOSE line_cursor;
1017 END IF;
1018 lock_status := dbms_lock.release(lockhandle);
1019 cep_standard.debug('DEBUG: sqlcode:' || sqlcode );
1020 cep_standard.debug('DEBUG: sqlerrm:' || sqlerrm);
1021 RAISE;
1022 END zba_generation;
1023
1024 END CE_ZBA_DEAL_GENERATION;