1 PACKAGE BODY CE_CEXSTMSR_XMLP_PKG AS
2 /* $Header: CEXSTMSRB.pls 120.0 2007/12/28 07:57:30 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 INTO
19 C_ALL_TRANSLATION
20 FROM
21 CE_LOOKUPS L
22 WHERE L.LOOKUP_TYPE = 'LITERAL'
23 AND L.LOOKUP_CODE = 'ALL';
24 EXCEPTION
25 WHEN NO_DATA_FOUND THEN
26 SET_NAME('CE'
27 ,'CE_PURGE_NO_SOB');
28 L_MESSAGE := GET;
29 RAISE_APPLICATION_ERROR(-20101
30 ,NULL);
31 END;
32 IF (P_BANK_ACCOUNT_ID IS NOT NULL) THEN
33 BEGIN
34 SELECT
35 ABB.BANK_NAME,
36 ABB.BANK_BRANCH_NAME,
37 ABA.BANK_ACCOUNT_NAME,
38 ABA.BANK_ACCOUNT_NUM,
39 ABA.CURRENCY_CODE
40 INTO
41 C_BANK_NAME_DSP
42 ,C_BANK_BRANCH_NAME_DSP
43 ,C_BANK_ACCOUNT_NAME_DSP
44 ,C_BANK_ACCOUNT_NUM_DSP
45 ,C_BANK_CURRENCY_CODE_DSP
46 FROM
47 CE_BANK_BRANCHES_V ABB,
48 CE_BANK_ACCTS_GT_V ABA
49 WHERE ABA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID
50 AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID;
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 SET_NAME('CE'
54 ,'CE_INVALID_BANK');
55 L_MESSAGE := GET;
56 RAISE_APPLICATION_ERROR(-20101
57 ,NULL);
58 END;
59 ELSE
60 C_BANK_NAME_DSP := C_ALL_TRANSLATION;
61 C_BANK_BRANCH_NAME_DSP := C_ALL_TRANSLATION;
62 C_BANK_ACCOUNT_NAME_DSP := C_ALL_TRANSLATION;
63 C_BANK_ACCOUNT_NUM_DSP := C_ALL_TRANSLATION;
64 C_BANK_CURRENCY_CODE_DSP := C_ALL_TRANSLATION;
65 END IF;
66 IF (P_AS_OF_DATE IS NULL) THEN
67 C_AS_OF_DATE_DSP := ' - ';
68 ELSE
69 C_AS_OF_DATE_DSP := P_AS_OF_DATE;
70 END IF;
71 SELECT
72 MEANING
73 INTO
74 C_BALANCES_BY
75 FROM
76 CE_LOOKUPS
77 WHERE LOOKUP_TYPE = 'BALANCES_BY'
78 AND LOOKUP_CODE = P_BALANCES_BY;
79 IF (P_STAT_DATE_FROM IS NOT NULL AND P_STAT_DATE_TO IS NOT NULL) THEN
80 C_STAT_DATE_LEX := 'sh.statement_date BETWEEN to_date(''' || TO_CHAR(P_STAT_DATE_FROM
81 ,'DD-MON-YYYY') || ''',''DD-MON-YYYY'') AND to_date(''' || TO_CHAR(P_STAT_DATE_TO
82 ,'DD-MON-YYYY') || ''',''DD-MON-YYYY'')';
83 ELSIF (P_STAT_DATE_FROM IS NULL AND P_STAT_DATE_TO IS NOT NULL) THEN
84 C_STAT_DATE_LEX := 'sh.statement_date <= to_date(''' || TO_CHAR(P_STAT_DATE_TO
85 ,'DD-MON-YYYY') || ''',''DD-MON-YYYY'')';
86 ELSIF (P_STAT_DATE_FROM IS NOT NULL AND P_STAT_DATE_TO IS NULL) THEN
87 C_STAT_DATE_LEX := 'sh.statement_date >= to_date(''' || TO_CHAR(P_STAT_DATE_FROM
88 ,'DD-MON-YYYY') || ''',''DD-MON-YYYY'')';
89 END IF;
90 IF (P_STAT_NUMBER_FROM IS NOT NULL AND P_STAT_NUMBER_TO IS NOT NULL) THEN
91 C_STAT_NUMBER_LEX := 'sh.statement_number BETWEEN ''' || P_STAT_NUMBER_FROM || ''' AND ''' || P_STAT_NUMBER_TO || '''';
92 ELSIF (P_STAT_NUMBER_FROM IS NULL AND P_STAT_NUMBER_TO IS NOT NULL) THEN
93 C_STAT_NUMBER_LEX := 'sh.statement_number <= ''' || P_STAT_NUMBER_TO || '''';
94 ELSIF (P_STAT_NUMBER_FROM IS NOT NULL AND P_STAT_NUMBER_TO IS NULL) THEN
95 C_STAT_NUMBER_LEX := 'sh.statement_number >= ''' || P_STAT_NUMBER_FROM || '''';
96 END IF;
97 IF (P_DEBUG_MODE = 'Y') THEN
98 NULL;
99 END IF;
100 END;
101 RETURN (TRUE);
102 END BEFOREREPORT;
103
104 FUNCTION AFTERREPORT RETURN BOOLEAN IS
105 BEGIN
106 RETURN (TRUE);
107 END AFTERREPORT;
108
109 FUNCTION C_NET_MOVEMENTFORMULA(C_STAT_END_BAL IN NUMBER
110 ,C_STAT_BEGIN_BAL IN NUMBER) RETURN NUMBER IS
111 BEGIN
112 RETURN (C_STAT_END_BAL - C_STAT_BEGIN_BAL);
113 END C_NET_MOVEMENTFORMULA;
114
115 FUNCTION C_ITEMSFORMULA(C_STAT_HEADER_ID IN NUMBER) RETURN NUMBER IS
116 BEGIN
117 DECLARE
118 ITEMS CE_STATEMENT_LINES.STATEMENT_LINE_ID%TYPE;
119 BEGIN
120 SELECT
121 count(*)
122 INTO
123 ITEMS
124 FROM
125 CE_STATEMENT_LINES
126 WHERE STATEMENT_HEADER_ID = C_STAT_HEADER_ID;
127 RETURN (NVL(ITEMS
128 ,0));
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 RETURN (0);
132 END;
133 RETURN NULL;
134 END C_ITEMSFORMULA;
135
136 FUNCTION C_UNREC_ITEMSFORMULA(C_STAT_HEADER_ID IN NUMBER) RETURN NUMBER IS
137 BEGIN
138 DECLARE
139 ITEMS CE_STATEMENT_LINES.STATEMENT_LINE_ID%TYPE;
140 BEGIN
141 SELECT
142 count(*)
143 INTO
144 ITEMS
145 FROM
146 CE_STATEMENT_LINES
147 WHERE STATEMENT_HEADER_ID = C_STAT_HEADER_ID
148 AND STATUS = 'UNRECONCILED';
149 RETURN (NVL(ITEMS
150 ,0));
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 RETURN (0);
154 END;
155 RETURN NULL;
156 END C_UNREC_ITEMSFORMULA;
157
158 FUNCTION C_STAT_UNREC_AMOUNTFORMULA(C_STAT_HEADER_ID IN NUMBER) RETURN NUMBER IS
159 BEGIN
160 DECLARE
161 AMOUNT CE_STATEMENT_LINES.AMOUNT%TYPE;
162 AMOUNT_CLEARED CE_RECONCILED_TRANSACTIONS_V.AMOUNT_CLEARED%TYPE;
163 BEGIN
164 SELECT
165 SUM(AMOUNT)
166 INTO
167 AMOUNT
168 FROM
169 CE_STATEMENT_LINES
170 WHERE STATEMENT_HEADER_ID = C_STAT_HEADER_ID;
171 SELECT
172 SUM(AMOUNT_CLEARED)
173 INTO
174 AMOUNT_CLEARED
175 FROM
176 CE_RECONCILED_TRANSACTIONS_V
177 WHERE STATEMENT_HEADER_ID = C_STAT_HEADER_ID;
178 RETURN (NVL(AMOUNT
179 ,0) - NVL(AMOUNT_CLEARED
180 ,0));
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 RETURN (0);
184 END;
185 RETURN NULL;
186 END C_STAT_UNREC_AMOUNTFORMULA;
187
188 FUNCTION G_BANKGROUPFILTER(C_BANK_ID IN NUMBER) RETURN BOOLEAN IS
189 BEGIN
190 IF (C_BANK_ID IS NOT NULL) THEN
191 C_THE_END := 'Y';
192 END IF;
193 RETURN (TRUE);
194 END G_BANKGROUPFILTER;
195
196 FUNCTION C_LINES_UNREC_AMOUNTFORMULA(C_STAT_LINE_STATUS IN VARCHAR2
197 ,C_STAT_LINE_TYPE IN VARCHAR2
198 ,C_STAT_LINE_AMOUNT IN NUMBER
199 ,C_LINES_SUM_CLEARED IN NUMBER) RETURN NUMBER IS
200 BEGIN
201 IF (C_STAT_LINE_STATUS = 'EXTERNAL') THEN
202 RETURN (0);
203 ELSE
204 IF (C_STAT_LINE_STATUS not in ('UNRECONCILED','ERROR')) THEN
205 RETURN (0);
206 ELSIF (C_STAT_LINE_TYPE = 'RECEIPT') THEN
207 RETURN (C_STAT_LINE_AMOUNT - NVL(C_LINES_SUM_CLEARED
208 ,0));
209 ELSE
210 RETURN -1 * (C_STAT_LINE_AMOUNT - NVL(C_LINES_SUM_CLEARED
211 ,0));
212 END IF;
213 END IF;
214 RETURN NULL;
215 END C_LINES_UNREC_AMOUNTFORMULA;
216
217 FUNCTION C_STAT_END_BALFORMULA(C_STAT_BEGIN_BAL IN NUMBER
218 ,C_STAT_DEBIT_AMOUNT IN NUMBER
219 ,C_STAT_CREDIT_AMOUNT IN NUMBER) RETURN NUMBER IS
220 BEGIN
221 RETURN (NVL(C_STAT_BEGIN_BAL
222 ,0) - C_STAT_DEBIT_AMOUNT + C_STAT_CREDIT_AMOUNT);
223 END C_STAT_END_BALFORMULA;
224
225 FUNCTION C_DATEFORMATFORMULA(C_DATEFORMAT IN VARCHAR2) RETURN VARCHAR2 IS
226 BEGIN
227 -- RETURN (C_DATEFORMAT);
228 RETURN (fnd_global.nls_date_format);
232 BEGIN
229 END C_DATEFORMATFORMULA;
230
231 FUNCTION C_ALL_TRANSLATION_P RETURN VARCHAR2 IS
233 RETURN C_ALL_TRANSLATION;
234 END C_ALL_TRANSLATION_P;
235
236 FUNCTION C_BANK_NAME_DSP_P RETURN VARCHAR2 IS
237 BEGIN
238 RETURN C_BANK_NAME_DSP;
239 END C_BANK_NAME_DSP_P;
240
241 FUNCTION C_STAT_DATE_LEX_P RETURN VARCHAR2 IS
242 BEGIN
243 RETURN C_STAT_DATE_LEX;
244 END C_STAT_DATE_LEX_P;
245
246 FUNCTION C_SOB_NAME_P RETURN VARCHAR2 IS
247 BEGIN
248 RETURN C_SOB_NAME;
249 END C_SOB_NAME_P;
250
251 FUNCTION C_SET_OF_BOOKS_ID_P RETURN NUMBER IS
252 BEGIN
253 RETURN C_SET_OF_BOOKS_ID;
254 END C_SET_OF_BOOKS_ID_P;
255
256 FUNCTION C_THE_END_P RETURN VARCHAR2 IS
257 BEGIN
258 RETURN C_THE_END;
259 END C_THE_END_P;
260
261 FUNCTION C_FUNC_CURR_CODE_P RETURN VARCHAR2 IS
262 BEGIN
263 RETURN C_FUNC_CURR_CODE;
264 END C_FUNC_CURR_CODE_P;
265
266 FUNCTION C_BANK_BRANCH_NAME_DSP_P RETURN VARCHAR2 IS
267 BEGIN
268 RETURN C_BANK_BRANCH_NAME_DSP;
269 END C_BANK_BRANCH_NAME_DSP_P;
270
271 FUNCTION C_BANK_ACCOUNT_NUM_DSP_P RETURN VARCHAR2 IS
272 BEGIN
273 RETURN C_BANK_ACCOUNT_NUM_DSP;
274 END C_BANK_ACCOUNT_NUM_DSP_P;
275
276 FUNCTION C_BANK_ACCOUNT_NAME_DSP_P RETURN VARCHAR2 IS
277 BEGIN
278 RETURN C_BANK_ACCOUNT_NAME_DSP;
279 END C_BANK_ACCOUNT_NAME_DSP_P;
280
281 FUNCTION C_BANK_CURRENCY_CODE_DSP_P RETURN VARCHAR2 IS
282 BEGIN
283 RETURN C_BANK_CURRENCY_CODE_DSP;
284 END C_BANK_CURRENCY_CODE_DSP_P;
285
286 FUNCTION C_STAT_NUMBER_LEX_P RETURN VARCHAR2 IS
287 BEGIN
288 RETURN C_STAT_NUMBER_LEX;
289 END C_STAT_NUMBER_LEX_P;
290
291 FUNCTION C_AS_OF_DATE_DSP_P RETURN VARCHAR2 IS
292 BEGIN
293 RETURN C_AS_OF_DATE_DSP;
294 END C_AS_OF_DATE_DSP_P;
295
296 FUNCTION C_BALANCES_BY_P RETURN VARCHAR2 IS
297 BEGIN
298 RETURN C_BALANCES_BY;
299 END C_BALANCES_BY_P;
300
301 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
302 ,NAME IN VARCHAR2) IS
303 BEGIN
304
305 begin FND_MESSAGE.SET_NAME(APPLICATION, NAME); end;
306 --STPROC.BIND_I(APPLICATION);
307 --STPROC.BIND_I(NAME);
308 --STPROC.EXECUTE;
309 END SET_NAME;
310
311 /* PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
312 ,VALUE IN VARCHAR2
313 ,TRANSLATE IN BOOLEAN) IS
314 BEGIN
315 STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
316 STPROC.BIND_I(TRANSLATE);
317 STPROC.BIND_I(TOKEN);
318 STPROC.BIND_I(VALUE);
319 STPROC.EXECUTE;
320 END SET_TOKEN;
321
322 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
323 BEGIN
324 STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
325 STPROC.BIND_O(MSGOUT);
326 STPROC.EXECUTE;
327 STPROC.RETRIEVE(1
328 ,MSGOUT);
329 END RETRIEVE;
330
331 PROCEDURE CLEAR IS
332 BEGIN
333 STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
334 STPROC.EXECUTE;
335 END CLEAR;
336
337 FUNCTION GET_STRING(APPIN IN VARCHAR2
338 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
339 X0 VARCHAR2(2000);
340 BEGIN
341 STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
342 STPROC.BIND_O(X0);
343 STPROC.BIND_I(APPIN);
344 STPROC.BIND_I(NAMEIN);
345 STPROC.EXECUTE;
346 STPROC.RETRIEVE(1
347 ,X0);
348 RETURN X0;
349 END GET_STRING;*/
350
351 FUNCTION GET RETURN VARCHAR2 IS
352 X0 VARCHAR2(2000);
353 BEGIN
354 begin X0 := FND_MESSAGE.GET; end;
355 --STPROC.BIND_O(X0);
356 --STPROC.EXECUTE;
357 --STPROC.RETRIEVE(1 ,X0);
358 RETURN X0;
359 END GET;
360
361 /*FUNCTION GET_ENCODED RETURN VARCHAR2 IS
362 X0 VARCHAR2(2000);
363 BEGIN
364 STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
365 STPROC.BIND_O(X0);
366 STPROC.EXECUTE;
367 STPROC.RETRIEVE(1
368 ,X0);
369 RETURN X0;
370 END GET_ENCODED;
371
372 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
373 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
374 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
375 BEGIN
376 STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
377 STPROC.BIND_I(ENCODED_MESSAGE);
378 STPROC.BIND_O(APP_SHORT_NAME);
379 STPROC.BIND_O(MESSAGE_NAME);
380 STPROC.EXECUTE;
381 STPROC.RETRIEVE(2
382 ,APP_SHORT_NAME);
383 STPROC.RETRIEVE(3
384 ,MESSAGE_NAME);
385 END PARSE_ENCODED;
386
387 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
388 BEGIN
389 STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
390 STPROC.BIND_I(ENCODED_MESSAGE);
391 STPROC.EXECUTE;
392 END SET_ENCODED;
393
394 PROCEDURE RAISE_ERROR IS
395 BEGIN
396 STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
397 STPROC.EXECUTE;
398 END RAISE_ERROR;
399
400 PROCEDURE DEBUG(LINE IN VARCHAR2) IS
401 BEGIN
402 STPROC.INIT('begin CEP_STANDARD.DEBUG(:LINE); end;');
403 STPROC.BIND_I(LINE);
404 STPROC.EXECUTE;
405 END DEBUG;
406
407 PROCEDURE ENABLE_DEBUG IS
408 BEGIN
409 STPROC.INIT('begin CEP_STANDARD.ENABLE_DEBUG; end;');
410 STPROC.EXECUTE;
411 END ENABLE_DEBUG;
412
413 PROCEDURE DISABLE_DEBUG IS
414 BEGIN
415 STPROC.INIT('begin CEP_STANDARD.DISABLE_DEBUG; end;');
416 STPROC.EXECUTE;
417 END DISABLE_DEBUG;*/
418
419 PROCEDURE INIT_SECURITY IS
420 BEGIN
421 begin cep_standard.init_security; end;
422 --STPROC.EXECUTE;
423 END INIT_SECURITY;
424
425 /* FUNCTION GET_WINDOW_SESSION_TITLE RETURN VARCHAR2 IS
426 X0 VARCHAR2(2000);
427 BEGIN
428 STPROC.INIT('begin :X0 := CEP_STANDARD.GET_WINDOW_SESSION_TITLE; end;');
429 STPROC.BIND_O(X0);
430 STPROC.EXECUTE;
431 STPROC.RETRIEVE(1
432 ,X0);
433 RETURN X0;
434 END GET_WINDOW_SESSION_TITLE;
435
436 FUNCTION GET_EFFECTIVE_DATE(P_BANK_ACCOUNT_ID IN NUMBER
437 ,P_TRX_CODE IN VARCHAR2
438 ,P_RECEIPT_DATE IN DATE) RETURN DATE IS
439 X0 DATE;
440 BEGIN
441 STPROC.INIT('begin :X0 := CEP_STANDARD.GET_EFFECTIVE_DATE(:P_BANK_ACCOUNT_ID, :P_TRX_CODE, :P_RECEIPT_DATE); end;');
442 STPROC.BIND_O(X0);
443 STPROC.BIND_I(P_BANK_ACCOUNT_ID);
444 STPROC.BIND_I(P_TRX_CODE);
445 STPROC.BIND_I(P_RECEIPT_DATE);
446 STPROC.EXECUTE;
447 STPROC.RETRIEVE(1
448 ,X0);
449 RETURN X0;
450 END GET_EFFECTIVE_DATE;*/
451
452 END CE_CEXSTMSR_XMLP_PKG;
453
[Home] [Help]