src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql
author Javi Armendáriz <katanagari@gmail.com>
Fri, 24 Jan 2020 08:55:59 +0100
changeset 37000 c5ff57ca6732
parent 35159 1a4041af1fa1
permissions -rw-r--r--
FIX-42957: Update package-lock to fix npm vulnerabilities.

This also updates linter/formatter scripts to use npm ci instead of install.
This ensures that package-lock.json won't be modified by those executions.
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>