-- FUNCTION: public.m_get_product_cost(character varying, timestamp without time zone, character)

-- DROP FUNCTION public.m_get_product_cost(character varying, timestamp without time zone, character);

CREATE OR REPLACE FUNCTION public.m_get_product_cost(
	p_product_id character varying,
	p_movementdate timestamp without time zone,
	p_costtype character)
    RETURNS numeric
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    SET search_path=public
AS $BODY$ DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
* Version 1.1  with a permitted attribution clause; you may not  use this
* file except in compliance with the License. You  may  obtain  a copy of
* the License at http://www.openbravo.com/legal/license.html
* Software distributed under the License  is  distributed  on  an "AS IS"
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
* License for the specific  language  governing  rights  and  limitations
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
* All portions are Copyright (C) 2001-2006 Openbravo SLU
* All Rights Reserved.
* Contributor(s):  ______________________________________.
************************************************************************/
  v_Cost     NUMERIC;
  v_Count    NUMERIC;
  v_CostType VARCHAR(60) ; --OBTG:VARCHAR2--
  --TYPE RECORD IS REFCURSOR;
    Cur_Cost RECORD;
  BEGIN
    v_CostType := p_CostType;
    IF(p_Product_ID IS NULL) THEN
      RETURN 0;
    END IF;
    IF(p_MovementDate IS NULL) THEN
      RETURN 0;
    END IF;
    /*IF (v_CostType IS NULL) THEN
    SELECT  COSTTYPE INTO v_CostType
    FROM M_PRODUCT
    WHERE M_PRODUCT_ID=p_Product_ID;
    END IF;*/
    SELECT COUNT(*)
    INTO v_Count
    FROM M_COSTING
    WHERE p_MovementDate BETWEEN DATEFROM AND DATETO
      AND M_PRODUCT_ID = p_Product_ID
      AND(v_CostType IS NULL
      OR v_CostType = COSTTYPE) ;
    IF(v_Count = 0) THEN
      RETURN 0;
    ELSE
      FOR Cur_Cost IN
        (SELECT COST
        FROM M_COSTING
        WHERE DATEFROM <= p_MovementDate
          AND DATETO > p_MovementDate
          AND M_PRODUCT_ID = p_Product_ID
          AND(v_CostType IS NULL
          OR v_CostType = COSTTYPE)
        ORDER BY DATEFROM,
          DATETO DESC
        )
      LOOP
        v_Cost := Cur_Cost.COST;
        EXIT;
      END LOOP;
      RETURN v_Cost;
    END IF;
END ; $BODY$;

ALTER FUNCTION public.m_get_product_cost(character varying, timestamp without time zone, character)
    OWNER TO tad;