src/org/openbravo/erpCommon/ad_reports/ReportNotPosted_data.xsql
author David Alsasua <david.alsasua@openbravo.com>
Mon, 01 Jun 2009 18:30:49 +0200
changeset 3940 df2da712501b
parent 1605 8a0fe0193bef
child 3945 02f5d14bcacb
permissions -rw-r--r--
Fixes Issue 9254: The Report Not Posted shouldn't show the good shipments and receipts if they are not being posted
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
<SqlClass name="ReportNotPostedData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    21
  <SqlClassComment>Clase ReportNotPostedData</SqlClassComment>
carlos@0
    22
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    23
    <SqlMethodComment>Select for relation</SqlMethodComment>
carlos@0
    24
    <Sql>
carlos@0
    25
    <![CDATA[
carlos@0
    26
        select documentno, dateacct, name as description, GRANDTOTAL  as amount, document as doctype
carlos@0
    27
        from
carlos@979
    28
        (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
carlos@0
    29
        from c_invoice
carlos@0
    30
        where processed = 'Y'
carlos@0
    31
        and posted = 'N'
carlos@0
    32
        and issotrx = 'Y'
david@3940
    33
        and 'Y'=(select isactive from c_acctschema_table where ad_table_id='318' and ad_client_id=?)
carlos@0
    34
        union
carlos@979
    35
        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
carlos@0
    36
        from m_inout
carlos@0
    37
        where processed = 'Y'
carlos@0
    38
        and posted = 'N'
carlos@0
    39
        and issotrx = 'N'
david@3940
    40
        and 'Y'=(select isactive from c_acctschema_table where ad_table_id='319' and ad_client_id=?)
carlos@0
    41
        union
carlos@979
    42
        select ad_client_id, documentno, dateacct, ad_column_identifier('C_Bpartner',to_char(C_bpartner_id), TO_CHAR(?)), GRANDTOTAL, 'Purchase Invoices'
carlos@0
    43
        from c_invoice
carlos@0
    44
        where processed = 'Y'
carlos@0
    45
        and posted = 'N'
carlos@0
    46
        and issotrx = 'N'
david@3940
    47
        and 'Y'=(select isactive from c_acctschema_table where ad_table_id='318' and ad_client_id=?)
carlos@0
    48
        union
carlos@979
    49
        select ad_client_id, name, dateacct, ad_column_identifier('C_Cash',to_char(C_Cash_id), TO_CHAR(?)), c_cash.STATEMENTDIFFERENCE, 'Cash Journal'
carlos@0
    50
        from C_Cash
carlos@0
    51
        where processed = 'Y'
carlos@0
    52
        and posted = 'N'
david@3940
    53
        and 'Y'=(select isactive from c_acctschema_table where ad_table_id='407' and ad_client_id=?)
carlos@0
    54
        union
carlos@979
    55
        select ad_client_id, name, statementdate, ad_column_identifier('C_Bankstatement',to_char(C_bankstatement_id), TO_CHAR(?)), STATEMENTDIFFERENCE, 'Bank Statements'
carlos@0
    56
        from c_bankstatement
carlos@0
    57
        where processed = 'Y'
carlos@0
    58
        and posted = 'N'
david@3940
    59
        and 'Y'=(select isactive from c_acctschema_table where ad_table_id='392' and ad_client_id=?)
carlos@0
    60
        union
carlos@979
    61
        select ad_client_id, documentno, dateacct, ad_column_identifier('C_Settlement',to_char(C_Settlement_id), TO_CHAR(?)), generatedamt, 'Manual Settlements'
carlos@0
    62
        from c_Settlement
carlos@0
    63
        where processed = 'Y'
carlos@0
    64
        and posted = 'N'
carlos@0
    65
        and settlementtype = 'I'
david@3940
    66
        and 'Y'=(select isactive from c_acctschema_table where ad_table_id='800019' and ad_client_id=?)
carlos@0
    67
        union
carlos@979
    68
        select ad_client_id, documentno, dateacct, ad_column_identifier('C_Settlement',to_char(C_Settlement_id), TO_CHAR(?)), generatedamt, 'Settlements'
carlos@0
    69
        from c_Settlement
carlos@0
    70
        where processed = 'Y'
carlos@0
    71
        and posted = 'N'
carlos@0
    72
        and settlementtype <> 'I'
david@3940
    73
        and 'Y'=(select isactive from c_acctschema_table where ad_table_id='800019' and ad_client_id=?)
carlos@0
    74
        union
carlos@979
    75
        select ad_client_id, documentno, dateacct, ad_column_identifier('GL_Journal',to_char(GL_Journal_id), TO_CHAR(?)), totaldr, 'GL Journal'
carlos@0
    76
        from GL_Journal
carlos@0
    77
        where processed = 'Y'
david@3940
    78
        and posted = 'N'
david@3940
    79
        and 'Y'=(select isactive from c_acctschema_table where ad_table_id='224' and ad_client_id=?)) AAA
juanpablo@1605
    80
        where dateacct >= ?
juanpablo@1605
    81
        and dateacct <= ?
juanpablo@1605
    82
        and ad_client_id = ?
carlos@0
    83
        order by  document, dateacct, name
carlos@0
    84
      ]]></Sql>
carlos@979
    85
    <Parameter name="adLanguage"/>
david@3940
    86
    <Parameter name="client"></Parameter>
carlos@979
    87
    <Parameter name="adLanguage"/>
david@3940
    88
    <Parameter name="client"></Parameter>
carlos@979
    89
    <Parameter name="adLanguage"/>
david@3940
    90
    <Parameter name="client"></Parameter>
carlos@979
    91
    <Parameter name="adLanguage"/>
david@3940
    92
    <Parameter name="client"></Parameter>
carlos@979
    93
    <Parameter name="adLanguage"/>
david@3940
    94
    <Parameter name="client"></Parameter>
carlos@979
    95
    <Parameter name="adLanguage"/>
david@3940
    96
    <Parameter name="client"></Parameter>
carlos@979
    97
    <Parameter name="adLanguage"/>
david@3940
    98
    <Parameter name="client"></Parameter>
carlos@979
    99
    <Parameter name="adLanguage"/>
david@3940
   100
    <Parameter name="client"></Parameter>
carlos@0
   101
    <Parameter name="dateFrom"></Parameter>
carlos@0
   102
    <Parameter name="dateTo"></Parameter>
carlos@0
   103
    <Parameter name="client"></Parameter>
carlos@0
   104
  </SqlMethod>
carlos@0
   105
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
   106
      <SqlMethodComment>Create a registry</SqlMethodComment>
carlos@0
   107
      <Sql></Sql>
carlos@0
   108
  </SqlMethod>
juanpablo@152
   109
</SqlClass>