[Home] [Help]
PACKAGE BODY: APPS.POS_AP_INVOICES_PKG
Source
1 PACKAGE BODY POS_AP_INVOICES_PKG AS
2 /* $Header: POSAPINB.pls 120.13 2006/09/28 00:25:29 abtrived noship $ */
3
4
5 -----------------------------------------------------------------------
6 -- Function get_po_number_list returns all the PO Numbers matched to
7 -- this invoice (comma delimited) or NULL if not matched.
8 --
9 FUNCTION get_po_number_list(l_invoice_id IN NUMBER)
10 RETURN VARCHAR2
11 IS
12 po_number VARCHAR2(20);
13 po_number_list VARCHAR2(4000) := NULL;
14 po_number_list2 VARCHAR2(4000) := NULL;
15
16
17 ---------------------------------------------------------------------
18 -- Declare cursor to retrieve the PO number
19 --
20 --togeorge 11/15/2000
21 --changed org specific views to _all tables
22 CURSOR po_number_cursor IS
23 SELECT DISTINCT ph.segment1
24 FROM ap_invoice_distributions_all aid,
25 po_distributions_all pd,
26 po_headers_all ph
27 WHERE aid.invoice_id = l_invoice_id
28 AND aid.po_distribution_id = pd.po_distribution_id
29 AND pd.po_header_id = ph.po_header_id
30 AND ph.type_lookup_code = 'STANDARD'
31 UNION ALL
32 SELECT DISTINCT (ph.segment1||'-'||pr.release_num)
33 FROM ap_invoice_distributions_all aid,
34 po_distributions_all pd,
35 po_headers_all ph,
36 po_releases_all pr
37 WHERE aid.invoice_id = l_invoice_id
38 AND aid.po_distribution_id = pd.po_distribution_id
39 AND pr.po_release_id = pd.po_release_id
40 AND ph.po_header_id = pr.po_header_id
41 AND ph.type_lookup_code = 'BLANKET';
42
43 BEGIN
44
45 OPEN po_number_cursor;
46
47 LOOP
48 FETCH po_number_cursor INTO po_number;
49 EXIT WHEN po_number_cursor%NOTFOUND;
50
51 IF (po_number_list IS NOT NULL) THEN
52 po_number_list := po_number_list || ', ';
53 END IF;
54
55 po_number_list := po_number_list || po_number;
56 po_number_list2 := po_number_list;
57
58 END LOOP;
59
60 CLOSE po_number_cursor;
61
62 RETURN(po_number_list);
63
64 EXCEPTION WHEN OTHERS THEN
65
66 RETURN(po_number_list2); /* for overflow conditions */
67
68
69 END get_po_number_list;
70
71
72 FUNCTION get_packing_slip_list(l_invoice_id IN NUMBER,
73 p_invoice_num IN VARCHAR2 )
74 RETURN VARCHAR2
75 IS
76 packing_slip VARCHAR2(20);
77 packing_slip_list VARCHAR2(4000) := NULL;
78 packing_slip_list2 VARCHAR2(4000) := NULL;
79
80 ---------------------------------------------------------------------
81
82 CURSOR packing_slip_cursor IS
83 select DISTINCT RSH.PACKING_SLIP packing_slip
84 FROM ap_invoice_distributions_all aid,
85 po_distributions_all pd,
86 rcv_shipment_headers rsh,
87 rcv_shipment_lines rsl
88 WHERE aid.invoice_id = l_invoice_id
89 AND aid.po_distribution_id = pd.po_distribution_id
90 AND pd.LINE_LOCATION_ID = rsl.po_line_location_id
91 AND rsl.shipment_header_id = rsh.shipment_header_id
92 AND rsh.packing_slip is not null
93 union
94 select DISTINCT RSL.PACKING_SLIP packing_slip
95 FROM ap_invoice_distributions_all aid,
96 po_distributions_all pd,
97 rcv_shipment_headers rsh,
98 rcv_shipment_lines rsl
99 WHERE aid.invoice_id = l_invoice_id
100 AND aid.po_distribution_id = pd.po_distribution_id
101 AND pd.LINE_LOCATION_ID = rsl.po_line_location_id
102 AND rsl.shipment_header_id = rsh.shipment_header_id
103 AND rsl.packing_slip is not null;
104
105
106 BEGIN
107 OPEN packing_slip_cursor;
108
109 LOOP
110 FETCH packing_slip_cursor INTO packing_slip;
111 EXIT WHEN packing_slip_cursor%NOTFOUND;
112
113 IF (packing_slip_list IS NOT NULL) THEN
114 packing_slip_list := packing_slip_list || ', ';
115 END IF;
116
117 packing_slip_list := packing_slip_list || packing_slip;
118 packing_slip_list2 := packing_slip_list;
119
120 END LOOP;
121
122 CLOSE packing_slip_cursor;
123
124
125 RETURN(packing_slip_list);
126
127 EXCEPTION WHEN OTHERS THEN
128
129 RETURN(packing_slip_list2); /* for overflow conditions */
130
131
132 END get_packing_slip_list;
133
134
135 FUNCTION get_packing_slip(l_invoice_id IN NUMBER,
136 p_invoice_num IN VARCHAR2 )
137 RETURN VARCHAR2
138 IS
139 packing_slip VARCHAR2(20);
140 packing_slip1 VARCHAR2(20);
141 packing_slip2 VARCHAR2(20);
142 ---------------------------------------------------------------------
143
144 CURSOR packing_slip_cursor IS
145 select DISTINCT RSH.PACKING_SLIP packing_slip
146 FROM ap_invoice_distributions_all aid,
147 po_distributions_all pd,
148 rcv_shipment_headers rsh,
149 rcv_shipment_lines rsl
150 WHERE aid.invoice_id = l_invoice_id
151 AND aid.po_distribution_id = pd.po_distribution_id
152 AND pd.LINE_LOCATION_ID = rsl.po_line_location_id
153 AND rsl.shipment_header_id = rsh.shipment_header_id
154 AND rsh.packing_slip is not null
155 union
156 select DISTINCT RSL.PACKING_SLIP packing_slip
157 FROM ap_invoice_distributions_all aid,
158 po_distributions_all pd,
159 rcv_shipment_headers rsh,
160 rcv_shipment_lines rsl
161 WHERE aid.invoice_id = l_invoice_id
162 AND aid.po_distribution_id = pd.po_distribution_id
163 AND pd.LINE_LOCATION_ID = rsl.po_line_location_id
164 AND rsl.shipment_header_id = rsh.shipment_header_id
165 AND rsl.packing_slip is not null;
166
167 BEGIN
168
169 packing_slip := ' ';
170
171 OPEN packing_slip_cursor;
172
173 FETCH packing_slip_cursor INTO packing_slip1;
174 if (packing_slip_cursor%NOTFOUND) then
175 --no packing slip
176 packing_slip := ' ';
177 else
178 --atleast one
179 FETCH packing_slip_cursor INTO packing_slip2;
180 if (packing_slip_cursor%NOTFOUND) then
181 --single
182 packing_slip := packing_slip1;
183 else
184 --multiple
185 packing_slip := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
186 end if;
187 end if;
188
189 CLOSE packing_slip_cursor;
190
191
192 RETURN(packing_slip);
193
194 EXCEPTION WHEN OTHERS THEN
195
196 RETURN(packing_slip);
197
198
199 END get_packing_slip;
200
201
202 -------------------------------------------------------------------------------------
203 -- Function to return due date for scheduled payments for an invoice
204 -- Returns:
205 -- due date if single scheduled payment/ 'Multiple' if multiple scheduled payments
206 -- @ABTRIVED
207 -------------------------------------------------------------------------------------
208 FUNCTION get_due_date(l_invoice_id IN NUMBER)
209 RETURN VARCHAR2 IS
210
211 CURSOR scheduled_payment_date_cursor IS
212 SELECT due_date
213 FROM AP_PAYMENT_SCHEDULES_ALL
214 WHERE invoice_id = l_invoice_id;
215
216 due_date1 date;
217 due_date2 date;
218 due_Date VARCHAR2(255);
219
220 BEGIN
221
222 OPEN scheduled_payment_date_cursor;
223
224 fetch scheduled_payment_date_cursor into due_date1;
225 if (scheduled_payment_date_cursor%NOTFOUND) then
226 due_date := '';
227 else
228 fetch scheduled_payment_date_cursor into due_date2;
229 if (scheduled_payment_date_cursor%NOTFOUND) then
230
231 if(fnd_timezones.timezones_enabled()='Y') then
232 fnd_date_tz.init_timezones_for_fnd_date(true);
233 due_date := fnd_date.date_to_displayDT(due_date1);
234 else
235 due_date := to_char(due_date1,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'));
236 end if;
237
238 else
239 due_date := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
240 end if;
241 end if;
242
243 CLOSE scheduled_payment_date_cursor;
244
245 Return(due_date);
246
247 EXCEPTION WHEN OTHERS THEN
248
249 Return ('');
250
251 END get_due_date;
252
253
254 --------------------------------------------------------------------------------------
255 -- Procedure for hold status of an invoice
256 -- Returns:
257 -- p_hold_status returns hold_status = Y/N
258 -- hold_reason = '<latest hold name>' if status is on hold, else ''
259 -- @ABTRIVED
260 --------------------------------------------------------------------------------------
261 PROCEDURE get_on_hold_info(l_invoice_id IN NUMBER,
262 p_hold_status OUT NOCOPY VARCHAR2,
263 p_hold_reason OUT NOCOPY VARCHAR2)
264 IS
265
266 CURSOR hold_reason_cursor IS
267 SELECT alc.displayed_field
268 from ap_holds_All aha,
269 ap_lookup_codes alc
270 where alc.lookup_type = 'HOLD CODE'
271 and aha.hold_lookup_code = alc.lookup_code (+)
272 and aha.invoice_id = l_invoice_id
273 and aha.release_lookup_code is null
274 order by aha.creation_date desc;
275
276 BEGIN
277
278 OPEN hold_reason_cursor;
279
280 FETCH hold_reason_cursor INTO p_hold_reason;
281
282 --bug 4583483
283 --Removing Yes/No from hold_reason
284 if (p_hold_reason is not null) then
285 --p_hold_reason := FND_MESSAGE.GET_STRING('POS','POS_YES') ||' - '|| p_hold_reason;
286 p_hold_status := 'Y';
287 else
288 --p_hold_reason := FND_MESSAGE.GET_STRING('POS','POS_NO');
289 p_hold_reason := '';
290 p_hold_status := 'N';
291 end if;
292
293
294 CLOSE hold_reason_cursor;
295
296
297 EXCEPTION WHEN OTHERS THEN
298
299 p_hold_status := 'E';
300 p_hold_reason := '';
301
302 END get_on_hold_info;
303
304
305 ----------------------------------------------------------------------
306 -- Procedure to return Receipt information associated with an invoice
307 -- Returns:
308 -- p_receipt_Switch: S/M/N/E Rcv_Single/Rcv_Multiple/Rcv_No/Exception
309 -- p_receipt_num: <RECEIPT_NUM>
310 -- p_receipt_id: <RECEIPT_ID>
311 -- @ABTRIVED
312 ----------------------------------------------------------------------
313 PROCEDURE get_receipt_info(l_invoice_id IN NUMBER,
314 p_receipt_switch OUT NOCOPY VARCHAR2,
315 p_receipt_num OUT NOCOPY VARCHAR2,
316 p_receipt_shipment_header_id OUT NOCOPY VARCHAR2)
317
318 IS
319
320 receipt_num2 varchar2(30);
321 receipt_shipment_header_id2 varchar2(30);
322
323 ---cursor
324 cursor receipt_info_cursor is
325 --using lines for matching
326 select distinct rsh.receipt_num, rsh.SHIPMENT_HEADER_ID
327 from ap_invoice_lines_all al,
328 rcv_transactions rt,
329 rcv_shipment_headers rsh
330 where al.invoice_id = l_invoice_id
331 and al.rcv_transaction_id = rt.transaction_id
332 and rt.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID;
333
334 --using distributions for matching
335 /* SELECT distinct rsh.SHIPMENT_HEADER_ID, rsh.receipt_num
336 FROM ap_invoice_distributions_all aid,
337 rcv_transactions rt,
338 rcv_shipment_headers rsh
339 WHERE aid.invoice_id = l_invoice_id
340 and aid.rcv_transaction_id = rt.transaction_id
341 and rt.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID;
342 */
343
344 BEGIN
345
346 OPEN receipt_info_cursor;
347
348 FETCH receipt_info_cursor INTO p_receipt_num, p_receipt_shipment_header_id;
349 if (receipt_info_cursor%NOTFOUND) then
350 -- no receipts
351 p_receipt_switch := 'N';
352 else
353 --atleast one po
354 FETCH receipt_info_cursor INTO receipt_num2, receipt_shipment_header_id2;
355 if (receipt_info_cursor%NOTFOUND) then
356 --exactly one receipt
357 p_receipt_switch := 'S';
358 else
359 -- multiple receipts
360 p_receipt_switch := 'M';
361 p_receipt_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
362 end if;
363 end if;
364
365 CLOSE receipt_info_cursor;
366
367 EXCEPTION WHEN OTHERS THEN
368
369 p_receipt_switch := 'E';
370
371 END get_receipt_info;
372
373
374 ----------------------------------------------------------------------
375 -- Procedure to return PO information associated with an invoice
376 -- Returns:
377 -- p_po_switch: S/M/N/E (Single/Multiple/No/Exception)
378 -- p_po_num: <PO_NUM>
379 -- p_header_id: <PO_HEADER_ID>
380 -- p_release_id: <PO_RELEASE_ID>
381 -- @ABTRIVED
382 ----------------------------------------------------------------------
383 PROCEDURE get_po_info(l_invoice_id IN NUMBER,
384 p_po_switch OUT NOCOPY VARCHAR2,
385 p_po_num OUT NOCOPY VARCHAR2,
386 p_header_id OUT NOCOPY VARCHAR2,
387 p_release_id OUT NOCOPY VARCHAR2)
388 IS
389
390
391 po_num2 VARCHAR2(41);
392 header_id2 VARCHAR2(40);
393 release_id2 VARCHAR2(40);
394
395 ---------------------------------------------------------------------
396 -- Declare cursor to retrieve po_numbers, header id's and release id's
397 CURSOR po_info_cursor IS
398 --using invoice lines
399 select distinct ph.segment1, ph.po_header_id, null
400 from ap_invoice_lines_all ail,
401 po_headers_all ph
402 where ail.invoice_id = l_invoice_id and
403 ail.po_release_id is null and
404 ph.po_header_id = ail.po_header_id and
405 ph.type_lookup_code = 'STANDARD'
406 union all
407 select distinct (ph.segment1||'-'||pr.release_num), ph.po_header_id, pr.po_release_id
408 from ap_invoice_lines_all ail,
409 po_headers_all ph,
410 po_releases_all pr
411 WHERE ail.invoice_id = l_invoice_id and
412 pr.po_release_id = ail.po_release_id and
413 ph.po_header_id = ail.po_header_id and
414 ph.type_lookup_code = 'BLANKET' ;
415
416 --using invoice distributions
417 /* SELECT DISTINCT ph.segment1,ph.po_header_id,null
418 FROM ap_invoice_distributions_all aid,
419 po_distributions_all pd,
420 po_headers_all ph
421 WHERE aid.invoice_id = l_invoice_id
422 AND aid.po_distribution_id = pd.po_distribution_id
423 AND pd.po_header_id = ph.po_header_id
424 AND ph.type_lookup_code = 'STANDARD'
425 UNION ALL
426 SELECT DISTINCT (ph.segment1||'-'||pr.release_num),ph.po_header_id, pr.po_release_id
427 FROM ap_invoice_distributions_all aid,
428 po_distributions_all pd,
429 po_headers_all ph,
430 po_releases_all pr
431 WHERE aid.invoice_id = l_invoice_id
432 AND aid.po_distribution_id = pd.po_distribution_id
433 AND pr.po_release_id = pd.po_release_id
434 AND ph.po_header_id = pr.po_header_id
435 AND ph.type_lookup_code = 'BLANKET';
436 */
437
438
439 BEGIN
440
441 p_po_num := '';
442 p_header_id := '';
443 p_release_id := '';
444
445 OPEN po_info_cursor;
446 FETCH po_info_cursor INTO p_po_num, p_header_id, p_release_id;
447 if (po_info_cursor%NOTFOUND) then
448 -- no po's
449 p_po_switch := 'N';
450 else
451 --atleast one po
452 FETCH po_info_cursor INTO po_num2, header_id2, release_id2;
453 if (po_info_cursor%NOTFOUND) then
454 --exactly one PO
455 p_po_switch := 'S';
456 else
457 -- multiple PO's
458 p_po_switch := 'M';
459 p_po_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
460 end if;
461 end if;
462 CLOSE po_info_cursor;
463
464 EXCEPTION WHEN OTHERS THEN
465 p_po_switch := 'E';
466
467 END get_po_info;
468
469 -----------------------------------------------------------------------
470 -- Function get_amount_withheld returns the AWT withheld amount on
471 -- an invoice.
472 --
473 FUNCTION get_amount_withheld(l_invoice_id IN NUMBER)
474 RETURN NUMBER
475 IS
476 amount_withheld NUMBER := 0;
477 BEGIN
478
479 --togeorge 11/15/2000
480 --changed org specific views to _all tables
481 select (0 - sum(nvl(amount,0)))
482 into amount_withheld
483 from ap_invoice_distributions_all --ap_invoice_distributions
484 where invoice_id = l_invoice_id
485 and line_type_lookup_code = 'AWT';
486
487 return(amount_withheld);
488
489 END get_amount_withheld;
490
491
492 ----------------------------------------------------------------------------------
493 -- Function to get retainage amount for an invoice
494 -- Sum of Retainage Release Lines and Retainage Distributions
495 -- bug 4952468
496 ----------------------------------------------------------------------------------
497 FUNCTION get_retainage_for_invoice(l_invoice_id IN NUMBER)
498 RETURN NUMBER
499 IS
500 retainage_amount1 NUMBER := 0; --Retainage amount from lines
501 retainage_amount2 NUMBER := 0; --Retainage amount from distributions
502 BEGIN
503
504 select nvl(sum(amount),0)
505 INTO retainage_amount1
506 from ap_invoice_lines_All
507 where invoice_id = l_invoice_id
508 and LINE_TYPE_LOOKUP_CODE = 'RETAINAGE RELEASE';
509
510 SELECT nvl(sum(amount),0)
511 INTO retainage_amount2
512 FROM ap_invoice_distributions_all aid
513 WHERE aid.invoice_id = l_invoice_id
514 AND aid.line_type_lookup_code = 'RETAINAGE'
515 AND EXISTS
516 (SELECT 'X' FROM ap_invoice_lines_all ail
517 WHERE ail.invoice_id = l_invoice_id
518 AND ail.line_number = aid.invoice_line_number
519 AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE'
520 );
521
522 return(retainage_amount1 + retainage_amount2);
523
524 END;
525
526 ----------------------------------------------------------------------------------
527 -- Function to get prepayment amount for an invoice
528 -- Sum of Prepay Lines and Prepay Distributions
529 -- bug 5441740
530 ----------------------------------------------------------------------------------
531 FUNCTION get_prepay_for_invoice(l_invoice_id IN NUMBER)
532 RETURN NUMBER
533 IS
534 prepay_amount1 NUMBER := 0; --Prepayment amount from lines
535 prepay_amount2 NUMBER := 0; --Prepayment amount from distributions
536 BEGIN
537
538 select nvl(sum(amount),0)
539 INTO prepay_amount1
540 from ap_invoice_lines_All
541 where invoice_id = l_invoice_id
542 and LINE_TYPE_LOOKUP_CODE = 'PREPAY';
543
544 SELECT nvl(sum(amount),0)
545 INTO prepay_amount2
546 FROM ap_invoice_distributions_all aid
547 WHERE aid.invoice_id = l_invoice_id
548 AND aid.line_type_lookup_code = 'PREPAY'
549 AND EXISTS
550 (SELECT 'X' FROM ap_invoice_lines_all ail
551 WHERE ail.invoice_id = l_invoice_id
552 AND ail.line_number = aid.invoice_line_number
553 AND ail.line_type_lookup_code <> 'PREPAY'
554 );
555
556 return(prepay_amount1 + prepay_amount2);
557
558 END;
559
560 ----------------------------------------------------------------------------------
561 -- Function to get tax amount for an invoice
562 -- Sum of Lines amount for lines of type tax
563 -- bug 5569244
564 ----------------------------------------------------------------------------------
565 FUNCTION get_tax_for_invoice(l_invoice_id IN NUMBER)
566 RETURN NUMBER
567 IS
568 amount1 NUMBER := 0; -- tax amount from lines
569 BEGIN
570
571 select nvl(sum(amount),0)
572 INTO amount1
573 from ap_invoice_lines_All
574 where invoice_id = l_invoice_id
575 and LINE_TYPE_LOOKUP_CODE = 'TAX' ;
576
577 return(amount1);
578
579 END;
580
581 ----------------------------------------------------------------------------------
582 -- Function to get total amount for an invoice including retainage
583 -- Sum of Lines amount and Retainage Distributions and Prepayment distributions
584 -- bug 4952468, 5441740
585 ----------------------------------------------------------------------------------
586 FUNCTION get_total_for_invoice(l_invoice_id IN NUMBER)
587 RETURN NUMBER
588 IS
589 amount1 NUMBER := 0; --amount from lines
590 retainage_amount2 NUMBER := 0; --Retainage amount from distributions
591 prepay_amount2 NUMBER := 0; --Prepayment amount from distributions
592 BEGIN
593
594 select nvl(sum(amount),0)
595 INTO amount1
596 from ap_invoice_lines_All
597 where invoice_id = l_invoice_id;
598
599 SELECT nvl(sum(amount),0)
600 INTO retainage_amount2
601 FROM ap_invoice_distributions_all aid
602 WHERE aid.invoice_id = l_invoice_id
603 AND aid.line_type_lookup_code = 'RETAINAGE'
604 AND EXISTS
605 (SELECT 'X' FROM ap_invoice_lines_all ail
606 WHERE ail.invoice_id = l_invoice_id
607 AND ail.line_number = aid.invoice_line_number
608 AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE'
609 );
610
611 SELECT nvl(sum(amount),0)
612 INTO prepay_amount2
613 FROM ap_invoice_distributions_all aid
614 WHERE aid.invoice_id = l_invoice_id
615 AND aid.line_type_lookup_code = 'PREPAY'
616 AND EXISTS
617 (SELECT 'X' FROM ap_invoice_lines_all ail
618 WHERE ail.invoice_id = l_invoice_id
619 AND ail.line_number = aid.invoice_line_number
620 AND ail.line_type_lookup_code <> 'PREPAY'
621 );
622 return(amount1 + retainage_amount2 + prepay_amount2);
623
624 END;
625
626 ----------------------------------------------------------------------------------
627 -- Function to get list of concated payment numbers for an invoice
628 -- Replacement for POS_AP_INVOICE_PAYMENTS_PKG.GET_PAID_BY_LIST
629 -- @ABTRIVED
630 ----------------------------------------------------------------------------------
631 FUNCTION get_payment_list(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
632 RETURN VARCHAR2
633 IS
634
635 pay_num varchar2(20);
636 pay_id varchar2(20);
637 pay_date1 date;
638 l_paid_by_list VARCHAR2(2000) := NULL;
639 l_paid_by_list2 VARCHAR2(2000) := NULL;
640
641 cursor payment_cursor_new is
642 SELECT ac.check_number,
643 ac.check_id,
644 ac.check_Date
645 FROM ap_invoice_payments_all aip,
646 ap_checks_all ac
647 WHERE aip.invoice_id = l_invoice_id
648 AND aip.check_id = ac.check_id;
649
650 BEGIN
651
652 OPEN payment_cursor_new;
653
654 LOOP
655 FETCH payment_cursor_new INTO pay_num, pay_id, pay_date1;
656 EXIT WHEN payment_cursor_new%NOTFOUND;
657
658 IF (l_paid_by_list IS NOT NULL) THEN
659 l_paid_by_list := l_paid_by_list || ', ';
660 END IF;
661
662 l_paid_by_list := l_paid_by_list || pay_num;
663 l_paid_by_list2 := l_paid_by_list;
664
665 END LOOP;
666
667 CLOSE payment_cursor_new;
668
669 RETURN(l_paid_by_list);
670
671 EXCEPTION WHEN OTHERS THEN
672
673 RETURN(l_paid_by_list2); /* for overflow conditions */
674
675 END get_payment_list;
676
677
678 /*deprecated - should be replaced by method with same name and which passes p_payment_method also*/
679 ----------------------------------------------------------------------------------
680 -- Function to get payments numbers for an invoice
681 -- Return parameters:
682 -- p_payment_switch is N - no payments, S - single payment,
683 -- M - Multiple payment
684 -- p_payment_num is payment number of the payment if only one payment, else null
685 -- p_payment_id is check_id if only one payment, else null
686 -- @ABTRIVED
687 ----------------------------------------------------------------------------------
688 PROCEDURE get_payment_info(l_invoice_id IN NUMBER,
689 p_payment_switch OUT NOCOPY VARCHAR2,
690 p_payment_num OUT NOCOPY VARCHAR2,
691 p_payment_id OUT NOCOPY VARCHAR2,
692 p_payment_date OUT NOCOPY VARCHAR2
693 )
694 /*deprecated - should be replaced by method with same name and which passes p_payment_method also*/
695
696 IS
697
698 pay_num varchar2(20);
699 pay_id varchar2(20);
700 -- pay_type varchar2(255);
701 pay_date1 date;
702 pay_date2 date;
703
704 cursor payment_cursor_new is
705 SELECT distinct ac.check_number,
706 ac.check_id,
707 ac.check_Date
708 --, alc2.displayed_field
709 FROM ap_invoice_payments_all aip,
710 ap_checks_all ac
711 --, ap_lookup_codes alc2
712 WHERE aip.invoice_id = l_invoice_id
713 AND aip.check_id = ac.check_id;
714 -- AND alc2.lookup_type = 'PAYMENT METHOD'
715 -- AND alc2.lookup_code = ac.payment_method_lookup_code;
716
717 /*deprecated - should be replaced by method with same name and which passes p_payment_method also*/
718 BEGIN
719
720 p_payment_switch := 'N';
721 p_payment_num := '';
722 p_payment_id := '';
723 --p_payment_date := '';
724
725 OPEN payment_cursor_new;
726
727 FETCH payment_cursor_new INTO p_payment_num, p_payment_id, pay_date1;--, pay_type;
728 if (payment_cursor_new%NOTFOUND) then
729 --no payments
730 p_payment_switch := 'N';
731 else
732 --atleast one payment
733 FETCH payment_cursor_new INTO pay_num, pay_id, pay_date2;--, pay_type;
734 if (payment_cursor_new%NOTFOUND) then
735 --just one payment
736 p_payment_switch := 'S';
737
738 if(fnd_timezones.timezones_enabled()='Y') then
739 fnd_date_tz.init_timezones_for_fnd_date(true);
740 p_payment_date := fnd_date.date_to_displayDT(pay_date1);
741 else
742 p_payment_date := to_char(pay_date1,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'));
743 end if;
744
745 else
746 p_payment_switch := 'M';
747 p_payment_date := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
748 p_payment_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
749 end if;
750 end if;
751
752 CLOSE payment_cursor_new;
753
754 EXCEPTION WHEN OTHERS THEN
755
756 p_payment_switch := 'E';
757
758 END get_payment_info;
759 /*deprecated - should be replaced by method with same name and which passes p_payment_method also*/
760
761
762
763 ----------------------------------------------------------------------------------
764 -- Function to get payments numbers for an invoice
765 -- Return parameters:
766 -- p_payment_switch is N - no payments, S - single payment,
767 -- M - Multiple payment
768 -- p_payment_num is payment number of the payment if only one payment, else null
769 -- p_payment_id is check_id if only one payment, else null
770 -- @ABTRIVED
771 ----------------------------------------------------------------------------------
772 PROCEDURE get_payment_info(l_invoice_id IN NUMBER,
773 p_payment_switch OUT NOCOPY VARCHAR2,
774 p_payment_num OUT NOCOPY VARCHAR2,
775 p_payment_id OUT NOCOPY VARCHAR2,
776 p_payment_date OUT NOCOPY VARCHAR2,
777 p_payment_method OUT NOCOPY VARCHAR2
778 )
779
780 IS
781
782 pay_num varchar2(20);
783 pay_id varchar2(20);
784 pay_type varchar2(255);
785 pay_date1 date;
786 pay_date2 date;
787
788 cursor payment_cursor_new is
789 SELECT distinct ac.check_number,
790 ac.check_id,
791 -- ac.check_Date,
792 alc2.displayed_field
793 FROM ap_invoice_payments_all aip,
794 ap_checks_all ac,
795 ap_lookup_codes alc2
796 WHERE aip.invoice_id = l_invoice_id
797 AND aip.check_id = ac.check_id
798 AND alc2.lookup_type(+) = 'PAYMENT METHOD'
799 AND alc2.lookup_code(+) = ac.payment_method_lookup_code;
800
801 cursor paymentdate_cursor_new is
802 SELECT distinct ac.check_Date
803 FROM ap_invoice_payments_all aip,
804 ap_checks_all ac
805 WHERE aip.invoice_id = l_invoice_id
806 AND aip.check_id = ac.check_id;
807
808
809 BEGIN
810
811 p_payment_switch := 'N';
812 p_payment_num := '';
813 p_payment_id := '';
814 --p_payment_date := '';
815
816 OPEN payment_cursor_new;
817
818 FETCH payment_cursor_new INTO p_payment_num, p_payment_id, p_payment_method;
819 if (payment_cursor_new%NOTFOUND) then
820 --no payments
821 p_payment_switch := 'N';
822 else
823 --atleast one payment
824 FETCH payment_cursor_new INTO pay_num, pay_id, pay_type;
825 if (payment_cursor_new%NOTFOUND) then
826 --just one payment
827 p_payment_switch := 'S';
828 else
829 p_payment_switch := 'M';
830 p_payment_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
831 p_payment_method := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
832 end if;
833 end if;
834
835 CLOSE payment_cursor_new;
836
837 OPEN paymentdate_cursor_new;
838
839 FETCH paymentdate_cursor_new INTO pay_date1;
840 if (paymentdate_cursor_new%NOTFOUND) then
841 --no payments
842 p_payment_date := '';
843 else
844 --atleast one payment
845 FETCH paymentdate_cursor_new INTO pay_date2;
846 if (paymentdate_cursor_new%NOTFOUND) then
847 --just one payment
848 if(fnd_timezones.timezones_enabled()='Y') then
849 fnd_date_tz.init_timezones_for_fnd_date(true);
850 p_payment_date := fnd_date.date_to_displayDT(pay_date1);
851 else
852 p_payment_date := to_char(pay_date1,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'));
853 end if;
854 else
855 p_payment_date := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
856 end if;
857 end if;
858
859 CLOSE paymentdate_cursor_new;
860
861 EXCEPTION WHEN OTHERS THEN
862
863 p_payment_switch := 'E';
864
865 END get_payment_info;
866
867
868 FUNCTION get_on_hold_status(l_invoice_id IN NUMBER)
869 RETURN VARCHAR2 IS
870
871 l_status VARCHAR2(60) := NULL;
872 l_count NUMBER;
873
874 BEGIN
875
876 select count(*)
877 into l_count
878 from ap_holds_all
879 where invoice_id = l_invoice_id
880 and release_lookup_code is null;
881
882 if (l_count > 0) then
883
884 l_status := fnd_message.get_string('POS', 'POS_ON_HOLD');
885
886 end if;
887
888 RETURN l_status;
889
890 EXCEPTION WHEN OTHERS THEN
891
892 RETURN l_status;
893
894 END get_on_hold_status;
895
896
897 END POS_AP_INVOICES_PKG;