src/org/openbravo/erpCommon/ad_reports/ReportBudgetGenerateExcel_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 732 88d93ac777a9
child 1604 a1c518e6ed93
permissions -rw-r--r--
Removed old frame parameter of windowTableId
carlos@0
     1
<?xml version="1.0" encoding="UTF-8" ?>
carlos@0
     2
<!--
carlos@0
     3
 *************************************************************************
carlos@0
     4
 * The contents of this file are subject to the Openbravo  Public  License
carlos@0
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
carlos@0
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
carlos@0
     7
 * file except in compliance with the License. You  may  obtain  a copy of
carlos@0
     8
 * the License at http://www.openbravo.com/legal/license.html 
carlos@0
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
carlos@0
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
carlos@0
    11
 * License for the specific  language  governing  rights  and  limitations
carlos@0
    12
 * under the License. 
carlos@0
    13
 * The Original Code is Openbravo ERP. 
carlos@0
    14
 * The Initial Developer of the Original Code is Openbravo SL 
carlos@0
    15
 * All portions are Copyright (C) 2001-2006 Openbravo SL 
carlos@0
    16
 * All Rights Reserved. 
carlos@0
    17
 * Contributor(s):  ______________________________________.
carlos@0
    18
 ************************************************************************
carlos@0
    19
-->
carlos@0
    20
carlos@0
    21
<SqlClass name="ReportBudgetGenerateExcelData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    22
  <SqlClassComment></SqlClassComment>
pablo@711
    23
pablo@711
    24
pablo@711
    25
carlos@0
    26
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    27
    <SqlMethodComment></SqlMethodComment>
carlos@0
    28
    <Sql>
carlos@0
    29
    <![CDATA[
pablo@711
    30
      SELECT  ' ' AS PARTNER, ' ' AS PARTNERGROUP, ' ' AS PRODUCT, ' ' AS PRODCATEGORY, /*' ' AS USER1, ' ' AS USER2,*/ ' ' AS SALESREGION, ' ' AS CAMPAIGN, ' ' AS ACTIVITY, ' ' AS PROJECT, ' ' AS TRXORG, ' ' AS MONTH, ' ' AS VALIDCOMBINATION, ' ' AS ACCOUNTSCHEMA, '' AS CURRENCY, 0 AS QTY, 0 AS PRICE, ' ' AS AMOUNT, '' AS NAME, '' AS ID, '' AS DESCRIPTION, '' AS ACTUAL, ' ' AS PADRE, '' AS EXPORTACTUAL
carlos@0
    31
      FROM DUAL
carlos@0
    32
     ]]></Sql>
pablo@711
    33
     <Parameter name="columns" optional="true" type="replace" after="SELECT  " text="' ' AS PARTNER, ' ' AS PARTNERGROUP, ' ' AS PRODUCT, ' ' AS PRODCATEGORY, /*' ' AS USER1, ' ' AS USER2,*/ ' ' AS SALESREGION, ' ' AS CAMPAIGN, ' ' AS ACTIVITY, ' ' AS PROJECT, ' ' AS TRXORG, ' ' AS MONTH, ' ' AS VALIDCOMBINATION, ' ' AS ACCOUNTSCHEMA, '' AS CURRENCY"/>
carlos@0
    34
     <Parameter name="tables" optional="true" type="replace" after=" DUAL" text="DUAL"/>
carlos@0
    35
  </SqlMethod>
pablo@711
    36
pablo@711
    37
pablo@711
    38
   <SqlMethod name="selectAccounts" type="preparedStatement" return="multiple">
pablo@711
    39
      <SqlMethodComment></SqlMethodComment>
pablo@711
    40
      <Sql><![CDATA[
pablo@711
    41
		SELECT V.C_ELEMENTVALUE_ID AS ID, S.C_ACCTSCHEMA_ID AS PADRE, AD_COLUMN_IDENTIFIER('C_ELEMENTVALUE', TO_CHAR(V.C_ELEMENTVALUE_ID), ?) AS NAME 
pablo@711
    42
		FROM C_ACCTSCHEMA_ELEMENT S, C_ELEMENTVALUE V
pablo@711
    43
		WHERE S.C_ELEMENT_ID = V.C_ELEMENT_ID
pablo@711
    44
		AND V.ELEMENTLEVEL = 'S'
pablo@711
    45
		AND V.AD_ORG_ID IN ('1')
pablo@711
    46
		AND V.AD_CLIENT_ID IN ('1')
pablo@711
    47
		ORDER BY V.VALUE
pablo@711
    48
      ]]></Sql>
pablo@711
    49
	  <Parameter name="adLanguage"/>
pablo@711
    50
	  <Parameter name="org" optional="true" type="replace" after="AND V.AD_ORG_ID IN (" text="'1'"/>      
pablo@711
    51
	  <Parameter name="client" optional="true" type="replace" after="AND V.AD_CLIENT_ID IN (" text="'1'"/>      
pablo@711
    52
   </SqlMethod>
pablo@711
    53
