Upgraders Testing/Create functions (PostgreSQL)

Script for 2.40/2.3x

/**********************IMP_VENTAS_TOT*******************************/

create or replace FUNCTION IMP_VENTAS_TOT(p_clientID NUMERIC, p_orgID  NUMERIC, p_productID NUMERIC, p_partner NUMERIC, p_year NUMERIC,
p_month NUMERIC,salesrep NUMERIC) RETURNS numeric AS 
$BODY$ DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.0  (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):  ______________________________________.
************************************************************************/
/*************************************************************************
* Title: Return 'Org Level' if orgID is a leaf of the org tree
*    where parentOrgID is the root. Else return -1
************************************************************************/

  v_amount NUMERIC ;
BEGIN
  SELECT COALESCE(SUM(C_CURRENCY_CONVERT(COALESCE(C_ORDERLINE.LINENETAMT,0), C_ORDER.c_currency_id, 102, C_ORDER.dateordered, null, c_order.AD_CLIENT_ID, c_order.AD_ORG_ID)),0) into v_amount
  FROM C_ORDER, C_ORDERLINE
  WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
  AND C_ORDERLINE.M_PRODUCT_ID=p_productID
  AND C_ORDER.AD_ORG_ID=p_orgID
  AND C_ORDER.AD_CLIENT_ID=p_clientID
      AND C_ORDER.ISSOTRX = 'Y'
      AND C_ORDER.PROCESSED = 'Y'
      AND COALESCE(C_ORDER.SALESREP_ID,0)=salesrep
  AND C_ORDER.C_BPARTNER_ID=p_partner
  AND TO_CHAR(C_ORDER.DATEORDERED,'YYYY')=TO_CHAR(p_year);


RETURN v_amount;
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION IMP_VENTAS_TOT(p_clientID NUMERIC, p_orgID NUMERIC, p_productID  NUMERIC, p_partner NUMERIC, p_year NUMERIC,
 p_month NUMERIC,salesrep NUMERIC) OWNER TO postgres;

 
/**********************IMP_VENTAS*******************************/

create or replace FUNCTION     IMP_VENTAS(p_clientID NUMERIC, p_orgID NUMERIC,  p_productID NUMERIC,p_partner NUMERIC, p_year NUMERIC, p_month NUMERIC
,salesrep NUMERIC) RETURNS numeric AS
$BODY$ DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.0  (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):  ______________________________________.
************************************************************************/
/*************************************************************************
* Title: Return 'Org Level' if orgID is a leaf of the org tree
*    where parentOrgID is the root. Else return -1
************************************************************************/

  v_amount NUMERIC ;
BEGIN
  SELECT COALESCE(SUM(C_CURRENCY_CONVERT(COALESCE(C_ORDERLINE.LINENETAMT,0),   C_ORDER.c_currency_id, 

102, C_ORDER.dateordered, null, c_order.AD_CLIENT_ID, c_order.AD_ORG_ID)),0) into v_amount
  FROM C_ORDER, C_ORDERLINE
  WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
  AND C_ORDERLINE.M_PRODUCT_ID=p_productID
  AND C_ORDER.AD_ORG_ID=p_orgID
  AND C_ORDER.AD_CLIENT_ID=p_clientID
      AND C_ORDER.ISSOTRX = 'Y'
      AND C_ORDER.PROCESSED = 'Y'
      AND COALESCE(C_ORDER.SALESREP_ID,0)=salesrep
  AND C_ORDER.C_BPARTNER_ID=p_partner
  AND TO_CHAR(C_ORDER.DATEORDERED,'YYYY')=TO_CHAR(p_year)
  AND TO_NUMBER(TO_CHAR(C_ORDER.DATEORDERED,'MM'))=p_month;


RETURN v_amount;
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION IMP_VENTAS(p_clientID NUMERIC, p_orgID NUMERIC, p_productID   NUMERIC,p_partner NUMERIC, p_year NUMERIC, p_month NUMERIC
,salesrep NUMERIC) OWNER TO postgres;

