invoiceopen(numeric, numeric)

/*************************************************************************

 * The contents of this file are subject to the Compiere License.  You may

 * obtain a copy of the License at    http://www.compiere.org/license.html

 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either

 * express or implied. See the License for details. Code: Compiere ERP+CRM

 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.

 *

 * converted to postgreSQL by Karsten Thiemann (Schaeffer AG),

 * kthiemann@adempiere.org

 *************************************************************************

 ***

 * Title:   Calculate Open Item Amount in Invoice Currency

 * Description:

 *  Add up total amount open for C_Invoice_ID if no split payment.

 *  Grand Total minus Sum of Allocations in Invoice Currency

 *

 *  For Split Payments:

 *  Allocate Payments starting from first schedule.

 *  Cannot be used for IsPaid as mutating

 *

 * Test:

 *  SELECT C_InvoicePaySchedule_ID, DueAmt FROM C_InvoicePaySchedule WHERE C_Invoice_ID=109 ORDER BY DueDate;

 *  SELECT invoiceOpen (109, null) FROM AD_System; - converted to default client currency

 *  SELECT invoiceOpen (109, 11) FROM AD_System; - converted to default client currency

 *  SELECT invoiceOpen (109, 102) FROM AD_System;

 *  SELECT invoiceOpen (109, 103) FROM AD_System;

 ************************************************************************/

DECLARE

    v_Currency_ID       NUMERIC(10);

    v_TotalOpenAmt      NUMERIC := 0;

    v_PaidAmt           NUMERIC := 0;

    v_Remaining         NUMERIC := 0;

        v_MultiplierAP          NUMERIC := 0;

        v_MultiplierCM          NUMERIC := 0;

        v_Temp                  NUMERIC := 0;

        v_Temp2                 NUMERIC := 0;

        v_Precision             NUMERIC := 0;

        v_Min                   NUMERIC := 0;

    v_PaidAmtIps        NUMERIC := 0;

        ar          RECORD;

        s           RECORD;

​

BEGIN

    --  Get Currency

    BEGIN

        SELECT  MAX(C_Currency_ID), SUM(GrandTotal), MAX(MultiplierAP), MAX(Multiplier)

        INTO    v_Currency_ID, v_TotalOpenAmt, v_MultiplierAP, v_MultiplierCM

        FROM    C_Invoice_v     --  corrected for CM / Split Payment

        WHERE   C_Invoice_ID = p_C_Invoice_ID;

    EXCEPTION   --  Invoice in draft form

        WHEN OTHERS THEN

                RAISE NOTICE 'InvoiceOpen - %', SQLERRM;

            RETURN NULL;

    END;

​

    SELECT StdPrecision

        INTO v_Precision

        FROM C_Currency

        WHERE C_Currency_ID = v_Currency_ID;

​

    SELECT 1/10^v_Precision INTO v_Min;

​

    --  Calculate Allocated Amount

    FOR ar IN

        SELECT  a.AD_Client_ID, a.AD_Org_ID,

        al.Amount, al.DiscountAmt, al.WriteOffAmt,

        a.C_Currency_ID, a.DateTrx, al.C_InvoicePaySchedule_ID

        FROM    C_AllocationLine al

        INNER JOIN C_AllocationHdr a ON (al.C_AllocationHdr_ID=a.C_AllocationHdr_ID)

        WHERE   al.C_Invoice_ID = p_C_Invoice_ID

            AND   a.IsActive='Y'

    LOOP

           v_Temp := ar.Amount + ar.DisCountAmt + ar.WriteOffAmt;

​

           v_Temp2 := currencyConvert(v_Temp * v_MultiplierAP,

                ar.C_Currency_ID, v_Currency_ID, ar.DateTrx, null, ar.AD_Client_ID, ar.AD_Org_ID);

           v_PaidAmt := v_PaidAmt + v_Temp2;

            -- Allocation

            --+ currencyConvert(v_Temp * v_MultiplierAP,

            --  ar.C_Currency_ID, v_Currency_ID, ar.DateTrx, null, ar.AD_Client_ID, ar.AD_Org_ID);

            RAISE NOTICE '   PaidAmt=% , Allocation= % * %', v_PaidAmt, v_Temp, v_MultiplierAP;

​

        IF (ar.C_InvoicePaySchedule_ID = p_C_InvoicePaySchedule_ID) THEN

            v_PaidAmtIps := v_PaidAmtIps + v_Temp2;

​

        else

            IF (COALESCE(ar.C_InvoicePaySchedule_ID,0) = 0 and p_C_InvoicePaySchedule_ID > 0) THEN

                v_PaidAmtIps := v_PaidAmtIps + v_PaidAmt;

            end if;

        END IF;

​

    END LOOP;

​

    --  Do we have a Payment Schedule ?

    IF (p_C_InvoicePaySchedule_ID > 0) THEN --   if not valid = lists invoice amount

        v_Remaining := v_PaidAmt;

        FOR s IN

            SELECT  C_InvoicePaySchedule_ID, DueAmt, IsPaid

            FROM    C_InvoicePaySchedule

        WHERE   C_Invoice_ID = p_C_Invoice_ID AND C_InvoicePaySchedule_ID = p_C_InvoicePaySchedule_ID

            AND   IsValid='Y'

            ORDER BY DueDate

        LOOP

            --IF (s.C_InvoicePaySchedule_ID = p_C_InvoicePaySchedule_ID AND s.IsPaid = 'Y') THEN

              --  v_TotalOpenAmt := (s.DueAmt*v_MultiplierCM) - v_Remaining;

                --IF (s.DueAmt - v_Remaining < 0) THEN

                  --  v_TotalOpenAmt := 0;

                --END IF;

            --ELSE -- calculate amount, which can be allocated to next schedule

                --v_Remaining := v_Remaining - s.DueAmt;

                --IF(v_TotalOpenAmt > 0 AND s.C_InvoicePaySchedule_ID = p_C_InvoicePaySchedule_ID) THEN

                IF ((s.DueAmt) - (v_PaidAmtIps*v_MultiplierCM) < 0) THEN

                    v_TotalOpenAmt := 0;

                else

                    v_TotalOpenAmt := (s.DueAmt*v_MultiplierCM) - v_PaidAmtIps;

                END IF;

​

                --END IF;

​

                --RAISE NOTICE 'IPS=%, C_InvoicePaySchedule_ID=%',p_C_InvoicePaySchedule_ID,s.C_InvoicePaySchedule_ID;

​

                IF (v_Remaining < 0) THEN

                    v_Remaining := 0;

                END IF;

            --END IF;

        END LOOP;

    ELSE

        v_TotalOpenAmt := v_TotalOpenAmt - v_PaidAmt;

    END IF;

--  RAISE NOTICE ''== Total='' || v_TotalOpenAmt;

​

    --  Ignore Rounding

    IF (v_TotalOpenAmt > -v_Min AND v_TotalOpenAmt < v_Min) THEN

        v_TotalOpenAmt := 0;

    END IF;

​

    --  Round to currency precision

    v_TotalOpenAmt := ROUND(COALESCE(v_TotalOpenAmt,0), v_Precision);

    RETURN  v_TotalOpenAmt;

END;