[Home] [Help]
PACKAGE BODY: APPS.CE_CP_PRIORDAY
Source
1 PACKAGE BODY CE_CP_PRIORDAY AS
2 /* $Header: cecpprib.pls 120.5 2006/07/11 13:25:20 jikumar ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5
6 FUNCTION body_revision RETURN VARCHAR2 IS
7 BEGIN
8
9 RETURN '$Revision: 120.5 $';
10
11 END body_revision;
12
13 FUNCTION spec_revision RETURN VARCHAR2 IS
14 BEGIN
15
16 RETURN G_spec_revision;
17
18 END spec_revision;
19
20 PROCEDURE set_parameters(p_worksheet_header_id NUMBER,
21 p_as_of_date VARCHAR2,
22 p_display_debug VARCHAR2,
23 p_debug_path VARCHAR2,
24 p_debug_file VARCHAR2) IS
25 BEGIN
26 IF l_debug in ('Y', 'C') THEN
27 cep_standard.debug('>>CE_CP_PRIORDAY.set_parameters -------');
28 END IF;
29 CE_CP_PRIORDAY.G_worksheet_header_id := p_worksheet_header_id;
30 IF (p_as_of_date is null) THEN
31 CE_CP_PRIORDAY.G_purge_flag := 'Y';
32 ELSE
33 CE_CP_PRIORDAY.G_purge_flag := 'N';
34 END IF;
35 CE_CP_PRIORDAY.G_as_of_date := nvl(to_date(p_as_of_date,
36 'YYYY/MM/DD HH24:MI:SS'),
37 trunc(sysdate));
38 CE_CP_PRIORDAY.G_display_debug := p_display_debug;
39 CE_CP_PRIORDAY.G_debug_path := p_debug_path;
40 CE_CP_PRIORDAY.G_debug_file := p_debug_file;
41
42 cep_standard.debug('G_worksheet_header_id = ' ||
43 CE_CP_PRIORDAY.G_worksheet_header_id);
44 cep_standard.debug('G_as_of_date = ' || CE_CP_PRIORDAY.G_as_of_date);
45 cep_standard.debug('G_display_debug = ' || CE_CP_PRIORDAY.G_display_debug);
46 cep_standard.debug('G_debug_path = ' || CE_CP_PRIORDAY.G_debug_path);
47 cep_standard.debug('G_debug_file = ' || CE_CP_PRIORDAY.G_debug_file);
48
49 END set_parameters;
50
51 PROCEDURE calculate_summary IS
52 l_ws_id CE_CP_WORKSHEET_HEADERS.worksheet_header_id%TYPE;
53 l_bank_account_id CE_CP_PRIORDAY_BALANCES.bank_account_id%TYPE;
54 l_currency_code VARCHAR2(15);
55 l_statement_date DATE;
56 tmp_balance NUMBER;
57
58 CURSOR wsba_cursor(p_ws_id NUMBER) IS
59 SELECT WBA.bank_account_id,
60 WBA.currency_code,
61 OPEN.statement_date
62 FROM CE_CP_OPEN_BAL_V OPEN,
63 CE_CP_WS_BA_V WBA
64 WHERE WBA.worksheet_header_id = p_ws_id
65 AND WBA.bank_account_id = OPEN.bank_account_id
66 AND OPEN.statement_date < CE_CP_PRIORDAY.G_as_of_date
67 AND OPEN.next_stmt_date >= CE_CP_PRIORDAY.G_as_of_date;
68
69 CURSOR ba_cursor IS
70 SELECT bank_account_id,
71 currency_code,
72 statement_date
73 FROM CE_CP_OPEN_BAL_V
74 WHERE statement_date < CE_CP_PRIORDAY.G_as_of_date
75 AND next_stmt_date >= CE_CP_PRIORDAY.G_as_of_date;
76
77 CURSOR ws_cursor IS
78 SELECT worksheet_header_id
79 FROM CE_CP_WORKSHEET_HEADERS
80 WHERE pd_flag = 'Y'
81 AND (CE_CP_PRIORDAY.G_worksheet_header_id is null
82 OR worksheet_header_id = CE_CP_PRIORDAY.G_worksheet_header_id);
83
84 BEGIN
85
86 IF l_debug in ('Y', 'C') THEN
87 cep_standard.debug('>>CE_CP_PRIORDAY.calculate_summary -------');
88 END IF;
89
90 /* purge prior-day data */
91 IF (CE_CP_PRIORDAY.G_purge_flag = 'Y') THEN
92 BEGIN
93 DELETE CE_CP_PRIORDAY_BALANCES
94 WHERE worksheet_header_id = -1
95 AND as_of_date <= trunc(sysdate);
96 END;
97 ELSE
98 BEGIN
99 DELETE CE_CP_PRIORDAY_BALANCES
100 WHERE worksheet_header_id = -1
101 AND as_of_date = CE_CP_PRIORDAY.G_as_of_date;
102 END;
103 END IF;
104
105 /* generate prior-day data */
106 OPEN ba_cursor;
107 LOOP
108 FETCH ba_cursor INTO l_bank_account_id, l_currency_code, l_statement_date;
109 EXIT WHEN ba_cursor%NOTFOUND OR ba_cursor%NOTFOUND IS NULL;
110
111 BEGIN
112
113 --bug5219376
114 LOCK TABLE CE_CP_PRIORDAY_BALANCES in EXCLUSIVE MODE;
115
116 INSERT INTO CE_CP_PRIORDAY_BALANCES
117 (worksheet_header_id,
118 as_of_date,
119 bank_account_id,
120 balance_date,
121 source_type,
122 balance,
123 last_update_date,
124 last_updated_by,
125 last_update_login,
126 creation_date,
127 created_by)
128 VALUES (-1,
129 CE_CP_PRIORDAY.G_as_of_date,
130 l_bank_account_id,
131 l_statement_date,
132 'HEAD',
133 0,
134 trunc(sysdate),
135 -1,
136 -1,
137 trunc(sysdate),
138 -1);
139 END;
140
141 tmp_balance := 0;
142
143 /* Prior Day for APP */
144 SELECT -sum(decode(currency_code, l_currency_code, amount, base_amount))
145 INTO tmp_balance
146 FROM CE_AP_FC_PAYMENTS_V
147 WHERE bank_account_id = l_bank_account_id
148 AND nvl(actual_value_date, nvl(anticipated_value_date,
149 nvl(maturity_date, payment_date))) > l_statement_date
150 AND nvl(actual_value_date, nvl(anticipated_value_date,
151 nvl(maturity_date, payment_date)))
152 < CE_CP_PRIORDAY.G_as_of_date;
153
154 IF (tmp_balance IS NOT NULL) THEN
155 BEGIN
156 INSERT INTO CE_CP_PRIORDAY_BALANCES
157 (worksheet_header_id,
158 as_of_date,
159 bank_account_id,
160 balance_date,
161 source_type,
162 balance,
163 last_update_date,
164 last_updated_by,
165 last_update_login,
166 creation_date,
167 created_by)
168 VALUES (-1,
169 CE_CP_PRIORDAY.G_as_of_date,
170 l_bank_account_id,
171 l_statement_date,
172 'APP',
173 tmp_balance,
174 trunc(sysdate),
175 -1,
176 -1,
177 trunc(sysdate),
178 -1);
179 END;
180 END IF;
181
182 tmp_balance := 0;
183
184 /* Prior Day for ARR */
185 SELECT sum(decode(currency_code, l_currency_code, amount, base_amount))
186 INTO tmp_balance
187 FROM CE_AR_FC_RECEIPTS_V
188 WHERE bank_account_id = l_bank_account_id
189 AND cash_activity_date > l_statement_date
190 AND cash_Activity_date < CE_CP_PRIORDAY.G_as_of_date;
191
192 IF (tmp_balance is not null) THEN
193 BEGIN
194 INSERT INTO CE_CP_PRIORDAY_BALANCES
195 (worksheet_header_id,
196 as_of_date,
197 bank_account_id,
198 balance_date,
199 source_type,
200 balance,
201 last_update_date,
202 last_updated_by,
203 last_update_login,
204 creation_date,
205 created_by)
206 VALUES (-1,
207 CE_CP_PRIORDAY.G_as_of_date,
208 l_bank_account_id,
209 l_statement_date,
210 'ARR',
211 tmp_balance,
212 trunc(sysdate),
213 -1,
214 -1,
215 trunc(sysdate),
216 -1);
217 END;
218 END IF;
219
220 tmp_balance := 0;
221
222 /* Prior Day for XTR */
223 SELECT sum(amount)
224 INTO tmp_balance
225 FROM CE_XTR_CASHFLOWS_V
226 WHERE bank_account_id = l_bank_account_id
227 AND trx_date > l_statement_date
228 AND trx_date < CE_CP_PRIORDAY.G_as_of_date;
229
230 IF (tmp_balance is not null) THEN
231 BEGIN
232 INSERT INTO CE_CP_PRIORDAY_BALANCES
233 (worksheet_header_id,
234 as_of_date,
235 bank_account_id,
236 balance_date,
237 source_type,
238 balance,
239 last_update_date,
240 last_updated_by,
241 last_update_login,
242 creation_date,
243 created_by)
244 VALUES (-1,
245 CE_CP_PRIORDAY.G_as_of_date,
246 l_bank_account_id,
247 l_statement_date,
248 'XTR',
249 tmp_balance,
250 trunc(sysdate),
251 -1,
252 -1,
253 trunc(sysdate),
254 -1);
255 END;
256 END IF;
257
258 tmp_balance := 0;
259
260 /* Prior Day for PAY */
261 SELECT -sum(decode(currency_code, l_currency_code, amount, base_amount))
262 INTO tmp_balance
263 FROM CE_PAY_FC_PAYROLL_V
264 WHERE bank_account_id = l_bank_account_id
265 AND trx_date > l_statement_date
266 AND trx_date < CE_CP_PRIORDAY.G_as_of_date;
267
268 IF (tmp_balance is not null) THEN
269 BEGIN
270 INSERT INTO CE_CP_PRIORDAY_BALANCES
271 (worksheet_header_id,
272 as_of_date,
273 bank_account_id,
274 balance_date,
275 source_type,
276 balance,
277 last_update_date,
278 last_updated_by,
279 last_update_login,
280 creation_date,
281 created_by)
282 VALUES (-1,
283 CE_CP_PRIORDAY.G_as_of_date,
284 l_bank_account_id,
285 l_statement_date,
286 'PAY',
287 tmp_balance,
288 trunc(sysdate),
289 -1,
290 -1,
291 trunc(sysdate),
292 -1);
293 END;
294 END IF;
295
296 /* Prior Day for CEI */
297 SELECT sum(decode(currency_code, l_currency_code, cashflow_amount, base_amount))
298 INTO tmp_balance
299 FROM CE_CE_CASHFLOWS_V
300 WHERE cash_activity_date > l_statement_date
301 AND cash_activity_date < CE_CP_PRIORDAY.G_as_of_date
302 AND source_trxn_type <> 'STMT'
303 AND cashflow_direction = 'RECEIPT';
304
305 IF (tmp_balance is not null) THEN
306 BEGIN
307 INSERT INTO CE_CP_PRIORDAY_BALANCES
308 (worksheet_header_id,
309 as_of_date,
310 bank_account_id,
311 balance_date,
312 source_type,
313 balance,
314 last_update_date,
315 last_updated_by,
316 last_update_login,
317 creation_date,
318 created_by)
319 VALUES (-1,
320 CE_CP_PRIORDAY.G_as_of_date,
321 l_bank_account_id,
322 l_statement_date,
323 'CEI',
324 tmp_balance,
325 trunc(sysdate),
326 -1,
327 -1,
328 trunc(sysdate),
329 -1);
330 END;
331 END IF;
332
333 tmp_balance := 0;
334
335 /* Prior Day for CEO */
336 SELECT sum(decode(currency_code, l_currency_code, cashflow_amount, base_amount))
337 INTO tmp_balance
338 FROM CE_CE_CASHFLOWS_V
339 WHERE cash_activity_date > l_statement_date
340 AND cash_activity_date < CE_CP_PRIORDAY.G_as_of_date
341 AND source_trxn_type <> 'STMT'
342 AND cashflow_direction = 'PAYMENT';
343
344 IF (tmp_balance is not null) THEN
345 BEGIN
346 INSERT INTO CE_CP_PRIORDAY_BALANCES
347 (worksheet_header_id,
348 as_of_date,
349 bank_account_id,
350 balance_date,
351 source_type,
352 balance,
353 last_update_date,
354 last_updated_by,
355 last_update_login,
356 creation_date,
357 created_by)
358 VALUES (-1,
359 CE_CP_PRIORDAY.G_as_of_date,
360 l_bank_account_id,
361 l_statement_date,
362 'CEO',
363 tmp_balance,
364 trunc(sysdate),
365 -1,
366 -1,
367 trunc(sysdate),
368 -1);
369 END;
370 END IF;
371
372 tmp_balance := 0;
373
374
375 END LOOP;
376 CLOSE ba_cursor;
377
378 COMMIT;
379
380 /* generate overdue data */
381 OPEN ws_cursor;
382 LOOP
383 FETCH ws_cursor INTO l_ws_id;
384 EXIT WHEN ws_cursor%NOTFOUND OR ws_cursor%NOTFOUND IS NULL;
385
386 cep_standard.debug('l_ws_id = ' || l_ws_id);
387
388 /* purge overdue data */
389 IF (CE_CP_PRIORDAY.G_purge_flag = 'Y') THEN
390 DELETE CE_CP_PRIORDAY_BALANCES
391 WHERE worksheet_header_id = l_ws_id
392 AND as_of_date <= trunc(sysdate);
393 ELSE
394 DELETE CE_CP_PRIORDAY_BALANCES
395 WHERE worksheet_header_id = l_ws_id
396 AND as_of_date = CE_CP_PRIORDAY.G_as_of_date;
397 END IF;
398
399 OPEN wsba_cursor(l_ws_id);
400 LOOP
401 FETCH wsba_cursor INTO l_bank_account_id, l_currency_code,
402 l_statement_date;
403 EXIT WHEN wsba_cursor%NOTFOUND OR wsba_cursor%NOTFOUND IS NULL;
404
405 cep_standard.debug('>>l_bank_account_id = ' || l_bank_account_id);
406 cep_standard.debug('>>l_currency_code = ' || l_currency_code);
407 cep_standard.debug('>>l_statement_date = ' || to_char(l_statement_date,
408 'YYYY/MM/DD'));
409
410 tmp_balance := 0;
411
412 SELECT -sum(decode(AP.currency_code, l_currency_code,
413 AP.amount, AP.base_amount))
414 INTO tmp_balance
415 FROM CE_AP_FC_PAYMENTS_V AP,
416 CE_CP_WORKSHEET_LINES WSL
417 WHERE WSL.worksheet_header_id = l_ws_id
418 AND WSL.source_type = 'APP'
419 AND WSL.include_flag = 'Y'
420 AND WSL.cut_off_days is not null
421 AND AP.bank_account_id = l_bank_account_id
422 AND AP.status = 'NEGOTIABLE'
423 AND nvl(AP.actual_value_date, nvl(AP.anticipated_value_date,
424 nvl(AP.maturity_date, AP.payment_date)))
425 > l_statement_date - WSL.cut_off_days
426 AND nvl(AP.actual_value_date, nvl(AP.anticipated_value_date,
427 nvl(AP.maturity_date, AP.payment_date)))
428 <= l_statement_date
429 AND (WSL.payment_method is null OR
430 AP.payment_method = WSL.payment_method);
431
432 IF (tmp_balance is not null) THEN
433 BEGIN
434 INSERT INTO CE_CP_PRIORDAY_BALANCES
435 (worksheet_header_id,
436 as_of_date,
437 bank_account_id,
438 balance_date,
439 source_type,
440 balance,
441 last_update_date,
442 last_updated_by,
443 last_update_login,
444 creation_date,
445 created_by)
446 VALUES (l_ws_id,
447 CE_CP_PRIORDAY.G_as_of_date,
448 l_bank_account_id,
449 l_statement_date,
450 'APPOD',
451 tmp_balance,
452 trunc(sysdate),
453 -1,
454 -1,
455 trunc(sysdate),
456 -1);
457 END;
458 END IF;
459
460 tmp_balance := 0;
461
462 SELECT sum(decode(ARR.currency_code, l_currency_code,
463 ARR.amount, ARR.base_amount))
464 INTO tmp_balance
465 FROM CE_AR_FC_RECEIPTS_V ARR,
466 CE_CP_WORKSHEET_LINES WSL
467 WHERE WSL.worksheet_header_id = l_ws_id
468 AND WSL.source_type = 'ARR'
469 AND WSL.include_flag = 'Y'
470 AND WSL.cut_off_days is not null
471 AND ARR.bank_account_id = l_bank_account_id
472 AND ARR.status not in ('CLEARED', 'RISK_ELIMINATED')
473 AND ARR.cash_activity_date > l_statement_date - WSL.cut_off_days
474 AND ARR.cash_activity_date <= l_statement_date
475 AND (WSL.receipt_method_id is null OR
476 ARR.receipt_method_id = WSL.receipt_method_id);
477
478
479 IF (tmp_balance is not null) THEN
480 BEGIN
481 INSERT INTO CE_CP_PRIORDAY_BALANCES
482 (worksheet_header_id,
483 as_of_date,
484 bank_account_id,
485 balance_date,
486 source_type,
487 balance,
488 last_update_date,
489 last_updated_by,
490 last_update_login,
491 creation_date,
492 created_by)
493 VALUES (l_ws_id,
494 CE_CP_PRIORDAY.G_as_of_date,
495 l_bank_account_id,
496 l_statement_date,
497 'ARROD',
498 tmp_balance,
499 trunc(sysdate),
500 -1,
501 -1,
502 trunc(sysdate),
503 -1);
504 END;
505 END IF;
506
507 tmp_balance := 0;
508
509 SELECT sum(XTR.amount)
510 INTO tmp_balance
511 FROM CE_CP_WORKSHEET_LINES WSL,
512 CE_XTR_CASHFLOWS_V XTR
513 WHERE WSL.worksheet_header_id = l_ws_id
514 AND WSL.source_type = 'XTI'
515 AND WSL.include_flag = 'Y'
516 AND WSL.cut_off_days is not null
517 AND XTR.bank_account_id = l_bank_account_id
518 AND XTR.amount >= 0
519 AND XTR.reconciled_reference is null
520 AND XTR.trx_date > l_statement_date - WSL.cut_off_days
521 AND XTR.trx_date <= l_statement_date
522 AND (WSL.trx_type is null OR XTR.deal_type = WSL.trx_type)
523 AND (WSL.indicative_flag = 'Y' OR NOT (XTR.dda_deal_type = 'EXP'
524 AND XTR.dda_deal_subtype = 'INDIC'));
525
526 IF (tmp_balance is not null) THEN
527 BEGIN
528 INSERT INTO CE_CP_PRIORDAY_BALANCES
529 (worksheet_header_id,
530 as_of_date,
531 bank_account_id,
532 balance_date,
533 source_type,
534 balance,
535 last_update_date,
536 last_updated_by,
537 last_update_login,
538 creation_date,
539 created_by)
540 VALUES (l_ws_id,
541 CE_CP_PRIORDAY.G_as_of_date,
542 l_bank_account_id,
543 l_statement_date,
544 'XTIOD',
545 tmp_balance,
546 trunc(sysdate),
547 -1,
548 -1,
549 trunc(sysdate),
550 -1);
551 END;
552 END IF;
553
554 tmp_balance := 0;
555
556 SELECT sum(XTR.amount)
557 INTO tmp_balance
558 FROM CE_CP_WORKSHEET_LINES WSL,
559 CE_XTR_CASHFLOWS_V XTR
560 WHERE WSL.worksheet_header_id = l_ws_id
561 AND WSL.source_type = 'XTO'
562 AND WSL.include_flag = 'Y'
563 AND WSL.cut_off_days is not null
564 AND XTR.bank_account_id = l_bank_account_id
565 AND XTR.amount < 0
566 AND XTR.reconciled_reference is null
567 AND XTR.trx_date > l_statement_date - WSL.cut_off_days
568 AND XTR.trx_date <= l_statement_date
569 AND (WSL.trx_type is null OR XTR.deal_type = WSL.trx_type)
570 AND (WSL.indicative_flag = 'Y' OR NOT (XTR.dda_deal_type = 'EXP'
571 AND XTR.dda_deal_subtype = 'INDIC'));
572
573 IF (tmp_balance is not null) THEN
574 BEGIN
575 INSERT INTO CE_CP_PRIORDAY_BALANCES
576 (worksheet_header_id,
577 as_of_date,
578 bank_account_id,
579 balance_date,
580 source_type,
581 balance,
582 last_update_date,
583 last_updated_by,
584 last_update_login,
585 creation_date,
586 created_by)
587 VALUES (l_ws_id,
588 CE_CP_PRIORDAY.G_as_of_date,
589 l_bank_account_id,
590 l_statement_date,
591 'XTOOD',
592 tmp_balance,
593 trunc(sysdate),
594 -1,
595 -1,
596 trunc(sysdate),
597 -1);
598 END;
599 END IF;
600
601 /*Overdue CEI*/
602
603 tmp_balance := 0;
604
605 SELECT -sum(decode(CEI.currency_code, l_currency_code,
606 CEI.cashflow_amount, CEI.base_amount))
607 INTO tmp_balance
608 FROM CE_CE_CASHFLOWS_V CEI,
609 CE_CP_WORKSHEET_LINES WSL
610 WHERE WSL.worksheet_header_id = l_ws_id
611 AND WSL.source_type = 'CEI'
612 AND WSL.include_flag = 'Y'
613 AND WSL.cut_off_days is not null
614 AND CEI.cashflow_bank_account_id = l_bank_account_id
615 AND CEI.cashflow_status = 'CREATED'
619 <= l_statement_date
616 AND CEI.cash_activity_date
617 > l_statement_date - WSL.cut_off_days
618 AND CEI.cash_activity_date
620 AND (WSL.trxn_subtype_code_id is null OR
621 CEI.trxn_subtype_code_id = WSL.trxn_subtype_code_id)
622 AND CEI.cashflow_direction = 'RECEIPT'
623 AND CEI.source_trxn_type <> 'STMT';
624
625
626 IF (tmp_balance is not null) THEN
627 BEGIN
628 INSERT INTO CE_CP_PRIORDAY_BALANCES
629 (worksheet_header_id,
630 as_of_date,
631 bank_account_id,
632 balance_date,
633 source_type,
634 balance,
635 last_update_date,
636 last_updated_by,
637 last_update_login,
638 creation_date,
639 created_by)
640 VALUES (l_ws_id,
641 CE_CP_PRIORDAY.G_as_of_date,
642 l_bank_account_id,
643 l_statement_date,
644 'CEIOD',
645 tmp_balance,
646 trunc(sysdate),
647 -1,
648 -1,
649 trunc(sysdate),
650 -1);
651 END;
652 END IF;
653
654 tmp_balance := 0;
655
656
657 /*Overdue CEO*/
658
659 tmp_balance := 0;
660
661 SELECT -sum(decode(CEO.currency_code, l_currency_code,
662 CEO.cashflow_amount, CEO.base_amount))
663 INTO tmp_balance
664 FROM CE_CE_CASHFLOWS_V CEO,
665 CE_CP_WORKSHEET_LINES WSL
666 WHERE WSL.worksheet_header_id = l_ws_id
667 AND WSL.source_type = 'CEO'
668 AND WSL.include_flag = 'Y'
669 AND WSL.cut_off_days is not null
670 AND CEO.cashflow_bank_account_id = l_bank_account_id
671 AND CEO.cashflow_status = 'CREATED'
672 AND CEO.cash_activity_date
673 > l_statement_date - WSL.cut_off_days
674 AND CEO.cash_activity_date
675 <= l_statement_date
676 AND (WSL.trxn_subtype_code_id is null OR
677 CEO.trxn_subtype_code_id = WSL.trxn_subtype_code_id)
678 AND CEO.cashflow_direction = 'PAYMENT'
679 AND CEO.source_trxn_type <> 'STMT';
680
681 IF (tmp_balance is not null) THEN
682 BEGIN
683 INSERT INTO CE_CP_PRIORDAY_BALANCES
684 (worksheet_header_id,
685 as_of_date,
686 bank_account_id,
687 balance_date,
688 source_type,
689 balance,
690 last_update_date,
691 last_updated_by,
692 last_update_login,
693 creation_date,
694 created_by)
695 VALUES (l_ws_id,
696 CE_CP_PRIORDAY.G_as_of_date,
697 l_bank_account_id,
698 l_statement_date,
699 'CEOOD',
700 tmp_balance,
701 trunc(sysdate),
702 -1,
703 -1,
704 trunc(sysdate),
705 -1);
706 END;
707 END IF;
708
709 tmp_balance := 0;
710
711 END LOOP;
712 CLOSE wsba_cursor;
713
714 BEGIN
715 INSERT INTO CE_CP_PRIORDAY_BALANCES
716 (worksheet_header_id,
717 as_of_date,
718 bank_account_id,
719 balance_date,
720 source_type,
721 balance,
722 last_update_date,
723 last_updated_by,
724 last_update_login,
725 creation_date,
726 created_by)
727 VALUES (l_ws_id,
728 CE_CP_PRIORDAY.G_as_of_date,
729 -1,
730 trunc(CE_CP_PRIORDAY.G_as_of_date),
731 'HEAD',
732 0,
733 trunc(sysdate),
734 -1,
735 -1,
736 trunc(sysdate),
737 -1);
738 END;
739
740 COMMIT;
741
742 END LOOP;
743 CLOSE ws_cursor;
744
745 END calculate_summary;
746
747 PROCEDURE gen_prior_day(errbuf OUT NOCOPY VARCHAR2,
748 retcode OUT NOCOPY NUMBER,
749 p_worksheet_header_id NUMBER,
750 p_as_of_date VARCHAR2,
751 p_display_debug VARCHAR2,
752 p_debug_path VARCHAR2,
753 p_debug_file VARCHAR2) IS
754 BEGIN
755
756 cep_standard.init_security;
757 IF l_debug in ('Y', 'C') THEN
758 cep_standard.enable_debug(p_debug_path, p_debug_file);
759 cep_standard.debug('>>CE_CP_PRIORDAY.gen_prior_day ------- '||sysdate||
760 ' -------');
761 cep_standard.debug('p_worksheet_header_id: '|| p_worksheet_header_id);
762 cep_standard.debug('p_as_of_date: '|| p_as_of_date);
763 cep_standard.debug('p_display_debug: '||p_display_debug);
764 cep_standard.debug('p_debug_path: '||p_debug_path);
765 cep_standard.debug('p_debug_file: '||p_debug_file);
766 END IF;
767
768 set_parameters(p_worksheet_header_id, p_as_of_date, p_display_debug,
769 p_debug_path, p_debug_file);
770
771 calculate_summary;
772
773 IF l_debug in ('Y', 'C') THEN
774 cep_standard.debug('<<CE_CP_PRIORDAY.gen_prior_day -------');
775 END IF;
776
777 END gen_prior_day;
778
779 END CE_CP_PRIORDAY;