src/org/openbravo/erpCommon/ad_reports/ReportNotPosted_data.xsql
author David Baz Fayos <david.baz@openbravo.com>
Tue, 27 May 2008 15:26:10 +0000
changeset 1044 8691bbc94032
parent 979 36cb9aecf607
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="ReportNotPostedData" package="org.openbravo.erpCommon.ad_reports">
  <SqlClassComment>Clase ReportNotPostedData</SqlClassComment>
  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment>Select for relation</SqlMethodComment>
    <Sql>
    <![CDATA[
        select documentno, dateacct, name as description, GRANDTOTAL  as amount, document as doctype
        from
        (select ad_client_id, documentno, dateacct, ad_column_identifier('C_Bpartner',to_char(C_bpartner_id), TO_CHAR(?)) as name, GRANDTOTAL, 'Sales Invoices' as document
        from c_invoice
        where processed = 'Y'
        and posted = 'N'
        and issotrx = 'Y'
        union
        select ad_client_id, documentno, dateacct, ad_column_identifier('C_Bpartner',to_char(C_bpartner_id), TO_CHAR(?)) as name, 0 as GRANDTOTAL, 'Goods Receipts' as document
        from m_inout
        where processed = 'Y'
        and posted = 'N'
        and issotrx = 'N'
        union
        select ad_client_id, documentno, dateacct, ad_column_identifier('C_Bpartner',to_char(C_bpartner_id), TO_CHAR(?)), GRANDTOTAL, 'Purchase Invoices'
        from c_invoice
        where processed = 'Y'
        and posted = 'N'
        and issotrx = 'N'
        union
        select ad_client_id, name, dateacct, ad_column_identifier('C_Cash',to_char(C_Cash_id), TO_CHAR(?)), c_cash.STATEMENTDIFFERENCE, 'Cash Journal'
        from C_Cash
        where processed = 'Y'
        and posted = 'N'
        union
        select ad_client_id, name, statementdate, ad_column_identifier('C_Bankstatement',to_char(C_bankstatement_id), TO_CHAR(?)), STATEMENTDIFFERENCE, 'Bank Statements'
        from c_bankstatement
        where processed = 'Y'
        and posted = 'N'
        union
        select ad_client_id, documentno, dateacct, ad_column_identifier('C_Settlement',to_char(C_Settlement_id), TO_CHAR(?)), generatedamt, 'Manual Settlements'
        from c_Settlement
        where processed = 'Y'
        and posted = 'N'
        and settlementtype = 'I'
        union
        select ad_client_id, documentno, dateacct, ad_column_identifier('C_Settlement',to_char(C_Settlement_id), TO_CHAR(?)), generatedamt, 'Settlements'
        from c_Settlement
        where processed = 'Y'
        and posted = 'N'
        and settlementtype <> 'I'
        union
        select ad_client_id, documentno, dateacct, ad_column_identifier('GL_Journal',to_char(GL_Journal_id), TO_CHAR(?)), totaldr, 'GL Journal'
        from GL_Journal
        where processed = 'Y'
        and posted = 'N') AAA
        where dateacct >= TO_DATE(?)
        and dateacct <= TO_DATE(?)
        and ad_client_ID = TO_NUMBER(?)
        order by  document, dateacct, name
      ]]></Sql>
    <Parameter name="adLanguage"/>
    <Parameter name="adLanguage"/>
    <Parameter name="adLanguage"/>
    <Parameter name="adLanguage"/>
    <Parameter name="adLanguage"/>
    <Parameter name="adLanguage"/>
    <Parameter name="adLanguage"/>
    <Parameter name="adLanguage"/>
    <Parameter name="dateFrom"></Parameter>
    <Parameter name="dateTo"></Parameter>
    <Parameter name="client"></Parameter>
  </SqlMethod>
  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment>Create a registry</SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>
</SqlClass>