[Home] [Help]
PACKAGE BODY: APPS.JL_BR_INTEREST_HANDLING
Source
1 PACKAGE BODY JL_BR_INTEREST_HANDLING AS
2 /* $Header: jlbrsinb.pls 120.5.12010000.2 2009/01/17 06:39:44 mkandula ship $ */
3
4 PROCEDURE JL_BR_INTEREST(X_Interest_Type IN VARCHAR2,
5 X_Interest_Rate_Amount IN NUMBER,
6 X_Period_Days IN NUMBER,
7 X_Interest_Formula IN VARCHAR2,
8 X_Grace_Days IN NUMBER,
9 X_Penalty_Type IN VARCHAR2,
10 X_Penalty_Rate_Amount IN NUMBER,
11 X_Due_Date IN DATE,
12 X_Payment_Date IN DATE,
13 X_Invoice_Amount IN NUMBER,
14 X_JLBR_Calendar IN VARCHAR2,
15 X_JLBR_Local_Holiday IN VARCHAR2,
16 X_JLBR_Action_Non_Workday IN VARCHAR2,
17 X_Interest_Calculated IN OUT NOCOPY NUMBER,
18 X_Days_Late IN OUT NOCOPY NUMBER,
19 X_Exit_Code OUT NOCOPY NUMBER) IS
20
21 P_Date_Ok DATE;
22 P_Late_Days1 NUMBER(38);
23 P_Late_Days2 NUMBER(38);
24 P_Penalty_Calculated NUMBER; -- BUG Number 859348
25 P_Status NUMBER(38);
26 P_WorkDay_Ok VARCHAR2(11);
27 P_WorkDay_Date DATE;
28 P_Return_Code NUMBER(38);
29 C_interest_tolerance_amount number;
30 jg_app_short_name VARCHAR2(10);
31
32 BEGIN
33 P_Status := 0;
34 X_Exit_Code := 0;
35 P_Late_Days1 := 0;
36 P_Late_Days2 := 0;
37 P_Penalty_Calculated := 0;
38 X_Interest_Calculated := 0;
39 X_Days_Late := 0;
40
41 P_Date_Ok := X_Due_Date + NVL(X_Grace_Days,0);
42 --
43 -- Validate Tolerance from ap_system_parameters
44 --
45 SELECT nvl(interest_tolerance_amount,0)
46 INTO C_interest_tolerance_amount
47 FROM ap_system_parameters;
48
49
50 jl_br_workday_calendar.jl_br_check_date(to_char(P_Date_Ok, 'DD-MM-YYYY'),
51 X_JLBR_Calendar,
52 X_JLBR_Local_Holiday,
53 X_JLBR_Action_Non_Workday,
54 P_WorkDay_Ok,
55 P_Status);
56
57 P_WorkDay_Date := to_date(P_WorkDay_Ok, 'DD-MM-YYYY');
58
59 IF P_Status = 0 THEN
60 X_Days_Late := trunc(X_Payment_Date - P_WorkDay_Date);
61 P_Late_Days2 := trunc(X_Payment_Date - X_Due_Date);
62
63 IF X_Days_Late > 0 THEN
64 IF X_Interest_Type IS NOT NULL THEN
65 IF X_Interest_Type = 'R' THEN
66 IF X_Interest_Formula = 'S' THEN
67 X_Interest_Calculated := round(((NVL(X_Interest_Rate_Amount,0)/NVL(X_Period_Days,0))/100)
68 * NVL(X_Invoice_Amount,0)
69 * NVL(P_Late_Days2,0),2);
70 ELSE
71 X_Interest_Calculated := round(NVL(X_Invoice_Amount,0) *
72 (POWER(( 1 + (NVL(X_Interest_Rate_Amount,0)/100)),
73 (NVL(P_Late_Days2,0)/NVL(X_Period_Days,0))) - 1),2);
74 END IF;
75 ELSE
76 X_Interest_Calculated := round(NVL(P_Late_Days2,0) * (NVL(X_Interest_Rate_Amount,0)/NVL(X_Period_Days,0)),2);
77 END IF;
78 END IF;
79
80 IF X_Penalty_Type IS NOT NULL THEN
81 IF X_Penalty_Type = 'R' THEN
82 P_Penalty_Calculated := round(NVL(X_Penalty_Rate_Amount,0)/100 * NVL(X_Invoice_Amount,0),2);
83 ELSE
84 P_Penalty_Calculated := round(NVL(X_Penalty_Rate_Amount,0),2);
85 END IF;
86 X_Interest_Calculated := round(NVL(X_Interest_Calculated,0) + NVL(P_Penalty_Calculated,0),2);
87 END IF;
88 -- Verify Tolerance vs Interest_Calculated
89 -- Bug# 1480683
90 -- Bug 2020279 check tolerance only for AP
91 --
92 --fnd_profile.get('JGZZ_APPL_SHORT_NAME', jg_app_short_name);
93 jg_app_short_name := JG_ZZ_SHARED_PKG.get_application;
94 --
95 IF jg_app_short_name = 'SQLAP' THEN
96 IF C_interest_tolerance_amount > X_Interest_Calculated THEN
97 X_Interest_Calculated := 0;
98 END IF;
99 END IF;
100 END IF;
101 ELSE
102 X_Exit_Code := 1;
103 END IF;
104 END JL_BR_INTEREST;
105
106
107 -- *****************************************************************************
108 -- This procedure updates the interest inovice description according to
109 -- brazilian rates.
110 -- Created by: Dario Betancourt.
111 -- Creation Date : 29-Mar-1999
112 -- ****************************************************************************
113 PROCEDURE JL_BR_CHANGE_INT_DES(P_invoice_related number,
114 P_invoice_original number,
115 P_payment_num_org number) IS
116 l_interest_type VARCHAR2(15);
117 --l_currency_symbol VARCHAR2(4) := 'R$';
118 --commented above for bug 2870854
119 --increased length to 12.
120 l_currency_symbol VARCHAR2(12) := 'R$';
121 l_rate_amount NUMBER;
122 l_due_date DATE;
123 l_check_date DATE;
124 l_invoice_days_late NUMBER;
125 l_nls_interest VARCHAR2(25);
126 l_nls_days VARCHAR2(25);
127 l_nls_percent VARCHAR2(25);
128 l_invoice_description VARCHAR2(240);
129 BEGIN
130 -- ********************************************************
131 -- Get the Translatable Words for filling the description
132 -- ********************************************************
133 SELECT l1.displayed_field,
134 l2.displayed_field,
135 l3.displayed_field
136 INTO l_nls_interest,
137 l_nls_days,
138 l_nls_percent
139 FROM ap_lookup_codes l1,
140 ap_lookup_codes l2,
141 ap_lookup_codes l3
142 WHERE l1.lookup_type = 'NLS TRANSLATION'
143 AND l1.lookup_code = 'INTEREST'
144 AND l2.lookup_type = 'NLS TRANSLATION'
145 AND l2.lookup_code = 'DAYS'
146 AND l3.lookup_type = 'NLS TRANSLATION'
147 AND l3.lookup_code = 'PERCENT';
148
149 -- ***********************************************************
150 -- Get the interest rate from(GA2) and due_date
151 -- from ap_payment_schedules
152 -- ***********************************************************
153 SELECT substr(global_attribute1, 1, 15),
154 nvl(to_number(substr(global_attribute2, 1, 15)), 0),
155 due_date
156 INTO l_interest_type, l_rate_amount, l_due_date
157 FROM ap_payment_schedules
158 WHERE invoice_id = P_invoice_original
159 AND payment_num = P_payment_num_org;
160
161 -- *********************************************************
162 -- Get the payment_date from ap_invoices (interest invoice)
163 -- *********************************************************
164 SELECT invoice_date
165 INTO l_check_date
166 FROM ap_invoices
167 WHERE invoice_id = P_invoice_related;
168
169
170 -- *********************************************************
171 -- Calculate the days late.
172 -- *********************************************************
173 l_invoice_days_late := LEAST(TRUNC(l_check_date), ADD_MONTHS(TRUNC(l_due_date), 12))
174 - TRUNC(l_due_date);
175
176 -- **********************************************************
177 -- Concat the Description with the appropriate interest rate
178 -- BUG Number 856304
179 -- **********************************************************
180 IF l_interest_type = 'R' THEN
181 l_invoice_description := l_nls_interest || ' ' || to_char(l_invoice_days_late)
182 || ' ' || l_nls_days || to_char(l_rate_amount) || l_nls_percent;
183 ELSIF l_interest_type = 'A' THEN
184 -- *********************************************************
185 -- Get the currency symbol
186 -- *********************************************************
187 SELECT fc.symbol
188 INTO l_currency_symbol
189 FROM ap_invoices ai, fnd_currencies_vl fc
190 WHERE ai.invoice_currency_code = fc.currency_code
191 AND ai.invoice_id = P_invoice_original;
192
193 l_invoice_description := l_nls_interest || ' ' || to_char(l_invoice_days_late)
194 || ' ' || l_nls_days || l_currency_symbol || to_char(l_rate_amount);
195 ELSE
196 l_invoice_description := 'Invalid Interest Type: ' || l_interest_type;
197 END IF;
198
199 UPDATE ap_invoices
200 SET description = l_invoice_description
201 WHERE invoice_id = P_invoice_related;
202
203 EXCEPTION
204 WHEN others THEN NULL;
205 END JL_BR_CHANGE_INT_DES;
206
207 PROCEDURE JL_BR_INTEREST(X_Interest_Type IN VARCHAR2,
208 X_Interest_Rate_Amount IN NUMBER,
209 X_Period_Days IN NUMBER,
210 X_Interest_Formula IN VARCHAR2,
211 X_Grace_Days IN NUMBER,
212 X_Penalty_Type IN VARCHAR2,
213 X_Penalty_Rate_Amount IN NUMBER,
214 X_Due_Date IN DATE,
215 X_Payment_Date IN DATE,
216 X_Invoice_Amount IN NUMBER,
217 X_JLBR_Calendar IN VARCHAR2,
218 X_JLBR_Local_Holiday IN VARCHAR2,
219 X_JLBR_Action_Non_Workday IN VARCHAR2,
220 X_Interest_Calculated IN OUT NOCOPY NUMBER,
221 X_Days_Late IN OUT NOCOPY NUMBER,
222 X_Exit_Code OUT NOCOPY NUMBER,
223 X_JLBR_State IN VARCHAR2) IS -- Bug # 2319552
224
225 P_Date_Ok DATE;
226 P_Late_Days1 NUMBER(38);
227 P_Late_Days2 NUMBER(38);
228 P_Penalty_Calculated NUMBER; -- BUG Number 859348
229 P_Status NUMBER(38);
230 P_WorkDay_Ok VARCHAR2(11);
231 P_WorkDay_Date DATE;
232 P_Return_Code NUMBER(38);
233 C_interest_tolerance_amount number;
234 jg_app_short_name VARCHAR2(10);
235
236 BEGIN
237 P_Status := 0;
238 X_Exit_Code := 0;
239 P_Late_Days1 := 0;
240 P_Late_Days2 := 0;
241 P_Penalty_Calculated := 0;
242 X_Interest_Calculated := 0;
243 X_Days_Late := 0;
244
245 P_Date_Ok := X_Due_Date + NVL(X_Grace_Days,0);
246 --
247 -- Validate Tolerance from ap_system_parameters
248 --
249 SELECT nvl(interest_tolerance_amount,0)
250 INTO C_interest_tolerance_amount
251 FROM ap_system_parameters;
252
253
254 jl_br_workday_calendar.jl_br_check_date(to_char(P_Date_Ok, 'DD-MM-YYYY'),
255 X_JLBR_Calendar,
256 X_JLBR_Local_Holiday,
257 X_JLBR_Action_Non_Workday,
258 P_WorkDay_Ok,
259 P_Status,
260 X_JLBR_State); -- Bug # 2319552
261
262 P_WorkDay_Date := to_date(P_WorkDay_Ok, 'DD-MM-YYYY');
263
264 IF P_Status = 0 THEN
265 X_Days_Late := trunc(X_Payment_Date - P_WorkDay_Date);
266 P_Late_Days2 := trunc(X_Payment_Date - X_Due_Date);
267
268 IF X_Days_Late > 0 THEN
269 IF X_Interest_Type IS NOT NULL THEN
270 IF X_Interest_Type = 'R' THEN
271 IF X_Interest_Formula = 'S' THEN
272 X_Interest_Calculated := round(((NVL(X_Interest_Rate_Amount,0)/NVL(X_Period_Days,0))/100)
273 * NVL(X_Invoice_Amount,0)
274 * NVL(P_Late_Days2,0),2);
275 ELSE
276 X_Interest_Calculated := round(NVL(X_Invoice_Amount,0) *
277 (POWER(( 1 + (NVL(X_Interest_Rate_Amount,0)/100)),
278 (NVL(P_Late_Days2,0)/NVL(X_Period_Days,0))) - 1),2);
279 END IF;
280 ELSE
281 X_Interest_Calculated := round(NVL(P_Late_Days2,0) * (NVL(X_Interest_Rate_Amount,0)/NVL(X_Period_Days,0)),2);
282 END IF;
283 END IF;
284
285 IF X_Penalty_Type IS NOT NULL THEN
286 IF X_Penalty_Type = 'R' THEN
287 P_Penalty_Calculated := round(NVL(X_Penalty_Rate_Amount,0)/100 * NVL(X_Invoice_Amount,0),2);
288 ELSE
289 P_Penalty_Calculated := round(NVL(X_Penalty_Rate_Amount,0),2);
290 END IF;
291 X_Interest_Calculated := round(NVL(X_Interest_Calculated,0) + NVL(P_Penalty_Calculated,0),2);
292 END IF;
293 -- Verify Tolerance vs Interest_Calculated
294 -- Bug# 1480683
295 -- Bug 2020279 check tolerance only for AP
296 --
297 --fnd_profile.get('JGZZ_APPL_SHORT_NAME', jg_app_short_name);
298 jg_app_short_name := JG_ZZ_SHARED_PKG.get_application;
299 --
300 IF jg_app_short_name = 'SQLAP' THEN
301 IF C_interest_tolerance_amount > X_Interest_Calculated THEN
302 X_Interest_Calculated := 0;
303 END IF;
304 END IF;
305 END IF;
306 ELSE
307 X_Exit_Code := 1;
308 END IF;
309 END JL_BR_INTEREST;
310
311 END JL_BR_INTEREST_HANDLING;