[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_TOLERANCE_PKG
Source
1 PACKAGE BODY jai_ap_tolerance_pkg AS
2 /* $Header: jai_ap_tolerance.plb 120.3.12020000.2 2012/07/16 07:09:41 nkodakan ship $ */
3
4
5 PROCEDURE inv_holds_check
6 (
7 p_invoice_id IN NUMBER,
8 p_org_id IN NUMBER,
9 p_set_of_books_id IN NUMBER,
10 p_invoice_amount IN NUMBER,
11 p_invoice_currency_code IN VARCHAR2,
12 p_return_code OUT NOCOPY VARCHAR2,
13 p_return_message OUT NOCOPY VARCHAR2
14 ) IS
15
16 CURSOR set_up_values IS
17 SELECT *
18 FROM JAI_AP_TOL_SETUPS_ALL
19 where org_id = p_org_id;
20
21 CURSOR check_entry(inv_id NUMBER, cp_vat_code ap_invoice_distributions_all.vat_code%type) IS
22 SELECT 1
23 FROM ap_invoice_distributions_all
24 WHERE invoice_id = inv_id
25 AND vat_code = cp_vat_code
26 AND rownum = 1;
27
28 CURSOR line_amounts(inv_id NUMBER) IS
29 SELECT SUM(ail.amount) line_amount
30 FROM ap_invoice_lines_all ail
31 WHERE ail.invoice_id = inv_id
32 AND EXISTS (SELECT 1
33 FROM ap_invoice_distributions_all apid
34 WHERE apid.invoice_line_number = ail.line_number
35 AND apid.invoice_id = inv_id
36 );
37
38 CURSOR least_values(h_amt NUMBER) IS
39 SELECT least(h_amt*NVL(tolerance_pos_percent,0)/100, NVL(tolerance_pos_amt,0)) max_val,
40 least(h_amt*NVL(tolerance_neg_percent,0)/100, NVL(tolerance_neg_amt,0)) min_val
41 FROM JAI_AP_TOL_SETUPS_ALL;
42
43 CURSOR from_inv_dist(inv_id NUMBER,
44 cp_description ap_invoice_distributions_all.description%type,
45 cp_invoice_line_number ap_invoice_lines_all.line_number%TYPE,
46 cp_line_type ap_invoice_lines_all.line_type_lookup_code%TYPE
47 )
48 IS
49 SELECT distribution_line_number, set_of_books_id
50 FROM ap_invoice_distributions_all
51 WHERE invoice_id = inv_id
52 AND invoice_line_number = cp_invoice_line_number
53 AND line_type_lookup_code = cp_line_type
54 AND description = cp_description
55 AND rownum = 1;
56
57 -- bug 7114863. Added by Lakshmi Gopalsami
58
59 CURSOR from_inv_line(inv_id NUMBER,
60 cp_description ap_invoice_lines_all.description%TYPE,
61 cp_line_type ap_invoice_lines_all.line_type_lookup_code%TYPE
62 )
63 IS
64 SELECT line_number, set_of_books_id
65 FROM ap_invoice_lines_all
66 WHERE invoice_id = inv_id
67 AND line_type_lookup_code =cp_line_type
68 AND description = cp_description
69 AND rownum = 1;
70
71 -- End for bug 7114863
72
73
74 CURSOR for_functional_currency(sob_id NUMBER) IS
75 SELECT currency_code
76 FROM gl_sets_of_books
77 WHERE set_of_books_id = sob_id;
78
79
80 upper_value NUMBER := 0;
81 lower_value NUMBER := 0;
82 diff_amount NUMBER := 0;
83 insertion_amount NUMBER := 0;
84 SOB_ID NUMBER;
85 v_check_entry NUMBER;
86 least_values_rec least_values%ROWTYPE;
87 from_inv_dist_rec from_inv_dist%ROWTYPE;
88 set_up_values_rec set_up_values%ROWTYPE;
89 line_amounts_rec line_amounts%ROWTYPE;
90 -- for_dist_insertion_rec for_dist_insertion%ROWTYPE; For Bug# 4445989
91 for_functional_currency_rec for_functional_currency%ROWTYPE;
92
93 lv_misc ap_invoice_distributions_all.line_type_lookup_code%type;
94 lv_vat_code ap_invoice_distributions_all.vat_code%type;
95 lv_description ap_invoice_distributions_all.description%type;
96 lv_not_required AP_INVOICE_LINES_ALL.WFAPPROVAL_STATUS%TYPE;
97 lv_not_matched AP_INVOICE_LINES_ALL.MATCH_TYPE%TYPE;
98 ln_max_lnno NUMBER;
99 lv_cash_posted_flag AP_INVOICE_DISTRIBUTIONS_ALL.CASH_POSTED_FLAG%TYPE;
100 ln_distribution_line_num AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER%TYPE;
101 ln_user_id AP_INVOICE_LINES_ALL.CREATED_BY%TYPE;
102 ln_login_id AP_INVOICE_LINES_ALL.LAST_UPDATE_LOGIN%TYPE;
103 ln_invc_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
104
105 --> Cursor to fetch the maximum line_number for current invoice
106 CURSOR cur_get_max_line_number
107 IS
108 SELECT max (line_number)
109 FROM ap_invoice_lines_all
110 WHERE invoice_id = p_invoice_id;
111
112 -- Cursor to fetch the details of
113 -- maximum line from ap_invoice_lines_all for current invoice
114 CURSOR cur_get_max_ap_inv_line (cpn_max_line_num AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE)
115 IS
116 SELECT accounting_date
117 ,period_name
118 ,deferred_acctg_flag
119 ,def_acctg_start_date
120 ,def_acctg_end_date
121 ,def_acctg_number_of_periods
122 ,def_acctg_period_type
123 ,set_of_books_id
124 FROM ap_invoice_lines_all
125 WHERE invoice_id = p_invoice_id
126 AND line_number = cpn_max_line_num;
127
128 rec_max_ap_lines_all CUR_GET_MAX_AP_INV_LINE%ROWTYPE;
129
130
131 -- Bug#7114863. Added by Lakshmi Gopalsami
132 CURSOR c_invoice_distribution(cp_invoice_id NUMBER)
133 IS
134 SELECT a.accrual_posted_flag,
135 a.assets_addition_flag,
136 a.assets_tracking_flag
137 FROM ap_invoice_distributions_all a,
138 ap_invoice_lines_all b
139 WHERE a.invoice_id = b.invoice_id
140 and a.invoice_line_number = b.line_number
141 and a.invoice_id = cp_invoice_id
142 AND b.line_type_lookup_code = 'ITEM';
143
144 r_invoice_distribution c_invoice_distribution%ROWTYPE;
145
146 from_inv_line_rec from_inv_line%ROWTYPE;
147
148 BEGIN
149
150 /* India Localization funtionality is not required */
151 if jai_cmn_utils_pkg.check_jai_exists
152 (p_calling_object => 'apaprvlb.pls',
153 p_org_id => p_org_id,
154 p_set_of_books_id => p_set_of_books_id) = FALSE
155 then
156 P_return_code := jai_constants.successful;
157 P_return_message := 'No need to create adjustment line as IL functionality is used';
158 return;
159 end if;
160
161 --p_return_code := jai_constants.successful ;
162
163 lv_vat_code := 'ROUNDING';
164 lv_description := 'Commercial Rounding Off Distribution';
165 ln_user_id := fnd_global.user_id;
166 ln_login_id := fnd_global.login_id;
167 lv_misc := 'MISCELLANEOUS'; -- Bug 7114863. Added by Laskhmi Gopalsami
168
169 OPEN set_up_values;
170 FETCH set_up_values INTO set_up_values_rec;
171
172 IF set_up_values%NOTFOUND THEN
173 CLOSE set_up_values;
174 RETURN;
175 END IF;
176 CLOSE set_up_values;
177
178 OPEN line_amounts(p_invoice_id);
179 FETCH line_amounts INTO line_amounts_rec;
180 CLOSE line_amounts;
181
182 OPEN cur_get_max_line_number;
183 FETCH cur_get_max_line_number INTO ln_max_lnno;
184 CLOSE cur_get_max_line_number;
185
186 OPEN cur_get_max_ap_inv_line (cpn_max_line_num => ln_max_lnno );
187 FETCH cur_get_max_ap_inv_line INTO rec_max_ap_lines_all;
188 CLOSE cur_get_max_ap_inv_line;
189
190 OPEN for_functional_currency(rec_max_ap_lines_all.set_of_books_id);
191 FETCH for_functional_currency INTO for_functional_currency_rec;
192 CLOSE for_functional_currency;
193
194 v_check_entry := 0 ;
195
196 OPEN check_entry(p_invoice_id, lv_vat_code);
197 FETCH check_entry INTO v_check_entry;
198 CLOSE check_entry;
199
200
201 --Check the header amount and the line total
202 -- 1
203 IF (( (p_invoice_amount = line_amounts_rec.line_amount) AND
204 (v_check_entry = 0)
205 )
206 OR
207 (for_functional_currency_rec.currency_code <> p_invoice_currency_code) OR
208 (NVL(set_up_values_rec.tolerance_flag,'X') = 'X')
209 ) THEN --0
210 -- there is no diff in header and distribution amount / currency is not INR / tolerance not setup
211 P_return_code := jai_constants.successful;
212 P_return_message := ' No difference found and so no adjustment line created';
213 RETURN;
214 END IF; --1
215
216 --2 Commented the IF condition for bug # 13531732
217 /*
218 IF ((p_invoice_amount = line_amounts_rec.line_amount)
219 AND v_check_entry <> 0) THEN
220
221 -- rounding entry has gone but the vat code not updated.
222 ln_invc_line_num := null;
223
224 UPDATE ap_invoice_distributions_all
225 SET amount = 0
226 WHERE invoice_id = p_invoice_id
227 AND vat_code = lv_vat_code
228 RETURNING invoice_line_number INTO ln_invc_line_num;
229
230 UPDATE ap_invoice_lines_all
231 SET amount = 0
232 WHERE invoice_id = p_invoice_id
233 AND line_number = ln_invc_line_num;
234
235 P_return_code := jai_constants.successful;
236 P_return_message := ' Updated existing rounding line';
237
238 RETURN;
239
240 END IF; */
241 --2
242
243
244 diff_amount := line_amounts_rec.line_amount - p_invoice_amount ;
245
246 --IF IT DOES NOT MATCH THEN CHECK THE TOLERANCE LEVEL
247 -- 3
248
249 IF (NVL(set_up_values_rec.tolerance_flag,'Z') = 'P') THEN
250 --CONSIDER ONLY THE PERCENTAGE TOLERANCE
251
252 upper_value := NVL(set_up_values_rec.tolerance_pos_percent,0) * p_invoice_amount/100;
253 lower_value := NVL(set_up_values_rec.tolerance_neg_percent,0) * p_invoice_amount/100;
254
255
256 ELSIF (NVL(set_up_values_rec.tolerance_flag,'Z') = 'A') THEN
257
258 --CONSIDER ONLY THE AMOUNT TOLERANCE
259
260 upper_value := NVL(set_up_values_rec.tolerance_pos_amt,0);
261 lower_value := NVL(set_up_values_rec.tolerance_neg_amt,0);
262
263
264 ELSE --1
265 --CONSIDER LEAST OF PERCENTAGE AND AMOUNT TOLERANCES
266
267 OPEN least_values(p_invoice_amount);
268 FETCH least_values INTO least_values_rec;
269 CLOSE least_values;
270
271 upper_value := least_values_rec.max_val;
272 lower_value := least_values_rec.min_val;
273
274 END IF;
275 -- 3
276
277 -- 4
278 IF ( (diff_amount > 0 AND diff_amount <= upper_value)
279 OR
280 (diff_amount < 0 AND ABS(diff_amount) <= lower_value)
281 ) THEN
282
283 --INSERT POSITIVE OR NEGATIVE DISTRIBUTION EQUAL TO DIFF_AMOUNT
284 insertion_amount := (-1)*diff_amount;
285 END IF;
286 -- 4
287
288 --5
289 IF insertion_amount <> 0 THEN
290
291 lv_not_required := 'NOT REQUIRED';
292 lv_not_matched := 'NOT_MATCHED';
293 lv_cash_posted_flag := 'N';
294
295 -- Check whether the rounding line already exists in table
296
297 OPEN from_inv_line(p_invoice_id, lv_description,lv_misc);
298 FETCH from_inv_line INTO from_inv_line_rec;
299 -- 5(a)
300 IF from_inv_line%FOUND THEN
301 ln_invc_line_num := from_inv_line_rec.line_number;
302
303 UPDATE ap_invoice_lines_all
304 SET amount = insertion_amount,
305 last_update_date = sysdate,
306 last_updated_by = ln_user_id,
307 last_update_login = ln_login_id
308 WHERE invoice_id =p_invoice_id
309 AND line_number = ln_invc_line_num ;
310
311 ELSE
312 ln_invc_line_num := ln_max_lnno + 1;
313 BEGIN
314 --> Create a record in ap_invoice_lines based on the maximum line
315 INSERT INTO ap_invoice_lines_all
316 (
317 INVOICE_ID
318 , LINE_NUMBER
319 , LINE_TYPE_LOOKUP_CODE
320 , DESCRIPTION
321 , ORG_ID
322 , MATCH_TYPE
323 , ACCOUNTING_DATE
324 , PERIOD_NAME
325 , DEFERRED_ACCTG_FLAG
326 , DEF_ACCTG_START_DATE
327 , DEF_ACCTG_END_DATE
328 , DEF_ACCTG_NUMBER_OF_PERIODS
329 , DEF_ACCTG_PERIOD_TYPE
330 , SET_OF_BOOKS_ID
331 , AMOUNT
332 , WFAPPROVAL_STATUS
333 , CREATION_DATE
334 , CREATED_BY
335 , LAST_UPDATED_BY
336 , LAST_UPDATE_DATE
337 , LAST_UPDATE_LOGIN
338 )
339 VALUES
340 (
341 p_invoice_id
342 , ln_invc_line_num
343 , lv_misc
344 , lv_description
345 , p_org_id
346 , lv_not_matched
347 , rec_max_ap_lines_all.accounting_date
348 , rec_max_ap_lines_all.period_name
349 , rec_max_ap_lines_all.deferred_acctg_flag
350 , rec_max_ap_lines_all.def_acctg_start_date
351 , rec_max_ap_lines_all.def_acctg_end_date
352 , rec_max_ap_lines_all.def_acctg_number_of_periods
353 , rec_max_ap_lines_all.def_acctg_period_type
354 , rec_max_ap_lines_all.set_of_books_id
355 , insertion_amount
356 , lv_not_required
357 , sysdate
358 , ln_user_id
359 , ln_user_id
360 , sysdate
361 , ln_login_id
362 );
363 EXCEPTION
364 WHEN OTHERS THEN
365
366 fnd_file.put_line(FND_FILE.LOG,' Error while inserting JAI Rounding
367 adjustment in ail'|| SQLERRM);
368 P_return_code := jai_constants.unexpected_error;
369 P_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARUID_T1 ' || substr(sqlerrm,1,1900);
370 END;
371 END IF;
372
373 -- 5(a)
374 IF from_inv_line%ISOPEN THEN
375 CLOSE from_inv_line;
376 END IF;
377
378 OPEN from_inv_dist(p_invoice_id, lv_description,ln_invc_line_num,lv_misc);
379 FETCH from_inv_dist INTO from_inv_dist_rec;
380
381 -- 5(b)
382 IF from_inv_dist%FOUND THEN
383
384 UPDATE ap_invoice_distributions_all
385 SET amount = insertion_amount,
386 last_update_date = sysdate,
387 last_updated_by = ln_user_id,
388 last_update_login = ln_login_id
389 WHERE invoice_id =p_invoice_id
390 AND distribution_line_number = from_inv_dist_rec.distribution_line_number
391 AND invoice_line_number =ln_invc_line_num ;
392 ELSE
393 CLOSE from_inv_dist;
394 ln_distribution_line_num := 1;
395
396 OPEN c_invoice_distribution(p_invoice_id);
397 FETCH c_invoice_distribution INTO r_invoice_distribution;
398 CLOSE c_invoice_distribution;
399 BEGIN
400 --> Create a record in distributions based on the new line created above
401 INSERT INTO ap_invoice_distributions_all
402 (
403 accounting_date,
404 accrual_posted_flag,
405 assets_addition_flag,
406 assets_tracking_flag,
407 cash_posted_flag,
408 distribution_line_number,
409 dist_code_combination_id,
410 invoice_id,
411 last_updated_by,
412 last_update_date,
413 line_type_lookup_code,
414 period_name,
415 set_of_books_id,
416 amount,
417 base_amount,
418 created_by,
419 creation_date,
420 description,
421 last_update_login,
422 posted_flag,
423 reversal_flag,
424 vat_code,
425 invoice_distribution_id,
426 org_id,
427 dist_match_type,
428 invoice_line_number
429 )
430 VALUES
431 (
432 rec_max_ap_lines_all.accounting_date,
433 r_invoice_distribution.accrual_posted_flag,
434 r_invoice_distribution.assets_addition_flag,
435 r_invoice_distribution.assets_tracking_flag,
436 lv_cash_posted_flag,
437 ln_distribution_line_num ,
438 set_up_values_rec.tolerance_charge_account_id,
439 p_invoice_id,
440 ln_user_id,
441 sysdate,
442 lv_misc,
443 rec_max_ap_lines_all.period_name,
444 rec_max_ap_lines_all.set_of_books_id,
445 insertion_amount,
446 insertion_amount,
447 ln_user_id,
448 sysdate,
449 lv_description, /* 'Commercial Rounding Off Distribution', Ramananda for removal of SQL LITERALs */
450 ln_login_id,
451 'N',
452 'N',
453 lv_vat_code, /* 'ROUNDING', Ramananda for removal of SQL LITERALs */
454 ap_invoice_distributions_s.nextval,
455 p_org_id
456 ,lv_not_matched
457 ,ln_invc_line_num
458 );
459 EXCEPTION
460 when others then
461
462 fnd_file.put_line(FND_FILE.LOG,' Error while inserting JAI Rounding adjustment in aid'|| SQLERRM);
463 P_return_code := jai_constants.unexpected_error;
464 P_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARUID_T1 ' || substr(sqlerrm,1,1900);
465 END;
466 END IF;
467 -- 5(b)
468
469 END IF;
470 -- 5
471
472 EXCEPTION
473
474 WHEN OTHERS THEN
475 fnd_file.put_line(FND_FILE.LOG,'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARUID_T1 '|| SQLERRM);
476
477 P_return_code := jai_constants.unexpected_error;
478 P_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARUID_T1 ' || substr(sqlerrm,1,1900);
479
480 END inv_holds_check;
481
482
483 END jai_ap_tolerance_pkg;