[Home] [Help]
PACKAGE BODY: APPS.CE_CEPURGE_XMLP_PKG
Source
1 PACKAGE BODY CE_CEPURGE_XMLP_PKG AS
2 /* $Header: CEPURGEB.pls 120.1 2008/01/07 21:22:03 abraghun noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 l_date_format VARCHAR2(10) := 'DD-MON-YY';
7 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
8 L_BANK_BRANCH_ID NUMBER;
9 COUNT_HEADERS NUMBER := 0;
10 COUNT_LINES NUMBER := 0;
11 COUNT_RECONS NUMBER := 0;
12 COUNT_ERRORS NUMBER := 0;
13 COUNT_INT_HEADERS NUMBER := 0;
14 COUNT_INT_LINES NUMBER := 0;
15 COUNT_INTRA_HEADERS NUMBER := 0;
16 COUNT_INTRA_LINES NUMBER := 0;
17 COUNT_INTRA_INT_HEADERS NUMBER := 0;
18 COUNT_INTRA_INT_LINES NUMBER := 0;
19 ERROR_FOUND BOOLEAN := FALSE;
20 X_HEADER_ID NUMBER := 0;
21 X_LINE_ID NUMBER := 0;
22 PURGE_LINES NUMBER := 0;
23 PURGE_HEADERS NUMBER := 0;
24 PURGE_RECONS NUMBER := 0;
25 PURGE_RECON_ERRORS NUMBER := 0;
26 L_DEF_ORG_ID NUMBER;
27 L_SP_ID NUMBER;
28 CURSOR C_PURGE_HEADERS IS
29 SELECT
30 CSH.STATEMENT_HEADER_ID
31 FROM
32 CE_STATEMENT_HEADERS CSH
33 WHERE CSH.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
34 ,BANK_ACCOUNT_ID)
35 AND CSH.BANK_ACCOUNT_ID IN (
36 SELECT
37 BANK_ACCOUNT_ID
38 FROM
39 CE_BANK_ACCTS_GT_V BA
40 WHERE BA.BANK_BRANCH_ID = NVL(P_BANK_BRANCH
41 ,BA.BANK_BRANCH_ID) )
42 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
43 ,TRUNC(CSH.STATEMENT_DATE))
44 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
45 ,TRUNC(CSH.STATEMENT_DATE))
46 AND not exists (
47 SELECT
48 1
49 FROM
50 CE_STATEMENT_HEADERS SH,
51 CE_STATEMENT_LINES SL,
52 CE_STATEMENT_RECONCILS_ALL SR
53 WHERE SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
54 AND SL.STATEMENT_LINE_ID = SR.STATEMENT_LINE_ID
55 AND SH.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
56 AND SH.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
57 ,BANK_ACCOUNT_ID)
58 AND SH.BANK_ACCOUNT_ID IN (
59 SELECT
60 BANK_ACCOUNT_ID
61 FROM
62 CE_BANK_ACCOUNTS BA
63 WHERE BA.BANK_BRANCH_ID = NVL(P_BANK_BRANCH
64 ,BA.BANK_BRANCH_ID) )
65 AND TRUNC(SH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
66 ,TRUNC(SH.STATEMENT_DATE))
67 AND TRUNC(SH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
68 ,TRUNC(SH.STATEMENT_DATE))
69 AND SR.CURRENT_RECORD_FLAG = 'Y'
70 AND SR.STATUS_FLAG = 'M'
71 AND ( SR.ORG_ID is not null
72 OR SR.LEGAL_ENTITY_ID is not null )
73 AND not exists (
74 SELECT
75 1
76 FROM
77 CE_SECURITY_PROFILES_GT LBG
78 WHERE LBG.ORGANIZATION_ID = SR.ORG_ID
79 OR SR.LEGAL_ENTITY_ID = LBG.ORGANIZATION_ID ) );
80 CURSOR C_PURGE_LINES IS
81 SELECT
82 CSL.STATEMENT_LINE_ID
83 FROM
84 CE_STATEMENT_LINES CSL
85 WHERE CSL.STATEMENT_HEADER_ID = X_HEADER_ID;
86 CURSOR C_PURGE_INTRA_HEADERS IS
87 SELECT
88 CSH.STATEMENT_HEADER_ID
89 FROM
90 CE_INTRA_STMT_HEADERS CSH
91 WHERE CSH.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
92 ,BANK_ACCOUNT_ID)
93 AND CSH.BANK_ACCOUNT_ID IN (
94 SELECT
95 BANK_ACCOUNT_ID
96 FROM
97 CE_BANK_ACCTS_GT_V BA
98 WHERE BA.BANK_BRANCH_ID = NVL(P_BANK_BRANCH
99 ,BA.BANK_BRANCH_ID) )
100 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
101 ,TRUNC(CSH.STATEMENT_DATE))
102 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
103 ,TRUNC(CSH.STATEMENT_DATE));
104 CURSOR C_PURGE_INTRA_LINES IS
105 SELECT
106 CSL.STATEMENT_LINE_ID
107 FROM
108 CE_INTRA_STMT_LINES CSL
109 WHERE CSL.STATEMENT_HEADER_ID = X_HEADER_ID;
110 BEGIN
111 P_STATEMENT_DATE_TO1 := P_STATEMENT_DATE_TO;
112
113 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
114 --P_STATEMENT_DATE_FROM_1 := to_char(to_date(P_STATEMENT_DATE_FROM),'DD-MON-YY');
115 P_STATEMENT_DATE_FROM_1 := to_char(P_STATEMENT_DATE_FROM,l_date_format);
116 INIT_SECURITY;
117
118 IF (P_STATEMENT_DATE_TO1 IS NULL) THEN
119 P_STATEMENT_DATE_TO1 := SYSDATE;
120 END IF;
121
122 --P_STATEMENT_DATE_TO_1 := to_char(to_date(P_STATEMENT_DATE_TO),'DD-MON-YY');
123 P_STATEMENT_DATE_TO_1 := to_char(P_STATEMENT_DATE_TO1,l_date_format);
124 IF (P_STATEMENT_TYPE IS NULL) THEN
125 P_STATEMENT_TYPE := 'BOTH';
126 END IF;
127 IF (P_BANK_ACCOUNT IS NOT NULL) THEN
128 SELECT
129 BB.BANK_NAME,
130 BB.BANK_BRANCH_NAME,
131 BA.BANK_ACCOUNT_NAME,
132 BA.BANK_ACCOUNT_NUM,
133 BA.CURRENCY_CODE
134 INTO
135 C_BANK_NAME
136 ,C_BANK_BRANCH_NAME
137 ,C_BANK_ACCOUNT_NAME
138 ,C_BANK_ACCOUNT_NUM
139 ,C_CURRENCY_CODE
140 FROM
141 CE_BANK_BRANCHES_V BB,
142 CE_BANK_ACCTS_GT_V BA
143 WHERE BA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT
144 AND BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID;
145 ELSIF (P_BANK_BRANCH IS NOT NULL) THEN
146 SELECT
147 BB.BANK_NAME,
148 BB.BANK_BRANCH_NAME
149 INTO
150 C_BANK_NAME
151 ,C_BANK_BRANCH_NAME
152 FROM
153 CE_BANK_BRANCHES_V BB
154 WHERE BB.BRANCH_PARTY_ID = P_BANK_BRANCH;
155 C_BANK_ACCOUNT_NAME := C_ALL_TRANSLATION;
156 C_BANK_ACCOUNT_NUM := C_ALL_TRANSLATION;
157 C_CURRENCY_CODE := C_ALL_TRANSLATION;
158 ELSE
159 C_BANK_NAME := C_ALL_TRANSLATION;
160 C_BANK_BRANCH_NAME := C_ALL_TRANSLATION;
161 C_BANK_ACCOUNT_NAME := C_ALL_TRANSLATION;
162 C_BANK_ACCOUNT_NUM := C_ALL_TRANSLATION;
163 C_CURRENCY_CODE := C_ALL_TRANSLATION;
164 END IF;
165 BEGIN
166 SELECT
167 L.MEANING
168 INTO
169 C_ALL_TRANSLATION
170 FROM
171 GL_SETS_OF_BOOKS GL,
172 CE_SYSTEM_PARAMETERS CB,
173 CE_LOOKUPS L
174 WHERE GL.SET_OF_BOOKS_ID = CB.SET_OF_BOOKS_ID
175 AND L.LOOKUP_TYPE = 'LITERAL'
176 AND L.LOOKUP_CODE = 'ALL'
177 AND ROWNUM = 1;
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 SET_NAME('CE'
181 ,'CE_PURGE_NO_SOB');
182 FND_MESSAGE.SET_NAME('CE', 'CE_PURGE_NO_SOB');
183 ERROR_FOUND := TRUE;
184 END;
185 IF (P_BANK_ACCOUNT IS NOT NULL AND P_BANK_BRANCH IS NOT NULL) THEN
186 SELECT
187 BANK_BRANCH_ID
188 INTO
189 L_BANK_BRANCH_ID
190 FROM
191 CE_BANK_ACCTS_GT_V
192 WHERE BANK_ACCOUNT_ID = P_BANK_ACCOUNT;
193 IF (L_BANK_BRANCH_ID <> P_BANK_BRANCH) THEN
194 SET_NAME('CE'
195 ,'CE_PURGE_BRANCH_ACCOUNT');
196 FND_MESSAGE.SET_NAME('CE', 'CE_PURGE_BRANCH_ACCOUNT');
197 ERROR_FOUND := TRUE;
198 END IF;
199 END IF;
200 IF (P_DEBUG_MODE = 'Y') THEN
201 NULL;
202 END IF;
203 IF (NOT ERROR_FOUND) THEN
204 IF (P_OPTION = 'BOTH' AND NVL(P_ACTION
205 ,'DELETE') = 'DELETE') THEN
206 IF (P_STATEMENT_TYPE in ('BOTH','PREVIOUS')) THEN
207 IF (P_OBJECTS in ('BOTH','STATEMENT')) THEN
208 SELECT
209 COUNT(*)
210 INTO
211 COUNT_HEADERS
212 FROM
213 CE_ARCH_HEADERS;
214 SELECT
215 COUNT(*)
216 INTO
217 COUNT_LINES
218 FROM
219 CE_ARCH_LINES;
220 SELECT
221 COUNT(*)
222 INTO
223 COUNT_RECONS
224 FROM
225 CE_ARCH_RECONCILIATIONS;
226 SELECT
227 COUNT(*)
228 INTO
229 COUNT_ERRORS
230 FROM
231 CE_ARCH_RECON_ERRORS;
232 END IF;
233 IF (P_OBJECTS in ('BOTH','INTERFACE')) THEN
234 SELECT
235 COUNT(*)
236 INTO
237 COUNT_INT_HEADERS
238 FROM
239 CE_ARCH_INTERFACE_HEADERS
240 WHERE NVL(INTRA_DAY_FLAG
241 ,'N') = 'N';
242 SELECT
243 COUNT(*)
244 INTO
245 COUNT_INT_LINES
246 FROM
247 CE_ARCH_INTERFACE_LINES
248 WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN (
249 SELECT
250 BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
251 FROM
252 CE_ARCH_INTERFACE_HEADERS
253 WHERE NVL(INTRA_DAY_FLAG
254 ,'N') = 'N' );
255 END IF;
256 END IF;
257 IF (P_STATEMENT_TYPE in ('BOTH','INTRADAY')) THEN
258 IF (P_OBJECTS in ('BOTH','STATEMENT')) THEN
259 SELECT
260 COUNT(*)
261 INTO
262 COUNT_INTRA_HEADERS
263 FROM
264 CE_ARCH_INTRA_HEADERS;
265 SELECT
266 COUNT(*)
267 INTO
268 COUNT_INTRA_LINES
269 FROM
270 CE_ARCH_INTRA_LINES;
271 END IF;
272 IF (P_OBJECTS in ('BOTH','INTERFACE')) THEN
273 SELECT
274 COUNT(*)
275 INTO
276 COUNT_INTRA_INT_HEADERS
277 FROM
278 CE_ARCH_INTERFACE_HEADERS
279 WHERE NVL(INTRA_DAY_FLAG
280 ,'N') = 'Y';
281 SELECT
282 COUNT(*)
283 INTO
284 COUNT_INTRA_INT_LINES
285 FROM
286 CE_ARCH_INTERFACE_LINES
287 WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN (
288 SELECT
289 BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
290 FROM
291 CE_ARCH_INTERFACE_HEADERS
292 WHERE NVL(INTRA_DAY_FLAG
293 ,'N') = 'Y' );
294 END IF;
295 END IF;
296 IF (COUNT_HEADERS > 0) THEN
297 DELETE FROM CE_ARCH_HEADERS;
298 END IF;
299 IF (COUNT_LINES > 0) THEN
300 DELETE FROM CE_ARCH_LINES;
301 END IF;
302 IF (COUNT_RECONS > 0) THEN
303 DELETE FROM CE_ARCH_RECONCILIATIONS_ALL
304 WHERE ORG_ID in (
305 SELECT
306 ORG_ID
307 FROM
308 CE_SECURITY_PROFILES_GT )
309 OR LEGAL_ENTITY_ID in (
310 SELECT
311 ORG_ID
312 FROM
313 CE_SECURITY_PROFILES_GT )
314 OR REFERENCE_TYPE in ( 'JE_LINE' , 'ROI_LINE' , 'STATEMENT' );
315 END IF;
316 IF (COUNT_ERRORS > 0) THEN
317 DELETE FROM CE_ARCH_RECON_ERRORS;
318 END IF;
319 IF (COUNT_INT_LINES > 0) THEN
320 DELETE FROM CE_ARCH_INTERFACE_LINES
321 WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN (
322 SELECT
323 BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
324 FROM
325 CE_ARCH_INTERFACE_HEADERS
326 WHERE NVL(INTRA_DAY_FLAG
327 ,'N') = 'N' );
328 END IF;
329 IF (COUNT_INT_HEADERS > 0) THEN
330 DELETE FROM CE_ARCH_INTERFACE_HEADERS
331 WHERE NVL(INTRA_DAY_FLAG
332 ,'N') = 'N';
333 END IF;
334 IF (COUNT_INTRA_HEADERS > 0) THEN
335 DELETE FROM CE_ARCH_INTRA_HEADERS;
336 END IF;
337 IF (COUNT_INTRA_LINES > 0) THEN
338 DELETE FROM CE_ARCH_INTRA_LINES;
339 END IF;
340 IF (COUNT_INTRA_INT_LINES > 0) THEN
341 DELETE FROM CE_ARCH_INTERFACE_LINES
342 WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN (
343 SELECT
344 BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
345 FROM
346 CE_ARCH_INTERFACE_HEADERS
347 WHERE NVL(INTRA_DAY_FLAG
348 ,'N') = 'Y' );
349 END IF;
350 IF (COUNT_INTRA_INT_HEADERS > 0) THEN
351 DELETE FROM CE_ARCH_INTERFACE_HEADERS
352 WHERE NVL(INTRA_DAY_FLAG
353 ,'N') = 'Y';
354 END IF;
355 END IF;
356 END IF;
357 IF (NOT ERROR_FOUND) THEN
358 IF (P_STATEMENT_TYPE in ('BOTH','PREVIOUS')) THEN
359 IF (P_OBJECTS in ('BOTH','STATEMENT')) THEN
360 IF (P_OPTION in ('BOTH')) THEN
361 IF (P_BANK_ACCOUNT IS NOT NULL) THEN
362 INSERT INTO CE_ARCH_HEADERS
363 (STATEMENT_COMPLETE_FLAG
364 ,DOC_SEQUENCE_ID
365 ,DOC_SEQUENCE_VALUE
366 ,STATEMENT_HEADER_ID
367 ,BANK_ACCOUNT_ID
368 ,STATEMENT_NUMBER
369 ,STATEMENT_DATE
370 ,AUTO_LOADED_FLAG
371 ,GL_DATE
372 ,CHECK_DIGITS
373 ,CONTROL_BEGIN_BALANCE
374 ,CONTROL_TOTAL_DR
375 ,CONTROL_TOTAL_CR
376 ,CONTROL_END_BALANCE
377 ,CASHFLOW_BALANCE
378 ,INT_CALC_BALANCE
379 ,ONE_DAY_FLOAT
380 ,TWO_DAY_FLOAT
381 ,CONTROL_DR_LINE_COUNT
382 ,CONTROL_CR_LINE_COUNT
383 ,CURRENCY_CODE
384 ,ATTRIBUTE_CATEGORY
385 ,ATTRIBUTE1
386 ,ATTRIBUTE2
387 ,ATTRIBUTE3
388 ,ATTRIBUTE4
389 ,ATTRIBUTE5
390 ,ATTRIBUTE6
391 ,ATTRIBUTE7
392 ,ATTRIBUTE8
393 ,ATTRIBUTE9
394 ,ATTRIBUTE10
395 ,ATTRIBUTE11
396 ,ATTRIBUTE12
397 ,ATTRIBUTE13
398 ,ATTRIBUTE14
399 ,ATTRIBUTE15
400 ,LAST_UPDATE_LOGIN
401 ,CREATED_BY
402 ,CREATION_DATE
403 ,LAST_UPDATED_BY
404 ,LAST_UPDATE_DATE)
405 SELECT
406 STATEMENT_COMPLETE_FLAG,
407 DOC_SEQUENCE_ID,
408 DOC_SEQUENCE_VALUE,
409 STATEMENT_HEADER_ID,
410 BANK_ACCOUNT_ID,
411 STATEMENT_NUMBER,
412 STATEMENT_DATE,
413 AUTO_LOADED_FLAG,
414 GL_DATE,
415 CHECK_DIGITS,
416 CONTROL_BEGIN_BALANCE,
417 CONTROL_TOTAL_DR,
418 CONTROL_TOTAL_CR,
419 CONTROL_END_BALANCE,
420 CASHFLOW_BALANCE,
421 INT_CALC_BALANCE,
422 ONE_DAY_FLOAT,
423 TWO_DAY_FLOAT,
424 CONTROL_DR_LINE_COUNT,
425 CONTROL_CR_LINE_COUNT,
426 CURRENCY_CODE,
427 ATTRIBUTE_CATEGORY,
428 ATTRIBUTE1,
429 ATTRIBUTE2,
430 ATTRIBUTE3,
431 ATTRIBUTE4,
432 ATTRIBUTE5,
433 ATTRIBUTE6,
434 ATTRIBUTE7,
435 ATTRIBUTE8,
436 ATTRIBUTE9,
437 ATTRIBUTE10,
438 ATTRIBUTE11,
439 ATTRIBUTE12,
440 ATTRIBUTE13,
441 ATTRIBUTE14,
442 ATTRIBUTE15,
443 LAST_UPDATE_LOGIN,
444 CREATED_BY,
445 CREATION_DATE,
446 LAST_UPDATED_BY,
447 LAST_UPDATE_DATE
448 FROM
449 CE_STATEMENT_HEADERS CSH
450 WHERE CSH.BANK_ACCOUNT_ID = P_BANK_ACCOUNT
451 AND CSH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
452 ,CSH.STATEMENT_DATE)
453 AND CSH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
454 ,CSH.STATEMENT_DATE)
455 AND not exists (
456 SELECT
457 1
458 FROM
459 CE_STATEMENT_HEADERS SH,
460 CE_STATEMENT_LINES SL,
461 CE_STATEMENT_RECONCILS_ALL SR
462 WHERE SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
463 AND SL.STATEMENT_LINE_ID = SR.STATEMENT_LINE_ID
464 AND SH.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
465 AND SH.BANK_ACCOUNT_ID = P_BANK_ACCOUNT
466 AND SH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
467 ,SH.STATEMENT_DATE)
468 AND SH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
469 ,SH.STATEMENT_DATE)
470 AND SR.CURRENT_RECORD_FLAG = 'Y'
471 AND SR.STATUS_FLAG = 'M'
472 AND ( SR.ORG_ID is not null
473 OR SR.LEGAL_ENTITY_ID is not null )
474 AND not exists (
475 SELECT
476 1
477 FROM
478 CE_SECURITY_PROFILES_GT LBG
479 WHERE LBG.ORGANIZATION_ID = SR.ORG_ID
480 OR SR.LEGAL_ENTITY_ID = LBG.ORGANIZATION_ID ) );
481 C_ARCHIVE_STAT_HEADERS := SQL%ROWCOUNT;
482 ELSIF (P_BANK_BRANCH IS NOT NULL) THEN
483 INSERT INTO CE_ARCH_HEADERS
484 (STATEMENT_COMPLETE_FLAG
485 ,DOC_SEQUENCE_ID
486 ,DOC_SEQUENCE_VALUE
487 ,STATEMENT_HEADER_ID
488 ,BANK_ACCOUNT_ID
489 ,STATEMENT_NUMBER
490 ,STATEMENT_DATE
491 ,AUTO_LOADED_FLAG
492 ,GL_DATE
493 ,CHECK_DIGITS
494 ,CONTROL_BEGIN_BALANCE
495 ,CONTROL_TOTAL_DR
496 ,CONTROL_TOTAL_CR
497 ,CONTROL_END_BALANCE
498 ,CASHFLOW_BALANCE
499 ,INT_CALC_BALANCE
500 ,ONE_DAY_FLOAT
501 ,TWO_DAY_FLOAT
502 ,CONTROL_DR_LINE_COUNT
503 ,CONTROL_CR_LINE_COUNT
504 ,CURRENCY_CODE
505 ,ATTRIBUTE_CATEGORY
506 ,ATTRIBUTE1
507 ,ATTRIBUTE2
508 ,ATTRIBUTE3
509 ,ATTRIBUTE4
510 ,ATTRIBUTE5
511 ,ATTRIBUTE6
512 ,ATTRIBUTE7
513 ,ATTRIBUTE8
514 ,ATTRIBUTE9
515 ,ATTRIBUTE10
516 ,ATTRIBUTE11
517 ,ATTRIBUTE12
518 ,ATTRIBUTE13
519 ,ATTRIBUTE14
520 ,ATTRIBUTE15
521 ,LAST_UPDATE_LOGIN
522 ,CREATED_BY
523 ,CREATION_DATE
524 ,LAST_UPDATED_BY
525 ,LAST_UPDATE_DATE)
526 SELECT
527 STATEMENT_COMPLETE_FLAG,
528 DOC_SEQUENCE_ID,
529 DOC_SEQUENCE_VALUE,
530 STATEMENT_HEADER_ID,
531 BANK_ACCOUNT_ID,
532 STATEMENT_NUMBER,
533 STATEMENT_DATE,
534 AUTO_LOADED_FLAG,
535 GL_DATE,
536 CHECK_DIGITS,
537 CONTROL_BEGIN_BALANCE,
538 CONTROL_TOTAL_DR,
539 CONTROL_TOTAL_CR,
540 CONTROL_END_BALANCE,
541 CASHFLOW_BALANCE,
542 INT_CALC_BALANCE,
543 ONE_DAY_FLOAT,
544 TWO_DAY_FLOAT,
545 CONTROL_DR_LINE_COUNT,
546 CONTROL_CR_LINE_COUNT,
547 CURRENCY_CODE,
548 ATTRIBUTE_CATEGORY,
549 ATTRIBUTE1,
550 ATTRIBUTE2,
551 ATTRIBUTE3,
552 ATTRIBUTE4,
553 ATTRIBUTE5,
554 ATTRIBUTE6,
555 ATTRIBUTE7,
556 ATTRIBUTE8,
557 ATTRIBUTE9,
558 ATTRIBUTE10,
559 ATTRIBUTE11,
560 ATTRIBUTE12,
561 ATTRIBUTE13,
562 ATTRIBUTE14,
563 ATTRIBUTE15,
564 LAST_UPDATE_LOGIN,
565 CREATED_BY,
566 CREATION_DATE,
567 LAST_UPDATED_BY,
568 LAST_UPDATE_DATE
569 FROM
570 CE_STATEMENT_HEADERS CSH
571 WHERE CSH.BANK_ACCOUNT_ID IN (
572 SELECT
573 ABA.BANK_ACCOUNT_ID
574 FROM
575 CE_BANK_ACCTS_GT_V ABA
576 WHERE ABA.BANK_BRANCH_ID = P_BANK_BRANCH )
577 AND CSH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
578 ,CSH.STATEMENT_DATE)
579 AND CSH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
580 ,CSH.STATEMENT_DATE)
581 AND not exists (
582 SELECT
583 1
584 FROM
585 CE_STATEMENT_HEADERS SH,
586 CE_STATEMENT_LINES SL,
587 CE_STATEMENT_RECONCILS_ALL SR
588 WHERE SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
589 AND SL.STATEMENT_LINE_ID = SR.STATEMENT_LINE_ID
590 AND SH.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
591 AND SH.BANK_ACCOUNT_ID IN (
592 SELECT
593 ABA.BANK_ACCOUNT_ID
594 FROM
595 CE_BANK_ACCOUNTS ABA
596 WHERE ABA.BANK_BRANCH_ID = P_BANK_BRANCH )
597 AND SH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
598 ,SH.STATEMENT_DATE)
599 AND SH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
600 ,SH.STATEMENT_DATE)
601 AND SR.CURRENT_RECORD_FLAG = 'Y'
602 AND SR.STATUS_FLAG = 'M'
603 AND ( SR.ORG_ID is not null
604 OR SR.LEGAL_ENTITY_ID is not null )
605 AND not exists (
606 SELECT
607 1
608 FROM
609 CE_SECURITY_PROFILES_GT LBG
610 WHERE LBG.ORGANIZATION_ID = SR.ORG_ID
611 OR SR.LEGAL_ENTITY_ID = LBG.ORGANIZATION_ID ) );
612 C_ARCHIVE_STAT_HEADERS := SQL%ROWCOUNT;
613 ELSE
614 INSERT INTO CE_ARCH_HEADERS
615 (STATEMENT_COMPLETE_FLAG
616 ,DOC_SEQUENCE_ID
617 ,DOC_SEQUENCE_VALUE
618 ,STATEMENT_HEADER_ID
619 ,BANK_ACCOUNT_ID
620 ,STATEMENT_NUMBER
621 ,STATEMENT_DATE
622 ,AUTO_LOADED_FLAG
623 ,GL_DATE
624 ,CHECK_DIGITS
625 ,CONTROL_BEGIN_BALANCE
626 ,CONTROL_TOTAL_DR
627 ,CONTROL_TOTAL_CR
628 ,CONTROL_END_BALANCE
629 ,CASHFLOW_BALANCE
630 ,INT_CALC_BALANCE
631 ,ONE_DAY_FLOAT
632 ,TWO_DAY_FLOAT
633 ,CONTROL_DR_LINE_COUNT
634 ,CONTROL_CR_LINE_COUNT
635 ,CURRENCY_CODE
636 ,ATTRIBUTE_CATEGORY
637 ,ATTRIBUTE1
638 ,ATTRIBUTE2
639 ,ATTRIBUTE3
640 ,ATTRIBUTE4
641 ,ATTRIBUTE5
642 ,ATTRIBUTE6
643 ,ATTRIBUTE7
644 ,ATTRIBUTE8
645 ,ATTRIBUTE9
646 ,ATTRIBUTE10
647 ,ATTRIBUTE11
648 ,ATTRIBUTE12
649 ,ATTRIBUTE13
650 ,ATTRIBUTE14
651 ,ATTRIBUTE15
652 ,LAST_UPDATE_LOGIN
653 ,CREATED_BY
654 ,CREATION_DATE
655 ,LAST_UPDATED_BY
656 ,LAST_UPDATE_DATE)
657 SELECT
658 STATEMENT_COMPLETE_FLAG,
659 DOC_SEQUENCE_ID,
660 DOC_SEQUENCE_VALUE,
661 STATEMENT_HEADER_ID,
662 BANK_ACCOUNT_ID,
663 STATEMENT_NUMBER,
664 STATEMENT_DATE,
665 AUTO_LOADED_FLAG,
666 GL_DATE,
667 CHECK_DIGITS,
668 CONTROL_BEGIN_BALANCE,
669 CONTROL_TOTAL_DR,
670 CONTROL_TOTAL_CR,
671 CONTROL_END_BALANCE,
672 CASHFLOW_BALANCE,
673 INT_CALC_BALANCE,
674 ONE_DAY_FLOAT,
675 TWO_DAY_FLOAT,
676 CONTROL_DR_LINE_COUNT,
677 CONTROL_CR_LINE_COUNT,
678 CURRENCY_CODE,
679 ATTRIBUTE_CATEGORY,
680 ATTRIBUTE1,
681 ATTRIBUTE2,
682 ATTRIBUTE3,
683 ATTRIBUTE4,
684 ATTRIBUTE5,
685 ATTRIBUTE6,
686 ATTRIBUTE7,
687 ATTRIBUTE8,
688 ATTRIBUTE9,
689 ATTRIBUTE10,
690 ATTRIBUTE11,
691 ATTRIBUTE12,
692 ATTRIBUTE13,
693 ATTRIBUTE14,
694 ATTRIBUTE15,
695 LAST_UPDATE_LOGIN,
696 CREATED_BY,
697 CREATION_DATE,
698 LAST_UPDATED_BY,
699 LAST_UPDATE_DATE
700 FROM
701 CE_STATEMENT_HEADERS CSH
702 WHERE CSH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
703 ,CSH.STATEMENT_DATE)
704 AND CSH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
705 ,CSH.STATEMENT_DATE)
706 AND not exists (
707 SELECT
708 1
709 FROM
710 CE_STATEMENT_HEADERS SH,
711 CE_STATEMENT_LINES SL,
712 CE_STATEMENT_RECONCILS_ALL SR
713 WHERE SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
714 AND SL.STATEMENT_LINE_ID = SR.STATEMENT_LINE_ID
715 AND SH.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
716 AND SH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
717 ,SH.STATEMENT_DATE)
718 AND SH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
719 ,SH.STATEMENT_DATE)
720 AND SR.CURRENT_RECORD_FLAG = 'Y'
721 AND SR.STATUS_FLAG = 'M'
722 AND ( SR.ORG_ID is not null
723 OR SR.LEGAL_ENTITY_ID is not null )
724 AND not exists (
725 SELECT
726 1
727 FROM
728 CE_SECURITY_PROFILES_GT LBG
729 WHERE LBG.ORGANIZATION_ID = SR.ORG_ID
730 OR SR.LEGAL_ENTITY_ID = LBG.ORGANIZATION_ID ) );
731 C_ARCHIVE_STAT_HEADERS := SQL%ROWCOUNT;
732 END IF;
733 INSERT INTO CE_ARCH_LINES
734 (STATEMENT_LINE_ID
735 ,STATEMENT_HEADER_ID
736 ,LINE_NUMBER
737 ,TRX_DATE
738 ,TRX_TYPE
739 ,AMOUNT
740 ,CHARGES_AMOUNT
741 ,STATUS
742 ,TRX_CODE_ID
743 ,EFFECTIVE_DATE
744 ,BANK_TRX_NUMBER
745 ,TRX_TEXT
746 ,CUSTOMER_TEXT
747 ,INVOICE_TEXT
748 ,CURRENCY_CODE
749 ,EXCHANGE_RATE_TYPE
750 ,EXCHANGE_RATE
751 ,EXCHANGE_RATE_DATE
752 ,ORIGINAL_AMOUNT
753 ,ATTRIBUTE_CATEGORY
754 ,ATTRIBUTE1
755 ,ATTRIBUTE2
756 ,ATTRIBUTE3
757 ,ATTRIBUTE4
758 ,ATTRIBUTE5
759 ,ATTRIBUTE6
760 ,ATTRIBUTE7
761 ,ATTRIBUTE8
762 ,ATTRIBUTE9
763 ,ATTRIBUTE10
764 ,ATTRIBUTE11
765 ,ATTRIBUTE12
766 ,ATTRIBUTE13
767 ,ATTRIBUTE14
768 ,ATTRIBUTE15
769 ,LAST_UPDATE_LOGIN
770 ,CREATED_BY
771 ,CREATION_DATE
772 ,LAST_UPDATED_BY
773 ,LAST_UPDATE_DATE
774 ,RECONCILE_TO_STATEMENT_FLAG)
775 SELECT
776 STATEMENT_LINE_ID,
777 STATEMENT_HEADER_ID,
778 LINE_NUMBER,
779 TRX_DATE,
780 TRX_TYPE,
781 AMOUNT,
782 CHARGES_AMOUNT,
783 STATUS,
784 TRX_CODE_ID,
785 EFFECTIVE_DATE,
786 BANK_TRX_NUMBER,
787 TRX_TEXT,
788 CUSTOMER_TEXT,
789 INVOICE_TEXT,
790 CURRENCY_CODE,
791 EXCHANGE_RATE_TYPE,
792 EXCHANGE_RATE,
793 EXCHANGE_RATE_DATE,
794 ORIGINAL_AMOUNT,
795 ATTRIBUTE_CATEGORY,
796 ATTRIBUTE1,
797 ATTRIBUTE2,
798 ATTRIBUTE3,
799 ATTRIBUTE4,
800 ATTRIBUTE5,
801 ATTRIBUTE6,
802 ATTRIBUTE7,
803 ATTRIBUTE8,
804 ATTRIBUTE9,
805 ATTRIBUTE10,
806 ATTRIBUTE11,
807 ATTRIBUTE12,
808 ATTRIBUTE13,
809 ATTRIBUTE14,
810 ATTRIBUTE15,
811 LAST_UPDATE_LOGIN,
812 CREATED_BY,
813 CREATION_DATE,
814 LAST_UPDATED_BY,
815 LAST_UPDATE_DATE,
816 RECONCILE_TO_STATEMENT_FLAG
817 FROM
818 CE_STATEMENT_LINES
819 WHERE STATEMENT_HEADER_ID IN (
820 SELECT
821 STATEMENT_HEADER_ID
822 FROM
823 CE_ARCH_HEADERS );
824 C_ARCHIVE_STAT_LINES := SQL%ROWCOUNT;
825 INSERT INTO CE_ARCH_RECONCILIATIONS_ALL
826 (STATEMENT_LINE_ID
827 ,REFERENCE_TYPE
828 ,REFERENCE_ID
829 ,JE_HEADER_ID
830 ,ORG_ID
831 ,LEGAL_ENTITY_ID
832 ,REFERENCE_STATUS
833 ,STATUS_FLAG
834 ,ACTION_FLAG
835 ,CURRENT_RECORD_FLAG
836 ,AUTO_RECONCILED_FLAG
837 ,CREATED_BY
838 ,CREATION_DATE
839 ,LAST_UPDATED_BY
840 ,LAST_UPDATE_DATE
841 ,REQUEST_ID
842 ,PROGRAM_APPLICATION_ID
843 ,PROGRAM_ID
844 ,PROGRAM_UPDATE_DATE
845 ,AMOUNT)
846 SELECT
847 STATEMENT_LINE_ID,
848 REFERENCE_TYPE,
849 REFERENCE_ID,
850 JE_HEADER_ID,
851 ORG_ID,
852 LEGAL_ENTITY_ID,
853 REFERENCE_STATUS,
854 STATUS_FLAG,
855 ACTION_FLAG,
856 CURRENT_RECORD_FLAG,
857 AUTO_RECONCILED_FLAG,
858 CREATED_BY,
859 CREATION_DATE,
860 LAST_UPDATED_BY,
861 LAST_UPDATE_DATE,
862 REQUEST_ID,
863 PROGRAM_APPLICATION_ID,
864 PROGRAM_ID,
865 PROGRAM_UPDATE_DATE,
866 AMOUNT
867 FROM
868 CE_STATEMENT_RECON_GT_V
869 WHERE STATEMENT_LINE_ID IN (
870 SELECT
871 STATEMENT_LINE_ID
872 FROM
873 CE_ARCH_LINES );
874 C_ARCHIVE_STAT_REC := SQL%ROWCOUNT;
875 INSERT INTO CE_ARCH_RECON_ERRORS
876 (STATEMENT_LINE_ID
877 ,MESSAGE_NAME
878 ,CREATION_DATE
879 ,CREATED_BY
880 ,APPLICATION_SHORT_NAME
881 ,STATEMENT_HEADER_ID)
882 SELECT
883 STATEMENT_LINE_ID,
884 MESSAGE_NAME,
885 CREATION_DATE,
886 CREATED_BY,
887 APPLICATION_SHORT_NAME,
888 STATEMENT_HEADER_ID
889 FROM
890 CE_RECONCILIATION_ERRORS
891 WHERE STATEMENT_LINE_ID IN (
892 SELECT
893 STATEMENT_LINE_ID
894 FROM
895 CE_ARCH_LINES );
896 C_ARCHIVE_STAT_ERRORS := SQL%ROWCOUNT;
897 END IF;
898 OPEN C_PURGE_HEADERS;
899 FETCH C_PURGE_HEADERS
900 INTO
901 X_HEADER_ID;
902 LOOP
903 EXIT WHEN C_PURGE_HEADERS%NOTFOUND;
904 DELETE FROM CE_STATEMENT_HEADERS
905 WHERE STATEMENT_HEADER_ID = X_HEADER_ID;
906 PURGE_HEADERS := PURGE_HEADERS + SQL%ROWCOUNT;
907 OPEN C_PURGE_LINES;
908 FETCH C_PURGE_LINES
909 INTO
910 X_LINE_ID;
911 LOOP
912 EXIT WHEN C_PURGE_LINES%NOTFOUND;
913 DELETE FROM CE_STATEMENT_LINES
914 WHERE STATEMENT_HEADER_ID = X_HEADER_ID;
915 PURGE_LINES := PURGE_LINES + SQL%ROWCOUNT;
916 DELETE FROM CE_STATEMENT_RECONCILS_ALL
917 WHERE STATEMENT_LINE_ID = X_LINE_ID
918 AND ( ORG_ID in (
919 SELECT
920 ORGANIZATION_ID
921 FROM
922 CE_SECURITY_PROFILES_GT )
923 OR LEGAL_ENTITY_ID in (
924 SELECT
925 ORGANIZATION_ID
926 FROM
927 CE_SECURITY_PROFILES_GT )
928 OR REFERENCE_TYPE in ( 'JE_LINE' , 'ROI_LINE' , 'STATEMENT' ) );
929 PURGE_RECONS := PURGE_RECONS + SQL%ROWCOUNT;
930 DELETE FROM CE_RECONCILIATION_ERRORS
931 WHERE STATEMENT_LINE_ID = X_LINE_ID;
932 PURGE_RECON_ERRORS := PURGE_RECON_ERRORS + SQL%ROWCOUNT;
933 FETCH C_PURGE_LINES
934 INTO
935 X_LINE_ID;
936 END LOOP;
937 CLOSE C_PURGE_LINES;
938 FETCH C_PURGE_HEADERS
939 INTO
940 X_HEADER_ID;
941 END LOOP;
942 CLOSE C_PURGE_HEADERS;
943 C_PURGE_STAT_LINES := PURGE_LINES;
944 C_PURGE_STAT_HEADERS := PURGE_HEADERS;
945 C_PURGE_STAT_REC := PURGE_RECONS;
946 C_PURGE_STAT_ERRORS := PURGE_RECON_ERRORS;
947 END IF;
948 IF (P_OBJECTS in ('BOTH','INTERFACE')) THEN
949 IF (P_OPTION = 'BOTH') THEN
950 INSERT INTO CE_ARCH_INTERFACE_HEADERS
951 (STATEMENT_NUMBER
952 ,BANK_ACCOUNT_NUM
953 ,STATEMENT_DATE
954 ,BANK_NAME
955 ,BANK_BRANCH_NAME
956 ,CHECK_DIGITS
957 ,CONTROL_BEGIN_BALANCE
958 ,CONTROL_TOTAL_DR
959 ,CONTROL_TOTAL_CR
960 ,CONTROL_END_BALANCE
961 ,CASHFLOW_BALANCE
962 ,INT_CALC_BALANCE
963 ,ONE_DAY_FLOAT
964 ,TWO_DAY_FLOAT
965 ,CONTROL_DR_LINE_COUNT
966 ,CONTROL_CR_LINE_COUNT
967 ,CONTROL_LINE_COUNT
968 ,RECORD_STATUS_FLAG
969 ,CURRENCY_CODE
970 ,ATTRIBUTE_CATEGORY
971 ,ATTRIBUTE1
972 ,ATTRIBUTE2
973 ,ATTRIBUTE3
974 ,ATTRIBUTE4
975 ,ATTRIBUTE5
976 ,ATTRIBUTE6
977 ,ATTRIBUTE7
978 ,ATTRIBUTE8
979 ,ATTRIBUTE9
980 ,ATTRIBUTE10
981 ,ATTRIBUTE11
982 ,ATTRIBUTE12
983 ,ATTRIBUTE13
984 ,ATTRIBUTE14
985 ,ATTRIBUTE15
986 ,CREATED_BY
987 ,CREATION_DATE
988 ,LAST_UPDATED_BY
989 ,LAST_UPDATE_DATE)
990 SELECT
991 STATEMENT_NUMBER,
992 BANK_ACCOUNT_NUM,
993 STATEMENT_DATE,
994 BANK_NAME,
995 BANK_BRANCH_NAME,
996 CHECK_DIGITS,
997 CONTROL_BEGIN_BALANCE,
998 CONTROL_TOTAL_DR,
999 CONTROL_TOTAL_CR,
1000 CONTROL_END_BALANCE,
1001 CASHFLOW_BALANCE,
1002 INT_CALC_BALANCE,
1003 ONE_DAY_FLOAT,
1004 TWO_DAY_FLOAT,
1005 CONTROL_DR_LINE_COUNT,
1006 CONTROL_CR_LINE_COUNT,
1007 CONTROL_LINE_COUNT,
1008 RECORD_STATUS_FLAG,
1009 CURRENCY_CODE,
1010 ATTRIBUTE_CATEGORY,
1011 ATTRIBUTE1,
1012 ATTRIBUTE2,
1013 ATTRIBUTE3,
1014 ATTRIBUTE4,
1015 ATTRIBUTE5,
1016 ATTRIBUTE6,
1017 ATTRIBUTE7,
1018 ATTRIBUTE8,
1019 ATTRIBUTE9,
1020 ATTRIBUTE10,
1021 ATTRIBUTE11,
1022 ATTRIBUTE12,
1023 ATTRIBUTE13,
1024 ATTRIBUTE14,
1025 ATTRIBUTE15,
1026 CREATED_BY,
1027 CREATION_DATE,
1028 LAST_UPDATED_BY,
1029 LAST_UPDATE_DATE
1030 FROM
1031 CE_STATEMENT_HEADERS_INT CSH
1032 WHERE NVL(CSH.BANK_BRANCH_NAME
1033 ,C_BANK_BRANCH_NAME) IN (
1034 SELECT
1035 BB.BANK_BRANCH_NAME
1036 FROM
1037 CE_BANK_BRANCHES_V BB,
1038 CE_BANK_ACCTS_GT_V BA
1039 WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
1040 AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1041 ,BA.BANK_ACCOUNT_ID)
1042 AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
1043 ,BB.BRANCH_PARTY_ID) )
1044 AND CSH.BANK_ACCOUNT_NUM IN (
1045 SELECT
1046 BANK_ACCOUNT_NUM
1047 FROM
1048 CE_BANK_ACCTS_GT_V
1049 WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1050 ,BANK_ACCOUNT_ID)
1051 AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
1052 ,BANK_BRANCH_ID) )
1053 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1054 ,TRUNC(CSH.STATEMENT_DATE))
1055 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1056 ,TRUNC(CSH.STATEMENT_DATE))
1057 AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
1058 ,'C'
1059 ,'C'
1060 ,'E'
1061 ,'E'
1062 ,'N'
1063 ,'N'
1064 ,'T'
1065 ,'T'
1066 ,'A'
1067 ,RECORD_STATUS_FLAG
1068 ,'T') )
1069 AND NVL(CSH.INTRA_DAY_FLAG
1070 ,'N') = 'N';
1071 C_ARCHIVE_INF_HEADERS := SQL%ROWCOUNT;
1072 INSERT INTO CE_ARCH_INTERFACE_LINES
1073 (EXCHANGE_RATE_DATE
1074 ,EXCHANGE_RATE
1075 ,BANK_TRX_NUMBER
1076 ,CUSTOMER_TEXT
1077 ,CREATED_BY
1078 ,CREATION_DATE
1079 ,LAST_UPDATED_BY
1080 ,LAST_UPDATE_DATE
1081 ,ATTRIBUTE_CATEGORY
1082 ,ATTRIBUTE1
1083 ,ATTRIBUTE2
1084 ,ATTRIBUTE3
1085 ,ATTRIBUTE4
1086 ,ATTRIBUTE5
1087 ,ATTRIBUTE6
1088 ,ATTRIBUTE7
1089 ,ATTRIBUTE8
1090 ,ATTRIBUTE9
1091 ,ATTRIBUTE10
1092 ,ATTRIBUTE11
1093 ,ATTRIBUTE12
1094 ,ATTRIBUTE13
1095 ,ATTRIBUTE14
1096 ,ATTRIBUTE15
1097 ,ORIGINAL_AMOUNT
1098 ,BANK_ACCOUNT_NUM
1099 ,STATEMENT_NUMBER
1100 ,LINE_NUMBER
1101 ,TRX_DATE
1102 ,TRX_CODE
1103 ,EFFECTIVE_DATE
1104 ,TRX_TEXT
1105 ,INVOICE_TEXT
1106 ,AMOUNT
1107 ,CHARGES_AMOUNT
1108 ,CURRENCY_CODE
1109 ,USER_EXCHANGE_RATE_TYPE)
1110 SELECT
1111 EXCHANGE_RATE_DATE,
1112 EXCHANGE_RATE,
1113 BANK_TRX_NUMBER,
1114 CUSTOMER_TEXT,
1115 CREATED_BY,
1116 CREATION_DATE,
1117 LAST_UPDATED_BY,
1118 LAST_UPDATE_DATE,
1119 ATTRIBUTE_CATEGORY,
1120 ATTRIBUTE1,
1121 ATTRIBUTE2,
1122 ATTRIBUTE3,
1123 ATTRIBUTE4,
1124 ATTRIBUTE5,
1125 ATTRIBUTE6,
1126 ATTRIBUTE7,
1127 ATTRIBUTE8,
1128 ATTRIBUTE9,
1129 ATTRIBUTE10,
1130 ATTRIBUTE11,
1131 ATTRIBUTE12,
1132 ATTRIBUTE13,
1133 ATTRIBUTE14,
1134 ATTRIBUTE15,
1135 ORIGINAL_AMOUNT,
1136 BANK_ACCOUNT_NUM,
1137 STATEMENT_NUMBER,
1138 LINE_NUMBER,
1139 TRX_DATE,
1140 TRX_CODE,
1141 EFFECTIVE_DATE,
1142 TRX_TEXT,
1143 INVOICE_TEXT,
1144 AMOUNT,
1145 CHARGES_AMOUNT,
1146 CURRENCY_CODE,
1147 USER_EXCHANGE_RATE_TYPE
1148 FROM
1149 CE_STATEMENT_LINES_INTERFACE CSL
1150 WHERE CSL.BANK_ACCOUNT_NUM IN (
1151 SELECT
1152 BANK_ACCOUNT_NUM
1153 FROM
1154 CE_ARCH_INTERFACE_HEADERS )
1155 AND STATEMENT_NUMBER IN (
1156 SELECT
1157 STATEMENT_NUMBER
1158 FROM
1159 CE_ARCH_INTERFACE_HEADERS
1160 WHERE CSL.BANK_ACCOUNT_NUM = BANK_ACCOUNT_NUM
1161 AND NVL(INTRA_DAY_FLAG
1162 ,'N') = 'N' );
1163 C_ARCHIVE_INF_LINES := SQL%ROWCOUNT;
1164 END IF;
1165 DELETE FROM CE_STATEMENT_LINES_INTERFACE CSL
1166 WHERE CSL.STATEMENT_NUMBER || '-' || CSL.BANK_ACCOUNT_NUM IN (
1167 SELECT
1168 CSH.STATEMENT_NUMBER || '-' || CSH.BANK_ACCOUNT_NUM
1169 FROM
1170 CE_STATEMENT_HEADERS_INT CSH
1171 WHERE NVL(CSH.BANK_BRANCH_NAME
1172 ,C_BANK_BRANCH_NAME) IN (
1173 SELECT
1174 BB.BANK_BRANCH_NAME
1175 FROM
1176 CE_BANK_BRANCHES_V BB,
1177 CE_BANK_ACCTS_GT_V BA
1178 WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
1179 AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1180 ,BA.BANK_ACCOUNT_ID)
1181 AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
1182 ,BB.BRANCH_PARTY_ID) )
1183 AND CSH.BANK_ACCOUNT_NUM IN (
1184 SELECT
1185 BANK_ACCOUNT_NUM
1186 FROM
1187 CE_BANK_ACCTS_GT_V
1188 WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1189 ,BANK_ACCOUNT_ID)
1190 AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
1191 ,BANK_BRANCH_ID) )
1192 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1193 ,TRUNC(CSH.STATEMENT_DATE))
1194 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1195 ,TRUNC(CSH.STATEMENT_DATE))
1196 AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
1197 ,'C'
1198 ,'C'
1199 ,'E'
1200 ,'E'
1201 ,'N'
1202 ,'N'
1203 ,'T'
1204 ,'T'
1205 ,'A'
1206 ,RECORD_STATUS_FLAG
1207 ,'T') )
1208 AND NVL(CSH.INTRA_DAY_FLAG
1209 ,'N') = 'N' );
1210 C_PURGE_INF_LINES := SQL%ROWCOUNT;
1211 DELETE FROM CE_STATEMENT_HEADERS_INT CSH
1212 WHERE NVL(CSH.BANK_BRANCH_NAME
1213 ,C_BANK_BRANCH_NAME) IN (
1214 SELECT
1215 BB.BANK_BRANCH_NAME
1216 FROM
1217 CE_BANK_BRANCHES_V BB,
1218 CE_BANK_ACCTS_GT_V BA
1219 WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
1220 AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1221 ,BA.BANK_ACCOUNT_ID)
1222 AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
1223 ,BB.BRANCH_PARTY_ID) )
1224 AND CSH.BANK_ACCOUNT_NUM IN (
1225 SELECT
1226 BANK_ACCOUNT_NUM
1227 FROM
1228 CE_BANK_ACCTS_GT_V
1229 WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1230 ,BANK_ACCOUNT_ID)
1231 AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
1232 ,BANK_BRANCH_ID) )
1233 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1234 ,TRUNC(CSH.STATEMENT_DATE))
1235 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1236 ,TRUNC(CSH.STATEMENT_DATE))
1237 AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
1238 ,'C'
1239 ,'C'
1240 ,'E'
1241 ,'E'
1242 ,'N'
1243 ,'N'
1244 ,'T'
1245 ,'T'
1246 ,'A'
1247 ,RECORD_STATUS_FLAG
1248 ,'T') )
1249 AND NVL(CSH.INTRA_DAY_FLAG
1250 ,'N') = 'N';
1251 C_PURGE_INF_HEADERS := SQL%ROWCOUNT;
1252 END IF;
1253 END IF;
1254 IF (P_STATEMENT_TYPE in ('BOTH','INTRADAY')) THEN
1255 IF (P_OBJECTS in ('BOTH','STATEMENT')) THEN
1256 IF (P_OPTION in ('BOTH')) THEN
1257 IF (P_BANK_ACCOUNT IS NOT NULL) THEN
1258 INSERT INTO CE_ARCH_INTRA_HEADERS
1259 (STATEMENT_COMPLETE_FLAG
1260 ,DOC_SEQUENCE_ID
1261 ,DOC_SEQUENCE_VALUE
1262 ,STATEMENT_HEADER_ID
1263 ,BANK_ACCOUNT_ID
1264 ,STATEMENT_NUMBER
1265 ,STATEMENT_DATE
1266 ,AUTO_LOADED_FLAG
1267 ,GL_DATE
1268 ,CHECK_DIGITS
1269 ,CONTROL_BEGIN_BALANCE
1270 ,CONTROL_TOTAL_DR
1271 ,CONTROL_TOTAL_CR
1272 ,CONTROL_END_BALANCE
1273 ,CASHFLOW_BALANCE
1274 ,INT_CALC_BALANCE
1275 ,ONE_DAY_FLOAT
1276 ,TWO_DAY_FLOAT
1277 ,CONTROL_DR_LINE_COUNT
1278 ,CONTROL_CR_LINE_COUNT
1279 ,CURRENCY_CODE
1280 ,ATTRIBUTE_CATEGORY
1281 ,ATTRIBUTE1
1282 ,ATTRIBUTE2
1283 ,ATTRIBUTE3
1284 ,ATTRIBUTE4
1285 ,ATTRIBUTE5
1286 ,ATTRIBUTE6
1287 ,ATTRIBUTE7
1288 ,ATTRIBUTE8
1289 ,ATTRIBUTE9
1290 ,ATTRIBUTE10
1291 ,ATTRIBUTE11
1292 ,ATTRIBUTE12
1293 ,ATTRIBUTE13
1294 ,ATTRIBUTE14
1295 ,ATTRIBUTE15
1296 ,LAST_UPDATE_LOGIN
1297 ,CREATED_BY
1298 ,CREATION_DATE
1299 ,LAST_UPDATED_BY
1300 ,LAST_UPDATE_DATE)
1301 SELECT
1302 STATEMENT_COMPLETE_FLAG,
1303 DOC_SEQUENCE_ID,
1304 DOC_SEQUENCE_VALUE,
1305 STATEMENT_HEADER_ID,
1306 BANK_ACCOUNT_ID,
1307 STATEMENT_NUMBER,
1308 STATEMENT_DATE,
1309 AUTO_LOADED_FLAG,
1310 GL_DATE,
1311 CHECK_DIGITS,
1312 CONTROL_BEGIN_BALANCE,
1313 CONTROL_TOTAL_DR,
1314 CONTROL_TOTAL_CR,
1315 CONTROL_END_BALANCE,
1316 CASHFLOW_BALANCE,
1317 INT_CALC_BALANCE,
1318 ONE_DAY_FLOAT,
1319 TWO_DAY_FLOAT,
1320 CONTROL_DR_LINE_COUNT,
1321 CONTROL_CR_LINE_COUNT,
1322 CURRENCY_CODE,
1323 ATTRIBUTE_CATEGORY,
1324 ATTRIBUTE1,
1325 ATTRIBUTE2,
1326 ATTRIBUTE3,
1327 ATTRIBUTE4,
1328 ATTRIBUTE5,
1329 ATTRIBUTE6,
1330 ATTRIBUTE7,
1331 ATTRIBUTE8,
1332 ATTRIBUTE9,
1333 ATTRIBUTE10,
1334 ATTRIBUTE11,
1335 ATTRIBUTE12,
1336 ATTRIBUTE13,
1337 ATTRIBUTE14,
1338 ATTRIBUTE15,
1339 LAST_UPDATE_LOGIN,
1340 CREATED_BY,
1341 CREATION_DATE,
1342 LAST_UPDATED_BY,
1343 LAST_UPDATE_DATE
1344 FROM
1345 CE_INTRA_STMT_HEADERS CSH
1346 WHERE CSH.BANK_ACCOUNT_ID = P_BANK_ACCOUNT
1347 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1348 ,TRUNC(CSH.STATEMENT_DATE))
1349 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1350 ,TRUNC(CSH.STATEMENT_DATE));
1351 C_ARCHIVE_STAT_HEADERS2 := SQL%ROWCOUNT;
1352 ELSIF (P_BANK_BRANCH IS NOT NULL) THEN
1353 INSERT INTO CE_ARCH_INTRA_HEADERS
1354 (STATEMENT_COMPLETE_FLAG
1355 ,DOC_SEQUENCE_ID
1356 ,DOC_SEQUENCE_VALUE
1357 ,STATEMENT_HEADER_ID
1358 ,BANK_ACCOUNT_ID
1359 ,STATEMENT_NUMBER
1360 ,STATEMENT_DATE
1361 ,AUTO_LOADED_FLAG
1362 ,GL_DATE
1363 ,CHECK_DIGITS
1364 ,CONTROL_BEGIN_BALANCE
1365 ,CONTROL_TOTAL_DR
1366 ,CONTROL_TOTAL_CR
1367 ,CONTROL_END_BALANCE
1368 ,CASHFLOW_BALANCE
1369 ,INT_CALC_BALANCE
1370 ,ONE_DAY_FLOAT
1371 ,TWO_DAY_FLOAT
1372 ,CONTROL_DR_LINE_COUNT
1373 ,CONTROL_CR_LINE_COUNT
1374 ,CURRENCY_CODE
1375 ,ATTRIBUTE_CATEGORY
1376 ,ATTRIBUTE1
1377 ,ATTRIBUTE2
1378 ,ATTRIBUTE3
1379 ,ATTRIBUTE4
1380 ,ATTRIBUTE5
1381 ,ATTRIBUTE6
1382 ,ATTRIBUTE7
1383 ,ATTRIBUTE8
1384 ,ATTRIBUTE9
1385 ,ATTRIBUTE10
1386 ,ATTRIBUTE11
1387 ,ATTRIBUTE12
1388 ,ATTRIBUTE13
1389 ,ATTRIBUTE14
1390 ,ATTRIBUTE15
1391 ,LAST_UPDATE_LOGIN
1392 ,CREATED_BY
1393 ,CREATION_DATE
1394 ,LAST_UPDATED_BY
1395 ,LAST_UPDATE_DATE)
1396 SELECT
1397 STATEMENT_COMPLETE_FLAG,
1398 DOC_SEQUENCE_ID,
1399 DOC_SEQUENCE_VALUE,
1400 STATEMENT_HEADER_ID,
1401 BANK_ACCOUNT_ID,
1402 STATEMENT_NUMBER,
1403 STATEMENT_DATE,
1404 AUTO_LOADED_FLAG,
1405 GL_DATE,
1406 CHECK_DIGITS,
1407 CONTROL_BEGIN_BALANCE,
1408 CONTROL_TOTAL_DR,
1409 CONTROL_TOTAL_CR,
1410 CONTROL_END_BALANCE,
1411 CASHFLOW_BALANCE,
1412 INT_CALC_BALANCE,
1413 ONE_DAY_FLOAT,
1414 TWO_DAY_FLOAT,
1415 CONTROL_DR_LINE_COUNT,
1416 CONTROL_CR_LINE_COUNT,
1417 CURRENCY_CODE,
1418 ATTRIBUTE_CATEGORY,
1419 ATTRIBUTE1,
1420 ATTRIBUTE2,
1421 ATTRIBUTE3,
1422 ATTRIBUTE4,
1423 ATTRIBUTE5,
1424 ATTRIBUTE6,
1425 ATTRIBUTE7,
1426 ATTRIBUTE8,
1427 ATTRIBUTE9,
1428 ATTRIBUTE10,
1429 ATTRIBUTE11,
1430 ATTRIBUTE12,
1431 ATTRIBUTE13,
1432 ATTRIBUTE14,
1433 ATTRIBUTE15,
1434 LAST_UPDATE_LOGIN,
1435 CREATED_BY,
1436 CREATION_DATE,
1437 LAST_UPDATED_BY,
1438 LAST_UPDATE_DATE
1439 FROM
1440 CE_INTRA_STMT_HEADERS CSH
1441 WHERE CSH.BANK_ACCOUNT_ID IN (
1442 SELECT
1443 ABA.BANK_ACCOUNT_ID
1444 FROM
1445 CE_BANK_ACCOUNTS ABA
1446 WHERE ABA.BANK_BRANCH_ID = P_BANK_BRANCH )
1447 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1448 ,TRUNC(CSH.STATEMENT_DATE))
1449 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1450 ,TRUNC(CSH.STATEMENT_DATE));
1451 C_ARCHIVE_STAT_HEADERS2 := SQL%ROWCOUNT;
1452 ELSE
1453 INSERT INTO CE_ARCH_INTRA_HEADERS
1454 (STATEMENT_COMPLETE_FLAG
1455 ,DOC_SEQUENCE_ID
1456 ,DOC_SEQUENCE_VALUE
1457 ,STATEMENT_HEADER_ID
1458 ,BANK_ACCOUNT_ID
1459 ,STATEMENT_NUMBER
1460 ,STATEMENT_DATE
1461 ,AUTO_LOADED_FLAG
1462 ,GL_DATE
1463 ,CHECK_DIGITS
1464 ,CONTROL_BEGIN_BALANCE
1465 ,CONTROL_TOTAL_DR
1466 ,CONTROL_TOTAL_CR
1467 ,CONTROL_END_BALANCE
1468 ,CASHFLOW_BALANCE
1469 ,INT_CALC_BALANCE
1470 ,ONE_DAY_FLOAT
1471 ,TWO_DAY_FLOAT
1472 ,CONTROL_DR_LINE_COUNT
1473 ,CONTROL_CR_LINE_COUNT
1474 ,CURRENCY_CODE
1475 ,ATTRIBUTE_CATEGORY
1476 ,ATTRIBUTE1
1477 ,ATTRIBUTE2
1478 ,ATTRIBUTE3
1479 ,ATTRIBUTE4
1480 ,ATTRIBUTE5
1481 ,ATTRIBUTE6
1482 ,ATTRIBUTE7
1483 ,ATTRIBUTE8
1484 ,ATTRIBUTE9
1485 ,ATTRIBUTE10
1486 ,ATTRIBUTE11
1487 ,ATTRIBUTE12
1488 ,ATTRIBUTE13
1489 ,ATTRIBUTE14
1490 ,ATTRIBUTE15
1491 ,LAST_UPDATE_LOGIN
1492 ,CREATED_BY
1493 ,CREATION_DATE
1494 ,LAST_UPDATED_BY
1495 ,LAST_UPDATE_DATE)
1496 SELECT
1497 STATEMENT_COMPLETE_FLAG,
1498 DOC_SEQUENCE_ID,
1499 DOC_SEQUENCE_VALUE,
1500 STATEMENT_HEADER_ID,
1501 BANK_ACCOUNT_ID,
1502 STATEMENT_NUMBER,
1503 STATEMENT_DATE,
1504 AUTO_LOADED_FLAG,
1505 GL_DATE,
1506 CHECK_DIGITS,
1507 CONTROL_BEGIN_BALANCE,
1508 CONTROL_TOTAL_DR,
1509 CONTROL_TOTAL_CR,
1510 CONTROL_END_BALANCE,
1511 CASHFLOW_BALANCE,
1512 INT_CALC_BALANCE,
1513 ONE_DAY_FLOAT,
1514 TWO_DAY_FLOAT,
1515 CONTROL_DR_LINE_COUNT,
1516 CONTROL_CR_LINE_COUNT,
1517 CURRENCY_CODE,
1518 ATTRIBUTE_CATEGORY,
1519 ATTRIBUTE1,
1520 ATTRIBUTE2,
1521 ATTRIBUTE3,
1522 ATTRIBUTE4,
1523 ATTRIBUTE5,
1524 ATTRIBUTE6,
1525 ATTRIBUTE7,
1526 ATTRIBUTE8,
1527 ATTRIBUTE9,
1528 ATTRIBUTE10,
1529 ATTRIBUTE11,
1530 ATTRIBUTE12,
1531 ATTRIBUTE13,
1532 ATTRIBUTE14,
1533 ATTRIBUTE15,
1534 LAST_UPDATE_LOGIN,
1535 CREATED_BY,
1536 CREATION_DATE,
1537 LAST_UPDATED_BY,
1538 LAST_UPDATE_DATE
1539 FROM
1540 CE_INTRA_STMT_HEADERS CSH
1541 WHERE TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1542 ,TRUNC(CSH.STATEMENT_DATE))
1543 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1544 ,TRUNC(CSH.STATEMENT_DATE));
1545 C_ARCHIVE_STAT_HEADERS2 := SQL%ROWCOUNT;
1546 END IF;
1547 INSERT INTO CE_ARCH_INTRA_LINES
1548 (STATEMENT_LINE_ID
1549 ,STATEMENT_HEADER_ID
1550 ,LINE_NUMBER
1551 ,TRX_DATE
1552 ,TRX_TYPE
1553 ,AMOUNT
1554 ,CHARGES_AMOUNT
1555 ,STATUS
1556 ,TRX_CODE_ID
1557 ,EFFECTIVE_DATE
1558 ,BANK_TRX_NUMBER
1559 ,TRX_TEXT
1560 ,CUSTOMER_TEXT
1561 ,INVOICE_TEXT
1562 ,CURRENCY_CODE
1563 ,EXCHANGE_RATE_TYPE
1564 ,EXCHANGE_RATE
1565 ,EXCHANGE_RATE_DATE
1566 ,ORIGINAL_AMOUNT
1567 ,ATTRIBUTE_CATEGORY
1568 ,ATTRIBUTE1
1569 ,ATTRIBUTE2
1570 ,ATTRIBUTE3
1571 ,ATTRIBUTE4
1572 ,ATTRIBUTE5
1573 ,ATTRIBUTE6
1574 ,ATTRIBUTE7
1575 ,ATTRIBUTE8
1576 ,ATTRIBUTE9
1577 ,ATTRIBUTE10
1578 ,ATTRIBUTE11
1579 ,ATTRIBUTE12
1580 ,ATTRIBUTE13
1581 ,ATTRIBUTE14
1582 ,ATTRIBUTE15
1583 ,LAST_UPDATE_LOGIN
1584 ,CREATED_BY
1585 ,CREATION_DATE
1586 ,LAST_UPDATED_BY
1587 ,LAST_UPDATE_DATE
1588 ,RECONCILE_TO_STATEMENT_FLAG)
1589 SELECT
1590 STATEMENT_LINE_ID,
1591 STATEMENT_HEADER_ID,
1592 LINE_NUMBER,
1593 TRX_DATE,
1594 TRX_TYPE,
1595 AMOUNT,
1596 CHARGES_AMOUNT,
1597 STATUS,
1598 TRX_CODE_ID,
1599 EFFECTIVE_DATE,
1600 BANK_TRX_NUMBER,
1601 TRX_TEXT,
1602 CUSTOMER_TEXT,
1603 INVOICE_TEXT,
1604 CURRENCY_CODE,
1605 EXCHANGE_RATE_TYPE,
1606 EXCHANGE_RATE,
1607 EXCHANGE_RATE_DATE,
1608 ORIGINAL_AMOUNT,
1609 ATTRIBUTE_CATEGORY,
1610 ATTRIBUTE1,
1611 ATTRIBUTE2,
1612 ATTRIBUTE3,
1613 ATTRIBUTE4,
1614 ATTRIBUTE5,
1615 ATTRIBUTE6,
1616 ATTRIBUTE7,
1617 ATTRIBUTE8,
1618 ATTRIBUTE9,
1619 ATTRIBUTE10,
1620 ATTRIBUTE11,
1621 ATTRIBUTE12,
1622 ATTRIBUTE13,
1623 ATTRIBUTE14,
1624 ATTRIBUTE15,
1625 LAST_UPDATE_LOGIN,
1626 CREATED_BY,
1627 CREATION_DATE,
1628 LAST_UPDATED_BY,
1629 LAST_UPDATE_DATE,
1630 RECONCILE_TO_STATEMENT_FLAG
1631 FROM
1632 CE_INTRA_STMT_LINES
1633 WHERE STATEMENT_HEADER_ID IN (
1634 SELECT
1635 STATEMENT_HEADER_ID
1636 FROM
1637 CE_ARCH_INTRA_HEADERS );
1638 C_ARCHIVE_STAT_LINES2 := SQL%ROWCOUNT;
1639 END IF;
1640 OPEN C_PURGE_INTRA_HEADERS;
1641 FETCH C_PURGE_INTRA_HEADERS
1642 INTO
1643 X_HEADER_ID;
1644 PURGE_HEADERS := 0;
1645 PURGE_LINES := 0;
1646 LOOP
1647 EXIT WHEN C_PURGE_INTRA_HEADERS%NOTFOUND;
1648 DELETE FROM CE_INTRA_STMT_HEADERS
1649 WHERE STATEMENT_HEADER_ID = X_HEADER_ID;
1650 PURGE_HEADERS := PURGE_HEADERS + SQL%ROWCOUNT;
1651 OPEN C_PURGE_INTRA_LINES;
1652 FETCH C_PURGE_INTRA_LINES
1653 INTO
1654 X_LINE_ID;
1655 LOOP
1656 EXIT WHEN C_PURGE_INTRA_LINES%NOTFOUND;
1657 DELETE FROM CE_INTRA_STMT_LINES
1658 WHERE STATEMENT_HEADER_ID = X_HEADER_ID;
1659 PURGE_LINES := PURGE_LINES + SQL%ROWCOUNT;
1660 FETCH C_PURGE_INTRA_LINES
1661 INTO
1662 X_LINE_ID;
1663 END LOOP;
1664 CLOSE C_PURGE_INTRA_LINES;
1665 FETCH C_PURGE_INTRA_HEADERS
1666 INTO
1667 X_HEADER_ID;
1668 END LOOP;
1669 CLOSE C_PURGE_INTRA_HEADERS;
1670 C_PURGE_STAT_LINES2 := PURGE_LINES;
1671 C_PURGE_STAT_HEADERS2 := PURGE_HEADERS;
1672 END IF;
1673 IF (P_OBJECTS in ('BOTH','INTERFACE')) THEN
1674 IF (P_OPTION = 'BOTH') THEN
1675 INSERT INTO CE_ARCH_INTERFACE_HEADERS
1676 (STATEMENT_NUMBER
1677 ,BANK_ACCOUNT_NUM
1678 ,STATEMENT_DATE
1679 ,BANK_NAME
1680 ,BANK_BRANCH_NAME
1681 ,CHECK_DIGITS
1682 ,CONTROL_BEGIN_BALANCE
1683 ,CONTROL_TOTAL_DR
1684 ,CONTROL_TOTAL_CR
1685 ,CONTROL_END_BALANCE
1686 ,CASHFLOW_BALANCE
1687 ,INT_CALC_BALANCE
1688 ,ONE_DAY_FLOAT
1689 ,TWO_DAY_FLOAT
1690 ,CONTROL_DR_LINE_COUNT
1691 ,CONTROL_CR_LINE_COUNT
1692 ,CONTROL_LINE_COUNT
1693 ,RECORD_STATUS_FLAG
1694 ,CURRENCY_CODE
1695 ,ATTRIBUTE_CATEGORY
1696 ,ATTRIBUTE1
1697 ,ATTRIBUTE2
1698 ,ATTRIBUTE3
1699 ,ATTRIBUTE4
1700 ,ATTRIBUTE5
1701 ,ATTRIBUTE6
1702 ,ATTRIBUTE7
1703 ,ATTRIBUTE8
1704 ,ATTRIBUTE9
1705 ,ATTRIBUTE10
1706 ,ATTRIBUTE11
1707 ,ATTRIBUTE12
1708 ,ATTRIBUTE13
1709 ,ATTRIBUTE14
1710 ,ATTRIBUTE15
1711 ,CREATED_BY
1712 ,CREATION_DATE
1713 ,LAST_UPDATED_BY
1714 ,LAST_UPDATE_DATE
1715 ,INTRA_DAY_FLAG)
1716 SELECT
1717 STATEMENT_NUMBER,
1718 BANK_ACCOUNT_NUM,
1719 STATEMENT_DATE,
1720 BANK_NAME,
1721 BANK_BRANCH_NAME,
1722 CHECK_DIGITS,
1723 CONTROL_BEGIN_BALANCE,
1724 CONTROL_TOTAL_DR,
1725 CONTROL_TOTAL_CR,
1726 CONTROL_END_BALANCE,
1727 CASHFLOW_BALANCE,
1728 INT_CALC_BALANCE,
1729 ONE_DAY_FLOAT,
1730 TWO_DAY_FLOAT,
1731 CONTROL_DR_LINE_COUNT,
1732 CONTROL_CR_LINE_COUNT,
1733 CONTROL_LINE_COUNT,
1734 RECORD_STATUS_FLAG,
1735 CURRENCY_CODE,
1736 ATTRIBUTE_CATEGORY,
1737 ATTRIBUTE1,
1738 ATTRIBUTE2,
1739 ATTRIBUTE3,
1740 ATTRIBUTE4,
1741 ATTRIBUTE5,
1742 ATTRIBUTE6,
1743 ATTRIBUTE7,
1744 ATTRIBUTE8,
1745 ATTRIBUTE9,
1746 ATTRIBUTE10,
1747 ATTRIBUTE11,
1748 ATTRIBUTE12,
1749 ATTRIBUTE13,
1750 ATTRIBUTE14,
1751 ATTRIBUTE15,
1752 CREATED_BY,
1753 CREATION_DATE,
1754 LAST_UPDATED_BY,
1755 LAST_UPDATE_DATE,
1756 INTRA_DAY_FLAG
1757 FROM
1758 CE_STATEMENT_HEADERS_INT CSH
1759 WHERE NVL(CSH.BANK_BRANCH_NAME
1760 ,C_BANK_BRANCH_NAME) IN (
1761 SELECT
1762 BB.BANK_BRANCH_NAME
1763 FROM
1764 CE_BANK_BRANCHES_V BB,
1765 CE_BANK_ACCTS_GT_V BA
1766 WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
1767 AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1768 ,BA.BANK_ACCOUNT_ID)
1769 AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
1770 ,BB.BRANCH_PARTY_ID) )
1771 AND CSH.BANK_ACCOUNT_NUM IN (
1772 SELECT
1773 BANK_ACCOUNT_NUM
1774 FROM
1775 CE_BANK_ACCTS_GT_V
1776 WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1777 ,BANK_ACCOUNT_ID)
1778 AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
1779 ,BANK_BRANCH_ID) )
1780 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1781 ,TRUNC(CSH.STATEMENT_DATE))
1782 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1783 ,TRUNC(CSH.STATEMENT_DATE))
1784 AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
1785 ,'C'
1786 ,'C'
1787 ,'E'
1788 ,'E'
1789 ,'N'
1790 ,'N'
1791 ,'T'
1792 ,'T'
1793 ,'A'
1794 ,RECORD_STATUS_FLAG
1795 ,'T') )
1796 AND CSH.INTRA_DAY_FLAG = 'Y';
1797 C_ARCHIVE_INF_HEADERS2 := SQL%ROWCOUNT;
1798 INSERT INTO CE_ARCH_INTERFACE_LINES
1799 (EXCHANGE_RATE_DATE
1800 ,EXCHANGE_RATE
1801 ,BANK_TRX_NUMBER
1802 ,CUSTOMER_TEXT
1803 ,CREATED_BY
1804 ,CREATION_DATE
1805 ,LAST_UPDATED_BY
1806 ,LAST_UPDATE_DATE
1807 ,ATTRIBUTE_CATEGORY
1808 ,ATTRIBUTE1
1809 ,ATTRIBUTE2
1810 ,ATTRIBUTE3
1811 ,ATTRIBUTE4
1812 ,ATTRIBUTE5
1813 ,ATTRIBUTE6
1814 ,ATTRIBUTE7
1815 ,ATTRIBUTE8
1816 ,ATTRIBUTE9
1817 ,ATTRIBUTE10
1818 ,ATTRIBUTE11
1819 ,ATTRIBUTE12
1820 ,ATTRIBUTE13
1821 ,ATTRIBUTE14
1822 ,ATTRIBUTE15
1823 ,ORIGINAL_AMOUNT
1824 ,BANK_ACCOUNT_NUM
1825 ,STATEMENT_NUMBER
1826 ,LINE_NUMBER
1827 ,TRX_DATE
1828 ,TRX_CODE
1829 ,EFFECTIVE_DATE
1830 ,TRX_TEXT
1831 ,INVOICE_TEXT
1832 ,AMOUNT
1833 ,CHARGES_AMOUNT
1834 ,CURRENCY_CODE
1835 ,USER_EXCHANGE_RATE_TYPE)
1836 SELECT
1837 EXCHANGE_RATE_DATE,
1838 EXCHANGE_RATE,
1839 BANK_TRX_NUMBER,
1840 CUSTOMER_TEXT,
1841 CREATED_BY,
1842 CREATION_DATE,
1843 LAST_UPDATED_BY,
1844 LAST_UPDATE_DATE,
1845 ATTRIBUTE_CATEGORY,
1846 ATTRIBUTE1,
1847 ATTRIBUTE2,
1848 ATTRIBUTE3,
1849 ATTRIBUTE4,
1850 ATTRIBUTE5,
1851 ATTRIBUTE6,
1852 ATTRIBUTE7,
1853 ATTRIBUTE8,
1854 ATTRIBUTE9,
1855 ATTRIBUTE10,
1856 ATTRIBUTE11,
1857 ATTRIBUTE12,
1858 ATTRIBUTE13,
1859 ATTRIBUTE14,
1860 ATTRIBUTE15,
1861 ORIGINAL_AMOUNT,
1862 BANK_ACCOUNT_NUM,
1863 STATEMENT_NUMBER,
1864 LINE_NUMBER,
1865 TRX_DATE,
1866 TRX_CODE,
1867 EFFECTIVE_DATE,
1868 TRX_TEXT,
1869 INVOICE_TEXT,
1870 AMOUNT,
1871 CHARGES_AMOUNT,
1872 CURRENCY_CODE,
1873 USER_EXCHANGE_RATE_TYPE
1874 FROM
1875 CE_STATEMENT_LINES_INTERFACE CSL
1876 WHERE CSL.BANK_ACCOUNT_NUM IN (
1877 SELECT
1878 BANK_ACCOUNT_NUM
1879 FROM
1880 CE_ARCH_INTERFACE_HEADERS )
1881 AND STATEMENT_NUMBER IN (
1882 SELECT
1883 STATEMENT_NUMBER
1884 FROM
1885 CE_ARCH_INTERFACE_HEADERS
1886 WHERE CSL.BANK_ACCOUNT_NUM = BANK_ACCOUNT_NUM
1887 AND INTRA_DAY_FLAG = 'Y' );
1888 C_ARCHIVE_INF_LINES2 := SQL%ROWCOUNT;
1889 END IF;
1890 DELETE FROM CE_STATEMENT_LINES_INTERFACE CSL
1891 WHERE CSL.STATEMENT_NUMBER || '-' || CSL.BANK_ACCOUNT_NUM IN (
1892 SELECT
1893 CSH.STATEMENT_NUMBER || '-' || CSH.BANK_ACCOUNT_NUM
1894 FROM
1895 CE_STATEMENT_HEADERS_INT CSH
1896 WHERE NVL(CSH.BANK_BRANCH_NAME
1897 ,C_BANK_BRANCH_NAME) IN (
1898 SELECT
1899 BB.BANK_BRANCH_NAME
1900 FROM
1901 CE_BANK_BRANCHES_V BB,
1902 CE_BANK_ACCTS_GT_V BA
1903 WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
1904 AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1905 ,BA.BANK_ACCOUNT_ID)
1906 AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
1907 ,BB.BRANCH_PARTY_ID) )
1908 AND CSH.BANK_ACCOUNT_NUM IN (
1909 SELECT
1910 BANK_ACCOUNT_NUM
1911 FROM
1912 CE_BANK_ACCTS_GT_V
1913 WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1914 ,BANK_ACCOUNT_ID)
1915 AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
1916 ,BANK_BRANCH_ID) )
1917 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1918 ,TRUNC(CSH.STATEMENT_DATE))
1919 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1920 ,TRUNC(CSH.STATEMENT_DATE))
1921 AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
1922 ,'C'
1923 ,'C'
1924 ,'E'
1925 ,'E'
1926 ,'N'
1927 ,'N'
1928 ,'T'
1929 ,'T'
1930 ,'A'
1931 ,RECORD_STATUS_FLAG
1932 ,'T') )
1933 AND CSH.INTRA_DAY_FLAG = 'Y' );
1934 C_PURGE_INF_LINES2 := SQL%ROWCOUNT;
1935 DELETE FROM CE_STATEMENT_HEADERS_INT CSH
1936 WHERE NVL(CSH.BANK_BRANCH_NAME
1937 ,C_BANK_BRANCH_NAME) IN (
1938 SELECT
1939 BB.BANK_BRANCH_NAME
1940 FROM
1941 CE_BANK_BRANCHES_V BB,
1942 CE_BANK_ACCTS_GT_V BA
1943 WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
1944 AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1945 ,BA.BANK_ACCOUNT_ID)
1946 AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
1947 ,BB.BRANCH_PARTY_ID) )
1948 AND CSH.BANK_ACCOUNT_NUM IN (
1949 SELECT
1950 BANK_ACCOUNT_NUM
1951 FROM
1952 CE_BANK_ACCTS_GT_V
1953 WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
1954 ,BANK_ACCOUNT_ID)
1955 AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
1956 ,BANK_BRANCH_ID) )
1957 AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
1958 ,TRUNC(CSH.STATEMENT_DATE))
1959 AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
1960 ,TRUNC(CSH.STATEMENT_DATE))
1961 AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
1962 ,'C'
1963 ,'C'
1964 ,'E'
1965 ,'E'
1966 ,'N'
1967 ,'N'
1968 ,'T'
1969 ,'T'
1970 ,'A'
1971 ,RECORD_STATUS_FLAG
1972 ,'T') )
1973 AND CSH.INTRA_DAY_FLAG = 'Y';
1974 C_PURGE_INF_HEADERS2 := SQL%ROWCOUNT;
1975 END IF;
1976 END IF;
1977 COMMIT;
1978 ELSE
1979 L_MESSAGE := GET;
1980 END IF;
1981 END;
1982 RETURN (TRUE);
1983 END BEFOREREPORT;
1984
1985 FUNCTION AFTERREPORT RETURN BOOLEAN IS
1986 BEGIN
1987 BEGIN
1988 NULL;
1989 END;
1990 RETURN (TRUE);
1991 END AFTERREPORT;
1992
1993 FUNCTION C_DATEFORMATFORMULA(C_DATEFORMAT IN VARCHAR2) RETURN VARCHAR2 IS
1994 BEGIN
1995 RETURN (C_DATEFORMAT);
1996 END C_DATEFORMATFORMULA;
1997
1998 FUNCTION C_ARCHIVE_INF_HEADERS_P RETURN NUMBER IS
1999 BEGIN
2000 RETURN C_ARCHIVE_INF_HEADERS;
2001 END C_ARCHIVE_INF_HEADERS_P;
2002
2003 FUNCTION C_ARCHIVE_INF_LINES_P RETURN NUMBER IS
2004 BEGIN
2005 RETURN C_ARCHIVE_INF_LINES;
2006 END C_ARCHIVE_INF_LINES_P;
2007
2008 FUNCTION C_ARCHIVE_STAT_HEADERS_P RETURN NUMBER IS
2009 BEGIN
2010 RETURN C_ARCHIVE_STAT_HEADERS;
2011 END C_ARCHIVE_STAT_HEADERS_P;
2012
2013 FUNCTION C_ARCHIVE_STAT_LINES_P RETURN NUMBER IS
2014 BEGIN
2015 RETURN C_ARCHIVE_STAT_LINES;
2016 END C_ARCHIVE_STAT_LINES_P;
2017
2018 FUNCTION C_ARCHIVE_STAT_REC_P RETURN NUMBER IS
2019 BEGIN
2020 RETURN C_ARCHIVE_STAT_REC;
2021 END C_ARCHIVE_STAT_REC_P;
2022
2023 FUNCTION C_ARCHIVE_STAT_ERRORS_P RETURN NUMBER IS
2024 BEGIN
2025 RETURN C_ARCHIVE_STAT_ERRORS;
2026 END C_ARCHIVE_STAT_ERRORS_P;
2027
2028 FUNCTION C_PURGE_INF_HEADERS_P RETURN NUMBER IS
2029 BEGIN
2030 RETURN C_PURGE_INF_HEADERS;
2031 END C_PURGE_INF_HEADERS_P;
2032
2033 FUNCTION C_PURGE_INF_LINES_P RETURN NUMBER IS
2034 BEGIN
2035 RETURN C_PURGE_INF_LINES;
2036 END C_PURGE_INF_LINES_P;
2037
2038 FUNCTION C_PURGE_STAT_HEADERS_P RETURN NUMBER IS
2039 BEGIN
2040 RETURN C_PURGE_STAT_HEADERS;
2041 END C_PURGE_STAT_HEADERS_P;
2042
2043 FUNCTION C_PURGE_STAT_LINES_P RETURN NUMBER IS
2044 BEGIN
2045 RETURN C_PURGE_STAT_LINES;
2046 END C_PURGE_STAT_LINES_P;
2047
2048 FUNCTION C_PURGE_STAT_REC_P RETURN NUMBER IS
2049 BEGIN
2050 RETURN C_PURGE_STAT_REC;
2051 END C_PURGE_STAT_REC_P;
2052
2053 FUNCTION C_PURGE_STAT_ERRORS_P RETURN NUMBER IS
2054 BEGIN
2055 RETURN C_PURGE_STAT_ERRORS;
2056 END C_PURGE_STAT_ERRORS_P;
2057
2058 FUNCTION C_BANK_NAME_P RETURN VARCHAR2 IS
2059 BEGIN
2060 RETURN C_BANK_NAME;
2061 END C_BANK_NAME_P;
2062
2063 FUNCTION C_BANK_BRANCH_NAME_P RETURN VARCHAR2 IS
2064 BEGIN
2065 RETURN C_BANK_BRANCH_NAME;
2066 END C_BANK_BRANCH_NAME_P;
2067
2068 FUNCTION C_BANK_ACCOUNT_NAME_P RETURN VARCHAR2 IS
2069 BEGIN
2070 RETURN C_BANK_ACCOUNT_NAME;
2071 END C_BANK_ACCOUNT_NAME_P;
2072
2073 FUNCTION C_BANK_ACCOUNT_NUM_P RETURN VARCHAR2 IS
2074 BEGIN
2075 RETURN C_BANK_ACCOUNT_NUM;
2076 END C_BANK_ACCOUNT_NUM_P;
2077
2078 FUNCTION C_CURRENCY_CODE_P RETURN VARCHAR2 IS
2079 BEGIN
2080 RETURN C_CURRENCY_CODE;
2081 END C_CURRENCY_CODE_P;
2082
2083 FUNCTION C_SET_OF_BOOK_P RETURN VARCHAR2 IS
2084 BEGIN
2085 RETURN C_SET_OF_BOOK;
2086 END C_SET_OF_BOOK_P;
2087
2088 FUNCTION C_ALL_TRANSLATION_P RETURN VARCHAR2 IS
2089 BEGIN
2090 RETURN C_ALL_TRANSLATION;
2091 END C_ALL_TRANSLATION_P;
2092
2093 FUNCTION C_ARCHIVE_INF_HEADERS2_P RETURN NUMBER IS
2094 BEGIN
2095 RETURN C_ARCHIVE_INF_HEADERS2;
2096 END C_ARCHIVE_INF_HEADERS2_P;
2097
2098 FUNCTION C_ARCHIVE_INF_LINES2_P RETURN NUMBER IS
2099 BEGIN
2100 RETURN C_ARCHIVE_INF_LINES2;
2101 END C_ARCHIVE_INF_LINES2_P;
2102
2103 FUNCTION C_ARCHIVE_STAT_HEADERS2_P RETURN NUMBER IS
2104 BEGIN
2105 RETURN C_ARCHIVE_STAT_HEADERS2;
2106 END C_ARCHIVE_STAT_HEADERS2_P;
2107
2108 FUNCTION C_ARCHIVE_STAT_LINES2_P RETURN NUMBER IS
2109 BEGIN
2110 RETURN C_ARCHIVE_STAT_LINES2;
2111 END C_ARCHIVE_STAT_LINES2_P;
2112
2113 FUNCTION C_PURGE_INF_HEADERS2_P RETURN NUMBER IS
2114 BEGIN
2115 RETURN C_PURGE_INF_HEADERS2;
2116 END C_PURGE_INF_HEADERS2_P;
2117
2118 FUNCTION C_PURGE_INF_LINES2_P RETURN NUMBER IS
2119 BEGIN
2120 RETURN C_PURGE_INF_LINES2;
2121 END C_PURGE_INF_LINES2_P;
2122
2123 FUNCTION C_PURGE_STAT_HEADERS2_P RETURN NUMBER IS
2124 BEGIN
2125 RETURN C_PURGE_STAT_HEADERS2;
2126 END C_PURGE_STAT_HEADERS2_P;
2127
2128 FUNCTION C_PURGE_STAT_LINES2_P RETURN NUMBER IS
2129 BEGIN
2130 RETURN C_PURGE_STAT_LINES2;
2131 END C_PURGE_STAT_LINES2_P;
2132
2133 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
2134 ,NAME IN VARCHAR2) IS
2135 BEGIN
2136 /* STPROC.INIT('begin FND_MESSAGE.SET_NAME(:APPLICATION, :NAME); end;');
2137 STPROC.BIND_I(APPLICATION);
2138 STPROC.BIND_I(NAME);
2139 STPROC.EXECUTE;*/
2140 FND_MESSAGE.SET_NAME(APPLICATION, NAME);
2141 END SET_NAME;
2142
2143 PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
2144 ,VALUE IN VARCHAR2
2145 ,TRANSLATE IN number) IS
2146 BEGIN
2147 declare
2148 TRANSLATE_1 BOOLEAN;
2149 /* STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
2150 STPROC.BIND_I(TRANSLATE);
2151 STPROC.BIND_I(TOKEN);
2152 STPROC.BIND_I(VALUE);
2153 STPROC.EXECUTE;*/
2154 begin
2155 TRANSLATE_1 := sys.diutil.int_to_bool(TRANSLATE);
2156 end;
2157 END SET_TOKEN;
2158
2159 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
2160 BEGIN
2161 /*STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
2162 STPROC.BIND_O(MSGOUT);
2163 STPROC.EXECUTE;
2164 STPROC.RETRIEVE(1
2165 ,MSGOUT);*/
2166 FND_MESSAGE.RETRIEVE(MSGOUT);
2167 END RETRIEVE;
2168
2169 PROCEDURE CLEAR IS
2170 BEGIN
2171 /* STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
2172 STPROC.EXECUTE;*/
2173 FND_MESSAGE.CLEAR;
2174 END CLEAR;
2175
2176 FUNCTION GET_STRING(APPIN IN VARCHAR2
2177 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
2178 X0 VARCHAR2(2000);
2179 BEGIN
2180 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
2181 STPROC.BIND_O(X0);
2182 STPROC.BIND_I(APPIN);
2183 STPROC.BIND_I(NAMEIN);
2184 STPROC.EXECUTE;
2185 STPROC.RETRIEVE(1
2186 ,X0);*/
2187 X0 := FND_MESSAGE.GET_STRING(APPIN, NAMEIN);
2188 RETURN X0;
2189 END GET_STRING;
2190
2191 FUNCTION GET_NUMBER(APPIN IN VARCHAR2
2192 ,NAMEIN IN VARCHAR2) RETURN NUMBER IS
2193 X0 NUMBER;
2194 BEGIN
2195 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_NUMBER(:APPIN, :NAMEIN); end;');
2196 STPROC.BIND_O(X0);
2197 STPROC.BIND_I(APPIN);
2198 STPROC.BIND_I(NAMEIN);
2199 STPROC.EXECUTE;
2200 STPROC.RETRIEVE(1
2201 ,X0);*/
2202 X0 := FND_MESSAGE.GET_NUMBER(APPIN, NAMEIN);
2203 RETURN X0;
2204 END GET_NUMBER;
2205
2206 FUNCTION GET RETURN VARCHAR2 IS
2207 X0 VARCHAR2(2000);
2208 BEGIN
2209 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET; end;');
2210 STPROC.BIND_O(X0);
2211 STPROC.EXECUTE;
2212 STPROC.RETRIEVE(1
2213 ,X0);*/
2214 X0 := FND_MESSAGE.GET;
2215 RETURN X0;
2216 END GET;
2217
2218 FUNCTION GET_ENCODED RETURN VARCHAR2 IS
2219 X0 VARCHAR2(2000);
2220 BEGIN
2221 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
2222 STPROC.BIND_O(X0);
2223 STPROC.EXECUTE;
2224 STPROC.RETRIEVE(1
2225 ,X0);*/
2226 X0 := FND_MESSAGE.GET_ENCODED;
2227 RETURN X0;
2228 END GET_ENCODED;
2229
2230 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
2231 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
2232 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
2233 BEGIN
2234 /* STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
2235 STPROC.BIND_I(ENCODED_MESSAGE);
2236 STPROC.BIND_O(APP_SHORT_NAME);
2237 STPROC.BIND_O(MESSAGE_NAME);
2238 STPROC.EXECUTE;
2239 STPROC.RETRIEVE(2
2240 ,APP_SHORT_NAME);
2241 STPROC.RETRIEVE(3
2242 ,MESSAGE_NAME);*/
2243 FND_MESSAGE.PARSE_ENCODED(ENCODED_MESSAGE, APP_SHORT_NAME, MESSAGE_NAME);
2244 END PARSE_ENCODED;
2245
2246 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
2247 BEGIN
2248 /* STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
2249 STPROC.BIND_I(ENCODED_MESSAGE);
2250 STPROC.EXECUTE;*/
2251 FND_MESSAGE.SET_ENCODED(ENCODED_MESSAGE);
2252 END SET_ENCODED;
2253
2254 PROCEDURE RAISE_ERROR IS
2255 BEGIN
2256 /* STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
2257 STPROC.EXECUTE;*/
2258 FND_MESSAGE.RAISE_ERROR;
2259 END RAISE_ERROR;
2260
2261 PROCEDURE DEBUG(LINE IN VARCHAR2) IS
2262 BEGIN
2263 /* STPROC.INIT('begin CEP_STANDARD.DEBUG(:LINE); end;');
2264 STPROC.BIND_I(LINE);
2265 STPROC.EXECUTE;*/
2266 CEP_STANDARD.DEBUG(LINE);
2267 END DEBUG;
2268
2269 PROCEDURE ENABLE_DEBUG IS
2270 BEGIN
2271 /* STPROC.INIT('begin CEP_STANDARD.ENABLE_DEBUG; end;');
2272 STPROC.EXECUTE;*/
2273 CEP_STANDARD.ENABLE_DEBUG;
2274
2275 END ENABLE_DEBUG;
2276
2277 PROCEDURE DISABLE_DEBUG IS
2278 BEGIN
2279 /*STPROC.INIT('begin CEP_STANDARD.DISABLE_DEBUG; end;');
2280 STPROC.EXECUTE;*/
2281 --CEP_STANDARD.DISABLE_DEBUG;
2282 null;
2283 END DISABLE_DEBUG;
2284
2285 FUNCTION GET_WINDOW_SESSION_TITLE(P_ORG_ID IN NUMBER := NULL
2286 ,P_LEGAL_ENTITY_ID IN NUMBER := NULL) RETURN VARCHAR2 IS
2287 X0 VARCHAR2(2000);
2288 BEGIN
2289 /* STPROC.INIT('begin :X0 := CEP_STANDARD.GET_WINDOW_SESSION_TITLE(p_org_id, p_legal_entity_id ); end;');
2290 STPROC.BIND_O(X0);
2291 STPROC.EXECUTE;
2292 STPROC.RETRIEVE(1
2293 ,X0);*/
2294 X0 := CEP_STANDARD.GET_WINDOW_SESSION_TITLE(p_org_id, p_legal_entity_id );
2295 RETURN X0;
2296 END GET_WINDOW_SESSION_TITLE;
2297
2298 FUNCTION GET_EFFECTIVE_DATE(P_BANK_ACCOUNT_ID IN NUMBER
2299 ,P_TRX_CODE IN VARCHAR2
2300 ,P_RECEIPT_DATE IN DATE) RETURN DATE IS
2301 X0 DATE;
2302 BEGIN
2303 /* STPROC.INIT('begin :X0 := CEP_STANDARD.GET_EFFECTIVE_DATE(:P_BANK_ACCOUNT_ID, :P_TRX_CODE, :P_RECEIPT_DATE);end;');
2304 STPROC.BIND_O(X0);
2305 STPROC.BIND_I(P_BANK_ACCOUNT_ID);
2306 STPROC.BIND_I(P_TRX_CODE);
2307 STPROC.BIND_I(P_RECEIPT_DATE);
2308 STPROC.EXECUTE;
2309 STPROC.RETRIEVE(1
2310 ,X0);*/
2311 X0 := CEP_STANDARD.GET_EFFECTIVE_DATE(P_BANK_ACCOUNT_ID, P_TRX_CODE, P_RECEIPT_DATE);
2312 RETURN X0;
2313 END GET_EFFECTIVE_DATE;
2314
2315 PROCEDURE INIT_SECURITY IS
2316 BEGIN
2317 /* STPROC.INIT('begin cep_standard.init_security; end;');
2318 STPROC.EXECUTE;*/
2319 cep_standard.init_security;
2320 END INIT_SECURITY;
2321
2322 END CE_CEPURGE_XMLP_PKG;