src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql
author Armaignac <collazoandy4@gmail.com>
Tue, 27 Nov 2018 09:17:11 +0100
changeset 35159 1a4041af1fa1
parent 33201 1f43ecba24a4
permissions -rw-r--r--
Fixes issue 36556: Organization filter in Orders Awaiting Invoice Report does not
work properly for summary level organizations

Organization filter in Orders Awaiting Invoice Report does not work properly for
summary level organizations, When parent organization is selected as a filter,
records for its child organization are not shown in the report.

The Organization child tree is now set as a filter for the report query.
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
priya@9072
     5
 * Version  1.1  (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. 
ggi@6701
    14
 * The Initial Developer of the Original Code is Openbravo SLU 
atul@33201
    15
 * All portions are Copyright (C) 2001-2018 Openbravo SLU 
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
carlos@0
    24
carlos@0
    25
<SqlClass name="ReportOrderNotInvoiceData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    26
  <SqlClassComment></SqlClassComment>
carlos@0
    27
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    28
    <SqlMethodComment></SqlMethodComment>
carlos@0
    29
    <Sql>
carlos@0
    30
    <![CDATA[
atul@33201
    31
     SELECT ORGNAME, C_BPARTNER_ID, BPARTNERNAME, C_ORDER_ID, DOCUMENTNO,
atul@33201
    32
       DATEORDERED, GRANDTOTAL, CONVGRANDTOTAL, INVOICERULE, LINE, PRODUCT, PRICE, CONVPRICE,
atul@33201
    33
       QTYORDERED, UOMSYMBOL, TAX, TAXBASE, CONVTAXBASE, LINENETAMT, CONVLINENETAMT,
atul@33201
    34
       ORDERCURRENCYSYM, TRANSCURRENCYIDORDER, TRANSDATEORDER, TRANSCLIENTIDORDER, TRANSORGIDORDER,
atul@33201
    35
       LINECURRENCYSYM, TRANSCURRENCYIDLINE, TRANSDATELINE, TRANSCLIENTIDLINE,
atul@33201
    36
       TRANSORGIDLINE, C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, C_CURRENCY_ISOSYM(?) AS CONVISOSYM
atul@33201
    37
     FROM
atul@33201
    38
       (
atul@33201
    39
         WITH pendingOrders AS
atul@33201
    40
         (
atul@33201
    41
           SELECT C.C_ORDER_ID, C.DOCUMENTNO, C.DATEORDERED, C.GRANDTOTAL,
atul@33201
    42
             C.C_CURRENCY_ID, C.AD_CLIENT_ID, C.AD_ORG_ID, C.C_BPARTNER_ID, C.INVOICERULE,
atul@33201
    43
             CL.LINE, CL.AD_CLIENT_ID AS CL_AD_CLIENT_ID, CL.AD_ORG_ID AS CL_AD_ORG_ID,
atul@33201
    44
             CL.C_CURRENCY_ID AS CL_C_CURRENCY_ID, CL.LINENETAMT, CL.QTYORDERED,
atul@33201
    45
             CL.QTYINVOICED, CL.PRICEACTUAL, CL.M_PRODUCT_ID, CL.C_UOM_ID,
atul@33201
    46
             AD_REF_LIST.AD_REF_LIST_ID,
atul@33201
    47
             AD_REF_LIST.NAME AS INVOICERULENAME
atul@33201
    48
           FROM c_order C JOIN C_Orderline CL on CL.C_Order_ID = C.C_Order_ID
atul@33201
    49
             JOIN AD_REF_LIST ON C.INVOICERULE = AD_REF_LIST.VALUE
atul@33201
    50
             AND AD_REF_LIST.AD_REFERENCE_ID = '150'
atul@33201
    51
           WHERE  C.invoicerule <> 'N'
atul@33201
    52
             AND C.processed = 'Y'
atul@33201
    53
             AND C.docstatus NOT IN ( 'CJ', 'UE', 'CA', 'DR', 'CL')
atul@33201
    54
             AND C.issotrx = 'Y'
atul@33201
    55
             AND OBEQUALS(cl.qtyordered, cl.qtyinvoiced) = 'N'
atul@33201
    56
             AND C.AD_Client_ID IN ('1')
atul@33201
    57
             AND C.AD_ORG_ID IN ('1')
atul@33201
    58
             AND 1=1
atul@33201
    59
        )
atul@33201
    60
        SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME,
atul@33201
    61
          pendingOrders.C_ORDER_ID, pendingOrders.DOCUMENTNO, pendingOrders.DATEORDERED,
atul@33201
    62
          pendingOrders.GRANDTOTAL,
atul@33201
    63
          CASE WHEN pendingorders.C_CURRENCY_ID = ? THEN pendingorders.GRANDTOTAL ELSE C_CURRENCY_CONVERT(pendingorders.GRANDTOTAL, pendingorders.C_CURRENCY_ID, ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingorders.AD_CLIENT_ID, pendingorders.AD_ORG_ID) END AS CONVGRANDTOTAL,
atul@33201
    64
          CASE WHEN pendingOrders.INVOICERULE = 'S' THEN COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME)||' ('||C_INVOICESCHEDULE.NAME||')'
atul@33201
    65
               ELSE COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME) END AS INVOICERULE,
atul@33201
    66
          pendingOrders.LINE AS LINE,
atul@33201
    67
          COALESCE(P.NAME, P.DESCRIPTION) AS PRODUCT, pendingOrders.PRICEACTUAL AS PRICE,
atul@33201
    68
          CASE WHEN COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) = ? THEN pendingOrders.PRICEACTUAL ELSE C_CURRENCY_CONVERT(pendingOrders.PRICEACTUAL, COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingOrders.CL_AD_CLIENT_ID, pendingOrders.CL_AD_ORG_ID) END AS CONVPRICE,
atul@33201
    69
          pendingOrders.QTYORDERED - pendingOrders.QTYINVOICED AS QTYORDERED,
atul@33201
    70
          U.UOMSYMBOL, NULL AS TAX, NULL AS TAXBASE, NULL AS CONVTAXBASE, pendingOrders.LINENETAMT,
atul@33201
    71
          CASE WHEN COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) = ? THEN pendingOrders.LINENETAMT ELSE C_CURRENCY_CONVERT(pendingOrders.LINENETAMT, COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingOrders.CL_AD_CLIENT_ID, pendingOrders.CL_AD_ORG_ID) END AS CONVLINENETAMT,
