[Home] [Help]
PACKAGE BODY: APPS.OKL_CASH_RULES_SUMRY_PVT
Source
1 PACKAGE BODY OKL_CASH_RULES_SUMRY_PVT AS
2 /* $Header: OKLRCSYB.pls 120.2 2006/07/11 09:44:45 dkagrawa noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE handle_cash_sumry
6 ---------------------------------------------------------------------------
7
8 PROCEDURE handle_cash_rl_sumry ( p_api_version IN NUMBER
9 ,p_init_msg_list IN VARCHAR2
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ,x_msg_count OUT NOCOPY NUMBER
12 ,x_msg_data OUT NOCOPY VARCHAR2
13 ,p_cash_rl_tbl IN okl_cash_rl_sumry_tbl_type
14 ) IS
15
16 ---------------------------
17 -- DECLARE Local Variables
18 ---------------------------
19
20 l_api_version NUMBER := 1;
21 l_api_name CONSTANT VARCHAR2(30) := 'handle_cash_rl_sumry';
22 l_init_msg_list VARCHAR2(1) ;
23 l_return_status VARCHAR2(1);
24 l_msg_count NUMBER := 0;
25 l_msg_data VARCHAR2(2000);
26
27
28 ------------------------------
29 -- DECLARE Record/Table Types
30 ------------------------------
31
32 -- Internal Trans
33
34 l_cash_rl_tbl OKL_CASH_RL_SUMRY_TBL_TYPE;
35
36 l_catv_rec OKL_CAT_PVT.CATV_REC_TYPE;
37 null_catv_rec OKL_CAT_PVT.CATV_REC_TYPE;
38 x_catv_rec OKL_CAT_PVT.CATV_REC_TYPE;
39
40 l_catv_tbl OKL_CAT_PVT.CATV_TBL_TYPE;
41 x_catv_tbl OKL_CAT_PVT.CATV_TBL_TYPE;
42
43 -------------------
44 -- DECLARE Cursors
45 -------------------
46 CURSOR get_this_car_id( p_cat_id NUMBER ) IS
47 SELECT B.cau_id, A.NAME, B.DEFAULT_RULE
48 FROM OKL_CSH_ALLCTN_RL_HDR A, OKL_CASH_ALLCTN_RLS B
49 WHERE A.ID = B.CAU_ID
50 AND B.ID = p_cat_id;
51
52 CURSOR get_last_cat_id ( p_car_id NUMBER ) IS
53 SELECT ID
54 FROM OKL_CASH_ALLCTN_RLS
55 WHERE cau_ID = p_car_id AND
56 (end_date >= trunc(sysdate) OR end_date IS NULL)
57 ORDER BY START_DATE DESC;
58
59 -------------------
60 l_this_car_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE;
61 l_last_cat_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE;
62 l_this_car_name OKL_CSH_ALLCTN_RL_HDR.NAME%TYPE;
63
64 l_default_rule OKL_CASH_ALLCTN_RLS.DEFAULT_RULE%TYPE;
65
66 BEGIN
67
68 l_return_status := OKL_API.START_ACTIVITY(l_api_name
69 ,G_PKG_NAME
70 ,p_init_msg_list
71 ,l_api_version
72 ,p_api_version
73 ,'_PVT'
74 ,l_return_status);
75
76 IF (l_return_status = OKL_API.g_ret_sts_unexp_error) THEN
77 RAISE OKL_API.g_exception_unexpected_error;
78 ELSIF (l_return_Status = OKL_API.g_ret_sts_error) THEN
79 RAISE OKL_API.g_exception_error;
80 END IF;
81
82
83 l_cash_rl_tbl := p_cash_rl_tbl;
84
85 FOR i IN p_cash_rl_tbl.first..p_cash_rl_tbl.LAST LOOP
86
87 -- Initialize Record
88 l_catv_rec := null_catv_rec;
89 x_catv_rec := null_catv_rec;
90
91 --Check if it is the last record
92 l_this_car_id := NULL;
93 l_last_cat_id := NULL;
94
95 OPEN get_this_car_id( p_cash_rl_tbl(i).ID );
96 FETCH get_this_car_id INTO l_this_car_id, l_this_car_name, l_default_rule;
97 CLOSE get_this_car_id;
98
99 OPEN get_last_cat_id( l_this_car_id );
100 FETCH get_last_cat_id INTO l_last_cat_id;
101 CLOSE get_last_cat_id;
102
103 IF l_default_rule = 'YES' THEN
104
105 -- You cannot delete default rules.
106 OKC_API.set_message( p_app_name => G_APP_NAME
107 ,p_msg_name => 'OKL_BPD_CANNOT_DEL_DEF_RL'
108 );
109
110 RAISE G_EXCEPTION_HALT_VALIDATION;
111
112 END IF;
113
114
115 IF (p_cash_rl_tbl(i).ID = l_last_cat_id) THEN
116 -- Populate Record for Update
117 l_catv_rec.ID := p_cash_rl_tbl(i).ID;
118 l_catv_rec.END_DATE := (TRUNC(SYSDATE) - 1);
119
120 Okl_Cash_Allctn_Rls_Pub.update_cash_allctn_rls( p_api_version => p_api_version
121 ,p_init_msg_list => p_init_msg_list
122 ,x_return_status => l_return_status
123 ,x_msg_count => l_msg_count
124 ,x_msg_data => l_msg_data
125 ,p_catv_rec => l_catv_rec
126 ,x_catv_rec => x_catv_rec
127 );
128
129 IF (l_return_status = OKL_API.g_ret_sts_unexp_error) THEN
130 RAISE OKL_API.g_exception_unexpected_error;
131 ELSIF (l_return_Status = OKL_API.g_ret_sts_error) THEN
132 RAISE OKL_API.g_exception_error;
133 END IF;
134 ELSE
135
136 -- Cash application rule NAME has one or more future versions. Please delete these first.
137 OKC_API.set_message( p_app_name => G_APP_NAME
138 ,p_msg_name => 'OKL_BPD_DEL_FUT_RLS_FIRST'
139 ,p_token1 => 'NAME'
140 ,p_token1_value => l_this_car_name
141 );
142
143 RAISE G_EXCEPTION_HALT_VALIDATION;
144 END IF;
145 END LOOP;
146
147 /*
148 IF l_cash_rl_tbl.COUNT > 0 THEN
149
150 LOOP
151
152 EXIT WHEN (i = l_cash_rl_tbl.LAST);
153 i := i + 1;
154
155 -- for the rules to be de-activated, set the end date to sysdate - 1
156
157 l_catv_tbl(i).ID := l_cash_rl_tbl(i).ID;
158 l_catv_tbl(i).END_DATE := (TRUNC(SYSDATE) - 1);
159
160 END LOOP;
161
162 -- update okl_cash_allctn_rls table
163
164 Okl_Cash_Allctn_Rls_Pub.update_cash_allctn_rls( p_api_version => p_api_version
165 ,p_init_msg_list => p_init_msg_list
166 ,x_return_status => l_return_status
167 ,x_msg_count => l_msg_count
168 ,x_msg_data => l_msg_data
169 ,p_catv_tbl => l_catv_tbl
170 ,x_catv_tbl => l_catv_tbl
171 );
172
173 IF (l_return_status = OKL_API.g_ret_sts_unexp_error) THEN
174 RAISE OKL_API.g_exception_unexpected_error;
175 ELSIF (l_return_Status = OKL_API.g_ret_sts_error) THEN
176 RAISE OKL_API.g_exception_error;
177 END IF;
178
179 END IF;
180 */
181
182 x_return_status := l_return_status;
183 x_msg_data := l_msg_data;
184 x_msg_count := l_msg_count;
185
186 OKL_API.end_activity(x_msg_count, x_msg_data);
187
188 EXCEPTION
189
190 WHEN G_EXCEPTION_HALT_VALIDATION THEN
191 x_return_status := OKL_API.G_RET_STS_ERROR;
192 x_return_status := OKL_API.HANDLE_EXCEPTIONS
193 (
194 l_api_name,
195 G_PKG_NAME,
196 'OKL_API.G_RET_STS_ERROR',
197 x_msg_count,
198 x_msg_data,
199 '_PVT'
200 );
201
202
203 WHEN OKL_API.g_exception_error THEN
204 x_return_status := OKL_API.HANDLE_EXCEPTIONS
205 (
206 l_api_name,
207 G_PKG_NAME,
208 'OKL_API.G_RET_STS_ERROR',
209 x_msg_count,
210 x_msg_data,
211 '_PVT'
212 );
213
214 WHEN OKL_API.g_exception_unexpected_error THEN
215 x_return_status := OKL_API.HANDLE_EXCEPTIONS
216 (
217 l_api_name,
218 G_PKG_NAME,
219 'OKL_API.G_RET_STS_ERROR',
220 x_msg_count,
221 x_msg_data,
222 '_PVT'
223 );
224
225 WHEN OTHERS THEN
226 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
227 OKL_API.set_message( p_app_name => g_app_name
228 , p_msg_name => g_unexpected_error
229 , p_token1 => g_sqlcode_token
230 , p_token1_value => SQLCODE
231 , p_token2 => g_sqlerrm_token
232 , p_token2_value => SQLERRM
233 ) ;
234
235 END handle_cash_rl_sumry;
236 END OKL_CASH_RULES_SUMRY_PVT;