1 PACKAGE CE_CEXSTMRR_XMLP_PKG AUTHID CURRENT_USER AS
2 /* $Header: CEXSTMRRS.pls 120.0 2007/12/28 07:57:15 abraghun noship $ */
3 P_CONC_REQUEST_ID NUMBER := 0;
4
5 P_SQL_TRACE VARCHAR2(32767);
6
7 P_TEST_LAYOUT VARCHAR2(30);
8
9 P_DEBUG_MODE VARCHAR2(32767);
10
11 P_STAT_DATE_FROM DATE;
12 P_STAT_DATE_FROM_T varchar2(44);
13
14 P_STAT_DATE_TO DATE;
15 P_STAT_DATE_TO_T varchar2(44);
16
17 P_BANK_ACCOUNT_ID NUMBER;
18
19 P_STATEMENT_OR_ACCOUNT VARCHAR2(15);
20
21 P_STAT_NUMBER_FROM VARCHAR2(50);
22
23 P_STAT_NUMBER_TO VARCHAR2(50);
24
25 P_STMT_LINE_STATUS VARCHAR2(32767);
26
27 C_TRX_REFERENCE_TYPE VARCHAR2(40);
28
29 C_STAT_DATE_SQL_LEX VARCHAR2(200) := '1=1';
30
31 C_BANK_NAME_DSP VARCHAR2(100);
32
33 C_ACCOUNT_NAME_DSP VARCHAR2(100);
34
35 C_BANK_BRANCH_DSP VARCHAR2(100);
36
37 C_EXTERNAL_TRANSLATION VARCHAR2(80);
38
39 C_ALL_TRANSLATION VARCHAR2(80);
40
41 C_STAT_NUMBER_SQL_LEX VARCHAR2(500) := '1=1';
42
43 C_THE_END VARCHAR2(1) := 'N';
44
45 C_GL_CURRENCY_CODE VARCHAR2(15);
46
47 C_NAME VARCHAR2(80);
48
49 C_SET_OF_BOOKS_ID NUMBER;
50
51 C_BANK_CURR_DSP VARCHAR2(80);
52
53 C_ACCOUNT_NUMBER_DSP VARCHAR2(100);
54
55 C_DISPLAY_MESSAGE VARCHAR2(1000);
56
57 FUNCTION BEFOREREPORT RETURN BOOLEAN;
58
59 FUNCTION AFTERREPORT RETURN BOOLEAN;
60
61 FUNCTION C_STAT_SUM_LINE_AMOUNTFORMULA(C_STAT_HEADER_ID1 IN NUMBER) RETURN NUMBER;
62
63 FUNCTION C_STAT_CR_ERRORSFORMULA(C_STAT_HEADER_ID1 IN NUMBER) RETURN NUMBER;
64
65 FUNCTION C_STAT_DR_ERRORSFORMULA(C_STAT_HEADER_ID1 IN NUMBER) RETURN NUMBER;
66
67 FUNCTION G_BANK_ACCOUNTGROUPFILTER(C_BANK_ACCOUNT_NAME IN VARCHAR2) RETURN BOOLEAN;
68
69 FUNCTION C_TX_AMOUNT_FORFORMULA(C_TX_TYPE IN VARCHAR2
70 ,C_SL_TYPE IN VARCHAR2
71 ,C_TX_BA_AMOUNT IN NUMBER
72 ,C_TX_DR_CR IN VARCHAR2
73 ,C_TX_AMOUNT_CLEARED IN NUMBER) RETURN NUMBER;
74
75 FUNCTION C_TX_AMOUNT_CLEARED_FORFORMULA(C_SL_TYPE IN VARCHAR2
76 ,C_SL_AMOUNT_CLEARED IN NUMBER) RETURN NUMBER;
77
78 FUNCTION C_TX_BANK_CHARGES_FORFORMULA(C_TX_BANK_CHARGES IN NUMBER) RETURN NUMBER;
79
80 FUNCTION C_TX_BANK_ERRORS_FORFORMULA(C_TX_BANK_ERRORS IN NUMBER) RETURN NUMBER;
81
82 FUNCTION C_STAT_SUM_REC_LINEFORMULA(C_STAT_SUM_LINE_AMOUNT IN NUMBER
83 ,C_STAT_UNREC_SUM_LINE IN NUMBER) RETURN NUMBER;
84
85 FUNCTION C_SL_REC_DRFORMULA(C_SL_TYPE IN VARCHAR2
86 ,C_STAT_LINE_STATUS IN VARCHAR2
87 ,C_SL_AMOUNT IN NUMBER
88 ,C_SL_UNREC_AMOUNT IN NUMBER
89 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
90
91 FUNCTION C_SL_REC_DR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
92 ,C_STAT_LINE_STATUS IN VARCHAR2
93 ,C_SL_AMOUNT IN NUMBER
94 ,C_SL_UNREC_AMOUNT IN NUMBER
95 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
96
97 FUNCTION C_SL_REC_CRFORMULA(C_SL_TYPE IN VARCHAR2
98 ,C_STAT_LINE_STATUS IN VARCHAR2
99 ,C_SL_AMOUNT IN NUMBER
100 ,C_SL_UNREC_AMOUNT IN NUMBER
101 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
102
103 FUNCTION C_SL_REC_CR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
104 ,C_STAT_LINE_STATUS IN VARCHAR2
105 ,C_SL_AMOUNT IN NUMBER
106 ,C_SL_UNREC_AMOUNT IN NUMBER
107 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
108
109 FUNCTION C_SL_REC_NSFFORMULA(C_SL_TYPE IN VARCHAR2
110 ,C_STAT_LINE_STATUS IN VARCHAR2
111 ,C_SL_AMOUNT IN NUMBER
112 ,C_SL_UNREC_AMOUNT IN NUMBER
113 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
114
115 FUNCTION C_SL_REC_REJECTEDFORMULA(C_SL_TYPE IN VARCHAR2
116 ,C_STAT_LINE_STATUS IN VARCHAR2
117 ,C_SL_AMOUNT IN NUMBER
118 ,C_SL_UNREC_AMOUNT IN NUMBER
119 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
120
121 FUNCTION C_SL_REC_STOPFORMULA(C_SL_TYPE IN VARCHAR2
122 ,C_STAT_LINE_STATUS IN VARCHAR2
123 ,C_SL_AMOUNT IN NUMBER
124 ,C_SL_UNREC_AMOUNT IN NUMBER
125 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
126
127 FUNCTION C_SL_UNREC_DRFORMULA(C_SL_TYPE IN VARCHAR2
128 ,C_STAT_TYPE IN VARCHAR2
129 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
130
131 FUNCTION C_SL_UNREC_DR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
132 ,C_STAT_TYPE IN VARCHAR2
133 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
134
135 FUNCTION C_SL_UNREC_CRFORMULA(C_SL_TYPE IN VARCHAR2
136 ,C_STAT_TYPE IN VARCHAR2
137 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
138
139 FUNCTION C_SL_UNREC_CR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
140 ,C_STAT_TYPE IN VARCHAR2
141 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
142
143 FUNCTION C_SL_UNREC_NSFFORMULA(C_SL_TYPE IN VARCHAR2
144 ,C_STAT_TYPE IN VARCHAR2
145 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
146
147 FUNCTION C_SL_UNREC_REJECTEDFORMULA(C_SL_TYPE IN VARCHAR2
148 ,C_STAT_TYPE IN VARCHAR2
149 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
150
151 FUNCTION C_SL_UNREC_STOPFORMULA(C_SL_TYPE IN VARCHAR2
152 ,C_STAT_TYPE IN VARCHAR2
153 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
154
155 FUNCTION C_TX_SIGN_AMOUNT_FORFORMULA(C_TX_TYPE IN VARCHAR2
156 ,C_TX_SIGN_BA_AMOUNT IN NUMBER) RETURN NUMBER;
157
158 FUNCTION C_TX_SIGN_AMOUNT_CLEARED_FORFO(C_TX_TYPE IN VARCHAR2
159 ,C_SL_AMOUNT_CLEARED IN NUMBER
160 ,C_TX_SIGN_AMOUNT_CLEARED IN NUMBER
161 ,C_TX_SIGN_BA_AMOUNT IN NUMBER
162 ,C_TX_DR_CR IN VARCHAR2) RETURN NUMBER;
163
164 FUNCTION C_SL_UNREC_SIGNED_AMOUNTFORMUL(C_SL_SIGNED_AMOUNT IN NUMBER
165 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
166
167 FUNCTION C_TX_AMOUNT_FOR2FORMULA(C_TX_TYPE IN VARCHAR2
168 ,C_TX_BA_AMOUNT IN NUMBER
169 ,C_TX_AMOUNT_CLEARED IN NUMBER
170 ,C_SL_TYPE IN VARCHAR2) RETURN NUMBER;
171
172 FUNCTION C_DATEFORMATFORMULA(C_DATEFORMAT IN VARCHAR2) RETURN VARCHAR2;
173
174 FUNCTION C_SL_OVERREC_CRFORMULA(C_SL_TYPE IN VARCHAR2
175 ,C_STAT_TYPE IN VARCHAR2
176 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
177
178 FUNCTION C_SL_OVERREC_CR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
179 ,C_STAT_TYPE IN VARCHAR2
180 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
181
182 FUNCTION C_SL_OVERREC_DRFORMULA(C_SL_TYPE IN VARCHAR2
183 ,C_STAT_TYPE IN VARCHAR2
184 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
185
186 FUNCTION C_SL_OVERREC_DR_MISCFORMULA(C_SL_TYPE IN VARCHAR2
187 ,C_STAT_TYPE IN VARCHAR2
188 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
189
190 FUNCTION C_SL_OVERREC_NSFFORMULA(C_SL_TYPE IN VARCHAR2
191 ,C_STAT_TYPE IN VARCHAR2
192 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
193
194 FUNCTION C_SL_OVERREC_REJECTEDFORMULA(C_SL_TYPE IN VARCHAR2
195 ,C_STAT_TYPE IN VARCHAR2
196 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
197
198 FUNCTION C_SL_OVERREC_STOPFORMULA(C_SL_TYPE IN VARCHAR2
199 ,C_STAT_TYPE IN VARCHAR2
200 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
201
202 FUNCTION C_SL_UNREC_POS_AMOUNTFORMULA(C_STAT_LINE_STATUS IN VARCHAR2
203 ,C_SL_AMOUNT IN NUMBER
204 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
205
206 FUNCTION C_SL_DR2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
207 ,C_SL_DR IN NUMBER) RETURN NUMBER;
208
209 FUNCTION C_SL_MISC_DR2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
210 ,C_SL_MISC_DR IN NUMBER) RETURN NUMBER;
211
212 FUNCTION C_SL_CR2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
213 ,C_SL_CR IN NUMBER) RETURN NUMBER;
214
215 FUNCTION C_SL_MISC_CR2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
216 ,C_SL_MISC_CR IN NUMBER) RETURN NUMBER;
217
218 FUNCTION C_SL_NSF2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
219 ,C_SL_NSF IN NUMBER) RETURN NUMBER;
220
221 FUNCTION C_SL_REJECTED2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
222 ,C_SL_REJECTED IN NUMBER) RETURN NUMBER;
223
224 FUNCTION C_SL_STOP2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
225 ,C_SL_STOP IN NUMBER) RETURN NUMBER;
226
227 FUNCTION C_TX_SIGN_SL_UNREC_AMOUNTFORMU(C_SL_TYPE IN VARCHAR2
228 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
229
230 FUNCTION C_SL_SWP_IN2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
231 ,C_SL_SWP_IN IN NUMBER) RETURN NUMBER;
232
233 FUNCTION C_SL_REC_SWP_INFORMULA(C_SL_TYPE IN VARCHAR2
234 ,C_STAT_LINE_STATUS IN VARCHAR2
235 ,C_SL_AMOUNT IN NUMBER
236 ,C_SL_UNREC_AMOUNT IN NUMBER
237 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
238
239 FUNCTION C_SL_UNREC_SWP_INFORMULA(C_SL_TYPE IN VARCHAR2
240 ,C_STAT_TYPE IN VARCHAR2
241 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
242
243 FUNCTION C_SL_OVERREC_SWP_INFORMULA(C_SL_TYPE IN VARCHAR2
244 ,C_STAT_TYPE IN VARCHAR2
245 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
246
247 FUNCTION C_SL_SWP_OUT2FORMULA(C_SL_UNREC_AMOUNT IN NUMBER
248 ,C_SL_SWP_OUT IN NUMBER) RETURN NUMBER;
249
250 FUNCTION C_SL_REC_SWP_OUTFORMULA(C_SL_TYPE IN VARCHAR2
251 ,C_STAT_LINE_STATUS IN VARCHAR2
252 ,C_SL_AMOUNT IN NUMBER
253 ,C_SL_UNREC_AMOUNT IN NUMBER
254 ,C_SL_REC_AMOUNT IN NUMBER) RETURN NUMBER;
255
256 FUNCTION C_SL_UNREC_SWP_OUTFORMULA(C_SL_TYPE IN VARCHAR2
257 ,C_STAT_TYPE IN VARCHAR2
258 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
259
260 FUNCTION C_SL_OVERREC_SWP_OUTFORMULA(C_SL_TYPE IN VARCHAR2
261 ,C_STAT_TYPE IN VARCHAR2
262 ,C_SL_UNREC_AMOUNT IN NUMBER) RETURN NUMBER;
263
264 FUNCTION C_STAT_DATE_SQL_LEX_P RETURN VARCHAR2;
265
266 FUNCTION C_BANK_NAME_DSP_P RETURN VARCHAR2;
267
268 FUNCTION C_ACCOUNT_NAME_DSP_P RETURN VARCHAR2;
269
270 FUNCTION C_BANK_BRANCH_DSP_P RETURN VARCHAR2;
271
272 FUNCTION C_EXTERNAL_TRANSLATION_P RETURN VARCHAR2;
273
274 FUNCTION C_ALL_TRANSLATION_P RETURN VARCHAR2;
275
276 FUNCTION C_STAT_NUMBER_SQL_LEX_P RETURN VARCHAR2;
277
278 FUNCTION C_THE_END_P RETURN VARCHAR2;
279
280 FUNCTION C_GL_CURRENCY_CODE_P RETURN VARCHAR2;
281
282 FUNCTION C_NAME_P RETURN VARCHAR2;
283
284 FUNCTION C_SET_OF_BOOKS_ID_P RETURN NUMBER;
285
286 FUNCTION C_BANK_CURR_DSP_P RETURN VARCHAR2;
287
288 FUNCTION C_ACCOUNT_NUMBER_DSP_P RETURN VARCHAR2;
289
290 FUNCTION C_DISPLAY_MESSAGE_P RETURN VARCHAR2;
291
292 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
293 ,NAME IN VARCHAR2);
294
295 /* PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
296 ,VALUE IN VARCHAR2
297 ,TRANSLATE IN BOOLEAN);
298
299 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2);
300
301 PROCEDURE CLEAR;
302
303 FUNCTION GET_STRING(APPIN IN VARCHAR2
304 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2;*/
305
306 FUNCTION GET RETURN VARCHAR2;
307
308 /* FUNCTION GET_ENCODED RETURN VARCHAR2;
309
310 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
311 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
312 ,MESSAGE_NAME OUT NOCOPY VARCHAR2);
313
314 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2);
315
316 PROCEDURE RAISE_ERROR;
317
318 PROCEDURE DEBUG(LINE IN VARCHAR2);
319
320 PROCEDURE ENABLE_DEBUG;
321
325
322 PROCEDURE DISABLE_DEBUG;*/
323
324 PROCEDURE INIT_SECURITY;
326 /* FUNCTION GET_WINDOW_SESSION_TITLE RETURN VARCHAR2;
327
328 FUNCTION GET_EFFECTIVE_DATE(P_BANK_ACCOUNT_ID IN NUMBER
329 ,P_TRX_CODE IN VARCHAR2
330 ,P_RECEIPT_DATE IN DATE) RETURN DATE;
331 */
332 END CE_CEXSTMRR_XMLP_PKG;
333