[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.1.12010000.3 2008/09/23 17:13:16 lgopalsa 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
217 IF ((p_invoice_amount = line_amounts_rec.line_amount)
218 AND v_check_entry <> 0) THEN
219
220 -- rounding entry has gone but the vat code not updated.
221 ln_invc_line_num := null;
222
223 UPDATE ap_invoice_distributions_all
224 SET amount = 0
225 WHERE invoice_id = p_invoice_id
226 AND vat_code = lv_vat_code
227 RETURNING invoice_line_number INTO ln_invc_line_num;
228
229 UPDATE ap_invoice_lines_all
230 SET amount = 0
231 WHERE invoice_id = p_invoice_id
232 AND line_number = ln_invc_line_num;
233
234 P_return_code := jai_constants.successful;
235 P_return_message := ' Updated existing rounding line';
236
237 RETURN;
238
239 END IF;
240 --2
241
242
243 diff_amount := line_amounts_rec.line_amount - p_invoice_amount ;
244
245 --IF IT DOES NOT MATCH THEN CHECK THE TOLERANCE LEVEL
246 -- 3
247
248 IF (NVL(set_up_values_rec.tolerance_flag,'Z') = 'P') THEN
249 --CONSIDER ONLY THE PERCENTAGE TOLERANCE
250
251 upper_value := NVL(set_up_values_rec.tolerance_pos_percent,0) * p_invoice_amount/100;
252 lower_value := NVL(set_up_values_rec.tolerance_neg_percent,0) * p_invoice_amount/100;
253
254
255 ELSIF (NVL(set_up_values_rec.tolerance_flag,'Z') = 'A') THEN
256
257 --CONSIDER ONLY THE AMOUNT TOLERANCE
258
259 upper_value := NVL(set_up_values_rec.tolerance_pos_amt,0);
260 lower_value := NVL(set_up_values_rec.tolerance_neg_amt,0);
261
262
263 ELSE --1
264 --CONSIDER LEAST OF PERCENTAGE AND AMOUNT TOLERANCES
265
266 OPEN least_values(p_invoice_amount);
267 FETCH least_values INTO least_values_rec;
268 CLOSE least_values;
269
270 upper_value := least_values_rec.max_val;
271 lower_value := least_values_rec.min_val;
272
273 END IF;
274 -- 3
275
276 -- 4
277 IF ( (diff_amount > 0 AND diff_amount <= upper_value)
278 OR
279 (diff_amount < 0 AND ABS(diff_amount) <= lower_value)
280 ) THEN
281
282 --INSERT POSITIVE OR NEGATIVE DISTRIBUTION EQUAL TO DIFF_AMOUNT
283 insertion_amount := (-1)*diff_amount;
284 END IF;
285 -- 4
286
287 --5
288 IF insertion_amount <> 0 THEN
289
290 lv_not_required := 'NOT REQUIRED';
291 lv_not_matched := 'NOT_MATCHED';
292 lv_cash_posted_flag := 'N';
293
294 -- Check whether the rounding line already exists in table
295
296 OPEN from_inv_line(p_invoice_id, lv_description,lv_misc);
297 FETCH from_inv_line INTO from_inv_line_rec;
298 -- 5(a)
299 IF from_inv_line%FOUND THEN
300 ln_invc_line_num := from_inv_line_rec.line_number;
301
302 UPDATE ap_invoice_lines_all
303 SET amount = insertion_amount,
304 last_update_date = sysdate,
305 last_updated_by = ln_user_id,
306 last_update_login = ln_login_id
307 WHERE invoice_id =p_invoice_id
308 AND line_number = ln_invc_line_num ;
309
310 ELSE
311 ln_invc_line_num := ln_max_lnno + 1;
312 BEGIN
313 --> Create a record in ap_invoice_lines based on the maximum line
314 INSERT INTO ap_invoice_lines_all
315 (
316 INVOICE_ID
317 , LINE_NUMBER
318 , LINE_TYPE_LOOKUP_CODE
319 , DESCRIPTION
320 , ORG_ID
321 , MATCH_TYPE
322 , ACCOUNTING_DATE
323 , PERIOD_NAME
324 , DEFERRED_ACCTG_FLAG
325 , DEF_ACCTG_START_DATE
326 , DEF_ACCTG_END_DATE
327 , DEF_ACCTG_NUMBER_OF_PERIODS
328 , DEF_ACCTG_PERIOD_TYPE
329 , SET_OF_BOOKS_ID
330 , AMOUNT
331 , WFAPPROVAL_STATUS
332 , CREATION_DATE
333 , CREATED_BY
334 , LAST_UPDATED_BY
335 , LAST_UPDATE_DATE
336 , LAST_UPDATE_LOGIN
337 )
338 VALUES
339 (
340 p_invoice_id
341 , ln_invc_line_num
342 , lv_misc
343 , lv_description
344 , p_org_id
345 , lv_not_matched
346 , rec_max_ap_lines_all.accounting_date
347 , rec_max_ap_lines_all.period_name
348 , rec_max_ap_lines_all.deferred_acctg_flag
349 , rec_max_ap_lines_all.def_acctg_start_date
350 , rec_max_ap_lines_all.def_acctg_end_date
351 , rec_max_ap_lines_all.def_acctg_number_of_periods
352 , rec_max_ap_lines_all.def_acctg_period_type
353 , rec_max_ap_lines_all.set_of_books_id
354 , insertion_amount
355 , lv_not_required
356 , sysdate
357 , ln_user_id
358 , ln_user_id
359 , sysdate
360 , ln_login_id
361 );
362 EXCEPTION
363 WHEN OTHERS THEN
364
365 fnd_file.put_line(FND_FILE.LOG,' Error while inserting JAI Rounding
366 adjustment in ail'|| SQLERRM);
367 P_return_code := jai_constants.unexpected_error;
368 P_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARUID_T1 ' || substr(sqlerrm,1,1900);
369 END;
370 END IF;
371
372 -- 5(a)
373 IF from_inv_line%ISOPEN THEN
374 CLOSE from_inv_line;
375 END IF;
376
377 OPEN from_inv_dist(p_invoice_id, lv_description,ln_invc_line_num,lv_misc);
378 FETCH from_inv_dist INTO from_inv_dist_rec;
379
380 -- 5(b)
381 IF from_inv_dist%FOUND THEN
382
383 UPDATE ap_invoice_distributions_all
384 SET amount = insertion_amount,
385 last_update_date = sysdate,
386 last_updated_by = ln_user_id,
387 last_update_login = ln_login_id
388 WHERE invoice_id =p_invoice_id
389 AND distribution_line_number = from_inv_dist_rec.distribution_line_number
390 AND invoice_line_number =ln_invc_line_num ;
391 ELSE
392 CLOSE from_inv_dist;
393 ln_distribution_line_num := 1;
394
395 OPEN c_invoice_distribution(p_invoice_id);
396 FETCH c_invoice_distribution INTO r_invoice_distribution;
397 CLOSE c_invoice_distribution;
398 BEGIN
399 --> Create a record in distributions based on the new line created above
400 INSERT INTO ap_invoice_distributions_all
401 (
402 accounting_date,
403 accrual_posted_flag,
404 assets_addition_flag,
405 assets_tracking_flag,
406 cash_posted_flag,
407 distribution_line_number,
408 dist_code_combination_id,
409 invoice_id,
410 last_updated_by,
411 last_update_date,
412 line_type_lookup_code,
413 period_name,
414 set_of_books_id,
415 amount,
416 base_amount,
417 created_by,
418 creation_date,
419 description,
420 last_update_login,
421 posted_flag,
422 reversal_flag,
423 vat_code,
424 invoice_distribution_id,
425 org_id,
426 dist_match_type,
427 invoice_line_number
428 )
429 VALUES
430 (
431 rec_max_ap_lines_all.accounting_date,
432 r_invoice_distribution.accrual_posted_flag,
433 r_invoice_distribution.assets_addition_flag,
434 r_invoice_distribution.assets_tracking_flag,
435 lv_cash_posted_flag,
436 ln_distribution_line_num ,
437 set_up_values_rec.tolerance_charge_account_id,
438 p_invoice_id,
439 ln_user_id,
440 sysdate,
441 lv_misc,
442 rec_max_ap_lines_all.period_name,
443 rec_max_ap_lines_all.set_of_books_id,
444 insertion_amount,
445 insertion_amount,
446 ln_user_id,
447 sysdate,
448 lv_description, /* 'Commercial Rounding Off Distribution', Ramananda for removal of SQL LITERALs */
449 ln_login_id,
450 'N',
451 'N',
452 lv_vat_code, /* 'ROUNDING', Ramananda for removal of SQL LITERALs */
453 ap_invoice_distributions_s.nextval,
454 p_org_id
455 ,lv_not_matched
456 ,ln_invc_line_num
457 );
458 EXCEPTION
459 when others then
460
461 fnd_file.put_line(FND_FILE.LOG,' Error while inserting JAI Rounding adjustment in aid'|| SQLERRM);
462 P_return_code := jai_constants.unexpected_error;
463 P_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARUID_T1 ' || substr(sqlerrm,1,1900);
464 END;
465 END IF;
466 -- 5(b)
467
468 END IF;
469 -- 5
470
471 EXCEPTION
472
473 WHEN OTHERS THEN
474 fnd_file.put_line(FND_FILE.LOG,'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARUID_T1 '|| SQLERRM);
475
476 P_return_code := jai_constants.unexpected_error;
477 P_return_message := 'Encountered an error in JAI_AP_IA_TRIGGER_PKG.ARUID_T1 ' || substr(sqlerrm,1,1900);
478
479 END inv_holds_check;
480
481
482 END jai_ap_tolerance_pkg;