1 PACKAGE BODY CE_CEXSTMRR_XMLP_PKG AS
2 /* $Header: CEXSTMRRB.pls 120.0 2007/12/28 07:56:57 abraghun noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
7 BEGIN
8 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
9 P_STAT_DATE_FROM_T := to_char(P_STAT_DATE_FROM,FND_GLOBAL.NLS_DATE_FORMAT);
10 P_STAT_DATE_TO_T := to_char(P_STAT_DATE_TO,FND_GLOBAL.NLS_DATE_FORMAT);
11 INIT_SECURITY;
12 IF (P_TEST_LAYOUT = 'Y') THEN
13 RAISE NO_DATA_FOUND;
14 END IF;
15 BEGIN
16 SELECT
17 L.MEANING,
18 L2.MEANING
19 INTO
20 C_ALL_TRANSLATION
21 ,C_EXTERNAL_TRANSLATION
22 FROM
23 CE_LOOKUPS L,
24 CE_LOOKUPS L2
25 WHERE L.LOOKUP_TYPE = 'LITERAL'
26 AND L.LOOKUP_CODE = 'ALL'
27 AND L2.LOOKUP_TYPE = 'STATEMENT_LINE_STATUS'
28 AND L2.LOOKUP_CODE = 'EXTERNAL';
29 EXCEPTION
30 WHEN OTHERS THEN
31 SET_NAME('CE'
32 ,'CE_PURGE_NO_SOB');
33 L_MESSAGE := GET;
34 RAISE_APPLICATION_ERROR(-20101
35 ,NULL);
36 END;
37 IF (P_BANK_ACCOUNT_ID IS NOT NULL) THEN
38 BEGIN
39 SELECT
40 ABB.BANK_NAME,
41 ABB.BANK_BRANCH_NAME,
42 ABA.BANK_ACCOUNT_NAME,
43 ABA.BANK_ACCOUNT_NUM,
44 ABA.CURRENCY_CODE
45 INTO
46 C_BANK_NAME_DSP
47 ,C_BANK_BRANCH_DSP
48 ,C_ACCOUNT_NAME_DSP
49 ,C_ACCOUNT_NUMBER_DSP
50 ,C_BANK_CURR_DSP
51 FROM
52 CE_BANK_BRANCHES_V ABB,
53 CE_BANK_ACCTS_GT_V ABA
54 WHERE ABA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID
55 AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID;
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 SET_NAME('CE'
59 ,'CE_INVALID_BANK');
60 L_MESSAGE := GET;
61 RAISE_APPLICATION_ERROR(-20101
62 ,NULL);
63 END;
64 ELSE
65 C_BANK_NAME_DSP := C_ALL_TRANSLATION;
66 C_BANK_BRANCH_DSP := C_ALL_TRANSLATION;
67 C_ACCOUNT_NAME_DSP := C_ALL_TRANSLATION;
68 C_ACCOUNT_NUMBER_DSP := C_ALL_TRANSLATION;
69 C_BANK_CURR_DSP := C_ALL_TRANSLATION;
70 END IF;
71 IF (P_STAT_DATE_FROM IS NOT NULL AND P_STAT_DATE_TO IS NOT NULL) THEN
72 C_STAT_DATE_SQL_LEX := 'trunc(sh.statement_date) BETWEEN to_date(''' || TO_CHAR(P_STAT_DATE_FROM
73 ,'DD-MON-YYYY') || ''',''DD-MON-YYYY'') AND to_date(''' || TO_CHAR(P_STAT_DATE_TO
74 ,'DD-MON-YYYY') || ''',''DD-MON-YYYY'')';
75 ELSIF (P_STAT_DATE_FROM IS NULL AND P_STAT_DATE_TO IS NOT NULL) THEN
76 C_STAT_DATE_SQL_LEX := 'sh.statement_date <= to_date(''' || TO_CHAR(P_STAT_DATE_TO
77 ,'DD-MON-YYYY') || ''',''DD-MON-YYYY'')';
78 ELSIF (P_STAT_DATE_FROM IS NOT NULL AND P_STAT_DATE_TO IS NULL) THEN
79 C_STAT_DATE_SQL_LEX := 'sh.statement_date >= to_date(''' || TO_CHAR(P_STAT_DATE_FROM
80 ,'DD-MON-YYYY') || ''',''DD-MON-YYYY'')';
81 END IF;
82 IF (P_STAT_NUMBER_FROM IS NOT NULL AND P_STAT_NUMBER_TO IS NOT NULL) THEN
83 C_STAT_NUMBER_SQL_LEX := 'sh.statement_number BETWEEN ''' || P_STAT_NUMBER_FROM || ''' AND ''' || P_STAT_NUMBER_TO || '''';
84 ELSIF (P_STAT_NUMBER_FROM IS NULL AND P_STAT_NUMBER_TO IS NOT NULL) THEN
85 C_STAT_NUMBER_SQL_LEX := 'sh.statement_number <= ''' || P_STAT_NUMBER_TO || '''';
86 ELSIF (P_STAT_NUMBER_FROM IS NOT NULL AND P_STAT_NUMBER_TO IS NULL) THEN
87 C_STAT_NUMBER_SQL_LEX := 'sh.statement_number >= ''' || P_STAT_NUMBER_FROM || '''';
88 END IF;
89 BEGIN
90 SET_NAME('FND'
91 ,'FND_MO_RPT_PARTIAL_LEDGER');
92 L_MESSAGE := GET;
93 C_DISPLAY_MESSAGE := L_MESSAGE;
94 END;
95 IF (P_DEBUG_MODE = 'Y') THEN
96 NULL;
97 END IF;
98 END;
99 RETURN (TRUE);
100 END BEFOREREPORT;
101
102 FUNCTION AFTERREPORT RETURN BOOLEAN IS
103 BEGIN
104 RETURN (TRUE);
105 END AFTERREPORT;
106
107 FUNCTION C_STAT_SUM_LINE_AMOUNTFORMULA(C_STAT_HEADER_ID1 IN NUMBER) RETURN NUMBER IS
108 BEGIN
109 DECLARE
110 AMOUNT CE_STATEMENT_LINES.AMOUNT%TYPE;
111 BEGIN
112 SELECT
113 SUM(SL.AMOUNT)
114 INTO
115 AMOUNT
116 FROM
117 CE_STATEMENT_LINES SL
118 WHERE SL.STATEMENT_HEADER_ID = C_STAT_HEADER_ID1
119 AND exists (
120 SELECT
121 DISTINCT
122 ART.STATEMENT_LINE_ID
123 FROM
124 CE_RECONCILED_TRANSACTIONS_V ART
125 WHERE ART.STATEMENT_LINE_ID = SL.STATEMENT_LINE_ID );
126 RETURN (NVL(AMOUNT
127 ,0));
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 RETURN (0);
131 END;
132 RETURN NULL;
133 END C_STAT_SUM_LINE_AMOUNTFORMULA;
134
135 FUNCTION C_STAT_CR_ERRORSFORMULA(C_STAT_HEADER_ID1 IN NUMBER) RETURN NUMBER IS
136 BEGIN
137 DECLARE
138 AMOUNT CE_STATEMENT_LINES.AMOUNT%TYPE;
139 BEGIN
140 SELECT
141 SUM(SL.AMOUNT)
142 INTO
143 AMOUNT
144 FROM
145 CE_STATEMENT_LINES SL
146 WHERE SL.STATEMENT_HEADER_ID = C_STAT_HEADER_ID1
147 AND SL.STATUS = 'ERROR'
148 AND ( SL.TRX_TYPE = 'CREDIT'
149 OR SL.TRX_TYPE = 'MISC_CREDIT'
150 OR SL.TRX_TYPE = 'SWEEP_IN' );
151 RETURN (NVL(AMOUNT
152 ,0));
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 RETURN (0);
156 END;
157 RETURN NULL;
158 END C_STAT_CR_ERRORSFORMULA;
159
160 FUNCTION C_STAT_DR_ERRORSFORMULA(C_STAT_HEADER_ID1 IN NUMBER) RETURN NUMBER IS
161 BEGIN
162 DECLARE
163 AMOUNT CE_STATEMENT_LINES.AMOUNT%TYPE;
164 BEGIN
165 SELECT
166 SUM(SL.AMOUNT)
167 INTO
168 AMOUNT
169 FROM
170 CE_STATEMENT_LINES SL
171 WHERE SL.STATEMENT_HEADER_ID = C_STAT_HEADER_ID1
172 AND SL.STATUS = 'ERROR'
173 AND SL.TRX_TYPE in ( 'DEBIT' , 'MISC_DEBIT' , 'NSF' , 'REJECTED' , 'SWEEP_OUT' );
174 RETURN (NVL(AMOUNT
175 ,0));
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178 RETURN (0);
179 END;
180 RETURN NULL;
181 END C_STAT_DR_ERRORSFORMULA;
182
183 FUNCTION G_BANK_ACCOUNTGROUPFILTER(C_BANK_ACCOUNT_NAME IN VARCHAR2) RETURN BOOLEAN IS
184 BEGIN
185 IF (C_BANK_ACCOUNT_NAME IS NULL) THEN
186 C_THE_END := 'N';
187 ELSE
188 C_THE_END := 'Y';
189 END IF;
190 RETURN (TRUE);
191 END G_BANK_ACCOUNTGROUPFILTER;
192
193 FUNCTION C_TX_AMOUNT_FORFORMULA(C_TX_TYPE IN VARCHAR2
194 ,C_SL_TYPE IN VARCHAR2
195 ,C_TX_BA_AMOUNT IN NUMBER
196 ,C_TX_DR_CR IN VARCHAR2
197 ,C_TX_AMOUNT_CLEARED IN NUMBER) RETURN NUMBER IS
198 BEGIN
199 IF (C_TX_TYPE = 'PAYMENT') THEN
200 IF (C_SL_TYPE in ('DEBIT','MISC_DEBIT','STOP','SWEEP_OUT')) THEN
201 RETURN (C_TX_BA_AMOUNT);
202 ELSE
203 RETURN (-C_TX_BA_AMOUNT);
204 END IF;
205 ELSIF (C_TX_TYPE = 'RECEIPT') THEN
206 IF (C_SL_TYPE in ('CREDIT','MISC_CREDIT','NSF','REJECTED','SWEEP_IN')) THEN
207 RETURN (C_TX_BA_AMOUNT);
208 ELSE
209 RETURN (-C_TX_BA_AMOUNT);
210 END IF;
211 ELSIF (C_TX_TYPE = 'JE_LINE') THEN
212 IF (C_SL_TYPE in ('CREDIT','MISC_CREDIT','NSF','REJECTED','SWEEP_IN')) THEN
213 IF (C_TX_DR_CR = 'DR') THEN
214 RETURN (C_TX_BA_AMOUNT);
215 ELSE
216 RETURN (-C_TX_BA_AMOUNT);
217 END IF;
218 ELSE
219 IF (C_TX_DR_CR = 'DR') THEN
220 RETURN (-C_TX_BA_AMOUNT);
221 ELSE
222 RETURN (C_TX_BA_AMOUNT);
223 END IF;
224 END IF;
225 ELSIF (C_TX_TYPE in ('ROI_LINE','XTR_LINE')) THEN
226 IF (C_SL_TYPE in ('CREDIT','MISC_CREDIT','NSF','REJECTED','SWEEP_IN')) THEN
227 IF (C_TX_DR_CR = 'CASH') THEN
228 RETURN (C_TX_BA_AMOUNT);
229 ELSE
230 RETURN (-C_TX_BA_AMOUNT);
231 END IF;
232 ELSE
233 IF (C_TX_DR_CR = 'CASH') THEN
234 RETURN (-C_TX_BA_AMOUNT);
235 ELSE
236 RETURN (C_TX_BA_AMOUNT);
237 END IF;
238 END IF;
239 RETURN (C_TX_BA_AMOUNT);
240 ELSIF (C_TX_TYPE = 'STATEMENT') THEN
241 IF (C_SL_TYPE = 'MISC_CREDIT') THEN
242 IF (C_TX_DR_CR in ('CR','MD')) THEN
243 RETURN (C_TX_AMOUNT_CLEARED);
244 ELSE
245 RETURN (-C_TX_AMOUNT_CLEARED);
246 END IF;
247 ELSIF (C_SL_TYPE = 'MISC_DEBIT') THEN
248 IF (C_TX_DR_CR in ('DR','MD')) THEN
249 RETURN (C_TX_AMOUNT_CLEARED);
250 ELSE
251 RETURN (-C_TX_AMOUNT_CLEARED);
252 END IF;
253 ELSE
254 RETURN (C_TX_AMOUNT_CLEARED);
255 END IF;
256 ELSIF (C_TX_TYPE = 'PAY') THEN
257 RETURN (C_TX_AMOUNT_CLEARED);
258 ELSIF (C_TX_TYPE = 'PAY_EFT') THEN
259 RETURN (C_TX_AMOUNT_CLEARED);
260 END IF;
261 RETURN NULL;
262 END C_TX_AMOUNT_FORFORMULA;
263
264 FUNCTION C_TX_AMOUNT_CLEARED_FORFORMULA(C_SL_TYPE IN VARCHAR2
265 ,C_SL_AMOUNT_CLEARED IN NUMBER) RETURN NUMBER IS
266 BEGIN
267 IF (C_SL_TYPE in ('MISC_CREDIT','RECEIPT','CREDIT','SWEEP_IN')) THEN
268 IF (C_TRX_REFERENCE_TYPE = 'PAYMENT') THEN
269 RETURN (NVL(-C_SL_AMOUNT_CLEARED
270 ,0));
271 ELSE
272 RETURN (NVL(C_SL_AMOUNT_CLEARED
273 ,0));
274 END IF;
275 ELSIF (C_SL_TYPE in ('MISC_DEBIT','PAYMENT','DEBIT','SWEEP_OUT')) THEN
276 IF (C_TRX_REFERENCE_TYPE = 'RECEIPT') THEN
277 RETURN (NVL(-C_SL_AMOUNT_CLEARED
278 ,0));
279 ELSE
280 RETURN (NVL(C_SL_AMOUNT_CLEARED
281 ,0));
282 END IF;
283 ELSE
284 RETURN (C_SL_AMOUNT_CLEARED);
285 END IF;
286 END C_TX_AMOUNT_CLEARED_FORFORMULA;
287
288 FUNCTION C_TX_BANK_CHARGES_FORFORMULA(C_TX_BANK_CHARGES IN NUMBER) RETURN NUMBER IS
289 BEGIN
290 RETURN (C_TX_BANK_CHARGES);
291 RETURN NULL;
292 END C_TX_BANK_CHARGES_FORFORMULA;
293
294 FUNCTION C_TX_BANK_ERRORS_FORFORMULA(C_TX_BANK_ERRORS IN NUMBER) RETURN NUMBER IS
295 BEGIN
296 RETURN (C_TX_BANK_ERRORS);
297 RETURN NULL;
298 END C_TX_BANK_ERRORS_FORFORMULA;
299
300 FUNCTION C_STAT_SUM_REC_LINEFORMULA(C_STAT_SUM_LINE_AMOUNT IN NUMBER
301 ,C_STAT_UNREC_SUM_LINE IN NUMBER) RETURN NUMBER IS
302 BEGIN
303 RETURN (C_STAT_SUM_LINE_AMOUNT - C_STAT_UNREC_SUM_LINE);
304 END C_STAT_SUM_REC_LINEFORMULA;
305
306 FUNCTION C_SL_REC_DRFORMULA(C_SL_TYPE IN VARCHAR2
307 ,C_STAT_LINE_STATUS IN VARCHAR2
308 ,C_SL_AMOUNT IN NUMBER
309 ,C_SL_UNREC_AMOUNT IN NUMBER
310 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
311 BEGIN
312 IF (C_SL_TYPE = 'DEBIT') THEN
313 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
314 RETURN (C_SL_AMOUNT);
315 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
316 RETURN (0);
317 ELSE
318 RETURN (C_SL_REC_AMOUNT);
319 END IF;
320 ELSE
321 RETURN (0);
322 END IF;
323 RETURN NULL;
324 END C_SL_REC_DRFORMULA;
325
326 FUNCTION C_SL_REC_DR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
327 ,C_STAT_LINE_STATUS IN VARCHAR2
328 ,C_SL_AMOUNT IN NUMBER
329 ,C_SL_UNREC_AMOUNT IN NUMBER
330 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
331 BEGIN
332 IF (C_SL_TYPE = 'MISC_DEBIT') THEN
333 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
334 RETURN (C_SL_AMOUNT);
335 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
336 RETURN (0);
337 ELSE
338 RETURN (C_SL_REC_AMOUNT);
339 END IF;
340 ELSE
341 RETURN (0);
342 END IF;
343 RETURN NULL;
344 END C_SL_REC_DR_MISCFORMULA;
345
346 FUNCTION C_SL_REC_CRFORMULA(C_SL_TYPE IN VARCHAR2
347 ,C_STAT_LINE_STATUS IN VARCHAR2
348 ,C_SL_AMOUNT IN NUMBER
349 ,C_SL_UNREC_AMOUNT IN NUMBER
350 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
351 BEGIN
352 IF (C_SL_TYPE = 'CREDIT') THEN
353 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
354 RETURN (C_SL_AMOUNT);
355 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
356 RETURN (0);
357 ELSE
358 RETURN (C_SL_REC_AMOUNT);
359 END IF;
360 ELSE
361 RETURN (0);
362 END IF;
363 RETURN NULL;
364 END C_SL_REC_CRFORMULA;
365
366 FUNCTION C_SL_REC_CR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
367 ,C_STAT_LINE_STATUS IN VARCHAR2
368 ,C_SL_AMOUNT IN NUMBER
369 ,C_SL_UNREC_AMOUNT IN NUMBER
370 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
371 BEGIN
372 IF (C_SL_TYPE = 'MISC_CREDIT') THEN
373 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
374 RETURN (C_SL_AMOUNT);
375 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
376 RETURN (0);
377 ELSE
378 RETURN (C_SL_REC_AMOUNT);
379 END IF;
380 ELSE
381 RETURN (0);
382 END IF;
383 RETURN NULL;
384 END C_SL_REC_CR_MISCFORMULA;
385
386 FUNCTION C_SL_REC_NSFFORMULA(C_SL_TYPE IN VARCHAR2
387 ,C_STAT_LINE_STATUS IN VARCHAR2
388 ,C_SL_AMOUNT IN NUMBER
389 ,C_SL_UNREC_AMOUNT IN NUMBER
390 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
391 BEGIN
392 IF (C_SL_TYPE = 'NSF') THEN
393 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
394 RETURN (C_SL_AMOUNT);
395 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
396 RETURN (0);
397 ELSE
398 RETURN (C_SL_REC_AMOUNT);
399 END IF;
400 ELSE
401 RETURN (0);
402 END IF;
403 RETURN NULL;
404 END C_SL_REC_NSFFORMULA;
405
406 FUNCTION C_SL_REC_REJECTEDFORMULA(C_SL_TYPE IN VARCHAR2
407 ,C_STAT_LINE_STATUS IN VARCHAR2
408 ,C_SL_AMOUNT IN NUMBER
409 ,C_SL_UNREC_AMOUNT IN NUMBER
410 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
411 BEGIN
412 IF (C_SL_TYPE = 'REJECTED') THEN
413 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
414 RETURN (C_SL_AMOUNT);
415 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
416 RETURN (0);
417 ELSE
418 RETURN (C_SL_REC_AMOUNT);
419 END IF;
420 ELSE
421 RETURN (0);
422 END IF;
423 RETURN NULL;
424 END C_SL_REC_REJECTEDFORMULA;
425
426 FUNCTION C_SL_REC_STOPFORMULA(C_SL_TYPE IN VARCHAR2
427 ,C_STAT_LINE_STATUS IN VARCHAR2
428 ,C_SL_AMOUNT IN NUMBER
429 ,C_SL_UNREC_AMOUNT IN NUMBER
430 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
431 BEGIN
432 IF (C_SL_TYPE = 'STOP') THEN
433 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
434 RETURN (C_SL_AMOUNT);
435 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
436 RETURN (0);
437 ELSE
438 RETURN (C_SL_REC_AMOUNT);
439 END IF;
440 ELSE
441 RETURN (0);
442 END IF;
443 RETURN NULL;
444 END C_SL_REC_STOPFORMULA;
445
446 FUNCTION C_SL_UNREC_DRFORMULA(C_SL_TYPE IN VARCHAR2
447 ,C_STAT_TYPE IN VARCHAR2
448 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
449 BEGIN
450 IF (C_SL_TYPE = 'DEBIT') THEN
451 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
452 RETURN (0);
453 ELSE
454 RETURN (C_SL_UNREC_AMOUNT);
455 END IF;
456 ELSE
457 RETURN (0);
458 END IF;
459 RETURN NULL;
460 END C_SL_UNREC_DRFORMULA;
461
462 FUNCTION C_SL_UNREC_DR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
463 ,C_STAT_TYPE IN VARCHAR2
464 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
465 BEGIN
466 IF (C_SL_TYPE = 'MISC_DEBIT') THEN
467 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
468 RETURN (0);
469 ELSE
470 RETURN (C_SL_UNREC_AMOUNT);
471 END IF;
472 ELSE
473 RETURN (0);
474 END IF;
475 RETURN NULL;
476 END C_SL_UNREC_DR_MISCFORMULA;
477
478 FUNCTION C_SL_UNREC_CRFORMULA(C_SL_TYPE IN VARCHAR2
479 ,C_STAT_TYPE IN VARCHAR2
480 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
481 BEGIN
482 IF (C_SL_TYPE = 'CREDIT') THEN
483 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
484 RETURN (0);
485 ELSE
486 RETURN (C_SL_UNREC_AMOUNT);
487 END IF;
488 ELSE
489 RETURN (0);
490 END IF;
491 RETURN NULL;
492 END C_SL_UNREC_CRFORMULA;
493
494 FUNCTION C_SL_UNREC_CR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
495 ,C_STAT_TYPE IN VARCHAR2
496 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
497 BEGIN
498 IF (C_SL_TYPE = 'MISC_CREDIT') THEN
499 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
500 RETURN (0);
501 ELSE
502 RETURN (C_SL_UNREC_AMOUNT);
503 END IF;
504 ELSE
505 RETURN (0);
506 END IF;
507 RETURN NULL;
508 END C_SL_UNREC_CR_MISCFORMULA;
509
510 FUNCTION C_SL_UNREC_NSFFORMULA(C_SL_TYPE IN VARCHAR2
511 ,C_STAT_TYPE IN VARCHAR2
512 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
513 BEGIN
514 IF (C_SL_TYPE = 'NSF') THEN
515 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
516 RETURN (0);
517 ELSE
518 RETURN (C_SL_UNREC_AMOUNT);
519 END IF;
520 ELSE
521 RETURN (0);
522 END IF;
523 RETURN NULL;
524 END C_SL_UNREC_NSFFORMULA;
525
526 FUNCTION C_SL_UNREC_REJECTEDFORMULA(C_SL_TYPE IN VARCHAR2
527 ,C_STAT_TYPE IN VARCHAR2
528 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
529 BEGIN
530 IF (C_SL_TYPE = 'REJECTED') THEN
531 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
532 RETURN (0);
533 ELSE
534 RETURN (C_SL_UNREC_AMOUNT);
535 END IF;
536 ELSE
537 RETURN (0);
538 END IF;
539 RETURN NULL;
540 END C_SL_UNREC_REJECTEDFORMULA;
541
542 FUNCTION C_SL_UNREC_STOPFORMULA(C_SL_TYPE IN VARCHAR2
543 ,C_STAT_TYPE IN VARCHAR2
544 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
545 BEGIN
546 IF (C_SL_TYPE = 'STOP') THEN
547 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
548 RETURN (0);
549 ELSE
550 RETURN (C_SL_UNREC_AMOUNT);
551 END IF;
552 ELSE
553 RETURN (0);
554 END IF;
555 RETURN NULL;
556 END C_SL_UNREC_STOPFORMULA;
557
558 FUNCTION C_TX_SIGN_AMOUNT_FORFORMULA(C_TX_TYPE IN VARCHAR2
559 ,C_TX_SIGN_BA_AMOUNT IN NUMBER) RETURN NUMBER IS
560 BEGIN
561 IF (C_TX_TYPE = 'PAYMENT') THEN
562 RETURN (C_TX_SIGN_BA_AMOUNT);
563 ELSIF (C_TX_TYPE = 'STATEMENT') THEN
564 RETURN (0);
565 ELSIF (C_TX_TYPE = 'PAY') THEN
566 RETURN (C_TX_SIGN_BA_AMOUNT);
567 ELSIF (C_TX_TYPE = 'PAY_EFT') THEN
568 RETURN (C_TX_SIGN_BA_AMOUNT);
569 ELSE
570 RETURN (C_TX_SIGN_BA_AMOUNT);
571 END IF;
572 RETURN NULL;
573 END C_TX_SIGN_AMOUNT_FORFORMULA;
574
575 FUNCTION C_TX_SIGN_AMOUNT_CLEARED_FORFO(C_TX_TYPE IN VARCHAR2
576 ,C_SL_AMOUNT_CLEARED IN NUMBER
577 ,C_TX_SIGN_AMOUNT_CLEARED IN NUMBER
578 ,C_TX_SIGN_BA_AMOUNT IN NUMBER
579 ,C_TX_DR_CR IN VARCHAR2) RETURN NUMBER IS
580 BEGIN
581 IF (C_TX_TYPE = 'PAYMENT') THEN
582 RETURN (-C_SL_AMOUNT_CLEARED);
583 ELSIF (C_TX_TYPE = 'RECEIPT') THEN
584 RETURN (C_TX_SIGN_AMOUNT_CLEARED);
585 ELSIF (C_TX_TYPE = 'JE_LINE') THEN
586 RETURN (C_TX_SIGN_BA_AMOUNT);
587 ELSIF (C_TX_TYPE = 'ROI_LINE') THEN
588 IF (C_TX_DR_CR = 'PAYMENT') THEN
589 RETURN (NVL(-C_SL_AMOUNT_CLEARED
590 ,0));
591 ELSE
592 RETURN (NVL(C_SL_AMOUNT_CLEARED
593 ,0));
594 END IF;
595 ELSIF (C_TX_TYPE = 'XTR_LINE') THEN
596 RETURN (C_TX_SIGN_BA_AMOUNT);
597 ELSIF (C_TX_TYPE = 'STATEMENT') THEN
598 RETURN (0);
599 ELSIF (C_TX_TYPE = 'PAY') THEN
600 RETURN (C_TX_SIGN_BA_AMOUNT);
601 ELSIF (C_TX_TYPE = 'PAY_EFT') THEN
602 RETURN (C_TX_SIGN_BA_AMOUNT);
603 END IF;
604 RETURN NULL;
605 END C_TX_SIGN_AMOUNT_CLEARED_FORFO;
606
607 FUNCTION C_SL_UNREC_SIGNED_AMOUNTFORMUL(C_SL_SIGNED_AMOUNT IN NUMBER
608 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
609 BEGIN
610 RETURN (C_SL_SIGNED_AMOUNT - C_SL_REC_AMOUNT);
611 END C_SL_UNREC_SIGNED_AMOUNTFORMUL;
612
613 FUNCTION C_TX_AMOUNT_FOR2FORMULA(C_TX_TYPE IN VARCHAR2
614 ,C_TX_BA_AMOUNT IN NUMBER
615 ,C_TX_AMOUNT_CLEARED IN NUMBER
616 ,C_SL_TYPE IN VARCHAR2) RETURN NUMBER IS
617 BEGIN
618 IF (C_TX_TYPE = 'PAYMENT') THEN
619 RETURN (C_TX_BA_AMOUNT);
620 ELSIF (C_TX_TYPE = 'STATEMENT') THEN
621 RETURN (C_TX_AMOUNT_CLEARED);
622 ELSIF (C_SL_TYPE = 'MISC_DEBIT') THEN
623 RETURN (-C_TX_BA_AMOUNT);
624 END IF;
625 RETURN (C_TX_BA_AMOUNT);
626 END C_TX_AMOUNT_FOR2FORMULA;
627
628 FUNCTION C_DATEFORMATFORMULA(C_DATEFORMAT IN VARCHAR2) RETURN VARCHAR2 IS
629 BEGIN
630 RETURN (FND_GLOBAL.NLS_DATE_FORMAT);
631 END C_DATEFORMATFORMULA;
632
633 FUNCTION C_SL_OVERREC_CRFORMULA(C_SL_TYPE IN VARCHAR2
634 ,C_STAT_TYPE IN VARCHAR2
635 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
636 BEGIN
637 IF (C_SL_TYPE = 'CREDIT') THEN
638 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
639 RETURN (-C_SL_UNREC_AMOUNT);
640 ELSE
641 RETURN (0);
642 END IF;
643 ELSE
644 RETURN (0);
645 END IF;
646 RETURN NULL;
647 END C_SL_OVERREC_CRFORMULA;
648
649 FUNCTION C_SL_OVERREC_CR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
650 ,C_STAT_TYPE IN VARCHAR2
651 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
652 BEGIN
653 IF (C_SL_TYPE = 'MISC_CREDIT') THEN
654 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
655 RETURN (-C_SL_UNREC_AMOUNT);
656 ELSE
657 RETURN (0);
658 END IF;
659 ELSE
660 RETURN (0);
661 END IF;
662 RETURN NULL;
663 END C_SL_OVERREC_CR_MISCFORMULA;
664
665 FUNCTION C_SL_OVERREC_DRFORMULA(C_SL_TYPE IN VARCHAR2
666 ,C_STAT_TYPE IN VARCHAR2
667 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
668 BEGIN
669 IF (C_SL_TYPE = 'DEBIT') THEN
670 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
671 RETURN (-C_SL_UNREC_AMOUNT);
672 ELSE
673 RETURN (0);
674 END IF;
675 ELSE
676 RETURN (0);
677 END IF;
678 RETURN NULL;
679 END C_SL_OVERREC_DRFORMULA;
680
681 FUNCTION C_SL_OVERREC_DR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
682 ,C_STAT_TYPE IN VARCHAR2
683 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
684 BEGIN
685 IF (C_SL_TYPE = 'MISC_DEBIT') THEN
686 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
687 RETURN (-C_SL_UNREC_AMOUNT);
688 ELSE
689 RETURN (0);
690 END IF;
691 ELSE
692 RETURN (0);
693 END IF;
694 RETURN NULL;
695 END C_SL_OVERREC_DR_MISCFORMULA;
696
697 FUNCTION C_SL_OVERREC_NSFFORMULA(C_SL_TYPE IN VARCHAR2
698 ,C_STAT_TYPE IN VARCHAR2
699 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
700 BEGIN
701 IF (C_SL_TYPE = 'NSF') THEN
702 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
703 RETURN (-C_SL_UNREC_AMOUNT);
704 ELSE
705 RETURN (0);
706 END IF;
707 ELSE
708 RETURN (0);
709 END IF;
710 RETURN NULL;
711 END C_SL_OVERREC_NSFFORMULA;
712
713 FUNCTION C_SL_OVERREC_REJECTEDFORMULA(C_SL_TYPE IN VARCHAR2
714 ,C_STAT_TYPE IN VARCHAR2
715 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
716 BEGIN
717 IF (C_SL_TYPE = 'REJECTED') THEN
718 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
719 RETURN (-C_SL_UNREC_AMOUNT);
720 ELSE
721 RETURN (0);
722 END IF;
723 ELSE
724 RETURN (0);
725 END IF;
726 RETURN NULL;
727 END C_SL_OVERREC_REJECTEDFORMULA;
728
729 FUNCTION C_SL_OVERREC_STOPFORMULA(C_SL_TYPE IN VARCHAR2
730 ,C_STAT_TYPE IN VARCHAR2
731 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
732 BEGIN
733 IF (C_SL_TYPE = 'STOP') THEN
734 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
735 RETURN (-C_SL_UNREC_AMOUNT);
736 ELSE
737 RETURN (0);
738 END IF;
739 ELSE
740 RETURN (0);
741 END IF;
742 RETURN NULL;
743 END C_SL_OVERREC_STOPFORMULA;
744
745 FUNCTION C_SL_UNREC_POS_AMOUNTFORMULA(C_STAT_LINE_STATUS IN VARCHAR2
746 ,C_SL_AMOUNT IN NUMBER
747 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
748 BEGIN
749 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
750 RETURN (0);
751 ELSIF (C_SL_AMOUNT - C_SL_REC_AMOUNT >= 0) THEN
752 RETURN (C_SL_AMOUNT - C_SL_REC_AMOUNT);
753 ELSE
754 RETURN (C_SL_REC_AMOUNT - C_SL_AMOUNT);
755 END IF;
756 RETURN NULL;
757 END C_SL_UNREC_POS_AMOUNTFORMULA;
758
759 FUNCTION C_SL_DR2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
760 ,C_SL_DR IN NUMBER) RETURN NUMBER IS
761 BEGIN
762 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
763 RETURN (0);
764 ELSE
765 RETURN (C_SL_DR);
766 END IF;
767 RETURN NULL;
768 END C_SL_DR2FORMULA;
769
770 FUNCTION C_SL_MISC_DR2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
771 ,C_SL_MISC_DR IN NUMBER) RETURN NUMBER IS
772 BEGIN
773 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
774 RETURN (0);
775 ELSE
776 RETURN (C_SL_MISC_DR);
777 END IF;
778 RETURN NULL;
779 END C_SL_MISC_DR2FORMULA;
780
781 FUNCTION C_SL_CR2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
782 ,C_SL_CR IN NUMBER) RETURN NUMBER IS
783 BEGIN
784 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
785 RETURN (0);
786 ELSE
787 RETURN (C_SL_CR);
788 END IF;
789 RETURN NULL;
790 END C_SL_CR2FORMULA;
791
792 FUNCTION C_SL_MISC_CR2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
793 ,C_SL_MISC_CR IN NUMBER) RETURN NUMBER IS
794 BEGIN
795 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
796 RETURN (0);
797 ELSE
798 RETURN (C_SL_MISC_CR);
799 END IF;
800 RETURN NULL;
801 END C_SL_MISC_CR2FORMULA;
802
803 FUNCTION C_SL_NSF2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
804 ,C_SL_NSF IN NUMBER) RETURN NUMBER IS
805 BEGIN
806 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
807 RETURN (0);
808 ELSE
809 RETURN (C_SL_NSF);
810 END IF;
811 RETURN NULL;
812 END C_SL_NSF2FORMULA;
813
814 FUNCTION C_SL_REJECTED2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
815 ,C_SL_REJECTED IN NUMBER) RETURN NUMBER IS
816 BEGIN
817 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
818 RETURN (0);
819 ELSE
820 RETURN (C_SL_REJECTED);
821 END IF;
822 RETURN NULL;
823 END C_SL_REJECTED2FORMULA;
824
825 FUNCTION C_SL_STOP2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
826 ,C_SL_STOP IN NUMBER) RETURN NUMBER IS
827 BEGIN
828 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
829 RETURN (0);
830 ELSE
831 RETURN (C_SL_STOP);
832 END IF;
833 RETURN NULL;
834 END C_SL_STOP2FORMULA;
835
836 FUNCTION C_TX_SIGN_SL_UNREC_AMOUNTFORMU(C_SL_TYPE IN VARCHAR2
837 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
838 BEGIN
839 IF (C_SL_TYPE in ('DEBIT','MISC_DEBIT','NSF','REJECTED','SWEEP_OUT')) THEN
840 RETURN (-C_SL_UNREC_AMOUNT);
841 ELSE
842 RETURN (C_SL_UNREC_AMOUNT);
843 END IF;
844 RETURN (C_SL_UNREC_AMOUNT);
845 END C_TX_SIGN_SL_UNREC_AMOUNTFORMU;
846
847 FUNCTION C_SL_SWP_IN2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
848 ,C_SL_SWP_IN IN NUMBER) RETURN NUMBER IS
849 BEGIN
850 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
851 RETURN (0);
852 ELSE
853 RETURN (C_SL_SWP_IN);
854 END IF;
855 END C_SL_SWP_IN2FORMULA;
856
857 FUNCTION C_SL_REC_SWP_INFORMULA(C_SL_TYPE IN VARCHAR2
858 ,C_STAT_LINE_STATUS IN VARCHAR2
859 ,C_SL_AMOUNT IN NUMBER
860 ,C_SL_UNREC_AMOUNT IN NUMBER
861 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
862 BEGIN
863 IF (C_SL_TYPE = 'SWEEP_IN') THEN
864 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
865 RETURN (C_SL_AMOUNT);
866 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
867 RETURN (0);
868 ELSE
869 RETURN (C_SL_REC_AMOUNT);
870 END IF;
871 ELSE
872 RETURN (0);
873 END IF;
874 END C_SL_REC_SWP_INFORMULA;
875
876 FUNCTION C_SL_UNREC_SWP_INFORMULA(C_SL_TYPE IN VARCHAR2
877 ,C_STAT_TYPE IN VARCHAR2
878 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
879 BEGIN
880 IF (C_SL_TYPE = 'SWEEP_IN') THEN
881 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
882 RETURN (0);
883 ELSE
884 RETURN (C_SL_UNREC_AMOUNT);
885 END IF;
886 ELSE
887 RETURN (0);
888 END IF;
889 END C_SL_UNREC_SWP_INFORMULA;
890
891 FUNCTION C_SL_OVERREC_SWP_INFORMULA(C_SL_TYPE IN VARCHAR2
892 ,C_STAT_TYPE IN VARCHAR2
893 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
894 BEGIN
895 IF (C_SL_TYPE = 'SWEEP_IN') THEN
896 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
897 RETURN (-C_SL_UNREC_AMOUNT);
898 ELSE
899 RETURN (0);
900 END IF;
901 ELSE
902 RETURN (0);
903 END IF;
904 RETURN NULL;
905 END C_SL_OVERREC_SWP_INFORMULA;
906
907 FUNCTION C_SL_SWP_OUT2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
908 ,C_SL_SWP_OUT IN NUMBER) RETURN NUMBER IS
909 BEGIN
910 IF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
911 RETURN (0);
912 ELSE
913 RETURN (C_SL_SWP_OUT);
914 END IF;
915 END C_SL_SWP_OUT2FORMULA;
916
917 FUNCTION C_SL_REC_SWP_OUTFORMULA(C_SL_TYPE IN VARCHAR2
918 ,C_STAT_LINE_STATUS IN VARCHAR2
919 ,C_SL_AMOUNT IN NUMBER
920 ,C_SL_UNREC_AMOUNT IN NUMBER
921 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER IS
922 BEGIN
923 IF (C_SL_TYPE = 'SWEEP_OUT') THEN
924 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
925 RETURN (C_SL_AMOUNT);
926 ELSIF (P_STMT_LINE_STATUS = 'UNRECONCILED' AND C_SL_UNREC_AMOUNT = 0) THEN
927 RETURN (0);
928 ELSE
929 RETURN (C_SL_REC_AMOUNT);
930 END IF;
931 ELSE
932 RETURN (0);
933 END IF;
934 END C_SL_REC_SWP_OUTFORMULA;
935
936 FUNCTION C_SL_UNREC_SWP_OUTFORMULA(C_SL_TYPE IN VARCHAR2
937 ,C_STAT_TYPE IN VARCHAR2
938 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
939 BEGIN
940 IF (C_SL_TYPE = 'SWEEP_OUT') THEN
941 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
942 RETURN (0);
943 ELSE
944 RETURN (C_SL_UNREC_AMOUNT);
945 END IF;
946 ELSE
947 RETURN (0);
948 END IF;
949 RETURN NULL;
950 END C_SL_UNREC_SWP_OUTFORMULA;
951
952 FUNCTION C_SL_OVERREC_SWP_OUTFORMULA(C_SL_TYPE IN VARCHAR2
953 ,C_STAT_TYPE IN VARCHAR2
954 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER IS
955 BEGIN
956 IF (C_SL_TYPE = 'SWEEP_OUT') THEN
957 IF (C_STAT_TYPE = 'RECONCILED' AND C_SL_UNREC_AMOUNT < 0) THEN
958 RETURN (-C_SL_UNREC_AMOUNT);
959 ELSE
960 RETURN (0);
961 END IF;
962 ELSE
963 RETURN (0);
964 END IF;
965 RETURN NULL;
966 END C_SL_OVERREC_SWP_OUTFORMULA;
967
968 FUNCTION C_STAT_DATE_SQL_LEX_P RETURN VARCHAR2 IS
969 BEGIN
970 RETURN C_STAT_DATE_SQL_LEX;
971 END C_STAT_DATE_SQL_LEX_P;
972
973 FUNCTION C_BANK_NAME_DSP_P RETURN VARCHAR2 IS
974 BEGIN
975 RETURN C_BANK_NAME_DSP;
976 END C_BANK_NAME_DSP_P;
977
978 FUNCTION C_ACCOUNT_NAME_DSP_P RETURN VARCHAR2 IS
979 BEGIN
980 RETURN C_ACCOUNT_NAME_DSP;
981 END C_ACCOUNT_NAME_DSP_P;
982
983 FUNCTION C_BANK_BRANCH_DSP_P RETURN VARCHAR2 IS
984 BEGIN
985 RETURN C_BANK_BRANCH_DSP;
986 END C_BANK_BRANCH_DSP_P;
987
988 FUNCTION C_EXTERNAL_TRANSLATION_P RETURN VARCHAR2 IS
989 BEGIN
990 RETURN C_EXTERNAL_TRANSLATION;
991 END C_EXTERNAL_TRANSLATION_P;
992
993 FUNCTION C_ALL_TRANSLATION_P RETURN VARCHAR2 IS
994 BEGIN
995 RETURN C_ALL_TRANSLATION;
996 END C_ALL_TRANSLATION_P;
997
998 FUNCTION C_STAT_NUMBER_SQL_LEX_P RETURN VARCHAR2 IS
999 BEGIN
1000 RETURN C_STAT_NUMBER_SQL_LEX;
1001 END C_STAT_NUMBER_SQL_LEX_P;
1002
1003 FUNCTION C_THE_END_P RETURN VARCHAR2 IS
1004 BEGIN
1005 RETURN C_THE_END;
1006 END C_THE_END_P;
1007
1008 FUNCTION C_GL_CURRENCY_CODE_P RETURN VARCHAR2 IS
1009 BEGIN
1010 RETURN C_GL_CURRENCY_CODE;
1011 END C_GL_CURRENCY_CODE_P;
1012
1013 FUNCTION C_NAME_P RETURN VARCHAR2 IS
1014 BEGIN
1015 RETURN C_NAME;
1016 END C_NAME_P;
1017
1018 FUNCTION C_SET_OF_BOOKS_ID_P RETURN NUMBER IS
1019 BEGIN
1020 RETURN C_SET_OF_BOOKS_ID;
1021 END C_SET_OF_BOOKS_ID_P;
1022
1023 FUNCTION C_BANK_CURR_DSP_P RETURN VARCHAR2 IS
1024 BEGIN
1025 RETURN C_BANK_CURR_DSP;
1026 END C_BANK_CURR_DSP_P;
1027
1028 FUNCTION C_ACCOUNT_NUMBER_DSP_P RETURN VARCHAR2 IS
1029 BEGIN
1030 RETURN C_ACCOUNT_NUMBER_DSP;
1031 END C_ACCOUNT_NUMBER_DSP_P;
1032
1033 FUNCTION C_DISPLAY_MESSAGE_P RETURN VARCHAR2 IS
1034 BEGIN
1035 RETURN C_DISPLAY_MESSAGE;
1036 END C_DISPLAY_MESSAGE_P;
1037
1038 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
1039 ,NAME IN VARCHAR2) IS
1040 BEGIN
1041 begin FND_MESSAGE.SET_NAME(APPLICATION, NAME); end;
1042 --STPROC.BIND_I(APPLICATION);
1043 --STPROC.BIND_I(NAME);
1044 --STPROC.EXECUTE;
1045 END SET_NAME;
1046 /*
1047 PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
1048 ,VALUE IN VARCHAR2
1049 ,TRANSLATE IN BOOLEAN) IS
1050 BEGIN
1051 STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
1052 STPROC.BIND_I(TRANSLATE);
1053 STPROC.BIND_I(TOKEN);
1054 STPROC.BIND_I(VALUE);
1055 STPROC.EXECUTE;
1056 END SET_TOKEN;
1057
1058 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
1059 BEGIN
1060 STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
1061 STPROC.BIND_O(MSGOUT);
1062 STPROC.EXECUTE;
1063 STPROC.RETRIEVE(1
1064 ,MSGOUT);
1065 END RETRIEVE;
1066
1067 PROCEDURE CLEAR IS
1068 BEGIN
1069 STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
1070 STPROC.EXECUTE;
1071 END CLEAR;
1072
1073 FUNCTION GET_STRING(APPIN IN VARCHAR2
1074 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
1075 X0 VARCHAR2(2000);
1076 BEGIN
1077 STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
1078 STPROC.BIND_O(X0);
1079 STPROC.BIND_I(APPIN);
1080 STPROC.BIND_I(NAMEIN);
1081 STPROC.EXECUTE;
1082 STPROC.RETRIEVE(1
1083 ,X0);
1084 RETURN X0;
1085 END GET_STRING;*/
1086
1087 FUNCTION GET RETURN VARCHAR2 IS
1088 X0 VARCHAR2(2000);
1089 BEGIN
1090 begin X0 := FND_MESSAGE.GET; end;
1091 --STPROC.BIND_O(X0);
1092 --STPROC.EXECUTE;
1093 --STPROC.RETRIEVE(1 ,X0);
1094 RETURN X0;
1095 END GET;
1096
1097 /* FUNCTION GET_ENCODED RETURN VARCHAR2 IS
1098 X0 VARCHAR2(2000);
1099 BEGIN
1100 STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
1101 STPROC.BIND_O(X0);
1102 STPROC.EXECUTE;
1103 STPROC.RETRIEVE(1
1104 ,X0);
1105 RETURN X0;
1106 END GET_ENCODED;
1107
1108 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
1109 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
1110 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
1111 BEGIN
1112 STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
1113 STPROC.BIND_I(ENCODED_MESSAGE);
1114 STPROC.BIND_O(APP_SHORT_NAME);
1115 STPROC.BIND_O(MESSAGE_NAME);
1116 STPROC.EXECUTE;
1117 STPROC.RETRIEVE(2
1118 ,APP_SHORT_NAME);
1119 STPROC.RETRIEVE(3
1120 ,MESSAGE_NAME);
1121 END PARSE_ENCODED;
1122
1123 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
1124 BEGIN
1125 STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
1126 STPROC.BIND_I(ENCODED_MESSAGE);
1127 STPROC.EXECUTE;
1128 END SET_ENCODED;
1129
1130 PROCEDURE RAISE_ERROR IS
1131 BEGIN
1132 STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
1133 STPROC.EXECUTE;
1134 END RAISE_ERROR;
1135
1136 PROCEDURE DEBUG(LINE IN VARCHAR2) IS
1137 BEGIN
1138 STPROC.INIT('begin CEP_STANDARD.DEBUG(:LINE); end;');
1139 STPROC.BIND_I(LINE);
1140 STPROC.EXECUTE;
1141 END DEBUG;
1142
1143 PROCEDURE ENABLE_DEBUG IS
1144 BEGIN
1145 STPROC.INIT('begin CEP_STANDARD.ENABLE_DEBUG; end;');
1146 STPROC.EXECUTE;
1147 END ENABLE_DEBUG;
1148
1149 PROCEDURE DISABLE_DEBUG IS
1150 BEGIN
1151 STPROC.INIT('begin CEP_STANDARD.DISABLE_DEBUG; end;');
1152 STPROC.EXECUTE;
1153 END DISABLE_DEBUG;*/
1154
1155 PROCEDURE INIT_SECURITY IS
1156 BEGIN
1157 begin CEP_STANDARD.init_security; end;
1158 --STPROC.EXECUTE;
1159 END INIT_SECURITY;
1160
1161 /* FUNCTION GET_WINDOW_SESSION_TITLE RETURN VARCHAR2 IS
1162 X0 VARCHAR2(2000);
1163 BEGIN
1164 STPROC.INIT('begin :X0 := CEP_STANDARD.GET_WINDOW_SESSION_TITLE; end;');
1165 STPROC.BIND_O(X0);
1166 STPROC.EXECUTE;
1167 STPROC.RETRIEVE(1
1168 ,X0);
1169 RETURN X0;
1170 END GET_WINDOW_SESSION_TITLE;
1171
1172 FUNCTION GET_EFFECTIVE_DATE(P_BANK_ACCOUNT_ID IN NUMBER
1173 ,P_TRX_CODE IN VARCHAR2
1174 ,P_RECEIPT_DATE IN DATE) RETURN DATE IS
1175 X0 DATE;
1176 BEGIN
1177 STPROC.INIT('begin :X0 := CEP_STANDARD.GET_EFFECTIVE_DATE(:P_BANK_ACCOUNT_ID, :P_TRX_CODE, :P_RECEIPT_DATE); end;');
1178 STPROC.BIND_O(X0);
1179 STPROC.BIND_I(P_BANK_ACCOUNT_ID);
1180 STPROC.BIND_I(P_TRX_CODE);
1181 STPROC.BIND_I(P_RECEIPT_DATE);
1182 STPROC.EXECUTE;
1183 STPROC.RETRIEVE(1
1184 ,X0);
1185 RETURN X0;
1186 END GET_EFFECTIVE_DATE;*/
1187
1188 END CE_CEXSTMRR_XMLP_PKG;
1189
[Home] [Help]