[Home] [Help]
PACKAGE BODY: APPS.FV_ECON_BENF_DISC
Source
1 PACKAGE BODY FV_ECON_BENF_DISC AS
2 -- $Header: FVXAPCHB.pls 120.9 2008/06/25 10:16:06 bnarang ship $
3 g_module_name VARCHAR2(100) := 'fv.plsql.FV_ECON_BENF_DISC.';
4
5 FUNCTION EBD_CHECK(x_batch_name IN VARCHAR2,
6 x_invoice_id IN NUMBER,
7 x_check_date IN DATE,
8 x_inv_due_date IN DATE,
9 x_discount_amount IN NUMBER,
10 x_discount_date IN DATE) RETURN CHAR AS
11 l_module_name VARCHAR2(200) := g_module_name || 'EBD_CHECK';
12
13 --MOAC changes
14 --v_org_id number := to_number(fnd_profile.value('ORG_ID'));
15
16 v_include char(1);
17
18 BEGIN
19
20 -- --------------------------------------
21 -- Just a dummy package body.
22 -- Created to be used by FV to implement
23 -- economically beneficial discount related details.
24 -- ------------------------------------------
25
26 --MOAC Changes : removed the org_id parameter that was being passed to the fv_install.enabled
27 IF fv_install.enabled THEN
28 -- if FV is installed then call the function to determine if
29 -- invoice should be included in the payment batch.
30
31 v_include := FV_ECON_BENF_DISC_PVT.EBD_CHECK(x_batch_name,
32 x_invoice_id,
33 x_check_date,
34 x_inv_due_date,
35 x_discount_amount,
36 x_discount_date);
37 RETURN v_include;
38 ELSE
39 RETURN 'Y';
40 END IF;
41 EXCEPTION
42 WHEN OTHERS THEN
43 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
44 fnd_message.set_token('ERROR',sqlerrm);
45 fnd_message.set_token('CALLING_SEQUENCE','FV_ECON_BENF_DISC.EBD_CHECK');
46 fnd_message.set_token('PARAMETERS','X_BATCH_NAME = '||x_batch_name||' X_INVOICE_ID = '||to_char(x_invoice_id)||' X_CHECK_DATE = '||to_char(x_check_date,'dd-MM-YYYY')||' X_INV_DUE_DATE = '||to_char(x_inv_due_date,'dd-MON-YYYY'));
47 fnd_message.set_token('DEBUG_INFO','FV EBD Code Hook');
48 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception');
49
50 app_exception.raise_exception;
51
52 END EBD_CHECK;
53
54
55 PROCEDURE FV_CALCULATE_INTEREST(x_invoice_id IN NUMBER,
56 x_sys_auto_flg IN VARCHAR2,
57 x_auto_calc_int_flg IN VARCHAR2,
58 x_check_date IN DATE,
59 x_payment_num NUMBER,
60 x_amount_remaining IN NUMBER,
61 x_discount_taken IN NUMBER,
62 x_discount_available IN NUMBER,
63 x_interest_amount OUT NOCOPY NUMBER) IS
64 p_set_of_books_id Gl_Sets_Of_Books.set_of_books_id%TYPE;
65 l_module_name VARCHAR2(2000);
66 P_interest_tolerance_amount NUMBER;
67 C_interest_amount NUMBER;
68 l_code VARCHAR2(30);
69 l_precision NUMBER;
70 BEGIN
71 l_module_name := g_module_name || 'fv_calculate_interest';
72 -------------------------------
73 -- Set the interest invoice_num
74 -------------------------------
75
76 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,'FVCI CODE HOOK');
77
78 -- modifed select below so that it does not fetch multiple rows
79 -- joined with ap_invoice_all to get ledger_id and org_id
80 -- Bug 5470357
81 SELECT nvl(aspa.interest_tolerance_amount,0)
82 INTO P_interest_tolerance_amount
83 FROM ap_system_parameters_all aspa,
84 ap_invoices_all aia
85 WHERE aia.invoice_id = x_invoice_id
86 AND aia.set_of_books_id = aspa.set_of_books_id
87 AND aia.org_id = nvl(aspa.org_id, aia.org_id)
88 AND rownum < 2;
89
90 -- comment out statement below. Bug 5470357
91 -- p_set_of_books_id := to_number(fnd_profile.value('GL_SET_OF_BKS_ID'));
92
93 BEGIN
94
95 SELECT (nvl(x_amount_remaining -
96 least(nvl(x_discount_taken, 0), x_discount_available), 0) *
97 power(1 + (annual_interest_rate / (12 * 100)),
98 trunc((least(x_check_date, due_date + 360)
99 -due_date) / 30)) *
100 (1 + ((annual_interest_rate / (360 * 100)) *
101 mod((least(x_check_date,due_date + 360)
102 -due_date), 30)))) -
103 nvl(x_amount_remaining - least(nvl(x_discount_taken, 0),
104 x_discount_available), 0)
105 INTO C_interest_amount
106 FROM ap_payment_schedules_all, ap_interest_periods
107 WHERE x_sys_auto_flg = 'Y'
108 AND x_auto_calc_int_flg = 'Y'
109 AND trunc(x_check_date) > trunc(due_date)
110 AND payment_num = x_payment_num
111 AND invoice_id = x_invoice_id
112 AND trunc(due_date+1) BETWEEN trunc(start_date) AND trunc(end_date)
113 AND (nvl(x_amount_remaining -
114 least(nvl(x_discount_taken, 0), x_discount_available), 0) *
115 power(1 + (annual_interest_rate / (12 * 100)),
116 trunc((least(x_check_date, due_date + 360)
117 -due_date) / 30)) *
118 (1 + ((annual_interest_rate / (360 * 100)) *
119 mod((least(x_check_date, due_date + 360)
120 -due_date), 30)))) -
121 nvl(x_amount_remaining - least(nvl(x_discount_taken, 0),
122 x_discount_available), 0)
123 >= P_interest_tolerance_amount;
124
125
126 Select invoice_currency_code
127 into l_code
128 from ap_invoices_all
129 where invoice_id =x_invoice_id;
130
131 select precision
132 into l_precision
133 from fnd_currencies
134 where currency_code =l_code;
135
136 -- Bug 5470357. Without this assignment statement the output would always be NULL
137 x_interest_amount := round(C_interest_amount ,l_precision);
138
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 x_interest_amount := NULL;
142 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
143 l_module_name,'FV Interest Amount is NULL');
144
145 WHEN OTHERS THEN
146
147 fnd_message.set_name('SQLAP','AP_DEBUG');
148 fnd_message.set_token('ERROR',sqlerrm);
149 fnd_message.set_token('CALLING_SEQUENCE','FV_ECON_BENF_DISC.FV_CALCULATE_INTEREST');
150 fnd_message.set_token('PARAMETERS','X_INVOICE_ID = '||x_invoice_id||' X_CHECK_DATE = '||to_char(x_check_date));
151 fnd_message.set_token('DEBUG_INFO','FV INTEREST Code Hook');
152 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
153 l_module_name,'Error in Calculating FV Interest Amount');
154
155 app_exception.raise_exception;
156 END;
157
158 END FV_CALCULATE_INTEREST;
159
160
161 END FV_ECON_BENF_DISC;