[Home] [Help]
PACKAGE BODY: APPS.FUN_NET_CANCEL_PKG
Source
1 PACKAGE BODY FUN_NET_CANCEL_PKG AS
2 /* $Header: funntcrb.pls 120.7 2007/11/20 18:02:03 shnaraya ship $ */
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER;
7 g_proc_level NUMBER;
8 g_event_level NUMBER;
9 g_excep_level NUMBER;
10 g_error_level NUMBER;
11 g_unexp_level NUMBER;
12 g_path VARCHAR2(100);
13
14 --===========================FND_LOG.END=======================================
15
16 --Declare all required global variables
17 g_user_id NUMBER;
18 g_login_id NUMBER;
19 g_today DATE;
20 g_batch_status fun_net_batches.batch_status_code%TYPE;
21
22 /* Selects the Batch Status of the given Batch ids */
23
24 PROCEDURE get_batch_status(
25 p_batch_id IN fun_net_batches_all.batch_id%TYPE,
26 x_return_status OUT NOCOPY VARCHAR2)
27 IS
28 BEGIN
29 x_return_status := FND_API.G_TRUE;
30
31 SELECT batch_status_code
32 INTO g_batch_status
33 FROM
34 fun_net_batches
35 WHERE
36 batch_id = p_batch_id;
37 EXCEPTION
38
39 WHEN OTHERS THEN
40 x_return_status := FND_API.G_FALSE;
41 END get_batch_status;
42
43 /* Validates Batch Status :
44 If mode = Cancel , then the batch should be in the following statuses
45 'SELECTED','SUSPENDED','CANCELLED','REJECTED','ERROR'
46 If mode = Reverse , then the batch should be in status 'COMPLETE' */
47
48 PROCEDURE Validate_Batch_Status
49 (p_mode IN VARCHAR2,
50 p_batch_id IN fun_net_batches.batch_id%TYPE,
51 x_return_status OUT NOCOPY VARCHAR2)
52 IS
53 l_return_status VARCHAR2(1);
54 BEGIN
55 x_return_status := FND_API.G_TRUE;
56 IF p_mode IS NULL or p_batch_id IS NULL THEN
57 x_return_status := FND_API.G_FALSE;
58 RETURN;
59 END IF;
60
61 get_batch_status(
62 p_batch_id => p_batch_id,
63 x_return_status => l_return_status);
64
65 IF l_return_status = FND_API.G_TRUE THEN
66 IF p_mode = 'CANCEL' THEN
67 IF g_batch_status NOT IN ('SELECTED','SUSPENDED',
68 'CANCELLED','REJECTED','ERROR') THEN
69 x_return_status := FND_API.G_FALSE;
70 RETURN;
71 END IF;
72 ELSIF p_mode = 'REVERSE' THEN
73 IF g_batch_status <> 'COMPLETE' THEN
74 x_return_status := FND_API.G_FALSE;
75 RETURN;
76 END IF;
77 END IF;
78
79 ELSE
80 x_return_status := FND_API.G_FALSE;
81 END IF;
82 EXCEPTION
83 WHEN OTHERS THEN
84 x_return_status := FND_API.G_FALSE;
85 END Validate_Batch_Status;
86
87 /* Unlocks AP Payment Schedule lines for the given batch if the batch status is
88 'SELECTED' and deletes Netting AP Invoices in FUN_NET_AP_INVS given a Batch Id */
89
90 PROCEDURE delete_ap_invs (
91 p_batch_id IN fun_net_batches.batch_id%TYPE,
92 x_return_status OUT NOCOPY VARCHAR2)
93 IS
94 TYPE l_inv_tab_type IS TABLE OF fun_net_ap_invs.invoice_id%TYPE;
95 l_inv_tab l_inv_tab_type;
96
97 BEGIN
98 x_return_status := FND_API.G_TRUE;
99
100 /* Unlock AP Transactions if the Batch Status = SELECTED
101 AR Transactions are not locked at this point */
102
103 IF (g_batch_status = 'SELECTED') OR (g_batch_status='ERROR') THEN
104 FUN_NET_ARAP_PKG.unlock_ap_pymt_schedules(
105 p_batch_id => p_batch_id,
106 x_return_status => x_return_status);
107 END IF;
108
109 IF x_return_status = FND_API.G_FALSE THEN
110 RETURN;
111 END IF;
112
113 SELECT invoice_id
114 BULK COLLECT INTO l_inv_tab
115 FROM fun_net_ap_invs
116 WHERE batch_id = p_batch_id;
117
118 IF l_inv_tab.EXISTS(1) THEN
119
120 FOR i IN l_inv_tab.FIRST..l_inv_tab.LAST
121 LOOP
122 FUN_NET_AP_INVS_PKG.Delete_Row(
123 x_batch_id => p_batch_id,
124 x_invoice_id => l_inv_tab(i));
125 END LOOP;
126
127 END IF;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 x_return_status := FND_API.G_FALSE;
132 END delete_ap_invs;
133
134 /* Deletes Netting AR transactions for the given batch */
135
136 PROCEDURE delete_ar_txns (
137 x_batch_id IN fun_net_batches.batch_id%TYPE,
138 x_return_status OUT NOCOPY VARCHAR2)
139 IS
140 TYPE l_txn_tab_type IS TABLE OF fun_net_ar_txns.customer_trx_id%TYPE;
141 l_txn_tab l_txn_tab_type;
142
143 BEGIN
144 x_return_status := FND_API.G_TRUE;
145 SELECT customer_trx_id
146 BULK COLLECT INTO l_txn_tab
147 FROM fun_net_ar_txns
148 WHERE batch_id = x_batch_id;
149
150 IF l_txn_tab.EXISTS(1) THEN
151
152 FOR i IN l_txn_tab.FIRST..l_txn_tab.LAST
153 LOOP
154 FUN_NET_AR_TXNS_PKG.Delete_Row(
155 x_batch_id => x_batch_id,
156 x_customer_trx_id => l_txn_tab(i));
157 END LOOP;
158 END IF;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 x_return_status := FND_API.G_FALSE;
163 END delete_ar_txns;
164
165 /* Gets the Agreement id for a given batch */
166
167 PROCEDURE get_agreement(
168 p_batch_id IN fun_net_batches.batch_id%TYPE,
169 x_agreement_id OUT NOCOPY fun_net_agreements.agreement_id%TYPE,
170 x_return_status OUT NOCOPY VARCHAR2)
171 IS
172 BEGIN
173 x_return_status := FND_API.G_TRUE;
174 SELECT agreement_id
175 INTO x_agreement_id
176 FROM fun_net_batches
177 WHERE batch_id = p_batch_id;
178 EXCEPTION
179 WHEN OTHERS THEN
180 x_return_status := FND_API.G_FALSE;
181 END get_agreement;
182
183
184 /* Deletes a Batch and all the transactions in a batch
185 that is not in COMPLETE Status. Unlocks AP Transaction
186 if the Batch Status is 'SELECTED' */
187
188 PROCEDURE cancel_net_batch(
189 -- ***** Standard API Parameters *****
190 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
191 p_commit IN VARCHAR2 := FND_API.G_FALSE,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 -- ***** Netting batch input parameters *****
196 p_batch_id IN NUMBER) IS
197
198 -- ***** local variables *****
199 l_return_status VARCHAR2(1);
200 l_msg_count NUMBER;
201 l_msg_data VARCHAR2(2000);
202 l_agreement_id fun_net_agreements.agreement_id%TYPE;
203
204
205
206 BEGIN
207 x_msg_count := NULL;
208 x_msg_data := NULL;
209 g_user_id := fnd_global.user_id;
210 g_login_id := fnd_global.login_id;
211
212 -- **** Standard start of API savepoint ****
213 SAVEPOINT cancel_net_batch_SP;
214
215 -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
216 IF FND_API.to_Boolean( p_init_msg_list ) THEN
217 FND_MSG_PUB.initialize;
218 END IF;
219
220 -- **** Initialize return status to SUCCESS *****
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222
223 /*-----------------------------------------------+
224 | ======== START OF API BODY ============ |
225 +-----------------------------------------------*/
226
227 /* Check for mandatory parameters */
228
229 IF p_batch_id IS NULL THEN
230 RAISE FND_API.G_EXC_ERROR;
231 END IF;
232
233 IF l_return_status = FND_API.G_FALSE THEN
234 RAISE FND_API.G_EXC_ERROR;
235 END IF;
236
237 /* Check the batch status before deleting */
238
239 Validate_Batch_Status(
240 p_mode => 'CANCEL',
241 p_batch_id => p_batch_id,
242 x_return_status => l_return_status);
243
244 IF l_return_status = FND_API.G_FALSE THEN
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247 /* Get Agreement Id of the Batch */
248
249 get_agreement(
250 p_batch_id => p_batch_id,
251 x_agreement_id => l_agreement_id,
252 x_return_status => x_return_status);
253
254 IF l_return_status = FND_API.G_FALSE THEN
255 RAISE FND_API.G_EXC_ERROR;
256 END IF;
257
258 /* Check Agreement Status and unset the in process flag so that
259 agreement can be used again */
260
261 FUN_NET_ARAP_PKG.Set_Agreement_Status(
262 x_batch_id => p_batch_id,
263 x_agreement_id => l_agreement_id,
264 x_mode => 'UNSET',
265 x_return_status => l_return_status);
266
267 IF l_return_status = FND_API.G_FALSE THEN
268 RAISE FND_API.G_EXC_ERROR;
269 END IF;
270
271
272 /* Delete AP Invoices belonging to the Batch */
273
274 delete_ap_invs(
275 p_batch_id => p_batch_id,
276 x_return_status => l_return_status);
277
278
279 IF l_return_status = FND_API.G_FALSE THEN
280 RAISE FND_API.G_EXC_ERROR;
281 END IF;
282
283 /* Delete AR Transactions belonging to the Batch */
284 delete_ar_txns(
285 x_batch_id => p_batch_id,
286 x_return_status => l_return_status);
287
288 IF l_return_status = FND_API.G_FALSE THEN
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 /* Delete the Batch */
293
294 FUN_NET_BATCHES_PKG.Delete_Row(
295 x_batch_id => p_batch_id);
296
297 -- Standard check of p_commit.
298 IF FND_API.To_Boolean( p_commit ) THEN
299 COMMIT WORK;
300 END IF;
301
302 EXCEPTION
303 WHEN FND_API.G_EXC_ERROR THEN
304 ROLLBACK TO cancel_net_batch_SP;
305 x_return_status := FND_API.G_RET_STS_ERROR;
306 FND_MSG_PUB.Count_And_Get (
307 p_count => x_msg_count,
308 p_data => x_msg_data);
309
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311 ROLLBACK TO cancel_net_batch_SP;
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 FND_MSG_PUB.Count_And_Get (
314 p_count => x_msg_count,
315 p_data => x_msg_data );
316
317 WHEN OTHERS THEN
318 ROLLBACK TO Cancel_Net_Batch_SP;
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
320 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
321 FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'cancel_net_batch');
322 END IF;
323 FND_MSG_PUB.Count_And_Get (
324 p_count => x_msg_count,
325 p_data => x_msg_data );
326
327 END cancel_net_batch;
328
329 PROCEDURE Validate_Settlement_Period(
330 x_appln_id IN fnd_application.application_id%TYPE,
331 p_batch_id IN fun_net_batches_all.batch_id%TYPE,
332 x_period_name OUT NOCOPY VARCHAR2,
333 x_return_status OUT NOCOPY VARCHAR2,
334 x_return_msg OUT NOCOPY VARCHAR2)
335
336 IS
337
338 CURSOR c_get_batch_details IS
339 SELECT org_id,
340 gl_date,
341 settlement_date
342 FROM fun_net_batches_all
343 WHERE batch_id = p_batch_id;
344
345 l_ledger_id gl_ledgers.ledger_id%TYPE;
346 l_ledger_name gl_ledgers.name%TYPE;
347 x_closing_status gl_period_statuses.closing_status%TYPE;
348 x_period_year gl_period_statuses.period_year%TYPE;
349 x_period_num gl_period_statuses.period_num%TYPE;
350 x_period_type gl_period_statuses.period_type%TYPE;
351 l_org_id fun_net_batches_all.org_id%TYPE;
352 l_gl_date fun_net_batches_all.gl_date%TYPE;
353 l_settlement_date fun_net_batches_all.settlement_date%TYPE;
354
355 l_path VARCHAR2(100);
356 BEGIN
357 l_path := g_path || 'Validate_Settlement_period';
358
359 /* Check if GL Period is open*/
360 x_return_status := FND_API.G_TRUE;
361 OPEN c_get_batch_details;
362 FETCH c_get_batch_details INTO l_org_id,l_gl_date,l_settlement_date;
363 CLOSE c_get_batch_details;
364
365 fun_net_util.Log_String(g_state_level,l_path,'Fetching ledger for org_id :'|| l_org_id);
366 MO_Utils.Get_Ledger_Info(
367 l_org_id,
368 l_ledger_id,
369 l_ledger_name);
370
371 /*SELECT set_of_books_id
372 INTO l_ledger_id
373 FROM hr_operating_units
374 WHERE organization_id = g_batch_details.org_id; */
375 fun_net_util.Log_String(g_state_level,l_path,'Ledger_id :'||l_ledger_id);
376
377 GL_PERIOD_STATUSES_PKG.get_period_by_date(
378 x_appln_id,
379 l_ledger_id,
380 nvl(l_gl_date,l_settlement_date),
381 x_period_name,
382 x_closing_status,
383 x_period_year,
384 x_period_num,
385 x_period_type);
386 fun_net_util.Log_String(g_state_level,l_path,'After getting period status');
387 IF (x_period_name IS NULL and x_closing_status IS NULL) OR
388 x_closing_status not in ('O','F') THEN
389 x_return_status := FND_API.G_FALSE;
390 fun_net_util.Log_String(g_state_level,l_path,'Period not open');
391 END IF;
392 EXCEPTION
393 WHEN OTHERS THEN
394 x_return_status := FND_API.G_FALSE;
395 END Validate_Settlement_Period;
396
397 /* Validates the GL Period before reversing AP Checks created for the batch.
398 Calls AP Reverse API to reverse a check */
399
400 PROCEDURE reverse_ap_checks(
401 p_batch_id IN fun_net_batches.batch_id%TYPE,
402 x_return_status OUT NOCOPY VARCHAR2)
403 IS
404 CURSOR ap_reverse_cur(p_batch_id IN NUMBER) IS
405 SELECT DISTINCT check_id AS check_id
406 FROM fun_net_ap_invs_all
407 WHERE batch_id = p_batch_id
408 AND check_id is not null;
409
410 l_return_status VARCHAR2(1);
411 l_return_msg VARCHAR2(1000);
412 l_period_name gl_period_statuses.period_name%TYPE;
413 l_num_cancelled NUMBER;
414 l_num_not_cancelled NUMBER;
415 l_msg_count NUMBER;
416 l_msg_data VARCHAR2(1000);
417 l_path VARCHAR2(100);
418 BEGIN
419 l_path := g_path || 'reverse_ap_checks';
420 x_return_status := FND_API.G_TRUE;
421
422 /* Validate GL Period */
423 fun_net_util.Log_String(g_state_level,l_path,'Validating settlement period');
424 Validate_Settlement_Period(
425 x_appln_id => 200,
426 p_batch_id => p_batch_id,
427 x_period_name => l_period_name,
428 x_return_status => l_return_status,
429 x_return_msg => l_return_msg);
430
431 IF l_return_status = FND_API.G_FALSE THEN
432 x_return_status := FND_API.G_FALSE;
433 RETURN;
434 END IF;
435
436 FOR ap_reverse_rec IN ap_reverse_cur(p_batch_id)
437 LOOP
438 fun_net_util.Log_String(g_state_level,l_path,'Reversing check for batch:'||p_batch_id);
439
440 fun_net_util.Log_String(g_state_level,l_path,'Reversing check for check :'||ap_reverse_rec.check_id);
441
442 AP_VOID_PKG.Ap_Reverse_Check(
443 P_Check_Id => ap_reverse_rec.check_id,
444 P_Replace_Flag => 'N',
445 P_Reversal_Date => sysdate,
446 P_Reversal_Period_Name => l_period_name,
447 P_Checkrun_Name => '',
448 P_Invoice_Action => 'NONE',
449 P_Hold_Code => '',
450 P_Hold_Reason => '',
451 P_Sys_Auto_Calc_Int_Flag => 'N',
452 P_Vendor_Auto_Calc_Int_Flag => 'N',
453 P_Last_Updated_By => g_user_id,
454 P_Last_Update_Login => g_login_id,
455 P_Num_Cancelled => l_num_cancelled,
456 P_Num_Not_Cancelled => l_num_not_cancelled,
457 P_Calling_Sequence => 'Netting Batch - Reversing',
458 X_return_status => l_return_status,
459 X_msg_count => l_msg_count,
460 X_msg_data => l_msg_data);
461
462
463 IF l_return_status = FND_API.G_FALSE THEN
464 x_return_status := FND_API.G_FALSE;
465 RETURN;
466 END IF;
467
468 END LOOP;
469 fun_net_util.Log_String(g_state_level,l_path,'Successfully reversed AP invoices');
470 EXCEPTION
471 WHEN OTHERS THEN
472 x_return_status := FND_API.G_FALSE;
473 END reverse_ap_checks;
474
475 /* Validates the GL Period for the Reversal Date. If the Period is not 'Open'
476 or 'Future' then raises an error.
477 Calls AR Reverse API to reverse the receipts created for the batch */
478
479 PROCEDURE reverse_ar_receipts(
480 p_batch_id IN fun_net_batches.batch_id%TYPE,
481 x_return_status OUT NOCOPY VARCHAR2)
482 IS
483 l_period_name GL_PERIOD_STATUSES.period_name%TYPE;
484
485 CURSOR ar_txn_cur(p_batch_id IN NUMBER) IS
486 SELECT DISTINCT txn.cash_receipt_id,
487 cr.receipt_number,
488 cr.receipt_date,
489 txn.org_id
490 FROM
491 fun_net_ar_txns txn,
492 ar_cash_receipts_all cr
493 WHERE txn.batch_id = p_batch_id
494 AND txn.cash_receipt_id = cr.cash_receipt_id
495 AND txn.org_id = cr.org_id;
496
497 /* p_receipt_gl_date should be populated as the max(gl_date) from CRH */
498 CURSOR c_get_reverse_gl_date(p_cr_id IN NUMBER) IS
499 SELECT max(crh.gl_date)
500 FROM ar_cash_receipt_history crh
501 WHERE crh.cash_receipt_id = p_cr_id;
502
503 l_msg_data VARCHAR2(1000);
504 l_msg_count NUMBER;
505 l_return_status VARCHAR2(1);
506 l_reverse_gl_date DATE;
507 l_reversal_date DATE;
508 l_path VARCHAR2(100);
509 BEGIN
510 l_path := g_path || 'reverse_ar_receipts';
511 x_return_status := FND_API.G_TRUE;
512 fun_net_util.Log_String(g_state_level,l_path,'Validating AR period');
513 Validate_Settlement_Period(
514 x_appln_id => 222,
515 p_batch_id => p_batch_id,
516 x_period_name => l_period_name,
517 x_return_status => x_return_status,
518 x_return_msg => l_msg_data);
519
520 IF x_return_status = FND_API.G_FALSE THEN
521 RETURN;
522 END IF;
523
524 FOR ar_txn_rec IN ar_txn_cur(p_batch_id)
525 LOOP
526 fun_net_util.Log_String(g_state_level,l_path,'Reversing transactions for batch:'||p_batch_id);
527 l_reversal_date := ar_txn_rec.receipt_date;
528 IF TRUNC(SYSDATE) >= l_reversal_date THEN
529 l_reversal_date := TRUNC(SYSDATE);
530 END IF;
531
532 OPEN c_get_reverse_gl_date(ar_txn_rec.cash_receipt_id);
533 FETCH c_get_reverse_gl_date INTO l_reverse_gl_date;
534 CLOSE c_get_reverse_gl_date;
535
536 IF TRUNC(SYSDATE) >= l_reverse_gl_date THEN
537 l_reverse_gl_date := TRUNC(SYSDATE);
538 END IF;
539
540 AR_RECEIPT_API_PUB.Reverse(
541 -- Standard API parameters.
542 p_api_version => 1.0,
543 p_init_msg_list => FND_API.G_TRUE,
544 p_commit => FND_API.G_FALSE,
545 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
546 x_return_status => l_return_status,
547 x_msg_count => l_msg_count,
548 x_msg_data => l_msg_data,
549 -- Receipt reversal related parameters
550 p_cash_receipt_id => ar_txn_rec.cash_receipt_id,
551 --p_receipt_number => ar_txn_rec.receipt_number,
552 p_reversal_category_code => 'REV',
553 p_reversal_gl_date => l_reverse_gl_date,
554 p_reversal_date => l_reversal_date,
555 p_reversal_reason_code => 'PAYMENT REVERSAL',
556 p_org_id => ar_txn_rec.org_id
557 );
558
559
560 FND_MSG_PUB.Count_And_Get (
561 p_count => l_msg_count,
562 p_data => l_msg_data );
563
564 fun_net_util.Log_String(g_event_level,l_path
565 ,'apply cash receipt package after AR_RECEIPT_API_PUB.Reverse:' ||l_msg_data);
566
567 IF l_msg_count > 1 THEN
568
569
570
571 FOR x IN 1..l_msg_count LOOP
572
573 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
574 fun_net_util.Log_String(g_event_level,l_path
575 ,'Reverse package Error message AR_RECEIPT_API_PUB.Reverse' ||l_msg_data||' '||' '||x);
576
577 END LOOP;
578
579
580 END IF;
581 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
582 fun_net_util.Log_String(g_state_level,l_path,'Error in reversing AR transactions');
583 x_return_status := FND_API.G_FALSE;
584 RETURN;
585 END IF;
586 END LOOP;
587 fun_net_util.Log_String(g_state_level,l_path,'Successfully reversed AR transactions');
588 EXCEPTION
589 WHEN OTHERS THEN
590 x_return_status := FND_API.G_FALSE;
591
592 END reverse_ar_receipts;
593
594 PROCEDURE Update_Amounts(
595 p_batch_id IN fun_net_batches.batch_id%TYPE,
596 x_return_status OUT NOCOPY VARCHAR2)
597 IS
598 BEGIN
599 BEGIN
600 UPDATE FUN_NET_AP_INVS
601 SET netted_amt = 0
602 WHERE batch_id = p_batch_id;
603 EXCEPTION
604 WHEN OTHERS THEN
605 x_return_status := FND_API.G_FALSE;
606 RETURN;
607 END;
608 BEGIN
609 UPDATE FUN_NET_AR_TXNS
610 SET netted_amt = 0
611 WHERE batch_id = p_batch_id;
612 EXCEPTION
613 WHEN OTHERS THEN
614 x_return_status := FND_API.G_FALSE;
615 RETURN;
616 END;
617 BEGIN
618 UPDATE FUN_NET_BATCHES
619 SET total_netted_amt = 0
620 WHERE batch_id = p_batch_id;
621 EXCEPTION
622 WHEN OTHERS THEN
623 x_return_status := FND_API.G_FALSE;
624 RETURN;
625 END;
626 EXCEPTION
627 WHEN OTHERS THEN
628 x_return_status := FND_API.G_FALSE;
629
630 END;
631
632 PROCEDURE reverse_net_batch(
633 -- ***** Standard API Parameters *****
634 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
635 p_commit IN VARCHAR2 := FND_API.G_FALSE,
636 x_return_status OUT NOCOPY VARCHAR2,
637 x_msg_count OUT NOCOPY NUMBER,
638 x_msg_data OUT NOCOPY VARCHAR2,
639 -- ***** Netting batch input parameters *****
640 p_batch_id IN NUMBER) IS
641
642 -- ***** local variables *****
643 l_return_status VARCHAR2(1);
644 l_msg_count NUMBER;
645 l_msg_data VARCHAR2(2000);
646
647 l_agreement_id FUN_NET_AGREEMENTS.AGREEMENT_ID%TYPE;
648 l_path VARCHAR2(100);
649 BEGIN
650 l_path := g_path || 'reverse_net_batch';
651 x_msg_count := NULL;
652 x_msg_data := NULL;
653 g_user_id := fnd_global.user_id;
654 g_login_id := fnd_global.login_id;
655
656 -- **** Standard start of API savepoint ****
657 SAVEPOINT reverse_net_batch_SP;
658 fun_net_util.Log_String(g_state_level,l_path,'Set the savepoint');
659 -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
660 IF FND_API.to_Boolean( p_init_msg_list ) THEN
661 FND_MSG_PUB.initialize;
662 END IF;
663
664 -- **** Initialize return status to SUCCESS *****
665 x_return_status := FND_API.G_RET_STS_SUCCESS;
666
667 /*-----------------------------------------------+
668 | ======== START OF API BODY ============ |
669 +-----------------------------------------------*/
670
671 /* Check for mandatory parameters */
672
673 IF p_batch_id IS NULL THEN
674 RAISE FND_API.G_EXC_ERROR;
675 END IF;
676
677 IF l_return_status = FND_API.G_FALSE THEN
678 RAISE FND_API.G_EXC_ERROR;
679 END IF;
680
681 /* Check the batch status before reversing */
682 fun_net_util.Log_String(g_state_level,l_path,'Validating batch status');
683 Validate_Batch_Status(
684 p_mode => 'REVERSE',
685 p_batch_id => p_batch_id,
686 x_return_status => l_return_status);
687
688 IF l_return_status = FND_API.G_FALSE THEN
689 RAISE FND_API.G_EXC_ERROR;
690 END IF;
691
692 /* Update Batch Status to Reversing */
693 fun_net_util.Log_String(g_state_level,l_path,'Updating batch status');
694 IF NOT FUN_NET_ARAP_PKG.update_batch_status('REVERSING') THEN
695 RAISE FND_API.G_EXC_ERROR;
696 END IF;
697
698 /* Reverse the Checks for the AP Invoices in the given batch */
699 fun_net_util.Log_String(g_state_level,l_path,'Reversing AP checks');
700 reverse_ap_checks(
701 p_batch_id => p_batch_id,
702 x_return_status => l_return_status);
703
704 IF l_return_status = FND_API.G_FALSE THEN
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707
708 /* Reverse the Receipts created for the AR Txns in the batch */
709 fun_net_util.Log_String(g_state_level,l_path,'Reversing AR receipts');
710 reverse_ar_receipts(
711 p_batch_id => p_batch_id,
712 x_return_status => l_return_status);
713
714 IF l_return_status = FND_API.G_FALSE THEN
715 RAISE FND_API.G_EXC_ERROR;
716 END IF;
717
718 /* Update Agreement Status */
719 fun_net_util.Log_String(g_state_level,l_path,'Get agreement');
720 get_agreement(
721 p_batch_id => p_batch_id,
722 x_agreement_id => l_agreement_id,
723 x_return_status => l_return_status);
724
725 IF l_return_status = FND_API.G_FALSE THEN
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728 fun_net_util.Log_String(g_state_level,l_path,'Updating agreement status to N');
729 FUN_NET_ARAP_PKG.Set_Agreement_Status(
730 x_batch_id => p_batch_id,
731 x_agreement_id => l_agreement_id,
732 x_mode => 'UNSET',
733 x_return_status => l_return_status);
734
735 IF l_return_status = FND_API.G_FALSE THEN
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738 /* Update Amounts */
739 fun_net_util.Log_String(g_state_level,l_path,'Updating batch amounts');
740 Update_amounts(
741 p_batch_id => p_batch_id,
742 x_return_status => l_return_status);
743
744 IF l_return_status = FND_API.G_FALSE THEN
745 RAISE FND_API.G_EXC_ERROR;
746 END IF;
747
748 /* Update Batch Status */
749 fun_net_util.Log_String(g_state_level,l_path,'Updating batch status to REVERSED');
750 UPDATE fun_net_batches
751 SET batch_status_code = 'REVERSED'
752 WHERE batch_id = p_batch_id;
753
754 /*IF NOT FUN_NET_ARAP_PKG.update_batch_status('REVERSED') THEN
755
756 RAISE FND_API.G_EXC_ERROR;
757 END IF; */
758
759 -- Standard check of p_commit.
760 IF FND_API.To_Boolean( p_commit ) THEN
761 COMMIT WORK;
762
763 END IF;
764 fun_net_util.Log_String(g_state_level,l_path,'Successfully batch reversal');
765 EXCEPTION
766 WHEN FND_API.G_EXC_ERROR THEN
767 ROLLBACK TO Reverse_net_batch_SP;
768 x_return_status := FND_API.G_RET_STS_ERROR;
769 FND_MSG_PUB.Count_And_Get (
770 p_count => x_msg_count,
771 p_data => x_msg_data );
772
773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
774 ROLLBACK TO reverse_net_batch_SP;
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776 FND_MSG_PUB.Count_And_Get (
777 p_count => x_msg_count,
778 p_data => x_msg_data );
779
780 WHEN OTHERS THEN
781 ROLLBACK TO reverse_Net_Batch_SP;
782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
783 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
784 --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
785 FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'reverse_net_batch');
786 END IF;
787 FND_MSG_PUB.Count_And_Get (
788 p_count => x_msg_count,
789 p_data => x_msg_data );
790 END reverse_net_batch;
791
792 BEGIN
793 g_today := TRUNC(sysdate);
794 --===========================FND_LOG.START=====================================
795
796 g_state_level := FND_LOG.LEVEL_STATEMENT;
797 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
798 g_event_level := FND_LOG.LEVEL_EVENT;
799 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
800 g_error_level := FND_LOG.LEVEL_ERROR;
801 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
802 g_path := 'FUN.PLSQL.funntcrb.FUN_NET_CANCEL_PKG.';
803
804 --===========================FND_LOG.END=======================================
805
806 END FUN_NET_CANCEL_PKG;