Upgraders Testing/Create functions (Oracle)

Script for 2.40/2.3x

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

create or replace FUNCTION     IMP_VENTAS_TOT(p_clientID IN NUMBER, p_orgID IN NUMBER, p_productID IN NUMBER, p_partner IN NUMBER, p_year IN NUMBER,
p_month IN NUMBER,salesrep IN NUMBER) RETURN NUMBER

AS
/*************************************************************************
* 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 SL
* All portions are Copyright (C) 2001-2006 Openbravo SL
* 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 NUMBER(10,2) ;
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 IMP_VENTAS_TOT
;

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

create or replace FUNCTION     IMP_VENTAS(p_clientID IN NUMBER, p_orgID IN  NUMBER, p_productID IN NUMBER,p_partner IN NUMBER, p_year IN NUMBER, p_month IN  NUMBER
,salesrep IN NUMBER) RETURN NUMBER

AS
/*************************************************************************
* 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 SL
* All portions are Copyright (C) 2001-2006 Openbravo SL
* 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 NUMBER(10,2) ;
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 IMP_VENTAS
;

Script for 2.50 or higher

create or replace FUNCTION     IMP_VENTAS_TOT(p_clientID IN VARCHAR2, p_orgID IN VARCHAR2, p_productID IN VARCHAR2, p_partner IN VARCHAR2, p_year IN NUMBER,
p_month IN NUMBER,salesrep IN VARCHAR2) RETURN NUMBER

AS
/*************************************************************************
* 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 SL
* All portions are Copyright (C) 2001-2006 Openbravo SL
* 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 NUMBER(10,2) ;
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 IMP_VENTAS_TOT
;

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

create or replace FUNCTION     IMP_VENTAS(p_clientID IN VARCHAR2, p_orgID IN  VARCHAR2, p_productID IN VARCHAR2,p_partner IN VARCHAR2, p_year IN NUMERIC, p_month IN  NUMERIC
,salesrep IN VARCHAR2) RETURN NUMBER

AS
/*************************************************************************
* 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 SL
* All portions are Copyright (C) 2001-2006 Openbravo SL
* 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 NUMBER(10,2) ;
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 IMP_VENTAS
;