PACKAGE BODY: APPS.GMF_AR_INVOICE_HEADER_INFO

Source

1 PACKAGE BODY GMF_AR_INVOICE_HEADER_INFO AS
2 /* $Header: gmfinvhb.pls 115.1 2002/11/11 00:39:32 rseshadr ship $ */
3   CURSOR INVOICE_HEADER(STARTDATE   DATE,
4                         ENDDATE     DATE,
5                         INVOICEID   NUMBER,
6                         INVOICENUM  NUMBER,
7                         INVOICEDATE DATE) IS
8   SELECT API.INVOICE_ID,
9          API.LAST_UPDATE_DATE,
10          API.VENDOR_ID,
11          API.INVOICE_NUM,
12          API.INVOICE_AMOUNT,
13          API.INVOICE_DATE,
14          API.SOURCE,
15          API.INVOICE_TYPE_LOOKUP_CODE,
16          API.DESCRIPTION,
17          API.INVOICE_CURRENCY_CODE,
18          APT.NAME,
19          NVL(API.INVOICE_AMOUNT,0) + NVL(API.VENDOR_PREPAY_AMOUNT,0) -
20          NVL(API.AMOUNT_PAID,0) - NVL(API.DISCOUNT_AMOUNT_TAKEN,0)
21   FROM   AP_INVOICES_ALL API,
22          AP_TERMS    APT
23   WHERE  API.INVOICE_ID   =	NVL(INVOICEID,API.INVOICE_ID)	    AND
24          API.INVOICE_DATE =	NVL(INVOICEDATE,API.INVOICE_DATE) AND
25          API.CREATION_DATE	BETWEEN
26               NVL(STARTDATE,API.CREATION_DATE)	AND
27               NVL(ENDDATE,API.CREATION_DATE)
28               AND API.TERMS_ID = APT.TERM_ID;
29 
30 PROCEDURE GET_INVOICE_HEADER_INFO
31         (STARTDATE             IN            DATE,
32          ENDDATE               IN            DATE,
33          INVOICEID 	       IN OUT NOCOPY NUMBER,
34          LASTUPDATEDATE        OUT    NOCOPY DATE,
35          VENDORID 	       OUT    NOCOPY NUMBER,
36          INVOICENUM 	       IN OUT NOCOPY VARCHAR2,
37          INVOICEAMOUNT 	       OUT    NOCOPY NUMBER,
38          INVOICEDATE 	       IN OUT NOCOPY DATE,
39          SOURC 		       OUT    NOCOPY VARCHAR2,
40          INVOICETYPELOOKUPCODE OUT    NOCOPY VARCHAR2,
41          DESCR 		       OUT    NOCOPY VARCHAR2,
42          CURRENCYCODE          OUT    NOCOPY VARCHAR2,
43          TERMSCODE             OUT    NOCOPY VARCHAR2,
44          HOLDREASON            OUT    NOCOPY VARCHAR2,
45          BALANCEAMOUNT         IN OUT NOCOPY NUMBER,
46          ROW_TO_FETCH          IN OUT NOCOPY NUMBER,
47          STATUSCODE            OUT    NOCOPY NUMBER)   IS
48 BEGIN
49   IF NOT INVOICE_HEADER%ISOPEN THEN
50     OPEN INVOICE_HEADER(STARTDATE,
51 										    ENDDATE,
52 										    INVOICEID,
53 										    INVOICENUM,
54 										    INVOICEDATE);
55   END IF;
56 
57   FETCH INVOICE_HEADER
58   INTO  INVOICEID,
59 				LASTUPDATEDATE,
60 				VENDORID,
61 				INVOICENUM,
62 				INVOICEAMOUNT,
63 				INVOICEDATE,
64 				SOURC,
65 				INVOICETYPELOOKUPCODE,
66 				DESCR,
67 				CURRENCYCODE,
68 				TERMSCODE,
69 				BALANCEAMOUNT;
70 
71   IF INVOICE_HEADER%NOTFOUND THEN
72 			STATUSCODE := 100;
73 	END IF;
74 
75 	IF INVOICE_HEADER%NOTFOUND OR ROW_TO_FETCH = 1 THEN
76     CLOSE INVOICE_HEADER;
77   END IF;
78 
79   SELECT COUNT(*)
80   INTO   HOLDREASON
81   FROM   AP_HOLDS_ALL
82   WHERE  INVOICE_ID = INVOICEID
83   AND    RELEASE_LOOKUP_CODE IS NULL;
84 
85   EXCEPTION
86     WHEN OTHERS THEN
87       STATUSCODE := SQLCODE;
88 
89       SELECT BALANCEAMOUNT - NVL(SUM(PREPAYMENT_AMOUNT_APPLIED),0)
90       INTO   BALANCEAMOUNT
91       FROM   AP_INVOICE_PREPAYS_ALL
92       WHERE  INVOICE_ID = INVOICEID;
93 
94 	END;	/* END OF PROCEDURE GET_INVOICE_HEADER_INFO*/
95 END GMF_AR_INVOICE_HEADER_INFO;