[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.4.12000000.2 2007/08/03 09:03:33 suchhabr ship $*/
3
4 /*==========================================================================
5 Customize the Interest Calculation
6 *=====================================================================*/
7 PROCEDURE ap_custom_calculate_interest(
8
9 P_invoice_id IN NUMBER,
10 P_sys_auto_calc_int_flag IN VARCHAR2, --bug 4995343
11 P_auto_calculate_interest_flag IN VARCHAR2, --bug 4995343
12 P_check_date IN DATE,
13 P_payment_num IN NUMBER,
14 P_amount_remaining IN NUMBER, --bug 4995343
15 P_discount_taken IN NUMBER, --bug 4995343
16 P_discount_available IN NUMBER, --bug 4995343
17 P_currency_code IN VARCHAR2,
18 P_payment_amount IN NUMBER,
19 P_interest_amount OUT NOCOPY NUMBER,
20 P_invoice_due_date IN DATE ) IS
21
22
23 l_interest_amount number;
24 l_interest_type varchar2(15);
25 l_interest_rate_amount number;
26 l_interest_period number;
27 l_interest_formula varchar2(30);
28 l_interest_grace_days number;
29 l_penalty_type varchar2(15);
30 l_penalty_rate_amount number;
31 l_calendar varchar2(10);
32 l_city varchar2(25);
33 l_payment_location varchar2(80);
34 l_payment_action varchar2(1);
35 l_days_late number;
36 l_exit_code number;
37 l_vendor_site_id number;
38 c INTEGER;
39 rows INTEGER;
40 statement VARCHAR2(2000);
41 dummy VARCHAR2(25);
42 l_country_code VARCHAR2(25);
43 l_org_id NUMBER(15);
44
45
46 BEGIN
47
48 FND_PROFILE.GET('JGZZ_COUNTRY_CODE', l_country_code);
49
50 -- bug 4995343.Added a code hook to call Federal
51 -- package for interest calculation
52
53
54 Select org_id into l_org_id
55 from ap_invoices
56 where invoice_id=p_invoice_id;
57
58 IF (FV_INSTALL.ENABLED (l_org_id)) THEN
59
60 FV_ECON_BENF_DISC.fv_calculate_interest(
61 P_invoice_id ,
62 P_sys_auto_calc_int_flag ,
63 P_auto_calculate_interest_flag ,
64 P_check_date ,
65 P_payment_num ,
66 P_amount_remaining ,
67 P_discount_taken ,
68 P_discount_available ,
69 P_interest_amount );
70
71 ELSIF l_country_code = 'BR' then
72
73 -- Get Global_Attribute1..7, due_date from ap_payment_schedule
74 --Bug6238399 Use fnd_number.canonical_to_number instead of to_number
75 SELECT substr(global_attribute1,1,15) interest_type,
76 fnd_number.canonical_to_number(substr(global_attribute2,1,15)) interest_rate_amount,
77 fnd_number.canonical_to_number(substr(global_attribute3,1,15)) interest_period,
78 substr(global_attribute4,1,30) interest_formula,
79 fnd_number.canonical_to_number(substr(global_attribute5,1,4)) interest_grace_days,
80 substr(global_attribute6,1,15) penalty_type,
81 fnd_number.canonical_to_number(substr(global_attribute7,1,15)) penalty_rate_amount
82 INTO l_interest_type,
83 l_interest_rate_amount,
84 l_interest_period,
85 l_interest_formula,
86 l_interest_grace_days,
87 l_penalty_type,
88 l_penalty_rate_amount
89 FROM ap_payment_schedules
90 WHERE invoice_id = p_invoice_id
91 AND payment_num = p_payment_num;
92
93
94 l_interest_rate_amount := nvl(l_interest_rate_amount,0);
95 l_interest_period := nvl(l_interest_period,0);
96 l_interest_grace_days := nvl(l_interest_grace_days,0);
97 l_penalty_rate_amount := nvl(l_penalty_rate_amount,0);
98
99 -- Get the Calendar
100 FND_PROFILE.GET('JLBR_CALENDAR',l_calendar);
101 --
102 -- Get the payment_location profile, vendor_site_id for getting CITY
103 --
104 -- Get payment_location
105 FND_PROFILE.GET('JLBR_PAYMENT_LOCATION',l_payment_location);
106
107 -- Get vendor_site_id from ap_invoices
108 SELECT vendor_site_id
109 INTO l_vendor_site_id
110 FROM ap_invoices
111 WHERE invoice_id = p_invoice_id;
112
113 IF NVL(l_payment_location,'$') = '1' THEN -- 1 COMPANY
114 -- Get city from ap_system_parameters
115 select substr(global_attribute4,1,25) city
116 into l_city
117 from ap_system_parameters;
118
119 ELSIF NVL(l_payment_location,'$') = '2' THEN -- 2 SUPPLIER
120 -- Get city from po_vendor_sites
121 select city
122 into l_city
123 from po_vendor_sites
124 where vendor_site_id = l_vendor_site_id
125 and nvl(inactive_date,sysdate + 1) > sysdate;
126
127 END IF;
128
129 -- Get the payment_action
130 FND_PROFILE.GET('JLBR_PAYMENT_ACTION',l_payment_action);
131
132 l_interest_amount := 0;
133 l_days_late := 0;
134
135 BEGIN
136
137 -- Call Stored Procedure JL_BR_INTEREST for calculation of INTEREST_AMOUNT
138
139 SELECT 'X' into dummy
140 from user_objects
141 where object_name = 'JL_BR_INTEREST_HANDLING'
142 and object_type = 'PACKAGE BODY';
143
144 c := dbms_sql.open_cursor;
145 statement := 'BEGIN
146 JL_BR_INTEREST_HANDLING.JL_BR_INTEREST(' ||
147 'X_Interest_Type=>:interest_type, ' ||
148 'X_Interest_Rate_Amount=>:interest_rate_amount, ' ||
149 'X_Period_Days=>:interest_period, ' ||
150 'X_Interest_Formula=>:interest_formula, ' ||
151 'X_Grace_Days=>:interest_grace_days, ' ||
152 'X_Penalty_Type=>:penalty_type, ' ||
153 'X_Penalty_Rate_Amount=>:penalty_rate_amount, ' ||
154 'X_Due_Date=>:invoice_due_date, ' ||
155 'X_Payment_Date=>:check_date, ' ||
156 'X_Invoice_Amount=>:payment_amount, ' ||
157 'X_JLBR_Calendar=>:calendar, ' ||
158 'X_JLBR_Local_Holiday=>:city, ' ||
159 'X_JLBR_Action_Non_Workday=>:payment_action, ' ||
160 'X_Interest_Calculated=>:interest_amount, ' ||
161 'X_Days_Late=>:days_late, ' ||
162 'X_Exit_Code=>:exit_code ); END; ';
163
164 dbms_sql.parse(c, statement, dbms_sql.native);
165
166 dbms_sql.bind_variable( c, 'interest_type', l_interest_type );
167 dbms_sql.bind_variable( c, 'interest_rate_amount', l_interest_rate_amount
168 );
169 dbms_sql.bind_variable( c, 'interest_period', l_interest_period );
170 dbms_sql.bind_variable( c, 'interest_formula', l_interest_formula );
171 dbms_sql.bind_variable( c, 'interest_grace_days', l_interest_grace_days );
172 dbms_sql.bind_variable( c, 'penalty_type', l_penalty_type );
173 dbms_sql.bind_variable( c, 'penalty_rate_amount', l_penalty_rate_amount );
174 dbms_sql.bind_variable( c, 'invoice_due_date', p_invoice_due_date );
175 dbms_sql.bind_variable( c, 'check_date', p_check_date );
176 dbms_sql.bind_variable( c, 'payment_amount', p_payment_amount );
177 dbms_sql.bind_variable( c, 'calendar', l_calendar );
178 dbms_sql.bind_variable( c, 'city', l_city );
179 dbms_sql.bind_variable( c, 'payment_action', l_payment_action );
180 dbms_sql.bind_variable ( c, 'interest_amount', l_interest_amount);
181 dbms_sql.bind_variable ( c, 'days_late', l_days_late);
182 dbms_sql.bind_variable ( c, 'exit_code', l_exit_code);
183
184 rows := dbms_sql.execute(c);
185 dbms_sql.variable_value ( c, 'interest_amount', p_interest_amount );
186 dbms_sql.variable_value ( c, 'days_late', l_days_late );
187 dbms_sql.variable_value ( c, 'exit_code', l_exit_code );
188
189 dbms_sql.close_cursor(c);
190
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN P_INTEREST_AMOUNT := NULL;
193 WHEN OTHERS THEN P_INTEREST_AMOUNT := NULL;
194 END;
195
196 ELSE
197
198 P_INTEREST_AMOUNT := NULL;
199
200 END IF;
201 END ap_custom_calculate_interest;
202
203
204 END AP_CUSTOM_INT_INV_PKG;