[Home] [Help]
PACKAGE BODY: APPS.XTR_XTRIGSUM_XMLP_PKG
Source
1 PACKAGE BODY XTR_XTRIGSUM_XMLP_PKG AS
2 /* $Header: XTRIGSUMB.pls 120.1 2007/12/28 12:50:00 npannamp noship $ */
3 FUNCTION CF_SET_PARAFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 SELECT
6 SUBSTR(USER
7 ,1
8 ,10)
9 INTO
10 CP_PARA
11 FROM
12 DUAL;
13 RETURN (CP_PARA);
14 END CF_SET_PARAFORMULA;
15
16 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
17 L_DMMY_NUM NUMBER;
18 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
19 CURSOR GET_LANGUAGE_DESC IS
20 SELECT
21 ITEM_NAME,
22 SUBSTR(TEXT
23 ,1
24 ,100) LANG_NAME
25 FROM
26 XTR_SYS_LANGUAGES_VL
27 WHERE MODULE_NAME = 'XTRIGSUM';
28 BEGIN
29 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
30 BEGIN
31 COMPANY_NAME_HEADER := CEP_STANDARD.GET_WINDOW_SESSION_TITLE;
32 EXCEPTION
33 WHEN OTHERS THEN
34 FND_MESSAGE.SET_NAME('XTR'
35 ,'XTR_LOOKUP_ERR');
36 L_MESSAGE := FND_MESSAGE.GET;
37 RAISE_APPLICATION_ERROR(-20101
38 ,NULL);
39 END;
40 IF (P_DISPLAY_DEBUG = 'Y') THEN
41 NULL;
42 END IF;
43 FOR c IN GET_LANGUAGE_DESC LOOP
44 IF C.ITEM_NAME = 'Z1COMPANY' THEN
45 Z1COMPANY := C.LANG_NAME;
46 ELSIF C.ITEM_NAME = 'Z1FOR_PERIOD' THEN
47 Z1FOR_PERIOD := C.LANG_NAME;
48 ELSIF C.ITEM_NAME = 'Z1INTERGROUP_PARTY' THEN
49 Z1INTERGROUP_PARTY := C.LANG_NAME;
50 ELSIF C.ITEM_NAME = 'Z1PARAMETERS' THEN
51 Z1PARAMETERS := C.LANG_NAME;
52 ELSIF C.ITEM_NAME = 'Z1REPORT_NOS' THEN
53 Z1REPORT_NOS := C.LANG_NAME;
54 ELSIF C.ITEM_NAME = 'Z1TO' THEN
55 Z1TO := C.LANG_NAME;
56 ELSIF C.ITEM_NAME = 'Z2ADJUSTMENT' THEN
57 Z2ADJUSTMENT := C.LANG_NAME;
58 ELSIF C.ITEM_NAME = 'Z2BALANCE' THEN
59 Z2BALANCE := C.LANG_NAME;
60 ELSIF C.ITEM_NAME = 'Z2CCY' THEN
61 Z2CCY := C.LANG_NAME;
62 ELSIF C.ITEM_NAME = 'Z2CURRENCY' THEN
63 Z2CURRENCY := C.LANG_NAME;
64 ELSIF C.ITEM_NAME = 'Z2DATE' THEN
65 Z2DATE := C.LANG_NAME;
66 ELSIF C.ITEM_NAME = 'Z2DAY' THEN
67 Z2DAY := C.LANG_NAME;
68 ELSIF C.ITEM_NAME = 'Z2END_OF_REPORT' THEN
69 Z2END_OF_REPORT := C.LANG_NAME;
70 ELSIF C.ITEM_NAME = 'Z2INTEREST' THEN
71 Z2INTEREST := C.LANG_NAME;
72 ELSIF C.ITEM_NAME = 'Z2INTEREST_BFWD' THEN
73 Z2INTEREST_BFWD := C.LANG_NAME;
74 ELSIF C.ITEM_NAME = 'Z2INTEREST_CFWD' THEN
75 Z2INTEREST_CFWD := C.LANG_NAME;
76 ELSIF C.ITEM_NAME = 'Z2INTEREST_FOR_THE_PERIOD' THEN
77 Z2INTEREST_FOR_THE_PERIOD := C.LANG_NAME;
78 ELSIF C.ITEM_NAME = 'Z2INTEREST_SETTLED' THEN
79 Z2INTEREST_SETTLED := C.LANG_NAME;
80 ELSIF C.ITEM_NAME = 'Z2INT_RATE' THEN
81 Z2INT_RATE := C.LANG_NAME;
82 ELSIF C.ITEM_NAME = 'Z2PRIN' THEN
83 Z2PRIN := C.LANG_NAME;
84 ELSIF C.ITEM_NAME = 'Z2SETTLED_ON' THEN
85 Z2SETTLED_ON := C.LANG_NAME;
86 ELSIF C.ITEM_NAME = 'Z2PAGE' THEN
87 Z2PAGE := C.LANG_NAME;
88 ELSIF C.ITEM_NAME = 'Z2REPORT' THEN
89 Z2REPORT := C.LANG_NAME;
90 ELSIF C.ITEM_NAME = 'Z2REQUESTED_BY' THEN
91 Z2REQUESTED_BY := C.LANG_NAME;
92 ELSIF C.ITEM_NAME = 'Z2NOTE' THEN
93 Z2NOTE := C.LANG_NAME;
94 ELSIF C.ITEM_NAME = 'REPORT_DATE' THEN
95 REPORT_DATE := C.LANG_NAME;
96 END IF;
97 END LOOP;
98 RETURN (TRUE);
99 END BEFOREREPORT;
100
101 FUNCTION AFTERREPORT RETURN BOOLEAN IS
102 BEGIN
103 RETURN (TRUE);
104 END AFTERREPORT;
105
106 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
107 L_ERROR NUMBER;
108 BEGIN
109 RETURN (TRUE);
110 END BEFOREPFORM;
111
112 FUNCTION AFTERPFORM RETURN BOOLEAN IS
113 LD_DATE DATE;
114 BEGIN
115 SELECT
116 TRUNC(SYSDATE)
117 INTO
118 LD_DATE
119 FROM
120 DUAL;
121 P_PERIOD_FROM_1 := TO_CHAR(TO_DATE(P_PERIOD_FROM
122 ,'YYYY/MM/DD HH24:MI:SS')
123 ,'DD-MON-YYYY');
124 P_PERIOD_TO_1 := TO_CHAR(TO_DATE(P_PERIOD_TO
125 ,'YYYY/MM/DD HH24:MI:SS')
126 ,'DD-MON-YYYY');
127 PER_FROM := P_PERIOD_FROM_1;
128 PER_TO := P_PERIOD_TO_1;
129 PER_TO := NVL(PER_TO
130 ,LD_DATE);
131 IF PER_FROM IS NOT NULL THEN
132 PER_TO := GREATEST(PER_TO
133 ,PER_FROM);
134 END IF;
135 SEL_COMP2 := P_COMPANY;
136 SEL_PARTY2 := P_INTERGROUP_PARTY;
137 SELECT
138 CP.USER_CONCURRENT_PROGRAM_NAME
139 INTO
140 REPORT_SHORT_NAME2
141 FROM
142 FND_CONCURRENT_PROGRAMS_VL CP,
143 FND_CONCURRENT_REQUESTS CR
144 WHERE CR.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
145 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
146 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
147
148 REPORT_SHORT_NAME2 := substr(REPORT_SHORT_NAME2,1,instr(REPORT_SHORT_NAME2,' (XML)'));
149 RETURN (TRUE);
150 END AFTERPFORM;
151
152 FUNCTION CF_INT_CFFORMULA(COMP_CODE IN VARCHAR2
153 ,PTY_CODE IN VARCHAR2
154 ,CCY IN VARCHAR2
155 ,CF_INTEREST_FOR_DUMMY_REC IN NUMBER
156 ,DAY_COUNT_TYPE IN VARCHAR2
157 ,ACC_INT_BF IN NUMBER) RETURN NUMBER IS
158 Lvr_Combination_Code Varchar2(100) := Comp_code||Pty_Code||Ccy;
159 Lnu_Int_Amt Number := nvl(Cf_interest_for_dummy_rec,0);
160 begin
161 If Lvr_Combination_Code <> Nvl(P_Combination,'*') Then
162
163 p_IntAmt_Running := 0;
164 p_SetAmt_Running := 0;
165 P_Prev_day_Count_Type := day_count_Type;
166 P_Prev_Prev_day_Count_Type := Null;
167 -- P_Oldest_Transfer_Date := Trans_Date;
168
169 P_Combination := Lvr_Combination_Code ;
170 Else
171 P_Prev_Prev_day_Count_Type := P_Prev_day_Count_Type;
172 P_Prev_day_Count_Type := day_count_Type;
173 End If;
174
175 p_IntAmt_Running := Nvl(p_IntAmt_Running,0)+Lnu_Int_Amt;
176 p_SetAmt_Running := (Nvl(p_SetAmt_Running,0)+nvl(Acc_Int_Bf,0));
177
178 Return(Nvl(p_IntAmt_Running,0) - Nvl(P_SetAmt_Running,0));
179
180 end;
181 /* FUNCTION CF_INT_CFFORMULA(COMP_CODE IN VARCHAR2
182 ,PTY_CODE IN VARCHAR2
183 ,CCY IN VARCHAR2
184 ,CF_INTEREST_FOR_DUMMY_REC IN NUMBER
185 ,DAY_COUNT_TYPE IN VARCHAR2
186 ,ACC_INT_BF IN NUMBER) RETURN NUMBER IS
187 LVR_COMBINATION_CODE VARCHAR2(100) := COMP_CODE || PTY_CODE || CCY;
188 LNU_INT_AMT NUMBER := NVL(CF_INTEREST_FOR_DUMMY_REC
189 ,0);
190 BEGIN
191 IF LVR_COMBINATION_CODE < NVL(P_COMBINATION
192 ,'*') THEN
193 P_INTAMT_RUNNING := 0;
194 P_SETAMT_RUNNING := 0;
195 P_PREV_DAY_COUNT_TYPE := DAY_COUNT_TYPE;
196 P_PREV_PREV_DAY_COUNT_TYPE := NULL;
197 P_COMBINATION := LVR_COMBINATION_CODE;
198 ELSE
199 P_PREV_PREV_DAY_COUNT_TYPE := P_PREV_DAY_COUNT_TYPE;
200 P_PREV_DAY_COUNT_TYPE := DAY_COUNT_TYPE;
201 END IF;
202 P_INTAMT_RUNNING := NVL(P_INTAMT_RUNNING
203 ,0) + LNU_INT_AMT;
204 P_SETAMT_RUNNING := (NVL(P_SETAMT_RUNNING
205 ,0) + NVL(ACC_INT_BF
206 ,0));
207 RETURN (NVL(P_INTAMT_RUNNING
208 ,0) - NVL(P_SETAMT_RUNNING
209 ,0));
210 END CF_INT_CFFORMULA;*/
211
212 FUNCTION FN_GET_DAYS(ANU_ST_DATE IN DATE) RETURN NUMBER IS
213 BEGIN
214 RETURN (0);
215 END FN_GET_DAYS;
216
217 FUNCTION CF_INTEREST_FOR_DUMMY_RECFORMU(CF_NO_OF_DAYS IN NUMBER
218 ,ROUNDING_FACTOR IN NUMBER
219 ,IG_YEAR_BASIS IN VARCHAR2
220 ,DAY_COUNT_TYPE IN VARCHAR2
221 ,SET_DATE IN DATE
222 ,PTY_CODE IN VARCHAR2
223 ,COMP_CODE IN VARCHAR2
224 ,CCY IN VARCHAR2
225 ,PRIN_ACTION IN VARCHAR2
226 ,CF_PRV_DAY IN DATE
227 ,TRANS_DATE IN DATE
228 ,BAL_BF IN NUMBER
229 ,CF_PRV_RATE IN NUMBER
230 ,ROUNDING_TYPE IN VARCHAR2) RETURN NUMBER IS
231 LNU_YEAR_DAYS NUMBER;
232 LNU_NO_OF_DAYS NUMBER := CF_NO_OF_DAYS;
233 LNU_YR_BASIS NUMBER;
234 LNU_ROUNDFAC NUMBER := ROUNDING_FACTOR;
235 LVR_YEAR_CALC_TYPE VARCHAR2(15) := IG_YEAR_BASIS;
236 LNU_INTEREST NUMBER;
237 LVR_DAY_COUNT_TYPE VARCHAR2(1) := DAY_COUNT_TYPE;
238 PRV_DATE DATE := SET_DATE;
239 L_OLDEST_DATE DATE := P_OLDEST_TRANSFER_DATE;
240 L_FIRST_TRANS_FLAG VARCHAR2(1) := 'N';
241 L_PRV_DAY_COUNT_TYPE VARCHAR2(1) := P_PREV_PREV_DAY_COUNT_TYPE;
242 L_DAY_COUNT_TYPE VARCHAR2(1) := P_PREV_DAY_COUNT_TYPE;
243
244 cursor OLDEST_DATE is
245 select min(TRANSFER_DATE)
246 from xtr_intergroup_transfers
247 where party_code = PTY_CODE
248 and company_code = COMP_CODE
249 and currency = CCY;
250
251 begin
252
253 open oldest_date;
254 fetch oldest_date into l_oldest_date;
255 close oldest_date;
256
257 if prin_action <> 'DUMMY' then
258 prv_date := cf_prv_day;
259 end if;
260
261 if l_day_count_type = 'B' and l_oldest_date = prv_date then
262 l_first_trans_flag := 'Y';
263 elsif (l_day_count_type = 'B' or l_day_count_type ='F') and l_oldest_date <> prv_date then
264 if l_day_count_type = 'F' and (l_prv_day_count_type = 'B' or l_prv_day_count_type = 'L') then
265 prv_date := prv_date + 1;
266 elsif l_day_count_type ='B' and l_prv_day_count_type = 'L' then
267 l_first_trans_flag := 'Y';
268 end if;
269 end if;
270
271 XTR_CALC_P.CALC_DAYS_RUN (prv_date,TRANS_DATE,Lvr_Year_calc_Type,Lnu_no_of_days,Lnu_Yr_Basis,Null,Lvr_day_count_type,l_first_trans_flag);
272 Lnu_interest := xtr_fps2_p.interest_round(BAL_BF * cf_prv_rate / 100 *Lnu_no_of_days /Lnu_yr_basis,Lnu_roundfac,rounding_type );
273 Return(Lnu_Interest);
274 end;
275
276 /* FUNCTION CF_INTEREST_FOR_DUMMY_RECFORMU(CF_NO_OF_DAYS IN NUMBER
277 ,ROUNDING_FACTOR IN NUMBER
278 ,IG_YEAR_BASIS IN VARCHAR2
279 ,DAY_COUNT_TYPE IN VARCHAR2
280 ,SET_DATE IN DATE
281 ,PTY_CODE IN VARCHAR2
282 ,COMP_CODE IN VARCHAR2
283 ,CCY IN VARCHAR2
284 ,PRIN_ACTION IN VARCHAR2
285 ,CF_PRV_DAY IN DATE
286 ,TRANS_DATE IN DATE
287 ,BAL_BF IN NUMBER
288 ,CF_PRV_RATE IN NUMBER
289 ,ROUNDING_TYPE IN VARCHAR2) RETURN NUMBER IS
290 LNU_YEAR_DAYS NUMBER;
291 LNU_NO_OF_DAYS NUMBER := CF_NO_OF_DAYS;
292 LNU_YR_BASIS NUMBER;
293 LNU_ROUNDFAC NUMBER := ROUNDING_FACTOR;
294 LVR_YEAR_CALC_TYPE VARCHAR2(15) := IG_YEAR_BASIS;
295 LNU_INTEREST NUMBER;
296 LVR_DAY_COUNT_TYPE VARCHAR2(1) := DAY_COUNT_TYPE;
297 PRV_DATE DATE := SET_DATE;
298 L_OLDEST_DATE DATE := P_OLDEST_TRANSFER_DATE;
299 L_FIRST_TRANS_FLAG VARCHAR2(1) := 'N';
300 L_PRV_DAY_COUNT_TYPE VARCHAR2(1) := P_PREV_PREV_DAY_COUNT_TYPE;
301 L_DAY_COUNT_TYPE VARCHAR2(1) := P_PREV_DAY_COUNT_TYPE;
302 CURSOR OLDEST_DATE IS
303 SELECT
304 MIN(TRANSFER_DATE)
305 FROM
306 XTR_INTERGROUP_TRANSFERS
307 WHERE PARTY_CODE = PTY_CODE
308 AND COMPANY_CODE = COMP_CODE
309 AND CURRENCY = CCY;
310 BEGIN
311 OPEN OLDEST_DATE;
312 FETCH OLDEST_DATE
313 INTO
314 L_OLDEST_DATE;
315 CLOSE OLDEST_DATE;
316 IF PRIN_ACTION < 'DUMMY' THEN
317 PRV_DATE := CF_PRV_DAY;
318 END IF;
319 IF L_DAY_COUNT_TYPE = 'B' AND L_OLDEST_DATE = PRV_DATE THEN
320 L_FIRST_TRANS_FLAG := 'Y';
321 ELSIF (L_DAY_COUNT_TYPE = 'B' OR L_DAY_COUNT_TYPE = 'F') AND L_OLDEST_DATE < PRV_DATE THEN
322 IF L_DAY_COUNT_TYPE = 'F' AND (L_PRV_DAY_COUNT_TYPE = 'B' OR L_PRV_DAY_COUNT_TYPE = 'L') THEN
323 PRV_DATE := PRV_DATE + 1;
324 ELSIF L_DAY_COUNT_TYPE = 'B' AND L_PRV_DAY_COUNT_TYPE = 'L' THEN
325 L_FIRST_TRANS_FLAG := 'Y';
326 END IF;
327 END IF;
328 XTR_CALC_P.CALC_DAYS_RUN(PRV_DATE
329 ,TRANS_DATE
330 ,LVR_YEAR_CALC_TYPE
331 ,LNU_NO_OF_DAYS
332 ,LNU_YR_BASIS
333 ,NULL
334 ,LVR_DAY_COUNT_TYPE
335 ,L_FIRST_TRANS_FLAG);
336 LNU_INTEREST := XTR_FPS2_P.INTEREST_ROUND(BAL_BF * CF_PRV_RATE / 100 * LNU_NO_OF_DAYS / LNU_YR_BASIS
337 ,LNU_ROUNDFAC
338 ,ROUNDING_TYPE);
339 RETURN (LNU_INTEREST);
340 END CF_INTEREST_FOR_DUMMY_RECFORMU;*/
341
342 FUNCTION CF_NO_OF_DAYSFORMULA(N_DAYS IN NUMBER
343 ,IG_YEAR_BASIS IN VARCHAR2
344 ,DAY_COUNT_TYPE IN VARCHAR2
345 ,SET_DATE IN DATE
346 ,PTY_CODE IN VARCHAR2
347 ,COMP_CODE IN VARCHAR2
348 ,CCY IN VARCHAR2
349 ,PRIN_ACTION IN VARCHAR2
350 ,CF_PRV_DAY IN DATE
351 ,TRANS_DATE IN DATE) RETURN NUMBER IS
352 LNU_NO_OF_DAYS NUMBER := N_DAYS;
353 LNU_YR_BASIS NUMBER;
354 LVR_YEAR_CALC_TYPE VARCHAR2(15) := IG_YEAR_BASIS;
355 LVR_DAY_COUNT_TYPE VARCHAR2(1) := DAY_COUNT_TYPE;
356 L_FIRST_TRANS_FLAG VARCHAR2(1) := 'N';
357 L_PRV_DAY_COUNT_TYPE VARCHAR2(1) := P_PREV_PREV_DAY_COUNT_TYPE;
358 L_DAY_COUNT_TYPE VARCHAR2(1) := P_PREV_DAY_COUNT_TYPE;
359 PRV_DATE DATE := SET_DATE;
360 L_OLDEST_DATE DATE := P_OLDEST_TRANSFER_DATE;
361 CURSOR OLDEST_DATE IS
362 SELECT
363 MIN(TRANSFER_DATE)
364 FROM
365 XTR_INTERGROUP_TRANSFERS
366 WHERE PARTY_CODE = PTY_CODE
367 AND COMPANY_CODE = COMP_CODE
368 AND CURRENCY = CCY;
369 BEGIN
370 OPEN OLDEST_DATE;
371 FETCH OLDEST_DATE
372 INTO
373 L_OLDEST_DATE;
374 CLOSE OLDEST_DATE;
375 IF PRIN_ACTION < 'DUMMY' THEN
376 PRV_DATE := CF_PRV_DAY;
377 END IF;
378 BEGIN
379 IF L_DAY_COUNT_TYPE = 'B' AND L_OLDEST_DATE = PRV_DATE THEN
380 L_FIRST_TRANS_FLAG := 'Y';
381 ELSIF (L_DAY_COUNT_TYPE = 'B' OR L_DAY_COUNT_TYPE = 'F') AND L_OLDEST_DATE < PRV_DATE THEN
382 IF L_DAY_COUNT_TYPE = 'F' AND (L_PRV_DAY_COUNT_TYPE = 'B' OR L_PRV_DAY_COUNT_TYPE = 'L') THEN
383 PRV_DATE := PRV_DATE + 1;
384 ELSIF L_DAY_COUNT_TYPE = 'B' AND L_PRV_DAY_COUNT_TYPE = 'L' THEN
385 L_FIRST_TRANS_FLAG := 'Y';
386 END IF;
387 END IF;
388 XTR_CALC_P.CALC_DAYS_RUN(PRV_DATE
389 ,TRANS_DATE
390 ,LVR_YEAR_CALC_TYPE
391 ,LNU_NO_OF_DAYS
392 ,LNU_YR_BASIS
393 ,NULL
394 ,LVR_DAY_COUNT_TYPE
395 ,L_FIRST_TRANS_FLAG);
396 RETURN (LNU_NO_OF_DAYS);
397 END;
398 END CF_NO_OF_DAYSFORMULA;
399
400 FUNCTION CF_PRV_DAYFORMULA(COMP_CODE IN VARCHAR2
401 ,PTY_CODE IN VARCHAR2
402 ,CCY IN VARCHAR2
403 ,TRANS_DATE IN DATE
404 ,TRANSACTION_NUMBER1 IN NUMBER) RETURN DATE IS
405 L_DATE DATE;
406 CURSOR PRV_DATE IS
407 SELECT
408 MAX(TRANSFER_DATE)
409 FROM
410 XTR_INTERGROUP_TRANSFERS
411 WHERE COMPANY_CODE = COMP_CODE
412 AND PARTY_CODE = PTY_CODE
413 AND CURRENCY = CCY
414 AND TRANSFER_DATE <= TRANS_DATE
415 AND TRANSACTION_NUMBER < TRANSACTION_NUMBER1;
416 BEGIN
417 OPEN PRV_DATE;
418 FETCH PRV_DATE
419 INTO
420 L_DATE;
421 CLOSE PRV_DATE;
422 RETURN (GREATEST(NVL(L_DATE
423 ,TRANS_DATE)
424 ,PER_FROM));
425 END CF_PRV_DAYFORMULA;
426
427 FUNCTION CF_PRV_RATEFORMULA(COMP_CODE IN VARCHAR2
428 ,PTY_CODE IN VARCHAR2
429 ,CCY IN VARCHAR2
430 ,TRANS_DATE IN DATE) RETURN NUMBER IS
431 L_RATE NUMBER;
432 CURSOR PRV_RATE IS
433 SELECT
434 INTEREST_RATE
435 FROM
436 XTR_INTERGROUP_TRANSFERS A
437 WHERE COMPANY_CODE = COMP_CODE
438 AND PARTY_CODE = PTY_CODE
439 AND CURRENCY = CCY
440 AND TRANSFER_DATE = (
441 SELECT
442 MAX(TRANSFER_DATE)
443 FROM
444 XTR_INTERGROUP_TRANSFERS B
445 WHERE A.DEAL_NUMBER = B.DEAL_NUMBER
446 AND TRANSFER_DATE < TRANS_DATE )
447 AND TRANSACTION_NUMBER = (
448 SELECT
449 MAX(TRANSACTION_NUMBER)
450 FROM
451 XTR_INTERGROUP_TRANSFERS C
452 WHERE A.DEAL_NUMBER = C.DEAL_NUMBER
453 AND TRANSFER_DATE < TRANS_DATE );
454 BEGIN
455 OPEN PRV_RATE;
456 FETCH PRV_RATE
457 INTO
458 L_RATE;
459 CLOSE PRV_RATE;
460 RETURN NVL(L_RATE
461 ,0);
462 END CF_PRV_RATEFORMULA;
463
464 FUNCTION BAL_BFFORMULA(COMP_CODE IN VARCHAR2
465 ,PTY_CODE IN VARCHAR2
466 ,CCY IN VARCHAR2
467 ,TRANS_DATE IN DATE) RETURN NUMBER IS
468 L_BAL_BF NUMBER;
469 CURSOR BAL_BF IS
470 SELECT
471 BALANCE_OUT
472 FROM
473 XTR_INTERGROUP_TRANSFERS A
474 WHERE COMPANY_CODE = COMP_CODE
475 AND PARTY_CODE = PTY_CODE
476 AND CURRENCY = CCY
477 AND TRANSFER_DATE = (
478 SELECT
479 MAX(TRANSFER_DATE)
480 FROM
481 XTR_INTERGROUP_TRANSFERS B
482 WHERE A.DEAL_NUMBER = B.DEAL_NUMBER
483 AND TRANSFER_DATE < TRANS_DATE )
484 AND TRANSACTION_NUMBER = (
485 SELECT
486 MAX(TRANSACTION_NUMBER)
487 FROM
488 XTR_INTERGROUP_TRANSFERS C
489 WHERE A.DEAL_NUMBER = C.DEAL_NUMBER
490 AND TRANSFER_DATE < TRANS_DATE );
491 BEGIN
492 OPEN BAL_BF;
493 FETCH BAL_BF
494 INTO
495 L_BAL_BF;
496 CLOSE BAL_BF;
497 RETURN NVL(L_BAL_BF
498 ,0);
499 END BAL_BFFORMULA;
500
501 FUNCTION CP_PARA_P RETURN VARCHAR2 IS
502 BEGIN
503 RETURN CP_PARA;
504 END CP_PARA_P;
505
506 END XTR_XTRIGSUM_XMLP_PKG;
507