1 PACKAGE BODY ce_bal_util AS
2 /*$Header: cebalutb.pls 120.2 2005/10/07 18:43:04 xxwang noship $ */
3
4 --l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5 --l_DEBUG varchar2(1) := 'Y';
6
7 /*=======================================================================+
8 | PUBLIC FUNCTION get_date_range |
9 | |
10 | DESCRIPTION |
11 | A pipelined function to return all the days between a date range. |
12 | |
13 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
14 | |
15 | ARGUMENTS |
16 | IN: |
17 | p_start_date Start Date. |
18 | p_end_date End Date. |
19 | OUT: |
20 | |
21 | RETURN |
22 | A table of days within the range. |
23 | MODIFICATION HISTORY |
24 | 03-FEB-2004 Xin Wang Created. |
25 +=======================================================================*/
26
27 FUNCTION get_date_range(p_start IN DATE,
28 p_end IN DATE) RETURN t_date_table PIPELINED IS
29 l_date ce_t_date:= ce_t_date(p_start);
30 BEGIN
31 loop
32 exit when l_date.single_date > p_end;
33 pipe row(l_date);
34 l_date.single_date := l_date.single_date + 1;
35 end loop;
36 return;
37 END;
38
39 FUNCTION get_balance(p_date IN DATE,
40 p_accts IN acct_id_refcursor)
41 RETURN t_balance_table PIPELINED IS
42 l_balance NUMBER;
43 in_rec p_accts%ROWTYPE;
44 CURSOR c_bal_1 IS
45 select value_dated_balance
46 from ce_bank_acct_balances
47 where balance_date = p_date
48 and bank_account_id = in_rec.account_id;
49 BEGIN
50 LOOP
51 FETCH p_accts INTO in_rec;
52 EXIT WHEN p_accts%NOTFOUND;
53 OPEN c_bal_1;
54 FETCH c_bal_1 INTO l_balance;
55 IF c_bal_1%NOTFOUND THEN
56 select Bal.value_dated_balance
57 into l_balance
58 from CE_BANK_ACCT_BALANCES Bal
59 where Bal.bank_account_id = in_rec.account_id
60 and trunc(Bal.balance_date) =
61 (select trunc(max(Bal2.balance_date))
62 from CE_BANK_ACCT_BALANCES Bal2
63 where Bal2.value_dated_balance is NOT NULL
64 and Bal2.bank_account_id = in_rec.account_id
65 and trunc(Bal2.balance_date) <= trunc(p_date));
66 END IF;
67 CLOSE c_bal_1;
68 pipe row(ce_t_balance(l_balance));
69 END LOOP;
70 CLOSE p_accts;
71 RETURN;
72 END get_balance;
73
74 /*
75 FUNCTION get_balance(p_acct_id IN NUMBER,
76 p_date IN DATE) RETURN NUMBER IS
77 CURSOR c_last_balance IS
78 SELECT Bal.interest_calculated_balance
79 FROM CE_BANK_ACCT_BALANCES Bal
80 WHERE Bal.bank_account_id = p_acct_id
81 AND Bal.balance_date =
82 (select max(Bal2.balance_date)
83 from CE_BANK_ACCT_BALANCES Bal2
84 where Bal2.interest_calculated_balance is NOT NULL
85 and Bal2.bank_account_id = p_acct_id
86 and trunc(Bal2.balance_date) <= trunc(p_date));
87 l_balance NUMBER := 0;
88 BEGIN
89 OPEN c_last_balance;
90 FETCH c_last_balance INTO l_balance;
91 IF c_last_balance%NOTFOUND THEN
92 l_balance := -1;
93 END IF;
94 CLOSE c_last_balance;
95
96 return l_balance;
97 END get_balance;
98 */
99
100
101 FUNCTION get_pool_balance (p_cashpool_id IN NUMBER,
102 p_balance_date IN DATE) RETURN NUMBER IS
103 CURSOR c_sub_accts IS
104 SELECT account_id
105 FROM ce_cashpool_sub_accts
106 WHERE cashpool_id = p_cashpool_id;
107 CURSOR c_bal(p_acct_id NUMBER) IS
108 select value_dated_balance
109 from ce_bank_acct_balances
110 where balance_date = p_balance_date
111 and bank_account_id = p_acct_id
112 and value_dated_balance is NOT NULL;
113 CURSOR c_last_bal(p_acct_id NUMBER) IS
114 select ce_bal.value_dated_balance
115 from CE_BANK_ACCT_BALANCES ce_bal
116 where ce_bal.bank_account_id = p_acct_id
117 and trunc(ce_bal.balance_date) =
118 (select trunc(max(ce_bal2.balance_date))
122 and trunc(ce_bal2.balance_date) <= trunc(p_balance_date));
119 from CE_BANK_ACCT_BALANCES ce_bal2
120 where ce_bal2.value_dated_balance is NOT NULL
121 and ce_bal2.bank_account_id = p_acct_id
123 l_acct_id NUMBER;
124 l_balance NUMBER := 0;
125 l_total_balance NUMBER := 0;
126 BEGIN
127 open c_sub_accts;
128
129 LOOP
130 FETCH c_sub_accts INTO l_acct_id;
131 EXIT WHEN c_sub_accts%NOTFOUND;
132
133 OPEN c_bal(l_acct_id);
134 FETCH c_bal INTO l_balance;
135 IF c_bal%NOTFOUND THEN
136 OPEN c_last_bal(l_acct_id);
137 FETCH c_last_bal INTO l_balance;
138 IF c_last_bal%NOTFOUND THEN
139 l_balance := 0;
140 END IF;
141 CLOSE c_last_bal;
142 /*
143 select ce_bal.value_dated_balance
144 into l_balance
145 from CE_BANK_ACCT_BALANCES ce_bal
146 where ce_bal.bank_account_id = l_acct_id
147 and trunc(ce_bal.balance_date) =
148 (select trunc(max(ce_bal2.balance_date))
149 from CE_BANK_ACCT_BALANCES ce_bal2
150 where ce_bal2.value_dated_balance is NOT NULL
151 and ce_bal2.bank_account_id = l_acct_id
152 and trunc(ce_bal2.balance_date) <= trunc(p_balance_date));
153 IF SQL%ROWCOUNT = 0 THEN
154 l_balance := 0;
155 END IF;
156 */
157 END IF;
158 CLOSE c_bal;
159
160 IF l_balance is null THEN
161 l_balance := 0;
162 END IF;
163
164 l_total_balance := l_total_balance + l_balance;
165
166 END LOOP;
167
168 CLOSE c_sub_accts;
169
170 return l_total_balance;
171 END get_pool_balance;
172
173 END ce_bal_util;
[Home] [Help]