src/org/openbravo/erpCommon/ad_reports/ReportProjectProfitability_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 990 3131cc6e4980
child 1057 dcbe50c4b7bb
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
carlos@0
    22
carlos@0
    23
<SqlClass name="ReportProjectProfitabilityData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    24
  <SqlClassComment></SqlClassComment>
carlos@0
    25
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    26
    <SqlMethodComment></SqlMethodComment>
carlos@0
    27
    <Sql>
carlos@0
    28
      <![CDATA[
carlos@0
    29
        SELECT P.NAME AS PROJECTNAME, P.DATECONTRACT AS INITDATE, BPRESP.NAME AS RESPONSIBLE,
carlos@0
    30
          P.AD_ORG_ID AS ORG, BPCLIENT.NAME AS PARTNER, ORG.NAME AS ORGNAME,PT.NAME AS PROJECTTYPE,
carlos@0
    31
          COALESCE(P.SERVREVENUE,0) AS PLANREVENUE, COALESCE(P.SERVCOST,0) AS PLANCOST, 
carlos@0
    32
          COALESCE(P.EXPREINVOICING,0) AS PLANREINVOICING, COALESCE(P.EXPEXPENSES,0) AS PLANEXPENSES,
carlos@0
    33
          COALESCE(SERREV.AMOUNT,0) AS REALREVENUE, COALESCE(COST.COST,0) AS REALCOST, 
carlos@0
    34
          COALESCE(EXPREI.AMOUNT,0) AS REALREINVOICED, COALESCE(EXPEXP.AMOUNT,0) AS REALEXPENSES,
carlos@0
    35
          COALESCE(COLLECTED.AMOUNT,0) AS COLLECTED, '' AS NODE_ID, '' AS ISSUMMARY
carlos@0
    36
        FROM C_BPartner bpclient, AD_Org org, 
carlos@0
    37
          C_Project p LEFT JOIN C_BPartner bpresp ON  p.Responsible_ID = bpresp.C_BPartner_ID
carlos@0
    38
                      LEFT JOIN C_Projecttype pt ON pt.C_Projecttype_ID = p.C_Projecttype_ID 
miguelangel@510
    39
                      LEFT JOIN (SELECT S_TimeExpenseLine.C_Project_ID, SUM((CASE S_TimeExpenseLine.IsTimereport WHEN 'Y' THEN S_TimeExpenseLine.qty ELSE 0 END)*  C_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(C_BPartner.c_bpartner_id,  s_timeexpense.DATEREPORT), s_timeexpense.DATEREPORT)) AS cost
miguelangel@510
    40
           FROM S_TimeExpenseLine , S_TimeExpense, C_BPartner
carlos@0
    41
           WHERE S_TimeExpense.S_TimeExpense_ID = S_TimeExpenseLine.S_TimeExpense_ID
carlos@0
    42
           AND S_TimeExpense.C_BPartner_ID = C_BPartner.C_BPartner_ID
rafael@990
    43
           AND S_TimeExpense.Processed = 'Y'
rafael@990
    44
           AND 1=1            
carlos@0
    45
           GROUP BY S_TimeExpenseLine.C_Project_ID) cost ON p.C_Project_ID = cost.C_Project_ID
carlos@0
    46
                      LEFT JOIN (SELECT c_Project, sum(amount) as amount
carlos@0
    47
           FROM (
carlos@0
    48
           SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project,
carlos@0
    49
                  CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt ELSE ila.amt END AS amount
carlos@0
    50
           FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID,
carlos@0
    51
             M_Product p
carlos@0
    52
           WHERE i.C_Invoice_ID = il.C_Invoice_ID
carlos@0
    53
             AND il.M_Product_ID = p.M_Product_ID
carlos@0
    54
             AND i.issotrx = 'Y'
carlos@0
    55
             AND i.docStatus IN ('CO', 'CL')
rafael@990
    56
	         AND 2=2
carlos@0
    57
             AND p.producttype = 'S') AAA
carlos@0
    58
           GROUP BY c_Project) serrev ON p.C_Project_ID = serrev.C_Project
carlos@0
    59
                      LEFT JOIN (SELECT c_Project, sum(amount) as amount
carlos@0
    60
           FROM (
carlos@0
    61
           SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project,
carlos@0
    62
                  CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt ELSE ila.amt END AS amount
carlos@0
    63
           FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID,
carlos@0
    64
             M_Product p
carlos@0
    65
           WHERE i.C_Invoice_ID = il.C_Invoice_ID
carlos@0
    66
             AND il.M_Product_ID = p.M_Product_ID
carlos@0
    67
             AND i.issotrx = 'Y'
carlos@0
    68
             AND i.docStatus IN ('CO', 'CL')
rafael@990
    69
	         AND 3=3
carlos@0
    70
             AND p.producttype <> 'S') BBB
carlos@0
    71
           GROUP BY c_Project) exprei ON p.C_Project_ID = exprei.C_Project
carlos@0
    72
                      LEFT JOIN (SELECT c_Project, sum(amount) as amount
carlos@0
    73
           FROM (
carlos@0
    74
           SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project,
carlos@0
    75
                  CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt ELSE ila.amt END AS amount
carlos@0
    76
           FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID,
carlos@0
    77
             M_Product p
carlos@0
    78
           WHERE i.C_Invoice_ID = il.C_Invoice_ID
carlos@0
    79
           AND il.M_Product_ID = p.M_Product_ID
carlos@0
    80
           AND i.docStatus IN ('CO', 'CL')
rafael@990
    81
	   	   AND 4=4
rafael@990
    82
           AND i.issotrx = 'N') CCC
carlos@0
    83
           GROUP BY c_Project) expexp ON p.C_Project_ID = expexp.C_Project
carlos@0
    84
                      LEFT JOIN (SELECT c_Project, sum(amount) as amount
carlos@0
    85
           FROM (
carlos@0
    86
           SELECT CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN i.c_project_id ELSE ila.c_project_id END AS C_Project,
gorka@302
    87
                  CASE WHEN ila.C_InvoiceLine_AcctDimension_ID IS NULL THEN il.linenetamt*C_Invoice_Percentage_Paid(i.C_Invoice_ID) ELSE ila.amt*C_Invoice_Percentage_Paid(i.C_Invoice_ID) END AS amount
carlos@0
    88
           FROM C_Invoice i, C_InvoiceLine il LEFT JOIN C_InvoiceLine_AcctDimension ila on il.C_InvoiceLine_ID = ila.C_InvoiceLine_ID,
carlos@0
    89
             M_Product p
carlos@0
    90
           WHERE i.C_Invoice_ID = il.C_Invoice_ID
carlos@0
    91
           AND il.M_Product_ID = p.M_Product_ID
carlos@0
    92
           AND i.docStatus IN ('CO', 'CL')
rafael@990
    93
	   	   AND 5=5
carlos@0
    94
             AND i.issotrx = 'Y') DDD
carlos@0
    95
           GROUP BY c_Project) collected ON p.C_Project_ID = collected.C_Project
carlos@0
    96
        WHERE p.C_BPartner_ID = bpclient.C_BPartner_ID
carlos@0
    97
          AND p.AD_Org_ID = org.AD_Org_ID
carlos@0
    98
          AND p.AD_Org_ID IN ('1')
asier@683
    99
          AND p.AD_Client_ID IN ('1')
rafael@990
   100
          AND 6=6
carlos@0
   101
        ORDER BY orgname, partner, initdate
carlos@0
   102
      ]]>
