postgre 存储过程 返回 <unnamed portal 1>

postgre 存储过程 返回 <unnamed portal 1>

CREATE OR REPLACe FUNCTION paymentavailable(p_c_payment_id numeric)
RETURNS numeric AS
$BODY$

DECLARE
v_Currency_ID NUMERIC(10);
v_AvailableAmt NUMERIC := 0;
v_IsReceipt C_Payment.IsReceipt%TYPE;
v_Amt NUMERIC := 0;
r RECORD;

BEGIN
-- Charge - fully allocated
SELECT MAX(PayAmt)
INTO v_Amt
FROM C_Payment
WHERe C_Payment_ID=p_C_Payment_ID AND C_Charge_ID > 0;
IF (v_Amt IS NOT NULL) THEN
RETURN 0;
END IF;

-- Get Currency
SELECt C_Currency_ID, PayAmt, IsReceipt
INTO v_Currency_ID, v_AvailableAmt, v_IsReceipt
FROM C_Payment_v -- corrected for AP/AR
WHERe C_Payment_ID = p_C_Payment_ID;
-- DBMS_OUTPUT.PUT_LINE('== C_Payment_ID=' || p_C_Payment_ID || ', PayAmt=' || v_AvailableAmt || ', Receipt=' || v_IsReceipt);

-- Calculate Allocated Amount
FOR r IN
SELECt a.AD_Client_ID, a.AD_Org_ID, al.Amount, a.C_Currency_ID, a.DateTrx
FROM C_AllocationLine al
INNER JOIN C_AllocationHdr a ON (al.C_AllocationHdr_ID=a.C_AllocationHdr_ID)
WHERe al.C_Payment_ID = p_C_Payment_ID
AND a.IsActive='Y'
LOOP
v_Amt := currencyConvert(r.Amount, r.C_Currency_ID, v_Currency_ID, r.DateTrx, null, r.AD_Client_ID, r.AD_Org_ID);
v_AvailableAmt := v_AvailableAmt - v_Amt;
-- DBMS_OUTPUT.PUT_LINE(' Allocation=' || a.Amount || ' - Available=' || v_AvailableAmt);
END LOOP;
-- Ignore Rounding
IF (v_AvailableAmt BETWEEN -0.00999 AND 0.00999) THEN
v_AvailableAmt := 0;
END IF;
-- Round to penny
v_AvailableAmt := ROUND(COALESCE(v_AvailableAmt,0), 2);
RETURN v_AvailableAmt;
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION paymentavailable(numeric)
OWNER TO postgres;