carlos@0
    54
  <SqlMethod name="selectLines" type="preparedStatement" return="multiple">
carlos@0
    55
    <SqlMethodComment></SqlMethodComment> 
carlos@0
    56
    <Sql>
carlos@0
    57
    <![CDATA[
pablo@711
    58
		SELECT QTY, PRICE, AMOUNT, B.DESCRIPTION, (
pablo@711
    59
		  SELECT ISO_CODE 
pablo@711
    60
		  FROM C_CURRENCY 
pablo@711
    61
		  WHERE C_CURRENCY_ID=B.C_CURRENCY_ID
pablo@711
    62
		  ) AS CURRENCY,    
carlos@732
    63
		  CASE WHEN B.C_BPartner_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('C_BPARTNER', TO_CHAR(B.C_BPARTNER_ID), TO_CHAR(?)) END AS PARTNER ,    
carlos@732
    64
		  CASE WHEN C_BP_GROUP_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('C_BP_GROUP', TO_CHAR(C_BP_GROUP_ID), TO_CHAR(?)) END AS PARTNERGROUP,    
carlos@732
    65
		  CASE WHEN B.M_PRODUCT_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('M_PRODUCT', TO_CHAR(B.M_PRODUCT_ID), TO_CHAR(?)) END AS PRODUCT,    
carlos@732
    66
		  CASE WHEN M_PRODUCT_CATEGORY_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('M_PRODUCT_CATEGORY', TO_CHAR(M_PRODUCT_CATEGORY_ID), TO_CHAR(?)) END AS PRODCATEGORY,    
carlos@732
    67
		  CASE WHEN B.C_SALESREGION_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('C_SALESREGION', TO_CHAR(B.C_SALESREGION_ID), TO_CHAR(?)) END AS SALESREGION,    
carlos@732
    68
		  CASE WHEN B.C_CAMPAIGN_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('C_CAMPAIGN', TO_CHAR(B.C_CAMPAIGN_ID), TO_CHAR(?)) END AS CAMPAIGN,    
carlos@732
    69
		  CASE WHEN B.C_ACTIVITY_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('C_ACTIVITY', TO_CHAR(B.C_ACTIVITY_ID), TO_CHAR(?)) END AS ACTIVITY,    
carlos@732
    70
		  CASE WHEN B.C_PROJECT_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('C_PROJECT', TO_CHAR(B.C_PROJECT_ID), TO_CHAR(?)) END AS PROJECT,    
carlos@732
    71
		  CASE WHEN B.AD_ORG_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('AD_ORG', TO_CHAR(B.AD_ORG_ID), TO_CHAR(?)) END AS TRXORG,    
pablo@711
    72
		  CASE WHEN C_PERIOD_ID IS NULL THEN ' '       ELSE AD_COLUMN_IDENTIFIER('AD_MONTH', (        
pablo@711
    73
		    SELECT TO_CHAR(AD_MONTH_ID)         
carlos@732
    74
		    FROM AD_MONTH WHERE TO_NUMBER(VALUE) = (          
pablo@711
    75
		      SELECT PERIODNO           
pablo@711
    76
		      FROM C_PERIOD           
carlos@732
    77
		      WHERE C_PERIOD.C_PERIOD_ID=B.C_PERIOD_ID)        ), TO_CHAR(?)      
pablo@711
    78
		    ) END AS MONTH,    
pablo@711
    79
		  C_BUDGET.EXPORTACTUAL,
pablo@711
    80
		  CASE WHEN C_BUDGET.EXPORTACTUAL = 'Y' THEN        (        
carlos@732
    81
		    SELECT CASE WHEN E.ACCOUNTSIGN='C' THEN SUM(AMTACCTCR-AMTACCTDR) ELSE SUM(AMTACCTDR-AMTACCTCR) END AS AMOUNT        
pablo@711
    82
		    FROM FACT_ACCT F, C_ELEMENTVALUE E        
pablo@711
    83
		    WHERE        
pablo@711
    84
		    COALESCE(B.C_ELEMENTVALUE_ID,1)     = CASE WHEN B.C_ELEMENTVALUE_ID     IS NULL THEN 1 ELSE E.C_ELEMENTVALUE_ID     END AND          
pablo@711
    85
		    COALESCE(B.C_ELEMENTVALUE_ID,1)     = CASE WHEN B.C_ELEMENTVALUE_ID     IS NULL THEN 1 ELSE F.ACCOUNT_ID            END AND          
pablo@711
    86
		    COALESCE(B.C_PERIOD_ID,1)           = CASE WHEN B.C_PERIOD_ID           IS NULL THEN 1 ELSE F.C_PERIOD_ID           END AND          
pablo@711
    87
		    COALESCE(B.C_BPARTNER_ID,1)         = CASE WHEN B.C_BPARTNER_ID         IS NULL THEN 1 ELSE F.C_BPARTNER_ID         END AND          
pablo@711
    88
		    COALESCE(B.M_PRODUCT_ID,1)          = CASE WHEN B.M_PRODUCT_ID          IS NULL THEN 1 ELSE F.M_PRODUCT_ID          END AND          
pablo@711
    89
		    COALESCE(B.C_SALESREGION_ID,1)      = CASE WHEN B.C_SALESREGION_ID      IS NULL THEN 1 ELSE F.C_SALESREGION_ID      END AND          
pablo@711
    90
		    COALESCE(B.C_CAMPAIGN_ID,1)         = CASE WHEN B.C_CAMPAIGN_ID         IS NULL THEN 1 ELSE F.C_CAMPAIGN_ID         END AND          
pablo@711
    91
		    COALESCE(B.C_ACTIVITY_ID,1)         = CASE WHEN B.C_ACTIVITY_ID         IS NULL THEN 1 ELSE F.C_ACTIVITY_ID         END AND          
pablo@711
    92
		    COALESCE(B.C_PROJECT_ID,1)          = CASE WHEN B.C_PROJECT_ID          IS NULL THEN 1 ELSE F.C_PROJECT_ID          END AND          
pablo@711
    93
		    COALESCE(B.M_PRODUCT_CATEGORY_ID,1) = CASE WHEN B.M_PRODUCT_CATEGORY_ID IS NULL THEN 1 ELSE (SELECT MAX(M_PRODUCT_CATEGORY_ID) 
pablo@711
    94
		                                                                                                            FROM M_PRODUCT 
pablo@711
    95
		                                                                                                            WHERE M_PRODUCT_ID=F.M_PRODUCT_ID
pablo@711
    96
		                                                                                                            AND M_PRODUCT_ID IS NOT NULL)          END AND          
pablo@711
    97
		    COALESCE(B.C_BP_GROUP_ID,1)         = CASE WHEN B.C_BP_GROUP_ID         IS NULL THEN 1 ELSE (SELECT MAX(C_BP_GROUP_ID) 
pablo@711
    98
		                                                                                                            FROM C_BPARTNER 
pablo@711
    99
		                                                                                                            WHERE C_BPARTNER_ID=F.C_BPARTNER_ID
pablo@711
   100
		                                                                                                            AND C_BPARTNER_ID IS NOT NULL)          END AND          
pablo@711
   101
		    COALESCE(B.AD_ORG_ID,1)             = CASE WHEN B.AD_ORG_ID             IS NULL THEN 1 ELSE F.AD_ORG_ID             END          
pablo@711
   102
		    GROUP BY E.ACCOUNTSIGN        )        ELSE -1 END AS ACTUAL,         
carlos@732
   103
		  AD_COLUMN_IDENTIFIER('C_ELEMENTVALUE', TO_CHAR(B.C_ELEMENTVALUE_ID), TO_CHAR(?)) AS VALIDCOMBINATION,        
carlos@732
   104
		  AD_COLUMN_IDENTIFIER('C_ACCTSCHEMA', TO_CHAR(B.C_ACCTSCHEMA_ID), TO_CHAR(?)) AS ACCOUNTSCHEMA      
pablo@711
   105
		FROM    C_BUDGETLINE B, C_BUDGET      
pablo@711
   106
		WHERE   B.C_BUDGET_ID = C_BUDGET.C_BUDGET_ID                  
pablo@711
   107
		  AND B.C_BUDGET_ID = TO_NUMBER(?)
carlos@0
   108
     ]]></Sql>
