src/org/openbravo/erpCommon/ad_reports/ReportRefundInvoiceCustomerDimensionalAnalyses_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 573 48f214c7b66e
child 1605 8a0fe0193bef
permissions -rw-r--r--
Removed old frame parameter of windowTableId
<?xml version="1.0" encoding="UTF-8" ?>
<!--
 *************************************************************************
 * 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):  ______________________________________.
 ************************************************************************
-->

<SqlClass name="ReportRefundInvoiceCustomerDimensionalAnalysesData" package="org.openbravo.erpCommon.ad_reports">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, SUM(LINENETAMT) AS LINENETAMT, SUM(REFUNDAMT) AS REFUNDAMT, SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY, SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF, DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 AS RATIO, SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, '' AS ID, '' AS NAME, UOMSYMBOL
      FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, to_char('6') AS NIVEL6,
      C_INVOICELINE.LINENETAMT AS LINENETAMT, CASE SIGN(C_INVOICELINE.LINENETAMT) WHEN  -1 THEN  -1*C_INVOICELINE.LINENETAMT ELSE  0 END AS REFUNDAMT, C_INVOICELINE.QTYINVOICED AS QTYINVOICED, CASE SIGN(C_INVOICELINE.QTYINVOICED) WHEN  -1 THEN  (-1*C_INVOICELINE.QTYINVOICED) ELSE  0 END AS REFUNDQTY, 0 AS LINENETREF, 0 AS REFUNDAMTREF, 0 AS QTYINVOICEDREF, 0 AS REFUNDQTYREF, C_UOM.UOMSYMBOL
      FROM C_INVOICE left join AD_USER on C_INVOICE.SALESREP_ID = AD_USER.AD_USER_ID,
           C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
                         left join M_PRODUCT_CATEGORY on  M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID 
                         left join C_UOM on C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID,
           C_BPARTNER, C_BP_GROUP
      WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
        AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
      AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
      AND C_INVOICE.ISSOTRX = 'Y'
      AND C_INVOICE.PROCESSED = 'Y'
      AND 0=0 AND C_INVOICE.AD_ORG_ID IN ('1')
      AND C_INVOICE.AD_CLIENT_ID IN ('7')
      AND 1=1
      UNION ALL SELECT to_char('8') AS NIVEL1 , to_char('9') AS NIVEL2 , to_char('10') AS NIVEL3 , to_char('11') AS NIVEL4 , to_char('12') AS NIVEL5 , to_char('13') AS NIVEL6,
      0 AS LINENETAMT, 0 AS REFUNDAMT, 0 AS QTYINVOICED, 0 AS REFUNDQTY, C_INVOICELINE.LINENETAMT AS LINENETREF, CASE SIGN(C_INVOICELINE.LINENETAMT) WHEN  -1 THEN  (-1*C_INVOICELINE.LINENETAMT) ELSE  0 END AS REFUNDAMTREF, C_INVOICELINE.QTYINVOICED AS QTYINVOICEDREF, CASE SIGN(C_INVOICELINE.QTYINVOICED) WHEN  -1 THEN  (-1*C_INVOICELINE.QTYINVOICED) ELSE  0 END AS REFUNDQTYREF, C_UOM.UOMSYMBOL
      FROM C_INVOICE left join AD_USER on C_INVOICE.SALESREP_ID = AD_USER.AD_USER_ID,
           C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
                         left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID
                         left join C_UOM on C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID,
          C_BPARTNER, C_BP_GROUP
      WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
      AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
      AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
      AND C_INVOICE.ISSOTRX = 'Y'
      AND C_INVOICE.PROCESSED = 'Y'
      AND 3=3 AND C_INVOICE.AD_ORG_ID IN ('1')
      AND C_INVOICE.AD_CLIENT_ID IN('14')
      AND 2=2) AA
      WHERE LINENETAMT <> 0
      OR REFUNDAMT <> 0
      OR LINENETREF <> 0
      OR REFUNDAMTREF <> 0
      GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, UOMSYMBOL, 1
     ]]></Sql>
    <Parameter name="nivel1" type="replace" optional="true" after="FROM (SELECT " text="'1'"/>
    <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1, " text="'2'"/>
    <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2, " text="'3'"/>
    <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3, " text="'4'"/>
    <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4, " text="'5'"/>
    <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5, " text="'6'"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND C_INVOICE.AD_ORG_ID IN (" text="'1'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND C_INVOICE.AD_CLIENT_ID IN (" text="'7'"/>
    <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED >= to_date(?)]]></Parameter>
    <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED < to_date(?)]]></Parameter>
    <Parameter name="cBpartnerGroupId" optional="true" after="AND 1=1"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="cBpartner" optional="true" type="argument" after="AND 1=1" text=" AND C_BPARTNER.C_BPARTNER_ID IN"/>
    <Parameter name="mProductCategoryId" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="mProduct" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="salesrepId" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.SALESREP_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="nivel1" type="replace" optional="true" after="UNION ALL SELECT " text="'8'"/>
    <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1 , " text="'9'"/>
    <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2 , " text="'10'"/>
    <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3 , " text="'11'"/>
    <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4 , " text="'12'"/>
    <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5 , " text="'13'"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 3=3 AND C_INVOICE.AD_ORG_ID IN (" text="'1'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND C_INVOICE.AD_CLIENT_ID IN(" text="'14'"/>
    <Parameter name="dateFromRef" optional="true" after="AND 2=2"><![CDATA[AND C_INVOICE.DATEINVOICED >= to_date(?)]]></Parameter>
    <Parameter name="dateToRef" optional="true" after="AND 2=2"><![CDATA[AND C_INVOICE.DATEINVOICED < to_date(?)]]></Parameter>
    <Parameter name="cBpartnerGroupId" optional="true" after="AND 2=2"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="cBpartner" optional="true" type="argument" after="AND 2=2" text=" AND C_BPARTNER.C_BPARTNER_ID IN"/>
    <Parameter name="mProductCategoryId" optional="true" after="AND 2=2"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="mProduct" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="salesrepId" optional="true" after="AND 2=2"><![CDATA[AND C_INVOICE.SALESREP_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="orderby" type="replace" optional="true" after="GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, UOMSYMBOL" text=", 1"/>
  </SqlMethod>
  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>
  <SqlMethod name="selectNoComparative" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, SUM(LINENETAMT) AS LINENETAMT, SUM(REFUNDAMT) AS REFUNDAMT, DIVIDE(SUM(REFUNDAMT), (SUM(LINENETAMT)+SUM(REFUNDAMT)))*100 AS RATIO, SUM(QTYINVOICED) AS QTYINVOICED, SUM(REFUNDQTY) AS REFUNDQTY, SUM(LINENETREF) AS LINENETREF, SUM(REFUNDAMTREF) AS REFUNDAMTREF, SUM(QTYINVOICEDREF) AS QTYINVOICEDREF, SUM(REFUNDQTYREF) AS REFUNDQTYREF, '' AS ID, '' AS NAME, UOMSYMBOL
      FROM (SELECT to_char('1') AS NIVEL1, to_char('2') AS NIVEL2, to_char('3') AS NIVEL3, to_char('4') AS NIVEL4, to_char('5') AS NIVEL5, to_char('6') AS NIVEL6,
      C_INVOICELINE.LINENETAMT AS LINENETAMT, CASE SIGN(C_INVOICELINE.LINENETAMT) WHEN  -1 THEN  -1*C_INVOICELINE.LINENETAMT ELSE  0 END AS REFUNDAMT, C_INVOICELINE.QTYINVOICED AS QTYINVOICED, CASE SIGN(C_INVOICELINE.QTYINVOICED) WHEN  -1 THEN  (-1*C_INVOICELINE.QTYINVOICED) ELSE  0 END AS REFUNDQTY, 0 AS LINENETREF, 0 AS REFUNDAMTREF, 0 AS QTYINVOICEDREF, 0 AS REFUNDQTYREF, C_UOM.UOMSYMBOL
      FROM C_INVOICE left join AD_USER on C_INVOICE.SALESREP_ID = AD_USER.AD_USER_ID,
           C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
                         left join C_UOM on C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID
                         left join M_PRODUCT_CATEGORY on M_PRODUCT.M_PRODUCT_CATEGORY_ID = M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID,
           C_BPARTNER, C_BP_GROUP
      WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
      AND C_BPARTNER.C_BP_GROUP_ID = C_BP_GROUP.C_BP_GROUP_ID
      AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
      AND C_INVOICE.ISSOTRX = 'Y'
      AND C_INVOICE.PROCESSED = 'Y'
      AND 0=0 AND C_INVOICE.AD_ORG_ID IN ('1')
      AND C_INVOICE.AD_CLIENT_ID IN ('7')
      AND 1=1) AA
      WHERE LINENETAMT <> 0
      OR REFUNDAMT <> 0
      OR LINENETREF <> 0
      OR REFUNDAMTREF <> 0
      GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, UOMSYMBOL, 1
     ]]></Sql>
    <Parameter name="nivel1" type="replace" optional="true" after="FROM (SELECT " text="'1'"/>
    <Parameter name="nivel2" type="replace" optional="true" after="AS NIVEL1, " text="'2'"/>
    <Parameter name="nivel3" type="replace" optional="true" after="AS NIVEL2, " text="'3'"/>
    <Parameter name="nivel4" type="replace" optional="true" after="AS NIVEL3, " text="'4'"/>
    <Parameter name="nivel5" type="replace" optional="true" after="AS NIVEL4, " text="'5'"/>
    <Parameter name="nivel6" type="replace" optional="true" after="AS NIVEL5, " text="'6'"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="AND 0=0 AND C_INVOICE.AD_ORG_ID IN (" text="'1'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND C_INVOICE.AD_CLIENT_ID IN (" text="'7'"/>
    <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED >= to_date(?)]]></Parameter>
    <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.DATEINVOICED < to_date(?)]]></Parameter>
    <Parameter name="cBpartnerGroupId" optional="true" after="AND 1=1"><![CDATA[AND C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="cBpartner" optional="true" type="argument" after="AND 1=1" text=" AND C_BPARTNER.C_BPARTNER_ID IN"/>
    <Parameter name="mProductCategoryId" optional="true" after="AND 1=1"><![CDATA[AND M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="mProduct" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND M_PRODUCT.M_PRODUCT_ID IN]]></Parameter>
    <Parameter name="salesrepId" optional="true" after="AND 1=1"><![CDATA[AND C_INVOICE.SALESREP_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="orderby" type="replace" optional="true" after="GROUP BY NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5, NIVEL6, UOMSYMBOL" text=", 1"/>
  </SqlMethod>
  <SqlMethod name="selectBpartner" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT C_BPARTNER_ID AS ID, C_BPARTNER.NAME
      FROM C_BPARTNER
      WHERE AD_ORG_ID IN ('1') 
      AND AD_CLIENT_ID IN ('1') 
      AND (1=2 )
     ]]></Sql>
     <Parameter name="adOrgClient" type="replace" optional="true" after="AD_ORG_ID IN (" text="'1'"/>
     <Parameter name="adUserClient" type="replace" optional="true" after="AND AD_CLIENT_ID IN (" text="'1'"/>
     <Parameter name="cBpartnerId" optional="true" type="argument" after="(1=2" text=" OR C_BPARTNER_ID IN"/>
  </SqlMethod>

  <SqlMethod name="selectMproduct" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT M_PRODUCT.M_PRODUCT_ID AS ID,M_PRODUCT.NAME
      FROM M_PRODUCT
      WHERE AD_ORG_ID IN ('1') 
      AND AD_CLIENT_ID IN ('1') 
      AND (1=2 )
     ]]></Sql>
     <Parameter name="adOrgClient" type="replace" optional="true" after="AD_ORG_ID IN (" text="'1'"/>
     <Parameter name="adUserClient" type="replace" optional="true" after="AND AD_CLIENT_ID IN (" text="'1'"/>
     <Parameter name="mProductId" optional="true" type="argument" after="(1=2" text=" OR M_PRODUCT_ID IN"/>
  </SqlMethod>

  <SqlMethod name="selectBpgroup" type="preparedStatement" return="String" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT C_BP_GROUP.NAME
      FROM C_BP_GROUP
      WHERE C_BP_GROUP.C_BP_GROUP_ID = TO_NUMBER(?)
     ]]></Sql>
    <Parameter name="cBpGroupId"/>
  </SqlMethod>

  <SqlMethod name="selectProductCategory" type="preparedStatement" return="String" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT M_PRODUCT_CATEGORY.NAME
      FROM M_PRODUCT_CATEGORY
      WHERE M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID = TO_NUMBER(?)
     ]]></Sql>
    <Parameter name="mProductCategoryId"/>
  </SqlMethod>

  <SqlMethod name="selectSalesrep" type="preparedStatement" return="String" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT AD_USER.FIRSTNAME||' '||AD_USER.LASTNAME
      FROM AD_USER
      WHERE AD_USER.AD_USER_ID = TO_NUMBER(?)
     ]]></Sql>
    <Parameter name="mWarehouseId"/>
  </SqlMethod>

  <SqlMethod name="selectNotShown" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT ID, NAME
      FROM (SELECT VALUE AS ID, NAME 
              FROM AD_REF_LIST 
             WHERE AD_REFERENCE_ID = 800087
             AND AD_REF_LIST.VALUE IN ('1', '2', '3', '4', '5', '6')
             ORDER BY TO_NUMBER(VALUE)) AA
      WHERE 1=1 
     ]]></Sql>
     <Parameter name="notShown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID NOT IN]]></Parameter>
  </SqlMethod>
  <SqlMethod name="selectShown" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT ID, NAME
      FROM (SELECT VALUE AS ID, NAME 
              FROM AD_REF_LIST 
             WHERE AD_REFERENCE_ID = 800087 
             AND AD_REF_LIST.VALUE IN ('1', '2', '3', '4', '5', '6')
             ORDER BY TO_NUMBER(VALUE)) AA
      WHERE 1=1 
     ]]></Sql>
     <Parameter name="shown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID IN]]></Parameter>
  </SqlMethod>

  <SqlMethod name="selectNotShownTrl" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT ID, NAME
      FROM (SELECT VALUE AS ID, T.NAME 
              FROM AD_REF_LIST_trl T,
                   AD_REF_LIST     L
             WHERE l.AD_REFERENCE_ID = 800087
               AND l.AD_REF_LIST_ID  = t.AD_REF_LIST_ID
               AND L.VALUE IN ('1', '2', '3', '4', '5', '6')
               AND t.AD_LANGUAGE = ?
             ORDER BY TO_NUMBER(VALUE)) AA
      WHERE 1=1 
     ]]></Sql>
     <Parameter name="lang"/> 
     <Parameter name="notShown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID NOT IN]]></Parameter>
  </SqlMethod>
  <SqlMethod name="selectShownTrl" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT ID, NAME
      FROM (SELECT VALUE AS ID, T.NAME 
              FROM AD_REF_LIST_trl T,
                   AD_REF_LIST     L
             WHERE l.AD_REFERENCE_ID = 800087
               AND l.AD_REF_LIST_ID  = t.AD_REF_LIST_ID
               AND L.VALUE IN ('1', '2', '3', '4', '5', '6')
               AND t.AD_LANGUAGE = ?
             ORDER BY TO_NUMBER(VALUE)) AA
      WHERE 1=1 
     ]]></Sql>
     <Parameter name="lang"/> 
     <Parameter name="shown" optional="true" type="argument" after="WHERE 1=1 "><![CDATA[AND ID IN]]></Parameter>
  </SqlMethod>
</SqlClass>