[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_MISC_PKG
Source
1 package body JAI_AP_MISC_PKG AS
2 /* $Header: jai_ap_misc_pkg.plb 120.2.12000000.1 2007/10/24 18:20:13 rallamse noship $ */
3
4 procedure jai_calc_ipv_erv (P_errmsg OUT NOCOPY VARCHAR2,
5 P_retcode OUT NOCOPY Number,
6 P_invoice_id in number,
7 P_po_dist_id in number,
8 P_invoice_distribution_id IN NUMBER,
9 P_amount IN NUMBER,
10 P_base_amount IN NUMBER,
11 P_rcv_transaction_id IN NUMBER,
12 P_invoice_price_variance IN NUMBER,
13 P_base_invoice_price_variance IN NUMBER,
14 P_price_var_ccid IN NUMBER,
15 P_Exchange_rate_variance IN NUMBER,
16 P_rate_var_ccid IN NUMBER
17 )
18 as
19
20 /* Cursors */
21
22 Cursor check_rec_tax ( ln_tax_id number) is
23 select tax_name,
24 tax_account_id,
25 mod_cr_percentage,
26 adhoc_flag,
27 nvl(tax_rate, 0) tax_rate,
28 tax_type
29 from JAI_CMN_TAXES_ALL
30 where tax_id = ln_tax_id;
31
32
33 Cursor get_misc_lines (ln_dist_line_number in number,
34 ln_invoice_id in number ) is
35 select *
36 from ap_invoice_distributions_all
37 where invoice_id = ln_invoice_id
38 and distribution_line_number = ln_dist_line_number;
39
40
41 /* precision */
42 Cursor get_prec (lv_currency_code varchar2) is
43 select precision
44 from fnd_currencies
45 where currency_code = lv_currency_code;
46
47
48 /* Local Variables */
49 ln_tax_ipv number;
50 ln_tax_bipv number;
51 ln_price_var_ccid number;
52
53 ln_tax_erv number;
54
55 lv_inv_curr_code varchar2(15);
56 lv_base_curr_code varchar2(15);
57
58 ln_inv_pre number;
59 ln_base_pre number;
60
61 r_get_misc_lines get_misc_lines%ROWTYPE;
62
63
64
65 Begin
66
67
68 fnd_file.put_line(FND_FILE.LOG, ' inside procedure ');
69
70 lv_base_curr_code := 'INR';
71
72 Begin
73 Select invoice_currency_code
74 into lv_inv_curr_code
75 from ap_invoices_all
76 where invoice_id = p_invoice_id;
77
78 Exception
79 When others then
80 null;
81 End;
82
83 If lv_inv_curr_code = 'INR' Then
84 open get_prec(lv_base_curr_code);
85 Fetch get_prec into ln_base_pre;
86 Close get_prec;
87
88 ln_inv_pre := ln_base_pre;
89
90 Else
91 open get_prec(lv_inv_curr_code);
92 Fetch get_prec into ln_inv_pre;
93 Close get_prec;
94
95 open get_prec(lv_base_curr_code);
96 Fetch get_prec into ln_base_pre;
97 Close get_prec;
98
99 End if;
100
101 fnd_file.put_line(FND_FILE.LOG, ' invoice id '|| p_invoice_id);
102 fnd_file.put_line(FND_FILE.LOG, ' po dist id '|| p_po_dist_id);
103
104 for Misc_loop in ( select *
105 from JAI_AP_MATCH_INV_TAXES
106 where invoice_id = p_invoice_id
107 and parent_invoice_distribution_id = p_invoice_distribution_id
108 )
109 loop
110
111
112 fnd_file.put_line(FND_FILE.LOG,' inside loop -- 2 ' );
113
114 /* For later use if necessary to check the tax type. now education cess will not be
115 created at invoice level if it is available in PO/Receipt level
116
117 for tax_loop in check_rec_tax (select tax_id
118 from JAI_AP_MATCH_INV_TAXES
119 where invoice_id = misc_loop.invoice_id
120 and distribution_line_number = misc_loop.distribution_line_number)
121 loop
122
123 Service and Education cess are recoverable taxes and
124 IPV should not be calculated on these lines
125 If not (tax_loop.tax_type like '%EDUCATION_CESS') Then
126
127 */
128
129 Open get_misc_lines(misc_loop.distribution_line_number, misc_loop.invoice_id);
130 Fetch get_misc_lines into r_get_misc_lines;
131 Close get_misc_lines;
132
133 If nvl(p_amount ,0) <> 0 Then
134
135 fnd_file.put_line(FND_FILE.LOG,' Inside item amount not zero ' || p_amount);
136
137 If nvl(r_get_misc_lines.amount , 0 ) <> 0 Then
138
139 fnd_file.put_line(FND_FILE.LOG,' Inside Tax amount not zero ' || r_get_misc_lines.amount);
140
141 IF nvl(p_invoice_price_variance,0 ) <> 0 Then
142
143 ln_tax_ipv := r_get_misc_lines.amount * (nvl(p_invoice_price_variance,0) /p_amount);
144
145 End if;
146
147 IF nvl(p_exchange_rate_variance,0 ) <> 0 Then
148
149 ln_tax_erv := r_get_misc_lines.amount * (nvl(p_exchange_rate_variance,0)/p_amount);
150
151 End if;
152
153 fnd_file.put_line(FND_FILE.LOG,' IPV '|| ln_tax_ipv);
154 fnd_file.put_line(FND_FILE.LOG,' ERV '|| ln_tax_erv);
155
156 /* IPV */
157
158 If nvl(ln_tax_ipv,0) <> 0 then
159
160 fnd_file.put_line(FND_FILE.LOG,' Inside IPV not zero '|| ln_tax_ipv);
161
162 ln_tax_bipv := ln_tax_ipv * nvl(r_get_misc_lines.exchange_rate,1);
163
164 update ap_invoice_distributions_all
165 set invoice_price_variance = round(ln_tax_ipv,ln_inv_pre),
166 base_invoice_price_variance = round(ln_tax_bipv, ln_base_pre),
167 price_var_code_combination_id = P_price_var_ccid
168 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
169 End if;
170
171 /* ERV */
172
173
174 If nvl(ln_tax_erv,0) <> 0 then
175
176 fnd_file.put_line(FND_FILE.LOG,' Inside ERV not zero '|| ln_tax_erv);
177 fnd_file.put_line(FND_FILE.LOG,' rate var CCID '|| P_rate_var_ccid);
178
179 update ap_invoice_distributions_all
180 set exchange_rate_variance = round(ln_tax_erv,ln_inv_pre),
181 rate_var_code_combination_id = P_rate_var_ccid
182 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
183 End if;
184
185
186 Else
187
188 /* update ipv and bipv to 0. no need to update Var CCID */
189
190 update ap_invoice_distributions_all
191 set invoice_price_variance = 0,
192 base_invoice_price_variance = 0,
193 exchange_rate_variance = 0
194 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
195 End if;
196 /* r_get_misc_lines.amount <> 0 */
197
198 End if; /* p_amount <> 0 */
199
200 -- end loop; -- End tax_loop
201 end loop; -- End misc_loop
202
203 p_errmsg :=NULL;
204 p_retcode := NULL;
205
206
207 Exception
208 When others then
209 P_errmsg := SQLERRM;
210 P_retcode := 2;
211 Fnd_File.put_line(Fnd_File.LOG, 'EXCEPTION END PROCEDURE - JAI_CALC_IPV ');
212 Fnd_File.put_line(Fnd_File.LOG, 'Error : ' || P_errmsg);
213 End jai_calc_ipv_erv;
214
215 -- added, Harshita for Bug 5553150
216
217 FUNCTION fetch_tax_target_amt
218 ( p_invoice_id IN NUMBER ,
219 p_line_location_id IN NUMBER ,
220 p_transaction_id IN NUMBER ,
221 p_parent_dist_id IN NUMBER,
222 p_tax_id IN NUMBER
223 )
224 RETURN NUMBER
225 IS
226
227 TYPE TAX_CUR IS RECORD
228 (
229 P_1 JAI_PO_TAXES.precedence_1%type,
230 P_2 JAI_PO_TAXES.precedence_2%type,
231 P_3 JAI_PO_TAXES.precedence_3%type,
232 P_4 JAI_PO_TAXES.precedence_4%type,
233 P_5 JAI_PO_TAXES.precedence_5%type,
234 P_6 JAI_PO_TAXES.precedence_6%type,
235 P_7 JAI_PO_TAXES.precedence_7%type,
236 P_8 JAI_PO_TAXES.precedence_8%type,
237 P_9 JAI_PO_TAXES.precedence_9%type,
238 P_10 JAI_PO_TAXES.precedence_10%type
239 ) ;
240
241 TYPE tax_cur_type IS REF CURSOR RETURN TAX_CUR;
242 c_tax_cur TAX_CUR_TYPE;
243 rec c_tax_cur%ROWTYPE;
244 ln_base_amt number ;
245
246
247 FUNCTION fetch_line_amt(p_precedence_value IN NUMBER)
248 RETURN NUMBER
249 IS
250 cursor c_line_amt
251 is
252 select NVL(SUM(tax_amount),-1) -- 5763527, Added SUM as partially recoverable taxes will have two lines
253 from JAI_AP_MATCH_INV_TAXES
254 where invoice_id = p_invoice_id
255 and line_no = p_precedence_value ;
256
257 cursor c_base_inv_amt
258 is
259 select amount
260 from ap_invoice_distributions_all
261 where invoice_distribution_id = p_parent_dist_id
262 and invoice_id = p_invoice_id ;
263
264 ln_line_amt number ;
265
266 BEGIN
267 if p_precedence_value = -1 then
268 return 0 ;
269 elsif p_precedence_value = 0 then
270 open c_base_inv_amt ;
271 fetch c_base_inv_amt into ln_line_amt ;
272 close c_base_inv_amt ;
273 return nvl(ln_line_amt,0) ;
274 else
275 open c_line_amt ;
276 fetch c_line_amt into ln_line_amt ;
277 close c_line_amt ;
278 return nvl(ln_line_amt,0) ;
279 end if ;
280
281 END fetch_line_amt;
282
283 BEGIN
284
285 IF p_line_location_id is not null then
286 OPEN c_tax_cur FOR
287 select Precedence_1 P_1,
288 Precedence_2 P_2,
289 Precedence_3 P_3,
290 Precedence_4 P_4,
291 Precedence_5 P_5,
292 Precedence_6 P_6,
293 Precedence_7 P_7,
294 Precedence_8 P_8,
295 Precedence_9 P_9,
296 Precedence_10 P_10
297 from JAI_PO_TAXES
298 where line_location_id = p_line_location_id
299 and tax_id = p_tax_id ;
300 ELSE
301 OPEN c_tax_cur FOR
302 select Precedence_1 P_1,
303 Precedence_2 P_2,
304 Precedence_3 P_3,
305 Precedence_4 P_4,
306 Precedence_5 P_5,
307 Precedence_6 P_6,
308 Precedence_7 P_7,
309 Precedence_8 P_8,
310 Precedence_9 P_9,
311 Precedence_10 P_10
312 from JAI_RCV_LINE_TAXES
313 where shipment_line_id IN
314 ( select shipment_line_id
315 from JAI_RCV_TRANSACTIONS
316 where transaction_id = p_transaction_id
317 )
318 and tax_id = p_tax_id ;
319
320 END IF ;
321
322 FETCH c_tax_cur INTO rec;
323 ln_base_amt := fetch_line_amt(nvl(rec.P_1,-1)) + fetch_line_amt(nvl(rec.P_2,-1))
324 + fetch_line_amt(nvl(rec.P_3,-1)) + fetch_line_amt(nvl(rec.P_4,-1))
325 + fetch_line_amt(nvl(rec.P_5,-1)) + fetch_line_amt(nvl(rec.P_6,-1))
326 + fetch_line_amt(nvl(rec.P_7,-1)) + fetch_line_amt(nvl(rec.P_8,-1))
327 + fetch_line_amt(nvl(rec.P_9,-1)) + fetch_line_amt(nvl(rec.P_10,-1));
328 CLOSE c_tax_cur ;
329 return ln_base_amt ;
330
331
332 END fetch_tax_target_amt ;
333 -- ended, Harshita for Bug 5553150
334
335 End JAI_AP_MISC_PKG;