[Home] [Help]
PACKAGE BODY: APPS.FUN_NET_CANCEL_PKG
Source
1 PACKAGE BODY FUN_NET_CANCEL_PKG AS
2 /* $Header: funntcrb.pls 120.7.12010000.5 2009/12/14 14:36:54 ychandra 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' or 'SUSPENDED' or 'ERROR' 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 OR SUSPENDED OR ERROR
101 AR Transactions are not locked at this point */
102 --Bug: 8342419
103 IF (g_batch_status = 'SELECTED') OR (g_batch_status='ERROR') OR (g_batch_status='SUSPENDED')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 p_reversal_date IN DATE, -- Bug # 9196412
333 x_period_name OUT NOCOPY VARCHAR2,
334 x_return_status OUT NOCOPY VARCHAR2,
335 x_return_msg OUT NOCOPY VARCHAR2)
336
337 IS
338
339 CURSOR c_get_batch_details IS
340 SELECT org_id,
341 gl_date,
342 settlement_date
343 FROM fun_net_batches_all
344 WHERE batch_id = p_batch_id;
345
346 l_ledger_id gl_ledgers.ledger_id%TYPE;
347 l_ledger_name gl_ledgers.name%TYPE;
348 x_closing_status gl_period_statuses.closing_status%TYPE;
349 x_period_year gl_period_statuses.period_year%TYPE;
350 x_period_num gl_period_statuses.period_num%TYPE;
351 x_period_type gl_period_statuses.period_type%TYPE;
352 l_org_id fun_net_batches_all.org_id%TYPE;
353 l_gl_date fun_net_batches_all.gl_date%TYPE;
354 l_settlement_date fun_net_batches_all.settlement_date%TYPE;
355
356 l_path VARCHAR2(100);
357 BEGIN
358 l_path := g_path || 'Validate_Settlement_period';
359
360 /* Check if GL Period is open*/
361 x_return_status := FND_API.G_TRUE;
362 OPEN c_get_batch_details;
363 FETCH c_get_batch_details INTO l_org_id,l_gl_date,l_settlement_date;
364 CLOSE c_get_batch_details;
365
366 fun_net_util.Log_String(g_state_level,l_path,'Fetching ledger for org_id :'|| l_org_id);
367 MO_Utils.Get_Ledger_Info(
368 l_org_id,
369 l_ledger_id,
370 l_ledger_name);
371
372 /*SELECT set_of_books_id
373 INTO l_ledger_id
374 FROM hr_operating_units
375 WHERE organization_id = g_batch_details.org_id; */
376 fun_net_util.Log_String(g_state_level,l_path,'Ledger_id :'||l_ledger_id);
377 -- Bug: 8509936.
378 GL_PERIOD_STATUSES_PKG.get_period_by_date(
379 x_appln_id,
380 l_ledger_id,
381 --nvl(l_gl_date,l_settlement_date),
382 trunc(p_reversal_date), -- Bug # 9196412
383 x_period_name,
384 x_closing_status,
385 x_period_year,
386 x_period_num,
387 x_period_type);
388 fun_net_util.Log_String(g_state_level,l_path,'After getting period status');
389 IF (x_period_name IS NULL and x_closing_status IS NULL) OR
390 x_closing_status not in ('O','F') THEN
391 x_return_status := FND_API.G_FALSE;
392 fun_net_util.Log_String(g_state_level,l_path,'Period not open');
393 END IF;
394 EXCEPTION
395 WHEN OTHERS THEN
396 x_return_status := FND_API.G_FALSE;
397 END Validate_Settlement_Period;
398
399 /* Validates the GL Period before reversing AP Checks created for the batch.
400 Calls AP Reverse API to reverse a check */
401
402 PROCEDURE reverse_ap_checks(
403 p_batch_id IN fun_net_batches.batch_id%TYPE,
404 x_return_status OUT NOCOPY VARCHAR2)
405 IS
406 CURSOR ap_reverse_cur(p_batch_id IN NUMBER) IS
407 SELECT DISTINCT check_id AS check_id
408 FROM fun_net_ap_invs_all
409 WHERE batch_id = p_batch_id
410 AND check_id is not null;
411
412 CURSOR c_get_batch_details IS -- Bug # 8904763
413 SELECT gl_date,
414 settlement_date
415 FROM fun_net_batches_all
416 WHERE batch_id = p_batch_id;
417
418 l_return_status VARCHAR2(1);
419 l_return_msg VARCHAR2(1000);
420 l_period_name gl_period_statuses.period_name%TYPE;
421 l_num_cancelled NUMBER;
422 l_num_not_cancelled NUMBER;
423 l_msg_count NUMBER;
424 l_msg_data VARCHAR2(1000);
425 l_path VARCHAR2(100);
426 l_gl_date fun_net_batches_all.gl_date%TYPE; -- Bug # 8904763
427 l_settlement_date fun_net_batches_all.settlement_date%TYPE; -- Bug # 8904763
428 l_reversal_date DATE; -- Bug # 8904763
429
430 BEGIN
431 l_path := g_path || 'reverse_ap_checks';
432 x_return_status := FND_API.G_TRUE;
433
434
435 OPEN c_get_batch_details; -- Bug # 8904763
436 FETCH c_get_batch_details INTO l_gl_date,l_settlement_date;
437 CLOSE c_get_batch_details;
438
439 IF l_gl_date IS NULL THEN -- Bug # 8904763
440 l_reversal_date:=l_settlement_date;
441 Else
442 l_reversal_date:=l_gl_date;
443 END IF;
444
445 SELECT GREATEST(SYSDATE,l_reversal_date) INTO l_reversal_date FROM dual; -- Bug # 9196412
446
447
448 /* Validate GL Period */
449 fun_net_util.Log_String(g_state_level,l_path,'Validating settlement period');
450 Validate_Settlement_Period(
451 x_appln_id => 200,
452 p_batch_id => p_batch_id,
453 p_reversal_date => l_reversal_date,
454 x_period_name => l_period_name,
455 x_return_status => l_return_status,
456 x_return_msg => l_return_msg);
457
458 IF l_return_status = FND_API.G_FALSE THEN
459 x_return_status := FND_API.G_FALSE;
460 RETURN;
461 END IF;
462
463
464 FOR ap_reverse_rec IN ap_reverse_cur(p_batch_id)
465 LOOP
466 fun_net_util.Log_String(g_state_level,l_path,'Reversing check for batch:'||p_batch_id);
467
468 fun_net_util.Log_String(g_state_level,l_path,'Reversing check for check :'||ap_reverse_rec.check_id);
469
470 AP_VOID_PKG.Ap_Reverse_Check(
471 P_Check_Id => ap_reverse_rec.check_id,
472 P_Replace_Flag => 'N',
473 P_Reversal_Date => l_reversal_date, -- Bug # 8904763
474 P_Reversal_Period_Name => l_period_name,
475 P_Checkrun_Name => '',
476 P_Invoice_Action => 'NONE',
477 P_Hold_Code => '',
478 P_Hold_Reason => '',
479 P_Sys_Auto_Calc_Int_Flag => 'N',
480 P_Vendor_Auto_Calc_Int_Flag => 'N',
481 P_Last_Updated_By => g_user_id,
482 P_Last_Update_Login => g_login_id,
483 P_Num_Cancelled => l_num_cancelled,
484 P_Num_Not_Cancelled => l_num_not_cancelled,
485 P_Calling_Sequence => 'Netting Batch - Reversing',
486 X_return_status => l_return_status,
487 X_msg_count => l_msg_count,
488 X_msg_data => l_msg_data);
489
490
491 IF l_return_status = FND_API.G_FALSE THEN
492 x_return_status := FND_API.G_FALSE;
493 RETURN;
494 END IF;
495
496 END LOOP;
497 fun_net_util.Log_String(g_state_level,l_path,'Successfully reversed AP invoices');
498 EXCEPTION
499 WHEN OTHERS THEN
500 x_return_status := FND_API.G_FALSE;
501 END reverse_ap_checks;
502
503 /* Validates the GL Period for the Reversal Date. If the Period is not 'Open'
504 or 'Future' then raises an error.
505 Calls AR Reverse API to reverse the receipts created for the batch */
506
507 PROCEDURE reverse_ar_receipts(
508 p_batch_id IN fun_net_batches.batch_id%TYPE,
509 x_return_status OUT NOCOPY VARCHAR2)
510 IS
511 l_period_name GL_PERIOD_STATUSES.period_name%TYPE;
512
513 CURSOR ar_txn_cur(p_batch_id IN NUMBER) IS
514 SELECT DISTINCT txn.cash_receipt_id,
515 cr.receipt_number,
516 cr.receipt_date,
517 txn.org_id
518 FROM
519 fun_net_ar_txns txn,
520 ar_cash_receipts_all cr
521 WHERE txn.batch_id = p_batch_id
522 AND txn.cash_receipt_id = cr.cash_receipt_id
523 AND txn.org_id = cr.org_id;
524
525 /* p_receipt_gl_date should be populated as the max(gl_date) from CRH */
526 CURSOR c_get_reverse_gl_date(p_cr_id IN NUMBER) IS
527 SELECT max(crh.gl_date)
528 FROM ar_cash_receipt_history crh
529 WHERE crh.cash_receipt_id = p_cr_id;
530
531 l_msg_data VARCHAR2(1000);
532 l_msg_count NUMBER;
533 l_return_status VARCHAR2(1);
534 l_reverse_gl_date DATE;
535 l_reversal_date DATE;
536 l_path VARCHAR2(100);
537 BEGIN
538 l_path := g_path || 'reverse_ar_receipts';
539 x_return_status := FND_API.G_TRUE;
540
541
542 FOR ar_txn_rec IN ar_txn_cur(p_batch_id)
543 LOOP
544 fun_net_util.Log_String(g_state_level,l_path,'Reversing transactions for batch:'||p_batch_id);
545 l_reversal_date := ar_txn_rec.receipt_date;
546 IF TRUNC(SYSDATE) >= l_reversal_date THEN
547 l_reversal_date := TRUNC(SYSDATE);
548 END IF;
549
550 OPEN c_get_reverse_gl_date(ar_txn_rec.cash_receipt_id);
551 FETCH c_get_reverse_gl_date INTO l_reverse_gl_date;
552 CLOSE c_get_reverse_gl_date;
553
554 IF TRUNC(SYSDATE) >= l_reverse_gl_date THEN
555 l_reverse_gl_date := TRUNC(SYSDATE);
556 END IF;
557
558 fun_net_util.Log_String(g_state_level,l_path,'Validating AR period');
559 Validate_Settlement_Period(
560 x_appln_id => 222,
561 p_batch_id => p_batch_id,
562 p_reversal_date => l_reverse_gl_date, -- Bug # 9196412
563 x_period_name => l_period_name,
564 x_return_status => x_return_status,
565 x_return_msg => l_msg_data);
566
567 IF x_return_status = FND_API.G_FALSE THEN
568 RETURN;
569 END IF;
570
571 AR_RECEIPT_API_PUB.Reverse(
572 -- Standard API parameters.
573 p_api_version => 1.0,
574 p_init_msg_list => FND_API.G_TRUE,
575 p_commit => FND_API.G_FALSE,
576 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
577 x_return_status => l_return_status,
578 x_msg_count => l_msg_count,
579 x_msg_data => l_msg_data,
580 -- Receipt reversal related parameters
581 p_cash_receipt_id => ar_txn_rec.cash_receipt_id,
582 --p_receipt_number => ar_txn_rec.receipt_number,
583 p_reversal_category_code => 'REV',
584 p_reversal_gl_date => l_reverse_gl_date,
585 p_reversal_date => l_reversal_date,
586 p_reversal_reason_code => 'PAYMENT REVERSAL',
587 p_org_id => ar_txn_rec.org_id
588 );
589
590
591 FND_MSG_PUB.Count_And_Get (
592 p_count => l_msg_count,
593 p_data => l_msg_data );
594
595 fun_net_util.Log_String(g_event_level,l_path
596 ,'apply cash receipt package after AR_RECEIPT_API_PUB.Reverse:' ||l_msg_data);
597
598 IF l_msg_count > 1 THEN
599
600
601
602 FOR x IN 1..l_msg_count LOOP
603
604 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
605 fun_net_util.Log_String(g_event_level,l_path
606 ,'Reverse package Error message AR_RECEIPT_API_PUB.Reverse' ||l_msg_data||' '||' '||x);
607
608 END LOOP;
609
610
611 END IF;
612 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
613 fun_net_util.Log_String(g_state_level,l_path,'Error in reversing AR transactions');
614 x_return_status := FND_API.G_FALSE;
615 RETURN;
616 END IF;
617 END LOOP;
618 fun_net_util.Log_String(g_state_level,l_path,'Successfully reversed AR transactions');
619 EXCEPTION
620 WHEN OTHERS THEN
621 x_return_status := FND_API.G_FALSE;
622
623 END reverse_ar_receipts;
624
625 PROCEDURE Update_Amounts(
626 p_batch_id IN fun_net_batches.batch_id%TYPE,
627 x_return_status OUT NOCOPY VARCHAR2)
628 IS
629 BEGIN
630 BEGIN
631 UPDATE FUN_NET_AP_INVS
632 SET netted_amt = 0
633 WHERE batch_id = p_batch_id;
634 EXCEPTION
635 WHEN OTHERS THEN
636 x_return_status := FND_API.G_FALSE;
637 RETURN;
638 END;
639 BEGIN
640 UPDATE FUN_NET_AR_TXNS
641 SET netted_amt = 0
642 WHERE batch_id = p_batch_id;
643 EXCEPTION
644 WHEN OTHERS THEN
645 x_return_status := FND_API.G_FALSE;
646 RETURN;
647 END;
648 BEGIN
649 UPDATE FUN_NET_BATCHES
650 SET total_netted_amt = 0
651 WHERE batch_id = p_batch_id;
652 EXCEPTION
653 WHEN OTHERS THEN
654 x_return_status := FND_API.G_FALSE;
655 RETURN;
656 END;
657 EXCEPTION
658 WHEN OTHERS THEN
659 x_return_status := FND_API.G_FALSE;
660
661 END;
662
663 PROCEDURE reverse_net_batch(
664 -- ***** Standard API Parameters *****
665 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
666 p_commit IN VARCHAR2 := FND_API.G_FALSE,
667 x_return_status OUT NOCOPY VARCHAR2,
668 x_msg_count OUT NOCOPY NUMBER,
669 x_msg_data OUT NOCOPY VARCHAR2,
670 -- ***** Netting batch input parameters *****
671 p_batch_id IN NUMBER) IS
672
673 -- ***** local variables *****
674 l_return_status VARCHAR2(1);
675 l_msg_count NUMBER;
676 l_msg_data VARCHAR2(2000);
677
678 l_agreement_id FUN_NET_AGREEMENTS.AGREEMENT_ID%TYPE;
679 l_path VARCHAR2(100);
680 BEGIN
681 l_path := g_path || 'reverse_net_batch';
682 x_msg_count := NULL;
683 x_msg_data := NULL;
684 g_user_id := fnd_global.user_id;
685 g_login_id := fnd_global.login_id;
686
687 -- **** Standard start of API savepoint ****
688 SAVEPOINT reverse_net_batch_SP;
689 fun_net_util.Log_String(g_state_level,l_path,'Set the savepoint');
690 -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
691 IF FND_API.to_Boolean( p_init_msg_list ) THEN
692 FND_MSG_PUB.initialize;
693 END IF;
694
695 -- **** Initialize return status to SUCCESS *****
696 x_return_status := FND_API.G_RET_STS_SUCCESS;
697
698 /*-----------------------------------------------+
699 | ======== START OF API BODY ============ |
700 +-----------------------------------------------*/
701
702 /* Check for mandatory parameters */
703
704 IF p_batch_id IS NULL THEN
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707
708 IF l_return_status = FND_API.G_FALSE THEN
709 RAISE FND_API.G_EXC_ERROR;
710 END IF;
711
712 /* Check the batch status before reversing */
713 fun_net_util.Log_String(g_state_level,l_path,'Validating batch status');
714 Validate_Batch_Status(
715 p_mode => 'REVERSE',
716 p_batch_id => p_batch_id,
717 x_return_status => l_return_status);
718
719 IF l_return_status = FND_API.G_FALSE THEN
720 RAISE FND_API.G_EXC_ERROR;
721 END IF;
722
723 /* Update Batch Status to Reversing */
724 fun_net_util.Log_String(g_state_level,l_path,'Updating batch status');
725 IF NOT FUN_NET_ARAP_PKG.update_batch_status('REVERSING') THEN
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728
729 /* Reverse the Checks for the AP Invoices in the given batch */
730 fun_net_util.Log_String(g_state_level,l_path,'Reversing AP checks');
731 reverse_ap_checks(
732 p_batch_id => p_batch_id,
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
739 /* Reverse the Receipts created for the AR Txns in the batch */
740 fun_net_util.Log_String(g_state_level,l_path,'Reversing AR receipts');
741 reverse_ar_receipts(
742 p_batch_id => p_batch_id,
743 x_return_status => l_return_status);
744
745 IF l_return_status = FND_API.G_FALSE THEN
746 RAISE FND_API.G_EXC_ERROR;
747 END IF;
748
749 /* Update Agreement Status */
750 fun_net_util.Log_String(g_state_level,l_path,'Get agreement');
751 get_agreement(
752 p_batch_id => p_batch_id,
753 x_agreement_id => l_agreement_id,
754 x_return_status => l_return_status);
755
756 IF l_return_status = FND_API.G_FALSE THEN
757 RAISE FND_API.G_EXC_ERROR;
758 END IF;
759 fun_net_util.Log_String(g_state_level,l_path,'Updating agreement status to N');
760 FUN_NET_ARAP_PKG.Set_Agreement_Status(
761 x_batch_id => p_batch_id,
762 x_agreement_id => l_agreement_id,
763 x_mode => 'UNSET',
764 x_return_status => l_return_status);
765
766 IF l_return_status = FND_API.G_FALSE THEN
767 RAISE FND_API.G_EXC_ERROR;
768 END IF;
769 /* Update Amounts */
770 fun_net_util.Log_String(g_state_level,l_path,'Updating batch amounts');
771 Update_amounts(
772 p_batch_id => p_batch_id,
773 x_return_status => l_return_status);
774
775 IF l_return_status = FND_API.G_FALSE THEN
776 RAISE FND_API.G_EXC_ERROR;
777 END IF;
778
779 /* Update Batch Status */
780 fun_net_util.Log_String(g_state_level,l_path,'Updating batch status to REVERSED');
781 UPDATE fun_net_batches
782 SET batch_status_code = 'REVERSED'
783 WHERE batch_id = p_batch_id;
784
785 /*IF NOT FUN_NET_ARAP_PKG.update_batch_status('REVERSED') THEN
786
787 RAISE FND_API.G_EXC_ERROR;
788 END IF; */
789
790 -- Standard check of p_commit.
791 IF FND_API.To_Boolean( p_commit ) THEN
792 COMMIT WORK;
793
794 END IF;
795 fun_net_util.Log_String(g_state_level,l_path,'Successfully batch reversal');
796 EXCEPTION
797 WHEN FND_API.G_EXC_ERROR THEN
798 ROLLBACK TO Reverse_net_batch_SP;
799 x_return_status := FND_API.G_RET_STS_ERROR;
800 FND_MSG_PUB.Count_And_Get (
801 p_count => x_msg_count,
802 p_data => x_msg_data );
803
804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
805 ROLLBACK TO reverse_net_batch_SP;
806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
807 FND_MSG_PUB.Count_And_Get (
808 p_count => x_msg_count,
809 p_data => x_msg_data );
810
811 WHEN OTHERS THEN
812 ROLLBACK TO reverse_Net_Batch_SP;
813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
814 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
815 --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
816 FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'reverse_net_batch');
817 END IF;
818 FND_MSG_PUB.Count_And_Get (
819 p_count => x_msg_count,
820 p_data => x_msg_data );
821 END reverse_net_batch;
822
823 BEGIN
824 g_today := TRUNC(sysdate);
825 --===========================FND_LOG.START=====================================
826
827 g_state_level := FND_LOG.LEVEL_STATEMENT;
828 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
829 g_event_level := FND_LOG.LEVEL_EVENT;
830 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
831 g_error_level := FND_LOG.LEVEL_ERROR;
832 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
833 g_path := 'FUN.PLSQL.funntcrb.FUN_NET_CANCEL_PKG.';
834
835 --===========================FND_LOG.END=======================================
836
837 END FUN_NET_CANCEL_PKG;