src/org/openbravo/erpCommon/ad_reports/ReportNotPosted_data.xsql
changeset 3945 02f5d14bcacb
parent 3940 df2da712501b
child 4417 9cb188c00976
equal deleted inserted replaced
3944:497df2a505ef 3945:02f5d14bcacb
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    11  * License for the specific  language  governing  rights  and  limitations
    11  * License for the specific  language  governing  rights  and  limitations
    12  * under the License. 
    12  * under the License. 
    13  * The Original Code is Openbravo ERP. 
    13  * The Original Code is Openbravo ERP. 
    14  * The Initial Developer of the Original Code is Openbravo SL 
    14  * The Initial Developer of the Original Code is Openbravo SL 
    15  * All portions are Copyright (C) 2001-2006 Openbravo SL 
    15  * All portions are Copyright (C) 2001-2009 Openbravo SL 
    16  * All Rights Reserved. 
    16  * All Rights Reserved. 
    17  * Contributor(s):  ______________________________________.
    17  * Contributor(s):  ______________________________________.
    18  ************************************************************************
    18  ************************************************************************
    19 -->
    19 -->
    20 <SqlClass name="ReportNotPostedData" package="org.openbravo.erpCommon.ad_reports">
    20 <SqlClass name="ReportNotPostedData" package="org.openbravo.erpCommon.ad_reports">
    23     <SqlMethodComment>Select for relation</SqlMethodComment>
    23     <SqlMethodComment>Select for relation</SqlMethodComment>
    24     <Sql>
    24     <Sql>
    25     <![CDATA[
    25     <![CDATA[
    26         select documentno, dateacct, name as description, GRANDTOTAL  as amount, document as doctype
    26         select documentno, dateacct, name as description, GRANDTOTAL  as amount, document as doctype
    27         from
    27         from
    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
    28         (select ad_client_id, documentno, dateacct, ad_column_identifier('C_Bpartner',to_char(C_bpartner_id), TO_CHAR(?)) as name, GRANDTOTAL, coalesce(to_char((select printname from c_doctype_trl where c_doctype_id=c_invoice.c_doctype_id and ad_language=to_char(?) and ad_client_id=?)), 'Sales Invoices') as document
    29         from c_invoice
    29         from c_invoice
    30         where processed = 'Y'
    30         where processed = 'Y'
    31         and posted = 'N'
    31         and posted = 'N'
    32         and issotrx = 'Y'
    32         and issotrx = 'Y'
    33         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='318' and ad_client_id=?)
    33         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='318' and ad_client_id=?)
    34         union
    34         union
    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
    35         select ad_client_id, documentno, dateacct, ad_column_identifier('C_Bpartner',to_char(C_bpartner_id), TO_CHAR(?)) as name, 0 as GRANDTOTAL, coalesce(to_char((select printname from c_doctype_trl where c_doctype_id=m_inout.c_doctype_id and ad_language=to_char(?) and ad_client_id=?)), 'Goods Receipts') as document
    36         from m_inout
    36         from m_inout
    37         where processed = 'Y'
    37         where processed = 'Y'
    38         and posted = 'N'
    38         and posted = 'N'
    39         and issotrx = 'N'
    39         and issotrx = 'N'
    40         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='319' and ad_client_id=?)
    40         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='319' and ad_client_id=?)
    41         union
    41         union
    42         select ad_client_id, documentno, dateacct, ad_column_identifier('C_Bpartner',to_char(C_bpartner_id), TO_CHAR(?)), GRANDTOTAL, 'Purchase Invoices'
    42         select ad_client_id, documentno, dateacct, ad_column_identifier('C_Bpartner',to_char(C_bpartner_id), TO_CHAR(?)), GRANDTOTAL, coalesce(to_char((select printname from c_doctype_trl where c_doctype_id=c_invoice.c_doctype_id and ad_language=to_char(?) and ad_client_id=?)), 'Purchase Invoices') as document
    43         from c_invoice
    43         from c_invoice
    44         where processed = 'Y'
    44         where processed = 'Y'
    45         and posted = 'N'
    45         and posted = 'N'
    46         and issotrx = 'N'
    46         and issotrx = 'N'
    47         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='318' and ad_client_id=?)
    47         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='318' and ad_client_id=?)
    48         union
    48         union
    49         select ad_client_id, name, dateacct, ad_column_identifier('C_Cash',to_char(C_Cash_id), TO_CHAR(?)), c_cash.STATEMENTDIFFERENCE, 'Cash Journal'
    49         select ad_client_id, name, dateacct, ad_column_identifier('C_Cash',to_char(C_Cash_id), TO_CHAR(?)), c_cash.STATEMENTDIFFERENCE, coalesce(to_char((select printname from c_doctype_trl where c_doctype_id=(select min(c_doctype_id) from c_doctype where ad_client_id=? and docbasetype='CMC') and ad_language=to_char(?) and ad_client_id=?)), 'Cash Journal') as document
    50         from C_Cash
    50         from C_Cash
    51         where processed = 'Y'
    51         where processed = 'Y'
    52         and posted = 'N'
    52         and posted = 'N'
    53         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='407' and ad_client_id=?)
    53         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='407' and ad_client_id=?)
    54         union
    54         union
    55         select ad_client_id, name, statementdate, ad_column_identifier('C_Bankstatement',to_char(C_bankstatement_id), TO_CHAR(?)), STATEMENTDIFFERENCE, 'Bank Statements'
    55         select ad_client_id, name, statementdate, ad_column_identifier('C_Bankstatement',to_char(C_bankstatement_id), TO_CHAR(?)), STATEMENTDIFFERENCE, coalesce(to_char((select printname from c_doctype_trl where c_doctype_id=(select min(c_doctype_id) from c_doctype where ad_client_id=? and docbasetype='CMB') and ad_language=to_char(?) and ad_client_id=?)), 'Bank Statements') as document
    56         from c_bankstatement
    56         from c_bankstatement
    57         where processed = 'Y'
    57         where processed = 'Y'
    58         and posted = 'N'
    58         and posted = 'N'
    59         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='392' and ad_client_id=?)
    59         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='392' and ad_client_id=?)
    60         union
    60         union
    61         select ad_client_id, documentno, dateacct, ad_column_identifier('C_Settlement',to_char(C_Settlement_id), TO_CHAR(?)), generatedamt, 'Manual Settlements'
    61         select ad_client_id, documentno, dateacct, ad_column_identifier('C_Settlement',to_char(C_Settlement_id), TO_CHAR(?)), generatedamt, coalesce(to_char((select printname from c_doctype_trl where c_doctype_id=c_Settlement.c_doctype_id and ad_language=to_char(?) and ad_client_id=?)), 'Manual Settlements') as document
    62         from c_Settlement
    62         from c_Settlement
    63         where processed = 'Y'
    63         where processed = 'Y'
    64         and posted = 'N'
    64         and posted = 'N'
    65         and settlementtype = 'I'
    65         and settlementtype = 'I'
    66         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='800019' and ad_client_id=?)
    66         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='800019' and ad_client_id=?)
    67         union
    67         union
    68         select ad_client_id, documentno, dateacct, ad_column_identifier('C_Settlement',to_char(C_Settlement_id), TO_CHAR(?)), generatedamt, 'Settlements'
    68         select ad_client_id, documentno, dateacct, ad_column_identifier('C_Settlement',to_char(C_Settlement_id), TO_CHAR(?)), generatedamt, coalesce(to_char((select printname from c_doctype_trl where c_doctype_id=c_Settlement.c_doctype_id and ad_language=to_char(?) and ad_client_id=?)), 'Settlements') as document
    69         from c_Settlement
    69         from c_Settlement
    70         where processed = 'Y'
    70         where processed = 'Y'
    71         and posted = 'N'
    71         and posted = 'N'
    72         and settlementtype <> 'I'
    72         and settlementtype <> 'I'
    73         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='800019' and ad_client_id=?)
    73         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='800019' and ad_client_id=?)
    74         union
    74         union
    75         select ad_client_id, documentno, dateacct, ad_column_identifier('GL_Journal',to_char(GL_Journal_id), TO_CHAR(?)), totaldr, 'GL Journal'
    75         select ad_client_id, documentno, dateacct, ad_column_identifier('GL_Journal',to_char(GL_Journal_id), TO_CHAR(?)), totaldr, coalesce(to_char((select printname from c_doctype_trl where c_doctype_id=GL_Journal.c_doctype_id and ad_language=to_char(?) and ad_client_id=?)), 'GL Journal') as document
    76         from GL_Journal
    76         from GL_Journal
    77         where processed = 'Y'
    77         where processed = 'Y'
    78         and posted = 'N'
    78         and posted = 'N'
    79         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='224' and ad_client_id=?)) AAA
    79         and 'Y'=(select isactive from c_acctschema_table where ad_table_id='224' and ad_client_id=?)) AAA
    80         where dateacct >= ?
    80         where dateacct >= ?
    81         and dateacct <= ?
    81         and dateacct <= ?
    82         and ad_client_id = ?
    82         and ad_client_id = ?
    83         order by  document, dateacct, name
    83         order by  document, dateacct, name
    84       ]]></Sql>
    84       ]]></Sql>
    85     <Parameter name="adLanguage"/>
    85     <Parameter name="adLanguage"/>
       
    86     <Parameter name="adLanguage"/>
       
    87     <Parameter name="client"></Parameter>
       
    88     <Parameter name="client"></Parameter>
       
    89     <Parameter name="adLanguage"/>
       
    90     <Parameter name="adLanguage"/>
       
    91     <Parameter name="client"></Parameter>
       
    92     <Parameter name="client"></Parameter>
       
    93     <Parameter name="adLanguage"/>
       
    94     <Parameter name="adLanguage"/>
       
    95     <Parameter name="client"></Parameter>
    86     <Parameter name="client"></Parameter>
    96     <Parameter name="client"></Parameter>
    87     <Parameter name="adLanguage"/>
    97     <Parameter name="adLanguage"/>
    88     <Parameter name="client"></Parameter>
    98     <Parameter name="client"></Parameter>
    89     <Parameter name="adLanguage"/>
    99     <Parameter name="adLanguage"/>
    90     <Parameter name="client"></Parameter>
   100     <Parameter name="client"></Parameter>
    91     <Parameter name="adLanguage"/>
       
    92     <Parameter name="client"></Parameter>
   101     <Parameter name="client"></Parameter>
    93     <Parameter name="adLanguage"/>
   102     <Parameter name="adLanguage"/>
    94     <Parameter name="client"></Parameter>
   103     <Parameter name="client"></Parameter>
    95     <Parameter name="adLanguage"/>
   104     <Parameter name="adLanguage"/>
    96     <Parameter name="client"></Parameter>
   105     <Parameter name="client"></Parameter>
       
   106     <Parameter name="client"></Parameter>
       
   107     <Parameter name="adLanguage"/>
    97     <Parameter name="adLanguage"/>
   108     <Parameter name="adLanguage"/>
    98     <Parameter name="client"></Parameter>
   109     <Parameter name="client"></Parameter>
       
   110     <Parameter name="client"></Parameter>
    99     <Parameter name="adLanguage"/>
   111     <Parameter name="adLanguage"/>
       
   112     <Parameter name="adLanguage"/>
       
   113     <Parameter name="client"></Parameter>
       
   114     <Parameter name="client"></Parameter>
       
   115     <Parameter name="adLanguage"/>
       
   116     <Parameter name="adLanguage"/>
       
   117     <Parameter name="client"></Parameter>
   100     <Parameter name="client"></Parameter>
   118     <Parameter name="client"></Parameter>
   101     <Parameter name="dateFrom"></Parameter>
   119     <Parameter name="dateFrom"></Parameter>
   102     <Parameter name="dateTo"></Parameter>
   120     <Parameter name="dateTo"></Parameter>
   103     <Parameter name="client"></Parameter>
   121     <Parameter name="client"></Parameter>
   104   </SqlMethod>
   122   </SqlMethod>