[Home] [Help]
PACKAGE BODY: APPS.AP_CUSTOM_INT_INV_PKG
Source
1 PACKAGE BODY AP_CUSTOM_INT_INV_PKG AS
2 /*$Header: apcstiib.pls 120.5.12010000.4 2009/07/09 11:22:39 njakkula ship $*/
3
4 /*==========================================================================
5 Customize the Interest Calculation
6 *=====================================================================*/
7 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
10 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
12 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
13 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
14 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'AP.PLSQL.AP_CUSTOM_INT_INV_PKG.';
15
16 PROCEDURE ap_custom_calculate_interest(
17
18 P_invoice_id IN NUMBER,
19 P_sys_auto_calc_int_flag IN VARCHAR2, --bug 4995343
20 P_auto_calculate_interest_flag IN VARCHAR2, --bug 4995343
21 P_check_date IN DATE,
22 P_payment_num IN NUMBER,
23 P_amount_remaining IN NUMBER, --bug 4995343
24 P_discount_taken IN NUMBER, --bug 4995343
25 P_discount_available IN NUMBER, --bug 4995343
26 P_currency_code IN VARCHAR2,
27 P_payment_amount IN NUMBER,
28 P_interest_amount OUT NOCOPY NUMBER,
29 P_invoice_due_date IN DATE ) IS
30
31
32 l_interest_amount number;
33 l_interest_type varchar2(15);
34 l_interest_rate_amount number;
35 l_interest_period number;
36 l_interest_formula varchar2(30);
37 l_interest_grace_days number;
38 l_penalty_type varchar2(15);
39 l_penalty_rate_amount number;
40 l_calendar varchar2(10);
41 l_city po_vendor_sites.city%type; --6708281
42 l_payment_location varchar2(80);
43 l_payment_action varchar2(1);
44 l_days_late number;
45 l_exit_code number;
46 l_vendor_site_id number;
47 c INTEGER;
48 rows INTEGER;
49 statement VARCHAR2(2000);
50 dummy VARCHAR2(25);
51 l_country_code VARCHAR2(25);
52 l_org_id NUMBER(15);
53 DBG_Loc VARCHAR2(30) := 'ap_custom_calculate_interest';
54 debug_info VARCHAR2(2000);
55
56 BEGIN
57
58 debug_info := 'Start of ap_custom_calculate_interest';
59 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
60 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
61 END IF;
62
63 FND_PROFILE.GET('JGZZ_COUNTRY_CODE', l_country_code);
64
65 -- bug 4995343.Added a code hook to call Federal
66 -- package for interest calculation
67
68
69 Select org_id into l_org_id
70 from ap_invoices
71 where invoice_id=p_invoice_id;
72
73 debug_info := 'apcci Invoice_id: '||to_char(p_invoice_id) ||
74 ' Org_id: '|| l_org_id;
75 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
76 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
77 END IF;
78
79 IF (FV_INSTALL.ENABLED (l_org_id)) THEN
80
81 FV_ECON_BENF_DISC.fv_calculate_interest(
82 P_invoice_id ,
83 P_sys_auto_calc_int_flag ,
84 P_auto_calculate_interest_flag ,
85 P_check_date ,
86 P_payment_num ,
87 P_amount_remaining ,
88 P_discount_taken ,
89 P_discount_available ,
90 P_interest_amount );
91
92 debug_info := 'apcci FV Int Amt '||to_char(P_interest_amount);
93 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
94 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
95 END IF;
96
97 ELSIF l_country_code = 'BR' then
98
99 -- Get Global_Attribute1..7, due_date from ap_payment_schedule
100 --Bug6238399 Use fnd_number.canonical_to_number instead of to_number
101 SELECT substr(global_attribute1,1,15) interest_type,
102 fnd_number.canonical_to_number(substr(global_attribute2,1,15)) interest_rate_amount,
106 substr(global_attribute6,1,15) penalty_type,
103 fnd_number.canonical_to_number(substr(global_attribute3,1,15)) interest_period,
104 substr(global_attribute4,1,30) interest_formula,
105 fnd_number.canonical_to_number(substr(global_attribute5,1,4)) interest_grace_days,
107 fnd_number.canonical_to_number(substr(global_attribute7,1,15)) penalty_rate_amount
108 INTO l_interest_type,
109 l_interest_rate_amount,
110 l_interest_period,
111 l_interest_formula,
112 l_interest_grace_days,
113 l_penalty_type,
114 l_penalty_rate_amount
115 FROM ap_payment_schedules
116 WHERE invoice_id = p_invoice_id
117 AND payment_num = p_payment_num;
118
119 debug_info := 'apcci Int Type '||l_interest_type;
120 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
121 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
122 END IF;
123
124 debug_info := 'apcci Int Rate Amt '||to_char(l_interest_rate_amount);
125 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
126 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
127 END IF;
128
129 l_interest_rate_amount := nvl(l_interest_rate_amount,0);
130 l_interest_period := nvl(l_interest_period,0);
131 l_interest_grace_days := nvl(l_interest_grace_days,0);
132 l_penalty_rate_amount := nvl(l_penalty_rate_amount,0);
133
134 -- Get the Calendar
135 FND_PROFILE.GET('JLBR_CALENDAR',l_calendar);
136 --
137 -- Get the payment_location profile, vendor_site_id for getting CITY
138 --
139 -- Get payment_location
140 FND_PROFILE.GET('JLBR_PAYMENT_LOCATION',l_payment_location);
141
142 -- Get vendor_site_id from ap_invoices
143 SELECT vendor_site_id
144 INTO l_vendor_site_id
145 FROM ap_invoices
146 WHERE invoice_id = p_invoice_id;
147
148 IF NVL(l_payment_location,'$') = '1' THEN -- 1 COMPANY
149 -- Get city from ap_system_parameters
150 select substr(global_attribute4,1,25) city
151 into l_city
152 from ap_system_parameters;
153
154 ELSIF NVL(l_payment_location,'$') = '2' THEN -- 2 SUPPLIER
155 -- Get city from po_vendor_sites
156 select city
157 into l_city
158 from po_vendor_sites
159 where vendor_site_id = l_vendor_site_id
160 and nvl(inactive_date,sysdate + 1) > sysdate;
161
162 END IF;
163
164 -- Get the payment_action
165 --FND_PROFILE.GET('JLBR_PAYMENT_ACTION',l_payment_action);
166 l_payment_action := JL_ZZ_SYS_OPTIONS_PKG.get_payment_action(l_org_id); --8493945
167
168 l_interest_amount := 0;
169 l_days_late := 0;
170
171 BEGIN
172
173 -- Call Stored Procedure JL_BR_INTEREST for calculation of INTEREST_AMOUNT
174
175 SELECT 'X' into dummy
176 from user_objects
177 where object_name = 'JL_BR_INTEREST_HANDLING'
178 and object_type = 'PACKAGE BODY';
179
180 debug_info := 'apcci calling JL_BR_INTEREST_HANDLING ';
181 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
182 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
183 END IF;
184
185 c := dbms_sql.open_cursor; --bug8652516: Added X_Org_id
186 statement := 'BEGIN
187 JL_BR_INTEREST_HANDLING.JL_BR_INTEREST(' ||
188 'X_Interest_Type=>:interest_type, ' ||
189 'X_Interest_Rate_Amount=>:interest_rate_amount, ' ||
190 'X_Period_Days=>:interest_period, ' ||
191 'X_Interest_Formula=>:interest_formula, ' ||
192 'X_Grace_Days=>:interest_grace_days, ' ||
193 'X_Penalty_Type=>:penalty_type, ' ||
194 'X_Penalty_Rate_Amount=>:penalty_rate_amount, ' ||
195 'X_Due_Date=>:invoice_due_date, ' ||
196 'X_Payment_Date=>:check_date, ' ||
197 'X_Invoice_Amount=>:payment_amount, ' ||
198 'X_JLBR_Calendar=>:calendar, ' ||
199 'X_JLBR_Local_Holiday=>:city, ' ||
200 'X_JLBR_Action_Non_Workday=>:payment_action, ' ||
201 'X_Interest_Calculated=>:interest_amount, ' ||
202 'X_Days_Late=>:days_late, ' ||
203 'X_Exit_Code=>:exit_code, ' ||
204 'X_Org_id=>:org_id ); END; ';
205
206 dbms_sql.parse(c, statement, dbms_sql.native);
207
208 dbms_sql.bind_variable( c, 'interest_type', l_interest_type );
209 dbms_sql.bind_variable( c, 'interest_rate_amount', l_interest_rate_amount
210 );
211 dbms_sql.bind_variable( c, 'interest_period', l_interest_period );
212 dbms_sql.bind_variable( c, 'interest_formula', l_interest_formula );
213 dbms_sql.bind_variable( c, 'interest_grace_days', l_interest_grace_days );
214 dbms_sql.bind_variable( c, 'penalty_type', l_penalty_type );
215 dbms_sql.bind_variable( c, 'penalty_rate_amount', l_penalty_rate_amount );
216 dbms_sql.bind_variable( c, 'invoice_due_date', p_invoice_due_date );
217 dbms_sql.bind_variable( c, 'check_date', p_check_date );
218 dbms_sql.bind_variable( c, 'payment_amount', p_payment_amount );
219 dbms_sql.bind_variable( c, 'calendar', l_calendar );
220 dbms_sql.bind_variable( c, 'city', l_city );
221 dbms_sql.bind_variable( c, 'payment_action', l_payment_action );
222 dbms_sql.bind_variable ( c, 'interest_amount', l_interest_amount);
223 dbms_sql.bind_variable ( c, 'days_late', l_days_late);
224 dbms_sql.bind_variable ( c, 'exit_code', l_exit_code);
225 dbms_sql.bind_variable( c, 'org_id', l_org_id );
226
227 rows := dbms_sql.execute(c);
228 dbms_sql.variable_value ( c, 'interest_amount', p_interest_amount );
232 dbms_sql.close_cursor(c);
229 dbms_sql.variable_value ( c, 'days_late', l_days_late );
230 dbms_sql.variable_value ( c, 'exit_code', l_exit_code );
231
233
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN P_INTEREST_AMOUNT := NULL;
236 debug_info := 'sqlcode: '|| sqlcode||' sqlerrm: '||sqlerrm;
237 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
238 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
239 END IF;
240 WHEN OTHERS THEN P_INTEREST_AMOUNT := NULL;
241 debug_info := 'sqlcode: '|| sqlcode||' sqlerrm: '||sqlerrm;
242 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
243 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
244 END IF;
245 END;
246
247 ELSE
248
249 P_INTEREST_AMOUNT := NULL;
250
251 END IF;
252
253 debug_info := 'apcci Int AMT 2 '||to_char(p_interest_amount);
254 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
255 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
256 END IF;
257
258 END ap_custom_calculate_interest;
259
260
261 END AP_CUSTOM_INT_INV_PKG;