1 PACKAGE BODY CE_CEXRECRE_XMLP_PKG AS
2 /* $Header: CEXRECREB.pls 120.0 2007/12/28 07:56:22 abraghun noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
7 BEGIN
8 IF (P_TEST_LAYOUT = 'Y') THEN
9 RAISE NO_DATA_FOUND;
10 END IF;
11 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
12 INIT_SECURITY;
13 BEGIN
14 IF (P_BANK_ACCOUNT_ID IS NOT NULL) THEN
15 BEGIN
16 SELECT
17 CB.SET_OF_BOOKS_ID,
18 CB.CASHBOOK_BEGIN_DATE
19 INTO
20 C_SET_OF_BOOKS_ID
21 ,C_CASHBOOK_BEGIN_DATE
22 FROM
23 CE_SYSTEM_PARAMETERS CB,
24 CE_BANK_ACCTS_GT_V BA
25 WHERE CB.LEGAL_ENTITY_ID = BA.ACCOUNT_OWNER_ORG_ID
26 AND BA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID;
27 EXCEPTION
28 WHEN NO_DATA_FOUND THEN
29 SET_NAME('CE'
30 ,'CE_PURGE_NO_SOB');
31 L_MESSAGE := GET;
32 RAISE_APPLICATION_ERROR(-20101
33 ,NULL);
34 END;
35 IF (C_SET_OF_BOOKS_ID IS NOT NULL) THEN
36 SELECT
37 GL.SET_OF_BOOKS_ID,
38 GL.CURRENCY_CODE,
39 GL.CHART_OF_ACCOUNTS_ID,
40 L.MEANING,
41 P.END_DATE
42 INTO
43 C_SET_OF_BOOKS_ID
44 ,C_GL_CURRENCY_CODE
45 ,C_CHART_OF_ACCOUNTS_ID
46 ,C_ALL_TRANSLATION
47 ,C_AS_OF_DATE
48 FROM
49 GL_SETS_OF_BOOKS GL,
50 CE_LOOKUPS L,
51 GL_PERIOD_STATUSES P
52 WHERE P.PERIOD_NAME = P_PERIOD_NAME
53 AND P.APPLICATION_ID = 101
54 AND P.SET_OF_BOOKS_ID = C_SET_OF_BOOKS_ID
55 AND GL.SET_OF_BOOKS_ID = C_SET_OF_BOOKS_ID
56 AND L.LOOKUP_TYPE = 'LITERAL'
57 AND L.LOOKUP_CODE = 'ALL';
58 END IF;
59 END IF;
60 SELECT
61 ( G.SHORT_NAME || ': ' || SUBSTR(G.CURRENCY_CODE
62 ,1
63 ,5) )
64 INTO
65 C_NAME
66 FROM
67 GL_SETS_OF_BOOKS G
68 WHERE G.SET_OF_BOOKS_ID = C_SET_OF_BOOKS_ID;
69 EXCEPTION
70 WHEN OTHERS THEN
71 SET_NAME('CE'
72 ,'CE_PURGE_NO_SOB');
73 L_MESSAGE := GET;
74 RAISE_APPLICATION_ERROR(-20101
75 ,NULL);
76 END;
77 IF (P_BANK_ACCOUNT_ID IS NOT NULL) THEN
78 BEGIN
79 SELECT
80 ABB.BANK_BRANCH_NAME,
81 ABB.BANK_NAME,
82 ABA.BANK_ACCOUNT_NAME,
83 ABA.BANK_ACCOUNT_NUM,
84 ABA.CURRENCY_CODE,
85 ABA.ASSET_CODE_COMBINATION_ID,
86 ABA.CASH_CLEARING_CCID
87 INTO
88 C_BANK_BRANCH_DSP
89 ,C_BANK_NAME_DSP
90 ,C_ACCOUNT_NAME_DSP
91 ,C_ACCOUNT_NUMBER_DSP
92 ,C_BANK_CURR_DSP
93 ,C_ASSET_CC_ID
94 ,P_CLEARING_CCID
95 FROM
96 CE_BANK_BRANCHES_V ABB,
97 CE_BANK_ACCTS_GT_V ABA
98 WHERE ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID
99 AND ABA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID;
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 SET_NAME('CE'
103 ,'CE_INVALID_BANK');
104 L_MESSAGE := GET;
105 RAISE_APPLICATION_ERROR(-20101
106 ,NULL);
107 END;
108 ELSE
109 C_ACCOUNT_NAME_DSP := C_ALL_TRANSLATION;
110 C_ACCOUNT_NUMBER_DSP := C_ALL_TRANSLATION;
111 C_BANK_CURR_DSP := C_ALL_TRANSLATION;
112 C_BANK_NAME_DSP := C_ALL_TRANSLATION;
113 C_BANK_BRANCH_DSP := C_ALL_TRANSLATION;
114 END IF;
115 IF (C_ASSET_CC_ID IS NOT NULL) THEN
116 NULL;
117 ELSE
118 BEGIN
119 SELECT
120 DISTINCT
121 GAC.ASSET_CODE_COMBINATION_ID
122 INTO
123 C_ASSET_CC_ID
124 FROM
125 CE_BANK_ACCT_USES_OU_V BAU,
126 CE_GL_ACCOUNTS_CCID GAC
127 WHERE GAC.BANK_ACCT_USE_ID = BAU.BANK_ACCT_USE_ID
128 AND BAU.BANK_ACCOUNT_ID = P_BANK_ACCOUNT_ID;
129 IF (C_ASSET_CC_ID IS NOT NULL) THEN
130 NULL;
131 END IF;
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134 SET_NAME('CE'
135 ,'CE_NO_ASSET_CC');
136 L_MESSAGE := GET;
137 RAISE_APPLICATION_ERROR(-20101
138 ,NULL);
139 WHEN TOO_MANY_ROWS THEN
140 NULL;
141 END;
142 END IF;
143 IF (P_RECON_ACCOUNTING_FLAG = 'Y') THEN
144 IF (NVL(P_CLEARING_CCID
145 ,C_ASSET_CC_ID) <> C_ASSET_CC_ID) THEN
146 P_RECON_USED_FLAG := 'Y';
147 ELSE
148 P_RECON_USED_FLAG := 'N';
149 END IF;
150 ELSE
151 P_RECON_USED_FLAG := 'N';
152 END IF;
153 IF (P_DEBUG_MODE = 'Y') THEN
154 NULL;
155 END IF;
156 END;
157 RETURN (TRUE);
158 END BEFOREREPORT;
159
160 FUNCTION AFTERREPORT RETURN BOOLEAN IS
161 BEGIN
162 RETURN (TRUE);
163 END AFTERREPORT;
164
165 FUNCTION C_REP_FLEXDATA_DSPFORMULA(C_FLEXDATA_DSP IN VARCHAR2) RETURN VARCHAR2 IS
166 BEGIN
167 RETURN (C_FLEXDATA_DSP);
168 END C_REP_FLEXDATA_DSPFORMULA;
169
170 FUNCTION C_DATEFORMATFORMULA(C_DATEFORMAT IN VARCHAR2) RETURN VARCHAR2 IS
171 BEGIN
172 RETURN (C_DATEFORMAT);
173 END C_DATEFORMATFORMULA;
174
175 FUNCTION CF_CLOSING_BALANCEFORMULA RETURN NUMBER IS
176 BEGIN
177 RETURN FND_NUMBER.CANONICAL_TO_NUMBER(P_CLOSING_BALANCE);
178 END CF_CLOSING_BALANCEFORMULA;
179
180 FUNCTION C_BANK_NAME_DSP_P RETURN VARCHAR2 IS
181 BEGIN
182 RETURN C_BANK_NAME_DSP;
183 END C_BANK_NAME_DSP_P;
184
185 FUNCTION C_ACCOUNT_NAME_DSP_P RETURN VARCHAR2 IS
186 BEGIN
187 RETURN C_ACCOUNT_NAME_DSP;
188 END C_ACCOUNT_NAME_DSP_P;
189
190 FUNCTION C_BANK_BRANCH_DSP_P RETURN VARCHAR2 IS
191 BEGIN
192 RETURN C_BANK_BRANCH_DSP;
193 END C_BANK_BRANCH_DSP_P;
194
195 FUNCTION C_ALL_TRANSLATION_P RETURN VARCHAR2 IS
196 BEGIN
197 RETURN C_ALL_TRANSLATION;
198 END C_ALL_TRANSLATION_P;
199
200 FUNCTION C_THE_END_P RETURN VARCHAR2 IS
201 BEGIN
202 RETURN C_THE_END;
203 END C_THE_END_P;
204
205 FUNCTION C_GL_CURRENCY_CODE_P RETURN VARCHAR2 IS
206 BEGIN
207 RETURN C_GL_CURRENCY_CODE;
208 END C_GL_CURRENCY_CODE_P;
209
210 FUNCTION C_NAME_P RETURN VARCHAR2 IS
211 BEGIN
212 RETURN C_NAME;
213 END C_NAME_P;
214
215 FUNCTION C_SET_OF_BOOKS_ID_P RETURN NUMBER IS
216 BEGIN
217 RETURN C_SET_OF_BOOKS_ID;
218 END C_SET_OF_BOOKS_ID_P;
219
220 FUNCTION C_BANK_CURR_DSP_P RETURN VARCHAR2 IS
221 BEGIN
222 RETURN C_BANK_CURR_DSP;
223 END C_BANK_CURR_DSP_P;
224
225 FUNCTION C_ACCOUNT_NUMBER_DSP_P RETURN VARCHAR2 IS
226 BEGIN
227 RETURN C_ACCOUNT_NUMBER_DSP;
228 END C_ACCOUNT_NUMBER_DSP_P;
229
230 FUNCTION C_ASSET_CC_ID_P RETURN NUMBER IS
231 BEGIN
232 RETURN C_ASSET_CC_ID;
233 END C_ASSET_CC_ID_P;
234
235 FUNCTION C_AS_OF_DATE_P RETURN DATE IS
236 BEGIN
237 RETURN C_AS_OF_DATE;
238 END C_AS_OF_DATE_P;
239
240 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
241 BEGIN
242 RETURN C_CHART_OF_ACCOUNTS_ID;
243 END C_CHART_OF_ACCOUNTS_ID_P;
244
245 FUNCTION C_FLEXDATA_P RETURN VARCHAR2 IS
246 BEGIN
247 RETURN C_FLEXDATA;
248 END C_FLEXDATA_P;
249
250 FUNCTION C_ADJUSTED_BANK_BALANCE_P RETURN NUMBER IS
251 BEGIN
252 RETURN C_ADJUSTED_BANK_BALANCE;
253 END C_ADJUSTED_BANK_BALANCE_P;
254
255 FUNCTION C_DIFFERENCE_P RETURN NUMBER IS
256 BEGIN
257 RETURN C_DIFFERENCE;
258 END C_DIFFERENCE_P;
259
260
261
262
263 /* PROCEDURE DEBUG(LINE IN VARCHAR2) IS
264 BEGIN
265 STPROC.INIT('begin CEP_STANDARD.DEBUG(:LINE); end;');
266 STPROC.BIND_I(LINE);
267 STPROC.EXECUTE;
268 END DEBUG;
269
270 PROCEDURE ENABLE_DEBUG IS
271 BEGIN
272 STPROC.INIT('begin CEP_STANDARD.ENABLE_DEBUG; end;');
273 STPROC.EXECUTE;
274 END ENABLE_DEBUG;
275
276 PROCEDURE DISABLE_DEBUG IS
277 BEGIN
278 STPROC.INIT('begin CEP_STANDARD.DISABLE_DEBUG; end;');
279 STPROC.EXECUTE;
280 END DISABLE_DEBUG;*/
281
282 PROCEDURE INIT_SECURITY IS
283 BEGIN
284 begin CEP_STANDARD.init_security; end;
285 -- STPROC.EXECUTE;
286 END INIT_SECURITY;
287
288 /* FUNCTION GET_WINDOW_SESSION_TITLE RETURN VARCHAR2 IS
289 X0 VARCHAR2(2000);
290 BEGIN
291 STPROC.INIT('begin :X0 := CEP_STANDARD.GET_WINDOW_SESSION_TITLE; end;');
292 STPROC.BIND_O(X0);
293 STPROC.EXECUTE;
294 STPROC.RETRIEVE(1
295 ,X0);
296 RETURN X0;
297 END GET_WINDOW_SESSION_TITLE;
298
299 FUNCTION GET_EFFECTIVE_DATE(P_BANK_ACCOUNT_ID IN NUMBER
300 ,P_TRX_CODE IN VARCHAR2
301 ,P_RECEIPT_DATE IN DATE) RETURN DATE IS
302 X0 DATE;
303 BEGIN
304 STPROC.INIT('begin :X0 := CEP_STANDARD.GET_EFFECTIVE_DATE(:P_BANK_ACCOUNT_ID, :P_TRX_CODE, :P_RECEIPT_DATE); end;');
305 STPROC.BIND_O(X0);
306 STPROC.BIND_I(P_BANK_ACCOUNT_ID);
307 STPROC.BIND_I(P_TRX_CODE);
308 STPROC.BIND_I(P_RECEIPT_DATE);
309 STPROC.EXECUTE;
310 STPROC.RETRIEVE(1
311 ,X0);
312 RETURN X0;
313 END GET_EFFECTIVE_DATE;*/
314
315 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
316 ,NAME IN VARCHAR2) IS
317 BEGIN
318 begin FND_MESSAGE.SET_NAME(APPLICATION, NAME); end; /*
319 STPROC.BIND_I(APPLICATION);
320 STPROC.BIND_I(NAME);
321 STPROC.EXECUTE;*/
322 END SET_NAME;
323
324 /* PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
325 ,VALUE IN VARCHAR2
326 ,TRANSLATE IN BOOLEAN) IS
327 BEGIN
328 STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
329 STPROC.BIND_I(TRANSLATE);
330 STPROC.BIND_I(TOKEN);
331 STPROC.BIND_I(VALUE);
332 STPROC.EXECUTE;
333 END SET_TOKEN;
334
335 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
336 BEGIN
337 STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
338 STPROC.BIND_O(MSGOUT);
339 STPROC.EXECUTE;
340 STPROC.RETRIEVE(1
341 ,MSGOUT);
342 END RETRIEVE;
343
344 PROCEDURE CLEAR IS
345 BEGIN
346 STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
347 STPROC.EXECUTE;
348 END CLEAR;
349
350 FUNCTION GET_STRING(APPIN IN VARCHAR2
351 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
352 X0 VARCHAR2(2000);
353 BEGIN
354 STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
355 STPROC.BIND_O(X0);
356 STPROC.BIND_I(APPIN);
357 STPROC.BIND_I(NAMEIN);
358 STPROC.EXECUTE;
359 STPROC.RETRIEVE(1
360 ,X0);
361 RETURN X0;
362 END GET_STRING;
363
364 FUNCTION GET_NUMBER(APPIN IN VARCHAR2
365 ,NAMEIN IN VARCHAR2) RETURN NUMBER IS
366 X0 NUMBER;
367 BEGIN
368 STPROC.INIT('begin :X0 := FND_MESSAGE.GET_NUMBER(:APPIN, :NAMEIN); end;');
369 STPROC.BIND_O(X0);
370 STPROC.BIND_I(APPIN);
371 STPROC.BIND_I(NAMEIN);
372 STPROC.EXECUTE;
373 STPROC.RETRIEVE(1
374 ,X0);
375 RETURN X0;
376 END GET_NUMBER;*/
377
378 FUNCTION GET RETURN VARCHAR2 IS
379 X0 VARCHAR2(2000);
380 BEGIN
381 begin X0 := FND_MESSAGE.GET; end;/*
382 STPROC.BIND_O(X0);
383 STPROC.EXECUTE;
384 STPROC.RETRIEVE(1
385 ,X0);*/
386 RETURN X0;
387 END GET;
388
389 /* FUNCTION GET_ENCODED RETURN VARCHAR2 IS
390 X0 VARCHAR2(2000);
391 BEGIN
392 STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
393 STPROC.BIND_O(X0);
394 STPROC.EXECUTE;
395 STPROC.RETRIEVE(1
396 ,X0);
397 RETURN X0;
398 END GET_ENCODED;
399
400 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
401 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
402 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
403 BEGIN
404 STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
405 STPROC.BIND_I(ENCODED_MESSAGE);
406 STPROC.BIND_O(APP_SHORT_NAME);
407 STPROC.BIND_O(MESSAGE_NAME);
408 STPROC.EXECUTE;
409 STPROC.RETRIEVE(2
410 ,APP_SHORT_NAME);
411 STPROC.RETRIEVE(3
412 ,MESSAGE_NAME);
413 END PARSE_ENCODED;
414
415 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
416 BEGIN
417 STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
418 STPROC.BIND_I(ENCODED_MESSAGE);
419 STPROC.EXECUTE;
420 END SET_ENCODED;
421
422 PROCEDURE RAISE_ERROR IS
423 BEGIN
424 STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
425 STPROC.EXECUTE;
426 END RAISE_ERROR;*/
427
428 END CE_CEXRECRE_XMLP_PKG;
429