atul@33201
    72
          C_CURRENCY_SYMBOL(pendingorders.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
atul@33201
    73
          pendingorders.C_CURRENCY_ID AS TRANSCURRENCYIDORDER, pendingorders.DATEORDERED AS TRANSDATEORDER,
atul@33201
    74
          pendingorders.AD_CLIENT_ID AS TRANSCLIENTIDORDER, pendingorders.AD_ORG_ID AS TRANSORGIDORDER,
atul@33201
    75
          C_CURRENCY_SYMBOL(COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
atul@33201
    76
          COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
atul@33201
    77
          TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())) AS TRANSDATELINE,
atul@33201
    78
          pendingOrders.CL_AD_CLIENT_ID AS TRANSCLIENTIDLINE, pendingOrders.CL_AD_ORG_ID AS TRANSORGIDLINE
atul@33201
    79
        FROM pendingOrders join m_product p on p.m_product_id = pendingOrders.m_product_id
atul@33201
    80
          join c_uom u on u.c_uom_id = pendingOrders.c_uom_id
atul@33201
    81
          LEFT JOIN AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
atul@33201
    82
            AND AD_REF_LIST_TRL.AD_LANGUAGE = ?,
atul@33201
    83
          C_BPARTNER LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
atul@33201
    84
          AD_ORG
atul@33201
    85
        WHERE pendingorders.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
atul@33201
    86
          AND pendingorders.AD_ORG_ID = AD_ORG.AD_ORG_ID
atul@33201
    87
atul@33201
    88
       UNION ALL
atul@33201
    89
atul@33201
    90
         SELECT MAX(AD_ORG.NAME) AS ORGNAME, MAX(C_BPARTNER.C_BPARTNER_ID) AS C_BPARTNER_ID, MAX(C_BPARTNER.NAME) AS BPARTNERNAME,
atul@33201
    91
           pendingOrders.C_ORDER_ID AS C_ORDER_ID, MAX(pendingOrders.DOCUMENTNO) AS DOCUMENTNO,
atul@33201
    92
           MAX(pendingOrders.DATEORDERED) AS DATEORDERED, MAX(pendingOrders.GRANDTOTAL) AS GRANDTOTAL,
atul@33201
    93
           CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(pendingOrders.GRANDTOTAL)
atul@33201
    94
             ELSE C_CURRENCY_CONVERT(MAX(pendingOrders.GRANDTOTAL), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(pendingOrders.AD_CLIENT_ID), MAX(pendingOrders.AD_ORG_ID)) END AS CONVGRANDTOTAL,
atul@33201
    95
           CASE WHEN MAX(pendingOrders.INVOICERULE) = 'S'
atul@33201
    96
             THEN COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME))||' ('||MAX(C_INVOICESCHEDULE.NAME)||')'
atul@33201
    97
		     ELSE COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME)) END AS INVOICERULE,
atul@33201
    98
           NULL AS LINE, MAX(C_TAX.NAME) AS PRODUCT, NULL AS PRICE, NULL AS CONVPRICE, NULL AS QTYORDERED, NULL AS UOMSYMBOL,
atul@33201
    99
           MAX(C_TAX.RATE) AS TAX, MAX(C_ORDERTAX.TAXBASEAMT) AS TAXBASE,
atul@33201
   100
           CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(C_ORDERTAX.TAXBASEAMT) ELSE C_CURRENCY_CONVERT(MAX(C_ORDERTAX.TAXBASEAMT), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(C_ORDERTAX.AD_CLIENT_ID), MAX(C_ORDERTAX.AD_ORG_ID)) END AS CONVTAXBASE,
atul@33201
   101
           MAX(C_ORDERTAX.TAXAMT) AS LINENETAMT,