carlos@0
   103
    </Sql>
rafael@990
   104
    <Parameter name="datefrom2" optional="true" after="AND 1=1"><![CDATA[ AND s_timeexpense.DATEREPORT >= to_date(?) ]]></Parameter>
rafael@990
   105
    <Parameter name="dateto2" optional="true" after="AND 1=1"><![CDATA[ AND s_timeexpense.DATEREPORT  <= to_date(?) ]]></Parameter>
rafael@990
   106
    <Parameter name="datefrom2" optional="true" after="AND 2=2"><![CDATA[ AND i.dateinvoiced >= to_date(?) ]]></Parameter>
rafael@990
   107
    <Parameter name="dateto2" optional="true" after="AND 2=2"><![CDATA[ AND i.dateinvoiced  <= to_date(?) ]]></Parameter>
rafael@990
   108
    <Parameter name="datefrom2" optional="true" after="AND 3=3"><![CDATA[ AND i.dateinvoiced >= to_date(?) ]]></Parameter>
rafael@990
   109
    <Parameter name="dateto2" optional="true" after="AND 3=3"><![CDATA[ AND i.dateinvoiced  <= to_date(?) ]]></Parameter>
rafael@990
   110
    <Parameter name="datefrom2" optional="true" after="AND 4=4"><![CDATA[ AND i.dateinvoiced >= to_date(?) ]]></Parameter>
