[Home] [Help]
PACKAGE BODY: APPS.OKL_BTCH_CASH_SUMRY_PVT
Source
1 PACKAGE BODY OKL_BTCH_CASH_SUMRY_PVT AS
2 /* $Header: OKLRBASB.pls 120.2 2007/11/13 10:02:57 ansethur ship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE handle_manual_pay
6 ---------------------------------------------------------------------------
7
8 PROCEDURE handle_batch_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_btch_tbl IN okl_btch_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_batch_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 l_trx_status_code OKL_TRX_CSH_BATCH_B.TRX_STATUS_CODE%TYPE DEFAULT NULL;
28
29 i NUMBER DEFAULT NULL;
30 j NUMBER DEFAULT NULL;
31 k NUMBER DEFAULT NULL;
32
33 /*
34 l_org_id OKL_TRX_CSH_BATCH_B.ORG_ID%TYPE DEFAULT NULL;
35
36 l_id OKL_TRX_CSH_BATCH_B.ID%TYPE DEFAULT NULL;
37 l_name OKL_TRX_CSH_BATCH_TL.NAME%TYPE DEFAULT NULL;
38 l_batch_qty OKL_TRX_CSH_BATCH_B.BATCH_QTY%TYPE DEFAULT NULL;
39 l_batch_total OKL_TRX_CSH_BATCH_B.BATCH_TOTAL%TYPE DEFAULT NULL;
40 l_currency_code OKL_TRX_CSH_RECEIPT_B.CURRENCY_CODE%TYPE DEFAULT NULL;
41 l_date_deposit OKL_TRX_CSH_BATCH_B.DATE_DEPOSIT%TYPE DEFAULT NULL;
42 l_date_gl_requested OKL_TRX_CSH_BATCH_B.DATE_GL_REQUESTED%TYPE DEFAULT NULL;
43 l_trx_status_code OKL_TRX_CSH_BATCH_B.TRX_STATUS_CODE%TYPE DEFAULT NULL;
44 */
45
46 ------------------------------
47 -- DECLARE Record/Table Types
48 ------------------------------
49
50 -- Internal Trans
51
52 l_btch_tbl OKL_BTCH_SUMRY_TBL_TYPE;
53
54 l_btcv_tbl OKL_BTC_PVT.BTCV_TBL_TYPE;
55 x_btcv_tbl OKL_BTC_PVT.BTCV_TBL_TYPE;
56
57 l_rctv_rec OKL_RCT_PVT.RCTV_REC_TYPE;
58 x_rctv_rec OKL_RCT_PVT.RCTV_REC_TYPE;
59 l_rctv_tbl OKL_RCT_PVT.RCTV_TBL_TYPE;
60 x_rctv_tbl OKL_RCT_PVT.RCTV_TBL_TYPE;
61
62 l_rcav_rec OKL_RCA_PVT.RCAV_REC_TYPE;
63 x_rcav_rec OKL_RCA_PVT.RCAV_REC_TYPE;
64 l_rcav_tbl OKL_RCA_PVT.RCAV_TBL_TYPE;
65 x_rcav_tbl Okl_RCA_PVT.RCAV_TBL_TYPE;
66
67 l_btc_init OKL_BTC_PVT.BTCV_TBL_TYPE;
68 l_rct_init OKL_RCT_PVT.RCTV_TBL_TYPE;
69 l_rca_init OKL_RCA_PVT.RCAV_TBL_TYPE;
70
71 -------------------
72 -- DECLARE Cursors
73 -------------------
74
75 -- get the batch status.
76 CURSOR get_btch_stat (cp_btc_id IN NUMBER) IS
77 SELECT btc.trx_status_code
78 FROM OKL_TRX_CSH_BATCH_V btc
79 WHERE btc.id = cp_btc_id;
80
81 -------------------
82 -- get the rct_id's ready for deletion.
83 CURSOR get_rct_id (cp_btc_id IN NUMBER) IS
84 SELECT rct.id
85 FROM OKL_TRX_CSH_RECEIPT_V rct
86 WHERE rct.btc_id = cp_btc_id;
87
88 -------------------
89
90 -- get the rct_id's ready for deletion.
91 CURSOR get_rca_id (cp_rct_id IN NUMBER) IS
92 SELECT rca.id
93 FROM OKL_TXL_RCPT_APPS_V rca
94 WHERE rca.rct_id_details = cp_rct_id;
95
96 -------------------
97
98 BEGIN
99
100 l_return_status := OKL_API.START_ACTIVITY(l_api_name
101 ,G_PKG_NAME
102 ,p_init_msg_list
103 ,l_api_version
104 ,p_api_version
105 ,'_PVT'
106 ,l_return_status);
107
108 IF (l_return_status = OKL_API.g_ret_sts_unexp_error) THEN
109 RAISE OKL_API.g_exception_unexpected_error;
110 ELSIF (l_return_Status = OKL_API.g_ret_sts_error) THEN
111 RAISE OKL_API.g_exception_error;
112 END IF;
113
114 l_btch_tbl := p_btch_tbl;
115
116 i := 0;
117 j := 0;
118
119 IF l_btch_tbl.COUNT > 0 THEN
120
121 l_btcv_tbl := l_btc_init;
122 l_rctv_tbl := l_rct_init;
123 l_rcav_tbl := l_rca_init;
124
125 LOOP
126
127 EXIT WHEN (i = l_btch_tbl.LAST);
128 i := i + 1;
129
130 OPEN get_btch_stat(l_btch_tbl(i).id);
131 FETCH get_btch_stat INTO l_trx_status_code;
132 CLOSE get_btch_stat;
133 --ansethur 13-nov-2007 allow update and resubmission of errored
134 --batches - removing the errored status to allow removal
135 IF l_trx_status_code NOT IN ('PROCESSED') THEN --,'ERROR'
136
137 l_btcv_tbl(i).id := l_btch_tbl(i).id; -- build up btc table
138
139 OPEN get_rct_id(l_btcv_tbl(i).id); -- get all the rct_id's for each batch
140 LOOP
141 EXIT WHEN get_rct_id%NOTFOUND;
142 j := j + 1;
143 FETCH get_rct_id INTO l_rctv_tbl(j).id;
144 END LOOP;
145 CLOSE get_rct_id;
146
147 END IF;
148
149 END LOOP;
150
151 END IF;
152
153 j := 0;
154 k := 0;
155
156 IF l_rctv_tbl.COUNT > 0 THEN
157
158 LOOP
159
160 EXIT WHEN (j = l_rctv_tbl.LAST);
161 j := j + 1;
162
163 OPEN get_rca_id(l_rctv_tbl(j).id); -- get all the rca_id's for each rct_id
164 LOOP
165 EXIT WHEN get_rca_id%NOTFOUND;
166 k := k + 1;
167 FETCH get_rca_id INTO l_rcav_tbl(k).id;
168 END LOOP;
169 CLOSE get_rca_id;
170
171 END LOOP;
172
173 END IF;
174
175 IF l_btcv_tbl.COUNT > 0 THEN -- batch level
176
177 okl_trx_csh_batch_pub.delete_trx_csh_batch( p_api_version => l_api_version
178 ,p_init_msg_list => l_init_msg_list
179 ,x_return_status => l_return_status
180 ,x_msg_count => l_msg_count
181 ,x_msg_data => l_msg_data
182 ,p_btcv_tbl => l_btcv_tbl
183 );
184
185 IF (l_return_status = OKL_API.g_ret_sts_unexp_error) THEN
186 RAISE OKL_API.g_exception_unexpected_error;
187 ELSIF (l_return_Status = OKL_API.g_ret_sts_error) THEN
188 RAISE OKL_API.g_exception_error;
189 END IF;
190
191 END IF;
192
193 IF l_rctv_tbl.COUNT > 0 THEN -- receipt level
194
195
196 OKL_INCSH_PVT.delete_internal_trans(p_api_version => l_api_version ,
197 p_init_msg_list => l_init_msg_list,
198 x_return_status => l_return_status,
199 x_msg_count => l_msg_count,
200 x_msg_data => l_msg_data,
201 p_rctv_tbl => l_rctv_tbl
202 );
203
204 IF (l_return_status = OKL_API.g_ret_sts_unexp_error) THEN
205 RAISE OKL_API.g_exception_unexpected_error;
206 ELSIF (l_return_Status = OKL_API.g_ret_sts_error) THEN
207 RAISE OKL_API.g_exception_error;
208 END IF;
209
210 END IF;
211
212 IF l_rcav_tbl.COUNT > 0 THEN -- receipt application level
213
214
215 OKL_INCSH_PVT.delete_internal_trans(p_api_version => l_api_version ,
216 p_init_msg_list => l_init_msg_list,
217 x_return_status => l_return_status,
218 x_msg_count => l_msg_count,
219 x_msg_data => l_msg_data,
220 p_rcav_tbl => l_rcav_tbl
221 );
222
223 IF (l_return_status = OKL_API.g_ret_sts_unexp_error) THEN
224 RAISE OKL_API.g_exception_unexpected_error;
225 ELSIF (l_return_Status = OKL_API.g_ret_sts_error) THEN
226 RAISE OKL_API.g_exception_error;
227 END IF;
228
229 END IF;
230
231 x_return_status := l_return_status;
232 x_msg_data := l_msg_data;
233 x_msg_count := l_msg_count;
234
235 OKL_API.end_activity(x_msg_count, x_msg_data);
236
237 EXCEPTION
238
239 WHEN G_EXCEPTION_HALT_VALIDATION THEN
240 x_return_status := OKL_API.G_RET_STS_ERROR;
241 x_return_status := OKL_API.HANDLE_EXCEPTIONS
242 (
243 l_api_name,
244 G_PKG_NAME,
245 'OKL_API.G_RET_STS_ERROR',
246 x_msg_count,
247 x_msg_data,
248 '_PVT'
249 );
250
251
252 WHEN OKL_API.g_exception_error THEN
253 x_return_status := OKL_API.HANDLE_EXCEPTIONS
254 (
255 l_api_name,
256 G_PKG_NAME,
257 'OKL_API.G_RET_STS_ERROR',
258 x_msg_count,
259 x_msg_data,
260 '_PVT'
261 );
262
263 WHEN OKL_API.g_exception_unexpected_error THEN
264 x_return_status := OKL_API.HANDLE_EXCEPTIONS
265 (
266 l_api_name,
267 G_PKG_NAME,
268 'OKL_API.G_RET_STS_ERROR',
269 x_msg_count,
270 x_msg_data,
271 '_PVT'
272 );
273
274 WHEN OTHERS THEN
275 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
276 OKL_API.set_message( p_app_name => g_app_name
277 , p_msg_name => g_unexpected_error
278 , p_token1 => g_sqlcode_token
279 , p_token1_value => SQLCODE
280 , p_token2 => g_sqlerrm_token
281 , p_token2_value => SQLERRM
282 ) ;
283
284 END handle_batch_sumry;
285 END OKL_BTCH_CASH_SUMRY_PVT;