1 PACKAGE BODY GMF_AP_INVOICE_PAYMENTS_INFO AS
2 /* $Header: gmfpaynb.pls 115.0 99/07/16 04:21:42 porting shi $ */
3 CURSOR invoice_payments( startdate date,
4 enddate date,
5 invoiceid number,
6 paymentnum number,
7 invoicepaymentid number) IS
8 SELECT aip.invoice_id,
9 aip.payment_num,
10 aip.check_id,
11 aip.invoice_payment_id,
12 aip.amount,
13 aip.last_update_date,
14 aip.set_of_books_id,
15 aip.posted_flag,
16 aip.accounting_date,
17 aip.period_name,
18 ach.check_number,
19 ach.check_date,
20 ach.payment_method_lookup_code
21 FROM AP_INVOICE_PAYMENTS_ALL aip,
22 AP_CHECKS_ALL ach
23 WHERE aip.invoice_id = NVL(invoiceid,aip.invoice_id)
24 AND aip.payment_num = NVL(paymentnum,aip.payment_num)
25 AND aip.invoice_payment_id=NVL(invoicepaymentid,aip.invoice_payment_id)
26 AND aip.creation_date BETWEEN
27 nvl(startdate,aip.creation_date) AND
28 nvl(enddate,aip.creation_date)
29 AND ach.check_id = aip.check_id;
30
31 PROCEDURE get_invoice_payments_info( startdate in date,
32 enddate in date,
33 invoiceid in out number,
34 paymentnum in out number,
35 checkid out number,
36 invoicepaymentid in out number,
37 amount out number,
38 lastupdatedate out date,
39 setofbooksid out number,
40 postedflag out varchar2,
41 accountingdate out date,
42 periodname out varchar2,
43 checknumber out number,
44 checkdate out date,
45 paymentlookupcode out varchar2,
46 row_to_fetch in out number,
47 statuscode out number) IS
48 Begin
49 IF NOT invoice_payments%ISOPEN THEN
50 OPEN invoice_payments( startdate,
51 enddate,
52 invoiceid,
53 paymentnum,
54 invoicepaymentid);
55 END IF;
56 FETCH invoice_payments
57 INTO invoiceid,
58 paymentnum,
59 checkid ,
60 invoicepaymentid ,
61 amount ,
62 lastupdatedate,
63 setofbooksid,
64 postedflag ,
65 accountingdate,
66 periodname,
67 checknumber,
68 checkdate,
69 paymentlookupcode;
70
71 if invoice_payments%NOTFOUND then
72 statuscode := 100;
73 end if;
74 IF invoice_payments%NOTFOUND or row_to_fetch = 1 THEN
75 CLOSE invoice_payments;
76 END IF;
77 EXCEPTION
78 WHEN OTHERS THEN
79 statuscode := SQLCODE;
80 End; /* End of procedure get_invoice_payment_info */
81 END GMF_AP_INVOICE_PAYMENTS_INFO;