1 PACKAGE BODY POS_AP_INVOICES_PKG AS
2 /* $Header: POSAPINB.pls 120.18 2011/08/03 11:41:09 nchundur ship $ */
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(100);
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.clm_document_number
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.clm_document_number||'-'||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 /* Calendar Support ,Passing calendar_aware=>1 ,It means the field always doesn't need Gregorian calendar */
234 due_date := fnd_date.date_to_displayDT(due_date1,calendar_aware=>1);
235 else
236 /* To show the due_date value according to the user profile calender(Eg : English Hijrah) */
237 due_date := fnd_date.date_to_displaydate(due_date1,calendar_aware=>1);
238 end if;
239
240 else
241 due_date := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
242 end if;
243 end if;
244
245 CLOSE scheduled_payment_date_cursor;
246
247 Return(due_date);
248
249 EXCEPTION WHEN OTHERS THEN
250
251 Return ('');
252
253 END get_due_date;
254
255
256 --------------------------------------------------------------------------------------
257 -- Procedure for hold status of an invoice
258 -- Returns:
259 -- p_hold_status returns hold_status = Y/N
260 -- hold_reason = '<latest hold name>' if status is on hold, else ''
261 -- @ABTRIVED
262 --------------------------------------------------------------------------------------
263 PROCEDURE get_on_hold_info(l_invoice_id IN NUMBER,
264 p_hold_status OUT NOCOPY VARCHAR2,
265 p_hold_reason OUT NOCOPY VARCHAR2)
266 IS
267
268 CURSOR hold_reason_cursor IS
269 SELECT alc.displayed_field
270 from ap_holds_All aha,
271 ap_lookup_codes alc
272 where alc.lookup_type = 'HOLD CODE'
273 and aha.hold_lookup_code = alc.lookup_code (+)
274 and aha.invoice_id = l_invoice_id
275 and aha.release_lookup_code is null
276 order by aha.creation_date desc;
277
278 BEGIN
279
280 OPEN hold_reason_cursor;
281
282 FETCH hold_reason_cursor INTO p_hold_reason;
283
284 --bug 4583483
285 --Removing Yes/No from hold_reason
286 if (p_hold_reason is not null) then
287 --p_hold_reason := FND_MESSAGE.GET_STRING('POS','POS_YES') ||' - '|| p_hold_reason;
288 p_hold_status := 'Y';
289 else
290 --p_hold_reason := FND_MESSAGE.GET_STRING('POS','POS_NO');
291 p_hold_reason := '';
292 p_hold_status := 'N';
293 end if;
294
295
296 CLOSE hold_reason_cursor;
297
298
299 EXCEPTION WHEN OTHERS THEN
300
301 p_hold_status := 'E';
302 p_hold_reason := '';
303
304 END get_on_hold_info;
305
306
307 ----------------------------------------------------------------------
308 -- Procedure to return Receipt information associated with an invoice
309 -- Returns:
310 -- p_receipt_Switch: S/M/N/E Rcv_Single/Rcv_Multiple/Rcv_No/Exception
311 -- p_receipt_num: <RECEIPT_NUM>
312 -- p_receipt_id: <RECEIPT_ID>
313 -- @ABTRIVED
314 ----------------------------------------------------------------------
315 PROCEDURE get_receipt_info(l_invoice_id IN NUMBER,
316 p_receipt_switch OUT NOCOPY VARCHAR2,
317 p_receipt_num OUT NOCOPY VARCHAR2,
318 p_receipt_shipment_header_id OUT NOCOPY VARCHAR2)
319
320 IS
321
322 receipt_num2 varchar2(30);
323 receipt_shipment_header_id2 varchar2(30);
324
325 ---cursor
326 cursor receipt_info_cursor is
327 --using lines for matching
328 select distinct rsh.receipt_num, rsh.SHIPMENT_HEADER_ID
329 from ap_invoice_lines_all al,
330 rcv_transactions rt,
331 rcv_shipment_headers rsh
332 where al.invoice_id = l_invoice_id
333 and al.rcv_transaction_id = rt.transaction_id
334 and rt.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID;
335
336 --using distributions for matching
337 /* SELECT distinct rsh.SHIPMENT_HEADER_ID, rsh.receipt_num
338 FROM ap_invoice_distributions_all aid,
339 rcv_transactions rt,
340 rcv_shipment_headers rsh
341 WHERE aid.invoice_id = l_invoice_id
342 and aid.rcv_transaction_id = rt.transaction_id
343 and rt.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID;
344 */
345
346 BEGIN
347
348 OPEN receipt_info_cursor;
349
350 FETCH receipt_info_cursor INTO p_receipt_num, p_receipt_shipment_header_id;
351 if (receipt_info_cursor%NOTFOUND) then
352 -- no receipts
353 p_receipt_switch := 'N';
354 else
355 --atleast one po
356 FETCH receipt_info_cursor INTO receipt_num2, receipt_shipment_header_id2;
357 if (receipt_info_cursor%NOTFOUND) then
358 --exactly one receipt
359 p_receipt_switch := 'S';
360 else
361 -- multiple receipts
362 p_receipt_switch := 'M';
363 p_receipt_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
364 end if;
365 end if;
366
367 CLOSE receipt_info_cursor;
368
369 EXCEPTION WHEN OTHERS THEN
370
371 p_receipt_switch := 'E';
372
373 END get_receipt_info;
374
375
376 ----------------------------------------------------------------------
377 -- Procedure to return PO information associated with an invoice
378 -- Returns:
379 -- p_po_switch: S/M/N/E (Single/Multiple/No/Exception)
380 -- p_po_num: <PO_NUM>
381 -- p_header_id: <PO_HEADER_ID>
382 -- p_release_id: <PO_RELEASE_ID>
383 -- @ABTRIVED
384 ----------------------------------------------------------------------
385 PROCEDURE get_po_info(l_invoice_id IN NUMBER,
386 p_po_switch OUT NOCOPY VARCHAR2,
387 p_po_num OUT NOCOPY VARCHAR2,
388 p_header_id OUT NOCOPY VARCHAR2,
389 p_release_id OUT NOCOPY VARCHAR2)
390 IS
391
392
393 po_num2 VARCHAR2(41);
394 header_id2 VARCHAR2(40);
395 release_id2 VARCHAR2(40);
396
397 ---------------------------------------------------------------------
398 -- Declare cursor to retrieve po_numbers, header id's and release id's
399 CURSOR po_info_cursor IS
400 --using invoice lines
401 select distinct ph.clm_document_number, ph.po_header_id, null
402 from ap_invoice_lines_all ail,
403 po_headers_all ph
404 where ail.invoice_id = l_invoice_id and
405 ail.po_release_id is null and
406 ph.po_header_id = ail.po_header_id and
407 ph.type_lookup_code = 'STANDARD'
408 union all
409 select distinct (ph.clm_document_number||'-'||pr.release_num), ph.po_header_id, pr.po_release_id
410 from ap_invoice_lines_all ail,
411 po_headers_all ph,
412 po_releases_all pr
413 WHERE ail.invoice_id = l_invoice_id and
414 pr.po_release_id = ail.po_release_id and
415 ph.po_header_id = ail.po_header_id and
416 ph.type_lookup_code = 'BLANKET' ;
417
418 --using invoice distributions
419 /* SELECT DISTINCT ph.segment1,ph.po_header_id,null
420 FROM ap_invoice_distributions_all aid,
421 po_distributions_all pd,
422 po_headers_all ph
423 WHERE aid.invoice_id = l_invoice_id
424 AND aid.po_distribution_id = pd.po_distribution_id
425 AND pd.po_header_id = ph.po_header_id
426 AND ph.type_lookup_code = 'STANDARD'
427 UNION ALL
428 SELECT DISTINCT (ph.segment1||'-'||pr.release_num),ph.po_header_id, pr.po_release_id
429 FROM ap_invoice_distributions_all aid,
430 po_distributions_all pd,
431 po_headers_all ph,
432 po_releases_all pr
433 WHERE aid.invoice_id = l_invoice_id
434 AND aid.po_distribution_id = pd.po_distribution_id
435 AND pr.po_release_id = pd.po_release_id
436 AND ph.po_header_id = pr.po_header_id
437 AND ph.type_lookup_code = 'BLANKET';
438 */
439
440
441 BEGIN
442
443 p_po_num := '';
444 p_header_id := '';
445 p_release_id := '';
446
447 OPEN po_info_cursor;
448 FETCH po_info_cursor INTO p_po_num, p_header_id, p_release_id;
449 if (po_info_cursor%NOTFOUND) then
450 -- no po's
451 p_po_switch := 'N';
452 else
453 --atleast one po
454 FETCH po_info_cursor INTO po_num2, header_id2, release_id2;
455 if (po_info_cursor%NOTFOUND) then
456 --exactly one PO
457 p_po_switch := 'S';
458 else
459 -- multiple PO's
460 p_po_switch := 'M';
461 p_po_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
462 end if;
463 end if;
464 CLOSE po_info_cursor;
465
466 EXCEPTION WHEN OTHERS THEN
467 p_po_switch := 'E';
468
469 END get_po_info;
470
471 -----------------------------------------------------------------------
472 -- Function get_amount_withheld returns the AWT withheld amount on
473 -- an invoice.
474 --
475 FUNCTION get_amount_withheld(l_invoice_id IN NUMBER)
476 RETURN NUMBER
477 IS
478 amount_withheld NUMBER := 0;
479 BEGIN
480
481 --togeorge 11/15/2000
482 --changed org specific views to _all tables
483 select (0 - sum(nvl(amount,0)))
484 into amount_withheld
485 from ap_invoice_distributions_all --ap_invoice_distributions
486 where invoice_id = l_invoice_id
487 and line_type_lookup_code = 'AWT';
488
489 return(amount_withheld);
490
491 END get_amount_withheld;
492
493
494 ----------------------------------------------------------------------------------
495 -- Function to get retainage amount for an invoice
496 -- Sum of Retainage Release Lines and Retainage Distributions
497 -- bug 4952468
498 ----------------------------------------------------------------------------------
499 FUNCTION get_retainage_for_invoice(l_invoice_id IN NUMBER)
500 RETURN NUMBER
501 IS
502 retainage_amount1 NUMBER := 0; --Retainage amount from lines
503 retainage_amount2 NUMBER := 0; --Retainage amount from distributions
504 BEGIN
505
506 select nvl(sum(amount),0)
507 INTO retainage_amount1
508 from ap_invoice_lines_All
509 where invoice_id = l_invoice_id
510 and LINE_TYPE_LOOKUP_CODE = 'RETAINAGE RELEASE';
511
512 SELECT nvl(sum(amount),0)
513 INTO retainage_amount2
514 FROM ap_invoice_distributions_all aid
515 WHERE aid.invoice_id = l_invoice_id
516 AND aid.line_type_lookup_code = 'RETAINAGE'
517 AND EXISTS
518 (SELECT 'X' FROM ap_invoice_lines_all ail
519 WHERE ail.invoice_id = l_invoice_id
520 AND ail.line_number = aid.invoice_line_number
521 AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE'
522 );
523
524 return(retainage_amount1 + retainage_amount2);
525
526 END;
527
528 ----------------------------------------------------------------------------------
529 -- Function to get prepayment amount for an invoice
530 -- Sum of Prepay Lines and Prepay Distributions
531 -- bug 5441740
532 ----------------------------------------------------------------------------------
533 FUNCTION get_prepay_for_invoice(l_invoice_id IN NUMBER)
534 RETURN NUMBER
535 IS
536 prepay_amount1 NUMBER := 0; --Prepayment amount from lines
537 prepay_amount2 NUMBER := 0; --Prepayment amount from distributions
538 BEGIN
539
540 select nvl(sum(amount),0)
541 INTO prepay_amount1
542 from ap_invoice_lines_All
543 where invoice_id = l_invoice_id
544 and LINE_TYPE_LOOKUP_CODE = 'PREPAY';
545
546 SELECT nvl(sum(amount),0)
547 INTO prepay_amount2
548 FROM ap_invoice_distributions_all aid
549 WHERE aid.invoice_id = l_invoice_id
550 AND aid.line_type_lookup_code = 'PREPAY'
551 AND EXISTS
552 (SELECT 'X' FROM ap_invoice_lines_all ail
553 WHERE ail.invoice_id = l_invoice_id
554 AND ail.line_number = aid.invoice_line_number
555 AND ail.line_type_lookup_code <> 'PREPAY'
556 );
557
558 return(prepay_amount1 + prepay_amount2);
559
560 END;
561
562 ----------------------------------------------------------------------------------
563 -- Function to get tax amount for an invoice
564 -- Sum of Lines amount for lines of type tax
565 -- bug 5569244
566 ----------------------------------------------------------------------------------
567 FUNCTION get_tax_for_invoice(l_invoice_id IN NUMBER)
568 RETURN NUMBER
569 IS
570 amount1 NUMBER := 0; -- tax amount from lines
571 BEGIN
572
573 select nvl(sum(amount),0)
574 INTO amount1
575 from ap_invoice_lines_All
576 where invoice_id = l_invoice_id
577 and LINE_TYPE_LOOKUP_CODE = 'TAX' ;
578
579 return(amount1);
580
581 END;
582
583 ----------------------------------------------------------------------------------
584 -- Function to get total amount for an invoice including retainage
585 -- Sum of Lines amount and Retainage Distributions and Prepayment distributions
586 -- bug 4952468, 5441740
587 ----------------------------------------------------------------------------------
588 FUNCTION get_total_for_invoice(l_invoice_id IN NUMBER)
589 RETURN NUMBER
590 IS
591 amount1 NUMBER := 0; --amount from lines
592 retainage_amount2 NUMBER := 0; --Retainage amount from distributions
593 prepay_amount2 NUMBER := 0; --Prepayment amount from distributions
594 BEGIN
595
596 select nvl(sum(amount),0)
597 INTO amount1
598 from ap_invoice_lines_All
599 where invoice_id = l_invoice_id;
600
601 SELECT nvl(sum(amount),0)
602 INTO retainage_amount2
603 FROM ap_invoice_distributions_all aid
604 WHERE aid.invoice_id = l_invoice_id
605 AND aid.line_type_lookup_code = 'RETAINAGE'
606 AND EXISTS
607 (SELECT 'X' FROM ap_invoice_lines_all ail
608 WHERE ail.invoice_id = l_invoice_id
609 AND ail.line_number = aid.invoice_line_number
610 AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE'
611 );
612
613 SELECT nvl(sum(amount),0)
614 INTO prepay_amount2
615 FROM ap_invoice_distributions_all aid
616 WHERE aid.invoice_id = l_invoice_id
617 AND aid.line_type_lookup_code = 'PREPAY'
618 AND EXISTS
619 (SELECT 'X' FROM ap_invoice_lines_all ail
620 WHERE ail.invoice_id = l_invoice_id
621 AND ail.line_number = aid.invoice_line_number
622 AND ail.line_type_lookup_code <> 'PREPAY'
623 );
624 return(amount1 + retainage_amount2 + prepay_amount2);
625
626 END;
627
628 ----------------------------------------------------------------------------------
629 -- Function to get list of concated payment numbers for an invoice
630 -- Replacement for POS_AP_INVOICE_PAYMENTS_PKG.GET_PAID_BY_LIST
631 -- @ABTRIVED
632 ----------------------------------------------------------------------------------
633 FUNCTION get_payment_list(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
634 RETURN VARCHAR2
635 IS
636
637 pay_num varchar2(20);
638 pay_id varchar2(20);
639 pay_date1 date;
640 l_paid_by_list VARCHAR2(2000) := NULL;
641 l_paid_by_list2 VARCHAR2(2000) := NULL;
642
643 cursor payment_cursor_new is
644 SELECT ac.check_number,
645 ac.check_id,
646 ac.check_Date
647 FROM ap_invoice_payments_all aip,
648 ap_checks_all ac
649 WHERE aip.invoice_id = l_invoice_id
650 AND aip.check_id = ac.check_id;
651
652 BEGIN
653
654 OPEN payment_cursor_new;
655
656 LOOP
657 FETCH payment_cursor_new INTO pay_num, pay_id, pay_date1;
658 EXIT WHEN payment_cursor_new%NOTFOUND;
659
660 IF (l_paid_by_list IS NOT NULL) THEN
661 l_paid_by_list := l_paid_by_list || ', ';
662 END IF;
663
664 l_paid_by_list := l_paid_by_list || pay_num;
665 l_paid_by_list2 := l_paid_by_list;
666
667 END LOOP;
668
669 CLOSE payment_cursor_new;
670
671 RETURN(l_paid_by_list);
672
673 EXCEPTION WHEN OTHERS THEN
674
675 RETURN(l_paid_by_list2); /* for overflow conditions */
676
677 END get_payment_list;
678
679
680 /*deprecated - should be replaced by method with same name and which passes p_payment_method also*/
681 ----------------------------------------------------------------------------------
682 -- Function to get payments numbers for an invoice
683 -- Return parameters:
684 -- p_payment_switch is N - no payments, S - single payment,
685 -- M - Multiple payment
686 -- p_payment_num is payment number of the payment if only one payment, else null
687 -- p_payment_id is check_id if only one payment, else null
688 -- @ABTRIVED
689 ----------------------------------------------------------------------------------
690 PROCEDURE get_payment_info(l_invoice_id IN NUMBER,
691 p_payment_switch OUT NOCOPY VARCHAR2,
692 p_payment_num OUT NOCOPY VARCHAR2,
693 p_payment_id OUT NOCOPY VARCHAR2,
694 p_payment_date OUT NOCOPY VARCHAR2
695 )
696 /*deprecated - should be replaced by method with same name and which passes p_payment_method also*/
697
698 IS
699
700 pay_num varchar2(20);
701 pay_id varchar2(20);
702 -- pay_type varchar2(255);
703 pay_date1 date;
704 pay_date2 date;
705
706 cursor payment_cursor_new is
707 SELECT distinct ac.check_number,
708 ac.check_id,
709 ac.check_Date
710 --, alc2.displayed_field
711 FROM ap_invoice_payments_all aip,
712 ap_checks_all ac
713 --, ap_lookup_codes alc2
714 WHERE aip.invoice_id = l_invoice_id
715 AND aip.check_id = ac.check_id;
716 -- AND alc2.lookup_type = 'PAYMENT METHOD'
717 -- AND alc2.lookup_code = ac.payment_method_lookup_code;
718
719 /*deprecated - should be replaced by method with same name and which passes p_payment_method also*/
720 BEGIN
721
722 p_payment_switch := 'N';
723 p_payment_num := '';
724 p_payment_id := '';
725 --p_payment_date := '';
726
727 OPEN payment_cursor_new;
728
729 FETCH payment_cursor_new INTO p_payment_num, p_payment_id, pay_date1;--, pay_type;
730 if (payment_cursor_new%NOTFOUND) then
731 --no payments
732 p_payment_switch := 'N';
733 else
734 --atleast one payment
735 FETCH payment_cursor_new INTO pay_num, pay_id, pay_date2;--, pay_type;
736 if (payment_cursor_new%NOTFOUND) then
737 --just one payment
738 p_payment_switch := 'S';
739
740 if(fnd_timezones.timezones_enabled()='Y') then
741 fnd_date_tz.init_timezones_for_fnd_date(true);
742 /* Calendar Support ,Passing calendar_aware=>1 ,It means the field always doesn't need Gregorian calendar */
743 p_payment_date := fnd_date.date_to_displayDT(pay_date1,calendar_aware=>1);
744 else
745 p_payment_date := to_char(pay_date1,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'));
746 end if;
747
748 else
749 p_payment_switch := 'M';
750 p_payment_date := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
751 p_payment_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
752 end if;
753 end if;
754
755 CLOSE payment_cursor_new;
756
757 EXCEPTION WHEN OTHERS THEN
758
759 p_payment_switch := 'E';
760
761 END get_payment_info;
762 /*deprecated - should be replaced by method with same name and which passes p_payment_method also*/
763
764
765
766 ----------------------------------------------------------------------------------
767 -- Function to get payments numbers for an invoice
768 -- Return parameters:
769 -- p_payment_switch is N - no payments, S - single payment,
770 -- M - Multiple payment
771 -- p_payment_num is payment number of the payment if only one payment, else null
772 -- p_payment_id is check_id if only one payment, else null
773 -- @ABTRIVED
774 ----------------------------------------------------------------------------------
775 PROCEDURE get_payment_info(l_invoice_id IN NUMBER,
776 p_payment_switch OUT NOCOPY VARCHAR2,
777 p_payment_num OUT NOCOPY VARCHAR2,
778 p_payment_id OUT NOCOPY VARCHAR2,
779 p_payment_date OUT NOCOPY VARCHAR2,
780 p_payment_method OUT NOCOPY VARCHAR2
781 )
782
783 IS
784
785 pay_num varchar2(20);
786 pay_id varchar2(20);
787 pay_type varchar2(255);
788 pay_date1 date;
789 pay_date2 date;
790
791 cursor payment_cursor_new is
792 SELECT distinct ac.check_number,
793 ac.check_id,
794 -- ac.check_Date,
795 alc2.displayed_field
796 FROM ap_invoice_payments_all aip,
797 ap_checks_all ac,
798 ap_lookup_codes alc2
799 WHERE aip.invoice_id = l_invoice_id
800 AND aip.check_id = ac.check_id
801 AND alc2.lookup_type(+) = 'PAYMENT METHOD'
802 AND alc2.lookup_code(+) = ac.payment_method_lookup_code;
803
804 cursor paymentdate_cursor_new is
805 SELECT distinct ac.check_Date
806 FROM ap_invoice_payments_all aip,
807 ap_checks_all ac
808 WHERE aip.invoice_id = l_invoice_id
809 AND aip.check_id = ac.check_id;
810
811
812 BEGIN
813
814 p_payment_switch := 'N';
815 p_payment_num := '';
816 p_payment_id := '';
817 --p_payment_date := '';
818
819 OPEN payment_cursor_new;
820
821 FETCH payment_cursor_new INTO p_payment_num, p_payment_id, p_payment_method;
822 if (payment_cursor_new%NOTFOUND) then
823 --no payments
824 p_payment_switch := 'N';
825 else
826 --atleast one payment
827 FETCH payment_cursor_new INTO pay_num, pay_id, pay_type;
828 if (payment_cursor_new%NOTFOUND) then
829 --just one payment
830 p_payment_switch := 'S';
831 else
832 p_payment_switch := 'M';
833 p_payment_num := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
834 p_payment_method := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
835 end if;
836 end if;
837
838 CLOSE payment_cursor_new;
839
840 OPEN paymentdate_cursor_new;
841
842 FETCH paymentdate_cursor_new INTO pay_date1;
843 if (paymentdate_cursor_new%NOTFOUND) then
844 --no payments
845 p_payment_date := '';
846 else
847 --atleast one payment
848 FETCH paymentdate_cursor_new INTO pay_date2;
849 if (paymentdate_cursor_new%NOTFOUND) then
850 --just one payment
851 if(fnd_timezones.timezones_enabled()='Y') then
852 fnd_date_tz.init_timezones_for_fnd_date(true);
853 /* Calendar Support ,Passing calendar_aware=>1 ,It means the field always doesn't need Gregorian calendar */
854 p_payment_date := fnd_date.date_to_displayDT(pay_date1,calendar_aware=>1);
855 else
856 /*Bug #:12827801 */
857 /* To show the due_date value according to the user profile calender(Eg : English Hijrah) */
858 p_payment_date := fnd_date.date_to_displaydate(pay_date1,calendar_aware=>1);
859 end if;
860 else
861 p_payment_date := FND_MESSAGE.GET_STRING('PO','PO_WF_NOTIF_MULTIPLE');
862 end if;
863 end if;
864
865 CLOSE paymentdate_cursor_new;
866
867 EXCEPTION WHEN OTHERS THEN
868
869 p_payment_switch := 'E';
870
871 END get_payment_info;
872
873
874 FUNCTION get_on_hold_status(l_invoice_id IN NUMBER)
875 RETURN VARCHAR2 IS
876
877 l_status VARCHAR2(60) := NULL;
878 l_count NUMBER;
879
880 BEGIN
881
882 select count(*)
883 into l_count
884 from ap_holds_all
885 where invoice_id = l_invoice_id
886 and release_lookup_code is null;
887
888 if (l_count > 0) then
889
890 l_status := fnd_message.get_string('POS', 'POS_ON_HOLD');
891
892 end if;
893
894 RETURN l_status;
895
896 EXCEPTION WHEN OTHERS THEN
897
898 RETURN l_status;
899
900 END get_on_hold_status;
901
902
903 END POS_AP_INVOICES_PKG;
[Home] [Help]