[Home] [Help]
PACKAGE BODY: APPS.AP_GET_SUPPLIER_BALANCE_PKG
Source
1 package body AP_GET_SUPPLIER_BALANCE_PKG as
2 /* $Header: apxsoblb.pls 120.20.12020000.2 2012/07/13 21:30:28 pgayen 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_amount_remaining ap_invoices.invoice_amount%TYPE;
20 l_session_language VARCHAR2(40);/* For MLS changes */
21 l_base_language VARCHAR2(40); /*For MLS changes*/
22 l_organization_name hr_organization_units.name%TYPE;
23 l_payment_cross_rate ap_invoices_all.payment_cross_rate%TYPE;
24 l_invoice_currency_code ap_invoices_all.invoice_currency_code%TYPE;
25 l_invoice_amount ap_invoices_all.invoice_amount%TYPE;
26 l_payment_amount ap_invoices_all.amount_paid%TYPE;
27 l_discount_taken ap_invoices_all.discount_amount_taken%TYPE;
28 l_discount_avail ap_invoices_all.discount_amount_taken%TYPE;
29
30
31 --The cursor vendorinfo holds all the information about the vendor and vendor site
32 CURSOR vendorinfo(p_base_language po_vendor_sites_all.language%TYPE,
33 p_session_language po_vendor_sites_all.language%TYPE ) is
34 SELECT pv.vendor_id,
35 pv.vendor_name supplier_name,
36 pv.segment1 supplier_number,
37 pv.num_1099 tax_payer_id,
38 pv.vat_code vat_registration_number,
39 pvs.vendor_site_code supplier_site_code,
40 pvs.vendor_site_id,
41 pvs.address_line1,
42 pvs.address_line2,
43 pvs.address_line3,
44 pvs.city,
45 pvs.state,
46 pvs.zip postal_code,
47 pvs.province,
48 pvs.country
49 FROM ap_suppliers pv ,
50 ap_supplier_sites_all pvs
51 WHERE
52 --bug 12927165
53 --upper(pv.vendor_name) between upper(nvl(p_supplier_name_from,'A'))
54 --and upper(nvl(p_supplier_name_to,'Z'))
55 upper(pv.vendor_name) between upper(nvl(p_supplier_name_from,pv.vendor_name))
56 and upper(nvl(p_supplier_name_to,pv.vendor_name))
57 AND pvs.vendor_id = pv.vendor_id
58 AND nvl(pvs.language,p_base_language)=p_session_language;
59
60
61 --Cursor invinfo holds data about invoices for the parameters entered
62 CURSOR invinfo(p_vendor_id ap_invoices.vendor_id%TYPE,
63 p_vendor_site_id ap_invoices.vendor_site_id%TYPE,
64 p_currency ap_invoices.invoice_currency_code%TYPE) is
65 SELECT DISTINCT ai.invoice_id,
66 ai.payment_cross_rate,
67 lkv.meaning trans_type,
68 ai.invoice_num trans_num,
69 ai.invoice_date trans_date,
70 ai.invoice_currency_code,
71 ai.invoice_amount invoice_amount,
72 nvl(ai.amount_paid,0) payment_amount,
73 nvl(ai.discount_amount_taken,0) discount_taken,
74 gl.currency_code --bug9050332
75 FROM ap_invoices_all ai,
76 fnd_lookup_types_vl lkp,
77 fnd_lookup_values_vl lkv,
78 gl_ledgers gl --bug9050332
79 WHERE ai.set_of_books_id = gl.ledger_id --bug9050332
80 AND lkp.lookup_type = 'INVOICE TYPE'
81 AND lkp.application_id = 200
82 AND lkv.view_application_id = 200 --bug13716228
83 AND lkv.lookup_code = ai.invoice_type_lookup_code
84 AND lkv.lookup_type=lkp.lookup_type
85 AND ai.invoice_type_lookup_code <> 'PREPAYMENT'
86 AND trunc(ai.invoice_date) <= trunc(p_as_of_date)
87 AND ai.vendor_id = p_vendor_id
88 AND ai.vendor_site_id = p_vendor_site_id
89 AND ai.invoice_currency_code = nvl(p_currency,ai.invoice_currency_code)
90 AND AP_INVOICES_UTILITY_PKG.get_approval_status(AI.INVOICE_ID,
91 AI.INVOICE_AMOUNT, AI.PAYMENT_STATUS_FLAG,
92 AI.INVOICE_TYPE_LOOKUP_CODE) = 'APPROVED'
93 ;
94
95 --Cursor to get the data of distinct invoice currency codes
96 CURSOR curinfo(p_supplier_id po_vendors.vendor_id%TYPE) IS
97 SELECT DISTINCT(invoice_currency_code) invoice_currency_code
98 FROM ap_invoices_all ai
99 WHERE ai.vendor_id=p_supplier_id
100 AND ai.invoice_currency_code=nvl(p_currency,ai.invoice_currency_code);
101
102 --3641604 : New cursor to get sum of discount available for invoice
103 CURSOR ps_cursor (p_invoice_id NUMBER) IS
104 SELECT payment_num
105 FROM ap_payment_schedules
106 WHERE invoice_id = p_invoice_id;
107
108
109 BEGIN --#1
110
111 -- bug#1721165 Get the session language
112 SELECT substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1)
113 INTO l_session_language
114 FROM dual;
115
116 -- Get the base language
117 SELECT nls_language
118 INTO l_base_language
119 FROM fnd_languages
120 WHERE installed_flag = 'B';
121
122 /* For bug 2113775
123 Selecting the organization from gl_sets_of_books
124 in case of single org since the org_id will be
125 null in hr_organization_units*/
126 BEGIN
127
128 SELECT name
129 INTO l_organization_name
130 FROM hr_organization_units
131 WHERE organization_id = FND_PROFILE.VALUE('ORG_ID');
132
133 EXCEPTION
134 WHEN no_data_found THEN
135 BEGIN
136 SELECT name
137 INTO l_organization_name
138 FROM gl_sets_of_books
139 WHERE set_of_books_id = P_Set_of_books_id;
140 EXCEPTION
141 WHEN OTHERS THEN
142 NULL;
143 END;
144 END;
145
146 BEGIN --#2 Begin Vendor Block
147
148 FOR vendor_rec IN vendorinfo (l_base_language, l_session_language)
149 LOOP
150
151 BEGIN --#3 Begin Currency Block
152
153 FOR currency_rec IN curinfo(vendor_rec.vendor_id)
154 LOOP
155
156 BEGIN --#4 Begin Invoices Block
157
158 FOR inv_rec IN invinfo (vendor_rec.vendor_id, vendor_rec.vendor_site_id, currency_rec.invoice_currency_code)
159 LOOP
160
161 -- get total dist amount including prepay applications
162 SELECT AI.payment_cross_rate, AI.invoice_currency_code, NVL(SUM(NVL(AID.amount, 0)), 0)
163 INTO l_payment_cross_rate, l_invoice_currency_code, l_invoice_amount
164 FROM ap_invoice_distributions AID, ap_invoices AI
165 WHERE AI.invoice_id = AID.invoice_id
166 AND AI.invoice_id = inv_rec.invoice_id
167 AND (AID.prepay_distribution_id IS NULL OR
168 trunc(AID.accounting_date) <= trunc(p_as_of_date))
169 GROUP BY AI.payment_cross_rate, AI.invoice_currency_code;
170
171 -- get payment amount along with the discount taken
172 SELECT NVL(SUM(NVL(AIP.amount, 0)), 0), NVL(SUM(NVL(AIP.discount_taken, 0)), 0)
173 INTO l_payment_amount, l_discount_taken
174 FROM ap_invoice_payments AIP, ap_invoices AI
175 WHERE AIP.invoice_id = AI.invoice_id
176 AND AI.invoice_id = inv_rec.invoice_id
177 AND trunc(AIP.accounting_date) <= trunc(p_as_of_date);
178
179 -- if invoice is open
180 IF AP_UTILITIES_PKG.ap_round_currency
181 ((l_invoice_amount
182 - ((l_payment_amount + l_discount_taken) / NVL(l_payment_cross_rate, 1))
183 ), l_invoice_currency_code) <> 0
184 THEN
185 l_discount_avail:=0;
186
187 -- get available discounts if any
188 FOR ps_rec IN ps_cursor(inv_rec.invoice_id)
189 LOOP
190 BEGIN
191 l_discount_avail := l_discount_avail +
192 ap_payment_schedules_pkg.get_discount_available
193 (inv_rec.invoice_id, ps_rec.payment_num, sysdate,
194 inv_rec.invoice_currency_code);
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 l_discount_avail:= l_discount_avail + 0;
198 END;
199 END LOOP;
200
201 --calculate amount remaining by also considering future available discounts
202 l_amount_remaining := ap_utilities_pkg.ap_round_currency
203 ((l_invoice_amount
204 - ((l_payment_amount + inv_rec.discount_taken+l_discount_avail)/
205 nvl(inv_rec.payment_cross_rate,1))
206 ) ,inv_rec.invoice_currency_code);
207
208 IF l_amount_remaining >= nvl(p_min_invoice_balance,0) THEN
209
210 INSERT INTO AP_SUPPLIER_BALANCE_ITF(
211 Request_id ,
212 as_of_date ,
213 organization_name ,
214 functional_currency_code ,
215 supplier_name ,
216 supplier_number,
217 vat_registration_number,
218 supplier_site_code ,
219 address_line1 ,
220 address_line2 ,
221 address_line3 ,
222 city ,
223 state ,
224 zip ,
225 country ,
226 invoice_type,
227 invoice_num,
228 invoice_date,
229 invoice_currency_code,
230 invoice_amount ,
231 amount_remaining ,
232 payment_amount ,
233 discount_taken ,
234 discount_amount_available )
235 VALUES(p_request_id,
236 p_as_of_date,
237 l_organization_name ,
238 inv_rec.currency_code, --bug9050332 fucntional currency
239 vendor_rec.supplier_name,
240 vendor_rec.supplier_number,
241 vendor_rec.vat_registration_number,
242 vendor_rec.supplier_site_code,
243 vendor_rec.address_line1,
244 vendor_rec.address_line2,
245 vendor_rec.address_line3,
246 vendor_rec.city,
247 vendor_rec.state,
248 vendor_rec.postal_code,
249 vendor_rec.country,
250 inv_rec.trans_type,
251 inv_rec.trans_num,
252 inv_rec.trans_date,
253 inv_rec.invoice_currency_code,
254 inv_rec.invoice_amount,
255 l_amount_remaining,
256 inv_rec.payment_amount,
257 inv_rec.discount_taken,
258 l_discount_avail
259 );
260
261 END IF; --End amount remaining if
262
263 END IF; -- End Invoice is open on As Of date
264 END LOOP; --inv_rec loop
265
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 EXIT;
269 WHEN OTHERS THEN
270 APP_EXCEPTION.RAISE_EXCEPTION;
271 END; --#4 End Invoices Block
272
273 /*Bug 2431936 made the following changes to SQL below:
274 1. Original and remaining amounts were switched. Corrected.
275 2. Was excluding Partially paid. According to doc, only paid
276 should be selected.
277 3. Was not restricting by minimun invoice balance. According to
278 doc, the minimum should restrict the transactions in the report.
279 4. SQL was selecting from vendor tables. Since we are within
280 the vendor loop, we can get values from cursor instead of from
281 the tables, so the SQL is simpler.*/
282
283 IF (NVL(p_include_prepayments,'N') = 'Y') THEN --bug6800315
284 --bug13716228
285 --Issue 1 - prepay dist total is multipled by no.of applications
286 --Issue 2 - fnd_lookup_values results in multiple rows as when the same value
287 -- defined for multiple applications
288 INSERT INTO ap_supplier_balance_itf(
289 Request_id ,
290 as_of_date,
291 organization_name ,
292 functional_currency_code ,
293 supplier_name ,
294 supplier_number,
295 vat_registration_number,
296 supplier_site_code ,
297 address_line1 ,
298 address_line2 ,
299 address_line3 ,
300 city ,
301 state ,
302 zip ,
303 country ,
304 invoice_type,
305 invoice_num,
306 invoice_date,
307 invoice_currency_code,
308 prepay_amount_original,
309 prepay_amount_remaining,
310 prepay_amount_applied,
311 invoice_amount , -- 8217987 3 Cols added
312 amount_remaining ,
313 payment_amount)
314 SELECT DISTINCT p_request_id,
315 p_as_of_date,
316 l_organization_name ,
317 pp_inv.currency_code, --bug9050332
318 vendor_rec.supplier_name,
319 vendor_rec.supplier_number,
320 vendor_rec.vat_registration_number,
321 vendor_rec.supplier_site_code,
322 vendor_rec.address_line1,
323 vendor_rec.address_line2,
324 vendor_rec.address_line3,
325 vendor_rec.city,
326 vendor_rec.state,
327 vendor_rec.postal_code,
328 vendor_rec.country,
329 pp_inv.meaning,
330 pp_inv.invoice_num,
331 pp_inv.invoice_date,
332 pp_inv.invoice_currency_code,
333 pp_inv.dist_total,
334 pp_inv.dist_total+pp_inv.appl_dist_total,
335 -pp_inv.appl_dist_total,
336 NULL,
337 NULL,
338 pp_inv.amount_paid
339 FROM (SELECT DISTINCT
340 ai.invoice_id,
341 aid1.invoice_distribution_id,
342 gl.currency_code,
343 lkv.meaning,
344 ai.invoice_num,
345 ai.invoice_date ,
346 ai.invoice_currency_code,
347 ai.amount_paid,
348 sum(NVL(aid1.amount, 0)) over (partition by ai.invoice_id) dist_total,
349 sum(sum(CASE WHEN trunc(pd.accounting_date) <= trunc(p_as_of_date)
350 THEN pd.amount
351 ELSE 0
352 END)) over (partition by ai.invoice_id) appl_dist_total
353 FROM ap_invoices ai,
354 ap_invoice_distributions aid1,
355 ap_invoice_distributions pd, --bug13411905
356 fnd_lookup_types_vl lkp,
357 fnd_lookup_values_vl lkv,
358 gl_ledgers gl --bug9050332
359 WHERE 1=1
360 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
361 AND ai.payment_status_flag = 'Y'
362 AND lkp.lookup_type = 'INVOICE TYPE'
363 AND lkp.application_id = 200
364 AND lkv.view_application_id = 200
365 AND lkp.lookup_type=lkv.lookup_type
366 AND lkv.lookup_code = ai.invoice_type_lookup_code
367 AND ai.vendor_id = vendor_rec.vendor_id
368 AND ai.vendor_site_id = vendor_rec.vendor_site_id
369 AND trunc(ai.invoice_date) <= trunc(p_as_of_date)
370 AND ai.invoice_currency_code = nvl(currency_rec.invoice_currency_code,
371 ai.invoice_currency_code) --bug6800315
372 AND ai.set_of_books_id = gl.ledger_id --bug9050332
373 AND ai.invoice_id=aid1.invoice_id
374 AND aid1.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'ERV',
375 'TIPV', 'TERV', 'TRV',
376 'REC_TAX', 'NONREC_TAX', 'TAX') -- bug13411905
377 AND NVL(aid1.reversal_flag,'N') <> 'Y' --bug6500253/6800315
378 AND pd.prepay_distribution_id (+) = aid1.invoice_distribution_id
379 GROUP BY ai.invoice_id,
380 ai.invoice_num,
381 ai.invoice_date ,
382 ai.invoice_currency_code,
383 ai.amount_paid,
384 aid1.invoice_distribution_id,
385 nvl(aid1.amount, 0),
386 gl.currency_code,
387 lkv.meaning
388 ) pp_inv
389 WHERE pp_inv.dist_total >= NVL(p_min_invoice_balance,0)--bug6800315
390 AND pp_inv.dist_total+pp_inv.appl_dist_total > 0 -- added bug 13411905
391 ;
392
393 END IF;--p_include_prepayments bug6800315
394
395 END LOOP; --currency_rec loop
396
397 EXCEPTION
398 WHEN NO_DATA_FOUND THEN
399 NULL;
400 WHEN OTHERS THEN
401 --APP_EXCEPTION.RAISE_EXCEPTION;
402 NULL;
403
404 END; --#3 End Currency Block
405
406 END LOOP; --vendor_rec loop
407
408 EXCEPTION
409 WHEN NO_DATA_FOUND THEN
410 NULL;
411 WHEN OTHERS THEN
412 APP_EXCEPTION.RAISE_EXCEPTION;
413 END; --#2 End Vendor Block
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 APP_EXCEPTION.RAISE_EXCEPTION;
418 END AP_GET_SUPPLIER_BALANCE; --#1
419
420 END AP_GET_SUPPLIER_BALANCE_PKG;