CREATE OR REPLACE FUNCTION MEW_GET_APINVOICE_BALANCE_F(P_INVOICE_ID NUMBER)
RETURN NUMBER IS
V_INVOICE_AMOUNT NUMBER;
V_PAYMENT_AMOUNT NUMBER;
V_PREPAID_AMOUNT NUMBER;
V_PREPAID_AMOUNT1 NUMBER;
V_INVOICE_TYPE_LOOKUP_CODE VARCHAR2(30);
BEGIN
--1发票金额
SELECT AIA.INVOICE_AMOUNT
,AIA.INVOICE_TYPE_LOOKUP_CODE
INTO V_INVOICE_AMOUNT
,V_INVOICE_TYPE_LOOKUP_CODE
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_ID = P_INVOICE_ID;
--2正常付款金额
BEGIN
SELECT SUM(NVL(AIP.AMOUNT
,0)) AMOUNT
INTO V_PAYMENT_AMOUNT
FROM AP_INVOICE_PAYMENTS_ALL AIP
,AP_INVOICES_ALL AI
,AP_INVOICES_ALL AI2
,AP_CHECKS_ALL AC
WHERE AIP.INVOICE_ID = AI.INVOICE_ID
AND AIP.OTHER_INVOICE_ID = AI2.INVOICE_ID(+)
AND AIP.CHECK_ID = AC.CHECK_ID
AND AIP.AMOUNT <> 0
AND AIP.INVOICE_ID = P_INVOICE_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_PAYMENT_AMOUNT := 0;
END;
IF V_INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
--预付款发票核销普通发票的金额
SELECT (-1) * SUM(AID1.AMOUNT) PREPAY_AMOUNT_APPLIED
INTO V_PREPAID_AMOUNT
FROM AP_INVOICES_ALL AI
,AP_INVOICE_DISTRIBUTIONS_ALL AID1
,AP_INVOICE_DISTRIBUTIONS_ALL AID2
WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID1.INVOICE_ID
AND AID1.AMOUNT < 0
AND NVL(AID1.REVERSAL_FLAG
,'N') != 'Y'
AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN
('PREPAYMENT'
,'CREDIT'
,'DEBIT')
AND AID2.INVOICE_ID = P_INVOICE_ID;
ELSE
--普通发票核销预付款发票的金额
SELECT (-1) * SUM(AID1.AMOUNT) PREPAY_AMOUNT_APPLIED
INTO V_PREPAID_AMOUNT
FROM AP_INVOICES_ALL AI
,AP_INVOICE_DISTRIBUTIONS_ALL AID1
,AP_INVOICE_DISTRIBUTIONS_ALL AID2
WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID2.INVOICE_ID
AND AID1.AMOUNT < 0
AND NVL(AID1.REVERSAL_FLAG
,'N') != 'Y'
AND AID1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AID1.INVOICE_ID = P_INVOICE_ID;
END IF;
DBMS_OUTPUT.PUT_LINE(' 1v_invoice_amount: ' || V_INVOICE_AMOUNT ||
' 2v_payment_amount: ' || V_PAYMENT_AMOUNT ||
' 3v_prepaid_amount: ' || V_PREPAID_AMOUNT);
IF V_INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
THEN
RETURN NVL(V_PAYMENT_AMOUNT
,0) - NVL(V_PREPAID_AMOUNT
,0);
ELSE
RETURN NVL(V_INVOICE_AMOUNT
,0) - NVL(V_PAYMENT_AMOUNT
,0) - NVL(V_PREPAID_AMOUNT
,0);
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;