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;