1 PACKAGE BODY AP_WEB_PAYMENTS_PKG AS
2 /* $Header: apwxpayb.pls 120.5.12010000.2 2008/08/06 10:19:25 rveliche ship $ */
3
4 -----------------------------------------------------------------------
5 -- Declare record type used to hold records returned from generic cursor
6 --
7 TYPE PAYMENT_RECORD IS
8 RECORD (check_number ap_checks.check_number%TYPE,
9 check_id ap_checks.check_id%TYPE,
10 check_date ap_checks.check_date%TYPE,
11 invoice_num ap_invoices.invoice_num%TYPE,
12 invoice_id ap_invoices.invoice_id%TYPE,
13 gl_date ap_invoice_payments.accounting_date%TYPE,
14 type ap_invoice_payments.invoice_payment_type%TYPE,
15 method ap_checks.payment_method_lookup_code%TYPE,
16 method_trans ap_lookup_codes.displayed_field%TYPE);
17
18 -----------------------------------------------------------------------
19 -- Declare generic cursor to get check number, check id, invoice number
20 -- invoice id, payment type, payment method lookup code, and translated
21 -- payment method
22 --
23 CURSOR payment_cursor (l_invoice_id NUMBER, l_payment_num NUMBER)
24 RETURN payment_record IS
25 SELECT ac.check_number,
26 ac.check_id,
27 ac.check_date,
28 ai.invoice_num,
29 ai.invoice_id,
30 aip.accounting_date,
31 aip.invoice_payment_type,
32 ac.payment_method_lookup_code,
33 alc2.displayed_field
34 FROM ap_invoice_payments aip,
35 ap_checks ac,
36 ap_invoices ai,
37 ap_lookup_codes alc2
38 WHERE aip.invoice_id = l_invoice_id
39 AND aip.payment_num = l_payment_num
40 AND aip.check_id = ac.check_id
41 AND aip.other_invoice_id = ai.invoice_id (+)
42 AND alc2.lookup_type = 'PAYMENT METHOD'
43 AND alc2.lookup_code = ac.payment_method_code
44 UNION ALL
45 SELECT ac.check_number,
46 ac.check_id,
47 ac.check_date,
48 ai.invoice_num,
49 ai.invoice_id,
50 aip.accounting_date,
51 aip.invoice_payment_type,
52 ac.payment_method_lookup_code,
53 alc2.displayed_field
54 FROM ap_invoice_payments aip,
55 ap_checks ac,
56 ap_invoices ai,
57 ap_lookup_codes alc2,
58 ap_invoice_lines ail1,
59 ap_invoice_lines ail2,
60 ap_invoice_distributions aid1,
61 ap_invoice_distributions aid2
62 WHERE aip.invoice_id = ail2.invoice_id
63 AND ail1.invoice_id = l_invoice_id
64 AND aip.payment_num = l_payment_num
65 AND aid1.invoice_id = ail1.invoice_id
66 AND aid1.invoice_line_number = ail1.line_number
67 AND aid2.invoice_id = ail2.invoice_id
68 AND aid2.invoice_line_number = ail2.line_number
69 AND aip.check_id = ac.check_id
70 AND aip.other_invoice_id = ai.invoice_id (+)
71 AND alc2.lookup_type = 'PAYMENT METHOD'
72 AND alc2.lookup_code = ac.payment_method_code
73 AND (ail1.line_type_lookup_code = 'PREPAY' OR
74 ail1.line_type_lookup_code = 'TAX' AND
75 aid1.prepay_tax_parent_id IS NOT NULL
76 )
77 AND aid2.INVOICE_DISTRIBUTION_ID = aid1.prepay_distribution_id;
78
79 -----------------------------------------------------------------------
80 -- Function concat_document_num_type concatenates the document number
81 -- to the document type
82 --
83 FUNCTION concat_document_num_type(l_payment_record IN PAYMENT_RECORD)
84 RETURN VARCHAR2
85 IS
86 l_paid_by VARCHAR2(80);
87 type_trans ap_lookup_codes.displayed_field%TYPE;
88 BEGIN
89
90 IF (l_payment_record.type = 'PREPAY') THEN
91
92 SELECT displayed_field
93 INTO type_trans
94 FROM ap_lookup_codes
95 WHERE lookup_type = 'NLS TRANSLATION'
96 AND lookup_code = 'PREPAY';
97
98 -------------------------------------------------------
99 -- Get prepayment number concatenated to prepayment type
100 --
101 l_paid_by := type_trans ||' #'||
102 l_payment_record.invoice_num;
103 ELSE
104 -------------------------------------------------------
105 -- Get payment number concatenated to payment method type
106 --
107 l_paid_by := l_payment_record.method_trans ||' #'||
108 l_payment_record.check_number;
109 END IF;
110
111 RETURN(l_paid_by);
112
113 END concat_document_num_type;
114
115
116 -----------------------------------------------------------------------
117 -- Function get_paid_by_list returns a list of document numbers
118 -- concatenated to the document type used to pay this payment schedule
119 -- or NULL if unpaid.
120 --
121 FUNCTION get_paid_by_list(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
122 RETURN VARCHAR2
123 IS
124 l_paid_by VARCHAR2(80);
125 l_paid_by_list VARCHAR2(2000) := NULL;
126 l_payment_record PAYMENT_RECORD;
127
128 BEGIN
129
130 OPEN payment_cursor(l_invoice_id, l_payment_num);
131
132 LOOP
133 FETCH payment_cursor INTO l_payment_record;
134 EXIT WHEN payment_cursor%NOTFOUND;
135
136 l_paid_by := AP_WEB_PAYMENTS_PKG.CONCAT_DOCUMENT_NUM_TYPE(l_payment_record);
137
138 IF (l_paid_by_list IS NOT NULL) THEN
139 l_paid_by_list := l_paid_by_list || ', ';
140 END IF;
141
142 l_paid_by_list := l_paid_by_list || l_paid_by;
143
144 END LOOP;
145
146 l_paid_by_list := payment_cursor%ROWCOUNT||'*'||l_paid_by_list;
147
148 CLOSE payment_cursor;
149
150 RETURN(l_paid_by_list);
151
152 END get_paid_by_list;
153
154 -----------------------------------------------------------------------
155 -- Function get_paid_by_list returns a list of document numbers
156 -- concatenated to the document type used to pay this payment schedule
157 -- or NULL if unpaid.
158 --
159 FUNCTION get_total_payments_made(l_invoice_id IN NUMBER)
160 RETURN VARCHAR2
161 IS
162 l_total_payment_made VARCHAR2(80);
163
164 BEGIN
165 SELECT SUM(AIP.AMOUNT)
166 INTO l_total_payment_made
167 FROM AP_INVOICE_PAYMENTS AIP, AP_INVOICES AI
168 WHERE AIP.INVOICE_ID = AI.INVOICE_ID
169 AND AI.INVOICE_ID = l_invoice_id;
170
171 RETURN(l_total_payment_made);
172
173 EXCEPTION
174 WHEN no_data_found THEN
175 return(null);
176 WHEN OTHERS THEN
177 raise;
178 END get_total_payments_made;
179
180 -----------------------------------------------------------------------
181 -- Function get_checkid returns the first Check ID for a
182 -- given Invoice ID.
183 FUNCTION get_checkid(l_invoice_id IN NUMBER)
184 RETURN NUMBER
185 IS
186 l_check_id NUMBER;
187
188 BEGIN
189
190 SELECT check_id
191 INTO l_check_id
192 FROM
193 ( SELECT ac.check_id check_id
194 FROM ap_invoice_payments aip,
195 ap_checks ac
196 WHERE aip.check_id = ac.check_id
197 AND aip.invoice_id = l_invoice_id
198 UNION ALL
199 SELECT ac.check_id check_id
200 FROM ap_invoice_payments aip,
201 ap_checks ac,
202 ap_invoice_lines ail1,
203 ap_invoice_lines ail2,
204 ap_invoice_distributions aid1,
205 ap_invoice_distributions aid2
206 WHERE aip.check_id = ac.check_id
207 AND ail1.invoice_id = l_invoice_id
208 AND ail2.invoice_id = aip.invoice_id
209 AND aid1.invoice_id = ail1.invoice_id
210 AND aid1.invoice_line_number = ail1.line_number
211 AND aid2.invoice_id = ail2.invoice_id
212 AND aid2.invoice_line_number = ail2.line_number
213 AND (ail1.line_type_lookup_code = 'PREPAY' OR
214 ail1.line_type_lookup_code = 'TAX' AND
215 aid1.prepay_tax_parent_id IS NOT NULL
216 )
217 AND aid2.invoice_distribution_id = aid1.prepay_distribution_id
218 )
219 WHERE ROWNUM = 1;
220
221
222 RETURN(l_check_id);
223
224 EXCEPTION
225 WHEN no_data_found THEN
226 return(null);
227 WHEN OTHERS THEN
228 raise;
229 END get_checkid;
230
231 -----------------------------------------------------------------------
232 -- Function get_last_payment_date returns the Payment Date. If multiple
233 -- payments and advances are made for a particular invoice, then it
234 -- returns the most recent Payment Date
235 FUNCTION get_last_payment_date(l_invoice_id IN NUMBER)
236 RETURN DATE
237 IS
238 l_last_payment_date DATE;
239
240 BEGIN
241 SELECT MAX(check_date)
242 INTO l_last_payment_date
243 FROM
244 ( SELECT ac.check_date check_date
245 FROM ap_invoice_payments aip,
246 ap_checks ac
247 WHERE aip.check_id = ac.check_id
248 AND aip.invoice_id = l_invoice_id
249 UNION ALL
250 SELECT ac.check_date check_date
251 FROM ap_invoice_payments aip,
252 ap_checks ac,
253 ap_invoice_lines ail1,
254 ap_invoice_lines ail2,
255 ap_invoice_distributions aid1,
256 ap_invoice_distributions aid2
257 WHERE aip.check_id = ac.check_id
258 AND ail1.invoice_id = l_invoice_id
259 AND ail2.invoice_id = aip.invoice_id
260 AND aid1.invoice_id = ail1.invoice_id
261 AND aid1.invoice_line_number = ail1.line_number
262 AND aid2.invoice_id = ail2.invoice_id
263 AND aid2.invoice_line_number = ail2.line_number
264 AND (ail1.line_type_lookup_code = 'PREPAY' OR
265 ail1.line_type_lookup_code = 'TAX' AND
266 aid1.prepay_tax_parent_id IS NOT NULL
267 )
268 AND aid2.invoice_distribution_id = aid1.prepay_distribution_id
269 );
270
271
272 RETURN(l_last_payment_date);
273
274 EXCEPTION
275 WHEN no_data_found THEN
276 return(null);
277 WHEN OTHERS THEN
278 raise;
279 END get_last_payment_date;
280
281 FUNCTION get_prepay_amount_remaining(P_invoice_id IN number,
282 p_Invoice_num IN VARCHAR2,
283 p_employee_id IN NUMBER,
284 p_currency IN VARCHAR2,
285 P_header_id IN NUMBER DEFAULT NULL,
286 p_resp_id IN NUMBER DEFAULT NULL,
287 p_apps_id NUMBER DEFAULT NULL)
288 RETURN number
289 IS
290
291 l_prepay_amt_invoiced NUMBER;
292 l_prepay_amt_expensed NUMBER;
293 l_prepay_amt_available NUMBER;
294
295 BEGIN
296
297 BEGIN
298 IF (nvl(FND_PROFILE.VALUE_SPECIFIC('OIE_CARRY_ADVANCES_FORWARD',NULL,p_resp_id,p_apps_id),'Y') = 'Y' )THEN
299
300 SELECT SUM(nvl(prepay_amount_remaining,amount))
301 INTO l_prepay_amt_invoiced
302 FROM ap_invoice_distributions aid,ap_invoices ai
303 WHERE aid.invoice_id = P_invoice_id
304 AND aid.line_type_lookup_code IN ('ITEM','TAX')
305 AND nvl(aid.reversal_flag,'N') <> 'Y'
306 AND ai.invoice_id = P_invoice_id
307 AND ai.earliest_settlement_date IS NOT NULL
308 AND ai.earliest_settlement_date <= trunc(SYSDATE);
309
310
311 ELSE
312 SELECT SUM(nvl(prepay_amount_remaining,amount))
313 INTO l_prepay_amt_invoiced
314 FROM ap_invoice_distributions aid,ap_invoices ai
315 WHERE aid.invoice_id = P_invoice_id
316 AND aid.line_type_lookup_code IN ('ITEM','TAX')
317 AND nvl(aid.reversal_flag,'N') <> 'Y'
318 AND ai.invoice_id = P_invoice_id
319 AND ai.earliest_settlement_date IS NOT NULL
320 AND ai.earliest_settlement_date <= trunc(SYSDATE)
321 AND nvl(prepay_amount_remaining,amount) = aid.amount;
322
323 END IF;
324
325 EXCEPTION WHEN NO_DATA_FOUND THEN
326 l_prepay_amt_invoiced := 0;
327 END;
328
329 BEGIN
330
331 IF(P_header_id IS NULL) THEN
332
333 select nvl(sum(maximum_amount_to_apply),0)
334 INTO l_prepay_amt_expensed
335 from ap_expense_report_headers
336 where employee_id = p_employee_id
337 and vouchno = 0
338 and default_currency_code = p_currency
339 AND prepay_num = p_Invoice_num
340 AND advance_invoice_to_apply = P_invoice_id;
341
342 ELSE
343
344 select nvl(sum(maximum_amount_to_apply),0)
345 INTO l_prepay_amt_expensed
346 from ap_expense_report_headers
347 where employee_id = p_employee_id
348 and vouchno = 0
349 and default_currency_code = p_currency
350 AND prepay_num = p_Invoice_num
351 AND advance_invoice_to_apply = P_invoice_id
352 AND report_header_id <> P_header_id;
353
354 END IF;
355
356 EXCEPTION WHEN NO_DATA_FOUND THEN
357 l_prepay_amt_expensed :=0;
358 END ;
359
360 l_prepay_amt_available := l_prepay_amt_invoiced - l_prepay_amt_expensed;
361
362 IF l_prepay_amt_available < 0 OR (nvl(FND_PROFILE.VALUE_SPECIFIC('OIE_CARRY_ADVANCES_FORWARD',NULL,p_resp_id,p_apps_id),'Y') = 'N' AND l_prepay_amt_expensed > 0)THEN
363 l_prepay_amt_available := 0;
364 END IF;
365
366 RETURN l_prepay_amt_available;
367
368 EXCEPTION WHEN OTHERS THEN
369 RETURN 0;
370 END get_prepay_amount_remaining;
371
372 FUNCTION get_prepay_balance(P_invoice_id IN number,
373 p_Invoice_num IN VARCHAR2,
374 p_employee_id IN NUMBER,
375 p_currency IN VARCHAR2)
376 RETURN number
377 IS
378
379 l_prepay_amt_invoiced NUMBER;
380 l_prepay_amt_expensed NUMBER;
381 l_prepay_amt_available NUMBER;
382
383 BEGIN
384
385 BEGIN
386 SELECT SUM(NVL(prepay_amount_remaining,amount))
387 INTO l_prepay_amt_invoiced
388 FROM ap_invoice_distributions aid,ap_invoices ai
389 WHERE aid.invoice_id = P_invoice_id
390 AND aid.line_type_lookup_code IN ('ITEM','TAX')
391 AND NVL(aid.reversal_flag,'N') <> 'Y'
392 AND ai.invoice_id = P_invoice_id
393 AND ai.earliest_settlement_date IS NOT NULL
394 AND ai.earliest_settlement_date <= TRUNC(SYSDATE);
395
396
397 EXCEPTION WHEN NO_DATA_FOUND THEN
398 l_prepay_amt_invoiced := 0;
399 END;
400
401 BEGIN
402
403 SELECT NVL(SUM(maximum_amount_to_apply),0)
404 INTO l_prepay_amt_expensed
405 FROM ap_expense_report_headers
406 WHERE employee_id = p_employee_id
407 AND vouchno = 0
408 AND default_currency_code = p_currency
409 AND prepay_num = p_Invoice_num
410 AND advance_invoice_to_apply = P_invoice_id;
411
412 EXCEPTION WHEN NO_DATA_FOUND THEN
413 l_prepay_amt_expensed :=0;
414 END ;
415
416 l_prepay_amt_available := l_prepay_amt_invoiced - l_prepay_amt_expensed;
417
418 RETURN l_prepay_amt_available;
419
420 EXCEPTION WHEN OTHERS THEN
421 RETURN 0;
422 END get_prepay_balance;
423
424
425 FUNCTION get_line_prepay_balance(P_invoice_id IN number,
426 line_id IN NUMBER)
427 RETURN number
428 IS
429
430 l_prepay_amt_available NUMBER;
431
432 BEGIN
433
434 BEGIN
435 SELECT SUM(NVL(aid.prepay_amount_remaining,aid.amount))
436 INTO l_prepay_amt_available
437 FROM ap_invoice_distributions aid,ap_invoice_lines al
438 WHERE aid.invoice_id = al.invoice_id
439 AND aid.invoice_line_number= al.line_number
440 and al.line_number=line_id
441 AND aid.line_type_lookup_code IN ('ITEM','TAX')
442 AND NVL(aid.reversal_flag,'N') <> 'Y'
443 AND al.invoice_id = P_invoice_id;
444
445
446 END;
447
448
449 RETURN l_prepay_amt_available;
450
451 EXCEPTION WHEN OTHERS THEN
452 RETURN 0;
453 END get_line_prepay_balance;
454
455
456 END AP_WEB_PAYMENTS_PKG;
457
458
[Home] [Help]