[Home] [Help]
PACKAGE BODY: APPS.OKL_REVERSE_CONTRACT_PVT
Source
1 PACKAGE BODY OKL_REVERSE_CONTRACT_PVT AS
2 /* $Header: OKLRRVKB.pls 120.19.12010000.3 2008/11/20 23:38:46 sgiyer ship $ */
3
4 G_TRX_RVC_TCN_TYPE CONSTANT VARCHAR2(3) := 'RVC';
5 G_TRX_TSU_CODE_SUBMITTED CONSTANT VARCHAR2(10) := 'SUBMITTED';
6 G_TRX_TSU_CODE_PROCESSED CONSTANT VARCHAR2(10) := 'PROCESSED';
7 G_TRX_TYPE_REVERSE CONSTANT VARCHAR2(10) := 'Reverse';
8 G_REVERSE CONSTANT VARCHAR2(10) := 'REVERSE';
9
10 -- Bug#4542290 - smadhava - 17-Aug-2005 - Added - Start
11 G_INT_CALC_REV_NOT_ALLWD CONSTANT VARCHAR2(30) := 'OKL_LLA_INT_CALC_REV_NOT_ALLWD';
12 -- Bug#4542290 - smadhava - 17-Aug-2005 - Added - End
13 /*
14 -- mvasudev, 08/17/2004
15 -- Added Constants to enable Business Event
16 */
17 G_WF_EVT_KHR_REVERSE_COMP CONSTANT VARCHAR2(52) := 'oracle.apps.okl.la.lease_contract.reversal_completed';
18
19 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(15) := 'CONTRACT_ID';
20 G_WF_ITM_TRX_DATE CONSTANT VARCHAR2(20) := 'TRANSACTION_DATE';
21
22 PROCEDURE Check_Contract_Status(
23 p_contract_id IN NUMBER,
24 x_return_status OUT NOCOPY VARCHAR2)
25 IS
26
27 CURSOR cntrct_status_cur IS
28 SELECT '1'
29 FROM okc_k_headers_v
30 WHERE id = p_contract_id
31 AND sts_code = G_BOOKED;
32
33 l_dummy_var VARCHAR2(1);
34 l_row_notfound BOOLEAN;
35
36 BEGIN
37
38 -- Check contract status. If not BOOKED, error out
39
40 x_return_status := G_RET_STS_SUCCESS;
41
42 OPEN cntrct_status_cur;
43 FETCH cntrct_status_cur INTO l_dummy_var;
44 l_row_notfound := cntrct_status_cur%NOTFOUND;
45 CLOSE cntrct_status_cur;
46
47 IF (l_row_notfound) THEN
48 OKC_API.SET_MESSAGE(p_app_name => g_app_name
49 ,p_msg_name => OKL_LLA_CONT_REV_BOOKED);
50 x_return_status := G_RET_STS_ERROR;
51 RAISE G_EXCEPTION_ERROR;
52 END IF;
53
54 EXCEPTION
55 WHEN G_EXCEPTION_ERROR THEN
56 x_return_status := G_RET_STS_ERROR;
57
58 END Check_Contract_Status;
59
60
61 -- Procedure to update the contract status
62
63 PROCEDURE Update_Contracts ( p_api_version IN NUMBER,
64 p_init_msg_list IN VARCHAR2,
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_msg_count OUT NOCOPY NUMBER,
67 x_msg_data OUT NOCOPY VARCHAR2,
68 p_contract_id IN NUMBER)
69 IS
70 l_return_status VARCHAR2(1);
71 l_api_version CONSTANT NUMBER := 1;
72 BEGIN
73
74 -- Update the status of the contract to REVERSED.
75
76 OKL_CONTRACT_STATUS_PUB.update_contract_status(
77 p_api_version => l_api_version,
78 p_init_msg_list => p_init_msg_list,
79 x_return_status => l_return_status,
80 x_msg_count => x_msg_count,
81 x_msg_data => x_msg_data,
82 p_khr_status => G_REVERSED,
83 p_chr_id => p_contract_id );
84
85 IF l_return_status = G_RET_STS_ERROR THEN
86 RAISE G_EXCEPTION_ERROR;
87 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
88 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
89 END IF;
90
91 EXCEPTION
92 WHEN G_EXCEPTION_ERROR THEN
93 x_return_status := G_RET_STS_ERROR;
94 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
95 x_return_status := G_RET_STS_UNEXP_ERROR;
96
97 END Update_Contracts;
98
99
100 -- Procedue to Check if the transcation type class is BOOKING, FUNDING etc
101
102 PROCEDURE Check_Trx_Type(
103 p_contract_id IN NUMBER,
104 x_return_status OUT NOCOPY VARCHAR2)
105 IS
106
107 CURSOR trx_type_cur IS
108 SELECT '1'
109 FROM OKL_TRX_CONTRACTS TC, OKL_TRX_TYPES_B TTB
110 WHERE TC.KHR_ID = p_contract_id
111 AND TC.TRY_ID = TTB.ID
112 --rkuttiya added for 12.1.1 Multi GAAP
113 AND TC.REPRESENTATION_TYPE = 'PRIMARY'
114 --
115 AND TTB.TRX_TYPE_CLASS NOT IN (G_BOOKING, G_GENERATE_YIELDS, G_REVERSE,
116 G_UPFRONT_TAX); -- G_UPFRONT_TAX introduced for R12 ebTax requirement
117
118 CURSOR asset_cur IS
119 SELECT '1'
120 FROM OKL_TRX_ASSETS A, OKL_TXL_ASSETS_B B, OKL_TRX_TYPES_B TTB
121 WHERE B.DNZ_KHR_ID = p_contract_id
122 AND A.ID = B.TAS_ID
123 AND A.TRY_ID = TTB.ID
124 AND TTB.TRX_TYPE_CLASS NOT IN (G_INTERNAL_ASSET_CREATION);
125
126 -- sjalasut, modified the cursor to refer khr_id from okl_txl_ap_inv_lns_all_b instead
127 -- of okl_trx_ap_invoices_b. changes made as part of OKLR12B disbursements project
128 CURSOR ap_inv_cur IS
129 SELECT '1'
130 FROM OKL_TRX_AP_INVOICES_B TAI, OKL_TRX_TYPES_B TTB
131 ,OKL_TXL_AP_INV_LNS_ALL_B TPL
132 WHERE TAI.ID = TPL.TAP_ID
133 AND TPL.KHR_ID = p_contract_id
134 AND TAI.TRY_ID = TTB.ID
135 AND TTB.TRX_TYPE_CLASS NOT IN (G_FUNDING)
136 AND TAI.TRX_STATUS_CODE = 'PROCESSED';
137
138 -- cklee 04/01/04
139 CURSOR ar_inv_cur IS
140 SELECT '1'
141 FROM OKL_TRX_AR_INVOICES_B TAI
142 WHERE TAI.KHR_ID = p_contract_id
143 AND TAI.TRX_STATUS_CODE = 'PROCESSED';
144
145 l_dummy_var VARCHAR2(1);
146 l_row_notfound BOOLEAN;
147 l_row_found BOOLEAN;
148 l_select_statement VARCHAR2(4000);
149
150 BEGIN
151 x_return_status := G_RET_STS_SUCCESS;
152
153 -- If there are any transactions with transaction type other than
154 -- BOOKING, FUNDING, INTERNAL_ASSET_CREATION and GENERATE_YIELDS,
155 -- error out.
156
157 OPEN trx_type_cur;
158 FETCH trx_type_cur INTO l_dummy_var;
159 l_row_notfound := trx_type_cur%NOTFOUND;
160 CLOSE trx_type_cur;
161
162 IF NOT (l_row_notfound) THEN
163 OKC_API.SET_MESSAGE(p_app_name => g_app_name
164 ,p_msg_name => okl_lla_cont_rev_trx_type
165 ,p_token1 => g_source_table
166 ,p_token1_value => g_okl_txl_cntrct_lns);
167 x_return_status := G_RET_STS_ERROR;
168 RAISE G_EXCEPTION_ERROR;
169 END IF;
170
171 OPEN asset_cur;
172 FETCH asset_cur INTO l_dummy_var;
173 l_row_notfound := asset_cur%NOTFOUND;
174 CLOSE asset_cur;
175
176 IF NOT (l_row_notfound) THEN
177 OKC_API.SET_MESSAGE(p_app_name => g_app_name
178 ,p_msg_name => okl_lla_cont_rev_trx_type
179 ,p_token1 => g_source_table
180 ,p_token1_value => g_okl_txl_assets_b);
181 x_return_status := G_RET_STS_ERROR;
182 RAISE G_EXCEPTION_ERROR;
183 END IF;
184
185 OPEN ap_inv_cur;
186 FETCH ap_inv_cur INTO l_dummy_var;
187 l_row_notfound := ap_inv_cur%NOTFOUND;
188 CLOSE ap_inv_cur;
189
190 IF NOT (l_row_notfound) THEN
191 OKC_API.SET_MESSAGE(p_app_name => g_app_name
192 ,p_msg_name => okl_lla_cont_rev_trx_type
193 ,p_token1 => g_source_table
194 ,p_token1_value => g_okl_txl_ap_inv_lns_b);
195 x_return_status := G_RET_STS_ERROR;
196 RAISE G_EXCEPTION_ERROR;
197 END IF;
198
199 -- cklee 04/01/2004
200 OPEN ar_inv_cur;
201 FETCH ar_inv_cur INTO l_dummy_var;
202 l_row_found := ar_inv_cur%FOUND;
203 CLOSE ar_inv_cur;
204
205 IF (l_row_found) THEN
206 OKC_API.SET_MESSAGE(p_app_name => g_app_name
207 ,p_msg_name => OKL_LLA_AR_INV_LINE_EXIST);
208
209 x_return_status := G_RET_STS_ERROR;
210 RAISE G_EXCEPTION_ERROR;
211 END IF;
212 -- cklee 04/01/2004
213
214 EXCEPTION
215 WHEN G_EXCEPTION_ERROR THEN
216 x_return_status := G_RET_STS_ERROR;
217
218 END Check_Trx_Type;
219
220
221 -- Procedure to reverse the transactions..
222 PROCEDURE Reverse_Trx (
223 p_contract_id IN NUMBER,
224 p_transaction_date IN DATE,
225 x_return_status OUT NOCOPY VARCHAR2,
226 -- R12B eBtax
227 p_reverse_trx_id IN NUMBER)
228 IS
229 l_tcnv_rec tcnv_rec_type;
230 l_tcnv_tbl OKL_TCN_PVT.tcnv_tbl_type;
231 x_tcnv_rec tcnv_rec_type;
232 l_tapv_rec tapv_rec_type;
233 x_tapv_rec tapv_rec_type;
234 l_thpv_rec thpv_rec_type;
235 x_thpv_rec thpv_rec_type;
236
237 l_api_version CONSTANT NUMBER := 1;
238 l_api_name CONSTANT VARCHAR2(30) := 'REVERSE_CONTRACT';
239 l_return_status VARCHAR2(1);
240 l_init_msg_list VARCHAR2(20) DEFAULT G_FALSE;
241 l_msg_count NUMBER;
242 l_msg_data VARCHAR2(2000);
243 l_trx_type_class VARCHAR2(60);
244 l_dist_not_found BOOLEAN;
245 l_found VARCHAR2(1);
246 l_count NUMBER :=0;
247
248 CURSOR ctrct_trx_cur IS
249 SELECT ID, TRY_ID, transaction_date
250 FROM OKL_TRX_CONTRACTS
251 WHERE KHR_ID = p_contract_id
252 --rkuttiya added for 12.1.1 Multi GAAP
253 AND REPRESENTATION_TYPE = 'PRIMARY';
254 --
255
256 CURSOR ctrct_trx_lines_cur (p_tcn_id NUMBER) IS
257 SELECT ID
258 FROM OKL_TXL_CNTRCT_LNS
259 WHERE TCN_ID = p_tcn_id;
260
261 CURSOR asset_trx_cur IS
262 SELECT DISTINCT TAS.ID TAS_ID,TAS.TRANSACTION_DATE
263 FROM OKL_TXL_ASSETS_B TA,OKL_TRX_ASSETS TAS
264 WHERE TA.DNZ_KHR_ID = p_contract_id
265 AND TA.TAS_ID=TAS.ID;
266
267 CURSOR asset_trx_lines_cur (p_tas_id NUMBER) IS
268 SELECT ID
269 FROM OKL_TXL_ASSETS_B TA
270 WHERE TA.TAS_ID = p_tas_id;
271
272 -- sjalasut, modified the cursor to refer khr_id from okl_txl_ap_inv_lns_all_b instead
273 -- of okl_trx_ap_invoices_b. changes made as part of OKLR12B disbursements project
274 CURSOR ap_inv_trx_cur IS
275 SELECT a.ID, a.try_id
276 FROM OKL_TRX_AP_INVOICES_B a
277 ,okl_txl_ap_inv_lns_all_b b
278 WHERE a.id = b.tap_id
279 AND b.khr_id = p_contract_id;
280
281 CURSOR ap_inv_trx_lines_cur (p_tap_id NUMBER) IS
282 SELECT ID
283 FROM OKL_TXL_AP_INV_LNS_B
284 WHERE TAP_ID = p_tap_id;
285
286 CURSOR trx_type_cur (p_try_id NUMBER) IS
287 SELECT TRX_TYPE_CLASS
288 FROM OKL_TRX_TYPES_B
289 WHERE ID = p_try_id;
290
291 CURSOR dist_cur (p_source_table VARCHAR2, p_source_id NUMBER) IS
292 SELECT '1'
293 FROM OKL_TRNS_ACC_DSTRS
294 WHERE SOURCE_ID = p_source_id
295 AND SOURCE_TABLE = p_source_table;
296
297 -- sjalasut, added local variables to support logging
298 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_REVERSE_CONTRACT_PVT.REVERSE_TRX';
299 l_debug_enabled VARCHAR2(10);
300 is_debug_statement_on BOOLEAN;
301
302 -- R12 Change - START
303
304 SUBTYPE SOURCE_ID_TBL_TYPE IS OKL_REVERSAL_PVT.SOURCE_ID_TBL_TYPE;
305 l_booking_source_id_tbl SOURCE_ID_TBL_TYPE;
306 l_upfronttax_source_id_tbl SOURCE_ID_TBL_TYPE;
307 l_upfronttax_trx_exists BOOLEAN := FALSE;
308 l_booking_trx_count NUMBER := 1;
309 l_upfronttax_trx_count NUMBER := 1;
310 l_upfronttax_trx_id OKL_TRX_CONTRACTS.ID%TYPE;
311
312 -- R12 Change - END
313
314
315 BEGIN
316
317 -- check if debug is enabled
318 l_debug_enabled := okl_debug_pub.check_log_enabled;
319 -- check for logging on STATEMENT level
320 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
321
322 -- Reverse Contract Lines Transactions
323 FOR ctrct_trx_rec IN ctrct_trx_cur
324 LOOP
325 l_tcnv_rec.id := ctrct_trx_rec.id;
326 l_tcnv_rec.tsu_code := G_CANCELED;
327 l_tcnv_rec.transaction_date := ctrct_trx_rec.transaction_date;
328 l_tcnv_rec.canceled_date := p_transaction_date;
329 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(
330 p_api_version => l_api_version
331 ,p_init_msg_list => l_init_msg_list
332 ,x_return_status => l_return_status
333 ,x_msg_count => l_msg_count
334 ,x_msg_data => l_msg_data
335 ,p_tcnv_rec => l_tcnv_rec
336 ,x_tcnv_rec => x_tcnv_rec );
337
338 IF l_return_status = G_RET_STS_ERROR THEN
339 RAISE G_EXCEPTION_ERROR;
340 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
341 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
342 END IF;
343
344 FOR trx_type_rec IN trx_type_cur (ctrct_trx_rec.try_id)
345 LOOP
346 l_trx_type_class := trx_type_rec.trx_type_class;
347 END LOOP;
348
349 -- Identify all Booking accounting entries
350 IF l_trx_type_class = G_BOOKING THEN
351
352 FOR ctrct_trx_lines_rec IN ctrct_trx_lines_cur (ctrct_trx_rec.id)
353 LOOP
354 OPEN dist_cur (G_OKL_TXL_CNTRCT_LNS, ctrct_trx_lines_rec.id);
355 FETCH dist_cur INTO l_found;
356 l_dist_not_found := dist_cur%NOTFOUND;
357 CLOSE dist_cur;
358 IF NOT l_dist_not_found THEN
359 l_booking_source_id_tbl(l_booking_trx_count) := ctrct_trx_lines_rec.id;
360 END IF;
361 l_booking_trx_count := l_booking_trx_count + 1;
362 END LOOP;
363
364 l_count := l_count+1;
365 l_tcnv_tbl(l_count).id := ctrct_trx_rec.id;
366 l_tcnv_tbl(l_count).canceled_date := p_transaction_date;
367
368 END IF;
369
370 -- Identify all upfront tax accounting entries
371 IF l_trx_type_class = G_UPFRONT_TAX THEN -- G_UPFRONT_TAX introduced for R12 ebTax requirement
372 l_upfronttax_trx_id := ctrct_trx_rec.id;
373 l_upfronttax_trx_exists := TRUE;
374
375 FOR ctrct_trx_lines_rec IN ctrct_trx_lines_cur (ctrct_trx_rec.id)
376 LOOP
377 l_upfronttax_source_id_tbl(l_upfronttax_trx_count) := ctrct_trx_lines_rec.id;
378 l_upfronttax_trx_count := l_upfronttax_trx_count + 1;
379 END LOOP;
380
381 END IF;
382
383 END LOOP;
384
385 -- R12 Changes - START
386 -- In R12 accounting reversal API will be called only once for
387 -- each transaction event, BOOKING and 'Upfront Tax'
388
389 -- 1. Reverse Booking accounting entries
390 OKL_REVERSAL_PUB.REVERSE_ENTRIES (
391 p_api_version => l_api_version,
392 p_init_msg_list => l_init_msg_list,
393 x_return_status => l_return_status,
394 x_msg_count => l_msg_count,
395 x_msg_data => l_msg_data,
396 p_source_table => G_OKL_TXL_CNTRCT_LNS,
397 p_acct_date => p_transaction_date,
398 p_source_id_tbl => l_booking_source_id_tbl);
399 IF l_return_status = G_RET_STS_ERROR THEN
400 RAISE G_EXCEPTION_ERROR;
401 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
402 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
403 END IF;
404
405 --Bug 7555210. Multi-GAAP Engine Call for reversing secondary representation accounting
406 OKL_MULTIGAAP_ENGINE_PVT.REVERSE_SEC_REP_TRX (
407 P_API_VERSION => l_api_version,
408 P_INIT_MSG_LIST => l_init_msg_list,
409 X_RETURN_STATUS => l_return_status,
410 X_MSG_COUNT => l_msg_count,
411 X_MSG_DATA => l_msg_data,
412 P_TCNV_TBL => l_tcnv_tbl);
413
414 IF l_return_status = G_RET_STS_ERROR THEN
415 RAISE G_EXCEPTION_ERROR;
416 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
417 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
418 END IF;
419
420
421 -- 2. Reverse Upfront Tax accounting entries
422 IF l_upfronttax_trx_exists = TRUE THEN
423
424 OKL_REVERSAL_PUB.REVERSE_ENTRIES (
425 p_api_version => l_api_version,
426 p_init_msg_list => l_init_msg_list,
427 x_return_status => l_return_status,
428 x_msg_count => l_msg_count,
429 x_msg_data => l_msg_data,
430 p_source_table => G_OKL_TXL_CNTRCT_LNS,
431 p_acct_date => p_transaction_date,
432 p_source_id_tbl => l_upfronttax_source_id_tbl);
433
434 IF l_return_status = G_RET_STS_ERROR THEN
435 RAISE G_EXCEPTION_ERROR;
436 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
437 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
438 END IF;
439
440 END IF;
441
442 -- R12B eBtax: Upfront Tax Lines should be reversed regardless of whether
443 -- or not Upfront Tax accounting entries exist
444 okl_process_sales_tax_pvt.calculate_sales_tax(
445 p_api_version => l_api_version,
446 p_init_msg_list => l_init_msg_list,
447 x_return_status => l_return_status,
448 x_msg_count => l_msg_count,
449 x_msg_data => l_msg_data,
450 p_source_trx_id => p_reverse_trx_id,
451 p_source_trx_name => 'Reverse',
452 p_source_table => 'OKL_TRX_CONTRACTS');
453
454 IF l_return_status = G_RET_STS_ERROR THEN
455 RAISE G_EXCEPTION_ERROR;
456 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
457 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
458 END IF;
459
460 -- R12 Changes - END
461
462
463 -- Reverse Asset Transactions
464 FOR asset_trx_rec IN asset_trx_cur
465 LOOP
466 l_thpv_rec.id := asset_trx_rec.tas_id;
467 l_thpv_rec.TSU_CODE := G_CANCELED;
468 l_thpv_rec.transaction_date := asset_trx_rec.transaction_date;
469 OKL_TRX_ASSETS_PUB.update_trx_ass_h_Def(
470 p_api_version => l_api_version
471 ,p_init_msg_list => l_init_msg_list
472 ,x_return_status => l_return_status
473 ,x_msg_count => l_msg_count
474 ,x_msg_data => l_msg_data
475 ,p_thpv_rec => l_thpv_rec
476 ,x_thpv_rec => x_thpv_rec );
477 IF l_return_status = G_RET_STS_ERROR THEN
478 RAISE G_EXCEPTION_ERROR;
479 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
480 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
481 END IF;
482 /*
483 -- Call the Accounting Engine to reverse the transactions, by passing source id
484 -- and source tables
485
486 FOR asset_trx_lines_rec IN asset_trx_lines_cur (asset_trx_rec.tas_id)
487 LOOP
488
489 OKL_ACCOUNT_DIST_PUB.REVERSE_ENTRIES
490 (p_api_version => l_api_version,
491 p_init_msg_list => l_init_msg_list,
492 x_return_status => l_return_status,
493 x_msg_count => l_msg_count,
494 x_msg_data => l_msg_data,
495 p_source_id => asset_trx_lines_rec.id,
496 p_source_table => G_OKL_TXL_ASSETS_B,
497 p_acct_date => p_transaction_date);
498
499
500 IF l_return_status = G_RET_STS_ERROR THEN
501 RAISE G_EXCEPTION_ERROR;
502 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
503 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
504 END IF;
505 END LOOP;
506 */
507
508 END LOOP;
509
510 /*
511 * sjalasut: Feb 25, 05 added logic to reverse subsidy pool transactions
512 * for assets on the contract
513 * Logic added as part of subsidy pools enhancement. START
514 */
515 -- write to log
516 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
517 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
518 l_module,
519 'calling okl_subsidy_pool_auth_trx_pvt.create_pool_trx_khr_reverse with p_chr_id '|| p_contract_id ||
520 ' p_transaction_date '||p_transaction_date
521 );
522 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
523
524 okl_subsidy_pool_auth_trx_pvt.create_pool_trx_khr_reverse(p_api_version => l_api_version
525 ,p_init_msg_list => l_init_msg_list
526 ,x_return_status => l_return_status
527 ,x_msg_count => l_msg_count
528 ,x_msg_data => l_msg_data
529 ,p_chr_id => p_contract_id
530 ,p_reversal_date => p_transaction_date
531 ,p_override_trx_reason => NULL -- pass this as null from there as this holds override value
532 );
533 -- write to log
534 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
535 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
536 l_module,
537 'okl_subsidy_pool_auth_trx_pvt.create_pool_trx_khr_reverse returned with '||l_return_status||
538 ' l_msg_data '||l_msg_data
539 );
540 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
541
542 IF l_return_status = G_RET_STS_ERROR THEN
543 RAISE G_EXCEPTION_ERROR;
544 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
545 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
546 END IF;
547
548 /*
549 * sjalasut: Feb 25, 05 added logic to reverse subsidy pool transactions
550 * for assets on the contract
551 * Logic added as part of subsidy pools enhancement. END
552 */
553
554
555
556 --
557 -- Commented out as the logic for processing funding request changes.
558 --
559
560 /*
561 -- Reverse AP Invoice Lines Transactions
562
563 FOR ap_inv_trx_rec IN ap_inv_trx_cur
564 LOOP
565
566 l_tapv_rec.id := ap_inv_trx_rec.id;
567 l_tapv_rec.TRX_STATUS_CODE := G_CANCELED;
568
569 OKL_TRX_AP_INVOICES_PUB.update_trx_ap_invoices(
570 p_api_version => l_api_version
571 ,p_init_msg_list => l_init_msg_list
572 ,x_return_status => l_return_status
573 ,x_msg_count => l_msg_count
574 ,x_msg_data => l_msg_data
575 ,p_tapv_rec => l_tapv_rec
576 ,x_tapv_rec => x_tapv_rec );
577
578 IF l_return_status = G_RET_STS_ERROR THEN
579 RAISE G_EXCEPTION_ERROR;
580 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
581 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
582 END IF;
583
584 FOR trx_type_rec IN trx_type_cur (ap_inv_trx_rec.try_id) LOOP
585 l_trx_type_class := trx_type_rec.trx_type_class;
586 END LOOP;
587
588
589 -- Call the Accounting Engine to reverse the transactions, by passing source id
590 -- and source tables
591
592 IF l_trx_type_class = G_FUNDING THEN
593 FOR ap_inv_trx_lines_rec IN ap_inv_trx_lines_cur (ap_inv_trx_rec.id)
594 LOOP
595
596 OPEN dist_cur (G_OKL_TXL_AP_INV_LNS_B, ap_inv_trx_lines_rec.id);
597 FETCH dist_cur INTO l_found;
598 l_dist_not_found := dist_cur%NOTFOUND;
599 CLOSE dist_cur;
600
601 IF NOT l_dist_not_found THEN
602
603 OKL_ACCOUNT_DIST_PUB.REVERSE_ENTRIES
604 (p_api_version => l_api_version,
605 p_init_msg_list => l_init_msg_list,
606 x_return_status => l_return_status,
607 x_msg_count => l_msg_count,
608 x_msg_data => l_msg_data,
609 p_source_id => ap_inv_trx_lines_rec.id,
610 p_source_table => G_OKL_TXL_AP_INV_LNS_B,
611 p_acct_date => p_transaction_date);
612
613 IF l_return_status = G_RET_STS_ERROR THEN
614 RAISE G_EXCEPTION_ERROR;
615 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
616 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
617 END IF;
618 END IF;
619 END LOOP;
620 END IF;
621
622 END LOOP;
623
624 */
625
626
627 --
628 -- Reverse Funding Requests once contract has been reversed : cklee 06/27/02
629 --
630
631 OKL_FUNDING_PVT.reverse_funding_requests
632 (p_api_version => l_api_version
633 ,p_init_msg_list => l_init_msg_list
634 ,x_return_status => l_return_status
635 ,x_msg_count => l_msg_count
636 ,x_msg_data => l_msg_data
637 ,p_contract_id => p_contract_id);
638
639 IF l_return_status = G_RET_STS_ERROR THEN
640 RAISE G_EXCEPTION_ERROR;
641 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
642 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
643 END IF;
644 --
645 -- End of Reverse Funding Requests once contract has been reversed : cklee 06/27/02
646 --
647
648 END Reverse_Trx ;
649
650
651
652 -- Procedure which reverses a contract
653
654 PROCEDURE Reverse_Contract (p_api_version IN NUMBER,
655 p_init_msg_list IN VARCHAR2,
656 x_return_status OUT NOCOPY VARCHAR2,
657 x_msg_count OUT NOCOPY NUMBER,
658 x_msg_data OUT NOCOPY VARCHAR2,
659 p_contract_id IN NUMBER,
660 p_transaction_date IN DATE )
661 IS
662 -- cklee
663 l_tcnv_rec tcnv_rec_type;
664 x_tcnv_rec tcnv_rec_type;
665 -- cklee
666 l_allow_reversal VARCHAR2(1);
667 l_dummy_var VARCHAR2(1);
668 l_trx_id NUMBER;
669 l_row_notfound BOOLEAN;
670 -- Manu 11-Aug-2004
671 l_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
672 l_row_found BOOLEAN;
673 --Added by dpsingh for LE uptake
674 l_legal_entity_id NUMBER;
675 l_api_version CONSTANT NUMBER := 1;
676 l_api_name CONSTANT VARCHAR2(30) := 'REVERSE_CONTRACT';
677 l_return_status VARCHAR2(1);
678 l_init_msg_list VARCHAR2(20) DEFAULT G_FALSE;
679 l_msg_count NUMBER;
680 l_msg_data VARCHAR2(2000);
681
682 -- cklee
683 l_try_id NUMBER;
684
685 -- Bug 5202448 : kbbhavsa : 12-May-2006 : Start
686 -- Changed the cursor definition for language support
687 CURSOR l_tryv_csr IS
688 SELECT TRYB.id
689 FROM OKL_TRX_TYPES_B TRYB,
690 OKL_TRX_TYPES_TL TRYT
691 WHERE
692 TRYB.ID = TRYT.ID and
693 TRYT.LANGUAGE = 'US' and
694 TRYT.NAME = G_TRX_TYPE_REVERSE;
695 -- Bug 5202448 : kbbhavsa : 12-May-2006 : End
696 -- cklee
697
698 CURSOR cntrct_cur IS
699 SELECT '1'
700 FROM okc_k_headers_v
701 WHERE id = p_contract_id;
702
703 CURSOR ctrct_trx_cur IS
704 SELECT '1'
705 FROM OKL_TRX_CONTRACTS
706 WHERE KHR_ID = p_contract_id
707 --rkuttiya added for 12.1.1 Multi GAAP
708 AND REPRESENTATION_TYPE = 'PRIMARY';
709
710 -- sjalasut, modified the cursor to refer khr_id from okl_txl_ap_inv_lns_all_b instead
711 -- of okl_trx_ap_invoices_b. changes made as part of OKLR12B disbursements project
712 CURSOR ap_inv_trx_cur IS
713 SELECT '1'
714 FROM OKL_TRX_AP_INVOICES_B a
715 , okl_txl_ap_inv_lns_all_b b
716 WHERE a.id = b.tap_id
717 AND b.KHR_ID = p_contract_id;
718
719 CURSOR asset_trx_cur IS
720 SELECT '1'
721 FROM OKL_TXL_ASSETS_B
722 WHERE DNZ_KHR_ID = p_contract_id;
723
724 -- Start Manu 11-Aug-2004.
725 -- Cursor to find if there are any Rollover Fee lines on a contract
726
727 CURSOR rollover_fee_line_csr(p_k_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
728 SELECT 1 FROM okc_k_lines_b CLEB, okl_k_lines KLE
729 WHERE CLEB.dnz_chr_id = p_k_id
730 AND KLE.FEE_TYPE = 'ROLLOVER'
731 AND KLE.ID = CLEB.ID
732 AND NOT EXISTS (
733 SELECT 'Y'
734 FROM okc_statuses_v okcsts
735 WHERE okcsts.code = cleb.sts_code
736 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
737
738 -- Cursor to select contract number
739
740 CURSOR contract_number_csr(p_k_id OKC_K_HEADERS_B.ID%TYPE) IS
741 SELECT contract_number FROM okc_k_headers_v
742 WHERE id = p_k_id;
743
744 -- End Manu 11-Aug-2004
745
746 -- Bug#4542290 - smadhava - 17-Aug-2005 - Added - Start
747 CURSOR chk_interest_processing(p_chr_id OKL_K_HEADERS.id%TYPE) IS
748 SELECT
749 'X'
750 FROM
751 OKL_K_HEADERS
752 WHERE
753 id = p_chr_id
754 AND DATE_LAST_INTERIM_INTEREST_CAL IS NOT NULL;
755
756 l_interest_processed_flag VARCHAR2(1);
757 -- Bug#4542290 - smadhava - 17-Aug-2005 - Added - End
758
759 /*
760 -- mvasudev, 08/30/2004
761 -- Added PROCEDURE to enable Business Event
762 */
763 PROCEDURE raise_business_event(x_return_status OUT NOCOPY VARCHAR2
764 )
765 IS
766 l_parameter_list wf_parameter_list_t;
767 BEGIN
768
769 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_contract_id,l_parameter_list);
770 wf_event.AddParameterToList(G_WF_ITM_TRX_DATE,fnd_date.date_to_canonical(p_transaction_date),l_parameter_list);
771
772 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
773 p_init_msg_list => p_init_msg_list,
774 x_return_status => x_return_status,
775 x_msg_count => x_msg_count,
776 x_msg_data => x_msg_data,
777 p_event_name => G_WF_EVT_KHR_REVERSE_COMP,
778 p_parameters => l_parameter_list);
779
780
781
782 EXCEPTION
783 WHEN OTHERS THEN
784 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
785 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
786 END raise_business_event;
787
788
789 /*
790 -- mvasudev, 08/30/2004
791 -- END, PROCEDURE to enable Business Event
792 */
793
794
795 BEGIN
796
797 -- Start Activity
798
799 x_return_status := Okl_Api.START_ACTIVITY(l_api_name
800 ,G_PKG_NAME
801 ,p_init_msg_list
802 ,l_api_version
803 ,p_api_version
804 ,'_PVT'
805 ,x_return_status);
806
807
808 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
809 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
810 ELSIF (x_return_Status = G_RET_STS_ERROR) THEN
811 RAISE G_EXCEPTION_ERROR;
812 END IF;
813
814
815
816 -- Check for the required attributes
817 IF (p_contract_id IS NULL OR p_contract_id = G_MISS_NUM) THEN
818 OKC_API.SET_MESSAGE(p_app_name => g_app_name
819 ,p_msg_name => g_required_value
820 ,p_token1 => g_col_name_token
821 ,p_token1_value => 'Contract ID');
822 x_return_status := G_RET_STS_ERROR;
823 RAISE G_EXCEPTION_ERROR;
824 END IF;
825
826 IF (p_transaction_date IS NULL OR p_transaction_date = G_MISS_DATE) THEN
827 OKC_API.SET_MESSAGE(p_app_name => g_app_name
828 ,p_msg_name => g_required_value
829 ,p_token1 => g_col_name_token
830 ,p_token1_value => 'Revision Date');
831 x_return_status := G_RET_STS_ERROR;
832 RAISE G_EXCEPTION_ERROR;
833 END IF;
834
835 -- Check if the contract is a valid one
836
837 OPEN cntrct_cur;
838 FETCH cntrct_cur INTO l_dummy_var;
839 l_row_notfound := cntrct_cur%NOTFOUND;
840 CLOSE cntrct_cur;
841
842 IF (l_row_notfound) THEN
843 OKC_API.SET_MESSAGE(p_app_name => g_app_name
844 ,p_msg_name => g_invalid_value
845 ,p_token1 => g_col_name_token
846 ,p_token1_value => 'Contract ID');
847 x_return_status := G_RET_STS_ERROR;
848 RAISE G_EXCEPTION_ERROR;
849 END IF;
850
851 -- Check if the status of the contract is BOOKED. If not , Error out
852 Check_Contract_Status(
853 p_contract_id => p_contract_id,
854 x_return_status => l_return_status) ;
855
856
857 IF l_return_status = G_RET_STS_ERROR THEN
858 RAISE G_EXCEPTION_ERROR;
859 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
860 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
861 END IF;
862
863 -- Bug#4542290 - smadhava - 17-Aug-2005 - Modified - Start
864 -- Moved code to facilitate usage of contract number in error messages
865 -- Get Contract Number.
866 OPEN contract_number_csr(p_k_id => p_contract_id);
867 FETCH contract_number_csr INTO l_contract_number;
868 CLOSE contract_number_csr;
869 -- Bug#4542290 - smadhava - 17-Aug-2005 - Modified - End
870
871 -- Bug#4542290 - smadhava - 17-Aug-2005 - Added - Start
872 OPEN chk_interest_processing(p_contract_id);
873 FETCH chk_interest_processing INTO l_interest_processed_flag;
874 CLOSE chk_interest_processing;
875
876 -- Contract Reversal not allowed if interest for this contract has already been processed.
877 IF ( l_interest_processed_flag = 'X') THEN
878 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME
879 ,p_msg_name => G_INT_CALC_REV_NOT_ALLWD
880 ,p_token1 => g_col_name_token
881 ,p_token1_value => l_contract_number);
882 RAISE G_EXCEPTION_ERROR;
883 END IF; -- end of check for interest processing
884 -- Bug#4542290 - smadhava - 17-Aug-2005 - Added - End
885
886 -- Start Manu 11-Aug-2004.
887 -- Check if there are any Rollover Fee lines on a contract.
888
889 OPEN rollover_fee_line_csr(p_k_id => p_contract_id);
890 FETCH rollover_fee_line_csr INTO l_dummy_var;
891 l_row_found := rollover_fee_line_csr%FOUND;
892 CLOSE rollover_fee_line_csr;
893
894 -- Error out if there are any Rollover Fee lines on a contract.
895
896 IF (l_row_found) THEN
897 OKC_API.SET_MESSAGE(p_app_name => g_app_name
898 ,p_msg_name => OKL_LA_NO_REV_CONTRACT
899 ,p_token1 => g_col_name_token
900 ,p_token1_value => l_contract_number);
901 x_return_status := G_RET_STS_ERROR;
902 RAISE G_EXCEPTION_ERROR;
903 END IF;
904
905 -- End Manu 11-Aug-2004.
906
907 -- start cklee
908 OPEN l_tryv_csr;
909 FETCH l_tryv_csr INTO l_try_id;
910 CLOSE l_tryv_csr;
911
912 l_tcnv_rec.tcn_type := G_TRX_RVC_TCN_TYPE;
913 l_tcnv_rec.tsu_code := G_TRX_TSU_CODE_SUBMITTED;
914 l_tcnv_rec.khr_id := p_contract_id;
915 l_tcnv_rec.try_id := l_try_id;
916 l_tcnv_rec.date_transaction_occurred := p_transaction_date;
917 --Added by dpsingh for LE Uptake
918 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_contract_id) ;
919 IF l_legal_entity_id IS NOT NULL THEN
920 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
921 ELSE
922 Okl_Api.set_message(p_app_name => g_app_name,
923 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
924 p_token1 => 'CONTRACT_NUMBER',
925 p_token1_value => l_contract_number);
926 RAISE OKL_API.G_EXCEPTION_ERROR;
927 END IF;
928
929 -- Create Transaction Header
930 Okl_Trx_Contracts_Pub.create_trx_contracts(
931 p_api_version => l_api_version
932 ,p_init_msg_list => l_init_msg_list
933 ,x_return_status => l_return_status
934 ,x_msg_count => l_msg_count
935 ,x_msg_data => l_msg_data
936 ,p_tcnv_rec => l_tcnv_rec
937 ,x_tcnv_rec => x_tcnv_rec);
938
939 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
940 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
941 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
942 RAISE Okl_Api.G_EXCEPTION_ERROR;
943 END IF;
944 -- end cklee
945
946 -- If the contract does not have any transactions, then update the status
947 -- of the contract to REVERSED
948
949 OPEN ctrct_trx_cur;
950 FETCH ctrct_trx_cur INTO l_dummy_var;
951 l_row_notfound := ctrct_trx_cur%NOTFOUND;
952 CLOSE ctrct_trx_cur;
953
954 IF l_row_notfound THEN
955 OPEN ap_inv_trx_cur;
956 FETCH ap_inv_trx_cur INTO l_dummy_var;
957 l_row_notfound := ap_inv_trx_cur%NOTFOUND;
958 CLOSE ap_inv_trx_cur;
959 END IF;
960
961 IF l_row_notfound THEN
962 OPEN asset_trx_cur;
963 FETCH asset_trx_cur INTO l_dummy_var;
964 l_row_notfound := asset_trx_cur%NOTFOUND;
965 CLOSE asset_trx_cur;
966 END IF;
967
968 IF (l_row_notfound) THEN -- If the contract does not have transactions
969
970 -- Update the status of the contract to REVERSED.
971
972
973 Update_Contracts ( p_api_version => l_api_version,
974 p_init_msg_list => l_init_msg_list,
975 x_return_status => l_return_status,
976 x_msg_count => l_msg_count,
977 x_msg_data => l_msg_data,
978 p_contract_id => p_contract_id );
979
980
981 IF l_return_status = G_RET_STS_ERROR THEN
982 RAISE G_EXCEPTION_ERROR;
983 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
984 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
985 END IF;
986
987 ELSE -- If the contract does have any transactions
988
989 -- If the contract has any transactions, then check to see if the transaction type
990 -- class is not in FUNDING and BOOKING, INTERNAL ASSET CREATION and GENEATE YIELS.
991 -- If there are transactions with any other trx type, then error out.
992
993 Check_Trx_Type(p_contract_id => p_contract_id,
994 x_return_status => l_return_status) ;
995
996 IF l_return_status = G_RET_STS_ERROR THEN
997 RAISE G_EXCEPTION_ERROR;
998 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
999 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1000 END IF;
1001
1002
1003 Reverse_Trx (
1004 p_contract_id => p_contract_id,
1005 p_transaction_date => p_transaction_date,
1006 x_return_status => x_return_status,
1007 -- R12B eBTax
1008 p_reverse_trx_id => x_tcnv_rec.id);
1009
1010 IF l_return_status = G_RET_STS_ERROR THEN
1011 RAISE G_EXCEPTION_ERROR;
1012 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1013 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1014 END IF;
1015
1016
1017 -- Update the status of the contract to REVERSED once all the transactions are reversed
1018 Update_Contracts ( p_api_version => l_api_version,
1019 p_init_msg_list => l_init_msg_list,
1020 x_return_status => l_return_status,
1021 x_msg_count => l_msg_count,
1022 x_msg_data => l_msg_data,
1023 p_contract_id => p_contract_id );
1024
1025 IF l_return_status = G_RET_STS_ERROR THEN
1026 RAISE G_EXCEPTION_ERROR;
1027 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1028 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1029 END IF;
1030
1031 END IF; -- If the contract has any transactions
1032
1033 -- start cklee
1034 l_tcnv_rec.id := x_tcnv_rec.id;
1035 l_tcnv_rec.tsu_code := G_TRX_TSU_CODE_PROCESSED;
1036 l_tcnv_rec.transaction_date := x_tcnv_rec.transaction_date;
1037 -- Update Transaction Header
1038 Okl_Trx_Contracts_Pub.update_trx_contracts(
1039 p_api_version => l_api_version
1040 ,p_init_msg_list => l_init_msg_list
1041 ,x_return_status => l_return_status
1042 ,x_msg_count => l_msg_count
1043 ,x_msg_data => l_msg_data
1044 ,p_tcnv_rec => l_tcnv_rec
1045 ,x_tcnv_rec => x_tcnv_rec);
1046
1047 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1048 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1049 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1050 RAISE Okl_Api.G_EXCEPTION_ERROR;
1051 END IF;
1052 -- end cklee
1053
1054 /*
1055 -- mvasudev, 08/23/2004
1056 -- Code change to enable Business Event
1057 */
1058 raise_business_event(x_return_status => x_return_status);
1059 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1060 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1061 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1062 RAISE OKL_API.G_EXCEPTION_ERROR;
1063 END IF;
1064
1065 /*
1066 -- mvasudev, 08/23/2004
1067 -- END, Code change to enable Business Event
1068 */
1069
1070 Okl_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1071 x_return_status := G_RET_STS_SUCCESS;
1072
1073
1074 EXCEPTION
1075 WHEN G_EXCEPTION_ERROR THEN
1076 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1077 ,g_pkg_name
1078 ,'OKL_API.G_RET_STS_ERROR'
1079 ,x_msg_count
1080 ,x_msg_data
1081 ,'_PVT');
1082
1083 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1084 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1085 ,g_pkg_name
1086 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1087 ,x_msg_count
1088 ,x_msg_data
1089 ,'_PVT');
1090 WHEN OTHERS THEN
1091 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1092 (l_api_name,
1093 G_PKG_NAME,
1094 'OTHERS',
1095 x_msg_count,
1096 x_msg_data,
1097 '_PVT');
1098
1099 END Reverse_Contract;
1100
1101
1102
1103
1104 END OKL_REVERSE_CONTRACT_PVT;