src/org/openbravo/erpCommon/ad_reports/ReportExpense_data.xsql
author Rafa Roda Palacios <rafael.roda@openbravo.com>
Fri, 20 Jun 2008 14:59:01 +0000
changeset 1312 5efc9ced4fbd
parent 423 ecf368072c48
child 1319 8059d8a44bc8
permissions -rw-r--r--
Fixes bug 0003957 Expense Report has been completely reviewed and now:
* Business Partner and Project selectors work as usual, that is, when typing search value and enter key, field is auto-completed.
* Records display in Employee drop-down.
* Time sheets and normal expenses can be differentiated.
* There is a Project filter and grouping level.
* Processed column values are translated.
<?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-2008 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->

<SqlClass name="ReportExpenseData" package="org.openbravo.erpCommon.ad_reports">
  <SqlClassComment></SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT CBE.NAME AS EMPLOYEE, (CASE S_L.ISTIMEREPORT WHEN 'Y' THEN S_L.QTY ELSE 0 END) * C_CALCULATECOST_CATSALARY(C_CALCULATE_CATSALARY(s.c_bpartner_id,  (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE END)), (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE END)) AS COST, 
	      CBC.NAME AS NAME, (P.VALUE || ' - ' ||P.NAME) AS DESCR, M_PRODUCT.NAME AS PRODUCTNAME, C_UOM.NAME AS UOMNAME,
	      (CASE S_L.ISTIMEREPORT WHEN 'N' THEN S_L.QTY ELSE '0' END) AS QTY, (CASE S_L.ISTIMEREPORT WHEN 'Y' THEN S_L.QTY ELSE '0' END) AS HORAS, 
	      (CASE WHEN S_L.DATEEXPENSE IS NULL THEN S.DATEREPORT ELSE S_L.DATEEXPENSE END) AS DATEEXPENSE, 
	      (CASE WHEN S_L.ISTIMEREPORT ='N' THEN (CASE WHEN S_L.CONVERTEDAMT IS NOT NULL THEN S_L.CONVERTEDAMT ELSE COALESCE(S_L.EXPENSEAMT,0) END) ELSE 0 END) AS INVOICEPRICE, 
	      AD_MESSAGE_GET2(S.PROCESSED,?) AS PROCESSED, (CASE WHEN S_L.DESCRIPTION IS NULL THEN S.DESCRIPTION ELSE S_L.DESCRIPTION END) AS DESCRIPTION, S_L.S_TIMEEXPENSELINE_ID, S.DOCUMENTNO
      FROM S_TIMEEXPENSE S, C_BPARTNER CBE, M_PRODUCT, C_UOM, S_TIMEEXPENSELINE S_L
      LEFT JOIN C_PROJECT P ON S_L.C_PROJECT_ID = P.C_PROJECT_ID
      LEFT JOIN C_BPARTNER CBC ON S_L.C_BPARTNER_ID = CBC.C_BPARTNER_ID
      WHERE S.S_TIMEEXPENSE_ID = S_L.S_TIMEEXPENSE_ID
	      AND s.C_BPARTNER_ID=CBE.C_BPARTNER_ID
	      AND S_L.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
	      AND S_L.C_UOM_ID = C_UOM.C_UOM_ID
	      AND S.AD_CLIENT_ID IN ('1')
	      AND S.AD_ORG_ID IN ('1')
	      AND 1=1
      ORDER BY NAME, DESCR, EMPLOYEE, PRODUCTNAME, DATEEXPENSE DESC
  ]]></Sql>
    <Field name="rownum" value="count"/>
    <Parameter name="adLanguage"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND S.AD_CLIENT_ID IN (" text="'1'"/>
    <Parameter name="adOrgClient" type="replace" optional="true" after="AND S.AD_ORG_ID IN (" text="'1'"/>
    <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND S.DATEREPORT >= TO_DATE(?)]]></Parameter>
    <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND S.DATEREPORT < TO_DATE(?)]]></Parameter>
    <Parameter name="cBpartner" optional="true" after="AND 1=1"><![CDATA[ AND S_L.C_BPARTNER_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="partner" optional="true" after="AND 1=1"><![CDATA[ AND S.C_BPARTNER_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="project" optional="true" after="AND 1=1"><![CDATA[ AND S_L.C_PROJECT_ID = TO_NUMBER(?)]]></Parameter>
    <Parameter name="time" optional="true" after="AND 1=1"><![CDATA[ AND S_L.ISTIMEREPORT = ?]]></Parameter>
    <Parameter name="expense" optional="true" after="AND 1=1"><![CDATA[ AND S_L.ISTIMEREPORT = ?]]></Parameter>
  </SqlMethod>

  <SqlMethod name="selectBpartner" type="preparedStatement" return="String" default="">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
    <![CDATA[
      SELECT C_BPARTNER.NAME
      FROM C_BPARTNER
      WHERE C_BPARTNER.C_BPARTNER_ID = TO_NUMBER(?)
     ]]></Sql>
    <Parameter name="cBpartnerId"/>
  </SqlMethod>

  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>
</SqlClass>