mht_brerp10.adempiere

Parameters

Name	Type	Mode
p_c_invoice_id	numeric	IN
p_c_invoicepayschedule_id	numeric	IN

Definition

/*************************************************************************
 * 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;

invoiceopen(numeric, numeric)

Parameters

Definition