atul@33201
   102
           CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(C_ORDERTAX.TAXAMT) ELSE C_CURRENCY_CONVERT(MAX(C_ORDERTAX.TAXAMT), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(C_ORDERTAX.AD_CLIENT_ID), MAX(C_ORDERTAX.AD_ORG_ID)) END AS CONVLINENETAMT,
atul@33201
   103
           C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS ORDERCURRENCYSYM, MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDORDER,
atul@33201
   104
           MAX(pendingOrders.DATEORDERED) AS TRANSDATEORDER, MAX(pendingOrders.AD_CLIENT_ID) AS TRANSCLIENTIDORDER,
atul@33201
   105
           MAX(pendingOrders.AD_ORG_ID) AS TRANSORGIDORDER, C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
atul@33201
   106
           MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())) AS TRANSDATELINE,
atul@33201
   107
           MAX(C_ORDERTAX.AD_CLIENT_ID) AS TRANSCLIENTIDLINE, MAX(C_ORDERTAX.AD_ORG_ID) AS TRANSORGIDLINE
atul@33201
   108
         FROM C_BPARTNER LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
atul@33201
   109
           C_ORDERTAX, AD_ORG, C_TAX,
atul@33201
   110
           pendingOrders left join AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
atul@33201
   111
           AND AD_REF_LIST_TRL.AD_LANGUAGE = ?
atul@33201
   112
         WHERE pendingOrders.C_ORDER_ID = C_ORDERTAX.C_ORDER_ID
atul@33201
   113
           AND pendingOrders.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
atul@33201
   114
           AND C_ORDERTAX.C_TAX_ID = C_TAX.C_TAX_ID
atul@33201
   115
           AND pendingOrders.AD_ORG_ID = AD_ORG.AD_ORG_ID
atul@33201
   116
         GROUP BY pendingOrders.C_ORDER_ID, C_ORDERTAX.C_TAX_ID
atul@33201
   117
     ) AAA ORDER BY ORGNAME, BPARTNERNAME, DATEORDERED DESC, DOCUMENTNO, LINE
atul@33201
   118
     ]]></Sql>
rafael@1868
   119
    <Parameter name="cCurrencyConv"/>
rafael@1868
   120
	<Parameter name="cCurrencyConv"/>
atul@33201
   121
	<Parameter name="adUserClient" type="replace" optional="true" after="C.AD_Client_ID IN (" text="'1'"/>
atul@33201
   122
	<Parameter name="adUserOrg" type="replace" optional="true" after="C.AD_ORG_ID IN (" text="'1'"/>
atul@33201
   123
	<Parameter name="cBpartnerId" optional="true" after="1=1">AND C.C_BPARTNER_ID = ?</Parameter>
collazoandy4@35159
   124
	<Parameter name="cOrgId" optional="true" type="argument" after="AND 1=1"><![CDATA[AND C.AD_ORG_ID IN ]]></Parameter>
atul@33201
   125
	<Parameter name="invoiceRule" optional="true" after="1=1">AND C.INVOICERULE = ?</Parameter>
atul@33201
   126
	<Parameter name="dateFrom" optional="true" after="1=1"><![CDATA[ AND C.DATEORDERED >= to_date(?) ]]></Parameter>
atul@33201
   127
	<Parameter name="dateTo" optional="true" after="1=1"><![CDATA[ AND C.DATEORDERED < to_date(?) ]]></Parameter>
rafael@1868
   128
	<Parameter name="cCurrencyConv"/>
rafael@1868
   129
	<Parameter name="cCurrencyConv"/>
rafael@1868
   130
	<Parameter name="cCurrencyConv"/>
atul@33201
   131
	<Parameter name="cCurrencyConv"/>
atul@33201
   132
	<Parameter name="cCurrencyConv"/>
atul@33201
   133
	<Parameter name="cCurrencyConv"/>
atul@33201
   134
	<Parameter name="adLanguage"/>
atul@33201
   135
	<Parameter name="cCurrencyConv"/>
atul@33201
   136
	<Parameter name="cCurrencyConv"/>
atul@33201
   137
    <Parameter name="cCurrencyConv"/>
atul@33201
   138
    <Parameter name="cCurrencyConv"/>
atul@33201
   139
    <Parameter name="cCurrencyConv"/>
atul@33201
   140
    <Parameter name="cCurrencyConv"/>
carlos@0
   141
    <Parameter name="adLanguage"/>
carlos@0
   142
  </SqlMethod>
carlos@0
   143
  <SqlMethod name="bPartnerDescription" type="preparedStatement" return="String" default="">
carlos@0
   144
    <SqlMethodComment></SqlMethodComment>
carlos@0
   145
    <Sql>
carlos@0
   146
    SELECT max(NAME) as name FROM C_BPARTNER 
juanpablo@1605
   147
    WHERE C_BPARTNER_ID = ?
carlos@0
   148
    </Sql>
carlos@0
   149
    <Parameter name="cBpartnerId"/>
carlos@0
   150
  </SqlMethod>
carlos@0
   151
</SqlClass>