[Home] [Help]
PACKAGE BODY: APPS.FV_AR_PKG
Source
1 PACKAGE BODY fv_ar_pkg AS
2 /* $Header: FVARPDRB.pls 115.4 2003/12/17 21:19:43 ksriniva noship $ */
3 g_module_name VARCHAR2(100) := 'fv.plsql.FV_AR_PKG.';
4
5 PROCEDURE delete_offsetting_unapp(p_posting_control_id IN NUMBER,
6 p_sob_id IN NUMBER,
7 p_status OUT NOCOPY NUMBER) IS
8 l_module_name VARCHAR2(200) := g_module_name || 'delete_offsetting_unapp';
9 l_errbuf VARCHAR2(1024);
10
11 -- This cursor identifies the cash_receipts where the total UNAPP debit
12 -- and UNAPP credit are equal and net out.
13 CURSOR get_unapp_amt(pcid IN NUMBER,
14 sob_id IN NUMBER) IS
15 SELECT SUBSTR(reference22,1,INSTR(reference22,'C')-1) cash_receipt_id,
16 accounting_date,
17 SUM(entered_dr) entered_dr,
18 SUM(entered_cr) entered_cr,
19 SUM(accounted_dr) accounted_dr,
20 SUM(accounted_cr) accounted_cr
21 FROM gl_interface gi,
22 ar_cash_receipts cr
23 WHERE gi.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
24 AND gi.group_id = pcid
25 AND gi.user_je_source_name = 'Receivables'
26 AND gi.set_of_books_id = sob_id
27 AND substr(gi.reference29,7) = 'UNAPP'
28 AND cr.cash_receipt_id = substr(gi.reference22,1,instr(gi.reference22,'C')-1)
29 GROUP BY SUBSTR(gi.reference22,1,INSTR(gi.reference22,'C')-1),
30 cr.amount, cr.status, gi.accounting_date
31 HAVING SUM(entered_dr) = SUM(entered_cr)
32 AND SUM(accounted_dr) = SUM(accounted_cr)
33 AND cr.amount <> 0 ;
34
35 TYPE NumTab IS TABLE OF NUMBER;
36 TYPE DateTab IS TABLE OF DATE;
37 cash_receipt_id_t NumTab;
38 entered_dr_t NumTab;
39 entered_cr_t NumTab;
40 accounted_dr_t NumTab;
41 accounted_cr_t NumTab;
42 accounting_date_t DateTab;
43
44 l_last_fetch BOOLEAN := FALSE;
45 -- g_debug VARCHAR2(1) := NVL(fnd_profile.value('FV_DEBUG_FLAG'),'N');
46
47 BEGIN
48
49 p_status := 0;
50
51 OPEN get_unapp_amt(p_posting_control_id, p_sob_id);
52 LOOP
53 FETCH get_unapp_amt BULK COLLECT INTO
54 cash_receipt_id_t,
55 accounting_date_t,
56 entered_dr_t,
57 entered_cr_t,
58 accounted_dr_t,
59 accounted_cr_t
60 LIMIT 1000;
61
62 IF get_unapp_amt%NOTFOUND THEN
63 l_last_fetch := TRUE;
64 END IF;
65
66 IF cash_receipt_id_t.COUNT = 0 and l_last_fetch
67 THEN
68 EXIT;
69 END IF;
70
71 FORALL j IN cash_receipt_id_t.FIRST..cash_receipt_id_t.LAST
72 DELETE FROM gl_interface
73 WHERE reference30 = 'AR_RECEIVABLE_APPLICATIONS'
74 AND group_id = p_posting_control_id
75 AND substr(reference29,7) = 'UNAPP'
76 AND substr(reference22,1,instr(reference22,'C')-1) =
77 cash_receipt_id_t(j)
78 AND accounting_date = accounting_date_t(j);
79
80 IF l_last_fetch THEN
81 EXIT;
82 END IF;
83
84 END LOOP;
85 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
86 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FV_AR_PKG successfully completed.');
87 END IF;
88
89 EXCEPTION WHEN OTHERS THEN
90 p_status := 1;
91 l_errbuf := SQLERRM;
92 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'FV_AR_PKG COMPLETED WITH THE FOLLOWING ERROR:');
93 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,l_errbuf);
94
95 END delete_offsetting_unapp;
96 END fv_ar_pkg;