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