carlos@0
   109
     <Parameter name="adLanguage"/>
carlos@0
   110
     <Parameter name="adLanguage"/>
carlos@0
   111
     <Parameter name="adLanguage"/>
carlos@0
   112
     <Parameter name="adLanguage"/>
carlos@0
   113
     <Parameter name="adLanguage"/>
carlos@0
   114
     <Parameter name="adLanguage"/>
carlos@0
   115
     <Parameter name="adLanguage"/>
carlos@0
   116
     <Parameter name="adLanguage"/>
carlos@0
   117
     <Parameter name="adLanguage"/>
carlos@0
   118
     <Parameter name="adLanguage"/>
carlos@0
   119
     <Parameter name="adLanguage"/>
pablo@711
   120
     <Parameter name="adLanguage"/>
carlos@0
   121
     <Parameter name="cbudgetid"/>
carlos@0
   122
  </SqlMethod>
carlos@0
   123
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
   124
      <SqlMethodComment></SqlMethodComment>
carlos@0
   125
      <Sql></Sql>
carlos@0
   126
  </SqlMethod>
carlos@0
   127
  <SqlMethod name="selectMonth" type="preparedStatement" return="multiple">
carlos@0
   128
    <SqlMethodComment></SqlMethodComment>
carlos@0
   129
    <Sql>
carlos@0
   130
    <![CDATA[
carlos@0
   131
      SELECT AD_MONTH_ID AS ID, NAME
carlos@0
   132
      FROM AD_MONTH
carlos@0
   133
    ]]></Sql>
carlos@0
   134
  </SqlMethod>
carlos@0
   135
</SqlClass>