[Home] [Help]
PACKAGE BODY: APPS.OKL_REV_LOSS_PROV_PVT
Source
1 PACKAGE BODY OKL_REV_LOSS_PROV_PVT AS
2 /* $Header: OKLRRPVB.pls 120.8.12010000.2 2008/08/28 22:57:26 sgiyer ship $ */
3
4 G_PRIMARY CONSTANT VARCHAR2(200) := 'PRIMARY';
5
6 -- this procedure reverses specific loss provision,general loss provision or both transactions
7 PROCEDURE REVERSE_LOSS_PROVISIONS (
8 p_api_version IN NUMBER
9 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
10 ,x_msg_count OUT NOCOPY NUMBER
11 ,x_msg_data OUT NOCOPY VARCHAR2
12 ,x_return_status OUT NOCOPY VARCHAR2
13 ,p_lprv_rec IN lprv_rec_type)
14
15 IS
16 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
17
18 /* variables */
19 l_api_name CONSTANT VARCHAR2(40) := 'REVERSE_LOSS_PROVISIONS';
20 l_api_version CONSTANT NUMBER := 1.0;
21 l_source_table CONSTANT OKL_TRNS_ACC_DSTRS.SOURCE_TABLE%TYPE := 'OKL_TXL_CNTRCT_LNS';
22 l_cntrct_id OKL_K_HEADERS_FULL_V.ID%TYPE;
23 l_sysdate DATE := SYSDATE;
24 l_reversal_date DATE;
25 l_COUNT NUMBER :=0;
26 /* record and table structure variables */
27 l_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
28 x_tclv_tbl OKL_TRX_CONTRACTS_PUB.tclv_tbl_type;
29 l_tcnv_tbl OKL_TRX_CONTRACTS_PUB.tcnv_tbl_type;
30 x_tcnv_tbl OKL_TRX_CONTRACTS_PUB.tcnv_tbl_type;
31 l_source_id_tbl OKL_REVERSAL_PUB.source_id_tbl_type;
32 --
33 TYPE t_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
34 l_trx_date_tbl t_date;
35 --
36 -- Cursor to select transaction headers for reversal
37 CURSOR reverse_trx_csr(p_khr_id NUMBER, p_tcn_type VARCHAR2) IS
38 SELECT id, date_transaction_occurred, transaction_date
39 FROM OKL_TRX_CONTRACTS
40 WHERE khr_id = p_khr_id
41 AND tsu_code = 'PROCESSED'
42 AND tcn_type = p_tcn_type
43 AND representation_type = G_PRIMARY;
44
45 -- Cursor to select transaction lines for reversal
46 CURSOR reverse_txl_csr(p_khr_id NUMBER, p_tcn_id NUMBER, p_tcn_type VARCHAR2) IS
47 SELECT txl.id, txl.amount, txl.currency_code
48 FROM OKL_TXL_CNTRCT_LNS txl, OKL_TRX_CONTRACTS trx
49 WHERE trx.khr_id = p_khr_id
50 AND txl.tcn_id = trx.id
51 AND txl.tcn_id = p_tcn_id --trx.id
52 AND trx.tsu_code = 'PROCESSED'
53 AND trx.tcn_type = p_tcn_type
54 AND trx.representation_type = G_PRIMARY;
55
56 -- Cursor to select all transaction headers for reversal
57 CURSOR reverse_all_trx_csr(p_khr_id NUMBER) IS
58 SELECT id, date_transaction_occurred, transaction_date
59 FROM OKL_TRX_CONTRACTS
60 WHERE khr_id = p_khr_id
61 AND tsu_code = 'PROCESSED'
62 AND (tcn_type = 'PSP' OR tcn_type = 'PGL')
63 AND representation_type = G_PRIMARY;
64
65 -- Cursor to select all transaction lines for reversal
66 CURSOR reverse_all_txl_csr(p_khr_id NUMBER, p_tcn_id NUMBER) IS
67 SELECT txl.id, txl.amount, txl.currency_code
68 FROM OKL_TXL_CNTRCT_LNS txl, OKL_TRX_CONTRACTS trx
69 WHERE trx.khr_id = p_khr_id
70 AND txl.tcn_id = trx.id
71 AND txl.tcn_id = p_tcn_id --trx.id
72 AND trx.tsu_code = 'PROCESSED'
73 AND (trx.tcn_type = 'PSP' OR trx.tcn_type = 'PGL')
74 AND trx.representation_type = G_PRIMARY;
75
76 -- Cursor to select the contract number for the given contract id
77 CURSOR contract_num_csr (p_ctr_num VARCHAR2) IS
78 SELECT id
79 FROM OKC_K_HEADERS_ALL_B
80 WHERE CONTRACT_NUMBER = p_ctr_num;
81
82 BEGIN
83
84 l_return_status := OKL_API.START_ACTIVITY(l_api_name
85 ,G_PKG_NAME
86 ,p_init_msg_list
87 ,l_api_version
88 ,p_api_version
89 ,'_PVT'
90 ,l_return_status);
91
92 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
93 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
94 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
95 RAISE OKL_API.G_EXCEPTION_ERROR;
96 END IF;
97
98 /* validate input record parameters */
99 IF (p_lprv_rec.cntrct_num IS NULL) THEN
100 Okl_Api.set_message(p_app_name => g_app_name,
101 p_msg_name => 'OKL_REV_LPV_CNTRCT_ERROR');
102 RAISE OKL_API.G_EXCEPTION_ERROR;
103 END IF;
104
105 -- get the contract number
106 OPEN contract_num_csr(p_lprv_rec.cntrct_num);
107 FETCH contract_num_csr INTO l_cntrct_id;
108 IF contract_num_csr%NOTFOUND THEN
109 CLOSE contract_num_csr;
110 Okl_Api.set_message(p_app_name => g_app_name,
111 p_msg_name => 'OKL_REV_LPV_CNTRCT_NUM_ERROR');
112 RAISE OKL_API.G_EXCEPTION_ERROR;
113 END IF;
114 CLOSE contract_num_csr;
115
116 /* validate input record parameters */
117 IF (p_lprv_rec.reversal_type IS NULL) THEN
118 -- Open reverse trx csr all for update of transaction header
119 --Bug 6961282. Used l_COUNT variable instead of cursor percent rowcount
120 FOR l_reverse_trx_csr IN reverse_all_trx_csr(l_cntrct_id)
121 LOOP
122 l_tcnv_tbl(l_COUNT).id := l_reverse_trx_csr.id;
123 l_trx_date_tbl(l_COUNT) := l_reverse_trx_csr.date_transaction_occurred;
124 l_tcnv_tbl(l_COUNT).transaction_date := l_reverse_trx_csr.transaction_date;
125 l_COUNT := l_COUNT+1;
126 END LOOP;
127 ELSIF (p_lprv_rec.reversal_type IN ('PSP','PGL')) THEN
128 -- Open reverse trx csr for update of transaction header
129 FOR l_reverse_trx_csr IN reverse_trx_csr(l_cntrct_id, p_lprv_rec.reversal_type)
130 LOOP
131 l_tcnv_tbl(l_COUNT).id := l_reverse_trx_csr.id;
132 l_trx_date_tbl(l_COUNT) := l_reverse_trx_csr.date_transaction_occurred;
133 l_tcnv_tbl(l_COUNT).transaction_date := l_reverse_trx_csr.transaction_date;
134 l_COUNT := l_COUNT+1;
135 END LOOP;
136 ELSE
137 Okl_Api.set_message(p_app_name => g_app_name,
138 p_msg_name => 'OKL_REV_LPV_TYPE_ERROR');
139 END IF;
140
141 l_COUNT :=0;
142
143 /* -- Bug 6194225 and 6194204 .. Reversals should be done by the transaction
144 and cannot be the same for all loss provision transactions.
145 IF p_lprv_rec.reversal_date IS NULL
146 OR p_lprv_rec.reversal_date = OKL_API.G_MISS_DATE THEN
147 l_reversal_date := l_sysdate;
148 ELSE
149 l_reversal_date := p_lprv_rec.reversal_date;
150 END IF;
151 */
152
153 IF l_tcnv_tbl.COUNT > 0 THEN
154 -- proceed only if records found for reversal
155
156 /* moved this logic down to process by tcn_id .. for bugs 6194225 and 6194204
157 IF p_lprv_rec.reversal_type IS NOT NULL THEN
158 -- Open reverse txl cursor to find out transaction line id's for reversal
159 FOR l_reverse_txl_csr IN reverse_txl_csr(l_cntrct_id, p_lprv_rec.reversal_type)
160 LOOP
161 l_source_id_tbl(reverse_txl_csr%ROWCOUNT) := l_reverse_txl_csr.id;
162 END LOOP;
163 ELSE
164 -- Open reverse all txl cursor to find out transaction line id's for reversal
165 FOR l_reverse_txl_csr IN reverse_all_txl_csr(l_cntrct_id)
166 LOOP
167 l_source_id_tbl(reverse_all_txl_csr%ROWCOUNT) := l_reverse_txl_csr.id;
168 END LOOP;
169 END IF;
170 */
171 -- Build the transaction record for update
172 FOR i IN l_tcnv_tbl.FIRST..l_tcnv_tbl.LAST
173 LOOP
174 l_tcnv_tbl(i).tsu_code := 'CANCELED';
175 --l_tcnv_tbl(i).canceled_date :=l_reversal_date; -- Bugs 6194225 and 6194204 .. logic below
176
177 IF p_lprv_rec.reversal_date IS NULL OR p_lprv_rec.reversal_date = OKL_API.G_MISS_DATE THEN
178 l_reversal_date := GREATEST(TRUNC(SYSDATE), l_trx_date_tbl(i));
179 ELSE
180 l_reversal_date := GREATEST(l_trx_date_tbl(i), p_lprv_rec.reversal_date);
181 END IF;
182
183 l_tcnv_tbl(i).canceled_date :=l_reversal_date;
184
185 END LOOP;
186
187 -- New code to process reversals by tcn_id .. bugs 6194225 and 6194204
188 FOR i IN l_tcnv_tbl.FIRST..l_tcnv_tbl.LAST LOOP
189 IF p_lprv_rec.reversal_type IS NOT NULL THEN
190 -- Open reverse txl cursor to find out transaction line id's for reversal
191 FOR l_reverse_txl_csr IN reverse_txl_csr(l_cntrct_id, l_tcnv_tbl(i).id, p_lprv_rec.reversal_type)
192 LOOP
193 l_source_id_tbl(l_COUNT) := l_reverse_txl_csr.id;
194 l_COUNT := l_COUNT+1;
195 END LOOP;
196 ELSE
197 -- Open reverse all txl cursor to find out transaction line id's for reversal
198 FOR l_reverse_txl_csr IN reverse_all_txl_csr(l_cntrct_id, l_tcnv_tbl(i).id)
199 LOOP
200 l_source_id_tbl(l_COUNT) := l_reverse_txl_csr.id;
201 l_COUNT:=l_COUNT+1;
202 END LOOP;
203 END IF;
204
205 -- reverse accounting entries
206 Okl_Reversal_Pub.REVERSE_ENTRIES(
207 p_api_version => p_api_version,
208 p_init_msg_list => p_init_msg_list,
209 x_return_status => l_return_status,
210 x_msg_count => x_msg_count,
211 x_msg_data => x_msg_data,
212 p_source_table => l_source_table,
213 p_acct_date => l_tcnv_tbl(i).canceled_date, -- l_reversal_date,
214 p_source_id_tbl => l_source_id_tbl);
215
216 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
217 Okl_Api.set_message(p_app_name => g_app_name,
218 p_msg_name => 'OKL_AGN_REV_DIST_ERROR',
219 p_token1 => g_contract_number_token,
220 p_token1_value => p_lprv_rec.cntrct_num);
221 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
222 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
223 Okl_Api.set_message(p_app_name => g_app_name,
224 p_msg_name => 'OKL_AGN_REV_DIST_ERROR',
225 p_token1 => g_contract_number_token,
226 p_token1_value => p_lprv_rec.cntrct_num);
227 END IF;
228
229 END LOOP; -- new logic for reversing by tcn_id.
230
231 --Call the transaction public api to update tsu_code
232 Okl_Trx_Contracts_Pub.update_trx_contracts
233 (p_api_version => p_api_version,
234 p_init_msg_list => p_init_msg_list,
235 x_return_status => l_return_status,
236 x_msg_count => x_msg_count,
237 x_msg_data => x_msg_data,
238 p_tcnv_tbl => l_tcnv_tbl,
239 x_tcnv_tbl => x_tcnv_tbl);
240
241 -- store the highest degree of error
242 IF (l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
243 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
244 -- need to leave
245 Okl_Api.set_message(p_app_name => g_app_name,
246 p_msg_name => 'OKL_AGN_TRX_UPD_ERROR',
247 p_token1 => g_contract_number_token,
248 p_token1_value => p_lprv_rec.cntrct_num);
249 RAISE OKL_API.G_EXCEPTION_ERROR;
250 ELSE
251 -- record that there was an error
252 Okl_Api.set_message(p_app_name => g_app_name,
253 p_msg_name => 'OKL_AGN_TRX_UPD_ERROR',
254 p_token1 => g_contract_number_token,
255 p_token1_value => p_lprv_rec.cntrct_num);
256 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
257 END IF;
258 END IF;
259 END IF; -- for if l_tcnv_tbl.count > 0 condition
260
261 --SGIYER - MGAAP Bug 7263041
262 IF x_tcnv_tbl.COUNT > 0 THEN
263
264 OKL_MULTIGAAP_ENGINE_PVT.REVERSE_SEC_REP_TRX
265 (p_api_version => p_api_version,
266 p_init_msg_list => p_init_msg_list,
267 x_return_status => l_return_status,
268 x_msg_count => x_msg_count,
269 x_msg_data => x_msg_data,
270 p_tcnv_tbl => x_tcnv_tbl);
271
272 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
273 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
274 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
275 RAISE OKL_API.G_EXCEPTION_ERROR;
276 END IF;
277
278 END IF; -- for if xcnv_tbl.count > 0 condition
279
280 -- set the return status
281 x_return_status := l_return_status;
282
283 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
284
285 EXCEPTION
286 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
287 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
288 ,g_pkg_name
289 ,'OKL_API.G_RET_STS_ERROR'
290 ,x_msg_count
291 ,x_msg_data
292 ,'_PVT');
293
294 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
295 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
296 ,g_pkg_name
297 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
298 ,x_msg_count
299 ,x_msg_data
300 ,'_PVT');
301
302 WHEN OTHERS THEN
303 x_return_status := OKL_API.HANDLE_EXCEPTIONS
304 (l_api_name,
305 G_PKG_NAME,
306 'OTHERS',
307 x_msg_count,
308 x_msg_data,
309 '_PVT');
310
311 END REVERSE_LOSS_PROVISIONS;
312
313 -- this procedure reverses specific loss provision,general loss provision or both transactions
314 PROCEDURE REVERSE_LOSS_PROVISIONS (
315 p_api_version IN NUMBER
316 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
317 ,x_msg_count OUT NOCOPY NUMBER
318 ,x_msg_data OUT NOCOPY VARCHAR2
319 ,x_return_status OUT NOCOPY VARCHAR2
320 ,p_lprv_tbl IN lprv_tbl_type)
321 IS
322
323 /* variables */
324 l_api_name CONSTANT VARCHAR2(40) := 'REVERSE_LOSS_PROVISIONS';
325 l_api_version CONSTANT NUMBER := 1.0;
326 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
327
328 BEGIN
329
330 l_return_status := OKL_API.START_ACTIVITY(l_api_name
331 ,G_PKG_NAME
332 ,p_init_msg_list
333 ,l_api_version
334 ,p_api_version
335 ,'_PVT'
336 ,l_return_status);
337
338 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
339 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
340 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
341 RAISE OKL_API.G_EXCEPTION_ERROR;
342 END IF;
343
344 -- call recrod level implementation in loop
345 IF p_lprv_tbl.COUNT > 0 THEN
346 FOR i in p_lprv_tbl.FIRST..p_lprv_tbl.LAST
347 LOOP
348 REVERSE_LOSS_PROVISIONS (
349 p_api_version => p_api_version
350 ,p_init_msg_list => p_init_msg_list
351 ,x_return_status => l_return_status
352 ,x_msg_count => x_msg_count
353 ,x_msg_data => x_msg_data
354 ,p_lprv_rec => p_lprv_tbl(i));
355
356 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
357 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
358 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
359 RAISE OKL_API.G_EXCEPTION_ERROR;
360 END IF;
361 END LOOP;
362 END IF; -- IF p_lprv_tbl.COUNT > 0 THEN
363 -- set the return status
364 x_return_status := l_return_status;
365
366 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
367
368 EXCEPTION
369 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
370 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
371 ,g_pkg_name
372 ,'OKL_API.G_RET_STS_ERROR'
373 ,x_msg_count
374 ,x_msg_data
375 ,'_PVT');
376
377 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
378 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
379 ,g_pkg_name
380 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
381 ,x_msg_count
382 ,x_msg_data
383 ,'_PVT');
384
385 WHEN OTHERS THEN
386 x_return_status := OKL_API.HANDLE_EXCEPTIONS
387 (l_api_name,
388 G_PKG_NAME,
389 'OTHERS',
390 x_msg_count,
391 x_msg_data,
392 '_PVT');
393
394 END REVERSE_LOSS_PROVISIONS;
395
396
397
398 END OKL_REV_LOSS_PROV_PVT;