Script 2.50 or higher

/**********************IMP_VENTAS_TOT*******************************/

create or replace FUNCTION IMP_VENTAS_TOT(p_clientID character varying, p_orgID  character varying, p_productID character varying, p_partner character varying, p_year NUMERIC,
p_month NUMERIC,salesrep character varying) RETURNS numeric AS 
$BODY$ DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.0  (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):  ______________________________________.
************************************************************************/
/*************************************************************************
* Title: Return 'Org Level' if orgID is a leaf of the org tree
*    where parentOrgID is the root. Else return -1
************************************************************************/

  v_amount NUMERIC ;
BEGIN
  SELECT COALESCE(SUM(C_CURRENCY_CONVERT(COALESCE(C_ORDERLINE.LINENETAMT,0), C_ORDER.c_currency_id, 102, C_ORDER.dateordered, null, c_order.AD_CLIENT_ID, c_order.AD_ORG_ID)),0) into v_amount
  FROM C_ORDER, C_ORDERLINE
  WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
  AND C_ORDERLINE.M_PRODUCT_ID=p_productID
  AND C_ORDER.AD_ORG_ID=p_orgID
  AND C_ORDER.AD_CLIENT_ID=p_clientID
      AND C_ORDER.ISSOTRX = 'Y'
      AND C_ORDER.PROCESSED = 'Y'
      AND COALESCE(C_ORDER.SALESREP_ID,0)=salesrep
  AND C_ORDER.C_BPARTNER_ID=p_partner
  AND TO_CHAR(C_ORDER.DATEORDERED,'YYYY')=TO_CHAR(p_year);


RETURN v_amount;
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION IMP_VENTAS_TOT(p_clientID character varying, p_orgID character varying, p_productID  character varying, p_partner character varying, p_year NUMERIC,
 p_month NUMERIC,salesrep character varying) OWNER TO postgres;

 
/**********************IMP_VENTAS*******************************/

create or replace FUNCTION     IMP_VENTAS(p_clientID character varying, p_orgID character varying,  p_productID character varying,p_partner character varying, p_year NUMERIC, p_month NUMERIC
,salesrep character varying) RETURNS numeric AS
$BODY$ DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.0  (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):  ______________________________________.
************************************************************************/
/*************************************************************************
* Title: Return 'Org Level' if orgID is a leaf of the org tree
*    where parentOrgID is the root. Else return -1
************************************************************************/

  v_amount NUMERIC ;
BEGIN
  SELECT COALESCE(SUM(C_CURRENCY_CONVERT(COALESCE(C_ORDERLINE.LINENETAMT,0),   C_ORDER.c_currency_id, 

102, C_ORDER.dateordered, null, c_order.AD_CLIENT_ID, c_order.AD_ORG_ID)),0) into v_amount
  FROM C_ORDER, C_ORDERLINE
  WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
  AND C_ORDERLINE.M_PRODUCT_ID=p_productID
  AND C_ORDER.AD_ORG_ID=p_orgID
  AND C_ORDER.AD_CLIENT_ID=p_clientID
      AND C_ORDER.ISSOTRX = 'Y'
      AND C_ORDER.PROCESSED = 'Y'
      AND COALESCE(C_ORDER.SALESREP_ID,0)=salesrep
  AND C_ORDER.C_BPARTNER_ID=p_partner
  AND TO_CHAR(C_ORDER.DATEORDERED,'YYYY')=TO_CHAR(p_year)
  AND TO_NUMBER(TO_CHAR(C_ORDER.DATEORDERED,'MM'))=p_month;


RETURN v_amount;
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION IMP_VENTAS(p_clientID character varying, p_orgID character varying, p_productID   character varying,p_partner character varying, p_year NUMERIC, p_month NUMERIC
,salesrep character varying) OWNER TO postgres;