[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT API.INVOICE_ID,
API.LAST_UPDATE_DATE,
API.VENDOR_ID,
API.INVOICE_NUM,
API.INVOICE_AMOUNT,
API.INVOICE_DATE,
API.SOURCE,
API.INVOICE_TYPE_LOOKUP_CODE,
API.DESCRIPTION,
API.INVOICE_CURRENCY_CODE,
APT.NAME,
NVL(API.INVOICE_AMOUNT,0) + NVL(API.VENDOR_PREPAY_AMOUNT,0) -
NVL(API.AMOUNT_PAID,0) - NVL(API.DISCOUNT_AMOUNT_TAKEN,0)
FROM AP_INVOICES_ALL API,
AP_TERMS APT
WHERE API.INVOICE_ID = NVL(INVOICEID,API.INVOICE_ID) AND
API.INVOICE_DATE = NVL(INVOICEDATE,API.INVOICE_DATE) AND
API.CREATION_DATE BETWEEN
NVL(STARTDATE,API.CREATION_DATE) AND
NVL(ENDDATE,API.CREATION_DATE)
AND API.TERMS_ID = APT.TERM_ID;
LASTUPDATEDATE OUT NOCOPY DATE,
VENDORID OUT NOCOPY NUMBER,
INVOICENUM IN OUT NOCOPY VARCHAR2,
INVOICEAMOUNT OUT NOCOPY NUMBER,
INVOICEDATE IN OUT NOCOPY DATE,
SOURC OUT NOCOPY VARCHAR2,
INVOICETYPELOOKUPCODE OUT NOCOPY VARCHAR2,
DESCR OUT NOCOPY VARCHAR2,
CURRENCYCODE OUT NOCOPY VARCHAR2,
TERMSCODE OUT NOCOPY VARCHAR2,
HOLDREASON OUT NOCOPY VARCHAR2,
BALANCEAMOUNT IN OUT NOCOPY NUMBER,
ROW_TO_FETCH IN OUT NOCOPY NUMBER,
STATUSCODE OUT NOCOPY NUMBER) IS
BEGIN
IF NOT INVOICE_HEADER%ISOPEN THEN
OPEN INVOICE_HEADER(STARTDATE,
ENDDATE,
INVOICEID,
INVOICENUM,
INVOICEDATE);
LASTUPDATEDATE,
VENDORID,
INVOICENUM,
INVOICEAMOUNT,
INVOICEDATE,
SOURC,
INVOICETYPELOOKUPCODE,
DESCR,
CURRENCYCODE,
TERMSCODE,
BALANCEAMOUNT;
SELECT COUNT(*)
INTO HOLDREASON
FROM AP_HOLDS_ALL
WHERE INVOICE_ID = INVOICEID
AND RELEASE_LOOKUP_CODE IS NULL;
SELECT BALANCEAMOUNT - NVL(SUM(PREPAYMENT_AMOUNT_APPLIED),0)
INTO BALANCEAMOUNT
FROM AP_INVOICE_PREPAYS_ALL
WHERE INVOICE_ID = INVOICEID;