1 package body AP_GET_SUPPLIER_BALANCE_PKG as
2 /* $Header: apxsoblb.pls 120.14 2008/02/06 08:13:56 schamaku ship $ */
3
4 Procedure AP_GET_SUPPLIER_BALANCE( p_request_id in number
5 ,p_set_of_books_id number
6 ,p_as_of_date in date
7 ,p_supplier_name_from in varchar2
8 ,p_supplier_name_to in varchar2
9 ,p_currency in varchar2
10 ,p_min_invoice_balance in number
11 ,p_min_open_balance in number
12 ,p_include_prepayments in varchar2
13 ,p_reference_number in varchar2
14 ,p_debug_flag in varchar2
15 ,p_trace_flag in varchar2
16 ) is
17
18
19 l_total_inv_balance ap_invoices.invoice_amount%TYPE;
20 l_discount_avail ap_invoices.invoice_amount%TYPE;
21 l_tot_discount_avail ap_invoices.invoice_amount%TYPE;
22 l_amount_remaining ap_invoices.invoice_amount%TYPE;
23 l_functional_currency_code ap_invoices.invoice_currency_code%TYPE;
24 l_open_balance ap_invoices.invoice_amount%TYPE;
25 l_total_prepayment ap_invoices.invoice_amount%TYPE;
26 l_session_language varchar2(40);/* For MLS changes */
27 l_base_language varchar2(40); /*For MLS changes*/
28 l_inv_balance ap_invoices.invoice_amount%TYPE;
29 --l_start_date ap_invoices.invoice_date%TYPE;
30 l_organization_name hr_organization_units.name%TYPE;
31 l_sum_discount NUMBER; -- 3641604
32 l_paid_amount NUMBER; -- 2901541
33 --The cursor vendorinfo holds all the information about the vendor and vendor site
34
35 CURSOR vendorinfo(p_base_language po_vendor_sites_all.language%TYPE,
36 p_session_language po_vendor_sites_all.language%TYPE ) is
37 SELECT
38 pv.vendor_id,
39 pv.vendor_name supplier_name,
40 pv.segment1 supplier_number,
41 pv.num_1099 tax_payer_id,
42 pv.vat_code vat_registration_number,
43 pvs.vendor_site_code supplier_site_code,
44 pvs.vendor_site_id,
45 pvs.address_line1,
46 pvs.address_line2,
47 pvs.address_line3,
48 pvs.city,
49 pvs.state,
50 pvs.zip postal_code,
51 pvs.province,
52 pvs.country
53 FROM
54 ap_suppliers pv ,
55 ap_supplier_sites_all pvs
56 WHERE
57 upper(pv.vendor_name) between upper(nvl(p_supplier_name_from,'A'))
58 and upper(nvl(p_supplier_name_to,'Z'))
59 and pvs.vendor_id = pv.vendor_id
60 and nvl(pvs.language,p_base_language)=p_session_language;
61
62 /*--Cusrsor start date holds the Start date for a period
63
64 CURSOR Start_date(p_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE) IS
65 SELECT gp.start_date
66 FROM gl_period_types gpt,gl_periods gp,
67 gl_period_sets gps,gl_sets_of_books sob
68 WHERE
69 sob.set_of_books_id=p_set_of_books_id
70 and sob.period_set_name=gps.period_set_name
71 and sob.accounted_period_type=gpt.period_type
72 and gp.period_set_name=gps.period_set_name
73 and gp.period_type=gpt.period_type
74 and p_as_of_date BETWEEN gp.start_date and gp.end_date;*/
75
76 --Cursor invinfo holds data about invoices for the parameters entered
77
78 --Bug 2431936 changed 'prepay' to 'PREPAY'
79
80 CURSOR invinfo(p_vendor_id ap_invoices.vendor_id%TYPE,
81 p_vendor_site_id ap_invoices.vendor_site_id%TYPE,
82 p_currency ap_invoices.invoice_currency_code%TYPE) is
83 SELECT
84 ai.invoice_id,
85 ai.payment_cross_rate,
86 -- 3641604 aps.payment_num,
87 lkv.meaning trans_type,
88 ai.invoice_num trans_num,
89 ai.invoice_date trans_date,
90 ai.invoice_currency_code,
91 ai.invoice_amount invoice_amount,
92 nvl(ai.amount_paid,0) payment_amount,
93 nvl(ai.discount_amount_taken,0) discount_taken,
94 sum(nvl(aid1.amount,0)) dist_amount --bug 3338086
95 FROM
96 /*bug 5264865*/
97 ap_invoices_all ai,
98 ap_invoice_distributions_all aid1,
99 fnd_lookup_types_vl lkp,
100 fnd_lookup_values_vl lkv
101 -- 3641604 ap_payment_schedules aps
102 WHERE
103 ai.invoice_id = aid1.invoice_id
104 -- and ai.payment_status_flag<>'P'
105 -- Fix for 2545297 commented above line and wrote below one
106 -- and ai.payment_status_flag in ('N','P') 2901541
107 -- and aid1.line_type_lookup_code = 'ITEM' Commented this line for bug: 3338086
108 and lkp.lookup_type = 'INVOICE TYPE'
109 and lkp.application_id = 200
110 and lkv.lookup_code = ai.invoice_type_lookup_code
111 and lkv.lookup_type=lkp.lookup_type
112 and ai.invoice_type_lookup_code <> 'PREPAYMENT'
113 and ai.invoice_date <= p_as_of_date
114 and ai.vendor_id = p_vendor_id
115 and ai.vendor_site_id = p_vendor_site_id
116 and ai.invoice_currency_code = nvl(p_currency,ai.invoice_currency_code)
117 -- 3641604 and ai.invoice_id=aps.invoice_id
118 GROUP BY
119 ai.invoice_id,
120 lkv.meaning ,
121 invoice_num ,
122 invoice_date,
123 ai.invoice_currency_code ,
124 ai.invoice_amount,
125 ai.amount_paid,
126 ai.discount_amount_taken,
127 ai.invoice_id,
128 -- 3641604 aps.payment_num,
129 ai.payment_cross_rate;
130 --Bug 3338086 Removed group_by for line_type_code. Else
131 --it will fetch multiple rows.
132
133 --bug 5264869 replace view with base table
134 CURSOR curinfo(p_supplier_id po_vendors.vendor_id%TYPE) IS
135 SELECT DISTINCT(invoice_currency_code) invoice_currency_code
136 FROM ap_invoices_all ai
137 WHERE ai.vendor_id=p_supplier_id
138 and ai.invoice_currency_code=nvl(p_currency,ai.invoice_currency_code);
139
140 /*
141 3641604 : New cursor to get sum of discount available for invoice
142 */
143
144 CURSOR ps_cursor (p_invoice_id NUMBER) IS
145 SELECT payment_num
146 FROM ap_payment_schedules
147 WHERE invoice_id = p_invoice_id;
148
149
150 BEGIN --#1
151
152 /*Commented the following code fnd added the next two SELECT stmts for bug#1721165 TMANDA
153 select substr(userenv('LANG'),1,4) into l_session_language from dual;
154 select language_code into l_base_language from fnd_languages where installed_flag='B';
155 */
156 -- for bug#1721165 Start TMANDA
157 -- Get the session language
158 select substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1)
159 into l_session_language
160 from dual;
161
162 -- Get the base language
163 select nls_language
164 into l_base_language
165 from fnd_languages
166 where installed_flag = 'B';
167
168 -- for bug#1721165 End TMANDA
169 /* For bug 2113775
170 Selecting the organization from gl_sets_of_books
171 in case of single org since the org_id will be
172 null in hr_organization_units
173 */
174
175 Begin
176
177 SELECT name
178 INTO l_organization_name
179 FROM hr_organization_units
180 WHERE organization_id = FND_PROFILE.VALUE('ORG_ID');
181
182 Exception
183 When no_data_found Then
184 Begin
185 Select name
186 INTO l_organization_name
187 FROM gl_sets_of_books
188 WHERE set_of_books_id = P_Set_of_books_id;
189 Exception
190 When others then
191 null;
192 End;
193 End;
194 -- End for bug 2113775
195
196
197
198
199 SELECT currency_code
200 INTO l_functional_currency_code
201 FROM gl_sets_of_books
202 WHERE set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
203
204 /*OPEN start_date(p_set_of_books_id);
205 FETCH start_date into l_start_date;
206 CLOSE start_date;*/
207
208 BEGIN --#2 Begin VENDOR BLOCK
209
210 FOR vendor_rec IN vendorinfo (l_base_language,
211 l_session_language)
212 LOOP ---Start vendor loop
213
214 BEGIN --#3 Begin Currency Block
215 FOR currency_rec IN curinfo(vendor_rec.vendor_id)
216 LOOP
217
218 l_total_inv_balance:=0;
219 l_open_balance:=0;
220
221 BEGIN --#4
222 FOR inv_rec IN invinfo (vendor_rec.vendor_id,
223 vendor_rec.vendor_site_id,
224 currency_rec.invoice_currency_code)
225 LOOP
226 /*
227 2901541 Check to see if this invoice has an open balance on the As Of Date
228 */
229 IF AP_GET_SUPPLIER_BALANCE_PKG.invoice_is_open_on_ao_date
230 (inv_rec.invoice_id, p_as_of_date)
231 THEN l_paid_amount := AP_GET_SUPPLIER_BALANCE_PKG.get_paid_amount_on_ao_date
232 (inv_rec.invoice_id, p_as_of_date);
233 l_discount_avail:=0;
234 l_tot_discount_avail:=0;
235
236 /*
237 3641604 : get the sum of the available discounts for the invoice
238 */
239 l_sum_discount := 0;
240 FOR ps_rec IN ps_cursor(inv_rec.invoice_id) LOOP
241 BEGIN
242 l_sum_discount := l_sum_discount +
243 ap_payment_schedules_pkg.get_discount_available
244 (inv_rec.invoice_id, ps_rec.payment_num, sysdate,
245 inv_rec.invoice_currency_code);
246 EXCEPTION
247 WHEN NO_DATA_FOUND
248 THEN l_sum_discount:= l_sum_discount + 0;
249 END;
250 END LOOP;
251 l_discount_avail := l_sum_discount;
252 /*
253 End 3641604 */
254
255 --for Bug 1798542 following line changed such that
256 -- payment_cross_rate doesn't divide invoice_amount
257
258
259 l_amount_remaining := /* Bug3338086 deduct the pmt/disc amt from dist_amount */
260 ap_utilities_pkg.ap_round_currency
261 ((inv_rec.dist_amount - ((l_paid_amount+
262 inv_rec.discount_taken+l_discount_avail)/
263 nvl(inv_rec.payment_cross_rate,1)))
264 ,inv_rec.invoice_currency_code);
265
266 --Start amount remaining if
267 IF l_amount_remaining>=nvl(p_min_invoice_balance,0) THEN
268
269 INSERT INTO AP_SUPPLIER_BALANCE_ITF(
270 Request_id ,
271 as_of_date ,
272 organization_name ,
273 functional_currency_code ,
274 supplier_name ,
275 supplier_number,
276 vat_registration_number,
277 supplier_site_code ,
278 address_line1 ,
279 address_line2 ,
280 address_line3 ,
281 city ,
282 state ,
283 zip ,
284 country ,
285 invoice_type,
286 invoice_num,
287 invoice_date,
288 invoice_currency_code,
289 invoice_amount ,
290 amount_remaining ,
291 payment_amount ,
292 discount_taken ,
293 discount_amount_available
294 )
295 VALUES (p_request_id,
296 p_as_of_date,
297 l_organization_name ,
298 l_functional_currency_code ,
299 vendor_rec.supplier_name,
300 vendor_rec.supplier_number,
301 vendor_rec.vat_registration_number,
302 vendor_rec.supplier_site_code,
303 vendor_rec.address_line1,
304 vendor_rec.address_line2,
305 vendor_rec.address_line3,
306 vendor_rec.city,
307 vendor_rec.state,
308 vendor_rec.postal_code,
309 vendor_rec.country,
310 inv_rec.trans_type,
311 inv_rec.trans_num,
312 inv_rec.trans_date,
313 inv_rec.invoice_currency_code,
314 inv_rec.invoice_amount,
315 l_amount_remaining,
316 inv_rec.payment_amount,
317 inv_rec.discount_taken,
318 l_tot_discount_avail);
319
320 END IF; --End amount remaining if
321 END IF; -- End Invoice is open on As Of date
322 END LOOP; --End Invoices Loop
323
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 EXIT;
327 WHEN OTHERS THEN
328 APP_EXCEPTION.RAISE_EXCEPTION;
329 END; --#4
330
331 /*Bug 2431936 made the following changes to SQL below:
332 1. Original and remaining amounts were switched. Corrected.
333 2. Was excluding Partially paid. According to doc, only paid
334 should be selected.
335 3. Was not restricting by minimun invoice balance. According to
336 doc, the minimum should restrict the transactions in the report.
337 4. SQL was selecting from vendor tables. Since we are within
338 the vendor loop, we can get values from cursor instead of from
339 the tables, so the SQL is simpler.*/
340
341 IF (NVL(p_include_prepayments,'N') = 'Y') THEN --bug6800315
342
343 INSERT INTO ap_supplier_balance_itf( Request_id ,
344 as_of_date,
345 organization_name ,
346 functional_currency_code ,
347 supplier_name ,
348 supplier_number,
349 vat_registration_number,
350 supplier_site_code ,
351 address_line1 ,
352 address_line2 ,
353 address_line3 ,
354 city ,
355 state ,
356 zip ,
357 country ,
358 invoice_type,
359 invoice_num,
360 invoice_date,
361 invoice_currency_code,
362 prepay_amount_original,
363 prepay_amount_remaining,
364 prepay_amount_applied)
365 SELECT
366 p_request_id,
367 p_as_of_date,
368 l_organization_name ,
369 l_functional_currency_code,
370 vendor_rec.supplier_name,
371 vendor_rec.supplier_number,
372 vendor_rec.vat_registration_number,
373 vendor_rec.supplier_site_code,
374 vendor_rec.address_line1,
375 vendor_rec.address_line2,
376 vendor_rec.address_line3,
377 vendor_rec.city,
378 vendor_rec.state,
379 vendor_rec.postal_code,
380 vendor_rec.country,
381 lkv.meaning,
382 ai.invoice_num,
383 ai.invoice_date ,
384 ai.invoice_currency_code,
385 SUM(nvl(AID1.AMOUNT,0)),
386 SUM(nvl(AID1.PREPAY_AMOUNT_REMAINING,AID1.AMOUNT) ),
387 (SUM(nvl(AID1.AMOUNT,0)) -
388 SUM(nvl(AID1.PREPAY_AMOUNT_REMAINING,AID1.AMOUNT)))
389 FROM
390 ap_invoices ai,
391 ap_invoice_distributions aid1,
392 fnd_lookup_types_vl lkp,
393 fnd_lookup_values_vl lkv
394 WHERE
395 ai.invoice_id=aid1.invoice_id
396 and ((aid1.line_type_lookup_code = 'ITEM') or
397 /* BUG 3935997: 'or' condition added */
398 (aid1.line_type_lookup_code = 'TAX' and
399 aid1.tax_calculated_flag = 'Y'))
400 and ai.invoice_type_lookup_code = 'PREPAYMENT'
401 and ai.payment_status_flag = 'Y'
402 and lkp.lookup_type = 'INVOICE TYPE'
403 and lkp.application_id = 200
404 and lkv.lookup_code = ai.invoice_type_lookup_code
405 and lkp.lookup_type=lkv.lookup_type
406 and ai.vendor_id = vendor_rec.vendor_id
407 and ai.vendor_site_id = vendor_rec.vendor_site_id
408 and ai.invoice_date <= p_as_of_date
409 and ai.invoice_currency_code = nvl(currency_rec.invoice_currency_code,
410 ai.invoice_currency_code) --bug6800315
411 and NVL(aid1.reversal_flag,'N') <> 'Y' --bug6500253/6800315
412 and nvl(aid1.prepay_amount_remaining,aid1.amount)> 0
413 HAVING SUM(nvl(AID1.AMOUNT,0)) >= NVL(p_min_invoice_balance,0)--bug6800315
414 GROUP BY
415 lkv.meaning,
416 ai.invoice_num,
417 ai.invoice_date,
418 ai.invoice_currency_code;
419
420 END IF;--p_include_prepayments bug6800315
421 END LOOP; --curinfo cursor
422
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 NULL;
426 WHEN OTHERS THEN
427 --APP_EXCEPTION.RAISE_EXCEPTION;
428 NULL;
429
430 END; --#3 Currency Block
431
432 END LOOP; --vendor loop
433
434 EXCEPTION
435 WHEN NO_DATA_FOUND THEN
436 NULL;
437 WHEN OTHERS THEN
438 APP_EXCEPTION.RAISE_EXCEPTION;
439
440 END; --#2 vendor block
441
442 EXCEPTION
443 WHEN OTHERS THEN
444 APP_EXCEPTION.RAISE_EXCEPTION;
445 END AP_GET_SUPPLIER_BALANCE; --#1
446
447 -- added the following 2 functions Bug: 2901541
448 FUNCTION get_paid_amount_on_ao_date
449 (p_invoice_id IN NUMBER,
450 p_as_of_date IN DATE)
451 RETURN NUMBER IS
452 v_payment_amount NUMBER;
453
454 BEGIN
455
456 SELECT NVL(SUM(NVL(amount, 0)), 0)
457 INTO v_payment_amount
458 FROM ap_invoice_payments
459 WHERE invoice_id = p_invoice_id
460 AND accounting_date <= p_as_of_date;
461
462 RETURN v_payment_amount;
463
464 END get_paid_amount_on_ao_date;
465
466 FUNCTION invoice_is_open_on_ao_date
467 (p_invoice_id IN NUMBER,
468 p_as_of_date IN DATE)
469 RETURN BOOLEAN IS
470 v_payment_amount NUMBER;
471 v_invoice_amount NUMBER;
472 v_payment_cross_rate NUMBER; -- 3542467
473 v_invoice_currency_code VARCHAR2(15); -- 3542467
474 v_discount_taken NUMBER; -- 3542467
475
476 BEGIN
477
478 /*
479 3542467: Get the payment cross rate and invoice currency code
480 */
481 SELECT AI.payment_cross_rate, AI.invoice_currency_code, NVL(SUM(NVL(AID.amount, 0)), 0)
482 INTO v_payment_cross_rate, v_invoice_currency_code, v_invoice_amount
483 FROM ap_invoice_distributions AID, ap_invoices AI
484 WHERE AI.invoice_id = AID.invoice_id
485 AND AI.invoice_id = p_invoice_id
486 GROUP BY AI.payment_cross_rate, AI.invoice_currency_code;
487
488 /*
489 3542467: Get the discount taken
490 */
491 SELECT NVL(SUM(NVL(AIP.amount, 0)), 0), NVL(SUM(NVL(AIP.discount_taken, 0)), 0)
492 INTO v_payment_amount, v_discount_taken
493 FROM ap_invoice_payments AIP, ap_invoices AI
494 WHERE AIP.invoice_id = AI.invoice_id
495 AND AI.invoice_id = p_invoice_id
496 AND accounting_date <= p_as_of_date;
497
498 /*
499 3542467: Modify the method for determining if there is an open balance
500 to account for payment rate variances and discount
501 */
502 IF AP_UTILITIES_PKG.ap_round_currency
503 ((v_invoice_amount - ((v_payment_amount + v_discount_taken) /
504 NVL(v_payment_cross_rate, 1))),
505 v_invoice_currency_code) <> 0
506 THEN RETURN TRUE;
507 ELSE RETURN FALSE;
508 END IF;
509
510
511 END invoice_is_open_on_ao_date;
512
513 END AP_GET_SUPPLIER_BALANCE_PKG;
[Home] [Help]