rafael@990
   111
    <Parameter name="dateto2" optional="true" after="AND 4=4"><![CDATA[ AND i.dateinvoiced  <= to_date(?) ]]></Parameter>
rafael@990
   112
    <Parameter name="datefrom2" optional="true" after="AND 5=5"><![CDATA[ AND i.dateinvoiced >= to_date(?) ]]></Parameter>
rafael@990
   113
    <Parameter name="dateto2" optional="true" after="AND 5=5"><![CDATA[ AND i.dateinvoiced  <= to_date(?) ]]></Parameter>
carlos@0
   114
    <Parameter name="org" optional="true" type="replace" after="AND p.AD_Org_ID IN (" text="'1'"/>
asier@683
   115
    <Parameter name="client" optional="true" type="replace" after="AND p.AD_Client_ID IN (" text="'1'"/>
rafael@990
   116
    <Parameter name="dateFrom" optional="true" after="AND 6=6"><![CDATA[ AND p.Datecontract > to_date(?) ]]></Parameter>
rafael@990
   117
    <Parameter name="dateTo" optional="true" after="AND 6=6"><![CDATA[ AND p.Datecontract < to_date(?) ]]></Parameter>
rafael@990
   118
    <Parameter name="projecttype" optional="true" after="AND 6=6"><![CDATA[ AND p.C_Projecttype_ID = TO_NUMBER(?) ]]></Parameter>
rafael@990
   119
    <Parameter name="project" optional="true" after="AND 6=6"><![CDATA[ AND p.C_Project_ID = TO_NUMBER(?) ]]></Parameter>
rafael@990
   120
    <Parameter name="Responsible" optional="true" after="AND 6=6"><![CDATA[ AND p.Responsible_ID = TO_NUMBER(?) ]]></Parameter>
rafael@990
   121
    <Parameter name="Partner" optional="true" after="AND 6=6"><![CDATA[ AND p.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
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
    <Parameter name="planrevenue"/>
carlos@0
   127
    <Parameter name="plancost"/>
carlos@0
   128
    <Parameter name="planreinvoicing"/>
carlos@0
   129
    <Parameter name="planexpenses"/>
carlos@0
   130
    <Parameter name="realrevenue"/>
carlos@0
   131
    <Parameter name="realcost"/>
carlos@0
   132
    <Parameter name="realreinvoiced"/>
carlos@0
   133
    <Parameter name="realexpenses"/>
carlos@0
   134
    <Parameter name="collected"/>
carlos@0
   135
    <Parameter name="org"/>
carlos@0
   136
    <Parameter name="partner"/>
carlos@0
   137
  </SqlMethod>
carlos@0
   138
    <SqlMethod name="selectOrg" type="preparedStatement" return="multiple">
carlos@0
   139
    <SqlMethodComment></SqlMethodComment>
carlos@0
   140
    <Sql>
carlos@0
   141
      <![CDATA[
carlos@0
   142
      SELECT AD_TREENODE.NODE_ID, AD_ORG.ISSUMMARY
carlos@0
   143
      FROM AD_CLIENTINFO, AD_TREENODE, AD_ORG
carlos@0
   144
      WHERE AD_CLIENTINFO.AD_TREE_ORG_ID = AD_TREENODE.AD_TREE_ID
carlos@0
   145
      AND AD_TREENODE.NODE_ID = AD_ORG.AD_ORG_ID
carlos@423
   146
      AND AD_TREENODE.PARENT_ID = TO_NUMBER(?)
carlos@423
   147
      AND AD_CLIENTINFO.AD_CLIENT_ID = TO_NUMBER(?)
carlos@0
   148
      ORDER BY seqno
carlos@0
   149
      ]]>
carlos@0
   150
    </Sql>
carlos@0
   151
    <Parameter name="adOrgId"/>
carlos@0
   152
    <Parameter name="adClientId"/>
carlos@0
   153
  </SqlMethod>
carlos@0
   154
</SqlClass>